Synapse DB Query

Ask questions about a Postgres database in natural language. The agent writes SQL, decides when to visualize, and the app renders the answer — as a chart, a table, or both — in a Synapse iframe inside NimbleBrain.

A Synapse app built on Upjack and packaged as an MCPB bundle. One FastMCP server exposes the tools, the UI resource (ui://db-query/main), and a persisted query entity for history.

---

Tools

The agent sees four hand-written tools plus three auto-generated by Upjack:

Tool	Source	Purpose
get_schema	Custom	List tables, columns, types, FKs. Each column carries a pre-quoted sql field so the agent never fumbles casing.
run_query	Custom	Execute a read-only SELECT. Returns rows to the agent only — not visible to the user. Use for probes, inspection, intermediate queries.
present_result	Custom	Display a result to the user. The only tool that updates the UI. Takes rows the agent already has, plus an optional Vega-Lite spec.
get_last_result	Custom	Return the most recently presented result (UI sync).
list_queries	Upjack	Browse presented results, newest first.
search_queries	Upjack	Full-text search across question/SQL/summary fields.
get_query	Upjack	Fetch a stored result by id.

No create_query, update_query, or delete_query — presented results are immutable snapshots and present_result is the sole writer.

Workflow

1. get_schema() — once per session.
2. run_query(sql) — probe or fetch. Invisible to the user. Can be called many times.
3. present_result(sql, columns, rows, vega_spec?, question?, summary?) — exactly once per user question, with the final answer. This commits the UI state and persists a query entity for history.
4. Users can review past queries on the History tab, or the agent can recall them via search_queries / get_query.

UI

Two tabs:

• Result — current answer: collapsible query details (question + SQL with a copy button), optional summary callout, chart (Vega-Lite), and data table with humanized headers.
• History — paginated list of every past presented result. Search box filters locally across question / summary / SQL. Click a row to replay it.

The UI consumes the full Synapse design-token set (typography scale, weights, shadows, radii, tertiary background) — no hardcoded colors, fonts, or radii.

Safety

Safety lives at the database, not in the tool surface. Connect as a Postgres role with SELECT grants only, and the worst an LLM-written query can do is time out.

• run_query rejects anything but SELECT or WITH … SELECT; no multi-statement.
• statement_timeout applied per query (default 10000 ms, override via QUERY_TIMEOUT_MS).
• Row cap per query (default 10000, override via MAX_ROWS).
• Connection URL sanitization drops non-libpq query parameters (e.g. TablePlus's statusColor) so pasted connection strings from GUI tools work as-is.

Architecture

synapse-db-query/
├── manifest.json              MCPB + Synapse host + Upjack metadata
├── server.json                NimbleBrain deployment spec
├── mpak.json                  mpak registry metadata
├── schemas/
│   └── query.schema.json      Upjack entity schema (persisted history)
├── skills/
│   └── query-coach/SKILL.md   Agent coaching (visualization defaults, spec library)
├── src/synapse_db_query/
│   ├── server.py              Tools + ui:// resource (Upjack + FastMCP)
│   └── ui.py                  Loads ui/dist/index.html
└── ui/                        React + Vite + Synapse SDK + Vega-Lite
    ├── src/App.tsx            Result view + History tab
    └── vite.config.ts         synapseVite() — auto-starts server in dev

Configuration

The bundle reads connection details from environment variables. For local dev, a .env in the project root is auto-loaded; in a NimbleBrain deployment, pass via the workspace's bundle config.

DATABASE_URL=postgresql://readonly_user:pass@host:5432/db   # required
QUERY_TIMEOUT_MS=10000                                       # optional
MAX_ROWS=10000                                               # optional

DATABASE_URL must point to a read-only role. The tool surface enforces SELECT-only, but the database role is the real boundary.

Dev loop

# One-time setup
uv sync
cd ui && npm install && cd ..

# Create .env with DATABASE_URL, then start the preview
#   (spawns the FastMCP server + Vite in the preview host iframe)
cd ui && npm run dev
# Open http://localhost:5173/__preview

Build

make build      # UI → ui/dist/index.html (single-file bundle)

Release bundles are built by .github/workflows/release.yml on every GitHub release: it runs the UI build, updates the version from the tag, and packs platform-specific .mcpb artifacts via NimbleBrainInc/mcpb-pack.

Vega-Lite contract

When calling present_result, pass a Vega-Lite v5 spec. Do not include a data field — the UI injects the returned rows automatically.

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "mark": {"type": "line", "point": true},
  "encoding": {
    "x": {"field": "month", "type": "temporal"},
    "y": {"field": "n", "type": "quantitative"}
  }
}

The UI renders via react-vega with ast: true and vega-interpreter so specs evaluate under a strict Content Security Policy (no unsafe-eval, required in sandboxed iframes).

See skills/query-coach/SKILL.md for the full spec library the agent can crib from (line, multi-series line, bar, heatmap, scatter, histogram).

License

MIT. See LICENSE.