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

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

Referência de schema do estado atual (audit-v6 DOCS-ARCH — antes, a única narrativa de schema em ARCHITECTURE.md parava na migration 0023). A fonte de verdade é o schema Drizzle pkg/octopus/customer-app/src/lib/db/schema.ts e as 48 migrations em pkg/octopus/customer-app/src/lib/db/migrations/. O dashboard (cockpit on-prem) tem seu próprio schema em dashboard/src/db/schema.ts.

1. Domínios¶

As ~52 tabelas do customer-app se agrupam em oito domínios:

Domínio	Tabelas centrais
Tenancy / contas	`customer_accounts`, `customer_users`, `customer_onboarding_profiles`, `mssp_accounts`, `mssp_quarterly_volume`
Auth / sessão	`customer_sessions`, `customer_refresh_tokens`, `verification_tokens`, `auth_events`, `admin_sessions`, `edu_verifications`
Token economy (ledger UTXO)	`utxos`, `utxo_mint_events`, `utxo_spend_events`, `tsu_budgets`, `auto_refill_settings`, `token_transfer_requests`
Uso & 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`
Suporte	`customer_support_tickets`, `customer_support_ticket_messages`, `customer_support_ticket_attachments`, `support_saved_replies`
Compliance / auditoria	`admin_audit_events` (hash-chain WORM), `customer_audit_forward_config`, `account_enforcement`, `deployment_anomalies`, `customer_access_denylist`
Risco / anti-fraude	`risk_ip_reputation`, `risk_user_locations`, `email_blocklist`, `rate_limit_buckets`, `leads`, `lead_activities`

2. ERD central (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. Dicionário de dados — tabelas load-bearing¶

Tabela	Colunas-chave	Notas
`customer_accounts`	`id (uuid pk)`, `deployment_id`, `cell_id`, `tier`, `status`, `kyc_status`, `country_code`, `referral_code`	Raiz do tenant. `status` controla o ciclo de vida (email_pending → kyc_pending → active). Âncora de RLS (`account_id` nas tabelas de tenant).
`utxos`	`id`, `account_id (fk)`, `amount`, `spent`, `created_at`	Ledger de unspent-outputs de tokens (ADR-0099). Enforçado por RLS; só eventos de mint/spend mutam.
`utxo_spend_events`	`id`, `account_id`, `amount`, `test_id`, `intensity`, `spent_at`, `refunded_at`, `refund_reason`	Log de spend imutável; refunds são registrados, nunca deletados.
`admin_audit_events`	`id`, `seq`, `prev_hash`, `hash`, `actor`, `action`, `payload`, `created_at`	WORM: trigger no DB bloqueia UPDATE + DELETE precoce; hash chain SHA-256; export RFC 5424.
`stripe_webhook_events`	`id (stripe evt id, pk)`, `event_type`, `outcome`, `stripe_created_at`, `livemode`	Idempotência + retry: `outcome` não-terminal (NULL/`failed`) é reprocessado.
`identity_webhook_events`	`event_id (pk)`, `event_type`, `account_id`, `session_id`, `outcome`	Mesmo contrato de idempotência/retry para Stripe Identity (KYC).
`licenses`	`id`, `account_id`, `deployment_id`, `tier`, `expires_at`, `signed_blob`	Licença on-prem; heartbeats caem em `license_heartbeats`.

4. Row-Level Security¶

As tabelas com escopo de tenant (utxos, utxo_mint_events, utxo_spend_events, usage_tickets, usage_events, api_keys, webhook_endpoints, outbound_events, tsu_budgets) carregam uma política RLS tenant_isolation (migrations 0026/0029/0034/0041). Rotas de tenant rodam dentro de withTenant() (seta app.account_id); caminhos cross-tenant de sistema (metrics, crons, admin) o deixam sem setar e passam — ver o guard test de rotas de tenant. O cutover completo para default-deny (dual DB roles) é rastreado separadamente.

5. Regenerando este documento¶

O agrupamento de domínios e a lista de tabelas derivam do schema.ts; quando você adicionar uma pgTable, adicione-a ao domínio certo na §1 e, se for load-bearing, à §3. Uma melhoria futura pode auto-gerar o ERD a partir dos metadados do Drizzle.