
The Analyst's Stack Part 22 of 22
Storing and searching what you collect
The opening post named 6 stages and said that 2 of them are the ones nobody asks for. This is those 2 stages, taken as seriously as they deserve.
Nobody asks for them because the question they answer only arrives later. It arrives about eight months in, and it sounds like this:
I have been collecting for most of a year. Where is all of it, and how do I get an answer back out of it?
If you have not hit that yet, the rest of this post is cheap insurance. If you have, some of it is a repair job, and I will be honest about which parts.
The one idea: store the observation, not your conclusion
Here is the single most useful thing I know about storing collected material, and it takes one sentence.
An observation is “source X said this, on date Y”, and it never changes. Everything else is derived, and everything derived can be thrown away and rebuilt.
A canonical name is derived. A merged event is derived. A classification, a translation, a relevance score, a tag your script applied: all derived. None of it is what the source said. It is what you concluded from what the source said, using the rules you had that week.
Most people build it the other way round without noticing. You fetch a page, parse out the 3 fields you care about, write those 3 fields to a table, and drop the rest. That table now holds your interpretation and nothing else. It is a perfectly good table right up until the first time your interpretation was wrong.
And it will be wrong. Not through carelessness. The source adds a field, changes a date format, starts putting the country in the title instead of its own column, and your parser has been quietly reading the wrong thing for 5 weeks. Now ask the question that decides everything: can you fix that without going back to the network?
If you stored only your interpretation, the answer is no. You have to re-fetch, from sources that rate-limit you, that have rotated their pages, that have removed the item entirely. Some of those weeks are simply gone.
If you stored the observation, the answer is yes, and the fix is a script that runs while you make coffee.
Read that diagram as a promise you make to yourself: nothing to the right of raw bytes is precious. If a layer cannot be deleted and regenerated, it has quietly become a second source of truth, and you now have two.
What that looks like as an actual table
Two tables, not one. This is a sketch you could paste into SQLite tonight and change later.
-- Layer 1 and 2: append-only. Rows are never updated, only added.
CREATE TABLE observations (
obs_id TEXT PRIMARY KEY, -- fingerprint of the item, not a counter
source TEXT NOT NULL, -- which source said it
fetched_at TEXT NOT NULL, -- when you asked
stated_at TEXT, -- the date the SOURCE claims. may be null
raw_path TEXT NOT NULL, -- where the bytes that produced this row live
parser_version INTEGER NOT NULL, -- which rules produced the fields below
subject TEXT,
title TEXT,
body TEXT
);
-- Layer 3: derived. Safe to DROP and rebuild from observations at any time.
CREATE TABLE entities (
entity_id TEXT PRIMARY KEY,
display_name TEXT NOT NULL,
first_seen TEXT NOT NULL,
resolver_version INTEGER NOT NULL
);
CREATE TABLE entity_members ( -- which observations joined which entity
entity_id TEXT NOT NULL,
obs_id TEXT NOT NULL,
PRIMARY KEY (entity_id, obs_id)
);
Three of those columns are where the design lives.
source and fetched_at are columns, not folder names. The moment you have 2
sources you will want to ask “what did this one say, before the other one confirmed it”,
and a folder cannot answer that.
stated_at is separate from fetched_at, and is allowed to be empty. The date the
source claims and the date you collected it are different facts, and conflating them is
one of the most common ways an OSINT timeline ends up wrong. Many sources give you a date
you cannot trust. That is information. NULL records it honestly; fetched_at in its
place records a lie.
Every derived thing carries the version of the rules that made it. Bump
parser_version when you change how you read a field, bump resolver_version when you
change how you decide 2 records match, and you can always answer “which rows need
redoing” with a WHERE clause instead of a guess.
Keep the bytes
The direct consequence of all this: store what the source actually returned. Not the parsed fields. The response body, as it arrived.
The mechanics are unremarkable. Gzip each response, name the file after the hash of its
contents, and put the path in raw_path. Naming by content hash means an identical
response fetched twice is the same file on disk, so nothing is stored twice and no path
can quietly point at the wrong bytes.
This costs disk, which is the cheapest thing in the stack and getting cheaper. It is also more compressible than you think: in one system of mine, one source’s stored payloads compressed 4.43 times, from 21,567,122 bytes to 4,871,975. That is one measured source in one corpus rather than a rule, but the shape is typical for text.
What you buy for that is the ability to say “my parser was wrong for 5 weeks” and then fix it in an afternoon. I have done exactly that, more than once, and it is the difference between a defect and an incident.
Two things the raw layer is not
Deduplication: the same item, arriving twice
Now the first of the 2 matching problems, and the easy one.
The first workflow post built a stable identifier so a monitor would not message you 96 times about one entry. That idea is the whole of deduplication. Grown up slightly, it is 3 rules.
Fingerprint the parts that identify the item, and nothing else. A hash of the URL and the title is usually right. A hash of the whole response is usually wrong, because most pages change something on every load: a session token, a view counter, a rendering timestamp. Fingerprint those and every fetch looks new forever.
Make the fingerprint the primary key. Not a column you check. The key.
Insert, and let the insert refuse. INSERT ... ON CONFLICT DO NOTHING in Postgres,
INSERT OR IGNORE in SQLite. The row count tells you whether it was new.
That last one is not a style preference. “Check whether it exists, then insert if not” is 2 operations with a gap in the middle, and 2 copies of your collector running at once will both look, both see nothing, and both write. The database can decide this atomically and you cannot. Let it.
Entity resolution: 2 records that might be one thing
Deduplication asks whether 2 records are literally the same item. Entity resolution (deciding that 2 differently-spelled records describe the same real thing) asks whether they are about the same thing despite having nothing in common that a computer can see.
This is the hard one. It is also the one that decides whether your counts mean anything.

Normalize before you compare
Never compare the names as they arrived. Reduce each one to a join key first: a stripped-down string whose only job is to be identical for 2 spellings of one thing.
import re
LEGAL_SUFFIXES = r"\b(inc|incorporated|llc|ltd|limited|gmbh|bv|plc|srl|pty|corp)\b"
MIN_KEY_LENGTH = 4 # anything shorter is an acronym, and acronyms collide
def join_key(name: str) -> str | None:
"""Reduce a name to something two spellings of it can agree on."""
key = name.casefold()
key = re.sub(LEGAL_SUFFIXES, " ", key)
key = re.sub(r"[^a-z0-9]+", "", key) # punctuation, spacing, everything
return key if len(key) >= MIN_KEY_LENGTH else None
join_key("Northwind Logistics GmbH") # 'northwindlogistics'
join_key("NORTHWIND LOGISTICS") # 'northwindlogistics'
join_key("NWL") # None. too short to join on
Two judgement calls sit behind that code.
The length guard is not fussiness. A 3-character key will collide, and it will collide with something plausible. Refusing to join on a key that short costs you a few real matches and saves you from confidently merging 2 unrelated organisations, which is a trade I will take every time.
Do not strip words that carry meaning. Legal suffixes are noise. Words like group, co and holdings look like noise and are not, because stripping them makes every organisation that shares a first word look like one organisation. Measure before you add a word to that list, and expect to remove some.
A name is not an identity on its own
A shared join key clears one test, not all of them. Almost always you need a second signal, and the most useful one is time.
NEAR_DAYS = 30 # same key, close in time: probably one thing
STRONG_KEY_DAYS = 90 # if the key is a domain or a registration number, widen it
def same_thing(a, b) -> bool:
if a.key is None or b.key is None:
return False # refused a key, so refuse the merge
if a.key != b.key:
return False
if a.stated_at is None or b.stated_at is None:
return False # no usable date means no merge, ever
window = STRONG_KEY_DAYS if a.key_is_strong else NEAR_DAYS
return abs((a.stated_at - b.stated_at).days) <= window
You will want to know where 30 came from. The honest answer is that I chose it, and so will you. There is rarely a clean gap in the data to put a threshold in, so any number you pick is a policy wearing the clothes of a discovery. Write it down as a named constant, say in your notes what it means, and change it deliberately rather than by feel.
And then check it. A resolver is a classifier like any other, so the previous post applies without modification: take a few dozen pairs, decide by hand which are the same thing, and measure your rules against that before you believe the collapse.
The rule that matters more than any threshold
A false split is recoverable. A false merge is not.
A false split leaves 2 rows where there should be one. You can see both. Someone reviewing the output notices the duplicate, and you merge them. The cost is visible and the fix is cheap.
A false merge fuses 2 different things into one row and deletes the evidence that there were ever two. Nothing in the output looks wrong. The count is smaller and cleaner and incorrect, and there is no reviewer in the world who spots an absence.
So when the rules are ambiguous, split. Build the bias into the code rather than into your intentions: refuse short keys, refuse missing dates, refuse a match that rests on a single weak signal. You will end up with some real duplicates surviving as separate rows, and you will know that, and you can say so.
Here are both numbers from a run of the rules above, join_key and the 30-day window,
against a small seeded demo database of invented organisations. One difference from
same_thing: the demo applies the window as a chain rather than pair by pair. Records that
share a key are sorted by date, and each one joins the group if it falls within 30 days of
the one before it, so a single entity can span more than 30 days end to end:
$ python3 seed.py demo.db
seeded 341 observations into demo.db
$ python3 resolve.py demo.db
read 341 observations
wrote 132 entities (resolver_version 1, window 30 days)
341 observations -> 132 entities: 2.58 observations per entity
Here is the general point, with a real number under it. In one corpus of mine, 340,920 observations collapsed into 120,063 canonical events across 90,565 victims, from 18 sources. Nearly 3 records per real event.
That ratio is the whole argument for this post. Before resolution, the honest description of that pile was “340,920 things sources said”, and every count drawn from it was inflated by a factor you could not see. Observations and events are different objects with different meanings, and a system that does not distinguish them will answer “how many incidents” with a number that is really “how many times somebody mentioned one”. The case study on that system walks through what it took; the point here is smaller and applies to every collection you will ever build.
Searching it afterwards
This is the part people over-plan.
What you need is almost always narrower than you fear. Across every collection tool I have built, analysts ask 4 kinds of question, and only 4:
- By source. What did this one publish, and what did only this one publish?
- By date range. What arrived, or was stated to have happened, between these dates?
- By tag or category. Everything matching this label.
- By text. Everything mentioning this string.
A relational database answers all 4, provided you build the indexes (a lookup structure that lets the database find matching rows without reading every row) for the first 3:
CREATE INDEX obs_by_source_date ON observations (source, stated_at);
CREATE INDEX obs_by_stated_at ON observations (stated_at);
-- Text search, Postgres. SQLite's FTS5 does the same job with different syntax.
ALTER TABLE observations ADD COLUMN search tsvector
GENERATED ALWAYS AS (to_tsvector('simple', coalesce(title,'') || ' ' || coalesce(body,''))) STORED;
CREATE INDEX obs_search ON observations USING GIN (search);
That is genuinely the whole search layer for a corpus in the low millions of rows, and it will answer in milliseconds.
A dedicated search engine earns its place when you need something a database is bad at: ranked relevance across a large body of text, fuzzy matching on misspellings, faceted counts recomputed live as someone narrows a filter, or languages that need their own tokenisers. Those are real needs, and they arrive with real scale.
What a search engine will not do is rescue a store with no stable identifiers and no resolution layer. It will index your duplicates beautifully. If you are choosing between spending a week on a search stack and a week on the 2 matching problems above, spend it on the matching.
One index most people forget
What the numbers are actually worth
Now the part that separates a dataset from a claim.
A resolved corpus produces clean-looking counts, and clean-looking counts get quoted. Before yours does, find out what its weakest axis is. Usually it is time.
In the corpus above, 73% of events sit at exactly midnight and 11% fall on the first of a month. Not because anything happened at midnight. Because the upstream sources supply day-precision or month-precision dates, and a date with no time in it becomes midnight the moment something stores it as a timestamp. Nothing records which dates were precise and which were rounded.
Here is that shape on a small seeded demo database, where the cause is known:

GROUP BY query on that database.Back to the real corpus, and live with it for a moment, because the consequence is concrete. A report covering the first to the seventh of a month picks up every month-precision record for that entire month. It over-counts, every time, and the output looks exactly as tidy as a correct one.
The fix is not technical. It is to publish the caveat next to the number instead of in a footnote nobody reaches. A figure that ships with “73% of these dates are day- or month-precision” is a dataset. The same figure alone is a claim, and it is one you cannot support.
Resolution is not verification
The event count is a ceiling, and it moves
Where this leaves the series
The series ran to 22 posts, and the last one is about a database schema. That is not an accident of scheduling.
The opening definition was that automation moves the act of looking from your attention to a machine’s, so your attention goes to deciding what things mean. Stage 3 is what decides whether the machine remembers anything it looked at. Get it wrong and you have built something that looks at everything, every time, and knows nothing: a very expensive way of being surprised by the same item twice.
Get it right and the looking becomes cumulative. Eight months of collection stops being a pile you have to read and becomes something you can ask a question of, with the raw bytes underneath it in case you asked the wrong one. That is the only reason to have collected it.