# SOP — Supabase Migrations (Management API)

Last verified: 2026-03-05 (Builder)

## Purpose
Run SQL migrations directly against Supabase without waiting on dashboard/manual clicks.

## Source of truth for access
- Supabase management token: `/root/.clawdbot/secrets.json` → `supabase_access_token`
- Endpoint: `POST https://api.supabase.com/v1/projects/{project_ref}/database/query`

## Preconditions
1. SQL file exists and is reviewed.
2. You know project ref (example: `memscjotxrzqnhrvnnkc`).
3. You have rollback SQL for destructive changes.

## Standard run steps

### 1) Load token and SQL
```bash
PROJECT_REF="memscjotxrzqnhrvnnkc"
SQL_FILE="/home/ccuser/opsman-control-centre/supabase-control-centre-schema.sql"
TOKEN=$(python3 - << 'PY'
import json, pathlib
obj=json.loads(pathlib.Path('/root/.clawdbot/secrets.json').read_text())
print(obj['supabase_access_token'])
PY
)
```

### 2) Execute migration
```bash
curl -sS -X POST "https://api.supabase.com/v1/projects/${PROJECT_REF}/database/query" \
  -H "Authorization: Bearer ${TOKEN}" \
  -H "Content-Type: application/json" \
  --data-binary "$(jq -n --arg q "$(cat "$SQL_FILE")" '{query:$q}')"
```

### 3) Interpret response
- `201` = success
- `400` with `already exists` = schema already applied (idempotency check)
- other errors = stop and fix SQL, then rerun

## Verification checklist (required)

Run verification query:
```sql
select
  (select count(*) from cc_council_topics) as cc_council_topics,
  (select count(*) from cc_council_messages) as cc_council_messages,
  (select count(*) from cc_projects) as cc_projects,
  (select count(*) from cc_tasks) as cc_tasks,
  (select count(*) from cc_agent_snapshots) as cc_agent_snapshots,
  (select count(*) from cc_approvals) as cc_approvals,
  (select count(*) from cc_activity_log) as cc_activity_log;
```

For realtime-enabled tables, verify publication membership:
```sql
select schemaname, tablename from pg_publication_tables
where pubname='supabase_realtime'
  and tablename in ('cc_council_messages','cc_activity_log','cc_approvals')
order by tablename;
```

## Proof logging format (post-run)
Record in daily memory/log:
- SQL file path
- HTTP status code
- key output (counts / publication rows)
- any errors encountered and resolution

## Known behavior
- Control Centre schema may already exist; migration can return:
  - `ERROR: relation "cc_tasks" already exists`
- This is acceptable if verification counts and tables are present.