Give Any API a History: Poll It, Hash It and Query Old Versions With SQL
Ask a launch schedule API when a rocket flies and you get one date. Ask what the date was last Tuesday, or how many times it has moved, and there’s no endpoint for that. Most APIs describe the present. Prices, timetables, government datasets and status pages all change in place, and the old value is gone the moment the new one is written. That’s a pity, because launch dates slip so often that the list of changes in a launch schedule is more interesting than the current date.
You can add a history from the outside: poll the URL, keep every distinct version, query the versions later. Simon Willison called one form of this git scraping in 2020. A scheduled job fetches a URL and commits the result to a git repository, so the commit log becomes the dataset. It needs no server and no database, which is why it spread. The next step is a store that understands JSON well enough to ignore noise, share unchanged parts between versions and answer questions in SQL.
Where Git Scraping Stops
Git is a remarkable accidental fit, and its limits show up around the third week. Diffs are line-based, so a re-sorted object looks like a rewrite. Asking what a price was on a given date means walking commits with a script. The repository is the interface, and the query is whatever you can express in shell.
The other tools in the area are built for other jobs. changedetection.io is open source and watches web pages for changes, with notifications, which suits pages better than structured data. Visualping and Distill are commercial page monitors built to alert you that something changed. The Wayback Machine captures pages mostly on its own schedule, so it can’t give you the state at every poll. None of them is shaped around asking SQL questions of the history of a JSON document.
HTTP supplies the cheap “has anything changed” check. A conditional request carries If-None-Match with the ETag from last time (or If-Modified-Since with Last-Modified), and a server with nothing new answers 304 Not Modified and sends no body (RFC 9110). Plenty of APIs send no validators, and some send ETags that change on every request, so the tool treats them as hints and falls back to hashing the body.
Canonical Hash, Subtree Storage, Rows on Top
Each source gets a schedule. A poll sends a conditional request first, and a 304 just records “checked, unchanged”. A 200 goes through three steps: parse, canonicalize, store.
Canonical form means sorted keys, one spelling per number (1, 1.0 and 1e0 are one value), no insignificant whitespace, and the configured noise paths removed. A standard exists for the encoding half, the JSON Canonicalization Scheme (RFC 8785). Noise removal is policy, and policy belongs in config. Here’s a sketch of that file for a tool that doesn’t exist yet.
db: history.db
sources:
- name: launches
url: https://api.example.com/v1/launches?limit=50
every: 15m
headers:
Accept: application/json
ignore: ["$.generated_at", "$.meta.request_id"]
keys:
"$.results": id # array items are identified by their id field
Storage is content-addressed at the subtree level, Merkle-style. Every object and array is hashed from the hashes of its children, and each is stored once in nodes(hash, json). A snapshot is a single row in snapshots(url, fetched_at, root_hash). When one launch moves, that leaf changes, and so do the objects above it up to the root. Nothing else is written, because the other forty-nine launches are shared with the previous snapshot by hash. The cost of a change is the depth of its path, not the size of the document. Granularity needs a decision, since hashing every number as its own node would bury you in tiny rows. Small scalars stay inline in their parent, and only objects and arrays above a size threshold become nodes.
On top sit views that turn the tree back into rows. A recursive query walks from any root hash and rebuilds the document. A function, value_at(url, path, time), finds the snapshot in force at a moment and reads one path from it, and a view named changes has one row each time a path took a new value. Against a launch schedule, the interesting query looks like this (sample output, made-up data):
$ snap sql "SELECT fetched_at, old, new FROM changes
WHERE url = 'https://api.example.com/v1/launches?limit=50'
AND path = '$.results[id=1234].launch_date'
ORDER BY fetched_at"
fetched_at old new
2026-03-02T06:15:00Z 2026-03-14T14:30:00Z 2026-03-16T14:30:00Z
2026-03-11T18:45:00Z 2026-03-16T14:30:00Z 2026-03-21T13:00:00Z
2026-03-19T06:00:00Z 2026-03-21T13:00:00Z 2026-03-27T13:00:00Z
The join work stays hidden behind the view. It pairs naturally with keeping API responses in SQLite, which stores whole bodies by hash; this goes one level down, to subtrees.
The Hard Part Is Telling Noise From News
Every API has fields that change on every call: timestamps, request IDs, pagination cursors, signed URLs. Left in, they make every poll look like a change, and the history turns into a log of the clock. The tool can help find them. Fetch the URL twice back to back and list the paths that differ, a cousin of the trick Twitter’s Diffy used to separate noise from real differences. Those paths become ignore candidates for a person to confirm. The tool shouldn’t decide silently.
Arrays are the other big source of fake change. Diff two lists by position and an item inserted at the front looks like every item changed. Key the elements by an id field when there is one, as in the config above. When there isn’t, hash each element and treat the array as an unordered set, or fall back to position and mark the diff as low confidence.
Polling samples states at poll times, and it misses the events between them. Two changes inside one interval look like one, and a value that flips and flips back is invisible. The interval is the resolution of the history, and the output should say so. When the provider offers webhooks, webhooks versus polling covers the trade, but a webhook usually delivers the new state, so keeping the old ones is still your job.
A snapshot of a paginated collection isn’t atomic. While you read pages one to ten, items move: an insert at the front pushes one item from page one onto page two, so you see it twice, and a deletion makes you skip one. Cursor-based pagination helps, fetching quickly helps, and the snapshot should record that it was assembled from several requests over so many seconds. Treat it as a smear, not a point in time.
Every poll costs somebody else’s server. Respect Retry-After, treat a Cache-Control: max-age as a floor on the interval, send a User-Agent with a contact address, and read the terms of service and any robots rules. Keeping a history for your own analysis and republishing it are different acts, and terms often treat them differently.
Subtree dedupe saves space only while changes are small. If every price in a response is recomputed each hour, each snapshot is nearly all new nodes, so compress the nodes and thin old snapshots on a schedule (every poll for a week, one a day after that). Schedules run in UTC, timestamps are stored as integers in UTC, and “every day at 9” needs an explicit zone, because local time skips or repeats an hour where daylight saving applies.
Who Would Pay and What 0.1 Skips
Price trackers, procurement and compliance teams, journalists watching government data, competitive intelligence: each wants a past that the source doesn’t keep. A public dataset such as federal contract awards is cheap to poll, since a POST search against it needs no key, so the first user can be one person with a cron job. The split is the usual one. The open-source core is a CLI and a SQLite file, and the paid part is what people don’t want to operate: scheduling from several places, keeping the poller alive, and alerting when a path changes.
Version 0.1 is a CLI that reads a YAML list of sources and intervals, sends conditional requests, hashes canonical JSON, dedupes subtrees, writes SQLite, and offers two commands, history and diff. It refuses HTML scraping, which is the page monitors’ territory and a different swamp. It refuses authentication flows beyond static headers. Alerting integrations wait until the history is trustworthy. A store like this also hands make for APIs its change signal, since downstream steps rerun only when a root hash moves, and it’s a small cousin of an embedded database built around change history.
History can’t be backfilled, so the clock starts when the poller does.