m/paliad
1
0
Fork 0
Code Issues 146 Pull Requests 8 Actions 1 Packages Projects Releases Wiki Activity

feat: dedicated paliad MCP — replace supabase-MCP-with-SQL-wrapper for Paliadin DB access #37

New Issue
Open
opened 2026-05-12 11:56:56 +00:00 by mAi · 0 comments
mAi commented 2026-05-12 11:56:56 +00:00
Collaborator
Copy Link

Why

t-paliad-156 shipped per-user RLS by wrapping every paliad.* query in BEGIN; SET LOCAL ROLE authenticated; SET LOCAL request.jwt.claims = '...'; <q>; ROLLBACK; and reaching the DB through the generic supabase MCP at ystudio.msbls.de. RLS now enforces visibility properly, but the trust boundary is at the prompt layer — SKILL.md says "never query paliad.* without the wrapper", and one forgotten wrapper means a leak. The MCP itself doesn't know about paliad's auth model; it just executes whatever SQL it gets, as supabase_admin (BYPASSRLS) before our wrapper kicks in.

A dedicated paliad MCP moves the trust boundary from prompt-discipline to tool-availability: remove mcp__supabase__execute_sql from the paliadin pane entirely; only mcp__paliad__* tools exist. Forgetting the wrapper becomes impossible because the unsafe surface is gone.

What we get

  • Per-call auth is native. MCP takes the JWT (or JWT path) in tool args, sets request.jwt.claims server-side once, claude calls paliad_whats_on_my_plate() without any SET LOCAL boilerplate.
  • Tool catalog is the recipe. Each MCP tool's description trains claude directly. references/sql-recipes.md either disappears or shrinks to an advanced "raw SQL escape hatch" section (clearly walled off).
  • Schema-level isolation. The MCP physically can't touch data.* or other schemas. The connection pool authenticates as a role with USAGE on paliad only.
  • Audit per tool call lands in paliad's own logs with (user_id, tool_name, args, row_count). Way better than parsing wrapped SQL.
  • Folds in the agent-write path. The /api/paliadin/suggest/* HTTP endpoints from t-paliad-161 (deadline / appointment suggestions, approval-pipeline) become paliad_suggest_deadline(...) MCP tools. Same auth, same audit, no curl-in-tmux dance.

Prior art in m's stack

We already run two MCPs in production, with very different shapes:

mBrian MCP — ~/dev/mBrian/src/mcp/server.ts

  • bun + TypeScript + @modelcontextprotocol/sdk + zod + supabase-js. Stdio transport.
  • Typed tools per concept — get_node, list_nodes, create_node, get_edges, get_backlinks, find_similar, get_neighbors, etc. ~30 tools, each with a zod schema and a one-line description that doubles as the recipe.
  • Tool descriptions are concrete enough that claude rarely needs an external reference doc.
  • Why it works well: the surface mirrors the domain (knowledge graph: nodes, edges, neighbors, backlinks) rather than the underlying SQL. Claude reasons in terms claude already understands.
  • What we'd copy: typed-tools-per-concept, zod-equivalent input validation, stdio transport, single-binary distribution.
  • What we'd diverge on: mBrian uses service-role for everything (single user — m). Paliad needs per-call auth.

mAi MCP — ~/dev/mAi/internal/api/mcp_handler.go + cmd_mcp_memory.go

  • Go + hand-rolled JSON-RPC 2.0 (no SDK; mcp.go defines the wire format directly).
  • Binary at ~/.local/bin/maimcp, registered in ~/.mcp.json as command: maimcp.
  • Tools cover: list_tasks, create_task, get_messages, send_message, list_projects, register_worker, etc. — domain verbs, not SQL.
  • Why it works well: Go means one binary, no node runtime on every host. Same stack as paliad → same deploy story.
  • What we'd copy: the Go + JSON-RPC pattern, the binary-deploy model, the project-scoped registration via .mcp.json.
  • What we'd diverge on: mAi's MCP authenticates per-call against mai.workers (worker_id → project access). Paliad's per-call auth is JWT-based (auth.uid() → RLS).

Naive paliad MCP would land somewhere between

  • Go, like maimcp — paliad already has the sqlx.DB pool, the mintTurnJWT helper, the typed services.* layer. Registering them as MCP tools is mostly wire-up.
  • Typed tools mirroring SKILL.md recipes, like mbrian — paliad_whats_on_my_plate, paliad_list_my_projects, paliad_get_project_detail, paliad_search_my_deadlines, paliad_list_my_appointments, paliad_lookup_court, paliad_lookup_deadline_rule. 7–10 tools to cover the current recipe set.
  • JWT in tool args, not env. Every tool takes a jwt_path (or raw JWT) parameter; the MCP reads + verifies the signature against SUPABASE_JWT_SECRET, extracts claims, runs the query inside a tx that sets request.jwt.claims. Bad/missing JWT → typed error, no fallback to service-role.

Design questions to settle before writing code

  1. Generic escape hatch — paliad_execute_sql(jwt_path, sql) or no? Pros: unblocks novel recipes during dogfood. Cons: reopens the discipline problem (claude could pass BEGIN; SET LOCAL ROLE supabase_admin; ...). Likely answer: no for v1; pre-build every recipe; add new tools as new patterns emerge. Revisit if dogfood reveals the tool list is too narrow.
  2. JWT handoff: file path or raw token? Today the shim writes /tmp/paliadin/<turn>.jwt and the envelope carries |jwt=<path>. Option A: keep file, MCP reads it (filesystem coupling between paliad-shim and paliad-mcp on the same host). Option B: skip the file, paliad-shim hands the JWT directly via tool args (simpler but JWT shows up in MCP stdio logs).
  3. Where does the MCP run? Same host as the claude pane (mRiver for prod, laptop for dev). Spawned via stdio by .mcp.json. DB connection to youpc supabase over Tailscale — same pattern as paliad itself.
  4. DB role for the MCP. Currently paliad's Go services connect as postgres (BYPASSRLS). The MCP should connect as a dedicated role with USAGE on paliad only + GRANT to switch into authenticated via SET ROLE. Migration on top of 078.
  5. Replace or coexist with the supabase MCP in the paliadin pane? Phase 1: coexist (paliad MCP for paliad., supabase MCP for data. / UPC case law). Phase 2: a data MCP swallows supabase MCP for paliad's purposes entirely. Out of scope for this issue.
  6. Distribution. cmd/paliad-mcp/main.go produces a binary, ships in the same Docker image, gets symlinked to ~/.local/bin/paliad-mcp on mRiver via the existing install scripts.

Surface sketch (v1)

Tools, all take jwt_path (or jwt raw, TBD per Q2):

  • paliad_whats_on_my_plate → overdue / today / this_week / appointments_today counts
  • paliad_list_my_projects → active projects, paginated
  • paliad_get_project_detail(id | slug) → project + deadlines + appointments + parties
  • paliad_search_my_deadlines(status?, due_after?, due_before?)
  • paliad_list_my_appointments(from, to)
  • paliad_lookup_court(q) — firm-wide ref, no auth needed but kept in same MCP for surface coherence
  • paliad_lookup_deadline_rule(q) — same
  • paliad_suggest_deadline(...) — agent-write, folds in /api/paliadin/suggest/deadline
  • paliad_suggest_appointment(...) — same

Each tool's description is the recipe; SKILL.md collapses to "call the relevant paliad_* tool with jwt_path from the envelope; on missing/invalid JWT the tool returns a structured error you should surface verbatim."

Out of scope

  • Replacing the supabase MCP for data.* (UPC case law in youpc.org). Different schema, different ownership.
  • A central paliad-mcp server reached over HTTP/SSE. Stdio + per-pane spawn keeps the trust model simple.
  • Migrating other paliad code paths to use the MCP. The MCP is for the claude pane only; paliad's own handlers keep using sqlx directly.

Done when

  • cmd/paliad-mcp/main.go builds, ships in the paliad image, deploys to mRiver via the existing scripts pattern (scripts/install-paliad-mcp next to scripts/install-paliadin-skill).
  • A paliadin test config swaps mcp__supabase__execute_sql for mcp__paliad__* in .mcp.json for the claude pane.
  • All 7 paliad.* SKILL recipes have equivalent MCP tools.
  • SKILL.md updated: drop the SET LOCAL wrapper section, drop references/sql-recipes.md (or wall it off as raw-SQL escape hatch).
  • Migration adds a paliad_mcp Postgres role with USAGE on schema paliad and GRANT to SET ROLE authenticated.
  • One end-to-end dogfood turn from m's account: pane uses only mcp__paliad__*, RLS enforces, audit row reflects per-call tool name.

Phasing relative to t-paliad-156

Do NOT block on t-156's dogfood. The SQL-wrapper-via-MCP cut shipped today is a working enforcement layer; this issue is the next tightening pass. Right order:

  1. Dogfood t-156 for a few days. Watch which recipes claude actually reaches for; let the tool list be data-driven, not pre-decided.
  2. Design call: settle Q1–Q6 above. ~half-day.
  3. Implement v1 with the dogfood-validated tool set. ~1–2 days.
  4. Cut over the paliadin pane's .mcp.json on mRiver. Roll back trivially by re-enabling supabase in the same file.
## Why t-paliad-156 shipped per-user RLS by wrapping every `paliad.*` query in `BEGIN; SET LOCAL ROLE authenticated; SET LOCAL request.jwt.claims = '...'; <q>; ROLLBACK;` and reaching the DB through the generic supabase MCP at `ystudio.msbls.de`. RLS now enforces visibility properly, but the **trust boundary is at the prompt layer** — SKILL.md says "never query paliad.* without the wrapper", and one forgotten wrapper means a leak. The MCP itself doesn't know about paliad's auth model; it just executes whatever SQL it gets, as `supabase_admin` (BYPASSRLS) before our wrapper kicks in. A dedicated **paliad MCP** moves the trust boundary from prompt-discipline to tool-availability: remove `mcp__supabase__execute_sql` from the paliadin pane entirely; only `mcp__paliad__*` tools exist. Forgetting the wrapper becomes impossible because the unsafe surface is gone. ## What we get - **Per-call auth is native.** MCP takes the JWT (or JWT path) in tool args, sets `request.jwt.claims` server-side once, claude calls `paliad_whats_on_my_plate()` without any SET LOCAL boilerplate. - **Tool catalog *is* the recipe.** Each MCP tool's description trains claude directly. `references/sql-recipes.md` either disappears or shrinks to an advanced "raw SQL escape hatch" section (clearly walled off). - **Schema-level isolation.** The MCP physically can't touch `data.*` or other schemas. The connection pool authenticates as a role with USAGE on `paliad` only. - **Audit per tool call** lands in paliad's own logs with `(user_id, tool_name, args, row_count)`. Way better than parsing wrapped SQL. - **Folds in the agent-write path.** The `/api/paliadin/suggest/*` HTTP endpoints from t-paliad-161 (deadline / appointment suggestions, approval-pipeline) become `paliad_suggest_deadline(...)` MCP tools. Same auth, same audit, no curl-in-tmux dance. ## Prior art in m's stack We already run two MCPs in production, with very different shapes: ### mBrian MCP — `~/dev/mBrian/src/mcp/server.ts` - **bun + TypeScript + @modelcontextprotocol/sdk + zod + supabase-js.** Stdio transport. - **Typed tools per concept** — `get_node`, `list_nodes`, `create_node`, `get_edges`, `get_backlinks`, `find_similar`, `get_neighbors`, etc. ~30 tools, each with a zod schema and a one-line description that doubles as the recipe. - Tool descriptions are concrete enough that claude rarely needs an external reference doc. - **Why it works well:** the surface mirrors the domain (knowledge graph: nodes, edges, neighbors, backlinks) rather than the underlying SQL. Claude reasons in terms claude already understands. - **What we'd copy:** typed-tools-per-concept, zod-equivalent input validation, stdio transport, single-binary distribution. - **What we'd diverge on:** mBrian uses service-role for everything (single user — m). Paliad needs per-call auth. ### mAi MCP — `~/dev/mAi/internal/api/mcp_handler.go` + `cmd_mcp_memory.go` - **Go + hand-rolled JSON-RPC 2.0** (no SDK; `mcp.go` defines the wire format directly). - Binary at `~/.local/bin/maimcp`, registered in `~/.mcp.json` as `command: maimcp`. - Tools cover: `list_tasks`, `create_task`, `get_messages`, `send_message`, `list_projects`, `register_worker`, etc. — domain verbs, not SQL. - **Why it works well:** Go means one binary, no node runtime on every host. Same stack as paliad → same deploy story. - **What we'd copy:** the Go + JSON-RPC pattern, the binary-deploy model, the project-scoped registration via `.mcp.json`. - **What we'd diverge on:** mAi's MCP authenticates per-call against `mai.workers` (worker_id → project access). Paliad's per-call auth is JWT-based (`auth.uid()` → RLS). ### Naive paliad MCP would land somewhere between - **Go**, like maimcp — paliad already has the `sqlx.DB` pool, the `mintTurnJWT` helper, the typed `services.*` layer. Registering them as MCP tools is mostly wire-up. - **Typed tools mirroring SKILL.md recipes**, like mbrian — `paliad_whats_on_my_plate`, `paliad_list_my_projects`, `paliad_get_project_detail`, `paliad_search_my_deadlines`, `paliad_list_my_appointments`, `paliad_lookup_court`, `paliad_lookup_deadline_rule`. 7–10 tools to cover the current recipe set. - **JWT in tool args, not env.** Every tool takes a `jwt_path` (or raw JWT) parameter; the MCP reads + verifies the signature against `SUPABASE_JWT_SECRET`, extracts claims, runs the query inside a tx that sets `request.jwt.claims`. Bad/missing JWT → typed error, no fallback to service-role. ## Design questions to settle before writing code 1. **Generic escape hatch — `paliad_execute_sql(jwt_path, sql)` or no?** Pros: unblocks novel recipes during dogfood. Cons: reopens the discipline problem (claude could pass `BEGIN; SET LOCAL ROLE supabase_admin; ...`). Likely answer: **no for v1**; pre-build every recipe; add new tools as new patterns emerge. Revisit if dogfood reveals the tool list is too narrow. 2. **JWT handoff: file path or raw token?** Today the shim writes `/tmp/paliadin/<turn>.jwt` and the envelope carries `|jwt=<path>`. Option A: keep file, MCP reads it (filesystem coupling between paliad-shim and paliad-mcp on the same host). Option B: skip the file, paliad-shim hands the JWT directly via tool args (simpler but JWT shows up in MCP stdio logs). 3. **Where does the MCP run?** Same host as the claude pane (mRiver for prod, laptop for dev). Spawned via stdio by `.mcp.json`. **DB connection to youpc supabase over Tailscale** — same pattern as paliad itself. 4. **DB role for the MCP.** Currently paliad's Go services connect as `postgres` (BYPASSRLS). The MCP should connect as a dedicated role with USAGE on `paliad` only + GRANT to switch into `authenticated` via `SET ROLE`. Migration on top of 078. 5. **Replace or coexist with the supabase MCP in the paliadin pane?** Phase 1: coexist (paliad MCP for paliad.*, supabase MCP for data.* / UPC case law). Phase 2: a data MCP swallows supabase MCP for paliad's purposes entirely. Out of scope for this issue. 6. **Distribution.** `cmd/paliad-mcp/main.go` produces a binary, ships in the same Docker image, gets symlinked to `~/.local/bin/paliad-mcp` on mRiver via the existing install scripts. ## Surface sketch (v1) Tools, all take `jwt_path` (or `jwt` raw, TBD per Q2): - `paliad_whats_on_my_plate` → overdue / today / this_week / appointments_today counts - `paliad_list_my_projects` → active projects, paginated - `paliad_get_project_detail(id | slug)` → project + deadlines + appointments + parties - `paliad_search_my_deadlines(status?, due_after?, due_before?)` - `paliad_list_my_appointments(from, to)` - `paliad_lookup_court(q)` — firm-wide ref, no auth needed but kept in same MCP for surface coherence - `paliad_lookup_deadline_rule(q)` — same - `paliad_suggest_deadline(...)` — agent-write, folds in `/api/paliadin/suggest/deadline` - `paliad_suggest_appointment(...)` — same Each tool's description is the recipe; SKILL.md collapses to "call the relevant `paliad_*` tool with `jwt_path` from the envelope; on missing/invalid JWT the tool returns a structured error you should surface verbatim." ## Out of scope - Replacing the supabase MCP for `data.*` (UPC case law in youpc.org). Different schema, different ownership. - A central paliad-mcp server reached over HTTP/SSE. Stdio + per-pane spawn keeps the trust model simple. - Migrating other paliad code paths to use the MCP. The MCP is for the claude pane only; paliad's own handlers keep using `sqlx` directly. ## Done when - `cmd/paliad-mcp/main.go` builds, ships in the paliad image, deploys to mRiver via the existing scripts pattern (`scripts/install-paliad-mcp` next to `scripts/install-paliadin-skill`). - A `paliadin` test config swaps `mcp__supabase__execute_sql` for `mcp__paliad__*` in `.mcp.json` for the claude pane. - All 7 paliad.* SKILL recipes have equivalent MCP tools. - SKILL.md updated: drop the SET LOCAL wrapper section, drop `references/sql-recipes.md` (or wall it off as raw-SQL escape hatch). - Migration adds a `paliad_mcp` Postgres role with `USAGE` on schema `paliad` and GRANT to `SET ROLE authenticated`. - One end-to-end dogfood turn from m's account: pane uses only `mcp__paliad__*`, RLS enforces, audit row reflects per-call tool name. ## Phasing relative to t-paliad-156 **Do NOT block on t-156's dogfood.** The SQL-wrapper-via-MCP cut shipped today is a working enforcement layer; this issue is the *next* tightening pass. Right order: 1. Dogfood t-156 for a few days. Watch which recipes claude actually reaches for; let the tool list be data-driven, not pre-decided. 2. Design call: settle Q1–Q6 above. ~half-day. 3. Implement v1 with the dogfood-validated tool set. ~1–2 days. 4. Cut over the paliadin pane's `.mcp.json` on mRiver. Roll back trivially by re-enabling `supabase` in the same file.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
deferred
Parked — not pursuing now, may un-park later
done
Work completed by an agent; awaiting m to close
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
mAi m
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: m/paliad#37
No description provided.
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?