Define the PostgreSQL observation and protection-lifetime binding for governed reads

[samovers]

Status and purpose

This issue tracks the already-authorized implementation-side PostgreSQL binding task. It is not another prerequisite to that task, a new canonical RFC, or implementation approval. The concrete mechanism is still unresolved; finish that Phase A here rather than commissioning another general design or coordination service.

Consumers: #353 / PR #359, then the applicable readback integration around #178, and eventually the separately bounded temporal child of #176. This issue is not that temporal child and does not satisfy #176.

Canonical owner: OFARM #36 / PR #37, approved decision OFARM-ISSUE36-GOVERNED-READ-TRANSACTION-COVERAGE-DISCLOSURE-001, version 2, at 41cd45b90fb8e133f6377d8f389858b89c3dd7ae. Approval record. That approval defines the required behavior, not an admitted PostgreSQL provider.

The task user authorized issue creation conditionally on a drift/overdesign check. The check supports one owner issue after removing the extra-prerequisite framing: no duplicate owner issue was found; #353 excludes storage/transaction ownership, #178 excludes governed reads from its write protocol, and #177 retains permission/redaction/output integration. The issue records existing RD-BIND03 work, including RD-BIND03-W, without adding an approval stage or prescribing a mechanism.

Outcome and primary trust boundary

Deliver the bounded database/source binding needed for one complete governed-read observation, exact evidence membership, and protection lasting across evidence commit until the first irreversible disclosure handoff or irrevocable termination.

Primary trust boundary: governed-read database observation, evidence membership and protection lifetime. Database enforcement, narrowly justified privileges and mechanical writer participation may belong to this boundary; independent identity, key, selector, authorization, output or custody authority does not.

The user-visible contribution is a prerequisite for truthful, currently authorized readback without revealing hidden history through a changed success/failure response. It does not itself expose an endpoint or claim that the complete reader is ready.

Inspected basis

OFARM2 main: 9d7541d96bc708e9270b986927d7f4b8a035454f. OFARM main: 71ca724a8b6ec23f1655b086a6f549496d10a47f. PR #359 remains at 7de8a2c4cf6eb1f293560af67e69565122c42f25; canonical PR #11 remains at 4494924998183fe3fa7bc1b63b76a85893335044. All three referenced PRs remain draft and unmerged.

• Tenant UnitOfWork ends its exposed unit before committing. It supplies no protected post-commit read lifetime.
• The current knowledge allocator uses the transaction-scoped tenant write lock. The existing concurrency test checks waiting and ordering, not privacy-safe admission or protection through disclosure.
• Binder READ COMMITTED semantics and protected lock privileges cannot be silently replaced by a stronger isolation setting or raw session-lock access.
• Batch uniqueness needs an explicit mapping between one logical read and its separate durable stages.

These findings reject particular reuse shortcuts. They neither prove every possible PostgreSQL design impossible nor establish that new roles, a service, a lease or a scheduler must be built.

Required binding and acceptance

Use the approved order: A = durable admission; S = final complete observation; P = frozen preparation; C = atomic evidence commit; L = first irreversible handoff; O = later truthful observations. Preserve A → S → P → C → L → O, with irrevocable stops and no same-identity protected replay.

The rows below map existing canonical obligations; they do not create another invariant registry.

Existing obligation	Required concrete result and smallest hostile/positive evidence
RD-I01/I08; RD-BIND01	Map the exact canonical logical tuple to distinct physical A/C/O transactions and immutable evidence. Concurrent duplicates, changed session/caller facts and lost acknowledgement cannot take over the old attempt. Internal transaction IDs never become new caller requests.
RD-I02/I09; RD-BIND03	Name the authoritative collections, exact membership predicates and consistent S/c mapping under unchanged binder semantics. Cover absence, sets, historical packages, qualifiers of qualifiers and unresolved/malformed-root partitions. A pre-cut commit with a delayed notification must be included; an omitted partition cannot yield a completeness claim.
RD-I13; RD-BIND03-W; RD-C20/C21	Select and explain the actual protection mechanism, acquisition/commit participation, deadlock order, lifetime and independently verifiable evidence. Hold independent authority/session/deadline/fault facts fixed: a hidden writer already holding or queuing protection cannot alone change a permitted limited reply into timeout/failure. Order any conflicting commit after L/termination; demonstrate actual post-L commitment in C20's admitted positive pair, without granting a new writer authority. Test uniform policy exclusion and the separate disclosable-history comparator too.
RD-I06/I07/I08; RD-BIND01/03	Bind the complete atomic C set and authoritative reconciliation. Partial or unknown C permits no L; any complete C leaves consumption spent even if its acknowledgement is lost or disclosure stops. A receipt records established preparation facts, not future continuity or delivery.
RD-I02/I07/I08; RD-C09/C13/C18	Specify the exact storage-to-output contract for continuous protection, owner loss, cancellation and exclusive deadlines. A final database check followed by an unguarded send is insufficient. The output owner implements L separately; this issue proves the database side and states its integration obligations honestly.

Inventory all paths that can introduce relevant authoritative candidates, including applicable historical import/migration paths. Prove unsupported paths remain unavailable; do not implement recovery/import or activate qualifying-record authorship to fill the inventory. Empty history and an inactive current writer do not establish completeness or writer-deferral proof.

A purported seal that permits the forbidden hidden-history interleaving fails conformance even when it subsequently suppresses output safely. Do not extend deadlines, hide notifications, invent processing bounds or redefine failure as a successful privacy result.

Smallest architecture and boundaries

Complete one concrete Phase A contract under AGENTS.md and TASK_PROMPT.md: trust model, one authority per fact, exact typed operations and ownership, state/ordering, production-entry counterexamples, source/profile bindings and invariant-to-test traceability. Document the actual mechanism and positive path; another list of required provider promises is not completion.

Use the existing tenant foundation where its guarantees fit. No generic SQL/cursor interface, legacy Store fallback, mutable trusted flag, duplicate history truth store or speculative coordination framework. Do not split an issue or PR for each helper, table, lock or test.

Likely implementation areas, to be justified by the selected design: narrow typed storage operations around kernel/tenant_uow.py, an additive migration under kernel/migrations/ if needed, the matching provisioning/readiness enforcement, and focused tenant/read database tests. Existing migrations remain immutable. Do not select new privileges or exact files merely from this list.

Retain existing identity, policy selection, source semantics and retention authority. Specify, but do not implement here, authorization evaluation, full content/redaction/assembly processing or HTTP/proxy/cache disclosure. Do not change the canonical four package families, the full ASSERT_OPERATION_CLAIM / RECEIVE_READ_DATA scope, #359 F4, or the deferred status of the other eighteen actions. No extra evidence-reference carrier unless the actual selected policy requires one.

Not provisional: no temporary unsafe reader or permanently unavailable substitute is proposed. Learning value is the concrete database mechanism and falsifiable proof that the approved read protocol can be bound, without spreading authority across unreviewed components.

Stages, verification and stop rules

First finish the existing implementation-side Phase A and obtain its required review/approval. This issue's creation is not that approval. Preserve PR #11's eleven stages: technical design/static bindings precede runtime work; canonical materialization, binding review, scoped promotion and byte-identical extraction remain separately governed.

At the later authorized implementation stage, use the real typed database entry and participating source paths for the cases above, including actual commit-order and output-owner integration evidence. Keep database-only proof distinct from end-to-end reader admission. Run the mandatory python3 conformance/ofarm_pkg_contract_check.py before commits using the repository's required runtime, plus the focused affected checks and PostgreSQL tests. No runtime experiment is required before its own implementation authorization.

Do not absorb #177/#178/#179/#180/#181/#182/#183/#185/#193 or reopen completed #173/#174. Necessary read inputs must precede their consumers; neither a blanket #353 dependency nor a fake producer may create a cycle.

Stop before an independent authority or canonical semantic change. Explain the exact change, affected files/authority and why it is necessary before requesting a split or exception. Failure to find a mechanism leaves this binding open; it is not an automatic reason to create another prerequisite. Once approved in-scope acceptance passes, remaining preferences become follow-ups under the existing merge and explicit-authorization rules.

Creation verification

Fresh issue/owner and live-head checks; comparison with the authorized binding plan and PR #37's unchanged requirements. No provider feasibility, runtime or semantic approval is claimed. No code, schema, role, source authority, PR, merge or production route changed, and no tests ran for this issue creation. Scope stayed inside recording the bounded database/source binding task.

What is next: finish the concrete PostgreSQL binding design in this issue, starting with the unresolved privacy-safe acquisition and protection-through-L mechanism; do not add another planning layer.

[samovers]

Phase A investigation — durable-barrier candidate, not approval-ready

Date: 2026-09-14. Base remains OFARM2 9d7541d96bc708e9270b986927d7f4b8a035454f; canonical PR #37 remains 41cd45b90fb8e133f6377d8f389858b89c3dd7ae, version 2 approved at the protocol level. This is concrete work inside #392, not another prerequisite, a completed binding, or a request to approve implementation.

1–3. Problem, learning value and non-goals

Evaluate a small durable PostgreSQL mechanism that could preserve history membership from the final observation S through evidence commit C and the first irreversible output handoff L. The learning value is distinguishing post-C safety from privacy-safe acquisition, rather than calling a longer-lived lock sufficient.

Primary trust boundary: governed-read database observation, evidence membership and protection lifetime. Scope stayed inside source inspection, a finite symbolic schedule calculation and this design record. No canonical policy, writer eligibility, identity/binder, selector, authorization evaluator, output transport, custody, recovery or runtime code is changed.

4–5. Trust and authority

Protect tenant data, hidden history, immutable evidence and the one-use disclosure right. In scope: malformed input, duplicate calls, participating concurrent writers, delayed notifications, permitted application-role SQL paths, connection loss, cancellation and crashes. Arbitrary in-process mutation, source/filesystem substitution, compromised dependencies and operator compromise are outside this proposed application boundary.

Existing identity/binder and selected-policy owners remain authoritative for identity and permission. Source owners determine candidate membership and historical admission. PostgreSQL determines durable guard/evidence facts; the original live output owner determines actual L or irrevocable termination. A client boolean, receipt flag, elapsed lease, missing heartbeat or missing outcome record is not that proof. The database binding does not create a new history classifier or permit writer admission.

6 and 9. Concrete candidate and ordering

This candidate uses a durable barrier, not a session lock:

1. One immutable admission A preserves the exact canonical logical request tuple and caller-submission binding. Independent physical A/C/O events reference it; their internal batch request identities are not new caller request IDs. The exact schema/projection mapping remains RD-BIND01 work, not a claim that existing batch uniqueness already supplies it.
2. Before final S, a narrow database operation registers protection for the independently selected complete source partition. Registration and relevant writer admission share one serialization gate. A writer that passes the gate retains its exclusion through its commit/rollback; registration cannot overtake that writer.
3. Once registration commits, the barrier remains present independently of the registering connection and evidence transaction. Every relevant source mutation path checks it under the same gate. An active barrier prevents a new candidate commit. Any waiting writer must not retain a lock needed by the read's C or barrier termination; otherwise the read blocks its own completion. No caller-selectable “evidence writer” exemption is allowed.
4. In a freshly bound READ COMMITTED transaction, obtain the final source observation in one closed statement over the declared complete sources, then prepare the exact immutable buffer/evidence and commit C. The candidate needs exact source/profile membership and an independently justified closed evidence-write path that cannot introduce a qualifying candidate. These mappings are not established by this sketch. A snapshot name, broad SELECT permission or copied cut is not completeness proof.
5. C does not delete the barrier. The original live owner may perform its one L only under the complete output/authority/deadline contract. After actual L or an irrevocable owner fence, the database may record the corresponding terminal fact and release protection. Lost acknowledgement of this release can prolong blocking, but cannot authorize a repeat disclosure. No automatic TTL cleanup or takeover is assumed; real terminal-witness and cleanup ownership still require the existing G3-HANDOFF binding.

The candidate therefore separates durable source exclusion from connection lifetime. It does not prove complete observation, safe acquisition, external guard validity, the output-side atomic handoff, or safe recovery merely by introducing the barrier.

7–8. Invariants and concrete counterexamples

Use the existing PR #37 invariants rather than creating another registry.

RD-I13 / RD-C21 — acquisition remains the blocker. An earlier summary was too broad about missing writer-duration limits: provisioning does define timeout defaults. Application defaults are a 2,000 ms lock timeout, 10,000 ms idle-in-transaction timeout and 60,000 ms total transaction timeout; worker total timeout is 120,000 ms. Role assignment connects those settings to the application/worker roles. These are inspected configured defaults, not a live measurement or an independently proved bound on read completion.

A concrete candidate-level pair needs no indefinitely stuck transaction:

• The planned governed reader has the same original, principal/session, policy and fixed future deadline in both executions. Its ordinary no-contention path completes.
• Only in the second execution, a relevant writer has already acquired the existing tenant write gate through batch allocation and retains it for three seconds before committing. That hold remains below the provisioned idle and transaction limits.
• Reusing that gate for barrier registration makes the reader hit its two-second lock timeout in the second execution only. Sanitized failure or omission still differs from the first response.

Three seconds is a counterexample schedule, not a proposed performance budget. The later real test must run the admitted typed reader and relevant source writer; current governed HTTP routes remain closed. The existing UoW concurrency test provides a controlled writer-hold starting point, not executed read/privacy evidence.

RD-I02/I13 — removing the serialization wait loses safety. A passive durable flag with only a writer check admits this schedule:

writer checks: no barrier
read registers its barrier
read obtains final S
read commits evidence C
writer commits its candidate
read reaches L

The writer passed its check before registration. It need not see a later flag insertion under ordinary MVCC. Its commit now lands in the forbidden S–L interval. Rechecking in a deferred trigger alone is not a serialization mechanism: another transaction can register after that check but before the writer commits. Also, PostgreSQL permits deferred checks to be forced earlier with SET CONSTRAINTS. Trigger documentation, SET CONSTRAINTS.

Holding the common gate through the writer's real commit repairs this race, but restores the acquisition wait above. Registering first and then waiting for already admitted writers merely moves that same wait before S.

RD-I07/I08 — timeout cleanup is not owner-loss fencing. After C, pause the live output owner before L. If a timer clears its durable barrier, a writer can commit while that owner remains capable of handoff. A missing connection or expired lease alone does not prove the owner was irrevocably fenced. The alternative must prove the real fence, not replace it with a flag.

For the remaining obligations: a concurrent duplicate cannot take over A; a pre-cut candidate notified late must remain in S; unknown/partial C prevents L; any actual complete C leaves consumption spent; lost O leaves disclosure unknown unless independent evidence resolves it. Those remain required tests, not completed mappings.

10. Elegance audit

The candidate would add one operational protection lifecycle, with registration and terminal release as its two transition points, while reusing the canonical A/S/P/C/L/O order and existing evidence families. It must not add a persistent classification, second policy matrix, new coordinator service or parallel flag/lease authorities.

No code patch exists to rewrite or delete. Neither a session-lock variation nor a lease layer is justified as an extra implementation: session locking still has acquisition and session-loss obligations; a durable barrier still has acquisition and terminal-fencing obligations. PostgreSQL's lock semantics do not establish those application guarantees.

Increasing lock_timeout only removes the specific two-second refusal; it does not prove success within the fixed read/authority deadline. A bounded-admission proposal would need an independently enforced bound covering the relevant writers, queueing, observation/preparation/C and handoff, plus admission rules based on independent facts. The current timeout values alone do not supply that proof. No new duration, scheduling service or throughput assumption is selected here.

11–12. PR boundary and provisional status

No implementation PR is ready. Candidate areas remain narrowly typed read-storage operations, an additive migration if justified, matching provisioning/readiness checks and focused database tests. Existing migrations and the READ COMMITTED binder remain unchanged. An exact implementation file list and privilege delta must follow the selected mechanism, not precede it.

Canonical materialization, genuine source/evidence producers and the output owner remain their existing dependencies. Do not implement their authorities here, add a fifth package family, broaden writer admission, narrow the two selected rules, or turn F4 into a redundant reference carrier. A policy-selected exclusion is an existing canonical option, but choosing it to avoid this blocker belongs to the actual policy/scope owner; this investigation makes no such choice and does not claim the permitted limited-reply branch complete.

Not provisional: this is a rejected/incomplete mechanism candidate, not temporary permission to ship it. No new follow-up issue or approval stage is created.

13. Traceability and verification

Existing invariant/case	Proposed owner and smallest verification	Evidence status
RD-I01/I08; C17	Read admission storage: concurrent same-tuple callers, changed session/submission, A acknowledgement loss	Mapping and live tests still owed
RD-I02/I09; C07/C21	Closed source reader: delayed pre-cut notification, missing partition, historical/unresolved candidate	Source inventory and live tests still owed
RD-I13; C20/C21	Database barrier plus participating writer: actual commit order and paired public result, including pre-acquisition contention	Concrete counterexamples above; no live provider test
RD-I06/I07/I08; C12/C13	Evidence storage: complete/partial/unknown C and lost O, with no protected replay	Existing commit-unknown groundwork only; new proof still owed
RD-I02/I07/I08; C09/C18	Storage/output seam: owner pause/loss, release failure, expiry and one exact L	Real output/terminal-witness binding still owed

A finite JavaScript schedule calculation enumerated the 15 order-preserving interleavings of two writer events (check, commit) and four reader events (register, S, C, L). With a passive flag, six schedules were admitted and two violated S–L exclusion, including the trace above. Encoding the common serialization gate admitted only the writer-before-registration and writer-after-L schedules, with zero violations in those two. This is a small sanity check of explicitly encoded assumptions, not PostgreSQL execution, a full concurrency proof or evidence that the gate's admission behavior conforms.

Also checked configured timeout arithmetic, immutable current-main source, live issue/head state and PostgreSQL 17 primary documentation. No PostgreSQL instance, package suite, benchmark, hosted baseline or production endpoint was run; no files, runtime code, roles, schemas, PRs or approved candidates changed.

14. Disposition

Blocker: no selected mechanism yet satisfies both RD-I13's privacy-safe acquisition and the required S–L ordering. The durable-barrier candidate has useful post-C behavior in its model, but cannot be approved as a compliant provider from that result. The default-timeout pair gives a concrete counterexample to straightforward gate reuse by the proposed reader under the inspected role defaults; it is not a claim that a deployed governed reader has failed. This does not prove that every possible PostgreSQL design is impossible.

Existing dependencies: complete source/profile/physical evidence mappings and real terminal/output witnesses remain open; none is fabricated or reclassified as a new prerequisite. Preferences: none.

What is next: focused review of this concrete barrier/admission finding within #392, then settle a defensible admission mechanism or the exact required owner decision. Keep implementation paused; do not create another planning issue or silently change the approved privacy/deadline rules.

[samovers]

Re-review of #392 — durable-barrier investigation

Verdict: the new investigation is sound, but the proposed mechanism remains blocked for implementation. One existing blocker confirmed; no additional corrective findings.

Reviewed the “Phase A investigation — durable-barrier candidate, not approval-ready” comment, rather than merely repeating the issue-framing review. It explicitly presents an unsuccessful mechanism candidate, not a completed binding or an implementation-approval request. The earlier “no blockers” verdict applied to the issue definition, not this subsequent design.

Review basis: OFARM2 9d7541d96bc708e9270b986927d7f4b8a035454f and the canonical governed-read protocol at 41cd45b90fb8e133f6377d8f389858b89c3dd7ae in samovers/OFARM PR #37. This is an investigation/design review, not an exact-head implementation clearance.

B1 — Privacy-safe acquisition remains unresolved: confirmed

Location: the investigation’s concrete ordering, acquisition counterexample, and final disposition.

The proposed durable barrier addresses one problem: protection need not disappear when the registering database connection ends or when evidence transaction C commits. However, registration still shares a serialization gate with participating writers. An already-admitted writer can therefore delay registration. The comment correctly identifies that distinction rather than treating durability as a complete solution.

I verified the cited configuration:

Setting	Application role	Worker role
Lock timeout	2 seconds	2 seconds
Idle-in-transaction timeout	10 seconds	10 seconds
Transaction timeout	60 seconds	120 seconds

These values are assigned to ofarm_app and ofarm_worker, respectively. Sources: configured defaults and role assignment.

Consequently, the proposed pair is valid under the stated gate-reuse and default-setting assumptions: a writer holding the conflicting gate for three seconds can remain within its configured limits while causing the reader’s two-second lock wait to fail. PostgreSQL applies lock_timeout to the acquisition wait; it does not require the blocking transaction itself to exceed its own timeout. See PostgreSQL 17 client-connection timeout documentation.

That produces the prohibited distinction: the otherwise equivalent permitted read succeeds without hidden-writer contention and fails with it. The canonical protocol §8.3, especially item 5 explicitly rejects hidden-history-only acquisition failure as proof of privacy compliance. Sanitizing the failure does not fix the changed response outcome.

The finding is correctly bounded. It defeats straightforward reuse of this gate with these defaults. It is neither an observed failure of an enabled governed reader nor proof that every PostgreSQL mechanism is impossible. The investigation preserves those limits.

The other counterexamples hold up

A passive barrier check does not establish commit exclusion. The writer can check before registration, then commit after the reader’s final observation. The comment’s proposed fix—serialization extending through the writer’s actual commit—closes that ordering gap in its model but reintroduces the acquisition wait. These are two different obligations, not interchangeable solutions.

A deferred trigger alone does not close the gap. Moving a check nearer commit is not equivalent to making that check and the conflicting registration mutually exclusive through commit. PostgreSQL also explicitly permits pending deferred constraint triggers to be fired early using SET CONSTRAINTS. The investigation’s rejection is therefore justified for the check-only mechanism it describes; it does not establish that every trigger-based design would fail. See CREATE TRIGGER and SET CONSTRAINTS.

Timeout cleanup is not proof that the output owner can no longer disclose. In the stated paused-owner schedule, clearing the barrier allows a writer to commit while the original owner remains capable of handing off the old buffer. Retaining the barrier avoids that particular premature-release race, but leaves safe termination and cleanup unresolved. The comment correctly leaves the real terminal witness and output handoff binding open rather than assigning that authority to a timer or missing heartbeat.

The symbolic calculation is reproducible—and appropriately limited

I independently reconstructed the described six-event model in Python and obtained the same results:

Model result	Count
Order-preserving interleavings	15
Interleavings admitted by the passive flag	6
Admitted interleavings violating S–L exclusion	2
Interleavings admitted by the encoded serialization gate	2
S–L violations in those two gated interleavings	0

The reconstruction preserves writer check → writer commit and register → S → C → L. The passive model admits a writer check before registration or after L; the gated model permits writer commit before registration or writer check after L. A violation is a writer commit strictly between S and L.

This supports the reported calculation. It does not establish PostgreSQL enforcement, successful acquisition within the fixed deadline, crash recovery, or the real disclosure boundary. In particular, the gated model’s zero violations follow from its encoded serialization assumptions; they do not resolve the timeout counterexample. The comment expressly makes that limitation.

Disposition

Keep implementation paused. No corrective rewrite of the investigation is required.

The next substantive work remains within #392: select and justify an acquisition mechanism that preserves the required response behavior and commit ordering. Increasing a timeout alone would remove this particular two-second failure, not prove the complete requirement. Choosing policy exclusion to avoid the permitted limited-reply branch would require the actual policy owner’s decision; it cannot silently count as completion of that branch.

Complete source membership, the evidence-write path, and terminal/output witnesses also remain necessary completion work. They are already disclosed—not new prerequisites introduced by this review. No baseline-law change is justified by the evidence examined here.

Verification limits: source and documentation inspection plus an independent reconstruction of the small schedule model. I did not run the author’s JavaScript, PostgreSQL tests, package checks, or hosted workflows. No runtime code, repository files, roles, schemas, or approved candidates were modified by this review.

This comment supplies no implementation approval, canonical promotion, baseline admission, or merge authorization.