# legi-mcp

> A read-only [Model Context Protocol](https://modelcontextprotocol.io) server (server name: `la-legislative`) that exposes Los Angeles City legislative records — Council Files, votes, council-member activity, and Neighborhood Council engagement — to an MCP client (Claude Code / Claude Desktop) as a small set of fixed, typed, parameterized query tools.

You point Claude at your local database of LA City legislative data, and it can answer questions like "how did each council member vote on Council File 24-0764?" or "what housing files touched Council District 5 last year?" without ever writing SQL itself.

## Why this design (Tier 1, not Tier 0)

This server does **not** let the model write raw SQL. That is a deliberate choice.

The underlying schema needs 5+ table joins to answer even basic questions (a single Council File's "dossier" pulls from `projects`, `file_activities`, `file_activities_url`, `online_documents`, `project_movers`, `votes`, `council_members`, and `project_address`). Letting an LLM improvise joins across that surface is exactly where you get silent wrong-join errors — answers that look right and are quietly wrong.

So this is a **Tier 1** server: every capability is a hand-written, parameterized query behind a typed tool. The model chooses *which* tool and supplies *typed parameters*; it never composes the query. There is no raw-SQL tool.

Scope is intentionally narrow: **personal, single-user, local only.** No public access, no auth, no write operations, no hosting. It talks to a local PostgreSQL database (`citywise_full`) over **stdio** — there is no remote URL or HTTP endpoint. The MCP client launches it as a subprocess.

## Project structure

```
legi-mcp/
├── .env                 # SQL_CONNECTION (gitignored — never commit)
├── .gitignore
├── requirements.txt
├── server.py            # FastMCP instance + tool registration
├── db.py                # connection pool + run_query helper
├── test_tools.py        # standalone verification harness (not an MCP tool)
└── tools/
    ├── get_project.py
    ├── search_projects.py
    ├── get_votes.py
    ├── get_member_activity.py
    └── get_nc_engagement.py
```

## The tools

The server exposes five tools. All are read-only and take typed parameters.

### `get_project(project_id)`
The full "dossier" for one Council File. Bundles the base record (including the aggregate vote tally `vote_given`), the activity timeline, attached documents, online documents, movers/seconders, individual per-member votes, and location/topic metadata (address, lat/long, topics, places).

- `project_id` — the Council File number, e.g. `"24-0764"`.

Returns a dict keyed `project`, `timeline`, `documents`, `online_documents`, `movers`, `votes`, `location`. Returns `{"error": ...}` if the file is not found.

### `search_projects(keyword, council_district, status, date_from, date_to, topic, address, limit=25)`
Advanced search for when you don't have a Council File number. All filters are optional and combined with AND. Returns lightweight rows.

- `keyword` — matched (case-insensitive) against project name OR about text.
- `council_district` — exact council district integer.
- `status` — exact status value, e.g. `"passed"`, `"planned"`.
- `date_from` / `date_to` — inclusive ISO-date range on `start_date`.
- `topic` — matched against topic metadata.
- `address` — matched against address metadata.
- `limit` — default 25, **hard-capped server-side at 50**.

Returns a list of `{id, name, status, council_district, start_date, about}`.

### `get_votes(project_id)`
Just the per-member roll-call vote for one file.

- `project_id` — the Council File number, e.g. `"24-0764"`.

Returns a list of `{member_name, district, vote}`.

### `get_member_activity(council_member, date_from, date_to)`
What a council member moved/seconded and how they voted. The free-text name is resolved via case-insensitive match against name / first name / last name. If it matches **0** members it returns an error; if it matches **2 or more** it returns a disambiguation list of candidates instead of guessing.

- `council_member` — full or partial member name.
- `date_from` / `date_to` — optional inclusive ISO-date range on project `start_date`.

Returns `{member, moved_or_seconded, votes}` on a unique match, or a disambiguation/error dict otherwise.

### `get_nc_engagement(council_district, nc_name)`
Neighborhood Council engagement (Community Impact Statement counts, date ranges) by district or by NC name. **At least one parameter is required.**

- `council_district` — matches NCs whose territory overlaps the district; results carry `overlap_fraction` and `is_primary`.
- `nc_name` — matched against NC name / normalized name; `overlap_fraction` and `is_primary` are null in this mode.

Returns a list of `{nc_name, council_member_name, cis_count, first_date, last_date, overlap_fraction, is_primary}`. Capped at 200 rows.

## Architecture

Built on the official MCP SDK's `mcp.server.fastmcp.FastMCP`. Each tool lives in its own module under `tools/`, and `server.py` imports and registers them on the `FastMCP("la-legislative")` instance. All database access is centralized in `db.py`.

`db.py` maintains one module-level `psycopg2.pool.SimpleConnectionPool` (min 1 / max 5) and exposes a single `run_query(sql, params)` helper that runs a parameterized query with a `RealDictCursor` and returns rows as dicts.

### Security posture

This is a first-class part of the design, not an afterthought:

- **Read-only DB role.** The server connects only as a read-only Postgres role (`mcp_readonly`). Write attempts are denied at the database level, not just by convention in the app code.
- **No string-built SQL.** Every query uses `%s` placeholders with a params tuple. There is zero f-string / string-formatted SQL anywhere in the codebase. Where filters are dynamic (e.g. `search_projects`), only the *values* are parameters — column names and clause fragments are hardcoded literals.
- **Server-enforced row caps.** Every list-returning tool has a hard cap (`search_projects` ≤ 50, `get_nc_engagement` ≤ 200) applied server-side regardless of what the caller requests.
- **No leaking internals.** Errors surfaced to the model are always generic (`"Database query failed."`). The real exception is logged to stderr only, so the raw error and the connection string — which contains the password — can never reach the caller.

## How to replicate

**Prerequisites:** Python 3.10+ (built and tested on 3.13), PostgreSQL, and access to a populated `citywise_full` database. The database itself (the legislative data) is a **prerequisite this repo does not create** — this project is a query layer over an existing DB, not an ingestion pipeline.

### 1. Create the read-only DB role

Run once as a DB admin (in `psql` or pgAdmin). Choose a real password in place of the placeholder:

```sql
CREATE ROLE mcp_readonly WITH LOGIN PASSWORD '<choose-a-password>';
GRANT CONNECT ON DATABASE citywise_full TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_readonly;
```

Then verify it is genuinely read-only — an `INSERT` or `UPDATE` as `mcp_readonly` must fail with a permission error:

```sql
-- Connect as mcp_readonly and try:
INSERT INTO projects (id) VALUES ('test');
-- Expected: ERROR: permission denied for table projects
```

### 2. Create `.env`

In the project root, create a `.env` file (already gitignored — never commit it) with one line:

```
SQL_CONNECTION="postgresql://mcp_readonly:<choose-a-password>@localhost:15432/citywise_full"
```

Adjust host / port / database to match your setup. The reference deployment uses port `15432`.

### 3. Set up the virtualenv and install dependencies

```
python3 -m venv .venv
./.venv/bin/pip install -r requirements.txt
```

Dependencies (see `requirements.txt` for exact pins): the official `mcp[cli]` (>=1.2,<2), `psycopg2-binary`, and `python-dotenv`.

### 4. Verify against the live DB

Before wiring into any client, run the included harness. It exercises each tool function directly against your database and prints sample output (including proof that the `search_projects` limit cap holds and that member disambiguation triggers):

```
./.venv/bin/python test_tools.py
```

### 5. Register with the MCP client

**Claude Code (CLI):**

```
claude mcp add --transport stdio la-legislative -- /full/path/to/.venv/bin/python /full/path/to/server.py
claude mcp list   # should show la-legislative ✔ Connected
```

**Claude Desktop:** add an entry under `mcpServers` in `claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "la-legislative": {
      "command": "/full/path/to/legi-mcp/.venv/bin/python",
      "args": ["/full/path/to/legi-mcp/server.py"]
    }
  }
}
```

> **Gotcha:** `command` must point at the Python **binary inside the venv** (`.venv/bin/python`), **not** at the `.venv` directory. Pointing at the directory causes a "Permission denied" spawn failure.

## Gotchas / notes

- **`.env` is loaded by absolute path, not cwd.** `db.py` resolves `.env` relative to the source file (`os.path.dirname(os.path.abspath(__file__))`), because the MCP client launches the server from its *own* working directory. A plain cwd-relative `load_dotenv()` would silently fail to find `.env`. This is already handled — don't "simplify" it back to a bare `load_dotenv()`.
- **Always use the venv's Python** in the launch command, so `mcp` and `psycopg2` resolve. A system `python3` will not have the dependencies.
- **Restart / reconnect the MCP client** after any config change before expecting it to take effect.

## Out of scope

By explicit design, this server does not include:

- Any write / insert / update tools.
- Hosting, auth, or multi-user access.
- A raw-SQL tool.
- Tools against `project_graphs` / `graph_types` or `file_activities_url.activity_file_summary` — those are currently unpopulated in the source data, so nothing is built on them yet.