Corpus Writer-Fence Runbook — mutual exclusion + LMDB non-rebuildability

Corpus Writer-Fence Runbook

Status: LIVE (id-138 closed S452). The fence is applied and wired into every id-138 writer — the walk’s pull-sync runs under it in production. The primitive was redesigned once after first landing: the original session-scoped advisory-lock RPCs (corpus_writer_fence_try_acquire / _release, 20260703160400_id138_writer_fence.sql) were empirically defeated by PostgREST connection pooling (S445, live staging — two “concurrent” .rpc() acquires landed on the SAME pooled backend session, where pg_try_advisory_lock is re-entrant, so BOTH returned true). The live primitive is the lease-row CAS model (corpus_writer_fence_lease_acquire/_release, supabase/migrations/20260704140000_id138_writer_fence_lease.sql) — see §2. Audience: a developer wiring a NEW corpus writer onto the fence, or an operator diagnosing a stuck/slow corpus write. Pair with: TECH.md §2.6 R(ops) + §3.4 O (specs/id-138-corpus-durable-home/TECH.md), lib/corpus/writer-fence.ts (TS leg), scripts/cocoindex_pipeline/writer_fence.py (Python leg), and reference/cocoindex-pipeline.md for the pipeline this fence’s busiest writer lives in (the POST /walk route — §3 row 3 below).

1. Why this exists — LMDB non-rebuildability

The VPS ingestion pipeline keeps its incremental-walk state in an on-disk LMDB engine store. That store is the pipeline’s memory of “what has already been walked and extracted” — cocoindex’s incremental re-walk logic depends on it to avoid re-processing unchanged files.

Losing the LMDB store is not a cosmetic problem: it is not rebuildable from the bucket. The bucket holds bytes; it does not hold cocoindex’s own incremental-change bookkeeping. If the LMDB store is lost or corrupted, the next walk silently becomes a full re-extraction of the entire corpus — every source document gets re-processed as if it were new, at full extraction cost (LLM calls, embedding calls, entity resolution — the complete Stage-1..5 pipeline cost, corpus-wide). There is no warning banner for this: the walk just runs, takes far longer than expected, and re-derives rows that were already correct.

The most likely way to lose the store is two writers stepping on the same on-disk state at once — e.g. a pull-sync walk running concurrently with an operator bulk-load into the same bucket/volume namespace, or a write-back racing an upload. The writer-fence primitive below exists specifically to make that class of interleaving impossible, by giving every corpus writer a single, shared “only one of us may write right now” barrier to acquire before touching the bucket or the volume.

Operational takeaway: treat the LMDB engine store as an irreplaceable byte layer, exactly like the corpus bucket itself (TECH.md §3.4 O env-isolation note). It belongs in the same backup/restore drill scope flagged for the bucket (PLAN.md §7 risk table, §8.5 launch gate) — that drill is a roadmap item, not built in this Subtask, but this runbook is the place that names the risk so it isn’t lost.

2. The fence posture — lease-row CAS, try-semantics, never blocking

The live primitive is two Postgres RPCs over a single control-plane lease row (introduced by supabase/migrations/20260704140000_id138_writer_fence_lease.sql, signatures widened by 20260717150000_id128_writer_fence_test_isolation.sql; table corpus_writer_fence_lease, ONE shared row per fence domain — every writer contends for the id138_corpus_writer_fence row, because the hazard being fenced is bucket/volume-level interleaving, not a per-row contention the DB’s own row locks already handle):

public.corpus_writer_fence_lease_acquire(p_holder_token uuid, p_holder text, p_ttl_seconds integer, p_fence_name text) → boolean — a single atomic INSERT … ON CONFLICT (fence_name) DO UPDATE … WHERE <row free or expired> RETURNING (“upsert as CAS”). Two concurrent acquires serialise on the row-level lock regardless of which pooled backend connection each HTTP request lands on — no session affinity is needed, because no state ever lives in a session.
public.corpus_writer_fence_lease_release(p_holder_token uuid, p_holder text, p_fence_name text) → boolean — frees the row ONLY if p_holder_token matches the current holder (fencing-token semantics): a caller whose lease already expired and was taken over by another writer gets false, and can never release someone else’s active lease.

Every parameter after p_holder_token carries a server-side default, so both language legs still call these as they always did. The trailing p_fence_name is a {128.20} test-isolation seam, not a production knob — no production caller passes it (§5).

Why a lease row, not an advisory lock: session-scoped pg_try_advisory_lock is the wrong primitive behind PostgREST/PgBouncer pooling — the S445 live-staging defect showed two supabase-js .rpc() acquire calls landing on the same pooled backend session, where the advisory lock is re-entrant, silently defeating mutual exclusion. (pg_advisory_xact_lock is disqualified too: each .rpc() runs in its own implicit transaction, so an xact-scoped lock releases the instant the acquire RPC returns — before the caller’s critical section even starts.) The lease ROW is the source of truth; both language legs call the SAME primitive, so there is exactly one mutual-exclusion mechanism to reason about.

Why try-semantics, never blocking: a blocking wait over a stateless HTTP RPC call (the TS leg goes via supabase-js .rpc() → PostgREST) would park a PostgREST backend connection for the whole wait — under real contention that exhausts the pool fast. Try-semantics makes “someone else is writing” an immediate, loud busy outcome the caller must handle (abort, or retry with its own backoff) rather than a silent hang.

TTL / crashed-writer recovery: every lease carries an expiry (p_ttl_seconds, server default 3600s). A writer that crashes without releasing blocks others only until the TTL lapses — the next acquire’s WHERE <free or expired> clause takes the row over; recovery is automatic and needs no operator step. Keep the acquire → work → release window short, and pick a TTL comfortably above the critical section’s worst case. No live writer overrides it today — withWriterFence exposes no TTL argument at all (pass one via the low-level acquireWriterFence if you need it), and the Python leg’s writer_fence() is called without ttl_seconds — so every writer currently runs at the 3600s default, including the short single-PUT ones the migration header suggests should tighten their own window.

id-382 release-on-cancel gap — docker stop mid-pass orphans the {138.9} lease (live S507). A docker stop of the cocoindex sidecar MID-PASS does not run Python finally blocks, so the {138.9} pull-sync fence hold is never released by normal critical-section completion. The lease then sits orphaned on the SHARED Staging project for its full 1h TTL, and the NEXT queued run’s walk step starves against it for its entire walk window. Proven on run 30390422726 (starved its full 30-min window inside the shadow of run 30388008506’s teardown-orphaned lease — acquired by the final pump-driven walk seconds before its container was docker-stopped, never released; the lease was released manually via the fence RPC to unblock). The cocoindex-nightly.yml teardown now runs a bounded always() quiesce step that waits (up to 120s) for every started walk to reach a terminal log line (/walk completed / fence busy / update_blocking failed) before containers stop, so the lease is released by normal pass completion. If you see a queued run starve for its full walk window with a still-active id138_corpus_writer_fence row from a prior run, this is the suspect — release the row via the operator escape hatch below.

Operator escape hatch (rare). To clear a lease BEFORE its TTL — a genuinely wedged writer you cannot wait an hour on — delete the row directly as the table owner (postgres; the lease table REVOKEs ALL from anon, authenticated AND service_role, and runs RLS-enabled with no policies, so an app-role connection cannot do this — use the dashboard SQL editor or an owner connection): DELETE FROM public.corpus_writer_fence_lease WHERE fence_name = 'id138_corpus_writer_fence'; This is deliberately manual SQL: there is no RPC force-release, so no client can ever trigger it. Confirm the holder really is dead first (SELECT holder_label, acquired_at, expires_at FROM public.corpus_writer_fence_lease;) — deleting an ACTIVE holder’s row re-opens the exact two-writers-at-once hazard this fence exists to prevent.

Acquire/release protocol (both language legs):

Caller mints a fresh holder_token (UUID) and attempts acquire with an optional holder label (a short string identifying which writer is asking — the live labels are 'write-back', 'write-back-restore', 'upload', 'pull_sync' — observability only; it plays no role in the exclusion logic).
Busy → a normal, expected outcome, not an error — the high-level wrappers surface it as WriterFenceBusyError (both legs); the low-level acquire returns false. The caller aborts its write attempt (or retries later with backoff); it must NOT proceed with the bucket/volume write.
Acquired → the caller owns the fence. It performs its critical section (the bucket/volume write), then releases with the same token.
Release returns false if the token no longer matches the current holder (lease expired + taken over) — a warning to investigate, not a hard failure.

Historical note. The original advisory-lock primitive shipped with a documented KNOWN LIMITATION (PostgREST session affinity) and a flagged lease-row CAS follow-up. The limitation was confirmed empirically at S445 — worse than documented: not just release-affinity drift but re-entrant double-acquire — and the follow-up was built as the full replacement for BOTH legs, not just TS. The advisory-lock RPCs are retired.

3. The writer acquisition map

Every writer that touches the corpus bucket or the VPS volume/LMDB store MUST acquire this fence before writing. Wiring each writer’s acquisition was that writer’s OWN Subtask/Task; all id-138-owned writers are wired and live (id-138 closed S452).

#	Writer	Wiring	Status
1	Write-back (file leg → Storage PUT)	{138.12}	✅ live — `writeBackFileFirst` acquires around the Storage PUT, and again around the compensating restore when the DB leg fails.
2	Upload leg (gate-pass → Storage PUT + minted sd row)	{138.13}	✅ live — the {131.24} upload leg acquires around the Storage PUT + identity mint.
3	Pull-sync (content-hash-gated bucket→volume materialise)	{138.14}	✅ live — acquires around the sync materialise step. The cocoindex incremental WALK runs UNDER the {138.14} pull-sync fence hold — it does NOT acquire separately. The nightly/scheduled walk contends through this same leg (the {128.20} migration note lists it among live contenders).
4	Operator bulk-load	id-45 ({45.7})	⬜ pending — id-45 (the client cutover) is in flight. S443 ratification seam-correction: every “ID-69” reference in TECH.md for this writer reads as id-45-owned — id-69 remains closed; the obligation is re-homed to ledger subtask {45.7}. id-45’s bulk-load MUST acquire this fence; wiring that acquisition is id-45’s own work, not ID-138’s.

Sequencing held the right way round: the barrier ({138.9}) landed before id-45’s {45.7} acquisition — the dependency direction is id-45 → ID-138, never the reverse. The outstanding obligation is {45.7} itself: id-45’s operator bulk-load must not ship as an unfenced writer.

4. Quick reference — calling the fence

TypeScript (lib/corpus/writer-fence.ts):

1
import { withWriterFence, WriterFenceBusyError } from '@/lib/corpus/writer-fence';
2

3
await withWriterFence(supabase, async () => {
4
  // ... Storage PUT / bucket write ...
5
}, 'write-back');
6
// busy fence → throws WriterFenceBusyError (never runs the callback);
7
// release always uses the token withWriterFence minted at acquire.

Or the low-level pair directly — mint the UUID token yourself and release with the SAME token: acquireWriterFence(supabase, holderToken, holder, ttlSeconds?) / releaseWriterFence(supabase, holderToken, holder) (both return boolean; busy/mismatch is false, never thrown). ttlSeconds is the only way to shorten a TS caller’s lease — withWriterFence does not take one.

Python (scripts/cocoindex_pipeline/writer_fence.py):

1
from scripts.cocoindex_pipeline.writer_fence import writer_fence
2

3
async with writer_fence(db_pool, holder="pull_sync") as conn:
4
    ...  # critical section (bucket/volume writes, including the walk)
5
# busy fence → raises WriterFenceBusyError; ttl_seconds is an optional kwarg.

The context manager mints the token internally and holds ONE checked-out connection for the whole span. Or the low-level pair directly on an already-checked-out connection: try_acquire_writer_fence(conn, holder_token, holder) / release_writer_fence(conn, holder_token, holder) — never pass a bare Pool to the low-level functions (see the module docstring).

5. Testing posture

TS unit test (__tests__/lib/corpus/writer-fence.test.ts) — mocked .rpc(), asserts the lease-RPC names/params (token + holder label + optional p_ttl_seconds), that a busy fence returns false from the low-level acquire (never thrown) and surfaces as WriterFenceBusyError from withWriterFence, that release uses the SAME minted token, and that a release failure never masks the guarded callback’s own error. GREEN (no live DB needed).
Python unit test (scripts/tests/test_cocoindex_writer_fence.py) — fake asyncpg pool/connection, same assertions plus the “never releases if never acquired” contract. GREEN.
Live-DB integration test (__tests__/integration/id138-writer-fence.integration.test.ts) — GREEN. Proves mutual exclusion under REAL concurrency (Promise.all of two simultaneous acquires — exactly one wins; try-semantics, neither blocks) plus solo acquire/release round-trips on matching tokens. Since {128.20} (migration 20260717150000_id128_writer_fence_test_isolation.sql) the test passes a per-run-random p_fence_name, operating on an isolated test row instead of the SHARED production fence row — live writer activity (pull-sync, write-back, upload, the nightly walk) can no longer fail the exclusivity assertions spuriously. Production callers omit p_fence_name (server-side default) and are byte-for-byte unaffected.