Safe Database Migrations

Scenario

You maintain a production application with a large PostgreSQL database.

Schema changes are deployed independently from some application instances, so old and new application versions may temporarily run against the same database.

A migration that works on an empty development database may still be unsafe in production.

You want coding agents to consider:

• existing data;
• compatibility;
• destructive operations;
• backfills;
• deployment ordering.

Repository Structure

platform/
├── src/
├── migrations/
│   ├── 101_create_accounts.sql
│   ├── 102_add_account_status.sql
│   └── 103_add_order_reference.sql
├── scripts/
│   └── backfills/
├── tests/
└── AGENTS.md

AGENTS.md

# Project Instructions

## Migration Safety

- Create a new migration for every production schema change.
- Do not rewrite already-applied migrations.
- Review migrations for data loss, locking, and compatibility risks.
- Consider existing production data when adding constraints or changing column types.

## Compatibility

- Prefer migrations that remain compatible with the currently deployed application during rolling deployments.
- Separate destructive cleanup from the change that introduces the replacement.
- Do not drop a column or table while deployed code may still depend on it.

## Backfills

- Do not assume a new required column can be populated instantly for all existing rows.
- Use the repository's backfill workflow for large or expensive data updates.
- Keep large data backfills separate from schema migrations when the repository's deployment model requires it.

## Destructive Changes

Before introducing operations such as:

- `DROP TABLE`;
- `DROP COLUMN`;
- destructive type conversions;
- large rewrites;

confirm that the old data or interface is no longer required.

## Validation

For migration changes:

- test the migration against representative existing data;
- test application behavior before and after the transition where practical;
- inspect the migration for destructive operations;
- validate rollback or forward-recovery expectations according to repository policy.

What This Does

This changes the agent's mental model from:

Edit schema
→ migration works
→ done

to:

Current production data
        ↓
Old application version
        ↓
Migration
        ↓
New application version
        ↓
Cleanup later

Production migrations are transitions between states.

They should not be treated only as definitions of the final state.

What This Does NOT Do

The instructions do not prohibit destructive migrations forever.

Eventually, obsolete columns and tables should often be removed.

The concern is when.

For example, suppose:

users.full_name

is being replaced by:

users.first_name
users.last_name

Dropping full_name in the first migration could break application instances still reading it.

A safer rollout may require several stages.

Why These Instructions Matter

Consider adding:

ALTER TABLE users
ADD COLUMN organization_id UUID NOT NULL;

On an empty database, this may look fine.

On a production table containing millions of users, existing rows do not yet have an organization_id.

The change may fail or require a potentially expensive operation.

A safer strategy might conceptually be:

1. Add nullable column
2. Deploy code that supports it
3. Backfill existing rows
4. Verify data
5. Add required constraint
6. Remove obsolete compatibility code later

The exact strategy depends on the database and application, but the important point is that existing data changes the problem.

Key Decisions

Migrations are transitions

Think about:

before
during
after

not only the desired final schema.

Existing data matters

A constraint that works for new rows may fail for millions of existing rows.

Compatibility matters during deployment

Application and database versions may overlap.

Schema changes should account for that deployment model.

Separate expansion from cleanup

A common safe pattern is:

expand
→ migrate usage/data
→ verify
→ contract

The destructive cleanup happens only after the old path is no longer needed.

Large backfills deserve their own workflow

Updating millions of rows inside a schema migration can create long-running locks or deployment failures.

Use repository-specific operational tooling when available.

When to Use This Pattern

Use this pattern when:

• databases contain important production data;
• deployments can have multiple application versions running;
• tables are large;
• schema changes may require backfills;
• downtime or data loss would be costly.

The more important the data, the more migration instructions should focus on transitions rather than only schema syntax.