Migrations

conta-command-postgresql ships a sequence of Flyway migrations under src/main/resources/migrations/. Each one is applied via the no.conta.archive-extract plugin into your project’s own migration directory — see Installing migrations for the wiring.

This page lists every migration the library currently ships, in order, with the rationale behind each one. When you upgrade the library and a new migration appears, add a corresponding rename (or skip) entry to the archiveExtract block — the build will fail until you do.

V001 — create_table_command

Creates the original command table with the full column set: identity, tenancy/user, command class/version, request/response state, problem details, jsonb cmd_body and attributes, timing, importance, retention.

The only index is the primary key on id. All other access patterns are unindexed at this point — that’s a property of V001, not a recommendation; later migrations add the indexes the read paths actually need.

V002 — rename_table_command_to_command_log

Renames command to command_log (and the PK constraint accordingly). The original name was too generic; command_log reads as what it actually is — an append-only audit log of executed commands.

Pure rename, no data movement.

V003 — create_helper_functions

Session-scoped setters and getters for common interactive query parameters: cmd_set_tenant_id, cmd_set_class_pattern, cmd_set_classes, cmd_set_user_id, cmd_set_uuid, cmd_set_since, plus cmd_set(…) for setting several at once and cmd_ts_bucket(ts, bucket) for time-bucketing.

Intended for ad-hoc psql / DataGrip exploration:

select cmd_set_tenant_id('_acme');
select cmd_set_classes('com.example.OrderCommand', 'com.example.PaymentCommand');
-- now use cmd_tenant_id() and cmd_classes() in your queries

set_config(…, false) makes the values persist for the session, not just the current transaction.

Skip this migration if you don’t want the helper functions on your global schema.

As shipped, V003 typed tenant_id as uuid in cmd_set_tenant_id, cmd_tenant_id, and cmd_set — but command_log.tenant_id is text, and Conta tenant ids are not uuids. V008 corrects this; see below. The rendered cmd_tenant_id and cmd_set blocks on the Example Queries page already show the corrected V008 definitions.

V004 — add_client_ref_to_command_log

Adds a free-form client_ref text column so callers can stamp a correlation ID (request ID, batch ID, etc.) onto the audit trail.

Two-statement design, both metadata-only on PostgreSQL:

  1. ADD COLUMN with no default and no NOT NULL — instant, just a catalog flip; existing rows get NULL by construction.

  2. ADD CONSTRAINT … CHECK (… or length(client_ref) ⇐ 512) NOT VALID — instant; the constraint is enforced for every new INSERT/UPDATE but not retroactively scanned.

Without NOT VALID, PostgreSQL would take ACCESS EXCLUSIVE and sequential-scan every row — a multi-minute write outage on a large table where every traced command is blocked from inserting.

V005 — add_indexes_to_command_log

Two indexes that turn the per-tenant activity-log read path from seq scans into index lookups:

  • UNIQUE INDEX ux_command_log_cmd_uuid (cmd_uuid) — supports findOne(tenantId, cmdUuid) and enforces uniqueness on the natural key.

  • INDEX ix_command_log_tenant_id_started_at (tenant_id, started_at DESC) — supports both shapes of the per-tenant activity feed: the before cursor (started_at < :cursor … LIMIT N) and the since polling cursor (started_at > :cursor).

DESC on started_at matches every caller’s natural ORDER BY, so the planner walks the index in storage order and skips the Sort node.

Both use CREATE INDEX CONCURRENTLY (companion .sql.conf disables Flyway’s transaction wrap) so the build does not block writes.

Configure Flyway with flyway.postgresql.transactional.lock=false before applying V005 (and V006/V007). The .sql.conf only stops Flyway from wrapping the migration itself in a transaction — but Flyway also takes a session-level coordination lock on a separate connection inside its own open transaction. That second transaction holds a snapshot for the entire migration run, and CREATE INDEX CONCURRENTLY waits for every older snapshot to finish before returning — so without this setting the migration deadlocks against Flyway itself and hangs forever.

See Indexes on command_log for the full picture.

V006 — add_global_query_indexes_to_command_log

Two indexes that cover the cross-tenant global-query path (support tooling, developer console, internal admin UIs):

  • INDEX ix_command_log_started_at (started_at DESC) — foundation for every time-windowed query that doesn’t have a sharper filter.

  • INDEX ix_command_log_attributes_gin USING GIN (attributes jsonb_path_ops) — the only structural way to make attributes @> '{…}' non-seq-scan.

These two compose with V005’s (tenant_id, started_at) via BitmapAnd for tenant-scoped attribute searches, and with each other for time-windowed attribute searches.

jsonb_path_ops is the right opclass when your application queries attributes only via the @> operator (the typical "match commands whose attributes contain this key/value subset" pattern). It is ~2× smaller than the default jsonb_ops and faster on inserts. If your application also uses ?, ?&, ?| against attributes, switch the opclass to jsonb_ops before applying.

cmd_body is deliberately not indexed — see Why some columns are deliberately not indexed.

V007 — add_conditional_query_indexes_to_command_log

Two optional indexes, gated on observed query patterns:

  • partial INDEX ix_command_log_high_started_at (started_at DESC) WHERE importance = 'High' — only worth it if a "show only High" view is frequent. High is typically ~15% of rows, so the partial is ~6× smaller than V006’s full (started_at).

  • INDEX ix_command_log_cmd_class_started_at (cmd_class, started_at DESC) — only worth it if cmd_class IN (…) filters against long-tail classes are common. Top-volume classes get little benefit because they’re not selective.

The migration’s preamble lists what to validate before applying (Query Insights / EXPLAIN ANALYZE evidence). If only one of the two patterns is confirmed in your application, comment out the other statement before running. Don’t ship indexes you don’t have evidence for — the insert cost is permanent.

V008 — fix_tenant_id_type_in_helper_functions

Corrects the tenant_id type bug in the V003 helpers.

command_log.tenant_id is text (V001) and Conta tenant ids have never been uuids, but V003 typed tenant_id as uuid in cmd_set_tenant_id, cmd_tenant_id, and cmd_set — a copy-paste from the adjacent cmd_uuid block where the type was never changed. Any call with a non-uuid tenant id failed with invalid input syntax for type uuid.

Because changing a function’s argument or return type cannot be done with CREATE OR REPLACE, this migration drops the three uuid-typed functions and recreates them with tenant_id text (casts removed):

drop function if exists cmd_set_tenant_id(uuid);
drop function if exists cmd_tenant_id();
drop function if exists cmd_set(uuid, text, text, uuid, timestamptz);

if exists keeps the migration safe for consumers who skipped V003. The recreate runs inside the normal Flyway transaction — no CREATE INDEX CONCURRENTLY, so no companion .sql.conf.

Adding new migrations

When you contribute a new migration to the library:

  1. Use the next sequential Vnnn__ number.

  2. If the migration touches command_log schema or large data, design it to be metadata-only or run with CREATE INDEX CONCURRENTLY / ALTER … NOT VALID so it doesn’t block writes.

  3. If it can’t run in a transaction, ship a companion Vnnn__….sql.conf with executeInTransaction=false. If the migration uses CREATE INDEX CONCURRENTLY, also call out that consumers must set flyway.postgresql.transactional.lock=false — the per-migration .sql.conf flag doesn’t cover Flyway’s coordination lock.

  4. Document it in this page with the rationale, not just the SQL.

  5. Update the example archiveExtract block in Installing migrations so consumers know they need to add a rename entry.