Relabel-only Postgres migration: idempotent function, replay test inside a rolled-back transaction, seed compared in a throwaway DB

You need to rename user-facing labels stored in the database (object names, property labels, enum option labels in a jsonb array, pipeline stage names) on existing databases, and keep the seed file in sync for fresh databases. Internal values must not change, labels the user already edited by hand must survive, and the test suite runs against a shared live database, so a test that asserts live labels breaks as soon as someone edits one.

1) Put the renames in a plpgsql function (apply_x_labels() returns int) that the migration calls. Each UPDATE matches the OLD label (where label = old) and skips any row whose new label is already taken (and not exists (...)). The function returns the row count, so running it a second time returns 0. Revoke execute from the API roles. 2) For jsonb option arrays, write a pure, immutable SQL helper relabel_options(options, renames {value: [old, new]}, add, order[]). It renames an option only when the label still matches the old one, appends missing values, and orders the known values first and the rest after them in their original order. jsonb_agg(... order by coalesce(array_position(order, value), cardinality + ordinality)) does this. 3) The pgTAP test captures the current state, puts back the pre-migration labels inside the test transaction with set local session_replication_role = replica (this skips guard triggers, e.g. one that refuses removing an option a live segment cites), hand-edits a few labels, then calls the function. It asserts the exact row count, 0 on the second call, the new labels, the hand edits kept, the internal values unchanged, and the records and history untouched. Then it rolls back. 4) To check the seed: createdb a throwaway DB in the same container, load pg_dump -s -n auth (drop the trigger lines that reference app functions), apply every migration in order, load the seed, diff a label-dump query against the live DB, then dropdb. 5) Control cancellations: in the same psql session, run begin;, then the migration with one guard removed (create or replace), then the test file. The test's own rollback undoes everything.

A migration UPDATE without the old-label condition overwrites labels the user edited. Asserting the live state in tests breaks on the user's later edits, so replaying the migration on a reconstructed old state is the robust way to test it. pgTAP cannot load a seed file from the host, so the seed comparison has to run as a scripted throwaway DB.