# Migrations

Entities describe the schema you want. A migration records each step from
one version of the database to the next. `jadpo migrate new add_priority`
compares the entities with the previous migration's snapshot and writes
the next folder:

```text
database/migrations/20261020T1400_add_priority/
  snapshot.json        generated: the whole schema at this point
  postgres.sql         generated: the difference from the previous snapshot
  changes.jadpo        only when a decision is needed
```

When a change needs a decision, `jadpo migrate new` writes `changes.jadpo`
with a line to fill in for each one, such as `Todo.priority: {}`. `jadpo
check` names what is missing until it is written, and running `jadpo
migrate new` again finishes the migration, writing the decisions into its
SQL. Here a required `priority` is added, so existing rows need a value,
and the legacy flag is retired:

database/migrations/20261020T1401_add_priority/changes.jadpo:

```jadpo
// changes.jadpo
/// Adds a priority to every todo and retires the legacy flag.
migration {
    Todo.priority: {
        backfill: Priority.normal  // existing rows need a value
    }
    Todo.legacy_flag: FieldChange.retired
}
```

- **Forgetting is caught at once.** `jadpo check` fails while the entities
  and the latest snapshot disagree, naming what changed and the command to
  run, so the editor, the build and CI all stop. Loose files, with no
  `app.jadpo`, have no migrations, so the check is a project's.
- **Decisions only where needed:** a value for existing rows of a
  required field, or retiring a field or an entity.
- **Removing data takes two migrations**, and a person
  ([Removing data](/docs/migrations.md#removing-data)).
- **Renames keep their data.** The identity registry, a file Jadpo keeps
  under `database/`, records what each renamed thing was born as
  ([Review and records](/docs/records-and-manifest.md)). Tables and columns
  keep the names they were born with, so renaming a field keeps its
  column. A field renamed by hand, without the registry, reads as one
  removed and one added, and the removed one's decision says so.
- **Field order is no part of the schema.** PostgreSQL can't move a
  column, so each column keeps its place, a new field's column goes last
  wherever the field is written, and fields written in another order are
  nothing to migrate.
- **Backfills can be worked out** from each row's own fields, for a new
  field that reshapes data the entity already holds
  ([Computed backfills](/docs/computed-backfills.md)).
- **`jadpo migrate apply`** runs pending migrations as a deploy step, each
  in a transaction, one per store, and records each in the store's
  `jadpo_migrations` table. It refuses a database that applied a
  migration the project doesn't have, or one that changed since. `jadpo
  migrate status` lists the migrations and which a database has applied.
- **A native deployable checks its database.** A native deployable built
  by `jadpo build` refuses to start against a database that hasn't applied
  every migration it was built with, or that applied one it doesn't have
  or that changed since, naming the store and the migrations
  ([A native deployable](/docs/a-native-deployable.md)).
- **The test database.** `jadpo test` gives each action test its own copy
  of a template built from the migrations, on the server
  `JADPO_TEST_DATABASE_URL` names or a disposable one, with copies
  prepared in the background. `--keep-failed` keeps a failed test's copy.

A migration's `postgres.sql` holds a section per store, each opened by a
`-- store: <name>` line. It names every table, column, constraint and
index as the snapshot does: a table after its entity's born name in
`lower_snake_case` (`Todo` is `todo`), a column after its field, and keys
and indexes `<table>_<name>_key`, `_fkey` and `_idx`, cut short with a
digest past PostgreSQL's 63 bytes. Each store that holds an entity also
gets the runtime's [system tables](/docs/system-tables.md), prefixed `jadpo_`.

Not built yet: `jadpo rename`, which writes the identity registry
([Tools](/docs/tooling.md)), and changing a column's type other than
widening it. `jadpo migrate new` says so for each.

## Removing data

Nothing a migration removes goes without a person, and nothing goes at
the first step. When a field or an entity leaves the source, it is
retired: code stops using it, and its column or table stays with its
data, for servers still running the old build. Dropping it for good is a
later migration of its own, which `jadpo migrate new` writes only when
you name what goes:

```sh
jadpo migrate new drop_legacy_flag --drop Todo.legacy_flag
```

```text
  compared the entities with 20261020T1401_retire_legacy_flag

  Todo
    - legacy_flag  boolean  dropped: the column and its data go for good

Wrote database/migrations/20261020T1402_drop_legacy_flag/
    snapshot.json  the whole schema at this point
    postgres.sql   1 statement: drop column
    changes.jadpo  every decision made

decision: 1 change waits for a person's acceptance
  next: A person accepts it: `jadpo acceptance new <purpose>` writes the draft for them. (human)
```

Its `changes.jadpo` says the same, `Todo.legacy_flag: FieldChange.dropped`,
and `postgres.sql` holds the one statement. `jadpo check` then fails until
an acceptance this change adds lists `Todo.legacy_flag:
DataChange.column_dropped` with a person's reason
([Acceptances](/docs/acceptances.md)), and
`jadpo approval` says what goes in plain words: the store, the table, each
column and its type.

| What leaves | First step | Needs a person | Second step | Needs a person |
|---|---|---|---|---|
| A field | Its column retired, `Todo.legacy_flag: FieldChange.retired` | | `--drop Todo.legacy_flag`, `FieldChange.dropped` | `DataChange.column_dropped` |
| An entity | Its table retired, `Todo: EntityChange.retired` | `DataChange.entity_removed` | `--drop Todo`, `EntityChange.dropped` | `DataChange.table_dropped` |
| An entity moved to another store | A new table there, the old one retired, nothing to write | `DataChange.entity_moved` | `--drop Todo` | `DataChange.table_dropped` |

- **A retired entity is out of reach.** Its table keeps its keys and
  indexes for servers on the old build, but no code reads it, and erasure
  and retention no longer reach its records, which is why retiring an
  entity needs an acceptance when retiring a field doesn't. It refers to
  nothing any more, so its rows don't hold back a delete of what they
  named.
- **Land the two steps in separate deploys.** A drop in the same deploy as
  its retirement takes the column from servers still running the old
  build. Nothing checks this for you: the acceptance is where it is
  weighed.
- **What was retired stays retired.** An entity can't take back a retired
  table, nor a field a retired column, until a migration has dropped it. A
  field or entity renamed by hand reads as one removed and one added, so
  its decision says to give it back its name.
- **Moving copies nothing.** A migration runs against one store at a
  time, so an entity moved to another starts empty there and its records
  stay behind, retired, until you copy them across and drop them.
- **Files are let go of.** Dropping a `File` column or a table with one
  takes a holder from each file its rows hold, in the same transaction,
  and the file's bytes go as any file's do once no record holds them
  ([Deleting and erasing](/docs/files.md#deleting-and-erasing)),
  as long as a `File` field still names their store of files. While it is
  only retired, its rows still hold them.
- **The audit trail stays.** No migration removes an entry of
  `jadpo_audit`. A dropped field's values stay in its entries until their
  retention ends, where no caller is shown them
  ([Retention and erasure](/docs/audit-trail.md#retention-and-erasure)).
  Nothing purges the entries of an entity that is gone, and the report says
  so.
- **System tables stay.** A table of Jadpo's own that nothing asks for any
  more, such as the audit trail's once no entity is audited, stays, with
  nothing to decide and no migration written. It may hold what can't be
  got back.
- **It can't run without its acceptance.** `jadpo migrate apply` refuses
  a project with a removal no acceptance lists with a reason
  ([`migration.drop_not_accepted`](/docs/errors/migration.drop_not_accepted.md)),
  whatever git says, before it reaches a database. So do `jadpo seed` and
  `jadpo dev` on a database they didn't start themselves. A test's database
  and one `jadpo dev` starts are thrown away, so they run it. Editing a
  migration already on the before side so that it removes something needs
  an acceptance too, and a statement that removes what the snapshots keep
  is [`migration.record_invalid`](/docs/errors/migration.record_invalid.md).