# Automatic database migrations

This directory contains forward database migrations executed automatically by
the deployment process. Files describe changes required by deployed code; they
are not a reconstructed history of every database object. Determine the state
of each environment from the migration runner and direct database inspection.

## File naming

Use a sortable UTC-style timestamp followed by a short English description:

```text
YYYYMMDDHHMMSS_short_description.sql
```

Example:

```text
20260720183000_add_delivery_status_to_order.sql
```

## Rules

- Commit every migration together with the code that requires it.
- Keep migrations compatible with every database server series used by test
  and production. Verify the actual server versions through the application PDO
  connection before relying on version-specific SQL.
- Never edit a migration after it has run on any environment. Add a later file
  to correct it.
- Migrations must be additive and backward-compatible with the previous code.
- Do not drop tables, columns, indexes required by the previous code, or user
  data through the automatic runner.
- Make every statement safe to execute again after a partial failure. MySQL
  DDL can commit implicitly, so the history record may not be written when a
  later statement fails.
- Do not use `DELIMITER`, stored routines, client commands, or environment
  configuration in migration files.
- Keep dynamic or user-provided values out of SQL files.
- File and server-configuration operations belong in `docs/`, not in SQL.

## Deployment behavior

Before the first pending migration, the runner:

1. acquires a database advisory lock;
2. creates a complete compressed SQL dump outside the web directory;
3. verifies the backup checksum;
4. executes pending files in filename order;
5. records the filename, SHA-256 checksum and execution time in
   `deployment_migration`.

The dump uses a consistent transaction for InnoDB tables. Legacy
non-transactional tables are dumped separately under a read lock, so the site
is not stopped by a global database lock. Backups are never deleted
automatically. They are stored outside the project in
`deployment-backups/<host>/` unless `DEPLOY_DATABASE_BACKUP_DIR` selects another
absolute path.

The runner refuses to continue when an already-applied file has changed. It
does not automatically roll back schema changes. A code rollback is safe only
because all automatic migrations must remain compatible with the previous
application version.

## Commands

Show status without changing the database:

```bash
php scripts/database-migrations.php status test-next.rollschool.pl
```

Create an on-demand backup without running migrations:

```bash
php scripts/database-migrations.php backup test-next.rollschool.pl
```

Back up and apply pending migrations:

```bash
php scripts/database-migrations.php migrate test-next.rollschool.pl
```