finance-app/docs/agent-access.md

Agent Access — querying the finance data from an LLM agent

How an AI agent (Hermes, Claude Code, Codex, Claude Desktop, …) reads the finance database. Read-only, audited, row-capped. Agents never write to personal.

The read path is an MCP server — postgres-personal — that lives in the personal-agent-gateway repo at mcp-servers/postgres-personal/. This app owns the data; that repo owns the server. Keep both in mind when changing the schema (see Schema changes).

---

Architecture

agent client (Hermes / Claude Code / Codex)
   │  stdio (JSON-RPC, MCP)
   ▼
server.py ── guardrails.validate_select (SQLGlot: single SELECT, LIMIT ≤ 100)
   ├── PG_DSN_RO  → role agent_ro   (SELECT only, statement_timeout 5s)  → queries
   └── PG_DSN_LOG → role agent_log  (INSERT into mcp_query_log only)     → audit

Both roles, the audit table, and the five analysis views are created by smarthome/personal-agent/migrations/006_agent_read_role_views.sql — not by this repo's Prisma migrations. Passwords are supplied at apply time from Infisical (homelab-w-ae9 / prod / /agent-gateway), never stored in the SQL file.

Defence in depth, in order:

1. agent_ro has no write grants at all — the DB refuses writes regardless of the SQL.
2. validate_select parses every SQL string (typed tools and adhoc_query alike) and rejects anything that isn't one plain SELECT, plus pg_sleep/pg_read_file/ dblink/lo_import/lo_export. It rewrites LIMIT to ≤ 100.
3. Third-party text columns (description, location, order_reference, line_items) come back wrapped in <<untrusted-data>>…<</untrusted-data>> so the runtime's system prompt can treat statement/receipt text as data, never instructions.
4. Every call — accepted or rejected — is inserted into mcp_query_log (tool, args, sql, row_count, duration_ms, error).

Audit review:

docker exec postgres-personal psql -U personal -d personal -c \
  "SELECT at, tool, row_count, duration_ms, error FROM mcp_query_log ORDER BY at DESC LIMIT 20"

---

Tools

Tool	What it answers
query_statements	Statements by bank / account number / billing_end_date range
query_transactions	Transaction search: merchant, category, date range, amount range, description substring
get_spending_summary	Outflow for one month (YYYY-MM) or date range, grouped by category or merchant
get_spending_comparison	Two months side by side, sorted by absolute delta — what drove the change
get_upcoming_payments	Statements with payment_due_date in the next N days (1–90, default 14)
get_recurring_spend	Recurring-payment candidates (heuristic — expect false positives)
adhoc_query	One read-only SELECT when the typed tools can't express the question

Views available to adhoc_query (all SELECT-granted to agent_ro):

Relation	Grain
transaction_search	Flat per-transaction surface: transaction ⟕ statement identity ⟕ expense_metadata. The right default for search.
merchant_monthly_spend	month × merchant → txn_count, total_spend, avg_amount
category_monthly_spend	month × category → txn_count, total_spend
cashflow_monthly	month → total_outflow, total_inflow, net, txn_count
recurring_candidates	merchant × amount bucket → occurrences, cadence, last_seen, next_expected

Base tables (transactions, statements, transaction_splits, …) are also readable — see README → Data Model.

Semantics baked into the views:

• outflows = transaction_type IN ('debit','fee','interest'); inflows = ('payment','credit','refund')
• amount prefers amount_aud (FX-normalised) over amount
• merchant prefers merchant_normalized → merchant_name → first 60 chars of description

Two caveats worth knowing

• No owner scoping. Unlike the app's API routes (COALESCE(t.owner_id, s.owner_id) = $1), the agent views expose all owners' rows. Fine while the only consumer is the household's own assistant; it must change before any agent is exposed to a second person.
• No transaction_overrides merge. The views read t.category / t.merchant_normalized directly, so manual corrections made in the UI are not reflected. The app's own queries use COALESCE(o.category_override, t.category). Numbers from an agent can therefore differ slightly from the same figure in the UI.

---

Connecting a client

The server speaks stdio MCP. There is no network listener, so a client connects by executing it. The easiest correct way is to exec it inside the hermes container: the repo is bind-mounted there (/mnt/user/projects/personal-agent-gateway → /opt/data/repo), the venv and deps already exist, PG_DSN_RO/PG_DSN_LOG are already in the container env, and postgres-personal resolves on networks_internal.

The canonical command, used by every recipe below:

docker exec -i hermes /opt/data/venvs/pgp/bin/python \
  /opt/data/repo/mcp-servers/postgres-personal/server.py

Smoke-test it before wiring a client in:

printf '%s\n' \
 '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"t","version":"0"}}}' \
 '{"jsonrpc":"2.0","method":"notifications/initialized"}' \
 '{"jsonrpc":"2.0","id":2,"method":"tools/list"}' \
 | docker exec -i hermes /opt/data/venvs/pgp/bin/python \
     /opt/data/repo/mcp-servers/postgres-personal/server.py 2>/dev/null | head -2

Claude Code (on the Unraid host)

claude mcp add postgres-personal -s user -- \
  docker exec -i hermes /opt/data/venvs/pgp/bin/python \
  /opt/data/repo/mcp-servers/postgres-personal/server.py

Then /mcp in a session to confirm the seven tools are listed.

Claude Code / Claude Desktop (from a laptop, over SSH)

Same command, tunnelled — stdio doesn't care what carries it:

claude mcp add postgres-personal -s user -- \
  ssh unraid docker exec -i hermes /opt/data/venvs/pgp/bin/python \
  /opt/data/repo/mcp-servers/postgres-personal/server.py

Needs key-based SSH to the host (Tailscale or LAN) and a non-interactive shell. For Claude Desktop, the same command / args go in claude_desktop_config.json under mcpServers.

Codex CLI

~/.codex/config.toml:

[mcp_servers.postgres-personal]
command = "docker"
args = [
  "exec", "-i", "hermes",
  "/opt/data/venvs/pgp/bin/python",
  "/opt/data/repo/mcp-servers/postgres-personal/server.py",
]

(Prefix args with ["unraid", "docker", …] and set command = "ssh" for the remote case.)

Hermes

Already wired — mcp_servers.postgres-personal in /mnt/user/appdata/docker/hermes/config.yaml (template: personal-agent-gateway/spike/hermes/config.yaml.example). It runs the same venv and script directly rather than via docker exec, since it is the container.

Running the server outside hermes

Only needed if hermes is down or you want isolation. Two constraints: Python ≥ 3.11 with mcp, psycopg[binary], sqlglot; and network reach to postgres-personal, which is not published to the host — it only resolves on the networks_internal Docker network. So run it in a container on that network, passing the DSNs:

docker run -i --rm --network networks_internal \
  --env-file /mnt/user/appdata/docker/hermes/.env \
  -v /mnt/user/projects/personal-agent-gateway:/app \
  personal-agent-gateway python /app/mcp-servers/postgres-personal/server.py

A bare host venv would need the container's current bridge IP in the DSN instead of the hostname, which breaks on every recreate — don't.

Secrets

PG_DSN_RO and PG_DSN_LOG live in /mnt/user/appdata/docker/hermes/.env (canonical copies in Infisical /agent-gateway). Never paste them into a client config, a claude mcp add command line, or this repo — the docker exec recipes above inherit them from the container, which is precisely why they're preferred.

---

Direct SQL (humans and one-off scripts)

For ad-hoc analysis where an agent isn't in the loop, skip MCP:

docker exec postgres-personal psql -U personal -d personal -c \
  "SELECT * FROM cashflow_monthly ORDER BY month DESC LIMIT 12"

That's the full-privilege personal role — no guardrail, no row cap, no audit row. Use agent_ro if you want the same safety envelope an agent gets.

---

Making this available more widely

Today: any MCP client that can run a subprocess can use it (all of the above), and any machine that can SSH to the host can too. That covers Claude Code, Claude Desktop, Codex, and Hermes without a code change.

What it would take to go further:

• Remote clients without SSH — FastMCP supports streamable-http; mcp.run() in server.py would become mcp.run(transport="streamable-http") behind Traefik with forward-auth. Small code change, real security decision: it puts the household's financial history behind a network listener. Not done, deliberately.
• A second user — requires owner scoping in the views (see caveats above) plus an identity the server can bind to; the DSN carries no user identity today.
• Write access — out of scope. The write path is the app's API routes with getCurrentUser(); agent_ro should stay SELECT-only.

---

Schema changes that affect agents

The views in 006_agent_read_role_views.sql read transactions, statements, and expense_metadata directly. If you rename or drop a column those views use, re-apply the migration in the smarthome repo — a Prisma migration here won't do it, and the views will either break or silently go stale:

The file is idempotent (CREATE OR REPLACE VIEW, guarded role creation) — re-applying is safe. It needs the agent.ro_password / agent.log_password settings supplied at apply time from Infisical; follow the apply instructions in the header of the SQL file itself (/mnt/user/appdata/smarthome/personal-agent/migrations/006_agent_read_role_views.sql) rather than piping it in blind.

New tables are readable by agent_ro automatically (ALTER DEFAULT PRIVILEGES), but new views need an explicit GRANT SELECT … TO agent_ro.