PostgreSQL Work protocol v1

This is the normative operation and lock manifest for Durable slice 3. It specifies observable behavior, transaction boundaries, and ordering. Internal C# and SQL may be decomposed differently, but must preserve these invariants. The package starts no worker; tests and later hosting code drive one operation at a time.

Authoritative records

PostgreSQL is the only durable truth. The Work/shared schema owns store metadata and migration history, scope tombstones and generation, payload-free dispatch, Work and its immutable acceptance snapshot, append-only history/stale observations, effect permits, and runtime epoch history.

• A terminal fact is the immutable history row committed with a terminal Work state.
• A permit records the exact attempt, lease generation, scope generation, runtime epoch, and activity identity that authorized one provider operation. A claim alone never authorizes an external effect.
• A quarantined observation is append-only evidence from a stale completion. It never changes current Work.
• ActivityId is immutable and is also the Provider contract's ProviderKey; there is no independent key hash.
• Notifications are optional payload-free latency hints. Discovery remains authoritative.

Global lock order

Every operation locking more than one protocol record uses: scope -> Work -> dispatch -> permit -> history.

Schema application instead takes one package session advisory lock before reading migration history and holds it through history validation, migration transactions, and final status validation. Runtime mutations take the shared, transaction-scoped form of that lock before validating the active epoch, so migration or epoch rotation waits for in-flight mutations and a stale epoch cannot commit afterward. Provider I/O never occurs while a database transaction or connection is held.

Operation manifest

Acceptance identity matrix

CommandId and IdempotencyKey are both mandatory and independently unique within a scope.

Concurrent first use creates one active generation-1 scope. A disabled scope is a permanent tombstone and returns ASDUR107; it is never silently reactivated.

Provider-effect safety

No unknown post-permit outcome becomes FailedTerminal. Cancellation after permit does not change this matrix.

Retry v1

Only exponential-v1 is supported. After failed attempt n >= 1:

delay = min(MaximumRetryDelay, InitialRetryDelay * 2^(n - 1))
eligible_at = PostgreSQL clock_timestamp() + delay

Calculation is cap-first and overflow safe, with no jitter or provider Retry-After. Unknown algorithms and invalid bounds fail before mutation.

Caller-owned transaction contract

Local preflight failures before SQL preserve transaction usability: inactive/wrong target, empty epoch/StoreId, unregistered Work, invalid payload/policy, or unsupported retry policy. Expected database domain outcomes also preserve usability: disabled scope, exact duplicate, command conflict, or stale fence.

PostgreSQL errors, timeout, network loss, server cancellation, or any aborting SQLSTATE require rollback. The writer never commits, rolls back, disposes, replaces, or opens a second connection for the caller's transaction. Savepoints are unsupported. Endpoint/database comparison is a configuration guard; durable identity is the StoreId read through the supplied transaction and compared with the deployment-time value.

Security and diagnostics

The migration owner alone owns DDL. The dispatcher reads payload-free discovery. The scoped runtime has minimum DML under forced RLS; PUBLIC has no privileges and runtime roles have neither ownership nor BYPASSRLS. Transaction-local scope context is defense in depth, not application authorization: a credential holder can deliberately select a scope.

Diagnostics preserve ASDURxxx, concrete Npgsql/PostgreSQL exception types, inner exceptions, stack traces, and SQLSTATE, but exclude connection strings, credentials, parameter values, payloads, and provider responses. See the ASDURxxx catalog.

Scale and contention gates

The strict PostgreSQL verification gate uses PostgreSQL 17.5 and must prove all of the following:

• discovery uses ix_dispatch_due, without a sequential scan, across 100,000 Work/dispatch rows in 100 scopes;
• 32 simultaneous claimers sharing an eight-connection pool complete within 30 seconds and produce one lease owner;
• disabling a scope projects 10,000 pre-effect Work items and dispatch rows within 30 seconds; and
• concurrent migration owners serialize, canceled lock waiters do not leak the session lock, and a terminated migration connection can retry safely on a new physical session.

These are regression gates, not throughput promises. Operators must benchmark their own payload sizes, retention, indexes, PostgreSQL settings, and contention patterns before setting production capacity.