# VCSYS Supabase CLI Command Reference Guide
# CRITICAL: Database Wizard MUST reference this file before ANY provisioning operations
# This file provides definitive command syntax and project detection logic

metadata:
  version: "1.0.0"
  purpose: "Authoritative command reference for Database Wizard Supabase operations"
  last_updated: "2025-01-14"
  critical_notice: "Database Wizard MUST read this file first before provisioning"

# Project Detection Logic - ALWAYS RUN FIRST
project_detection:
  description: "Determine how to execute vcsys commands based on project structure"
  
  detection_steps:
    1_check_npm_package:
      command: "ls -la node_modules/@vcsys/cli/dist/cli/index.js"
      if_found: "use npm_package_method"
      if_not_found: "continue to step 2"
    
    2_check_local_development:
      command: "ls -la src/cli/index.ts && ls -la package.json"
      if_found: "validate package.json has vcsys scripts, then use local_development_method"
      if_not_found: "continue to step 3"
      validation: "grep -q 'vcsys' package.json"
      validation_failure: "continue to step 3 (incomplete development setup)"
    
    3_check_global_install:
      command: "which vcsys"
      if_found: "use global_install_method"
      if_not_found: "show installation_error"

  execution_methods:
    npm_package_method:
      description: "Project has @vcsys/cli installed as npm dependency"
      command_prefix: "npx vcsys"
      example: "npx vcsys auth status"
      
    local_development_method:
      description: "Working in vcsys-cli development environment"
      command_prefix: "npm start --"
      example: "npm start -- vcsys auth status"
      
    global_install_method:
      description: "vcsys CLI installed globally"
      command_prefix: "vcsys"
      example: "vcsys auth status"
      
    installation_error:
      message: "VCSYS CLI not found. Install with: npm install @vcsys/cli"
      action: "Stop workflow and request user to install CLI"
      
    incomplete_development_setup:
      message: "Found src/cli/index.ts but no valid package.json with vcsys scripts"
      solution: "This appears to be an incomplete development setup. Use npm package method instead."
      fallback_action: "Install @vcsys/cli as npm package: npm install @vcsys/cli"

# Core Authentication Commands
authentication:
  check_status:
    purpose: "Check if user is authenticated with Supabase"
    command: "{prefix} auth status"
    success_indicators: ["Status: Authenticated", "✅"]
    failure_indicators: ["Not authenticated", "❌"]
    
  login:
    purpose: "Authenticate with Supabase via OAuth"
    command: "{prefix} auth login"
    expected_flow: 
      - "Opens browser for OAuth"
      - "User completes authentication"
      - "Returns success message"
    
  logout:
    purpose: "Clear authentication tokens"
    command: "{prefix} auth logout"

# Data Gathering Commands - RUN BEFORE PROVISIONING
data_gathering:
  list_organizations:
    purpose: "Get available Supabase organizations"
    command: "{prefix} provision --list-orgs"
    output_format: "JSON with organization names and IDs"
    required_for: "Provisioning - user must select org"
    
  list_regions:
    purpose: "Get available deployment regions"
    command: "{prefix} provision --list-regions" 
    output_format: "JSON with region codes and descriptions"
    required_for: "Provisioning - user must select region"
    
  list_templates:
    purpose: "Get available schema templates"
    command: "{prefix} provision --list-templates"
    output_format: "JSON with template names and descriptions"
    required_for: "Template-based provisioning"

# Core Provisioning Commands
provisioning:
  template_based:
    purpose: "Provision Supabase project with built-in template"
    command: "{prefix} provision --org \"{org_name}\" --name \"{project_name}\" --template \"{template_name}\""
    parameters:
      org_name: "Exact organization name from --list-orgs"
      project_name: "New project name (alphanumeric, hyphens)"
      template_name: "Template name from --list-templates (e.g., 'saas-starter')"
    example: "npx vcsys provision --org \"My Company\" --name \"my-saas-app\" --template \"saas-starter\""
    
  custom_schema:
    purpose: "Provision Supabase project with custom SQL schema"
    command: "{prefix} provision --org \"{org_name}\" --name \"{project_name}\" --schema \"{schema_path}\""
    parameters:
      org_name: "Exact organization name from --list-orgs"  
      project_name: "New project name (alphanumeric, hyphens)"
      schema_path: "Path to SQL file (e.g., '.vcsys/generated-schema.sql')"
    example: "npx vcsys provision --org \"My Company\" --name \"my-saas-app\" --schema \"./.vcsys/generated-schema.sql\""

# Standard File Locations
file_locations:
  generated_schema: ".vcsys/generated-schema.sql"
  env_file: ".env.local"
  backup_location: ".vcsys/backups/"
  logs: ".vcsys/logs/supabase.log"

# Error Handling Patterns
error_patterns:
  authentication_required:
    indicators: ["not authenticated", "401", "unauthorized"]
    solution: "Run authentication: {prefix} auth login"
    
  invalid_organization:
    indicators: ["organization not found", "invalid org"]
    solution: "Get valid orgs: {prefix} provision --list-orgs"
    
  project_name_exists:
    indicators: ["project already exists", "name conflict"]
    solution: "Use different project name"
    
  schema_file_missing:
    indicators: ["schema file not found", "cannot read schema"]
    solution: "Check file path exists and is readable"
    
  insufficient_permissions:
    indicators: ["permission denied", "access denied"]
    solution: "Check Supabase account permissions for organization"

# Local Supabase Development Commands
local_development:
  prerequisites:
    docker_check:
      command: "docker --version && docker info"
      purpose: "Verify Docker is installed and running"
      success_indicators: ["Docker version", "Server Version"]
      failure_action: "Install Docker Desktop or start Docker service"
      
    node_version_check:
      command: "node --version"
      purpose: "Verify Node.js version compatibility"
      required_version: "20.19.4"
      version_managers: ["nvm", "n", "fnm"]
      
    port_availability:
      command: "lsof -i :54321,54322,54323,54324 || netstat -an | grep -E ':(54321|54322|54323|54324) '"
      purpose: "Check for port conflicts on Supabase default ports"
      required_ports: [54321, 54322, 54323, 54324, 54325, 54326, 54327, 54329]
      conflict_resolution: "Stop conflicting services or modify supabase/config.toml"

  initialization:
    install_cli:
      purpose: "Install Supabase CLI with compatibility fallback"
      primary_command: "npm install supabase --save-dev"
      fallback_command: "npm install supabase@2.32.0 --save-dev"
      fallback_trigger: "Node.js syntax errors in Docker containers"
      
    project_init:
      command: "npx supabase init"
      purpose: "Initialize Supabase project in current directory"
      creates: ["supabase/config.toml", "supabase/migrations/", "supabase/seed.sql"]
      
    configure_project:
      purpose: "Set project ID and optimize configuration"
      config_file: "supabase/config.toml"
      default_ports:
        api: 54321
        db: 54322
        studio: 54323
        inbucket: 54324

  service_management:
    start_services:
      command: "npx supabase start"
      purpose: "Start all Supabase services with Docker"
      startup_time: "60-120 seconds"
      health_check_timeout: 300
      
    stop_services:
      command: "npx supabase stop"
      purpose: "Stop all Supabase services"
      cleanup: "Preserves data volumes"
      
    service_status:
      command: "npx supabase status"
      purpose: "Check status of all services and get connection details"
      output_includes: ["API URL", "DB URL", "Studio URL", "anon key", "service_role key"]
      
    reset_database:
      command: "npx supabase db reset"
      purpose: "Reset database with fresh migrations"
      warning: "Destroys all existing data"

  troubleshooting:
    container_logs:
      command: "docker logs [container_name]"
      purpose: "View logs for specific Supabase container"
      containers: ["supabase_db", "supabase_studio", "supabase_storage", "supabase_auth"]
      
    clean_restart:
      commands:
        - "npx supabase stop"
        - "docker stop $(docker ps --filter 'name=supabase' -q)"
        - "docker rm $(docker ps --filter 'name=supabase' -aq)"
        - "npx supabase start"
      purpose: "Clean restart of all Supabase containers"
      
    cli_rollback:
      commands:
        - "npm uninstall supabase"
        - "npm install supabase@2.32.0 --save-dev"
      trigger: "SyntaxError or Node.js compatibility issues"
      purpose: "Rollback to stable CLI version"

  environment_setup:
    local_env_variables:
      file: ".env.local"
      variables:
        - "NEXT_PUBLIC_SUPABASE_URL=http://127.0.0.1:54321"
        - "NEXT_PUBLIC_SUPABASE_ANON_KEY=[from supabase status]"
        - "SUPABASE_SERVICE_ROLE_KEY=[from supabase status]"
        - "DATABASE_URL=[from supabase status]"
        - "SUPABASE_STUDIO_URL=http://127.0.0.1:54323"
        - "SUPABASE_ENVIRONMENT=local"
        - "SUPABASE_LOCAL_MODE=true"
        
    studio_access:
      url: "http://127.0.0.1:54323"
      purpose: "Database management and query interface"
      credentials: "No authentication required for local development"

# Database Wizard Integration Workflow
wizard_workflow:
  phase_1_detection:
    step: "Run project_detection to determine command prefix"
    critical: true
    
  phase_2_deployment_choice:
    step: "Ask user: Local development environment OR Live cloud deployment?"
    local_option: "Use provision-supabase-local.md task for Docker-based local setup"
    cloud_option: "Continue with cloud provisioning workflow"
    
  phase_3_authentication:
    step: "If cloud deployment: Check authentication status with {prefix} auth status"
    if_not_authenticated: "Run {prefix} auth login"
    local_deployment_skip: "Skip authentication for local development"
    
  phase_4_data_gathering:
    step: "If cloud deployment: Gather organizations, regions, templates"
    commands:
      - "{prefix} provision --list-orgs"
      - "{prefix} provision --list-regions" 
      - "{prefix} provision --list-templates"
    local_deployment_skip: "Skip data gathering for local development"
    
  phase_5_schema_decision:
    step: "If cloud deployment: Choose schema approach"
    options:
      custom_schema: "Use --schema parameter with generated file"
      template_schema: "Use --template parameter with built-in template"
      user_provided: "Use --schema parameter with user's file path"
    local_deployment_note: "Local deployment supports optional schema file integration"
      
  phase_6_provisioning:
    step: "Execute appropriate provision method"
    local_deployment: "Run provision-supabase-local.md task with Docker setup"
    cloud_deployment: "Execute cloud provision command using detected prefix"
    validation: "Confirm deployment success and get connection details"
    
  phase_7_validation:
    step: "Verify deployment success"
    local_outputs: "Provide local Studio URL (http://127.0.0.1:54323), API endpoints, .env.local setup"
    cloud_outputs: "Provide live project URL, API keys, connection details"

# Claude Code Integration Output
claude_integration:
  structured_output:
    format: "JSON wrapped in --- CLAUDE CODE INTEGRATION ---"
    location: "Commands output this automatically for parsing"
    example: |
      --- CLAUDE CODE INTEGRATION ---
      {
        "organizations": [{"name": "My Company", "id": "abc123"}],
        "regions": [{"code": "us-east-1", "name": "US East"}],
        "templates": [{"name": "saas-starter", "description": "SaaS template"}]
      }
      --- END INTEGRATION DATA ---

# Success Patterns
success_indicators:
  authentication: ["✅ Status: Authenticated", "Connected successfully"]
  provisioning: ["✅ Project created successfully", "Database provisioned"]
  deployment: ["✅ Deployment complete", "Your Supabase project is ready"]

# Common Issues & Solutions
troubleshooting:
  command_not_found:
    issue: "vcsys command not recognized"
    solution: "Follow project_detection workflow"
    
  oauth_timeout:
    issue: "Browser authentication timeout"
    solution: "Retry auth login, check browser popup blockers"
    
  schema_syntax_error:
    issue: "SQL syntax error in custom schema"
    solution: "Validate SQL file, check PostgreSQL compatibility"
    
  network_connectivity:
    issue: "Cannot connect to Supabase API"
    solution: "Check internet connection, verify API endpoints"