A Database Where Every Value Remembers Its Source, Confidence and Extraction Time
Somebody pastes a population figure into a shared spreadsheet: 1,234,567. It might have come from a census table, a Wikipedia infobox, a city press release or a model’s summary of one of those, and three weeks later nobody can say which. The paste kept the digits. Everything that made them believable stayed in a browser tab that closed long ago.
A value without provenance is a rumor with a data type.
Databases make the same mistake with better manners. A population column holds an integer, and the row may carry created_at and updated_by, but those columns describe the write. They tell you when your system heard the number and which service account stored it. They can’t tell you which document said it, what that document was counting (city proper or metro area, and for which year), or how sure the extractor was when it pulled the figure off page 12 of a PDF.
The tool worth building is a small database where provenance is part of the value, and where the store refuses any value that shows up without it. The obvious version, a source text column beside each field, breaks almost at once. One fact can have three sources that agree and two that don’t, and a derived value (a per-capita rate, say) gets its provenance from other rows instead of from a URL. So evidence gets its own table and values point at it. Computed values inherit the evidence of their inputs without anyone typing a link.
AI agents make this urgent. An agent that writes “the population is 1.2 million” into a report should be able to say which document it read and when it read it, and so should the next agent that pulls that report back out of storage. A retrieval pipeline can usually print a citation, because it keeps chunk IDs around. Once facts get extracted into structured tables, the citation tends to vanish, which is the spreadsheet paste again at machine speed.
What Already Exists
Wikidata is the best public example of the model working. A statement such as a city’s population carries references (where it was stated), qualifiers such as “point in time”, and a rank: preferred, normal or deprecated. Several population figures can coexist on one item, each with its own sources, while the preferred rank tells consumers which to use by default. That’s the shape you want. It’s also a wiki, ranked by human editors, and you can’t embed it in an app.
W3C PROV, published in 2013, supplies a shared vocabulary for the same ideas: wasDerivedFrom, wasGeneratedBy, wasAttributedTo, generatedAtTime. It’s an interchange format. It doesn’t say how a query engine should carry provenance from inputs to outputs.
Database research worked that part out. The provenance semirings paper by Green, Karvounarakis and Tannen (PODS 2007) showed you can tag every input row and push the tags through a query algebraically: a join multiplies its inputs’ annotations, and a union or a projection that merges rows adds them. A result row produced by joining rows a and b, or alternatively by row c on its own, carries the polynomial a·b + c. Stanford’s Trio project kept uncertainty and lineage together in one system. Pipeline tools sit at the coarse end of the scale; dbt’s lineage graph tells you which model built which table, which is useful and far too blunt to explain one number in one cell.
The pieces are known. The gap is packaging: a store small enough to embed in an agent, a newsroom tool or an analyst’s notebook, where provenance is mandatory and propagation works for the handful of operations people actually run.
The Shape of the Store
Four tables carry most of it. A fact row names a subject and a property and holds one candidate value. An evidence row describes one retrieval: which source, which archived copy, which extractor. A link table attaches evidence to facts with a locator and a confidence, while a derivation table records computed facts as edges back to their inputs.
CREATE TABLE evidence (
id INTEGER PRIMARY KEY,
source_uri TEXT NOT NULL,
snapshot TEXT NOT NULL, -- sha256 of the bytes as retrieved
retrieved_at TEXT NOT NULL,
extractor TEXT NOT NULL -- '[email protected]', 'llm:claims-v3'
);
CREATE TABLE fact (
id INTEGER PRIMARY KEY,
subject TEXT NOT NULL, -- 'city:Q12345'
property TEXT NOT NULL, -- 'population'
value TEXT NOT NULL,
valid_at TEXT, -- when the source says it was true
rank TEXT NOT NULL DEFAULT 'normal'
CHECK (rank IN ('preferred', 'normal', 'deprecated'))
);
CREATE TABLE fact_evidence (
fact_id INTEGER,
evidence_id INTEGER,
locator TEXT, -- 'page=12;table=3;row=7' or a char span
confidence REAL
);
CREATE TABLE derivation (fact_id INTEGER, input_id INTEGER, op TEXT);
Two columns matter more than they look. snapshot is a content hash of the source as it was read, because a URL is a promise and promises break. And valid_at stays apart from retrieved_at: a 2021 census figure read yesterday is a 2021 fact, and anything that reasons about age, such as rules for facts that expire, needs the older date.
Several facts can share a subject and property. That’s deliberate. Two sources that disagree become two rows, each with its own evidence, and a ranking policy picks the preferred one for default reads: explicit rank first, then source tier, then the latest valid_at, then confidence. Queries get the preferred value unless they ask for the whole argument. When the disagreement is itself the story (who said what about whom), you’re in the territory of an evidence graph built from extracted claims, which sits naturally on top of a store like this.
The query side needs only a few additions to plain SQL. A sketch of the proposed functions:
SELECT subject, value, provenance(value)
FROM preferred_fact
WHERE property = 'population'
AND min_confidence(value) >= 0.8
AND NOT from_source(value, 'aggregator.example');
provenance(value) returns the evidence set as JSON. For a copied value that’s one evidence row and a locator. For an average of forty district populations it’s the union of forty evidence sets, and that’s where the bill arrives.
Where It Gets Expensive
Storage comes first. The population itself takes a few bytes; a source URL, a timestamp, an extractor string and a locator take a few hundred, so naive provenance can outweigh the data by two orders of magnitude. Deduplicate aggressively: one evidence row per document per extractor run, with locators that point into the archived snapshot instead of copying quoted text. Snapshots get stored once by hash, however many facts cite them. A census table with ten thousand rows should cost one snapshot, one evidence row and ten thousand short locators.
Aggregates are the second problem. A SUM over a million rows has a million-element provenance set, and building it on every query is silly. There are two ways out. Compute lineage lazily, only when someone calls provenance(), by re-running the query with tracking switched on. Or make the store append-only, so a derived value’s provenance can be recorded as “this query, over this version of the data” and expanded on demand. The second is cheaper and more honest. It’s also one more reason never to update a fact in place: supersede it with a new row and demote the old one.
Confidence is third, and people underestimate it. A float named confidence invites everyone to type 0.8 and mean nothing in particular. A language model’s self-reported certainty isn’t calibrated either. The version that holds up measures confidence per extractor and source class: hand-check 200 values the PDF table parser pulled from statistics-office tables, find 194 correct, and that pairing earns 0.97. Store which calibration run produced the number, so it gets recomputed when the extractor ships a new version.
Link rot is fourth. A URL cited today can return a 404, redirect to a homepage or quietly change its numbers within months. Archive the bytes at retrieval time and keep the hash in the evidence row. For sources that update on a schedule, a polling job that hashes every version gives you the same guarantee with a history attached. Without the snapshot, provenance is a pointer to something that may no longer say what you claim it said.
Then the interface. Provenance that buries the value fails as badly as provenance that’s missing. Show the value plainly, with a small marker for source count and age that expands on click. When a lower-ranked value differs from the preferred one by more than a set tolerance, flag it where the reader can’t miss it, because hidden disagreement is how an analyst ends up defending a figure that two of their own sources contradict.
A First Version That Says No
Before building, count. Take the tables you already have and measure how many values have any recorded source, the same two-minute habit as auditing an API field’s completeness before building on it. If the number is close to zero, that’s the argument for making provenance mandatory at the door.
Version 0.1 is a library over SQLite: the four tables, an insert call that rejects a fact without at least one evidence link, a content-addressed snapshot directory, a ranking policy in a small config file, and a why(fact_id) call that walks derivation edges back to source spans. Derivations come from a short list: copy, unit conversion, arithmetic between named facts, and SUM, AVG, MIN and MAX, each recording its inputs.
It refuses plenty. There’s no provenance through arbitrary SQL, because that’s still a research problem and the short operator list covers most reporting work. Confidence scores are never invented, only loaded from stored calibration runs, and the library never rules on which source is telling the truth. A check that found nothing counts as evidence too, and recording verified absence can reuse the same evidence table.
Nobody asks where a number came from until it’s wrong.