PHX Data Agent Starter

Chat with your PostgreSQL data in natural language. A Phoenix LiveView starter built around the Model Context Protocol (MCP): a policy-enforced, read-only SQL boundary; an MCP server external AI clients can call; and optional local inference via Ollama — the app boots and runs fine without any model host.

Ships with a synthetic demo dataset (a stable of 20 horses) so every example query works out of the box. Point it at your own schema by editing the seeds and the schema description — see Customization.

🚀 Quick start (no model required)

The app runs without Ollama: the UI, MCP endpoint, and the read-only SQL policy all work; chat requests return a clear error until a model host is configured.

Prerequisites

Elixir >= 1.18 / Erlang OTP >= 27 (the MCP stack — hermes_mcp/peri — needs Elixir 1.18+)
PostgreSQL >= 15

(The optional DevContainer ships newer versions — Elixir 1.18 / OTP 28 / PostgreSQL 17 — but the app supports the floors above; see SECURITY.md.)

Setup

mix deps.get
mix ecto.setup       # creates DB, runs migrations, seeds 20 demo records
mix phx.server

Web interface: http://localhost:4000 (override with PORT=4010 mix phx.server)
Database host/port: DB_HOST / DB_PORT env vars (default localhost:5432)
MCP endpoint: http://localhost:4000/mcp (restricted to localhost/private IPs)

Copy .env.example to see every supported environment variable.

🤖 Enabling AI chat (optional, local models via Ollama)

Install Ollama (https://ollama.ai) and start it: ollama serve
Pull a model: ollama pull qwen2.5 (~4.4 GB)
Restart Phoenix. The Chat Buddy widget (bottom-right) now answers questions like:

"How many horses do I have?"
"Show me all the Arabian horses"
"List horses by age, oldest first"
"Show me horses under 15 hands"

Model settings live in config/config.exs:

config :phx_ai, :ollama,
  base_url: "http://localhost:11434",
  default_model: "qwen2.5"
  # auto_pull: true   # opt-in: pull the model automatically at boot

auto_pull is off by default — the app never downloads gigabytes without being asked.

🐳 DevContainer (optional)

A pre-configured DevContainer (.devcontainer/) provides Elixir, PostgreSQL, and Ollama (with GPU support where Docker exposes one):

devpod up .          # or "Reopen in Container" in VS Code

First launch builds the container and runs mix deps.get + mix ecto.setup. GPU inference is optional; everything works CPU-only.

🗄️ Demo database schema

Table: horses
  id: uuid (primary key)
  name: string
  age: integer
  colour: string
  hands: integer (height in hands)
  breed: string
  inserted_at / updated_at: datetime

mix run priv/repo/seeds.exs resets the 20 synthetic demo records (Thoroughbred, Arabian, Friesian, …). No real data ships with the starter.

🏗️ Architecture

MCP Server (lib/phx_ai/mcp/mcp_server.ex) — exposes the natural_language_query tool over MCP StreamableHTTP at /mcp; works with standards-compliant external MCP clients as well as the internal path.
MCP Client (lib/phx_ai/mcp/mcp_client.ex) — tool selection, retries, and error recovery for the in-app chat.
SQL Policy (lib/phx_ai/mcp/sql_policy.ex) — the security boundary: only SELECT/WITH read queries execute, inside a READ ONLY transaction; 15 adversarial tests prove writes, DDL, statement stacking, and comment-hidden mutations are refused.
Chat Service (lib/phx_ai/chat_service.ex) — LiveView integration.
MCP Security plug (lib/phx_ai_web/plugs/mcp_security.ex) — restricts the MCP endpoint to localhost/private networks and logs access.

Data flow

User question → Chat Buddy → MCP client → tool selection (AI)
     ↓
Tool execution → SQL generation (AI) → read-only query → interpretation (AI)
     ↓
Conversational response

🛡️ Security model

Read-only by policy and by transaction: the SQL policy rejects anything but read queries, and execution happens in a READ ONLY transaction as a second line of defence. See docs/security-model.md.
Network security: the MCP endpoint only answers localhost/private IPs; security events are logged.
Honest boundary: generated SQL and AI interpretations require human validation — the policy prevents writes, it is not an authorization system.
Retries: unsafe or invalid SQL is corrected and retried (3 attempts max).

🔧 Customization guide

Your own data: replace the horses schema/migration/seeds with your tables; update the schema description the SQL-generation prompt uses (lib/phx_ai/mcp/params.ex).
Another model: change default_model, or point base_url at any Ollama-compatible host.
External MCP clients: connect any MCP client to /mcp and call natural_language_query with {"question": "..."}.
More tools: add modules under lib/phx_ai/mcp/ and register them with the server.

🔍 Troubleshooting

Chat returns an error

No model host running is the expected cause — the banner reads “Both primary and fallback chat methods failed”. Start Ollama and retry.
Check ollama list shows your model; check Phoenix logs.

Database errors

Verify PostgreSQL is reachable at DB_HOST/DB_PORT; mix ecto.reset rebuilds the demo DB.

MCP access denied

The endpoint only answers localhost/private IPs; check the security log lines (grep "MCP" log/dev.log).

DevContainer

Needs Docker running and >8 GB free disk; GPU is optional (docker run --rm --gpus all nvidia/cuda:11.0-base nvidia-smi to verify).

📚 Project docs

CHANGELOG.md — release history
docs/security-model.md — the read-only SQL boundary in depth
docs/RELEASE-NOTES-OVERHAUL.md — honest scope and known limitations
AGENTS.md / CLAUDE.md — guidance for AI coding agents working in this repo
SECURITY.md, SUPPORT.md — policy and support boundaries

PHX Data Agent Starter README