Database Migrations

UsePostgres and UseSqlite apply every pending migration at startup, so most upgrades need nothing from you. The migrations on this page change something you can see: a column your code reads, an index worth knowing about on a large database, or how an existing row behaves after the upgrade. If your host calls SkipMigrations, run them yourself before the new version serves traffic.

Postgres and SQLite number their migrations independently, so where both have one, both names are given.

On Postgres each script waits at most five seconds for a table lock, so a migration behind a long transaction on a running instance does not stall enqueue and dispatch on every host. A script that gives up is run again, up to ten times, and then startup fails with 55P03 (why).

Upgrading a scheduler from Trax.Effect 1.57.2 or earlier: stop every scheduler host before starting one on the new version. The leader-lock key changed in 1.57.3, so an old host and a new one would both run the ManifestManager (details).

ManifestGroup (014)

014_manifest_group.sql promotes a manifest's group from a denormalized string (group_id) to its own table with per-group dispatch controls. It:

1. creates trax.manifest_group with name, max_active_jobs, priority, is_enabled and timestamp columns;
2. seeds a manifest_group row for each distinct group_id on existing manifests, and one per ungrouped manifest, named after its external_id;
3. adds a NOT NULL manifest_group_id foreign key to trax.manifest;
4. drops the old group_id column.

Existing manifests keep their groups. It is not idempotent: it predates the rule that every script can run again, and its CREATE TABLE, ADD COLUMN and CREATE INDEX carry no IF NOT EXISTS. The migrator journals it and never runs it twice, but if it stops partway (a killed process, a failed statement) the next start runs it again from the top and fails on the table it already created. Repair that by hand: finish or undo the partial change, then start again.

Manifest.GroupId (string?) is replaced by Manifest.ManifestGroupId (long, since 018_bigint_ids.sql) and the Manifest.ManifestGroup navigation. Code that read GroupId directly has to move to one of those; nothing else changes. A manifest scheduled without a group name still gets a group of its own, named after its externalId. Naming a group and setting its MaxActiveJobs, Priority and IsEnabled are covered in Per-Group Dispatch Controls. The namespaces of the scheduler types involved are listed in the Namespace Reference.

Scaling Indexes (026)

026_scaling_indexes.sql adds partial and composite indexes that keep the hot queries off sequential scans at high row counts. Every index is CREATE INDEX IF NOT EXISTS, so it is safe to re-run.

They matter once metadata holds more than a few thousand rows. They change query performance, not results.

Foreign-key and Manifest Indexes (036)

036_fk_and_manifest_eval_indexes.sql (Postgres) or 004_fk_and_manifest_eval_indexes.sql (SQLite). Every index is CREATE INDEX IF NOT EXISTS, so it is safe to re-run.

PostgreSQL does not index the referencing side of a foreign key on its own. Without the first three, the ON DELETE RESTRICT checks behind DeleteExpiredMetadataJunction's cleanup DELETE scanned those tables, so the cleanup grew with the size of the database. The fourth bounds the per-manifest failed-count subquery in LoadManifestsJunction to failed rows instead of the manifest's entire terminal history.

Queued Subject Index (046)

046_work_queue_subject_queued_index.sql (Postgres) or 011_work_queue_subject_queued_index.sql (SQLite) adds one partial index. It is CREATE INDEX IF NOT EXISTS and safe to re-run. On Postgres it is built CONCURRENTLY where it has not been built yet, so enqueue and dispatch are not blocked while it builds; a database that already ran 046 is not touched again.

The two subject indexes from migration 042 cover dispatched rows, so before this the lookup read every queued row and filtered on the key. With 500,000 queued entries that took about 137 ms per detail view; through this index it takes about 1 ms. Entries without a subject key, which is most manifest work, are left out of the index, so it stays small.

State-machine Request Scope (048)

048_snapshot_draft_request_scope.sql (Postgres) or 013_snapshot_draft_request_scope.sql (SQLite) adds two nullable columns to snapshot_draft: last_request_trigger and last_request_from_state. The Postgres statements are ADD COLUMN IF NOT EXISTS and safe to re-run. Nothing is backfilled.

A draft now records the trigger and from-state of its last request id, and replays a retry only for the same trigger (how a request id is matched). A draft written before the migration has no recorded trigger, so a retry of its last request is refused once as request-id-reused rather than replayed. A custom ISnapshotStore should override UpdateWithRequest to store the whole request; without it, every retry against that store is refused the same way.

Timestamps as timestamptz (049)

049_timestamptz_columns.sql (Postgres only) changes five columns from timestamp without time zone to timestamptz: work_queue.created_at and dispatched_at, manifest_group.created_at and updated_at, and manifest.next_scheduled_run. Their defaults, and background_job.created_at's, become now().

A plain timestamp stores the writing session's wall-clock time, so a host whose Postgres session was not in UTC stored these hours off: CreatedAt read back four or five hours early from New York, and variance schedules fired early or late. Each stored value is read as UTC, which is what every host with a UTC session wrote. A row written from another zone before the upgrade was shifted when it was stored, and keeps that shift.

It does not rewrite the tables. Each block sets its own transaction's time zone to UTC and changes the type with no USING, and in a UTC session Postgres reads each stored value as UTC and keeps the table's storage as it is, so the ACCESS EXCLUSIVE lock each ALTER takes lasts for a catalog change, not a copy of the table. That holds even when the scripts run from a session in another zone. The lock still has to be granted, so it waits for transactions already open on those tables, at most five seconds per try (see the top of this page). Each change checks the column's type first, so an interrupted run resumes where it stopped.

External Id Index (050)

050_metadata_external_id_index.sql (Postgres only) adds ix_metadata_external_id, a plain btree index on trax.metadata (external_id). It is not unique: a dispatch retry adds a run under the same id.

External id is the one key that is the same from enqueue to run, so a consumer correlating its own records with runs looks them up by it. Without an index each lookup read the whole table: 48 to 77 ms at a million rows, against 0.05 ms with it. external_id is char(32), so compare a char(32) to use it without a cast.

It is built CREATE INDEX CONCURRENTLY, so runs keep being written while it builds, and the build takes as long as the table is large. It waits for transactions already open on metadata to finish before it starts, so a long transaction on a running instance delays startup of the upgraded one, and one that stays open for about a minute fails it with 55P03 until that transaction ends.

SQLite enum partial indexes (014)

014_enum_integer_partial_indexes.sql (SQLite only) recreates eleven partial indexes on work_queue, metadata and dead_letter. Their predicates compared the enum columns with Postgres labels (status = 'queued'), but SQLite stores an enum as its integer, so the indexes covered no row: the unique one-queued-entry-per-manifest index enforced nothing, and the others served no query. They now compare integers.

Because that unique index was inert, a SQLite database can hold two queued entries for one manifest, and the index cannot be built over them. Before rebuilding it, 014 keeps each manifest's oldest queued entry and cancels the rest (status Cancelled), which is what the queue would have held had the index worked.

SQLite fixed-width offset timestamps (017)

017_fixed_width_offset_timestamps.sql (SQLite only) rewrites effect_claim.lease_expires_at and created_at and snapshot_draft.updated_at into the fixed-width UTC text Trax now writes (2026-09-29 12:00:00.1200000+00:00). SQLite compares these columns as text, and a row written before the fixed-width form held EF's default text, with the shortest fraction and the value's own offset. At the exact instant it compared as earlier than itself, so a lease or a draft's age read one tick early, and a non-UTC offset sorted wrong outright. The whole seconds are converted to UTC and the fraction is padded, so no precision is lost. Rows already in the fixed-width form are not touched, and a second run changes nothing.

Effect claim content fingerprint (053, SQLite 018)

053_effect_claim_content_fingerprint.sql (Postgres) and 018_effect_claim_content_fingerprint.sql (SQLite) add a nullable content_fingerprint text column to effect_claim. The state-machine effect runner records the SHA-256 of the draft's canonical wire there when it claims the effect, and replays the claim's receipt only onto a draft with the same content; see effects.

Nothing is backfilled. A claim written before the upgrade has no fingerprint and replays as it did, and a host still on the previous version reads and writes the table unchanged, so a rolling deploy is safe. Adding a nullable column is a catalog change on both providers, not a rewrite of the table.