SchemaFlow

SchemaFlow implements a credential-isolation architecture where AI-IDEs authenticate via time-limited MCP tokens rather than direct database credentials. The server maintains cached schema metadata separately from the database layer, and token validation occurs at the SSE gateway before any schema data is transmitted. This eliminates the need for AI-IDEs to store or transmit production database passwords, reducing attack surface and audit complexity.

SchemaFlow maintains an in-memory or persistent cache of PostgreSQL/Supabase schema metadata that is populated during initial database connection and updated when users trigger a refresh via the web dashboard. The caching strategy stores table definitions, column metadata, constraints, indexes, and relationships without requiring continuous polling of the live database. Cache invalidation is explicit (user-initiated) rather than time-based, ensuring schema consistency across all connected AI-IDEs while minimizing database load.

SchemaFlow implements a manual schema refresh workflow where users trigger cache updates via the web dashboard after running database migrations. The refresh process re-executes schema introspection queries against the live database, updates the cached metadata, and notifies all connected AI-IDEs of the schema change. The workflow is explicit (user-initiated) rather than automatic, ensuring schema consistency across all IDEs and preventing stale data issues.

SchemaFlow enforces HTTPS-only communication between AI-IDEs and the MCP server, with token-based authentication validated at the SSE gateway before any schema data is transmitted. The implementation uses standard HTTPS with TLS encryption, and tokens are validated on every request using cryptographic verification. No unencrypted HTTP connections are allowed, and tokens are never logged or exposed in error messages.

SchemaFlow exposes three MCP-compliant tools (get_schema, analyze_database, check_schema_alignment) that AI-IDEs invoke through the Model Context Protocol. These tools are registered with the MCP server and callable by AI assistants during conversation, returning structured schema metadata, analysis results, and validation reports. The implementation uses SSE (Server-Sent Events) over HTTPS for bidirectional communication, allowing AI-IDEs to request schema data and receive results without polling.

The get_schema MCP tool retrieves filtered schema metadata from the cache, accepting optional parameters to target specific tables or return full database structure. It returns structured JSON containing table definitions, column metadata (name, type, nullable, default), constraints (primary key, foreign key, unique), and indexes. The tool implements parameter validation and error handling for missing tables, returning clear error messages when requested schema elements don't exist.

The analyze_database MCP tool performs static analysis on cached schema metadata to identify design issues, optimization opportunities, and best practice violations. It examines table structures, constraint definitions, index coverage, and naming conventions, returning a structured report with findings categorized by severity (error, warning, info). The analysis runs entirely on cached data without querying the live database, making it fast and suitable for real-time AI-assisted feedback.

The check_schema_alignment MCP tool validates cached schema against a set of configurable best practices and standards, returning a compliance report. It checks for naming conventions (snake_case vs camelCase), constraint coverage (all tables have primary keys), index presence (foreign keys are indexed), and other structural patterns. The tool returns a structured report indicating which standards are met, which are violated, and severity of violations, enabling AI-assisted schema remediation.

SchemaFlow uses Server-Sent Events (SSE) over HTTPS for real-time, bidirectional communication between the MCP server and AI-IDEs. The SSE connection is established after token authentication and remains open for the duration of the IDE session, allowing the server to push schema data and analysis results to the IDE without polling. The implementation handles connection lifecycle (establishment, heartbeat, reconnection) and message serialization/deserialization for MCP protocol compliance.

SchemaFlow connects to PostgreSQL and Supabase databases using standard database drivers, executing system catalog queries (pg_tables, pg_columns, pg_constraints, pg_indexes) to introspect schema structure. The connection is established once during setup and used to populate the initial schema cache; the connection is not maintained for every IDE request. The introspection process extracts table definitions, column metadata, constraints, indexes, and relationships, storing them in the cache for subsequent access.

SchemaFlow provides a dashboard interface for generating time-limited MCP tokens that authenticate AI-IDE connections. Tokens are generated per database connection and can be revoked or regenerated via the dashboard. The token generation process creates a cryptographically secure token with configurable expiration (default unclear from docs but typical is 24-90 days), and token validation occurs at the SSE gateway before any schema data is transmitted. Token metadata (creation date, last used, expiration) is tracked for audit purposes.

SchemaFlow provides IDE-specific configuration guides for Cursor IDE, Windsurf IDE, and VS Code + Cline, documenting how to register the SchemaFlow MCP server and authenticate with tokens. The configuration process involves adding SchemaFlow to the IDE's MCP server list with the server URL (api.schemaflow.dev/mcp/) and authentication token, then restarting the IDE to activate the connection. Each IDE has slightly different configuration syntax and file locations, but the underlying MCP registration mechanism is identical.