Blog · General

Storing real-time data: from JSON to database, no gaps

How to store what arrives from a live API: a unique key, upsert, time zones, gap detection, and the backfill that closes the window while your program was down.

Published on October 30, 2026 · 6 min read

Receiving the data is the easy part. The work is storing it in a way that, six months later, lets you look at the history and trust it: no repeated round, no silent gap, and none of that "which time zone is this?" doubt. This article is the path from the JSON arriving out of the API to a table you query without fear.

It applies to any continuous data API. The examples use rounds from our live casino APIs, where each record has a fixed id and a settlement time — the same format you will find in the documentation.

The decision that solves half the problems: a unique key

Everything that arrives has an identifier from whoever produced it. Use that as the primary key, not an AUTOINCREMENT of your own.

CREATE TABLE rounds (
  id          TEXT PRIMARY KEY,   -- the id from the API, fixed and stable
  game        TEXT NOT NULL,
  settled_at  TEXT NOT NULL,      -- ISO 8601 in UTC, exactly as it arrives
  outcome     TEXT NOT NULL,
  data        TEXT NOT NULL       -- the original data object, as JSON
);
CREATE INDEX rounds_by_time ON rounds (game, settled_at);

With that, writing the same round twice stops being a bug and becomes a non-event:

import json
import sqlite3

db = sqlite3.connect("rounds.db")

def store(round_):
    db.execute(
        "INSERT INTO rounds (id, game, settled_at, outcome, data) VALUES (?, ?, ?, ?, ?)"
        " ON CONFLICT(id) DO NOTHING",
        (round_["id"], round_["game"], round_["settledAt"], round_["outcome"], json.dumps(round_["data"])),
    )
    db.commit()

ON CONFLICT DO NOTHING (or INSERT ... ON CONFLICT (id) DO UPDATE in Postgres, if you want to overwrite) makes your writer idempotent: it can run twice, it can reprocess a file, it can restart mid-way — the result is the same. That is what lets you sleep through reconnects, retries and the stream's event: ready, which repeats the last round on purpose.

Store the original object alongside

Besides the columns you query, store the JSON as it came. It costs little and solves next month's problem, when you want a field that never became a column — each side's cards, the multiplier, the pair. Reprocessing from the original is trivial; inventing data you threw away is impossible.

UTC in the database, time zone only on screen

The API returns dates in UTC, with the Z at the end: "2026-10-30T18:10:58.903Z". Store exactly that and convert only when displaying.

Why: local time has daylight saving, has users in other zones and has one ambiguous hour a year. UTC has none of that, and sorting ISO 8601 text in UTC gives you chronological order for free.

from datetime import datetime
from zoneinfo import ZoneInfo

def local(iso, zone="America/Sao_Paulo"):
    return datetime.fromisoformat(iso.replace("Z", "+00:00")).astimezone(ZoneInfo(zone))

print(local("2026-10-30T18:10:58.903Z").strftime("%d/%m %H:%M:%S"))   # 30/10 15:10:58

If your database is Postgres, use timestamptz; in SQLite, ISO text works well.

The backfill: closing the window while you were away

Every live collection has a gap at some point: a deploy, a dropped connection, a restart. The way to close it is to ask for what you missed, and that is what the after parameter is for: it takes the id of the last round you have and returns only what came after it, in chronological order.

def sync(last_id, key):
    """Asks for everything that settled after last_id, paginating until you are up to date."""
    while True:
        body = request("/bacbo-live/results", key, {"after": last_id, "limit": 250})
        for round_ in body:
            store(round_)
            last_id = round_["id"]
        if len(body) < 250:
            return last_id

That request is the client with error handling and backoff from the article on API errors and limits. Call sync every time the program starts, before you begin listening live: it is a cheap call when there is nothing to fetch, and it is what keeps the history gap-free when there is.

Anyone using /stream has a shortcut: on reconnecting with last-event-id, the rounds from your absence come back in the ready event itself, up to 50. after is for absences longer than that — the details are in real-time results.

How to find a gap you did not know you had

This is the most useful query of all, and almost nobody writes it: where is the interval between consecutive records too large? If the table runs a round every ~35 seconds, a 3-minute interval is a gap, not a pause.

WITH seq AS (
  SELECT settled_at,
         LAG(settled_at) OVER (PARTITION BY game ORDER BY settled_at) AS previous
  FROM rounds WHERE game = 'bacbo-live'
)
SELECT previous, settled_at,
       (julianday(settled_at) - julianday(previous)) * 86400 AS seconds
FROM seq
WHERE seconds > 90
ORDER BY seconds DESC;

Run it once a week. It was exactly this kind of query that showed, in a round-by-round comparison, that a well-known source was leaving about 20% of the rounds out — the gaps showed up in its list, not in ours.

Where to store it, by size

VolumeWhereWhy
Up to a few hundred thousand rowsSQLiteA single file, no server, and it already has window functions (LAG) and indexes
Millions of rows, several readersPostgresConcurrency, timestamptz, JSONB and real upserts
Analysis only, onceCSV or ParquetOpens in Excel, Sheets or pandas with nothing to install

A live casino table runs about 2,500 rounds a day, or close to 1 million a year. That is little for any database and a lot for a spreadsheet — which is why the usual path is a database to store and CSV to analyse.

A common mistake: two writers on the same table

If your Telegram bot and your site both write to the same table, at some point both will try to write the same round at the same instant. With a unique key that is harmless — one writes, the other gets a conflict and moves on. Without a unique key, you wake up to duplicate rows and wrong statistics.

If you can, keep one process writing and the others reading. It is easier to understand and to debug than any locking scheme.

A checklist before trusting your history

  • The source's id is the primary key, and writing is idempotent.
  • Dates are in UTC in the database, and the time zone appears only on screen.
  • The program calls the backfill on startup.
  • A gap query exists, and somebody looks at its output.
  • The original JSON is stored, not just today's columns.
  • The volume fits the chosen database, with an index on the time field.

Frequently asked questions

What should the primary key of an API data table be?

The identifier that comes from the API itself, not an autoincrement of your own. It is fixed and stable, so a repeated write becomes a silent conflict instead of a duplicate row, and that makes your writer idempotent.

Should I store dates in UTC or local time?

In UTC, exactly as the API returns them, converting only when displaying. Local time brings daylight saving, users in other zones and one ambiguous hour a year; sorting ISO 8601 text in UTC already gives chronological order.

How do I recover data from the period my program was down?

With the after parameter on the history endpoint: you pass the id of the last row you have and receive only what came after it, paginating until you are up to date. Call it every time the program starts. Anyone using /stream receives up to 50 rounds from the absence in the ready event when reconnecting with last-event-id.

How do I know whether data is missing from my history?

By comparing the interval between consecutive records with the source's normal pace, using a window query (LAG). Intervals well above the expected are gaps, and that is the check that reveals incomplete collection before it ruins your statistics.

Read also