Overview / Ingest & Entity Resolution

Ingest & Entity Resolution

The dataset describes machines. A sales team needs companies. Everything difficult about this project lives in that gap.

The dataset is not what the brief implied

Profiling the first 8 MB settled the product direction before any code was written. It is not company data — it is internet scan records, one row per exposed service on an IP address. That makes it intent data, not fit data: almost nothing about company size or industry, a great deal about what is broken right now.

The trap

The org field names whoever registered the IP block — not the company using it.

Google LLC         2,745,419   30.8%
Incapsula Inc      1,778,843   20.0%
Cloudflare, Inc.     302,449
Amazon.com, Inc.     287,584

Group by org and your top prospects are Google, Imperva and Amazon — worthless to a rep and instantly disqualifying. So the entity key is the registrable domain (eTLD+1) from reverse-DNS hostnames, with the TLS certificate CN as a secondary anchor. org is used only to match the infrastructure denylist.

Why it streams

The source is 12.36 GB compressed and ~74 GB decompressed — a 6.03× ratio — on a box with about 64 GB free. It does not fit. zstd -d fails outright, so decompression is incremental and the JSON is never materialised. Memory stays flat regardless of input size.

The IPv6 gap, and why it was left alone

Shodan fills ip_str for IPv4 and ipv6 for IPv6, leaving the other null. The ingest projects ip_str only — so IPv6-only services arrive with no address at all. That is 106,419 records, 1.19% of the source.

There is no "fix the data" option here — Shodan produced it. The only things under our control are which fields we project, and what we assert about them. So the first move was to measure what the gap actually costs:

Three quarters cost nothing: they have no hostname and no certificate CN either, so int_entity_hosts already discards them for lack of any identity. The rest work normally — because entity resolution keys on hostnames, not addresses. A host named mail.acme.com resolves to acme.com whether or not its address was captured.

What nearly went wrong

Overview / Transform & Scoring

Transform & Scoring

Every deterministic decision lives in dbt on DuckDB — grouped by what it decides, not by table. These are set operations over columns; they need to be readable by someone who didn't write them, and tested as part of the build rather than by a suite someone remembers to invoke.

The models staging → intermediate → marts

Four of the 48 tests guard failures that would actually cost something: infrastructure reaching a rep's list, unclassified entities being treated as prospects, honeypots surviving into the entity table, and scores drifting outside their range. The rest are schema hygiene.

Five ways to spot infrastructure, not one list of names

125,037 of 252,078 entities are excluded here. A curated list of provider names cannot reach the long tail of regional hosts — there are thousands of them, and adding the ones visible in a sample would overfit to that sample. So four behavioural heuristics sit alongside the list, each catching a different shape of infrastructure.

Two scores, deliberately not one

Intent — how urgently they need help: exploitation probability, critical CVEs, exposed datastores, remote access, end-of-life software, certificate state, TLS posture. Fit — how well they match the ICP: estate size band, whitespace, real estate, multi-market presence, security maturity.

Every component is kept as its own column, so the weights can be refitted against real outcomes later — and so a rep can disagree with a ranking by seeing what drove it.

Classification is checked before either score matters:

no verdict yet                 →  U - unclassified
verdict is infrastructure      →  X - not a prospect
company, confidence < 0.60     →  R - needs review

then, and only then:
  fit ≥ 50  and  urgency ≥ 50  →  A - call now
  fit ≥ 50  and  urgency < 50  →  B - nurture
  fit < 50  and  urgency ≥ 50  →  C - opportunistic
  otherwise                    →  D - deprioritise

Tier is assigned twice. dbt assigns a provisional one, where everything the rules could not resolve sits in U. The export reassigns it once the model has spoken — the only point R can exist, since it depends on a confidence score that does not exist until then. The shipped artifact has no U rows at all, because all 3,000 queued entities were classified.

Why EPSS, not CVSS or CVE counts

Every CVE in this dataset is version-inferred, never tested. Shodan matches a detected version against advisories; verified was false on 100% of 7,066 sampled CVE entries. So a host reporting 99 CVEs may have every one of them patched.

Both are published scores about the vulnerability, not about this host — so both survive the uncertainty. The difference is what they measure:

• CVSS — how bad it would be if exploited
• EPSS — how likely it is being exploited, right now, in the wild

Identical severity, completely different urgency. A CVSS-ordered list treats them as equivalent. EPSS is a published probability about the vulnerability rather than an inference about this host, so it survives the same uncertainty.

Overview / Classification

Classification

The LLM answers exactly one question: is this entity a real company, or is it infrastructure? Everything else in the pipeline is SQL.

Why a model is here at all the asymmetry

The boundary is set by an asymmetry, not by taste.

So rules settle the provable half, and anything unresolved sits in U - unclassified and cannot reach a rep until the model adjudicates it.

How it runs

Tool-use against a fixed schema — never free text. Three retries with exponential backoff, concurrency 8, resume keyed on the (prompt version, model) pair, and a dry-run estimator that prices the work before spending and reports whether caching will engage.

Every call appends a trace. One real line, unedited:

{
  "task":              "entity_classification",
  "prompt_version":    "v4",
  "model":             "claude-sonnet-5",
  "subject":           "imshealth.com",
  "decision":          "end_customer_company",
  "confidence":        0.92,
  "input_tokens":      511,
  "output_tokens":     168,
  "cached_tokens":     6470,
  "cache_write_tokens": 0,
  "cost_usd":          0.003996,
  "latency_ms":        2422,
  "attempt":           1,
  "error":             null,
  "response": {
    "entity_class":   "end_customer_company",
    "canonical_name": "IQVIA",
    "confidence":     0.92,
    "reasoning":      "Org matches domain (IQVIA Holdings, formerly IMS Health);
                       coherent enterprise stack: OWA, IIS, Azure gateway."
  }
}

Three things that line proves. Caching is working — 6,470 tokens read from cache against 511 fresh, and zero written, so this call reused a prefix someone else's call paid for. The cost is computed, not estimated: 6,470 × $0.20 + 511 × $2.00 + 168 × $10.00 per million is $0.003996, exactly what the line records. And the verdict is auditable — the reasoning names the evidence, so a wrong call can be attributed to the model or to the input rather than guessed at.

One account, and all three thousand

The trace above is one call. Totalled across the shipped run only — v4 on Sonnet 5, the configuration that produced the current artifact:

Every call hit the cache — 3,000 of 3,000, no retries. But the useful figure is 94.2%, the share of input tokens read from cache rather than billed fresh. The missing 5.8% is the per-account JSON, which differs on every call and so can never be cached. That is a structural ceiling, not a tuning target.

At 300,000 accounts the same work is $1,105 on-demand, or about $553 batched — the point where a budget guard stops being optional. Full economics on the Cost Model page.

The four prompt versions

The prompt is the instruction sheet sent with every call. It is identical for all 3,000 — only the entity's data changes. Every version is kept, and every verdict records which one produced it.

Two models, and why the prompt got longer on purpose

Haiku 4.5 is cheap, Sonnet 5 is stronger. Each prompt file names a default model, which the runner can override with --model.

Caching only engages if the unchanging part of the prompt clears a minimum size, and that minimum is different for each model and not in any sensible order:

The floor measures the prefix, not the whole request:

request  =  [ tools schema + system prompt ]  +  [ entity JSON ]
            └──── cacheable prefix ────────┘     └ never cached ┘
            └──────────────── total input ──────────────────────┘

Which is why the same field means different things depending on whether caching worked. Three real trace lines:

In the last column 4,467 > 4,096 is true and irrelevant — most of it is the entity JSON, which was never going to be cached. And the prefix there is never reported: cached_tokens and cache_write_tokens both describe cache activity, so when nothing caches they are zero and no field gives the prefix size. A sweep placed the real boundary between 3,875 (refused) and 4,203 (cached).

Note the cost row: Haiku costs more than Sonnet, paying full price on 4,467 tokens while Sonnet pays a tenth on 6,470 of its 6,981. v4 is v3 plus ~300 tokens of margin, which is why a longer prompt costs 2.5× less.

The funnel is the cost model see Cost Model

Naive — one call per source record         8,914,693 × ~2,000 tok  ≈ $17,800

Designed
  8,914,693 records
  →   252,078 entities        aggregation
  →   127,041 unresolved      rule tier removed 125,037
  →    43,577 queued          signal filter removed 83,464   ≈ $161

The bigger lever is the signal filter, not the rules. An entity with no security findings is not a prospect whether or not it is a real company, so classifying it buys nothing — and that filter is a WHERE clause on an aggregate, not a model call.

Overview / Serving & App

Serving & App

A curated Parquet and a Streamlit app that performs no inference and holds no credentials.

The boundary between pipeline and product

Three things happen in the export and nowhere else. Classification verdicts are merged in, so tiers can finally be assigned. Raw IP addresses are dropped — a rep needs the company, the finding and the reason to call, never an address. And per-call telemetry is joined on, so the app can show the real trace of the decision.

The export filters to exactly one (prompt version, model) configuration: the one the eval measured.

Three questions per account

The evidence distinction

Observed — seen directly in the scan. Tested — confirmed by an active probe. Inferred — implied by a version banner; the software is confirmed, the vulnerability is not. Collapsing those is how a rep ends up asserting a vulnerability that was never confirmed.

Overview / Evals

Evals & Measured Quality

75 hand-labelled entities, two batches, so a prompt tuned on the first is scored on the second. The most useful thing this eval did was prove itself wrong — twice.

Shipped: v4 on Sonnet 5 current

What Precision, Recall and Held-out mean here

Take eight entities, five of them really companies. The model calls four of them companies — three correctly, plus one hosting provider it gets wrong — and misses two companies entirely, returning unknown. Two numbers come out of that, and they count different things:

Precision  look only at what the model CALLED a company
           said "company" 4 times, 3 were right          3/4 = 0.750

Recall     look only at the companies that really exist
           5 exist, it found 3                           3/5 = 0.600

Precision is the quality of the list you hand a salesperson — one name in four is wrong. Recall is how much of the market you found — two real prospects exist that nobody will ever call. A model that says "company" once and is right scores 1.000 precision and 0.200 recall: a perfect list with four prospects missing.

Precision on end_customer_company governs deployment — not accuracy. A false positive puts a hosting provider in a rep's call list; they phone a datacenter about its customers' vulnerabilities and never trust the tool again. A false negative removes a company from the market invisibly — costly, but recoverable.

Held-out — the same number, measured on entities the prompt never saw

The 75 labels were made in two batches, and the order matters:

1. labelled 25 entities
2. ran v3 on them, read which ones it got wrong
3. wrote v4 specifically to fix those failures
4. labelled 50 more
5. scored v4 on all 75

Precision on all 75      0.765   <- includes the 25 it was built from
Precision on the 50      0.727   <- never seen before

Held-out is the 0.727. A student who studies the practice paper will ace those questions; their score on the new ones is the real measure. That is why the lower number is the one quoted, and why the batch is recorded in evals/labelled_set.jsonl rather than merged in — so a later prompt can always be scored on data that postdates it.

The eval was wrong twice, for two different reasons

Both are called reversals, but they are not the same failure:

A bigger eval set fixes the first and does nothing for the second. You can have a perfectly reliable eval and still ship the wrong thing, because a change made for one reason turned out to do something else as well.

Reversal one — same configurations, more examples

Three combinations, scored twice: first on 25 labels, then on 75.

First became last, last became first. Nothing changed except how many examples were measured — so the original ranking was never real. It was noise reported to three decimal places, with a plausible story attached: that Sonnet "commits where Haiku hedges", and decisiveness is the wrong disposition when a wrong commitment reaches a salesperson.

Reversal two — v4 turned out to be more than a cache fix

v3's cacheable prefix sat just under Haiku's 4,096-token floor, so caching silently failed there. v4 is v3 with enough margin to clear it, and it was evaluated on Haiku because Haiku is where the problem was. It worked — cost fell 3.3×.

But clearing the floor meant adding content, and that content was three real changes: scan the organisation list for security-vendor names, a recognisable organisation name outweighs a small estate, and three more worked examples. Those are improvements to the prompt, not padding — so they were worth measuring on Sonnet, the model that had already won on quality. $0.28, and it changed what ships.

Held-out precision across the grid:

                 Haiku 4.5        Sonnet 5
     v2            0.500              ·
     v3            0.385            0.667
     v4            0.467            0.727   <- best of all five

Recall decided it, not precision. The held-out precision gain of 0.060 is less than one row and sits inside the measured noise. Held-out recall moved 0.500 → 0.667 — two more real companies out of twelve — with the same number of false positives, three each. Judging on precision alone would have called it a tie and shipped the worse configuration.

Across all 75 labels the same gap reads larger — recall 0.444 → 0.722, five more companies — but that set includes the 25 entities v4 was tuned against, so the held-out figure is the one quoted. v4 · Sonnet leads both held-out columns and no other configuration leads either, by about two rows each. A real margin, not a wide one.

Both times the recommendation changed, production was re-run rather than leaving a conclusion the evidence no longer supported.

The noise floor underneath all of it

The same 3,000 entities were classified twice under an identical configuration — same prompt version, same model, nothing changed between the runs. Why that happened, and what it cost, is on the Cost Model page. Any difference between the two is therefore the model disagreeing with itself, and it can be counted:

Overview / Cost Model

Cost Model

Measured from traces, not derived from assumed token counts.

The rates, so every figure here can be checked

(6,470 cached × $0.20  +  400 fresh × $2.00  +  159 out × $10.00) / 1e6  =  $0.00368

That reconciles with the traces to five decimal places. Note where the money goes: output is 43% of per-call cost on 159 tokens, while 6,470 cached input tokens are 35%. That is why the reasoning field is capped at twenty words.

Per full refresh

Caching, not model choice, is the dominant term. The shipped Sonnet configuration is cheaper per call than uncached Haiku, despite triple the list price.

What it actually cost: $34.46

Every figure above is a projection for a full 43,577-entity refresh, which was never run. The experiment classified 3,000 entities, three times. Summed from traces/entity_classification.jsonl and traces/eval.jsonl:

pilots, 25 entities each    v1 / v2 / v3 Haiku, v3 Sonnet      $0.39
v3 Sonnet, 2,975 entities   first production run              $10.49
v3 Sonnet, 3,000 + 837      the same work, billed again       $10.69
v4 Sonnet, 3,000 entities   shipped configuration             $11.05
evals, 5 configurations     75 labels each                     $1.84
                                                             ------
                                                             $34.46

Nearly a third of the total classified entities that were already classified. The duplicate run cost almost exactly what the shipped run cost — $10.69 against $11.05 — which is what a broken resume key buys you.

Batching would have halved every line above — about $16 — which is the honest size of that gap, not the $81 a reader might infer from the projection table.

Caching failed silently, and four diagnoses all failed the same way

The minimum cacheable prefix is model-dependent and non-monotonic — Haiku 4.5 needs 4,096 tokens where Opus 5 needs 512. Below the floor nothing caches, the request still succeeds, and the cache fields just return zero.

Two compounding errors. The floor applies to the prefix rather than the whole request — the anatomy is on the Classification page. And every attempt derived the prefix instead of observing it: from chars-per-token, then by subtracting an estimated message size from an observed total, then from a purpose-built script that did the same subtraction in tokens. That script over-reported by ~315 tokens — enough to report v3's prefix as 86 above a floor it was ~230 below.

Overview / Known Limitations

Known Limitations

Eight limitations change how you should read the output. After them, the same pipeline checked against the standard LLM failure modes. The full list is in the repo, organised by layer — 36 live entries, plus bugs already fixed, kept because the same kind of mistake can happen again.

Audited against the standard LLM failure modes

This is not an agent. It is a stateless single-turn classifier — one request per entity, no conversation, no tool-selection loop — so three of these cannot occur by construction. Claiming coverage there would be false.

Cost explosion is the one real gap. --dry-run prices the work before a single call is made, which is what makes a token budget a decision rather than a discovery — but nothing checks accumulated spend while a run is in flight. The one time a run needed halting, the provider's credit limit did it rather than the pipeline. What that costs at ten times the volume is on the Scaling page.

The two marked Full are the two that paid off. Schema-forced output caught a class the enum alone would have let through, and per-call tracing is why the 4.5% self-disagreement rate below could be measured at all rather than assumed.

The pattern worth naming

What I would do differently

Overview / Scaling to 10×

Scaling to 10×

89 million records, ~740 GB decompressed. Two layers already hold, two need restructuring, one changes character entirely — and the real bottleneck is accuracy, not compute.

The things that get worse faster than linearly