feat(cli): add Supabase mode and JSON output for prepare-db command

Summary

This MR adds two features to the prepare-db CLI command:

1. Supabase Mode (--supabase)

Execute database setup via Supabase Management API instead of direct PostgreSQL connection. Useful when direct database access is restricted.

# Using environment variables
export SUPABASE_ACCESS_TOKEN='your_management_api_token'
export SUPABASE_PROJECT_REF='your_project_ref'
npx postgresai prepare-db --supabase

# Using command-line options
npx postgresai prepare-db --supabase \
  --supabase-access-token 'your_token' \
  --supabase-project-ref 'your_project_ref'

# Auto-detect project ref from a Supabase database URL
npx postgresai prepare-db postgresql://postgres:password@db.abc123.supabase.co:5432/postgres \
  --supabase --supabase-access-token 'your_token'

Options:

  • --supabase - Enable Supabase Management API mode
  • --supabase-access-token <token> - Supabase Management API access token (or SUPABASE_ACCESS_TOKEN env)
  • --supabase-project-ref <ref> - Supabase project reference (or SUPABASE_PROJECT_REF env)

Security features:

  • SQL injection prevention via escapeLiteral() (null byte check) and isValidIdentifier()
  • Path traversal prevention via isValidProjectRef() validation
  • Access tokens never logged or included in error output

2. JSON Output (--json)

Machine-readable JSON output for both success and error cases.

Success output:

{
  "success": true,
  "mode": "supabase" | "direct",
  "action": "apply" | "reset-password" | "verify",
  "database": "postgres",
  "monitoringUser": "postgres_ai_mon",
  "applied": ["01.role", "02.permissions", ...],
  "skippedOptional": ["03.optional_rds"],
  "warnings": [],
  "generatedPassword": "..." // only if auto-generated
}

Error output:

{
  "success": false,
  "mode": "supabase" | "direct",
  "error": {
    "message": "...",
    "step": "01.role",
    "code": "42501",
    "detail": "...",
    "hint": "...",
    "httpStatus": 403
  }
}

Files Changed

  • cli/lib/supabase.ts - New Supabase client module
  • cli/bin/postgres-ai.ts - Added --supabase and --json options
  • cli/test/supabase.test.ts - Comprehensive tests (100% line coverage)
  • cli/README.md - Documentation for Supabase mode

Test Plan

  • Unit tests for Supabase client (35 tests, 100% line coverage)
  • Tests for URL extraction (direct, legacy pooler, modern AWS pooler)
  • Tests for security validations (null bytes, path traversal, invalid identifiers)
  • Manual testing with real Supabase project (verified working)
  • Security review passed (no issues found)
  • Bug review passed (all issues fixed)
Edited by Nikolay Samokhvalov

Merge request reports

Loading