Skip to content

Latest commit

 

History

History
365 lines (307 loc) · 21.6 KB

postgres-outbox.md

File metadata and controls

365 lines (307 loc) · 21.6 KB

Transactional PostgreSQL outbox

TL;DR

  • edgeagent-outbox-postgres stores exact validated CloudEvents bytes inside a caller-owned PostgreSQL transaction.
  • Inbox callbacks and standalone producers use one SQLx enqueue API, while the relay uses the same driver for short claim and outcome transactions.
  • Relay operations require an idle, SQLx-managed connection and return success only after a top-level commit; an existing transaction is rejected unchanged.
  • CloudEvents identity is (source, id); an identical retry is idempotent and different content under the same identity is rejected.
  • Relay workers claim bounded batches with FOR UPDATE SKIP LOCKED and expiring leases, so concurrent workers receive disjoint records and crashed work recovers.
  • Publication, retry release, and quarantine require the current, unexpired lease owner and the generation returned by that specific claim. Reusing a worker token cannot authorize an older claim.
  • An authorized replay request atomically appends operator audit evidence and releases immutable quarantine content with a fresh attempt budget.
  • Published and quarantined records remain as operational evidence; the adapter does not delete, archive, or silently rewrite them.

Transaction boundary

Every service applies PostgresOutbox::MIGRATIONS in order inside its service-owned database schema. The migrations create and evolve edgeagent_message_outbox in the connection's current schema; they do not create a shared cross-service table.

The crate root is the stable public façade. enqueue owns exact-byte insertion and identity checks; leasing owns claim, publish, retry, and quarantine transitions; replay owns authorization audit and release; validation and error centralize bounded adapter inputs and failure categories. The existing relay module composes these operations behind the portable storage port.

Application persistence writes domain state and calls PostgresOutbox::enqueue with the caller's mutable SQLx transaction before committing. An inbound callback passes its existing transaction, so a rollback removes the inbox marker, service state, and outbox row together; a second database connection cannot provide this guarantee. The relay uses SQLx for separate, short lease and outcome transactions. External publication never occurs inside a database transaction. ADR-0016 records the single-driver boundary and migration from the temporary dual path.

PostgresOutboxRelay borrows an idle connection and owns a top-level transaction for every portable storage operation. Passing a connection borrowed from an active SQLx transaction is unsupported: the adapter returns Invariant before beginning another transaction, rather than releasing a savepoint and reporting an uncommitted transition as durable. The rejection leaves the caller's transaction and its application writes intact. Caller-owned PostgresOutbox methods remain valid inside application transactions; this restriction applies to the relay adapter, not atomic producer enqueue.

Use SQLx's transaction API for transaction control on a relay connection. Raw BEGIN, COMMIT, ROLLBACK, and savepoint statements can bypass SQLx's tracked transaction state and are unsupported. No connection may be shared with other work while borrowed by the relay. Claims are decoded and validated before commit; a Rust conversion or invariant failure cannot commit the new lease.

Dropping an uncommitted SQLx transaction queues rollback; it does not establish that PostgreSQL has already completed it. Drive the connection before reusing it, or discard an unusable connection. Cancellation or connection loss during commit confirmation leaves the outcome uncertain: never infer rollback or apply compensation. Recover through persisted state, expiry and the existing claim generation. Re-publication retains immutable message identity and relies on broker/inbox deduplication as described below. See the portable outbox storage contract for the recovery responsibility of each operation.

The outbox stores:

  • CloudEvents source and ID as a composite primary key;
  • versioned message type and definition-derived transport subject;
  • exact structured JSON bytes in BYTEA;
  • creation and next-availability timestamps from the database clock;
  • attempt count, monotonic lease generation, current lease owner, and lease expiry;
  • publication time and the last bounded failure code;
  • terminal quarantine time and bounded operator reason; and
  • append-only replay request, actor, reason, prior quarantine, and attempt evidence.

Reusing an identity with identical type, subject, and bytes returns AlreadyPresent. Reusing it with different immutable content returns MessageIdentityConflict. This prevents a retry key from silently acquiring new meaning. After an insert conflict, PostgreSQL compares the validated type, subject, and exact envelope bytes and returns one Boolean; the adapter does not download the stored envelope for the comparison. The comparison runs as a separate statement so a conflicting transaction that commits while the insert waits is visible under PostgreSQL's default READ COMMITTED isolation. The concurrency proofs require that isolation explicitly; stronger isolation may return a serialization failure and requires whole-transaction recovery.

Relay leasing and recovery

A relay performs each state change in a short transaction:

  1. claim_batch accepts a portable, validated LeaseDuration and validates the worker token and batch size.
  2. PostgreSQL selects eligible records in deterministic order using FOR UPDATE SKIP LOCKED.
  3. The claim sets a database-clock lease and increments both the attempt counter and a per-record lease generation. Its committed result returns an opaque LeaseGeneration with the message.
  4. Outside the transaction, the relay revalidates stored bytes against its MessageRegistry and publishes through MessagePublisher.
  5. A confirmed Persisted or Duplicate result calls mark_published with that claim's generation in a new transaction.
  6. A retryable failure calls release_for_retry with the same generation, policy-selected OutboxRetryDelay, and a validated, non-sensitive FailureCode. Terminal quarantine also requires the claim's generation and a validated code. The PostgreSQL adapter rechecks the token at its storage boundary.

Deterministic candidate selection is not ordered UPDATE RETURNING output, FIFO delivery, partition ordering, or publication/completion order. A locked earlier candidate may be skipped, and independent workers finish independently. Services requiring aggregate ordering must enforce it at their owning boundary.

If a relay stops after claiming, the record becomes eligible when its lease expires. If it stops after broker persistence but before mark_published, the next worker republishes the same identity and relies on bounded broker deduplication. The transactional PostgreSQL inbox provides consumer-side deduplication after the broker window expires.

Every outcome predicate compares message identity, worker token, claim generation, and unexpired lease. The adapter rejects a stale claim even when the expired lease was reclaimed by a worker using the same token. The attempt counter cannot serve as the fence: authorized replay resets the attempt budget, but never resets lease generation. A stale worker may still have sent the identical message to the broker; fencing prevents it from recording an outcome for a newer claim, while broker and inbox deduplication contain duplicate effects. Lease duration must exceed the configured publication timeout while remaining short enough for the recovery objective. The portable value bounds it to 1 millisecond through 15 minutes before storage work.

OutboxRetryDelay accepts zero through 24 hours, including fractional milliseconds; IMMEDIATE explicitly makes a released record eligible without delay. It is distinct from broker RetryDelay, which requires at least 1 millisecond. Portable values preserve precision; PostgreSQL persists these durations by flooring to integer milliseconds, including a zero-millisecond retry for positive inputs below 1 millisecond. These existing semantics and the Rust API migration are recorded in ADR-0019.

quarantine retains a leased record while removing it from future claims. It requires the current unexpired claim, pairs timestamp with a bounded reason, and cannot coexist with published state. The bounded relay owns publication failure classification and attempt policy; storage does not infer those application decisions.

Outbound replay authorization and recovery

replay_quarantined is the persistence boundary used after a control plane has authenticated an operator, authorized the target and reason, and applied any required approval policy. ReplayRequest bounds the replay request ID, operator identity, and reason before database work; it is audit evidence, not an authentication mechanism.

Migration 0003 adds edgeagent_message_outbox_replay_audit. One transaction:

  1. locks the requested quarantined record;
  2. appends its replay request ID, operator identity, reason, prior quarantine reason, prior attempt count, and database timestamps;
  3. clears quarantine and prior failure state; and
  4. resets the attempt count so the next relay claim begins at attempt one, without resetting the monotonic lease generation.

The envelope, CloudEvents identity, routing metadata, and creation time never change. Preserving identity makes an uncertain earlier publication safe: broker deduplication may suppress it, and a consumer inbox suppresses it after the broker window. The replay request ID is independently idempotent. An exact duplicate returns AlreadyRequested; reuse with changed target, actor, or reason returns ReplayRequestConflict. If the target is published, missing, or already released, a new request returns NotQuarantined.

Concurrent requests cannot release one quarantine generation twice. The first committed request wins; another request must observe a later quarantine and use a new authorization ID. Runtime roles should receive only the minimum table privileges needed by their component. Operators and browsers must not receive direct table access, and audit rows must remain append-only under deployment policy.

Failure and security behavior

Category Meaning Required response
Contract Envelope or message definition is invalid Do not enqueue; correct the producer defect
MessageIdentityConflict Existing immutable content differs Fail closed and investigate identity reuse
InvalidArgument Batch, lease, worker, delay, or replay request violates a bound Correct relay configuration or request
Storage PostgreSQL is unavailable or a transaction is retryable or ambiguous Discard failed transaction state and apply bounded recovery; uncertain confirmation does not prove rollback
StorageInvariant Stored data, schema, codec, or deterministic database rejection contradicts adapter assumptions Stop outage retries and investigate; verify persisted state before explicit repair or quarantine
LeaseLost Lease expired, disappeared, or its owner/generation no longer names this claim Stop processing that record without marking it
ReplayRequestConflict Replay request identity already names different evidence Fail closed and investigate request-ID reuse
NotQuarantined Target is missing, published, or already released Refresh operator state; do not infer success

Public errors contain stable categories and bounded validation text. PostgreSQL causes remain in the Rust error chain for redacted diagnostics. Failure codes accept only lowercase ASCII tokens and never raw exception or payload text.

The relay applies the same SQLx failure classifier during begin, statement execution and commit. Through OutboxRelayStore, retryable/ambiguous Storage becomes Unavailable, and only LeaseLost becomes StateTransition. Deterministic database faults, invalid adapter arguments, identity/replay conflicts, contract failures and unsupported transaction ownership become Invariant; an invalid configuration is not a stale-lease outcome. Local stored-row failures retain bounded field/reason context and actual conversion or validation causes without exposing stored values. A category-only failure does not fabricate an underlying cause. Raw source chains remain diagnostic evidence, not safe default log output.

Database roles should grant each service access only to its own schema. Relay workers need select/update access to their service's outbox, while unrelated services and browser identities receive none. Migration authority remains separate from runtime identity in deployment profiles.

Migration and rollout

The additive migration initializes lease_generation BIGINT NOT NULL DEFAULT 0 for existing records. Each successful claim increments it atomically under the row lock; a generation overflow fails the claim rather than wrapping or reusing an earlier generation. Publication, retry, quarantine, and audited replay leave the generation unchanged. The next claim after replay receives a new generation even though its attempt count restarts at one. This is the persisted and public-port compatibility decision in ADR-0009.

Quiesce older relay binaries and drain their in-flight work or let their leases expire. Apply the migration, then start only the generation-aware version. An older binary can still execute its owner-only outcome predicate, so mixed relay versions do not provide fencing. A rollback that restores an older relay must use the same quiesce-and-drain procedure and explicitly accepts loss of the fencing guarantee; retaining the corrected relay is the safe operational default. The column is additive and does not change message identity, envelope bytes, or retained audit evidence.

Verification

Default workspace tests validate identity construction, argument bounds, migration invariants, stored-envelope revalidation, and dependency direction without requiring PostgreSQL.

The isolated local-platform conformance test can be run with:

EDGEAGENT_POSTGRES_URL=postgresql://edgeagent:edgeagent-local-postgres@127.0.0.1:5432/edgeagent cargo test --locked -p edgeagent-outbox-postgres --test postgres_outbox -- --ignored --exact transaction_identity_and_lease_invariants_hold
EDGEAGENT_POSTGRES_URL=postgresql://edgeagent:edgeagent-local-postgres@127.0.0.1:5432/edgeagent cargo test --locked -p edgeagent-inbox-handler --test sqlx_outbox_atomicity -- --ignored --exact inbox_domain_and_sqlx_outbox_commit_or_roll_back_together

It creates a unique run-owned schema and verifies transactional rollback, idempotent enqueue, conflicting content, leasing, foreign-owner rejection, quarantine, audited replay, duplicate and conflicting replay requests, attempt budget reset, unchanged envelope bytes, publication, and queue exhaustion.

The same-owner fencing regression is a separate isolated test:

EDGEAGENT_POSTGRES_URL=postgresql://edgeagent:edgeagent-local-postgres@127.0.0.1:5432/edgeagent cargo test --locked -p edgeagent-outbox-postgres --test postgres_outbox -- --ignored --exact same_owner_reclaim_rejects_stale_transitions_and_preserves_replay_fence

It expires and reclaims a record with the same worker token, rejects the old generation for publish, retry, and quarantine without altering the new claim, and proves audited replay resets attempt budget without reusing the generation. Both tests await bounded schema cleanup after success, returned error, caught panic, or controlled timeout. Run them only against an isolated development or CI database. The local-platform CI job invokes these ignored tests explicitly; the default credential-free test gate does not run them.

The relay transaction-boundary suite is registered in the same protected local-platform inventory. With EDGEAGENT_POSTGRES_URL pointing to the isolated local PostgreSQL profile, run:

cargo xtask verify --check outbox-relay-boundaries

The runner discovers all five mandatory test identities before execution. The suite rejects nested execution for claim, publish, retry and quarantine while preserving the caller's transaction; observes successful commits from an independent connection; injects a deferred commit rejection and connection termination to distinguish invariant faults from unavailable transport; and proves an invalid stored claim rolls back before connection reuse. These are real database-boundary proofs, not a claim of general crash recovery. The default gate verifies their discovery only.

Competing workers, cancellation, and fixture recovery

cargo xtask verify --check outbox-recovery

This protected eight-test suite uses independent PostgreSQL connections at READ COMMITTED. It observes blocking with pg_blocking_pids before releasing a competing enqueue/replay transaction; no sleeps choose which operation wins. It proves disjoint SKIP LOCKED claims while the first transaction remains open, actual batch limits, identical/conflicting enqueue after commit, insertion after rollback, and one audit/release for competing replay requests. A persisted nonzero retry deadline prevents an immediate claim; advancing test-owned eligibility explicitly then permits recovery. Assertions compare identities and counts, not returned or completion order.

Cancellation probes pause all six caller-owned transitions after their SQL statement but before commit. Another connection sees unchanged committed state; dropping the operation and then reusing its connection must finish rollback before state is checked again. A separate probe drops a blocked outcome query, releases its blocker, drains pending protocol/rollback work by connection reuse, then expires/reclaims the lease and rejects the stale fence. Dropping a query future alone does not establish immediate server cancellation. These tests do not cancel during commit confirmation or compensate an unknown result; the portable cancellation contract remains authoritative for that uncertainty.

The outbox conformance, relay-boundary and recovery suites share the same owned schema fixture with the real relay proof. The relay proof retains its initial migration/enqueue/upgrade scenario. Schema names include process, time and counter identity and stay below PostgreSQL's identifier limit. Only an acknowledged successful schema creation authorizes deletion; creation runs in a transaction so interrupted unacknowledged DDL rolls back. Connections and session setup have a five-second deadline, statements five seconds, lock waits three seconds, setup ten seconds, cases thirty seconds, and cleanup ten seconds. A task boundary catches synchronous and asynchronous panics. Explicit awaited finalization preserves both case and cleanup failures and verifies namespace absence. The cleanup regression injects returned error, both panic forms and a controlled timeout; these are intentional synthetic failures caught by the test.

This fixture is not a process-kill or arbitrary non-yielding-code guarantee. The CI job's unconditional Compose teardown remains the process-crash backstop. The default ignored-test census and T3 discovery protect every named proof; missing identities fail before execution. Existing local-platform CI runs the new suite without a second acceptance runner.

Retention and operational ownership

Published rows preserve immutable retry identity; quarantine and replay audit rows preserve recovery evidence. This adapter does not implement pruning or a retention scheduler. Before adding either, the service owner must define the inbox/broker deduplication horizon, replay authorization/evidence retention, archival and legal/privacy obligations, and concurrent-claim safety. Deleting an identity can permit a later duplicate insert; an age-only cleanup job is not a safe default. See migration and rollout before changing stored state, and transaction boundary for cancellation.

Compile-checked queries and input boundaries

All eight production statements use checked SQLx query macros and committed offline metadata. Enqueue and replay identity lookups stay inline; the longer lease and replay statements live in capability-owned queries/*.sql files. The SQLx metadata guide describes schema assembly, refresh, the CI freshness gate, and the offline Clippy bypass-rejection fixtures. PostgreSQL conformance tests continue to verify behavior and migration compatibility, including exact-byte conflicts and atomic inbox/outbox writes.

Pure adapter tests exercise batch sizes 0, 1, 1000 and 1001; inclusive and adjacent token/replay bounds; contract/replay identity parity; and persisted VARCHAR widths. Enqueue relies on the validated MessageEnvelope rather than duplicating its identifier/source rules. Failure codes are still checked at the persistence boundary against storage bounds. Validator callers supply explicit safe diagnostics, not field-name dispatch with a fallback category. These checks do not replace database proofs of fences or audited replay.

Current limitations

This increment provides storage and leasing, not a continuously running worker. Bounded retry and outbound quarantine policy are implemented by the relay crate. Control-plane authentication, authorization and dual approval, operator inspection UI/API, inbound quarantine replay, archival, telemetry export, active lease extension, and quarantine retention remain separate work. Consumer inbox deduplication is implemented separately.