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

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

Referencia de schema del estado actual (audit-v6 DOCS-ARCH — antes, la única narrativa de schema en ARCHITECTURE.md se detenía en la migration 0023). La fuente de verdad es el schema Drizzle pkg/octopus/customer-app/src/lib/db/schema.ts y las 48 migrations en pkg/octopus/customer-app/src/lib/db/migrations/. El dashboard (cockpit on-prem) tiene su propio schema en dashboard/src/db/schema.ts.

1. Dominios¶

Las ~52 tablas del customer-app se agrupan en ocho dominios:

Dominio	Tablas centrales
Tenancy / cuentas	`customer_accounts`, `customer_users`, `customer_onboarding_profiles`, `mssp_accounts`, `mssp_quarterly_volume`
Auth / sesión	`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`
Soporte	`customer_support_tickets`, `customer_support_ticket_messages`, `customer_support_ticket_attachments`, `support_saved_replies`
Compliance / auditoría	`admin_audit_events` (hash-chain WORM), `customer_audit_forward_config`, `account_enforcement`, `deployment_anomalies`, `customer_access_denylist`
Riesgo / 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. Diccionario de datos — tablas load-bearing¶

Tabla	Columnas clave	Notas
`customer_accounts`	`id (uuid pk)`, `deployment_id`, `cell_id`, `tier`, `status`, `kyc_status`, `country_code`, `referral_code`	Raíz del tenant. `status` controla el ciclo de vida (email_pending → kyc_pending → active). Ancla de RLS (`account_id` en las tablas de tenant).
`utxos`	`id`, `account_id (fk)`, `amount`, `spent`, `created_at`	Ledger de unspent-outputs de tokens (ADR-0099). Aplicado con RLS; solo eventos de mint/spend mutan.
`utxo_spend_events`	`id`, `account_id`, `amount`, `test_id`, `intensity`, `spent_at`, `refunded_at`, `refund_reason`	Log de spend inmutable; los refunds se registran, nunca se borran.
`admin_audit_events`	`id`, `seq`, `prev_hash`, `hash`, `actor`, `action`, `payload`, `created_at`	WORM: un trigger en el DB bloquea UPDATE + DELETE temprano; hash chain SHA-256; export RFC 5424.
`stripe_webhook_events`	`id (stripe evt id, pk)`, `event_type`, `outcome`, `stripe_created_at`, `livemode`	Idempotencia + retry: un `outcome` no terminal (NULL/`failed`) se reprocesa.
`identity_webhook_events`	`event_id (pk)`, `event_type`, `account_id`, `session_id`, `outcome`	Mismo contrato de idempotencia/retry para Stripe Identity (KYC).
`licenses`	`id`, `account_id`, `deployment_id`, `tier`, `expires_at`, `signed_blob`	Licencia on-prem; los heartbeats caen en `license_heartbeats`.

4. Row-Level Security¶

Las tablas con scope de tenant (utxos, utxo_mint_events, utxo_spend_events, usage_tickets, usage_events, api_keys, webhook_endpoints, outbound_events, tsu_budgets) llevan una política RLS tenant_isolation (migrations 0026/0029/0034/0041). Las rutas de tenant corren dentro de withTenant() (setea app.account_id); los caminos cross-tenant de sistema (metrics, crons, admin) lo dejan sin setear y pasan — ver el guard test de rutas de tenant. El cutover completo a default-deny (dual DB roles) se rastrea por separado.

5. Regenerando este documento¶

El agrupamiento de dominios y la lista de tablas derivan del schema.ts; cuando agregue una pgTable, agréguela al dominio correcto en la §1 y, si es load-bearing, a la §3. Una mejora futura puede auto-generar el ERD desde los metadatos de Drizzle.