# Schema migrations

> **Sentinel v2 (beta):** This page documents Sentinel v2, a beta rewrite that may change before the stable release. It does not apply to Sentinel v1, the current stable version: see the [Sentinel v1 documentation](/docs/sentinel.md).

Sentinel defines its own tables and, by default, creates and upgrades them every time a server starts. When the database account Sentinel runs as isn't allowed to change the schema, turn this off: Sentinel then only checks the schema at startup, and `altcha-sentinel schema sql` prints the migration for a database administrator to apply.

Two independent settings control this:

| Key | Default | Covers |
| --- | --- | --- |
| [`[database] auto_migrate`](/docs/sentinel/v2/reference/configuration/#database) | `true` | The main database: Sentinel's tables and, on an embedded database, ExotDB's system tables and the built-in KV store's tables. |
| [`[clickhouse] auto_migrate`](/docs/sentinel/v2/reference/configuration/#clickhouse) | `true` | The ClickHouse tables, the hourly rollup view, and the `raw_retention` TTL. |

You can migrate the main database automatically and ClickHouse by hand, or the other way round.

## What Sentinel migrates

| Tables | Where | When |
| --- | --- | --- |
| ExotDB system tables (`_exotdb_relations`, `_exotdb_locks`, `_exotdb_license_*`, `_exotdb_jobs`, `_exotdb_audit`, `_exotdb_policies`, …) | Main database | Embedded databases only (`sqlite:`, `postgres://`, `mysql://`, `mariadb://`, `mssql://`, `oracle://`). An ExotDB server (`http(s)://`) manages its own. |
| Sentinel's tables (`accounts`, `users`, `api_keys`, `request_logs`, `license_state`, …) with their indexes and relations | Main database | Always, named with [`[database] table_prefix`](/docs/sentinel/v2/configure/databases/#share-a-database-with-table_prefix). |
| Built-in KV store tables (`_exotdb_kv`, `_exotdb_kv_license_*`) | Main database | When the built-in KV store keeps its data in the main database: no `[redis] nodes` or `[redis.sentinel]`, and `[redis] builtin` isn't `"memory"`. |
| `request_logs`, `request_stats_hourly`, `request_stats_state`, `request_stats_hourly_mv` | ClickHouse | With `[clickhouse] url` and the `clickhouse` license feature. See [ClickHouse](#clickhouse). |

Migrations only add tables, columns, indexes, and relations. They never drop or rename anything, so other applications' tables in a shared database are left alone, and a table or column that a later version stops using stays in place. A changed column type can't be migrated: startup fails, and the column needs a manual backfill or cast.

## Automatic migrations

With `auto_migrate = true`, the server migrates at every start, before it serves requests, and logs the number of changes it applied:

```text
INFO schema migrations complete applied=0
```

The database account in `[database] url` needs the right to create tables and indexes; with an ExotDB server, `[database] token` must be an admin token. With embedded SQLite, Sentinel creates `data_dir` and the database file when they're missing.

`altcha-sentinel license request` migrates the same way, because the license state lives in the database. `altcha-sentinel doctor` and `altcha-sentinel schema sql` never change the schema.

### Replicas and rolling updates

Replicas that start together migrate one at a time, under a database lock. The first one applies the changes; the others wait, find nothing left to do, and start. A replica that waits longer than 30 seconds fails to start with `timed out after 30000ms waiting for migration lock '__exotdb_migrate__'`; restart it.

With an ExotDB server, that wait is a single HTTP request, so [`[database] request_timeout`](/docs/sentinel/v2/reference/configuration/#database) (default `60s`) must be above `30s`. With `auto_migrate = false`, no replica waits, and any `request_timeout` up to `1h` is accepted.

Because migrations only add, a new version can migrate the database while replicas of the old version keep running. That's what makes rolling updates work, and it holds for a script applied by hand before the rollout too.

## Manual migrations

Turn automatic migrations off for the main database:

```toml
[database]
auto_migrate = false
```

The server then issues no schema changes. At startup it checks that every table, column, index, and relation it needs exists, and refuses to start otherwise:

```text
Error: database schema is not current: 12 pending changes; run `altcha-sentinel schema sql` and apply the script as a database administrator
```

With a current schema, it logs:

```text
INFO schema verified (auto_migrate = false)
```

The check ignores tables and columns Sentinel doesn't declare, so a schema that an administrator migrated ahead of the running version passes. `altcha-sentinel license request` runs the same check and fails the same way.

Manual migrations are meant for external databases with a separate administrator account. Embedded SQLite has no accounts, so whoever can write the database file can also change its schema.

### Apply a migration

Use these steps for every version change and for a new install, where the script creates every table. The examples run the commands in a one-off container of the **new** image, with the same config file and secret mounts as the server. With embedded SQLite, mount the server's data volume too.

1. **Pull the new version, but don't start it.** The running server keeps working while you prepare the database.

2. **Generate the migration with the new version and the server's config:**

```bash
docker run --rm \
 -v "$PWD/sentinel.toml:/etc/sentinel/sentinel.toml:ro" \
 -v "$PWD/secrets:/run/secrets:ro" \
 ghcr.io/altcha-org/sentinel:<new-version> --config /etc/sentinel/sentinel.toml \
 schema sql > migration.sql
```

The command connects with the `[database]` credentials, reads the schema, and prints the SQL that brings it to the new version's schema. It changes nothing. Use the server's own config: `url`, `table_prefix`, and the `[redis]` settings decide which tables the script covers. With embedded SQLite, it creates `data_dir` and an empty database file when they're missing, so run it as the server's user.

3. **Review `migration.sql` and apply it as a database administrator.** See [Run the script](#run-the-script).

4. **Check the result with the new version:**

```bash
docker run --rm \
 -v "$PWD/sentinel.toml:/etc/sentinel/sentinel.toml:ro" \
 -v "$PWD/secrets:/run/secrets:ro" \
 ghcr.io/altcha-org/sentinel:<new-version> --config /etc/sentinel/sentinel.toml \
 doctor
```

`storage.schema` reports `The database schema is current.`

5. **Start the new version**, or roll it out replica by replica.

<Callout type="warning" title="Generate the script with the version you deploy">

The script brings the database from its state when the script was generated to the schema of the version that generated it. `schema sql` in the running, older container prints nothing to apply. After the database changes, or for another version, generate the script again.

### The migration script

```sql
-- Generated by altcha-sentinel 2.0.0
-- Dialect: postgres
BEGIN;

-- == ExotDB system tables ==
-- create table _exotdb_relations
CREATE TABLE "_exotdb_relations" (…);
…

-- == Application schema ==
-- create table accounts
CREATE TABLE "accounts" (…);
…

-- == ExotDB KV ==
-- create table _exotdb_kv
CREATE TABLE "_exotdb_kv" (…);
…

COMMIT;
```

- **Header:** the Sentinel version that generated the script and the SQL dialect it's written in.
- **Sections**, in the order they must run: `ExotDB system tables`, `ExotDB policies table`, `Application schema` (Sentinel's tables), and `ExotDB KV`. Sections with nothing to change are left out.
- **Steps:** a `--` comment names each change (`create table …`, `add column …`, `create index …`, `add relation …`), followed by its statements.
- **Transactions:** on SQLite and PostgreSQL, the script runs in one transaction, so it applies completely or not at all. MySQL and MariaDB commit each schema statement on its own; if the script fails halfway, fix the cause and generate a new script, which covers only what is still missing. SQL Server separates statements with `GO`, and Oracle with `/`, ending with `COMMIT`.
- **Nothing to do:** a current schema prints only the header and `-- The schema is current; nothing to apply.`

### Run the script

Run the script with the database's own client, connected as an account that may create tables, in the database Sentinel uses:

| Database | Example |
| --- | --- |
| SQLite | `sqlite3 /var/lib/sentinel/sentinel.db < migration.sql` |
| PostgreSQL | `psql -v ON_ERROR_STOP=1 -d sentinel -f migration.sql` |
| MySQL, MariaDB | `mysql sentinel < migration.sql` |
| SQL Server | `sqlcmd -b -d sentinel -i migration.sql` |
| Oracle | `sqlplus admin@sentinel @migration.sql` |

Tables the administrator creates belong to the administrator's account, so grant Sentinel's account access to them.

### Database privileges

With `auto_migrate = false`, Sentinel's account needs no right to create, alter, or drop tables. It needs to:

- read, insert, update, and delete rows (`SELECT`, `INSERT`, `UPDATE`, `DELETE`) in every table of the script: Sentinel's, ExotDB's system tables (`_exotdb_*`), and the KV store's;
- read the database catalog, to check the schema at startup and to generate the script.

For example, with an administrator `sentinel_admin` and Sentinel's account `sentinel`:

<Tabs padding={false}>

### PostgreSQL

```sql
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO sentinel;
-- Tables that later scripts create:
ALTER DEFAULT PRIVILEGES FOR ROLE sentinel_admin IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO sentinel;
```

### MySQL, MariaDB

```sql
-- Database-level grants cover tables that later scripts create.
GRANT SELECT, INSERT, UPDATE, DELETE ON sentinel.* TO 'sentinel'@'%';
```

### ExotDB server

With `[database] url = "https://…"`, Sentinel works through an ExotDB server:

- The script has only the `Application schema` section, in the dialect of the server's backend. The ExotDB server manages its own system tables, and the KV store is a Redis or the server's `[kvstore]` port, never the database.
- Reading the schema needs an admin `token`: for automatic migrations, the startup check, `schema sql`, and `doctor` alike.
- Apply the script to the backend database that the ExotDB server serves as `[database] name`. The ExotDB server picks up the new tables within seconds.

## ClickHouse

With [ClickHouse](/docs/sentinel/v2/operations/clickhouse/), Sentinel keeps raw request logs in `request_logs`, hourly stats in `request_stats_hourly`, which the materialized view `request_stats_hourly_mv` fills from the raw logs, and the rollup state in `request_stats_state`. The ClickHouse database itself (`[clickhouse] database`) must exist in either mode.

With `[clickhouse] auto_migrate = true`, the server creates these objects at startup, one replica at a time under a database lock, and keeps the `request_logs` TTL in line with `[clickhouse] raw_retention`.

### Manual ClickHouse migrations

```toml
[clickhouse]
auto_migrate = false
```

At startup, the server checks that the four objects and the rollup state exist, and refuses to start otherwise:

```text
Error: ClickHouse schema is not current: missing request_logs, request_stats_hourly, request_stats_state, request_stats_hourly_mv; run `altcha-sentinel schema sql --clickhouse` and apply the script
```

Generate the script with the server's config, as in [Apply a migration](#apply-a-migration), and run it in the configured database:

```bash
docker run --rm \
-v "$PWD/sentinel.toml:/etc/sentinel/sentinel.toml:ro" \
-v "$PWD/secrets:/run/secrets:ro" \
ghcr.io/altcha-org/sentinel:<new-version> --config /etc/sentinel/sentinel.toml \
schema sql --clickhouse > clickhouse.sql

clickhouse-client --database altcha_sentinel --multiquery < clickhouse.sql
```

After a header with the generating version and the target database, the script names tables without a database and contains what the server would do at that moment:

- `CREATE TABLE IF NOT EXISTS` for each missing table, and `CREATE MATERIALIZED VIEW IF NOT EXISTS` for the rollup view;
- `ALTER TABLE request_logs MODIFY SETTING ttl_only_drop_parts = 1` for a `request_logs` table that an older version created without it;
- `INSERT INTO request_stats_state …` rows that record the rollup boundary;
- the `raw_retention` TTL, when it is due (see [Raw log retention](#raw-log-retention)).

A current schema prints `-- The ClickHouse schema is current; nothing to apply.` ClickHouse has no transactions: if the script fails halfway, fix the cause and generate a new one.

### The rollup deadline

On a new ClickHouse database, the rollup view counts every log from the start. On an existing `request_logs` without a rollup, written by an older version, the view counts logs from the start of the next full hour, and the server rolls up the older logs in the background. That hour is the rollup boundary, and the script states the deadline it implies:

```sql
-- Apply before 2026-11-19T15:00:00Z: the rollup view counts rows from that hour on; regenerate the script after it.
```

> **Warning:**
>
> Apply the script before that time. A script applied later leaves the logs between the boundary and the moment the view was created out of the hourly stats. If the deadline has passed, generate a new script.

### Raw log retention

`[clickhouse] raw_retention` (default `90d`) becomes a TTL on `request_logs`. The server applies it once every replica reads stats from the rollup: 10 minutes after the background rollup finished (on a new database, 10 minutes after setup), and again after every change of `raw_retention`. Until then, raw logs keep the table's 5-year TTL.

With `auto_migrate = false`, the server doesn't change the TTL. It logs once per run:

```text
WARN [clickhouse] raw_retention changed: run `altcha-sentinel schema sql --clickhouse` and apply the script
```

Generate the script again and apply it; it then holds the `ALTER TABLE request_logs MODIFY TTL …` statement and the state row that records it. On a new install, expect this warning 10 minutes after the first setup.

### ClickHouse privileges

With `auto_migrate = false`, Sentinel's ClickHouse user needs no `CREATE`, `ALTER TABLE … MODIFY`, or `DROP` rights. It needs:

- `SELECT` on `system.tables` (for the startup check) and on the four objects;
- `INSERT` on `request_logs`, `request_stats_hourly`, and `request_stats_state`;
- `ALTER DELETE` on `request_stats_hourly`, for the background rollup of an existing `request_logs`, which replaces a day's hourly rows before it writes them.

## Check the schema

`altcha-sentinel doctor` reports the main database's schema as `storage.schema`. It counts the pending changes to Sentinel's tables and, on an embedded database, to ExotDB's and the KV store's:

| Status | Message | When |
| --- | --- | --- |
| `ok` | `The database schema is current.` | Nothing to change. |
| `ok` | `N schema changes are pending; the server applies them at startup.` | `[database] auto_migrate = true` |
| `FAIL` | `N schema changes are pending; the server refuses to start until they are applied.` | `[database] auto_migrate = false` |

The count is in `details.pendingOps` of the JSON report. `storage.schema` runs only in the CLI, not under **Doctor** in the admin UI, and `doctor` itself never creates tables. Run it with the version you are about to start, since it compares the database with that version's schema. See [`doctor`](/docs/sentinel/v2/reference/cli/#doctor).

## Switch modes

- **To manual:** set `auto_migrate = false` once the schema is current, that is, `storage.schema` reports no pending changes or `schema sql` prints nothing to apply. Then remove the account's schema rights if you want.
- **To automatic:** set `auto_migrate = true` and give the account the right to create tables. The next start migrates whatever is missing.

## Errors

These messages stop startup (or `license request`), with exit code `1`:

| Message | Cause | Fix |
| --- | --- | --- |
| `database schema is not current: N pending changes; run altcha-sentinel schema sql …` | `[database] auto_migrate = false`, and Sentinel's tables are behind this version. | Generate and apply the script. |
| `database schema check failed; run altcha-sentinel schema sql …` | `[database] auto_migrate = false` on an embedded database, and ExotDB's system tables are missing or behind. | Generate and apply the script. |
| `kv store schema check failed; run altcha-sentinel schema sql …` | `[database] auto_migrate = false`, and the built-in KV store's tables are missing or behind. | Generate and apply the script. |
| `ClickHouse schema is not current: missing …` or `… rollup state missing` | `[clickhouse] auto_migrate = false`, and ClickHouse tables or the boundary rows are missing. | Generate and apply the ClickHouse script. |
| `timed out after 30000ms waiting for migration lock '__exotdb_migrate__'` | Another replica held the migration lock for over 30 seconds. | Restart the replica. |
| `timed out waiting for ClickHouse setup lock …` | Another replica held the ClickHouse setup lock, or the ExotDB server's `max_long_poll_ms` is below 60 seconds. | Restart the replica, or raise `max_long_poll_ms`. |
| `database.request_timeout must be above 30s (the migration-lock wait) and at most 1h` | `[database] auto_migrate = true`, and `request_timeout` is 30 seconds or less. | Raise `request_timeout`, or migrate by hand. |
| `no [clickhouse] url configured` | `schema sql --clickhouse` without `[clickhouse] url`. | Set the url in the config passed with `--config`. |

## Related

- [Databases & KV store](/docs/sentinel/v2/configure/databases/)
- [`schema sql` in the CLI reference](/docs/sentinel/v2/reference/cli/#schema-sql)
- [`[database]`](/docs/sentinel/v2/reference/configuration/#database) and [`[clickhouse]`](/docs/sentinel/v2/reference/configuration/#clickhouse) in the Configuration reference
- [ClickHouse](/docs/sentinel/v2/operations/clickhouse/)
- [Clustering & high availability](/docs/sentinel/v2/operations/clustering/)
- [Updating Sentinel](/docs/sentinel/v2/operations/updating/)
