# Persistence and Transactions

**Status:** Approved target-state baseline  
**First realization:** I0

## Target State

PostgreSQL is authoritative product state. Drizzle is the baseline relational schema/query layer. DBOS uses PostgreSQL for durable-workflow mechanics but does not replace AWP's explicit domain state.

A lifecycle-changing command follows:

```text
begin transaction
  -> load authoritative state / expected revision
  -> resolve Principal
  -> authorize
  -> evaluate domain/policy guards
  -> write domain state
  -> write business event + audit + outbox
commit
  -> asynchronous providers/realtime consume committed result
```

## Invariants From Day 1

- no provider/Kubernetes/GitHub state is authoritative over AWP lifecycle state;
- state + required business event/audit/outbox commit atomically;
- no external network call is held open inside a database transaction unless an explicit design proves it safe/necessary;
- retries are idempotent and distinguish operation identity from execution attempt identity;
- immutable provenance/history records are not rewritten to simplify current-state queries;
- secrets are references, never plaintext product columns;
- large binary/log/artifact bodies use object storage when relational storage is inappropriate;
- migrations are version controlled and compatible with the increment's deployment strategy.

## Data Families

### Current authoritative state

Tables for Projects, Plans/Revisions, Tasks, Runs, Attempts, Changes, Reviews, CI, Decisions/Approvals, Delivery, Cluster metadata, incidents and configuration as increments activate them.

### Immutable/history/provenance

Decision history, Attempt provenance, ChangeSet revisions, Review dispositions, CI results, Release/Deployment history and audit records are append/supersede oriented.

### Business event/outbox

Events required for durable downstream/realtime/provider coordination are persisted in the same transaction as state. Consumers are idempotent.

### Object references

Artifacts, large logs, checkpoints and generated evidence may live in S3-compatible storage, referenced by immutable digest/object ID and access policy.

## Optimistic Concurrency

Mutable aggregates expose revision/version fields. Commands that would overwrite user/agent changes use expected revisions and return conflict/stale state rather than last-write-wins where consequences matter.

## Transaction Ownership

Application use cases own transaction scope. Domain modules do not start nested infrastructure transactions independently. Cross-domain changes use an explicit unit of work so event/audit ordering is deterministic.

## External Side Effects

Use transaction/outbox + durable workflow pattern:

```text
commit intent/state
  -> durable workflow/provider call
  -> observe/reconcile result
  -> commit resulting lifecycle transition
```

Provider idempotency keys include AWP operation/attempt identity where available.

## Reconciliation

Observed provider state may update observation fields or trigger reconciler commands. It cannot silently overwrite intended AWP state without lifecycle rules. Missing/external drift is surfaced as a Finding/Incident/blocked state depending on domain.

## Migrations

- schema migrations are forward/retry safe;
- destructive changes require explicit compatibility/migration plan;
- stable/external contract changes participate in R4 gates;
- data backfills are durable/idempotent and observable;
- I9 adds enterprise backup/restore/upgrade guarantees without replacing the underlying authority model.

## Increment Realization

| Increment | Persistence activation |
|---|---|
| I0 | core IDs, Project/repository seams, config, connections, audit/events/outbox, DBOS integration. |
| I1 | Plan/Task/Run/Attempt/Workspace refs/ChangeSet/Review/evidence. |
| I2–I5 | Planning, decisions/policy, observability read models, CI/evidence ledger. |
| I6 | artifacts/releases/environments/deployments. |
| I7–I8 | cluster product state, incidents/resolvers. |
| I9 | tenant/retention/backup/restore and operational migration hardening. |

## Current Implementation State

No legacy database is a compatibility requirement unless explicitly imported by a future migration Decision.

## Deferred Realization

Partitioning, read replicas, sharding and high-scale retention are evidence-driven later mechanics behind the same repository/transaction contracts.

## Temporary Dogfood Behavior

Single PostgreSQL deployment is acceptable. It must still use migration/versioning, backup-capable storage and explicit object-store references rather than local ephemeral files for durable artifacts.

## Decisions / ADRs

See ADR 0004. Changing authoritative persistence requires a superseding architecture Decision.