Complied Map

How /map works: the filter model, the query service, the snapshot pipeline and the tenant boundary. For engineers debugging a map request, adding a filter, or operating map-service/. The user-facing behaviour is in violations, buildings and the map.

Overview

The map has one filter object, held in the URL. That filter goes to one query service (map-service/, DuckDB over a nightly Parquet copy of the open city records). One response carries the totals, the ranked list and the pins, all computed from the same filtered set, so they cannot disagree.

URL facets -> MapQueryFilter -> POST /map/query -> { totals, in_view, ranked, pins }

If the three ever disagree, something is wrong in the service. Do not repair it by filtering in the browser.

Postgres prepares, DuckDB answers:

• Postgres keeps the vocabulary and the mapping: the taxonomy tables and the view map_event_source, the one place a public_events row gets its department and category.
• The exporter reads that view plus buildings and the taxonomy and writes Parquet.
• The service holds a snapshot in memory and answers every request from it.
• The browser never talks to the service about tenant data without the user's own token.

Why DuckDB

The first design ran the query in Postgres: a slim copy of open events, one query function, a result cache. Timed on a local database inflated to prod scale (38.7M public_events rows, 10M open, 1.1M buildings; row counts synthetic, timings meaningful):

	Postgres	DuckDB over Parquet
Worst cold question ("every building")	13.9 s (fails the 8 s authenticated statement timeout)	about 0.2 s
Worst repeat question	2.4 s (cached)	about 0.2 s, no cache
Narrow lens (616 overdue in Brooklyn)	0.7 s cold	24-29 ms
Nightly refresh	about 2 min incremental, 16 min full	about 80 s for a full export of all history
Needs a result cache	yes	no
History over all 38.7M rows	not possible in that design	250-530 ms

Both engines returned identical building counts on every lens. The cost is one small extra service. The benchmark scripts in scripts/map-bench/ time the retired Postgres path and are historical; see scripts/AGENTS.md.

The filter model

Axis	Filter fields	Semantics
What	depts, categories, orders	ORed with each other. Kept in one normal form by normalizeWhat (whatTree.ts, shared by the menus and smart search): children of a chosen parent are dropped; a department with every category chosen folds into the department. Orders never fold into their category, because the category also holds records with no order code. Orders are written dept:code.
Status	statuses, agencyStatuses	ORed with each other, ANDed with What. Agency statuses are dept:NAME.
Where	boroughs, yearFrom/yearTo, minUnits/maxUnits	Building facts, ANDed.
Whose	scope: all, tracked, prospects	Needs the tenant overlay for tracked and prospects.
Owed	Obligation facets, sent as buildingIds	Evaluated in the browser; forces scope = tracked.

An empty What and empty Status means every building, still narrowed by Where, scope and buildingIds.

Departments come from the map_departments table: HPD (violations by order number, complaints, housing-court cases, 311), DOB (violations by code, complaints), and ECB / OATH (summonses by issuing agency). FDNY, DOHMH and DSNY are seeded with available = false and shown disabled; what exists for them is their OATH summonses under ECB / OATH. Categories and orders live in map_categories and map_category_orders, not in TypeScript. An HPD order the seed does not name falls into its department's catch-all. GET /map/taxonomy serves the whole tree with each option's open-record count in the loaded snapshot, so the menus never offer a choice with nothing behind it.

Statuses

Key	Meaning	Source
overdue	Correct-by date passed and not certified	HPD newcorrectbydate / originalcorrectbydate, certifieddate, evaluated on the New York date of the request
in_window	Has a correct-by date that has not passed	public
postponed	HPD granted a certification postponement	status_detail = OPEN_POSTPONEMENT_GRANTED, with or without _OVERDUE
hearing	An OATH hearing is on the calendar	ECB / OATH hearing date on or after today
owed	A city-issued balance is outstanding	ECB / OATH balance_due, never a figure Complied derives
contested	The tenant filed a contest	Complied's own record: the latest non-superseded project_order_decisions row with path = 'contest'
agency names	HPD currentstatus, ECB/OATH hearing_status, DOB violation category	public

Lenses are named presets over the filter plus a colour palette (MAP_LENSES in mapFacets.ts); they are not a separate query path. Two flags come from the lens list: child at risk (an open order 618, the only public building-level sign a child tested positive) and records overdue (RPO past deadline).

Obligation facets. The obligation filters (annual notice, tenant response, visual inspection, children under 6, turnover, LL31 and others) are tenant-scoped facts about apartments. The browser evaluates them with the same needsForUnit() that /deadlines uses, so there is one ladder (mapObligations.ts). The page sends the portfolio-wide list of matching tracked buildings as buildingIds, so the server narrows totals, list and pins by it. While the facts load, the query waits. Tracked buildings with no apartments on file cannot match, and the rail says so.

URL vocabulary. View state lives in the query string, so a view is a link and a saved view is a stored query string (saved views are v: 2).

• dept, cat are comma-separated; order, agencyStatus are |-separated because HPD status names contain commas.
• Statuses are state, not status. On /violations, status means OPEN / CLOSED / DISMISSED; every map status is a state of an OPEN record. mapLinkFromViolations() translates a violations link, mapping agencies to departments and dropping filters with no map equivalent.
• A facet cleared from a lens preset is written none, so cleared and unset stay distinct.
• A selected building is ?b=<buildingId>.

The tenant line

Three parts of the filter are tenant-specific: scope, contested and buildingIds. map-service holds no service-role key and no JWT secret.

1. The browser sends the user's access token.
2. The service calls PostgREST map_tenant_overlay() with that same token.
3. The function is SECURITY INVOKER and matches tenant_id = current_tenant_id() explicitly, not RLS alone, because a platform operator's broad read policy would otherwise turn "already in Complied" into "in anybody's Complied". Impersonation works because current_tenant_id() honours it.
4. The result lands in small in-memory tables keyed by that tenant id, and every query filters on it. Overlay results are cached per token for MAP_OVERLAY_TTL_SECONDS (60 s).
5. There is no shared result cache, so there is nothing for one tenant's answer to leak into.

If PostgREST refuses the token, the map refuses the request. Never accept tenant_id from the request body, fetch the overlay with a service key, or cache a public result that already folded in tenant predicates. Tested in snapshot.test.ts ("the tenant line") and in db-tests/tests/leakage.test.ts ("map_tenant_overlay").

Request path

Map load or filter change

1. The user changes a chip or lands on a URL; CompliedMapPage decodes facets from the query string.
2. If obligation facets are set, the page waits for portfolio facts and derives buildingIds.
3. facetsToFilter builds a plain MapQueryFilter; queryMap({ filter, viewport, zoom, sort }) in mapQuery.ts POSTs it with the session JWT.
4. The service resolves the tenant overlay, compiles SQL once (compileMapQuery), runs it against the snapshot and returns one JSON document.
5. The page paints totals, rail and pins from that one document.

Viewport affects only in_view, ranked and the pins. Totals cover the whole filtered set. Pins are individual buildings when the view holds at most 3,000 matches (MAX_POINTS), otherwise grid cells (centroid of matching buildings per square, about 1/8 of a map tile, snapped to a fixed world grid). ranked holds up to 500 rows in view, ordered server-side, so changing the sort is exact.

Opening a building. Selecting a pin or rail row sets ?b=; getMapBuildingDetail calls POST /map/building, which uses the same predicates as the query so its matching counts equal the pin. Facts the snapshot does not carry (BBL, zip, client, projects) come from Postgres via getMapBuildingExtras. The photo is Mapillary street level if VITE_MAPILLARY_TOKEN is set, otherwise a Mapbox aerial; that lookup goes browser to provider and never through the service.

Smart search. POST /map/search { q } turns one sentence into a filter. The model sees a fixed vocabulary plus the snapshot's taxonomy and answers in a JSON schema; sanitize drops anything outside the vocabulary; the browser applies the result through facetsFromSearch, the same codec as manual filters, never raw model output. Without MAP_SEARCH_PROVIDER the route answers 501.

HTTP API

Every route except /health requires Authorization: Bearer <Supabase access token>.

Method	Path	Body	Returns
POST	/map/query	{ filter, viewport, zoom, sort, limit? }	totals, in_view, ranked, pins, sync_at, as_of, build_ms
POST	/map/building	{ filter, buildingId }	Building facts and matching / open counts by department and order
GET	/map/taxonomy		Departments, categories, orders and status counts for the loaded snapshot
POST	/map/search	{ q }	Sanitized facets plus summary, unmatched, empty
GET	/health		{ ok, snapshot }, no auth, no tenant data

Snapshot pipeline

1. Every public-data sync ends with mark_map_stale(), which stamps map_data_version.changed_at (the orchestrator and both CLI tools in db-tests/scripts/ call it).
2. The exporter (exportIfStale, or MAP_EXPORT_WATCH in a long-running process) exports when the stamp is newer than the current snapshot and has been quiet for MAP_EXPORT_QUIET_MINUTES (default 10). Cron fires eight feeds together, so one night costs one export.
3. Export reads map_event_source, buildings and the taxonomy and writes Parquet under snapshots/<id>/, then writes latest.json last, so a reader never sees half a snapshot. At prod scale: about 2 minutes, about 235 MB for 10M open rows.
4. The server polls latest.json, loads the new snapshot into a second in-memory DuckDB, swaps, and closes the old one when its in-flight requests finish. Memory briefly holds two copies (a 2 GB machine is enough).

Storage is a local directory or s3://bucket/prefix through DuckDB's httpfs, so AWS S3, Supabase Storage's S3 endpoint and R2 all work. httpfs cannot delete objects, so old snapshots expire by bucket lifecycle rule.

File map

Frontend

Path	Role
src/pages/CompliedMapPage.tsx	Owns filter state; fires query, building, taxonomy and search calls; URL sync
src/components/map/mapFacets.ts	Facets, lenses, URL codec, facetsToFilter, facetsFromSearch
src/components/map/MapTopBar.tsx, MapRecordFilters.tsx, MapSmartSearch.tsx	Lens and scope chrome; department, category, order and status pickers; the "Ask the map" box
src/components/map/MapResultsRail.tsx, MapBuildingDetails.tsx, BuildingPhoto.tsx	Ranked list with expand-in-place rows; shared popup and row body; building photo
src/components/map/CompliedMapView.tsx	Mapbox canvas drawing the query's pins only (no second unfiltered layer); the cell colour ramp tops out at the 90th percentile of the current answer
src/lib/mapFilter.ts	MapQueryFilter, status vocabulary, What-tree collapse, summaries
src/data/mapQuery.ts	All HTTP to the service, the tracked-point overlay, and building extras from Postgres

map-service (map-service/)

Path	Role
src/sql.ts	The only place a filter becomes SQL; also building detail and taxonomy. Values go through lit() or are validated numbers and UUIDs.
src/snapshot.ts, src/overlay.ts	One loaded DuckDB with hot swap; JWT to map_tenant_overlay()
src/exporter.ts, src/storage.ts, src/cli-export.ts	Postgres to Parquet and the staleness rule; local or S3 store; npm run export
src/search.ts, searchVocabulary.ts, llm.ts	Smart search; Anthropic Messages or OpenAI-compatible providers over plain fetch
src/app.ts, server.ts, lambda.ts	Routes with no server attached; node:http plus poll and export timers; Lambda adapter
src/shared/	The wire contract the frontend imports through the @map-shared/* alias; imports nothing outside shared/

Postgres

Migration	Role
20260924120000_map_query.sql	Taxonomy tables and the map_event_source view
20260924150000_map_duckdb_service.sql	map_data_version, mark_map_stale(), map_tenant_overlay()
20260925120000_map_postponed_status_detail.sql	postponed reads status_detail instead of raw_data->>'currentstatusid'

Local development

npm run dev                      # repo root: Supabase check, Vite, functions and map-service with export watch
cd map-service && npm install && npm start    # service only, :8787
cd map-service && npm run export              # take a snapshot now
cd map-service && npm test

The first local snapshot appears a few seconds after start and re-exports a minute after any local sync. Node runs the .ts files directly (type stripping, Node 22.18 or later), so only erasable TypeScript syntax is allowed. npm run dev -- --no-map skips the service. In a dev build the frontend falls back to http://127.0.0.1:8787; a production build has no fallback and an unset VITE_MAP_SERVICE_URL makes the map fail loudly. Environment variables are tabulated in map-service/AGENTS.md. Deployment: deployment.

Changing common things

To	Do this
Add a lens	Add an entry to MAP_LENSES. No SQL change.
Add a status key	MAP_STATUS_OPTIONS, the predicates in sql.ts, and the taxonomy if needed. Keep searchVocabulary.ts aligned; smartSearch.test.ts fails on drift.
Remap an HPD order to a category	Edit the taxonomy tables through a migration, not TypeScript.
Change the pin threshold	MAX_POINTS and the pins CASE in sql.ts. DuckDB plans both branches of a CASE, subqueries included, so bound anything expensive inside a branch.
Change the ranked limit	MAP_RANKED_LIMIT in mapQuery.ts and the service's limit handling.
Change obligation logic	mapObligations.ts only, so /deadlines and the map share one ladder.

Gotcha: getRowObjectsJson() returns BIGINT as a string; cast counts to INTEGER or build JSON in SQL with to_json, as sql.ts does.

Tests

Suite	Covers
map-service npm test	SQL compiler, a fixture world, the HTTP layer, the tenant line
src/lib/mapFilter.test.ts, src/components/map/mapFacets.test.ts	What-tree collapse and summaries; URL codec and lenses
src/components/map/smartSearch.test.ts	Frontend and service vocabularies stay in sync
db-tests/tests/leakage.test.ts	map_tenant_overlay cross-tenant leakage