Complied — Ingestion Layer Architecture

Decision: Store NYC agency data in the local database. Sync on a schedule. Do not live-fetch Socrata when the map pans or when someone browses violations.

Core model (three tables, not four):

Table	Role
`buildings`	Citywide identity spine — every BIN, searchable, adoptable
`public_events`	Every agency violation/event citywide — one row per event
`tenant_buildings`	Portfolio scope — "this tenant tracks this building for this client"

There is no separate citywide_lead_buildings table. The map, search, adopt flow, and project creation all read from buildings. Violation detail lives in public_events. "Tracked vs prospect" is a query filter (tenant_buildings join), not a separate events store.

This document is both the target architecture and the build plan: what to build, what's already solid in domain/src/ingestion/, what's broken and needs fixing first, and what we verified against live NYC Open Data (not assumed) along the way. The live-Socrata dataset facts below were verified 2026-08-25; the bug list and build order were re-verified against the current code and the first real citywide run on 2026-09-02.

Where it stands: Layers 1 and 2b are done — 1,101,504 buildings. Layer 2 is built and has run once (2026-08-30) but is stuck at ~2.6M of ~35M available public_events rows, and cannot resume as written. See B1 and "Layer 2 backfill."

Sync rhythms

Layer	What	Cadence	Style
1 — Buildings identity	`buildings` (Building Footprints + PLUTO)	Once + weekly, or on-demand per building (see Layer 1)	Seed / refresh identity + PLUTO facts
2 — Citywide sync	`public_events` (+ auto-create `buildings`)	Once per day	`:updated_at`-filtered where verified, full pull otherwise
2b — Summary refresh	`buildings` compliance columns	Once per day (after Layer 2)	Roll up from `public_events`
3 — Tracked portfolio	same `public_events`, faster cadence	Only where the source data itself updates faster than daily — see Layer 3	Batched `IN()` per tracked BIN
4 — On adopt	`tenant_buildings` + immediate sync	Immediate	One building
5 — Obligations	computed deadlines	Daily	No Socrata

The layers in plain language

Layer 1 — Buildings identity → `buildings`

Two datasets, not one. PLUTO (Primary Land Use Tax Lot Output) alone is not enough: PLUTO is organized around tax lots, and has no building-ID field at all — the whole system looks buildings up by BIN. One tax lot can also hold several buildings (attached houses, a campus), so a lot's location isn't a building's location either. That gap was caught by reviewing the map plan against the data model, not assumed.

NYC Building Footprints — has the real BIN and building outlines.
PLUTO — has year built and unit count, matched to Building Footprints on BBL. Year built is the one thing nothing else gives you for free, and it's the whole reason this exists: "was this building built before 1978" (the lead-paint cutoff) can't be answered without it.
When: Once initially, then weekly — or see the on-demand alternative below.
Why it matters beyond map-browsing: resolveBuilding() (the shared building-matching logic every feed uses) only matches BIN → BBL → address against rows that already exist in buildings. Feeds that carry a real BIN (HPD, DOB, ECB, litigation) can bootstrap their own building row on the fly via Layer 2's auto-create. But 311 and OATH hearings carry no BIN or BBL at all — only an address — and the resolver deliberately never guesses a building from an address alone (see "Why runFeed exists," below, on why that guess was removed). Without buildings pre-filled, every 311/OATH row for a building nobody's touched yet goes straight to quarantine and stays there. Pre-filling buildings is what makes the multi-agency enforcement layer work uniformly across all feeds, not just the ones that happen to carry a BIN.
Not for: Violations — neither dataset has enforcement data.

Decision (settled 2026-08-27): load the full citywide dataset. The project is on the paid Supabase plan (~$25/mo), so the old free-tier 500 MB cap no longer applies and the ~500–600 MB citywide footprint is fine. The two alternatives previously weighed here — scoping Layer 1 to pre-1978 residential only, and skipping the preload in favour of on-demand single-BBL PLUTO lookups — are both rejected: citywide browse is exactly what makes Complied Map a prospecting tool, and no-BIN feeds (311/OATH) need buildings pre-filled to resolve at all. Do not re-open.

Layer 2 — Citywide sync → `public_events`

Daily job pulls all agency feeds citywide.

When: Once per day, upserts only.
Critical rule: if a violation's BIN has no buildings row yet, auto-create a minimal building row first (BIN-only — never guess from an address), then upsert the event.

Fetch shape — verified live, not assumed: rather than looping over every building asking "anything new for BIN 12345?", ask each Socrata dataset "what changed since my last run?" via its hidden :updated_at system field ($where=:updated_at > '<last run>'). Checked directly against all 8 live feeds on 2026-08-25:

Feed	Total rows	Updated in last ~36h	Distinct update timestamps	`:updated_at` usable as a filter?
HPD violations (`wvxf-dwi5`)	11,186,624	78,803 (0.7%)	1 (one nightly batch)	Yes
HPD complaints (`ygpa-z7cr`)	16,263,673	32,949 (0.2%)	1 (one nightly batch)	Yes
HPD litigation (`59kj-x8nc`)	240,163	0	0	Untested at scale — no activity in window
DOB violations (`3h2n-5cm9`)	2,476,378	3	1	Yes, low daily volume
DOB complaints (`eabe-havv`)	3,125,527	3,125,527 (100%)	1	No — full nightly replace. Pull the whole table each day instead.
ECB violations (`6bgk-3dad`)	1,831,766	0	0	Untested at scale — no activity in window
NYC 311 (`erm2-nwe9`)	large	556,417	9 (several updates through the day)	Yes, and updates intraday (see Layer 3)
OATH hearings (`jz4z-kudi`)	—	1,019,364	inconsistent result	Needs a clean manual re-check before relying on it
DOF liens (`9rz4-mjek`)	—	0	0	Matches known ~9-month staleness of this dataset

Rule before shipping each feed: run $select=count(*)&$where=:updated_at > '<yesterday>' against the live dataset and compare to the total row count. Small fraction → safe to use as the daily filter. Close to 100% → that publisher does a full replace; page through the whole table each day instead (still fine at daily cadence, just not an incremental filter).

Why daily: map browse and project intake need reasonably fresh open/closed status without live API calls at click time.
DB-side batching is needed regardless of fetch mode — a smaller, filtered Socrata response still means the same number of rows need building resolution and a database write each. Done: findByBins/upsertEvents resolve and write in bulk per chunk.

Layer 2b — Summary refresh → `buildings` (denormalized counts)

After the citywide sync, a rollup job writes summary columns onto buildings (or a sibling building_compliance_summary table keyed by building_id):

open_hpd_lead, overdue_hpd_lead, open_dob, open_ecb, …
last_compliance_sync_at

Why: the map needs fast pins ("3 open lead, 1 overdue") without aggregating millions of public_events rows on every pan.
This replaces the old citywide_lead_buildings table — same job, different target column.

Do not store raw violation rows on buildings. Identity + summary counts yes; full event records no. Those belong in public_events.

Layer 3 — Tracked portfolio → same `public_events`, faster cadence where it actually helps

The original idea: buildings in tenant_buildings get the same sync pipeline re-run every 6 hours, all agencies. Refined after checking the live data above: for most feeds (HPD, DOB, ECB, litigation, liens), the source itself only publishes once a day or less — a 6-hour recheck of a once-daily-batched dataset returns the exact same data four times, for zero extra freshness. It's not a different pipeline worth building for those feeds; the daily citywide sync in Layer 2 already has everything, tracked or not.

Where it genuinely earns its keep: NYC 311 demonstrably updates multiple times within a single day (9 distinct :updated_at stamps across ~36h, not 1) — a real live-service-request feed, not a scheduled compliance extract. For a tenant's actively-managed buildings, checking 311 (and, pending a cleaner re-check, possibly OATH) more often than once a day genuinely surfaces newer information sooner. If that intraday freshness matters to the product, scope the faster cadence to those specific feeds only — not all 8 — for tracked BINs.

Not a different table — tracked buildings read the same public_events as prospects.
Fetch shape: batch the tracked-BIN list into chunked bin IN(...) requests instead of one request per BIN — verified live: $where=bin IN('1059261','1000000','1007511') returned matches for all three BINs in a single call. For a few hundred tracked buildings, that's a handful of requests per feed, not hundreds.
A pure "did the daily job actually run" safety net is a legitimate want for a compliance product with legal deadlines — but that's better solved with monitoring/retry on the one daily Layer 2 job than by running a whole second pipeline on a timer for feeds that never move intraday anyway.

Layer 4 — On adopt → `tenant_buildings` + immediate sync

When a user adopts a building from the map:

Insert tenant_buildings row (building is now "ours").
Immediate per-BIN sync → public_events (all feeds).
Enrich buildings facts (HPD registration, unit count, etc.).

The building row already exists (from Layer 1 or auto-created by Layer 2). Adopt adds the tracking relationship, not a new building identity. This is a UX need (instant feedback on the action just taken), independent of whether the underlying data updates faster than daily — it stays, regardless of what Layer 3 ends up covering.

Layer 5 — Obligations → computed deadlines

When: Daily.
Source: domain engine over local public_events + building facts.
No Socrata.

Why one shared `runFeed` engine, not eight custom scripts

Every one of the 8 datasets is shaped completely differently — different field names, different status vocabularies, different ways of identifying a building (BIN vs. BBL vs. address). runFeed (domain/src/ingestion/runFeed.ts) is the one shared piece of code that does the same five things regardless of which agency the data came from:

Fetch (feed.fetchRows/fetchSince) and normalize (feed.normalize) — the only two things each feed supplies itself.
Resolve which building the row belongs to (resolveBuilding: BIN → BBL → normalized address).
Save it, or set it aside for manual review if no building matched (upsertEvent / quarantineEvent).
Notice when a previously-open event has quietly disappeared from the feed (NYC's way of saying "resolved") and close it — or resurrect one that reappeared (tombstoneMissing).
Write down what happened (logRun).

Without this shared engine, the same five-step logic would need to be hand-written eight times, and any fix — like the bugs below — would need applying in eight places instead of one. resolveBuilding deliberately never auto-creates a building from an address guess; the old system's fuzzy ILIKE address-substring match caused real violations to get attached to the wrong building, and the hard unlinked_events quarantine constraint exists precisely so a genuinely ambiguous row gets flagged for review instead of silently mis-linked. That invariant is why Layer 2's auto-create is BIN-only, never address-based.

Known implementation bugs

The original 14-bug list from 2026-08-25 has been worked through. Bugs 1–4, 6–8 and 14 are fixed — scope-aware tombstoning (RunScope in types.ts), paginated loadCandidates and tombstone sweep, bulk resolve/upsert (findByBins / upsertEvents), loud truncation (FetchOutcome.truncated → outcome: "partial", sweep skipped), the three status-field fixes (complaint_status, ecb_violation_status, violation_category), and real-row test fixtures in domain/tests/ingestion/feeds.test.ts. Don't re-report them.

What follows is the list as of 2026-09-02, re-verified against the current code and against the first real citywide run (batch of 2026-08-30, sync_runs).

The one thing blocking a complete Layer 2 load

B1 — the fetch is capped, and the watermark that should backfill it is poisoned. These are one problem, not two, and together they mean the citywide load stops permanently at ~2.6M of ~35M available rows.

fetchAllPages (feeds/socrata.ts) stops at SAFETY_CAP_OFFSET = 500000 plus one final 50k page → an exact 550000. Four of eight feeds hit it on 2026-08-30 (DOB violations, ECB, OATH, 311); a fifth (dob-violations) fetched 555,199 because its own paging shape differs slightly.
lastSuccessfulRunStartedAt (sync-orchestrator/index.ts and the local runner) derives the incremental sinceIso from any run with outcome = 'success', regardless of how much that run actually covered. The 2026-08-30 run had no prior watermark, so it fell back to a 45-day window — and three feeds finished that 45-day window cleanly and were logged success. Every run from now on asks only "what changed since 2026-08-30," so the 11M rows of HPD history before that date are never requested again.

Truncated runs log partial and correctly don't advance the watermark — so the four capped feeds will keep re-pulling the same 45-day window forever instead. Neither branch backfills.

Fix (designed, not yet built): a chunked, resumable backfill with an explicit cursor. See "Layer 2 backfill" below. It replaces the derive-from-sync_runs watermark with two explicit per-feed values, makes each fetch small by construction (so the 500k cap stops mattering), and makes bug B2 moot.

B2 — everything buffered in memory (was bug 5). Citywide, millions of raw-payload objects at once. The hpd-litigation run on 2026-08-30 failed partway with could not resize shared memory segment … No space left on device — the Postgres side of the same pressure. Not fixed directly; the backfill's bounded chunks make it stop mattering.

Real, lower-priority

B3 — 391,947 quarantined rows are never retried. unlinked_events has grown to nearly 400k rows, most from the 2026-08-30 run (OATH quarantined 203,904, 311 quarantined 134,747 — the two no-BIN feeds). Layer 1's citywide buildings table (1,101,504 rows) landed after some of those quarantines, so a large share would resolve today with no new fetching at all. Needs a re-drive job that re-runs resolveBuildingsBulk over unlinked_events and promotes what now matches.

B4 — ingestion is nominally gated on manage_tenant, a tenant permission — the wrong category of check for a system job. It does not block scheduling: _shared/auth.ts's authorize() returns ok immediately when the bearer equals SUPABASE_SERVICE_ROLE_KEY, before any permission lookup, so a cron caller passing the service-role key already authenticates today. Cosmetic/hygiene only — the permission name misdescribes who the function is really for.

B5 — no scheduler exists. Feeds declare a cadence; nothing reads it. Intentional so far.

B6 — address-matching where a real building ID is in the row. HPD complaints' fetchRows scopes by house-number+street though every row carries bin; 311's row type has no bbl field despite the dataset populating it on most rows; OATH's comment claims no BIN/BBL, worth re-verifying since dofLiens.ts shows block/lot can compose into a BBL. Accuracy and request-volume, not corruption — but it is a direct cause of B3's quarantine pile.

B7 — tax liens are the wrong dataset. 9rz4-mjek is the lien-sale-eligibility roster, not actual liens: no amounts, no dates, ~9 months stale. 213,673 rows of it are already loaded. If liens matter, source a different dataset; if not, gate the feed off.

Layer 2 backfill — chunked, resumable, cursor-driven

Status: designed 2026-09-02, not yet built. This is the fix for B1/B2 and the shape the citywide load actually runs in.

Two cursors per feed, moving in opposite directions

Today's single derived watermark tries to answer two different questions with one value, which is why it fails. Split them into an explicit feed_sync_state table, one row per feed_id:

Value	Means	Moves
`delta_watermark`	"everything changed at or after this moment is loaded"	forward, once a day, forever
`backfill_cursor`	"everything dated at or after this day is loaded"	backward, one window at a time, until done

They start together at the day the backfill begins and walk apart. When backfill_cursor passes the dataset's earliest record the feed is complete, and only the daily delta keeps running — no seam, no special case, no code that has to know a backfill ever happened.

Crucially, delta_watermark is written explicitly by a run that genuinely covered its window, not derived from sync_runs.outcome. A truncated or windowed run leaves it alone. That alone kills B1's second half.

One chunk = one bounded window

Each backfill invocation does exactly one window and stops:

Read the feed's backfill_cursor (say 2026-07-01) and its window_days (say 30).
Ask Socrata how big the window is first — $select=count(*)&$where=<date> >= '2026-06-01' AND <date> < '2026-07-01'. One cheap request.
If the count is over the chunk ceiling (~150k), halve the window and re-check. If it's tiny, widen it. The chunk size tunes itself per feed — HPD violations will settle on days, DOF liens on years, without anyone hand-maintaining a table of window sizes.
Fetch that window, normalize, resolve, upsert. No tombstoning — a historical slice is not a current-state snapshot, so "not seen" means nothing here.
Write the new backfill_cursor (the window's lower bound) and the rows-loaded counter. Done.

If the chunk crashes, the cursor was never advanced and the same window simply runs again. That is the whole resumability story — no job state, no queue, no partial-progress bookkeeping.

Direction is newest-first, walking backward, so the most operationally relevant data (recent, likely-still-open violations) lands in the first days and decade-old closed rows fill in last. Feeds currently ordered by :id get switched to their real date field for backfill purposes (issue_date, inspectiondate, received_date — every feed in the Layer 2 table above has one).

Where it runs

The chunk itself is one domain function (runBackfillChunk), wired to Postgres by both existing Db implementations — nothing new is invented:

Daily delta → the deployed edge function, on a schedule. Small by definition, well inside an edge function's budget. Needs B4 fixed first.
Backfill chunks → a local CLI over a direct Postgres connection, the same surface Layer 1 already uses (db-tests/scripts/run-sync-orchestrator-locally.ts identity) and for the same reason: no wall-clock or memory ceiling. Modest chunks can also run from the scheduled edge function once the sizes are known to fit.

Deliberately no third-party job runner (Trigger.dev et al). Once the cursor is in Postgres, "who calls the chunk" is a one-line detail — cron, the CLI, or a person — and a hosted runner would add a fourth deploy target and a second copy of the domain bundle to buy nothing the cursor doesn't already provide. Long-running-job orchestration is exactly the problem the chunking removes.

The CLI

A small tsx cockpit, not a framework — feed picker, progress, and the three verbs:

ingest status                       # per feed: rows loaded, % backfilled, cursor date, watermark, last run
ingest backfill <feed> [--chunks=N] # run N windows and stop
ingest delta [feed]                 # today's forward pull
ingest redrive                      # B3 — retry quarantined rows against the now-full buildings table

With no feed argument it shows the picker. status reads feed_sync_state + sync_runs and is the answer to "where is this actually up to" — the thing sync_runs alone can't tell you today.

Function reference — what's called, why, where

Symbol	File	Called by	Purpose
`runFeed`	`domain/src/ingestion/runFeed.ts`	`sync-orchestrator/index.ts`, once per feed	Orchestrates one feed's run: fetch → normalize → resolve → upsert/quarantine → tombstone → log.
`resolveBuilding`	`domain/src/ingestion/buildingResolver.ts`	`runFeed`, per row	BIN → BBL → normalized-address lookup. Never creates. `null` triggers quarantine.
`normalizeAddress` / `normalizeBorough`	`buildingResolver.ts`	`resolveBuilding`	Canonicalizes free-text addresses so PLUTO/HPD/DOB spellings match.
`tombstoneMissing`	`types.ts` (contract) / `sync-orchestrator/index.ts` (impl)	`runFeed`, per agency, after the row loop	Closes events not seen this run, resurrects ones that reappeared. Scope-aware via `RunScope`; never runs on an incremental, truncated, or backfill fetch.
`upsertEvent` / `quarantineEvent`	`types.ts` / `sync-orchestrator/index.ts`	`runFeed`, per row	Writes a resolved row to `public_events`, or an unresolved one to `unlinked_events`.
`logRun`	same	`runFeed`, once per run	Writes to `sync_runs` — the only observability into what a run did.
`computeHpdComplaintStatus` / `computeEcbStatus` / `computeDobStatus` / etc.	`domain/src/ingestion/statusNormalization.ts`	Each feed's `normalize()`	Collapses agency-specific status vocabulary to the 3-value `StatusNorm`. Field/value mappings live-verified against real rows in `domain/tests/ingestion/feeds.test.ts`.
`fetchAllPages` / `socrataUrl`	`domain/src/ingestion/feeds/socrata.ts`	Every feed's `fetchRows`	Shared Socrata pagination (50k page size, 500k safety cap, surfaced as `truncated: true` — the cap is B1's first half; the backfill's bounded windows are how it stops mattering).
`loadCandidates`	`sync-orchestrator/index.ts`	`Deno.serve` handler	Loads the buildings a scoped run fetches against. Paginated with `.range()` past PostgREST's `max_rows = 1000`.

Three tables, three questions

Table	Question it answers
`buildings`	Who/where is this building? Address, BIN, year built, units, map pin, summary counts
`public_events`	What happened? Full violation detail — dates, status, order numbers, raw agency data
`tenant_buildings`	Who cares? Which tenant tracks this building for which client

Tracked vs prospect — same data, different filter

Prospects on map:
  buildings
  JOIN building_compliance_summary (or summary columns)
  LEFT JOIN tenant_buildings → NULL (not tracked by this tenant)

Our portfolio on map:
  buildings
  JOIN tenant_buildings → NOT NULL
  JOIN public_events for detail pages

You do not need a second events table for tracked buildings. tenant_buildings is the "something else."

What goes on `buildings` vs `public_events`

Data	`buildings`	`public_events`
Address, BIN, BBL, lat/lng	yes	no (join via building_id)
Year built, units (PLUTO)	yes	no
Open violation counts by family	yes (summary)	no
Individual violation rows	no	yes
Correct-by dates per violation	no	yes
Project links	no	yes (via project_event_links)
Raw Socrata payload	no	yes (raw_data jsonb)

Rule of thumb: if the map needs it as a number on a pin → summary on buildings. If a project or deadline engine needs it → row in public_events.

Why we moved away from a separate discovery table

The old app had citywide_lead_violations because:

public_events allowed nullable building_id — citywide upserts could stomp portfolio rows.
The map only cared about HPD lead — a separate table seemed simpler.

The rebuild fixes (1): building_id is NOT NULL, and the sync always resolves or creates a building first. That makes one citywide public_events table safe.

For (2): the map still needs fast aggregates, but those live as summary columns on buildings, not a parallel violation store. One spine, one event log, one portfolio join.

End-to-end flow

Once / weekly (Layer 1):
  Building Footprints + PLUTO → upsert buildings (identity + year-built/unit facts)
  — or, on-demand: single-BBL PLUTO lookup at the moment a buildings row is first created

Daily (citywide, Layer 2):
  For each agency feed:
    fetch rows changed since last run (:updated_at where verified, full pull otherwise)
    → auto-create buildings row if BIN missing (BIN only, never an address guess)
    → upsert public_events (with building_id)
  Then (Layer 2b):
    roll up counts per building_id → update buildings summary columns

Faster-than-daily (Layer 3, only for feeds proven to update intraday — e.g. 311):
  same pipeline, tracked BINs only, batched bin IN(...) requests

Map browse:
  buildings + summary columns
  + tenant_buildings join for ours/prospects scope
  → no external API

User adopts (Layer 4):
  insert tenant_buildings
  → immediate per-BIN sync, all feeds

User creates project:
  link events from public_events → project_event_links

Daily (Layer 5):
  domain engine over public_events + building facts → computed obligations/deadlines

Expansion path (lead-only today → all agencies tomorrow)

Layer 2 starts with HPD lead violations if you want a smaller first sync. The architecture does not change when you add DOB, ECB, 311:

More rows in public_events.
More summary columns on buildings (or summary table).
Same map query pattern.
Same tenant_buildings portfolio filter.

No new tables per agency. New feeds plug into sync-orchestrator per REQUIREMENTS §8.3.

What live APIs are still used for

Live fetching is fine for interaction, not citywide rendering:

Use case	API	Why live
"Fly to this address"	NYC Geosearch or Mapbox	One-off, user-initiated
"Search building not yet in DB"	Geosearch → BIN, or HPD MDR	Rare edge case before first sync creates the row
Optional map overlays (AEP, 311 paint)	Socrata viewport bbox	Sparse, zoom-gated toggles

The core map and violation browse do not live-fetch.

Local testing setup

Nothing beyond what's already in the repo is needed:

Local Postgres/Supabase: supabase start (per CLAUDE.md). public_events, unlinked_events, and sync_runs already have migrations under supabase/migrations/. New migrations only for Layer 1's building-identity columns and Layer 2b's summary columns.
No live NYC Open Data calls during development. FeedFetchDeps.fetchJson (types.ts:80) is already an injected dependency, so tests and local runs can pass a fixture function returning canned JSON. Those fixtures are real rows saved from the live datasets — that single change is what caught the status bugs (original 6–8) this document used to list as open.
No Supabase MCP, no linked/remote project. Point SUPABASE_URL / SUPABASE_SERVICE_ROLE_KEY at the local stack; sync-orchestrator/index.ts already just reads those from env.
Building Footprints + PLUTO are public NYC Socrata datasets, not tools or packages — the same fetchAllPages mechanism every other feed already uses.
If this repo's local Supabase stack and another project's local stack are ever run side by side, stop the one you don't want by container name, not supabase stop (it can grab the other project's default ports).

Build order

Done: original bugs 1–4, 6–8, 14 (see "Known implementation bugs"). Layer 1 — citywide Building Footprints + PLUTO, 1,101,504 buildings rows loaded. Layer 2b — summary rollup (refresh_building_compliance_summary). Layer 2 — built and run once (2026-08-30), but incomplete: ~2.6M of ~35M rows, and stuck (B1).

Remaining, in order:

feed_sync_state migration — the two-cursor table. Backfill and delta both read it; nothing else can be built first.
runBackfillChunk in domain — one bounded window, self-sizing via the count probe, no tombstoning. Unit-tested against a fake Db like every other runner.
Stop deriving the watermark from sync_runs. Replace lastSuccessfulRunStartedAt in both the edge function and the local runner with an explicit read/write of feed_sync_state.delta_watermark, advanced only by a run that covered its window. This is the half of B1 that silently loses data, so it lands with (1) even if the backfill trails.
The CLI — status / backfill / delta / redrive. status first; it's what makes the rest observable.
Run the backfill. Days of chunks, newest-first, feed by feed. HPD violations and HPD complaints first (11M and 16M rows, and the ones the product actually reads).
B3 — quarantine re-drive (ingest redrive). ~400k rows, many of which resolve today against the now-full buildings table without any fetching.
B5 — schedule the daily delta against prod. Not blocked by B4 (see above): a scheduled caller passing the service-role bearer already authenticates. B4 is a rename, whenever.
B6 (address→BIN/BBL upgrades) and B7 (the tax-lien dataset call) — accuracy cleanups that also shrink the quarantine pile at the source.
Layer 3 (311 only — the one feed proven to move intraday) and Layer 4 (on-adopt immediate sync) share the existing scoped-run machinery. Layer 5 (obligations) is explicitly out of scope for now.

Complied · ingestion architecture · source of truth · updated 2026-09-02

Sync rhythms​

The layers in plain language​

Layer 1 — Buildings identity → buildings​

Layer 2 — Citywide sync → public_events​

Layer 2b — Summary refresh → buildings (denormalized counts)​

Layer 3 — Tracked portfolio → same public_events, faster cadence where it actually helps​

Layer 4 — On adopt → tenant_buildings + immediate sync​

Layer 5 — Obligations → computed deadlines​

Why one shared runFeed engine, not eight custom scripts​

Known implementation bugs​

The one thing blocking a complete Layer 2 load​

Real, lower-priority​

Layer 2 backfill — chunked, resumable, cursor-driven​

Two cursors per feed, moving in opposite directions​

One chunk = one bounded window​

Where it runs​

The CLI​

Function reference — what's called, why, where​

Three tables, three questions​

Tracked vs prospect — same data, different filter​

What goes on buildings vs public_events​

Why we moved away from a separate discovery table​

End-to-end flow​

Expansion path (lead-only today → all agencies tomorrow)​

What live APIs are still used for​

Local testing setup​

Build order​