External ID Restructuring

Context

This feature was delivered as part of the XML ingestion redesign. See the redesign hub (engineering documentation) for working notes, alignment decisions, and references.

References

Linear issues:

• D2D-2: Design: broadcaster integration - direct XML
• D2D-11: Design: broadcaster integration - production company connection

Source decisions:

• Field Reference v5.4: “This ID must be unique across all projects” (episode ID constraint)
• Field Reference v5.4: “Record Identification — all ID fields are internal identifiers defined by the broadcaster, stored in broadcaster-specific namespaces”
• CDN x TEP weekly catch-up (13/04/26): auto-generated episode IDs
• XSD v1.1: post-TX <Publication> has only episodeId — no supplier or project context
• CDN Integration-Lite proposal (circulated 27/03/26)

Working docs (engineering): 09-id-strategy-and-scenarios.md, 01-data-model-redesign.md, 04-integration-lite.md

Dependencies: Build: XML - Episode Data Model (episode data model must be in place for episode-level external IDs).

---

Overview

The XML integration requires mapping between broadcaster-specific identifiers (supplier IDs, project IDs, episode IDs) and TEP’s internal identifiers. The current implementation uses externalIdentifiers objects with funder slugs as keys on both company and screenProject documents.

This feature restructures external ID storage into a three-level design optimised for the queries we need:

Why This Matters

When a broadcaster sends post-transmission data, the only identifier in the XML is the episode ID — there is no supplier ID, project ID, or any other context. TEP must go directly from that episode ID to the correct episode in the database. This means episode IDs must be stored on episode documents where they can be looked up efficiently.

Additionally, when a new supplier is linked to a production company, all existing projects from that supplier need to be transferred in one operation. This requires the supplier ID to be queryable on project documents.

The Three Levels

1. Supplier ID → Production Company — stored on the broadcaster’s company document. Used during XML processing to identify which production company a supplier belongs to.

2. Supplier ID + Project ID — stored on the project document. Used during XML processing to match incoming data to the correct project, and for bulk transfer of all projects when a supplier is first linked.

3. Episode ID — stored on the episode document. Used to match post-transmission data to the correct episode. Each episode ID must be unique across all projects for a given broadcaster — this is enforced by a database constraint.

Auto-Generated Episode IDs

When a funder is linked to a project, episode IDs are auto-generated for all episodes that don’t already have one for that funder. This applies universally — whether the project was created via XML, the TEP UI by a broadcaster, or by a production company. The IDs are visible to both parties and editable. The exact format should be short and human-friendly (broadcasters will use these in spreadsheets) while being unique per broadcaster. This was agreed at the CDN x TEP weekly catch-up on 13 April 2026.

Optional Supplier ID on Project Creation

The existing createScreenProject mutation is extended with an optional supplierId field. Any company creating a project can optionally provide a supplier ID — there is no separate broadcaster project creation flow. The supplier ID is stored in the project’s externalIds array.

S3 Bucket Setup for Integration-Lite

Broadcasters using Ben’s spreadsheet upload tool (instead of XML) need an S3 bucket configured for their post-TX data. This is the same infrastructure as XML broadcasters — the difference is that files come from Ben’s tool rather than automated systems.

---

Acceptance Criteria

Funder Company: Supplier Mappings

• Funder company document stores supplierMappings: { "<supplierId>": "<prodCoCompanyId>" }
• Lookup by known supplier ID is O(1) (in-memory on the loaded document)
• Multiple supplier IDs can map to the same production company
• supplierMappings is populated during supplier linking (Feature 5)
• Old externalIdentifiers structure on company documents is migrated/removed

ScreenProject: External IDs

• ScreenProject stores externalIds as an array: [{ funderCompanyId, supplierId, projectId }]
• Compound index on { "externalIds.funderCompanyId": 1, "externalIds.projectId": 1 } for project matching
• Compound index on { "externalIds.funderCompanyId": 1, "externalIds.supplierId": 1 } for bulk transfer
• Pre-TX ingestion: finds existing project by $elemMatch: { funderCompanyId, projectId }
• Bulk transfer query: $elemMatch: { funderCompanyId, supplierId } finds all projects for a supplier
• Prod-co-initiated projects may have an empty externalIds array (no broadcaster IDs)
• Old externalIdentifiers structure on screenProject documents is migrated/removed

Episode: External Episode IDs

• Episodes store externalEpisodeIds as an array: [{ funderCompanyId, episodeId }]
• Unique compound index on { "externalEpisodeIds.funderCompanyId": 1, "externalEpisodeIds.episodeId": 1 } enforces per-broadcaster uniqueness
• Post-TX ingestion: finds episode by $elemMatch: { funderCompanyId, episodeId }
• Duplicate episode ID for the same broadcaster causes a database error (caught and reported)
• Different broadcasters can use the same episode ID value (scoped by funderCompanyId)

Auto-Generated Episode IDs

• When a funder is linked to a project, episode IDs are auto-generated for all episodes that don’t already have an ID for that funder
• IDs are auto-generated universally (XML, broadcaster UI, and prod-co-initiated flows)
• Format is short and human-friendly while being unique per broadcaster
• IDs are editable by either party
• Adding new episodes to a project generates the next incremental index
• Episode IDs are visible in the dashboard for both broadcaster and production company

Optional Supplier ID on Project Creation

• createScreenProject mutation accepts an optional supplierId parameter
• If supplierId is provided, it is stored in externalIds on the project
• If supplierId is provided and is already mapped to a prod co, the project is created under that prod co with status = APPROVED
• If supplierId is provided and is unknown, the project is created under the creating company with status = DRAFT
• If supplierId is not provided, no externalIds entry is created (standard project creation)

S3 Bucket Setup

• S3 buckets can be configured for integration-lite broadcasters (same infrastructure as XML)
• AWS credentials are provided to each broadcaster using Ben’s upload tool

Maker IDs

• Episodes store makerIds as an array: [{ funderCompanyId, makerId }]
• Compound index for reporting scope queries
• If makerId is omitted in XML, defaults to the parent Supplier.id (ref: Field Reference v5.4)

funderSlug Removal

• All references to funderSlug in the codebase are replaced with funder company ObjectId
• No funderSlug in database documents, queries, or ingestion configuration

---

Technical Specification

Data Structures

Funder company document:

{
  "supplierMappings": {
    "supplier123": "<prodCoCompanyId>",
    "supplier456": "<prodCoCompanyId>"
  }
}

Note: supplierMappings remains an object-with-known-keys (not an array) because it’s only queried by known key in-memory, never searched/filtered via database query.

ScreenProject document:

{
  "externalIds": [
    {
      "funderCompanyId": "<bbcCompanyId>",
      "supplierId": "supplier123",
      "projectId": "project456"
    }
  ]
}

Episode document:

{
  "externalEpisodeIds": [
    { "funderCompanyId": "<bbcCompanyId>", "episodeId": "episode789" }
  ],
  "makerIds": [
    { "funderCompanyId": "<bbcCompanyId>", "makerId": "maker123" }
  ]
}

Indexes

// ScreenProject
db.screenProjects.createIndex({ "externalIds.funderCompanyId": 1, "externalIds.projectId": 1 })
db.screenProjects.createIndex({ "externalIds.funderCompanyId": 1, "externalIds.supplierId": 1 })

// Episodes
db.episodes.createIndex(
  { "externalEpisodeIds.funderCompanyId": 1, "externalEpisodeIds.episodeId": 1 },
  { unique: true }
)
db.episodes.createIndex({ "makerIds.funderCompanyId": 1, "makerIds.makerId": 1 })

XML Ingestion Changes

services/ProjectIdentificationServiceDom.ts:

• Replace externalIdentifiers[funderSlug].supplierId lookup with supplierMappings[supplierId] on the funder company document
• Replace project lookup by externalIdentifiers[funderSlug].projectId with $elemMatch query on externalIds
• Replace episode lookup with $elemMatch query on externalEpisodeIds
• All references to funderSlug replaced with funder company ObjectId (known from the S3 bucket configuration)

config/types.ts:

• FunderConfig.companyId is already present — ensure it’s used everywhere instead of slug-based lookups

Auto-Generated Episode ID Logic

When a project is linked to a broadcaster (either at project creation via XML, broadcaster-initiated integration-lite, or prod-co-initiated when selecting a funder):

async function generateEpisodeIds(projectId: ObjectId, funderCompanyId: ObjectId): Promise<void> {
  const episodes = await db.episodes.find({ projectId }).toArray();
  const broadcasterId = funderCompanyId.toString();

  for (let i = 0; i < episodes.length; i++) {
    const episode = episodes[i];
    const hasIdForFunder = episode.externalEpisodeIds?.some(
      e => e.funderCompanyId.equals(funderCompanyId)
    );

    if (!hasIdForFunder) {
      // Format TBD — should be short and human-friendly while unique per broadcaster
      const episodeId = generateUniqueEpisodeId(broadcasterId, projectId, i + 1);
      await db.episodes.updateOne(
        { _id: episode._id },
        { $push: { externalEpisodeIds: { funderCompanyId, episodeId } } }
      );
    }
  }
}

GraphQL Schema Changes

Current state: externalIdentifiers is NOT exposed in the GraphQL schema — it exists only at the database level. No fields on ScreenProject or Company types expose external IDs, and no mutations manage them via GraphQL.

Changes needed:

The new external ID structure should be exposed for the broadcaster dashboard (viewing owned/unlinked projects, seeing supplier IDs, viewing episode IDs):

# Add to ScreenProject type
type ScreenProjectExternalId {
  funderCompanyId: ObjectID!
  supplierId: String
  projectId: String
}

extend type ScreenProject {
  externalIds: [ScreenProjectExternalId!]
}

# Add to Episode type
type EpisodeExternalId {
  funderCompanyId: ObjectID!
  episodeId: String!
}

extend type Episode {
  externalEpisodeIds: [EpisodeExternalId!]
}

# Add to Company type (for viewing supplier mappings)
extend type Company {
  supplierMappings: JSONObject    # { supplierId: prodCoCompanyId }
}

Note: External IDs are primarily written by the XML ingestion pipeline, not via GraphQL mutations. The existing createScreenProject mutation is extended with an optional supplierId parameter — if provided, externalIds is populated on the project. Episode IDs are auto-generated when a funder is linked (not user-input via mutations).

Resolver changes:

• Add field resolvers for externalIds on ScreenProject
• Add field resolvers for externalEpisodeIds on Episode
• Add field resolver for supplierMappings on Company (filtered by authorization — a funder should only see their own mappings)

Database Type Changes

ScreenProject types (types.ts):

• Replace externalIdentifiers?: { [funderSlug: string]: ScreenProjectExternalIdentifier } with externalIds?: Array<{ funderCompanyId: ObjectId, supplierId?: string, projectId?: string }>
• Remove ScreenProjectExternalIdentifier interface

Company types (types.ts):

• Add supplierMappings?: Record<string, ObjectId>
• Replace externalIdentifiers?: { [funderSlug: string]: CompanyExternalIdentifier } with supplierMappings
• Remove CompanyExternalIdentifier interface

Episode types (types.ts):

• Add externalEpisodeIds?: Array<{ funderCompanyId: ObjectId, episodeId: string }>
• Add makerIds?: Array<{ funderCompanyId: ObjectId, makerId: string }>

Migration

No data migration needed — externalIdentifiers are not yet in production use. The changes are:

• Replace the externalIdentifiers code with the new externalIds, externalEpisodeIds, and supplierMappings structures
• Create new indexes
• Remove all funderSlug references from codebase
• Update MongoDB validation schemas for screenProject, episode, and company collections

---

Scenario Verification

This feature must be verified against all 9 scenarios in 09-id-strategy-and-scenarios.md (engineering working docs):

1. XML pre-TX, known supplier, known project — project matched via externalIds
2. XML pre-TX, known supplier, new project — new externalIds entry created
3. XML pre-TX, unknown supplier — project created under funder, externalIds stores supplier ID for bulk transfer
4. XML post-TX — episode matched via externalEpisodeIds
5. XML post-TX, unknown episode — rejected
6. Integration-lite, broadcaster initiated — auto-generated episode IDs
7. Integration-lite, prod co initiated — auto-generated episode IDs on funder linking
8. Ben’s tool for post-TX — auto-generated episode IDs match correctly
9. Producer project + XML broadcaster — duplicate risk (operational, not solved by this feature)

---

Testing Requirements

• Unit tests for each ID lookup path (supplier → company, project matching, episode matching)
• Unit tests for auto-generated episode ID format and uniqueness
• Integration test: duplicate episode ID for same broadcaster triggers unique constraint error
• Integration test: same episode ID from different broadcasters succeeds (scoped correctly)
• E2E tests for all 9 scenarios via test harness