Runbook — repairing memories stranded pending with no approval row

Tool: cmd/tools/memory-approval-repair Issue: #2323 · Root cause: #2286 · Pattern precedent: #2287 / cmd/tools/memory-backfill

When to run this

A memory sits at agent_memory.status='pending' and has no governance_approvals row at all.

That combination is an absorbing state. The approvals inbox lists approval rows, so the memory never renders; RecordDecision keys on an approval_id that does not exist; and the memory_extraction_runs ledger makes re-extraction a no-op, so nothing will re-emit it. The memory is quarantined forever.

It is the residue of #2286: the memory-review requester set the org on the request body, but the orchestrator authenticates tenancy from gRPC metadata, so every RequestApproval died Unauthenticated. The extraction consumer swallows that failure by design (a review-request failure must not fail an extraction), so the memory persisted and the approval silently never existed. #2286 is fixed; the fix repairs no history.

Run this once per environment after the #2286 fix has deployed, and whenever the detection query below returns rows.

#2286 is not the only producer. cmd/tools/memory-extraction-replay leaves the review-requester deliberately unwired, so every dead-letter replay that recovers a quarantined memory strands it here — by design, not by regression (#2383). This tool is therefore a mandatory step of the replay procedure, not just a one-off historical repair: see memory-extraction-dead-letter-replay.md step 4. On 2026-07-31 a replay of 28 dead letters stranded 21 memories this way.

Detection

SELECT m.id, m.memory_type, m.status, m.created_at
FROM agent_memory m
LEFT JOIN governance_approvals g
       ON g.action_type = 'memory_review'
      AND (g.target = m.id::text OR g.metadata->>'memory_id' = m.id::text)
WHERE m.status = 'pending' AND g.id IS NULL
ORDER BY m.created_at;

This is exactly the tool's predicate. Cluster the results by created_at — a tight window almost always maps to a specific incident window (on beta-dev: 13:03–13:21 on 2026-07-28).

Procedure

1. Dry run first — always

A dry run needs only DATABASE_URL. It does not contact the governance surface and cannot open an approval.

DATABASE_URL=postgres://... \
  go run ./cmd/tools/memory-approval-repair -dry-run

It logs one line per candidate with memory_id, org_id, agent_id, memory_type, memory_created_at. Confirm the count and the created_at window match the incident you are repairing.

2. Repair

DATABASE_URL=postgres://... \
AGENT_ORCHESTRATOR_GRPC_ADDR=agent-orchestrator:50051 \
TLS_CERT=... TLS_KEY=... TLS_CA_CERT=... \
  go run ./cmd/tools/memory-approval-repair \
    -org <org-uuid> [-agent <agent-uuid>] [-memory <uuid,uuid,uuid>] [-limit N]

For a first live run, narrow the blast radius: -memory with the exact ids from the dry run, or -limit 1 to repair one and inspect it in the inbox before doing the rest.

3. Verify

Re-run the detection query — it should return zero rows. Then confirm the memories now render in the approvals inbox and that a human decision drives them to active or rejected.

Flags

Flag	Default	Notes
-dry-run	false	Enumerate only. Needs no governance connection.
-org	—	Narrows the predicate and sets app.org_id on the read tx. Not sufficient alone — see below.
-agent	—	Narrows the predicate and sets app.agent_id on the read tx. Not sufficient alone — see below.
-memory	—	Comma-separated ids. A filter, never an override: a healthy memory named here is still skipped.
-batch	200	Keyset page size.
-limit	0	Max rows to repair. 0 = no limit.
-deadline	0	Review window from now. 0 inherits the review gate default (7d). Must be positive.

Properties you can rely on

• Idempotent. A repaired row leaves the predicate, so a re-run is a cheap no-op. Even if two operators run it concurrently, ix_gov_approvals_dedup collapses the second request onto the existing pending approval rather than creating a duplicate.
• Resumable. Keyset-paged by memory id; interrupt it and re-run.
• Consumer-independent. Needs no running extraction consumer, no Redis, no stream replay. It repairs the table gap regardless of what survived anywhere else.
• Uses the online path. The approval is opened through the same review.Requester the extraction consumer and the upsquad-memory MCP server use, so template, required_clearance, expires_at and the resource dedup key are identical to an online request by construction. There is no raw INSERT anywhere in this tool.
• Non-destructive. It opens an approval; it decides nothing. A human still approves or denies in the inbox.
• Fail-safe on partial failure. One bad row is logged and skipped, stays in the predicate, and is retried by a re-run. The process exits non-zero if any row failed.

Things that will bite you

Read-side RLS — and this paragraph used to be wrong. agent_memory FORCEs an org and agent scoped policy (migration 008):

org_id = app.org_id AND (agent_id = app.agent_id OR app_has_share_grant(id))

It said a misconfigured role "does not report 0 candidates — an unreadable predicate aborts loudly". It does not abort. Measured on a throwaway database as the NOSUPERUSER NOBYPASSRLS app_rw role, with two stranded rows waiting, the tool printed candidates=0 repaired=0 and exited 0 (#3498). The same is true for -org <uuid> on its own: one stranded row in that org, candidates=0, exit 0 — an org-only GUC leaves app.agent_id NULL and the AND's second arm is NULL for every row.

So, since #3498, there are exactly two ways to run this and the tool refuses anything else before it reads:

1. a DB role that BYPASSES RLS — the migration role today — for a cross-org sweep; or
2. both -org and -agent, which set both GUCs on the read transaction.

Not the schema owner. FORCE ROW LEVEL SECURITY removes the owner's exemption, so an owner without BYPASSRLS is refused exactly as app_rw is. -memory is a SQL filter, not a GUC, and never satisfies the policy on its own.

After the #3259 cutover, the app DSN is app_rw — give this invocation the migrate DSN explicitly rather than inheriting the service environment. See docs/runbooks/migration-role-rls-posture.md.

Write-side RLS needs nothing from you. The tool performs no raw write. The approval INSERT happens inside the approval store, which sets app.org_id from the org carried on the request — i.e. from the stranded row itself.

Never backdate -deadline. The scheduler expires overdue approvals and the memory-review handler treats an expiry as a denial, which rejects the memory. An approval backdated to a stranded row's created_at would destroy the exact rows you are recovering. The tool refuses a non-future deadline; do not work around it.

This is not memory-review -op approve. That tool force-activates a memory outside governance and leaves no approval record. Use it only when you explicitly want an ungoverned override. This tool restores the governed path.

Scope boundary

Only memories with no approval row at all are repaired. A pending memory whose approval was denied, expired, or is still outstanding reached governance — its state is a governance outcome, not a lost write — and is deliberately excluded.