A dependency-free snapshot dashboard: http.server + sqlite3, and the ALTER TABLE trap

Background

I run more than twenty small sites as a solo developer. Publishing is automated; looking at the results was not. Google Analytics and Search Console are both fine for one site, but opening two consoles per site, aligning the date ranges, and remembering yesterday's number does not scale to twenty. I stopped doing it after three days.

So I put a small server on a Mac mini at home and started pushing every site's numbers to it a few times a day. It uses only the Python standard library, http.server and sqlite3. There is no pip install step, no framework, and nothing to keep updated.

This post is about the storage design, and specifically about one decision that paid off and one bug it caused.

How it works

Three pieces, nothing else.

1. A collector (Node) gathers Analytics and Search Console numbers for every site into one JSON object.
2. It sends that object to POST /api/ingest, authenticated with a single shared token in an X-Token header.
3. The Python server appends it to SQLite and serves the dashboard HTML from GET /.

The whole server boot is this:

from http.server import ThreadingHTTPServer, BaseHTTPRequestHandler
import sqlite3, json

if __name__ == "__main__":
    init_db()
    ThreadingHTTPServer(("0.0.0.0", PORT), H).serve_forever()

ThreadingHTTPServer rather than plain HTTPServer because the plain one is sequential: the browser's favicon request blocks the HTML response. Even with a single viewer, opening two tabs makes it feel broken.

The ingest handler returns the id of the row it just created:

def do_POST(self):
    if self.path != "/api/ingest":
        return self._send(404, "not found", "text/plain")
    tk = token()
    if not tk or self.headers.get("X-Token") != tk:
        return self._send(401, json.dumps({"error": "unauthorized"}), "application/json")
    n = int(self.headers.get("Content-Length", "0"))
    payload = json.loads(self.rfile.read(n) or b"{}")
    return self._send(200, json.dumps({"ok": True, "snapshot_id": ingest(payload)}), "application/json")

Returning snapshot_id is the design in one line. Each push creates a new row rather than updating an existing one.

Implementation

One snapshot = one parent row plus N child rows

The schema below is what init_db() runs on every boot. snapshots holds one row per push, and site_metrics holds one row per site within that push, linked by snapshot_id:

CREATE TABLE IF NOT EXISTS snapshots(
    id INTEGER PRIMARY KEY AUTOINCREMENT, ts TEXT, ts_jst TEXT, gcp_cost TEXT, meta TEXT);

CREATE TABLE IF NOT EXISTS site_metrics(
    snapshot_id INTEGER, site_key TEXT, label TEXT, url TEXT,
    ga_sessions_y REAL, ga_users_y REAL, ga_pv_y REAL,
    sc_clicks REAL, sc_impressions REAL, sc_ctr REAL, sc_position REAL, added INTEGER);

CREATE INDEX IF NOT EXISTS idx_sm_snap ON site_metrics(snapshot_id);

So a push of 23 sites writes 1 row into snapshots and 23 into site_metrics, all carrying the same snapshot_id. site_metrics deliberately has no primary key. A row is not "the current state of a site", it is "how that site looked at that push". Dozens of rows per site_key is the correct state. Nothing is ever updated or deleted; reads always filter by the latest snapshot_id, which is why the index is on that column.

Adding a metric is one ALTER TABLE

Metrics always grow. This table started with a handful of columns and now has 29, after splitting organic search out of total sessions, then adding a per-channel breakdown, then a traffic-quality figure.

Append-only storage makes that cheap, because nothing has to be backfilled:

def ensure_columns(c, table, cols):
    """Idempotent migration: ALTER TABLE ADD COLUMN only for columns that are missing.
    Existing rows are never rewritten; the new column is NULL for them, which is correct."""
    have = {r["name"] for r in c.execute(f"PRAGMA table_info({table})").fetchall()}
    for name, decl in cols:
        if name not in have:
            c.execute(f"ALTER TABLE {table} ADD COLUMN {name} {decl}")

PRAGMA table_info(<table>) is SQLite's introspection query: it returns one row per column. Building the set of existing names and adding only what is missing makes the function idempotent, meaning it can run on every boot and produce the same result no matter how many times it has run before.

The important part is that NULL on old rows is semantically right. A July snapshot has no organic-search column because that metric did not exist yet, not because the value was zero.

That distinction is enforced end to end: a failed fetch stores NULL, a measured zero stores 0. Collapse both into 0 and a flat line in the chart becomes unreadable forever after, because nothing tells you whether traffic died or collection did.

And that is exactly what breaks positional INSERT

Cheap column additions mean frequent column additions. And INSERT INTO t VALUES(?,?,?) names no columns, so it breaks the moment the column count changes.

Here is a reproduction I actually ran:

import sqlite3
c = sqlite3.connect(":memory:")
c.execute("CREATE TABLE m(snapshot_id INTEGER, site_key TEXT, clicks REAL)")
c.execute("INSERT INTO m VALUES(?,?,?)", (1, "coffee", 3))
print("before ALTER:", c.execute("SELECT * FROM m").fetchall())

c.execute("ALTER TABLE m ADD COLUMN impressions REAL")
print("after ALTER :", c.execute("SELECT * FROM m").fetchall())

try:
    c.execute("INSERT INTO m VALUES(?,?,?)", (2, "beer", 5))
except Exception as e:
    print("positional INSERT ->", type(e).__name__, e)

c.execute("INSERT INTO m(snapshot_id,site_key,clicks) VALUES(?,?,?)", (2, "beer", 5))
print("named INSERT ok  :", c.execute("SELECT * FROM m").fetchall())

Output:

before ALTER: [(1, 'coffee', 3.0)]
after ALTER : [(1, 'coffee', 3.0, None)]
positional INSERT -> OperationalError table m has 4 columns but 3 values were supplied
named INSERT ok  : [(1, 'coffee', 3.0, None), (2, 'beer', 5.0, None)]

Three things to take from that. ALTER TABLE ADD COLUMN does not damage existing rows. The new column is NULL for them. And the unnamed INSERT fails from the very next write onward.

It does not fail for one site. Every site goes through the same statement, so the entire push fails. The change that caused it was a display change, which puts the cause and the symptom far apart.

The fix is to define the column list once and build the SQL from it:

SITE_METRIC_COLS = ["snapshot_id", "site_key", "label", "url",
                    "ga_sessions_y", "ga_users_y", "ga_pv_y", "ga_sessions_7d", "ga_pv_7d",
                    "sc_clicks", "sc_impressions", "sc_ctr", "sc_position", "added",
                    "ga_organic_y", "ga_organic_7d", "ga_organic_engaged_7d"]

sm_sql = ("INSERT INTO site_metrics(" + ",".join(SITE_METRIC_COLS) + ") VALUES("
          + ",".join("?" * len(SITE_METRIC_COLS)) + ")")

Adding a column now touches three places: the CREATE TABLE, the ensure_columns list, and SITE_METRIC_COLS. Three sounds like a lot, but forgetting one fails loudly and immediately instead of silently reaching production.

Gotchas

• "It works today" is not evidence that positional INSERT is safe. As the repro shows, there is no warning until the first write after a column is added. Name columns from the start in any table you expect to grow.
• Separate NULL from 0 on day one. You cannot reconstruct the difference later; a stored 0 gives you nothing to decide with. Watch the collector too, where a ?? 0 default quietly destroys it.
• http.server is sequential. Switching to ThreadingHTTPServer is a one-line fix, but until you do, one favicon request stalls the page.
• ALTER TABLE ADD COLUMN is a metadata-only operation in SQLite, so it is fast regardless of row count. The flip side is that no default is written into existing rows. For this design, not writing one was the correct behaviour.

The result

A live example of the sites this dashboard tracks: https://hashitosystem.com

Wrap-up

Store operational metrics as append-only snapshots. Adding a metric costs one ALTER TABLE ADD COLUMN, no observation is ever rewritten, and NULL keeps its natural meaning of "not measured then".

In exchange, never use INSERT INTO t VALUES(?,?,?) in a table whose columns will grow. Keep the column list as a constant and generate the statement from it. If you make adding columns cheap, pair it with writes that survive the addition.

---

This article is about my own side project. It was written with AI assistance.