DB Spec Template

Use this schema template to reduce migration risk and make data changes explicit across the team. It is optimized for migration safety, compatibility windows, and rollback clarity.

db-spec-template.md

# DB Spec Template

## Table
- Name:
- Domain / owner:

## Columns
- id BIGINT PK
- field_name: type / nullable / default / notes

## Constraints
- PRIMARY KEY (...)
- UNIQUE (...)
- FOREIGN KEY (...)

## Indexes
- idx_xxx (col1, col2)
- expected query pattern:

## Migration Plan
- deploy steps:
- backfill plan:
- rollback plan:

## Compatibility
- read/write compatibility window:
- old clients impact:

## Test Checklist
- [ ] migration up/down
- [ ] constraint behavior
- [ ] index performance
- [ ] data integrity checks

When a DB spec is mandatory

Any schema change touching primary business tables.
Migrations with backfill, data rewrite, or lock risk.
Index changes that affect high-traffic query paths.
Changes requiring compatibility with old clients or jobs.

How to avoid migration incidents

Declare migration stages: deploy, backfill, verify, cutover, cleanup.
Estimate affected rows and expected run time for each stage.
Specify rollback trigger conditions and exact rollback commands.
Document read/write compatibility window for old and new code.

Small operational details in the spec often prevent large production outages.

High-risk mistakes

Adding non-null columns without default/backfill strategy.
Dropping indexes before confirming replacement query plans.
Running long blocking DDL in peak traffic windows.
Assuming rollback is possible without validating down migration.

Operational review checklist

Migration tested on realistic data volume.
Constraint/index behavior validated with representative queries.
Monitoring and alerts in place during rollout window.
Rollback runbook tested and approved by owner.

Related guides: edge-case checklist and spec review checklist.

Real-world example: adding a JSONB preferences column

## Table: users
## Change: Add notification_preferences column

### Schema
| Column                    | Type   | Nullable | Default |
|--------------------------|--------|----------|---------|
| notification_preferences | JSONB  | yes      | NULL    |

### Constraints
- No NOT NULL (existing rows have no prefs → null = "use defaults")
- CHECK: jsonb_typeof(notification_preferences) = 'object'
  OR notification_preferences IS NULL

### Indexes
- GIN index on notification_preferences for @> queries
- Estimated table size: 2.4M rows, ~180 bytes per JSONB value

### Migration Plan
- UP: ALTER TABLE users ADD COLUMN notification_preferences JSONB;
- This is a metadata-only change on Postgres 11+ (no rewrite)
- Estimated lock duration: < 1 second
- No backfill needed (null = use defaults in application code)

### Rollback
- DOWN: ALTER TABLE users DROP COLUMN notification_preferences;
- Application code must handle missing column gracefully
- Rollback window: 72 hours before dependent API ships

### Monitoring
- Alert if column add takes > 5 seconds (indicates lock contention)
- Watch for query plan changes on users table after GIN index creation

Editorial note

This template covers database specification for spec-first engineering teams. The JSONB migration example is an illustrative scenario based on PostgreSQL.

Author: Daniel Marsh
Editorial policy: How we review and update content
Corrections: Contact the editor

Tip: include SQL examples, estimated row impact, and lock expectations for risky migrations. Last updated: March 25, 2026.