Phase 4.2 — WOF SQLite Loader

Parent: PHASE_4_resolver.md (Option B picked: SQLite FTS5FTS5SQLite's built-in full-text-search module (with BM25 ranking). Mailwoman uses it for prefix and name matching against the gazetteer. + WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. SQLite).

Goal: ship @mailwoman/resolver-wof-sqlite — a small standalone package that loads a Who's On FirstWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. SQLite distribution and exposes a typed PlaceLookup interface backed by FTS5FTS5SQLite's built-in full-text-search module (with BM25 ranking). Mailwoman uses it for prefix and name matching against the gazetteer. full-text matching. Independently useful: callers can resolve "Paris, FR" → id: 101751119, lat: 48.85, lon: 2.34 without going through the full mailwoman parser.

Branch: feature/phase-4-2-wof-sqlite (this PR).

Depends on: @mailwoman/core@2.x Kysely boilerplate from #82.

Why a separate package

Three independent reasons:

1. Optional dependency. Not every mailwoman user wants geocodinggeocodingThe process of converting an address into geographic coordinates (latitude and longitude). Mailwoman geocodes in a multi-tier cascade: exact address-point match → street interpolation → locality centroid. Each tier is progressively coarser but more widely available.. npm install mailwoman should not pull in a SQLite loader they're not going to use. @mailwoman/resolver-wof-sqlite is opt-in.
2. WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. data versioning is independent from parser versioning. The SQLite distributions update on their own cadence at data.geocode.earth/wof/dist/sqlite/. Decoupling the package version from @mailwoman/core means we can ship a resolverresolverThe component that converts parsed address components (locality, region, postcode) into coordinates by looking them up in the gazetteer. The resolver ranks candidates by name match, population, and proximity, and returns the best-matching place with its centroid or polygon. patch without bumping core.
3. License separation. WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. data is CC-BY 4.0 — attribution required in any redistribution. Keeping the loader package separate from the AGPL @mailwoman/core makes the attribution surface clean: anyone shipping the resolverresolverThe component that converts parsed address components (locality, region, postcode) into coordinates by looking them up in the gazetteer. The resolver ranks candidates by name match, population, and proximity, and returns the best-matching place with its centroid or polygon. inherits the obligation; anyone shipping just the parser does not.

What's in scope for 4.2

• New workspace at resolver-wof-sqlite/ (flat, no packages/ nesting per the operator's monorepo convention).
• Typed Kysely Database schema for the WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. tables we'll touch: spr, names, geojson, ancestors, place_search (the FTS5FTS5SQLite's built-in full-text-search module (with BM25 ranking). Mailwoman uses it for prefix and name matching against the gazetteer. virtual table we build ourselves).
• PlaceLookup interface (the public surface).
• WofSqlitePlaceLookup implementation: FTS5FTS5SQLite's built-in full-text-search module (with BM25 ranking). Mailwoman uses it for prefix and name matching against the gazetteer. MATCH over name + name_alts, placetypeplacetypeThe Who's On First hierarchical classification of places: planet → continent → country → region → county → locality → neighbourhood. The resolver uses placetype to rank candidates — an exact locality match outranks a county-level match. + countrycountryThe top-level address component (an ISO country). Closed-vocabulary, so it is best handled by a deterministic matcher feeding a proposal rather than a retrained model head. filters, BM25 + boosts.
• A small fixture SQLite DB built inline in tests (no checked-in binary; tests CREATE TABLE + seed a handful of WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations.-shaped rows).
• README documenting: which WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. distribution to use, how to point the loader at it, CC-BY attribution requirement.

What's NOT in scope for 4.2

• Wiring into the parser pipelinestaged pipelineMailwoman's runtime architecture: a sequence of pure-function stages (normalize → query-shape → locale-gate → kind-classifier → phrase-grouper → classifier → decoder) connected by typed handoffs. Each stage is published as its own npm package. — that's PhasephaseA milestone in the implementation plan (Foundation, Corpus, Training, Integration, and forward-looking phases). Distinct from stage (runtime pipeline) and tier (model vocabulary). 4.3.
• Decorating AddressTree with WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. ids / lat / lon / src="wof-admin:..." — that's PhasephaseA milestone in the implementation plan (Foundation, Corpus, Training, Integration, and forward-looking phases). Distinct from stage (runtime pipeline) and tier (model vocabulary). 4.3.
• CLI --resolve flag — that's PhasephaseA milestone in the implementation plan (Foundation, Corpus, Training, Integration, and forward-looking phases). Distinct from stage (runtime pipeline) and tier (model vocabulary). 4.3.
• Downloading the actual WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. distribution — that's an operator-side mkdir + curl (documented in the README; not automated by the package).
• Whether to publish a separate wof-sqlite-data-* weightsparameterA single learned number inside a model — one weight or bias. Mailwoman's encoder has roughly 30 million of them; training is the search for good values.-style package — defer; for v0.1 the operator points the loader at a path on disk.

Architecture sketch

                 ┌─────────────────────────────────────────────────────┐
                 │  @mailwoman/resolver-wof-sqlite                     │
                 │                                                     │
                 │  ┌──────────────────────┐   ┌────────────────────┐  │
                 │  │ PlaceLookup          │←──│ WofSqlitePlaceLookup│  │
                 │  │ (interface)          │   │ (impl)             │  │
                 │  └──────────────────────┘   └─────────┬──────────┘  │
                 │                                       │             │
                 │                              ┌────────▼──────────┐  │
                 │                              │ Kysely<WofSchema> │  │
                 │                              │ → node:sqlite     │  │
                 │                              │   DatabaseSync    │  │
                 │                              └────────┬──────────┘  │
                 └───────────────────────────────────────┼─────────────┘
                                                         │
                                          ┌──────────────▼──────────────┐
                                          │  WOF SQLite distribution    │
                                          │  /path/to/whosonfirst-      │
                                          │  data-admin-us-latest.db    │
                                          │  (CC-BY 4.0 — attribute!)   │
                                          └─────────────────────────────┘

Public API (this PR)

// resolver-wof-sqlite/index.ts

export interface PlaceCandidate {
	id: number
	name: string
	placetype: WofPlacetype
	country: string // ISO 3166-1 alpha-2
	lat: number
	lon: number
	parent_id?: number
	score: number // BM25 + boosts; higher = better match
}

export type WofPlacetype =
	| "country"
	| "macroregion"
	| "region"
	| "macrocounty"
	| "county"
	| "localadmin"
	| "locality"
	| "borough"
	| "neighbourhood"
	| "microhood"
	| "postalcode"
	| "venue"
	| "campus"
	| "address"

export interface FindPlaceQuery {
	text: string
	placetype?: WofPlacetype | WofPlacetype[]
	country?: string // ISO 3166-1 alpha-2 — narrows the search
	parentId?: number // narrows to descendants of a specific WOF place
	limit?: number // default 10
}

export interface PlaceLookup {
	findPlace(query: FindPlaceQuery): Promise<PlaceCandidate[]>
	close(): void
}

export interface WofSqlitePlaceLookupOpts {
	databasePath: string
	/** Optional: pre-opened DatabaseSync (testing). Mutually exclusive with databasePath. */
	database?: DatabaseSync
}

export class WofSqlitePlaceLookup implements PlaceLookup, Disposable {
	constructor(opts: WofSqlitePlaceLookupOpts)
	findPlace(query: FindPlaceQuery): Promise<PlaceCandidate[]>
	close(): void
	[Symbol.dispose](): void
}

WOF schema mapping

The Geocode Earth WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. SQLite distributions ship these tables that matter to us:

Table	Use	Notes
spr	Standard Places Response — the core lookup table. One row per place with all the lookup fields.	PK id. Carries parent_id, name, placetype, country, latitude, longitude, min_*/max_* bbox, and lifecycle flags. is_current carries TWO conventions: both -1 (modern Who's On FirstWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations.) and 1 (legacy Mapzen-era) mean "currently valid"; only 0 means "not current" (see #91 for the diagnostic that uncovered the mixed convention — affected ~42% of admin-US rows).
names	Alternate names per place, keyed by BCP-47 language tag subfields.	Joins back to spr.id via id (NOT place_id — the column name is id on both tables, even though it's a FK on names). No kind column; FTS just concatenates ALL names.
geojson	Per-place GeoJSON blob.	Not consulted by PhasephaseA milestone in the implementation plan (Foundation, Corpus, Training, Integration, and forward-looking phases). Distinct from stage (runtime pipeline) and tier (model vocabulary). 4.2 — lat/lon already live as spr.latitude / spr.longitude. Modeled in schema.ts for PhasephaseA milestone in the implementation plan (Foundation, Corpus, Training, Integration, and forward-looking phases). Distinct from stage (runtime pipeline) and tier (model vocabulary). 4.3+ where bbox or full geometry may be needed.
ancestors	Adjacency: ancestor relationships per place.	Used for parentId filter — descendant lookup is WHERE spr.id IN (SELECT id FROM ancestors WHERE ancestor_id = ?).
place_search	FTS5FTS5SQLite's built-in full-text-search module (with BM25 ranking). Mailwoman uses it for prefix and name matching against the gazetteer. virtual table. NOT in the upstream WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. distro — we build it.	CREATE VIRTUAL TABLE place_search USING fts5(wof_id UNINDEXED, name, alt_names, ...). Built by buildPlaceSearchFts() lazily or by the mailwoman-wof-build-fts CLI ahead-of-time.

The Kysely Database interface declared in resolver-wof-sqlite/schema.ts types the columns we touch. Tables we don't touch (e.g. concordances, spr sibling tables) are not modeled — we don't want to pretend we understand schema we haven't read.

Schema-discovery note (2026-05-20): the initial PhasephaseA milestone in the implementation plan (Foundation, Corpus, Training, Integration, and forward-looking phases). Distinct from stage (runtime pipeline) and tier (model vocabulary). 4.2 ship assumed a places table with names joined on place_id. Real WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. has none of that — it uses spr, names join on id, and lat/lon are direct columns. Caught during the first run against real data; fixed in a same-day follow-up. The fixture tests were correctly green against the made-up schema; only the integration tests against actual WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. caught the mismatch. Always validate against the real artifact before declaring a data-integration done.

Ranking

FTS5FTS5SQLite's built-in full-text-search module (with BM25 ranking). Mailwoman uses it for prefix and name matching against the gazetteer. BM25 default, with manual post-scoring:

• PlacetypeplacetypeThe Who's On First hierarchical classification of places: planet → continent → country → region → county → locality → neighbourhood. The resolver uses placetype to rank candidates — an exact locality match outranks a county-level match. boost. When the query specifies placetype: "locality" and the candidate matches, +0.5 to score. When the query doesn't specify, localitylocalityThe city / town / settlement component of an address: a populated place sitting between region and neighbourhood in the hierarchy. gets +0.2 (most common case).
• CountrycountryThe top-level address component (an ISO country). Closed-vocabulary, so it is best handled by a deterministic matcher feeding a proposal rather than a retrained model head. match boost. When the query specifies country, candidates from that countrycountryThe top-level address component (an ISO country). Closed-vocabulary, so it is best handled by a deterministic matcher feeding a proposal rather than a retrained model head. get +0.3.
• Parent match boost. When the query specifies parentId, candidates that are direct children get +0.5, transitive descendants get +0.2.
• Length penalty. Candidates whose name is much longer than the query get a small penalty (-0.1 * max(0, candidateLen - queryLen - 3) / 10) — favors Paris over Paris-l'Hôpital when querying "paris".

Returned score is the post-boost number, not the raw BM25 — callers shouldn't depend on a specific scale, just relative ordering.

Tests

Build a small fixture DB inline (~10 places: a couple of countries, a few regionsregionThe first-level administrative subdivision of a country — a US state, a French region, a province. The component between country and locality., a handful of localities). No checked-in binary.

• findPlace({text: "Paris"}) returns Paris,FR first.
• findPlace({text: "Paris", country: "US"}) returns Paris,TX first.
• findPlace({text: "London", placetype: "locality"}) filters out the boroughboroughAn administrative or historical division of a city — e.g. the five boroughs of New York City. May be postal, legal, or both, and complicates the locality hierarchy. of London,Ontario.
• findPlace({text: "Springfield", parentId: <Illinois>}) returns Springfield,IL not Springfield,MA.
• Alternate-name match: findPlace({text: "パリ"}) returns Paris,FR (via the names join).
• Disposable: [Symbol.dispose]() closes the DB.

Integration tests against a real WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. distribution will be added in a follow-up once the operator authorizes a download (the night-shift agent ran into an auto-mode block on the data.geocode.earth fetch).

Open questions

• Sync vs async driver. node:sqlite is sync-only. For the resolverresolverThe component that converts parsed address components (locality, region, postcode) into coordinates by looking them up in the gazetteer. The resolver ranks candidates by name match, population, and proximity, and returns the best-matching place with its centroid or polygon. use case the entire findPlace is a single SQL query + post-scoring; sync is fine. If we ever need to query in a hot inferenceinferenceRunning the trained model on new input to get predictions, as opposed to training, which produces the model. In Mailwoman that means a small transformer encoder reads an address string and classifies every token — house number, street, locality, region, postcode, and the rest. A Who's On First gazetteer can feed soft location hints into the pass, but the model makes the final call on every label. Where a generative model writes text token by token, Mailwoman's output is a retrieval-augmented token classification: one label per input piece. loop, revisit by moving the driver into a Worker.
• FTS5FTS5SQLite's built-in full-text-search module (with BM25 ranking). Mailwoman uses it for prefix and name matching against the gazetteer. build cost. Building the FTS5FTS5SQLite's built-in full-text-search module (with BM25 ranking). Mailwoman uses it for prefix and name matching against the gazetteer. index on first open is expensive against the planet-scale WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. distro (millions of rows). Mitigation in this PR: build is opt-in via WofSqlitePlaceLookupOpts.buildFts: true (default false). The README documents a one-shot CLI to build it ahead of time; the resolverresolverThe component that converts parsed address components (locality, region, postcode) into coordinates by looking them up in the gazetteer. The resolver ranks candidates by name match, population, and proximity, and returns the best-matching place with its centroid or polygon. assumes it exists.
• Concurrency. Kysely's node:sqlite driver (from core/kysley/) uses a single connection with a mutex. The resolverresolverThe component that converts parsed address components (locality, region, postcode) into coordinates by looking them up in the gazetteer. The resolver ranks candidates by name match, population, and proximity, and returns the best-matching place with its centroid or polygon. doesn't open multiple connections — one resolverresolverThe component that converts parsed address components (locality, region, postcode) into coordinates by looking them up in the gazetteer. The resolver ranks candidates by name match, population, and proximity, and returns the best-matching place with its centroid or polygon. instance = one DB connection = serialized queries. For multi-request servers, instantiate one resolverresolverThe component that converts parsed address components (locality, region, postcode) into coordinates by looking them up in the gazetteer. The resolver ranks candidates by name match, population, and proximity, and returns the best-matching place with its centroid or polygon. per request handler, not one shared.

Changelog

• 2026-05-20 — opened. Picks the public API surface; defers the integration tests to follow-up once WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. data download is authorized.
• 2026-05-20 (same day) — WOFWOF (Who's On First). An open-source gazetteer of places maintained by Mapzen/whosonfirst. Mailwoman builds a custom SQLite database from WOF GeoJSON repos, extended with postcode data, importance scores, and coincident-role relations. download authorized; schema-discovery fix needed (places → spr, place_id → id, no JSON-extract for lat/lon). 10 integration tests added against the real US admin shardshardA partial output file of the corpus build, written in Parquet format. The training pipeline streams shards row by row.. FTS5FTS5SQLite's built-in full-text-search module (with BM25 ranking). Mailwoman uses it for prefix and name matching against the gazetteer. build on the full shardshardA partial output file of the corpus build, written in Parquet format. The training pipeline streams shards row by row. (142k rows) completes in ~0.81s — much faster than the ~minutes estimate in the original plan. No popularity signal in the current ranking (documented limitation; bare findPlace({text: "Paris"}) returns small-town US Parises in unspecified order); country / placetype / parentId filters all behave as designed.