Database & migrations

Tradr applies its own schema changes. There is no migration step for an operator to run and no window in which the code and the schema disagree. This page explains what happens and why it is built that way.

If you only want the commands, read Upgrade an instance.

Migrations run when the api boots

The api runs migrations during startup, before it accepts a request. It does this on every boot, not only on the first one. A boot with nothing pending costs one query.

That choice removes a class of operator error. An instance cannot serve traffic against a schema it was not built for, because the code that would serve the traffic has not started yet.

The cost is startup time. A migration that takes four minutes is four minutes of downtime. That is why the api healthcheck allows a 180-second start_period before Docker calls the container unhealthy.

Two tracks

Tradr keeps two sets of SQL files, applied in order, each with its own journal and its own lock.

Track	Where	Runs in a transaction	Journal
Standard	apps/api/src/db/migrations/	Yes	drizzle.__drizzle_migrations
Post	apps/api/src/db/post-migrations/	No	_post_migrations_journal

Standard migrations are generated by Drizzle Kit from the schema files and applied by Drizzle’s migrator. Each runs inside a transaction, so a failure leaves nothing half-applied.

Post-migrations exist for statements PostgreSQL refuses to run inside a transaction. CREATE INDEX CONCURRENTLY is the one Tradr uses. It builds an index without locking writes, which matters on a positions table with real history in it. PostgreSQL will not run it inside a transaction, so it lives in its own directory and runs outside one.

The post-migration runner recovers from an interrupted build. CREATE INDEX CONCURRENTLY leaves an invalid index behind when it is killed part-way. The runner finds an invalid index of the expected name, drops it, and builds it again.

One migrator at a time

Before either track runs, the api takes a PostgreSQL session-level advisory lock — key 7064001 for standard migrations, 7064002 for post-migrations.

Advisory locks are cooperative and cost nothing when uncontended. A second api container starting at the same moment blocks on the lock, waits for the first to finish, then finds nothing pending and proceeds. Neither container has to know the other exists.

You can see the locks in the status command:

docker compose exec api tradr migrate --status

Expected result: exit code 0, and held: false against both keys on an idle instance.

Behind a transaction pooler

The lock is held by a session, so it only works on a direct connection. A transaction-mode pooler can route the next statement to a different backend, which would leave the lock holder and the migration in separate sessions.

Tradr refuses to guess. Setting DB_TRANSACTION_POOLER=true without DIRECT_DATABASE_URL fails the boot with a message naming both variables. See Bring your own Postgres.

Forward-only, and what that buys

Drizzle has no down-migrations, and Tradr does not write them. Migrations only go forward.

That sounds like it removes an escape route. It adds one, because of the rule it forces on contributors: add before you remove.

• A release that adds a column, table, or index is safe in both directions. The previous release’s code never referenced the new thing, so it keeps working.
• A release that removes something an earlier release still reads is a one-way door. Past that version, you cannot go back.

Contributors split a change across two releases: add the new column and write to both in release N, drop the old one in release N+1 or later. A rename is a drop plus an add, so it splits the same way. The rule and its rationale are in CONTRIBUTING.md.

The result is that “redeploy the previous image” is a real recovery for almost every release, without any down-migration ever being written. Releases that close the door say so in their notes.

Skipping post-migrations

SKIP_POST_MIGRATIONS=true makes the api log the skip and start serving without building indexes. It is meant for an operator who runs the index build out of band against a large table, from a source checkout.

Leave it false on a Compose instance. The published image ships no command to apply the skipped work, so the indexes stay missing and the queries they support stay slow.

Checking the state

tradr migrate --status reads both journals and reports what is applied and what is pending. It opens its own read-only connection, and never takes the migration lock, so it is safe to run against a live instance.

Standard migrations (drizzle.__drizzle_migrations):
  table exists: true
  applied: 24
  pending: none
Post-migrations (_post_migrations_journal):
  table exists: true
  applied: 1
  pending: none
Advisory locks:
  migrations (7064001) held: false
  post-migrations (7064002) held: false
Schema is up to date.

Exit code	Meaning
0	The schema is current.
1	Migrations are pending on at least one track.
2	The command cannot reach the database.

A held: true against either key means a migration is running right now. That is the expected reading during a long upgrade.

Next steps

• Upgrade an instance — the commands, and the rollback range.
• Back up and restore — take one before every upgrade.
• Bring your own Postgres / run behind a pooler — what a pooler changes.