FollowTheMoney projections

Axiom Overwatch stores a FollowTheMoney (FTM) projection alongside the native row for every entity-producing table. The projection is a small, Aleph-compatible JSON document — schema name plus a property bag of string arrays — written to an ftm JSONB column. Downstream consumers (Aleph sync, entity resolution, the investigations graph, Cytoscape views) read the projection instead of joining across producer-specific shapes, so a vessel, a permit applicant, a sanctioned entity, and a council decision are all addressable through one wire format.

When to use it

Reach for the ftm column when you need a cross-producer view of entities, not the producer-specific row. Typical use cases:

• Joining a sanctioned company to its LEI record and to any zoning permits it has filed
• Pushing a slice of the entity graph into Aleph for cross-jurisdiction search
• Running entity resolution (for example Splink) over a single canonical entity shape
• Driving a Cytoscape or visx investigation graph without writing per-table adapters

If you only need fields from one producer — vessel positions, a permit's filing date, the raw GLEIF response — keep reading the native columns. The projection is additive; it never replaces the source row.

Wire format

Every ftm value is a single object with three fields:

{
  "id": "urn:axiom:Company:gleif:5493001KJTIIGC8Y1R12",
  "schema": "Company",
  "properties": {
    "name": ["ACME GRAIN EXPORTS LLC"],
    "leiCode": ["5493001KJTIIGC8Y1R12"],
    "jurisdiction": ["US-DE"],
    "registrationNumber": ["5493001KJTIIGC8Y1R12"]
  }
}

Three invariants hold across every projection:

• All property values are arrays of strings, even when a single value is set. Consumers can iterate without branching on type.
• URNs follow urn:axiom:<Schema>:<source>:<localId>, where <source> identifies the producer table (gleif, zoning_permits, council_decisions, overwatch_vessels, …) and <localId> is the producer's primary key. The schema segment in the URN must match the schema field.
• Partial projections are stored as NULL. If a producer cannot fill the minimum required properties for a schema, it writes ftm = NULL rather than a half-filled object. Consumers can therefore treat the presence of ftm as a signal the projection is complete.

Schemas

The projection uses a fixed set of FTM-compatible schemas. Each producer commits to one or more of them:

The zoning_permits applicant projection uses a LLC|INC|CORP|LP|LTD|… heuristic to split companies from natural persons. A name that matches is projected as a Company; everything else falls through to Person with a firstName / lastName split on whitespace.

Storage

The ftm column lives on the producer table itself — there is no separate projection table — and is indexed for the two query shapes consumers actually need:

• ((ftm->>'schema')) — a btree expression index so WHERE ftm->>'schema' = 'Company' is cheap when fanning out by entity type.
• (ftm jsonb_path_ops) — a GIN index so containment queries (ftm @> '{"properties":{"leiCode":["5493…"]}}') hit an index instead of scanning the producer table.

Both indexes are partial-friendly: rows where ftm IS NULL are skipped, so they cost nothing on producers that have not finished backfilling.

-- All companies with a given LEI, across every producer that emits Company.
SELECT id, ftm
FROM gleif_lei_records
WHERE ftm @> '{"properties": {"leiCode": ["5493001KJTIIGC8Y1R12"]}}';

-- All events tied to a metro, regardless of which producer wrote them.
SELECT id, ftm
FROM council_decisions
WHERE ftm->>'schema' = 'Event'
  AND ftm @> '{"properties": {"location": ["nyc"]}}';

Querying across producers

Because every producer uses the same wire format, you can UNION ALL across tables to assemble a typed slice of the entity graph without per-producer adapters:

SELECT 'gleif' AS source, ftm
FROM gleif_lei_records
WHERE ftm->>'schema' = 'Company'

UNION ALL

SELECT 'zoning_permits' AS source, ftm
FROM zoning_permits
WHERE ftm->>'schema' = 'Company';

Use the id field (the canonical URN) as the join key when feeding entity resolution. Two producers that emit a Company for the same LEI will share leiCode in their property bag but will keep distinct id values — resolution happens in the consumer, not in the producer.

Producing FTM entities

Server-side producers (Edge Functions, ingest scripts, the civic document pipeline) build projections through the @axiom/ftm package, which ships builders, adapters per producer table, and a zod validator. The validator runs before the row is upserted; producers either write a fully-formed entity or NULL.

import { companyFromGleif, validateFtmEntity } from '@axiom/ftm'

const entity = companyFromGleif({
  lei: row.lei,
  legal_name: row.legal_name,
  legal_form_code: row.legal_form_code,
  jurisdiction: row.jurisdiction,
  registered_address: row.registered_address,
  entity_status: row.entity_status,
  source_url: row.source_url,
})

if (entity) {
  await supabase
    .from('gleif_lei_records')
    .update({ ftm: validateFtmEntity(entity) })
    .eq('lei', row.lei)
}

The builder enforces minimum-property gates per schema — for example, a Company projection requires name, and a Vessel projection requires imoNumber. If the gate fails, the adapter returns null and the producer writes ftm = NULL rather than a partial object.

Validation rules

validateFtmEntity rejects four classes of malformed input:

• A scalar (or non-array) property value — every property must be an array of strings.
• An empty property bag — projections with no properties are not addressable and would defeat the index.
• A URN that does not match the documented urn:axiom:<Schema>:<source>:<localId> shape.
• A URN whose schema segment disagrees with the entity's schema field.

Acceptance for AXL-229 is a producer validation rate above 99% — the small remainder is rows where the source is missing the minimum required fields (no legal_name on a GLEIF row, no applicant_name on a permit) and the projection is correctly stored as NULL.

Backfill and freshness

The ftm column is populated forward by producers as they ingest new rows. Existing rows are backfilled by the same adapter run against historical data; no row-level migration is required because the column is nullable and the indexes are GIN/expression-based.

Consumers that need a complete snapshot — for example, an Aleph sync — should filter on ftm IS NOT NULL and rely on the producer's own freshness signal in ingestion_logs to decide whether to wait for a backfill pass to complete.