# TRD Option C: Add Display Name to `reference_sap_profit_center` + Junction Table

**Date:** 2026-05-18
**Author:** Michael Rojas
**Status:** Draft — alternative to TRD_A and TRD_B

---

## 1. Overview

Add a `display_name` column to the existing `reference_sap_profit_center` table and create a lightweight junction table (`signing_entity_profit_center`) that maps which profit centers are available for each signing entity. No new domain entity. The user-facing label lives on the SAP profit center itself.

See [TRD_COMPARISON.md](TRD_COMPARISON.md) for a side-by-side comparison of all three options.

---

## 2. System Context

Same as TRD_A sections 2.1-2.3.

---

## 3. Current Schema

Same as TRD_A section 3.

---

## 4. Target Schema

### Entity Relationship Diagram — After

```mermaid
erDiagram
    contract {
        MEDIUMINT contract_id PK
        MEDIUMINT reference_signing_entity_id FK
        MEDIUMINT signing_entity_profit_center_id FK "NEW"
        VARCHAR contract_name
        ENUM contract_type
    }

    signing_entity_profit_center {
        MEDIUMINT signing_entity_profit_center_id PK "NEW — junction table"
        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"
        MEDIUMINT reference_sap_profit_center_id FK "LEGACY"
    }

    reference_sap_profit_center {
        MEDIUMINT reference_sap_profit_center_id PK
        VARCHAR profit_center
        VARCHAR company_code
        VARCHAR business_group
        VARCHAR display_name "NEW — user-facing label"
    }

    reference_payment_entity {
        MEDIUMINT reference_payment_entity_id PK
        VARCHAR payment_entity_name
        CHAR country_of_tax_reporting
    }

    contract }o--|| reference_signing_entity : "unchanged"
    contract }o--|| signing_entity_profit_center : "NEW"
    signing_entity_profit_center }o--|| reference_signing_entity : "which signing entity"
    signing_entity_profit_center }o--|| reference_sap_profit_center : "which profit center"
    reference_signing_entity }o--|| reference_payment_entity : "unchanged"
    reference_signing_entity }o--|| reference_sap_profit_center : "LEGACY"
```

**Key relationships:**
- `signing_entity_profit_center` is a pure junction table — no business attributes, just two FKs
- `display_name` lives on `reference_sap_profit_center` — the user-facing label for each SAP profit center
- The junction table controls which profit centers are available for each signing entity
- `contract` points to a specific junction row

### What Changed

| Element | Before | After |
|---------|--------|-------|
| `reference_sap_profit_center.display_name` | Does not exist | **New column** — user-facing label (e.g., "Foundation 3P") |
| `signing_entity_profit_center` table | Does not exist | **New junction table** — maps signing entities to SAP profit centers |
| `contract.signing_entity_profit_center_id` | Does not exist | **New FK** — points to a specific mapping |
| `contract_history.signing_entity_profit_center_id` | Does not exist | **New FK** — tracks historical changes |
| Profit center resolution path | `contract -> signing_entity -> sap_profit_center` | `contract -> signing_entity_profit_center -> sap_profit_center` |

---

## 5. DDL Changes

### 5.1 Add `display_name` to `reference_sap_profit_center`

```sql
ALTER TABLE `reference_sap_profit_center`
  ADD COLUMN `display_name` VARCHAR(180) DEFAULT NULL
    COMMENT 'User-facing label for this profit center (e.g. "Foundation 3P", "The Orchard"). Shown on contracts and external docs. Nullable for existing rows until business provides names.'
    AFTER `business_group`;
```

Nullable because existing rows (~25,000+ SAP profit centers) won't have names immediately. Only the ~82 actively used profit centers need names for the UI.

### 5.2 New table: `signing_entity_profit_center`

```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 'The signing entity (legal entity) side of the mapping.',
  `reference_sap_profit_center_id` MEDIUMINT UNSIGNED NOT NULL
    COMMENT 'The SAP profit center side of the mapping.',
  `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_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='Junction table mapping signing entities to SAP profit centers. Decouples the 1:1 relationship in reference_signing_entity. The display name for each profit center is on reference_sap_profit_center.display_name.';
```

**Design decisions:**
- Pure junction table — no business attributes beyond the two FKs
- `display_name` lives on `reference_sap_profit_center`, not here — one name per SAP profit center, shared across all signing entities that use it
- SoftDeleteMixin for consistency

### 5.3 Alter `contract`

```sql
ALTER TABLE `contract`
  ADD COLUMN `signing_entity_profit_center_id` MEDIUMINT UNSIGNED DEFAULT NULL
    COMMENT 'The specific signing-entity-to-profit-center mapping for this contract. Nullable during backfill; will be enforced NOT NULL after verification.'
    AFTER `reference_signing_entity_id`,
  ADD CONSTRAINT `fk_contract_sepc`
    FOREIGN KEY (`signing_entity_profit_center_id`)
    REFERENCES `signing_entity_profit_center` (`signing_entity_profit_center_id`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  ADD KEY `idx_contract_sepc` (`signing_entity_profit_center_id`);
```

### 5.4 Alter `contract_history`

```sql
ALTER TABLE `contract_history`
  ADD COLUMN `signing_entity_profit_center_id` MEDIUMINT UNSIGNED DEFAULT NULL
    COMMENT 'Historical record of the signing-entity-to-profit-center mapping at the time of this contract change.'
    AFTER `reference_signing_entity_id`,
  ADD CONSTRAINT `fk_contract_history_sepc`
    FOREIGN KEY (`signing_entity_profit_center_id`)
    REFERENCES `signing_entity_profit_center` (`signing_entity_profit_center_id`)
    ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD KEY `idx_contract_history_sepc` (`signing_entity_profit_center_id`);
```

### 5.5 `contract_template` — no change

Same rationale as Option A.

---

## 6. Data Migration

### 6.1 Backfill `display_name` on `reference_sap_profit_center`

```sql
UPDATE `reference_sap_profit_center` rspc
  JOIN `reference_signing_entity` rse
    ON rse.`reference_sap_profit_center_id` = rspc.`reference_sap_profit_center_id`
SET rspc.`display_name` = CONCAT(rse.`legal_name`, ' -- ', rspc.`profit_center`)
WHERE rse.`deleted_at` IS NULL;
```

Note: If multiple signing entities share the same SAP profit center, this UPDATE will run multiple times for the same `rspc` row — last write wins. This is acceptable for placeholder names; business provides real names later.

### 6.2 Backfill `signing_entity_profit_center` from existing 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;
```

### 6.3 Backfill `contract.signing_entity_profit_center_id`

```sql
UPDATE `contract` c
  JOIN `reference_signing_entity` rse
    ON c.`reference_signing_entity_id` = rse.`reference_signing_entity_id`
  JOIN `signing_entity_profit_center` sepc
    ON sepc.`reference_signing_entity_id` = rse.`reference_signing_entity_id`
    AND sepc.`reference_sap_profit_center_id` = rse.`reference_sap_profit_center_id`
SET c.`signing_entity_profit_center_id` = sepc.`signing_entity_profit_center_id`;
```

### 6.4 Backfill `contract_history.signing_entity_profit_center_id`

```sql
UPDATE `contract_history` ch
  JOIN `reference_signing_entity` rse
    ON ch.`reference_signing_entity_id` = rse.`reference_signing_entity_id`
  JOIN `signing_entity_profit_center` sepc
    ON sepc.`reference_signing_entity_id` = rse.`reference_signing_entity_id`
    AND sepc.`reference_sap_profit_center_id` = rse.`reference_sap_profit_center_id`
SET ch.`signing_entity_profit_center_id` = sepc.`signing_entity_profit_center_id`;
```

### 6.5 NOT NULL enforcement (deferred)

```sql
ALTER TABLE `contract` MODIFY `signing_entity_profit_center_id` MEDIUMINT UNSIGNED NOT NULL;
```

---

## 7. Migration Execution Order

```
Step  | Operation                                                          | Depends On
------+--------------------------------------------------------------------+-----------
  1   | ALTER TABLE reference_sap_profit_center ADD display_name            | --
  2   | UPDATE reference_sap_profit_center SET display_name (backfill)      | 1
  3   | CREATE TABLE signing_entity_profit_center                           | --
  4   | INSERT INTO signing_entity_profit_center (backfill)                 | 3
  5   | ALTER TABLE contract ADD signing_entity_profit_center_id             | 3
  6   | UPDATE contract SET signing_entity_profit_center_id                  | 4, 5
  7   | ALTER TABLE contract_history ADD signing_entity_profit_center_id     | 3
  8   | UPDATE contract_history SET signing_entity_profit_center_id          | 4, 7
  9   | Verify: SELECT COUNT(*) WHERE signing_entity_profit_center_id IS NULL | 6
 10   | INSERT Foundation Media data (separate migration)                    | 3
 11   | ALTER TABLE contract MODIFY ... NOT NULL                             | 9
```

Steps 1-8 can be a single Liquibase migration file.

---

## 8. What NOT to Change

Same as TRD_A section 8.

---

## 9. Downstream System Updates

### 9.1 Contract View

```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, rspc.display_name
FROM contract c
  JOIN signing_entity_profit_center sepc
    ON c.signing_entity_profit_center_id = sepc.signing_entity_profit_center_id
  JOIN reference_sap_profit_center rspc
    ON sepc.reference_sap_profit_center_id = rspc.reference_sap_profit_center_id
```

### 9.2-9.4

Same pattern as TRD_A/B with different table/column names.

---

## 10. API Changes

### 10.1 Backend (ows-royalties)

| Endpoint | Change |
|----------|--------|
| `POST /contracts` | Accept `signing_entity_profit_center_id`; validate it belongs to the selected `reference_signing_entity_id` |
| `GET /contracts/:id` | Return profit center info (id, display_name, company_code, profit_center) |
| `PATCH /contracts/:id` (M3) | Accept `signing_entity_profit_center_id` change; write to `contract_history`; trigger P&L notification |
| `GET /reference/signing-entities/:id/profit-centers` (NEW) | Return available profit centers for a signing entity (powers cascading dropdown) |
| `POST /reference/signing-entity-profit-centers` (M2, admin) | Create a new mapping |
| `DELETE /reference/signing-entity-profit-centers/:id` (M2, admin) | Soft-delete a mapping |

### 10.2 GraphQL (graphql-abacus)

```graphql
type ProfitCenterMapping {
  signingEntityProfitCenterId: Int!
  displayName: String
  companyCode: String!
  profitCenter: String!
}

extend type AbacusContract {
  profitCenterMapping: ProfitCenterMapping
}

extend type SigningEntity {
  availableProfitCenters: [ProfitCenterMapping!]!
}
```

Note: `displayName` is nullable because `reference_sap_profit_center.display_name` may not be populated for all profit centers.

### 10.3 Frontend (frontend-royalties)

Same as Option B.

---

## 11. Testing Strategy

See [TEST_PLAN.md](TEST_PLAN.md) — structurally identical with table/column name substitutions.

---

## 12. Risk Assessment

Same as TRD_A, plus:

| Risk | Likelihood | Impact | Mitigation |
|------|-----------|--------|------------|
| `display_name` backfill clobbers when multiple signing entities share a SAP PC | Medium | Low | Placeholder names only; real names provided by business override |
| `display_name` nullable means UI must handle NULLs | Medium | Low | Fall back to `profit_center` code when `display_name` is NULL |
| 25,000+ SAP profit center rows get a new column | Low | Low | Nullable column addition is instant; no data migration for unused rows |

---

## 13. Pros and Cons

**Pros:**
- Simplest junction table — pure mapping, no business attributes
- `display_name` lives on the entity it describes (`reference_sap_profit_center`) — single source of truth
- If two signing entities share the same SAP profit center, they automatically share the same display name (consistent)
- Smallest new table — just two FKs + audit fields
- No new naming debate — "display_name" is self-describing

**Cons:**
- `display_name` is nullable on a table with 25,000+ rows — most will be NULL forever
- If the business wants different labels for the same SAP profit center under different signing entities, this model can't support it (Option B can)
- The ideal mapping spreadsheet shows SVC_CTR_ID 2 ("The Orchard") mapping to different SAP PCs — under this model, each SAP PC would be labeled separately, not grouped under a shared name
- One extra JOIN to get the display name (contract -> junction -> sap_profit_center) — same as Option B, but the display name is one table further away than in Option B
- Modifying an existing table (`reference_sap_profit_center`) that other systems read — lower risk since it's just adding a nullable column, but worth noting