tool:
  schema_version: 2.0
  id: supabase
  type: mcp
  name: Supabase Platform Management
  version: 1.0.0
  description: Supabase project and database management with SQL execution, migrations, RLS policies, and real-time subscriptions
  knowledge_strategy: executable

  executable_knowledge:
    validators:
      # Validator for execute_sql
      - id: validate-execute-sql
        validates: execute_sql
        language: javascript
        checks:
          - required_fields: [project_id, query]
          - sql_injection_prevention: true
        function: |
          (function() {
            const errors = [];

            // 1. Required fields
            if (!args.args.project_id) {
              errors.push("project_id is required");
            }
            if (!args.args.query) {
              errors.push("query is required");
            }

            // 2. Validate project_id format (proj_xxx)
            if (args.args.project_id && !/^proj_[a-z0-9_]+$/i.test(args.args.project_id)) {
              errors.push("project_id must match format: proj_[id]");
            }

            // 3. Basic SQL injection prevention
            if (args.args.query) {
              const query = args.args.query.toLowerCase();
              // Check for dangerous patterns
              if (query.includes('drop table') || query.includes('drop database')) {
                errors.push("DROP operations not allowed via execute_sql - use apply_migration");
              }
              // Check for DDL operations
              if (query.match(/\b(create|alter|drop)\s+(table|index|view|function|trigger)/)) {
                errors.push("DDL operations not allowed in execute_sql - use apply_migration instead");
              }
            }

            return {
              valid: errors.length === 0,
              errors: errors
            };
          })();

      # Validator for apply_migration
      - id: validate-apply-migration
        validates: apply_migration
        language: javascript
        checks:
          - required_fields: [project_id, name, query]
          - migration_name_format: snake_case
        function: |
          (function() {
            const errors = [];

            // 1. Required fields
            if (!args.args.project_id) {
              errors.push("project_id is required");
            }
            if (!args.args.name) {
              errors.push("name is required");
            }
            if (!args.args.query) {
              errors.push("query is required");
            }

            // 2. Validate migration name format (snake_case)
            if (args.args.name && !/^[a-z0-9_]+$/.test(args.args.name)) {
              errors.push("migration name must be snake_case (lowercase letters, numbers, underscores only)");
            }

            // 3. Check for hardcoded IDs in data migrations
            if (args.args.query && args.args.query.match(/\b[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\b/i)) {
              errors.push("WARNING: Migration contains hardcoded UUID - consider using generated IDs instead");
            }

            return {
              valid: errors.length === 0,
              errors: errors
            };
          })();

      # Validator for create_branch
      - id: validate-create-branch
        validates: create_branch
        language: javascript
        checks:
          - required_fields: [project_id, confirm_cost_id]
          - branch_name_format: true
        function: |
          (function() {
            const errors = [];

            // 1. Required fields
            if (!args.args.project_id) {
              errors.push("project_id is required");
            }
            if (!args.args.confirm_cost_id) {
              errors.push("confirm_cost_id is required - call confirm_cost first");
            }

            // 2. Validate branch name if provided
            if (args.args.name && !/^[a-z0-9-]+$/.test(args.args.name)) {
              errors.push("branch name must be lowercase letters, numbers, and hyphens only");
            }

            return {
              valid: errors.length === 0,
              errors: errors
            };
          })();

      # Validator for deploy_edge_function
      - id: validate-deploy-edge-function
        validates: deploy_edge_function
        language: javascript
        checks:
          - required_fields: [project_id, name, files]
          - file_structure: true
        function: |
          (function() {
            const errors = [];

            // 1. Required fields
            if (!args.args.project_id) {
              errors.push("project_id is required");
            }
            if (!args.args.name) {
              errors.push("name is required");
            }
            if (!args.args.files) {
              errors.push("files array is required");
            }

            // 2. Validate files structure
            if (args.args.files) {
              if (!Array.isArray(args.args.files)) {
                errors.push("files must be an array");
              } else {
                args.args.files.forEach((file, index) => {
                  if (!file.name) {
                    errors.push(`files[${index}] missing required 'name' field`);
                  }
                  if (!file.content) {
                    errors.push(`files[${index}] missing required 'content' field`);
                  }
                });
              }
            }

            // 3. Check for entrypoint
            if (args.args.entrypoint_path && !args.args.entrypoint_path.endsWith('.ts')) {
              errors.push("entrypoint_path should be a TypeScript file (.ts)");
            }

            return {
              valid: errors.length === 0,
              errors: errors
            };
          })();

      # Validator for create_project
      - id: validate-create-project
        validates: create_project
        language: javascript
        checks:
          - required_fields: [name, region, organization_id, confirm_cost_id]
          - region_validation: true
        function: |
          (function() {
            const errors = [];

            // 1. Required fields
            if (!args.args.name) {
              errors.push("name is required");
            }
            if (!args.args.region) {
              errors.push("region is required");
            }
            if (!args.args.organization_id) {
              errors.push("organization_id is required");
            }
            if (!args.args.confirm_cost_id) {
              errors.push("confirm_cost_id is required - call confirm_cost first");
            }

            // 2. Validate region
            const validRegions = [
              'us-west-1', 'us-east-1', 'us-east-2', 'ca-central-1',
              'eu-west-1', 'eu-west-2', 'eu-west-3', 'eu-central-1', 'eu-central-2', 'eu-north-1',
              'ap-south-1', 'ap-southeast-1', 'ap-northeast-1', 'ap-northeast-2', 'ap-southeast-2',
              'sa-east-1'
            ];

            if (args.args.region && !validRegions.includes(args.args.region)) {
              errors.push(`region must be one of: ${validRegions.join(', ')}`);
            }

            return {
              valid: errors.length === 0,
              errors: errors
            };
          })();

    helpers:
      - id: build-select-query
        language: javascript
        runtime: isolated_vm
        description: "Build SELECT query with RLS considerations"
        function: |
          (function() {
            const { table, columns, where, orderBy, limit, checkRLS } = args;

            if (!table) {
              return { error: 'Table name is required' };
            }

            let query = 'SELECT ';
            query += columns && columns.length > 0 ? columns.join(', ') : '*';
            query += ` FROM ${table}`;

            if (where && typeof where === 'object') {
              const conditions = Object.entries(where)
                .map(([key, value]) => {
                  if (typeof value === 'string') {
                    return `${key} = '${value}'`;
                  }
                  return `${key} = ${value}`;
                })
                .join(' AND ');
              query += ` WHERE ${conditions}`;
            }

            if (orderBy) {
              query += ` ORDER BY ${orderBy}`;
            }

            if (limit) {
              query += ` LIMIT ${limit}`;
            }

            return {
              query: query,
              requiresRLS: checkRLS !== false,
              hint: checkRLS !== false ? 'Ensure RLS policies are enabled on this table' : null
            };
          })();

      - id: build-insert-query
        language: javascript
        runtime: isolated_vm
        description: "Build INSERT query with RLS validation"
        function: |
          (function() {
            const { table, data, returning } = args;

            if (!table) {
              return { error: 'Table name is required' };
            }

            if (!data || typeof data !== 'object') {
              return { error: 'Data object is required' };
            }

            const columns = Object.keys(data);
            const values = Object.values(data).map(v => {
              if (typeof v === 'string') {
                return `'${v}'`;
              }
              if (v === null) {
                return 'NULL';
              }
              return v;
            });

            let query = `INSERT INTO ${table} (${columns.join(', ')}) VALUES (${values.join(', ')})`;

            if (returning) {
              query += ` RETURNING ${returning === true ? '*' : returning}`;
            }

            return {
              query: query,
              requiresRLS: true,
              hint: 'Ensure user has INSERT permissions via RLS policy'
            };
          })();

      - id: build-update-query
        language: javascript
        runtime: isolated_vm
        description: "Build UPDATE query with RLS validation"
        function: |
          (function() {
            const { table, data, where, returning } = args;

            if (!table) {
              return { error: 'Table name is required' };
            }

            if (!data || typeof data !== 'object') {
              return { error: 'Data object is required' };
            }

            if (!where || typeof where !== 'object') {
              return { error: 'WHERE condition is required for UPDATE' };
            }

            const setClauses = Object.entries(data).map(([key, value]) => {
              if (typeof value === 'string') {
                return `${key} = '${value}'`;
              }
              if (value === null) {
                return `${key} = NULL`;
              }
              return `${key} = ${value}`;
            });

            const whereClauses = Object.entries(where).map(([key, value]) => {
              if (typeof value === 'string') {
                return `${key} = '${value}'`;
              }
              return `${key} = ${value}`;
            });

            let query = `UPDATE ${table} SET ${setClauses.join(', ')} WHERE ${whereClauses.join(' AND ')}`;

            if (returning) {
              query += ` RETURNING ${returning === true ? '*' : returning}`;
            }

            return {
              query: query,
              requiresRLS: true,
              hint: 'Ensure user has UPDATE permissions via RLS policy'
            };
          })();

      - id: validate-rls-policy
        language: javascript
        runtime: isolated_vm
        description: "Validate RLS policy syntax and structure"
        function: |
          (function() {
            const { policy, table, operation } = args;

            if (!policy || typeof policy !== 'object') {
              return { valid: false, errors: ['Policy object is required'] };
            }

            const errors = [];

            // Check required fields
            if (!policy.name) {
              errors.push('Policy name is required');
            }

            if (!table) {
              errors.push('Table name is required');
            }

            if (!operation) {
              errors.push('Operation is required (SELECT, INSERT, UPDATE, DELETE, ALL)');
            }

            // Validate operation
            const validOperations = ['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'ALL'];
            if (operation && !validOperations.includes(operation.toUpperCase())) {
              errors.push(`Operation must be one of: ${validOperations.join(', ')}`);
            }

            // Check for USING clause (required for SELECT, UPDATE, DELETE, ALL)
            if (!policy.using && ['SELECT', 'UPDATE', 'DELETE', 'ALL'].includes(operation?.toUpperCase())) {
              errors.push('USING clause is required for this operation');
            }

            // Check for WITH CHECK clause (required for INSERT, UPDATE)
            if (!policy.withCheck && ['INSERT', 'UPDATE'].includes(operation?.toUpperCase())) {
              errors.push('WITH CHECK clause recommended for this operation');
            }

            return {
              valid: errors.length === 0,
              errors: errors,
              warnings: policy.withCheck ? [] : ['Consider adding WITH CHECK clause for additional security']
            };
          })();

      - id: format-realtime-subscription
        language: javascript
        runtime: isolated_vm
        description: "Format real-time subscription configuration"
        function: |
          (function() {
            const { table, event, filter, schema } = args;

            if (!table) {
              return { error: 'Table name is required' };
            }

            const events = event ? (Array.isArray(event) ? event : [event]) : ['INSERT', 'UPDATE', 'DELETE'];

            const config = {
              event: events.join(','),
              schema: schema || 'public',
              table: table
            };

            if (filter) {
              config.filter = filter;
            }

            return {
              channel: `${schema || 'public'}:${table}`,
              config: config,
              example: `
                const channel = supabase
                  .channel('${schema || 'public'}:${table}')
                  .on('postgres_changes', ${JSON.stringify(config, null, 2)}, (payload) => {
                    console.log('Change received!', payload)
                  })
                  .subscribe()
              `
            };
          })();

      - id: validate-table-permissions
        language: javascript
        runtime: isolated_vm
        description: "Validate table permissions and RLS status"
        function: |
          (function() {
            const { table, hasRLS, policies } = args;

            if (!table) {
              return { error: 'Table name is required' };
            }

            const warnings = [];
            const recommendations = [];

            // Check if RLS is enabled
            if (hasRLS === false) {
              warnings.push('RLS is disabled - table data is publicly accessible');
              recommendations.push('Enable RLS with: ALTER TABLE ' + table + ' ENABLE ROW LEVEL SECURITY');
            }

            // Check if policies exist
            if (hasRLS && (!policies || policies.length === 0)) {
              warnings.push('RLS is enabled but no policies exist - table is inaccessible');
              recommendations.push('Create at least one RLS policy to allow access');
            }

            // Check for overly permissive policies
            if (policies && Array.isArray(policies)) {
              policies.forEach(policy => {
                if (policy.using === 'true' || policy.using === '(true)') {
                  warnings.push(`Policy '${policy.name}' allows access to all rows`);
                }
                if (policy.operation === 'ALL' && (policy.using === 'true' || !policy.using)) {
                  warnings.push(`Policy '${policy.name}' allows all operations without restrictions`);
                }
              });
            }

            return {
              table: table,
              rlsEnabled: hasRLS === true,
              policyCount: policies ? policies.length : 0,
              warnings: warnings,
              recommendations: recommendations,
              isSecure: hasRLS && policies && policies.length > 0
            };
          })();

      - id: parse-postgres-error
        language: javascript
        runtime: isolated_vm
        description: "Parse and format Postgres error messages"
        function: |
          (function() {
            const { error, query } = args;

            if (!error) {
              return null;
            }

            const result = {
              message: error.message || 'Unknown error',
              code: error.code || null,
              hint: null,
              context: query ? `Query: ${query.substring(0, 100)}...` : null
            };

            // Common Postgres error codes
            const errorMap = {
              '23505': 'Unique constraint violation',
              '23503': 'Foreign key constraint violation',
              '23502': 'Not null constraint violation',
              '23514': 'Check constraint violation',
              '42P01': 'Table does not exist',
              '42703': 'Column does not exist',
              '42501': 'Insufficient privilege (check RLS policies)',
              '42883': 'Function does not exist'
            };

            if (error.code && errorMap[error.code]) {
              result.hint = errorMap[error.code];
            }

            // RLS-specific hints
            if (error.code === '42501' || error.message?.includes('permission denied')) {
              result.hint = 'Permission denied - check RLS policies and user authentication';
              result.rlsHint = 'Ensure user is authenticated and RLS policy allows this operation';
            }

            return result;
          })();

      - id: generate-migration-name
        language: javascript
        runtime: isolated_vm
        description: "Generate timestamped migration name"
        function: |
          (function() {
            const { description } = args;

            if (!description) {
              return { error: 'Description is required' };
            }

            // Convert to snake_case
            const snakeCase = description
              .toLowerCase()
              .replace(/[^a-z0-9]+/g, '_')
              .replace(/^_|_$/g, '');

            // Generate timestamp (YYYYMMDDHHMMSS format)
            const now = new Date();
            const timestamp = now.toISOString()
              .replace(/[-:T.]/g, '')
              .substring(0, 14);

            const migrationName = `${timestamp}_${snakeCase}`;

            return {
              name: migrationName,
              timestamp: timestamp,
              description: snakeCase,
              example: `apply_migration({ name: "${migrationName}", query: "..." })`
            };
          })();

  api_complexity:
    rls_policy_patterns:
      - pattern: authenticated_user
        description: "Allow access only to authenticated users"
        example: |
          USING (auth.uid() IS NOT NULL)
        use_case: "Restrict access to logged-in users only"

      - pattern: user_owned_rows
        description: "Users can only access their own rows"
        example: |
          USING (auth.uid() = user_id)
        use_case: "Private user data (profiles, settings)"

      - pattern: role_based_access
        description: "Access based on user role"
        example: |
          USING (
            EXISTS (
              SELECT 1 FROM user_roles
              WHERE user_id = auth.uid()
              AND role IN ('admin', 'editor')
            )
          )
        use_case: "Admin panels, content management"

      - pattern: team_member_access
        description: "Access for team/organization members"
        example: |
          USING (
            EXISTS (
              SELECT 1 FROM team_members
              WHERE team_id = projects.team_id
              AND user_id = auth.uid()
            )
          )
        use_case: "Collaborative workspaces, multi-tenant apps"

    realtime_subscription_patterns:
      - pattern: table_changes
        description: "Subscribe to all changes on a table"
        config: |
          {
            event: '*',
            schema: 'public',
            table: 'messages'
          }
        use_case: "Real-time chat, activity feeds"

      - pattern: filtered_changes
        description: "Subscribe to specific rows"
        config: |
          {
            event: 'INSERT',
            schema: 'public',
            table: 'messages',
            filter: 'room_id=eq.123'
          }
        use_case: "Room-specific updates, filtered feeds"

      - pattern: user_specific_changes
        description: "Subscribe to user's own data"
        config: |
          {
            event: '*',
            schema: 'public',
            table: 'notifications',
            filter: 'user_id=eq.[USER_ID]'
          }
        use_case: "User notifications, personal updates"

    auth_integration_quirks:
      - quirk: auth_uid_null
        description: "auth.uid() returns NULL for unauthenticated requests"
        impact: "RLS policies using auth.uid() will block anonymous access"
        solution: "Use separate policies for authenticated and anonymous users"

      - quirk: jwt_token_expiry
        description: "JWT tokens expire after configured duration (default 1 hour)"
        impact: "Long-running operations may fail mid-execution"
        solution: "Implement token refresh logic in client applications"

      - quirk: rls_bypass_service_role
        description: "Service role key bypasses RLS policies"
        impact: "Backend operations ignore RLS - can access all data"
        solution: "Never expose service role key to clients - use anon/user keys only"

      - quirk: policy_evaluation_order
        description: "Multiple policies are OR'd together (any match grants access)"
        impact: "Cannot create deny policies - all policies must grant access"
        solution: "Design policies to be restrictive by default"

  anti_patterns:
    - pattern: missing_rls_policies
      description: "Enabling RLS without creating policies"
      category: security
      severity: high
      wrong: |
        ALTER TABLE users ENABLE ROW LEVEL SECURITY;
        -- ❌ No policies created - table is now inaccessible
      correct: |
        ALTER TABLE users ENABLE ROW LEVEL SECURITY;

        CREATE POLICY "Users can view own profile"
          ON users FOR SELECT
          USING (auth.uid() = id);

        CREATE POLICY "Users can update own profile"
          ON users FOR UPDATE
          USING (auth.uid() = id);
        -- ✅ RLS enabled with appropriate policies
      rationale: "RLS without policies makes tables completely inaccessible. Always create policies after enabling RLS."

    - pattern: overly_permissive_policies
      description: "Using USING (true) for all operations"
      category: security
      severity: critical
      wrong: |
        CREATE POLICY "Allow all"
          ON sensitive_data FOR ALL
          USING (true);
        -- ❌ Allows anyone to do anything
      correct: |
        CREATE POLICY "Allow authenticated users"
          ON sensitive_data FOR SELECT
          USING (auth.uid() IS NOT NULL AND auth.uid() = user_id);
        -- ✅ Restricts access to authenticated users and their own data
      rationale: "USING (true) defeats the purpose of RLS. Always implement meaningful access controls."

    - pattern: hardcoded_uuids_in_migrations
      description: "Hardcoding UUIDs in data migrations"
      category: migrations
      severity: medium
      wrong: |
        -- ❌ Hardcoded UUID will fail if record doesn't exist
        INSERT INTO projects (id, name, owner_id)
        VALUES ('123e4567-e89b-12d3-a456-426614174000', 'My Project', '...');
      correct: |
        -- ✅ Generate UUIDs or use RETURNING clause
        INSERT INTO projects (name, owner_id)
        VALUES ('My Project', (SELECT id FROM users WHERE email = 'admin@example.com'))
        RETURNING id;
      rationale: "Hardcoded UUIDs cause migration failures across environments. Use generated IDs or lookups."

    - pattern: ddl_in_execute_sql
      description: "Running DDL operations via execute_sql"
      category: migrations
      severity: high
      wrong: |
        execute_sql({
          query: "CREATE TABLE users (id uuid, name text)"
        })
        -- ❌ DDL not tracked in migrations
      correct: |
        apply_migration({
          name: "create_users_table",
          query: "CREATE TABLE users (id uuid PRIMARY KEY DEFAULT uuid_generate_v4(), name text NOT NULL)"
        })
        -- ✅ DDL tracked in migrations table
      rationale: "DDL operations must be tracked via migrations for version control and rollback capability."

  examples:
    execute_sql:
      - scenario: success
        description: "Execute SELECT query with RLS"
        input:
          project_id: "proj_abc123"
          query: "SELECT * FROM posts WHERE user_id = auth.uid()"
        output:
          rows: [{ id: 1, title: "My Post", user_id: "user_xyz" }]
          count: 1

      - scenario: failure_invalid_param
        description: "Missing required project_id"
        input:
          query: "SELECT * FROM posts"
        error:
          code: VALIDATION_ERROR
          message: "project_id is required"
          validator: validate-execute-sql

      - scenario: failure_ddl_operation
        description: "Attempting DDL via execute_sql"
        input:
          project_id: "proj_abc123"
          query: "CREATE TABLE test (id int)"
        error:
          code: VALIDATION_ERROR
          message: "DDL operations not allowed in execute_sql - use apply_migration instead"
          validator: validate-execute-sql

    apply_migration:
      - scenario: success
        description: "Apply migration to create table"
        input:
          project_id: "proj_abc123"
          name: "create_posts_table"
          query: |
            CREATE TABLE posts (
              id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
              title text NOT NULL,
              content text,
              user_id uuid REFERENCES auth.users(id),
              created_at timestamptz DEFAULT now()
            );

            ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

            CREATE POLICY "Users can view own posts"
              ON posts FOR SELECT
              USING (auth.uid() = user_id);
        output:
          migration_id: "mig_xyz789"
          status: "applied"

      - scenario: failure_invalid_param
        description: "Invalid migration name format"
        input:
          project_id: "proj_abc123"
          name: "CreatePostsTable"
          query: "CREATE TABLE posts (id int)"
        error:
          code: VALIDATION_ERROR
          message: "migration name must be snake_case (lowercase letters, numbers, underscores only)"
          validator: validate-apply-migration

  mcp_specific:
    server_command: "https://mcp.supabase.com/mcp"
    transport: sse
    auth: oauth
    query_parameters:
      - name: project_ref
        required: false
        description: "Scope server to specific project (recommended)"
      - name: read_only
        required: false
        description: "Restrict to read-only operations (recommended)"
      - name: features
        required: false
        description: "Specify which tool groups to enable"
    health_check:
      method: tool_call
      command: list_projects
      expected_response: "Array of projects or organization prompt"
      timeout_ms: 10000