Capstone 2: Operate a Secure Multi-Tenant SaaS Database
Turn SignalDesk from a correct schema into an operable multi-tenant SaaS database. Implement least-privilege roles, row-level security, typed application access, connection budgets, zero-downtime migration, indexes from plans, observability, backup and restore, replica consistency policy, and incident runbooks. Pass concurrent, adversarial, saturation, recovery, and failover evidence gates.
What will you be able to do?
- Defend tenant isolation across every runtime path.
- Tune from plans and workload evidence.
- Ship a backward-compatible migration under load.
- Demonstrate recovery and degraded-mode operations.
Prerequisites and mental model
Complete Parts 6, 7, and 9 plus Capstone 1. Assume hostile inputs, retries, partial failures, rolling deploys, and operator mistakes.
Required deliverables
- owner/migrator/API/worker/read-only/admin role graph and allow/deny matrix;
- RLS on tenant relations with transaction-local context and real-role tests;
- Python or TypeScript service implementing ticket create/read/resolve with pooling/timeouts;
- connection budget for normal, peak, deploy, and failover;
- representative dataset, slow-query inventory, plans, and justified index changes;
- expand/backfill/validate/switch/contract migration rehearsal;
- SLO dashboard specification and redacted trace/log examples;
- logical restore plus physical/PITR runbook/evidence appropriate to environment;
- replica lag/read-consistency rules and failover game day;
- incident response for WAL disk pressure, connection exhaustion, and bad migration.
Failure drills
Inject: missing tenant context, stale pooled context, deadlock, serialization failure, pool exhaustion, statement timeout, failed concurrent index, paused replica/slot growth, restore missing extension, and app restart during migration. Record detection, containment, customer effect, recovery, and prevention.
Why does this matter in AI-native systems?
AI tools will inherit these boundaries. If the database cannot survive retry, stale state, tenant attacks, and restore before AI is added, vector retrieval and agents only increase blast radius.
Prove it worked: rubric
Common mistakes
- RLS tests as owner: false confidence → execute actual roles.
- HA without restore: availability confused with recovery → test both.
- Index benchmark on tiny uniform data: no production relevance → preserve scale/skew/concurrency.
AI Pair-Programmer Prompt
Hands-on exercise
Run one 60-minute game day combining connection exhaustion and replica lag during a rolling migration.
Acceptance criterion: SLO impact is detected, migration pauses safely, primary remains protected, clients degrade predictably, and the timeline yields concrete fixes.
Knowledge check
- Why test RLS through API and direct SQL?
- What must an index change include besides a faster plan?
- What proves recovery?
Answers
- Different paths may set context or permissions differently.
- Write/storage/WAL cost, rollout, monitoring, and rollback/removal.
- An isolated validated restore that meets RPO/RTO and application/security checks.
Completion checklist
Primary references