PostgreSQL persistence for Prosaic Harness

Run on worker A, inspect/resume on worker B, without a shared writable run directory. Core remains database-free; install this separate adapter only when you need shared durable state. The API is synchronous; a private I/O thread bounds network waits. Model execution holds no database connection/transaction. The adapter is independently versioned: b3-prosaic-harness-postgres 0.4.1 requires b3-prosaic-harness>=0.9,<0.10. PostgreSQL drivers remain optional for core Harness users. Imports and CLI names are unchanged.

Durability profiles

The default durability_profile="postgresql" requires full_page_writes=on. An operator who has independently verified an Amazon Aurora PostgreSQL cluster may explicitly select durability_profile="aurora-postgresql" on both PostgresRunStore and PostgresSessionStore (including from_env). This accepts full_page_writes=off under Aurora's log-based storage contract. It does not weaken fsync, synchronous commit, writable-primary, logged-table, lease or unknown-commit checks. The guard runs for every operation and connection reuse. Invalid profiles fail before connecting. This profile is an operator assertion, not engine autodetection or proof that arbitrary storage is safe. NEVER select it for ordinary PostgreSQL just to suppress a readiness failure.

For every CLI command, pass --durability-profile aurora-postgresql explicitly. Migration and runtime must select the same profile. Verify the actual engine and version with AWS RDS metadata/DBA evidence; AWS also documents aurora_version(). Capture a failed SQL probe's actual error and inspect its schema/search path; NEVER convert an unavailable probe into permission to bypass durability checks. No AWS credentials or model calls are needed by the adapter.

AWS describes the storage contract in its architecture guide. Native PostgreSQL tests qualify the policy and all existing recovery behavior; they do not emulate Aurora storage. An actual Aurora deployment needs synthetic persistence/restart/failover qualification before enabling production work.

1. Install and supply an existing database

Python 3.11+, PostgreSQL 16 or 18 on a writable primary, and libpq 17+ are required. psycopg[binary] supplies libpq; older libpq is rejected. From the exact candidate Harness checkout, use a new environment and a curated wheelhouse containing only the manifest-selected b3 candidate wheels:

python3 -m venv .venv-postgres
.venv-postgres/bin/python -m pip install --find-links /absolute/path/to/curated-wheelhouse . ./adapters/postgres
export PATH="$PWD/.venv-postgres/bin:$PATH"
prosaic --version
prosaic-harness-postgres --help

ALWAYS migrate using a fresh environment or image; NEVER install legacy and b3 distributions together because they own the same import paths. See package migration. The Runtime 0.9 candidate must be available before this installation can resolve.

Harness does not provision PostgreSQL, create databases/users, start a service, or fall back to files. Your operator supplies two DSNs through a secret manager: HARNESS_MIGRATION_DSN (schema owner with database CREATE permission) and HARNESS_RUNTIME_DSN (restricted runtime role). Never put DSNs in Markdown, YAML, argv, logs or Git. For a local terminal:

export HARNESS_MIGRATION_DSN="$(python -c 'import getpass; print(getpass.getpass("Migration DSN: "))')"
export HARNESS_RUNTIME_DSN="$(python -c 'import getpass; print(getpass.getpass("Runtime DSN: "))')"

Preserve operator TLS/authentication settings, e.g. sslmode=verify-full and certificate roots. The adapter adds target_session_attrs=read-write for primary routing; it never weakens TLS.

2. Initialize once, then grant runtime access

prosaic-harness-postgres init --dsn-env HARNESS_MIGRATION_DSN

Initialization is explicit, transactional, repeatable and concurrency-safe. It creates schema/version 1, not your database. It refuses unknown versions, foreign-owned, partial or damaged schemas; it does not repair or downgrade them.

As schema owner, apply these grants, replacing harness_runtime with your existing runtime role (role/password creation belongs to your operator):

GRANT USAGE ON SCHEMA prosaic_harness TO harness_runtime;
GRANT SELECT ON prosaic_harness.schema_version, prosaic_harness.leases,
    prosaic_harness.runs, prosaic_harness.receipts TO harness_runtime;
GRANT INSERT, UPDATE ON prosaic_harness.leases, prosaic_harness.runs TO harness_runtime;
GRANT INSERT ON prosaic_harness.receipts TO harness_runtime;

Do not grant runtime schema CREATE, schema-version UPDATE, receipt UPDATE, or table DELETE. Check using the actual application's credentials:

prosaic-harness-postgres doctor --dsn-env HARNESS_RUNTIME_DSN

Default doctor reads only catalogs/settings/privileges: no rows, model calls or migrations. For an explicit write-and-cleanup test, use migration or dedicated diagnostic credentials with DELETE on the three data tables:

prosaic-harness-postgres doctor --dsn-env HARNESS_MIGRATION_DSN --check-write

This exercises guarded create/CAS/receipt/read/release operations in a fresh prosaic-diagnostic-<uuid> namespace. Reserve this prefix; applications must not use it. Cleanup deletes only its exact receipt/run/lease identities, in that order, after release. It never deletes production lease generations. Cleanup, interruption and commit-ack failures fail the command. Its error includes the diagnostic namespace for reconciliation: inspect exact rows before manual cleanup/retry, never blindly delete by prefix.

3. Embed the single Harness engine

from prosaic_harness import Harness, Workflow
from prosaic_harness_postgres import PostgresRunStore

workflow = Workflow.load('/srv/workflows/approval/pause.yml')
# Construct after fork; keep one long-lived store per bounded trusted namespace.
with PostgresRunStore.from_env('HARNESS_RUNTIME_DSN', namespace='my-app') as store:
    store.check_ready()  # startup gate; no implicit init
    h = Harness(workflow, store=store, run_id='0123456789abcdef0123456789abcdef')
    h.run({'task': 'example'})  # use a NEW UUID for each new run
    snapshot = h.status()     # read-only .state and opaque .revision
    h.resume(choice='approve', expected_revision=snapshot.revision)

Worker B reconstructs the same Workflow/namespace/run ID and calls status/resume, not run. Human choices, structured responses and explicit interrupted-call retry require the current revision. A stale approval cannot approve a later same-named pause. Never silently fetch a fresh revision to bypass rejection: show current state and require a new decision. See the runnable example.

load_run returns an independent RunSnapshot; create/save return revisions. Receipt absence is None, corruption is an error. Writes require this store/run's active lease. Tokens are run/namespace scoped, not lease owner/fencing tokens. Receipts are immutable; identical repeats need live ownership. Sealed finite JSON is limited to 8 MiB per document including UTF-8 bytes. Public APIs return no driver objects. The legacy Harness(workflow, run_dir) and filesystem CLI remain supported; the adapter adds no new workflow interpreter.

Deployment and safety

• All reads/writes use the authoritative writable primary, never replicas/caches. Tables must be permanent/logged; fsync/full_page_writes must be on, effective synchronous_commit on or remote_apply. Existing remote_apply is preserved. Primary/WAL guards run on every operation/reconnection.
• Mount immutable workflow, prose, schema, evidence and reviewed tool assets at identical absolute paths across workers. Keep executables, validator versions and Runtime configuration identical. Drift fails before dispatch. PostgreSQL moves run state, not assets or private tool scratch.
• Synchronize worker/database clocks and avoid abrupt clock steps. Expiry uses fresh clock_timestamp() after the lease-row lock; local ownership uses a conservative monotonic deadline starting before the request, not at reply.
• Default lease/renewal 60s/15s. Duration >=15s; renewal >=1s and <=duration/3; renewal + operation budget + 1s must be less than duration. Default total I/O budget 10s, pool/connect 5s, row lock 1s, statement 5s. Deadlines cover pool, network and COMMIT waits. Stalled connections are quarantined; unresolved operations retain capacity and prevent unsafe close.
• Default lazy pool bounds per store/process: 8 ordinary + 4 renewal connections, maximum 4 active leases. Renewal has reserved capacity. Multiply capacity across replicas/namespaces; do not create a pool per HTTP request. Applications still own scheduling, model concurrency and quotas.
• Construct after fork; do not share across processes. Drain executions before close/context exit. Threads can share a store; use a separate Harness per run.
• Namespaces are trusted isolation keys, not authorization or DB row-level security. A shared DB role can directly access other namespaces. Derive namespace/run ownership from authenticated server state, never browser/model input. Applications own tenant authorization and admission policies.
• Raw status state contains private inputs, evidence, responses and history. Products must expose a filtered authorized view, not the operator demo JSON.
• Prose frontmatter requests tools; Runtime/workflow permissions authorize them. Neither grants database permissions. No HTTP service, queue, PostgreSQL server or historical file importer is included.
• Fencing stops stale DB mutations, not external tool/model effects. Use application-owned idempotency keys. Pending calls without receipts require explicit fresh retry consent, not automatic redispatch. Unknown token usage may still prevent a token-budgeted retry; abandoned calls retain their budget.
• Acknowledged WAL durability is not zero-loss asynchronous failover. A lagging promoted primary or older backup can lose receipts/generations. Require operator replication guarantees where needed; do not promise exactly-once external effects from this adapter.

Errors and recovery

Development verification

Core tests need no database/driver. Adapter tests use disposable databases and synthetic inference, never TokenProxy. Required mode fails rather than skips missing infrastructure. Select Podman/Docker explicitly for crash/standby gates:

python -m pip install -e '.[test]' -e './adapters/postgres[test]'
python adapters/postgres/tests/support/local_cluster.py start --engine podman --version 16
# Retain the printed ownership.json path as FIXTURE_MANIFEST.
python adapters/postgres/tests/support/local_cluster.py test --manifest "$FIXTURE_MANIFEST"
python adapters/postgres/tests/support/local_cluster.py stop --manifest "$FIXTURE_MANIFEST"

Repeat with version 18. Stop validates exact IDs/names/ownership labels before removing only owned containers/volumes/network. Generated fixture networks and loopback ports use synthetic trust authentication, never production defaults.

Lease registration candidate

The adapter transfers acquisition capacity into registered session capacity atomically under the existing store mutex. A registering lease can no longer be counted twice while maintenance is scheduled. The database storage/fencing contract is unchanged. A unit scheduling-boundary regression and an owned PostgreSQL 16/18 race regression cover the transition.

Optional conversation sessions

The conversation candidate adds PostgresSessionStore to this same optional distribution. It implements the public Harness SessionStore contract and uses the independent, logged prosaic_harness_conversations schema, storage version 1. Existing PostgresRunStore tables, grants and CLI init/doctor behavior remain compatible; those CLI commands continue to address workflow storage.

Initialize conversation storage once with the schema owner's credentials:

from prosaic_harness_postgres import PostgresSessionStore

with PostgresSessionStore.from_env(
    'HARNESS_MIGRATION_DSN', namespace='my-chat'
) as owner:
    owner.initialize()

Apply these grants as the owner, replacing chat_runtime with the existing application role. Role, database and secret creation remain operator tasks.

GRANT USAGE ON SCHEMA prosaic_harness_conversations TO chat_runtime;
GRANT SELECT ON prosaic_harness_conversations.schema_version,
    prosaic_harness_conversations.leases,
    prosaic_harness_conversations.sessions,
    prosaic_harness_conversations.receipts TO chat_runtime;
GRANT INSERT, UPDATE ON prosaic_harness_conversations.leases,
    prosaic_harness_conversations.sessions TO chat_runtime;
GRANT INSERT ON prosaic_harness_conversations.receipts TO chat_runtime;

The runtime role needs no schema CREATE, schema-version UPDATE, receipt UPDATE or DELETE. A role with only workflow grants can continue using PostgresRunStore and cannot use conversation storage until it receives the separate grants. Conversation readiness rejects forbidden effective grants on non-owner runtime roles, including inherited and column-only grants. It also rejects schema-version INSERT/DELETE and table DELETE/TRUNCATE privileges. Revoke accidental extra permissions before accepting work.

Schema owners and roles that immediately inherit schema-owner privileges remain administrative credentials: initialize() and operator check_ready() can validate their installation. Those checks cannot make an owner a restricted role. ALWAYS configure worker processes with separate non-owner runtime credentials and verify their ownership/grants; NEVER use migration credentials as the application's runtime DSN. Readiness performs no migration or fallback:

from prosaic_harness import Conversation, ConversationScope
from prosaic_harness_postgres import PostgresSessionStore

scope = ConversationScope('my-chat', 'production', 'my-service', tenant, actor)
with PostgresSessionStore.from_env(
    'HARNESS_RUNTIME_DSN', namespace=scope.namespace
) as sessions:
    sessions.check_ready()
    chat = Conversation(approved_bindings, store=sessions, scope=scope)
    created = chat.create()

Derive tenant, actor and any billing account from current authenticated host state before each public operation. Scope ownership filters every session and receipt read; leases, revisions and writes also bind to the exact scope and address. A foreign owner sees session_not_found. The SQL role can still access its granted tables directly: namespaces and scope hashes do not replace host authorization or database row-level security.

Use the same sealed bindings and logical workspace/journal identities on every worker. The default bounded pools and lease timings documented above apply. Each operation checks the writable primary and WAL settings, acquires a short transaction, then returns its connection. A conversation's model, tool and linked workflow execution holds no transaction or checked-out connection. Database generations fence late writes after ownership changes; they cannot cancel an external effect already in progress.

Snapshots and immutable receipts use the common finite canonical UTF-8 session codec, with at most 8 MiB each. Their addresses and receipts are checked against allocated attempts, dispatch markers and the frozen scope/binding/input/context. Exact receipt repeats are idempotent under a live lease; conflicts fail without overwriting evidence. A lost COMMIT acknowledgement raises store_unavailable with outcome_unknown and invalidates the lease. Recover by reading authoritative state with fresh ownership; never blindly repeat inference or business effects.

The session policy bounds retained/reserved logical records. PostgreSQL indexes, WAL, tool journals, replicas and backups add physical storage. The host must also enforce retained-session quotas and own any explicit retention/deletion policy; this adapter never automatically expires or deletes records.

The required adapter suite includes both the existing workflow backend and the conversation backend. Run the owned PostgreSQL 16 and 18 fixtures shown above with --require-postgres. Missing infrastructure or any skipped required case fails qualification. Installed Linux AMD64 and ARM64 acceptance is required before claiming the conversation candidate is ready for production.

Optional context storage contract

Conversation storage contract 2 supports context_assembly_v1, reads both v1 and v2 records, and preserves existing documents. New database initialization creates contract 2. Existing contract-1 databases require an explicit migration; runtime readiness never performs it. Drain old writers, deploy upgraded workers, then use migration-owner credentials:

prosaic-harness-postgres migrate-conversations --dsn-env HARNESS_MIGRATION_DSN

PostgresSessionStore.migrate_context_storage() is the equivalent operator API. The migration is locked, transactional and repeatable; restricted runtime roles cannot update schema metadata. Old workers reject the upgraded storage contract. Keep migration-owner credentials separate from runtime credentials. Enable host context policies only after Runtime, Harness and store advertise the capability. No production token counter is bundled or certified by this storage adapter.