Two things changed since the last version:
The full event history now writes to an explicit Google Sheet that the admin specifies and manages, rather than only living in the Supabase events table. See "The Google Sheet history log" below.
(Carried over from before) the app watches your Gmail account through two independent paths, either of which can trigger an event:
Trusted senders (up to 5) — e.g. the Village office's own address, the 4620 Communications Committee's own address. Any email from one of these is picked up automatically, subject line doesn't matter. This is the original sender-whitelist capability — it's still here.
Forward + keyword — anyone forwards a notice to the watched Gmail address with a trigger keyword (default POST-TO-EVENTS) in the subject. This is the newer addition, for notices that don't come from a small fixed list of senders.
Both paths land in the same inbox and go through the same classify-and-extract step below — the only difference is why a message was picked up, which is recorded per-event (match_reason) for admin review.
The watched senders, the watched address, and the trigger keyword all live in the database (watched_senders and app_settings — see schema.sql), edited from an admin page in the app rather than hardcoded — that's the "elicit" step: the app asks for these values once, at setup.
schema.sql — app_settings, events, and the active_events view the slide reads from. Run this first in the Supabase SQL editor.
process-events-inbox/index.ts — the scheduled job. Scans for new matching emails, classifies each with Claude, writes a row to events.
get-active-events/index.ts — the public endpoint the slide's ?events= URL parameter points to. Returns only current, non-expired events, already shaped for the slide.
Each matched email's body (not just subject/sender, since we need real content to classify and extract dates) goes to Claude with instructions to:
Decide VILLAGE, CONDO, or OTHER — OTHER is the deliberate fallback when it's ambiguous, rather than guessing.
Pull a short title, location, and start/end date-time if the email states or clearly implies one.
Reply SKIP for anything with no real event content (so spam or a stray reply-all doesn't become a fake event).
Low-confidence extractions are still saved (tagged confidence: "low") rather than dropped — the raw_snippet column keeps the original text so someone can review and correct them later if you build an admin review page.
Every event gets a computed expires_at:
If an end time was deduced → that's the expiry.
If only a start time was deduced → expiry is end-of-day on that date (so a 10am event doesn't disappear at 10:01am).
If neither could be deduced → expires_at is left null, and the event stays on the slide indefinitely rather than risk silently dropping something real. It'll need a manual dismiss (flip status to 'dismissed') if it turns out to be stale.
The active_events view filters on expires_at, so removal is just a query-time filter — nothing is deleted. That means a bad classification is always recoverable by editing the row, and you can add a separate housekeeping job later (e.g. hard-delete anything expired for 90+ days) without touching this logic.
Nothing about the Supabase side changed — events is still the source of truth, and "removed" still just means filtered out of active_events. What's new is that the full history is also written live to a Google Sheet the admin owns, so anyone on the committee can open a familiar spreadsheet and browse the whole history without touching the database.
To turn it on, the admin:
Creates (or reuses) a Google Sheet, owned by or shared as Editor with the same Google account GMAIL_REFRESH_TOKEN was issued for.
Makes sure it has a tab named exactly Events (a brand-new Sheet's default Sheet1 tab needs renaming).
Pastes the Sheet's normal share link into app_settings.event_history_sheet_link.
From then on, every run of process-events-inbox:
Writes the header row once, the first time it sees an empty sheet.
Appends one row per new event: Event Name, Category, Location, Date/Time Added, Start, End, Appeared At, Disappeared At, Photo (the last two left blank at first).
Goes back and fills in Disappeared At for any already-logged event that has since expired or been manually dismissed — so the sheet ends up as a complete, append-only audit trail: every event that ever appeared, and exactly when it stopped being shown.
Reads the Photo column back and syncs any admin-pasted link into events.photo_url — see "The Photo column" below.
Location is auto-filled straight from the same extraction that produces the title — it's whatever Claude read out of the email. Left blank if the email didn't state a location clearly, rather than guessing one. This one's read-only from the app's side: edit it in the Sheet for your own records if you like, but (unlike Photo) changes there aren't read back into the display.
Leave event_history_sheet_link blank to skip Sheet logging entirely — none of the rest of the app depends on it.
Uses the same OAuth token as Gmail. Rather than a second credential set, the one-time consent flow that produces GMAIL_REFRESH_TOKEN should request both scopes together: https://www.googleapis.com/auth/gmail.readonly and https://www.googleapis.com/auth/spreadsheets. One consent screen, one refresh token, works against both APIs. (If that consent flow already happened without the Sheets scope, it needs to be redone with both scopes present — a refresh token can't pick up a new scope after the fact.)
Each event tile on the slide has room for a photo illustrating that specific event (a shredder truck, a moving van, whatever fits) — see "Event tile photos" below for how it's displayed. The system has no way to find that photo on its own; it comes from the Photo column, which the admin manages directly:
Open the Sheet, find the event's row, paste a direct image link into the Photo cell — e.g. right-click an image anywhere on the web and copy its image address, or grab a share link from wherever the photo already lives.
The next process-events-inbox run (once a day) picks it up and the event starts showing that photo. To see it sooner, the admin can hit the function's URL directly — it's a plain HTTP endpoint, and running it twice in a row is harmless (the dedup check just skips everything it's already seen).
Leave the cell blank, or type a plain description instead of a link ("ask the front desk for the flyer"), and the tile simply shows no photo for that event — the description is still saved in the Sheet and in events.photo_url for the record, it just isn't something a web page can render as an image.
There's no separate "does this event have a photo" flag anywhere — get-active-events checks whether the Photo cell's value starts with http:// or https:// and serves it if so, null otherwise. Pasting a new link, or clearing the cell, is the whole editing flow.
When there are zero active events, the slide shows a full-bleed image instead of a blank display. By default that's a built-in graphic — the "4620 Information, brought to you by the Communications Committee" branded image — baked directly into the slide's HTML file, so it works out of the box with no setup.
To replace it with something else (a seasonal graphic, a PSA, whatever the committee wants up when nothing's scheduled), the admin pastes a normal Google Drive "share" link into app_settings.empty_state_drive_link (share the file as "Anyone with the link → Viewer" first). When that setting has a value, it takes priority over the built-in default; get-active-events converts it to a directly-embeddable URL and passes it to the slide. Clear the setting to fall back to the built-in graphic again — nothing to re-upload or redeploy either way.
Known caveat: Google occasionally rate-limits or interstitial-pages the drive.google.com/uc?export=view hotlink pattern this relies on, especially for larger files or high request volume — it's fine for a lobby screen refreshing once a minute, but if it ever starts failing, the more robust fix is to have process-events-inbox (or a small separate job) download the Drive image periodically and re-host it in Supabase Storage instead. Flagging this now so it's not a surprise later, not because it's likely to be a problem at this scale.
Each tile on the slide can show two different photos, doing two different jobs:
A small category thumbnail in the tile's upper-right corner — the fountain photo for Village events, the building photo for Condo events. This is fixed per category, baked into the slide file, and needs no admin action.
The event photo filling most of the tile — specific to that one event (a shredder truck, a donation truck, whatever fits), sourced entirely from the Photo column above. No photo set → the tile just shows its plain tinted background, same as before this feature existed. Other-category events don't get a category thumbnail (there's no third baked-in photo for that catch-all bucket), but can still show an event photo if the admin sets one.
village-condo-events-slide-tiles.html ships with a SAMPLE_EVENTS array (search for it in the <script> block) so the file renders something reasonable when opened standalone, with no ?events= URL pointed at a live get-active-events endpoint. None of that is real data — "Community center lot," "Clubhouse," and the rest are made up to fill out the preview, the same way a design mockup uses placeholder text. The moment the slide's URL includes ?events=<get-active-events URL>, SAMPLE_EVENTS is ignored entirely and every field — including Location — comes from what Claude actually extracted from a real email, or is blank if it didn't find one.
Run schema.sql.
In the app's admin page (or directly in the database for now):
set app_settings.gmail_inbox_email to the committee Gmail address
add up to 5 rows to watched_senders for trusted addresses
adjust app_settings.trigger_keyword if you don't want the default
paste a Google Drive share link into app_settings.empty_state_drive_link
create (or reuse) a Google Sheet with an Events tab and paste its share link into app_settings.event_history_sheet_link — see "The Google Sheet history log" above
Set these secrets in your Supabase/Lovable project:
GMAIL_CLIENT_ID, GMAIL_CLIENT_SECRET, GMAIL_REFRESH_TOKEN (from the Google Cloud OAuth setup — still on our to-do list; grant both the Gmail readonly scope AND .../auth/spreadsheets in the same consent so one token covers both APIs)
ANTHROPIC_API_KEY
Deploy both functions (process-events-inbox, get-active-events).
Schedule process-events-inbox to run daily (matches the "once a day is fine" requirement) — Supabase supports cron triggers on Edge Functions directly.
Point the slide's URL at: .../village-condo-events-slide-tiles.html?events=<get-active-events URL>