Self-Hosted Supabase MCP Server

Instructions

Implementation Reference

• src/tools/get_database_stats.ts:65-127 (handler)

  The execute handler function that runs SQL queries against pg_stat_database and pg_stat_bgwriter to fetch database activity statistics and background writer stats, processes the results, and returns them structured according to the output schema.
  execute: async (input: GetDbStatsInput, context: ToolContext) => { const client = context.selfhostedClient; // Combine queries for efficiency if possible, but RPC might handle separate calls better. // Using two separate calls for clarity. const getDbStatsSql = ` SELECT datname, numbackends, xact_commit::text, xact_rollback::text, blks_read::text, blks_hit::text, tup_returned::text, tup_fetched::text, tup_inserted::text, tup_updated::text, tup_deleted::text, conflicts::text, temp_files::text, temp_bytes::text, deadlocks::text, checksum_failures::text, checksum_last_failure::text, blk_read_time, blk_write_time, stats_reset::text FROM pg_stat_database `; const getBgWriterStatsSql = ` SELECT checkpoints_timed::text, checkpoints_req::text, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint::text, buffers_clean::text, maxwritten_clean::text, buffers_backend::text, buffers_backend_fsync::text, buffers_alloc::text, stats_reset::text FROM pg_stat_bgwriter `; // Execute both queries const [dbStatsResult, bgWriterStatsResult] = await Promise.all([ executeSqlWithFallback(client, getDbStatsSql, true), executeSqlWithFallback(client, getBgWriterStatsSql, true), ]); // Use handleSqlResponse for each part; it throws on error. const dbStats = handleSqlResponse(dbStatsResult, GetDbStatsOutputSchema.shape.database_stats); const bgWriterStats = handleSqlResponse(bgWriterStatsResult, GetDbStatsOutputSchema.shape.bgwriter_stats); // Combine results into the final schema return { database_stats: dbStats, bgwriter_stats: bgWriterStats, }; },
• src/tools/get_database_stats.ts:7-45 (schema)

  Zod output schema defining the structure for database_stats (array from pg_stat_database) and bgwriter_stats (array from pg_stat_bgwriter), handling string representations of bigints and timestamps.
  // Note: Types are often bigint from pg_stat, returned as string by JSON/RPC. // Casting to numeric/float in SQL or parsing carefully later might be needed for calculations. const GetDbStatsOutputSchema = z.object({ database_stats: z.array(z.object({ datname: z.string().nullable(), numbackends: z.number().nullable(), xact_commit: z.string().nullable(), // bigint as string xact_rollback: z.string().nullable(), // bigint as string blks_read: z.string().nullable(), // bigint as string blks_hit: z.string().nullable(), // bigint as string tup_returned: z.string().nullable(), // bigint as string tup_fetched: z.string().nullable(), // bigint as string tup_inserted: z.string().nullable(), // bigint as string tup_updated: z.string().nullable(), // bigint as string tup_deleted: z.string().nullable(), // bigint as string conflicts: z.string().nullable(), // bigint as string temp_files: z.string().nullable(), // bigint as string temp_bytes: z.string().nullable(), // bigint as string deadlocks: z.string().nullable(), // bigint as string checksum_failures: z.string().nullable(), // bigint as string checksum_last_failure: z.string().nullable(), // timestamp as string blk_read_time: z.number().nullable(), // double precision blk_write_time: z.number().nullable(), // double precision stats_reset: z.string().nullable(), // timestamp as string })).describe("Statistics per database from pg_stat_database"), bgwriter_stats: z.array(z.object({ // Usually a single row checkpoints_timed: z.string().nullable(), checkpoints_req: z.string().nullable(), checkpoint_write_time: z.number().nullable(), checkpoint_sync_time: z.number().nullable(), buffers_checkpoint: z.string().nullable(), buffers_clean: z.string().nullable(), maxwritten_clean: z.string().nullable(), buffers_backend: z.string().nullable(), buffers_backend_fsync: z.string().nullable(), buffers_alloc: z.string().nullable(), stats_reset: z.string().nullable(), })).describe("Statistics from the background writer process from pg_stat_bgwriter"), });
• src/tools/get_database_stats.ts:48-49 (schema)

  Zod input schema for get_database_stats tool, which takes no parameters.
  const GetDbStatsInputSchema = z.object({}); type GetDbStatsInput = z.infer<typeof GetDbStatsInputSchema>;
• src/index.ts:106-106 (registration)

  Registration of the getDatabaseStatsTool in the availableTools object, which is used to populate the MCP server's tool capabilities.
  [getDatabaseStatsTool.name]: getDatabaseStatsTool as AppTool,
• src/index.ts:17-17 (registration)

  Import statement for getDatabaseStatsTool from its implementation file.
  import { getDatabaseStatsTool } from './tools/get_database_stats.js';