SchemaFlow MCP Server: Live PostgreSQL & Supabase Schema

Overview

SchemaFlow MCP Server exposes live PostgreSQL and Supabase schemas over a small Model Context Protocol (MCP) server so AI-powered developer tools (AI-IDEs, assistants, or code generators) can fetch up-to-date schema context and get real-time schema change notifications. It provides secure Server-Sent Events (SSE) for streaming schema updates and three RPC-like tools — get_schema, analyze_database, and check_schema_alignment — that return structured schema information and analysis suitable for programmatic consumption by models.

This server is useful when you need your AI tooling to reason about the current database layout (tables, columns, indexes, relationships) or validate proposed migrations against the actual running schema. Rather than shipping static SQL DDL or trusting stale docs, SchemaFlow MCP Server gives live, queryable schema data and alignment checks to keep model context accurate.

Features

• Live discovery of PostgreSQL and Supabase schemas
• Secure SSE endpoint to stream schema change events
• MCP-style tool endpoints: get_schema, analyze_database, check_schema_alignment
• Returns structured JSON that AI models can ingest directly
• Token-based authentication and CORS configuration
• Docker and local development setup

Installation / Configuration

Prerequisites: Node.js (16+), PostgreSQL or a Supabase project.

Clone and install:

git clone https://github.com/CryptoRadi/schemaflow-mcp-server.git
cd schemaflow-mcp-server
npm install

Environment variables (example .env):

PORT=4000
DATABASE_URL=postgres://user:pass@host:5432/dbname
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_KEY=service_role_or_anon_key
MCP_API_KEY=replace_with_secure_token
CORS_ORIGINS=http://localhost:3000

Run locally:

npm run build
npm start
# or in development
npm run dev

Docker (example):

docker build -t schemaflow-mcp .
docker run -e DATABASE_URL=$DATABASE_URL -e MCP_API_KEY=$MCP_API_KEY -p 4000:4000 schemaflow-mcp

Security notes:

• Protect MCP_API_KEY and Supabase service keys — they grant schema access.
• Use HTTPS and restrict CORS origins for web clients.
• Consider running behind an API gateway for additional rate-limiting and auth.

Available Tools / Resources

The server exposes several resources useful to AI integrations. Endpoints and verbs:

Authentication:

• Send Authorization: Bearer <MCP_API_KEY> header with requests and SSE connections.

Example: fetch current schema with curl

curl -X POST https://your-server.example.com/tool/get_schema \
  -H "Authorization: Bearer $MCP_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"source":"postgres","connection_string":"'"$DATABASE_URL"'"}'

SSE example (JavaScript):

const es = new EventSource("https://your-server.example.com/sse/schema-updates", {
  headers: { Authorization: `Bearer ${MCP_API_KEY}` } // some clients require proxy for custom headers
});
es.onmessage = (ev) => console.log("schema update", JSON.parse(ev.data));

Note: If your SSE client cannot set headers, use a reverse proxy that injects Authorization from a cookie or query token.

Use Cases

• AI-assisted query generation: An AI-IDE calls get_schema before generating SQL so produced queries reference correct table and column names.
• Migration validation: Run check_schema_alignment with a proposed DDL or schema JSON to get a diff and detect breaking changes before applying migrations.
• Performance recommendations: analyze_database identifies missing indexes or suspicious patterns that an assistant can surface during code review or PR creation.
• Live coding and refactor tools: Subscribe to SSE schema-updates so an in-editor assistant is aware when other engineers deploy schema changes and can re-check open queries or code snippets.
• Documentation & onboarding: Automatically generate up-to-date schema docs for new team members by piping get_schema output into doc generators.

Quick Tips

• For Supabase projects, use the service role key if you need to inspect policies and other metadata; otherwise the anon key is sufficient for basic schema details.
• Structure AI prompts to reference the JSON shape returned by get_schema; this avoids ambiguity and keeps model responses grounded in the real schema.
• Limit SSE listeners per client session and use exponential backoff on reconnects to avoid overloading the server.

This server is intended to be integrated into AI tooling workflows that require accurate, live database context. The endpoints return machine-friendly JSON designed to be consumed directly by model agents or IDE plugins.