ehildebrandtrojo/censusGPT

CensusGPT

Link: https://ehildebrandtrojo-censusgpt.hf.space

A stateful chat agent that answers natural-language questions about US population demographics using the SafeGraph / ACS 2019 5-year estimates stored in Snowflake.

Architecture

User Browser
     │
     ▼
Chainlit (app.py)           — real-time streaming chat UI, per-session history
     │
     ▼
Agent Loop (agent.py)       — drives Anthropic tool-use conversation
     │
     ├── Anthropic Claude (claude-sonnet-4-5)   — reasoning + SQL generation
     │
     └── Tools (tools.py)
           ├── list_table_fields        → browse all fields in an ACS table series
           ├── search_census_metadata   → 9-column ILIKE search with word-fallback
           ├── get_geography            → FIPS code resolution
           └── execute_census_query     → read-only SQL execution
                    │
                    ▼
              Snowflake (db.py)
              US_OPEN_CENSUS_DATA (Marketplace share)

Key design decisions

Agentic loop

1. 1.Scope check — system prompt rejects off-topic questions without any tool call.
2. 2.Field discovery — multi-strategy lookup to find the right ACS column:
3. 3.Check the pre-cached COMMON_METRICS cheat-sheet (30+ common metrics)
4. 4.Search metadata with search_census_metadata() — 9 columns, two-pass (phrase → word OR)
5. 5.If search fails: identify the ACS table series from the system prompt index, then call list_table_fields() to browse its full field hierarchy
6. 6.Geography resolution — get_geography() translates "Cook County" → STATE='IL', COUNTY='Cook' (GEOMETRY-style, no suffix).
7. 7.SQL execution — execute_census_query() runs the generated SELECT; errors surface back to Claude for one automatic retry.
8. 8.Synthesis — plain-English answer with every statistic bolded and a one-line data caveat.

ACS Table Coverage

The system prompt includes a comprehensive index of all major ACS table series so the agent knows where to look for any topic:

Local Setup

Prerequisites

• —Python 3.11+
• —A Snowflake account with the SafeGraph US Open Census Data Marketplace listing installed as US_OPEN_CENSUS_DATA
• —An Anthropic API key

Installation

git clone <repo-url>
cd censusGPT

python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt

cp .env.example .env
# Fill in your credentials in .env

chainlit run app.py
# → open http://localhost:8000

Environment variables

Running Tests

pytest tests/ -v

Tests mock both Snowflake and Anthropic — no live credentials needed.

Deployment (Railway)

1. 1.Push this repo to GitHub (private).
2. 2.Create a new Railway project → Deploy from GitHub repo.
3. 3.Set all environment variables in Railway → Variables.
4. 4.Railway auto-detects the Dockerfile and deploys.
5. 5.The public URL is shown in the Railway dashboard.

Example Questions

Income & Housing

• —What is the median household income in Cook County, Illinois?
• —How many renters in Chicago spend more than half their income on rent?
• —What is the median home value in King County, Washington?

Education & Demographics

• —What percentage of adults in Los Angeles County have a bachelor's degree or higher?
• —What is the racial composition of Miami-Dade County?
• —What share of households in Harris County speak Spanish at home?

Poverty & Social Programs

• —Which counties in Texas have the highest poverty rates?
• —What share of households in Cook County receive food stamps?
• —Compare the income-to-poverty ratio in Bronx County vs. Manhattan.

Employment & Commute

• —What percentage of workers in King County commute by public transit?
• —What is the unemployment rate in Wayne County, Michigan?

Health & Disability

• —What is the uninsured rate in Harris County, Texas?
• —What share of the population in Maricopa County has a disability?

Project Structure

censusGPT/
├── app.py             Chainlit entry point, session lifecycle, error handling
├── agent.py           Agentic loop (real-time streaming + tool-call dispatch)
├── tools.py           Tool implementations + Anthropic tool definitions
├── prompts.py         System prompt (ACS table index, vocab hints, golden SQL patterns)
├── db.py              Snowflake connection singleton + async wrapper
├── config.py          Environment-variable configuration
├── tests/
│   ├── conftest.py        Shared fixtures (Snowflake mock)
│   ├── test_tools.py      Unit tests for all four tools
│   └── test_agent.py      Integration tests for the agentic loop
├── .chainlit/
│   └── config.toml        UI theme (dark, wide layout, blue accent)
├── chainlit.md            Welcome screen content
├── Dockerfile
├── railway.toml
└── .env.example