# migrations/

Every schema change to the connect database, one file each, applied in order by
`scripts/migrate.php` and recorded in the `schema_migration` table.

The runner is shared byte-for-byte with apisso, hhmanager, bmwapp2, cacti and cwframework
(see *The pieces*). `migrations/config.php` holds everything that differs.

## Why the table matters

Before this, schema changes were made by hand and `docs/schema.sql` was
re-dumped. That recorded what the schema **should** be. It never recorded what a
given database had actually **run**, so the only way to answer "is production
actually on this schema?" was to go and look at production. `schema_migration`
is that record.

## Day to day

```bash
php scripts/migrate.php --new=ticket-index   # create today's next migration, empty
php scripts/migrate.php                      # status. Writes nothing, safe on prod
php scripts/migrate.php --apply              # run everything pending, in order
```

Then fold the change into `docs/schema.sql`. It is still the full current
picture and still what a brand-new database is built from.

Exit codes, for a deploy gate: `0` up to date, `3` migrations pending, `1`
something is wrong (drift included, even with files pending), `2` bad
command-line usage.

The runner prints which database it connected to first
(`── app  api_connect@host  (PROD)`). `PROD=true` in `.env` selects
the prod credentials in `code/site.inc.php`. Anything else gets dev.

## First run on a database

There is no `schema_migration` table yet, so the runner refuses `--apply` and
asks you to choose:

```bash
php scripts/migrate.php --baseline --apply   # existing DB, schema already current
```

This records the pending files as applied **without running them**. That is the
right call for dev and prod today, and for any database loaded from
`docs/schema.sql`, which already contains every migration folded in.

## Naming

```
YYYYMMDD_slug.sql        the day's first migration
YYYYMMDD-NN_slug.sql     the day's Nth, NN from 02
```

Lowercase slug, letters/digits/hyphens. `--new=` picks the name for you.

**Order is parsed, never string-sorted.** `-` is `0x2D` and `_` is `0x5F`, so a
plain sort puts `20261009-02_second.sql` *before* `20261009_first.sql`. The
day's second migration would run before its first, silently, and only on days
that have two. `migration_sort()` in `code/migrations.inc.php` parses date and
sequence instead, and `scripts/test-migrations.php` pins it.

A filename that does not match the grammar is **refused**, not sorted to one end.

## Writing one

* **Say why, not what.** The diff shows what changed. Open each file with the
  reason.
* **Make it re-runnable.** Use `CREATE TABLE IF NOT EXISTS`,
  `ADD COLUMN IF NOT EXISTS`, `DROP … IF EXISTS`, and a guard before an `INSERT`
  that seeds rows. MariaDB does not roll DDL back. If a file fails on statement 4
  of 6, statements 1–3 stay in effect and the file stays *pending*. The recovery
  is to fix it and re-run it, and only a re-runnable file survives that.
* **One concern per file.** A failure halfway through a file means manual
  cleanup. A failure between two files does not.
* **No stored routines.** Triggers, procedures, functions and events need
  `DELIMITER` handling, which the runner does not do. It refuses them rather than
  split a routine body on its internal semicolons.
* **Once applied, it is history.** The checksum covers the *statements* with
  whitespace collapsed, not the file bytes. You can reword the comments freely,
  but if a statement changes, the runner refuses to proceed without `--force`.
  Make the change in a new file instead.

Run `php scripts/test-migrations.php` after adding one. It splits every file in
this directory without touching a database.

`/*! … */` and `/*M! … */` executable comments are refused, not stripped.
mariadb-dump writes them, so watch for them in pasted dump fragments.

## What the runner will not do

* **Guess on first run.** See above.
* **Run two copies at once.** `--apply` takes a `GET_LOCK` named for the
  database *before* reading `schema_migration`, so a second run that waited sees
  what the first applied instead of replaying it. A status run takes no lock.
* **Accept an unknown flag.** `code/cli.inc.php` refuses anything undeclared, so
  a typo exits with code 2 instead of being silently ignored.

## The pieces

| File | What it is |
|---|---|
| `migrations/config.php` | **This project only**: bootstrap, timezone, target database. |
| `scripts/migrate-bootstrap.inc.php` | **This project only**: the DB-only bootstrap (not `cw_include`). |
| `scripts/migrate.php` | The runner: discovery, `schema_migration`, locking, execution, status. |
| `code/migrations.inc.php` | **Pure**: filename grammar, ordering, SQL splitting, checksums, config validation. |
| `code/cli.inc.php` | Strict option parsing and `--help` for scripts/. Reusable by other scripts. |
| `scripts/test-migrations.php` | The pure half, this project's config, and every real file in here. |
| `scripts/test-migrate-runner.php` | The real CLI in a temp project against a PDO double. No database. |

The five files that aren't project-only are **byte-identical** across apisso,
hhmanager, connect, bmwapp2, cacti and cwframework (cacti keeps the tests in `tests/`). To
change the runner, change it in one project, copy it to all the others, and run
both tests in each.