Lumen

Ask your business a question in plain language and get the exact number, computed live on your own database.

Lumen (working name: Business Assistant) is a dashboard where the owner of a small or medium business connects their own relational database and their own Claude API key, then asks things like "how much did I sell in May?". The answer is not an estimate from a language model: the model only picks one of a few predefined, parameterized, read-only query functions, the backend runs the SQL against the live database, and the model writes the sentence around the figure that came back.

The project is a work in progress built for the Brazilian market, so the product copy (UI, transactional emails and the assistant prompt) is in Brazilian Portuguese while the code and engineering documentation are in English.

The problem and the differentiator

Business owners already have the data. The answer is locked behind SQL, a spreadsheet or whoever knows how to extract it. Lumen puts an AI layer over structured relational data rather than documents, which is what makes the answers exact: the database does the math, the model never does. It is deliberately generic (any business with a database) rather than another e-commerce plugin.

Everything else follows from five values that each became an architectural decision:

• Privacy and least privilege. Read-only access, the owner chooses which tables the assistant can see, secrets are encrypted at rest.
• Precision. Numbers come from query results only; the model is instructed never to invent, estimate or recompute one.
• Security by construction. The model has no door to arbitrary SQL. It can only choose among allow-listed functions over allow-listed tables.
• Transparency. Every function call is logged (function, status, duration, sanitized params) and shown to the owner on an audit page. Raw row values are never stored.
• Isolation. Every table carries org_id, every query is scoped by it, and the id always comes from the JWT, never from the client.

How it works

1. The owner generates a MySQL onboarding script, runs it on their own server to create a dedicated SELECT-only user, and saves the credentials. Lumen tests the connection, rejects over-privileged users, introspects the schema and lets the owner pick which tables and foreign-key relationships to expose.
2. The owner pastes their own Anthropic API key. Lumen validates it with a real call, encrypts it and stores the default model.
3. In the chat, the owner asks a question. Claude (via the Vercel AI SDK) chooses a query function and its parameters, the backend validates them against the exposure allow-list, builds parameterized SQL, runs it read-only on the owner's MySQL, and streams the written answer back over SSE.

What exists today

Accounts and sessions

• Signup creates an organization plus its owner; passwords are hashed with argon2id.
• Email verification through Resend with hashed, single-use, expiring tokens (without an API key the sender is a no-op that logs only that the send was skipped, never the link).
• Login issues a short-lived JWT access token and a rotating refresh token, both in httpOnly; Secure; SameSite=None cookies; refresh tokens are stored hashed and are revocable. Logout, /auth/me, rate limiting on login and resend, and a constant-time guard against email enumeration.

Connecting the client database (MySQL)

• Consent terms captured with a version before any credential is accepted.
• Generated onboarding script that only ever emits CREATE USER + GRANT SELECT; the password is set by the owner and never generated by Lumen.
• Connection test with sanitized error categories; SHOW GRANTS is parsed with a deny-by-allowlist rule, so any privilege beyond SELECT, USAGE and SHOW VIEW fails the test.
• The database password is encrypted with AES-256-GCM under a master key that lives outside the database; blobs are self-describing (version|key_id|iv|tag|ciphertext) so the key can be rotated.
• Schema introspection (tables, columns, foreign keys) and an exposure allow-list of tables and relationships chosen by the owner.

Connecting the AI (Claude, bring your own key)

• The key is validated with a real test call, stored encrypted with the same crypto module, and never returned to the browser.
• A curated list of Claude models with an org-level default and a per-message switcher.

Chat

• Org-scoped sessions with history, auto-titles and renaming.
• A query-function registry with two functions: aggregate_over_time (sum/count/avg bucketed by day, week or month over a date column) and filtered_aggregate (sum/count/avg with equality and range filters, optional group-by and an optional join through an approved relationship). Aggregates, grains and operators are closed enums; tables and columns must be members of the allow-list; every value is bound as a parameter; results are capped.
• Streaming answers over Server-Sent Events, a bounded number of tool steps per turn, and explicit handling of the unhappy paths (AI not connected, invalid key, database unreachable, out-of-scope question, empty result).
• Sanitized function_call_logs and an audit page listing what the assistant queried.

Platform

• Sentry on both API and web, disabled cleanly without a DSN, with a scrubber that drops headers, bodies, cookies and secret-looking strings. Opaque request ids are returned on errors.
• A design system with light and dark themes where the frosted-glass effect is restricted to the chrome (sidebar, topbar, chat composer, menus) and data always sits on solid, high-contrast surfaces. A test enforces that rule.
• Environment validated at startup with a shared Zod schema (fail fast, readable messages).

Architecture

Browser ──HTTPS──> apps/web (React SPA, Vite)        static, Cloudflare Pages
   │
   └──HTTPS, credentials──> apps/api (Fastify, Node 22) ──> PostgreSQL (app data, Drizzle ORM)
                                  │                      └──> tenant's MySQL (live, read-only)
                                  └──> Anthropic API (tenant's own key, server-side, Vercel AI SDK)

• Monorepo: pnpm workspaces + Turborepo. Three packages: apps/web, apps/api, packages/shared.
• Frontend: React 18, Vite, React Router 7, TanStack Query, Zod forms, Vitest + Testing Library. The design system lives in apps/web/src/design-system.
• Backend: Fastify 5 with constructor-injected services (routes are registered only when their dependency is supplied, so tests drive the app with fakes through app.inject). Vercel AI SDK (ai + @ai-sdk/anthropic) for tool calling and streaming, jose for JWTs, @node-rs/argon2 for passwords, mysql2 for the tenant database.
• Application database: PostgreSQL 16 with Drizzle ORM; migrations are generated by drizzle-kit and applied by a small programmatic migrator. The reference schema is documented in db/schema.sql.
• Shared contracts: packages/shared holds the Zod schemas for every request and response, the environment contract, the query-function parameter schemas and the redaction helpers, consumed as TypeScript source by both apps.
• Secrets: AES-256-GCM with a versioned keyring; the master key comes from the environment. Tokens are stored as SHA-256 hashes.

Repository structure

Path	What it is
apps/api/	Fastify API: auth, db-connection, ai-connection, query-registry, chat, audit, crypto, observability. Drizzle schema, migrations and seed under src/db and drizzle/.
apps/web/	React SPA: auth pages, connect-database wizard, connect-AI page, chat, audit page, design system.
packages/shared/	Zod contracts shared by API and web (auth, connections, chat, query functions, env, redaction).
db/schema.sql	Commented reference schema of the application database (PostgreSQL).
design-system/	Original design-system handoff: tokens, React components, guide and a static preview.
deploy/	Caddyfile for the TLS-terminating reverse proxy in front of the API.
docker-compose.yml	Local Postgres and MySQL for development.
docker-compose.prod.yml	Production API container (non-root, read-only, env-only configuration).
.github/workflows/ci.yml	CI: build, lint, type-check and test across the workspace with a frozen lockfile.
context/	Project knowledge vault: constitution, specs 00-16, learnings, conventions and rules.
DECISIONS.md	Cross-iteration ledger of defaults taken, items to confirm with a human and pending live verifications.
DEPLOY.md	Deploy and operations runbook.
HANDOFF.md, projeto.md	Original product and architecture handoff (in Portuguese).
functions.prototype.ts	Early prototype of the generic query primitives with joins; not part of any build.

Getting started

Prerequisites: Node 22 (.nvmrc), pnpm 10.33 (enable it with corepack enable) and Docker.

pnpm install
docker compose up -d                 # Postgres on 5432 and a stand-in MySQL on 3306
cp .env.example .env

Fill in the two secrets in .env:

openssl rand -base64 32              # SECRETS_ENCRYPTION_KEY (must decode to exactly 32 bytes)
openssl rand -base64 48              # JWT_SECRET (at least 32 characters)

Then migrate, optionally seed, and run both apps:

pnpm --filter @lumen/api db:migrate
pnpm --filter @lumen/api db:seed     # demo org and owner; the login is printed at the end
pnpm dev                             # API on http://localhost:3001, web on http://localhost:5173

Environment variables (see .env.example for the full, commented list):

Variable	Required	Purpose
DATABASE_URL	yes	Application PostgreSQL.
MYSQL_URL	yes	Local MySQL stand-in used by the DB-facing tests; tenants connect their own database at runtime.
SECRETS_ENCRYPTION_KEY	yes	Base64 32-byte AES-256-GCM master key.
JWT_SECRET	yes	HS256 signing secret.
PORT, NODE_ENV, APP_URL, WEB_ORIGIN	defaulted	Runtime, link base URL and the explicit CORS allow-list (never *).
VITE_API_URL	defaulted	API origin baked into the SPA at build time.
RESEND_API_KEY, RESEND_FROM_EMAIL	optional	Transactional email; without a key the sender is a logging no-op.
SENTRY_DSN, SENTRY_ENVIRONMENT, SENTRY_TRACES_SAMPLE_RATE	optional	Observability; empty DSN disables Sentry.
ANTHROPIC_API_KEY	optional	Only for the env-gated live tests. The product itself always uses the tenant's own key.

Workspace scripts (each runs across apps/web, apps/api and packages/shared through Turborepo):

Command	What it does
pnpm dev	Vite dev server and tsx watch API.
pnpm build	Type-checks the API and shared package, builds the SPA.
pnpm lint / pnpm type-check	ESLint and tsc --noEmit.
pnpm test	Vitest. Tests that need Postgres, MySQL or a Claude key are env-gated and skip when the resource is absent.
pnpm format / pnpm format:check	Prettier.
pnpm --filter @lumen/api db:generate	Generate a Drizzle migration from the schema.
pnpm --filter @lumen/api build:bundle	esbuild bundle of the API used by the Docker image.

Deployment

DEPLOY.md is the runbook. In short, v1 is one API container on one VPS, the static frontend on Cloudflare Pages and a managed PostgreSQL (Neon or Supabase):

1. Create the managed Postgres and run the migrations with its direct connection string: DATABASE_URL=... pnpm --filter @lumen/api db:migrate (forward-only and idempotent).
2. On the VPS, export every secret as host environment variables, then docker compose -f docker-compose.prod.yml build && docker compose -f docker-compose.prod.yml up -d. The image is multi-stage, non-root, carries only the bundle and production dependencies, and exposes a /health healthcheck. Caddy (deploy/Caddyfile) terminates TLS and proxies to 127.0.0.1:3001.
3. Connect the repository to Cloudflare Pages with build command pnpm --filter @lumen/web build, output apps/web/dist and VITE_API_URL pointing at the API origin. A _redirects file provides the SPA fallback.

Because the auth cookie is cross-site, both origins must be HTTPS and WEB_ORIGIN must list the exact Pages origin. Rotating SECRETS_ENCRYPTION_KEY is a re-encryption pass, not an env swap; the runbook explains why.

Status and roadmap

All seventeen implementation specs (00-16, from the monorepo scaffold to deploy) are marked shipped in context/_index/specs.md, with build, lint, type-check and tests green in CI. It is still a work in progress rather than a finished product:

• Several integrations are verified only against local Docker or are still pending a live run; DECISIONS.md tracks them under LIVE-VERIFICATION-PENDING (real Resend sends, cross-site cookie behavior in production, end-to-end chat against a real Claude key, among others).
• The query registry is intentionally small (two functions). functions.prototype.ts sketches broader primitives (generic aggregate and list with joins by approved relationship) that have not been promoted into the registry.
• Deliberately out of scope for v1, recorded in the constitution so they are not accidentally built: inviting more users per organization, more than one AI provider or client database per organization, column-level exposure, and any caching or copying of the client database.
• Only MySQL is supported as the client database and only Claude as the model provider.

Further reading

• context/constitution.md for the non-negotiable principles.
• context/specs/ for the specification, plan and task list of each feature.
• context/learnings/ for the gotchas found along the way.
• DECISIONS.md for every default taken during the build.