Modelling a corpus for two readers

This series documents an automated intelligence platform built over operational-robotics data between April and July 2026, and wound down to a static directory. Part 1 covers the data model.

The corpus had two consumers: people reading a web page, and language models reading the same page or the bulk export. Conventionally these are separate artifacts — a normalized database, and a lossy prose rendering on top. The model below collapses them.

The canonical schema

The published graph is 25 base tables and 2 views in a schema named directory. Every entity is UUID-keyed, uniformly, so the polymorphic tag and edge tables need no per-type key handling.

companies is the hub. As introspected from information_schema.columns, 33 columns:

CREATE TABLE directory.companies (
  id                uuid        NOT NULL DEFAULT gen_random_uuid(),  -- PK
  slug              text        NOT NULL,                            -- UNIQUE
  name              text        NOT NULL,
  tagline           text,
  description       text,
  website           text,
  linkedin          text,
  x_handle          text,
  hq_location       text,
  country           text,
  country_iso       text,
  region            text,
  hq_latitude       double precision,
  hq_longitude      double precision,
  founded           smallint,
  employee_range    text,
  ownership_type    text,
  ticker            text,
  exchange          text,
  funding_total     bigint,
  latest_round      text,
  logo_url          text,
  hero_image_url    text,
  aliases           text[],
  data_completeness integer,
  provenance        jsonb,
  published         boolean     NOT NULL DEFAULT false,
  search_vector     tsvector,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now(),
  completeness_pct  smallint,
  last_verified_at  timestamptz,
  freshness_pct     smallint
);

The last three columns were added after the table was created, by a later migration. There is no sector column, no segment column, no region taxonomy column: those are facet edges, covered below. There is no contact column, no lifecycle column, no pipeline stage. The 24th column, aliases, belongs to entity resolution.

The other entity tables are narrower and share the same tail — provenance jsonb, published boolean, created_at, updated_at, and for the scored types completeness_pct, last_verified_at, freshness_pct:

table	columns	own fields
`products`	18	`company_id`, `slug`, `name`, `description`, `url`, `launch_year`, `key_specs`, `deployment_status`, `operating_environment`, `search_vector`
`systems`	17	`slug`, `canonical_name`, `system_class`, `system_role`, `is_class`, `manufacturer_company_id`, `product_id`, `origin_country_iso`, `description`
`deals`	17	`company_id`, `deal_type`, `title`, `date`, `value`, `value_num`, `value_confidence`, `funding_stage`, `region`, `buyer_investor`, `source_url`, `signal_id`
`people`	15	`slug`, `name`, `title`, `bio`, `linkedin`, `x_handle`, `company_id`

deals carries both value text and value_num numeric. The raw string is the observation; the parsed number is the derived value; value_confidence records how far to trust the parse. The capture-raw discipline of entity resolution applied to a scalar.

Twenty-five foreign keys hold the graph together. Read from pg_constraint:

directory.business_events    → companies(id) ON DELETE SET NULL   (primary_company_id)
directory.company_activity   → companies(id) ON DELETE CASCADE
directory.company_cut_completeness → companies(id) ON DELETE CASCADE
directory.company_depth      → companies(id) ON DELETE CASCADE
directory.competitors        → companies(id) ON DELETE CASCADE    (company_id)
directory.competitors        → companies(id) ON DELETE CASCADE    (competitor_id)
directory.component_suppliers→ companies(id) ON DELETE CASCADE
directory.component_suppliers→ components(id) ON DELETE CASCADE
directory.components         → companies(id) ON DELETE SET NULL   (manufacturer_company_id)
directory.deals              → companies(id) ON DELETE CASCADE
directory.entity_facets      → facet_values(id) ON DELETE CASCADE
directory.facet_values       → facet_dimensions(id) ON DELETE CASCADE
directory.facet_values       → facet_values(id) ON DELETE SET NULL (parent_id)
directory.landing_pages      → sites(id) ON DELETE CASCADE
directory.people             → companies(id) ON DELETE SET NULL
directory.people_companies   → companies(id) ON DELETE CASCADE
directory.people_companies   → people(id) ON DELETE CASCADE
directory.products           → companies(id) ON DELETE SET NULL
directory.site_deployments   → companies(id) ON DELETE SET NULL
directory.site_deployments   → products(id) ON DELETE SET NULL
directory.site_deployments   → deployment_sites(id) ON DELETE SET NULL
directory.site_filters       → facet_values(id) ON DELETE CASCADE
directory.site_filters       → sites(id) ON DELETE CASCADE
directory.systems            → companies(id) ON DELETE SET NULL   (manufacturer_company_id)
directory.systems            → products(id) ON DELETE SET NULL

The delete rules split along one line. CASCADE is used where the child row has no meaning without its parent — a facet edge, a junction row, a materialized score. SET NULL is used where the child is an entity in its own right and the link is an assertion about it: a product survives losing its manufacturer link, and the surviving row records an unknown maker rather than disappearing. There is no ON DELETE RESTRICT anywhere in the schema.

entity_facets carries no foreign key on entity_id at all — it is polymorphic across eight entity types, and the type is constrained instead:

CHECK (entity_type = ANY (ARRAY['company','product','system','person','component',
                                'deployment_site','regulation','association']))
PRIMARY KEY (entity_type, entity_id, facet_value_id)

Row counts at the last export:

table	rows
`entity_facets`	46,985
`deployment_sites`	31,683
`products`	7,077
`deals`	5,143
`people`	4,911
`people_companies`	4,616
`companies`	1,757
`research_reports`	1,673
`company_cut_completeness`	1,180
`systems`	498
`business_events`	187
`components`	88
`landing_pages`	50
`competitors`	49
`facet_dimensions`	17
`sites`	11

Three of the 1,757 company rows are evaluation fixtures with slugs prefixed l1eval-; the exporter excludes them with AND slug NOT LIKE 'l1eval-%', which is why the shipped snapshot holds 1,754.

CREATE TABLE directory.entity_facets (
  entity_type     text  NOT NULL,   -- 'company', 'product', 'system', …
  entity_id       uuid  NOT NULL,
  facet_value_id  uuid  NOT NULL,
  provenance      jsonb             -- why this tag was applied
);

The facet join is four columns. Provenance sits on the edge, not only on the node, so the reason a company was classified into a sector is as traceable as the sector itself.

The corpus served several verticals. The conventional approach is a tenant column or a dataset per vertical. This model uses a single shared graph with many-to-many facet tagging, and dimensions are themselves rows, carrying two flags that control behaviour downstream:

dimension	values	hierarchical	indexable
`capability`	107	yes	no
`platform_category`	22	yes	yes
`funding_source`	15	no	yes
`sector`	12	yes	yes
`asset_class`	12	no	yes
`use_case`	11	no	yes
`grant_program`	10	no	yes
`end_market`	10	no	yes
`inspection_modality`	9	no	yes
`psr_discipline`	8	no	yes
`geography`	7	yes	yes
`supply_chain_role`	6	no	no
`business_model`	5	no	yes
`cuas_function`	4	no	yes
`deployment_form`	4	no	yes
`owner_type`	3	no	yes
`cuas_legal_class`	2	no	yes

Seventeen dimensions, 247 defined values, 23,808 edges attached to published companies. 179 of the 247 values carry at least one published company; the remainder were defined by the taxonomy and never populated.

indexable marks a dimension as eligible for its own browse pages. hierarchical marks one whose values form a tree, where a match on a child implies the parent. Both are data, so the set of generated pages is bounded by a query rather than by a hard-coded list — the static build reads WHERE indexable = 1 to decide what to emit.

Companies are not the only tagged type. Of the 46,985 facet edges in the graph, 23,808 point at companies, 22,661 at products, and 516 at systems. A product carries its own platform category and capability tags independent of its maker’s.

A site is a row

The tenancy the facets replaced was expressed instead as two tables. sites holds a subdomain, a name, an entity type, and a presentation blob; site_filters holds the facet values that constrain it. Membership is defined once, in SQL, and is the same for every site:

an entity qualifies if, for every dimension the site constrains, the entity carries at least one of the site’s values in that dimension

AND across dimensions, OR within one. No filters means everything. The resolution is a doubly-nested NOT EXISTS: there must be no constrained dimension for which the entity has no matching value.

Eleven site rows exist; four are published — asset-inspection, drone-detection, public-safety-robots, security-robots — and seven were configured and left unpublished (warehouse-robots, field-robots, cleaning-robots, grounds-robots, jobsite-robots, mine-haulage, yard-logistics). Adding one required a row and its filters, not a deployment. Fifty landing_pages rows sit beneath them, each pinning a specific facet combination to a URL.

A new vertical is a configuration row plus a saved query. There is one copy of every fact, and the same edges serve a person browsing by sector and a model filtering by capability.

The admission rule that keeps this tractable:

Scope-time discipline; entities are stable nouns. Gatekeep what earns a row or a type at scope time, not by filtering a mixed population at query time.

Assertions carry provenance, not truth

A field value is stored as an observation with an origin and a timestamp, not as a current fact. companies.provenance is a JSONB column with this shape:

{
  "first_seen": "2026-03-11T01:51:09.430505+00:00",
  "last_seen":  "2026-07-23T23:46:33.052073+00:00",
  "sources": [],
  "verification": {
    "method":                 "jina_site_and_source_identity_v1",
    "verified_at":            "2026-07-23T23:46:33.052073+00:00",
    "website_url":            "https://ascentaerosystems.com/",
    "supporting_source_url":  "https://ascentaerosystems.com/",
    "cut":                    "security-robots",
    "backlog_ref":            1180
  }
}

method is a versioned identifier for the extraction path. backlog_ref points at the work item that produced the verification, which makes the provenance chain traversable back into the operation’s own records.

The governing rule and its three dependents were recorded as tenets:

Provenance, not verification. We stand behind the provenance and confidence of every assertion, not the current truth of a value.

Provenance is born at collection. Carried through, never reconstructed at publish time.

An output is a projection. Check the lineage, not the output. Every artifact inherits the lineage of its data.

Justify, not prove. Confidence by source tier + corroboration (HIGH/MEDIUM/LOW/UNVERIFIED). The auditor checks that the lineage chain holds, not whether the fact is currently true.

The operational form of the third is a gate: click/event → agent run → search query → source URL. An assertion whose chain breaks at any link does not ship.

Manual corrections are provenance too

def _correction_entry(field, from_val, to_val, reason, session):
    return {
        "kind":    "manual_correction",
        "field":   field,
        "from":    from_val,
        "to":      to_val,
        "reason":  reason,
        "session": session,
    }

engine/data/queries/corrections.py is a data-layer module whose functions append that record to provenance.corrections[] on every write. QA sweeps surface facts no automated resolver will fix — a wrong country of origin, a duplicate entity. The two otherwise available responses were an ad-hoc UPDATE, which the canonical-write invariant forbids, or a throwaway script.

The correctable field set is declared, not inferred — for systems it is {"origin_country_iso", "canonical_name", "description"}, and a call naming any other column raises rather than silently no-ops. Neither function commits; the caller owns the transaction, so the same code path runs as a rolled-back dry run or an applied change.

Three of the 523 rows in drone_systems carry a corrections array. Each records what changed, from what, to what, why, and in which session. The largest is a merge, and it records the direction of the merge and the direction of the fact separately:

{
  "source": "llm",
  "triggering_alias": "akinci",
  "corrections": [
    { "kind": "manual_correction", "field": "merge",
      "from": "4668e120-82a7-4514-ad29-1c563b786459",
      "to":   "2eb2fabf-53c5-459b-b6cc-13246cbb6928",
      "reason": "QA #1081: duplicate Bayraktar Akıncı (diacritic row, mangled slug)",
      "session": 369 },
    { "kind": "manual_correction", "field": "canonical_name",
      "from": "Bayraktar Akinci", "to": "Bayraktar Akıncı",
      "reason": "QA #1081: correct spelling from merged row; Baykar is Turkish",
      "session": 369 },
    { "kind": "manual_correction", "field": "origin_country_iso",
      "from": null, "to": "TR",
      "reason": "QA #1081: correct spelling from merged row; Baykar is Turkish",
      "session": 369 }
  ]
}

Bayraktar Akıncı existed twice: 4668e120-82a7-4514-ad29-1c563b786459, carrying the correct diacritics but a mangled slug, and 2eb2fabf-53c5-459b-b6cc-13246cbb6928, carrying the ASCII spelling, a clean slug, and a NULL country of origin. Neither normalization nor the alias table caught it, because the surviving row’s own alias — triggering_alias: "akinci" — was already the normalized form of both. The row that survived is the one with the better identifiers; the facts that survived came from the row that was deleted. Two event_systems rows and two system_aliases rows were re-pointed at the survivor, with zero key collisions, before the duplicate was removed. A collision-safe merge_drone_systems utility, FK-census guarded, came out of the same session.

The other two rows carrying a corrections array were fixed in the same sweep: FK-2000 SHORAD had origin_country_iso corrected from TR to CN (“Norinco FK-2000 SHORAD is Chinese, not Turkish”), and CH-95 had it set from NULL to CN.

At teardown the warfare layer, the editorial layer and the commercial model were deleted; the remaining rows required no migration, because none of them asserted a current value.

Coverage as axes

Coverage is a weighted presence count, per entity type, generated into a set-based UPDATE. For companies the weight map is:

"companies": {
    "cols": [("name", 3), ("description", 3), ("website", 2), ("hq_location", 1),
             ("country", 1), ("founded", 1), ("tagline", 1), ("logo_url", 1),
             ("linkedin", 1)],
    "exists": [("facet", 2), ("person", 1), ("product", 1)],
},

Fourteen points of column weight, four points of linked-entity weight, denominator 18. The compiled predicate is:

LEAST(100, round(100.0 * (
    (3 * (t.name IS NOT NULL)::int) + (3 * (t.description IS NOT NULL)::int) + …
  + (2 * (EXISTS(SELECT 1 FROM directory.entity_facets ef
                 WHERE ef.entity_type='company' AND ef.entity_id=t.id))::int)
  + (1 * (EXISTS(SELECT 1 FROM directory.people_companies pc
                 WHERE pc.company_id=t.id))::int)
  + (1 * (EXISTS(SELECT 1 FROM directory.products p
                 WHERE p.company_id=t.id))::int)
) / 18))::smallint

The exists terms are what separate this from a field-fill count: a company with two points of facet tagging and a linked product scores higher than one with a longer description and no edges. The predecessor metric was filled fields over schema fields, which rewarded cheap fields and could not distinguish a company with a documented funding history from one with a long description.

Across the 1,757 published companies: minimum 17, median 61, mean 61.9, maximum 100.

Freshness is a step function, not a curve. last_verified_at is taken as the later of the row’s updated_at and its provenance last_seen, with a regex guard so a malformed timestamp falls back rather than raising:

last_verified_at = GREATEST(t.updated_at,
  CASE WHEN t.provenance ? 'last_seen'
        AND (t.provenance->>'last_seen') ~ '^\d{4}-\d\d-\d\d'
       THEN (t.provenance->>'last_seen')::timestamptz
       ELSE t.updated_at END)

freshness_pct = CASE
  WHEN last_verified_at IS NULL                              THEN   0
  WHEN last_verified_at > now() - interval  '30 days'        THEN 100
  WHEN last_verified_at > now() - interval  '90 days'        THEN  85
  WHEN last_verified_at > now() - interval '180 days'        THEN  70
  WHEN last_verified_at > now() - interval '365 days'        THEN  50
  WHEN last_verified_at > now() - interval '730 days'        THEN  30
  ELSE                                                              15
END

The floor is 15, not 0. A never-verified row scores 0; a two-year-old verified row scores 15. The distinction is between no observation and an old observation, and the scale refuses to collapse them. At the last recompute the published companies sat in three bands: 398 at 100, 1,298 at 85, 61 at 70. Nothing below 70, because the July backfill had touched every row.

The axes as materialized

The single number was replaced with a profile, materialized into directory.company_depth — one row per company, six axes plus the raw counts each was computed from.

axis	question	computed from	target
`identity_depth`	who they are	10 weighted `companies` columns	all present (weight 15)
`classification_depth`	what they do	distinct facet dimensions tagged	4 dimensions
`connections_depth`	who they work with	products, people, competitors, deals	2 / 3 / 3 / 5
`magnitude_depth`	how much they do	deal value, deals, signals, deployment	see below
`evidence_depth`	what backs it	`provenance.sources` + `deals.source_url`	≥ 5 distinct
`analysis_depth`	what was written	current report, free summary	both

Each axis is 0–100 against a deliberate target, not against a theoretical maximum. connections_depth is a weighted blend of four capped counts — 0.30 · min(products,2)/2 + 0.20 · min(people,3)/3 + 0.20 · min(competitors,3)/3 + 0.30 · min(deals,5)/5 — so the third product adds nothing and the corpus is not ranked by row count. magnitude_depth takes deal value logarithmically, 100 · ln(max(total,1)) / ln(50,000,000), capped at 100 and weighted 0.40, so the difference between a $2M company and a $20M company registers and the difference between $200M and $2B compresses.

The overall score uses four of the six. The excluded two are named in a versioned constant:

DEPTH_WEIGHTS_VERSION = "v2-20-30-25-25"
DEPTH_OVERALL_WEIGHTS = {
    "identity_depth":       0.20,
    "classification_depth": 0.30,
    "connections_depth":    0.25,
    "magnitude_depth":      0.25,
}

evidence_depth and analysis_depth are materialized and reported but kept out of the headline number, because both move with editorial effort rather than with what is known about the company — a research report raises analysis_depth by 50 points without adding a fact.

Corpus means across all 1,757 companies:

axis	mean
`analysis_depth`	92.2
`classification_depth`	78.5
`identity_depth`	57.3
`connections_depth`	47.1
`magnitude_depth`	44.9
`evidence_depth`	44.8
`depth_overall`	58.0

Classification is the strongest axis because facets were cheap to produce and were the backfill’s main output. Analysis is the highest because a report was generated for nearly every company. Evidence is among the lowest, and it is the axis the product was sold on.

Completeness against a gold schema, per cut

directory.company_cut_completeness scores each company against the gold schema of the cut it belongs to — a weighted requirement list, versioned gold-v1-1125, with four requirement kinds: col (a column is present), col_any (any of a set), facet_dim (at least one tag in a dimension), facet_val (at least one tag from a curated value subset), and evidence (a named EXISTS over the fact tables). A requirement whose dimension has not been seeded evaluates false, so the gap shows up as a low score rather than as a special case.

Each cut also declares must_have requirements and a launch_bar:

cut	scored	mean `gold_pct`	bar	`launch_ready`
`security-robots`	531	63.2	70	43
`drone-detection`	307	72.7	68	124
`asset-inspection`	260	71.9	68	50
`public-safety-robots`	82	60.3	65	5

The generic target scores every company against the same fields regardless of what a reader is trying to decide. A buyer shortlisting counter-drone systems needs to know whether a product is detection-only or mitigation-capable, which the generic profile does not ask about. The discriminators live in the facet layer, never in a new JSONB column — the 2026-07-18 decision that produced this table also added eight facet dimensions (business_model, cuas_legal_class, deployment_form, asset_class, inspection_modality, psr_discipline, grant_program, owner_type), taking the schema from 6 dimensions to 14. The missing column on each row is a JSONB array naming exactly which requirements failed.

Two cross-cutting layers sit alongside: evidence (provenance.sources non-empty) and freshness (freshness_pct, decaying from 100 at ≤30 days to 15 beyond two years, recomputed by a scheduled job).

Measured coverage at export: products on 75% of companies, people on 71%, deals on 78%, signals on 92%. Counterparties, deployments and sources were empty corpus-wide; competitors covered 49 companies of 1,754.

The profile renders inline on each entity, including where it is thin.

A re-verification worker moves last_verified_at rather than letting it decay: fetch the company website through the existing extraction path, require the extracted page to identify the company, require one provenance source to still identify it, and only then stamp. No model call, $0.00 reserved per company, $0.50 cap per run, oldest-first within each cut, tightest freshness target first. Its first live pass attempted 23 companies, verified 1, and failed 22, almost entirely on missing_or_unsafe_website — the stalest members of each cut were the ones with no website field to fetch. Freshness recovery was gated on backfilling an identity column, not on verification throughput. Part 2 carries the worker’s selection query and its measured run history.

The free/paid split, as roles and policies

CREATE VIEW directory.reports_public AS
  SELECT id, subject_type, subject_id, title, report_type, report_date,
         summary, source_count, word_count, is_current
  FROM directory.research_reports
  WHERE is_current;

GRANT SELECT ON directory.reports_public   TO anon, authenticated;  -- summary: free
GRANT SELECT ON directory.research_reports TO authenticated;        -- body: paid

body is absent from the view’s select list, so anon has no path to it that does not go through a grant it does not hold. regulations uses the identical pattern with regulations_public. This is column-level free/paid without column-level security.

The commercial line was facts free, understanding paid. Row-level gating would have hidden entities from search engines, which were the acquisition channel. Column-level security would have put the paywall inside the table definition, where a schema change could silently open it. The split landed on the table boundary instead, with one indirection: the paid text stays in the base table, and a view exposes everything except it.

Everything else is a policy. All 25 tables have RLS enabled, and 50 policies are live across them — two per table, in three shapes:

policy	tables	role	predicate
`pub_read`	entity tables carrying `published`	`public`	`USING (published)`
`all_read`	taxonomy, edges, junctions, materialized scores	`public`	`USING (true)`
`paid_read`	`research_reports`, `regulations`	`authenticated`	`USING (true)` / `USING (published)`
`writer_all`	all 25	`graph_writer`	`USING (true) WITH CHECK (true)`

The pub_read/all_read division is the free/paid line drawn a second way. Facet edges, dimension rows, junction rows and the materialized score tables are readable unconditionally, because they carry no assertion of their own — an entity_facets row is meaningless without the entity, and the entity is behind published. Gating them would have added a join to every query for no protection.

Reads for the public site went through PostgREST against four functions rather than table selects — get_company_dossier(slug), get_company_module(company_id, module, cursor), search_companies(site, filters, query, sort, cursor), and get_fact(fact_id) — with the 1,000-row PostgREST response cap handled by the cursor argument. Bulk metering was specified to live at that API layer and was never built; the corpus shipped as a single unmetered JSON file instead.

The writer role could not write

Read live from pg_roles:

role	`rolbypassrls`
`postgres`	true
`service_role`	true
`anon`	false
`authenticated`	false
`graph_writer`	false

writer_all exists because of a failure found in June, during an attempted cutover of the engine from the postgres superuser to a least-privilege graph_writer role. The migration granted graph_writer INSERT, UPDATE, DELETE and SELECT on all 62 canonical base tables, revoked anon and authenticated writes on the same 62, and hardened default privileges. All of that verified. The smoke test — rolled-back INSERTs as the new role — then failed on every table with new row violates row-level security policy.

The canonical tables were rls=true, force=false with zero policies: pure deny-all. The engine had been working because postgres carries the BYPASSRLS attribute. Grants are necessary but not sufficient when RLS is on. The spike design had documented the deny-all RLS and had specified the grants, and had never reconciled the two; the result was a role provisioned correctly against its own specification and unable to write a single row.

Two repairs were available. ALTER ROLE graph_writer BYPASSRLS is one line and reversible, and makes the least-privilege role functionally as trusted as postgres for row security. Sixty-two permissive policies keep RLS in force and cost 62 policies. The cutover was paused rather than decided, the engine stayed on the postgres path, and the Doppler connection string for the writer role was removed so that no restart could re-introduce it. The dangling graph_writer_app login role was dropped.

The directory schema, designed a month later, took the second repair by construction: writer_all is created in the same DO loop that enables RLS on each table, with a comment naming the finding. The rule the episode produced was narrower than the design question, and was written down: the smoke test runs as the role, before cutover, not after.

Entity resolution, four steps

Resolution was implemented once and applied to every entity type:

Capture raw. The source string is stored unmodified. Normalization happens downstream; the raw value remains as evidence.
Deterministic match. Exact, then normalized (case-folded, punctuation-stripped, suffix-normalized for Inc/Ltd/GmbH), then alias-table lookup.
Model fallback on misses only. Invoked when steps 1–2 return nothing.
Provenance on the resolution edge, recording which step produced the match.

The tenet:

Entity resolution is one pattern. Capture raw; deterministic-first; LLM-fallback only on misses; every edge provenance-tagged.

One pattern in many instances (systems, actors, manufacturers): normalize, deterministic pass (exact/prefix/regex), then call_llm_json fallback only on misses. Free-text capture columns are never destroyed; the join carries the resolved truth. Coverage measured on mentions; accuracy on a hand-labeled stratified sample. Every fusion edge records resolution_method + resolution_confidence.

Step 2 is thirteen lines. It case-folds, strips a fixed list of 21 corporate-suffix forms from the end of the string, deletes every non-word non-space character, and collapses runs of whitespace:

def _normalize_name(name: str) -> str:
    """Normalize a company name for comparison."""
    name = name.lower().strip()
    for suffix in [
        ", inc.", ", inc", " inc.", " inc", ", llc", " llc",
        ", ltd.", ", ltd", " ltd.", " ltd", ", corp.", ", corp",
        " corp.", " corporation", ", co.", " co.", " gmbh",
        ", s.a.", " s.a.", " plc", " ag", " pty",
    ]:
        if name.endswith(suffix):
            name = name[: -len(suffix)].strip()
    name = re.sub(r"[^\w\s]", "", name)
    name = re.sub(r"\s+", " ", name)
    return name

The suffixes are anchored to the end of the string, so “Corporation Street Robotics” is untouched. Only one suffix is stripped per call, so “Something Ltd. Inc.” normalizes to “something ltd.” and then to “something ltd” — order-dependent and deliberately shallow. Punctuation removal runs after suffix stripping, so the comma-and-period variants have to be enumerated rather than falling out of a general rule. The same function is duplicated verbatim in the listener’s matcher (engine/listen/matcher.py), where it is annotated "Same logic as engine." — a copy, not an import, and a real defect surface.

Near-matching is a separate function with an explicit threshold that tightens on short strings, because a 0.90 similarity ratio over five characters is noise:

def _fuzzy_match(name, existing_names, threshold=0.90):
    normalized = _normalize_name(name)
    for existing in existing_names:
        existing_norm = _normalize_name(existing)
        if normalized == existing_norm:
            return existing
        ratio = SequenceMatcher(None, normalized, existing_norm).ratio()
        min_len = min(len(normalized), len(existing_norm))
        effective_threshold = threshold if min_len > 15 else 0.93
        if ratio >= effective_threshold:
            return existing
    return None

The alias tables

Aliases are stored in two places, for two different jobs.

directory.companies.aliases is a text[] on the entity itself — the names a company is also known by. It is populated on 73 of 1,757 rows, concentrated where naming is genuinely contested: state enterprises, design bureaus, and sanctioned entities whose legal names differ from their trading names across transliterations.

United Aircraft Corporation  ['Mikoyan', 'Sukhoi', 'UAC', 'United Aircraft Corporation']
MKB Raduga                   ['MKB Raduga', 'Raduga', 'Raduga Design Bureau',
                              'Dubna Machine-Building Plant']
HESA                         ['HESA', 'Iran Aircraft Manufacturing Company',
                              'Iran Aircraft Manufacturing Industrial Company',
                              'Iran Aircraft Manufacturing Industries Corporation']

public.system_aliases is a separate table, because system naming is many-to-one at a scale an array column cannot carry:

CREATE TABLE public.system_aliases (
  id              uuid,
  alias_norm      text,        -- the normalized surface form
  drone_system_id uuid,        -- FK to the resolved system
  match_kind      text,        -- 'exact'
  match_pattern   text,        -- the pattern that produced the match
  source          text,        -- 'seed' | 'llm'
  created_at      timestamptz,
  updated_at      timestamptz
);

2,972 rows. source='seed' on 14 of them — hand-written anchors — and source='llm' on the other 2,958. Every row is match_kind='exact'; the prefix and regex kinds the design allowed for were never populated, so in practice the deterministic pass ran as exact-match-on-normalized-form only.

The distribution is heavily skewed. The five systems holding the most aliases:

system	aliases
Drone	505
Air defense system	198
Loitering munition	160
FPV drone	158
Shahed-136 / Geran-2 family	142

The top four are class nodes, not models — drone_systems.is_class = true. Roughly a thousand of the alias rows resolve generic source language (“uas”, “russian uav”, “missile systems”) onto a taxonomy class rather than onto a specific platform: the alias table absorbing vagueness at the edge so the graph’s specific nodes stay specific.

Where the model runs, and where it is refused

The fallback resolver (engine/handlers/resolution_fallback.py, 936 lines) opens with its own scope limit: “The deterministic pass owns exact alias matches. This module only operates on misses.” It runs anthropic/claude-haiku-4.5 through call_llm_json, which writes a trace row per call, and discards any resolution below MIN_CONFIDENCE = 0.72. Its output classes are closed sets — fourteen SYSTEM_CLASSES, three SYSTEM_ROLES, seven ACTOR_TYPES — so a model answer that is not already a member of the vocabulary is not a new vocabulary entry.

Step 3’s ordering is a cost control, stated as a tenet:

Deterministic-first; LLM only the gap. Never spend an LLM call on a fact the deterministic path already produced.

Measured effect: average cost per model call fell from $0.0174 to $0.0061 between May and June while call volume rose 11%. Part 5 has the breakdown.

The deterministic grooming pass that runs alongside it, engine/handlers/entity_resolution_groom.py, is more restrictive than the pattern requires, and the restriction was derived from a measurement. Its first pass backfills people.company_id only when a person has exactly one people_companies junction edge — copying an edge that already exists, so confidence is 1.0 by construction. People with several junction companies are stamped as attempted and left NULL. Against the headline that 82% of people were unlinked, 3,717 people already carried a junction edge that had never been copied to the denormalized column; the pass moved linked people from 848 to 4,545 of 4,766, at zero model cost.

Its second pass, system to manufacturer, is gated to a hand-curated allowlist of two company slugs — zala-aero and fire-point. A read-only audit dated 2026-06-12 tested generic company-name-token matching against system names and found 2 of 13 matches genuine; the other 11 were product-line names, ship classes, and cases where a system name collides with an unrelated company’s name (“Project 22800 Karakurt” onto KARAKURT the ship class; “Sting interceptor drone” onto a company named STING). Token length is floored at MIN_COMPANY_TOKEN_LEN = 4 for the same reason. The allowlist is the extension point: a slug is added after a manufacturer is confirmed, never by sweeping.

Every resolution carries the method that produced it, as a literal string — people_companies_denorm_backfill_v1, system_name_company_token_allowlist_v1 — so a later reader can tell a copied edge from a matched one.

From a drone schema to a neutral one

The new schema was built beside the old one, not on top of it. directory was created as a parallel schema by three migrations applied on the same day — 20260713150000_directory_schema_1083, 20260713150100_directory_facet_taxonomy_1085, 20260713150200_directory_entity_scores_1082 — with matching _down files. Nothing in public was touched. The DDL had been reviewed as a standalone artifact five days before it was applied, and carried the note “reviewed, NOT applied” in its header.

The corpus began as a drone-warfare graph with attack_events, conflict modelling and CARVER exposure scoring. The legacy schema held the directory; the directory was buried under three kinds of contamination, and the migration was mostly an act of shedding.

Warfare columns. public.deployment_sites had 62 columns. Twenty-six of them were targeting apparatus: nine carver_* (criticality, accessibility, recuperability, vulnerability, effect, recognizability, plus a rationale, a robotics-relevance score and a composite), ten dres_* (air, ground, surface, subsurface, hardening, criticality, accessibility, severity, target profile, composite), and threat_profile, conflict_zone, known_cuas, cisa_sector, ncf_functions, feature_vector, attack_count. The neutral table has 21 columns: what, where, who, and a PostGIS point.

CRM columns. public.companies had 58. Dropped by omission: contact_email, contact_emails, contact_found_at, contact_source, contact_status, do_not_contact, do_not_contact_at, do_not_contact_reason, do_not_contact_source, pipeline_stage, coverage_priority, intelligence_rating, beat, and the interaction counters. The public directory has no use for a sales pipeline, and a column that exists is a column that leaks.

Hardcoded verticals. drone_systems became systems, on the principle that drone is one value of platform_category, not a table. Five classifier columns stopped being columns and became facet edges: companies.sector, .sub_sector, .segments, .operating_regions, .technologies, plus products.platform_type and drone_systems.system_class. The company_capabilities and product_capabilities join tables collapsed into entity_facets against a capability dimension.

The backfill was a separate step, run by the operator rather than by the migration runner, and written to run through the engine’s data layer. Its default mode is a dry run: the entire backfill executes inside one transaction and rolls back, printing statistics.

Keys were preserved rather than remapped. Every UUID-keyed legacy table kept its primary key in the new schema, which left every cross-table foreign key valid with no translation table. One table did not have a UUID: deployment_sites was bigint. It got a deterministic derivation rather than a sequence, so re-running the backfill produces the same identifiers:

md5('directory-dsite:' || legacy_id)::uuid

Unmapped classifier values were not auto-created. A legacy supply-chain-intermediary segment, or a missile system_class with no home in the new vocabulary, was skipped and reported as a coverage gap — an input to a controlled-vocabulary decision, not a silently invented facet value.

The first backfill landed 1,676 companies and 46,641 facet edges against a taxonomy of 5 dimensions and 133 values, of which 86 were the existing capability taxonomy imported with its parent chain intact. The taxonomy grew afterwards — to 14 dimensions with the launch-cut discriminators, and to 17 by the last export.

The research-report migration was the one place a model ran during the backfill. Legacy reports had a content column and no summary; the free/paid split needed one. Summaries were generated from bodies through the logging LLM wrapper — never a raw provider SDK, per the no-untracked-ai-work tenet — bounded by a --summary-sample flag, with the unbounded run left as an operator decision.

The exercise in schema terms: public.companies 58 columns to directory.companies 33; public.deployment_sites 62 to 21; 523 rows of drone_systems to 498 rows of systems; and the classifier columns that had been the hardcoded vertical redistributed across 46,985 facet edges, where a second vertical could reach them.

Vertical logic in the product layer, never the foundation.

Next: Part 2 — Pipeline.