Runbook: the effective_until trigger and the 20.0.0 library

Author: Miguel Pinilla Last Verified: 2026-10-20 Environments: all (local, dev, stage, demo, prod)

This runbook covers the operational side of the effective_until machinery that common-module 20.0.0 brings to every bitemporal table and that operations adopts table by table with its bts_attach migrations. It answers four questions an operator meets around a deploy: why a fleet of pods on two library versions is safe, in which order a rollback happens, what dropping the trigger entails, and how a large table is refreshed online.

Everything below was read from the library’s shipped SQL at tag v20.0.0 (beforeMigrate__bts_effective_until.sql, contract version 1) and from the design it implements. Where a step is derived from the library’s own tests rather than from a procedure that has run on an environment, the step says so.

Purpose and Audience

A bitemporal table records every version of an entity as its own row (see Bitemporal Persistence). Until 20.0.0 the end of a version’s validity was computed by every reader; since 20.0.0 the database keeps it in a nullable column, effective_until:

• A BEFORE INSERT trigger, bts_effective_until, stamps the new row’s end from its successor and closes its predecessor’s end, inside the writer’s own transaction, under an advisory lock per lineage (one entity’s versions, within one tenant). Writers on an older library that do not name the column are stamped too, which is the whole point of keeping the stamp in the database.
• Two unique partial indexes make “one tip per lineage” (<table>_bts_tip_uq, on the rows whose effective_until is null) and “one genesis per entity” (<table>_bts_root_uq, on the rows whose previous is null) database facts. A third index, <table>_bts_pred_idx, serves the trigger’s lookups. A natural-key table has a fourth, <table>_bts_nk_uq.
• Every <table>_bts_* name in this runbook assumes a table name of 48 bytes or fewer (bytes, not characters).
  • For a longer name, bts_attach names the indexes from a hashed base, so the literal names match nothing.
  • Read the real names from the catalog (the Prerequisites’ _bts_ index read), or compute the base as in 4.3.
  • No table operations attaches today has a longer name.
• The functions are installed by a Flyway beforeMigrate callback that ships inside the library’s classpath and runs at the start of every migrate, before any versioned migration. They are CREATE OR REPLACE and idempotent, and they leave no row in the module’s Flyway history table (<module>_flyway_history in operations); the audit trail is the contract version, select bts_contract_version(), which returns 1.
• A table is attached by one versioned migration of its module that calls select bts_attach('<table>'): the column (typed like effective_as_of), the trigger, the backfill of every existing row in effective-time order, and the indexes, in one statement.

This runbook is addressed to the operator who deploys or rolls back an operations release that pins common-module 20.0.0 or later. It assumes the operator can run SQL against a module database with the credentials the deploy pipeline uses, and can read a pod’s log.

Prerequisites

• Access required: the module database of the affected table, with the application role the pipeline uses (no credential is printed or copied by any step here); kubectl on the partition’s cluster for the pods’ logs.
• Tools required: psql; kubectl.
• Production: any read against production goes through the route of Controlled Production Reads, returns counts only, and leaves no result outside custody. Restoring a production snapshot locally is a separate act that needs the owner’s word.
• Know the window’s shape before a deploy that attaches tables: each bts_attach migration holds the table’s ACCESS EXCLUSIVE from its ADD COLUMN to its module’s group commit, so the table’s reads and writes wait for the whole group, and a request that waits past the gateway’s timeout fails while a write still commits. The rehearsal’s per-migration durations give the windows; on the first attach of every table (operations 15.1.0, production, 2026-10-20) the longest was item_supply’s, 25.7 s, and two sampled requests waited 21 s and answered.
• Know the library’s state before acting. Three reads, all harmless:

-- the contract version the callback installed (1 for 20.0.0)
select bts_contract_version();

-- the tables attached in this database
select tgrelid::regclass as attached_table, tgenabled
from pg_trigger
where tgname = 'bts_effective_until';

-- the machinery's indexes and whether each is valid
select indexrelid::regclass as index_name, indisvalid
from pg_index
where indexrelid::regclass::text like '%\_bts\_%';

Expected output: 1; one row per attached table with tgenabled = 'O' (enabled); every index with indisvalid = true.

---

Step 1 — A Rolling Deploy with a Mixed Fleet Is Safe for Stamping

During a rolling deploy, pods on the previous library version and pods on the new one write to the same tables for a while. Nothing is required of the operator for the stamps, for these reasons; the one thing the mix changes is in the table below (the previous release’s handling of a lost race):

• The stamp lives in the database, not in the library. A pod on a library older than 20.0.0 inserts a version without naming effective_until; the trigger stamps it exactly as it stamps a row from a 20.0.0 pod. No row is left unstamped by the mix.
• The callback runs at every migrate. The first pod of the new release replaces the functions for every pod, the older ones included, while their writes continue. Within one contract version a replacement only changes behavior backwards-compatibly; an incompatible change is delivered as a new function name kept beside the old one for one release. So a body of release n+1 runs correctly against the trigger arguments release n attached.
• Two pods starting at once do not race the install. The callback runs before Flyway’s own schema-history lock, and serializes itself for the length of its transaction. A tuple concurrently updated error during a start is the race being refused; the pod’s next start succeeds.

What to watch in the pods’ logs during the rollout:

Message	Meaning	Action
effective_until: stale anchor (tenant …, eid …), SQLSTATE 40001	A writer’s predecessor was not the one seen under the lineage lock: a lost race on the lineage, raised by the trigger	None on a pod of the new release: the 20.0.0 library retries it under the policy the call passes, and an unretried one surfaces as a 409 conflict. On a pod of the previous release, during the rollout, the same 40001 is unmapped: that one request fails with a server error, nothing of it is written, and the winner’s row stands; a listener of the previous release drops its write with a WARN. Only the loser of a race on one lineage is affected; the window ends with the last old pod
effective_until: concurrent stamp on predecessor (tenant …, eid …), 40001	The compare-and-set on the predecessor’s end found a concurrent stamp	None, as above: the new release retries or answers 409; the previous release’s pod fails that one request
23505 on <table>_bts_tip_uq or <table>_bts_root_uq	A lost race surfacing on the one-tip or one-genesis index	None: the caller receives a conflict. A sustained stream of them on one table after a trigger was dropped is Step 3’s symptom
function bts_attach does not exist during a migrate	The callback did not run before the versioned migration: the library’s migration location is missing from the module’s Flyway locations	Stop; this is a build defect of the release, not an operational step

Expected output: the deploy completes with the reads of the Prerequisites unchanged, and the census of the Verification section at zero.

Step 2 — Rollback Order: the Library First; the Trigger, Column and Indexes Stay

A rollback of an operations release that brought bts_attach migrations is a rollback of the application only, and it is possible only while no module’s migrations have committed. The database keeps the machinery.

2.1 Roll the deployment back

Every pod of the release is a migration runner.

• Each module’s Flyway migration runs at the module’s start, inside every application pod (each module’s newDb with its Flyway configuration).
• The deployment rolls its pods (RollingUpdate).
• So “no module’s group has committed” holds only while no pod of the new release can start. A new-release pod that restarts while the rollback is underway can commit its group in the middle of it.
• The Incident Response Runbook records that race, in its post-rollback Flyway validation sub-diagnosis: a pod of the newer release restarted during helm rollback and applied its migrations, and the rolled-back pods then crash-looped on validation.
• The migration runners are the application’s own pods, so stopping them stops the whole component: the steps below are an outage of operations until step 7.
• The kubectl steps are derived, not rehearsed: rehearse them on dev before relying on them.

1. Record the deployment’s replica count and the autoscaler’s state: kubectl -n <namespace> get deployment/operations hpa/operations (where the HPA is enabled; elsewhere get deployment/operations alone). Production runs with the HPA enabled, and the chart then omits the deployment’s replicas, so a redeploy does not restore the count. Step 7 restores it explicitly.

2. Stop every pod: kubectl -n <namespace> scale deployment/operations --replicas=0.

3. Check that the stop holds:

   • kubectl -n <namespace> get pods -l app=operations lists no pod;
   • the deployment’s replica count stays 0;
   • kubectl -n <namespace> describe hpa/operations shows that the autoscaler is not scaling it back up.

   A migration that was running when its pod stopped is rolled back with its connection when the module’s group runs in one transaction, as every group of this release does.

   • Flyway runs a module’s pending migrations as one group, and in one transaction only if every migration in it can (group and mixed, common-module DbMigration.kt:74-75).
   • A group that holds an executeInTransaction=false migration is not transactional, and it keeps whatever it had already done.

4. Check that no session of the application remains. In each module database, with the application role:

   select count(*) from pg_stat_activity where usename = current_user and pid <> pg_backend_pid();

   Expected output: 0.

5. Read every module’s migration history.

   • Find the history tables (<module>_flyway_history in operations):

     select schemaname, tablename from pg_tables where tablename like '%\_flyway\_history';

   • Count the versioned migrations that the new release adds (its V…__*.sql files; the version as in the file name, V015__… is '015'):

     select count(*) from <schema>.<module>_flyway_history where version in ('<version>', …);

   Expected output: 0 in every module, to roll back. A row installed after the deploy started (installed_on) is a second signal, not the authority: it compares the database’s clock with the pipeline’s.

6. Roll back, if every count is 0: run the deploy pipeline with the previous release’s chart version (deploy.yaml, a helm upgrade). Then confirm that the deployment’s image is the previous release’s (kubectl -n <namespace> get deployment/operations -o jsonpath='{.spec.template.spec.containers[*].image}').

7. Only then, scale back to the count recorded in 1: kubectl -n <namespace> scale deployment/operations --replicas=<count>. Scaling up before 6’s image check would start pods of the new release, and they would migrate.

Nothing in the database is touched by a rollback that passes 5.

If any count in 5 is not 0, a module’s group has committed: the way back is forward. Deploy the new release, or a fix release, and then scale back as in 7. Once a module’s group has committed, a pod of the previous release cannot start against that database: Flyway refuses an applied migration the jar does not resolve (the library ignores missing migrations only), and the rollback pod crash-loops with Detected applied migration not resolved locally. The pods of the previous release that are still running keep serving until they stop. The way back is then forward, a fix release, or the pre-deploy snapshot restored, which loses every write since it; keep that snapshot until the release is verified.

Expected output (before any commit): the previous pods serve; their writes continue. A rolled-back library does not name effective_until. What a table holds depends on which release attached it:

• A table attached by an earlier release keeps its column, trigger and indexes (2.2), and the trigger stamps its rows as in Step 1.
• A table whose first attach was in the rolled-back release has none of the machinery: a rollback that passes 5 means no migration of the release is recorded as applied, and in a transactional group (every group of this release, step 3) none of it committed; bts_attach creates the column, the trigger and the indexes inside that migration’s transaction (beforeMigrate__bts_effective_until.sql:200-224, v20.0.0), so the table is as it was before the release, unattached and without an effective_until column. It is attached again by the release that goes forward.

Tell the two apart per table with the trigger read, which answers 1 or 0 and no identifier (to_regclass answers null, so the count is 0, when the rolled-back release created the table itself; a ::regclass cast would raise instead):

select count(*) from pg_trigger where tgname = 'bts_effective_until' and tgrelid = to_regclass('<schema>.<table>');

2.2 Leave the trigger, the column and the indexes in place

Do not drop the trigger, the column or the indexes as part of a rollback. They are harmless to the previous library, and dropping the trigger has consequences of its own (Step 3). The current reads of the rolled-back version are the coordinate reads it always used; 20.0.0 adds the tip-path reads (readCurrent, listCurrent, countCurrent) beside them, so neither version is left without a read path.

2.3 Verify

Run the Verification section on the tables the trigger read of 2.1 answers 1 for: the census stays at zero and the indexes stay valid; nothing else changes. A table the read answers 0 for (a first attach rolled back with its release) has no effective_until column, so the census would fail on it with a missing column: skip it, and expect it attached when the release goes forward.

Step 3 — Dropping the Trigger Is a Deliberate Step, and Re-adoption Requires a Refresh

Dropping the trigger is never part of a rollback. It is a deliberate, separate step for the case where the trigger itself must be removed from a table, and it has a cost: while it is off, new rows arrive with a null end and their predecessors keep their old ends, so the table’s stamps go stale and its one-tip index can no longer hold. The procedure below is derived from the library’s own re-adoption test (T-79 in EffectiveUntilLifecycleTest) and from bts_attach’s contract; rehearse it on a locally restored snapshot before running it on an environment.

3.1 Lift the one-tip index, and on a natural-key table the natural-key index, then drop the trigger

The one-tip index refuses a second open-ended version in a lineage, which is exactly what every write produces once the trigger is off. The natural-key index does the same for a natural-key table: its predicate, where effective_until is null and not retired, covers both the unstamped predecessor and the non-retired successor that carries the same key, so an ordinary append would fail with 23505. Both indexes come off first.

The index names below assume a table name of 48 bytes or fewer (the naming rule under Purpose and Audience). For a longer name, use the names the catalog gives: drop index if exists with the literal name drops nothing, and the hashed one-tip index would stay while the trigger goes.

drop index if exists <schema>.<table>_bts_tip_uq;
-- natural-key tables only
drop index if exists <schema>.<table>_bts_nk_uq;
drop trigger if exists bts_effective_until on <schema>.<table>;

Expected output: the trigger row is gone from pg_trigger; <table>_bts_tip_uq (and <table>_bts_nk_uq on a natural-key table) is gone from pg_index. The predecessor index and the one-genesis index stay.

3.2 While the trigger is off

• Writes to the table succeed, and leave stale stamps: the newest version of a lineage and its predecessor both carry a null end.
• The tip-path reads of 20.0.0 (readCurrent, listCurrent, countCurrent) are not trustworthy on this table while it is in this state, because the tip is no longer unique. Callers that read “current” through a coordinate are unaffected.
• Record the window (first and last write without the trigger) for the re-adoption’s verification.
• On a natural-key table, the natural-key index is off too (3.1), so two entities can take the same live key: a cross-lineage collision that no refresh undoes, checked in 3.3 before the re-attach.

3.3 Re-adopt by attaching again

bts_attach is idempotent. On a table that is already attached it keeps the column, drops and re-creates the trigger, runs the whole-table refresh that repairs every stale stamp, and re-creates the indexes that are missing. The refresh runs before the index, which is why re-adoption needs no separate step: a one-tip index built over unrepaired stamps would fail on the first lineage with two open ends.

On a natural-key table, check for key collisions first. While the natural-key index was off, two entities (two lineages) can have taken the same live key. The refresh repairs stamps within a lineage only (bts_refresh_effective_until’s window partitions by the lineage, beforeMigrate__bts_effective_until.sql:127, the key chosen at :136, v20.0.0), and both censuses of the Verification section group by lineage too, so they read 0 while the key is held twice; the re-attach then fails with 23505 on <table>_bts_nk_uq (:220-223), and the whole statement rolls back. The check derives each lineage’s current tip from the version order the trigger uses, not from the stamps, which are stale while the trigger is off; it answers one number and no identifier:

-- natural-key tables only; expected 0. <nk lead> is `tenant_id, ` on a tenant-scoped table and nothing otherwise (4.3)
select count(*) from (
  select <nk lead><natural key columns>
  from (
    select distinct on (tenant_id, eid) tenant_id, eid, <natural key columns>, retired
    from <schema>.<table>
    order by tenant_id, eid, effective_as_of desc, recorded_as_of desc, bts_seq desc
  ) tips
  where not retired
  group by <nk lead><natural key columns>
  having count(*) > 1
) collisions;

On a table without tenant_id, remove it from the distinct on, the order by and the select list; on an occurrence table, add where <version_column> = 'COMMITTED' to the inner select. If the count is not 0, stop: do not re-attach, and escalate to the component’s owner with the table and the count. Which entity keeps the key is a data decision, and this runbook has no procedure for it.

Two limits of the check: a key column that is null groups with the other nulls here, while the unique index never refuses two nulls, so such rows can count where the index would not; and a key declared as an expression needs that expression in the select and the group by in place of its columns. No operations table declares a natural key today (the first come with operations#R02-16), so neither limit is met yet.

select bts_attach('<schema>.<table>');

For an occurrence table, pass its version column as the second argument, and for a natural-key table its key as the third, exactly as the table’s original attach migration did (select bts_attach('<table>', '<version_column>', '<natural key columns>'), null where the table has none).

Expected output: pg_trigger shows bts_effective_until enabled on the table (tgenabled = 'O'); <table>_bts_tip_uq is back and valid, and so is <table>_bts_nk_uq on a natural-key table (the refresh runs before the index builds, so neither build meets a stale stamp); the census of the Verification section is zero.

3.4 Large tables

The refresh inside bts_attach runs over the whole table under the statement’s lock, and the library (contract 1, v20.0.0) has no attach form that skips it: create_indexes = false defers the indexes only. The statement’s lock is the ACCESS EXCLUSIVE its add column if not exists and drop trigger take, held to the migration’s commit, so the table’s reads wait as well as its writes. A table whose refresh would take longer than the outage the environment tolerates is re-adopted in a maintenance window sized by a rehearsal on a copy of the environment (operations 15.1.0 attached every table this way; the longest production window was 25.7 s, item_supply’s, held from its ADD COLUMN across its module’s group to the commit, as the Prerequisites say; the longest single attach was 21.2 s, on item). The index builds can be deferred and run concurrently afterwards (Step 4); the backfill cannot.

Step 4 — Deferred Indexes and the Per-Lineage Refresh

Operations attaches every table with the small-table form, select bts_attach('<table>') (an occurrence table passes its version column, a natural-key table its key), on the row counts measured before the release. The library has no online attach. bts_attach always runs the whole-table backfill inside its statement, under the table’s lock; create_indexes = false defers only the indexes, so the index builds can leave the window (4.1, 4.3) and the backfill cannot; the window is an outage of the table’s reads and writes, under the ACCESS EXCLUSIVE the statement’s add column and drop trigger hold to the commit. The per-lineage refresh (4.2) repairs stale stamps after Step 3.2, or a lineage the census names; it is not a substitute for the backfill. The properties are the library’s: the per-lineage refresh is idempotent and resumable (safe to repeat, safe to interrupt), and a concurrent index build fails safe (an invalid index to drop).

4.1 Attach with the indexes deferred

One versioned migration of the module, transactional, with the same version column and natural key as the table’s original attach migration (null where the table has none):

Transactional only in a Flyway run of its own. A module’s pending migrations run as one group, and the group runs in one transaction only if every migration in it can (group(true) and mixed(true), common-module DbMigration.kt:74-75, v20.0.0; 2.1). So 4.3’s migration, which is executeInTransaction=false, must not be pending in the same migrate run as this one: a separate file is not enough. Ship 4.3 in a later release, after 4.2 has completed on every environment, so that on each environment this migration commits in a run that holds no non-transactional migration. Where both are pending at once (an environment that never ran the release of this migration, or a fresh database), the whole group runs without a transaction (Flyway 11.4.1, DbMigrate.java:278-287): each migration that completes is recorded as applied at once (:419), and a migration that fails gets a history row with success = false (:297-303). So if 4.3’s build then fails, this attach stays applied and recorded, 4.3’s migration is recorded as failed, and every later migrate refuses with “contains a failed migration to version …” (:220-232; the library ignores *:missing patterns only, DbMigration.kt:78): the module’s pods do not start until that row is removed (4.3’s failure path). The way back is forward, as in Step 2, once the row is dealt with.

select bts_attach('<schema>.<table>', '<version_column>', '<natural key columns>', false);

create_indexes = false defers the indexes: the three of every table and, on a natural-key table, <table>_bts_nk_uq. The column, the trigger and the whole-table backfill still run inside this statement, under the table’s lock, so this form shortens the window by the index builds only; the backfill is the part the rehearsal measures against the threshold.

create_indexes = false only skips the builds; it removes no index (bts_attach builds its indexes only when create_indexes is true and drops none: beforeMigrate__bts_effective_until.sql:216-224, v20.0.0). So what is left depends on which attach this is.

Expected output: the trigger is enabled on the table (tgenabled = 'O'), and then:

• On a first attach: no <table>_bts_* index exists yet.
• On a re-adoption after Step 3 (a large table that 3.4 sends here): <table>_bts_pred_idx and <table>_bts_root_uq are still present and valid, because Step 3.1 kept them. <table>_bts_tip_uq, and on a natural-key table <table>_bts_nk_uq, are absent until 4.3, which skips the indexes that already exist (if not exists).

4.2 Refresh lineage by lineage

For a table whose stamps are known to be stale (rows written while the trigger was off, Step 3.2) or a lineage the census names, refresh in batches, one lineage per call, from an operator session or a maintenance job. An interrupted bts_attach leaves nothing to resume: the select bts_attach(…) statement rolls back whole either way, as one statement. What its group keeps differs: in a transactional group (4.1, in a run of its own) the whole group is rolled back and nothing is recorded; in a non-transactional group (4.3’s migration pending in the same run) the migrations that completed before it stay applied and recorded; one that fails with an error gets a success = false history row, and every later migrate refuses until that row is removed (4.1, 4.3); one cut off with its process (a killed pod: an OOM kill, a rollout kill) leaves no row, since Flyway writes that row only when it catches the error (DbMigrate.java:289-303), and runs again at the next start, which is safe because bts_attach is idempotent (the case operations’ V008__inventory_pool_item_index.sql:48-58 records).

-- one lineage: takes that lineage's lock for the statement, as the trigger does
select bts_refresh_effective_until('<schema>.<table>', '<eid>');

-- the whole table in one statement, where its duration is acceptable
select bts_refresh_effective_until('<schema>.<table>');

Each call is safe to repeat and safe to interrupt: a batch run again changes no row, and the batches run apart give the whole-table result. The throughput to expect per environment is measured in the release’s rehearsal, not assumed.

Expected output: after the last batch, the census of the Verification section is zero for the table.

4.3 Build the indexes concurrently

One non-transactional migration of the module (executeInTransaction=false), in a release of its own after 4.2 has completed on every environment (4.1: pending in the same run as the attach, it would turn that run’s whole group non-transactional), with the definitions bts_attach would have used. The placeholders follow the library’s own definitions (v20.0.0, beforeMigrate__bts_effective_until.sql:217-222):

• <lead> is tenant_id, eid on a tenant-scoped table, and eid alone otherwise.
• <nk lead> is tenant_id, (with its comma) on a tenant-scoped table, and nothing otherwise.
• An occurrence table adds and <version_column> = 'COMMITTED' to the predicate of every unique index: the one-tip, the one-genesis and the natural-key index.
• <table>_bts_* holds only for a table name of 48 bytes or fewer (bytes, not characters; the naming rule under Purpose and Audience).

  • Longer names: bts_attach names the indexes from a hashed base instead: the longest leading run of the name that fits in 39 bytes, then _ and the first 8 hex digits of the name’s md5 (beforeMigrate__bts_effective_until.sql:184-187, v20.0.0).

  • Why it matters: with the literal name, if not exists would build a second index beside the existing one.

  • To get the real names: read them from the catalog, for example with the Prerequisites’ _bts_ index read, or for one table:

    select indexname from pg_indexes where schemaname = '<schema>' and tablename = '<table>' and indexname like '%\_bts\_%';

    Or compute the base the library uses:

    select case when octet_length('<table>') > 48
                then (select left('<table>', k) from generate_series(0, length('<table>')) k
                      where octet_length(left('<table>', k)) <= 39 order by k desc limit 1) || '_' || left(md5('<table>'), 8)
                else '<table>' end as base;

  • None of the tables operations attaches today is affected: the 20 bts_attach calls in its migrations name 19 tables, and the longest name is 19 bytes (operations 15.1.3).

create index concurrently if not exists <table>_bts_pred_idx
  on <schema>.<table> (eid, effective_as_of, recorded_as_of, bts_seq);
create unique index concurrently if not exists <table>_bts_tip_uq
  on <schema>.<table> (<lead>) where effective_until is null;
create unique index concurrently if not exists <table>_bts_root_uq
  on <schema>.<table> (<lead>) where previous is null;

-- natural-key tables only: the key's columns as declared, after <nk lead> (tenant_id first only on a tenant-scoped table)
create unique index concurrently if not exists <table>_bts_nk_uq
  on <schema>.<table> (<nk lead><natural key columns>) where effective_until is null and not retired;

A concurrent build that fails leaves an invalid index behind (indisvalid = false in the Prerequisites’ read). Which index failed tells the cause, and the cause decides the repair:

It also leaves a failed history row, and that row stops the module. This migration is non-transactional, so a build that fails is recorded in the module’s history table with success = false (Flyway 11.4.1, DbMigrate.java:297-303), and every later migrate, which is every pod’s start, refuses with “contains a failed migration to version …” (:220-232; DbMigration.kt:78 ignores *:missing patterns only). The module’s pods do not start until that row is removed; the knowledge base records the same mechanism for the bts_seq migrations (operations knowledge-base/bts-seq-migrations.md). “Build again” below therefore means: the row removed first, then the next migrate runs this migration again, and if not exists skips the indexes that did build. A build cut off with its process instead (a killed pod) leaves the invalid index and no row: the migration runs again at the next start, its if not exists matches the invalid index by name and skips it, and the migration completes with the index still invalid, silently (the hazard V008__inventory_pool_item_index.sql:48-58 guards with a drop before its create). The Prerequisites’ validity read is what finds it: drop the index and build it with 4.3’s statement by hand after diagnosing its cause as below, since the recorded migration does not run again; a root or natural-key index over a fork or a collision would fail again. How the row is removed is not this runbook’s to say: a Flyway repair (flyway repair, or the row’s deletion by hand) is a change to the migration history, a deploy-time act that belongs to whoever owns the deploy (knowledge-base/flyway-checksum-covers-the-file-not-the-schema.md), and no procedure for it is documented for operations. Stop and escalate to the component’s owner with the module, the failed version and the index, as for a genesis fork.

• <table>_bts_tip_uq: a lineage still carries two open ends. These are stale stamps, which the Verification’s effective-tip census counts. Drop the index, repair the lineages by 4.2, and build again.

• <table>_bts_root_uq: an entity has two roots, two versions whose previous is null. This is a genesis fork, which the Verification’s genesis census counts.

  • A refresh cannot repair it: bts_refresh_effective_until writes only effective_until and never previous (beforeMigrate__bts_effective_until.sql:107-140, v20.0.0). So dropping, refreshing and rebuilding fails the same way every time.
  • Stop: drop the invalid index, and do not build it again while the genesis census is above zero.
  • Escalate to the component’s owner with the table and the census count. Repairing a lineage’s roots changes which version an entity starts from, which is a data decision, and this runbook has no procedure for it.

• <table>_bts_nk_uq: two lineages hold the same live key, a collision neither census counts and no refresh repairs (3.3’s check reads it). Stop: drop the invalid index, do not build it again while 3.3’s collision count is above zero, and escalate as for the one-genesis index.

Expected output: the three indexes present and valid; four on a natural-key table.

Verification

Run these after any step above, on the environment’s database. On production, only the counts-only forms, through the controlled-reads route: they return one number and no identifier.

-- effective-tip forks: lineages with two open ends; expected 0
select count(*) from (
  select 1 from <schema>.<table>
  where effective_until is null
  group by tenant_id, eid
  having count(*) > 1
) forks;

-- genesis forks: entities with two roots; expected 0
select count(*) from (
  select 1 from <schema>.<table>
  where previous is null
  group by tenant_id, eid
  having count(*) > 1
) forks;

On dev, stage and demo, where the lineages to repair may be named, the row forms replace select 1 by select tenant_id, eid, count(*) and drop the outer count(*); the expected result is no row.

On a table without tenant_id, remove it from the group by of both forms and from the row form’s select list; on an occurrence table, add and <version_column> = 'COMMITTED' to both where clauses. Then the three reads of the Prerequisites: contract version 1, the trigger enabled, every _bts_ index present and valid.

• Both census queries return 0 (the counts-only forms) or no row (the row forms) for every attached table touched by the step.
• select bts_contract_version() returns 1.
• Every attached table has bts_effective_until enabled.
• Every attached table has _bts_pred_idx, _bts_tip_uq and _bts_root_uq, a natural-key table also _bts_nk_uq, and every one of them is valid.

Rollback

• Of Step 2: a rollback is itself the step while nothing has committed; after a commit there is no rollback of the application, only a fix release or the snapshot (2.1). Redeploying the newer release afterwards needs nothing from the database.
• Of Step 3: the re-adoption of 3.3 is the rollback of a dropped trigger; there is no state in which the trigger is off and the table is trustworthy for tip-path reads.
• Of Step 4: an interrupted per-lineage refresh is resumed, not undone (4.2); an interrupted attach rolled back whole as one statement, and with its whole group only when that group was transactional (4.1); a migration that failed in a non-transactional group leaves a success = false history row that every later migrate refuses until it is removed (4.3); a failed concurrent index build is dropped, and rebuilt once its cause is repaired and its failed history row is removed by whoever owns the deploy: a genesis fork is escalated, not refreshed (4.3).

Troubleshooting

Symptom	Likely Cause	Resolution
The census finds lineages with two open ends	Rows written while the trigger was off or disabled (Step 3), or an interrupted backfill	select bts_refresh_effective_until('<table>'), or per lineage in batches (4.2); then verify
The census finds entities with two roots	A genesis fork: two versions of one entity with a null previous. Not a stale stamp	No refresh repairs it (4.3). Do not build <table>_bts_root_uq; escalate to the component’s owner with the table and the count
23505 on <table>_bts_tip_uq for many writers at once	The trigger is off or disabled while the index is in place	Check pg_trigger; re-attach (3.3)
tuple concurrently updated at a pod’s start	Two pods ran the callback at the same instant	None; the next start succeeds
function bts_attach does not exist in a migrate	The library’s migration location missing from the module’s Flyway locations	A build defect of the release; stop and report
55P03 (lock_not_available) on an insert	A writer held a lineage’s lock past the session’s lock_timeout	Transient; the caller’s retry policy decides. A sustained case names a long transaction to find
bts_attach: … has an effective_until of type …, expected … or … declared NOT NULL	A hand-made effective_until column that the machinery cannot write	Fix the column to match effective_as_of’s type, nullable; attach again
<table>_bts_tip_uq with indisvalid = false	A failed create index concurrently over stale stamps	Drop it, repair the lineages the effective-tip census names (4.2); the failed history row is removed by whoever owns the deploy, then the next migrate builds again (4.3)
<table>_bts_root_uq with indisvalid = false	A failed build over a genesis fork	Drop it; do not rebuild while the genesis census is above zero; escalate, with the failed history row that stops the module (4.3)
The module’s pods do not start after a 4.3 deploy: “contains a failed migration to version …“	4.3’s non-transactional migration failed and left a success = false history row (Flyway 11.4.1, DbMigrate.java:297-303, :220-232)	Stop and escalate: the row’s removal is a change to the migration history, whoever owns the deploy makes it; the invalid index is diagnosed as in 4.3

Related

• Controlled Production Reads — the only route for a read against production.
• Bitemporal Persistence — the versioning model the trigger serves.
• Bitemporal composite indexes — the latest-version access pattern the one-tip index completes.
• The Occurrence Pattern — why an occurrence table passes its version column to bts_attach.

Change History

Date	Author	Change
2026-10-20	Miguel Pinilla	Initial version, written against common-module v20.0.0 (bts contract 1)
2026-10-21	Miguel Pinilla	After Copilot’s review: the deferred-indexes attach carries the version column and the natural key, 4.3 builds the natural-key index, the library has no online attach (a large table is a rehearsed window), counts-only census forms for production
2026-10-21	Miguel Pinilla	After Copilot’s review (r4333988577): the rollback first stops every pod of the release, because each one runs migrations at start, as in the incident-response runbook’s Helm rollback race. It then checks that the stop holds, that no session remains and that no module’s migration history has the release’s versions, and scales back only after the rollback’s image is confirmed. The Flyway history table is named per module
2026-10-21	Miguel Pinilla	After Copilot’s review: a failed index build is diagnosed by which index failed. A one-tip index is repaired by the refresh. A one-genesis index over a genesis fork is not: the refresh never writes previous, so the operator stops and escalates instead of cycling drop, refresh and rebuild
2026-10-21	Miguel Pinilla	After Copilot’s review: 4.1’s expected output tells a first attach (no index) from a re-adoption after Step 3 (the predecessor and one-genesis indexes kept by 3.1 remain), since create_indexes = false skips builds and removes nothing
2026-10-21	Miguel Pinilla	After Copilot’s review: 4.3’s natural-key index leads with <nk lead>, tenant_id only on a tenant-scoped table, as the library builds it. An occurrence table’s version clause applies to all three unique indexes, the natural-key index included
2026-10-21	Miguel Pinilla	4.3 says the literal <table>_bts_* names hold only for table names of 48 bytes or fewer, since bts_attach names longer ones from a hashed base. It gives the catalog read and the base’s expression, and notes that no table operations attaches today is affected (the longest is 19 bytes)
2026-10-21	Miguel Pinilla	The 48-byte naming rule is stated once, where the index names are introduced, for every <table>_bts_* name in the runbook. 3.1’s drops and 4.3 point to it: on a longer name a literal drop index if exists drops nothing, and the hashed one-tip index would stay while the trigger goes
2026-10-22	Miguel Pinilla	After Copilot’s review (5607746816): 2.1’s expected output and 2.3 tell a table attached by an earlier release from one whose first attach was in the rolled-back release, which has none of the machinery after a rollback that passes 5; the census runs only where the trigger read answers 1
2026-10-22	Miguel Pinilla	After Copilot’s review (5607746816): on a natural-key table, 3.3 checks for current-tip key collisions across lineages before the re-attach, with a counts-only read that derives each lineage’s tip from the version order and a stop-and-escalate path; 3.2 names the hazard and 4.3 diagnoses a failed _bts_nk_uq build the same way
2026-10-22	Miguel Pinilla	After Copilot’s review (5607746816): 4.1’s attach group is transactional only in a Flyway run that holds no non-transactional migration (group(true), mixed(true), DbMigration.kt:74-75), so 4.3’s migration ships in a later release, after 4.2; 4.2 and 4.3 say so; the mixed-group case is the way forward
2026-10-22	Miguel Pinilla	After SPEARHEAD’s check of round 4 (workbooks 76fdf9eed, F1 and F2): in a non-transactional group Flyway records each completed migration as applied and a failed one with success = false, and every later migrate refuses until that row is removed (DbMigrate.java:278-287, :297-303, :419, :220-232, 11.4.1); 4.1, 4.2 and the Rollback section say so, and a single bts_attach statement rolls back whole either way
2026-10-22	Miguel Pinilla	After SPEARHEAD’s check of round 4 (76fdf9eed, F3): 4.3’s failed build also leaves a success = false history row that stops every later migrate, so the module’s pods do not start until it is removed; the removal is a change to the migration history that belongs to whoever owns the deploy, with no procedure documented for operations: stop and escalate. 4.3’s failure path, two Troubleshooting rows and a new one, the Rollback line
2026-10-22	Miguel Pinilla	After SPEARHEAD’s check of round 4 (76fdf9eed, P1 to P4): 2.1’s trigger read uses to_regclass, which answers 0 where a ::regclass cast would raise; 2.1’s “never committed” is said of a transactional group; 3.3’s collision check names its null-key and expression-key limits (no operations table declares a natural key today); the refresh is cited at :127 and :136, the natural-key index at :220-223
2026-10-22	Miguel Pinilla	After SPEARHEAD’s re-check (workbooks 7c2d5b6b8, F4): the success = false row is written only for a migration that fails with an error (DbMigrate.java:289-303); one cut off with its process leaves no row and runs again at the next start, safely for bts_attach (idempotent) and silently for a concurrent build, whose rerun’s if not exists skips the invalid index it left (operations V008__inventory_pool_item_index.sql:48-58); 4.2 and 4.3 say so