# TRD: Single Supply Chain — Foundation Media

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

---

## 1. Overview

Decouple the 1:1 relationship between Signing Entity and SAP Profit Center in the Abacus royalty accounting database, enabling one legal entity to map to multiple profit centers. Implements **Option B** from [SPIKE_SCHEMA_DESIGN.md](SPIKE_SCHEMA_DESIGN.md), refined per DECISION_LOG entries D24–D28.

**Two structural changes:**

1. **`signing_entity_profit_center`** — a new **pure junction table** (just `(signing_entity_id, sap_profit_center_id)` plus audit/soft-delete columns). Encodes which profit centers are authorized for each signing entity. Contains no business attributes — the user-facing label is **not** on this table.

2. **`contract.reference_sap_profit_center_id`** — a new **direct FK** from contract to the SAP profit center, independent of the junction. The junction governs *availability*; the contract stores the *chosen* profit center.

The user-facing label (`display_name`) lives on `reference_sap_profit_center` — one label per profit center, shared by every signing entity that maps to it.

---

## 2. System Context

### 2.1 Data Architecture

- **Source of truth:** MySQL (`royalty_accounting` schema)
- **Downstream replica:** Snowflake (`orchard_app_reporting_v2`) — synced from MySQL
- **ORM:** SQLAlchemy (1.4 in most services, 2.0 in ows-abacus-event/state)
- **Model package:** `python-abacus-models` — auto-generated models for both MySQL and Snowflake
- **Feature flag platform:** Harness — gates the dual-path read window during rollout

### 2.2 Affected Services

| Service | Role | Impact |
|---------|------|--------|
| `ows-royalties` | Contract read/derive logic | FF-gated dual read path: `contract → SE → SE.PC_id` (OFF) vs `contract.PC_id` (ON) |
| `ows-abacus-contract` | Contract CRUD writes | Accept `reference_sap_profit_center_id` on contract create/update; validate against junction |
| `graphql-abacus` | Apollo Federation gateway | New `SigningEntityProfitCenter` + `SapProfitCenter` types; extend `AbacusContract` |
| `frontend-royalties` | React SPA | Display `display_name` on contract detail (M1, FF-gated); cascading dropdown (M2) |
| `lambda-abacus / sync_contract_sap` | SAP sync handler | FF-gated dual read path for `Prctr` source |
| `ows-abacus-legacy-sync` | MySQL → Snowflake ETL | No direct changes — verified neither this service nor `lambda-abacus/ows_abacus_legacy_sync.py` references signing entity / profit center |

### 2.3 External Integrations

| System | Integration | Impact |
|--------|-------------|--------|
| SAP | Settlement feed reads from `royalty_accounting_prod_view_dim_abacus_contract` | View updated to resolve through `contract.reference_sap_profit_center_id` (D4) |
| SAP | `sync_contract_sap` lambda sends `Prctr` field | FF-gated dual read path |
| Snowflake | Replicated from MySQL via Fivetran | New columns picked up automatically; junction table added to replication scope |
| Looker | LookML views on `dim_abacus_contract`, `fact_contract_history`, settlement feed | Update 10 LookML views (`abacus-looker`) to surface `display_name` |
| Payoneer | Payment processing via payment entity | No change (payment entity stays on signing entity — D5) |

---

## 3. Current Schema

### Entity Relationship Diagram — Before

```mermaid
erDiagram
    contract {
        MEDIUMINT contract_id PK
        MEDIUMINT reference_signing_entity_id FK
        VARCHAR contract_name
        ENUM contract_type
        TINYINT is_primary_contract
        TINYINT is_paythrough_contract
        DATETIME sap_created_at
        DATE term_start
        DATE term_end
    }

    reference_signing_entity {
        MEDIUMINT reference_signing_entity_id PK
        MEDIUMINT reference_payment_entity_id FK
        VARCHAR legal_name
        VARCHAR company_code "COUPLED - drives financial routing"
        MEDIUMINT reference_sap_profit_center_id FK "COUPLED - 1:1 with PC"
        VARCHAR tax_entity_company_code
        VARCHAR vat_number
        VARCHAR company_registration_number
        VARCHAR address
    }

    reference_sap_profit_center {
        MEDIUMINT reference_sap_profit_center_id PK
        VARCHAR profit_center
        VARCHAR company_code
        VARCHAR business_group
    }

    reference_payment_entity {
        MEDIUMINT reference_payment_entity_id PK
        VARCHAR payment_entity_name
        CHAR country_of_tax_reporting
    }

    contract }o--|| reference_signing_entity : "reference_signing_entity_id (RESTRICT)"
    reference_signing_entity }o--|| reference_payment_entity : "reference_payment_entity_id"
    reference_signing_entity }o--|| reference_sap_profit_center : "reference_sap_profit_center_id (1:1)"
```

**Problem:** `reference_signing_entity.reference_sap_profit_center_id` is a NOT NULL FK, enforcing every legal entity to exactly one profit center. The contract derives its profit center entirely through the signing entity: `contract → SE → PC`. There is no way to assign a different profit center to a contract without creating a duplicate signing entity.

**FK constraints on contract:**
- `contract.reference_signing_entity_id` → `reference_signing_entity` with `ON DELETE RESTRICT`
- `contract_history.reference_signing_entity_id` → same, `ON DELETE RESTRICT ON UPDATE RESTRICT`
- `contract_template.reference_signing_entity_id` → same, `ON DELETE RESTRICT`

---

## 4. Target Schema

### Entity Relationship Diagram — After

```mermaid
erDiagram
    contract {
        MEDIUMINT contract_id PK
        MEDIUMINT reference_signing_entity_id FK
        MEDIUMINT reference_sap_profit_center_id FK "NEW - direct FK to PC"
        VARCHAR contract_name
        ENUM contract_type
    }

    signing_entity_profit_center {
        MEDIUMINT signing_entity_profit_center_id PK "NEW - pure junction"
        MEDIUMINT reference_signing_entity_id FK "NOT NULL"
        MEDIUMINT reference_sap_profit_center_id FK "NOT NULL"
    }

    reference_signing_entity {
        MEDIUMINT reference_signing_entity_id PK
        MEDIUMINT reference_payment_entity_id FK
        VARCHAR legal_name
        VARCHAR company_code "LEGACY - removed in M3"
        MEDIUMINT reference_sap_profit_center_id FK "LEGACY - removed in M3"
    }

    reference_sap_profit_center {
        MEDIUMINT reference_sap_profit_center_id PK
        VARCHAR profit_center "UNIQUE"
        VARCHAR display_name "NEW - user-facing label, NOT NULL after backfill"
        VARCHAR company_code
        VARCHAR business_group
    }

    reference_payment_entity {
        MEDIUMINT reference_payment_entity_id PK
        VARCHAR payment_entity_name
        CHAR country_of_tax_reporting
    }

    contract }o--|| reference_signing_entity : "reference_signing_entity_id (unchanged)"
    contract }o--|| reference_sap_profit_center : "reference_sap_profit_center_id (NEW direct FK)"
    signing_entity_profit_center }o--|| reference_signing_entity : "which SE"
    signing_entity_profit_center }o--|| reference_sap_profit_center : "which PC"
    reference_signing_entity }o--|| reference_payment_entity : "unchanged"
    reference_signing_entity }o--|| reference_sap_profit_center : "LEGACY"
```

**Key relationships:**

| Relationship | Cardinality | Purpose |
|--------------|-------------|---------|
| `contract → reference_signing_entity` | N:1 | Who signed (unchanged) |
| `contract → reference_sap_profit_center` | N:1 | Where revenue routes (NEW, direct) |
| `signing_entity_profit_center → reference_signing_entity` | N:1 | Which SE is authorized |
| `signing_entity_profit_center → reference_sap_profit_center` | N:1 | For which PC |
| Effective SE↔PC | N:M (via junction) | Replaces the old 1:1 |

**Critical semantic distinction:** the junction encodes *authorization* (which PCs an SE is allowed to route revenue through). The contract's `reference_sap_profit_center_id` stores the *chosen* PC. The (SE, PC) pair on every contract must exist as a row in the junction — enforced at the application layer (write-path validation), not the DB layer.

### What Changed

| Element | Before | After |
|---------|--------|-------|
| `signing_entity_profit_center` table | Does not exist | **New pure junction** — `(signing_entity_id, sap_profit_center_id)` + audit + soft-delete. No business columns. |
| `contract.reference_sap_profit_center_id` | Does not exist | **New direct FK** — chosen PC for this contract |
| `contract_history.reference_sap_profit_center_id` | Does not exist | **New FK** — tracks PC changes |
| `reference_sap_profit_center.display_name` | Does not exist | **New column** — user-facing label, NOT NULL after backfill, no UNIQUE constraint |
| `reference_sap_profit_center.profit_center` | No UNIQUE constraint | **UNIQUE** added |
| `reference_signing_entity.reference_sap_profit_center_id` | NOT NULL 1:1 FK | **Legacy** — read-only during dual-path window; dropped in M3 |
| Profit center resolution path | `contract → SE → SE.PC_id` | `contract.PC_id` (direct) |
| `contract_template` | Points to SE only | **Unchanged** (D28 carries D9 forward) |

---

## 5. DDL Changes

### 5.1 New table: `signing_entity_profit_center` (pure junction)

```sql
CREATE TABLE `signing_entity_profit_center` (
  `signing_entity_profit_center_id`  MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
    COMMENT 'Primary key.',
  `reference_signing_entity_id`      MEDIUMINT UNSIGNED NOT NULL
    COMMENT 'Signing entity authorized for this profit center.',
  `reference_sap_profit_center_id`   MEDIUMINT UNSIGNED NOT NULL
    COMMENT 'SAP profit center the signing entity is authorized to route revenue to.',
  `created_by`       VARCHAR(255) NOT NULL,
  `created_at`       DATETIME     NOT NULL,
  `last_modified_by` VARCHAR(255) NOT NULL,
  `last_modified`    DATETIME     NOT NULL,
  `deleted_by`       VARCHAR(180) DEFAULT NULL,
  `deleted_at`       DATETIME     DEFAULT NULL,

  PRIMARY KEY (`signing_entity_profit_center_id`),
  UNIQUE KEY `uidx_sepc_signing_entity_sap_pc`
    (`reference_signing_entity_id`, `reference_sap_profit_center_id`),
  KEY `idx_sepc_signing_entity` (`reference_signing_entity_id`),
  KEY `idx_sepc_sap_pc`         (`reference_sap_profit_center_id`),

  CONSTRAINT `fk_sepc_signing_entity`
    FOREIGN KEY (`reference_signing_entity_id`)
    REFERENCES `reference_signing_entity` (`reference_signing_entity_id`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_sepc_sap_profit_center`
    FOREIGN KEY (`reference_sap_profit_center_id`)
    REFERENCES `reference_sap_profit_center` (`reference_sap_profit_center_id`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Pure junction: authorizes which SAP profit centers a signing entity may route revenue through. No business attributes — labels live on reference_sap_profit_center.';
```

**Design decisions** (see DECISION_LOG D24–D28, D10–D12):

- **No `display_name` column** (D25) — the user-facing label lives on `reference_sap_profit_center.display_name`. One label per PC, shared across every SE that maps to it.
- **No denormalized `company_code` or `profit_center`** (D11) — read through `reference_sap_profit_center` via the FK.
- **No `is_default` or `is_active`** (D12) — soft-delete via `deleted_at`; default selection is UI logic (auto-select when an SE has exactly one mapping).
- **Composite UNIQUE** on `(reference_signing_entity_id, reference_sap_profit_center_id)` prevents duplicate live mappings. **Soft-delete interaction:** the UNIQUE applies regardless of `deleted_at`; restoring a soft-deleted mapping is done by clearing `deleted_at` on the existing row, not by inserting a duplicate. If the business needs the ability to "create a new mapping with the same SE/PC pair after deletion," replace this with a functional UNIQUE filtered on `deleted_at IS NULL`. **Decision: keep simple UNIQUE for M1; revisit only if needed.**
- **`ON DELETE RESTRICT`** on both FKs — prevents accidental orphaning.

### 5.2 Alter `reference_sap_profit_center`

```sql
-- 5.2.a Add display_name nullable (so ALTER succeeds against existing ~100 rows)
ALTER TABLE `reference_sap_profit_center`
  ADD COLUMN `display_name` VARCHAR(255) DEFAULT NULL
    COMMENT 'User-facing label (e.g. "The Orchard US", "Foundation 3P"). NOT NULL after backfill. Not unique — multiple PCs may share the same label.'
    AFTER `profit_center`;

-- 5.2.b Add UNIQUE on profit_center (the SAP code)
ALTER TABLE `reference_sap_profit_center`
  ADD UNIQUE KEY `uidx_sap_profit_center_code` (`profit_center`);

-- (5.2.c — after backfill, see §6.2) Enforce NOT NULL on display_name
-- ALTER TABLE `reference_sap_profit_center`
--   MODIFY COLUMN `display_name` VARCHAR(255) NOT NULL;
```

### 5.3 Alter `contract`

```sql
ALTER TABLE `contract`
  ADD COLUMN `reference_sap_profit_center_id` MEDIUMINT UNSIGNED DEFAULT NULL
    COMMENT 'Direct FK to the SAP profit center this contract routes revenue to. Nullable during backfill; NOT NULL after verification.'
    AFTER `reference_signing_entity_id`,
  ADD CONSTRAINT `fk_contract_sap_profit_center`
    FOREIGN KEY (`reference_sap_profit_center_id`)
    REFERENCES `reference_sap_profit_center` (`reference_sap_profit_center_id`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  ADD KEY `idx_contract_sap_profit_center` (`reference_sap_profit_center_id`);
```

### 5.4 Alter `contract_history`

```sql
ALTER TABLE `contract_history`
  ADD COLUMN `reference_sap_profit_center_id` MEDIUMINT UNSIGNED DEFAULT NULL
    COMMENT 'Historical record of the SAP profit center at the time of this contract change.'
    AFTER `reference_signing_entity_id`,
  ADD CONSTRAINT `fk_contract_history_sap_profit_center`
    FOREIGN KEY (`reference_sap_profit_center_id`)
    REFERENCES `reference_sap_profit_center` (`reference_sap_profit_center_id`)
    ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD KEY `idx_contract_history_sap_profit_center` (`reference_sap_profit_center_id`);
```

### 5.5 Update `after_contract_update` trigger

The existing trigger (from `ACC-9197`) writes `contract` columns into `contract_history` on every update. After adding `reference_sap_profit_center_id` to both tables, the trigger must include the new column or PC changes will not be captured in history.

```sql
DROP TRIGGER IF EXISTS `after_contract_update`;
DELIMITER #
CREATE TRIGGER `after_contract_update` AFTER UPDATE ON `contract`
FOR EACH ROW
BEGIN
    INSERT INTO contract_history(
        `contract_id`,
        `reference_signing_entity_id`,
        `reference_sap_profit_center_id`,  -- NEW
        `contract_name`,
        `contract_type`,
        `sap_created_at`,
        `initial_start_date`,
        `execution_date`,
        `is_excluded_from_accounting_run`,
        `summary_note`,
        `general_note`,
        `term_start`,
        `term_end`,
        `created_by`,
        `created_at`,
        `last_modified_by`,
        `last_modified`
    ) VALUES (
        OLD.contract_id,
        OLD.reference_signing_entity_id,
        OLD.reference_sap_profit_center_id,  -- NEW
        OLD.contract_name,
        OLD.contract_type,
        OLD.sap_created_at,
        OLD.initial_start_date,
        OLD.execution_date,
        OLD.is_excluded_from_accounting_run,
        OLD.summary_note,
        OLD.general_note,
        OLD.term_start,
        OLD.term_end,
        OLD.created_by,
        OLD.created_at,
        OLD.last_modified_by,
        OLD.last_modified
    );
END#
DELIMITER ;
```

### 5.6 GRANT statements

Every reference table in the schema has explicit GRANT statements per existing convention. The new junction table needs grants for every service that already reads `reference_sap_profit_center`:

```sql
GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ON `signing_entity_profit_center` TO `ows-abacus-contract`@'%';
GRANT SELECT ON `signing_entity_profit_center` TO `ows-royalties`@'%';
GRANT SELECT ON `signing_entity_profit_center` TO `ows-moneyhub`@'%';
-- Add any other consumers identified during the audit
```

### 5.7 `contract_template` — no change (D28 carries D9)

Templates define contract terms and exclusions, not financial routing. The PC is selected at contract creation time, not template time. `contract_template` retains its existing `reference_signing_entity_id` FK only.

---

## 6. Data Migration

### 6.1 Backfill `signing_entity_profit_center` from existing 1:1 data

```sql
INSERT INTO `signing_entity_profit_center` (
  `reference_signing_entity_id`,
  `reference_sap_profit_center_id`,
  `created_by`, `created_at`, `last_modified_by`, `last_modified`
)
SELECT
  rse.`reference_signing_entity_id`,
  rse.`reference_sap_profit_center_id`,
  'system_backfill', NOW(), 'system_backfill', NOW()
FROM `reference_signing_entity` rse
WHERE rse.`deleted_at` IS NULL;
```

One row per active signing entity. Safe given the current 1:1 — every SE has exactly one PC.

### 6.2 Backfill `reference_sap_profit_center.display_name`

```sql
-- Placeholder format: "<legal_name> -- <profit_center>"
-- Where multiple SEs share a PC, picks the first SE (deterministic via ID).
UPDATE `reference_sap_profit_center` rspc
SET `display_name` = (
  SELECT CONCAT(rse.`legal_name`, ' -- ', rspc.`profit_center`)
  FROM `reference_signing_entity` rse
  WHERE rse.`reference_sap_profit_center_id` = rspc.`reference_sap_profit_center_id`
    AND rse.`deleted_at` IS NULL
  ORDER BY rse.`reference_signing_entity_id`
  LIMIT 1
)
WHERE `display_name` IS NULL;

-- Catch any PCs not referenced by any active SE (orphan PCs):
UPDATE `reference_sap_profit_center`
SET `display_name` = CONCAT('PC ', `profit_center`)
WHERE `display_name` IS NULL;
```

Placeholder format is documented to business so Finance knows the `legal_name -- profit_center` shape is intentional and replaceable via SQL UPDATE (D20).

Then enforce NOT NULL:

```sql
ALTER TABLE `reference_sap_profit_center`
  MODIFY COLUMN `display_name` VARCHAR(255) NOT NULL;
```

### 6.3 Backfill `contract.reference_sap_profit_center_id`

```sql
UPDATE `contract` c
  JOIN `reference_signing_entity` rse
    ON c.`reference_signing_entity_id` = rse.`reference_signing_entity_id`
SET c.`reference_sap_profit_center_id` = rse.`reference_sap_profit_center_id`;
```

Safe because today's 1:1 SE→PC means every contract has exactly one inherited PC.

### 6.4 Backfill `contract_history.reference_sap_profit_center_id`

```sql
UPDATE `contract_history` ch
  JOIN `reference_signing_entity` rse
    ON ch.`reference_signing_entity_id` = rse.`reference_signing_entity_id`
SET ch.`reference_sap_profit_center_id` = rse.`reference_sap_profit_center_id`;
```

May leave NULLs where a historical row references a soft-deleted signing entity. Investigate any non-soft-delete NULLs; do not enforce NOT NULL on `contract_history.reference_sap_profit_center_id`.

### 6.5 Verify backfill correctness (not just zero-NULL)

```sql
-- Zero-NULL check (gate before NOT NULL enforcement)
SELECT COUNT(*) AS null_count
FROM `contract`
WHERE `reference_sap_profit_center_id` IS NULL;
-- Must be 0

-- Spot-check: contract's PC matches the legacy SE→PC path
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
```

### 6.6 Enforce NOT NULL on `contract.reference_sap_profit_center_id`

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

---

## 7. Migration Execution Order

```
Step  | Operation                                                       | Depends On
------+-----------------------------------------------------------------+-----------
  1   | CREATE TABLE signing_entity_profit_center                       | --
  2   | ALTER reference_sap_profit_center ADD display_name (nullable)   | --
  2a  | ALTER reference_sap_profit_center ADD UNIQUE on profit_center   | --
  3   | ALTER contract ADD reference_sap_profit_center_id (nullable)    | --
  4   | ALTER contract_history ADD reference_sap_profit_center_id       | --
  5   | DROP+CREATE after_contract_update trigger (new col included)    | 3, 4
  6   | GRANT permissions on signing_entity_profit_center               | 1
  7   | Backfill signing_entity_profit_center from reference_signing_entity | 1
  8   | Backfill reference_sap_profit_center.display_name (placeholders) | 2
  9   | Verify zero NULL display_name + ALTER NOT NULL                  | 8
  10  | Backfill contract.reference_sap_profit_center_id                | 3
  11  | Backfill contract_history.reference_sap_profit_center_id        | 4
  12  | Verify zero NULL contract.reference_sap_profit_center_id        | 10
  13  | Verify backfill correctness (legacy = new on all contracts)     | 10
  14  | ALTER contract.reference_sap_profit_center_id NOT NULL          | 12, 13
```

Steps 1–6 are a single Liquibase migration. Steps 7–11 are data migrations (separate changesets or scripts). Steps 12–14 happen after the dual-path FF is enabled and verified in prod (see §9 below).

---

## 8. What NOT to Change

| Table / Column | Why |
|----------------|-----|
| `reference_signing_entity.company_code` | Keep as legacy during dual-path window; dropped in M3 |
| `reference_signing_entity.reference_sap_profit_center_id` | Keep as legacy during dual-path window; dropped in M3 |
| `account_payment_term` | Payment entity derivation unchanged; stays through signing entity (D5) |
| `account_contract_vat_detail.company_code` | VAT-specific; set independently |
| `contract_template` | No PC FK; selection happens at contract creation time (D28 carries D9) |
| Signing entity dedup | `ON DELETE RESTRICT` on all FKs makes this high-risk; defer to M3 after the new path is proven (D8 carries D7) |

---

## 9. Dual-Path Rollout via Harness Feature Flag

The migration leaves two PC-read paths in production for a controlled window:

- **Old path:** `contract → reference_signing_entity → reference_sap_profit_center_id`
- **New path:** `contract.reference_sap_profit_center_id` (direct)

Both paths return identical data after backfill — the legacy column on SE has not been dropped yet. A Harness Feature Flag `single_supply_chain_company_codes` (default **OFF**) controls which path each consumer takes (D27).

### 9.1 FF-gated consumers

| Consumer | Behavior with FF OFF | Behavior with FF ON |
|----------|----------------------|---------------------|
| `ows-royalties` `Contract.get_sap_profit_center_by_contract_id()` | JOIN through `reference_signing_entity` | Read `contract.reference_sap_profit_center_id` directly |
| `lambda-abacus/sync_contract_sap` | Resolve `Prctr` via SE JOIN | Resolve `Prctr` via `contract.reference_sap_profit_center_id` |
| `frontend-royalties` contract detail view | Hide `display_name` column | Show `display_name` column |

### 9.2 Non-FF consumers

| Consumer | Switch mechanism |
|----------|------------------|
| `royalty_accounting_prod_view_dim_abacus_contract` (Snowflake view) | Atomic SQL switch after step 14 (NOT NULL enforced) |
| LookML views in `abacus-looker` | Updated to use `display_name`; shipped after Snowflake view switch |

DB views can't be FF-gated. Their join paths return equivalent results post-backfill, so the switch is data-safe — but it does require app code to be reading the new path first to surface any bugs.

### 9.3 Rollout sequence (per environment)

```
1. Deploy all consumers with FF-gated code (FF still OFF — old path active)
2. Run DDL + backfill (steps 1–11)
3. Verify zero NULLs and correctness (steps 12–13)
4. Enable FF in staging → monitor → flip ON in prod
5. ALTER NOT NULL (step 14)
6. Switch Snowflake view + LookML views to new path
7. Run settlement feed regression test
8. Soak window before starting M3 cleanup
```

---

## 10. Downstream System Updates

### 10.1 Snowflake `dim_abacus_contract` view

```sql
-- Before:
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;

-- After:
SELECT c.*,
       rspc.company_code,
       rspc.profit_center,
       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;
```

### 10.2 `sync_contract_sap` lambda

FF-gated dual read. The `Prctr` field source switches from `SE → PC` to `contract → PC` when the FF is ON.

### 10.3 Snowflake replication

New columns on existing tables (`contract.reference_sap_profit_center_id`, `contract_history.reference_sap_profit_center_id`, `reference_sap_profit_center.display_name`) are picked up automatically by Fivetran. The new `signing_entity_profit_center` table must be added to the replication scope explicitly.

### 10.4 `python-abacus-models`

Regenerate (per `bumpversion` convention) for both MySQL and Snowflake:

**New model:**
```python
class SigningEntityProfitCenter(Base, CreateMixin, SoftDeleteMixin, UpdateMixin):
    __tablename__ = 'signing_entity_profit_center'
    signing_entity_profit_center_id: Mapped[int] = mapped_column(MEDIUMINT, primary_key=True, autoincrement=True)
    reference_signing_entity_id: Mapped[int] = mapped_column(MEDIUMINT, ForeignKey('reference_signing_entity.reference_signing_entity_id'), nullable=False)
    reference_sap_profit_center_id: Mapped[int] = mapped_column(MEDIUMINT, ForeignKey('reference_sap_profit_center.reference_sap_profit_center_id'), nullable=False)
    # ... audit + soft-delete via mixins
```

**Altered models:**
- `Contract`: add `reference_sap_profit_center_id` + `reference_sap_profit_center` relationship
- `ContractHistory`: same
- `ReferenceSapProfitCenter`: add `display_name` field + reverse relationships to `signing_entity_profit_center` and `Contract`
- `ReferenceSigningEntity`: add reverse relationship to `signing_entity_profit_center`; keep existing `reference_sap_profit_center_id` (legacy) until M3

---

## 11. API Changes

### 11.1 Backend (ows-abacus-contract)

| Endpoint | Change | Milestone |
|----------|--------|-----------|
| `POST /contracts` | Accept `reference_sap_profit_center_id`; validate (SE, PC) pair exists in `signing_entity_profit_center` | M1 (write path) |
| `GET /contracts/:id` | Return `reference_sap_profit_center` (id, profit_center, display_name, company_code) | M1 |
| `PATCH /contracts/:id` | Accept `reference_sap_profit_center_id` change; trigger trigger-driven `contract_history` write; P&L notification | M3 |
| `GET /signing-entities/:id/profit-centers` (NEW) | Return active mappings for an SE (cascading dropdown) | M2 |
| `POST /signing-entity-profit-centers` (admin) | Create a mapping | M2 |
| `PATCH /signing-entity-profit-centers/:id` (admin) | Update / soft-delete a mapping | M2 |
| `PATCH /reference-sap-profit-centers/:id/display-name` (admin) | Update `display_name` (validate non-empty) | M2 |

### 11.2 GraphQL (graphql-abacus)

```graphql
type SapProfitCenter {
  referenceSapProfitCenterId: Int!
  profitCenter: String!
  displayName: String!
  companyCode: String!
  businessGroup: String!
}

type SigningEntityProfitCenter {
  signingEntityProfitCenterId: Int!
  signingEntity: SigningEntity!
  sapProfitCenter: SapProfitCenter!
}

extend type AbacusContract {
  sapProfitCenter: SapProfitCenter
}

extend type SigningEntity {
  authorizedProfitCenters(activeOnly: Boolean = true): [SapProfitCenter!]!
}
```

### 11.3 Frontend (frontend-royalties)

| Feature | Milestone |
|---------|-----------|
| Contract detail view: display `display_name` + SAP code (D18) | M1, FF-gated |
| Contract creation: cascading dropdown (SE → authorized PCs) | M2 |
| Auto-select when SE has exactly one mapping | M2 (D12 — UI logic, not DB) |
| Admin screen: SE↔PC mapping CRUD; PC display_name edit | M2 |

---

## 12. Testing Strategy

See [TEST_PLAN.md](TEST_PLAN.md) for full test cases, environments, and owners.

Key invariants tested at each phase:

- **Post-DDL:** schema matches spec; pre-existing rows unaffected
- **Post-backfill:** zero NULLs on `contract.reference_sap_profit_center_id`; legacy path = new path for every contract; zero NULLs on `display_name`
- **Post-FF-on:** all read consumers produce identical output to pre-migration; settlement feed regression clean (byte-for-byte same)
- **Post-NOT-NULL:** schema enforces non-null; no inserts fail unexpectedly
- **Post-view-switch:** Snowflake view returns identical data to pre-switch
- **Post-FF-removal (M3):** all consumers operate on new path only; old code paths gone
- **Post-column-drop (M3):** `reference_signing_entity` no longer has `reference_sap_profit_center_id`; nothing breaks

---

## 13. Risk Assessment

| Risk | Likelihood | Impact | Mitigation |
|------|-----------|--------|------------|
| Backfill produces incorrect PC mappings | Low | High | §6.5 spot-check; sample 50 contracts and verify legacy = new |
| `sync_contract_sap` emits NULL `Prctr` during dual-path window | Low | High | FF default OFF; switch only after backfill verified |
| Snowflake view switch breaks settlement feed | Low | Critical | Post-NOT-NULL data is equivalent on both paths; settlement feed regression test (T11) |
| Soft-delete + UNIQUE collision when restoring mappings | Low | Low | Documented in §5.1; revisit only if business hits it |
| Signing entity dedup breaks contracts | High | Critical | **Defer to M3** (D8 carries D7) — do not attempt in M1 |
| Business doesn't provide PC names in time | Medium | Medium | Placeholder names (D20); update via SQL UPDATE, no deploy |
| Trigger update missed → PC changes not in history | Medium (process) | High (audit gap) | §5.5 trigger update is a mandatory step; ticket explicitly blocks contract write changes |

---

## 14. Security & Compliance

- No new PII (`display_name` is a business label)
- M2 admin endpoints gated by existing Abacus admin / Finance role permissions
- `contract_history` continues to track every change; the trigger update in §5.5 extends this to PC changes
- No changes to auth patterns or access control for existing endpoints
- Soft-delete enables full audit of mapping retirement without losing history

---

## 15. References

| Document | Purpose |
|----------|---------|
| [PRD.md](PRD.md) | Product requirements, personas, success criteria |
| [SPIKE_SCHEMA_DESIGN.md](SPIKE_SCHEMA_DESIGN.md) | Option comparison (A/B/C/D/E); Option B selected |
| [DECISION_LOG.md](DECISION_LOG.md) | 28 decisions, including D24–D28 for this TRD |
| [ROADMAP.md](ROADMAP.md) | M1/M2/M3 epic + ticket breakdown |
| [MIGRATION_RUNBOOK.md](MIGRATION_RUNBOOK.md) | Operational procedure, verification SQL, rollback |
| [TEST_PLAN.md](TEST_PLAN.md) | Test cases per milestone |
| [OPEN_QUESTIONS.md](OPEN_QUESTIONS.md) | Non-blocking questions and sequencing risks |