Entry/Exit
- Entry: Need to add/modify database tables, write migrations, design indexes, optimize slow queries, handle data constraints, or formulate data repair plans
- Exit: Rollback-capable migration scripts + verified queries + data integrity constraints + migration risk assessment
- Points to: After completion, return to
build-workflow-executeto continue to the next slice - Preload: CANON.md
- Output path: After completion, return to
build-workflow-executeto continue to the next slice - Supplementary reference:
references/database-examples.md(detailed examples, complete wording tables, zero-downtime cases, transaction isolation levels, data repair templates)
When not to use
- Only changing API-layer field naming or request validation, without involving persistence structure
- Pure frontend state, caching, or display logic issues
- Database behavior is already clear, and you are only implementing business code
- ORM usage issues (connection pool configuration, transaction error troubleshooting) not involving schema/query design
Core anchors
Data Integrity Constraints
Database constraints are the type system for data. Application validation is supplementary; database constraints are the last line of defense.
Execution rules:
- Primary keys, foreign keys, NOT NULL, UNIQUE, CHECK, DEFAULT should be declared in DDL first
- Do not rely on the application layer as the sole guarantee of data integrity
- When direct SQL updates, data repairs, or ETL bypass the application layer, database constraints are the last safety net
Migration as Code
All schema changes must go into migration files; manual production changes without records are not allowed.
Execution rules:
- Every migration must have a rollback plan; if DOWN can be written, it must be written; irreversible migrations must first be deprecated, backed up, and provided with a compensation plan
- Migrations must be verified in staging
- Specific DDL, lock behavior, and safe index operations are subject to the current database engine; when unsure, consult the database documentation first
Expand-Contract Migration
Production migrations default to expand → migrate/backfill → contract. Do not complete destructive schema changes all at once.
Execution rules:
- Adding a NOT NULL column: first add a nullable column → deploy compatible code → backfill → add constraint
- Dropping a column: first stop reading → deploy code that does not read the old column → drop after one release
- Renaming: first add the new column → dual-write → migrate reads → stop writing to the old column → drop the old column
Online Schema Change
ALTER on large tables, backfills, and index creation must assess lock-table risk, duration, and batching strategy.
Execution rules:
- Large-table backfills must be executed in batches, be pausable/resumable, have progress records, and avoid long transactions
- Large-table index creation should assess concurrent index creation capability (e.g., PostgreSQL
CONCURRENTLY) - Full-table rewrite operations must assess the downtime window
EXPLAIN-first Query Design
After writing a complex query, immediately validate the execution plan with EXPLAIN / EXPLAIN ANALYZE.
Execution rules:
- Large table (>10k rows) + highly selective query + Seq Scan → index needed
- Seq Scan on small tables or low-selectivity queries is a reasonable optimizer choice, not a red flag
- Estimated row count differs greatly from actual → run
ANALYZEto update statistics
Query Pattern–Driven Indexing
Create indexes based on WHERE, JOIN, ORDER BY patterns, not blindly by column.
Execution rules:
- Composite index column order: equality filter columns → range filter columns → sort columns (Leftmost Prefix Rule)
- Avoid wrapping indexed columns in functions, implicit type conversions, or leading wildcards (SARGability)
- Evaluate write cost: the number of indexes on write-heavy tables requires balancing query benefit against write amplification
N+1 Query Smell
findMany() / list() then querying the database again inside the loop = N+1.
Execution rules:
- N+1 detected → switch to JOIN / include / batch queries
- Any database call inside a loop body must be proven not to be N+1
Transaction Boundary
Multi-table consistency writes must define transaction boundaries.
Execution rules:
- Calling external APIs inside a transaction is prohibited (slow/failed external calls → long-held transaction locks → connection leaks)
- Long tasks should not occupy database transactions; first write a "pending" status, execute asynchronously, and update upon completion
- Concurrent state updates (inventory, balance, task claiming) must evaluate optimistic locking / row lock
- Default to Read Committed; only raise the isolation level when business rules require snapshot consistency or prevention of concurrency anomalies
Data Repair Playbook
Production data repair is not seed, nor is it an ordinary migration. The three are not mixed: Migration changes schema (ordered, requires rollback plan); Seed fills development data (idempotent, repeatable); Data Repair fixes production data.
Execution rules:
- Must have dry-run mode + backup and recovery plan + batched execution + audit log + post-repair verification query
Schema Design Conventions
Project conventions (team-wide consistency is sufficient, not absolute standards): table names plural snake_case, column names snake_case, primary key UUID or BIGSERIAL, time TIMESTAMPTZ(do not use bare TIMESTAMP), boolean prefix is_/has_, soft delete preferred deleted_at. Short examples see references/database-examples.md §1-2。
Migration Risk Classification
| Level | Scenario | Requirements |
|---|---|---|
| Low | Nullable columns, ordinary indexes, small table constraints | UP + DOWN, staging verification |
| Medium | Backfill, adding NOT NULL, changing default values | + stepwise migration + backfill strategy |
| High | DROP/rename columns, change field types, large table indexes/backfill | + expand-contract + backup + rollback verification |
| Critical | CASCADE delete, DML without WHERE, irreversible changes | + manual approval + backup and recovery drill |
Process
Step 1: Design Schema + Constraints
Checkpoint: every constraint has business semantics, Schema conventions are consistent.
Step 2: Write migration + risk classification
Checkpoint: migration has a rollback plan, risks have been classified.
Step 3: Execute migration (Medium+ uses expand-contract; large tables backfill in batches)
Checkpoint: staging verification + rollback plan have been tested.
Step 4: Create indexes + query verification
Create indexes based on query patterns → EXPLAIN ANALYZE → confirm no N+1. Checkpoint: every index has a query explanation, key query latency meets the target.
Common Excuses
| Excuse | Reality | Consequence |
|---|---|---|
| "Constraints in application code are enough" | Direct SQL updates, data fixes, and ETL all bypass the application layer. Database constraints are the last line of defense. | Data corruption risk ×10; garbage data enters the database without validation |
| "Add the column first, build the index later" | Slow queries are happening now. Building an index is low-cost. | Large table without an index queries slowly ×100-1000; adding the index requires locking the table or downtime |
| "Migrations don't need DOWN — there won't be a rollback" | When problems occur, writing a rollback script at 3 a.m. is even more painful. | With DOWN = 5-second rollback; without DOWN = 30-60 minutes of high-risk manual work |
| "Run the large table backfill all at once" | A full-table UPDATE on a large table locks rows, causes bloat, and creates long transactions. | Long transactions → connection pool exhaustion → service unavailable |
For the complete table of 8 excuses, see references/database-examples.md §7。
Red flag — STOP
.findMany()/list()there is still a database query inside the loop (N+1)- Migration file has no rollback plan
- ALTER on a large table without assessing table-lock risk
- Backfill on a large table is not batched
- Destructive migration has no backup/recovery path
- DROP COLUMN without a deprecated phase
- Column type conversion without a data compatibility check
- Multi-table consistency writes without a transaction
- Calling an external API inside a transaction
- High-concurrency state updates without concurrency control
- Manually modifying data in production — no record, no migration, nobody knows
- Migration uses
CASCADEto drop tables SELECT *in production code- UPDATE / DELETE without WHERE
- Blindly creating indexes for low-selectivity fields with no explanation of the serving query
- Production data fixes without a dry-run
Validation failure handling
| Validation item | Failure symptom | Handling method |
|---|---|---|
| Migration has no rollback plan | Only UP, no rollback | Write a DOWN or backup + compensation plan; irreversible operations require deprecated + phased steps |
| Key columns have no constraints | NOT NULL / CHECK missing | Add constraints at the DDL layer |
| Large table Seq Scan + high selectivity | EXPLAIN shows a full table scan | Add a composite index for the query pattern |
| N+1 query | Nested query inside a findMany loop | Change to include/JOIN batch queries |
| Time columns use TIMESTAMP | Stored without time zone | Change to TIMESTAMPTZ; unify data migration to UTC |
| Backfill on a large table is not batched | Single UPDATE over the entire table | Change to batched execution + recoverable strategy |
| Calling an external API inside a transaction | External call inside the transaction block | Move the external call outside the transaction; persist the intent first, execute asynchronously |
| Migration risk is not classified | All migrations treated the same | Classify by Low/Medium/High/Critical and apply the corresponding requirements |
Good and bad examples
Good — constraints first + phased migration + query-driven indexes
-- 约束和默认值一起定义
CREATE TABLE tasks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title TEXT NOT NULL CHECK (char_length(title) > 0),
status TEXT NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'completed')),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 索引覆盖查询模式: WHERE status = X AND assignee_id = Y
CREATE INDEX idx_tasks_status_assignee ON tasks(status, assignee_id);
-- 零停机: 可空列 → 回填 → 加约束(三步迁移)
Bad — unconstrained + non-rollbackable + N+1
CREATE TABLE taskAssign (id TEXT, taskId TEXT, usr TEXT);
-- 无约束、缩写列名、无主键、无外键
ALTER TABLE tasks ADD COLUMN description TEXT NOT NULL;
-- 一次性 NOT NULL → 锁表 → 现有行无值 → 迁移失败
-- 无 DOWN 脚本 → 不可回滚
SELECT * FROM tasks;
-- SELECT * + 无 WHERE + 无索引
Output template
数据库工程完成:
迁移文件: <timestamp>_<name>.up.sql + .down.sql(或备份+补偿方案)
风险级别: Low / Medium / High / Critical
大表: 是/否 | 破坏性变更: 是/否 | staging 已验证: 是/否 | rollback 已测试: 是/否
Schema 变更: [新表 +约束数 | 新列 +约束 | 外键引用]
索引: [索引名 + 服务的查询模式 + 写入成本评估]
查询: N+1 [已修复/无] | EXPLAIN [Index/Seq Scan] | 延迟 [< 50ms]
完整性: 约束 [PK/FK/NOT NULL/CHECK/UNIQUE/DEFAULT] | 时间 TIMESTAMPTZ | 软删除 [deleted_at/is_deleted]
Backfill: [需要/不需要 | 分批策略 | 可恢复](如有)
事务: [边界 | 并发控制: optimistic/row lock/none](如有)
Validation checklist
- Every table has a primary key + foreign key references
- Key columns have NOT NULL / CHECK / UNIQUE / DEFAULT
- Time columns use TIMESTAMPTZ; soft delete strategy is consistent
- Migration has a rollback plan; risks are classified
- Medium+ uses expand-contract; large table backfill is batched; destructive changes are backed up
- staging validation + rollback plan testing
- EXPLAIN is reasonable (large tables have no unexpected Seq Scan); no N+1
- Every index has a query pattern description
- Multi-table writes have transactions; no external APIs inside transactions; concurrency has a locking strategy