Db Reset

The Commands

Step 1: Run the setup script:

cd /Users/silverbeer/gitrepos/missing-table && ./scripts/setup-local-db.sh --from-prod

Step 2: Reset PostgreSQL sequences (REQUIRED after data restore):

# pragma: allowlist secret (local dev database only)
PGPASSWORD=postgres psql "postgresql://postgres:[email protected]:54322/postgres" -c "
SELECT setval(pg_get_serial_sequence('public.matches', 'id'), COALESCE((SELECT MAX(id) FROM public.matches), 0) + 1, false);
SELECT setval(pg_get_serial_sequence('public.teams', 'id'), COALESCE((SELECT MAX(id) FROM public.teams), 0) + 1, false);
SELECT setval(pg_get_serial_sequence('public.playoff_bracket_slots', 'id'), COALESCE((SELECT MAX(id) FROM public.playoff_bracket_slots), 0) + 1, false);
SELECT setval(pg_get_serial_sequence('public.leagues', 'id'), COALESCE((SELECT MAX(id) FROM public.leagues), 0) + 1, false);
SELECT setval(pg_get_serial_sequence('public.divisions', 'id'), COALESCE((SELECT MAX(id) FROM public.divisions), 0) + 1, false);
SELECT setval(pg_get_serial_sequence('public.seasons', 'id'), COALESCE((SELECT MAX(id) FROM public.seasons), 0) + 1, false);
SELECT setval(pg_get_serial_sequence('public.age_groups', 'id'), COALESCE((SELECT MAX(id) FROM public.age_groups), 0) + 1, false);
SELECT setval(pg_get_serial_sequence('public.clubs', 'id'), COALESCE((SELECT MAX(id) FROM public.clubs), 0) + 1, false);
"

Why sequences must be reset: After restoring data from backup, PostgreSQL sequences still start at 1. Without resetting, INSERT operations fail with "duplicate key" errors.

This does everything:

1. Resets database (schema + migrations + seed)
2. Backs up fresh data from production
3. Restores all data to local (teams, matches, leagues, etc.)
4. Flushes Redis cache (clears stale cached data)
5. Syncs users from prod with role-based passwords
6. Seeds test users (tom, tom_ifa, tom_ifa_fan, tom_club)
7. Resets PostgreSQL sequences to avoid duplicate key errors

NEVER use supabase db reset directly — it skips data restore, Redis flush, sequence reset, and user setup.

Test Users

After reset, these users are available:

Prerequisites

Local Supabase must be running:

cd /Users/silverbeer/gitrepos/missing-table/supabase-local && npx supabase status

If not running:

cd /Users/silverbeer/gitrepos/missing-table/supabase-local && npx supabase start

Workflow

1. FIRST: Verify environment is LOCAL (see safety check above) — STOP if prod
2. Ensure local Supabase is running
3. Run: ./scripts/setup-local-db.sh --from-prod
4. Wait for completion (backs up prod, restores data, flushes Redis, seeds users)
5. Run the sequence reset SQL command (Step 2 above)
6. Inform user when complete and remind them of test credentials

Verification

After reset, verify users exist:

cd /Users/silverbeer/gitrepos/missing-table/backend && uv run python scripts/manage_users.py list