CWR and DDEX Lineage Tracking Patterns

A payee dispute rarely starts with “the hash doesn’t match” — it starts with “why was I paid this amount, and where did that number come from.” Answering that requires walking a provenance path from a DSP usage row through its matched isrc, the iswc it resolves to, the underlying CWR work registration, the DDEX release it was delivered under, and finally the payout line itself. As part of the Royalty Audit Trail Reference, the patterns below focus specifically on how to model, store, and query that provenance path once individual batches are already tamper-evident.

Lineage Storage Trade-offs

Approach Query Pattern Write Complexity Schema Evolution Royalty Impact
Graph database (e.g., property graph over isrc/iswc/work_id nodes) Native multi-hop traversal, “why was this payee paid” resolves in one query Higher — separate write path from the relational ledger Flexible — new edge types added without migrating existing data Best for ad hoc, deep provenance queries during a dispute, at the cost of running and reconciling a second data store alongside the ledger
Junction tables in the relational store Multi-hop requires recursive CTEs or repeated joins Lower — same transaction as the ledger write Rigid — new relationship types need new tables or columns Keeps lineage transactionally consistent with the payout it describes, but deep traversal queries get expensive past four or five hops
Denormalized lineage snapshot per payout Single-row lookup, no traversal needed Highest — must be rebuilt whenever any upstream identifier changes Brittle — a schema change upstream means reprocessing every snapshot Fastest possible dispute response for a single payout, but stale snapshots risk showing a lineage that no longer matches a corrected upstream mapping
Event-sourced edges (append lineage as immutable events, materialize a view) Query the materialized view; full history via event replay Moderate — same append-only discipline as the audit log itself Flexible — new edge types are just new event types Preserves every historical lineage state, so a corrected iswc mapping doesn’t erase how a past payout was actually justified at the time

Most production pipelines land on junction tables for the transactional path, with a periodic export into a graph structure or DuckDB view for investigation tooling — giving auditors traversal ergonomics without making the payout transaction itself depend on a second database.

Prerequisites & Assumptions

The patterns below assume pyarrow>=14, polars>=0.20, and DuckDB for querying lineage edges, with identifiers already normalized by the matching logic in ISRC to ISWC Mapping Workflows. CWR work registrations are assumed to be parsed CWR 2.1/3.0 transaction records reduced to a work_id, and DDEX releases are assumed to be parsed ERN messages reduced to a release_id, both keyed consistently with the batch and event identifiers used in the parent reference’s audit log.

Implementation

Step 1: Define the lineage edge schema

Model each relationship as a typed, directed edge rather than a loose foreign key, so new relationship types can be added without altering existing rows.

python 
from datetime import datetime, timezone
from typing import Literal

from pydantic import BaseModel, Field

NodeType = Literal["dsp_usage_row", "isrc", "iswc", "work_id", "release_id", "payout_id"]


class LineageEdge(BaseModel):
    src: str
    src_type: NodeType
    dst: str
    dst_type: NodeType
    batch_id: str
    reporting_period: str
    recorded_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))

CWR and DDEX describe two different objects — a musical work versus a sound recording release — that must be joined through the iswc and isrc respectively before a lineage edge between them means anything.

python 
import polars as pl


def link_cwr_to_ddex(
    cwr_registrations: pl.DataFrame,  # columns: work_id, iswc, territory_code
    ddex_releases: pl.DataFrame,      # columns: release_id, isrc, iswc
) -> pl.DataFrame:
    return cwr_registrations.join(
        ddex_releases, on="iswc", how="inner", suffix="_release"
    ).select(["work_id", "iswc", "release_id", "isrc", "territory_code"])

Step 3: Persist edges and materialize a queryable lineage view

python 
import duckdb


def materialize_lineage_view(con: duckdb.DuckDBPyConnection) -> None:
    con.execute("""
        CREATE OR REPLACE VIEW payout_lineage AS
        SELECT
            u.src AS usage_row_id,
            i.dst AS isrc,
            w.dst AS iswc,
            r.dst AS work_id,
            p.dst AS payout_id,
            p.batch_id,
            p.reporting_period
        FROM lineage_edges u
        JOIN lineage_edges i ON i.src = u.dst AND i.src_type = 'isrc'
        JOIN lineage_edges w ON w.src = i.dst AND w.src_type = 'iswc'
        JOIN lineage_edges r ON r.src = w.dst AND r.src_type = 'work_id'
        JOIN lineage_edges p ON p.src = r.dst AND p.dst_type = 'payout_id'
        WHERE u.src_type = 'dsp_usage_row'
    """)

Step 4: Answer “why was this payee paid” from the materialized view

python 
def trace_payout(con: duckdb.DuckDBPyConnection, payout_id: str) -> list[dict]:
    result = con.execute(
        "SELECT * FROM payout_lineage WHERE payout_id = ?", [payout_id]
    ).fetchall()
    columns = [desc[0] for desc in con.description]
    return [dict(zip(columns, row)) for row in result]

Verification & Validation

Before trusting a traced lineage path, confirm three properties: every edge in the path belongs to the same reporting_period as the payout it terminates in (a path spanning periods usually indicates a stale or orphaned edge from an earlier reprocessing run); the iswc resolution rate for the traced isrc is unambiguous — exactly one live isrc → iswc edge should exist for the period, not several competing ones; and the terminal work_id → payout_id edge’s batch_id matches a batch_id with a verified hash chain, tying lineage correctness back to the tamper-evidence guarantees described in SHA-256 Batch Hashing for Tamper-Evident Royalty Logs. Log every trace query itself as an audit event, since “who looked up this payee’s lineage and when” is frequently part of what an auditor wants to see.

Edge Cases & Gotchas

Orphaned ISWCs — an isrc that resolves to no iswc at all because the underlying work was never registered via CWR — will terminate a lineage path early; these should surface as an explicit gap in the trace output rather than a silent empty result, so operators can distinguish “not yet registered” from “lookup failed.” Territory-code mismatches between a CWR registration’s territory_code and a DDEX release’s distribution territories can produce a technically valid join that nonetheless traces to the wrong rights holder for that territory — always carry territory_code through the join rather than dropping it after the first hop. ERN namespace drift across DDEX versions (ERN 3.x versus 4.2 element names) can leave release_id values inconsistently formatted between older and newer deliveries, breaking the join predicate silently; normalize release identifiers to a single canonical form before they ever enter the lineage edge table. Finally, a corrected iswc mapping must never overwrite an existing lineage edge in place — append a new edge and mark the old one superseded, or a historical payout’s justification will retroactively appear to reference a mapping that didn’t exist when the payout was actually issued.

For the hashing and chaining mechanics that make the batches referenced in these lineage edges verifiable in the first place, see SHA-256 Batch Hashing for Tamper-Evident Royalty Logs, both part of the Royalty Audit Trail Reference.