# Migration Runbook: Single Supply Chain

**Date:** 2026-05-27
**Author:** Michael Rojas
**Status:** Approved — Option B (see [TRD.md](TRD.md) and [DECISION_LOG.md](DECISION_LOG.md))

**Migration file:** `database/royalty_accounting/build/changelog/ddl/ACC-XXXXX_single_supply_chain.sql` *(ticket number TBD when M1.1 is created)*

All DDL, trigger, GRANT, and backfill SQL lives in Liquibase changesets. This runbook covers operational procedure, verification queries, and rollback.

---

## Pre-Migration Checklist

- [ ] Migration file reviewed and approved
- [ ] Harness Feature Flag `single_supply_chain_company_codes` created (default OFF) — ticket M1.7
- [ ] `python-abacus-models` regenerated and published — ticket M1.8
- [ ] All FF-gated consumer code deployed to prod with FF OFF — tickets M1.9, M1.11, M1.22
- [ ] Staging environment tested end-to-end including FF flip
- [ ] Rollback plan reviewed
- [ ] Notify: Abacus engineering, Finance team, Robert Kordisch (settlement feed owner)

---

## Phase 1: Execute Migration (Changesets 1–6 DDL + 7–11 Backfill)

**Risk:** Low for DDL (additive only). Medium for backfill (writes to production data, idempotent).
**Downtime:** None.

Changesets execute in order via Liquibase:

| Changeset | Operation |
|-----------|-----------|
| 1 | `CREATE TABLE signing_entity_profit_center` + indexes + FKs |
| 2 | GRANT permissions on `signing_entity_profit_center` |
| 3 | `ALTER reference_sap_profit_center ADD COLUMN display_name VARCHAR(255) NULL` + `ADD UNIQUE` on `profit_center` |
| 4 | `ALTER contract ADD COLUMN reference_sap_profit_center_id` (nullable) + FK + index |
| 5 | `ALTER contract_history ADD COLUMN reference_sap_profit_center_id` (nullable) + FK + index |
| 6 | `DROP TRIGGER + CREATE TRIGGER after_contract_update` (with new column) |
| 7 | Backfill `signing_entity_profit_center` from `reference_signing_entity` |
| 8 | Backfill `reference_sap_profit_center.display_name` with placeholders |
| 9 | Backfill `contract.reference_sap_profit_center_id` |
| 10 | Backfill `contract_history.reference_sap_profit_center_id` |
| 11 | `ALTER reference_sap_profit_center MODIFY display_name NOT NULL` |

`ALTER contract MODIFY reference_sap_profit_center_id NOT NULL` is **deferred** until after FF rollout (Phase 4).

### Pre-migration baseline

Capture BEFORE running:

```sql
-- B1: Contract count
SELECT COUNT(*) AS total_contracts FROM contract;

-- B2: Active signing entity count
SELECT COUNT(*) AS active_se
FROM reference_signing_entity
WHERE deleted_at IS NULL;

-- B3: Existing PC count (no display_name yet)
SELECT COUNT(*) AS total_pc FROM reference_sap_profit_center;

-- B4: Contract view sample (10 rows) for regression comparison
SELECT c.contract_id,
       c.reference_signing_entity_id,
       rse.company_code,
       rse.legal_name,
       rspc.profit_center
FROM contract c
  JOIN reference_signing_entity rse
    ON c.reference_signing_entity_id = rse.reference_signing_entity_id
  JOIN reference_sap_profit_center rspc
    ON rse.reference_sap_profit_center_id = rspc.reference_sap_profit_center_id
ORDER BY c.contract_id
LIMIT 10;
```

Save the output for post-migration comparison (B4 is the regression baseline).

---

## Phase 2: Post-Migration Verification

### 2.1 Table & column creation

```sql
SHOW CREATE TABLE signing_entity_profit_center;
-- Confirm: PK, composite UNIQUE, two FKs (NOT NULL), two indexes, soft-delete cols, no display_name

SHOW COLUMNS FROM reference_sap_profit_center LIKE 'display_name';
-- Confirm: VARCHAR(255), nullable for now (will be NOT NULL after Phase 2.3)

SHOW INDEX FROM reference_sap_profit_center WHERE Key_name = 'uidx_sap_profit_center_code';
-- Confirm: UNIQUE on profit_center

SHOW COLUMNS FROM contract LIKE 'reference_sap_profit_center_id';
SHOW COLUMNS FROM contract_history LIKE 'reference_sap_profit_center_id';
-- Confirm: MEDIUMINT UNSIGNED, nullable
```

### 2.2 Trigger updated

```sql
SHOW CREATE TRIGGER after_contract_update;
-- Confirm: INSERT statement includes reference_sap_profit_center_id from OLD
```

### 2.3 Junction backfill completeness

```sql
-- Row count matches active SE count
SELECT (SELECT COUNT(*) FROM signing_entity_profit_center) AS junction_count,
       (SELECT COUNT(*) FROM reference_signing_entity WHERE deleted_at IS NULL) AS active_se_count;
-- Must be equal

-- No active SE missing from junction
SELECT rse.reference_signing_entity_id, rse.legal_name
FROM reference_signing_entity rse
  LEFT JOIN signing_entity_profit_center sepc
    ON rse.reference_signing_entity_id = sepc.reference_signing_entity_id
WHERE rse.deleted_at IS NULL
  AND sepc.signing_entity_profit_center_id IS NULL;
-- Must return 0 rows

-- No duplicate mappings (every junction row points to a valid current 1:1 SE→PC)
SELECT sepc.reference_signing_entity_id,
       COUNT(*) AS row_count
FROM signing_entity_profit_center sepc
GROUP BY sepc.reference_signing_entity_id
HAVING row_count > 1;
-- Must return 0 rows (before any M2 additions)
```

### 2.4 `display_name` backfill + NOT NULL

```sql
-- Zero NULLs before NOT NULL enforcement (Changeset 11 runs after this passes)
SELECT COUNT(*) AS null_display_name
FROM reference_sap_profit_center
WHERE display_name IS NULL;
-- Must be 0

-- After Changeset 11:
SHOW COLUMNS FROM reference_sap_profit_center LIKE 'display_name';
-- Confirm: NO (not nullable)

-- Spot-check placeholder format
SELECT reference_sap_profit_center_id, profit_center, display_name
FROM reference_sap_profit_center
ORDER BY reference_sap_profit_center_id
LIMIT 10;
-- Expect: '<legal_name> -- <profit_center>' format or 'PC <code>' for orphans
```

### 2.5 Contract backfill completeness

```sql
-- Zero NULLs
SELECT COUNT(*) AS null_count
FROM contract
WHERE reference_sap_profit_center_id IS NULL;
-- Must be 0

-- Total contract count unchanged from B1
SELECT COUNT(*) AS total_contracts FROM contract;
-- Must match pre-migration baseline B1

-- Correctness: contract.PC matches legacy SE.PC for every contract
SELECT c.contract_id,
       rse.reference_sap_profit_center_id AS legacy_pc,
       c.reference_sap_profit_center_id   AS new_pc
FROM contract c
  JOIN reference_signing_entity rse
    ON c.reference_signing_entity_id = rse.reference_signing_entity_id
WHERE c.reference_sap_profit_center_id != rse.reference_sap_profit_center_id;
-- Must return 0 rows — this is the critical correctness check
```

### 2.6 Contract history backfill

```sql
SELECT COUNT(*) AS null_count
FROM contract_history
WHERE reference_sap_profit_center_id IS NULL;
-- May be > 0 if historical rows reference soft-deleted SEs.
-- Investigate any non-soft-delete NULLs.

-- FK integrity for non-NULL rows
SELECT ch.contract_history_id
FROM contract_history ch
  LEFT JOIN reference_sap_profit_center rspc
    ON ch.reference_sap_profit_center_id = rspc.reference_sap_profit_center_id
WHERE ch.reference_sap_profit_center_id IS NOT NULL
  AND rspc.reference_sap_profit_center_id IS NULL;
-- Must return 0 rows
```

### 2.7 Sample regression vs baseline

Re-run B4 (10 sample contracts) and compare against pre-migration output:

```sql
SELECT c.contract_id,
       c.reference_signing_entity_id,
       c.reference_sap_profit_center_id,
       rspc.profit_center,
       rspc.company_code,
       rspc.display_name
FROM contract c
  JOIN reference_sap_profit_center rspc
    ON c.reference_sap_profit_center_id = rspc.reference_sap_profit_center_id
WHERE c.contract_id IN (<10 IDs from B4>)
ORDER BY c.contract_id;
```

**Expected:** `profit_center` and `company_code` values match B4 baseline exactly.

---

## Phase 3: FF Rollout (Application Read Switch)

After Phase 2 verification passes:

### 3.1 Enable FF in staging

```
Harness UI: Toggle `single_supply_chain_company_codes` to ON in staging environment.
```

Smoke test:
- Hit `Contract.get_sap_profit_center_by_contract_id()` for sampled contracts in `ows-royalties`
- Verify `sync_contract_sap` lambda emits expected `Prctr` for staging fixtures
- Confirm contract detail page shows `display_name`

### 3.2 Enable FF in prod (canary if available)

If Harness allows percentage rollout, enable for 10% → 50% → 100% over 24 hours. Otherwise flip to 100% with active monitoring.

Monitor:
- `sync_contract_sap` lambda error rate and `Prctr` NULL rate
- ows-royalties error logs for `get_sap_profit_center_by_contract_id` calls
- Settlement feed dry-run output (if available daily)

### 3.3 NOT NULL on `contract.reference_sap_profit_center_id`

Only after FF has been ON in prod for at least one full accounting cycle:

```sql
ALTER TABLE contract
  MODIFY COLUMN reference_sap_profit_center_id MEDIUMINT UNSIGNED NOT NULL;
```

Verify:
```sql
SHOW COLUMNS FROM contract LIKE 'reference_sap_profit_center_id';
-- Confirm: NO (not nullable)
```

---

## Phase 4: Downstream Switches (Atomic, Post-FF)

### 4.1 Snowflake `dim_abacus_contract` view

Update the view to join through `contract.reference_sap_profit_center_id` directly:

```sql
CREATE OR REPLACE VIEW royalty_accounting_prod_view_dim_abacus_contract AS
SELECT c.*,
       rspc.profit_center,
       rspc.company_code,
       rspc.display_name
FROM contract c
  JOIN reference_sap_profit_center rspc
    ON c.reference_sap_profit_center_id = rspc.reference_sap_profit_center_id;
```

**Verify:** run settlement feed in dry-run / staging mode; compare output to pre-migration baseline.

### 4.2 LookML views

Update 10 views in `abacus-looker` (identified during audit). Surface `display_name`; remove SE→PC join references. Deploy and refresh dashboards.

### 4.3 Settlement feed regression

Robert Kordisch runs settlement feed against staging with the updated view + LookML. Compare output to pre-migration baseline byte-for-byte (for the same input data). Sign-off blocks M1 completion.

---

## Rollback Plan

### Rollback during Phase 1 (DDL/backfill)

Liquibase handles rollback per-changeset. Each changeset has a `--rollback` block. Run Liquibase rollback targeting the failed changeset.

### Rollback during Phase 3 (FF rollout)

```
Harness UI: Toggle `single_supply_chain_company_codes` to OFF.
```

All FF-gated consumers immediately fall back to the legacy SE→PC path. The new columns and junction table remain in the DB (additive, harmless).

### Rollback during Phase 4 (view switch)

```sql
-- Revert Snowflake view to pre-migration version
CREATE OR REPLACE VIEW royalty_accounting_prod_view_dim_abacus_contract AS
SELECT c.*,
       rse.company_code,
       rspc.profit_center
FROM contract c
  JOIN reference_signing_entity rse
    ON c.reference_signing_entity_id = rse.reference_signing_entity_id
  JOIN reference_sap_profit_center rspc
    ON rse.reference_sap_profit_center_id = rspc.reference_sap_profit_center_id;
```

The legacy `reference_signing_entity.reference_sap_profit_center_id` column is still present (not dropped until M3), so the old join path continues to work throughout M1.

### Rollback after NOT NULL enforcement

```sql
-- Revert NOT NULL (rare — only if a critical write path can't supply the FK)
ALTER TABLE contract
  MODIFY COLUMN reference_sap_profit_center_id MEDIUMINT UNSIGNED DEFAULT NULL;
```

### Data rollback (re-run backfill)

If data is incorrect after migration:

```sql
-- Reset contract FKs (does not affect application — column is nullable until 3.3)
UPDATE contract SET reference_sap_profit_center_id = NULL;
UPDATE contract_history SET reference_sap_profit_center_id = NULL;

-- Clear junction
DELETE FROM signing_entity_profit_center;

-- Re-run changesets 7, 9, 10
```

`display_name` rollback:

```sql
UPDATE reference_sap_profit_center SET display_name = NULL;
-- Then re-run changeset 8 + 11
```

### Key safety property

The old join path (`contract → reference_signing_entity → reference_sap_profit_center`) continues to work throughout M1. The new path (`contract.reference_sap_profit_center_id`) is purely additive. Nothing is removed in M1 — destructive changes are deferred to M3.

---

## Contacts

| Role | Person | When to Contact |
|------|--------|-----------------|
| Settlement feed owner | Robert Kordisch | Before/after Snowflake view switch (Phase 4.1) |
| Signing entity cleanup | Humda Rahman | Coordinate dedup timing with M3.13 |
| Finance QC | Max Lester, Tina Kim | After Foundation Media data inserted (M2.9) |
| Product owner | Danielle Vu | Display name placeholder issues; UI label decisions |
| Technical advisor | Charles Owens | Escalation on business logic questions |
| Snowflake sync owner | *(identify before Phase 4)* | Before/after view + replication changes |
| Harness FF owner | Engineering on-call | FF flip in prod (Phase 3.2) |