PostgreSQL Flow protocol v1

This is the normative operation and lock manifest for Durable slice 4. It specifies observable behavior, transaction boundaries, lock hierarchy, crash recovery boundaries, and execution state transitions for the PostgreSQL Flow engine. The package starts no worker or background polling service; unit and integration tests and future hosted runtime code drive each operation deterministically.

Authoritative records

PostgreSQL is the sole durable truth for Flow state. The appsurface_durable schema owns flow instances, idempotency keys, commands, event delivery tracking, execution history, active waits, timers, and RLS security policies alongside the Work/shared protocol tables.

• appsurface_durable.flow_instance: Primary state table keyed by (scope_id, flow_instance_id). Tracks current node, execution state, context/resume payload envelopes, active epoch, scope generation, lease ownership, and suspension details.
• appsurface_durable.flow_command: Command log keyed by (scope_id, command_id). Deduplicates incoming start, event, cancel, and release commands. Enforces start identity through ix_flow_command_start_idempotency and single-use event delivery through ix_flow_command_event.
• appsurface_durable.flow_history: Append-only sequence of execution events keyed by auto-incrementing event_id. Records state transitions, node entries/exits, inputs, outputs, context snapshots, and diagnostic details in jsonb.
• appsurface_durable.flow_wait: Retained wait lineage keyed by wait_id. Supports event and activity waits, permits at most one active/suspended wait per Flow, and links child activities through (scope_id, child_work_id).
• appsurface_durable.flow_timer: Scheduled timers keyed by timer_id, tied to an exact wait and registered Flow revision.
• appsurface_durable.flow_dispatch: The payload-free global discovery surface for Flow and timer candidates. The dispatcher can select it but cannot mutate or read payload tables.

Flow state machine

A Flow instance transitions through the following formal states:

• ready: Ready for step evaluation or initial execution.
• evaluating: Transition evaluation in progress under active lease/lock.
• waiting_event: Suspended awaiting an incoming external event or timer expiry.
• waiting_timer: Suspended awaiting timer expiry.
• waiting_activity: Suspended awaiting completion of an enqueued child Work activity.
• cancel_pending: Cancellation requested while an external activity or step is in progress.
• completed: Terminal state following successful execution to a completion node.
• faulted: Terminal state following an unhandled exception or failed condition.
• canceled: Terminal state following confirmed cancellation.
• suspended: Non-terminal safety state entered when definition mismatch, non-restorable child failure, or unexpected state occurs (suspended_from_state preserves the recovery source; evaluation failures preserve their last releasable state, ready).

Global lock order

To prevent deadlocks across concurrent flow steps, event deliveries, and child activity completions, all operations adhere to the canonical lock hierarchy:

scope -> child Work (when present) -> parent Flow -> Flow command -> Flow wait -> Flow timer -> Flow dispatch -> Work dispatch -> permit/operator command -> Work history -> Flow history

Every operation acquires only the relations it needs, but always preserves this relative order when the relation row already exists; rows within one class are locked in stable primary-key order. The only creation exception is activity acceptance: it creates a previously absent deterministic child Work under its held Flow claim, when no child Work row exists to lock before the parent. It must not lock a pre-existing child after claiming the parent; later operations that lock an existing child and parent acquire the child Work before the parent Flow. Any parent-fence loss rolls the entire transaction back.

Schema management and epoch rotation take the same exclusive session advisory lock as Work operations before altering schema or rotating active epoch credentials.

Operation manifest

11 Crash recovery boundaries

Flow persistence guarantees deterministic recovery across 11 explicit process crash points:

1. Start Flow Pre-Commit: Crash before SQL commit. PostgreSQL rolls back transaction; no flow_instance, flow_command, or flow_history row exists. Caller may safely retry.
2. Start Flow Post-Commit (ready): Crash after commit. flow_instance is durable in ready state at revision 1. Recovery worker discovers ready instance and invokes evaluation.
3. Evaluation In-Flight Before Child Activity Accept: Crash during step calculation before Work enqueue transaction. Transaction rolls back; instance remains in ready state at current revision.
4. Child Activity Accepted (waiting_activity): Crash after Work item and flow_wait commit. Work item is durable in dispatch; Flow is durable in waiting_activity. Work dispatcher processes child activity independently.
5. Child Activity Execution Complete / Permit Acquired: Crash after provider acquires Work effect permit and executes child activity. Work recovery or completion path handles Work item; Flow remains in waiting_activity.
6. Child Activity Terminal Completion Committed, Flow Evaluation Pending: Crash after child Work completion transaction commits (flow_wait set to activity_completed, Flow state set to ready), but before next Flow step evaluation completes. Flow recovery discovers instance in ready state with completed activity result payload and continues step evaluation.
7. Event Delivery Pre-Commit: Crash during external event delivery transaction. Transaction rolls back; event_id is unconsumed and flow_wait remains active. Caller may retry event delivery.
8. Event Delivered (event_won), Flow Evaluation Pending: Crash after event delivery commits (flow_command accepted, wait state event_won, timer superseded, Flow state ready). Discovery finds Flow in ready state with delivered event payload and evaluates next step.
9. Timer Fired (timer_won), Flow Evaluation Pending: Crash after timer fire transaction commits (flow_timer state fired, wait state timer_won, Flow state ready). Discovery finds Flow in ready state with timer-expiry signal and evaluates next step.
10. Flow Suspended: Crash after safety suspension commits (state = 'suspended'). Instance remains durable in suspended state with preserved suspended_from_state. Requires explicit operator release or reconciliation command.
11. Flow Terminal State Committed (completed / faulted / canceled): Crash after terminal state commit. flow_instance is permanently terminal; subsequent commands or events are safely rejected with already_terminal or ASDUR110.

Idempotency and race resolution matrix

Options reuse and configuration sharing

PostgreSqlDurableWorkOptions is reused directly by PostgreSqlDurableFlowClient and PostgreSqlDurableFlowStore, or shared via compatible options types:

• ExpectedStoreId: Must match the deployment-time StoreId stored in appsurface_durable.store_metadata. Mismatch fails closed with ASDUR115.
• RuntimeEpoch: Must match the currently active epoch initialized or rotated via IDurableRuntimeSchemaManager. Stale epoch fails closed with ASDUR108 or epoch fence violation.
• WakeNotificationMode: Flow and Work engines share PostgreSQL LISTEN/NOTIFY notification settings (default: Disabled).
• Schema Compatibility: Shared schema manager checks the current forward migration catalog. Flow requires its 0001_work_shared.sql, 0002_forced_rls.sql, and 0003_flow_protocol.sql prerequisites and remains compatible with later Schedule migrations that it does not interpret.

Migration order and rollback posture

PostgreSQL Flow schema requires applying migrations strictly in order:

1. 0001_work_shared.sql: Defines store_metadata, schema_migration, scope, work, dispatch, work_operator_command, effect_permit, scope_history, work_history.
2. 0002_forced_rls.sql: Enables and forces Row Level Security on Work entities.
3. 0003_flow_protocol.sql: Defines the six Flow relations, indexes, constraints, and forced RLS policies.

After applying any migration that adds package relations, run configure-postgresql-roles.sql again: migrations must run first, then the role recipe grants the reviewed Flow privileges to existing dispatcher and scoped-runtime roles.

Rollback posture

• Applied migrations are forward-only. The package provides no destructive down-migration scripts.
• Rolling back application binaries does not authorize rolling back database schema.
• Strict ./Durable/verify-postgresql.sh --ci --flow builds the pinned v2 Work binary from 0e57477bab00b1951192c82ca28fdda977da2092 and runs it concurrently with current Work/Flow operations against v3. The rolling claim is limited to that Work-only path; v2 cannot process Flow.
• Manual DDL execution requires using psql with -v ON_ERROR_STOP=1.

Security and Row-Level Security (RLS)

All six Flow tables, including payload-free flow_dispatch, have Row Level Security enabled and forced:

The dispatcher credential normally receives global flow_dispatch discovery. Run configure-postgresql-roles.sql immediately after applying 0003 and before granting SELECT on flow_dispatch: the migration's flow_dispatch_global_discovery policy is initially PUBLIC, because it has no scope-restricted discovery fallback. The role recipe narrows that policy to the dispatcher credential and migration owner; the latter is required only for the migration-owner SECURITY DEFINER aggregate-health function introduced by 0005_runtime_heartbeat.sql. The recipe also adds the runtime-role scope predicate. The scoped runtime credential then retains SELECT and column-scoped UPDATE privileges but sees Flow dispatch rows directly only after its transaction sets the matching appsurface_durable.scope_id.

ALTER TABLE appsurface_durable.flow_instance ENABLE ROW LEVEL SECURITY;
ALTER TABLE appsurface_durable.flow_instance FORCE ROW LEVEL SECURITY;
CREATE POLICY flow_instance_scope_isolation ON appsurface_durable.flow_instance
    USING (scope_id = nullif(current_setting('appsurface_durable.scope_id', true), ''))
    WITH CHECK (scope_id = nullif(current_setting('appsurface_durable.scope_id', true), ''));

Runtime connections must set appsurface_durable.scope_id transaction-locally. Unset or empty scope settings prevent access to all rows.

Diagnostics catalog mapping

Flow operations emit append-only ASDURxxx codes. Safe error reporting excludes credentials, payloads, and parameter values.

See the diagnostics catalog for full error details.