That VARCHAR(255) Was Never a Decision. It Was a Default.

#sql #postgres #programming #database

A migration file is permanent. Once it runs against production, you cannot take it back without writing another migration.

Yet most teams write migrations the way they write application code: open an editor, start typing columns, figure out constraints as they go. The result is schemas full of implicit decisions nobody documented — default values nobody questioned, nullability choices that haunt the codebase for years.

Writing a 30-minute schema spec before the migration forces those decisions into the open, where they can be reviewed, challenged, and agreed upon.

Why schemas deserve specs more than code does

Application code gets rewritten. Frameworks get swapped. The database schema outlives almost everything else in the stack. A column added in 2019 is still there in 2026 unless someone explicitly removed it — and removing columns from a production database is one of the riskiest operations a team can perform.

That longevity makes schema decisions high-leverage. And yet most of them are made implicitly:

An engineer types status VARCHAR(255) and moves on. Nobody asks why it's a varchar instead of a smallint. Nobody asks whether 255 is the right length or just the ORM default. Nobody asks what values are valid. The migration passes review because reviewers focus on application logic, not on whether placed_at should be TIMESTAMP or TIMESTAMPTZ.

A year later: status has 14 distinct string values in production, three of which are typos. placed_at stored times without timezone info, and the billing reconciliation job has been silently miscalculating for months.

Each bug traces back to a decision that was never made — just defaulted into.

The schema spec template

A schema spec is not a design document. It's a concrete description of the table you're about to create, detailed enough that a reviewer can evaluate every column, constraint, and index before a single line of SQL exists:

## Schema Spec: orders

### Table: orders
Description: Stores customer purchase orders. One row per checkout.

| Column      | Type        | Nullable | Default           | Constraints              |
|-------------|-------------|----------|-------------------|--------------------------|
| id          | uuid        | NO       | gen_random_uuid() | PRIMARY KEY              |
| user_id     | uuid        | NO       | —                 | FK -> users(id)          |
| total_cents | bigint      | NO       | —                 | CHECK (total_cents >= 0) |
| currency    | char(3)     | NO       | 'USD'             | CHECK (currency IN ('USD','EUR','GBP')) |
| status      | smallint    | NO       | 0                 | CHECK (status IN (0,1,2,3)) |
| placed_at   | timestamptz | NO       | now()             | —                        |
| canceled_at | timestamptz | YES      | NULL              | —                        |

### Status mapping
0 = pending, 1 = confirmed, 2 = shipped, 3 = canceled

### Indexes
- idx_orders_user_id ON (user_id) — supports lookup by customer
- idx_orders_placed_at ON (placed_at DESC) — supports dashboard sorting
- idx_orders_status ON (status) WHERE status IN (0, 1) — partial index for active orders

### Foreign keys
- user_id -> users(id) ON DELETE RESTRICT

### Migration order
Must run after: 001_create_users

Three things this format buys you:

Every column-level decision is visible — total_cents is a bigint with a non-negative check, not a decimal or a float, and a reviewer can see that.
Indexes surface before the migration is written, so the team discusses query patterns at review time instead of discovering missing indexes in production.
Migration ordering is documented, so dependency failures happen on paper instead of in staging.

Every column is four decisions

Nullability. The database default is nullable — if you don't say NOT NULL, the column accepts nulls. This is almost always wrong for new columns. A null email means a user without an email: is that a valid state? The spec forces an answer for every column. Write YES or NO. Don't leave it blank.

Defaults. A default isn't a convenience — it defines what happens to existing rows when you add a column to a populated table. Add currency CHAR(3) NOT NULL without a default and the migration fails on any non-empty table. And the default itself is a business decision: 'USD' is fine if all existing customers are US-based, wrong if you have customers in Europe.

Check constraints. CHECK (total_cents >= 0) means the database rejects negative order totals regardless of what the application does. It's your last line of defense against bad data — and every check constraint is a business rule that belongs in review. If the app later needs a status 4, the team knows it's a schema change, not just a code change.

Foreign keys. ON DELETE CASCADE means deleting a user deletes all their orders. RESTRICT means you can't delete a user who has orders. SET NULL keeps the order but orphans it. Each option is a different failure mode. Decide in the spec — not in whoever-writes-the-migration's head.

Dependency ordering is a design decision, not a framework detail

A feature adds subscriptions, subscription_items, and billing_events:

subscriptions references users (exists)
subscription_items references subscriptions and products (being created in this same branch)
billing_events references subscriptions

So the real order is: products → subscriptions → subscription_items → billing_events. Your migration framework probably handles ordering mechanically — but the dependency chain is a design fact reviewers need to see. Without the spec, the engineer discovers it when staging fails, fixes it by trial and error, and nobody learns anything.

The spec should also note rollback ordering: if migration 003 depends on 002, rolling back 002 while 003 is applied breaks the FK. That note matters most during incident response, when nobody has time to trace dependency chains manually.

The 30-minute habit

Before your next migration, write the table as a spec: columns with explicit nullability and defaults, every check constraint, every index with the query it supports, FK delete behavior, and migration order.

Put it in the PR description or a specs/ directory. Let someone reject it before it becomes permanent.

Thirty minutes of writing. Weeks of debugging saved. The math has never once failed me.

I write about spec-first delivery, API contracts, and release engineering at spec-coding.dev. The full guide — including zero-downtime backward-compatible migration patterns — is at spec-coding.dev/blog/spec-database-schemas-before-writing-migrations, and there's a free DB spec generator that outputs this exact template.