How to store and version odds data

Odds history is something you build yourself: append every price you read as a new row, stamped with the board's own time and keyed on fixture ids you own. The OddsRelay feed serves the current pre-match board and has no history endpoint, so the archive starts the day you start writing it.

Append, never update

A feed tells you the price now. An archive tells you what the price was then. You get from one to the other by inserting a row for each observation and never touching it again. The current price is the newest row. Every older row is the record.

The tempting shortcut is one row per selection, updated on each read. It is smaller and it destroys the past. A price that drifted from 2.9 to 2.6 over an afternoon leaves only 2.6 behind, and no query can recover the journey.

Stamp rows with the feed's time

Your own clock records when you fetched a board. The feed tells you when the board was built, and that is the better timestamp. Every board carries it as meta.processed_at and again in the X-Processed-At header. Store it on every row you write from that board.

Matched offers carry no time of their own. The envelope's meta.last_seen gives, per venue and sport, the last time that venue was read, so now minus last_seen is an upper bound on a price's age. Keep it beside the snapshot if you want to answer later how old a stored price could have been.

Raw works differently. Each bookmaker's market carries a last_update, which is when that market last changed. It is not a heartbeat, so a quiet market's last_update ages while the venue is still being read. A restart can also move it once with nothing changed, which is why you dedupe on content rather than on the timestamp. The freshness guide has the exact rules.

Key the archive on ids you own

The feed's event_id (and raw's id) is opaque. It identifies a fixture on today's board, and it is not a promise that the same string will name the same fixture months from now. A displayed team name or kick-off time can also be corrected. An archive keyed directly on either will split one fixture's history in two the first time that happens.

So give each fixture your own primary key, and keep a small alias table that maps every feed id you have seen to it. When a new id arrives for a fixture you already hold, add an alias row pointing at the existing fixture. Your snapshots reference your key, and the feed's ids become lookup handles. Matching the new id to the old fixture is the same sport, kick-off and team-name work covered in normalising odds across bookmakers.

-- example: the customer's schema, not the feed's
create table fixture (
  fixture_id    bigserial primary key,
  sport_key     text not null,          -- e.g. soccer_epl
  home_team     text,
  away_team     text,
  commence_time timestamptz not null
);

create table feed_alias (
  feed_event_id text primary key,       -- event_id on a matched board, id on raw
  fixture_id    bigint not null references fixture
);

create table price_snapshot (
  fixture_id  bigint not null references fixture,
  market_key  text not null,            -- e.g. h2h
  outcome     text not null,            -- e.g. Arsenal
  venue       text not null,            -- bookmaker or exchange key, e.g. william_hill
  side        text not null,            -- 'back' or 'lay'
  price       numeric,                  -- null records that the offer left the board
  available   numeric,                  -- lay offers only
  board_time  timestamptz not null,     -- meta.processed_at
  primary key (fixture_id, market_key, outcome, venue, side, board_time)
);

Write only what changed

Most reads of a board change little, and an unchanged price adds nothing to an archive. Change-only writes keep every move and drop the repeats, at two levels.

At the level of the whole board, the feed does the work. Every data reply carries an ETag. Send it back as If-None-Match and an unchanged board is a 304 with an empty body, which costs no tokens and gives you nothing to write. The conditional requests guide covers it. On raw, walk again with updatedSince set to the start of your previous walk and you get back only the venues whose last_update is at or after it. The walking raw guide explains the cursor and the duplicates to expect.

At the level of a single offer, the work is yours. Compare against the last row you wrote for the same key and skip it if the price and available match. When an offer you hold is missing from a new board, write a row with a null price. Without it, the archive shows a price that looks live long after the offer went.

// example: the customer's poller. fixtureFor() and insertSnapshot() are yours.
// lastWritten holds this one board query's keys: share it with another board or filter and live offers look gone.
const res = await fetch(url, { headers: { "x-api-key": key, "If-None-Match": etag } });
if (res.status === 304) return; // unchanged board: nothing to write
etag = res.headers.get("ETag");
const { meta, data } = await res.json();
const seen = new Set();

for (const event of data) {
  const fixtureId = await fixtureFor(event.event_id, event);
  for (const market of event.markets) {
    for (const outcome of market.outcomes) {
      const offers = [
        ...outcome.back.map((o) => ({ venue: o.bookmaker, side: "back", price: o.price, available: null })),
        ...outcome.lay.map((o) => ({ venue: o.exchange, side: "lay", price: o.price, available: o.available })),
      ];
      for (const o of offers) {
        const k = [fixtureId, market.key, outcome.name, o.venue, o.side].join("|");
        seen.add(k);
        const prev = lastWritten.get(k);
        if (prev && prev.price === o.price && prev.available === o.available) continue;
        const row = { fixtureId, market: market.key, outcome: outcome.name, ...o };
        lastWritten.set(k, row);
        await insertSnapshot({ ...row, boardTime: meta.processed_at });
      }
    }
  }
}
// an offer you hold that this board no longer carries gets a null-price row
for (const [k, prev] of lastWritten) {
  if (seen.has(k) || prev.price === null) continue;
  const gone = { ...prev, price: null, available: null };
  lastWritten.set(k, gone);
  await insertSnapshot({ ...gone, boardTime: meta.processed_at });
}

Store the prices and derive the rest

A matched board is paired against exchange lay and liquidity-gated. The rating and qualifying loss are simple arithmetic on the pair, and the oddsmatcher widget shows them. Keep them out of the archive. Store the back price, the lay price and available, and compute the rest when you read the history back.

This is the one design choice worth being stubborn about. A derived number carries its assumptions, the commission rate above all. Store the inputs and a change of commission is a new query. Store the outputs and it is a rewrite of every row you hold.

Granularity and retention follow the question

Granularity is how often you write a row. It can be no finer than how often you read the board, and change-only writes make it as fine as your reads at a fraction of the size. Retention is how long you keep the rows. Decide both before the first insert, because detail you never stored cannot be backfilled.

A common pattern is tiered retention. Keep every change for recent fixtures, then thin older ones to a fixed interval, or to the first and last price per offer. Old fixtures then cost a fixed number of rows each, however often you polled them.

Point the schema at a real board

The quickest test of an archive design is a day of real boards written into it. Request access for a key, then read the docs for the full response shapes your rows will come from.

Related posts