The Orphan Table

Every record a police department sends to the FBI's National Incident-Based Reporting System carries a one-character alphabetic field at position six. The 2025.0 NIBRS Technical Specification calls it Segment Action Type. It takes five values. One of them is D, and a D against an incident already on file “deletes all data from the database that is associated with a Group A Incident Report.” Three fields are mandatory to do it: the agency identifier, the incident number, the incident date. Everything else, the spec says, is ignored.

Most software treats a crime feed as an append-only log. Incidents happen, incidents get written, the file grows. That model is wrong, and position six is where it goes wrong.

Position six

The five values are worth learning, because two of them are almost always misread.

I Incident Report. All known data at the time a Group A incident is first submitted.

A Add. Group B arrest reports and Zero Reports only. Not Group A incidents.

D Delete. Removes the incident and everything hanging off it.

M Modify. Narrower than it sounds. See below.

W Time-Window Submission. For facts that arrive after the incident is too old to touch.

Note that a Group A incident does not use A. It uses I. Agencies get this wrong, vendors get this wrong, and the resulting rejections come back in a fixed-width error file where, if one segment of a twenty-five-segment incident fails, only that one segment is described. The other twenty-four are also rejected. The spec is explicit that they will not appear in the error file. You are told about one problem and quietly handed the consequences of twenty-four more.

There is no edit

Here is the part that surprised us, and we have been ingesting these feeds for years.

Suppose a detective needs to correct one field on a robbery from last month. A victim's age was typed wrong. In most systems that is an UPDATE. In NIBRS it is not. The spec says the I action type is “also used to resubmit an entire Group A Incident Report previously reported to the FBI, but which is deleted because one or more segments needed to be added, modified, or deleted.”

Read that again. To change one field, the agency deletes the whole incident and sends all of it back. Update is not a primitive here. Delete-then-rewrite is the primitive, and the spec has to add a guardrail about ordering: an agency's system “must be designed so that the first I = Incident Report submission is never preceded by a D = Delete action for that incident.” That sentence exists because somebody shipped it the other way around.

The consequence for anyone downstream is small and permanent. There is a moment when the incident does not exist. If your pipeline polls during that moment, it sees a deletion. If it polls after, it sees an incident with a new revision it has no way to detect, because nothing in the record says “this is version two.” The incident number is the same. The date is the same. One field moved.

The practical version: two fetches of the same incident, a week apart, can differ in any field, and the feed will not tell you that it changed or why. We have watched pipelines treat the second fetch as a new incident and double-count it. We have watched others treat it as unchanged because the primary key matched, and silently keep the stale copy.

The column that stopped being public

The old Summary Reporting System handled retractions in the open. Return A, the monthly form for offenses known to police, had columns. Column 2 was offenses reported. Column 3 was unfounded. Column 4 was actual offenses, and it was defined as the difference: reported minus unfounded. A reader could see all three numbers and form an opinion about the middle one.

NIBRS eliminated the category. Jacob Kaplan's practitioner's guide to FBI data puts the old rates plainly: unfounded reports run under 2% for most crime types, with one exception, where “some agencies report that over 10% of rapes in certain years are unfounded.”

Which brings us to Baltimore. In 2010 the Baltimore Sun reported that city police were discarding rape complaints as unfounded at more than 30% — roughly five times the national average, and the highest rate in the country. Over the same stretch, reported rapes in Baltimore fell from 684 in 1995 to 158 in 2009. The Justice Department was still finding those investigations deeply flawed six years later.

We are not claiming NIBRS caused anything in Baltimore. The reporting predates the transition. The point is narrower and, in our view, worse: the number that let the Sun tell that story used to have a column. Now the same decision is a D at position six, and nothing about the retraction reaches the person reading the data. The rate did not become less interesting. It became harder to compute.

The orphan table

Crime facts arrive late. An arrest lands two years after the burglary. Property turns up in an evidence locker. A clearance gets exceptional status long after the case went cold. NIBRS has a channel for this, and it is the strangest thing in the document.

Appendix D describes Time-Window submissions: a fragment used to update an incident that falls outside the UCR program's two-year reporting window, a limit the spec attributes to “the legacy UCR technical solution.” Then it explains what happens to one.

“When a Window Record is received by the UCR system (both legacy and Tech Refresh), the added data does not append to an existing incident. The data is stored within an orphan table. The system does not, and will not, provide a notification to the contributor regarding status within the database.”

Does not, and will not. An agency sends the FBI a real arrest for a real incident, and the arrest goes into a table attached to nothing, and no one is told. (The appendix carries a 2023 footer inside the 2025 document, and notes that from January 1, 2021 the program reverted to pre-NIBRS policy for collecting arrest, clearance, and recovered property. We would not build against this paragraph without asking the UCR program directly, which is what the appendix tells you to do.)

There is a second detail in that appendix we have not seen discussed anywhere. W and D occupy the same position in the administrative segment, so the system cannot always tell whether an incoming delete is aimed at a window record or at an entire incident. The disambiguation is a heuristic. If the delete carries exceptional clearance codes, it is treated as a delete of the window record. If it does not, it is treated as a delete of the incident.

A whole incident's existence turns on whether a clearance code happens to be populated. That is the actual rule, in the actual spec, for the actual national crime database.

What a delete does to a rate

Percent change is the unit this entire industry trades in. The Real-Time Crime Index currently shows violent crime down 5.9% and murder down 17.3% for January through June 2026 against the same months of 2025, across 590 agencies covering 119.4 million people. Property crime is down 10.1%, motor vehicle theft down 18.7%. Those are careful numbers from a careful project, and we cite them constantly.

A percent change compares two periods. Both periods are mutable. The 2025 figure you are dividing by is not the 2025 figure that existed in 2025 — it has absorbed a year of deletes, resubmissions, late arrests, and reclassifications. Some of those move it up and some move it down, and the net is usually small. Usually is doing work in that sentence.

Reclassification is the cleanest example. Under UCR rules, a death caused by injuries from an assault is counted as criminal homicide. So an aggravated assault in March, whose victim dies in June, retroactively becomes a March homicide. March's homicide count goes up three months after March ended. Nobody did anything wrong. The number was correct in March and is correct now, and the two numbers are different.

We wrote about a related version of this when LAPD published a backfill of 150,431 cases in a single release. That was an extreme case of an ordinary mechanism. Every agency does a smaller one every month.

Six things we do about it

None of this is exotic engineering. It is the standard treatment for a mutable source, applied to a domain that mostly pretends its sources are immutable.

Store two timestamps, not one. When the incident happened, and when we learned what we currently believe about it. Bitemporal storage is the whole fix, and everything below is a consequence of it.
Tombstone, never hard-delete. An incident that vanishes from a source becomes a row marked withdrawn, with the date we stopped seeing it. Deleting our copy destroys the only evidence that a retraction happened.
Snapshot before you compute. Any published figure gets pinned to an immutable snapshot. If we cannot reproduce a number we shipped, we should not have shipped it.
Diff every refresh, and alarm on the diff.Not just new rows. Changed fields and disappeared rows. A day where 4% of last month's incidents changed is worth a human look; that is usually a records migration, and records migrations break time series.
Never cache a count without its vintage.A cached “47 incidents this month” with no as-of date is a number that will eventually be wrong with no way to notice.
Show the vintage to the user.“As of” is not clutter. It is the difference between a figure a reader can check and a figure they have to trust.

The failure mode we care most about is the quiet one. A block-level safety score computed on Monday, cached, and served for six months while three of the incidents underneath it are withdrawn. Nobody sees an error. The score is just wrong, slowly. We went into how thin the ground gets at that resolution in our post on small-number statistics, and mutation makes it thinner: at three incidents, one retraction is a 33% move.

If you are evaluating a crime data vendor, this is a good question to ask them, and a short one. What happens in your system when an incident is deleted at the source? The honest answers are a tombstone and a diff. The answer you will usually get is a pause. We wrote a longer evaluation checklist for the rest of it, and a primer on NIBRS itself if position six was new to you.

The record that points nowhere

Somewhere in the FBI's system there is a table of arrests and recovered property and exceptional clearances, submitted in good faith by agencies who did the work, attached to no incident and reported to no one. The spec names it in passing, in an appendix, in a paragraph that ends by telling you to email the program if you want to know more.

Crime data is not a log of what happened. It is the current state of a database that a few thousand agencies can write to, and that state has a history nobody is keeping. If you want that history, you have to keep it yourself, starting from the day you decide to.