One sentence: Upload a CSV, write SQL (or a Python-ish DSL), chart the result with PowerBI-class interactive charts, ask AI for help. One prism, every view.
┌────────────┐ ┌──────────────────────┐ ┌──────────────┐
│ Datasets │ → │ SQL / Python query │ → │ Chart / KPI │
└────────────┘ └──────────────────────┘ └──────────────┘
↑ ↑ ↑
CSV upload Monaco editor + Save + bundle into
type inference Cmd+Enter to run a dashboard or notebook
per-column AI: NL → SQL
profile Python-ish DSL
compiled to SQL
-
SQLite is the analytical engine. Every uploaded CSV becomes a real
ds_<id>table. The user's SQL runs natively. No mock query engine, no pandas-in-the-browser — actualSELECTagainst actual rows. -
Python-ish, compiled to SQL.
df.filter(status == "paid") .groupby("country").count()parses to an AST, then to a SQLite query. Analysts get a familiar surface; we get the same safe execution path. -
Bring-your-own AI keys, OpenAI primary. Both OpenAI and Anthropic are first-class providers. Users manage their own keys from Settings → AI providers — add, verify, delete. Keys are encrypted at rest (AES-256-GCM, derived from
JWT_SECRET). When both are configured, OpenAI is the default. Per-call override is supported viaprefer. -
AI assistance at the right seam. The NL→SQL endpoint receives the live column schema + sample values for the bound dataset. No blanket "AI guess what your data looks like" — the LLM gets real context.
-
Auto-insights are programmatic where they can be. Data-quality findings (nulls, single-value columns, identifier-like columns) and anomaly detection (3σ rule on numerics) are pure SQL. The LLM only writes the human-readable summary.
-
Real auth. Email + password (bcrypt, 10 rounds) + JWT in httpOnly cookies (jose, 7-day expiry). Three roles — admin / analyst / viewer — enforced at the API boundary.
-
Append-only audit log. Every mutation routes through
audit(). The audit page is a filtered view; no separate write path means no inconsistencies. -
No external chart library — but PowerBI-class interactivity. All charts are SVG primitives (
components/charts/PrismChart.tsx). Eleven chart kinds: bar · stacked_bar · grouped_bar · line · area · pie · donut · scatter · heatmap · combo · KPI. Every chart has hover tooltips with exact values, click events for cross-filter (click a bar on a dashboard → every other chart re-runs with that predicate), clickable legend to toggle series, and entry animations. Per-chart settings: data labels, sort direction, color offset. No dependencies pulled in.
- Next.js 15 App Router · React 19 RC · TypeScript strict
- Tailwind CSS — midnight + violet + cyan ("the prism")
- Monaco Editor (
@monaco-editor/react) with a custom Prism-Dark theme - better-sqlite3 WAL · proper foreign keys · dynamic data tables
- bcryptjs + jose for auth
- Anthropic SDK for AI assistance
Requires Node 18+.
cp .env.example .env
# Edit .env:
# - JWT_SECRET: long random string (>= 16 chars). Also derives the
# AES key that encrypts stored AI provider keys — rotating it
# invalidates both sessions AND stored keys.
# - OPENAI_API_KEY / ANTHROPIC_API_KEY: optional fallbacks. Users
# can override with their own keys from Settings → AI providers.
npm install --legacy-peer-deps # Monaco's React peer range is narrow
npm run seed # demo users + datasets + charts + notebook
npm run dev # http://localhost:5151Sign in with one of:
admin@prism.local/admin1234(admin · full access)analyst@prism.local/analyst1234(analyst · write)viewer@prism.local/viewer1234(viewer · read-only)
Or register a new account — the first user becomes admin automatically.
- Home. See the KPI strip, recent datasets, recent queries.
- Datasets → sales. Tabs: Preview · Schema · AI insights. Click "Regenerate" on Insights to see deterministic + LLM-authored findings.
- Queries → "Revenue by country". Press
Ctrl/Cmd+Enterto run. Toggle Results → Chart, pick "bar" → "Save as chart". - Dashboards → Quarter snapshot. Pre-wired with two charts.
- Queries → New query → Python-ish. Try:
df.groupby("region").sum("sessions").sort("sum_sessions", desc=true)— it compiles to SQL and runs againstweb_traffic. - Notebooks → Welcome to Prism. Edit cells, run them inline.
- API keys. Mint a key (shown once), then call:
curl -H "X-Prism-Key: prism_..." \ http://localhost:5151/api/datasets
app/
(app)/ authenticated panel — sidebar + main
home/ KPIs + recent datasets/queries/notebooks
datasets/ list + upload + [id]/detail (preview/schema/insights)
queries/ list + new + [id]/editor (split view + AI)
charts/ list + [id]/builder
dashboards/ list + [id]/grid
notebooks/ list + [id]/cell-based editor
insights/ flattened view across all datasets
schedules/ cron-style automation list + creator
api-keys/ mint + list
audit/ append-only event log
settings/ account + maturity panel
login/, register/ auth screens
api/
auth/ register · login · me · logout
datasets/ list, upload, [id]/get/patch/delete,
[id]/data (paginated preview),
[id]/profile (recompute stats)
queries/ CRUD + [id]/run + [id]/export?format=csv
charts/, dashboards/ CRUD + dashboard layout aggregate
notebooks/ CRUD + nested cells
ai/sql NL → SQL bound to a dataset's schema
ai/insight build + read auto-insights
schedules/ CRUD (runner not wired in this build)
api-keys/ create + list
audit filtered read of audit_log
globals.css design tokens
layout.tsx, page.tsx
components/
Sidebar.tsx fixed nav · destructive sign-out via ConfirmModal
Brand.tsx Prism wordmark — chromatic tilted square
Modal.tsx, ConfirmModal.tsx destructive confirms NEVER use window.confirm
CodeEditor.tsx Monaco wrapper · Prism-Dark theme · Cmd+Enter run
DataTable.tsx paginated/searchable read-only table
ColumnTypeBadge.tsx type-coloured chip
charts/PrismChart.tsx single SVG chart switching on kind
lib/
db.ts better-sqlite3 + WAL + 1 migration · audit()
auth.ts bcrypt + jose JWT + currentUser/requireUser
api-helpers.ts handle(), bad(), param()
client-api.ts fetch + multipart upload helpers
csv.ts RFC4180-ish parser + type inference + coercion
ingest.ts CSV → ds_<id> table + column metadata + profile
profile.ts per-column stats + data-quality findings
sql-runner.ts safe SELECT executor + row cap + system-table guard
py-runner.ts Python-ish DSL → SQL compiler
ai/claude.ts Anthropic client + dry-run fallback + cost estimator
ai/nl2sql.ts schema-aware NL → SQL prompt
ai/insights.ts deterministic findings + LLM summary
types.ts, format.ts, types-helpers.ts
scripts/seed.ts 3 synthetic datasets · queries · charts · notebook
- No
window.confirm()anywhere. Logging out, deleting a dataset/query/ chart/dashboard — all go through<ConfirmModal>. - SQL safety: only
SELECT/WITH/EXPLAINallowed; mutating keywords blocked; references to system tables (users,api_keys,audit_log,password_hash,workspace_members,schema_version) rejected; row count clamped to 10 000. - Soft-archive where cascade history matters; hard-delete when it doesn't.
Datasets
DROP TABLE; query history goes with the query. - Aesthetic intentionally distinct from siblings: midnight #0a0d18, violet #8b5cf6 primary, cyan #22d3ee secondary, Geist sans + JetBrains Mono. The wordmark gradient ("prism fan") is the only place we use the full chromatic palette — everywhere else stays violet/cyan to avoid carnival vibes.
The Settings → Maturity panel lists these honestly:
- Real-time collaboration (multi-cursor, presence)
- The schedules runner — UI is in, but no cron loop wakes them up yet
- SSO / OAuth — only password auth
- Multi-workspace (the schema supports it; the UI assumes one)
- Embeddable charts / shareable links
MIT.