ER Diagram¶
All 29 tables from Alembic migrations 0001–0004 (backend/alembic/versions/). Key
columns only; mission_id ≡ mandates.id everywhere; composite same-mission FKs and
partial uniques are annotated in comments. Append-only tables (triggers + REVOKE incl.
TRUNCATE): journals, journal_lines, events, policy_evaluations,
approval_decisions, prompt_versions (write-once fields only).
erDiagram
%% ---------- mission core ----------
mandates ||--o{ procurement_tasks : decomposes
mandates ||--o{ budget_pools : funds
mandates ||--|| evidence_plans : plans
mandates ||--o{ provider_offers : gathers
mandates ||--o| selection_decisions : selects
mandates ||--|| ledger_heads : serializes
mandates ||--o{ events : ledgers
mandates {
uuid id PK
text status "draft|active|completed|frozen|failed"
bigint budget_minor "immutable ceiling"
int quality_target_bp
bool attack_simulation_enabled "durable demo switch (0003)"
}
procurement_tasks {
uuid id PK
uuid mission_id FK
text kind "audition|purchase|verification"
}
evidence_plans {
uuid id PK
uuid mission_id FK "UNIQUE"
text decision "run_audition|reuse|skip"
jsonb rationale
}
%% ---------- double-entry money ----------
budget_pools ||--o{ accounts : "pool accounts"
mandates ||--o| accounts : "one funding account (partial unique)"
journals ||--|{ journal_lines : "balanced legs (deferred triggers on BOTH tables)"
accounts ||--o{ journal_lines : posts
budget_pools {
uuid id PK
uuid mission_id FK
text pool_key "UNIQUE(mission,pool_key)"
uuid parent_id FK "same-mission nesting"
bigint allocated_minor
}
accounts {
uuid id PK
uuid mission_id FK
uuid pool_id FK "NULL iff kind=funding"
text kind "funding|available|reserved|spent"
}
journals {
bigint id PK
uuid mission_id FK
text reason "allocate|reserve|commit|release|refund|transfer"
uuid proposal_id FK "required for reserve/commit/release/refund"
text idempotency_key UK
}
journal_lines {
bigint journal_id PK "composite w/ line_no"
uuid account_id FK
text direction "debit|credit"
bigint amount_minor
}
%% ---------- providers & evidence ----------
providers ||--o{ provider_offers : quotes
provider_offers ||--o{ auditions : evidences
procurement_tasks ||--o{ auditions : audits
provider_offers ||--o| selection_decisions : wins
providers {
text id PK
text rail "x402|rain_card"
text endpoint_url
text trial_endpoint_url
}
provider_offers {
uuid id PK
uuid mission_id FK
text provider_id FK
bigint price_minor
int version "UNIQUE(mission,provider,version)"
}
auditions {
uuid id PK
uuid mission_id FK
uuid task_id FK "composite same-mission"
uuid attempt_id FK "paid x402 call"
text evidence_class "measured_production|measured_trial|provider_claim"
int quality_bp
}
selection_decisions {
uuid id PK
uuid mission_id FK "UNIQUE"
uuid offer_id FK
text scoring_policy_version
jsonb term_breakdown "provenance-labeled"
}
%% ---------- payment pipeline ----------
payment_proposals ||--o{ policy_evaluations : "append-only verdict history"
payment_proposals ||--o{ approval_decisions : "one terminal per version (partial unique)"
payment_proposals ||--o{ payment_attempts : attempts
payment_proposals ||--o| credentials : "FULL unique proposal_id"
payment_proposals ||--o{ rail_operations : "typed side-effects"
payment_proposals ||--o{ journals : journalizes
payment_attempts ||--o| payment_receipts : receipt
payment_attempts ||--o| reconciliation_cases : "held settlements"
payment_attempts ||--o{ outcome_verifications : verifies
payment_attempts ||--o{ reconciliation_jobs : "webhook-driven wake (0004)"
payment_proposals {
uuid id PK
uuid mission_id FK
uuid pool_id FK "composite same-mission"
uuid task_id FK
uuid offer_id FK
text state "9 lowercase states"
int version "optimistic lock"
}
policy_evaluations {
uuid id PK
uuid proposal_id FK
text verdict "allow|block|escalate"
text block_class "security|validation"
jsonb rule_results
}
approval_decisions {
uuid id PK
uuid proposal_id FK
text decision "approved|rejected"
text merchant_amount_digest
}
payment_attempts {
uuid id PK
uuid proposal_id FK
text idempotency_key UK
text status "pending|submitted|reconciling|settlement_unknown|confirmed|failed"
text rail_ref "tx hash / auth id"
}
payment_receipts {
uuid id PK
uuid attempt_id FK "UNIQUE"
text kind "x402_settlement|card_transaction|signed_receipt"
text external_ref
}
credentials {
uuid id PK
uuid proposal_id FK "UNIQUE ever"
text state "active|locked|used|retired|expired"
text merchant_lock
}
rail_operations {
uuid id PK
uuid proposal_id FK
text rail "rain|x402"
text operation_type "issue|freeze|retire|submit|confirm"
text idempotency_key "UNIQUE(rail,type,key)"
text status "prepared|in_progress|succeeded|failed|unknown"
text owner "lease fencing (0004)"
timestamptz lease_expires_at "takeover (0004)"
bytea prepared_payload "encrypted signed bytes"
jsonb recovery_payload "resume data (0004)"
}
reconciliation_cases {
uuid id PK
uuid attempt_id FK "UNIQUE"
text status "open|resolved"
}
reconciliation_jobs {
uuid id PK
uuid attempt_id FK "idempotent per attempt"
timestamptz next_attempt_at
}
outcome_verifications {
uuid id PK
uuid mission_id FK
uuid attempt_id FK "composite same-mission"
int quality_bp
text verdict "pass|fail"
}
%% ---------- agents ----------
agent_definitions ||--o{ prompt_versions : versions
agent_definitions ||--o{ spend_grants : granted
spend_grants ||--o{ grant_reservations : consumes
agent_definitions {
uuid id PK
text status "draft|evaluating|active|frozen"
uuid active_prompt_version_id FK "composite same-agent"
}
prompt_versions {
uuid id PK
uuid agent_id FK
int version "UNIQUE(agent,version)"
text content_hash
text boilerplate_hash
timestamptz gated_at "write-once"
}
spend_grants {
uuid id PK
uuid agent_id FK
uuid mission_id FK "NULL = org envelope; partial uniques per scope"
bigint max_per_task_minor
bigint max_total_minor
timestamptz revoked_at "mission-scoped freeze"
}
grant_reservations {
uuid id PK
uuid grant_id FK
uuid proposal_id FK "UNIQUE"
text status "reserved|committed|released"
}
%% ---------- infrastructure ----------
events {
bigint id PK
uuid mission_id FK
bigint seq "UNIQUE(mission,seq); gapless via ledger_heads lock"
text type "dotted vocabulary"
bytea hash "sha256 canonical envelope, chain via prev_hash"
text trace_id
}
ledger_heads {
uuid mission_id PK
bigint last_seq
bytea last_hash
}
idempotency_keys {
text scope PK "composite w/ key"
text key PK
text status "in_progress|completed|failed + owner/lease (0003)"
jsonb response "verbatim replay"
}
rain_webhook_events {
text event_id PK
text payload_hash "dup id + diff hash = incident"
text status "received|processed|failed"
}