239 lines
10 KiB
Markdown
239 lines
10 KiB
Markdown
# 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`](ssh://git@localhost:2222/siddharthd/personal-agent-gateway.git)
|
||
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](#schema-changes-that-affect-agents)).
|
||
|
||
---
|
||
|
||
## 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:
|
||
|
||
```bash
|
||
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](../README.md#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:
|
||
|
||
```bash
|
||
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:
|
||
|
||
```bash
|
||
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)
|
||
|
||
```bash
|
||
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:
|
||
|
||
```bash
|
||
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`:
|
||
|
||
```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:
|
||
|
||
```bash
|
||
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:
|
||
|
||
```bash
|
||
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`.
|