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 (orSUPABASE_ACCESS_TOKENenv) -
--supabase-project-ref <ref>- Supabase project reference (orSUPABASE_PROJECT_REFenv)
Security features:
- SQL injection prevention via
escapeLiteral()(null byte check) andisValidIdentifier() - 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