SQL Migrations

Incremental schema changes applied in order by timestamp prefix.

Naming Convention

Migration directories follow the format:

YYYY-MM-DD-HHMMSS-NNNN_description_goes_here/
  up.sql
  down.sql

These are created with the diesel tool, and in particular:

diesel migration generate description_goes_here

NNNN is a zero-padded sequence number for ordering multiple migrations on the same timestamp.
up.sql applies the migration forward.
down.sql reverses it.

Primary key id columns must be BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT. Do not use signed IDs.
Always create tables with ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin.
Application-owned state/status/type columns must not have schema defaults. Use NOT NULL without DEFAULT when required, and have application inserts explicitly supply the value. Document the corresponding Rust enum; application queries bind its variants instead of hardcoding enum strings in SQL. See crates/schema/database/mysql_queries/AGENTS.md for the query rules.

All SQL files should start with the standard IntelliJ noinspection comments:

-- noinspection SqlDialectInspectionForFile
-- noinspection SqlNoDataSourceInspectionForFile
-- noinspection SqlResolveForFile

Keep documentation in this directory as regular files, not symlinks. Diesel can treat a documentation symlink as a migration directory and reject the entire migration list.
Every up.sql must have a corresponding down.sql that cleanly reverses the migration.
For CREATE TABLE migrations, the down.sql is typically DROP TABLE IF EXISTS table_name;.
For ALTER TABLE migrations, the down.sql reverses the alteration (e.g. DROP COLUMN).