A read-only MCP server over Atrio

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

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

Related MCP server: AgenticMCP

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, real PostgreSQL compiled to WebAssembly, so RLS and roles behave as in production.

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

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

Claude Desktop (claude_desktop_config.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:

Against a real PostgreSQL

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

All names and figures are fictional.