integration.job-application-store
FORCE-RLS Postgres persistence for candidate-private job applications.
@lssm-tech/integration.job-application-storebun add @lssm-tech/integration.job-application-storeManifest declarations describe the current workspace; they do not verify registry publication or runtime execution.
Package guide
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.
Migration 0018_candidate_profile_fact_retention.sql (P-01f) adds the value-free profile_fact_invalidation_cursors table (FORCE RLS, worker-only writes of the sequence, no DELETE for any role) that persists the invalidation consumer's per-candidate position, and the worker-only profile_fact_invalidation_pending_candidates function that lists, in deployment worker mode, candidate ids with a no-longer-confirmed event after their cursor. It also adds scrub_deleted_application_candidate_records, run inside application deletion as the runtime role: it tombstones the mandate-authorized document revisions (which the runtime role, without UPDATE on documents, could not do directly), removes from every profile fact revision the dependent refs naming the deleted application, its packet or a deleted document revision (facts themselves are candidate profile data and are retained; dependent refs are outside the disclosure snapshot view), and tombstones the matching invalidation records, which stay listed as value-free replay guards. The deletion receipt records database_profile_facts and database_profile_fact_invalidations when they changed. Cursor rows are value-free and retained.
Migration 0019_candidate_profile_fact_revised_invalidations.sql (P-01h) widens the invalidation reason check to rejected, superseded and revised and re-creates the worker-only profile_fact_invalidation_pending_candidates function for both no-longer-confirmed and profile.fact.revised events (same signature and grants), with a partial outbox index for both keys. recordProfileFactInvalidations dispatches by event key; the two events of one withdrawal resolve as siblings (the first stored reason is kept).
Migration 0020_candidate_profile_fact_invalidation_quarantine.sql (P-01i) adds value-free, FORCE-RLS, insert-only profile_fact_invalidation_failures, profile_fact_invalidation_quarantine and profile_fact_invalidation_quarantine_resolutions (worker SELECT/INSERT only). Permanent recording refusals are counted per candidate+event; at the bound (default 3, JOB_APPLICATION_PROFILE_FACT_INVALIDATION_MAX_ATTEMPTS on the worker) the event is quarantined and the consumer advances. While an entry is unresolved, listProfileFactInvalidations refuses with APPLICATION_PACKET_INVALIDATION_QUARANTINED (a typed packet binding refusal), so exact packet submits fail closed until PostgresJobApplicationProfileFactInvalidationQuarantineStoreV1.resolve records an operator resolution.
Migration 0021_profile_fact_invalidation_operator.sql (P-01j) adds two SECURITY DEFINER functions, callable by the runtime and worker roles only, that list and resolve quarantined invalidation events within one exact candidate scope for AuthOS operators (operator_invalidation_read / operator_invalidation_resolve, fresh MFA via the decision assurance); the 0020 table grants are unchanged.
Candidate erasure (P-01k, migration 0022)
Migration 0022 adds per-candidate 32-byte data keys wrapped by the deployment key and a key_version column on the material vault (0 = legacy deployment-key material). A vault constructed with { profileEvidenceDeploymentKey } serves only profile-evidence: refs under the candidate key; the legacy form refuses them. job-application.profile.erase (and the internal AuthOS account-deletion path) runs in one transaction under an exclusive per-candidate lock: tombstones and redacts every profile-fact revision, destroys the candidate keys and tombstones vault rows, deletes the scope's invalidation/cursor/failure/quarantine/resolution rows, records erased_through_sequence as the replay fence and writes a value-free candidate_erasure_receipts row. Erasure is per exact scope; documents, S3 objects and database backups are not covered. Deploy 0022 before the runtime. 0022 also fixes the 0009 vault check constraint, which called the revoked jsonb_object_length and blocked every real-Postgres vault insert.
Profile-evidence writer (P-01l)
job-application.profile.evidence.write is the single path that stores a profile-evidence value: in one transaction it takes the erasure guard (share lock) before the idempotency reservation (beforeReserve hook in resource-mutations.ts), encrypts the value under the candidate data key at candidate-material://<fingerprint>/profile-evidence:<id> (material-vault-write.ts) and appends the fact revision with the value sha256. Replays return the stored receipt; writes and replays after erasure are refused. conformance/erasure-concurrency.sh proves the erasure/writer lock order on real Postgres with two connections; the docker receipt now measures forceRlsTables, schemaTables and nonOwnerRoles.
Candidate opportunities (TAFIO-OPP-01)
PostgresCandidateOpportunityStoreV1 (./candidate-opportunities) implements the runtime opportunity port for one exact candidate scope. Migration 0023 adds candidate_opportunities (immutable, one row per keyed id and version) and append-only candidate_opportunity_observations (FK to the opportunity version), both FORCE RLS with the exact-scope policy: runtime SELECT/INSERT, readonly SELECT, no UPDATE/DELETE/TRUNCATE, and no access for worker, webhook, public, anon or authenticated. Title, job text, job digest, shared listing reference and observations are sealed under the candidate data key; the id is an HMAC of the text under that key. Every write takes the erasure guard before the idempotency reservation, then an advisory lock and a version/sequence CAS, and only inserts; nothing reaches the outbox or job-source. Candidate erasure crypto-erases the sealed values (reads and writes then refuse with JOB_APPLICATION_CANDIDATE_ERASED); the erasure function does not count or tombstone these rows yet. Deploy 0023 before the runtime.
Profile interview (TAFIO-FC-01s, migration 0024)
PostgresJobApplicationProfileInterviewStoreV1 (./profile-interview-store, with ./profile-interview-store-read and ./profile-interview-store-operations) implements the runtime JobApplicationProfileInterviewStorePortV1 for one exact candidate scope. Migration 0024 adds eight insert-only FORCE RLS tables (sessions, sources, turns, proposals, answers, reviews, operation claims and operation results): runtime SELECT/INSERT only, no access for worker, readonly or webhook. Source text, turn content and every proposal are sealed in candidate_material_vault under the candidate data key (./profile-interview-material); rows carry only profile-evidence:interview-{source|turn|proposal}:<sha256> references. Every write takes the erasure guard first; claims are idempotent per (session, key), turns are appended by sequence compare-and-set under a session advisory lock, and a source is stored only with its answer, so declined text is never stored. A 0024 trigger on candidate_erasure_receipts deletes every interview row of the erased scope in the erasure transaction. ./profile-interview-facts is the decrypted active-facts view; ./profile-fact-correction-value (PostgresCandidateProfileFactCorrectionV1) corrects a fact with its value atomically (erasure guard, idempotency reservation, fact locks, revision CAS, P-01l value write, append), and refuses a new fact without value bytes (..._VALUE_INVALID). Deploy 0024 before the runtime.
Subject bindings and destination disclosures (P-01n, migrations 0025-0026)
PostgresJobApplicationSubjectBindingStoreV1 (./subject-binding) is bound to ONE product application id. Migration 0025 adds candidate_subject_bindings (pairwise product subject -> one exact candidate scope, written by the runtime role only after a verified AuthOS admission; replay for the same pair, ..._BINDING_CONFLICT otherwise), append-only candidate_subject_binding_consents (per product and purpose, CAS on the revision, latest wins) and value-free candidate_subject_binding_tombstones. candidate_subject_profile_access (runtime) authorizes a read only for this product's binding with granted consent; candidate_subject_binding_scopes (runtime, worker) serves the account-deletion consumer, unknown for a never-bound pair. Migration 0026 adds append-only candidate_destination_disclosures (runtime SELECT/INSERT, worker SELECT) served by PostgresJobApplicationDestinationDisclosureStoreV1 (./destination-disclosure). All four tables are FORCE RLS with no UPDATE/DELETE/TRUNCATE. Two AFTER INSERT triggers on candidate_erasure_receipts remove the erased scope's bindings, consents and disclosures inside the erasure transaction (a binding tombstone is kept); the 0022 receipt counts do not include them. Deploy 0025 and 0026 before the runtime.
Native processor subject compatibility
P-01n processor subject references preserve the exact admitted AuthOS pairwise bytes, including Base64 +, / and =; they are never trimmed, re-encoded or substituted with a canonical identity. The 1–200 byte bound remains. Application IDs and admission/decision refs retain their stricter existing schemas. Apply the owner-numbered forward native-subject migration before accepting these bytes; it changes only the subject check and the two read-function input guards. It does not rewrite keys, consent history or erasure digests, create associations, or grant access to a previously unbound subject. Unknown subjects retain their existing refusal. No second subject namespace is introduced. The local erasure consumer accepts these bytes; canonical governed rights ingress has a separate subject-schema compatibility dependency and must be qualified before enabling that transport for native subjects.
Candidate questions (E-01g, migration 0027)
PostgresCandidateQuestionStoreV1 (./candidate-questions) implements the runtime candidate question port for one exact candidate scope. Migration 0027 adds immutable candidate_questions (FK to applications, pinned application revision and packet digest), append-only candidate_question_answer_revisions and candidate_question_transitions (exactly one terminal status row per question; a question without one is open). All three are FORCE RLS with the exact-scope policy: runtime SELECT/INSERT, readonly SELECT, no UPDATE/DELETE/TRUNCATE for any role, and no access for worker, webhook, public, anon or authenticated. Field ids, question schemas and values are sealed under the candidate data key. Each command takes the erasure guard first, locks the application row FOR UPDATE, validates every item before any write and only inserts; candidate erasure crypto-erases the sealed values. conformance/question-answer-concurrency.sh proves two concurrent answers serialize on PostgreSQL. conformance/profile-question-concurrency.sh (CVING-01d) proves that two runtime-role connections answering profile questions of one CV import serialize on the profile advisory lock and that a stale reviewed text revision is refused; both run in test:postgres:docker. Deploy 0027 before the runtime. PostgresCandidateQuestionReaderV1 (./candidate-questions-read, E-01g2) reads one bound scope under FORCE RLS with the erasure guard as its only lock; another scope's or an unknown id is JOB_APPLICATION_QUESTION_NOT_FOUND. PostgresCandidateQuestionAnswerResolverV1 (./candidate-questions-read-resolver) opens a pinned candidate-question-answer: ref only after an exact ref check, a field and status check and a read of that exact revision row. No migration or grant change.
CVING-01c (D-181) adds a profile context to the same store: askProfile, answerProfile and listProfile pin a question to a CV import's reviewed text revision instead of an application (./candidate-questions-profile). Migration 0032 (./candidate-questions-profile-migration) adds the context columns, drops NOT NULL on the application columns and adds one exactly-one-context check per table plus a profile index; there is deliberately no FK to cv_imports, and grants and RLS are unchanged. Profile commands write no outbox event, seal under a separate AAD/reference namespace and serialize on a transaction-scoped advisory lock per (scope, import). ./candidate-questions-profile-reseal re-seals the CV stage plan after answers; PostgresCvConfirmedFactsReadV1 (./cv-ingestion-confirmed-facts) returns only normalized keys of the candidate's confirmed facts for de-duplication. Deploy 0032 before the runtime.
Sealed inbox subjects (E-01o, migration 0029)
PostgresJobApplicationInboxStoreV1 takes the candidate data-key strategy inboxSubjectKeysV1(scope, materialKey) (./inbox-subject) and no longer stores a subject in SQL. In one scoped transaction as the webhook role it inserts the inbox_messages row with an opaque subject_ref (candidate-material://<scope fingerprint>/inbox-subject:<messageRef>), seals the subject (at most 500 characters) in candidate_material_vault under the per-candidate key and appends the outbox row; it never updates the message row. An identical replay is accepted, a different subject or application for the same provider message is APPLICATION_INBOX_MESSAGE_COLLISION, and an erased candidate scope is JOB_APPLICATION_CANDIDATE_ERASED. readApplicationInboxV1 returns subject_ref (null for legacy rows, deleted after deletion); openInboxSubjectV1 opens a reference of its own scope only. Migration 0029 (./inbox-subject-migration, inboxSubjectSealingMigrationSqlV1) adds subject_ref, drops the plaintext subject column and gives job_application_webhook route-scoped SELECT/INSERT (never UPDATE/DELETE) on candidate keys and inbox-subject: vault rows plus EXECUTE on the erasure guard and the vault check helper. Deploy 0029 before the runtime and the webhook. Application deletion tombstones the sealed rows and sets subject_ref = 'deleted'; candidate erasure (0022) tombstones them and destroys the candidate key. conformance/security.sql asserts the 0029 grants, the four webhook policies and the absent subject column.
Corrective revision fence protocol v2
Every canonical revision append (mandates, profiles, answers, documents and profile facts) takes the existing candidate erasure SHARE fence before any revision lock, then the existing per-table/id advisory key. Cross-table callers prelock all known facts → documents → profiles → answers → mandates; ids are sorted within each table. Latest revision reads happen after lock acquisition, so READ COMMITTED observes any writer committed while acquisition waited. The current packet owner holds candidate EXCLUSIVE first and the same revision keys through actual callback settlement. Row SHARE alone is insufficient.
Active/new private revisions are refused after erasure through generic and legacy direct entry points. Only a terminal cleanup successor of an existing latest row may append after erasure, with every field unchanged except revision and revokedAt/deletedAt/supersededAt. No new value/ref/id is admitted. Existing approved bytes/records remain under their owning cleanup/custody contract; erasure-owned value-free fact tombstones use the existing SQL erasure owner. The code protocol marker is compatibility metadata, never authority.
The package root re-exports lockJobApplicationRevisionsV1, JOB_APPLICATION_REVISION_LOCK_PROTOCOL_V2 and JOB_APPLICATION_REVISION_LOCK_TABLE_ORDER_V2 so the managed current-packet owner acquires these same keys instead of a second lock namespace. Taking these locks grants nothing; callers still bind and revalidate current authority.
Clickwrap acceptance receipts (D-238, migration 0042)
PostgresCandidateLegalAcceptancesStoreV1 (./candidate-legal-acceptances) records, for one exact candidate scope and product application, the legal documents a candidate accepted with the sign-in clickwrap: per document its id, role (terms_acceptance or privacy_disclosure), version and source sha256, or only the https URL of an unversioned external reference, plus the locale, the gesture instant and candidateLegalAcceptanceDigestV1 of the receipt the page showed. Migration 0042 adds candidate_legal_acceptances: FORCE RLS with the exact-scope policy, SELECT/INSERT for job_application_runtime and job_application_web (mirrored _web policy), no UPDATE/DELETE/TRUNCATE and no access for worker, readonly, webhook, public, anon or authenticated. record takes the erasure guard first and inserts with on conflict do nothing on (scope, product, document id, version or URL), so a replay records nothing and the first acceptance instant is kept; hasAccepted is true only when every given current revision is recorded. An AFTER INSERT trigger on candidate_erasure_receipts deletes the erased scope's receipts (no separate legal-retention class). Deploy 0042 after 0041 and before the web host that records receipts.
Relationships
Arrows point from a package to its direct dependency. Counts are derived from captured workspace manifests.
4 declarations
@lssm-tech/lib.contracts-integrationsworkspace:^
Read the package manual ↗@lssm-tech/lib.job-application-runtimeworkspace:^
Read the package manual ↗@lssm-tech/lib.authos-specworkspace:^
Read the package manual ↗@lssm-tech/lib.contractspec-connectworkspace:^
Read the package manual ↗
Declared capabilities
FORCE-RLS Postgres persistence for candidate-private job applications.
Scripts
buildcontractspec-bun-build buildbuild:bundlecontractspec-bun-build transpilebuild:typescontractspec-bun-build typescleanrimraf dist .turbolint:checknode ../../../scripts/biome.cjs check .testbun testtest:postgres:docker./scripts/test-postgres-docker.shtypechecktsgo --noEmit