ProcureGraph — Database Design (PostgreSQL 16, rev 4 — CONSOLIDATED)

This file is the single authoritative schema. It contains complete DDL — not amendments. research/06-architecture-data.md is historical evidence only and is NOT an implementation input; where they differ, this file wins. Migrations (Alembic) implement exactly this document.

0. Global conventions

• Money: BIGINT minor units; this build pins currency = 'USD' via CHECK (multi-currency is roadmap). Rail-native units: NUMERIC(38,0) on attempts.
• Quality: integer basis points 0..10000. Durations ≥ 0. Versions > 0.
• mission_id ≡ mandates.id (1:1). Every mission-owned table carries mission_id; only the mandates table itself is the exception.
• All states are lowercase strings mirrored exactly by Python StrEnums (no mapping layer).
• Financial history tables (journals, journal_lines, events, policy_evaluations, approval_decisions, prompt_versions) are immutable: append-only triggers + REVOKE UPDATE, DELETE, TRUNCATE from app_rw. Corrections are new rows (reversal journals, new evaluations, new versions).
• Composite FKs ((x_id, mission_id) referencing UNIQUE (id, mission_id)) enforce same-mission integrity so no row can reference a sibling mission's data.

1. Canonical state vocabulary

Entity	States
mandates.status	draft · active · completed · frozen · failed
procurement_tasks.status	pending · running · done · failed
payment_proposals.state	proposed · policy_allowed · policy_blocked · policy_escalated · reserved · executing · settled · failed · compensated
payment_attempts.status	pending · submitted · reconciling · settlement_unknown · confirmed · failed
credentials.state	active · locked · used · retired · expired (Rain mapper: retired→canceled, locked→locked)
rail_operations.status	in_progress · succeeded · failed · unknown
reconciliation_cases.status	open · resolved
agent_definitions.status	draft · evaluating · active · frozen
grant_reservations.status	reserved · committed · released
idempotency_keys.status	in_progress · completed · failed
policy verdict	allow · block · escalate (+ block_class: security · validation)
evidence_class	measured_production · measured_trial · provider_claim

2. ER overview

erDiagram
    mandates ||--o{ budget_pools : funds
    mandates ||--o{ procurement_tasks : decomposes
    mandates ||--|| evidence_plans : plans
    mandates ||--o{ provider_offers : gathers
    mandates ||--|| selection_decisions : selects
    mandates ||--o{ events : ledgers
    mandates ||--|| ledger_heads : serializes
    budget_pools ||--o{ accounts : "pool accounts"
    mandates ||--|| accounts : "one funding account"
    accounts ||--o{ journal_lines : posts
    journals ||--|{ journal_lines : balances
    providers ||--o{ provider_offers : quotes
    provider_offers ||--o{ auditions : evidences
    procurement_tasks ||--o{ auditions : audits
    procurement_tasks ||--o{ payment_proposals : pays
    provider_offers ||--o{ payment_proposals : "selected offer"
    payment_proposals ||--o{ policy_evaluations : "append-only verdicts"
    payment_proposals ||--o{ approval_decisions : approves
    payment_proposals ||--o{ payment_attempts : attempts
    payment_proposals ||--|| credentials : "0..1 credential"
    payment_proposals ||--o{ journals : journalizes
    payment_attempts ||--|| payment_receipts : "0..1 receipt"
    payment_attempts ||--|| reconciliation_cases : "0..1 case"
    payment_attempts ||--o{ outcome_verifications : verifies
    payment_proposals ||--o{ rail_operations : "typed side-effects"
    agent_definitions ||--o{ prompt_versions : versions
    agent_definitions ||--o{ spend_grants : granted
    spend_grants ||--o{ grant_reservations : consumes

3. Complete DDL

-- ============================ mission core ============================
CREATE TABLE mandates (
    id              UUID PRIMARY KEY,                -- ≡ mission_id everywhere
    status          TEXT NOT NULL CHECK (status IN
                     ('draft','active','completed','frozen','failed')),
    raw_prompt      TEXT NOT NULL,
    compiled        JSONB,
    quality_target_bp INTEGER CHECK (quality_target_bp BETWEEN 0 AND 10000),
    deadline_seconds  INTEGER CHECK (deadline_seconds > 0),
    budget_minor    BIGINT NOT NULL CHECK (budget_minor > 0),  -- IMMUTABLE ceiling
    currency        CHAR(3) NOT NULL DEFAULT 'USD' CHECK (currency = 'USD'),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE procurement_tasks (
    id              UUID PRIMARY KEY,
    mission_id      UUID NOT NULL REFERENCES mandates(id),
    kind            TEXT NOT NULL CHECK (kind IN ('audition','purchase','verification')),
    provider_id     TEXT,                            -- FK added below
    status          TEXT NOT NULL CHECK (status IN ('pending','running','done','failed')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (id, mission_id)
);
CREATE UNIQUE INDEX one_purchase_task_per_mission
  ON procurement_tasks (mission_id) WHERE kind = 'purchase';
CREATE UNIQUE INDEX one_audition_task_per_provider
  ON procurement_tasks (mission_id, provider_id) WHERE kind = 'audition';

-- ============================ pools & accounts ============================
CREATE TABLE budget_pools (
    id              UUID PRIMARY KEY,
    mission_id      UUID NOT NULL REFERENCES mandates(id),
    pool_key        TEXT NOT NULL,                   -- 'benchmark'|'purchase'|'contingency' seeds
    kind            TEXT NOT NULL CHECK (kind IN ('benchmark','purchase','contingency')),
    parent_id       UUID,
    allocated_minor BIGINT NOT NULL CHECK (allocated_minor >= 0), -- declared ceiling
    currency        CHAR(3) NOT NULL DEFAULT 'USD' CHECK (currency = 'USD'),
    UNIQUE (mission_id, pool_key),                   -- nesting-safe (NOT unique on kind)
    UNIQUE (id, mission_id),
    FOREIGN KEY (parent_id, mission_id) REFERENCES budget_pools (id, mission_id)
);
-- cycle/depth (≤3) guarded in application + migration test.

CREATE TABLE accounts (
    id              UUID PRIMARY KEY,
    mission_id      UUID NOT NULL REFERENCES mandates(id),
    pool_id         UUID,
    kind            TEXT NOT NULL CHECK (kind IN ('funding','available','reserved','spent')),
    currency        CHAR(3) NOT NULL DEFAULT 'USD' CHECK (currency = 'USD'),
    CHECK ((kind = 'funding') = (pool_id IS NULL)),  -- funding ⇔ no pool
    UNIQUE (id, mission_id),
    FOREIGN KEY (pool_id, mission_id) REFERENCES budget_pools (id, mission_id)
);
CREATE UNIQUE INDEX one_funding_account_per_mission
  ON accounts (mission_id) WHERE kind = 'funding';
CREATE UNIQUE INDEX one_account_per_pool_kind
  ON accounts (pool_id, kind) WHERE pool_id IS NOT NULL;

-- ============================ double-entry journal ============================
CREATE TABLE journals (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    mission_id      UUID NOT NULL REFERENCES mandates(id),
    reason          TEXT NOT NULL CHECK (reason IN
                     ('allocate','reserve','commit','release','refund','transfer')),
    proposal_id     UUID,                            -- FK added after proposals
    idempotency_key TEXT NOT NULL UNIQUE,
    currency        CHAR(3) NOT NULL DEFAULT 'USD' CHECK (currency = 'USD'),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    CHECK ( (reason IN ('reserve','commit','release','refund')) = (proposal_id IS NOT NULL) )
);

CREATE TABLE journal_lines (
    journal_id      BIGINT NOT NULL REFERENCES journals(id),
    line_no         SMALLINT NOT NULL,
    account_id      UUID NOT NULL REFERENCES accounts(id),
    direction       TEXT NOT NULL CHECK (direction IN ('debit','credit')),
    amount_minor    BIGINT NOT NULL CHECK (amount_minor > 0),
    PRIMARY KEY (journal_id, line_no)
);

-- Immutability: journals + lines are append-only (corrections = reversal journals).
CREATE OR REPLACE FUNCTION forbid_mutation() RETURNS trigger AS $$
BEGIN RAISE EXCEPTION '% is append-only', TG_TABLE_NAME; END $$ LANGUAGE plpgsql;
CREATE TRIGGER journals_append_only BEFORE UPDATE OR DELETE ON journals
  FOR EACH ROW EXECUTE FUNCTION forbid_mutation();
CREATE TRIGGER journal_lines_append_only BEFORE UPDATE OR DELETE ON journal_lines
  FOR EACH ROW EXECUTE FUNCTION forbid_mutation();

-- Deferred journal validation at COMMIT (fires per journal row → catches empty journals):
--   1. journal has ≥ 2 lines;  2. Σdebit = Σcredit;
--   3. every line's account.mission_id = journal.mission_id
--      and account.currency = journal.currency;
--   4. posting matrix by reason (§5) — exact (debit-kind, credit-kind) pairs only;
--   5. resulting balance of every touched non-funding account ≥ 0;
--   6. funding balance stays within [-mandate.budget_minor, 0];
--   7. for 'allocate': Σ pool allocations journalized ≤ mandate budget.
CREATE CONSTRAINT TRIGGER journal_valid
  AFTER INSERT ON journals DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION assert_journal_valid();   -- plpgsql per rules above

CREATE VIEW account_balances AS
  SELECT a.id AS account_id, a.mission_id, a.pool_id, a.kind,
         COALESCE(SUM(CASE jl.direction WHEN 'credit' THEN jl.amount_minor
                                        ELSE -jl.amount_minor END), 0) AS balance_minor
  FROM accounts a LEFT JOIN journal_lines jl ON jl.account_id = a.id
  GROUP BY a.id;

-- ============================ providers, offers, evidence ============================
CREATE TABLE providers (
    id              TEXT PRIMARY KEY,
    name            TEXT NOT NULL,
    rail            TEXT NOT NULL CHECK (rail IN ('x402','rain_card')),
    category        TEXT NOT NULL,
    endpoint_url    TEXT NOT NULL,
    trial_endpoint_url TEXT
);
ALTER TABLE procurement_tasks
  ADD CONSTRAINT tasks_provider_fk FOREIGN KEY (provider_id) REFERENCES providers(id);

CREATE TABLE provider_offers (               -- price lives HERE, never on providers
    id              UUID PRIMARY KEY,
    mission_id      UUID NOT NULL REFERENCES mandates(id),
    provider_id     TEXT NOT NULL REFERENCES providers(id),
    price_minor     BIGINT NOT NULL CHECK (price_minor >= 0),
    currency        CHAR(3) NOT NULL DEFAULT 'USD' CHECK (currency = 'USD'),
    pricing_model   TEXT NOT NULL CHECK (pricing_model IN ('per_unit','flat_bundle')),
    claimed_quality_bp INTEGER CHECK (claimed_quality_bp BETWEEN 0 AND 10000),
    merchant_id     TEXT NOT NULL,
    merchant_name   TEXT NOT NULL,
    terms           JSONB NOT NULL DEFAULT '{}',
    version         INTEGER NOT NULL DEFAULT 1 CHECK (version > 0),
    expires_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (mission_id, provider_id, version),
    UNIQUE (id, mission_id)
);

CREATE TABLE evidence_plans (
    id              UUID PRIMARY KEY,
    mission_id      UUID NOT NULL UNIQUE REFERENCES mandates(id),
    decision        TEXT NOT NULL CHECK (decision IN ('run_audition','reuse','skip')),
    rationale       JSONB NOT NULL,          -- expected improvement, cost, freshness, deadline impact
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE auditions (                     -- was "benchmarks"; product vocabulary
    id              UUID PRIMARY KEY,
    mission_id      UUID NOT NULL REFERENCES mandates(id),
    task_id         UUID NOT NULL,
    provider_id     TEXT NOT NULL REFERENCES providers(id),
    offer_id        UUID,
    attempt_id      UUID,                    -- the paid x402 call, when evidence_class = measured_production
    evidence_class  TEXT NOT NULL CHECK (evidence_class IN
                     ('measured_production','measured_trial','provider_claim')),
    quality_bp      INTEGER NOT NULL CHECK (quality_bp BETWEEN 0 AND 10000),
    latency_ms      INTEGER NOT NULL CHECK (latency_ms >= 0),
    cost_minor      BIGINT NOT NULL CHECK (cost_minor >= 0),
    raw_samples     JSONB NOT NULL DEFAULT '[]',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (task_id, mission_id) REFERENCES procurement_tasks (id, mission_id),
    FOREIGN KEY (offer_id, mission_id) REFERENCES provider_offers (id, mission_id)
    -- (attempt_id, mission_id) FK added after payment_attempts
);

CREATE TABLE selection_decisions (
    id              UUID PRIMARY KEY,
    mission_id      UUID NOT NULL UNIQUE REFERENCES mandates(id),
    offer_id        UUID NOT NULL,
    scoring_policy_version TEXT NOT NULL,
    scoring_policy_hash    TEXT NOT NULL,
    term_breakdown  JSONB NOT NULL,          -- every cost term + provenance label
    decided_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (offer_id, mission_id) REFERENCES provider_offers (id, mission_id)
);

-- ============================ proposals & policy ============================
CREATE TABLE payment_proposals (
    id              UUID PRIMARY KEY,
    mission_id      UUID NOT NULL REFERENCES mandates(id),
    pool_id         UUID NOT NULL,
    task_id         UUID NOT NULL,
    provider_id     TEXT NOT NULL REFERENCES providers(id),
    offer_id        UUID,                    -- NULL for audition micro-payments
    rail            TEXT NOT NULL CHECK (rail IN ('x402','rain_card')),
    amount_minor    BIGINT NOT NULL CHECK (amount_minor > 0),
    currency        CHAR(3) NOT NULL DEFAULT 'USD' CHECK (currency = 'USD'),
    state           TEXT NOT NULL CHECK (state IN
                     ('proposed','policy_allowed','policy_blocked','policy_escalated',
                      'reserved','executing','settled','failed','compensated')),
    version         INTEGER NOT NULL DEFAULT 1 CHECK (version > 0),
    current_policy_evaluation_id UUID,       -- FK added below
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (id, mission_id),
    FOREIGN KEY (pool_id, mission_id) REFERENCES budget_pools (id, mission_id),
    FOREIGN KEY (task_id, mission_id) REFERENCES procurement_tasks (id, mission_id),
    FOREIGN KEY (offer_id, mission_id) REFERENCES provider_offers (id, mission_id)
);
ALTER TABLE journals ADD CONSTRAINT journals_proposal_fk
  FOREIGN KEY (proposal_id, mission_id) REFERENCES payment_proposals (id, mission_id);

CREATE TABLE policy_evaluations (            -- APPEND-ONLY: full verdict history
    id              UUID PRIMARY KEY,
    proposal_id     UUID NOT NULL REFERENCES payment_proposals(id),
    proposal_version INTEGER NOT NULL CHECK (proposal_version > 0),
    policy_version  TEXT NOT NULL,
    policy_hash     TEXT NOT NULL,
    input_digest    TEXT NOT NULL,
    verdict         TEXT NOT NULL CHECK (verdict IN ('allow','block','escalate')),
    block_class     TEXT CHECK (block_class IN ('security','validation')),
    rule_results    JSONB NOT NULL,
    approval_decision_id UUID,               -- FK added below
    evaluated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    CHECK ((verdict = 'block') = (block_class IS NOT NULL))
);
CREATE TRIGGER policy_evaluations_append_only BEFORE UPDATE OR DELETE
  ON policy_evaluations FOR EACH ROW EXECUTE FUNCTION forbid_mutation();
ALTER TABLE payment_proposals ADD CONSTRAINT current_evaluation_fk
  FOREIGN KEY (current_policy_evaluation_id) REFERENCES policy_evaluations(id);

CREATE TABLE approval_decisions (            -- APPEND-ONLY: escalation approvals
    id              UUID PRIMARY KEY,
    proposal_id     UUID NOT NULL REFERENCES payment_proposals(id),
    proposal_version INTEGER NOT NULL CHECK (proposal_version > 0),
    approver        TEXT NOT NULL,           -- real identity = ADR-004 roadmap
    decision        TEXT NOT NULL CHECK (decision IN ('approved','rejected')),
    merchant_amount_digest TEXT NOT NULL,
    decided_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TRIGGER approval_decisions_append_only BEFORE UPDATE OR DELETE
  ON approval_decisions FOR EACH ROW EXECUTE FUNCTION forbid_mutation();
ALTER TABLE policy_evaluations ADD CONSTRAINT approval_fk
  FOREIGN KEY (approval_decision_id) REFERENCES approval_decisions(id);

-- ============================ attempts, receipts, credentials, rail ops ============================
CREATE TABLE payment_attempts (
    id              UUID PRIMARY KEY,
    proposal_id     UUID NOT NULL,
    mission_id      UUID NOT NULL,
    idempotency_key TEXT NOT NULL UNIQUE,
    status          TEXT NOT NULL CHECK (status IN
                     ('pending','submitted','reconciling','settlement_unknown',
                      'confirmed','failed')),
    rail_ref        TEXT,                    -- monad tx hash / card auth id
    rail_amount     NUMERIC(38,0),
    rail_currency   TEXT,
    request_digest  TEXT NOT NULL,
    response_redacted JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (id, mission_id),
    FOREIGN KEY (proposal_id, mission_id) REFERENCES payment_proposals (id, mission_id)
);
CREATE UNIQUE INDEX one_live_attempt_per_proposal
  ON payment_attempts (proposal_id) WHERE status <> 'failed';
ALTER TABLE auditions ADD CONSTRAINT auditions_attempt_fk
  FOREIGN KEY (attempt_id, mission_id) REFERENCES payment_attempts (id, mission_id);

CREATE TABLE payment_receipts (
    id              UUID PRIMARY KEY,
    attempt_id      UUID NOT NULL UNIQUE REFERENCES payment_attempts(id),
    kind            TEXT NOT NULL CHECK (kind IN
                     ('x402_settlement','card_transaction','signed_receipt')),
    external_ref    TEXT NOT NULL,           -- tx hash / auth id / receipt id
    payload_redacted JSONB NOT NULL,
    received_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE credentials (                   -- was "cards"; internal vocabulary
    id              UUID PRIMARY KEY,
    proposal_id     UUID NOT NULL UNIQUE REFERENCES payment_proposals(id),
                                             -- FULL unique: replacement unsupported
    merchant_lock   TEXT NOT NULL,
    limit_minor     BIGINT NOT NULL CHECK (limit_minor > 0),
    expires_at      TIMESTAMPTZ NOT NULL,
    state           TEXT NOT NULL CHECK (state IN
                     ('active','locked','used','retired','expired'))
);

CREATE TABLE rail_operations (               -- typed external side-effect log
    id              UUID PRIMARY KEY,
    rail            TEXT NOT NULL CHECK (rail IN ('rain','x402')),
    operation_type  TEXT NOT NULL CHECK (operation_type IN
                     ('issue_card','freeze_card','retire_card',
                      'submit_payment','confirm_settlement')),
    proposal_id     UUID NOT NULL REFERENCES payment_proposals(id),
    attempt_id      UUID REFERENCES payment_attempts(id),
    credential_id   UUID REFERENCES credentials(id),
    idempotency_key TEXT NOT NULL,
    request_digest  TEXT NOT NULL,
    status          TEXT NOT NULL CHECK (status IN
                     ('in_progress','succeeded','failed','unknown')),
    external_ref    TEXT,
    response_redacted JSONB,
    failure_class   TEXT,
    submitted_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    confirmed_at    TIMESTAMPTZ,
    reconciled_at   TIMESTAMPTZ,
    UNIQUE (rail, operation_type, idempotency_key)
);

CREATE TABLE reconciliation_cases (
    id              UUID PRIMARY KEY,
    attempt_id      UUID NOT NULL UNIQUE REFERENCES payment_attempts(id),
    reason          TEXT NOT NULL,
    status          TEXT NOT NULL CHECK (status IN ('open','resolved')),
    opened_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    next_attempt_at TIMESTAMPTZ,
    attempts        INTEGER NOT NULL DEFAULT 0 CHECK (attempts >= 0),
    assigned_to     TEXT,
    resolution      TEXT,
    resolved_by     TEXT,
    resolved_at     TIMESTAMPTZ
);

CREATE TABLE outcome_verifications (
    id              UUID PRIMARY KEY,
    mission_id      UUID NOT NULL REFERENCES mandates(id),
    task_id         UUID NOT NULL,
    attempt_id      UUID NOT NULL,
    quality_bp      INTEGER NOT NULL CHECK (quality_bp BETWEEN 0 AND 10000),
    elapsed_ms      INTEGER NOT NULL CHECK (elapsed_ms >= 0),
    verdict         TEXT NOT NULL CHECK (verdict IN ('pass','fail')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (task_id, mission_id) REFERENCES procurement_tasks (id, mission_id),
    FOREIGN KEY (attempt_id, mission_id) REFERENCES payment_attempts (id, mission_id)
);

-- ============================ idempotency & webhooks ============================
CREATE TABLE idempotency_keys (
    scope           TEXT NOT NULL,           -- e.g. 'create_mission', 'run_mission'
    key             TEXT NOT NULL,
    request_hash    TEXT NOT NULL,           -- canonical request fingerprint
    status          TEXT NOT NULL CHECK (status IN ('in_progress','completed','failed')),
    http_status     INTEGER,
    response        JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (scope, key)                 -- org_id joins the PK with tenancy (ADR-004)
);
-- Semantics (round-10 review): same key + same fingerprint + completed → replay stored
-- response verbatim; same key + same fingerprint + in_progress → 409 in-progress;
-- same key + DIFFERENT fingerprint → 422 reject; status='failed' → controlled retry
-- allowed (an accidental 500 is never cached as permanent).

CREATE TABLE rain_webhook_events (           -- transactional inbox, not just dedup
    event_id        TEXT PRIMARY KEY,
    payload_hash    TEXT NOT NULL,           -- same id + different hash = security incident
    status          TEXT NOT NULL CHECK (status IN ('received','processed','failed')),
    attempts        INTEGER NOT NULL DEFAULT 0,
    last_error      TEXT,
    received_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    processed_at    TIMESTAMPTZ,
    payload         JSONB NOT NULL
);
-- Processing rule (round-10 review): apply the business transition and mark
-- status='processed' IN THE SAME transaction — a crash between insert and apply leaves
-- status='received', so Rain's retry (or our poll) reprocesses instead of no-opping.
-- Webhook handling and reconciliation polling both lock the payment_attempts row and
-- use monotonic transitions: 'confirmed' can never regress.

-- ============================ evidence ledger ============================
CREATE TABLE ledger_heads (
    mission_id  UUID PRIMARY KEY REFERENCES mandates(id),
    last_seq    BIGINT NOT NULL DEFAULT 0,
    last_hash   BYTEA NOT NULL DEFAULT '\x'
);

CREATE TABLE events (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    mission_id      UUID NOT NULL REFERENCES mandates(id),
    seq             BIGINT NOT NULL,
    type            TEXT NOT NULL,
    schema_version  SMALLINT NOT NULL DEFAULT 1,
    causation_id    TEXT,
    task_id         UUID,
    payment_id      UUID REFERENCES payment_proposals(id),
    trace_id        TEXT,
    payload         JSONB NOT NULL,
    prev_hash       BYTEA,
    hash            BYTEA NOT NULL CHECK (octet_length(hash) = 32),
    occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (mission_id, seq)
);
CREATE TRIGGER events_append_only BEFORE UPDATE OR DELETE ON events
  FOR EACH ROW EXECUTE FUNCTION forbid_mutation();
CREATE OR REPLACE FUNCTION notify_event() RETURNS trigger AS $$
BEGIN PERFORM pg_notify('events', NEW.id::text); RETURN NULL; END $$ LANGUAGE plpgsql;
CREATE TRIGGER events_notify AFTER INSERT ON events
  FOR EACH ROW EXECUTE FUNCTION notify_event();

-- ============================ agent registry ============================
CREATE TABLE agent_definitions (
    id              UUID PRIMARY KEY,
    name            TEXT NOT NULL UNIQUE,
    description     TEXT NOT NULL,
    rules           JSONB NOT NULL DEFAULT '[]',
    status          TEXT NOT NULL CHECK (status IN
                     ('draft','evaluating','active','frozen')),
    active_prompt_version_id UUID,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE prompt_versions (
    id              UUID PRIMARY KEY,
    agent_id        UUID NOT NULL REFERENCES agent_definitions(id),
    version         INTEGER NOT NULL CHECK (version > 0),
    content         TEXT NOT NULL,
    content_hash    TEXT NOT NULL,
    boilerplate_hash TEXT NOT NULL,
    langfuse_prompt_ref TEXT,
    eval_report     JSONB,
    gated_at        TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (agent_id, version),
    UNIQUE (agent_id, id)
);
-- immutability trigger: only NULL→value transitions of gated_at / eval_report allowed.
ALTER TABLE agent_definitions ADD CONSTRAINT active_prompt_same_agent
  FOREIGN KEY (id, active_prompt_version_id) REFERENCES prompt_versions (agent_id, id);

CREATE TABLE spend_grants (
    id              UUID PRIMARY KEY,
    agent_id        UUID NOT NULL REFERENCES agent_definitions(id),
    mission_id      UUID REFERENCES mandates(id),    -- NULL = org-level envelope
    max_per_task_minor BIGINT NOT NULL CHECK (max_per_task_minor >= 0),
    max_total_minor BIGINT CHECK (max_total_minor >= 0),  -- NULL = per-task cap only
    currency        CHAR(3) NOT NULL DEFAULT 'USD' CHECK (currency = 'USD'),
    granted_by      TEXT NOT NULL,
    revoked_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX one_active_mission_grant
  ON spend_grants (agent_id, mission_id)
  WHERE revoked_at IS NULL AND mission_id IS NOT NULL;
CREATE UNIQUE INDEX one_active_org_grant
  ON spend_grants (agent_id) WHERE revoked_at IS NULL AND mission_id IS NULL;

CREATE TABLE grant_reservations (            -- consumable-envelope accounting
    id              UUID PRIMARY KEY,
    grant_id        UUID NOT NULL REFERENCES spend_grants(id),
    proposal_id     UUID NOT NULL UNIQUE REFERENCES payment_proposals(id),
    amount_minor    BIGINT NOT NULL CHECK (amount_minor > 0),
    status          TEXT NOT NULL CHECK (status IN ('reserved','committed','released')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- invariant (app + integration test): Σ(reserved+committed) per grant ≤ max_total_minor.

4. Index & FK inventory (FKs do NOT auto-index in PostgreSQL)

Beyond PK/UNIQUE-created indexes, create explicitly: procurement_tasks(mission_id) · budget_pools(mission_id) · budget_pools(parent_id) · accounts(mission_id) · journal_lines(account_id) · journals(mission_id) · journals(proposal_id) · provider_offers(mission_id) · provider_offers(provider_id) · auditions(mission_id) · auditions(provider_id) · auditions(attempt_id) · auditions(task_id) · payment_proposals(mission_id) · payment_proposals(task_id) · payment_proposals(offer_id) · policy_evaluations(proposal_id) · approval_decisions(proposal_id) · payment_attempts(proposal_id) · rail_operations(proposal_id) · rail_operations(attempt_id) · outcome_verifications(mission_id) · outcome_verifications(attempt_id) · events(mission_id, seq) (UNIQUE) · events(payment_id) · prompt_versions(agent_id) · spend_grants(agent_id) partials · spend_grants(mission_id) · grant_reservations(grant_id).

5. Posting matrix (enforced by assert_journal_valid)

reason	debit account kind	credit account kind
allocate	funding	available
reserve	available	reserved
commit	reserved	spent
release	reserved	available
refund	spent	available
transfer	available (source pool)	available (target pool)

Balance = credits − debits. Non-funding balances ≥ 0 always; funding balance ∈ [−mandate.budget, 0]; identity: available + reserved + spent = journalized allocation per pool; Σ journalized allocations = −funding balance ≤ mandate budget.

Budget-figure semantics: mandates.budget_minor = immutable authorized ceiling. budget_pools.allocated_minor = declared allocation ceiling, valid only if matched by allocate journals (integration-tested identity). Actual money state = journal balances, nothing else.

6. Invariant matrix (each row = a migration/integration test)

Invariant	Domain	App transaction	Database
Pool allocations ≤ mandate budget	Mandate aggregate	compile/fund use case	journal trigger rule 7
Journals balanced, ≥2 lines, single currency	JournalEntry VO	UoW commit	deferred trigger
Posting matrix per reason	postings.py table	—	deferred trigger
Non-negative balances	BudgetPool.reserve	authorize_and_reserve locks	deferred trigger
One live attempt per proposal	transitions guard	execute_payment	partial unique
One credential per proposal (ever)	saga	prepare_rail	full UNIQUE
Attempt/proposal/pool/task same mission	id types	use-case wiring	composite FKs
Ledger gapless + hash-chained	hashing.py	serialized append	ledger_heads lock + UNIQUE(mission,seq)
Financial history immutable	—	—	append-only triggers + REVOKE incl. TRUNCATE
Verdict history preserved	—	authorize_and_reserve appends	policy_evaluations append-only
Grant envelope not exceeded	grant.py	authorize_and_reserve locks grant	integration-tested (Σ reservations ≤ max_total)
Prompt immutable, gate before active	PromptVersion	activate use case	trigger + composite FK
Idempotent external ops	—	ports require keys	UNIQUE(rail, op, key); attempts.key UNIQUE

7. Transaction recipes (the only ways money/state moves)

Global lock order (every use case, no exceptions): 1 mission row → 2 spend grant / agent authority → 3 payment proposal → 4 accounts sorted by UUID → 5 attempt / credential / rail_operation rows → 6 ledger head. Session settings: lock_timeout and statement_timeout configured; SQLSTATE 40001/40P01 retried as the WHOLE transaction, never partially; no external HTTP, LLM call, or human wait ever happens while holding locks (claim → commit → call → finalize). Small races closed by constraints: approval_decisions gets a partial unique — one terminal decision per (proposal, proposal_version) — first valid decision wins; prompt version allocation locks the agent row (the UNIQUE(agent_id, version) is the backstop, not the mechanism); rail_operations gains prepared in its status set plus an encrypted prepared-payload column for x402 (exact signed bytes + nonce persisted BEFORE submission, reused verbatim on any retry). The provider mocks' payment-identifier cache lives in shared Postgres, never in process memory. Financial metrics are derived from journals/events, never from incrementing counters (retried code must not double-count money).

1. allocate (mission funding): one UoW — create pools + accounts + funding account → one allocate journal per pool → ledger events → commit.
2. authorize_and_reserve: one UoW — lock proposal (version check), effective grant row, pool accounts (deterministic id order) → re-evaluate all rules → append policy_evaluations row (+ set current_policy_evaluation_id) → on allow: reserve journal + grant_reservations row + state reserved → ledger events → commit. On block/escalate: no journal, state + events only.
3. submit (execute_payment): one UoW records rail_operations(in_progress) + attempt submitted + events, commit; THEN the external call happens (never inside the DB transaction); next UoW records the response.
4. confirm_settlement: one UoW — attempt confirmed + payment_receipts row + commit journal + grant reservation committed + credential used→retired (via rail op) + events.
5. compensate (proven failure only): one UoW — attempt failed + release journal + grant reservation released + credential retire op + proposal compensated + events.
6. reconcile unknown: attempt settlement_unknown + reconciliation_cases(open) + credential freeze op + events; reservation UNTOUCHED. Resolution later runs recipe 4 or 5 exactly once (case row is the dedup guard).
7. revoke (security block): spend_grants.revoked_at on the mission-scoped grant + policy evaluation row + events. Never touches journals.

8. Access, migrations, ops (unchanged posture)

Two roles (app_rw without UPDATE/DELETE/TRUNCATE on immutable tables; migrator); Alembic with tested downgrades (up→down→up in CI); LangGraph checkpoints in schema langgraph; Langfuse in its own DB; pool sizing documented in composition; EXPLAIN snapshots for hot queries (balances, ledger replay, transactions-by-card) in Phase 2; scripts/backup.sh/restore.sh + runbook; demo SLO p95 < 50 ms asserted once. NOTIFY = wake-up only; SSE broadcaster short-polls as recovery and streams per-event-type public projections. Canonical hash input: sha256(canonical_json(envelope)) where the envelope is {hash_version, prev_hash, mission_id, seq, type, schema_version, causation_id, task_id, payment_id, occurred_at, payload} — RFC 8785-style canonicalization, implemented once in domain/evidence/hashing.py, property-tested.