Data-Citizen: an agentic layer over NYC Open Data

The problem#

NYC Open Data publishes roughly 2,400 datasets through Socrata. Finding the right one is hard, knowing whether it is any good is harder, and knowing what it can be joined to is close to impossible without opening each one. Every application built on top of the portal ends up re-solving the same three problems: catalog, quality and relationships.

Data-Citizen solves them once and persists the answers, so a second application can query them instead of starting from raw endpoints. The first consumer is the app itself: an agent that searches the catalog, reads schemas, writes SoQL, and answers questions with live figures.

Architecture#

flowchart TB
    SOC["NYC Open Data (Socrata)<br/>every source row, never copied"]
    subgraph Worker["Nightly worker (Docker on a small VPS)"]
        W1["crawl catalog"] --> W2["classify geography"] --> W3["profile columns"] --> W4["score quality"] --> W5["embed changed datasets"]
    end
    subgraph Silver["Silver layer (Supabase Postgres)"]
        DS["datasets"]
        COLS["dataset_columns<br/>typed + profiled"]
        Q["quality_scores<br/>five dimensions, A-F"]
        REL["dataset_relationships"]
        EMB["dataset_embeddings"]
    end
    subgraph App["Next.js on Vercel"]
        PLAN["query planner<br/>columns allowlisted, literals quoted"]
        AGENT["agent<br/>search, schema, SoQL, answer"]
        EP["published endpoints<br/>versioned, with provenance"]
    end

    SOC --> Worker --> Silver
    Silver -->|"what columns exist, what type"| PLAN
    PLAN -->|"pushed-down SoQL"| SOC
    SOC -->|"aggregated answer, kilobytes"| PLAN
    AGENT --> PLAN
    EP --> PLAN

The one-line version: Socrata does the computing, the silver layer remembers what is worth knowing about Socrata, and an endpoint is a question somebody wrote down.

• Frontend and API on Vercel (Next.js). Catalog UI, dataset pages, a public read API, a query composer, and a streaming agent.
• Silver layer on Supabase. Datasets, typed and profiled columns (about 56,000 of them), five-dimension quality scores at full catalog coverage, relationship edges, geography classification, and one embedding per dataset.
• Middleware on a single small VPS. A nightly crawl, column profiling, and bulk scoring, which are too long-running for a serverless function. The worker's operator endpoints bind to localhost only; the admin console reaches it by inserting a row into a commands table that the worker polls, so no port is open.

Stack choices and why#

Pushdown instead of a bronze layer. The obvious design is to pull rows, store them, and query your own copy. I measured the alternative first: a within_polygon filter over a 21.9-million-row dataset returned 280 bytes in 2.4 seconds, because Socrata filters and aggregates server-side. So the silver layer holds derived facts about datasets and never source rows. A mirror of NYC on a two-vCPU box would be stale by lunchtime. The cost curve is flat in the size of the data, and the whole middleware tier runs on a box that costs a few euros a month.

An inclusion test for the layer. A fact belongs in it only if it is expensive to derive, stable relative to query time, and wanted by more than one consumer. The counter-test is the one that gets used: if Socrata can answer it with a predicate in a couple of seconds, it does not belong.

Capability metadata makes a public query API safe. Because dataset_columns knows every column's real name and type, the planner allowlists identifiers and quotes literals before a query is ever built. Users compose questions without writing SoQL and without the app accepting arbitrary query text. Published endpoints take typed parameters that fill in filter values the author left open, never a column or operator, and every answer exposes its provenance: the exact SoQL that produced it, so the number can be re-derived on the portal.

Database mechanisms for the agent's SQL. The analyst agent can write real SQL against the silver layer, which an allowlist cannot cover. Nothing inspects that SQL. Instead the app connects as a dedicated login whose only membership is a read-only role, inside a read-only transaction with a statement timeout and a row cap. An earlier version ran through a more privileged role, and agent SQL could switch back out of the restricted role. A login with nowhere else to go was the fix.

Free models, made reliable. The agent runs on NVIDIA NIM's free tier. On five repeats of the same question, the baseline chain (search, then schema, then query) was right once. Putting a dataset shortlist in the system prompt, so common questions become a single query call, made it right five times out of five. Model choice came from a measured bake-off on multi-turn conversations; a model that looked best on a single tool call fell apart mid-conversation.

What broke / what I learned#

Inertness that reads as success. The quality-scoring batch selected datasets by popularity, so it re-scored the same 300 every night. Each run reported scored: 300, status: ok while coverage sat frozen at 13% for two weeks. Later, two new layers shipped to production against tables nobody had created, and an empty search answered 200 {"count":0}, which is exactly what a healthy, empty layer answers. The countermeasures: batches select by staleness, a single list of expected tables lives in the repo, a missing table returns a 503 naming the command that fixes it, and every worker pass writes its counters to a runs table the admin console reads.

The row-moving paths are where the cost lives. Quality scoring pulls a 500-row sample per dataset, one of the few places rows must cross the wire. Running that through a serverless function put the Vercel team at 310% of its data transfer allowance and paused every project on it. Sampling was not the mistake; running it on infrastructure billed by the second was. It moved to the VPS.

Vocabulary gaps bite silently. Knowing a column exists is not knowing its values. A filter on '104 MANHATTAN' returns zero rows when the stored value is '04 MANHATTAN', and zero rows looks exactly like a quiet neighbourhood. Filter values in the composer now autocomplete from observed data.

A fallback you have not exercised is not a fallback. A second model provider was wired up as automatic failover. Its key had run out of credit weeks earlier, so the "fallback" only added a second failure and a slower path to the same error. I removed it.

Sibling project: DataBolt#

DataBolt is a data-engineering learning platform I build alongside Data-Citizen: structured learning paths, hands-on challenges with leaderboards, write-ups, and DataBolt Studio, a drag-and-drop and CLI builder for medallion (bronze, silver, gold) pipelines that run locally. It is Next.js and Supabase, like Data-Citizen.

The video side of DataBolt lives in a separate content repo: module scripts, Manim scenes for the explanations, and a Remotion compositor that assembles clips into finished modules. A single modules.json registry ties the three together, and CI validates it on every push, because drift there is expensive and quiet: Remotion renders a black frame for a clip that was never produced, and Manim spends ten minutes rendering a scene nothing uses.

The two projects share a premise. Data-Citizen is a worked example of the medallion ideas DataBolt teaches, including the case where the textbook shape (land everything in bronze) is the wrong one.

Status#

Live at data-citizen.vercel.app. The silver layer covers the full catalog and refreshes nightly. CI runs unit, replay, database, end-to-end and worker suites on every PR, and an agent evaluation suite (30 cases, three runs each, graded against answers computed at run time) runs nightly. A DuckDB warehouse for joins Socrata cannot express is provisioned but deliberately not wired to the public app until a privilege issue in it is fixed. The open question is not the next feature but the first outside consumer of the API.