Data Model & ERD — customer-app (SaaS control plane)¶

Read in your language: English · Português · Español

Current-state schema reference (audit-v6 DOCS-ARCH — previously the only schema narrative in ARCHITECTURE.md stopped at migration 0023). Source of truth is the Drizzle schema pkg/octopus/customer-app/src/lib/db/schema.ts and the 48 migrations in pkg/octopus/customer-app/src/lib/db/migrations/. The dashboard (on-prem cockpit) has its own schema under dashboard/src/db/schema.ts.

1. Domains¶

The ~52 customer-app tables group into eight domains:

Domain	Core tables
Tenancy / accounts	`customer_accounts`, `customer_users`, `customer_onboarding_profiles`, `mssp_accounts`, `mssp_quarterly_volume`
Auth / session	`customer_sessions`, `customer_refresh_tokens`, `verification_tokens`, `auth_events`, `admin_sessions`, `edu_verifications`
Token economy (UTXO ledger)	`utxos`, `utxo_mint_events`, `utxo_spend_events`, `tsu_budgets`, `auto_refill_settings`, `token_transfer_requests`
Usage & metering	`usage_tickets`, `usage_events`, `usage_report_nonces`, `license_heartbeats`, `licenses`
Billing (Stripe)	`stripe_webhook_events`, `identity_webhook_events`, `promo_codes`, `promo_redemptions`, `rate_cards`, `pricing_templates`
Support	`customer_support_tickets`, `customer_support_ticket_messages`, `customer_support_ticket_attachments`, `support_saved_replies`
Compliance / audit	`admin_audit_events` (WORM hash-chain), `customer_audit_forward_config`, `account_enforcement`, `deployment_anomalies`, `customer_access_denylist`
Risk / anti-fraud	`risk_ip_reputation`, `risk_user_locations`, `email_blocklist`, `rate_limit_buckets`, `leads`, `lead_activities`

2. Core ERD (tenancy + token economy + billing)¶

erDiagram
    customer_accounts ||--o{ customer_users : "has"
    customer_accounts ||--o| customer_onboarding_profiles : "onboarding"
    customer_accounts ||--o{ customer_sessions : "sessions"
    customer_accounts ||--o{ utxos : "owns (RLS)"
    customer_accounts ||--o{ utxo_mint_events : "mints"
    customer_accounts ||--o{ utxo_spend_events : "spends"
    customer_accounts ||--o| tsu_budgets : "budget"
    customer_accounts ||--o{ usage_tickets : "meters"
    customer_accounts ||--o{ api_keys : "keys"
    customer_accounts ||--o{ licenses : "on-prem license"
    customer_accounts ||--o{ customer_support_tickets : "tickets"
    mssp_accounts ||--o{ customer_accounts : "parent-of (sub-accounts)"
    customer_users ||--o{ auth_events : "audit"
    utxos ||--o{ utxo_spend_events : "consumed-by"
    customer_support_tickets ||--o{ customer_support_ticket_messages : "thread"
    customer_support_ticket_messages ||--o{ customer_support_ticket_attachments : "files"
    stripe_webhook_events }o--|| customer_accounts : "billing events"
    identity_webhook_events }o--|| customer_accounts : "KYC events"

3. Data dictionary — load-bearing tables¶

Table	Key columns	Notes
`customer_accounts`	`id (uuid pk)`, `deployment_id`, `cell_id`, `tier`, `status`, `kyc_status`, `country_code`, `referral_code`	Tenant root. `status` gates the lifecycle (email_pending → kyc_pending → active). RLS anchor (`account_id` on tenant tables).
`utxos`	`id`, `account_id (fk)`, `amount`, `spent`, `created_at`	Token unspent-output ledger (ADR-0099). RLS-enforced; only mint/spend events mutate.
`utxo_spend_events`	`id`, `account_id`, `amount`, `test_id`, `intensity`, `spent_at`, `refunded_at`, `refund_reason`	Immutable spend log; refunds are recorded, never deleted.
`admin_audit_events`	`id`, `seq`, `prev_hash`, `hash`, `actor`, `action`, `payload`, `created_at`	WORM: DB trigger blocks UPDATE + early DELETE; SHA-256 hash chain; RFC 5424 export.
`stripe_webhook_events`	`id (stripe evt id, pk)`, `event_type`, `outcome`, `stripe_created_at`, `livemode`	Idempotency + retry: non-terminal `outcome` (NULL/`failed`) is reprocessed.
`identity_webhook_events`	`event_id (pk)`, `event_type`, `account_id`, `session_id`, `outcome`	Same idempotency/retry contract for Stripe Identity (KYC).
`licenses`	`id`, `account_id`, `deployment_id`, `tier`, `expires_at`, `signed_blob`	On-prem license; heartbeats land in `license_heartbeats`.

4. Row-Level Security¶

Tenant-scoped tables (utxos, utxo_mint_events, utxo_spend_events, usage_tickets, usage_events, api_keys, webhook_endpoints, outbound_events, tsu_budgets) carry a tenant_isolation RLS policy (migrations 0026/0029/0034/0041). Tenant routes run inside withTenant() (sets app.account_id); cross-tenant system paths (metrics, crons, admin) leave it unset and pass — see the tenant-route guard test. The full default-deny cutover (dual DB roles) is tracked separately.

5. Regenerating this document¶

The domain grouping and table list are derived from schema.ts; when you add a pgTable, add it to the right domain in §1 and, if it is load-bearing, to §3. A future enhancement can auto-generate the ERD from the Drizzle metadata.