Self-Hosted Supabase MCP Server

Instructions

Implementation Reference

• src/tools/list_storage_objects.ts:57-116 (handler)

  The handler function that performs the core logic: validates input, checks DB availability, builds and executes a dynamic parameterized SQL query on storage.objects table to list objects matching bucket_id, optional prefix (LIKE prefix%), ordered by name, with limit/offset pagination, processes response with handleSqlResponse, and logs results.

  execute: async (
      input: ListStorageObjectsInput,
      context: ToolContext
  ): Promise<ListStorageObjectsOutput> => {
      const client = context.selfhostedClient;
      const { bucket_id, limit, offset, prefix } = input;
  
      console.error(`Listing objects for bucket ${bucket_id} (Prefix: ${prefix || 'N/A'})...`);
  
      if (!client.isPgAvailable()) {
          context.log('Direct database connection (DATABASE_URL) is required to list storage objects.', 'error');
          throw new Error('Direct database connection (DATABASE_URL) is required to list storage objects.');
      }
  
      // Use a transaction to get access to the pg client for parameterized queries
      const objects = await client.executeTransactionWithPg(async (pgClient: PoolClient) => {
          // Build query with parameters
          let sql = `
              SELECT
                  id,
                  name,
                  bucket_id,
                  owner,
                  version,
                  metadata ->> 'mimetype' AS mimetype,
                  metadata ->> 'size' AS size, -- Extract size from metadata
                  metadata,
                  created_at::text,
                  updated_at::text,
                  last_accessed_at::text
              FROM storage.objects
              WHERE bucket_id = $1
          `;
          const params: (string | number)[] = [bucket_id];
          let paramIndex = 2;
  
          if (prefix) {
              sql += ` AND name LIKE $${paramIndex++}`;
              params.push(`${prefix}%`);
          }
  
          sql += ' ORDER BY name ASC NULLS FIRST';
          sql += ` LIMIT $${paramIndex++}`;
          params.push(limit);
          sql += ` OFFSET $${paramIndex++}`;
          params.push(offset);
          sql += ';';
  
          console.error('Executing parameterized SQL to list storage objects within transaction...');
          const result = await pgClient.query(sql, params); // Raw pg result
  
          // Explicitly pass result.rows, which matches the expected structure
          // of SqlSuccessResponse (unknown[]) for handleSqlResponse.
          return handleSqlResponse(result.rows as SqlSuccessResponse, ListStorageObjectsOutputSchema);
      });
  
      console.error(`Found ${objects.length} objects.`);
      context.log(`Found ${objects.length} objects.`);
      return objects;
  },

• src/tools/list_storage_objects.ts:9-35 (schema)

  Zod schemas for input validation (bucket_id required, limit/offset/prefix optional) and output validation (array of StorageObject with fields like id, name, bucket_id, metadata-extracted mimetype/size, timestamps).

  const ListStorageObjectsInputSchema = z.object({
      bucket_id: z.string().describe('The ID of the bucket to list objects from.'),
      limit: z.number().int().positive().optional().default(100).describe('Max number of objects to return'),
      offset: z.number().int().nonnegative().optional().default(0).describe('Number of objects to skip'),
      prefix: z.string().optional().describe('Filter objects by a path prefix (e.g., \'public/\')'),
  });
  type ListStorageObjectsInput = z.infer<typeof ListStorageObjectsInputSchema>;
  
  // Output schema
  const StorageObjectSchema = z.object({
      id: z.string().uuid(),
      name: z.string().nullable(), // Name can be null according to schema
      bucket_id: z.string(),
      owner: z.string().uuid().nullable(),
      version: z.string().nullable(),
      // Get mimetype directly from SQL extraction
      mimetype: z.string().nullable(), 
      // size comes from metadata
      size: z.string().pipe(z.coerce.number().int()).nullable(),
      // Keep raw metadata as well
      metadata: z.record(z.any()).nullable(),
      created_at: z.string().nullable(),
      updated_at: z.string().nullable(),
      last_accessed_at: z.string().nullable(),
  });
  const ListStorageObjectsOutputSchema = z.array(StorageObjectSchema);
  type ListStorageObjectsOutput = z.infer<typeof ListStorageObjectsOutputSchema>;

• src/index.ts:98-121 (registration)

  The tool is imported (line 33) and registered in the availableTools object (line 119), which is used to populate MCP server capabilities and handle tool calls.

  const availableTools = {
      // Cast here assumes tools will implement AppTool structure
      [listTablesTool.name]: listTablesTool as AppTool,
      [listExtensionsTool.name]: listExtensionsTool as AppTool,
      [listMigrationsTool.name]: listMigrationsTool as AppTool,
      [applyMigrationTool.name]: applyMigrationTool as AppTool,
      [executeSqlTool.name]: executeSqlTool as AppTool,
      [getDatabaseConnectionsTool.name]: getDatabaseConnectionsTool as AppTool,
      [getDatabaseStatsTool.name]: getDatabaseStatsTool as AppTool,
      [getProjectUrlTool.name]: getProjectUrlTool as AppTool,
      [getAnonKeyTool.name]: getAnonKeyTool as AppTool,
      [getServiceKeyTool.name]: getServiceKeyTool as AppTool,
      [generateTypesTool.name]: generateTypesTool as AppTool,
      [rebuildHooksTool.name]: rebuildHooksTool as AppTool,
      [verifyJwtSecretTool.name]: verifyJwtSecretTool as AppTool,
      [listAuthUsersTool.name]: listAuthUsersTool as AppTool,
      [getAuthUserTool.name]: getAuthUserTool as AppTool,
      [deleteAuthUserTool.name]: deleteAuthUserTool as AppTool,
      [createAuthUserTool.name]: createAuthUserTool as AppTool,
      [updateAuthUserTool.name]: updateAuthUserTool as AppTool,
      [listStorageBucketsTool.name]: listStorageBucketsTool as AppTool,
      [listStorageObjectsTool.name]: listStorageObjectsTool as AppTool,
      [listRealtimePublicationsTool.name]: listRealtimePublicationsTool as AppTool,
  };

• src/types/index.ts:72-85 (schema)

  TypeScript interface defining the StorageObject type used in the tool's output schema and comments reference the tool.

   */
  export interface StorageObject {
      id: string; // uuid
      name: string | null;
      bucket_id: string;
      owner: string | null; // uuid
      version: string | null;
      mimetype: string | null; // Extracted from metadata
      size: number | null;     // Extracted from metadata, parsed as number
      metadata: Record<string, unknown> | null; // Use unknown instead of any
      created_at: string | null; // Timestamps returned as text from DB
      updated_at: string | null;
      last_accessed_at: string | null;
  } 

• src/tools/list_storage_objects.ts:110-110 (helper)

  Uses the shared handleSqlResponse utility to validate and type the SQL query results against the output schema.

  return handleSqlResponse(result.rows as SqlSuccessResponse, ListStorageObjectsOutputSchema);