Local database

Local Supabase Postgres: Setup, Backup, Restore, Switch

Overview - Run a local Supabase Postgres via Docker Compose on 127.0.0.1:54322. - Back up the cloud database safely and restore into local. - Switch the app between cloud and local by flipping one .env variable.

Prerequisites - Docker + Docker Compose installed. - psql available locally (psql --version). - Cloud DB DSN available (pooler DSN). Do not commit secrets.

1) Start Local Supabase - Option A (recommended, alongside Redis): docker compose up -d db - Option B (standalone file): docker compose -f supabase/docker-compose.local.yml up -d - Verify container: docker ps - Verify DB: PGPASSWORD=postgres psql -h 127.0.0.1 -p 54322 -U postgres -d postgres -c "SELECT 1;"

2) Backup Cloud DB - Ensure .env contains HIREEX_DATABASE__URL pointing to your cloud pooler DSN (postgresql+asyncpg://... or postgresql://...). - Run one of: - ./scripts/local_db_ops.py backup (Python wrapper; normalizes driver) - ./run.sh backup (shell wrapper) - Output: backups/backup_YYYYmmdd_HHMMSS.sql (excluded from VCS).

3) Restore Into Local - Start local DB if not already running (see step 1). - Run one of: - ./scripts/local_db_ops.py restore (uses latest backup) - scripts/db_restore_local.sh (bash version) - The restore process: - Finds the latest backups/backup_*.sql. - Waits for local DB to be healthy. - Drops and recreates public schema. - Applies the backup. - Prints tables and a sample job_listings count if present.

4) Switch Environments - Local DSN: HIREEX_DATABASE__URL=postgresql+asyncpg://postgres:postgres@127.0.0.1:54322/postgres - Use one of: - ./scripts/local_db_ops.py switch-local (writes local DSN to .env, saves .env.cloud if missing) - scripts/use-env.sh local|cloud (symlink-based swap) - The app reads HIREEX_DATABASE__URL already; no code changes needed.

5) Verify App Against Local - Connectivity: python3 __main__.py check-db -t 10 - Sample data: python3 __main__.py list-jobs --limit 10 - Optional flows: python3 __main__.py db-manage stats, ./run.sh update --check-all --min-score 70 - Quick smoke: ./scripts/local_db_ops.py smoke (30s timeouts where possible)

Maintenance - Stop and remove local DB (with volume wipe): - docker compose -f supabase/docker-compose.local.yml down -v - Troubleshooting: - Port conflicts: ensure 54322 is free (lsof -i :54322). - Healthcheck failing: check container logs (docker logs hireex_supabase_db). - Corrupted volume: down with -v to remove and re-create. - DNS/Pooler issues: core/db/engine.py logs helpful hints and checks local 127.0.0.1:54322.

Notes - Backups are excluded via .gitignore (backups/*). Do not commit secrets. - Consider storing backups outside the repo and/or encrypting at rest.