v1.5.0 migration runbook

Audience: operators upgrading an existing Sentry WMS install from v1.4.x to v1.5.0.

Scope: the order migrations 020-024 plus 025 should be applied, the lock windows to expect on each, and the alternative two-step pattern for the external_id backfill on multi-million-row production databases. The runbook assumes a Compose-based deployment; swap the commands accordingly for a bare-metal Postgres.

What changes at the schema level

Migration	Summary	Lock profile (apartment-lab)	Lock profile (large tables)
020	Creates `integration_events` + four indexes + deferred `visible_at` trigger. Adds `external_id UUID UNIQUE NOT NULL DEFAULT gen_random_uuid()` to ten aggregate / actor tables.	Sub-second per table.	Per-table `ACCESS EXCLUSIVE` while the DEFAULT backfills every row. Proportional to row count. See two-step pattern below.
021	Creates `connectors` + `consumer_groups`.	Sub-second.	Sub-second (new tables).
022	Adds `credential_type VARCHAR(32) NOT NULL DEFAULT 'connector_api_key'` to `connector_credentials`.	Sub-second.	Sub-second (small table).
023	Creates `wms_tokens` + `wms_tokens_status_rotated` index.	Sub-second.	Sub-second (new table).
024	Creates `snapshot_scans` + `snapshot_scans_status_started` index + `notify_snapshot_scans_pending()` function + `tr_snapshot_scans_notify` trigger.	Sub-second.	Sub-second (new table).
025	Drops the `external_id` DEFAULT on the ten retrofitted tables.	Sub-second per `ALTER TABLE`.	Sub-second (metadata only).

The only migration that carries a non-trivial lock window on large tables is 020. Everything else creates new tables or touches small metadata tables.

Applying the migrations

Apartment-lab (default compose)

git fetch --tags
git checkout v1.5.0
docker compose down
docker compose build
docker compose up -d

db/seed.sh runs db/schema.sql on a fresh volume. For a fresh install this is the full v1.5.0 schema; no separate migration step is needed.

For an upgrade on an existing volume, run the migrations in numeric order:

for m in 020 021 022 023 024 025; do
  docker compose exec db psql -U sentry -d sentry \
    -f /db/migrations/${m}_*.sql
done

The api container's create_app() will refuse to boot without SENTRY_TOKEN_PEPPER in .env; generate one first:

echo "SENTRY_TOKEN_PEPPER=$(python -c 'import secrets; print(secrets.token_hex(32))')" >> .env

Large production databases (multi-million rows in any of the ten retrofitted tables)

ALTER TABLE ... ADD COLUMN external_id UUID UNIQUE NOT NULL DEFAULT gen_random_uuid() writes every row once to populate the default and then builds a UNIQUE index over the filled column. Postgres holds an ACCESS EXCLUSIVE lock for the duration of that rewrite, which blocks every reader and writer on the table. On a 10M-row table this is typically seconds to low minutes; on a 100M+ row table it can be tens of minutes.

The two-step alternative trades lock time for total work:

Schema step (fast, no data migration). Skip migration 020's retrofit block and instead run:
```
ALTER TABLE <each> ADD COLUMN external_id UUID;  -- no default, no NOT NULL, no UNIQUE
```
ACCESS EXCLUSIVE for milliseconds.

Backfill step (slow, online). In batches of 50-100k rows:

UPDATE <each>
   SET external_id = gen_random_uuid()
 WHERE external_id IS NULL
   AND <pk> IN (SELECT <pk> FROM <each> WHERE external_id IS NULL LIMIT 50000);

Each batch takes a ROW EXCLUSIVE lock only. Run until SELECT COUNT(*) FROM <each> WHERE external_id IS NULL returns 0.

Constraint step (fast). After the backfill:
```
ALTER TABLE <each> ALTER COLUMN external_id SET NOT NULL;
ALTER TABLE <each> ADD CONSTRAINT <each>_external_id_key UNIQUE (external_id);
```
SHARE UPDATE EXCLUSIVE while the unique index builds (concurrent reads + writes continue; blocks only DDL on the table).
Only after the constraint step succeeds on every retrofitted table, run migration 025 to drop the DEFAULT. The v1.5.0 code paths already supply an explicit uuid.uuid4() at every insert site, verified by api/tests/test_external_id_inserts.py CI guardrail.

After the migrations land

Verify the keeper is up.
```
docker compose ps snapshot-keeper
```
Should be running (healthy). The heartbeat file is written every 5 seconds; the Compose healthcheck marks the container unhealthy when the file is older than 30 seconds.
Verify the new routes are reachable.
```
# Requires a wms_tokens row; create one through the admin panel first.
curl -s http://localhost:5000/api/v1/events/types \
  -H "X-WMS-Token: <your-plaintext>" | jq .
```
Expected: a types array with seven entries (receipt.completed, adjustment.applied, transfer.completed, pick.confirmed, pack.confirmed, ship.confirmed, cycle_count.adjusted).

Smoke-test the snapshot endpoint.

curl -s "http://localhost:5000/api/v1/snapshot/inventory?warehouse_id=1&limit=10" \
  -H "X-WMS-Token: <your-plaintext>" | jq .snapshot_event_id

Expected: an integer (0 if no events have been emitted yet, otherwise the MAX committed event_id at snapshot time).

Rollback

The v1.5.0 schema additions are all new tables or new nullable columns; the only destructive migration is 025 (drops external_id DEFAULT). Rollback in order:

-- Rollback 025: restore the DEFAULT so v1.4.x insert sites keep working.
ALTER TABLE users                 ALTER COLUMN external_id SET DEFAULT gen_random_uuid();
-- ... repeat for items, bins, sales_orders, purchase_orders, item_receipts,
-- inventory_adjustments, bin_transfers, cycle_counts, item_fulfillments

-- Rollback 024 through 020: drop the tables + columns
DROP TABLE IF EXISTS snapshot_scans CASCADE;
DROP TABLE IF EXISTS wms_tokens CASCADE;
DROP TABLE IF EXISTS consumer_groups CASCADE;
DROP TABLE IF EXISTS connectors CASCADE;
ALTER TABLE connector_credentials DROP COLUMN IF EXISTS credential_type;
DROP TABLE IF EXISTS integration_events CASCADE;
DROP FUNCTION IF EXISTS set_integration_event_visible_at();
DROP FUNCTION IF EXISTS notify_snapshot_scans_pending();
ALTER TABLE users                 DROP COLUMN IF EXISTS external_id;
-- ... repeat the DROP COLUMN for the other nine tables

Code-side rollback: checkout the v1.4.5 tag, rebuild, bring the stack up.

Rolling back after any connector has started polling is safe but the connector will lose cursor state and restart from event_id=0 on the next upgrade.