Skip to content

BACKUP_AND_RECOVERY.md

Latest commit

 

History

History
111 lines (83 loc) · 3.36 KB

BACKUP_AND_RECOVERY.md

File metadata and controls

111 lines (83 loc) · 3.36 KB

Backup and Recovery — Protocol Guide

Production Database

Provider: Supabase (PostgreSQL) Project ID: vjmjzavgjpjfhshqjfpk Region: Supabase-managed

Automated Backups

Supabase provides automated daily backups on Pro tier and above:

  • Frequency: Daily at ~2:00 AM UTC
  • Retention: 7 days (Pro tier), 30 days (Enterprise)
  • Type: Full PostgreSQL pg_dump
  • Location: Supabase infrastructure (same region)

Verify Backup Status

  1. Go to Supabase Dashboard > Project Settings > Database
  2. Confirm "Backups" section shows recent backup timestamps
  3. Verify backup frequency matches expected schedule

Point-in-Time Recovery (PITR)

PITR allows restoring the database to any second within the retention window.

Enable PITR

  1. Supabase Dashboard > Project Settings > Addons
  2. Enable "Point in Time Recovery"
  3. Requires Pro plan or higher

Restore from PITR

  1. Go to Supabase Dashboard > Database > Backups
  2. Select "Point in Time Recovery"
  3. Choose target timestamp
  4. Confirm restore (creates new project or restores in place)

Critical Tables

These tables contain irreplaceable data and MUST be backed up:

Table Records Description
manus_protocol_chunks ~64,000 EMS protocol content + embeddings
manus_agencies ~2,700 LEMSA/agency registry
county_agency_mapping ~3,100 County-to-agency jurisdiction mapping
manus_users varies User accounts
search_history varies Usage analytics

Manual Backup Procedure

For critical deployments or before major migrations:

# Export critical tables
pg_dump "$DATABASE_URL" \
  --table=manus_protocol_chunks \
  --table=manus_agencies \
  --table=county_agency_mapping \
  --format=custom \
  --file=backup-$(date +%Y%m%d).dump

# Verify backup integrity
pg_restore --list backup-$(date +%Y%m%d).dump | head -20

Restore Procedure

Full Restore (from Supabase backup)

  1. Go to Supabase Dashboard > Database > Backups
  2. Select the backup to restore
  3. Click "Restore" and confirm
  4. Wait for restore to complete (may take 10-30 minutes for large databases)
  5. Verify data integrity: SELECT COUNT(*) FROM manus_protocol_chunks; (expect ~64,000)

Partial Restore (from manual backup)

pg_restore --dbname="$DATABASE_URL" \
  --table=manus_protocol_chunks \
  --clean --if-exists \
  backup-YYYYMMDD.dump

Embedding Data

Protocol embeddings (~64,000 vectors) are expensive to regenerate:

  • Model: Gemini Embedding 2 Preview
  • Re-embedding time: ~3 hours at API rate limits
  • Re-embedding script: scripts/re-embed-all-gemini.ts

If embeddings are lost but text content is intact, run the re-embedding script. Ensure GOOGLE_API_KEY is configured.

Post-Restore Checklist

After any restore:

  • Verify row counts match expected values
  • Run embedding health check: startup log should show "All chunks at current version"
  • Test search: "cardiac arrest" with LA County → should return relevant results
  • Verify auth: existing users can log in
  • Check Stripe: subscription status matches Stripe dashboard

Emergency Contact

If data loss occurs:

  1. Stop all writes immediately (set Railway to maintenance mode)
  2. Contact Supabase support for assisted recovery
  3. Check PITR availability for the target timestamp
  4. Document the incident timeline

Last updated: 2026-03-19