A True-Crime Database a Model Can't Lie To
I built a research database for cold cases where a model can query everything and invent nothing. Every fact in it has to point at the exact record it came from, and that rule is enforced by the database itself, not by a prompt or a policy.
Cold case data is the demonstration domain because it is where getting it wrong is worst. A confidently made-up connection between two people is not a bad answer, it is a defamation problem.
The problem
True crime is the worst possible place to point a language model, and amateur sleuths already produced the cautionary tales.
During the Boston Marathon bombing they misidentified a missing student as a suspect and turned on his grieving family. A tabloid ran two innocent men on its front page in a case that ended in a defamation settlement. Police organizations now write about these groups as an operational risk rather than a resource.
So the question is not "can AI help with cold cases." It is what a system would have to look like before you were willing to point a model at this data at all.
The answer is one database rule
Every assertion in this system lives in one table, and that table has a column that cannot be empty: a foreign key to the raw record it came from.
Not a citation string a model could type. A key that has to resolve to bytes actually stored in the database.
A model can propose a fact, but not its source. There is no code path that writes one without the other, so an unsourced claim is not something the system detects and rejects. It is something the system cannot represent.
Most current work on AI provenance is a referee: let the model generate, then break the output into atomic claims, retrieve evidence, and score whether the citations hold up. This pushes the guarantee into the schema instead, where a bug in the application layer cannot get around it.
Four more rules fall out of the same design
- Sources disagree, so keep both. Two conflicting claims are stored as two claims, never an overwrite. The conflict is data.
- The machine proposes, a human disposes. Every match candidate starts with a null verdict.
- A statistical result carries its caveat. Caveat is a required column, and every finding belongs to a numbered analysis run, so a number that will not reproduce is traceable to the run behind it.
- Access policy is code, not documentation. Every source carries a policy, and the check runs before any network call.
What "access policy is code" cost me in practice
That last rule has teeth. One federal missing-persons system has an internal JSON API that works unauthenticated and returns full case detail. Its robots.txt disallows everything.
The terms of use do not prohibit automation in words, but the operator's preference is unambiguous and this is a Department of Justice system. So it is marked manual only, and no script in this project can fetch it.
Unblocking it means editing the source registry and writing down why, which puts the reason in git history.
What is loaded
All of it is public federal or Census data. Roughly 910,000 case-level homicide records and 183,469 agency-year count records clear a million rows between them, and the Census, FBI and archive pulls sit on top of that.
The five sources, with years and row counts
- Roughly 910,000 case-level homicide records from the federal supplementary reporting program, 1976 to 2024, one row per victim.
- 183,469 agency-year records from the federal count-level program, 1965 to 2024, across 13,730 agencies.
- Census county poverty and income, 2005 to 2023, as denominators.
- Live FBI wanted, seeking-information and missing-person listings, pulled from the public API.
- A county crosswalk that ties two different geography schemes together, plus a historic newspaper archive queried on demand.
The Census data went in before any analysis, deliberately. Without population and poverty denominators, every geographic cluster is just a map of where poor people live.
What the joins found
The working question was whether stacking independent public datasets makes their contradictions visible. It does. Three things came out.
Homicides that exist as a number and not as a case
Those two homicide files, one a count per agency per year and one a row per victim, are two independent measurements of the same thing from the same program and the same aggregator.
In 604 agency-years, an agency reported five or more homicides to the count file and filed zero case records.
homicides present as a tally and absent as cases.
Why that matters beyond tidiness
Every case-level pattern method keys on victim details, geography and method, and a count carries none of those.
Those homicides are unreachable by any pattern detection, and an analyst who never joins the two files never learns they were excluded.
proposals, zero adjudicated. The caveat is the point, and the system enforces it. Every one of those is a machine proposal sitting in a review queue with no verdict, and none of them is a finding until a person rules on it.
States reporting whole years as 100 percent cleared
Twenty-five state-years report exactly zero unsolved homicides on 20 or more cases, against a national unsolved share of roughly 26 to 31 percent.
Almost certainly a reporting artifact, not falsification: either the state submits only cleared cases, or it never sends the update record that flips the flag.
It is not evidence of good policing. It does mean any clearance analysis on those states finds nothing, forever, and the analyst concludes there is no problem.
A direct contradiction between two federal datasets
The FBI is publicly seeking information on two unsolved 2019 homicides in one state. The national homicide file records that same state and year as zero unsolved.
Both are public federal data. They cannot both be right, and neither file alone shows it. Only the join does.
The review loop
One script is the only thing in the project that writes a verdict, and it will not accept one without a written reason, because a bare "rejected" six months later is indistinguishable from a misclick.
Why the queue is not sorted strongest candidate first
The default queue order is deliberately not strongest candidate first. The strongest candidate teaches you the least, because you already know the answer. A city with 732 counted homicides and zero case records in one year is not in doubt.
The queue round-robins across classes and severity bands instead, always drawing from the band with the fewest verdicts so far, so review effort lands where the uncertainty is.
The MCP server
An MCP server gives a model a read-only query surface into the database, enforced in three layers, the strongest of them verified at startup: the server attempts a write and refuses to start if it succeeds.
It also will not hand back a count without the integrity warnings for the state-years behind it, because a count drawn from a state-year claiming 100 percent clearance is a lie of omission.
The three layers, in descending order of how much they should be trusted
- A SELECT-only database role. Even a bug in the server cannot write. Temp-table creation is revoked too, because creating a temp table is a write and Postgres grants that to everyone by default.
- Read-only transactions set on every connection.
- A statement filter on the raw SQL tool. This is a blocklist, and blocklists leak, so it is the least trusted layer. It exists to produce a clear error message, not to be the thing standing between a model and the data.
What it will not do, and why that matters
No living person is named as a suspect, ever, and nothing gets published with case-level detail attached to a named individual. A proposal with no verdict is never called a match. Every finding here is about an agency's record keeping, not about a person.
Cold cases are the stress test, not the point. Anywhere an AI answer becomes a decision (a claim, a diagnosis, a compliance finding, a customer record) has the same problem: the model is fluent enough that a wrong answer looks exactly like a right one.
Provenance you check after the fact is better than nothing. Provenance the system cannot skip is a different category of thing. This is what it took to build the second kind, and what happened when I pointed it at data everyone assumes is already clean.