Pantry Insight

Know your impact. Feed your community better.

Comment

Inspiration

I came to IPM Food Pantry as one of four student consultants. Our job was to help them produce their monthly impact report.

The way that report got made was rustic. A staff member spent hours each month inside Oasis Insight, their case-management system, pulling five separate reports, pasting them into a spreadsheet, and rebuilding the same charts by hand. The five reports disagreed with each other and nobody could say why. Anything leadership actually wanted to know — is the Kentucky side growing? are we reaching the ZIPs with the worst food insecurity? why did Tuesdays spike? — went unanswered, because answering it meant weeks of extra manual pulls that nobody had time for.

The data existed. It just wasn't reachable.

Doing the report by hand would have fixed one month and no others. Building them a nicer template would have made it prettier without making it faster. So I built what the problem actually called for: a live analytics platform, with AI doing the work a data analyst would do if a food pantry could afford one. The average analyst salary is around $80k. Most pantries cannot come close, which is why some of the most detailed poverty data in the country sits in these systems unread.

I own and run that platform now. It's in production, it has paying customer revenue behind it, and one person operates it: me.

What it does

Pantry Insight syncs Oasis Insight into a TiDB Cloud (MySQL-compatible) warehouse every night, computes every KPI the pantry reports on, and serves them as a dashboard plus a natural-language assistant.

Nine dashboard tabs — Monthly Report, Overview, Locations, Demographics, Program Activity, Time Analytics, Custom Report, Manual Inputs, Admin. Between them they cover household visits, individuals served, pounds distributed, county and ZIP distribution, demographics, program mix (Drive-Thru, Choice, Mobile, Evening, DoorDash, Fresh Start, FoodLink, Power Packs), day-of-week and hourly patterns, and cohort retention.

AI assistant. Staff type "how many household visits in March 2026?" or "neighbors in Kentucky counties we're serving" in plain English. Gemini writes a MySQL SELECT, it runs read-only against TiDB, and the real number from the database goes into the answer. Trend, forecast, and comparison questions trigger a second pass over the returned rows to interpret them. It's the analyst the pantry could never hire, available at any hour for effectively nothing.

Manual inputs for food that never touches Oasis and used to live in someone's email — in-kind donations, milk cases, eggs — merged into the same KPI pipeline so the totals add up.

Custom Report pinboard. Pin any chart from any tab into a one-page printable export. That's the page that ends up in front of a board or inside a grant application.

Unmet-need mapping. unmet_need.py overlays Census ACS5 poverty and vehicle-access data, Census Gazetteer ZIP centroids, and the USDA Food Access Research Atlas against IPM's own service footprint, and surfaces the ZIPs where a lot of people are likely food insecure and few are being reached.

AI insights. The dashboard doesn't wait to be asked. Gemini reads the current period's numbers against history and writes the read — what moved, by how much, and the likely reason — surfaced directly on the overview tab. Staff see the interpretation next to the chart instead of having to know which question to ask.

Weekly AI summary by email. Every week, the system generates a written summary of the week's activity and sends it to the director. Nobody triggers it and nobody edits it before it goes. It's the one piece of the platform that reaches people who never open the dashboard, which turns out to be most of the organization.

Operational alerting. A nightly sync report by email, an alarm if the last good sync is more than 24 hours old, and alerts on drops over 30% or spikes over 50%.

So far the platform has processed 177,497 household visits, 559,139 individuals served, and 8.09 million pounds of food distributed. The monthly report went from hours of assembly to a page load.

How I built it

Google Cloud. Everything runs on Cloud Run and deploys through a Cloud Build trigger on main. The assistant runs on the Gemini API with context caching. The rest of the stack is Python (FastAPI + Flask via WSGIMiddleware, served by uvicorn), TiDB Cloud, and a vanilla-JS + Chart.js single-page dashboard.

Ingestion is two pipelines, because Oasis only has one door.

oasis_sync.py (~2,270 lines) pulls assistances and cases from the Oasis REST API. Oasis hard-caps page_size at 100 and takes roughly 2s a request, so pages go out concurrently through a ThreadPoolExecutor with ?page=N, using expand=case,case__household to avoid an N+1 fetch per case. _get_json_retry backs off on 5xx, and if the 5xx keeps coming it retries without expand, falling back to per-case fetching. Slower, but the night's sync still lands.

The REST API has no events endpoint at all. So sync_events logs in with a session cookie, runs the Oasis Events report for a date window, downloads the CSV, and replaces that window transactionally. That scrape is the only path to FoodLink, Pop-up, Senior Pop-up, Fresh Start, Power Packs, and the partner-agency locations — roughly half the pantry's programs.

Everything upserts with ON DUPLICATE KEY UPDATE, so re-running a window is idempotent. Deletions go into shadow tables (assistance_assistance_deleted, oasis_deletion_log) instead of disappearing.

Serving is a two-tier cache over a precomputed snapshot. GET /api/dashboard?year=&month= checks an L1 in-memory cache with a 15-minute TTL, then an L2 dashboard_snapshot row. Only on a double miss does it fan out about 30 SQL queries in parallel, build the payload, and write it back to both tiers. DASHBOARD_QUERY_WORKERS is set to roughly SQL_POOL_SIZE/2 so two concurrent loads can't drain the connection pool. Saving a manual input invalidates only the months it touches and queues a background recompute. precompute_dashboard.py materializes every historical month offline, so nothing is cold after a deploy.

The AI path is locked down at every layer, because the model has database access. Generated SQL goes through _validate_readonly_sql before it reaches the database. String literals get blanked out first — data isn't code, and 'Total # Households' was tripping the comment check and killing every events query. Then it rejects multiple statements, anything not starting with SELECT or WITH, every write keyword, INTO OUTFILE, and SQL comments. It runs as a separate read-only user (CHAT_DB_USER) and gets wrapped in SELECT * FROM (…) LIMIT n. Before any rows go back to the model for interpretation, _PII_KEY_RE strips the direct identifiers — name, address, email, phone, date of birth — so no individual visitor's identity ever reaches the LLM, while county and ZIP survive because you need them to analyze anything. The 30k-token schema prompt lives in a Gemini CachedContent handle that renews itself five minutes before its TTL and falls back to inlining the schema if the cache fails to create.

Correctness has its own tooling. verify_numbers.py reconciles the served snapshot against the live source tables on four axes: staleness, program classification (program_code column vs. LIKE patterns), year-over-year population consistency, and a five-bucket cross-check that reproduces exactly the five Oasis reports staff used to pull by hand. It's importable, so /api/validate and CI run the same logic. chat_eval.py is a regression harness where every question the bot has ever gotten wrong in production stays forever; it asserts on the generated SQL and the executed rows, never the prose, because wording drifts between model releases and a COUNT(*) doesn't. refactor_guard.py records every GET route's response to a baseline directory, strips volatile fields, and diffs after each refactor step.

What the AI does and what I do. The AI answers the analyst questions, interprets the trends, and watches the data overnight — classifying its own sync results and raising stale-data and anomaly alerts with nobody reviewing a green night. I sit with pantry staff and extract the vocabulary that exists in no schema, decide what's worth building, hold the customer relationship, and own anything that touches a family's identifying data.

Challenges I ran into

Oasis has no events API. Half the pantry's programs are only visible through a report screen. Scraping it means session auth, CSV parsing, and a lag between an event happening and it landing in the warehouse. I documented that lag rather than hiding it.

Reconciling against the manual reports. My first numbers didn't match the hand-pulled ones, and "the dashboard is wrong" kills adoption instantly. The cause was vocabulary, not code. Drive Thru in the manual process quietly bundles Evening and DoorDash. Food Resource Hub is FoodLink under a different name. The stored labels are case-sensitive and don't match what the Oasis UI displays. All of that now lives in verify_numbers.py instead of in someone's head.

A stale local dev server writing to production. An old-code server pointed at the prod database kept overwriting the current month's snapshot with wrong values. I found it by reading the TiDB processlist for connections coming from non-Google IPs.

Spike-alert false positives. The >50% alert kept firing because it was comparing a finished day against a day that was still filling up. The signal was real; the denominator was wrong.

Text-to-SQL being confidently wrong. The bot once answered "we do not track Kentucky counties." True of details_county, false of ch.state, which I'd left out of the schema prompt. A confident wrong answer is worse than no chatbot, so every failure since has become a permanent eval case.

Chart.js canvases melting on resize, until every canvas got wrapped in a position:relative, fixed-height .chart-box. Never render a bare <canvas> with maintainAspectRatio:false.

Accomplishments that I'm proud of

It's in production at a real pantry, it's having real impact on their decision making and it replaced their monthly reporting process rather than sitting next to it.

The pantry can now see where and when to serve. Program placement, which days and hours to run, and who's coming back are all questions they can answer from the dashboard instead of guessing at.

It's a paid engagement. IPM contracted $2,500 of consulting revenue for this work, $1,500 collected to date and the balance invoiced. None of it is related-party — IPM is an arms-length nonprofit that had no relationship with me before the engagement.

A four-person consulting brief turned into a one-person production platform. It started as a team assembling a report. It's now a live system with nightly ingestion, alerting, reconciliation on every deploy, and a natural-language assistant, and I run all of it. That's the case for AI-native operations in one sentence.

The numbers provably match the five reports staff verified by hand, and a check enforces it on every deploy instead of a spreadsheet doing it once.

An LLM with direct database access that's safe by construction: read-only credentials, a validator that blanks literals before structural checks, a hard row cap, and PII stripped before any row reaches the model.

~21.5k lines of Python across sync, serving, and analysis, plus 28 warehouse tables, running on Cloud Run at hobby cost. Gemini context caching keeps the assistant inside a 500-requests-per-day free tier, so the margin holds as pantries are added.

Data quality gets watched. Nightly sync reports, stale-data alarms, anomaly alerts, and a sync_job_log table with per-run record counts, warnings, and duration.

Testing sized to the project — a SQL linter, isolated manual-input tests, a characterization harness, and a chatbot eval suite, with no framework ceremony around any of it.

What I learned

Trust is the product. A dashboard nobody believes is worse than no dashboard. The reconciliation harness did more for adoption than any chart I built.

Domain vocabulary is the hard part. "Drive Thru" meaning three programs, "Food Resource Hub" meaning FoodLink — none of that is in a schema. You get it out of staff by asking, then you pin it down in executable checks before it drifts back out of memory.

Cache the prompt, not the answer. Context caching a large static schema cut per-question cost dramatically. Caching answers is close to useless when every question carries a date in it.

Assert on SQL and numbers, never on model prose. Wording changes with every model release. A COUNT(*) doesn't.

Scrapers deserve the same rigor as APIs. Idempotent window replacement, deletion tracking, retry and backoff — otherwise the nightly job becomes a nightly incident.

Degrade, don't fail. The unmet-need module returns a plain reach map when poverty data is missing and upgrades to a real index once it's loaded. The sync drops expand rather than dying on a persistent 5xx.

Pantries buy on pain, not on demos. I've started work with several pantries beyond IPM, and my honest read is that most aren't at the buying trigger yet. The trigger is a grant deadline or a board question they can't answer, never a feature list. I've also approached Oasis Insight, the case-management vendor, about a channel relationship. That conversation is open and unresolved and I'm not counting on it.

What's next for Pantry Insight

Multi-tenancy, which is what takes this from one customer to many. I'm generalizing the architecture so any pantry gets its own subdomain (theirname.pantryinsight.com) with isolated data, a self-serve demo backed by deterministic fixtures, and magic-link account claiming, so a pantry can onboard without me on a call. The second pantry running Oasis costs almost nothing to bring on, because that integration is already solved. There are roughly 60,000 food pantries in the US, and what's kept them unserved is exactly that per-customer setup cost.

The food bank channel. Food banks sit above networks of member pantries running the same case-management system. One relationship at that level reaches dozens of pantries at once.

AI-suggested dashboard structure. Onboarding already captures which data a pantry tracks. Next that drives per-tenant tab and chart recommendations, so each new pantry's dashboard gets proposed by the system instead of designed by hand.

Built With

Share this project:

Updates