operability(document-records): make durable metadata persistence idempotent under uncertain retry

[seonghobae]

Verified buyer/operability gap

The current document_records persistence stack (#98 → #107) correctly hardens evidence identity, immutable metadata, tenant RLS, exact canonical bytes, and duplicate-key ambiguity. The current #107 0021_document_record_persistence.sql write boundary, however, still has no explicit idempotency authority for an uncertain-outcome retry.

The table has tenant-scoped unique constraints for document/artifact/audit/outbox references. Those constraints prevent duplicate durable facts, but they do not by themselves give a caller deterministic replay semantics. If PostgreSQL commits an insert and the caller loses the response, retrying the same logical command can surface a uniqueness error rather than return the first committed authoritative result. A uniqueness violation is not proof to the caller that the existing row was produced by the exact same semantic command rather than by a conflicting command that reused one reference.

This is a document_records owner concern. Do not solve it in talent_acquisition, people_core, #307, or a consumer by retry heuristics, cross-service SQL, timeout assumptions, or by copying mutable #98/#107 source.

Required domain contract

Define a tenant-scoped, versioned persistence command/result boundary with an explicit idempotency identity and a deterministic semantic command digest over the already-governed persistence inputs. The authoritative persistence transaction must distinguish:

• first execution: write one immutable document_record plus its required audit/outbox evidence and return one authoritative receipt/result;
• same idempotency identity + same semantic digest: converge to the original committed document identity and receipt without creating another business/audit/outbox fact;
• same idempotency identity + different semantic digest: fail closed as a conflicting replay;
• concurrent first attempts for the same idempotency identity: serialize so exactly one semantic command wins and every same-semantic participant converges to that result;
• timeout/connection loss after commit: a later retry can recover the authoritative first result without treating absence, elapsed time, or a generic uniqueness error as success evidence.

The idempotency key/receipt must be purpose-bound and PII-minimized. It must not store document bytes, credentials, free-form HR content, or duplicate Person/Employment truth. Preserve #107's exact canonical-evidence byte/digest invariants and document_records ownership.

Executable acceptance

• Add RED PostgreSQL/application contracts for uncertain post-commit retry, same-key/same-semantic concurrency, same-key/different-semantic conflict, duplicate delivery/reordering where applicable, and connection cleanup.
• Exercise real PostgreSQL concurrency; a single-threaded unit fixture is insufficient transaction evidence.
• Prove one durable document fact and exactly-once logical audit/outbox evidence for same-semantic replay.
• Bind the returned replay result to the original committed document identity and immutable receipt; do not generate a fresh identity on replay unless the contract explicitly proves why that identity is non-authoritative.
• Keep aggregate/transaction scope minimal and tenant-scoped; use UPSERT/locking only where the semantic digest and result-recovery contract make the replay decision unambiguous.
• Preserve FORCE RLS/NOBYPASSRLS acceptance and no cross-service application-table SQL.
• Record failure/recovery semantics in ADR/OPERABILITY/TEST_STRATEGY/TRACEABILITY through the canonical writer paths.
• Any new PostgreSQL acceptance contract must be admitted through the package-/contract-neutral Foundation owner (ci: preserve declared Python compatibility in canonical Foundation ownership #258/ci: enforce declared Python package compatibility #259 or verified successor), not a resurrected feature-local workflow.
• Final integration requires fresh exact-head hosted evidence after feat(document-records): add governed HR document evidence #98/feat(document-records): persist immutable HR document metadata #107 and Foundation prerequisites are reconciled with protected develop.

This issue is not a reason to close or weaken #107. It is the next operability increment after immutable persistence authority is established.

[seonghobae]

Implementation successor is #312, stacked directly on #107 rather than copying document-record truth into a consumer.

Current exact authority is #312 45a0296f9ad513b3f73dfce24a09d26d629b447a on base #107 7ce73aa44f47113b2ecd42d51bb5d38a22c0367d.

Repair lineage now covers two additional real retry hazards beyond the original uncertain-outcome gap:

• timezone drift: db836080... RED → 00ba4ee... fix; same semantic command must retain one replay identity across UTC and Asia/Seoul caller sessions, so digest serialization is function-local UTC;
• unsupported isolation: 7a5393c... RED → bfc2694... fix; the owner now fails closed unless transaction_isolation = read committed, because the post-lock replay lookup relies on a fresh Read Committed statement snapshot. ADR/traceability are current through 20dc8c8... / 45a0296....

The transaction advisory lock remains limited to replay lookup + local durable writes; OCR/model/artifact/network work stays outside the transaction. Same key + same semantic digest returns the first committed receipt; same key + changed semantics fails closed.

#312 remains Draft. Hosted GREEN is not claimed because both PostgreSQL contracts are not yet admitted by the protected Foundation owner (#310/#311) and the branch is correctly stacked on #107.

[seonghobae]

Implementation update: successor #312 is now at exact head 8ba15958762ed49bc33c4f6d78573b0427bec927 and remains Draft/stacked on #107. A fresh audit found one tenant-boundary defect beyond the original retry semantics: the function could reach pg_advisory_xact_lock(...) for p_tenant_record_id before explicitly proving that the session tenant matched that tenant. RLS only applies at table access and privileged Foundation/maintenance roles can bypass it.

Ordinary-forward repair lineage: cb7076c... adds the cross-tenant RED contract; 147973e... adds the explicit current_tenant_record_id() = p_tenant_record_id fail-closed guard before digest/lock/write; 4ba85c5... repairs existing retry/concurrency fixtures to provide tenant context; db226ed.../8ba1595... currentize ADR/traceability. Hosted GREEN is not claimed until the #259/#311 Foundation stack and #107 history are reconciled and all three PostgreSQL contracts run on the exact protected-base head.

[seonghobae]

Implementation successor #312 has advanced to 8d28ab4a8beaf21a6405fce9b23b3642b7008924. A prior CodeRabbit review on predecessor 3ef61434... identified two acceptance gaps that remained after the timezone repair: fixed-delay concurrency scheduling and metadata-only receipt RLS checks. #312 now proves the second session is actually blocked on the first backend's ungranted advisory lock before release, and exercises receipt isolation through a temporary NOBYPASSRLS non-superuser role. Hosted Foundation GREEN is still not claimed because #312 remains correctly stacked on #107 while #310/#311 own PostgreSQL Foundation admission.

[seonghobae]

Implementation authority update: #312 is now exact a9dc83aaf139f4f0bf0ee1ddb70cf2ba37000866 on #107 7ce73aa44f47113b2ecd42d51bb5d38a22c0367d, still Draft/mergeable and intentionally not claiming hosted PostgreSQL GREEN before Foundation-stack reconciliation.

Fresh review found one additional real operability defect in the acceptance path: the temporary NOBYPASSRLS receipt-probe role could survive an early RLS assertion failure and poison later executions. Ordinary-forward repair 4757e24... made the principal run-unique and attached removal to EXIT cleanup; be064e24... then made nominal successful cleanup strict/fail-closed while keeping failure cleanup best-effort so it cannot replace the original assertion failure. CodeRabbit reviewed exact code head be064e24... and found no new issues. a9dc83aa... only currentizes traceability. Keep #309 open until the owner-neutral Foundation stack admits all three PostgreSQL contracts on reconciled protected truth and exact-head gates/independent approval complete.

[seonghobae]

Implementation authority advanced ordinary-forward to #312 exact fb8da85bc62df519828c480a3f07b8a44be344ee.

Fresh audit found a remaining acceptance-isolation defect in the temporary NOBYPASSRLS probe principal: PROBE_ROLE=..._${BASHPID} was only process-local and PID-reusable. Because failure cleanup is intentionally best-effort, a role can survive a transient cleanup failure; another runner/container or later shell can reuse that PID against the same PostgreSQL database and collide before the actual RLS acceptance executes.

fb8da85b... replaces the PID-derived suffix with a fresh UUID-derived 24-hex suffix while preserving strict successful cleanup and best-effort failure cleanup. The exact delta is one test file, +7/-2, ordinary-forward from a9dc83aa...; production persistence semantics and gates are unchanged. Current head still has zero PR-triggered workflow runs and zero submitted reviews, so hosted PostgreSQL GREEN/approval is not claimed or inherited. #309 remains open until prerequisite reconciliation and exact protected-base acceptance.

[seonghobae]

Current implementation authority advanced ordinary-forward to #312 8692bb11f457dda06b3b5ab1afb3754a135e5825 (base remains #107 7ce73aa...). Fresh acceptance audit found a false-GREEN in the lost-response result contract: the test compared document/audit/outbox identities and semantic/receipt digests but omitted the server-owned recorded_at, so a future replay regression returning a fresh timestamp could pass. Commit 3b9938902327ab819dc876544252ff9036993fb4 now includes timezone-independent extract(epoch FROM recorded_at) in first/retry result convergence and requires the durable document/receipt timestamps to be identical; 8692bb11... currentizes traceability. Migration 0024 semantics were not weakened or changed. Hosted PostgreSQL GREEN remains pending Foundation-stack reconciliation.

[seonghobae]

Implementation authority advanced to #312 exact head a087a08eb672a35730c406fc2ae79d5ef3b3a94a by ordinary-forward commits. CodeRabbit’s latest exact-head finding on 8692bb11... was valid: comparing first/retry returned times and separately proving durable document/receipt time equality still allowed both returned values to be the same wrong timestamp. Commit e55e8076e595f6ae5afcd083bdca58c150d3fa6c now extracts the epoch returned by persist_document_record_once(...) and requires it to equal both durable document_record.recorded_at and document_record_persist_receipt.recorded_at; a087a08... currentizes owner traceability. Hosted PostgreSQL GREEN is still not claimed because #312 remains correctly stacked on #107 while Foundation admission is owned by the separate #258/#259 → #310/#311 prerequisite stack. Keep #309 open until that normal reconciliation and exact-head runtime evidence complete.

[seonghobae]

Fresh owner-doc audit found an acceptance gap in the current #312 implementation. Issue #309 explicitly requires uncertain-retry failure/recovery semantics to be recorded through ADR, OPERABILITY, TEST_STRATEGY, and TRACEABILITY. Exact #312 a087a08eb672a35730c406fc2ae79d5ef3b3a94a changes ADR 0309 and the focused traceability document, but its changed-file set does not include canonical docs/OPERABILITY.md or docs/TEST_STRATEGY.md; both current files still describe People mutation idempotency/audit-outbox behavior without the document_records replay receipt, Read Committed visibility requirement, tenant-before-advisory-lock boundary, observable wait-graph concurrency proof, timezone-stable result recovery, or temporary-principal cleanup contract. This is an owner-path documentation/operability finding, not a reason to close or weaken #312. Repair must ordinary-forward update the canonical operability/test-strategy truth on #312, preserve the current migration/tests, then reacquire exact-head review/evidence.

[seonghobae]

Owner-doc finding from a087a08... is repaired ordinary-forward, and a fresh audit closed the remaining executable recovery-evidence gap instead of treating sequential replay as sufficient.

Current implementation authority is #312 exact 4535d9fea7b2b995be627a23316a65474d714957 on #107 7ce73aa..., still Draft/mergeable. c3aa86c... and 06f3fa9... currentized canonical OPERABILITY/TEST_STRATEGY, with 7a5e06e... resealing the manifest. Then 3157214... added a real post-commit connection-loss acceptance companion: one simple-query batch commits the document/receipt and remains in pg_sleep; a supervisory session waits until the receipt is externally visible, terminates that committed backend, requires the original client to fail, and retries the exact semantic command from a new connection. The retry must equal the durable first identity/references/digests/original database time with counts 1|1 and no surviving named session.

The first companion shape incorrectly reapplied migrations even though Foundation companions run after their root in the same database. cb587679... repaired that immediately: it now fails closed unless the main idempotency root has already established the owner schema/function and no longer reapplies migrations. 4535d9f... currentizes focused traceability and explicitly keeps this as a reviewed companion of the existing main root rather than inventing a fourth filename-specific root.

Local bash -n passes for the companion, but hosted PostgreSQL GREEN is still not claimed: #312 remains correctly stacked on #107 while #259→#311 owns protected Foundation admission. Keep #309 open until ordinary reconciliation, registry companion binding, exact-head runtime execution, and required independent review complete.

[seonghobae]

Implementation authority update: #312 is now ebe5ec1c297eb48f95363aff5123163b09b10430 (Draft on #107). The post-commit uncertain-outcome companion already proved durable receipt visibility before forced connection loss, but its destructive step still consumed only a previously observed numeric backend PID. That left a checked-versus-used identity gap if the intended backend disappeared and the PID was reused, and the fixed PGAPPNAME also allowed cross-run ambiguity. RED 626c62e5... records the invariant; causal fix 8a485d60... uses an execution-unique UUID-derived application marker, captures (pid, application_name, backend_start), and makes both asserted termination and EXIT cleanup go through the same guarded identity match. Traceability is current at ebe5ec1c.... Migration 0024 semantics are unchanged; real PostgreSQL execution remains pending Foundation-stack reconciliation, so keep #309 open rather than treating this as protected-head completion.

[seonghobae]

Superseding implementation authority: #312 exact head is 8dc8527a5e85920798f49609546892ff1c03eee1. Exact-head review of ebe5ec1... found the runtime guard correct but its source regression incomplete because it did not prove EXIT cleanup used the same guarded helper. 8dc8527... repairs only that regression: cleanup must call terminate_captured_backend, must not call pg_terminate_backend directly, must retain captured backend_start_epoch, and remains best-effort during abort. The uncertain-retry production contract and migration 0024 are unchanged; hosted PostgreSQL evidence is still pending Foundation-stack reconciliation.

[seonghobae]

Superseding implementation authority: #312 exact head is eb99d8e93741215ba3940fc616ab8b875bc7b3bf. Exact-head review of 8dc8527... found a remaining test false-GREEN: asserted termination predicates were searched across the full companion rather than specifically inside terminate_captured_backend(). eb99d8e... changes only the source regression so that helper body itself must contain the captured PID/application-name/backend-start predicates, exactly one guarded pg_terminate_backend(pid) call, no direct PID-variable termination, and exact 1|true. Runtime fixture and migration 0024 are unchanged; hosted PostgreSQL execution remains pending Foundation-stack reconciliation.

[seonghobae]

Superseding implementation authority: #312 exact head is 58d6d680db6ef6f4b445e4c791d2d5d4377e882e. Exact-head review of eb99d8e... found one remaining source-contract weakness: a second PID-only backend-termination call with different whitespace or through another helper could coexist with the guarded call. The successor changes only the regression contract so the entire companion must contain exactly one pg_terminate_backend invocation, its argument must be row pid, and that same sole invocation must occur inside terminate_captured_backend(); cleanup must contain no backend termination invocation. Migration 0024 and recovery-shell behavior are unchanged; hosted PostgreSQL evidence is still pending Foundation-stack reconciliation.