Self-Hosted Supabase MCP Server

Instructions

Implementation Reference

• src/tools/get_auth_user.ts:48-101 (handler)

  The execute function that queries the auth.users table for the specified user_id using a direct PostgreSQL connection, validates the output with Zod, and returns the AuthUser object.

  execute: async (input: GetAuthUserInput, context: ToolContext): Promise<GetAuthUserOutput> => { // Use GetAuthUserOutput
      const client = context.selfhostedClient;
      const { user_id } = input;
  
      if (!client.isPgAvailable()) {
          context.log('Direct database connection (DATABASE_URL) is required to get auth user details.', 'error');
          throw new Error('Direct database connection (DATABASE_URL) is required to get auth user details.');
      }
  
      const sql = `
          SELECT
              id,
              email,
              role,
              raw_app_meta_data,
              raw_user_meta_data,
              created_at::text,
              last_sign_in_at::text
          FROM auth.users
          WHERE id = $1
      `;
      const params = [user_id];
  
      console.error(`Attempting to get auth user ${user_id} using direct DB connection...`);
  
      // Use transaction for parameterized query
      const user = await client.executeTransactionWithPg(async (pgClient: PoolClient) => {
          const result = await pgClient.query(sql, params);
  
          if (result.rows.length === 0) {
              throw new Error(`User with ID ${user_id} not found.`);
          }
  
          // handleSqlResponse expects SqlExecutionResult (SuccessResponse | ErrorResponse)
          // We pass the single row which structurally matches SqlSuccessResponse[0]
          // but handleSqlResponse expects the array wrapper or error.
          // So, we validate the single object directly.
          try {
              const singleUser = AuthUserZodSchema.parse(result.rows[0]);
              return singleUser;
          } catch (validationError) {
               if (validationError instanceof z.ZodError) {
                  console.error("Zod validation failed:", validationError.errors);
                  throw new Error(`Output validation failed: ${validationError.errors.map(e => `${e.path.join('.')}: ${e.message}`).join(', ')}`);
              } 
              throw validationError; // Rethrow other errors
          }
      });
  
      console.error(`Found user ${user_id}.`);
      context.log(`Found user ${user_id}.`);
      // The return type is already AuthUser (via GetAuthUserOutput)
      return user;
  },

• src/tools/get_auth_user.ts:8-25 (schema)

  Defines the Zod schemas for input (user_id UUID) and output (AuthUser fields) validation.

  const GetAuthUserInputSchema = z.object({
      user_id: z.string().uuid().describe('The UUID of the user to retrieve.'),
  });
  type GetAuthUserInput = z.infer<typeof GetAuthUserInputSchema>;
  
  // Output schema - Zod for validation (single user)
  const AuthUserZodSchema = z.object({
      id: z.string().uuid(),
      email: z.string().email().nullable(),
      role: z.string().nullable(),
      created_at: z.string().nullable(),
      last_sign_in_at: z.string().nullable(),
      raw_app_meta_data: z.record(z.unknown()).nullable(),
      raw_user_meta_data: z.record(z.unknown()).nullable(),
      // Add more fields as needed
  });
  // Use AuthUser for the output type hint
  type GetAuthUserOutput = AuthUser;

• src/index.ts:114-114 (registration)

  Registers the getAuthUserTool in the availableTools dictionary used by the MCP server.

  [getAuthUserTool.name]: getAuthUserTool as AppTool,

• src/index.ts:25-25 (registration)

  Imports the getAuthUserTool for use in the MCP server.

  import { getAuthUserTool } from './tools/get_auth_user.js';

• src/tools/get_auth_user.ts:44-47 (schema)

  Tool definition including inputSchema, mcpInputSchema, and outputSchema references.

  inputSchema: GetAuthUserInputSchema,
  mcpInputSchema: mcpInputSchema,
  outputSchema: AuthUserZodSchema, // Use the single user Zod schema