DB Schema Migration for Dispatch Identity

[Cho-Geer]

Description

Add the dispatch_key and lookup indices to the dispatch_queue table so that exact-match lease can operate efficiently. This migration must be run before Phase B can function.

Code Reference (§5.2)

SQL Migration

ALTER TABLE dispatch_queue ADD COLUMN dispatch_key TEXT;
ALTER TABLE dispatch_queue ADD COLUMN parent_session_id TEXT;
ALTER TABLE dispatch_queue ADD COLUMN call_id TEXT;
CREATE UNIQUE INDEX IF NOT EXISTS idx_dispatch_queue_key
  ON dispatch_queue(dispatch_key);
CREATE INDEX IF NOT EXISTS idx_dispatch_queue_exact_pending
  ON dispatch_queue(status, agent_type, dag_task_id, prompt_ref_id);

Dispatch Key Deterministic Formula

dispatch_key = sha256(agent_type + "\0" + dag_task_id + "\0" + prompt_sha256 + "\0" + parent_session_id)

New DB Functions (in dispatch-db.ts)

Replace the current two-step enqueue/dequeue with these exact-match APIs:

• dbCreateDispatchAtomic(input): Inserts prompt ref, queue row, context row, and first attempt row in one transaction
• dbLeaseDispatchExact(input): Requires agent_type, dag_task_id, prompt_sha256, and parent_session_id or dispatch_key
• dbConsumeDispatchExact(queue_id, lease_owner, outcome): Requires the currently leased row
• dbFailDispatchExact(queue_id, reason, error): Records failure with exact id
• dbReconcileLegacyDispatchFiles(): One-time migration/doctor only; never used by Task()

Hard Requirement

DB failure is fatal in strict/locked mode. Current dispatch-db.ts:20-23 says operations are non-fatal and callers should fallback to file ops — this must be changed for dispatch identity paths.

Additional Tables (if not existing)

• dispatch_prompt_refs — stores prompt content with prompt_sha256 as key
• dispatch_context — stores parent session, target agent, dag task, domain per dispatch
• dispatch_attempts — stores attempt history with attempt_number, lease_owner, timestamps

Acceptance Criteria

• dispatch_key is unique — duplicate dispatch to same task/hash is idempotent
• Exact lease uses WHERE dispatch_key = ? AND status = 'pending'
• All dispatch lifecycle operations use these new APIs
• Legacy agent_type-only dequeue is removed