Self-Hosted Supabase MCP Server

Instructions

Implementation Reference

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

  The execute handler function that performs the core logic: builds and executes a parameterized SQL query on storage.objects table, applies filters, and returns validated list of objects.

  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:8-47 (schema)

  Zod schemas defining input parameters (bucket_id required, optional limit/offset/prefix) and output structure (array of StorageObject with fields like id, name, metadata). Also includes static MCP JSON input schema.

  // Input schema
  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>;
  
  // Static JSON schema for MCP
  export const mcpInputSchema = {
      type: 'object',
      properties: {
          bucket_id: { type: 'string', description: 'The ID of the bucket to list objects from.' },
          limit: { type: 'number', description: 'Max number of objects to return', default: 100 },
          offset: { type: 'number', description: 'Number of objects to skip', default: 0 },
          prefix: { type: 'string', description: "Filter objects by a path prefix (e.g., 'public/')" },
      },
      required: ['bucket_id'],
  };

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

  Registration of the list_storage_objects tool into the availableTools object used by the MCP server.

  [listStorageObjectsTool.name]: listStorageObjectsTool as AppTool,

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

  Import of the listStorageObjectsTool module.

  import listStorageObjectsTool from './tools/list_storage_objects.js';