The data model

What this part does

The data model answers the same small set of questions for every sport. A new sport is added by settings and a plugin, not by new tables.

Why it matters: every other part of omnium reads and writes these rows. Commands change them. Feeds are built from them. If you know these tables, you can follow any change from the scorer's tap to the client file.

How it works

1. A sport is a row. sport has a unique code such as football or athletics. A sport can sit under a parent sport (Aquatics holds swimming and diving).
2. Competitions form a tree. Each competition row points at its parent with parent_id. The tree can be any depth. Its type is a free word: games, league, season, session, event, stage. A Games root has no sport, so one tree can hold forty sports.
3. A fixture hangs off one competition node. It carries its own sport_id, an archetype, a status, a start time and three JSONB documents: format (rules before), live_state (now) and result (after).
4. Sides and entries are fixture_competitor rows. A football match has two top-level rows. A 100 m final has eight. A player nests under their team through parent_id. A slot that is not decided yet ("Winner of QF1") has a placeholder_label instead of a participant.
5. Every party is one participant row. A person, team, pair, crew or horse. A country is a participant too, kept in the Commons: no sport, so every sport sees the same India.
6. Outside ids sit in a side table. external_id_map links (source_code, entity_type, external_id) to our internal_id. One fixture can have many outside ids, one per source.
7. Change is logged, not lost. Each fixture has an ordered log in timeline_item. The scoreboard row is a cache of the headline, rebuilt from that log. Structure edits (start time, sides) also write an insert-only row to fixture_amendment.

The two shapes: head-to-head and ranked field

Most contests in any sport are one of two shapes. The shape is stored on every fixture in fixture.archetype.

The enum has two more values for later shapes. They exist in code, but no fixture on the laptop database uses them.

class Archetype(StrEnum):
    """How a fixture arrives at its result ...
    ``SERIES`` (a set of attempts: high jump, weightlifting, diving) and
    ``COMPOSITE`` (a result folded from child fixtures: a Davis Cup tie, a
    best-of-three) join the two originals. The column is a bare VARCHAR ...
    so the set grows without DDL, and Python is what enforces it.
    """

    HEAD_TO_HEAD = "HEAD_TO_HEAD"
    RANKED_FIELD = "RANKED_FIELD"
    SERIES = "SERIES"
    COMPOSITE = "COMPOSITE"

packages/contract/src/omnium_contract/models/enums.py:11-26

• HEAD_TO_HEAD and RANKED_FIELD are the two shapes in real use.
• SERIES is for attempt sports (high jump, weightlifting). Each attempt is a fixture_attempt row.
• COMPOSITE is a result built from child fixtures, linked by fixture_relation.
• The column is a plain VARCHAR(32) with no database check. Adding a value needs no migration. Python is the only guard.

How an import picks the shape. The Games fixture import decides per unit: two sides means head-to-head, anything else is a ranked field.

        archetype=Archetype.HEAD_TO_HEAD if unit.head_to_head else Archetype.RANKED_FIELD,

packages/core/src/omnium_core/ingest/fixtures.py:1080

A draw is stored two ways today. A match scored live by the football plugin puts both sides on rank 1 ("A draw is both sides on rank 1", packages/flows/src/omnium_flows/football/actions.py:169-171). The imported Games match above has rank empty on both sides. A reader must handle both. This is a gap, not a design choice.

Where is the standings overlay? CLAUDE.md and the API spec describe a third idea, AGGREGATE_CLASSIFICATION: a table (a league table, a general classification) folded over many fixtures. It is not a fixture. The old classification and classification_standing tables were dropped in migration 041. A standings table today is a stat_table built by the stats engine:

    classification, classification_standing  the workflow's own standings path.
                                           A table above the match is a
                                           ``stat_table`` now, computed the same
                                           way for every sport from the finishing
                                           place on each line

packages/core/alembic/versions/20260908_041_one_writer_one_queue.py:12-16

So the overlay still exists as an idea. Its home is the stats engine, covered in Stats, feeds and delivery.

A Games is a competition tree

A multi-sport event is one competition tree. The root has sport_id empty. Each sport is a child node with a sport. Everything below a sport node carries that sport.

The real Asian Games 2026 tree on the laptop database (7 Oct):

That is 2,709 nodes and 8,917 fixtures under one root. The type words come from each import, so they are not the same across sports. Code must not depend on a type word to find the sport. It must use sport_id.

Why the root has no sport, in the model's own words:

class Competition(UUIDPk, Timestamps, Base):
    """A container node in the sport's grouping tree (= the API ``Event`` resource).
    ...
    ``sport_id`` is **nullable**, and that is what makes a multi-sport event
    possible. The Olympic Games belongs to no sport; the swimming node inside it
    does, and everything below that inherits it. Without this an Olympics has to
    be split into forty unrelated competitions with nothing tying them together,
    and a medal table cannot be folded at all.
    """

    __tablename__ = "competition"
    __table_args__ = (
        sa.UniqueConstraint("sport_id", "code"),
        sa.Index(
            "uq_competition_code_no_sport",
            "code",
            unique=True,
            postgresql_where=sa.text("sport_id IS NULL"),
        ),
        sa.Index("ix_competition_parent_id", "parent_id"),
    )

    sport_id: Mapped[uuid.UUID | None] = mapped_column(sa.ForeignKey("sport.id"), nullable=True)
    parent_id: Mapped[uuid.UUID | None] = mapped_column(
        sa.ForeignKey("competition.id"), nullable=True
    )
    type: Mapped[str] = mapped_column(sa.String(64), nullable=False)
    code: Mapped[str] = mapped_column(sa.String(64), nullable=False)
    ...
    fixture_type: Mapped[str | None] = mapped_column(sa.String(64), nullable=True)
    format_code: Mapped[str | None] = mapped_column(sa.String(64), nullable=True)
    ...
    config: Mapped[dict[str, Any]] = mapped_column(
        JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")
    )

packages/contract/src/omnium_contract/models/taxonomy.py:70-127 (trimmed)

• code is unique per sport. In Postgres two empty (NULL) values never clash, so the partial index uq_competition_code_no_sport makes codes unique among the sportless roots too.
• fixture_type and format_code are inherited downward. A whole tournament is T20; a fixture takes the nearest answer above it unless someone picks otherwise.
• A Games-wide record hangs off the root: competition_entry (who is entered, for which country), medal_standing (the medal table) and event_workflow (which workflow serves each sport).

Reading a whole subtree. Walking a tree with a recursive query is slow at this size. So the tree is also kept flat in competition_closure: one row for every (ancestor, descendant) pair, with the depth. Database triggers keep it right, so no code path can forget.

competition_closure = sa.Table(
    "competition_closure",
    metadata,
    sa.Column("ancestor_id", sa.Uuid, sa.ForeignKey("competition.id", ondelete="CASCADE"), primary_key=True),
    sa.Column("descendant_id", sa.Uuid, sa.ForeignKey("competition.id", ondelete="CASCADE"), primary_key=True),
    sa.Column("depth", sa.Integer, nullable=False),
    sa.Index("ix_competition_closure_descendant", "descendant_id"),
)

packages/contract/src/omnium_contract/models/stats_engine.py:60-78 (lines joined)

The migration that added it measured "every fixture under this season": 2.4 s with a recursive walk, 96 ms with the closure table (packages/core/alembic/versions/20260908_033_competition_closure.py:3-5; how and where it was measured is not written there, not checked).

Outside ids: external_id_map

A provider never sees our ids, and we never store theirs in our main tables. One table links them.

class ExternalIdMap(UUIDPk, Timestamps, Base):
    __tablename__ = "external_id_map"
    __table_args__ = (
        sa.UniqueConstraint("source_code", "entity_type", "external_id"),
        sa.Index("ix_external_id_map_entity_type_internal_id", "entity_type", "internal_id"),
    )

    source_code: Mapped[str] = mapped_column(sa.String(64), nullable=False)
    # sport/competition/fixture/participant/venue/... — which kind of internal entity.
    entity_type: Mapped[str] = mapped_column(sa.String(32), nullable=False)
    external_id: Mapped[str] = mapped_column(sa.String(256), nullable=False)
    # Deliberately NO ForeignKey: this map is polymorphic across entity types,
    # so internal_id is a bare UUID resolved against the table named by
    # entity_type at the application layer.
    internal_id: Mapped[uuid.UUID] = mapped_column(sa.Uuid, nullable=False)
    #: How the link was made: ``onboarding``, ``integration``, ``upload`` or
    #: ``hand``. NULL for links made before this was recorded.
    origin: Mapped[str | None] = mapped_column(sa.String(16), nullable=True)
    created_by: Mapped[str | None] = mapped_column(sa.String(256), nullable=True)

packages/contract/src/omnium_contract/models/ingestion.py:22-40

• The key is (source_code, entity_type, external_id). One outside id points at exactly one of our rows.
• The reverse index (entity_type, internal_id) answers "what ids does this fixture have elsewhere?". One of our rows can have many outside ids, one per source.
• No foreign key. internal_id can point at a fixture, a participant or a competition, so Postgres cannot check it. If a row is deleted, its map rows stay behind. On the laptop database on 7 Oct, 25 map rows point at fixtures that no longer exist (15 fixture, 10 fixture_pairing).

The busiest links on the laptop database (7 Oct):

feed is not a provider. It is the band of plain numbers we hand to clients, because client feeds use integers, not uuids (packages/core/src/omnium_core/catalogue/feed_ids.py:1-50). So the same table holds ids going in and ids going out.

How an import uses it. A scraped row is matched to our record by one of three keys, in order of trust: their id through this table, the three-letter country code, or the name.

async def _by_external_id(
    session: AsyncSession, source_code: str, keys: set[str]
) -> dict[str, uuid.UUID]:
    if not keys:
        return {}
    found = await session.execute(
        sa.select(ExternalIdMap.external_id, ExternalIdMap.internal_id).where(
            ExternalIdMap.source_code == source_code,
            ExternalIdMap.entity_type == _ENTITY_TYPE,
            ExternalIdMap.external_id.in_(sorted(keys)),
        )
    )
    return {str(external): internal for external, internal in found.all()}

packages/core/src/omnium_core/ingest/resolve.py:82-94

• One query for the whole batch, never one per row. A calendar has over a thousand rows; a loop would time out (resolve.py:158-162).
• A row that cannot be matched is kept with a sentence saying why. It is not dropped and it is not guessed. The operator sees the list before turning a feed on (resolve.py:28-34).

Where sport-specific data goes

The house rule, from CLAUDE.md: sport-specific tables and columns are a last resort. When data has no home in the shared columns, it goes into a JSONB field on an existing row. A new sport-specific table or column needs explicit human sign-off.

Each JSONB field has one job. Mixing jobs is what the split prevents.

Why format and meta are two fields, from the model:

    # This fixture's own facts, typed by whoever entered it: the going, the
    # attendance, the match number, the belts a bout is for, who won the toss.
    #
    # Separate from ``format`` because the two share only their timing. Rules come
    # from the config and are identical across every fixture played to it; these
    # belong to one fixture. Mixed in one blob there would be no telling them
    # apart, so a changed config could not be re-applied without risking
    # overwriting something a person typed ...
    meta: Mapped[dict[str, Any]] = mapped_column(
        JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")
    )

packages/contract/src/omnium_contract/models/fixtures.py:86-97

Who checks the shape? Not Postgres. Each sport's allowed fields are declared in a versioned sport definition (sport_definition_version.schema, models/metadata.py:40-67), and a fixture pins the version it was made with (fixture.definition_version_id). The sport's plugin validates commands against it. How a plugin is written is on Writing a workflow in code.

Numbers do not live in JSONB. A match's countable facts (goals, runs, cards) are stat_value lines with a values document per subject, written by the sport's action inside the command. Season totals and tables are built from those lines. See Stats, feeds and delivery.

A worked example

SELECT p.name, p.country, f.name AS fixture, fc.result->>'score' AS mark
FROM competition root
JOIN competition_closure cl ON cl.ancestor_id = root.id
JOIN fixture f             ON f.competition_id = cl.descendant_id
JOIN sport s               ON s.id = f.sport_id AND s.code = 'athletics'
JOIN fixture_competitor fc ON fc.fixture_id = f.id AND fc.parent_id IS NULL
JOIN participant p         ON p.id = fc.participant_id
WHERE root.code = 'asian-games-2026' AND root.sport_id IS NULL
  AND fc.result->>'medal' = 'ME_GOLD';

Low-level design

Keys and ids

Two kinds of primary key are used. Both are made in one shared file.

class UUIDPk:
    """UUIDv7 primary key, generated app-side.

    v7 is time-ordered, giving btree index locality on inserts. Generated
    client-side (``default=uuid7``) so ids are assigned during flush and object
    graphs can be wired up before the INSERT lands.
    """

    id: Mapped[uuid.UUID] = mapped_column(sa.Uuid, primary_key=True, default=uuid7)


class BigIntPk:
    """Auto-incrementing bigint identity primary key."""

    id: Mapped[int] = mapped_column(BigInteger, Identity(always=False), primary_key=True)

packages/contract/src/omnium_contract/models/base.py:23-46 (lines joined)

• UUIDv7 for almost every table. Its first bits are a time, so new rows land at the end of the index. The app makes the id, so related rows can be linked before the insert.
• Bigint identity for the busiest logs (timeline_item, and the stats line tables). Nobody outside refers to these by their own id.
• A human code too: sport.code, competition.code, fixture.code, participant.code. Unique per sport. Used in URLs and logs.
• Enums are strings. Every enum is a VARCHAR with no native Postgres enum (base.py:64-76). New values need no migration.

The core tables, in SQL

Real columns from the laptop database at migration 059 (pg_dump --schema-only), trimmed to the columns that matter and re-ordered for reading. created_at and updated_at are on most tables and left out.

CREATE TABLE sport (
    id              uuid PRIMARY KEY,
    code            varchar(64)  NOT NULL UNIQUE,      -- 'football'
    name            varchar(256) NOT NULL,
    parent_id       uuid REFERENCES sport(id),        -- Aquatics > swimming
    archetype       varchar(32),                      -- default for new fixtures only
    competitor_kind varchar(32),                      -- PERSON / TEAM / PAIR / COMBO / CREW
    status          varchar(16)  NOT NULL DEFAULT 'ACTIVE'
);

CREATE TABLE competition (
    id           uuid PRIMARY KEY,
    sport_id     uuid REFERENCES sport(id),           -- NULL for a Games root
    parent_id    uuid REFERENCES competition(id),     -- the tree
    type         varchar(64)  NOT NULL,               -- games / league / season / event ...
    code         varchar(64)  NOT NULL,
    name         varchar(256) NOT NULL,
    fixture_type varchar(64),                         -- inherited downward
    format_code  varchar(64),                         -- inherited downward
    status       varchar(32),
    ordinal      integer,
    start_date   date,
    end_date     date,
    config       jsonb NOT NULL DEFAULT '{}',
    UNIQUE (sport_id, code)
);
CREATE UNIQUE INDEX uq_competition_code_no_sport ON competition (code) WHERE sport_id IS NULL;

CREATE TABLE fixture (
    id                    uuid PRIMARY KEY,
    sport_id              uuid NOT NULL REFERENCES sport(id),
    competition_id        uuid REFERENCES competition(id),
    venue_id              uuid REFERENCES venue(id),
    definition_version_id uuid REFERENCES sport_definition_version(id),
    code                  varchar(64)  NOT NULL,
    name                  varchar(256),
    archetype             varchar(32)  NOT NULL,      -- HEAD_TO_HEAD / RANKED_FIELD / ...
    fixture_type          varchar(64),
    format_code           varchar(64),
    status                varchar(32)  NOT NULL DEFAULT 'SCHEDULED',
    results_official      boolean      NOT NULL DEFAULT false,
    coverage_tier         varchar(32)  NOT NULL,      -- LIVE / DELAYED / EOD
    scheduled_start       timestamptz,
    format                jsonb NOT NULL DEFAULT '{}',
    meta                  jsonb NOT NULL DEFAULT '{}',
    live_state            jsonb,
    result                jsonb,
    pinned                jsonb NOT NULL DEFAULT '[]', -- fields a person set by hand
    last_seq              bigint  NOT NULL DEFAULT 0,  -- the log's position counter
    version               integer NOT NULL DEFAULT 0,  -- optimistic-lock counter
    scoring_program_version_id uuid,                   -- rules pinned at first command
    scoring_ui_id         uuid,
    UNIQUE (sport_id, code)
);

CREATE TABLE fixture_competitor (
    id                uuid PRIMARY KEY,
    fixture_id        uuid NOT NULL REFERENCES fixture(id),
    participant_id    uuid REFERENCES participant(id),
    placeholder_label varchar(128),                    -- 'Winner of QF1'
    parent_id         uuid REFERENCES fixture_competitor(id), -- player under team
    ordinal           integer,                         -- lane, leg, batting order
    slot              varchar(64),                     -- 'anchor', 'skip'
    alignment         varchar(16),                     -- Home / Away / RED / BLUE
    result_status     varchar(32) NOT NULL DEFAULT 'ENTERED',
    status_code       varchar(64),                     -- the sport's own word
    rank              integer,                         -- not unique: dead heats
    win_method        varchar(64),                     -- KO / DLS / PENALTIES
    entry             jsonb NOT NULL DEFAULT '{}',
    result            jsonb NOT NULL DEFAULT '{}',
    pinned            jsonb NOT NULL DEFAULT '[]',
    CHECK ((participant_id IS NULL) <> (placeholder_label IS NULL))
);

CREATE TABLE participant (
    id             uuid PRIMARY KEY,
    sport_id       uuid REFERENCES sport(id),          -- where it was created; NULL = Commons
    code           varchar(64)  NOT NULL,
    type           varchar(32)  NOT NULL,              -- PERSON / PAIR / TEAM / HORSE / CREW
    name           varchar(256) NOT NULL,
    country        char(3) CHECK (country ~ '^[A-Z]{3}$'),
    meta           jsonb NOT NULL DEFAULT '{}',
    identity_hash  text UNIQUE,                        -- duplicate detection
    merged_into_id uuid REFERENCES participant(id),
    UNIQUE (sport_id, code)
);

CREATE TABLE participant_sport (
    participant_id uuid REFERENCES participant(id) ON DELETE CASCADE,
    sport_id       uuid REFERENCES sport(id)       ON DELETE CASCADE,
    PRIMARY KEY (participant_id, sport_id)             -- no rows = seen by every sport
);

CREATE TABLE external_id_map (
    id          uuid PRIMARY KEY,
    source_code varchar(64)  NOT NULL,
    entity_type varchar(32)  NOT NULL,
    external_id varchar(256) NOT NULL,
    internal_id uuid NOT NULL,                         -- no FK: points at any table
    origin      varchar(16),
    created_by  varchar(256),
    UNIQUE (source_code, entity_type, external_id)
);

fixture: status, locks and the log counter

Three columns on fixture do quiet but important work.

    status: Mapped[FixtureStatus] = mapped_column(
        str_enum(FixtureStatus, "fixture_status"),
        nullable=False,
        server_default=sa.text("'SCHEDULED'"),
    )
    # Ratification sub-state (the old RESULT vs OFFICIAL); meaningful when COMPLETED.
    results_official: Mapped[bool] = mapped_column(...)
    ...
    # Internal optimistic-lock counter. NEVER a conflict resolver (per design).
    version: Mapped[int] = mapped_column(sa.Integer, nullable=False, server_default=sa.text("0"))
    ...
    # The ledger's high-water mark for this fixture. Taken with
    # `UPDATE fixture SET last_seq = last_seq + 1 ... RETURNING last_seq`, which
    # is one row lock and O(1) whether the fixture holds five events or five
    # million — no MAX(), no scan, no race. It also serializes commands per
    # fixture for free, which the async queue needs anyway.
    last_seq: Mapped[int] = mapped_column(sa.BigInteger, nullable=False, server_default=sa.text("0"))
    # Field names a person set by hand — "name", "start", "venue", "status",
    # "result", "competitors". A schedule feed writes every field except these,
    # so a correction made on the console outlives the next scrape.
    pinned: Mapped[list[str]] = mapped_column(JSONB, nullable=False, server_default=sa.text("'[]'::jsonb"))

packages/contract/src/omnium_contract/models/fixtures.py:68-132 (trimmed)

• status is one of SCHEDULED, LIVE, SUSPENDED, COMPLETED, POSTPONED, CANCELLED, ABANDONED, VOID (enums.py:50-66). "Official" is not a status. It is results_official = true on a completed fixture.
• last_seq is the position counter of the fixture's log. Taking the next number locks the fixture row. That one lock is what keeps two scorers from writing out of order. The real code is next_seq in packages/core/src/omnium_core/scoring/ledger.py:184-202.
• pinned lists fields a person changed by hand. An import skips them. The same idea is on fixture_competitor, competition_entry and medal_standing. See Truth and ownership.

fixture_competitor: sides, players and placeholders

class FixtureCompetitor(UUIDPk, Timestamps, Base):
    """An entrant in a fixture (the competing parties).

    ``parent_id`` (self) nests a member under their side's entry — a footballer
    under their team, a swimmer under their club's relay quartet; NULL for a
    top-level entrant. ``result_status``/``rank`` are meaningful on top-level
    rows; win/loss derives from ``rank``.

    There is deliberately **no** ``unique(fixture_id, participant_id)``: a club
    enters an A and a B relay team, and both entries name the same club. ...
    """

    __table_args__ = (
        sa.Index("ix_fixture_competitor_fixture_id", "fixture_id"),
        sa.Index("ix_fixture_competitor_ordinal", "fixture_id", "parent_id", "ordinal"),
        sa.CheckConstraint(
            "(participant_id IS NULL) != (placeholder_label IS NULL)",
            name="participant_xor_placeholder",
        ),
    )

packages/contract/src/omnium_contract/models/fixtures.py:147-175 (trimmed)

• Win and loss are never stored. They come from rank. Rank 1 is the winner. rank is not unique, so a dead heat is two rows on the same rank.
• result_status is one of six canonical values: ENTERED, ACTIVE, FINISHED, DNS, DNF, DSQ (enums.py:91-112). The sport's own word ("pulled up") goes in status_code, checked against outcome_code.
• Exactly one of participant or placeholder is set. The database enforces it with the CHECK above.
• A player stays in the same fixture as their team. A trigger refuses a nested row whose parent is in another fixture (packages/core/alembic/versions/20260718_004_integrity_guards.py:70-95).

Extra tables hang off one entry when a sport needs more than one number:

participant: one record per party

class Participant(UUIDPk, Timestamps, Base):
    """A party: person, team, horse, pair, crew — competitor, official, or support.

    **Which sports see this record is decided by ``sports``, not by ``sport_id``.**
    ...
    * a record listing **none** is seen everywhere — India as a country, the
      Wankhede as a ground. This is what makes an Olympic medal table possible,
      because it needs one India rather than one per sport ...
    """
    ...
    # App-computed normalized fingerprint for duplicate detection; unique when set.
    identity_hash: Mapped[str | None] = mapped_column(sa.Text, unique=True, nullable=True)
    # Set when this row was merged into a surviving duplicate — reads follow the
    # chain; the merged row is kept so old external ids keep resolving.
    merged_into_id: Mapped[uuid.UUID | None] = mapped_column(
        sa.ForeignKey("participant.id"), nullable=True
    )

packages/contract/src/omnium_contract/models/participants.py:21-75 (trimmed)

• Role is not a type. The same table holds athletes, teams, officials and coaches. What a party is in one fixture comes from how it is linked: as an entry, a connection or an official.
• Visibility is a list. participant_sport lists the sports that see a record. No rows means every sport. On the laptop database, 97 records have no sport and no links: the Commons countries.
• Teams change over time. participant_membership links a team to a member with valid_from and valid_to. A player who re-signs gets a new row, so history is kept.
• The flag is on the entry. The country an athlete represents at one Games is competition_entry.country_id, not participant.country, because athletes change flags between Games (models/entries.py:7-13).
• Duplicates are merged, not deleted. The losing row points at the winner with merged_into_id, so its outside ids keep working.

The log: ledger_stream and timeline_item

Every change to a fixture or an event is logged in order. The details of how a command writes the log are on Commands and the workflow engine and The live scoring engine. The tables:

class LedgerStream(UUIDPk, Base):
    """One ordered, append-only log per subject."""

    __tablename__ = "ledger_stream"
    __table_args__ = (sa.UniqueConstraint("subject_kind", "subject_id"),)

    #: ``fixture`` | ``event``.
    subject_kind: Mapped[str] = mapped_column(sa.String(16), nullable=False)
    subject_id: Mapped[uuid.UUID] = mapped_column(sa.Uuid, nullable=False)
    #: The position counter for subjects without one of their own. A fixture
    #: keeps counting in ``fixture.last_seq`` ...; an event counts here.
    last_seq: Mapped[int] = mapped_column(sa.BigInteger, nullable=False, server_default=sa.text("0"))

packages/contract/src/omnium_contract/models/workflows.py:107-124 (trimmed)

class TimelineItem(BigIntPk, Timestamps, Base):
    """An item on a fixture's timeline.

    APPEND-ONLY. Items are never mutated in place; a correction is a NEW item
    (status CORRECTED) whose supersedes_id points at the item it revises, and a
    deletion flips an item to VOIDED — preserving full history and seq continuity.
    """
    __table_args__ = (
        sa.UniqueConstraint("stream_id", "source_code", "unit", "seq",
                            name="uq_timeline_item_stream_seq",
                            postgresql_nulls_not_distinct=True),
        ...
        sa.Index("uq_timeline_item_idempotency", "stream_id", "source_code", "idempotency_key",
                 unique=True, postgresql_nulls_not_distinct=True,
                 postgresql_where=sa.text("idempotency_key IS NOT NULL")),
    )

    stream_id: Mapped[uuid.UUID] = mapped_column(sa.ForeignKey("ledger_stream.id"), nullable=False)
    fixture_id: Mapped[uuid.UUID | None] = mapped_column(sa.ForeignKey("fixture.id"), nullable=True)
    actor: Mapped[str | None] = ...       # an account name, or integration:<code>
    source_code: Mapped[str | None] = ... # which feed wrote it
    event_type: Mapped[str | None] = ...  # 'football.goal', 'games.set_medals'
    unit: Mapped[str] = ...               # the grain
    seq: Mapped[int] = ...                # position in the stream
    payload: Mapped[dict[str, Any]] = ... # the command's input
    supersedes_id: Mapped[int | None] = ...
    idempotency_key: Mapped[str | None] = ...

packages/contract/src/omnium_contract/models/timeline.py:16-102 (trimmed, lines joined)

• One stream per subject. A fixture has one; so does an event (the medal table, entries and schedule are changed by commands too). A trigger fills stream_id from fixture_id for older writers (timeline_item_fill_stream, migration 048).
• No duplicates on retry. A resent command with the same idempotency_key hits the unique index and is recognised as the first one.
• Corrections append. A fix is a new row that points at the old one with supersedes_id.
• Two grains are in use. The scoring path writes unit = 'event' (scoring/ledger.py:36). The workflow engine writes unit = 'record' (workflows/engine.py:56). On the laptop database, the most common event_type values are games.import_units (248) and games.hand_back_medals (176), all record.
• timeline_actor links a log row to the people in it (scorer, carded player) by id, so "every goal by X" needs no JSON search.

Live headline and match numbers

• scoreboard: one row per fixture, one JSONB snapshot. It is a cache. Its only writer is the sport's state action through the engine (fixtures.py:476-499).
• stat_value: one line per (fixture, subject, role, segment), with a values document. Partitioned by year on occurred_on; the laptop database has 2015 to 2028 plus a default. It copies sport_id, competition_id and format_code from the fixture, so a season read never joins (stats_engine.py:83-130).

    sa.UniqueConstraint(
        "fixture_id", "subject_id", "subject_role", "segment", "occurred_on",
        name="uq_stat_value_line",
    ),
    ...
    postgresql_partition_by="RANGE (occurred_on)",

packages/contract/src/omnium_contract/models/stats_engine.py:115-130 (trimmed)

Football's tallies action is an example of who writes these lines: one per side per half (segment = 'half-1'), one per side for the whole match (goals_for, goals_against), and one per player (packages/flows/src/omnium_flows/football/actions.py:120-166).

Guards inside the database

Some rules are too important to leave to code. These triggers exist on the laptop database (checked in pg_trigger, 7 Oct):

The closure trigger, as it runs on insert:

IF TG_OP = 'INSERT' THEN
  INSERT INTO competition_closure (ancestor_id, descendant_id, depth)
  SELECT p.ancestor_id, NEW.id, p.depth + 1
    FROM competition_closure p
   WHERE p.descendant_id = NEW.parent_id
  UNION ALL
  SELECT NEW.id, NEW.id, 0;
  RETURN NEW;

packages/core/alembic/versions/20260908_033_competition_closure.py:25-35

In words: the new node gets every ancestor its parent has, one level deeper, plus a row for itself at depth 0. A move (UPDATE OF parent_id) cuts the subtree from its old ancestors and hangs it under the new ones.

On main since 30 Sep (PR #155)

Commit 23cf8d5 on main adds migration 060, 20260925_060_unified_platform_evolution.py. It is not on the branch checked out for this page, and not in the laptop database (which stops at 059). Read with git show, not run.

The migration says it plainly: "Nothing here is read by any running code yet, so nothing changes behaviorally until a later migration wires it up." A git grep on main finds these names only in the models (models/stat_unified.py, models/__init__.py) and the migration.

The new column, as added to fixture on main:

    # Reference-safe merge (mirrors participant.merged_into_id): point the loser
    # at the winner instead of deleting, so existing references keep resolving.
    merged_into_id: Mapped[uuid.UUID | None] = mapped_column(
        sa.ForeignKey("fixture.id"),
        nullable=True,
        comment=(
            "Reference-safe merge (mirrors participant.merged_into_id): never "
            "delete a row, point the loser at the winner instead."
        ),
    )

origin/main:packages/contract/src/omnium_contract/models/fixtures.py:58-67

What this means for the model:

• Merging, not deleting, for every core row. Two copies of one fixture or one competition can be joined the way two copies of a person already are. Old references and old outside ids keep working, so a merge would no longer leave dead external_id_map rows. No code does the merge yet.
• A second id map exists. xref.entity_map keys on (entity, sport_code, source_table, source_id) and points at a unified_id. external_id_map keys on (source_code, entity_type, external_id). Two tables now answer "their id is our id". Which one wins is not decided in the code.
• A second stat store exists. stat sits beside the stats engine's stat_value and is not written by anything. The migration calls retiring the old engine "a deferred follow-up migration".

Every table, by group

79 tables on the laptop database at migration 059, not counting 65 partitions, 4 procrastinate_* job-queue tables and alembic_version. The models are in packages/contract/src/omnium_contract/models/.

This list is the branch at migration 059. main adds stat_code and stat in public, and 14 tables in the new xref, enrich and recon schemas (see above).

docs/erd.md in the repo is out of date. It was generated on 7 Aug 2026, lists 45 tables, and still shows classification, scoring_job and other tables dropped since. Use this table, or run make erd.

When things go wrong

Decisions

There is no design section for this page. These are the choices written into the code, with the file that explains each one. All are built.

Built today, or still to build

The exact names of the agreed new tables are on the linked pages. They are not repeated here, so there is one place to change them.

Numbers

The laptop database is a working copy, not production. Production counts were not checked for this page.

Read next

• Truth and ownership: which of these rows is the truth, who may change it, and how pinned works.
• Commands and the workflow engine: how a change reaches these tables.
• The live scoring engine: how timeline_item, scoreboard and stat_value are written during a match.
• Database and the read-only copy: growth, partitions and who reads where.