Database schema¶

Implemented

Source: doc/architecture/db_schema_v1.sql (2,574 lines) · doc/architecture/ERD.md

PostgreSQL 16. Single database. Service packages own their own tables (no cross-service joins; cross-domain data flows via NATS).

Domain map¶

flowchart LR
    classDef id   fill:#fff8e1,stroke:#f57f17
    classDef inv  fill:#e3f2fd,stroke:#1565c0
    classDef prov fill:#fff3e0,stroke:#e65100
    classDef bil  fill:#e8f5e9,stroke:#2e7d32
    classDef pay  fill:#f3e5f5,stroke:#6a1b9a
    classDef app  fill:#fce4ec,stroke:#c2185b
    classDef stor fill:#e0f7fa,stroke:#00838f
    classDef adm  fill:#ede7f6,stroke:#5e35b1
    classDef sys  fill:#eceff1,stroke:#455a64

    subgraph Identity[Identity & tenancy]
      ORG[organizations<br/>tenants]:::id
      USR[users]:::id
      PROJ[projects]:::id
      TMEM[tenant_memberships]:::id
      PMEM[project_memberships]:::id
      SVC[service_accounts]:::id
      UPI[user_posix_identities]:::id
    end

    subgraph Inventory[Inventory]
      SKU[sku_catalog]:::inv
      NODE[nodes]:::inv
      SLOT[node_resource_slots]:::inv
      OSI[os_images]:::inv
      NIC[node_image_cache]:::inv
    end

    subgraph Provisioning[Provisioning]
      ALOC[allocations]:::prov
      ARC[allocation_resource_claims]:::prov
      APL[allocation_placements]:::prov
      ASK[allocation_ssh_public_keys]:::prov
      NTSK[node_tasks]:::prov
      OXI[outbox_intents]:::prov
    end

    subgraph Billing[Billing]
      USE[usage_records]:::bil
      LED[ledger_entries]:::bil
      LBE[low_balance_events]:::bil
    end

    subgraph Payments[Payments]
      PAY[payment_sessions]:::pay
      PWH[payment_webhook_events]:::pay
    end

    subgraph Apps[App platform]
      AIN[app_instances]:::app
      AIM[app_instance_members]:::app
      AIE[app_instance_events]:::app
      AMF[app_manifests]:::app
    end

    subgraph Storage[Storage]
      STO[storage_objects]:::stor
      STN[storage_namespaces]:::stor
    end

    subgraph Admin[Admin]
      AUD[audit_logs]:::adm
      REF[refund_records]:::adm
    end

    subgraph System[System]
      OXE[outbox_events]:::sys
      POL[policy_values]:::sys
    end

    USR --> TMEM --> ORG
    USR --> PMEM --> PROJ
    PROJ --> ORG
    PROJ --> ALOC
    USR --> ALOC
    SKU --> ALOC
    NODE --> ALOC
    NODE --> SLOT
    NODE --> NIC
    SLOT --> ARC --> ALOC
    ALOC --> APL
    ALOC --> ASK
    ALOC --> USE
    USE --> LED
    LED --> LBE
    USR --> PAY --> LED
    OXE -.outbox-relay.-> NTSK

Key tables — by domain¶

Identity & tenancy¶

Table	Notes
`organizations`	Tenant root. Carries `stripe_customer_id`.
`users`	Actor identity. Not the owner of resources.
`projects`	Operational scope inside a tenant.
`tenant_memberships`	User→tenant binding with role; `UNIQUE(user_id)` in MVP enforces single-tenant.
`project_memberships`	User→project binding with role.
`service_accounts`	Project-scoped non-human identities.
`user_posix_identities`	OS uid/gid + username for SSH provisioning.

Inventory¶

Table	Notes
`sku_catalog`	`capacity_shape`, `gpus_total > 0` (GPU-only constraint in MVP), `resource_profile` jsonb
`nodes`	Physical hosts. `org_id` nullable = shared pool; non-null = tenant-dedicated.
`node_resource_slots`	Slice slot map. Required `capacity_metadata` keys: see Capacity shapes.
`os_images`	Image catalog with `digest_sha256`, `target` (`baremetal\\|vm_slice`), `compatible_skus[]`.
`node_image_cache`	Per-node cache state for images.

Provisioning¶

Table	Notes
`allocations`	Customer lease, lifecycle, billing anchor. `node_id` is compatibility/read-model.
`allocation_resource_claims`	Durable placement source of truth. `claim_kind` ∈ `node_exclusive \\| slot`.
`allocation_placements`	Resolved placement summary.
`allocation_ssh_public_keys`	Public keys registered to an allocation.
`node_tasks`	Typed task queue for node-agent. `status`: queued → dispatched → completed/failed.
`outbox_intents`	Intent rows tied to allocations for outbox ordering.

Billing & payments¶

Table	Notes
`usage_records`	Per-allocation per-interval usage. Source for ledger debits.
`ledger_entries`	Immutable. No UPDATE, no DELETE. Corrections are new entries.
`low_balance_events`	Idempotency for warning emission.
`payment_sessions`	Stripe checkout sessions.
`payment_webhook_events`	Webhook idempotency by event id.
`refund_records`	Hybrid policy: provider refund vs internal credit.

Apps¶

Table	Notes
`app_instances`	One row per launched app instance.
`app_instance_members`	Allocation/node/slot binding for distributed instances.
`app_instance_events`	Lifecycle event timeline.
`app_manifests`	Registered app manifests with compatibility metadata.

Storage¶

Table	Notes
`storage_objects`	Metadata for object-storage-backed user files.
`storage_namespaces`	Per-user / per-project storage scope.

Admin / system¶

Table	Notes
`audit_logs`	Immutable. Allowlisted `metadata` jsonb keys.
`policy_values`	`global → plan → org → user` scoped policy resolution.
`outbox_events`	Cross-cutting outbox table read by `cmd/outbox-relay`.

Hard invariants¶

Invariant	Where enforced
`ledger_entries` immutable	No update/delete grants; reviewer-enforced
`audit_logs` immutable + allowlisted metadata	Same; CI gate `audit_mandatory_guard.sh`
Slot is schedulable only with full `capacity_metadata`	Orchestrator query predicates
One active claim per node-exclusive lock	DB constraint + `FOR UPDATE`
`allocations.capacity_shape` ∈ `{baremetal, gpu_slice}`	Check constraint + SKU validation
`policy_values` carry `min/max/enum` bounds	Policy update API
Outbox row + domain change in one tx	Reviewer-enforced; integration tests

Concurrency primitives in use¶

FOR UPDATE SKIP LOCKED for slot reservation, outbox claim, and queued task claim
Row-level node lock (FOR UPDATE) for baremetal placement
CTE-based task claim (UPDATE … WHERE status='queued' RETURNING …) in cmd/api
clock_timestamp() (not now()) for terminal-state timestamps after long waits

Partitioning & retention¶

See doc/architecture/Partitioning_and_Retention_Strategy.md:

audit_logs — partition by month; long retention
usage_records — partition by month; medium retention
ledger_entries — partition by year; retain indefinitely (compliance)
node_tasks — short retention; older than 30 days archived

Migrations¶

Forward-only DDL in doc/architecture/db_schema_v1.sql.
Migration plan: Schema_Migration_Plan.md.
Verification: forward + rollback tested in make test-integration.