MULTI-TENANT SAAS SCHEMA DESIGN (SQL, row-level security)

FUNDAMENTAL DECISIONS:
SQL (PostgreSQL) chosen over NoSQL because: ACID transactions needed for billing accuracy, relational queries (cross-customer analytics), strong data integrity. Row-level security via tenant_id column + RLS policy = security at DB layer.

CORE TABLES:

table tenants (id, name, plan_tier, created_at)
Multi-tenant anchor. One row = one customer account.

table users (id, tenant_id, email, password_hash, role, created_at)
Index: (tenant_id, email). RLS policy: users can only access rows where tenant_id matches their session.

table events (id, tenant_id, user_id, event_type, properties_jsonb, created_at)
Large table (scale target: 100M+ rows). Partitioning by tenant_id or created_at (monthly). Index: (tenant_id, created_at DESC) for fast "events for tenant in last 30 days" queries.

table subscriptions (id, tenant_id, plan_tier, status, billing_cycle, renewal_date, amount_cents, currency, created_at, updated_at)
Billing source-of-truth. Immutable insert pattern: never UPDATE, only insert new rows (soft delete old). Audit trail preserved.

RELATIONSHIP DIAGRAM (simplified):
tenants 1:many users
tenants 1:many subscriptions
tenants 1:many events
users 1:many events

PARTITIONING STRATEGY (scale plan):
At 10M events/month (120M/year), partition events table by created_at (monthly partitions). This: 1) keeps index scan fast (only needed partition scanned), 2) enables easy archival (drop old partitions to cold storage).

MIGRATION PLAN:
V1 (week 1): Create schema, populate tenants/users/subscriptions from production. Keep old system live in parallel (reads only).
V2 (week 2): Dual-write new events table while legacy system still active. Verify parity.
V3 (week 3): Cutover: switch application to new schema, backfill historical events if needed, decommission legacy.

BACKUP/RECOVERY:
Daily automated backups. Point-in-time recovery enabled (WAL archiving). Tenant-level restoration possible (restore single tenant to test environment without affecting others).

QUERY PATTERNS OPTIMIZED:
"Show me events for tenant X in last 7 days" → Partition + index scan = 200ms (millions of total events). "Billing revenue by plan_tier last month" → denormalized summary table refreshed nightly = 50ms. "Export user data for GDPR" → tenant_id filter returns exact set = 500ms for 1M users.