# 19.6 Changing the tables safely (/building-ai-workers/business-data-and-retrieval/changing-the-tables-safely)

---
type: Document
title: "19.6 Changing the tables safely"
description: "Numbered migrations, applied once and in order, one database per environment, a database branch per pull request, and add before you remove."
status: stable
order: 319.06
ksor:
  owner: team:panaversity
  audience: [ public ]
  approval:
    by: process:panaversity
    at: 2026-11-04T22:13:33Z
chapter: "19"
part: III
expert_status: co-build
concepts: [ "19.6" ]
objectives:
  - { id: CCDV-F.D2.S2, label: supporting }
last_verified: 2026-11-02
generated:
  at: 2026-11-04T22:13:33Z
  by: process:panaversity
trust_tier: unverified
build_id: sha256:e1fbcb7d9a8f8b9b7cb96875022325c02c9ba4be94cef13b301cbd39a9edad85
dirty: true
ksor_version: 0.0.60
---

**In everyday life.** A city changes a road in steps. It builds the new lane while the old one stays open, moves the traffic, and only then closes the old lane. Each step is planned on paper, tried at night, and recorded, so anyone can see what changed and when.

**What you will learn.** How the tables change safely: numbered migrations, applied once and in order, one database per environment, and a fresh copy of the database for every pull request.

**Why it matters.** A table changed by hand cannot be repeated, reviewed or undone, and the running connector reads the tables while they change. A numbered change, tried on a copy first, reaches real data last.

**One request, start to finish.** Ana wants each invoice's currency recorded, because Brightline has started buying from a Canadian vendor. The change reaches four databases, one after another:

1. **The builder's `dev` copy.** Claude Code writes a new numbered SQL file, `0005_invoice_currency.sql`, that adds a `currency` column, filled with `USD` for every existing row. It runs it on `dev`, and the tests pass.
2. **The pull request's own copy.** When the pull request opens, the build copies the staging database, which does not have 0005 yet, applies 0005 to the copy, and runs every test against it.
3. **The staging database,** after the merge, through the deploy workflow.
4. **The production database,** only when a person runs it.

At no point does anyone change a table by hand.

### What is a migration?

A **migration** is a numbered SQL file that changes the schema, kept in the repository like code. The lab's runner applies the files in order, each in its own transaction (a group of statements that succeed or fail together, [19.9](one-company-per-transaction.md)), so a failing file changes nothing. It records each one in a table called `schema_migrations`, with the file's SHA-256 fingerprint. Run it again and it applies nothing new. Change a file after it ran, and the runner refuses it.

### Why does each environment get its own database?

[Chapter 17](../connectors-and-mcp/overview.md) gave the connector environments: your computer, staging and production. The database follows the same rule. Each environment has its own database, so a change is tried on copies before it reaches real data. A **database branch** makes the extra copies cheap: a copy of a database made in seconds, which shares the original's stored data until something changes, so it costs almost nothing until it is used. The lab makes one for each pull request and deletes it when the pull request closes.

### How do you change a table that is in use?

Add, then switch, then remove. A running connector reads the tables while a migration changes them. So a change that removes or renames something is split into steps, as the city does: first add the new column (old code ignores it), then change the code to use it, and only in a later migration remove the old one. Every migration in this chapter adds. None removes.

**At work.** In [Lab 19](lab.md), Task 2, Claude Code runs `npm run db:migrate` twice on `dev`. The first time: `dev: 2 applied, 0 already applied, 2 in all`. The second: `dev: 0 applied, 2 already applied, 2 in all`. The builder then asks it to edit a comment in `0002_tables.sql` and run again, and the runner refuses: `stopped: 0002_tables.sql has changed since it ran on dev. Never edit a migration that ran. Add a new one.` The agent reverts the edit.

**The operating rule:** change the schema only through numbered migrations, applied by a runner, tried on a copy first. Never edit one that ran. Add first, remove later.

![One migration file, 0005_invoice_currency, traveling left to right through four databases: dev, the pull request's own branch (a copy of staging), staging and production. Under each, who applies it: the coding agent on dev, the build on the pull request's branch, the deploy workflow on staging, a person on production. Each database has its own schema_migrations list, gaining the line 0005 when it arrives. The pull request's branch is drawn as a short-lived copy that disappears after the merge.](img/chapter-19-migration-path.png)

*Figure 19.12. One migration, four databases, always in the same order.*