supabase-operations

Supabase — Operations Workbook

Supabase hosted PostgreSQL project (mwkqmgadqnkkihjdeqsi). Primary persistent data store for the p24 platform and all infrastructure DevOps tooling.

---

Architecture

Supabase Cloud (AWS eu-central-1)
└── Project: mwkqmgadqnkkihjdeqsi
 
    Access paths:
    ├── PostgREST API  https://mwkqmgadqnkkihjdeqsi.supabase.co
    ├── Direct TCP     db.mwkqmgadqnkkihjdeqsi.supabase.co:5432 (IPv4, SSL required)
    └── Session pooler aws-1-eu-central-1.pooler.supabase.com:5432 (for exporters)
 
    Consumers:
    ├── et-operational-platform (Next.js) — via PostgREST + service_role key
    ├── grafana-readonly role             — via direct TCP (Grafana datasource)
    ├── queue-exporter (vps-i1:9200)      — via session pooler, grafana_readonly
    ├── pg-stats-exporter (vps-i1:9201)   — via session pooler, grafana_readonly
    ├── cost-exporter (vps-i1:9210)       — via Supabase management API
    └── n8n workflows (vps-h1)            — via PostgREST + service_role key

Roles

Role	Access level	Used by
service_role	Full access, bypasses RLS	Next.js backend, n8n, scripts
grafana_readonly	SELECT on specific tables	Grafana datasource, queue-exporter, pg-stats-exporter
anon	Public access via RLS policies	PostgREST anonymous requests
postgres (superuser)	Full DB admin	Supabase dashboard only — not used in application code

Key tables

Table	Schema	Purpose
dev_r_services	public	Unified element registry — compliance source of truth
dev_r_projects	public	Project registry
dev_r_incidents	public	Infrastructure incident log
dev_r_exporters_queues	public	Queue-exporter scrape config (dynamic, no code redeploy needed)
whatsapp_messages	public	WAHA → n8n → Supabase message store
incidents	public	Business incident tracking
fleet_positions	public	GPS position history (Traccar sync)
pending_transcriptions	public	Audio transcription job queue
pending_pdf_processing	public	PDF processing job queue
p24_issues	public	Issue tracking mirror

All tables have RLS enabled. dev_r_* tables have permissive policies for anon + public access (DevOps tooling pattern). Application tables use role-based or auth-scoped policies.

---

Config Management

Schema migrations

Schema changes are applied via Supabase MCP (apply_migration) or direct SQL in the Supabase SQL editor. There is no local migration file directory tracked in the repo.

Before any schema change:

1. Check current structure: SELECT * FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;
2. Apply migration via MCP or SQL editor.
3. Verify RLS is enabled on the new table: ALTER TABLE public.<table> ENABLE ROW LEVEL SECURITY;
4. Grant access as required (see RLS standard in CLAUDE.md).
5. Update dev_r_services if the change affects a tracked element.

RLS standard (mandatory)

Every table we own must have RLS enabled. service_role bypasses RLS by design. For anon/grafana_readonly access, add explicit policies. See CLAUDE.md for the full RLS decision table.

grafana_readonly grants

The grafana_readonly role must be granted SELECT explicitly on each table it needs to read:

GRANT SELECT ON public.<table> TO grafana_readonly;

Current grants (as of last audit): pending_transcriptions, pending_pdf_processing, incidents, fleet_positions, whatsapp_messages, p24_issues, dev_r_services, dev_r_projects, dev_r_incidents, dev_r_exporters_queues, plus any tables added to dev_r_exporters_queues.

---

Backup

Supabase Pro plan includes:

• Point-in-time recovery (PITR): 7-day rolling window, granularity 1 second
• Daily snapshots: retained 7 days, accessible via Supabase dashboard

No additional backup configuration is required. Backups are fully provider-managed.

Manual pg_dump (emergency snapshot before a risky migration):

# Via session pooler (works from any machine with psql)
PGPASSWORD="${SUPABASE_DB_PASSWORD}" pg_dump \
  -h aws-1-eu-central-1.pooler.supabase.com \
  -p 5432 \
  -U postgres.<project_ref> \
  -d postgres \
  --schema=public \
  -f /tmp/supabase-manual-$(date +%F-%H%M).sql
 
# Compress and upload to Wasabi for off-platform copy
gzip /tmp/supabase-manual-*.sql
s3cmd put /tmp/supabase-manual-*.sql.gz s3://p24-infra/supabase-manual/ \
  --host=s3.eu-central-1.wasabisys.com

Replace <project_ref> with mwkqmgadqnkkihjdeqsi.

---

Restore

Scenario 1: Row-level data accidentally deleted

Use Supabase PITR to restore a copy of the database to a specific point in time:

1. Supabase dashboard → Project → Database → Backups → Point in Time.
2. Select a timestamp before the deletion.
3. Restore to a new project (do not overwrite production — use the restored project to extract the rows, then insert back into production).
4. Run targeted SQL to restore specific rows.

Scenario 2: Full project restore (major incident)

1. Supabase dashboard → Project → Database → Backups → Daily Snapshots → select target date → Restore.
2. Restoration process takes 15–60 minutes depending on database size.
3. After restore completes, update all consumers with the new project credentials if the project ref has changed (it will change if you restore to a new project).

Estimated RTO: < 2 hours for full restore. RPO: up to 1 second with PITR, or up to 24h with daily snapshots.

Update .env files after a project ref change:

Location	Variable to update
vps-i1: monitoring/.env	SUPABASE_URL, SUPABASE_DB_HOST, SUPABASE_SERVICE_KEY
vps-h1: docker-compose.yml / .env	Same
GitHub Secrets	SUPABASE_URL, SUPABASE_DB_HOST, SUPABASE_SERVICE_KEY, SUPABASE_DB_PASSWORD
.env.local on local workstation	All of the above
Grafana datasource	monitoring/grafana/provisioning/datasources/supabase.yml

Scenario 3: Restore from manual pg_dump

PGPASSWORD="${SUPABASE_DB_PASSWORD}" psql \
  -h aws-1-eu-central-1.pooler.supabase.com \
  -p 5432 \
  -U postgres.mwkqmgadqnkkihjdeqsi \
  -d postgres \
  -f /tmp/supabase-manual-YYYY-MM-DD-HHMM.sql

---

Healthcheck / Monitoring

pg-stats-exporter

Container on vps-i1 (port :9201) connects to Supabase every 60s via session pooler and scrapes extensions.pg_stat_statements for top-200 slowest queries. If the exporter is up and returning metrics, the database is reachable.

# Check exporter health on vps-i1
curl -s http://localhost:9201/metrics | grep pg_stats_query_duration_ms | head -5

Prometheus alert PgStatsExporterDown fires if the exporter is unreachable for > 5 minutes.

queue-exporter

Container on vps-i1 (port :9200) polls queue depths from tables listed in dev_r_exporters_queues. If polling fails, metrics go stale. TranscriptionQueueCritical and similar alerts will fire on queue depth thresholds.

Direct connectivity check

# From any machine with psql + network access to Supabase
PGPASSWORD="${SUPABASE_DB_PASSWORD}" psql \
  -h aws-1-eu-central-1.pooler.supabase.com \
  -p 5432 \
  -U postgres.mwkqmgadqnkkihjdeqsi \
  -d postgres \
  -c "SELECT version();"

Grafana datasource check

In Grafana: Settings → Data Sources → Supabase PostgreSQL → Save & test. Should return “Database Connection OK”.

Supabase dashboard

Project health and real-time metrics at https://supabase.com/dashboard/project/mwkqmgadqnkkihjdeqsi. Check for:

• Database CPU and connections under Database → Reports
• Storage size under Database → Database Size
• API error rates under Logs → API

---

Password / Credential Rotation

Credential	What it is	Tracked entry	Rotation frequency	Last rotated	Next due
SUPABASE_SERVICE_KEY	service_role JWT	dev_r_services — supabase	90d	2026-05-08	2026-08-06
SUPABASE_ACCESS_TOKEN	Management API token	dev_r_services — supabase	90d	2026-05-08	2026-08-06
SUPABASE_GRAFANA_PASSWORD	grafana_readonly DB role password	dev_r_services — supabase	90d	2026-05-08	2026-08-06
SUPABASE_DB_PASSWORD	Direct DB access password (postgres superuser)	dev_r_services — supabase	90d	2026-06-13	2026-09-13

Rotate SUPABASE_SERVICE_KEY (service_role JWT)

The service_role key is a JWT signed with the project’s JWT secret. Resetting it invalidates all existing tokens.

1. Supabase dashboard → Project → Settings → API → Reset service_role key.
2. Copy the new key.
3. Update all consumers:

   # GH Secrets
   gh secret set SUPABASE_SERVICE_KEY -b "<new>" -R radieu/p24-infra
   gh secret set SUPABASE_SERVICE_KEY -b "<new>" -R radieu/et-operational-platform
    
   # vps-i1 .env
   # Edit /opt/p24-infra/monitoring/.env → SUPABASE_SERVICE_KEY
    
   # vps-h1 .env / docker-compose.yml
   # SSH to vps-h1 and update

4. Restart queue-exporter and pg-stats-exporter on vps-i1:

   docker compose restart queue-exporter pg-stats-exporter cost-exporter

5. Update .env.local on local workstation.
6. Log rotation in docs/secrets-rotation-log.md.

Rotate SUPABASE_ACCESS_TOKEN (management API)

Used by the cost-exporter and provisioning scripts.

1. Supabase dashboard → Account → Access Tokens → Generate new token.
2. Update in:

   gh secret set SUPABASE_ACCESS_TOKEN -b "<new>" -R radieu/p24-infra
   # vps-i1 .env
   # .env.local on local workstation

3. Restart cost-exporter: docker compose restart cost-exporter
4. Revoke old token in Supabase dashboard.
5. Log rotation in docs/secrets-rotation-log.md.

Rotate SUPABASE_GRAFANA_PASSWORD (grafana_readonly role)

1. Generate new password:

   NEW_PASS=$(openssl rand -base64 24)

2. Update the role password in Supabase:

   ALTER ROLE grafana_readonly WITH PASSWORD '<new_pass>';

3. Update Grafana datasource config on vps-i1:

   # Edit /opt/p24-infra/monitoring/grafana/provisioning/datasources/supabase.yml
   # Update the password field, then:
   docker compose restart grafana

   Or update in .env if the datasource YAML reads from an env variable.
4. Update GH Secret and .env.local.
5. Verify Grafana datasource: Settings → Data Sources → Supabase → Save & test.
6. Log rotation in docs/secrets-rotation-log.md.

Rotate SUPABASE_DB_PASSWORD (postgres direct access)

Used by manual pg_dump, emergency psql access, and the db-maintenance.py GitHub Actions job.

Scope: postgres superuser only. Grafana uses the separate grafana_readonly role (SUPABASE_GRAFANA_PASSWORD) — rotating SUPABASE_DB_PASSWORD does NOT require updating Grafana or vps-i1 monitoring/.env.

Correct API verb: PATCH — PUT and POST return 404 on this endpoint.

# 1. Read access token from .env.local
$token = (gc d:\code_2026\p24-infra\.env.local | sls "^SUPABASE_ACCESS_TOKEN=(.+)").Matches[0].Groups[1].Value
$ref   = "mwkqmgadqnkkihjdeqsi"
 
# 2. Rotate via management API
Invoke-RestMethod "https://api.supabase.com/v1/projects/$ref/database/password" `
  -Method PATCH `
  -Headers @{ Authorization = "Bearer $token"; "Content-Type" = "application/json" } `
  -Body (ConvertTo-Json @{ password = "<new_password>" })
 
# 3. Update GH Secret
gh secret set SUPABASE_DB_PASSWORD -b "<new>" -R radieu/p24-infra
 
# 4. Update .env.local on local workstation
# (vps-i1 monitoring/.env does NOT need updating — grafana_readonly is a separate role)

5. Log rotation in docs/secrets-rotation-log.md.
6. Update dev_r_services:

   UPDATE dev_r_services SET last_rotated = '<date>', next_due = '<date+90d>'
   WHERE service_name = 'SUPABASE_DB_PASSWORD';

---

Troubleshooting

Symptom	Cause	Fix
queue-exporter scrapes 0 rows	All rows active=false in dev_r_exporters_queues	SELECT * FROM dev_r_exporters_queues; — set active=true
pg-stats-exporter connection refused	Wrong SUPABASE_DB_HOST	Use session pooler aws-1-eu-central-1.pooler.supabase.com, not db.*.supabase.co
Grafana datasource “connection refused”	SUPABASE_GRAFANA_PASSWORD wrong or DB host changed	Verify credentials in .env; restart grafana
RLS error on exporter query	grafana_readonly lacks SELECT on referenced table	GRANT SELECT ON public.<table> TO grafana_readonly;
service_role operations fail	JWT expired / key rotated	Redeploy with new SUPABASE_SERVICE_KEY
PITR not available in dashboard	Project on Free plan	Pro plan required for PITR — confirm billing in Supabase dashboard
Slow query alerts firing	New unindexed query pattern	Check pg-stats-exporter metrics; add index or optimize query

---

Schema Maintenance Log

2026-05-15 — Drop archive and backup tables (#176)

Applied migration drop_archive_backup_tables to remove 5 dead-weight tables from the public schema.

Motivation: schema introspection (Supabase Dashboard) was slow due to 151+ tables. These 5 tables were confirmed as dead weight with no active application code references.

Table dropped	Reason
p24_l_cars_deleted	Archive snapshot of deleted cars — no longer queried
p24_l_cars_duplicate	Duplicate detection scratch table — unused
p24_l_cars_fleet	Old schema version of p24_l_cars — superseded
profiles_backup	Manual backup snapshot from migration 20260317224417
user_roles_backup	Manual backup snapshot from migration 20260317224417

Pre-drop verification: grepped et-operational-platform codebase — references found only in auto-generated type files (types.ts, database.types.ts) and historical migration SQL. No active service, hook, page, or component queried these tables. No n8n workflow references found in p24-infra.