coder-system-design-db-schema

Designing relational schema for new services or major feature changes Planning schema evolution and data migrations in production Reviewing index/constraint strategy and multi-tenant data isolation </when_to_use> Core entities and relationships Read/write access patterns and query shapes Data retention, audit, and compliance constraints Deployment constraints (downtime, lock tolerance, rollback) </input_requirements> Start normalized; denormalize only for measured bottlenecks Enforce integrity in database using PK/FK/unique/check constraints Design indexes from real query predicates and sort patterns Use compatibility-first schema evolution via expand and contract Treat tenant isolation as explicit schema and policy decision Separate audit history needs from soft-delete convenience </design_principles> Normalization vs denormalization based on read latency and write amplification tradeoff Tenant model: database-per-tenant vs schema-per-tenant vs shared-schema with tenant_id Deletion model: hard delete vs soft delete vs temporal/audit tables Key strategy: surrogate vs natural keys with interoperability constraints </decision_points> Migration is split into expand, backfill, switch, and contract phases Lock impact and long-running DDL risk are analyzed before rollout Online index strategy is used where supported Backfill is batched, idempotent, and observable Rollback or roll-forward path is explicitly documented Post-migration validation queries are defined before deploy </migration_safety_checklist> Do not perform breaking schema changes without compatibility window Do not rely on app-level validation for integrity-critical constraints only Do not ship index changes without query-path rationale Do not run unbounded data backfill during peak load without controls </quality_rules> Do not rename/drop hot-path columns and tables in same release as app switch Do not add broad indexes "just in case" Do not treat soft delete as complete audit solution </do_not> Schema proposal with constraints and index rationale Phased migration plan with safety controls Verification SQL and rollback strategy Risks and operational caveats </output_requirements> PostgreSQL ALTER TABLE PostgreSQL Explicit Locking PostgreSQL CREATE INDEX PostgreSQL Multicolumn Indexes PostgreSQL Partial Indexes PostgreSQL Row Level Security Azure SQL SaaS Tenancy Patterns Evolutionary Database Design