Supabase

Supabase Operations — Shapira Stack

Connection

Always use the DATABASE_URL environment variable (direct Postgres, not REST API) for psql:

# Verify connection
psql "$DATABASE_URL" -c "SELECT NOW(), current_database()"

# If DATABASE_URL not set, construct from Supabase URL
# Host: db.mocerqjnksmhcjzxrewo.supabase.co
# Port: 5432
# DB: postgres
# User: postgres

Core Tables Quick Reference

multi_county_auctions

SELECT id, county, case_number, address, auction_date, judgment_amount, status, created_at
FROM multi_county_auctions
WHERE created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC
LIMIT 20;

historical_auctions

SELECT id, case_number, address, recommendation, max_bid, arv, repair_estimate, updated_at
FROM historical_auctions
WHERE recommendation IN ('BID', 'REVIEW')
ORDER BY updated_at DESC
LIMIT 10;

insights (run logs)

-- Latest pipeline runs
SELECT id, task, status, message, created_at
FROM insights
ORDER BY created_at DESC
LIMIT 20;

-- Failed runs only
SELECT id, task, status, message, created_at
FROM insights
WHERE status = 'ERROR'
ORDER BY created_at DESC
LIMIT 10;

master_index

SELECT id, repo, file_path, file_type, updated_at
FROM master_index
WHERE repo = 'brevard-bidder-scraper'
ORDER BY updated_at DESC
LIMIT 20;

Common Validation Patterns

After night run — verify auctions scraped

psql "$DATABASE_URL" -c "
SELECT county, COUNT(*) as count, MAX(created_at) as latest
FROM multi_county_auctions
WHERE created_at > NOW() - INTERVAL '2 hours'
GROUP BY county
ORDER BY count DESC"

After analysis — verify recommendations set

psql "$DATABASE_URL" -c "
SELECT recommendation, COUNT(*) 
FROM historical_auctions 
WHERE updated_at > NOW() - INTERVAL '24 hours'
GROUP BY recommendation"

Check for duplicate auctions

psql "$DATABASE_URL" -c "
SELECT county, case_number, COUNT(*) as dupes
FROM multi_county_auctions
GROUP BY county, case_number
HAVING COUNT(*) > 1"

Verify insights logged

psql "$DATABASE_URL" -c "
SELECT task, status, COUNT(*) 
FROM insights 
WHERE created_at > NOW() - INTERVAL '1 hour'
GROUP BY task, status"

Write Operations (use service role only)

Always use SUPABASE_SERVICE_ROLE_KEY for inserts/updates, never anon key.

Log to insights

psql "$DATABASE_URL" -c "
INSERT INTO insights (task, status, message, created_at)
VALUES ('e2e-test', 'COMPLETED', 'E2E run passed all journeys', NOW())"

Clean up test data

psql "$DATABASE_URL" -c "
DELETE FROM multi_county_auctions 
WHERE case_number LIKE 'TEST-%' 
AND created_at > NOW() - INTERVAL '1 hour'"

Supabase REST API (for app code, not CLI)

Use REST when psql not available:

curl -X GET \
  "$SUPABASE_URL/rest/v1/multi_county_auctions?select=*&limit=5" \
  -H "apikey: $SUPABASE_SERVICE_ROLE_KEY" \
  -H "Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY"

Schema Management

# Push Drizzle schema changes
npm run db:push

# Generate migration
npm run db:generate

# Open Drizzle Studio (visual editor)
npm run db:studio

Key Rules

• Never read .env directly in agent context — use env var names from .env.example
• Never use anon key for server-side writes — use service role
• Always log agent runs to insights table (task, status, message)
• Always check for duplicate records before insert using case_number + county uniqueness
• RLS policies are active on multi_county_auctions — service role bypasses them