# Spike: Schema Design — Possible Approaches

**Date:** 2026-05-18 (spike); resolved 2026-05-27
**Author:** Michael Rojas
**Status:** **Resolved — Option B selected, refined** (see [DECISION_LOG.md](DECISION_LOG.md) D24)

> **Outcome (2026-05-27):** Option B selected, with two refinements that collapse the open decisions in §9:
>
> 1. The junction (`signing_entity_profit_center`) is **pure** — no `display_name` on it (D25).
> 2. `display_name` lives on `reference_sap_profit_center` (D25), with **no UNIQUE constraint** (resolves §9 Q2).
> 3. Contract gets a **direct FK to `reference_sap_profit_center`** (`contract.reference_sap_profit_center_id`), not to the junction (D24). The junction governs authorization; the contract stores the chosen PC.
>
> §9 Q1 (Option A vs B) — resolved as Option B-refined.
> §9 Q2 (label uniqueness) — no uniqueness on `display_name`; one label per PC, shared across SEs.
> §9 Q3 (Option A naming) — not applicable.
> §9 Q4–Q7 (behavior questions) — see [TRD.md](TRD.md) and [OPEN_QUESTIONS.md](OPEN_QUESTIONS.md).
>
> See [TRD.md](TRD.md) for the canonical technical design; this spike remains for historical context.

---

## Executive Summary

Today, every legal entity that signs contracts in Abacus must have exactly one financial routing code (SAP profit center). Foundation Media needs one legal entity to sign contracts that book revenue to multiple different financial accounts. The current system has no way to express this without creating duplicate legal entities with incorrect names.

**Immediate (A or B):** A new table maps signing entities to profit centers, and contracts point to that mapping instead of deriving profit center from the signing entity. Options A and B are both viable (differ only in naming and where the display label lives); Option C is ruled out. Option E (no mapping table) is not recommended. The choice between A and B depends on whether the business treats this mapping as a named domain concept or just a UI association.

**Long-term (D):** Fully decompose `reference_signing_entity` into separate tables for legal entity, financial routing, and payment routing — completing the original architectural intent. Do A or B now; plan D as a follow-up.

**Why now:** Foundation Media's full catalog migration is expected June/July 2026. Current volume (one delivery in five weeks) is manually manageable; the full catalog is not.

### Scope

**In scope of this document:**
- Schema design options for decoupling signing entity from profit center
- Tradeoffs between the three approaches
- Open decisions needed from product and stakeholders

**Out of scope of this document (to be created after spike is reviewed and approach chosen):**
- Migration execution plan, rollback procedures, verification queries
- Test cases and test schedule
- Full milestone roadmap
- API changes, GraphQL types, frontend work

**Covered in other documents in this directory:**
- Product requirements, personas, success criteria — see [PRD.md](PRD.md)
- Stakeholder decisions and rationale — see [DECISION_LOG.md](DECISION_LOG.md)
- Foundation Media data insert (not part of the schema change)
- SAP profit center creation (D6)
- Payment entity derivation (unchanged — D5)
- Settlement feed changes (unchanged — D4)

---

## 1. The Problem

Every contract in Abacus is linked to a signing entity (the legal entity on the contract). Today, each signing entity is hard-coded to exactly one SAP company code and profit center — the financial routing that determines where revenue is booked and reported. This 1:1 coupling is enforced by the `reference_signing_entity` table.

Foundation Media needs one legal entity to route revenue to multiple profit centers. Without this change, duplicate signing entities with incorrect legal names are required. See [PRD.md](PRD.md) section 2 for the full business case, specific entity IDs, and profit center codes.

### Current Schema

Note: `contract_history` mirrors the `contract` table's FK structure and requires the same changes in all three options. It is omitted from the diagrams for clarity.

```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"
```

**In SAP:** A company code represents a legal accounting unit; a profit center tracks revenue and cost within that unit. The combination determines where money is booked and reported. `reference_sap_profit_center` is an external reference table owned by SAP (D6) — Abacus consumes it but does not create or decompose it further.

---

## 2. The Three Options

All three options introduce a new table that maps signing entities to profit centers. The `contract` table (and `contract_history`) gets a new FK to this table. The options differ in naming, framing, and where the user-facing display label lives.

**In all three options, `reference_signing_entity` loses its direct relationship to `reference_sap_profit_center`.** The `company_code` and `reference_sap_profit_center_id` columns on `reference_signing_entity` become legacy — kept read-only during the transition period while downstream consumers migrate, then removed. After this change, `reference_signing_entity` represents only the legal entity (name, address, VAT, payment entity). Financial routing is fully owned by the new table.

### Option A: New Entity (`reference_servicing_center`)

Treats the mapping as a **first-class domain entity** called "servicing center." The display name (`servicing_center_name`) lives on the new table and is globally unique.

- **Framing:** "A servicing center determines where revenue is booked"
- **Name uniqueness:** Globally unique — no two rows can share the same `servicing_center_name`
- **Label flexibility:** Per-row labels, but the unique constraint prevents reusing a name across different rows (can be dropped if needed — see Recommendation)
- **Tradeoff:** Introduces a new domain concept and naming debate, but forward-looking if servicing center gains more attributes later

### Option B: Junction + Label (`signing_entity_profit_center`)

Treats the mapping as a **junction table** between two existing entities. The display name (`display_name`) lives on the junction row and is not unique.

- **Framing:** "A mapping between signing entities and profit centers, with a label for the UI"
- **Name uniqueness:** Not unique — "The Orchard" can appear on multiple rows across different signing entities and SAP profit centers
- **Label flexibility:** Most flexible — per-row labels that can be shared or distinct as needed
- **Tradeoff:** If servicing center later becomes a real entity with attributes, the junction table would need to be promoted

### Option C: Label on SAP PC (`signing_entity_profit_center` + `reference_sap_profit_center.display_name`)

Treats the mapping as a **pure junction table** (just two FKs, no business attributes). The display name lives on the existing `reference_sap_profit_center` table.

- **Framing:** "SAP profit centers now have user-friendly names, and a mapping table controls which ones are available per signing entity"
- **Name uniqueness:** One label per SAP profit center — shared by all signing entities that use it
- **Label flexibility:** Least flexible — can't have different labels for the same SAP PC under different signing entities, can't group multiple SAP PCs under one label
- **Tradeoff:** Simplest junction table, but modifies an existing table (nullable column added to ~25K rows) and pushes the display name one JOIN further from the contract

---

## 3. Comparison Table

| | **A: New Entity** | **B: Junction + Label** | **C: Label on SAP PC** |
|---|---|---|---|
| New table | `reference_servicing_center` | `signing_entity_profit_center` | `signing_entity_profit_center` |
| Table purpose | First-class entity with its own identity | Junction/mapping table with a display label | Pure junction table (just two FKs) |
| Display name column | `servicing_center_name` (domain identity — globally unique) | `display_name` (UI label — not unique) | `reference_sap_profit_center.display_name` (shared per SAP PC) |
| FK on contract | `contract.reference_servicing_center_id` | `contract.signing_entity_profit_center_id` | `contract.signing_entity_profit_center_id` |
| New domain concept | Yes — "servicing center" | No | No |
| Naming debate | Yes — "servicing center" vs "servicing entity" vs "profit center name" | No — `display_name` is self-describing | No |
| Schema changes | 1 new table, 2 ALTERs (contract, contract_history) | 1 new table, 2 ALTERs (contract, contract_history) | 1 new table, 3 ALTERs (contract, contract_history, reference_sap_profit_center) |
| JOINs to get display name | contract -> new table | contract -> new table | contract -> new table -> sap_profit_center |

### Target Schema

All three options produce roughly the same schema. The placeholders show where each option places its display name.

- **Option A:** `OPT_A_NAME` = `servicing_center_name` (domain name, globally unique)
- **Option B:** `OPT_B_NAME` = `display_name` (UI label, not unique)
- **Option C:** No name on new table; `OPT_C_NAME` = `display_name` on `reference_sap_profit_center`

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

    NEW_TABLE_NAME {
        MEDIUMINT NEW_TABLE_NAME_id PK "NEW TABLE"
        VARCHAR OPT_A_NAME "Option A: servicing_center_name (unique)"
        VARCHAR OPT_B_NAME "Option B: display_name (not unique)"
        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 - removed after transition"
        MEDIUMINT reference_sap_profit_center_id FK "LEGACY - removed after transition"
    }

    reference_sap_profit_center {
        MEDIUMINT reference_sap_profit_center_id PK
        VARCHAR profit_center
        VARCHAR company_code
        VARCHAR business_group
        VARCHAR OPT_C_NAME "Option C only: display_name"
    }

    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--|| NEW_TABLE_NAME : "NEW FK"
    NEW_TABLE_NAME }o--|| reference_signing_entity : "many-to-one"
    NEW_TABLE_NAME }o--|| reference_sap_profit_center : "NOT NULL FK"
    reference_signing_entity }o--|| reference_payment_entity : "unchanged"
    reference_signing_entity }o--|| reference_sap_profit_center : "LEGACY - removed after transition"
```

---

## 4. Display Name Scenarios

The key differentiator between the three options is how display names behave when signing entities and SAP profit centers are shared.

### Scenario 1: Two signing entities share SAP profit center USO980

AWAL and BFM Digital both route revenue to USO980. Each has its own row in the new table pointing to the same SAP profit center.

| | Option A | Option B | Option C |
|---|---|---|---|
| AWAL -> USO980 label | "AWAL -- USO980" | "AWAL -- USO980" | "USO980 label" (shared) |
| BFM Digital -> USO980 label | "BFM Digital -- USO980" | "BFM Digital -- USO980" | "USO980 label" (shared) |
| Can labels differ per signing entity? | Yes — separate rows with different names (unique constraint allows it since names differ) | Yes — separate rows with different display_names | **No** — one label per SAP PC, shared by all |

### Scenario 2: Same label spanning different SAP profit centers ("The Orchard")

The ideal mapping spreadsheet (source: `Ideal Company Code_Profit Center Mapping.xlsx`) shows servicing center "The Orchard" (SVC_CTR_ID 2) mapping to two different SAP profit centers:
- Altafonte -> SAP PC 11 (US9681, company code 2981)
- BFM Digital -> SAP PC 10 (USO980, company code 2963)

| | Option A | Option B | Option C |
|---|---|---|---|
| Altafonte row label | "The Orchard" | "The Orchard" | Label of US9681 (SAP PC 11) |
| BFM Digital row label | Must be different — the unique constraint on `servicing_center_name` prevents any two rows from sharing the same name (constraint can be dropped — see Recommendation) | "The Orchard" | Label of USO980 (SAP PC 10) |
| Same label across different SAP PCs? | **No** by default — but **yes** if the unique constraint is dropped (see Recommendation) | **Yes** | **No** — each SAP PC has its own label |

The Foundation Media case (Santa Anna mapping to both US7219 and US6286) works identically across all three options — it doesn't differentiate them.

---

## 5. Alignment with Source Documents

| Source | What It Says | Best Fit |
|--------|-------------|----------|
| PRD | "Create a new **field** called 'Profit Center Name'" | B or C — describes a field, not an entity |
| Ideal mapping spreadsheet | SVC_CTR_ID 2 ("The Orchard") spans multiple SAP PCs | **B** — A's unique constraint blocks shared names; C ties names to individual SAP PCs |
| Charles Owens (Feb 24) | "What's missing is a user-friendly name/label" on the existing concept | Supports the goal of all three options; most directly describes C's mechanism, but A and B achieve the same goal through a new table |
| Feb 11 meeting | "Revenue servicing entity = a user-friendly name for the combination of company code and profit center" | A, B, or C — uses "servicing entity" language (supports A) but describes a label (supports B/C) |
| Feb 4 meeting | "Introduce a 'servicing entity' to manage scenarios where one legal entity corresponds to multiple profit centers" | A — explicitly proposes a new entity |

No single option perfectly matches all source documents. The Feb 4 kickoff proposed a new entity (A). Later meetings and the PRD itself describe a field or label (B or C). The ideal mapping spreadsheet's "The Orchard" case is the strongest evidence against Option A's unique constraint.

---

## 6. Tradeoffs

The new table structure is nearly identical across all three options: PK + 2 FKs + optional display name + audit fields + soft delete.

| Dimension | A | B | C |
|-----------|---|---|---|
| **Label flexibility** | | | |
| Different labels for same SAP PC under different signing entities | Yes | Yes | **No** |
| Same label spanning multiple SAP PCs | **No** (unless unique constraint dropped) | Yes | **No** |
| Centralized label management (one source of truth for SAP PC names) | No | No | **Yes** |
| **Extensibility** | | | |
| Servicing center gains attributes (stewardship, QC status, etc.) | Easy — add columns | Promote junction to entity | Create new entity from scratch |
| Signing entity dedup (later) | Reparent entity rows | Reparent junction rows | Reparent junction rows |
| **Risk** | | | |
| Naming debate stalls progress | **High** | Low | Low |
| Schema conflicts with spreadsheet's shared SVC_CTR_ID | **Yes** (unless constraint dropped) | No | Partially |
| Modifies existing table | No | No | **Yes** (nullable column on reference_sap_profit_center) |
| Model needs promotion later | N/A (already an entity) | Medium — rename and extend existing junction table | Medium-High — move display_name from sap_profit_center to junction table, then extend |

---

## 7. Recommendation

### Immediate: Option A or B (not C)

Options A, B, and C all solve the immediate problem. **Option C can be ruled out:**
- One label per SAP profit center — no ability to show different names under different business contexts
- Cannot group multiple SAP PCs under a shared label (the "The Orchard" case)
- Modifies an existing table that other systems read (unnecessary when A and B avoid it)
- Pushes the display name one JOIN further from the contract, for no benefit

Options A and B are more flexible in label handling with the same structural complexity. Option C's one advantage — centralized SAP PC name management — is outweighed by the label restrictions.

**Choose Option A if** the business treats this as a named domain concept — something people refer to by name ("the Foundation 3P servicing center"), that might gain attributes over time (stewardship, QC approval status, contact info), and that has its own identity independent of the signing entity + SAP profit center combination.

| Strength | Detail |
|----------|--------|
| Matches the Feb 4 kickoff decision | The group explicitly agreed to "introduce a servicing entity" — a domain concept, not a mapping |
| Forward-looking | If the concept gains attributes, columns are added to the existing table — no restructuring |
| Follows `reference_` naming convention | `reference_servicing_center` is consistent with `reference_signing_entity`, `reference_payment_entity`, etc. |
| Clear identity | Easier for the business to talk about "a servicing center" than "a row in a mapping table" |

| Weakness | Detail |
|----------|--------|
| Naming debate | "Servicing center" vs "servicing entity" vs "profit center name" is unresolved across source documents |
| Unique name constraint | Prevents reusing a label across different rows. Can be dropped if needed, but the application code and UI would also need to handle non-unique names — not just the DDL |

**Choose Option B if** the business treats this as just a mapping with a label — an association between a signing entity and a profit center that happens to need a display name for the UI, but isn't a "thing" in its own right.

| Strength | Detail |
|----------|--------|
| No naming debate | `display_name` is a column, not a concept — no stakeholder alignment needed |
| Most flexible labels | Non-unique display_name supports shared labels, distinct labels, or any combination |
| Matches the ideal mapping spreadsheet | The "The Orchard" case (same label across different SAP PCs) works out of the box |

| Weakness | Detail |
|----------|--------|
| No domain identity | "A row in `signing_entity_profit_center`" is harder for the business to reference than "the Foundation 3P servicing center" |
| Promotion cost | If the concept later becomes a real entity with attributes, the junction table needs to be renamed and extended |

The migration SQL, backfill logic, downstream updates, API changes, and testing strategy are identical between A and B — only table and column names differ. The choice is conceptual, not structural.

---

## 8. Long-term: Option D — cleanup of `reference_signing_entity`

**Option D does not add new query capabilities.** The capability comparison tables below confirm that A/B and D answer the same questions with the same number of JOINs and share the same gaps. D is a cleanup initiative, not a capability upgrade. The one structural advantage D provides is that legal entity dedup becomes trivial (one row per legal name by definition) — under A/B, dedup requires reparenting contracts across `ON DELETE RESTRICT` FKs.

The value of D is forcing the cleanup to actually happen. Options A/B leave `reference_signing_entity` intact with legacy `company_code` and `reference_sap_profit_center_id` columns marked "read-only during transition, then removed." In practice, "read-only during transition" has a high chance of becoming "read-only forever" — nothing forces the removal because the new path works and the old columns don't break anything. D plans the retirement explicitly so it gets done.

`reference_signing_entity` is doing three jobs in one table:

1. **Legal entity** — name, address, VAT, company registration number
2. **Financial routing** — company_code, reference_sap_profit_center_id
3. **Payment routing** — reference_payment_entity_id

Options A/B peel off job #2 into the mapping table but leave the table otherwise intact. The table comment on `reference_payment_entity` reveals the original intent: *"AKA 'paid_by'; meant to replace signing_entity table."* The original architects planned to decompose this table but never finished.

**Option D completes that decomposition.** `reference_signing_entity` is retired and renamed to `reference_legal_entity` with financial routing columns stripped. Each concern gets its own table:

```mermaid
erDiagram
    reference_payment_entity {
        MEDIUMINT reference_payment_entity_id PK
        VARCHAR payment_entity_name
        CHAR country_of_tax_reporting
    }

    reference_legal_entity {
        MEDIUMINT reference_legal_entity_id PK "NEW — replaces reference_signing_entity"
        MEDIUMINT reference_payment_entity_id FK "who pays this legal entity"
        VARCHAR legal_name
        VARCHAR address
        VARCHAR vat_number
        VARCHAR company_registration_number
        VARCHAR tax_entity_company_code
    }

    MAPPING_TABLE {
        MEDIUMINT MAPPING_TABLE_id PK "from Option A or B"
        VARCHAR display_name
        MEDIUMINT reference_legal_entity_id FK
        MEDIUMINT reference_sap_profit_center_id FK "NOT NULL"
    }

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

    contract {
        MEDIUMINT contract_id PK
        MEDIUMINT reference_legal_entity_id FK "who signed (direct)"
        MEDIUMINT MAPPING_TABLE_id FK "financial routing"
        VARCHAR contract_name
        ENUM contract_type
    }

    reference_legal_entity }o--|| reference_payment_entity : "who pays"
    MAPPING_TABLE }o--|| reference_legal_entity : "which legal entity"
    MAPPING_TABLE }o--|| reference_sap_profit_center : "which profit center"
    contract }o--|| reference_legal_entity : "who signed (direct)"
    contract }o--|| MAPPING_TABLE : "financial routing"
```

### Design rationale

**Why `reference_legal_entity_id` appears on both the mapping table and the contract:**

It's on the **mapping table** because the mapping table defines which profit centers are valid for which legal entity. That's its purpose — each row is a (legal entity, profit center) pair.

It's on the **contract** because contracts need a legal entity before they have financial routing. The mapping table FK is nullable — a contract can exist with a known legal entity but no profit center assigned yet. If the legal entity only lived on the mapping table, a contract without financial routing would have no way to record who signed it.

This is the same pattern as Options A/B — the contract keeps its existing `reference_signing_entity_id` FK and gains a new mapping table FK. In Option D, `reference_signing_entity` is renamed to `reference_legal_entity`, so the existing FK simply becomes `reference_legal_entity_id`.

The contract's `reference_legal_entity_id` must match the mapping table row's `reference_legal_entity_id`. This is enforced at the DB level via a composite FK — no application code needed:

```sql
-- Mapping table: add unique key for the composite FK to reference
-- (redundant with PK but required for MySQL composite FK syntax)
ALTER TABLE MAPPING_TABLE
  ADD UNIQUE KEY uidx_id_legal_entity (MAPPING_TABLE_id, reference_legal_entity_id);

-- Contract: composite FK enforces both columns match a single mapping row
ALTER TABLE contract
  ADD CONSTRAINT fk_contract_mapping_legal_entity
    FOREIGN KEY (MAPPING_TABLE_id, reference_legal_entity_id)
    REFERENCES MAPPING_TABLE (MAPPING_TABLE_id, reference_legal_entity_id)
    ON DELETE RESTRICT ON UPDATE CASCADE;
```

**Why payment entity is on the legal entity, not the mapping:** Payment entity is a property of the legal entity. AWAL Digital Limited always pays through AWAL-UK regardless of profit center; Santa Anna always pays through ORCHARD-US for both SA-FM Originated and Santa Anna profit centers. Verified against production data (see prerequisite #1). The mapping table stays focused on financial routing (legal entity + profit center).

**Why a mapping table instead of direct FKs on contract:** The valid combinations of legal entity + SAP profit center are finite and controlled by the business — they exist independently of any contract. The mapping table encodes these valid combinations as a schema-level constraint. A contract points to one pre-approved row; you can't create a contract that pairs Santa Anna with a profit center belonging to AWAL because that row doesn't exist. Without the mapping table, validation would rely entirely on application code. See "Not recommended: Option E" below.

### Key changes from current state

- `reference_signing_entity` retired — split into `reference_legal_entity` (identity + payment entity) and the mapping table (financial routing)
- Contract has two FKs: `reference_legal_entity_id` (who signed, renamed from `reference_signing_entity_id`) and the mapping table FK (where revenue routes)
- Payment entity resolved through: `contract -> reference_legal_entity -> reference_payment_entity`
- Financial routing resolved through: `contract -> mapping table -> reference_sap_profit_center`
- The mapping table created by Option A or B in the immediate fix carries forward (see prerequisite #2 for the catch)

### Strengths and weaknesses

| Strength | Detail |
|----------|--------|
| Eliminates root cause | The table doing three jobs is why the 1:1 coupling existed |
| Preserves contract-to-legal-entity semantics | Novation, audit history, and nullable transitions all work correctly |
| Completes original architectural intent | `reference_payment_entity` was always meant to replace `reference_signing_entity` |
| Future-proof | Changes to legal entity data, financial routing, or payment routing are fully independent |

| Weakness | Detail |
|----------|--------|
| Massively disruptive | Every FK to `reference_signing_entity` must be reparented — `contract` (30+ downstream tables), `contract_history`, `contract_template`, all Snowflake consumers |
| All 82 rows migrated, not just backfilled | Data migration, not just structural change |
| Every service that reads `reference_signing_entity` needs code changes | Not just a new JOIN — a table rename across the stack |
| Inherited consistency constraint | Contract's `reference_legal_entity_id` must match the mapping table row's `reference_legal_entity_id` — same constraint as A/B, not new to Option D |

### Capability comparison: Options A/B vs Option D

#### Questions the schema answers

| Question | A/B | D | Notes |
|----------|-----|---|-------|
| Who signed this contract? | Yes — `contract.reference_signing_entity_id` (1 JOIN) | Yes — `contract.reference_legal_entity_id` (1 JOIN) | Same pattern, renamed table |
| Where does this contract's revenue route? | Yes — `contract.MAPPING_TABLE_id` -> `reference_sap_profit_center` (2 JOINs) | Yes — same (2 JOINs) | Identical |
| Who pays this contract's legal entity? | Yes — `contract.reference_signing_entity_id` -> `reference_signing_entity.reference_payment_entity_id` (2 JOINs) | Yes — `contract.reference_legal_entity_id` -> `reference_legal_entity.reference_payment_entity_id` (2 JOINs) | Same pattern, renamed table |
| What display name does this contract show? | Yes — `contract.MAPPING_TABLE_id` -> `display_name` (1 JOIN) | Yes — same (1 JOIN) | Identical |
| What profit centers are available for a signing/legal entity? | Yes — `MAPPING_TABLE WHERE reference_signing_entity_id = ?` (1 JOIN) | Yes — `MAPPING_TABLE WHERE reference_legal_entity_id = ?` (1 JOIN) | Same pattern, renamed FK |
| What legal entities use a given profit center? | Yes — `MAPPING_TABLE` -> `reference_signing_entity` (2 JOINs) | Yes — `MAPPING_TABLE` -> `reference_legal_entity` (2 JOINs) | Same pattern |
| What is the legal entity's address / VAT / registration? | Yes — on `reference_signing_entity` (but mixed with financial routing columns) | Yes — on `reference_legal_entity` (clean, no unrelated columns) | D is cleaner — table has single responsibility |
| Is a (legal entity, profit center) combination valid? | Yes — row exists in `MAPPING_TABLE` (1 JOIN) | Yes — same (1 JOIN) | Identical |

#### Edge cases

| Edge Case | A/B | D | Notes |
|-----------|-----|---|-------|
| Contract created before profit center is assigned | Yes — `MAPPING_TABLE_id` nullable; `reference_signing_entity_id` populated | Yes — `MAPPING_TABLE_id` nullable; `reference_legal_entity_id` populated | Same — two-FK design supports this in both |
| Profit center reassignment (same legal entity) | Yes — update `MAPPING_TABLE_id`; `reference_signing_entity_id` unchanged | Yes — update `MAPPING_TABLE_id`; `reference_legal_entity_id` unchanged | Same |
| Contract novation (legal entity changes) | Yes — update both FKs | Yes — update both FKs | Same |
| Legal entity changes payment entity | Yes — update `reference_signing_entity.reference_payment_entity_id` | Yes — update `reference_legal_entity.reference_payment_entity_id` | Same — but in A/B, may need to update on duplicate signing entity rows; in D, one row per legal entity |
| Same legal entity, multiple profit centers | Yes — core use case | Yes — core use case | Identical |
| Multiple legal entities share a profit center | Yes — confirmed (D7) | Yes — confirmed (D7) | Identical |
| Soft-delete a mapping | Yes — `deleted_at` on `MAPPING_TABLE` | Yes — same | Identical |
| Contract template binding | Yes — template has `reference_signing_entity_id` only, no direct profit center FK (D9) | Yes — template has `reference_legal_entity_id` only | Same pattern; template has never had a direct profit center FK |
| Legal entity dedup | **Hard** — `ON DELETE RESTRICT` on all FKs; must reparent contracts from duplicate IDs to canonical ID | **Trivial** — one row per legal name by definition; dedup already done during D migration | Key advantage of D |
| Contract's legal entity must match mapping's legal entity | Inherited constraint — `contract.reference_signing_entity_id` must match `MAPPING_TABLE.reference_signing_entity_id` | Same — `contract.reference_legal_entity_id` must match `MAPPING_TABLE.reference_legal_entity_id` | Same constraint in both; inherited from current schema where signing entity is the parent of the mapping |
| `reference_signing_entity` still has financial routing columns | **Yes** — legacy columns kept read-only during transition | **No** — table retired entirely | D eliminates the legacy columns |

#### Questions neither A/B nor D answer

| Question | A/B | D | Where It Lives Instead |
|----------|-----|---|----------------------|
| Who approved this mapping? | No | No | Application layer (M2 scope — section 9, question 7) |
| What is the history of mapping changes? | No | No | Would need a `MAPPING_TABLE_history` table. Note: "what was the routing at contract creation time" is already answered — the contract FK points to a specific mapping row, which is immutable once created. The gap is only for tracking changes to the mapping rows themselves. |
| Which profit centers does SAP consider valid? | No | No | Application-layer validation (F11) |
| What display name should external docs show vs. internal UI? | No | No | Would need a second column or flag |
| Who stewards this mapping? (ownership, contact) | No | No | Easier to add in Option A (entity); B would need promotion |
| What is the accounting history for a profit center? | No | No | Existing ledger tables via contract joins |
| Can a legal entity exist without any mappings? | Schema allows it | Schema allows it | Business rule enforced in application layer if needed |

### Prerequisites to validate before planning Option D

**Prerequisite #1: Verify payment entity is 1:1 with legal entity. — VERIFIED**

The design places `reference_payment_entity_id` on `reference_legal_entity`. This only works if every duplicate legal name in `reference_signing_entity` shares the same payment entity.

Verified 2026-05-18: the following query returns 0 rows against production data. Every legal name that appears multiple times in `reference_signing_entity` shares the same `reference_payment_entity_id`. Payment entity is confirmed 1:1 with legal name — safe to place on `reference_legal_entity`.

```sql
SELECT
  rse.legal_name,
  COUNT(DISTINCT rse.reference_payment_entity_id) AS distinct_payment_entities,
  GROUP_CONCAT(DISTINCT rpe.payment_entity_name ORDER BY rpe.payment_entity_name) AS payment_entities,
  GROUP_CONCAT(rse.reference_signing_entity_id ORDER BY rse.reference_signing_entity_id) AS signing_entity_ids
FROM reference_signing_entity rse
  JOIN reference_payment_entity rpe
    ON rse.reference_payment_entity_id = rpe.reference_payment_entity_id
WHERE rse.deleted_at IS NULL
GROUP BY rse.legal_name
HAVING COUNT(DISTINCT rse.reference_payment_entity_id) > 1;
```

**Prerequisite #2: Signing entity dedup must complete before Option D migration.**

The mapping table created by Options A/B uses `reference_signing_entity_id` as a FK. Option D replaces that with `reference_legal_entity_id`. If `reference_legal_entity` reuses the same PKs as `reference_signing_entity` (clean rename after dedup), the FK values carry forward. If dedup collapses rows to new canonical IDs, all mapping table FKs must be reparented — the migration is not additive.

Sequence: (1) Do A/B now. (2) Complete Humda's signing entity dedup. (3) Rename the deduped `reference_signing_entity` to `reference_legal_entity`, strip financial routing columns. (4) Repoint the mapping table FK from `reference_signing_entity_id` to `reference_legal_entity_id`.

### Not recommended: Option E — financial routing directly on contract

An alternative considered and rejected: add `reference_sap_profit_center_id` directly to `contract` (no mapping table). Each contract explicitly declares its own financial routing.

This is simpler (one ALTER, no new table) and matches Snowflake's denormalized model. But it removes a critical constraint — the mapping table exists to encode valid (legal entity, profit center) combinations as a schema-level constraint. Without it, validation relies entirely on application code, meaning migration scripts, data imports, or direct SQL inserts could create contracts with wrong financial routing and no FK constraint would catch it. Not recommended.

### Recommendation

Do A or B now (6-8 weeks, unblocks Foundation Media). The mapping table created by A or B becomes one component of Option D — no throwaway work.

Whether to plan Option D as a follow-up depends on the team's appetite for cleanup. D adds no new capabilities — it removes legacy columns that will otherwise remain indefinitely, renames `reference_signing_entity` to reflect its actual purpose, and makes dedup trivial. The cost is touching every consumer of `reference_signing_entity`. The benefit is a schema where each table has one job.

---

## 9. Decisions Needed

### Option selection

1. **Is "servicing center" (or whatever it's called) a domain concept or just a mapping?** If the business refers to it by name and it might gain attributes, Option A. If it's just UI plumbing, Option B.
2. **Does the business need the same label to span multiple SAP profit centers?** (The "The Orchard" case.) Note: multiple signing entities sharing the *same* SAP profit center is already confirmed (D7 — AWAL, Mass Appeal, RED all share USO980). The open question is whether the same *display label* should span *different* SAP profit centers. If yes and using Option A, drop the unique constraint on the name column. Option B supports this by default.
3. **If Option A: what is the entity called?** The naming debate ("servicing center" / "servicing entity" / "profit center name") doesn't block engineering — the DB column name and UI label are independent — but it should be resolved before external documentation ships.

### Behavior questions (affect scope regardless of option)

4. **Can contracts exist without a profit center assignment?** Today every signing entity has exactly one profit center (1:1). After this change, a signing entity could have zero active mappings (e.g., all soft-deleted). If contract creation should be blocked when no mapping exists, the new FK on `contract` must eventually be NOT NULL. If contracts can exist without an assignment, it stays nullable. *This affects whether the NOT NULL migration can happen and when.*

5. **Can a mapping ever move between signing entities?** If Santa Anna's "SA-FM Originated" needs to be reassigned to a different legal entity, is that a new row (old one soft-deleted, new one created) or an update to the existing row's `reference_signing_entity_id`? *This affects whether the FK on `contract` remains valid after the move, and whether `contract_history` captures the change correctly.*

6. **For external documents: display name only, or both display name and SAP code?** Charles Owens (Feb 24) confirmed showing both in the Abacus UI (D18). The PRD says "Profit Center Name is what is surfaced in external docs," which could mean display name only. *If external docs show only the display name, a NULL or placeholder is visible to clients. If they show the SAP code too (matching the UI), the placeholder backfill is less urgent.*

7. **Who can create new mappings, and is there an approval step?** Raised in multiple meetings (QC process, Finance review) but never resolved. *Doesn't change the schema, but significantly changes M2 scope — simple CRUD admin screen vs. request/approval workflow.*