@lssm/integration.job-application-store

PostgreSQL persistence for the privacy-isolated candidate application context. It stores only encrypted/opaque candidate references, immutable lifecycle receipts, exact effect reservations, inbox aliases, verification challenges, and outbox records.

The runtime role is a non-owner and every table has FORCE RLS across ecosystem, tenant, workspace, candidate, and deployment scope. The deployment worker can claim work items across candidates only within one exact ecosystem, tenant, workspace, and deployment; the claimed row restores the candidate scope before domain execution. Worker credentials have no direct queue or effect-reservation DML. Narrowly granted functions enforce the complete composite identity, live lease, owner, and fence for claim, heartbeat, completion, failure, and effect reservation. No wider access exists for profiles, accounts, inboxes, effects, or receipts.

Queue digest migration is expand/backfill/verify/activate: add the nullable column and partial index, deploy digest-writing producers, run bounded resumable backfill batches to zero, observe mixed versions, then activate NOT NULL only in a later release.

Migration 0008_browser_execution_state.sql adds candidate-scoped browser bindings, immutable signed account-workflow generations, and digest-idempotent browser evidence. Active binding/workflow indexes are rebuildable current-state seams while superseded rows retain replay history. Exact replays are distinct from payload/signature collisions. Candidate deletion clears opaque session handles, workflow payloads/signatures, and evidence metadata while preserving tombstone digests and the deletion receipt. It captures authorized document revision references before the aggregate tombstone erases them, cryptographically clears matching candidate credentials, and tombstones document metadata in the same transaction.

The hermetic PGlite proof applies migrations 0001–0015 in order, including queue functions, browser state, encrypted material, documents, alias routing, and webhook ingress. The material migration owns its bounded JSON-object helper inside job_application_core; it does not rely on a test-only or nonexistent PostgreSQL built-in. Migration 0014 reconciles the legacy document columns with the canonical payload-revision writer through an additive nullable-column upgrade, activated payload constraint, and current-document index. Migration 0015 captures external object, candidate-material, browser-session, inbox-content, and export references into a private FORCE-RLS deletion ledger before database scrubbing. Deployment workers can claim and settle tasks only through owner/fence/lease-checked functions; the fifteen-minute due index makes propagation breaches observable.

bun run test:postgres:docker runs the same chain against a pinned disposable PostgreSQL 17 image and authenticates as separate runtime, read-only, webhook, and worker login roles. It enumerates every private table to prove ENABLE plus FORCE RLS, proves that no application role owns a table or has SUPERUSER/BYPASSRLS, verifies transaction-local scope reset, and exercises negative cross-candidate, secret, webhook-route, and direct-worker-access cases. The disposable proof uses a unique host temporary directory and never reuses or prunes Docker state.

Inbound Resend routing uses a dedicated job_application_webhook role. It has schema usage plus SELECT-only access to deployment-scoped HMAC alias routes; it cannot read candidate applications or mutate routes. Executable PGlite tests switch into the runtime, read-only, and webhook roles and prove FORCE-RLS candidate isolation and negative write permissions. Verification work persists only opaque inbox/material references and collision-sensitive request digests.

Migration 0013_webhook_scoped_ingress.sql permits the dedicated webhook role to append inbox events/messages, verification challenges, and matching outbox events only when an active, unexpired alias route binds the same ecosystem, tenant, workspace, candidate, deployment, and application. Durable verification execution atomically moves one pending challenge to reserved; completion, retry release, and takeover are guarded state transitions.