Schema migrations
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 | 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 | 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. |
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. |
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:
INFO schema migrations complete applied=0The 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 (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:
[database]
auto_migrate = falseThe 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:
Error: database schema is not current: 12 pending changes; run `altcha-sentinel schema sql` and apply the script as a database administratorWith a current schema, it logs:
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.
Pull the new version, but don't start it. The running server keeps working while you prepare the database.
Generate the migration with the new version and the server's config:
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.sqlThe 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 createsdata_dirand an empty database file when they're missing, so run it as the server's user.Review
migration.sqland apply it as a database administrator. See Run the script.Check the result with the new version:
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 \ doctorstorage.schemareportsThe database schema is current.Start the new version, or roll it out replica by replica.
The migration script
-- 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), andExotDB 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 withCOMMIT. - 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:
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;-- 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 schemasection, 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, anddoctoralike. - 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, 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
[clickhouse]
auto_migrate = falseAt startup, the server checks that the four objects and the rollup state exist, and refuses to start otherwise:
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 scriptGenerate the script with the server's config, as in Apply a migration, and run it in the configured database:
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.sqlAfter 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 EXISTSfor each missing table, andCREATE MATERIALIZED VIEW IF NOT EXISTSfor the rollup view;ALTER TABLE request_logs MODIFY SETTING ttl_only_drop_parts = 1for arequest_logstable that an older version created without it;INSERT INTO request_stats_state …rows that record the rollup boundary;- the
raw_retentionTTL, when it is due (see 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:
-- Apply before 2026-11-19T15:00:00Z: the rollup view counts rows from that hour on; regenerate the script after it.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:
WARN [clickhouse] raw_retention changed: run `altcha-sentinel schema sql --clickhouse` and apply the scriptGenerate 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:
SELECTonsystem.tables(for the startup check) and on the four objects;INSERTonrequest_logs,request_stats_hourly, andrequest_stats_state;ALTER DELETEonrequest_stats_hourly, for the background rollup of an existingrequest_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.
Switch modes
- To manual: set
auto_migrate = falseonce the schema is current, that is,storage.schemareports no pending changes orschema sqlprints nothing to apply. Then remove the account's schema rights if you want. - To automatic: set
auto_migrate = trueand 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
schema sqlin the CLI reference[database]and[clickhouse]in the Configuration reference- ClickHouse
- Clustering & high availability
- Updating Sentinel