<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:

```json
{
  "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 `DEFAULT`s, `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`](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/>.