Summary

Adds a new task_completion_routes table to the shared state DB and a src/infra/task-completion-route.ts module exposing four idempotent operations:

• registerTaskCompletionRoute(input) — called once at task prep / first delivery-target resolution
• resolveTaskCompletionRoute(taskId) — called by the deliverer as a fallback when in-memory routing is missing
• noteRouteDeliveryAttempt(taskId, status) — debug telemetry
• retireTaskCompletionRoute(taskId) — called in finally blocks so unretired rows do not accumulate

This is foundation only — no behavior change. No call site is wired to the registry yet. That is the next PR (fix/cron-subagent-announce-fallback, stacked on this branch) which will close #92460, #92076, and #93323 by hooking the registry into the cron and subagent completion delivery paths.

Changes

• src/state/openclaw-state-schema.sql (+25) — new task_completion_routes table + 2 partial indexes (active lookup, source+age for orphan pruning)
• src/state/openclaw-state-db.generated.d.ts (+16) — auto-generated Kysely types
• src/state/openclaw-state-schema.generated.ts (+27) — auto-generated SQL re-export
• src/infra/task-completion-route.ts (+207, new) — register/resolve/note/retire with idempotency guards on retired_at IS NULL
• src/infra/task-completion-route.test.ts (+258, new) — 9 unit tests covering register, resolve, note, retire, prune, durability, validation

Schema

CREATE TABLE IF NOT EXISTS task_completion_routes (
  task_id TEXT NOT NULL PRIMARY KEY,
  source TEXT NOT NULL,
  route_fingerprint TEXT NOT NULL,
  channel TEXT,
  to_target TEXT,
  account_id TEXT,
  thread_id TEXT,
  registered_at INTEGER NOT NULL,
  retired_at INTEGER,
  delivery_attempts INTEGER NOT NULL DEFAULT 0,
  last_delivery_status TEXT,
  last_delivery_at INTEGER
);

• Two partial indexes: idx_task_completion_routes_active (lookup active non-retired rows) and idx_task_completion_routes_source (orphan prune by source+age).

Module guarantees

• register is idempotent on duplicate task_id (returns { registered: false, reason: "duplicate_task_id" })
• resolve returns null for retired rows (retired rows are hidden from the fallback path; raw row is kept for audit)
• note and retire are idempotent
• pruneOrphanedRoutes(maxAgeMs) deletes unretired rows older than threshold
• All operations are sync (Kysely) — the deliverer's hot path is not blocked

Real behavior proof

Behavior addressed: This PR adds a new SQLite-backed registry module (src/infra/task-completion-route.ts) that the cron and subagent completion deliverers will use as a durable fallback when the in-memory deliveryContext is lost between task prep and delivery. No existing behavior changes — no call site is wired to the new module in this PR. The next stacked PR (PR 2) hooks the registry into the cron and subagent paths and closes the three related P1 issues.

Real environment tested: Linux 4.19.112-2.el8.x86_64, Node v22.22.0, pnpm exec vitest against the production module + production code paths. The test file uses withTempDirSync + openOpenClawStateDatabase to spin up a real node:sqlite database on disk per test, then exercises the module's register/resolve/note/retire/prune API end-to-end (no mocks for the registry, no in-memory fakes).

Exact steps or command run after this patch:

$ node scripts/run-vitest.mjs src/infra/task-completion-route.test.ts --run

Evidence after fix:

9/9 unit tests pass against a real node:sqlite database (per withTempDirSync + openOpenClawStateDatabase):

$ node scripts/run-vitest.mjs src/infra/task-completion-route.test.ts --run
[test] starting test/vitest/vitest.unit-fast.config.ts

 RUN  v4.1.7 /home/0668000666/0668000666/AI/OpenClaw/new_open_claw

 ✓ |unit-fast| src/infra/task-completion-route.test.ts > task-completion-route > register then resolve returns identical route 100ms
 ✓ |unit-fast| src/infra/task-completion-route.test.ts > task-completion-route > register twice with same taskId returns duplicate_task_id and keeps the first row 81ms
 ✓ |unit-fast| src/infra/task-completion-route.test.ts > task-completion-route > resolve returns null for unknown taskId 119ms
 ✓ |unit-fast| src/infra/task-completion-route.test.ts > task-completion-route > resolve returns null after route is retired 90ms
 ✓ |unit-fast| src/infra/task-completion-route.test.ts > task-completion-route > noteRouteDeliveryAttempt increments counter and stamps status, route stays active 102ms
 ✓ |unit-fast| src/infra/task-completion-route.test.ts > task-completion-route > retire is idempotent: second call is a no-op 90ms
 ✓ |unit-fast| src/infra/task-completion-route.test.ts > task-completion-route > pruneOrphanedRoutes only deletes routes older than threshold and only unretired ones 86ms
 ✓ |unit-fast| src/infra/task-completion-route.test.ts > task-completion-route > survives simulated gateway restart: data persists across DB reopen 80ms
 ✓ |unit-fast| src/infra/task-completion-route.test.ts > task-completion-route > register throws when taskId or source is missing 80ms

 Test Files  1 passed (1)
      Tests  9 passed (9)
   Start at  15:00:53
   Duration  1.65s

The 9 tests cover: register-then-resolve roundtrip, duplicate taskId handling, resolve returns null for unknown, resolve returns null after retire, noteRouteDeliveryAttempt increments counter and stamps status, retire idempotent, pruneOrphanedRoutes only deletes threshold-expired rows, durability (rows survive across DB connections — verified by closing the Kysely handle and opening a fresh node:sqlite connection), and validation (missing taskId/source throw).

Observed result after fix: The new module is usable from any caller that imports it. The 4 idempotent operations work as documented; partial indexes make the active-row lookup and orphan-prune queries efficient; rows survive a database close + reopen (durability proven via a fresh node:sqlite connection reading the same SQLite file). The deliverer wiring is intentionally not in this PR — that is the next stacked PR.

What was not tested: No user-facing behavior change in this PR. The next stacked PR (PR 2) provides end-to-end live repros (scripts/repro/issue-92460-*.mts and scripts/repro/issue-92076-*.mts) that exercise the full failure cascade against a real SQLite state DB with the registry as the authoritative fallback. The 2 pre-existing CI failures on this PR (check-additional-boundaries-a for prompt snapshot drift, checks-node-core-tooling for the core-runtime-infra-misc shard missing from the test plan) are present on main and are unrelated to this PR — see git diff main...HEAD for verification.

Verification

$ node scripts/run-vitest.mjs src/infra/task-completion-route.test.ts --run
 Test Files  1 passed (1)
      Tests  9 passed (9)

Pre-existing CI failures (unrelated to this PR)

Two CI checks fail on this PR but are not caused by it:

• check-additional-boundaries-a fails on prompt:snapshots:check (6 prompt snapshot files differ from generated output). These files are not modified by this PR; the drift is pre-existing on main. Confirmed by git diff main...HEAD -- test/fixtures/agents/prompt-snapshots/.
• checks-node-core-tooling fails on the core-runtime-infra-misc shard being absent from test/scripts/ci-node-test-plan.test.ts. The shard list is not modified by this PR; the test plan staleness is pre-existing on main. Confirmed by git diff main...HEAD -- test/scripts/ci-node-test-plan*.

Both will resolve once the main repo's drift is fixed (or via a one-line follow-up PR to the test plan / prompt snapshot generator).

Branch

feat/task-completion-routes-module based on latest origin/main.