| name | database-patterns |
| description | Database migration and schema change standards for any project using raw SQL migrations. Activate whenever a task involves schema changes, column renames, field additions, table creation, data backfills, index creation, or any modification to how data is stored. Also activate when backend TypeScript interfaces need to reflect a new database shape.
|
| user-invocable | false |
| disable-model-invocation | true |
| last_reviewed | "2026-04-28T00:00:00.000Z" |
Database Patterns
This guides how you handle any database schema change. The patterns assume
PostgreSQL with raw SQL migrations — no ORM. Adapt file paths to the target
project's conventions.
Migration-First Principles
Migration-first principles are defined in architectural-principles. This skill
covers the mechanics: file naming, templates, schema change patterns, and the
full-stack checklist.
Migration File Conventions
File Naming
backend/src/db/migrate-{descriptive-slug}.ts
The slug describes what the migration does in kebab-case: migrate-add-priority.ts,
migrate-soft-delete.ts, migrate-rename-description.ts.
File Structure
Every migration file follows this template:
import { pool } from './pool.js';
const migration = `
-- Comment explaining what this migration does
<SQL statements here>
`;
async function migrate() {
console.log('Running <descriptive name> migration...');
try {
await pool.query(migration);
console.log('<Descriptive name> migration completed successfully');
} catch (error) {
console.error('Migration failed:', error);
throw error;
} finally {
await pool.end();
}
}
migrate();
Import pool from ./pool.js (ESM). SQL in a template literal. Always call
pool.end() in the finally block.
npm Script Registration
{
"db:migrate:<slug>": "node dist/db/migrate-<slug>.js",
"db:migrate:<slug>:dev": "tsx src/db/migrate-<slug>.ts"
}
Schema Change Type Catalog
- Add column —
ADD COLUMN IF NOT EXISTS with DEFAULT or NULL; fails
on non-empty tables without one
- Add with backfill — combine
ALTER TABLE + UPDATE in one migration;
succeed or fail as a unit
- New table —
CREATE TABLE IF NOT EXISTS; index every foreign key and
frequent-lookup column
- Rename column —
RENAME COLUMN is not idempotent; wrap in
DO $$ ... END $$ with information_schema.columns existence check
- Transform data — wrap
ALTER TABLE + UPDATE in DO $$ ... END $$
so add and transform are atomic
- Soft delete — add
deleted_at TIMESTAMP with index; update all queries
for the table to filter WHERE deleted_at IS NULL (both steps required)
Full-Stack Checklist for Schema Changes
- Migration file —
backend/src/db/migrate-<slug>.ts — the authoritative change
- npm scripts — both
db:migrate:<slug> and db:migrate:<slug>:dev
- TypeScript model interface —
backend/src/models/{entity}.ts — interface and CreateInput type
- SQL queries in model — every
SELECT, INSERT, UPDATE referencing the old column name
- Zod validation schema —
backend/src/routes/{entity}.ts — the z.object({...}) for request bodies
- Allowed fields list — the
allowedFields array in update functions (mass-assignment guard)
- Seed data —
backend/src/db/seed.ts if the field is seeded
- Frontend API types —
frontend/src/api/client.ts — TypeScript types for API data
- Frontend components — every component that reads or writes the field
- E2E tests — page objects, fixture data, and test specs referencing the old field name
- Base schema DDL —
CREATE TABLE statements used for fresh installs
Rollback Strategy
Document a reverse operation for every migration. Column rename rollback: the
inverse RENAME COLUMN. Column addition rollback: DROP COLUMN IF EXISTS.
Destructive migrations may require backup-and-restore — document that in the
plan. Include rollback SQL as a comment in the migration file.
Detailed Examples
For concrete SQL and TypeScript examples using a to-do list application domain,
see references/migration-examples.md. Use
the Read tool to load this file — it is not auto-loaded with the skill.