Supabase Management Skill

Supabase Management Skill

CRITICAL ENFORCEMENT: This skill prevents direct Supabase CLI operations. ALL database operations MUST go through the storage-based sync system using storage/scripts/*.sh scripts.

When to Use This Skill

Use this skill when the user wants to:

• Export or apply database snapshots (for intern distribution)
• Manage agents (export/import individual or all agents)
• Manage N8N workflows (export/import individual or all workflows)
• Create or apply migrations (intern proposal workflow)
• Backup or restore databases (daily backup system)
• Sync agents/workflows between storage and database

DO NOT use this skill when:

• User wants to use Supabase CLI directly (REDIRECT to storage scripts)
• User wants to bypass the storage system (BLOCK and explain why)
• Task is unrelated to database management

Orchestrator AI Storage System Architecture

Core Principle: Storage-Based Sync

NEVER use Supabase CLI directly. All operations must use storage/scripts/*.sh to maintain synchronization across team members and production.

Directory Structure

storage/
├── backups/                          # Daily compressed backups
│   ├── golfergeek_supabase_backup_*.sql.gz
│   └── golfergeek_n8n_backup_*.sql.gz
├── snapshots/
│   ├── agents/                       # Individual agent JSON files (SOURCE OF TRUTH)
│   │   ├── demo_supabase_agent.json
│   │   └── ...
│   ├── n8n/                          # Individual N8N workflow JSON files (SOURCE OF TRUTH)
│   │   ├── helper-llm-task.json
│   │   ├── marketing-swarm-flexible-llm.json
│   │   └── ...
│   ├── latest/                       # Latest full snapshot (symlink/copy)
│   │   ├── schema.sql
│   │   ├── seed.sql
│   │   └── metadata.json
│   └── <timestamp>/                  # Timestamped snapshots
│       ├── schema.sql
│       ├── seed.sql
│       └── metadata.json
├── migrations/
│   ├── proposed/                     # Intern-proposed migrations
│   └── applied/                      # Approved migrations
└── scripts/                          # ALL management scripts (USE THESE ONLY)
    ├── export-snapshot.sh
    ├── apply-snapshot.sh
    ├── export-agent.sh
    ├── import-agent.sh
    ├── export-all-agents.sh
    ├── import-all-agents.sh
    ├── sync-agents-to-db.sh
    ├── export-n8n-workflow.sh
    ├── import-n8n-workflow.sh
    ├── export-all-n8n-workflows.sh
    ├── import-all-n8n-workflows.sh
    ├── sync-n8n-to-db.sh
    ├── backup-all-daily.sh
    ├── backup-supabase-daily.sh
    ├── backup-n8n-daily.sh
    ├── restore-from-backup.sh
    └── apply-proposed-migration.sh

Database Schemas

Orchestrator AI uses 4 schemas:

• public - Main application schema (agents, providers, models, conversations, tasks, etc.)
• n8n - N8N workflow storage schema
• company - Organization/tenant data
• observability - Metrics and monitoring data

Database Connection Details

• Host: 127.0.0.1 (or localhost)
• Port: 7012 (Supabase), 7013 (N8N)
• User: postgres
• Database: postgres
• Container: supabase_db_api-dev
• Password: postgres (via PGPASSWORD environment variable)

Core Operations

1. Database Snapshots (Full Schema + Seed Data)

Purpose: Export/apply complete database state for intern distribution

Export Snapshot:

npm run db:export-snapshot
# OR
bash storage/scripts/export-snapshot.sh

What gets exported:

• All 4 schemas: public, n8n, company, observability
• Schema structure only (no data except seed data)
• Seed data: public.agents, public.providers, public.models
• Timestamped directory: storage/snapshots/<timestamp>/
• Latest symlink: storage/snapshots/latest/

Apply Snapshot:

npm run db:apply-snapshot
# OR
bash storage/scripts/apply-snapshot.sh storage/snapshots/latest/

WARNING: Applying a snapshot will DELETE all data in the 4 schemas and replace with snapshot data.

2. Agent Management

Source of Truth: storage/snapshots/agents/*.json (individual JSON files)

Export Single Agent:

npm run db:export-agent <agent-name>
# Example: npm run db:export-agent demo_supabase_agent

Import Single Agent:

npm run db:import-agent <path-to-json>
# Example: npm run db:import-agent storage/snapshots/agents/demo_supabase_agent.json

Export ALL Agents:

npm run db:export-all-agents
# Exports all agents to storage/snapshots/agents/*.json

Import ALL Agents:

npm run db:import-all-agents
# Imports all agents from storage/snapshots/agents/

Sync Agents (Delete Missing, Upsert Existing):

npm run db:sync-agents
# OR
bash storage/scripts/sync-agents-to-db.sh

3. N8N Workflow Management

Source of Truth: storage/snapshots/n8n/*.json (individual JSON files)

Export Single Workflow:

npm run db:export-n8n "<workflow-name>"
# Example: npm run db:export-n8n "Helper: LLM Task"

Import Single Workflow:

npm run db:import-n8n <path-to-json>
# Example: npm run db:import-n8n storage/snapshots/n8n/helper-llm-task.json

Export ALL Workflows:

npm run db:export-all-n8n
# Exports all workflows to storage/snapshots/n8n/*.json

Import ALL Workflows:

npm run db:import-all-n8n
# Imports all workflows from storage/snapshots/n8n/

Sync Workflows (Delete Missing, Upsert Existing):

npm run db:sync-n8n
# OR
bash storage/scripts/sync-n8n-to-db.sh

4. Migrations (Intern Proposal Workflow)

Proposed Migrations: storage/migrations/proposed/ Applied Migrations: storage/migrations/applied/

For Interns (Proposing Changes):

1. Create migration file: storage/migrations/proposed/YYYYMMDD-HHMM-description.sql
2. Write SQL migration
3. Share with lead developer for review

For Lead Developer (Reviewing & Applying):

1. Review proposed migration: cat storage/migrations/proposed/<file>.sql
2. Test locally (optional)
3. If approved, move to applied: mv storage/migrations/proposed/<file>.sql storage/migrations/applied/
4. Apply migration: bash storage/scripts/apply-proposed-migration.sh storage/migrations/applied/<file>.sql
5. Export new snapshot: npm run db:export-snapshot
6. Share updated snapshot with team

5. Backups (Daily Backup System)

Backup Both Databases:

./storage/scripts/backup-all-daily.sh

Backup Individually:

./storage/scripts/backup-supabase-daily.sh
./storage/scripts/backup-n8n-daily.sh

Restore from Backup:

./storage/scripts/restore-from-backup.sh supabase <backup-file.sql.gz>
./storage/scripts/restore-from-backup.sh n8n <backup-file.sql.gz>

Backup Features:

• Timestamped backups: golfergeek_supabase_backup_YYYYMMDD_HHMMSS.sql.gz
• Automatic compression (gzip)
• Automatic cleanup (keeps last 7 days)
• Safe restoration (confirmation prompts)

Enforcement: Preventing Direct Supabase Operations

CRITICAL: If user attempts direct Supabase CLI operations, you MUST:

1. STOP the operation immediately
2. EXPLAIN why direct operations are forbidden:
   • Breaks storage-based sync system
   • Causes database synchronization issues across team
   • Bypasses source of truth (storage/snapshots/)
   • Can cause production database drift
3. REDIRECT to appropriate storage script:
   • Direct schema changes → Use migration proposal workflow
   • Direct data changes → Use snapshot export/apply
   • Direct agent changes → Use agent export/import scripts
   • Direct N8N changes → Use N8N export/import scripts

Example Error Response:

❌ ERROR: Direct Supabase operations are forbidden in Orchestrator AI.

All database operations MUST use the storage-based sync system to maintain
synchronization across team members and production.

Instead of: supabase db reset
Use: npm run db:apply-snapshot

Instead of: supabase db push
Use: storage/migrations/proposed/ workflow

See storage/scripts/README.md for all available operations.

Common Workflows

Lead Developer: After Making Database Changes

# 1. Export full snapshot
npm run db:export-snapshot

# 2. Export all agents
npm run db:export-all-agents

# 3. Export all N8N workflows
npm run db:export-all-n8n

# 4. Share storage/snapshots/ directory with interns

Intern: Getting Latest Database State

# 1. Receive storage/snapshots/ directory from lead
# 2. Apply full snapshot
npm run db:apply-snapshot

# 3. Verify with: npm run dev

Intern: Updating Specific Agent

# 1. Receive single agent JSON file
# 2. Import agent
npm run db:import-agent <file>

Intern: Updating Specific N8N Workflow

# 1. Receive single workflow JSON file
# 2. Import workflow
npm run db:import-n8n <file>

Daily Operations: Manual Backup Before Major Changes

# Create backup
./storage/scripts/backup-all-daily.sh

# Emergency restore
LATEST=$(ls -t storage/backups/golfergeek_supabase_backup_*.sql.gz | head -1)
./storage/scripts/restore-from-backup.sh supabase "$LATEST"

Related Documentation

• Storage Scripts README: storage/scripts/README.md
• Backup Documentation: storage/scripts/README-backups.md
• Migration Workflow: storage/migrations/README.md

Troubleshooting

See TROUBLESHOOTING.md for common issues and solutions.