SuperAPI AI Directory

build-backend-database

Coding & development

View repository

Updated 10-16 09:44

About this skill

Database — Database Engineering

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-execute to continue to the next slice
  • Preload: CANON.md
  • Output path: After completion, return to build-workflow-execute to 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 ANALYZE to 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 CASCADE to 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