Schema Sentinel

<p align="center"> <img src="assets/banner.png" width="720" alt="Schema Sentinel — read-only MCP server for Postgres schemas, migrations, and the git history behind them"> </p>

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.

Tools

Tool	Args	What it does
get_schema_overview	—	Tables, columns, PKs, FKs for the connected db
find_missing_indexes	—	Flags FK columns with no covering index
find_circular_foreign_keys	—	Catches FK cycles across tables, and shows one concrete cycle per group
find_table_complexity	—	Per table: column count, FK fan-in/fan-out, whether it's tangled in a cycle
generate_erd	—	Spits out a Mermaid erDiagram of the schema
check_migration_risk	sql_path	Statically parses one migration file and flags risky stuff. Never runs it
find_schema_churn	since_days?	How often, and how recently, each table's migrations changed
generate_report	since_days?	Rolls all of the above into one health report

What counts as migration risk

check_migration_risk parses the file and flags six patterns:

Pattern	Severity	Why
DROP COLUMN	high	irreversible data loss
ALTER COLUMN ... TYPE	high	rewrites the table, holds a long lock, can silently truncate
RENAME COLUMN	high	breaks in-flight app code still using the old name mid-deploy
RENAME TO (table)	high	same, but takes out every FK pointing at the table too
ADD COLUMN ... NOT NULL with no DEFAULT	medium	fails outright once the table has rows
CREATE INDEX without CONCURRENTLY	medium	blocks writes for however long the build takes

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/>.