# 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.

## Table Conventions

- 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:
  ```sql
  -- noinspection SqlDialectInspectionForFile
  -- noinspection SqlNoDataSourceInspectionForFile
  -- noinspection SqlResolveForFile
  ```

## Rules

- 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`).