# Test Plan: Single Supply Chain — Foundation Media

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

---

## 1. Overview

This test plan covers validation for the Single Supply Chain schema change across M1/M2/M3. The change touches the most-connected table in the database (`contract`, 30+ downstream tables) and feeds SAP, Snowflake, and Looker.

**Principle:** Every phase of the migration runbook has corresponding verification steps. No phase proceeds until the previous phase's tests pass.

**Key differences from earlier TRD_A/B drafts:**
- Junction table (`signing_entity_profit_center`) is **pure** — no `display_name` column
- `display_name` lives on `reference_sap_profit_center` — **NOT NULL** after backfill, **no UNIQUE constraint**
- `contract` has a **direct FK** to `reference_sap_profit_center` (not to the junction)
- Read consumers are gated by Harness FF `single_supply_chain_company_codes`

---

## 2. Environments

| Environment | Purpose | Data |
|-------------|---------|------|
| **Local dev** | Unit tests, model validation | Seed data / fixtures |
| **Staging** | Integration tests, backfill dry-run, FF flip validation | Copy of prod reference data, subset of contracts |
| **Production** | Migration + post-migration verification | Live data |

All integration and E2E tests must pass in staging before any prod migration step.

---

## 3. Test Schedule

| Phase | Tests | Environment | When | Gate For |
|-------|-------|-------------|------|----------|
| Pre-migration | T1–T5 (schema, model, trigger) | Local + Staging | Before any prod DDL | Phase 1 DDL |
| Post-DDL | T6 (DDL verification) | Production | After Phase 1 of runbook | Phase 1 backfill |
| Post-backfill | T7–T10 (data integrity + correctness) | Production | After backfill changesets | Phase 3 FF rollout |
| FF rollout | T11–T13 (FF-gated paths, lambda, settlement) | Staging then Production | After Phase 3 of runbook | Phase 4 view switch |
| Post-view-switch | T14–T16 (Snowflake view, LookML, settlement regression) | Staging then Production | After Phase 4 of runbook | M1 sign-off |
| M2 | T17–T21 (admin CRUD, cascading dropdown, Foundation Media) | Staging then Production | After M2 deploys | M2 sign-off |
| M3 | T22–T26 (PC moves, P&L, FF removal, column drop) | Staging then Production | After M3 deploys | M3 sign-off |

---

## 4. M1 Test Cases

### Schema & Model (Pre-migration)

#### T1: `signing_entity_profit_center` table DDL

**Type:** Unit | **Owner:** Engineering | **Environment:** Local / Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T1.1 | Create table; verify column types, defaults, indexes | Matches TRD §5.1 |
| T1.2 | PK auto-increments | Insert without explicit ID; assigned |
| T1.3 | Composite UNIQUE on `(reference_signing_entity_id, reference_sap_profit_center_id)` | Insert two rows with same combo; second fails |
| T1.4 | FK to `reference_signing_entity` enforced | Insert with non-existent SE ID; fails |
| T1.5 | FK to `reference_sap_profit_center` enforced | Insert with non-existent PC ID; fails |
| T1.6 | Both FKs NOT NULL | Insert with NULL on either FK; fails |
| T1.7 | `ON DELETE RESTRICT` on both FKs | Delete SE with mappings; fails. Delete PC with mappings; fails |
| T1.8 | Soft-delete fields nullable | Insert without `deleted_by`/`deleted_at`; succeeds |
| T1.9 | No `display_name` column exists | `SHOW COLUMNS` does not include `display_name` |

#### T2: `reference_sap_profit_center.display_name` + UNIQUE on `profit_center`

**Type:** Unit | **Owner:** Engineering | **Environment:** Local / Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T2.1 | Column added as nullable initially | `SHOW COLUMNS` confirms VARCHAR(255), NULL allowed |
| T2.2 | UNIQUE constraint on `profit_center` | Insert two rows with same `profit_center`; second fails |
| T2.3 | `display_name` is NOT unique | Insert two rows with same `display_name`; both succeed |
| T2.4 | After backfill: `display_name` NOT NULL | `SHOW COLUMNS` confirms NO (not nullable) |
| T2.5 | Cannot insert NULL `display_name` after NOT NULL | INSERT without `display_name`; fails |

#### T3: `contract.reference_sap_profit_center_id` column

**Type:** Unit | **Owner:** Engineering | **Environment:** Local / Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T3.1 | Column added as nullable | MEDIUMINT UNSIGNED, NULL allowed |
| T3.2 | FK to `reference_sap_profit_center` enforced | Insert with non-existent PC ID; fails |
| T3.3 | NULL allowed pre-backfill | Insert with NULL; succeeds |
| T3.4 | `ON DELETE RESTRICT` | Delete PC with contracts; fails |
| T3.5 | Pre-existing contracts have NULL after ALTER | All contracts before backfill have NULL |

#### T4: `contract_history.reference_sap_profit_center_id`

**Type:** Unit | **Owner:** Engineering | **Environment:** Local / Staging

Same shape as T3 — column add, FK, nullable.

#### T5: `after_contract_update` trigger

**Type:** Unit | **Owner:** Engineering | **Environment:** Local / Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T5.1 | Trigger exists and includes `reference_sap_profit_center_id` | `SHOW CREATE TRIGGER after_contract_update` |
| T5.2 | UPDATE on `contract` writes new column to history | UPDATE a contract's PC; new row in `contract_history` captures `OLD.reference_sap_profit_center_id` |
| T5.3 | UPDATE without PC change still works | UPDATE another column; history row written with correct PC from OLD |
| T5.4 | Trigger does not break on NULL PC | UPDATE a contract whose PC is NULL (pre-backfill); succeeds |

#### T6: SQLAlchemy model (`python-abacus-models`)

**Type:** Unit | **Owner:** Engineering | **Environment:** Local

| # | Test Case | Expected |
|---|-----------|----------|
| T6.1 | `SigningEntityProfitCenter` model round-trips | ORM create/read succeeds |
| T6.2 | `Contract.reference_sap_profit_center` relationship resolves | `contract.reference_sap_profit_center.display_name` returns expected |
| T6.3 | `ReferenceSapProfitCenter.contracts` reverse relationship | Returns list of contracts pointing to this PC |
| T6.4 | `ReferenceSigningEntity.signing_entity_profit_centers` reverse | Returns junction rows for this SE |
| T6.5 | `ReferenceSigningEntity.reference_sap_profit_center` (legacy) still works | Returns old 1:1 PC (kept during M1/M2) |
| T6.6 | Soft-delete mixin on junction excludes deleted rows | Query with `deleted_at IS NULL` filter |

### DDL & Data Integrity (Post-migration)

#### T7: Production DDL check

**Type:** Verification | **Owner:** Engineering | **Environment:** Production

Run runbook §2.1 + §2.2 queries; all assertions pass.

#### T8: Junction backfill completeness

**Type:** Integration | **Owner:** Engineering | **Environment:** Staging, then Production

Run runbook §2.3 queries; all return expected counts / 0 rows.

#### T9: `display_name` backfill + NOT NULL

**Type:** Integration | **Owner:** Engineering | **Environment:** Staging, then Production

| # | Test Case | Expected |
|---|-----------|----------|
| T9.1 | Zero NULLs after backfill | `SELECT COUNT(*) WHERE display_name IS NULL` = 0 |
| T9.2 | Placeholder format used | Sampled rows show `<legal_name> -- <profit_center>` |
| T9.3 | Orphan PCs (no active SE) get `PC <code>` fallback | Spot-check expected |
| T9.4 | NOT NULL enforced after Changeset 11 | Inserting NULL `display_name` fails |

#### T10: Contract backfill correctness (the critical one)

**Type:** Integration | **Owner:** Engineering | **Environment:** Staging, then Production

| # | Test Case | Expected |
|---|-----------|----------|
| T10.1 | Zero NULLs on `contract.reference_sap_profit_center_id` | 0 |
| T10.2 | Total contract count unchanged from baseline B1 | Match |
| T10.3 | Legacy = new for **every** contract: `contract.PC_id` = `SE.PC_id` for `contract → SE` join | 0 mismatches (runbook §2.5 query) |
| T10.4 | Spot-check 50 random contracts manually | All match legacy path |
| T10.5 | Contract history backfill — non-soft-delete NULLs investigated | Any NULLs correspond to soft-deleted SEs only |

### FF Rollout (Application Read Switch)

#### T11: FF-gated `ows-royalties` read path

**Type:** Integration | **Owner:** Backend | **Environment:** Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T11.1 | FF OFF: `Contract.get_sap_profit_center_by_contract_id()` joins via SE | Returns expected PC; query plan shows SE JOIN |
| T11.2 | FF ON: same method reads `contract.reference_sap_profit_center_id` directly | Returns same PC; query plan shows no SE JOIN |
| T11.3 | FF OFF and ON produce identical results for sampled contracts | 100% match |

#### T12: FF-gated `sync_contract_sap` lambda

**Type:** Integration | **Owner:** Backend + SAP | **Environment:** Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T12.1 | FF OFF: lambda emits `Prctr` via SE join | Matches pre-migration behavior |
| T12.2 | FF ON: lambda emits `Prctr` via `contract.PC_id` | Matches T12.1 output for same contract |
| T12.3 | Lambda doesn't emit NULL `Prctr` under either FF state | 0 NULL emissions for valid contracts |
| T12.4 | Switching FF mid-run doesn't corrupt output | Re-run lambda with FF flipped; output stable |

#### T13: Settlement feed dry-run

**Type:** Integration | **Owner:** Engineering + Robert Kordisch | **Environment:** Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T13.1 | Run settlement feed against staging with FF ON (pre-view-switch) | Output unchanged — feed reads from view, view still uses old path |
| T13.2 | Run settlement feed against staging with FF ON + view switched | Output byte-for-byte matches T13.1 |

### Post-View-Switch (Phase 4)

#### T14: Snowflake `dim_abacus_contract` view regression

**Type:** Integration | **Owner:** Engineering | **Environment:** Staging, then Production

| # | Test Case | Expected |
|---|-----------|----------|
| T14.1 | View returns same `profit_center` and `company_code` for sampled contracts (vs baseline B4) | 100% match |
| T14.2 | View includes new `display_name` column | Column present, populated |
| T14.3 | View JOIN plan uses `contract.reference_sap_profit_center_id` only | No SE JOIN |

#### T15: LookML view updates

**Type:** Smoke test | **Owner:** Analytics | **Environment:** Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T15.1 | All 10 updated LookML views compile cleanly | No errors in Looker |
| T15.2 | Dashboards using `dim_abacus_contract` render correctly | Visual smoke test pass |
| T15.3 | `display_name` available as a dimension | Can group/filter by it |

#### T16: Settlement feed regression (final)

**Type:** Integration | **Owner:** Engineering + Robert Kordisch | **Environment:** Staging → Production

| # | Test Case | Expected |
|---|-----------|----------|
| T16.1 | Full settlement feed run in staging with all M1 changes applied | Output byte-for-byte matches pre-migration baseline |
| T16.2 | Settlement feed run in prod after FF + view switch | No regression; sign-off from Robert Kordisch |

---

## 5. M2 Test Cases

#### T17: API — list authorized PCs for an SE

**Type:** Integration | **Owner:** Backend | **Environment:** Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T17.1 | `GET /signing-entities/21/profit-centers` for Santa Anna (after M2.9 data load) | Returns 2: SA-FM Originated + Santa Anna |
| T17.2 | `GET /signing-entities/10/profit-centers` for The Orchard | Returns 1 |
| T17.3 | Soft-deleted mappings excluded | Soft-delete one; no longer in list |

#### T18: Admin — create / update / soft-delete mapping

**Type:** E2E | **Owner:** Frontend + Backend | **Environment:** Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T18.1 | Create mapping with valid SE + PC | Row created; appears in T17 listing |
| T18.2 | Create duplicate `(SE, PC)` pair | 409 / UNIQUE violation |
| T18.3 | Create with non-existent SE | 400 error |
| T18.4 | Create with non-existent PC | 400 error |
| T18.5 | Soft-delete a mapping with no contracts | Succeeds; `deleted_at` set |
| T18.6 | Soft-delete a mapping with contracts | Behavior TBD — either block or warn (capture in OPEN_QUESTIONS) |
| T18.7 | Non-admin user attempts admin screen | 403 |

#### T19: Admin — update `display_name`

**Type:** Integration | **Owner:** Backend | **Environment:** Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T19.1 | PATCH `display_name` to a valid non-empty string | Succeeds; UI reflects new label |
| T19.2 | PATCH `display_name` to empty string | 400 — non-empty enforced |
| T19.3 | PATCH `display_name` to NULL | 400 — column is NOT NULL |
| T19.4 | Updated label appears immediately in contract detail view | UI refresh shows new value |

#### T20: Cascading dropdown on contract creation

**Type:** E2E | **Owner:** Frontend | **Environment:** Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T20.1 | Select SE with 1 authorized PC | PC auto-selected; dropdown hidden or pre-filled |
| T20.2 | Select SE with multiple PCs (Santa Anna) | Dropdown shows 2 options labeled with `display_name` |
| T20.3 | Change SE | PC dropdown resets and repopulates |
| T20.4 | Submit contract with chosen PC | Contract created with correct `reference_sap_profit_center_id` |
| T20.5 | Submit fails if `(SE, PC)` pair is not in junction | 400 — validation rejects |

#### T21: Foundation Media data load

**Type:** Integration | **Owner:** Engineering | **Environment:** Staging, then Production

| # | Test Case | Expected |
|---|-----------|----------|
| T21.1 | Santa Anna (SE 21) has 2 mappings after M2.9 | SA-FM Originated + Santa Anna |
| T21.2 | OVO (SE 22) has 2 mappings | OVO Catalog + OVO New |
| T21.3 | Foundation Media (SE 20) has correct mappings | Per business spec |
| T21.4 | All new mappings point to valid PC rows | FK integrity |
| T21.5 | New PCs have `display_name` set (not placeholder) | Verified manually |

---

## 6. M3 Test Cases

#### T22: Contract move — change PC

**Type:** E2E | **Owner:** Frontend + Backend | **Environment:** Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T22.1 | Change a contract's PC to another authorized for the same SE | Succeeds; `contract_history` row written by trigger |
| T22.2 | Change to a PC not in the junction for this SE | 400 — validation rejects |
| T22.3 | Change to a soft-deleted mapping's PC | 400 — soft-deleted mappings not allowed |

#### T23: P&L reconciliation notification

**Type:** Integration | **Owner:** Backend | **Environment:** Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T23.1 | Change PC on a contract | Notification triggered with: contract ID, old PC, new PC, timestamp, user |
| T23.2 | Notification reaches Finance via the existing channel (extend SE-change flow) | Confirmed |

#### T24: SE change forces PC re-selection

**Type:** E2E | **Owner:** Frontend + Backend | **Environment:** Staging

| # | Test Case | Expected |
|---|-----------|----------|
| T24.1 | Change SE on a contract | PC cleared; user must re-select |
| T24.2 | Save without new PC selected | 400 |
| T24.3 | New PC dropdown shows options for the new SE | Filtered list correct |

#### T25: FF removal + cleanup

**Type:** Integration | **Owner:** Backend | **Environment:** Staging → Production

| # | Test Case | Expected |
|---|-----------|----------|
| T25.1 | After 3.7: no FF check remains in `ows-royalties`, `sync_contract_sap`, or frontend | Grep returns 0 references |
| T25.2 | After 3.7: code runs identically regardless of Harness FF state | Toggle FF; behavior unchanged |
| T25.3 | After 3.8: Harness FF `single_supply_chain_company_codes` is deleted | Harness UI confirms |
| T25.4 | After 3.9: `reference_signing_entity.reference_sap_profit_center_id` column does not exist | `SHOW COLUMNS` |
| T25.5 | After 3.9: `after_contract_update` trigger does not reference dropped column | `SHOW CREATE TRIGGER` |
| T25.6 | After 3.11: `ReferenceSigningEntity.reference_sap_profit_center` relationship removed | Code grep + model test |

#### T26: SE dedup (3.13)

**Type:** Integration | **Owner:** Engineering | **Environment:** Staging → Production

| # | Test Case | Expected |
|---|-----------|----------|
| T26.1 | Identify duplicate SE rows (same legal name) | Audit query |
| T26.2 | Reparent contracts from duplicate to canonical SE IDs | Successful UPDATE; `ON DELETE RESTRICT` errors handled |
| T26.3 | Junction rows reparented or deduped | UNIQUE constraint respected |
| T26.4 | Post-dedup: contract count unchanged; orphan SE count reduced | Verified |
| T26.5 | Settlement feed regression clean | No surprises |

---

## 7. Regression Watchlist

NOT being changed but could break if the migration introduces unexpected side effects. Spot-check after each phase:

| Area | What to Check | How |
|------|--------------|-----|
| Payment entity derivation | `account_payment_term.payment_entity_id` still set correctly on contract creation | Create a contract; verify payment entity matches SE's `reference_payment_entity_id` |
| KN&R payable feed | Feed output unchanged | Compare staging output pre/post |
| Slaughterhouse mapping | UPC/ISRC → account_id resolution | Spot-check 10 mappings |
| Contract advance calculations | Advance balances unaffected | Query `contract_advance` for a known contract; compare |
| Accounting run | Run completes without errors | Trigger a test run in staging |
| VAT details | `account_contract_vat_detail.company_code` unchanged | Spot-check 10 rows |
| Contract template | No PC FK added; existing templates still work | Verified — D28 carries D9 |

---

## 8. Sign-Off

| Milestone | Approver | Criteria |
|-----------|----------|----------|
| M1 | Engineering lead + Robert Kordisch | T1–T16 pass; settlement feed regression clean; FF soaked in prod |
| M2 | Engineering lead + Product (Danielle Vu) | T17–T21 pass; Finance confirms admin workflow |
| M3 | Engineering lead + Finance (Max Lester) | T22–T26 pass; P&L notification confirmed; column drop verified |