legi-mcp

A read-only Model Context Protocol 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.

Related MCP server: sql-explorer-mcp

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:

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:

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

{
  "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.