Backend: Sync GitHub Action (listings.json → geocode → Supabase upsert)

[Jose-Gael-Cruz-Lopez]

Context

Ties #11 (table) and #12 (geocoder) together: a workflow that keeps Supabase in sync whenever the repo's hackathon list changes, plus a weekly refresh. Mirror the existing automation patterns in .github/workflows/ (e.g. update_readmes.yml, gallery.yml).

What to build

New workflow .github/workflows/sync_supabase.yml:

• Triggers: push with paths: ['.github/scripts/listings.json'] on main, weekly schedule cron, and workflow_dispatch.
• Steps:
  1. Checkout, setup Python, pip install requests.
  2. Run geocode.py (Backend: Geocoding step + geocode cache (Nominatim) #12) to produce enriched records.
  3. Run a sync script that upserts into Supabase via REST:
     POST {SUPABASE_URL}/rest/v1/hackathons with headers
     apikey: <SERVICE_KEY>, Authorization: Bearer <SERVICE_KEY>,
     Prefer: resolution=merge-duplicates (upsert on id PK).
  4. Optionally mark rows whose id is no longer in listings as is_visible=false (soft delete) so the globe drops them.
  5. Commit the updated geocode_cache.json back to main (use the fetch+rebase+retry push pattern from the other workflows).
• Secrets: SUPABASE_URL, SUPABASE_SERVICE_KEY (from Backend: Supabase hackathons table + RLS read policy #11). Never echo/commit the key.

Acceptance

• Runs on listings.json change + weekly cron + manual dispatch
• Upserts all rows (idempotent on id — re-runs don't duplicate)
• Removed hackathons disappear from the site (soft-delete or delete)
• Uses the service key from secrets; key never leaked in logs/commits
• Commits refreshed geocode cache without conflicts

---

Part of #10 · Design spec · Repo data lives in .github/scripts/listings.json