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);