# A read-only MCP server over Atrio

[![test](https://github.com/alexisvieto/atrio-mcp-sample/actions/workflows/test.yml/badge.svg)](https://github.com/alexisvieto/atrio-mcp-sample/actions/workflows/test.yml)

A sample from **Atrio**, a multi-tenant SaaS I built for managing residential buildings in Panama (accounting, payroll, resident portal). It shows how I connect an AI agent to a business system: through the [Model Context Protocol](https://modelcontextprotocol.io), with the database, not the prompt, deciding what the agent can see.

Any MCP client (Claude Desktop, Claude Code, Cursor and others) can plug into it and answer questions like *"Who's behind on fees this month?"* or *"What's booked in the party room next week?"* for the person who's signed in, and only for them.

## The three tools

| Tool | Answers | Who can use it |
|---|---|---|
| `unit_balance` | Balance of one unit: charged, paid, overdue, latest charges and payments | Staff: any unit in their building. A resident: their own unit |
| `arrears` | Units with an overdue balance at the end of a month, largest first, with the total | Staff only |
| `upcoming_bookings` | Bookings of common areas for the next N days | Everyone in the building. Residents don't see which unit booked |

All three are marked `readOnlyHint`, so clients can call them without asking for confirmation.

## How the agent is kept in its lane

The model can be talked into anything, so the rules don't live in the model or in the tool code. Each tool call runs inside the database like this:

1. **Read-only transaction.** `begin read only`. An `update` fails even if a bug or an injected prompt produces one.
2. **A role with no write grants.** `set local role atrio_agent`. The role can only `select`, and it is subject to Row Level Security.
3. **The caller's identity.** `set_config('app.user_id', …, true)`, scoped to the transaction. In production this is the verified JWT (`auth.uid()` in Supabase).
4. **Row Level Security on every table.** The policies use that identity. Staff see their whole building, a resident sees their own unit, and nobody sees another building. With no identity, every table looks empty.

When a resident asks for a neighbour's unit, the answer is the same as for a unit that doesn't exist: `No unit "A-102" is visible to you.` That way the answer doesn't reveal whether the unit exists.

The tool layer adds one check of its own (`arrears` is staff-only) and input validation with Zod. Even if a tool's SQL were wrong, it still couldn't return rows the policies hide.

## Run the tests

You need Node.js 22.18+ and nothing else. The tests use [PGlite](https://pglite.dev), real PostgreSQL compiled to WebAssembly, so RLS and roles behave as in production.

```bash
npm install
npm test
# ℹ tests 15
# ℹ pass 15
```

The tests talk to the server over the MCP protocol as three different users (a Torre Coral admin, a Torre Coral resident and an admin of another building). They check the numbers, the isolation between users and buildings, and that the agent role can't write even with hand-written SQL.

## Try it in Claude

With no `DATABASE_URL` the server starts with the fictional demo data in memory.

**Claude Code**

```bash
claude mcp add atrio -- node /path/to/atrio-mcp-sample/src/main.ts
```

**Claude Desktop** (`claude_desktop_config.json`)

```json
{
  "mcpServers": {
    "atrio": {
      "command": "node",
      "args": ["/path/to/atrio-mcp-sample/src/main.ts"],
      "env": { "ATRIO_USER_ID": "aaaaaaaa-0000-0000-0000-000000000001" }
    }
  }
}
```

Switch `ATRIO_USER_ID` to see the same questions from different people:

| User | Who |
|---|---|
| `aaaaaaaa-0000-0000-0000-000000000001` | Admin, Torre Coral |
| `bbbbbbbb-0000-0000-0000-000000000001` | Resident of A-101, Torre Coral |
| `cccccccc-0000-0000-0000-000000000001` | Admin, PH Mirador del Mar |

## Against a real PostgreSQL

```bash
psql "$DATABASE_URL" -f sql/schema.sql -f sql/seed.sql
psql "$DATABASE_URL" -c "create role atrio_mcp login password '…'; grant atrio_agent to atrio_mcp;"
DATABASE_URL=postgres://atrio_mcp:…@host/db ATRIO_USER_ID=… npm start
```

The server's login role only needs to be able to `set role atrio_agent`. It never reads a table with its own privileges.

## What changes in production

- **Transport.** It runs over Streamable HTTP instead of stdio, and the user id comes from the client's OAuth token rather than an environment variable.
- **Schema.** It's the full Atrio schema. The in-app assistant uses twelve read-only tools under the same model: finances, maintenance, packages, visitor passes, votes and more.
- **Writes.** Anything that writes, such as a payment reminder or a booking, would be a separate tool that drafts the action for a person to approve.

## Files

| File | Content |
|---|---|
| `sql/schema.sql` | Tables, the `atrio_agent` role and the RLS policies |
| `sql/seed.sql` | Two fictional buildings, three users, dates relative to today |
| `src/database.ts` | Runs each call as the user: read-only, `atrio_agent`, RLS on |
| `src/server.ts` | The three MCP tools |
| `src/main.ts` | stdio entry point |
| `test/server.test.ts` | 15 tests over the MCP protocol |

All names and figures are fictional.