Migration Schema Reference

The migration schema template defines the four PostgreSQL tables that back the live tenant migration system in eventsource.application.migration: tenant_migrations, tenant_routing, migration_position_mappings, and migration_audit_log.

The canonical definition is src/eventsource/adapters/sql/schemas/templates/migration.sql, loaded through eventsource.adapters.sql.schemas.get_schema("migration"). Everything below describes that file exactly as it ships; where a value is enforced by a CHECK constraint or a unique index, the constraint is named so you can match it against database errors.

Scope and audience

This page is for operators and integrators who provision the database for a multi-tenant deployment that will move tenants between event stores while the system stays online. It documents the physical schema only — table structure, constraints, indexes, triggers, and which repository class touches which table.

It does not describe the migration workflow (bulk copy, dual write, cutover) or the orchestration API; those live with the eventsource.application.migration package and its README.md. Use this page when you need to answer "what does the database look like, and what will the database reject?"

Prerequisites (PostgreSQL 12+; no SQLite variant)

The template header states PostgreSQL 12+, required for advisory lock support and for the partial and DESC-ordered indexes it declares. It also uses UUID, JSONB, BIGSERIAL, TIMESTAMP WITH TIME ZONE, and plpgsql trigger functions.

There is no SQLite variant. src/eventsource/adapters/sql/schemas/templates/sqlite/ contains only checkpoints.sql, dlq.sql, events.sql, outbox.sql, and snapshots.sql. Calling get_schema("migration", backend="sqlite") raises ValueError with the list of schemas available for that backend, and list_schemas(backend="sqlite") does not include "migration". The live migration system is PostgreSQL-only at the storage layer.

Loading the schema with get_schema("migration")

from sqlalchemy import text
from eventsource.adapters.sql.schemas import get_schema

async with engine.begin() as conn:
    await conn.execute(text(get_schema("migration")))

get_schema returns the raw SQL text; it does not execute anything. The default backend is "postgresql", so the backend argument can be omitted. The convenience constant MIGRATION_SCHEMA (exported from eventsource.adapters.sql.schemas) equals "migration", and get_template_path("migration") returns the on-disk Path to templates/migration.sql if you would rather feed the file to psql or copy it into an Alembic revision.

Every CREATE TABLE uses IF NOT EXISTS, every CREATE INDEX and CREATE UNIQUE INDEX uses IF NOT EXISTS, the trigger functions use CREATE OR REPLACE FUNCTION, and each trigger is preceded by DROP TRIGGER IF EXISTS. Re-running the whole template against an existing database is therefore safe.

Why get_all_schemas() does not include it

get_all_schemas() reads the pre-combined file src/eventsource/adapters/sql/schemas/schemas/all.sql, which creates only events, event_outbox, projection_checkpoints, dead_letter_queue, and snapshots. The migration tables are not in it.

That is deliberate: the migration tables are only meaningful for deployments running multi-tenant store migrations, and they carry PostgreSQL-specific triggers and functions that most installations never need. Apply the migration schema as an explicit, separate step:

from eventsource.adapters.sql.schemas import get_all_schemas, get_schema

async with engine.begin() as conn:
    await conn.execute(text(get_all_schemas()))
    await conn.execute(text(get_schema("migration")))

Apply order relative to events/outbox/checkpoints/dlq schemas

The migration schema declares no foreign keys to events, event_outbox, projection_checkpoints, dead_letter_queue, or snapshots — all three of its foreign keys (tenant_routing.active_migration_id, migration_position_mappings.migration_id, migration_audit_log.migration_id) point at tenant_migrations, inside the same file. Ordering relative to the core schemas is therefore not enforced by the database.

Apply the core schemas first anyway. The migration system reads and writes the event store it is migrating, and migration_position_mappings records positions that only exist once the events table does. Within the migration template itself the order is fixed and must not be rearranged: tenant_migrations is created before tenant_routing, migration_position_mappings, and migration_audit_log, each of which references it.

Table: tenant_migrations

Tracks active and historical migrations with the full phase state machine. One row per migration attempt; history is retained after completion.

Columns

Column	Type	Notes
id	UUID	Primary key. Supplied by the application, not generated by the database.
tenant_id	UUID NOT NULL	The tenant being migrated.
source_store_id	VARCHAR(255) NOT NULL	Identifier of the source event store.
target_store_id	VARCHAR(255) NOT NULL	Identifier of the target event store.
phase	VARCHAR(50) NOT NULL DEFAULT 'pending'	State machine position; see the CHECK values below.
events_total	BIGINT DEFAULT 0	Total events to migrate.
events_copied	BIGINT DEFAULT 0	Events copied so far.
last_source_position	BIGINT DEFAULT 0	Legacy integer position column. Kept for backward compatibility only -- no longer read or written.
last_target_position	BIGINT DEFAULT 0	Legacy integer position column. Kept for backward compatibility only -- no longer read or written.
last_source_position_token	TEXT	Opaque token for the last source position copied to the target, as returned by Position.to_str(). NULL until the first event copies.
last_target_position_token	TEXT	Opaque token for the last written target position. NULL until the first event copies.
started_at	TIMESTAMPTZ	Nullable until the migration starts.
bulk_copy_started_at	TIMESTAMPTZ	Phase timestamp.
bulk_copy_completed_at	TIMESTAMPTZ	Phase timestamp.
dual_write_started_at	TIMESTAMPTZ	Phase timestamp.
cutover_started_at	TIMESTAMPTZ	Phase timestamp.
completed_at	TIMESTAMPTZ	Phase timestamp.
config	JSONB NOT NULL DEFAULT '{}'	Free-form migration configuration (batch size, timeouts, retry settings).
error_count	INTEGER DEFAULT 0	Cumulative recoverable errors.
last_error	TEXT	Most recent error message.
last_error_at	TIMESTAMPTZ	When last_error was recorded.
is_paused	BOOLEAN DEFAULT FALSE	Operator pause flag.
paused_at	TIMESTAMPTZ	When the pause was applied.
pause_reason	TEXT	Free text supplied by the operator.
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Row creation time.
updated_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Maintained by trigger; see below.
created_by	VARCHAR(255)	Operator or system identifier.

phase CHECK constraint values

phase is restricted to exactly seven values:

Value	Meaning
pending	Migration created, not started.
bulk_copy	Copying historical events.
dual_write	Real-time sync; writing to both stores.
cutover	Brief pause while routing switches.
completed	Successfully completed.
aborted	Operator-initiated cancellation.
failed	Unrecoverable error.

The last three are the terminal phases, and the partial indexes below treat ('completed', 'aborted', 'failed') as "not active". Legal transitions between phases are enforced in Python by VALID_TRANSITIONS in eventsource.adapters.sql.migration, not by the database — the CHECK constraint only validates the value itself.

Progress and position columns

events_total is set once the source event count is known (set_events_total); events_copied, last_source_position_token, and last_target_position_token advance as the copier and dual-write interceptor run (update_progress). events_total and events_copied default to 0, so a freshly created migration reports zero progress rather than NULL; the token columns default to NULL and stay NULL until the first event copies. They exist for monitoring and for resuming an interrupted bulk copy from the last recorded source position. The legacy last_source_position / last_target_position BIGINT columns remain in the table but are no longer read or written -- they retire with their own schema revision, not this one.

Phase timestamp columns

Each phase transition stamps its own column: started_at, bulk_copy_started_at, bulk_copy_completed_at, dual_write_started_at, cutover_started_at, completed_at. All are nullable and all remain NULL until the corresponding transition occurs, which makes them a reliable record of which phases a migration actually entered. The repository chooses which timestamps to set during update_phase.

Pause and error columns

is_paused, paused_at, and pause_reason implement operator pause/resume and are written by set_paused. Pausing does not change phase — a paused migration remains in whatever phase it was in, so queries for active migrations must check is_paused separately if they care.

error_count, last_error, and last_error_at are written by record_error and accumulate recoverable errors without moving the migration to failed.

Indexes, including the one-active-migration-per-tenant unique partial index

Index	Definition
idx_tenant_migrations_tenant_id	(tenant_id) — all migrations for a tenant.
idx_tenant_migrations_phase	(phase) WHERE phase NOT IN ('completed', 'aborted', 'failed') — partial index for listing active migrations.
idx_tenant_migrations_active_unique	UNIQUE (tenant_id) WHERE phase NOT IN ('completed', 'aborted', 'failed')
idx_tenant_migrations_completed_at	(completed_at) WHERE completed_at IS NOT NULL — historical queries.

idx_tenant_migrations_active_unique is the important one. Because it is a unique partial index, a tenant may have any number of terminal migration rows but at most one non-terminal row. Attempting to insert a second in-flight migration for the same tenant fails at the database level with a unique violation, regardless of what the application layer checks. Once a migration reaches completed, aborted, or failed, it drops out of the index and a new migration for that tenant becomes possible.

Table: tenant_routing

Holds the tenant-to-store routing configuration consulted by the tenant store router. Exactly one row per tenant.

Columns

Column	Type	Notes
tenant_id	UUID PRIMARY KEY	One row per tenant.
store_id	VARCHAR(255) NOT NULL	Store this tenant currently routes to.
migration_state	VARCHAR(50) NOT NULL DEFAULT 'normal'	Routing behavior; see values below.
active_migration_id	UUID	FK to tenant_migrations(id), nullable.
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Row creation time.
updated_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Maintained by trigger.

migration_state CHECK constraint values

Value	Routing behavior
normal	Route to the configured store; no migration in progress.
bulk_copy	Reads and writes both go to the source store.
dual_write	Writes go through the interceptor and land in both stores.
cutover_paused	Writes are blocked while cutover completes.
migrated	Route to the new store; migration complete.

Note that this is a distinct vocabulary from tenant_migrations.phase: it has five values, not seven, and describes what the router should do rather than where the migration process is. cutover_paused has no phase counterpart of the same name, and there are no pending/completed/aborted/failed routing states — a tenant with no active migration is simply normal or migrated.

Foreign key to tenant_migrations (ON DELETE SET NULL)

active_migration_id UUID REFERENCES tenant_migrations(id) ON DELETE SET NULL

Deleting a migration row therefore does not delete the tenant's routing row; it clears the pointer and leaves store_id and migration_state intact. This is what you want operationally — purging old migration history must never un-route a live tenant. It also means migration_state can be left at a non-normal value with active_migration_id set to NULL if history is purged mid-migration, so purge only terminal migrations.

Indexes

Index	Definition
idx_tenant_routing_store_id	(store_id) — find all tenants on a given store.
idx_tenant_routing_migration_state	(migration_state) WHERE migration_state != 'normal' — partial index; scanning for tenants currently affected by a migration stays cheap even when the vast majority of tenants are normal.

Table: migration_position_mappings

Records the correspondence between a source-store global position and the target-store position the same event landed at, so that projection checkpoints can be translated across the cutover.

Columns

Column	Type	Notes
id	BIGSERIAL PRIMARY KEY	Surrogate key. Also the ordering key for nearest-position lookups -- see below.
migration_id	UUID NOT NULL	FK to tenant_migrations(id) ON DELETE CASCADE.
source_position	BIGINT	Legacy integer position column. Kept for backward compatibility only -- no longer read or written; nullable since slice (c).
target_position	BIGINT	Legacy integer position column. Kept for backward compatibility only -- no longer read or written; nullable since slice (c).
source_position_token	TEXT	Opaque token for the source-store position, as returned by Position.to_str().
target_position_token	TEXT	Opaque token for the corresponding target-store position.
event_id	UUID NOT NULL	Event identifier, for correlation and verification.
mapped_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	When the mapping was recorded.

Unlike tenant_migrations and tenant_routing, this table has no updated_at column and no trigger — mappings are append-only facts.

uq_position_mappings_source_token constraint

CREATE UNIQUE INDEX uq_position_mappings_source_token
ON migration_position_mappings (migration_id, source_position_token)
WHERE source_position_token IS NOT NULL;

Each source position maps to exactly one target position within a migration — the token-era equivalent of the legacy unique_migration_source_position constraint on the now-unused integer columns. A retried batch that re-inserts an already-mapped source position fails loudly rather than creating a second, possibly divergent, mapping. The same source_position_token may of course appear under a different migration_id.

Nearest-position checkpoint translation

Index	Definition
idx_position_mappings_source_token	(migration_id, source_position_token) — exact token lookups.
idx_position_mappings_target_token	(migration_id, target_position_token) — reverse translation.
idx_position_mappings_event_id	(event_id) — debugging and verification.

Checkpoint translation rarely finds an exact match: a projection's checkpoint sits at an arbitrary source position, not necessarily one that was mapped. Position tokens are opaque and JSON-encoded, so their lexicographic string order is not position order — a DESC-ordered index over the token column cannot serve "greatest mapped source position ≤ X" the way the legacy integer index did. find_nearest_source_position instead does a binary search over rows ordered by the surrogate id, comparing decoded Position values in Python at each step, and bounds its row reads at O(log n) mappings rather than scanning a range. This rests on a documented precondition: mappings are recorded in ascending source-position order by a single writer, so id order and source-position order coincide.

Table: migration_audit_log

Append-only audit trail of migration operations, present for compliance as much as for debugging.

Columns

Column	Type	Notes
id	BIGSERIAL PRIMARY KEY	Surrogate key.
migration_id	UUID NOT NULL	FK to tenant_migrations(id) ON DELETE CASCADE.
event_type	VARCHAR(100) NOT NULL	Classification; see values below.
old_phase	VARCHAR(50)	Previous phase, for phase_changed entries.
new_phase	VARCHAR(50)	New phase, for phase_changed entries.
details	JSONB	Additional structured context, shape varies by event_type.
operator	VARCHAR(255)	Who initiated the action (system or operator ID).
occurred_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	When it happened.

old_phase and new_phase are plain VARCHAR(50) with no CHECK constraint — they are not validated against the phase vocabulary.

event_type CHECK constraint values

Fifteen values are permitted:

migration_started, phase_changed, migration_paused, migration_resumed, migration_aborted, migration_completed, migration_failed, error_occurred, cutover_initiated, cutover_completed, cutover_rolled_back, verification_started, verification_completed, verification_failed, progress_checkpoint.

These correspond one-to-one with the AuditEventType enum in eventsource.ports.migration.models. Adding a new audit event type requires changing both the enum and this CHECK constraint.

Indexes for compliance and time-range queries

Index	Definition
idx_migration_audit_migration_id	(migration_id) — full trail for one migration.
idx_migration_audit_occurred_at	(occurred_at) — time-based compliance reporting across all migrations.
idx_migration_audit_event_type	(event_type) — filter by kind of event.
idx_migration_audit_migration_time	(migration_id, occurred_at) — composite, for a time-bounded slice of a single migration's trail.

Triggers and functions (updated_at maintenance)

The template defines two plpgsql functions, each setting NEW.updated_at = NOW() and returning NEW:

• update_tenant_migrations_timestamp()
• update_tenant_routing_timestamp()

and two BEFORE UPDATE ... FOR EACH ROW triggers that call them:

• tenant_migrations_updated on tenant_migrations
• tenant_routing_updated on tenant_routing

Both triggers are dropped with DROP TRIGGER IF EXISTS before being created, so re-applying the template replaces them cleanly. Application code should not set updated_at on these two tables; the trigger overwrites it on every update.

migration_position_mappings and migration_audit_log have no triggers, since neither has an updated_at column.

Table and column COMMENT metadata

The template attaches COMMENT ON TABLE to all four tables and COMMENT ON COLUMN to the columns whose meaning is not obvious from the name — including every column of migration_audit_log except id and occurred_at, the identity/progress/config columns of tenant_migrations, all four non-timestamp columns of tenant_routing, and the mapping columns of migration_position_mappings.

These comments are queryable at runtime (\d+ in psql, or obj_description/col_description), which makes the database self-describing for operators inspecting a live system without the source tree at hand. They are part of the schema contract: the test suite asserts the presence of the table comments and of the documented column comments.

Which repository classes read and write each table

Each table has a Protocol defining the interface, in eventsource.ports.migration.repositories, and a PostgreSQL implementation in eventsource.adapters.sql.migration, constructed from a SQLAlchemy AsyncConnection or AsyncEngine, with optional OpenTelemetry tracing.

Table	Repository	Representative operations
tenant_migrations	PostgreSQLMigrationRepository (protocol MigrationRepository)	create, get, get_by_tenant, update_phase, update_progress, set_events_total, record_error, set_paused, list_active
tenant_routing	PostgreSQLTenantRoutingRepository (protocol TenantRoutingRepository)	get_or_default, set_migration_state; supports an optional process-local TTL cache (enable_cache, cache_ttl_seconds, default 5s)
migration_position_mappings	PostgreSQLPositionMappingRepository (protocol PositionMappingRepository)	create, create_batch, find_by_source_position, find_by_target_position, find_nearest_source_position, find_by_event_id, list_by_migration, list_in_source_range, count_by_migration, get_position_bounds, delete_by_migration
migration_audit_log	PostgreSQLMigrationAuditLogRepository (protocol MigrationAuditLogRepository)	record, get_by_migration, get_by_id, get_latest, count_by_migration

No repository writes to a table it does not own; cross-table consistency comes from the foreign keys and from the coordinator in eventsource.application.migration.coordinator.

The routing repository's cache is process-local with a short TTL by design. Multi-instance deployments see a bounded inconsistency window equal to that TTL after a routing change, which is why the default is only five seconds.

Retention, growth, and cleanup considerations

Growth is dominated by migration_position_mappings: in the worst case it holds one row per migrated event, so a tenant with tens of millions of events produces tens of millions of mappings. migration_audit_log grows with lifecycle events and progress_checkpoint entries, so its size scales with migration duration and checkpoint frequency, not with event count. tenant_migrations grows by one row per migration attempt and tenant_routing is bounded by tenant count — neither needs a retention policy.

Position mappings are only needed while checkpoints still have to be translated from source to target positions. Once every projection and subscription for the tenant has been repointed past the cutover, the mappings for that migration are dead weight; delete_by_migration on the position mapping repository removes them for a single migration without touching the migration row itself.

Audit log entries are retained for compliance, so prefer archiving to deleting. idx_migration_audit_occurred_at makes time-ranged export cheap. If you do prune, prune by occurred_at under your retention policy rather than by migration.

There is no automatic cleanup anywhere in the schema — no partitioning, no TTL, no scheduled job. Retention is entirely an operator responsibility.

Teardown and rollback notes

The template contains no DROP statements other than the two DROP TRIGGER IF EXISTS guards, so there is no supplied teardown script. Drop in reverse dependency order, children before tenant_migrations:

DROP TABLE IF EXISTS migration_audit_log;
DROP TABLE IF EXISTS migration_position_mappings;
DROP TABLE IF EXISTS tenant_routing;
DROP TABLE IF EXISTS tenant_migrations;
DROP FUNCTION IF EXISTS update_tenant_routing_timestamp();
DROP FUNCTION IF EXISTS update_tenant_migrations_timestamp();

Watch the two different delete behaviors. Deleting a tenant_migrations row cascades to its migration_position_mappings and migration_audit_log rows — including its audit trail, which is usually not what a compliance policy wants — while the tenant_routing reference is only set to NULL. Dropping the migration tables therefore destroys routing configuration too, since tenant_routing is itself one of them; if tenants have already been migrated, their store_id assignments must be preserved elsewhere before teardown.

Never drop these tables while any tenant sits in a non-normal migration_state. Finish or abort the migration first, so that routing settles on normal or migrated, then tear down. Because every statement in the template is idempotent, re-applying it after a partial teardown is safe and recreates whatever is missing.

Related documentation

• src/eventsource/adapters/sql/schemas/templates/migration.sql — the authoritative schema.
• src/eventsource/adapters/sql/schemas/SCHEMA_DESIGN.md — design notes for the schema templates.
• src/eventsource/migration/README.md — the migration system and its workflow.
• Repositories reference — checkpoint, outbox, and DLQ repositories.
• Multitenancy reference — tenant context and scoping.
• tests/unit/migrations/test_migration_schema.py — the assertions that pin every constraint, index, trigger, and comment described here.