How to store and version odds data

OddsRelay also sells historical odds, the bookmakers' and exchanges' prices on its raw feed since 17 October 2026. For the matched boards' pairs, or a record kept your own way, 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. A quiet market therefore keeps an old last_update 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, one per fixture, and never changes once served: every later board names that fixture by the same string, whatever its exchanges, spellings or kick-off do next. A displayed team name or kick-off time is different, because either can be corrected. An archive keyed on names and times will split one fixture's history in two the first time that happens.

Two ids can still turn out to be one fixture. The feed then keeps the id it served first and lists the other in that event's previous_ids on /v2/events, raw and the event route, and the other id still answers on /v2/odds/event/{id}. 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 an event's previous_ids names an id you hold, point that alias at the same fixture. Your snapshots reference your key, and the same table takes a second provider's ids, matched on sport, kick-off and team names as 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. horse_racing
  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 sends prices only: back offers, lay offers and the available money at each lay price. A rating or a qualifying loss is arithmetic you run on a pair, as the oddsmatcher widget does, so keep both 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, except the bookmakers' and exchanges' prices since 17 October 2026, which historical odds hold.

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.