Public-data ingestion

How NYC agency data (HPD, DOB, ECB, OATH, 311, DOF, DOHMH, DOT, FDNY, DEP) gets into Complied's own database, and why nothing in the app calls the city's APIs at browse time. For engineers working on domain/src/ingestion/ or the ingestion CLI.

Principle

Agency data is stored locally and synced on a schedule. The map, the violations explorer and project intake read Postgres; they never live-fetch Socrata. Live calls are reserved for user-initiated interactions such as geocoding an address.

Three tables

Table	Question it answers	Notes
buildings	Who and where is this building?	One row per BIN, citywide. Address, BIN, BBL, coordinates, year built, unit count, plus denormalized compliance counts for map pins.
public_events	What happened?	One row per agency violation, complaint or event, keyed (agency, source_id). Dates, status_norm, order numbers, and the agency facts screens show (see Promoted fields). The raw agency payload is not stored; see Raw payload archive.
tenant_buildings	Who cares?	A tenant's portfolio: "this tenant tracks this building for this client".

There is no separate store for tracked buildings' events. "Tracked" and "prospect" are the same rows, distinguished by a tenant_buildings join. Individual violation rows never live on buildings; only summary counts do (a number on a map pin comes from buildings, anything a project or the deadline engine needs comes from public_events).

Supporting tables:

Table	Role
unlinked_events	Quarantine for rows that could not be tied to a building. Unique on (agency, source_id); carries the attempted BIN/BBL/address and a reason.
sync_runs	One row per feed invocation: rows fetched/upserted/quarantined/tombstoned, outcome (success, partial, failure), trigger (cron, manual).
feed_sync_state	Per-feed position: the forward delta_watermark and the backward backfill cursor.

buildings and public_events carry a guard (ingestion_guard schema, see 20260929090000_protect_ingestion_tables.sql) that blocks DELETE, TRUNCATE and dropping the tables or their columns unless a transaction first sets complied.allow_ingestion_table_deletes = 'on'. Ingestion only upserts.

The five layers

Layer	What	Cadence	Runs in
1. Buildings identity	buildings from NYC Building Footprints (BIN, outlines) joined to PLUTO on BBL (year built, unit count, and the lot attributes client onboarding reads: floors, floor area, buildings on the lot, building class, landmark, historic district, condo number)	Weekly, Saturdays 04:00 UTC	ingest identity on the ingestion box, driven by a systemd timer
2. Citywide delta	public_events for every feed, changed rows only	Nightly 08:10 UTC	ingest CLI on the ingestion box, driven by a systemd timer
2 backfill	Historical rows, walking backward in date windows	Manual	ingest CLI
2b. Summary refresh	Compliance counts on buildings, map grid, snapshot stamp	After every nightly delta	RPCs called by the CLI
5. Obligations	Deadlines computed from public_events and building facts	On read	@complied/domain in the browser; no ingest job

Layer 1 needs both datasets. PLUTO is organised by tax lot and carries no BIN; one lot can hold several buildings. Footprints supplies the BIN and geometry, PLUTO supplies year built (the pre-1978 lead-paint cutoff cannot be answered without it) and, for client onboarding's pre-filled answers, num_floors, bldg_area, num_bldgs, bldg_class, landmark_name, historic_district and condo_no (migration 20261015110000). These are per tax lot, so floors and floor area describe one building only when num_bldgs is 1. The owner of record is not kept: nothing reads it, and the run holds all ~858k PLUTO rows in memory. The whole citywide set is loaded (about 1.1M rows), which is also what lets 311 and OATH rows with no BBL resolve by address at all. Identity loading is too large for an edge function's time and memory budget, so it runs through the ingestion CLI as ingest identity, over a direct Postgres connection.

Layer 1 refreshes weekly, and most weeks writes little. Footprints is republished weekly (new BINs, buildings moved to another lot); PLUTO quarterly. Each run re-fetches both, because a new footprint is useless without its lot's PLUTO facts, but writes only rows whose values changed, and a missing value never clears a known one: a lot PLUTO does not match keeps the address and the HPD-placed coordinates the row already has. The landmark, historic-district and condo columns are the exception: whenever the lot matched they follow PLUTO, so a lot that stops being one stops being one here. A run that changed anything marks the map stale. Each run logs a sync_runs row with feed_id identity, so sync_health_summary shows when it last succeeded.

Layer 2 uses Socrata's hidden :updated_at field where a publisher updates incrementally ($where=:updated_at > '<watermark>'), instead of asking each building "anything new?". Some datasets are replaced wholesale nightly (DOB complaints reports ~100% changed daily); a feed like that is pulled in full each run and is safe to tombstone against. Check a dataset's :updated_at behaviour (changed-row count against total) before relying on it for a new feed.

Layer 2b runs refresh_building_compliance_summary() to write open/overdue counts onto buildings, refresh_building_flags() to rebuild the map's "Building has" facts, and mark_map_stale() to tell the map exporter the data changed (see map.md).

Adopting a building inserts a tenant_buildings row (trackBuilding in src/data/tenantBuildings.ts). The building row already exists, so adopting adds the tracking relationship, not an identity. Events for the building already arrived through the citywide sync.

The shared runFeed engine

Every dataset has its own field names, status vocabulary and building identifier (BIN, BBL or only an address). One shared engine does the same steps for all of them; a feed supplies only how to fetch and how to normalize.

Piece	File	Job
runCitywideFeed	runFeedCitywide.ts	The nightly runner: streams fetchSince page by page (normalize and persist each page before fetching the next), tombstones only against a complete snapshot, logs, passes on each :updated_at stamp the plan settles (onSettled) while everything before it has landed, and reports complete for the watermark.
runRecordFeed	runRecordFeed.ts	A record feed's full pull: streams the feed's plan page by page through persistRecords, then, only after a complete pull, RecordDb.sweepUnseen deletes the feed's records not seen since it began. Logs the sweep count as rows_tombstoned.
runFeed	runFeed.ts	The candidate-scoped runner (fetch scoped to a list of buildings). Never auto-creates buildings.
persistNormalizedEvents	persistEvents.ts	The shared middle: resolve, auto-create from BIN, link units, dedupe, upsert or quarantine. The nightly sync and the backfill both call it so history never disagrees with the present.
persistRecords	records.ts	The record feeds' shared middle: resolve (BIN, BBL, address, or resolveVia), dedupe, upsert into the four record tables or quarantine. RecordDb is its Postgres seam (recordDb.ts implements it).
buildingEventFeed, groupedEventFeed	eventFeed.ts	Factory for an event feed from a spec: dataset, prefix, and a normalize returning the event's own fields.
sanitizeCompactDate and the other date parsers	feeds/dates.ts	Parse the city's date spellings (compact YYYYMMDD, MM/DD/YYYY, timestamps) to YYYY-MM-DD or reject them, so one impossible date never reaches Postgres.
runBackfillChunk	runBackfill.ts	One bounded date window of history.
resolveBuilding(s)	buildingResolver.ts	BIN, then BBL, then normalized address. Never creates, never fuzzy-matches.
computeXxxStatus	statusNormalization.ts	Collapses each agency's status vocabulary to OPEN, CLOSED or DISMISSED; the agency-specific value is kept in status_detail.
changedSinceFetch, updatedSinceFetch	feeds/socrata.ts	The nightly delta of every :updated_at feed: :updated_at > watermark, read in (:updated_at, :id) keyset pages so each stamp it finishes is settled as it goes (see the watermark invariant below). updatedSinceFetch adds OATH's republish skip.
streamAllPages, streamedFetch, fetchAllPages, socrataBackfill	feeds/socrata.ts	Shared pagination (a safety cap surfaced as truncated: 10k-row pages up to 5M rows for a streamed full pull; 50k-row pages up to 500k for the buffered per-building and backfill fetches) and a declarative backfill spec.
units.ts, apartmentLabelKey	units.ts, apartments.ts	Links HPD apartment labels to units by the shared apartment rule (APT1C, 1-C and 1C find one unit), creating one only if the label passes isCreatableUnitLabel. A key in unit_label_aliases (a person merged it into another unit) links to that unit. public.apartment_key is the same rule in SQL (units.apt_key); the vectors in domain/tests/ingestion/fixtures/apartmentKeyVectors.json hold the two together.

Invariants the engine enforces:

• Resolution never guesses from an address. A fuzzy address match once attached violations to the wrong building. The only auto-created building comes from a real BIN; an address-only row that does not match exactly is quarantined.
• Tombstoning needs a complete snapshot. "Not seen this run" means "gone" only when the fetch was neither incremental nor truncated. A truncated fetch logs partial and skips the sweep; a backfill never tombstones. Tombstoned rows are set to CLOSED with tombstone_reason = 'NOT_IN_LATEST_FETCH' and resurrected if they reappear.
• Idempotency. Every write is an upsert on (agency, source_id). Each feed's source_id carries its sourceIdPrefix, so feeds sharing an agency (three HPD feeds) never collide on upsert or tombstone.
• Bounded memory. The nightly delta streams: one 10k-row page is normalized and persisted (in batches of 2,000) before the next is fetched, so a 1M-row night costs the same memory as a small one. The backfill bounds each window instead.
• Building facts never overwrite Layer 1's position. HPD violation rows also carry HPD's building ids and a geocode; see Promoted fields.
• The watermark only moves over rows that landed. A run is complete when its fetch succeeded, was not truncated, and every row normalized and persisted (quarantine counts as persisted); a complete run moves delta_watermark to the moment it started. A thrown fetch, a failed upsert batch or a truncated fetch is not complete, so what is left of the window runs again; pages already written stay written, since every write is idempotent. A degraded Socrata answers an empty page or a count(*) of 0 with HTTP 200, so the delta asks the count again while it says 0 (up to eight times) and asks a short page again (up to twelve times for a stamp-ordered delta) while the count says rows are left; a stream that ends short of the count throws. The flip side: a row that fails to persist deterministically holds its feed's watermark until it is fixed, which is why feeds sanitize impossible dates before they reach Postgres.
• A long window moves the watermark as it is read (#160). An :updated_at delta (changedSinceFetch) reads its window in stamp order, so once a page reaches a later stamp, every earlier stamp is whole. Each such stamp is settled: runCitywideFeed moves the watermark there, as long as no row has failed so far in the run and the stretch since the last settled stamp did not fetch rows yet persist none (runBackfill's cursor rule, #88). A failure then costs only the stamp being read, not the whole window: before this, OATH's 1.35M-row window from 2026-10-08 failed part way every night and the next night's window only grew. A stamp is one publisher batch and cannot be split (OATH stamped 1,003,241 rows at once on 2026-10-14), which is why the split is by stamp rather than by day or row count. Pages are keyset pages, (:updated_at, :id) after the last row read, so a deep page costs what the first does. Watermarks keep milliseconds, because Socrata stamps carry them and a watermark truncated to the second re-reads its whole stamp. DOB complaints (date-based delta), HPD litigation and DOF liens (full pulls) and the rodent feed (whole jobs) are not stamp-ordered and move only on a complete run.

Promoted fields

The facts screens show are real columns on public_events, filled by each feed's normalize() at ingest. The parsing rules (trimmed text with blank as null, plain-decimal money, floating timestamps, the rent-impairing yes-spellings) live once in promotedFields.ts.

Column	HPD violations	ECB violations	OATH hearings
hpd_class	class
hpd_rent_impairing	rentimpairing
hpd_certify_by	newcertifybydate, else originalcertifybydate
hpd_certified_date	certifieddate
penalty_amount		penality_imposed	penalty_imposed, else total_violation_amount
balance_due		balance_due	balance_due
hearing_result		hearing_status	hearing_result
issuing_agency			issuing_agency
agency_order_code	ordernumber	infraction_code1	charge_1_code
agency_status_text	currentstatus	hearing_status	hearing_status
unit_label	apartment (HPD complaints too)

HPD violation rows also name their building's HPD buildingid, registrationid, house range and coordinates. persistNormalizedEvents collects those once per building per batch and calls update_building_hpd_facts(), which writes the HPD ids onto buildings and the coordinates only where the building has none, so Layer 1's position always wins. geom_3857 is generated from the coordinates, so a building placed this way appears on the map.

One writer upserts public_events: upsertEvents in ingest/src/db/eventDb.ts. A new promoted column is added to its column list.

Raw payload archive

public_events used to keep each source row verbatim in raw_data. Once every fact the app reads was a column (above), the column was archived and dropped (issue #163, migration 20261007140000). Only quarantined rows keep their source row (unlinked_events.raw_data), because redrive re-normalizes it. To see what the city publishes for one record, the violation page's Source record panel fetches it live from NYC Open Data.

The archive: id, agency, source_id, raw_data for all 39,650,464 rows as of 2026-10-07, in s3://complied-public-events-raw-archive-915349285180/public-events-raw/2026-10-07/ (421 zstd Parquet parts plus manifest.json; private, versioned). It was read back and matched per agency against count(*) before the drop. To read one row back:

-- DuckDB, with the httpfs extension and AWS credentials
select raw_data from read_parquet('s3://complied-public-events-raw-archive-915349285180/public-events-raw/2026-10-07/part-*.parquet')
 where source_id = 'hpd-violation:12345';

Feeds

Event feeds write public_events: the 9 original feeds below, one module each, then the 8 enforcement event feeds. The nightly registry, FEED_REGISTRY in ingest/src/feeds/registry.ts, holds 16 of them; dof-liens runs only in the weekly records pull (WEEKLY_EVENT_FEEDS), and EVENT_FEEDS is all 17 for scoped, seed-buildings and redrive. The 23 record feeds are under Record feeds.

Feed id	Dataset	Notes
hpd-violations	HPD violations wvxf-dwi5	Incremental by :updated_at. Carries unit labels.
hpd-complaints	HPD complaints ygpa-z7cr	Incremental. Rows carry bin. Carries unit labels.
hpd-litigation	HPD litigation 59kj-x8nc	Full pull each run (about 240k rows); no backfill.
dob-violations	DOB violations 3h2n-5cm9	Incremental by :updated_at (a few hundred rows a night). Compact-text dates.
dob-complaints	DOB complaints eabe-havv	The source rebuilds the whole table nightly, so :updated_at is useless. The delta is every ACTIVE complaint plus anything entered, inspected or disposed since the watermark (less a 14-day lookback): about 30k rows instead of 3.1M. Dates are MM/DD/YYYY text.
ecb-violations	ECB violations 6bgk-3dad	Incremental by :updated_at (one batch per weekday evening, 1–5k rows). Compact-text dates.
oath-hearings	OATH hearings jz4z-kudi	Incremental by :updated_at (one batch around 04:00 UTC, usually 40k–130k rows, at most 1.04M). A stamp with more than 2M rows in the window is a republish of the whole dataset (2026-10-09: 15.65M rows under one stamp) and is skipped, less its tickets issued or decided within two weeks of the watermark (updatedSinceFetch, #208). Socrata sometimes answers that per-stamp count with an empty page, so an empty answer counts only when the stamps add up to the window; otherwise the run fails and the watermark holds. Resolves by the BBL composed from borough, block and lot, falling back to address and borough when block and lot are blank (00000/0000) or the borough is NOT NYC (#93). Agency (FDNY, DSNY, DOHMH) is decided per row; DOB-issued tickets are excluded to avoid duplicating ECB.
nyc-311	311 requests erm2-nwe9	Incremental. Resolves by the dataset's bbl, falling back to address and borough when it is blank (#93). Filtered to DEP, DSNY, FDNY, DOT. HPD and DOB requests are left out: each becomes an HPD or DOB complaint, which hpd-complaints and dob-complaints load with the agency's status and the apartment (#206). Updates several times a day.
dof-liens	DOF tax lien sale list 9rz4-mjek	The lots DOF noticed for a tax lien sale. One event per lot (#205): the up to five lists per sale year (90, 60, 30, 10 Day Notice, Final Sale) are one case, so the rows are grouped by lot and the newest list is the status detail. OPEN while the lot is on the newest sale year's lists, CLOSED when only older years list it. Resolves by BBL composed from borough, block, lot. Weekly full pull in records (264k rows, last published 2025-06); no delta, no backfill.
Enforcement events (8)	hpd-vacate-orders, hpd-aep, hpd-omo-charges, hpd-hwo-charges, dob-safety-violations, fdny-vacates, dohmh-rodent-inspections, dot-sidewalk-violations	Built with the factory. Incremental by :updated_at; no backfill (the weekly records full pull is their history). A rodent delta reads the changed rows only for their job ids, then fetches each job whole. See Record feeds.

Field-level detail per dataset: NYC open data reference.

Record feeds

Most NYC Open Data is not an event. A boiler inspection report, an elevator, an HPD registration contact or a building's place on the CONH pilot list has no open/closed status, so it does not belong in public_events. Record feeds write those to four tables instead (migration 20261010090000_building_records.sql):

Table	Question it answers	Written by
building_filings	Paperwork was filed	hpd-registrations, hpd-bedbug-reports, dob-elevators, dob-boilers, dob-facades, dob-now-permits, dob-bis-permits, dob-certificates-of-occupancy, dob-elevator-details, dohmh-water-tanks, dohmh-cooling-tower-inspections, dohmh-water-tank-results, dep-boiler-permits, ll84-benchmarking, dof-exemptions, dof-abatements, fdny-mandatory-inspections, fdny-risk-inspections
building_assets	Something exists there	dob-elevators, dob-boilers, dohmh-cooling-towers, dohmh-water-tanks, fdny-building-summary
building_parties	Someone is responsible	hpd-registration-contacts
building_designations	The building is on a city list	hpd-conh, hpd-jurisdiction

There are 23 record feeds. Each is a RecordFeedDefinition (records.ts) in one module per agency in feeds/: hpdRecords.ts, dobRecords.ts, dohmhRecords.ts, dofRecords.ts, fdnyRecords.ts, and the single-dataset depBoilerPermits.ts and ll84Benchmarking.ts. RECORD_FEEDS in feeds/index.ts lists them, ordered by inLoadOrder (a loadAfter naming an unknown feed, or a cycle, throws at startup). A record feed returns zero or more records per source row: an elevator row is the device plus its latest periodic, CAT1 and CAT5 filings. persistRecords resolves buildings exactly as the event path does (BIN, then BBL, then normalized address; a real BIN with no building creates one; anything else goes to unlinked_events with event_type record:<table>). The differences:

• One record, several buildings. An LL84 property lists several BINs and BBLs; the record lands once per building. Rows are unique on (source_id, building_id).
• A record with no keys resolves through something already loaded (resolveVia, a tagged union; VIA_RESOLVERS in records.ts holds one resolver per kind: its key, its lookup and the quarantine reason for an unmatched record):
  • Contacts resolve through their registration (kind: "registration"). HPD contacts carry no building keys, only a registration id, and land on every building that registration covers (from hpd-registrations filings, or buildings.hpd_registration_id), so the feed declares loadAfter: ["hpd-registrations"].
  • Device details resolve through the device (kind: "device"). DOB NOW elevator details (dob-elevator-details) carry no BIN, only the device number, and land on the building of that device as dob-elevators loaded it (lookup index from migration 20261011090000), so the feed declares loadAfter: ["dob-elevators"]; a device not yet loaded quarantines as NO_BUILDING_MATCH_FOR_DEVICE.

Placeholder BINs (1000000 to 5000000, DOB's "BIN unknown") are ignored by the shared resolver for every feed, event or record (isPlaceholderBin in buildingResolver.ts): the row falls back to its BBL or address, and a placeholder never auto-creates a building.

A field gets a typed column when a deadline is computed from it or a screen filters on it, goes in details when a screen only shows it, and is left in the city's dataset otherwise. building_parties names private individuals and is readable by staff only; the other three are readable by every signed-in user. Equipment rows deliberately carry no owner names. The building page reads all four through get_building_records(building_id), one round trip under the caller's RLS.

The 8 enforcement event feeds (ENFORCEMENT_EVENT_FEEDS in feeds/index.ts) are open until the agency closes them and each has a real :updated_at. All are built with the eventFeed.ts factory. Six are in enforcementEvents.ts: hpd-vacate-orders (tb8q-a3ar), hpd-aep (hcir-3275), hpd-omo-charges (mdbu-nrqn) and hpd-hwo-charges (sbnd-xujn) for HPD's emergency repair charges, dob-safety-violations (855j-jady, DOB NOW's penalties for missed periodic filings; the 755k it carried over from BIS, numbered like 120210AEUHAZ100359, are skipped because dob-violations loads them as V120210AEUHAZ100359, #207) and fdny-vacates (n5xc-7jfa). dohmh-rodent-inspections is in dohmhRodentInspections.ts and dot-sidewalk-violations in dotSidewalkViolations.ts.

Current state only. Several source rows can be one record: every report for a boiler, every bedbug period or LL84 year for a building, every FISP submission for a building, every yearly report for a water tank. Each record carries a source_date (filing, inspection or report date), and the upsert keeps the newest per (source_id, building_id), so an older row arriving later in a pull never replaces a newer one. Older history is one live fetch away. Every upsert also stamps last_seen_at (migration 20261010110000).

Loading. ingest records [feed...] is the full sync: record feeds stream the whole dataset a page at a time, paged by row id (keysetFetch: each page asks for rows after the last :id, which costs the same at row 4M as at row 0, where a deep $offset slows and, on a bad night, returns an early empty page), check they received the dataset's own count(*), and only then sweep, deleting that feed's rows not seen since the pull began; a failed or short pull deletes nothing. Event feeds run through runCitywideFeed as a full pull (which tombstones events no longer published) and set the delta watermark. The command ends by rebuilding building_flags and marking the map stale. ingest sample still loads the newest N rows of each, for a quick look.

The 23 record feeds have no delta. The 8 enforcement event feeds run both ways: in the nightly delta, which reads the small :updated_at batch each dataset stamps per day (live-checked 2026-10-11: HPD vacates ~10 rows a day, DOB safety ~300, HPD emergency repairs 200–900, rodent ~1,000, sidewalks ~50, AEP once a month), and in the weekly full pull, which tombstones what the city dropped and moves their watermark (migration 20261011120000). A weekly systemd timer on the ingestion box, complied-ingest-records (Sundays 15:00 UTC), runs ingest records as a full sync with the sweep, so a record the city has dropped is removed. Before an event feed joins the nightly registry, check its :updated_at behaviour. On the map, the enforcement event feeds are layers (each prefix has a map_event_sources row giving its department and category, migration 20261011130000), and the record tables feed the "Building has" filter through building_flags, one row per building rebuilt by refresh_building_flags() after each load (see map).

Datasets with repeated rows

For these 14 feeds (12 record feeds and the rodent and sidewalk event feeds), what makes a row unique was measured on the live data on 2026-10-11. Most repeat themselves: a permit once per renewal, a tax benefit once per roll, an inspection once per violation. Every dataset is loaded in full; what is collapsed is that repetition. The table is the card for each: what one record is, which source row wins, and what was found.

Feed	Dataset	One record per	Current (newest wins by)	Found
dob-now-permits	rbx6-tga4	work_permit	issued_date	A renewal is a new row under the same permit with a higher sequence_number; 224,329 rows are verbatim copies of 93,182. 542 "Permit is not yet issued" rows are skipped.
dob-bis-permits	ipu4-2q9a	job__ + job_doc___ + permit_type + permit_subtype	issuance_date	permit_si_no is unique per row, but 2.0M rows are renewals of 736k permits. No BIS job number appears in DOB NOW's permits, so the two never describe one permit.
dob-certificates-of-occupancy	bs8b-p36w + pkdm-hqz6	job	issue date	One feed over both datasets: a BIS job keeps getting COs after the move to DOB NOW (66,474 of DOB NOW's 82,860 rows are BIS jobs), so both write dob-co:<job>. bs8b's bin holds placeholders where bin_number is real. Neither publishes a temporary CO's expiry: expires_on is an estimate, the issue or renewal date plus 90 days, DOB's typical TCO validity (no different period for new buildings or alterations); null on a final CO. The records card reads it as "likely expired" / "expires soon".
dob-elevator-details	juyv-2jek	device_id	latest job filing number	One row per device per job filing and no date column; resolves through the dob-elevators device.
dohmh-cooling-tower-inspections	f9wb-g8mb	tower + inspection_date	(history kept)	One row per violation: rows are grouped by system_id and folded per inspection.
dohmh-water-tank-results	rytv-g5ui	BIN + activity_year + activity	(history kept)	Self-reported rows without a violation restate dohmh-water-tanks' tank reports and are skipped.
dep-boiler-permits	f4rp-2kvy	applicationid	—	Unique. Different paperwork from DOB's boiler inspections; both stay.
hpd-jurisdiction	kj4p-ruqc	buildingid	—	Unique. 18,745 rows resolve by BBL (no usable BIN).
dof-exemptions	muvi-b6kx	lot (or condo) + exmp_code	newest year × period of that lot	Listed again every roll; exmp_seq changes between rolls, so rows are grouped per lot and summed per code. Condo units (1.26M rows) are grouped by condo_number onto the billing lot via the Digital Tax Map's condo list (p8u6-a6it).
dof-abatements	rgyu-ii48	lot (or condo) + tccode/tcsubcode	newest taxyr × quarter	Listed every quarter; grouped like the exemptions, condo units by condoid.
fdny-mandatory-inspections	kfgh-h6re	insptn_ops_detail	(history kept)	No outcome field: inspection history, not certificates. 4,560 rows carry a date in the type column.
fdny-risk-inspections	itd7-gx3g	insptn_ops_detail	(history kept)	733 ids appear twice. Shares no ids with the mandatory dataset.
fdny-building-summary	nvgj-hbht	BIN + system (sprinkler / standpipe)	—	One row per BIN, a March 2019 snapshot. Writes an asset only for a system the row names (Siamese connection count > 0), never a "none"; is_active stays null. No fire-alarm data.
dohmh-rodent-inspections (event)	p937-wjvj	job_id	—	A job is one case on one lot (initial → compliance → baiting); its rows fold into one event, open while the latest initial/compliance failed. 16,396 rows with no job are an event each.
dot-sidewalk-violations (event)	6kbp-uz6m	violationid	—	Open until vdismissdate; 2100-01-01 means "not yet". bblid is DOT's lot number, turned into a BBL per page from i642-2fxq.

Three engine pieces make this possible, all in socrata.ts and all usable by any feed: keysetFetch (row-id paging, optionally grouped by a column so a feed sees every row of a job, tower or lot together; a group never splits across pages), concatPlans (several datasets or passes as one pull, swept together) and mapPages (a lookup applied to each page before normalize). A record feed that needs them sets plan(deps); an event feed returns one from fetchSince. A grouped feed's normalize receives a RowGroup, read with rowsOf; groupRows builds the same groups from a scoped fetch or a sample.

Fines from the DOHMH tower and tank results are OATH summonses: their outcome is in oath-hearings, so the results carry the summons number rather than a second open/closed status.

Sync state: two cursors per feed

feed_sync_state holds one row per feed and answers "where is this feed up to", which sync_runs cannot.

Value	Meaning	Moves
delta_watermark	Everything changed at or after this moment is loaded	Forward, nightly. Written by a complete run, and during a stamp-ordered delta at each settled stamp.
backfill_cursor	Everything dated at or after this day is loaded	Backward, one window per chunk, until it passes backfill_floor (the dataset's earliest record, probed once).

backfill_status is pending, running, undated (the dated walk is done and the undated sweep below is under way), complete, or not_applicable (feeds with no backfill spec, because the nightly full pull already covers the dataset). The two cursors start together and walk apart; once the backfill completes, only the nightly delta runs, with no special case.

A backfill chunk:

1. Reads the cursor and backfill_window_days.
2. Probes count(*) for the window and halves or widens it so it lands between 20,000 and 150,000 rows. Window size therefore tunes itself per feed.
3. Fetches, normalizes and persists that window (no tombstoning), then writes the new cursor and row counter.

The cursor advances only after the window's rows are durably written, so a crash or Ctrl-C just reruns the same window. Direction is newest first: recent, likely-open violations arrive before decade-old closed rows. A window is written as a range (>= / <) on timestamp and compact YYYYMMDD columns, and as LIKE / IN patterns on DOB complaints' MM/DD/YYYY text, which no range comparison can sort; the floor of such a column is found by counting rows per decade, then per year.

The undated sweep

Rows whose date column is null or a placeholder match no window, so once the cursor reaches the floor the feed moves to undated and sweeps them (fetchUndated). These are not edge cases: about 810k HPD violations have no novissueddate (almost all class I "informational" notices, ~226k of them open), along with ~64k OATH tickets (most of which normalize drops as non-FDNY/DSNY/DOHMH) and a handful of DOB rows using the string "0".

The sweep is chunked and resumable like the walk, but on a keyset of Socrata row ids instead of dates: each chunk fetches up to 150,000 undated rows ordered by :id, after backfill_undated_after, then stores the last id written. A keyset page is always a clean prefix, so a short page never skips rows. Only a chunk whose closing count(*) of undated rows past the cursor is zero marks the feed complete. A failed fetch or count leaves the state as it was, so ingest backfill <feed> just retries.

Scheduling and where things run

Job	Runner	Details
Nightly delta	systemd timer complied-ingest-delta on the ingestion box (complied-ingest, EC2 us-east-1), 08:10 UTC, running run-delta-daily.sh	Runs ingest delta --all --since-days=45 over a direct Postgres connection, then ingest status: the nine violation and complaint feeds and the eight enforcement event feeds. It does not chain a map export. When any feed's run was not complete, delta ends with a summary of the held feeds (watermark, its age, the first error) and exits 1, and the unit fails, so the job's Healthchecks.io check alerts.
Weekly records	systemd timer complied-ingest-records on the ingestion box, Sundays 15:00 UTC, running run-records-weekly.sh	Runs ingest records: the full sync, with sweep, of the 31 weekly feeds (23 record feeds, 8 enforcement event feeds). Log: /var/log/complied/records.log.
Weekly identity	systemd timer complied-ingest-identity on the ingestion box, Saturdays 04:00 UTC, running run-identity-weekly.sh	Runs ingest identity: Layer 1, the citywide Building Footprints + PLUTO load, writing only changed rows. Log: /var/log/complied/identity.log.
Manual delta, backfill, redrive, identity	The ingest/ CLI	Direct Postgres connection: no wall-clock or memory ceiling. Restricted to whoever holds the connection string; there is no HTTP endpoint that triggers a sync.

The three timers are the only schedulers of public-data ingestion; their unit files are versioned in map-service/deploy/ec2/ and installed by install.sh. The retired sync-orchestrator edge function and its pg_cron job are gone (20261010120000_retire_sync_orchestrator.sql). Migrations: 20260903083457_nightly_delta_sync_per_feed.sql, 20260903090000_feed_sync_state.sql.

The ingestion CLI

The CLI is its own package, ingest/ (citty for commands, @clack/prompts for the menu). Run it from ingest/ as npm run ingest -- <command>. With no arguments at a terminal it opens a menu that walks action → feeds → options → confirm, prints the equivalent command line, and runs that same command. It writes to INGEST_DATABASE_URL, else TEST_DATABASE_URL, else the local stack, and prints the target before every command.

Command	Effect
status	Per feed: rows loaded, percent backfilled, cursor, watermark, last run, quarantined total. Run this first, always.
backfill [feed...] [--all] [--chunks=N] [--max-rows=N] [--batch-size=N]	Runs N windows per feed and stops; with no feed at a terminal, a multi-select picker.
delta [feed...] [--all] [--since-days=45]	Forward pull, then the rollups; the only command that moves delta_watermark. delta --all is the full citywide Layer 2 + 2b run.
redrive [--limit=50000]	Re-normalizes quarantined rows from raw_data, re-resolves against the current buildings table, promotes matches into public_events, and deletes the promoted quarantine rows. Safe to repeat.
backfill-units [--minutes=5] [--limit=N]	Links unit ids on events that landed before unit auto-linking existed, from each row's stored unit_label.
merge-units [--apply] [--building=ID]	Folds units that are one apartment spelled differently (same apt_key) into one: picks the unit most records point at, repoints every unit_id reference, logs each fold in unit_merges, deletes the rest. A dry run (counts per table, a sample, skipped groups) unless --apply. Skips a group where two units hold the same per-unit tenant record.
refresh	Runs the Layer 2b rollup, map grid and building flags rebuild, and marks the map stale, so the UI reflects a session's loads.
sample [feed...] [--limit=1000]	Loads the newest N rows of each weekly feed (the record feeds and the enforcement event feeds), grouped as the full pull groups them (groupBy). Contacts are fetched for the registrations already loaded, not the newest contacts. Logs to sync_runs; leaves feed_sync_state alone.
records [feed...]	Full sync of the weekly feeds. A complete record-feed pull deletes that feed's rows not seen since it started; an event feed's full pull tombstones and then sets its watermark.
identity	Layer 1: the citywide Building Footprints + PLUTO identity load. Writes only changed rows, never clears a known value with a missing one, marks the map stale when anything changed, and logs to sync_runs (feed_id identity).
scoped	Every scheduled feed, scoped to the buildings already in the table (Layer 3/4 shape; for a small database only).
seed-buildings [--bins=…] [--tenant=…] [--client=…] [--with-oath]	Loads a few real buildings with their full history and tracks them for a tenant, for testing against real data.

A mistake in what was asked for (unknown feed, a non-numeric --chunks) fails before anything connects. The CLI imports the shared ingestion logic from domain/src/ingestion/ through one file, ingest/src/domain.ts; it adds only the Postgres transport and the operator surface.

Operational guidance is in the hpd-ingestion-backfill skill. See local development for the warning about the local database holding the real citywide data.

Quarantine

Rows reach unlinked_events with a reason: NO_BUILDING_MATCH_FOR_BIN, _BBL, _ADDRESS, NO_RESOLUTION_KEYS, or BUILDING_CREATE_FAILED. Two exits exist:

• ingest redrive promotes rows that resolve after buildings has grown (the common case for 311 and OATH).
• resolve_unlinked_event(p_id, p_building_id) marks one row resolved by hand; it is restricted to platform operators.

Adding a feed

An event feed

Use this for a dataset with open/closed status and a real per-row :updated_at.

1. Build the feed with the factory in eventFeed.ts: buildingEventFeed when one row is one event, groupedEventFeed when the rows sharing a column are one event (a rodent job). A spec gives the id, sourceIdPrefix (unique across feeds), agency, datasetUrl and a normalize returning the event's own fields and id. Options replace a default: binField, keys (the building keys), idFields (for the Source record panel), fetchRows (the scoped fetch) and fetchSince (the citywide fetch; DOT sidewalks look each row's lot up before normalizing). Put it in the agency's module, or a new <name>.ts in feeds/.
2. Write a FeedDefinition by hand only when the factory cannot express the feed: a stable id, a sourceIdPrefix, possibleAgencies, the datasetUrl ingest sample reads, fetchSince(sinceIso, deps) returning a FetchSincePlan ({ incremental, stream(onPage) }, usually built with streamedFetch), and normalize(raw) returning a NormalizedEvent (or null to skip a row). Page with socrataUrl under a total $order (a unique column, or :id as a tie-break); $offset paging over a non-unique order can skip rows. Parse dates with feeds/dates.ts.
3. Only trust :updated_at after checking how many rows each nightly stamp touches. For a large dataset, add backfill: socrataBackfill({ datasetUrl, dateField, format, extraWhere }); extraWhere must repeat the feed's permanent filter so a backfill loads exactly the slice the delta keeps current. A feed whose dataset is small omits backfill.
4. Map the status vocabulary through statusNormalization.ts; keep the agency's own wording in statusDetail.
5. Export it from feeds/index.ts. An enforcement event goes in ENFORCEMENT_EVENT_FEEDS, which puts it in the nightly and weekly registries; any other event feed goes in FEED_REGISTRY in ingest/src/feeds/registry.ts.
6. Add a migration that seeds a feed_sync_state row (not_applicable if it has no backfill), as 20261011120000_enforcement_events_nightly.sql does.
7. To show it on the map, add its map_event_sources row in a migration (20261011130000_map_event_sources.sql; see map).
8. Test against real rows saved from the live dataset in domain/tests/ingestion/fixtures (fetch is injected through FeedFetchDeps.fetchJson, so tests never call Socrata), then check the load with ingest status.

A record feed

Use this for a dataset with no open/closed status: filings, equipment, contacts, list membership. It needs no migration: a record feed has no delta or backfill, so it keeps no feed_sync_state row; each run is logged to sync_runs.

1. Define a RecordFeedDefinition (records.ts) in its agency's module (hpdRecords.ts, dobRecords.ts, dohmhRecords.ts, dofRecords.ts, fdnyRecords.ts); a new agency gets a new <agency>Records.ts. It gives id, sourceIdPrefix, datasetUrl, agency, order and normalize. List it in RECORD_FEEDS in feeds/index.ts.
2. normalize returns zero or more NormalizedRecords, each into one of the four tables, with a sourceDate so the newest of several source rows wins. Decide each field's home by the rule above: typed column, details, or left in the city's dataset. Put no person's name in a filing; people go in building_parties.
3. Set groupBy when several rows are one item (every row of a tower or lot). normalize then receives a RowGroup, read with rowsOf; groupRows groups a scoped fetch or an ingest sample the same way.
4. Set resolveVia on each record whose row has no BIN, BBL or address, with loadAfter naming the feed it resolves through; inLoadOrder then runs that feed first. Pick registration or device. A new kind is a member of the ResolveVia union plus a VIA_RESOLVERS entry.
5. Set plan(deps) only for a multi-dataset pull or one that looks something up per page: compose keysetFetch, concatPlans and mapPages from socrata.ts. Otherwise the default pull is keysetFetch grouped by groupBy, else one pass in order.
6. Nothing else wires the run. runRecordFeed.ts pulls the dataset, and RecordDb.sweepUnseen deletes the feed's rows not seen since the pull began, only after a complete pull. The weekly ingest records picks the feed up from RECORD_FEEDS.
7. A "Building has" map fact is a separate step: see map.
8. Test with real-row fixtures in domain/tests/ingestion/fixtures, in the style of recordFeeds.test.ts.

No new tables are needed per agency: an event feed adds rows to public_events and, where useful, summary columns on buildings; a record feed adds rows to the four record tables.

Tests

domain/tests/ingestion/ covers the resolver, dedupe, status mapping, the feed normalizers against real-row fixtures, the Socrata pager, runFeed and runBackfillChunk against an in-memory Db. db-tests/tests/ingestion.test.ts covers the SQL side: constraints, the normalized_address trigger, quarantine RPCs, buildings identity, the compliance summary, and ops views.