Salaheldinaz

Search posts

Table of contents
AI-generated illustration: Long rows of metal shelving filled with labelled cardboard boxes, receding under fluorescent ceiling lights.

The Analyst's Stack Part 22 of 22

Storing and searching what you collect

16 min read osint automation data methodology
The Analyst's Stack · Methods

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.

Four layers left to right: raw bytes, observations, entities, the view you query. A dashed arrow runs underneath from raw bytes back along to the view, showing that every layer to the right of raw bytes can be rebuilt from it. Observations is highlighted. Four layers left to right: raw bytes, observations, entities, the view you query. A dashed arrow runs underneath from raw bytes back along to the view, showing that every layer to the right of raw bytes can be rebuilt from it. Observations is highlighted.
Four layers, one direction of trust. Only the leftmost is a fact about the world. Everything else is a fact about your rules, which is why it has to be disposable.

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.

A bar chart of one source's stored payloads before and after gzip. As returned, 21,567,122 bytes. Gzipped, 4,871,975 bytes, 4.43 times smaller. One measured source in one corpus, not a rule. A bar chart of one source's stored payloads before and after gzip. As returned, 21,567,122 bytes. Gzipped, 4,871,975 bytes, 4.43 times smaller. One measured source in one corpus, not a rule.
Keeping the bytes is cheaper than it sounds. The measured source from the paragraph above, drawn to scale.

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

It is not a backup, because it is useless without the code that reads it. And it is not a free pass on what you collect: personal data in a raw payload is still personal data, and keeping it forever is a decision with a legal shape, not just a storage cost. Decide the retention period when you build the store, not when someone asks you about it.

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.

Two rows. Deduplicate: the same item arrives twice, you fingerprint it, the key refuses it, and the matter is settled with no judgement. Resolve: 2 spellings of one thing, you normalize a key, you add a time window, and you merge or split, preferring the split. Two rows. Deduplicate: the same item arrives twice, you fingerprint it, the key refuses it, and the matter is settled with no judgement. Resolve: 2 spellings of one thing, you normalize a key, you add a time window, and you merge or split, preferring the split.
Two problems that get confused constantly. The top row has a right answer. The bottom row has a policy.

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.

A 2-row table from a demo database. Row 1: source-a, organisation “Northwind Logistics GmbH”, stated_at 2026-06-11 09:12:00, precision “full timestamp”. Row 2: source-c, organisation “NORTHWIND LOGISTICS”, stated_at 2026-06-11 00:00:00, precision “date only (stored as 00:00:00)”. The 4 compared columns are highlighted; a fifth column, obs_id, holds each row’s own ID and is not.

A human sees this in under a second. Nothing in those 2 rows is byte-identical, so nothing you wrote for the previous section will touch them. Invented rows in a seeded demo database, viewed in Datasette; the highlight on the compared columns is added for the picture.

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.

The 2 ways a resolver goes wrong. A false split: one thing with 2 spellings is kept as 2 rows, which stay visible, so a reviewer notices the duplicate and merges them. A false merge: 2 different things sharing a key are fused into one row, and the evidence that there were ever two is gone; the count is smaller, cleaner and wrong. The 2 ways a resolver goes wrong. A false split: one thing with 2 spellings is kept as 2 rows, which stay visible, so a reviewer notices the duplicate and merges them. A false merge: 2 different things sharing a key are fused into one row, and the evidence that there were ever two is gone; the count is smaller, cleaner and wrong.
Two mistakes, only one of them visible. Build the resolver so that when it is wrong, it is wrong in the top row.

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
The 2 numbers you want printed on every run. A rule change that quietly merges more is the kind of thing you want to notice the same day you made it. Invented data: 341 seeded observations from 4 demo sources, in the schema above. The 2.58 per entity is the demo’s own ratio, not a measurement of the real corpus below.

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

Index the column you sort by, not just the columns you filter on. “The most recent 50” is the most common query in any monitoring tool, and without an index on the sort column the database reads everything and throws almost all of it away. This is the difference between a page that loads instantly at 10,000 rows and one that times out at 2,000,000.

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:

A bar chart titled “Event hour, seeded demo database”: 24 bars, one per hour. The bar for hour 00 reaches 167; every other hour sits between 2 and 12. A caveat panel below it says 160 of the 167 midnight rows come from the 2 demo sources that give a date without a time, so the bar measures date precision, not activity.

The shape of an imprecise date column. Nothing happened at midnight. This is what day-precision looks like once something has stored it as a timestamp. The data is invented: a small seeded SQLite database in which 2 of 4 demo sources give a date only, and the ingest parses those into timestamps, so each one lands on 00:00. That accounts for 160 of the 167 rows at midnight; the other 7 are full timestamps that happen to fall in that hour. The chart is plotted directly from the result of an hour-of-day 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

A resolved event is an accurate summary of what your sources said. It is not evidence that the thing happened. The collapse makes counting possible and makes nothing true. If 5 sources agree and 4 of them copied the fifth, you have one source and 4 mirrors, and a system that counts them as 5 will hand you a confidence you have not earned.

The event count is a ceiling, and it moves

Because you prefer splitting, some real duplicates survive as separate rows: your event count is an upper bound on events and your entity count is a lower bound on coverage. Worse, both numbers change when you improve the rules and re-run. That is the system working as designed, and it means a figure you quote has to carry the date it was measured on. A number from a rebuild 3 months ago is not wrong so much as it is about a different corpus.

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.