Food on the Table

Picture a fridge with more food than the household can finish: produce going soft, a sealed box past its Best By date, a tray of leftovers cooked yesterday. Each of those has a better ending than the trash. But every option lives somewhere different, and the fridge door is a bad place to look up food safety rules.

A few blocks away there are neighbors who could use that tray, plus food banks, pantries and community fridges. I wanted one app that answers "what should I do with this?" and then makes the handoff to a neighbor or a food bank easy.

Food on the Table follows food from the shelf to its best ending. You track your fridge, freezer and pantry by scanning a barcode (looked up in Open Food Facts) or with a form that pre-fills typical shelf life. The home page shows what to use next, and you can drag expiring items into a Cooking Pot, pick a cuisine, time and diet, and get a full recipe; "I cooked this" takes the items out of your inventory.

For food you won't use, a short triage quiz sorts it into one of 10 outcomes, from donate to compost. A Google Maps finder shows nearby food banks, pantries, soup kitchens, shelters and community fridges with hours and directions, and lets you log a donation with a photo. The community board lets you offer extra food to neighbors: they request it, a chat opens right away, you pick who gets it, and both of you confirm the pickup.

Around that sit swipeable food-safety lessons, notifications you can switch on or off by type, and an impact score with levels from Seedling to Forest Guardian that tracks meals saved, CO2 avoided and money saved. An AI food check suggests whether to cook, eat, donate or discard an item and says why. If OpenAI is down, the app falls back to a plain expiry-date rule.

The frontend is a React and TypeScript single-page app that reads and writes its data straight through Supabase. All 27 tables have row-level security turned on, so a browser holding only the public anon key reaches only the rows its policies allow. The actions that span several tables (accepting, declining, confirming a pickup, sending a message) run as Postgres functions with SECURITY DEFINER and a pinned search_path. The diagram takes one plate of food from post to pickup.

Sharing is a small state machine. A post moves from active to reserved to picked_up, and a request moves from pending to accepted and then completed, or to cancelled. When the giver picks someone, accept_interest checks that the caller really is the giver, accepts that request, reserves the post, cancels every other pending request, posts a system message in the chat and notifies the winner and everyone who missed out, all in one transaction, so a half-finished handoff can't exist.

Pickup needs both sides. confirm_community_pickup records each person's confirmation, and only when both are in does it close the request, mark the post picked up, write impact events (meals, kilograms, CO2 and dollars from per-serving coefficients) and award 50 points to the giver and 30 to the receiver. Chat runs on Supabase Realtime with optimistic sends: a temporary copy appears instantly and the real row replaces it when the insert arrives. The OpenAI key lives only in 2 Deno Edge Functions, and the Places server key only in a small proxy.

The chat gave me the most trouble. Expressing interest was supposed to open a conversation with the giver, but the chat queries came back with 500 and 400 errors. I wrote diagnostic SQL that dumped every chat table's columns, constraints, sample rows and policies, and ran it in the Supabase SQL editor to see what was really wrong.

The 500s pointed to row-level security. My first policy for conversation participants decided who could see a participant row by querying the participants table itself, a self-referencing rule Postgres can't evaluate. I replaced it with a flat rule (you can read your own participant rows) and keyed the conversation and message policies off that list. The 400s were a missing link: the app joined each message to its sender's profile through a foreign key that didn't exist yet, so I added it.

Then I made conversation setup automatic. A trigger on every new request creates the conversation and adds both people in the same transaction as the request itself. A unique key plus ON CONFLICT means a retry can't open a second thread, and a one-time backfill repaired every request made before the fix.

During a 5-week AI program I took a Lovable scaffold to a working prototype across 11 feature areas: barcode inventory, the AI food check, recipes, triage, the donation map, community sharing, live chat, notifications, lessons, impact points, and accounts with Google sign-in. That covers both halves of the problem: deciding what to do with food, and handing it to someone who will eat it. I demoed it to the AI Squads program at the end of the 5 weeks.

The database carries the weight: 27 tables, all behind row-level security, plus 27 Postgres functions, 11 triggers and 3,983 lines of SQL across 27 migrations. Chat, conversation lists, request status and notifications update live over Supabase Realtime, and zero secret keys ship in the browser bundle.

Tests and CI come before any new feature. I'd start with database tests for the sharing flow (accept, decline, confirm pickup, points) against a local Supabase, and run them in GitHub Actions on every push next to lint and a type check. In the same pass I'd consolidate the migration history so a fresh supabase db push succeeds; the README already documents the 2 files that share a version number and the 2 that run before the tables they need.

Then I'd hold every function to the bar accept_interest sets. confirm_community_pickup should work out the caller's role from their login instead of trusting the app, and award points only once even if it's called twice; accept_interest should lock the post row and accept only a pending request on an active post. After that, Donate moves to Google's Places API (New) and the place-details proxy gets a production home.