# TRD: Single Supply Chain — Foundation Media

**Date:** 2026-05-18
**Author:** Michael Rojas
**Status:** Draft

---

## 1. Overview

Schema normalization of the Abacus royalty accounting database to decouple Signing Entity from Profit Center, enabling many-to-one relationships. Introduces a new `reference_servicing_center` table and adds a FK on `contract` to support Foundation Media, Santa Anna, and OVO — where one legal entity maps to multiple profit centers.

---

## 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

### 2.2 Affected Services

| Service | Role | Impact |
|---------|------|--------|
| `ows-royalties` | Contract CRUD, lifecycle | Accept `reference_servicing_center_id` on contract creation; new reference endpoint |
| `graphql-abacus` | Apollo Federation gateway | New `ServicingCenter` type; extend `AbacusContract` and `SigningEntity` |
| `frontend-royalties` | React SPA | Cascading dropdown on contract creation (M2); read-only display (M1) |
| `ows-abacus-legacy-sync` | MySQL → Snowflake ETL | Replicate `reference_servicing_center` table; update denormalization logic |
| `lambda-abacus` | SAP sync handler | Resolve company_code through `reference_servicing_center` |

### 2.3 External Integrations

| System | Integration | Impact |
|--------|-------------|--------|
| SAP | Settlement feed reads company_code + profit_center from contract view | View updated; feed unaffected (confirmed Robert Kordisch) |
| SAP | Sync-to-SAP job creates contracts in SAP company codes | Must resolve through `reference_servicing_center` |
| SAP | KN&R payable feed uses SAP vendor ID | No change |
| Slaughterhouse | Maps UPC/ISRC to account_id | No change (doesn't touch profit center) |
| Snowflake | Synced from MySQL; denormalizes profit center onto contract | Sync job updated |
| Looker | Reports query Snowflake | New table available after sync; report definitions TBD |
| Payoneer | Payment processing via payment entity | No change (payment entity stays on signing entity) |

---

## 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 profit center"
        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"
```

**Problem:** `company_code` is denormalized on both `reference_signing_entity` and `reference_sap_profit_center`. The contract derives its profit center entirely through the signing entity: `contract -> reference_signing_entity -> reference_sap_profit_center`. There is no way to assign a different profit center 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_servicing_center_id FK "NEW"
        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_servicing_center {
        MEDIUMINT reference_servicing_center_id PK "NEW TABLE"
        VARCHAR servicing_center_name "user-facing label"
        MEDIUMINT reference_signing_entity_id FK
        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 - read-only during transition"
        MEDIUMINT reference_sap_profit_center_id FK "LEGACY - read-only during transition"
        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 (unchanged)"
    contract }o--|| reference_servicing_center : "reference_servicing_center_id (NEW)"
    reference_servicing_center }o--|| reference_signing_entity : "many-to-one"
    reference_servicing_center }o--|| reference_sap_profit_center : "NOT NULL FK"
    reference_signing_entity }o--|| reference_payment_entity : "reference_payment_entity_id (unchanged)"
    reference_signing_entity }o--|| reference_sap_profit_center : "LEGACY - kept during transition"
```

**Key relationships:**
- `reference_signing_entity` 1 --> N `reference_servicing_center` (one legal entity, many profit centers)
- `reference_servicing_center` 1 --> 1 `reference_sap_profit_center` (one servicing center = one SAP profit center)
- `contract` N --> 1 `reference_signing_entity` (unchanged)
- `contract` N --> 1 `reference_servicing_center` (NEW)

### What Changed

| Element | Before | After |
|---------|--------|-------|
| `reference_servicing_center` table | Does not exist | **New table** — maps signing entities to profit centers (many-to-one) |
| `contract.reference_servicing_center_id` | Does not exist | **New FK** — points to `reference_servicing_center` |
| `contract_history.reference_servicing_center_id` | Does not exist | **New FK** — tracks historical changes |
| `reference_signing_entity.company_code` | Drives financial routing | **Legacy** — read-only during transition, do not remove yet |
| `reference_signing_entity.reference_sap_profit_center_id` | Drives profit center lookup | **Legacy** — read-only during transition, do not remove yet |
| Profit center resolution path | `contract -> signing_entity -> sap_profit_center` | `contract -> reference_servicing_center -> sap_profit_center` |
| `reference_payment_entity` | Linked via signing entity | **Unchanged** — payment entity derivation stays on signing entity |
| `contract_template` | Points to signing entity | **Unchanged** — servicing center selected at contract creation time, not template time |

---

## 5. DDL Changes

### 5.1 New table: `reference_servicing_center`

```sql
CREATE TABLE reference_servicing_center (
  reference_servicing_center_id           MEDIUMINT    NOT NULL AUTO_INCREMENT,
  servicing_center_name         VARCHAR(180) NOT NULL,
  reference_signing_entity_id   MEDIUMINT    NOT NULL,
  reference_sap_profit_center_id MEDIUMINT   NOT NULL,
  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) NULL,
  deleted_at                    DATETIME     NULL,

  PRIMARY KEY (reference_servicing_center_id),
  UNIQUE KEY uidx_servicing_center_name (servicing_center_name),
  UNIQUE KEY uidx_signing_entity_sap_pc
    (reference_signing_entity_id, reference_sap_profit_center_id),

  CONSTRAINT fk_servicing_center_signing_entity
    FOREIGN KEY (reference_signing_entity_id)
    REFERENCES reference_signing_entity (reference_signing_entity_id)
    ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT fk_servicing_center_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,

  INDEX idx_servicing_center_signing_entity (reference_signing_entity_id),
  INDEX idx_servicing_center_sap_pc (reference_sap_profit_center_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
```

**Design decisions:**
- `MEDIUMINT` PKs/FKs match existing schema convention
- `SoftDeleteMixin` pattern (`deleted_by`, `deleted_at`) matches `reference_signing_entity`
- `CreateMixin` / `UpdateMixin` audit fields match other reference tables
- `servicing_center_name` globally unique — no two servicing centers share a display name
- `reference_sap_profit_center_id` NOT NULL — SAP profit center row must exist before creating a servicing center; company_code and profit_center are read through this FK
- `ON DELETE RESTRICT` on both FKs — prevents orphaning

### 5.2 Alter `contract`

```sql
ALTER TABLE contract
  ADD COLUMN reference_servicing_center_id MEDIUMINT NULL
    AFTER reference_signing_entity_id,
  ADD CONSTRAINT fk_contract_servicing_center
    FOREIGN KEY (reference_servicing_center_id)
    REFERENCES reference_servicing_center (reference_servicing_center_id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  ADD INDEX idx_contract_reference_servicing_center (reference_servicing_center_id);
```

Nullable initially; NOT NULL enforced after backfill verification.

### 5.3 Alter `contract_history`

```sql
ALTER TABLE contract_history
  ADD COLUMN reference_servicing_center_id MEDIUMINT NULL
    AFTER reference_signing_entity_id,
  ADD CONSTRAINT fk_contract_history_servicing_center
    FOREIGN KEY (reference_servicing_center_id)
    REFERENCES reference_servicing_center (reference_servicing_center_id)
    ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD INDEX idx_contract_history_reference_servicing_center (reference_servicing_center_id);
```

### 5.4 `contract_template` — no change

Templates define terms/exclusions, not financial routing. The servicing center is selected at contract creation time.

---

## 6. Data Migration

### 6.1 Backfill `reference_servicing_center` from existing data

```sql
INSERT INTO reference_servicing_center (
  servicing_center_name, reference_signing_entity_id,
  reference_sap_profit_center_id,
  created_by, created_at, last_modified_by, last_modified
)
SELECT
  CONCAT(rse.legal_name, ' -- ', rspc.profit_center),
  rse.reference_signing_entity_id,
  rse.reference_sap_profit_center_id,
  'system_backfill', NOW(), 'system_backfill', NOW()
FROM reference_signing_entity rse
  JOIN reference_sap_profit_center rspc
    ON rse.reference_sap_profit_center_id = rspc.reference_sap_profit_center_id
WHERE rse.deleted_at IS NULL;
```

Placeholder names (`legal_name -- profit_center`) ensure uniqueness. Business provides real names post-backfill.

### 6.2 Backfill `contract.reference_servicing_center_id`

```sql
UPDATE contract c
  JOIN reference_signing_entity rse
    ON c.reference_signing_entity_id = rse.reference_signing_entity_id
  JOIN reference_servicing_center sc
    ON sc.reference_signing_entity_id = rse.reference_signing_entity_id
    AND sc.reference_sap_profit_center_id = rse.reference_sap_profit_center_id
SET c.reference_servicing_center_id = sc.reference_servicing_center_id;
```

Safe: today's 1:1 means each signing entity maps to exactly one servicing center row.

### 6.3 Backfill `contract_history.reference_servicing_center_id`

```sql
UPDATE contract_history ch
  JOIN reference_signing_entity rse
    ON ch.reference_signing_entity_id = rse.reference_signing_entity_id
  JOIN reference_servicing_center sc
    ON sc.reference_signing_entity_id = rse.reference_signing_entity_id
    AND sc.reference_sap_profit_center_id = rse.reference_sap_profit_center_id
SET ch.reference_servicing_center_id = sc.reference_servicing_center_id;
```

### 6.4 Insert Foundation Media data

```sql
INSERT INTO reference_servicing_center (
  servicing_center_name, reference_signing_entity_id,
  reference_sap_profit_center_id,
  created_by, created_at, last_modified_by, last_modified
) VALUES
  ('Foundation 3P',    20, <sap_pc_id>, 'system', NOW(), 'system', NOW()),
  ('SA-FM Originated', 21, <sap_pc_id>, 'system', NOW(), 'system', NOW()),
  ('Santa Anna',       21, <sap_pc_id>, 'system', NOW(), 'system', NOW()),
  ('OVO Catalog',      22, <sap_pc_id>, 'system', NOW(), 'system', NOW()),
  ('OVO New',          22, <sap_pc_id>, 'system', NOW(), 'system', NOW());
```

Note: `reference_sap_profit_center_id` is NOT NULL — the corresponding rows in `reference_sap_profit_center` (for company code 2755, profit centers US6284/US7219/US6286/US6703/US7260) must be created first. This is a separate migration.

### 6.5 NOT NULL enforcement

After verification (zero NULLs in `contract.reference_servicing_center_id`):

```sql
ALTER TABLE contract MODIFY reference_servicing_center_id MEDIUMINT NOT NULL;
```

---

## 7. Migration Execution Order

```
Step  | Operation                                            | Depends On
------+------------------------------------------------------+-----------
  1   | CREATE TABLE reference_servicing_center                        | --
  2   | ALTER TABLE contract ADD reference_servicing_center_id          | 1
  3   | ALTER TABLE contract_history ADD reference_servicing_center_id  | 1
  4   | INSERT INTO reference_servicing_center (backfill from ref data) | 1
  5   | UPDATE contract SET reference_servicing_center_id               | 2, 4
  6   | UPDATE contract_history SET reference_servicing_center_id       | 3, 4
  7   | Verify: SELECT COUNT(*) WHERE reference_servicing_center_id IS NULL | 5
  8   | INSERT INTO reference_servicing_center (Foundation Media data)  | 1
  9   | ALTER TABLE contract MODIFY ... NOT NULL              | 7
```

Steps 1-3: single DDL migration. Steps 4-6: data migration script. Step 9: separate migration after M1 proven.

---

## 8. What NOT to Change

| Table / Column | Why |
|----------------|-----|
| `reference_signing_entity.company_code` | Keep as read-only legacy; removing before consumer migration breaks downstream |
| `reference_signing_entity.reference_sap_profit_center_id` | Same — legacy column during transition |
| `account_payment_term` | Payment entity derivation unchanged; stays through signing entity |
| `account_contract_vat_detail.company_code` | VAT-specific; set independently |
| Signing entity dedup | High risk (RESTRICT FKs); defer to M2+ after servicing center indirection is proven |

---

## 9. Downstream System Updates

### 9.1 Contract View

`royalty_accounting_prod_view_dim_abacus_contract_view` must be updated to resolve company_code and profit_center through `reference_servicing_center` instead of `reference_signing_entity`:

```sql
-- Before:
SELECT c.*, rse.company_code, rspc.profit_center
FROM contract c
  JOIN reference_signing_entity rse ON ...
  JOIN reference_sap_profit_center rspc ON ...

-- After:
SELECT c.*, rspc.company_code, rspc.profit_center, sc.servicing_center_name
FROM contract c
  JOIN reference_servicing_center sc ON c.reference_servicing_center_id = sc.reference_servicing_center_id
  JOIN reference_sap_profit_center rspc ON sc.reference_sap_profit_center_id = rspc.reference_sap_profit_center_id
```

### 9.2 Sync-to-SAP Job

Must resolve company_code from `reference_servicing_center` instead of `reference_signing_entity` when creating/updating contracts in SAP.

### 9.3 Snowflake Sync

1. Replicate new `reference_servicing_center` table
2. Update Snowflake `contract` to include `reference_servicing_center_id`
3. Update denormalization logic: `contract.reference_sap_profit_center_id` in Snowflake resolves through `reference_servicing_center`

### 9.4 python-abacus-models

Regenerate models for both MySQL and Snowflake:

**New model:**
```python
class ServicingCenter(Base, CreateMixin, SoftDeleteMixin, UpdateMixin):
    __tablename__ = 'reference_servicing_center'
    reference_servicing_center_id: Mapped[int] = mapped_column(MEDIUMINT, primary_key=True, autoincrement=True)
    servicing_center_name: Mapped[str] = mapped_column(String(180), nullable=False)
    reference_signing_entity_id: Mapped[int] = mapped_column(MEDIUMINT, nullable=False)
    reference_sap_profit_center_id: Mapped[int] = mapped_column(MEDIUMINT, nullable=False)
    # ... audit fields, soft delete fields, relationships
```

**Altered models:**
- `Contract`: add `reference_servicing_center_id` + `reference_servicing_center` relationship
- `ContractHistory`: add `reference_servicing_center_id` + `reference_servicing_center` relationship
- `ReferenceSigningEntity`: add `reference_servicing_center` reverse relationship (one-to-many)
- `ReferenceSapProfitCenter`: add `reference_servicing_center` reverse relationship (one-to-many)

---

## 10. API Changes

### 10.1 Backend (ows-royalties)

| Endpoint | Change |
|----------|--------|
| `POST /contracts` | Accept `reference_servicing_center_id`; validate it belongs to the selected `reference_signing_entity_id` |
| `GET /contracts/:id` | Return `reference_servicing_center` object (id, servicing_center_name, company_code, profit_center) |
| `PATCH /contracts/:id` (M3) | Accept `reference_servicing_center_id` change; write to `contract_history`; trigger P&L notification |
| `GET /reference/signing-entities/:id/servicing-centers` (NEW) | Return active servicing centers for a signing entity (powers cascading dropdown) |
| `POST /reference/servicing-centers` (M2, admin) | Create a new servicing center |
| `PATCH /reference/servicing-centers/:id` (M2, admin) | Update a servicing center; soft-delete via `deleted_at` |

### 10.2 GraphQL (graphql-abacus)

```graphql
type ServicingCenter {
  servicingCenterId: Int!
  servicingCenterName: String!
  companyCode: String!
  profitCenter: String!
}

extend type AbacusContract {
  servicingCenter: ServicingCenter
}

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

### 10.3 Frontend (frontend-royalties)

- Contract detail view: display profit center name (M1, read-only)
- Contract creation form: cascading dropdown after signing entity selection (M2)
- Admin screen: CRUD for servicing centers (M2)

---

## 11. Testing Strategy

See [TEST_PLAN.md](TEST_PLAN.md) for the full test plan with test cases, verification queries, environments, and owners.

---

## 12. Risk Assessment

| Risk | Likelihood | Impact | Mitigation |
|------|-----------|--------|------------|
| Backfill produces incorrect reference_servicing_center_id mappings | Low | High | Dry-run mode; verify with `SELECT COUNT(*) WHERE reference_servicing_center_id IS NULL`; compare pre/post contract view output |
| Downstream consumers break when contract view changes | Medium | High | Audit all consumers before changing the view; dual-write period if needed |
| Signing entity dedup breaks contracts | High | Critical | **Defer dedup to M2+** — do not attempt in M1 |
| Business doesn't provide profit center names in time | Medium | Medium | Use placeholder names (legal_name + profit_center); update later |
| Snowflake sync fails to replicate new table | Low | Medium | Test in staging; monitor sync job logs |
| Settlement feed regression | Low | Critical | Robert Kordisch confirmed no impact; add regression test anyway |

---

## 13. Security & Compliance

- No new PII introduced (servicing_center_name is a business label, not personal data)
- Servicing center admin endpoints (M2) gated by existing Abacus admin / Finance role permissions
- Contract history table continues to track all changes for audit compliance
- No changes to auth patterns or access control for existing endpoints