# Supabase Migration SOP

Run migrations programmatically via the Supabase Management API.
No manual SQL Editor unless the API path is blocked.

## Preconditions

- Project ref: `memscjotxrzqnhrvnnkc`
- Access token: `/root/.clawdbot/secrets.json` -> `supabase_access_token`
- Migration SQL file in `supabase/migrations/`

## Get Token

```bash
# On VPS
TOKEN=$(python3 -c "import json,pathlib; print(json.loads(pathlib.Path('/root/.clawdbot/secrets.json').read_text())['supabase_access_token'])")

# Via SSH from local
TOKEN=$(ssh root@134.199.153.159 "python3 -c \"import json,pathlib; print(json.loads(pathlib.Path('/root/.clawdbot/secrets.json').read_text())['supabase_access_token'])\"")
```

## Run Migration

### With jq (Linux/VPS)

```bash
PROJECT_REF="memscjotxrzqnhrvnnkc"
SQL_FILE="/absolute/path/to/migration.sql"

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}')"
```

### With Node (Windows/cross-platform)

```bash
cd /path/to/rateright-growth-deploy
node -e "
const fs = require('fs'), https = require('https'), path = require('path');
const sql = fs.readFileSync(path.join(process.cwd(), 'supabase/migrations/YOUR_MIGRATION.sql'), 'utf8');
const body = JSON.stringify({ query: sql });
const req = https.request({
  hostname: 'api.supabase.com',
  path: '/v1/projects/memscjotxrzqnhrvnnkc/database/query',
  method: 'POST',
  headers: {
    'Authorization': 'Bearer TOKEN_HERE',
    'Content-Type': 'application/json',
    'Content-Length': Buffer.byteLength(body),
  },
}, (res) => {
  let data = '';
  res.on('data', c => data += c);
  res.on('end', () => console.log('HTTP', res.statusCode, data));
});
req.write(body); req.end();
"
```

## Interpret Response

| Status | Meaning |
|--------|---------|
| 201 | Success |
| 400 + "already exists" | Schema already applied (idempotent, acceptable) |
| 401 | Token expired or invalid |
| Other | Stop and fix SQL |

## Verification

### Table Counts

```sql
SELECT
  (SELECT count(*) FROM cc_council_topics) as topics,
  (SELECT count(*) FROM cc_council_messages) as messages,
  (SELECT count(*) FROM cc_council_rounds) as rounds,
  (SELECT count(*) FROM cc_council_dispatches) as dispatches,
  (SELECT count(*) FROM cc_projects) as projects,
  (SELECT count(*) FROM cc_tasks) as tasks;
```

### Realtime Publication

```sql
SELECT schemaname, tablename
FROM pg_publication_tables
WHERE pubname = 'supabase_realtime'
  AND tablename LIKE 'cc_%'
ORDER BY tablename;
```

### Column Verification (after ALTER TABLE)

```sql
SELECT column_name, data_type, column_default
FROM information_schema.columns
WHERE table_name = 'cc_council_messages'
  AND column_name IN ('round_id','message_type','reply_to','structured_data','dispatch_status')
ORDER BY column_name;
```

## Migration History

| Date | File | Tables/Changes | Result |
|------|------|----------------|--------|
| 2026-03-03 | `20260303000000_control_centre.sql` | 8 cc_* tables, RLS, realtime, seed data | Applied |
| 2026-03-06 | `20260306000000_council_router.sql` | cc_council_rounds, cc_council_dispatches, 5 new columns on cc_council_messages, indexes | Applied |