Capstone: Shard a Multi-Tenant SaaS Database

• Shard key: workspace_id — almost every query and transaction is workspace-scoped.
• Sharded (co-located by workspace_id): projects, tasks, comments, attachments, memberships.
• Global (small, cross-workspace): users, workspaces directory, billing accounts — in a separate unsharded database (replicated/cached for reads).
• Add workspace_id to every sharded table and to its primary/unique keys and foreign keys before migrating.

Look for: shard key aligned with access patterns, co-location of related tables, a clear global/sharded split, and schema prep. Misses: sharding by user_id or task_id.

• Many logical shards (e.g. 1,024) via hash(workspace_id), mapped to fewer physical clusters (e.g. 16) in a routing table — rebalancing moves logical shards.
• Directory overrides for the five whales: each pinned to dedicated clusters; if one outgrows a cluster, sub-shard its biggest table by project_id.
• Routing table cached in every service, with a version number so changes propagate safely.

Look for: logical-vs-physical separation, directory overrides for outliers, cached routing with safe updates. Misses: modulo over physical databases, ignoring the whales.

1. Prep: add workspace_id everywhere, remove cross-workspace joins/transactions from code, route all queries through a shard-aware data layer (still pointing at the one database).
2. Logical sharding first (e.g. per-shard schemas or views on the same server) to prove routing.
3. Per logical shard batch: replicate (logical replication or audited double-writes) → backfill → verify (row counts, checksums, shadow reads) → switch reads → brief write pause → switch writes. Keep the source copy for rollback.
4. Move small shards first, whales last with dedicated rehearsals.

Look for: preparation in the single DB, logical before physical, the double-write/backfill/verify/switch recipe per small unit, rollback ability. Misses: big-bang cutover.

• "My tasks across workspaces": a per-user read model fed by CDC from all shards (or parallel scatter-gather limited to the user's few workspaces).
• Billing/reporting: an analytics warehouse fed by CDC; never scatter-gather on the hot path.
• Admin search: a search index fed by CDC.
• Moves of a workspace between shards use a saga with idempotent steps.

Look for: CDC-fed read models and warehouses for cross-shard reads, bounded scatter-gather, sagas for moves. Misses: cross-shard joins in OLTP.

• Data divergence during double-write → continuous verification and diff alerts per logical shard.
• Queries missing workspace_id → enforced by the data layer (reject unscoped queries) and logged fan-outs.
• Hot shards → per-shard load dashboards and the ability to move logical shards.
• Routing mistakes → canary workspaces, fast routing rollback, and per-shard error rates.

Look for: concrete detection for divergence, unscoped queries, hot shards and routing errors, plus rollback. Misses: 'test it well' without mechanisms.