Supabase Operations for Local Pulse

Overview

Local Pulse uses Supabase PostgreSQL for data storage with two environments:

• Staging: SUPABASE_DATABASE_URL - for development/testing
• Production: SUPABASE_DATABASE_URL_PROD - for live data

All scripts are read-only and located in scripts/supabase/.

Quick Reference

Available Scripts

1. list_posters.py - List All Posters

Lists all posters with count, status, and summary stats.

poetry run python scripts/supabase/list_posters.py --prod
poetry run python scripts/supabase/list_posters.py --prod --limit 10

Output: Table with poster IDs, status, creation date, uploader + status breakdown.

2. get_latest_poster.py - Latest Poster Summary

Quick overview of the most recent poster.

poetry run python scripts/supabase/get_latest_poster.py --prod
poetry run python scripts/supabase/get_latest_poster.py --prod --full  # includes context doc

Output: Poster metadata, user inputs, event info, user-provided URLs.

3. show_all_poster_data.py - ALL Database Fields ⭐

The comprehensive script - shows EVERY field from ALL related tables.

poetry run python scripts/supabase/show_all_poster_data.py --latest --prod
poetry run python scripts/supabase/show_all_poster_data.py <poster_id> --prod

Output includes ALL fields from:

• posters table (status, vision, OCR, website, costs, editing)
• vision_analysis table (context document, extracted entities/events)
• events table (all event details, pricing, schedule)
• entities table (all entities with Spotify, Google Places, social media data)
• Compiled Pulse Script

4. get_context_document.py - Context Document

Fetches the enriched context document.

poetry run python scripts/supabase/get_context_document.py --latest --prod
poetry run python scripts/supabase/get_context_document.py <poster_id> --prod

Output: Full context document content + OCR poster text.

5. show_enrichment_quality.py - Quality Diagnosis ⭐

Diagnose enrichment quality - shows what was found vs. what's missing.

poetry run python scripts/supabase/show_enrichment_quality.py --latest --prod
poetry run python scripts/supabase/show_enrichment_quality.py <poster_id> --prod

Output includes:

• User inputs quality score
• Vision analysis quality score
• Context document sections check
• Event completeness per event
• Entity enrichment coverage (Spotify, Google Places, etc.)
• Pulse Script analysis
• Cost summary

6. show_pulse_script.py - Compiled Pulse Script

Shows the LLM-generated markdown for the event page.

poetry run python scripts/supabase/show_pulse_script.py --latest --prod
poetry run python scripts/supabase/show_pulse_script.py <poster_id> --prod

Output: Full Pulse Script + analysis (entity refs, sections present).

7. get_poster.py - Poster Details by ID

Basic poster info with events and entities summary.

poetry run python scripts/supabase/get_poster.py <poster_id> --prod

Common Workflows

"I just enriched a poster, show me everything"

# Full data dump
poetry run python scripts/supabase/show_all_poster_data.py --latest --prod

# Or quality focused
poetry run python scripts/supabase/show_enrichment_quality.py --latest --prod

"What's in production?"

poetry run python scripts/supabase/list_posters.py --prod

"Check if enrichment worked properly"

poetry run python scripts/supabase/show_enrichment_quality.py --latest --prod

"Show me the context document"

poetry run python scripts/supabase/get_context_document.py --latest --prod

"Show me the Pulse Script"

poetry run python scripts/supabase/show_pulse_script.py --latest --prod

"Get all data for a specific poster"

POSTER_ID="31ad6cba-1dec-4789-8ecc-57b1ee35422f"
poetry run python scripts/supabase/show_all_poster_data.py $POSTER_ID --prod

Database Schema Reference

posters Table

Core poster data including:

• Identity: poster_id, file_path, status, owner_id
• User Inputs: user_provided_urls, location_hint, extra_context, ticketing_url
• Vision: vision_status, overall_theme, visual_style, atmosphere_tags, audience_tags
• OCR: poster_text, word_count, ocr_confidence, ocr_strategy
• Website: website_url, website_source, website_content
• Processing: enrichment_providers, cost_summary, processing_date
• Output: compiled_pulse_script, compiled_at
• Editing: uploaded_by, last_edited_by, last_edited_at

vision_analysis Table

• context_document (the big enriched document)
• extracted_entities, extracted_events (JSON)
• vision_model, prompt_version
• is_safe, rejection_reason

events Table

• title, description, location
• event_datetime, event_end_datetime, door_time
• performers, pricing (JSON)
• status, age_restriction
• is_recurring, is_multi_day, is_cancelled, is_sold_out

entities Table

• name, entity_type, confidence, event_role
• spotify (JSON with followers, popularity, genres, top tracks)
• google_places (JSON with rating, address, phone)
• social_media (JSON with Instagram, Facebook, etc.)
• google_kg, tavily, google_cse (additional enrichment)

Environment Setup

Add to .env:

# Staging (default)
SUPABASE_DATABASE_URL=postgresql://...

# Production (use --prod flag)
SUPABASE_DATABASE_URL_PROD=postgresql://...

Flags Reference

Troubleshooting

"No context document yet"

The poster hasn't been enriched. Check status field - should be enriched or compiled.

"Connection failed"

Check that SUPABASE_DATABASE_URL_PROD is set in .env.

"Missing enrichment data"

Run show_enrichment_quality.py to diagnose what's missing and why.

CRITICAL: Local Supabase Migration Safety

NEVER Do These (Data Loss Risk)

Migration File Rules

# FORBIDDEN - renaming existing migration:
mv supabase/migrations/20241228000000_foo.sql supabase/migrations/20241228010000_foo.sql

# CORRECT - create NEW migration for changes:
supabase migration new my_changes
# Then edit the new file

Safe vs Destructive Commands

Safe (no confirmation needed):

supabase start
supabase stop
supabase status
supabase migration new <name>
supabase migration list
supabase db diff

DESTRUCTIVE (MUST ask user first):

supabase db reset        # Wipes ALL data
supabase stop --no-backup
supabase db push --force

Before ANY Migration Work

1. Check current state: supabase migration list
2. Never rename/delete existing migration files
3. Always create NEW migrations for schema changes
4. If timestamp conflicts: use a different timestamp, don't rename

If Migrations Get Out of Sync

Ask user: "This will DELETE ALL DATA in local Supabase. Continue?"

# Only after explicit user approval:
supabase db reset

Local Development Quick Reference

Start Local Supabase

./scripts/local/start.sh
# Or manually:
supabase start

Check Status

supabase status
psql "postgresql://postgres:postgres@127.0.0.1:54322/postgres" -c "SELECT COUNT(*) FROM posters;"

Local vs Production URLs

Local:      postgresql://postgres:postgres@127.0.0.1:54322/postgres
Production: postgresql://postgres.[project-id]:[pass]@aws-0-[region].pooler.supabase.com:6543/postgres

Storage Buckets

Local Supabase needs buckets created manually. Check supabase/config.toml or create via SQL:

INSERT INTO storage.buckets (id, name, public) 
VALUES ('drafts', 'drafts', true) ON CONFLICT DO NOTHING;