coder-system-design-db-schema - SKILL.md Agent Skill

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

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 Core entities and relationships Read/write access patterns and query shapes Data retention, audit, and compliance constraints Deployment constraints (downtime, lock tolerance, rollback) 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 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 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 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 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 Schema proposal with constraints and index rationale Phased migration plan with safety controls Verification SQL and rollback strategy Risks and operational caveats 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