coder-system-design-db-schema

name	coder-system-design-db-schema
description	Database schema design and migration safety rules for production systems.

<when_to_use> 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>

<input_requirements> 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>

<design_principles> 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>

<decision_points> 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_safety_checklist> 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>

<quality_rules> 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> 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>

<output_requirements> 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