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:58If 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_idThat 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
| Volume | Where | Why |
|---|---|---|
| Up to a few hundred thousand rows | SQLite | A single file, no server, and it already has window functions (LAG) and indexes |
| Millions of rows, several readers | Postgres | Concurrency, timestamptz, JSONB and real upserts |
| Analysis only, once | CSV or Parquet | Opens 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
idis 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
- What an API is and how to connect to one for the first time
APIs explained without the jargon: what they are, how REST, JSON and keys work, and how to make your first call in the browser, the terminal, Python and JavaScript.
- How we read a live table: from video to JSON
The path a result takes from the table to the API: video segments, a neural network, the check against the game's rules, and why we discard rather than guess.
- Scrape the site or use an API? The cost of a scraper
Web scraping looks free, but it breaks on layout changes, logins, blocks and lost data. Here is when a scraper makes sense and when an API comes out cheaper.