Schema Sentinel

Read-only MCP server that lets an AI agent look at a Postgres db + its paired git repo and just... know what's going on. Schema, ERD, missing indexes, circular FKs, migration risk, git-churn hotspots, all the stuff you'd normally dig up by hand with psql and git log.

Works against any Postgres + repo pair via .env config (connection string + repo path). Not hardcoded to one project.

Why I built this

Two reasons: it's a portfolio piece, and it was my hands-on way of actually learning MCP, schema introspection, static SQL parsing, git analysis, and wiring all of it up as agent-callable tools.

Related MCP server: tusk-mcp

Tools

What counts as migration risk

check_migration_risk parses the file and flags six patterns:

Setup

1. pip install -e . (or pip install -e ".[dev]" to also get pytest).

2. Copy .env.example to .env and fill in SCHEMA_SENTINEL_DB_URL, SCHEMA_SENTINEL_REPO_PATH, SCHEMA_SENTINEL_MIGRATIONS_PATH. The db role has to be read-only, run scripts/setup_readonly_role.sql against your database first if you don't already have one.

3. Run it: schema-sentinel (installed as a console script), or python -m schema_sentinel.server. Either way it speaks MCP over stdio.

To wire it into an MCP client, point the client at the console script and hand it the three env vars:

{
  "mcpServers": {
    "schema-sentinel": {
      "command": "schema-sentinel",
      "env": {
        "SCHEMA_SENTINEL_DB_URL": "postgresql://schema_sentinel_ro@localhost:5432/your_database",
        "SCHEMA_SENTINEL_REPO_PATH": "/path/to/your/repo",
        "SCHEMA_SENTINEL_MIGRATIONS_PATH": "/path/to/your/repo/migrations"
      }
    }
  }
}

Decisions I've locked in

• Python + psycopg v3 (psycopg[binary]) for Postgres.

• The mcp SDK's bundled FastMCP (mcp.server.fastmcp) for the server, not the standalone fastmcp package. Pinned to mcp<2 deliberately, see the rough edges below.

• Mermaid erDiagram text for the ERD, no Graphviz, no rendering lib. GitHub and Notion already render Mermaid natively, so why bother.

• pglast (wraps libpg_query, Postgres's own C parser) to statically parse migrations. check_migration_risk only ever parses, never runs, a migration. Non-negotiable.

  Worth saying why it's pglast and not a generic multi-dialect parser: I started on one and found it silently gave up on multi-item DDL. ALTER TABLE x DROP COLUMN a, ALTER COLUMN b TYPE int came back as an unparsed blob, which meant a genuinely dangerous migration would sail through reporting zero risks, and DROP TABLE a, b; raised outright. Both are ordinary SQL. pglast doesn't approximate the grammar, it is the grammar, so neither is a problem.

• GitPython for the churn/file-history stuff.

• Introspection goes through pg_catalog, not information_schema. Not a style preference: information_schema.table_constraints and friends gate visibility behind write-ish privileges, so a strictly read-only role sees zero rows there. Which is exactly the role this thing is designed to run as.

• psycopg3 param binding: list filters use = ANY(%s), not IN %s. psycopg3 doesn't auto-expand a Python list into a SQL IN (...) the way psycopg2 did. Bit me once, not doing it again.

• Churn and complexity stay separate. Churn is a pure git signal, complexity is a pure schema signal, neither reaches into the other's half. generate_report hands you both.

Security posture (read-only, belt and suspenders)

Enforced in src/schema_sentinel/db/connection.py:

1. Session-level lock, SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY right after connecting, before anything else runs.

2. Startup privilege check, checks pg_roles for rolsuper / rolcreatedb / rolcreaterole, and information_schema.role_table_grants for any non-SELECT grant on the connecting role. Either one fails, the connection gets closed and it raises WritableConnectionError, no usable connection handed back, period.

3. scripts/setup_readonly_role.sql sets up a correctly-scoped read-only role in one step, instead of doing it by hand.

The migration checker never touches the database at all, it only reads files off disk.

Project layout

src/schema_sentinel/
├── config.py             env config -> Settings
├── schema.py             get_schema_overview, find_missing_indexes,
│                         find_circular_foreign_keys, find_table_complexity
├── erd.py                generate_erd
├── migrations.py         check_migration_risk
├── report.py             generate_report
├── server.py             MCP entrypoint, registers all 8 tools
├── db/connection.py      the read-only gatekeeper
└── git_ops/churn.py      find_schema_churn

tests/                    mirrors src/, plus tests/test_db/ and tests/test_git_ops/
scripts/                  setup_readonly_role.sql, setup_test_db.sql

Running the tests

Most of the suite is DB-free, but the schema/connection/report tests run against a real local Postgres, since the whole point of the connection tests is proving actual grant enforcement and you can't meaningfully mock that.

createdb schema_sentinel_test
psql -d schema_sentinel_test -f scripts/setup_test_db.sql
pytest

setup_test_db.sql builds the fixture tables (simple and composite PKs, simple and composite FKs, one FK deliberately left unindexed) plus the three roles the connection tests need. Point the SCHEMA_SENTINEL_TEST_* URLs in .env at them. CI does exactly this against a throwaway Postgres container on every push.

Known rough edges

• Schema-qualified names get flattened. Churn keys everything by bare table name, so public.orders and analytics.orders would land in the same bucket. Fine for the single-schema case, wrong for anything fancier.

• The risk checker knows six patterns. Plenty of other things worth flagging aren't in there yet: ADD CONSTRAINT without NOT VALID, SET NOT NULL on an existing column, volatile DEFAULTs, VACUUM FULL, CLUSTER.

• generate_report re-queries more than it needs to. Several tools call get_schema_overview or the constraint fetch independently, so a full report hits pg_constraint a handful of times over. Each tool being self-contained was the deliberate tradeoff, but on a big schema it's wasteful.

• Pinned to mcp 1.x. 2.0 removed mcp.server.fastmcp, which is what server.py is written against, so upgrading means porting the tool registration to whatever replaced it. Pinned rather than rushed.

• Complexity is a raw count, not a score. Fan-in, fan-out and column count get sorted, not weighted, and nothing multiplies churn against complexity to give you a single "hotspot" number. You get both halves and draw your own conclusions.

License

AGPL-3.0-or-later, full text in LICENSE.

Short version: read it, run it, fork it, learn from it, all fine. But if you distribute a modified version, or run one as a service other people can reach, you have to publish your source too.

Copyright (C) 2026 Ramón Iglesias

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Affero General Public License as published
by the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU Affero General Public License for more details.

You should have received a copy of the GNU Affero General Public License
along with this program.  If not, see <https://www.gnu.org/licenses/>.