SQL Copilot

Ask analytics questions in plain English. Claude Fable 5 translates the question into SQL, a defense-in-depth validation pipeline confirms it's safe, and the result renders as a table plus a 3D bar chart.

Built as a portfolio piece to demonstrate safe handling of untrusted LLM output, not just "call the API and hope."

Architecture

• Next.js 16 (App Router) + TypeScript + Tailwind, deployed on Vercel
• Two Postgres schemas, two roles (Neon): app (users, query history) via a Prisma-managed app_runtime role; sandbox (the seeded demo dataset) via a sandbox_query_role that only ever has SELECT -- see prisma/seed/roles-and-grants.sql
• Defense-in-depth SQL execution -- every generated query passes through parser/allowlist/limit/cost checks before it ever reaches the database, in lib/sql-safety/pipeline.ts
• GitHub OAuth via Auth.js v5
• Claude Fable 5 (claude-fable-5) via the Anthropic API, with structured output for the SQL generation and a server-side fallback to Opus 4.8 on a policy refusal -- see lib/ai/nl-to-sql.ts
• PostHog for product analytics
• GitHub Actions CI (lint, typecheck, unit tests, migrate + seed against a real Postgres service, build, smoke test) gating merges to main; Vercel's Git integration handles the actual deploy

Why two database roles

The single most important design decision in this app: the LLM-generated SQL executes under sandbox_query_role, a Postgres role that is granted SELECT on the sandbox schema and nothing else, anywhere -- no access to the app schema (users, query history), no INSERT/UPDATE/DELETE/DDL, no pg_sleep/dblink. This holds even under total application-layer compromise: the app-level parser/allowlist in lib/sql-safety/ is the fast, specific rejection path, but the database role is the backstop that works even if that code has a bug.

Local setup

Requires Node 24+ and a Postgres instance (Neon recommended -- see below).

npm install
cp .env.example .env.local   # fill in as you go through the steps below

1. Database (Neon)

1. Create a project at neon.tech. Copy the pooled connection string into DATABASE_URL and the unpooled one into DIRECT_URL.
2. Generate two passwords for APP_RUNTIME_DB_PASSWORD and SANDBOX_QUERY_ROLE_DB_PASSWORD in .env.local (e.g. openssl rand -base64 24).
3. Run, in order:

   npx prisma migrate deploy      # creates the `app` schema + tables
   npm run db:seed-sandbox        # creates + seeds the `sandbox` schema
   npm run db:grants              # creates app_runtime / sandbox_query_role, applies all GRANTs/REVOKEs
   npm run db:seed-app            # seeds the one `app.datasets` row

4. Build SANDBOX_DB_URL using the sandbox_query_role credentials you just created, pointed at the same pooled host as DATABASE_URL.

2. GitHub OAuth

Create a new OAuth App at github.com/settings/developers (callback URL: http://localhost:3000/api/auth/callback/github for local dev). Fill in AUTH_GITHUB_ID / AUTH_GITHUB_SECRET. Generate AUTH_SECRET with openssl rand -base64 32.

3. Anthropic

Get an API key from the Anthropic Console and set ANTHROPIC_API_KEY. Claude Fable 5 requires the organization's data retention to be 30 days or more -- a zero-data-retention org gets a 400 on every request; check this under the Console's settings before deploying.

4. PostHog (optional locally)

Create a project at posthog.com, set NEXT_PUBLIC_POSTHOG_KEY / NEXT_PUBLIC_POSTHOG_HOST. Safe to leave blank locally -- analytics calls no-op without a key.

Run it

npm run dev

Testing

npm run test        # unit tests -- lib/sql-safety/*.test.ts, pure fixtures, no DB
npm run typecheck
npm run lint
npm run build

The SQL safety pipeline (parser.ts, allowlist.ts, limit-injector.ts) is unit-tested against SQL string fixtures with no database dependency. pipeline.ts itself (which executes against Postgres) and the full /api/query route are exercised by CI's smoke test and by manual testing against a real deployment, not vitest -- see the comments in .github/workflows/ci.yml for why.

Deployment

Push to GitHub, then import the repo on Vercel. Add every variable from .env.example in the Vercel project's environment variables. Vercel deploys automatically on every push to main (production) and every PR (preview); CI in .github/workflows/ci.yml gates merges to main via branch protection but does not itself deploy.