WhyQLWhyQL

The sceneThe scene

It is Monday morning. An operations lead notices that orders fell in the third quarter but recovered in August. The company has a PostgreSQL database, a BI tool with forty dashboards, and no dedicated data analyst. The question is not on any dashboard because nobody predicted it.

She opens WhyQL, selects the production read replica, and asks:

"Why did orders drop in Q3 but not in August?"

WhyQL does not return a chart and hope she interprets it. It returns:

• a section showing monthly order counts and revenue,
• a section comparing August against the rest of Q3,
• a section showing the share of orders by channel and status,
• and a narrative that says what the evidence supports and what it does not.

She pins the answer to a board, keeps it for the next team review, and moves on. The entire interaction takes minutes, not a ticket cycle.

That is the product.

The gap WhyQL closesThe gap WhyQL closes

Most analytics tools assume the question is already known. Dashboards are answers to yesterday's questions. The long tail of operational questions — the ones that start with "why", "which", "what changed", "who is affected" — falls into one of three bad options:

WhyQL is designed for the space between the dashboard and the analyst queue: questions that are too specific for a dashboard and too valuable to wait a week for.

What "why" actually meansWhat "why" actually means

SQL can prove what happened. It cannot, by itself, explain why. A naive system asks the model to "explain why sales dropped" and gets a fluent story that may have nothing to do with the data.

WhyQL separates the two jobs:

• The database supplies evidence. The model is instructed to write SQL that selects the rows and columns that make the situation visible: missing joins, nulls, date mismatches, channel splits, status distributions.
• The narrator supplies an interpretation. It receives only the executed result rows, with a strict row and cell budget, and is told that the rows are the only evidence allowed. If the data does not determine a cause, it must say so.

This is the product's central insight: an explanation is only trustworthy when it is downstream of evidence the user can inspect.

Four outcomes, one interfaceFour outcomes, one interface

Not every question deserves a database query. WhyQL routes each question before spending LLM or database work on it.

The router is itself an LLM call, but its failure policy is deliberately boring: if the router is down or returns something unparseable, the question falls back to the normal data-query path. A router outage must never break the product.

WhyQL is a read-only, AI-native analytics workspace. A user connects a PostgreSQL or MySQL database, asks a question in plain English, and receives a real answer from their own data: the rows that answer it, rendered as a table or chart, and — for "why" or "how" questions — a short explanation grounded only in the returned evidence. For data-query answers, the validated query plan is persisted with the job.

It is not a chatbot with database credentials, and it is not a dashboard builder with a natural-language box bolted on. It is a complete question-to-evidence pipeline:

1. Understand the intent. A schema-metadata fast path answers questions about the schema itself, then a small LLM call routes everything else to one of three paths: answer from general knowledge, query the data, or query the data and then explain it.
2. Ground the model. The prompt contains the schema snapshot (with hidden columns marked), dialect rules, the top retrieved schema notes, and validated past question-to-SQL pairs. Result rows never enter the generation prompt.
3. Constrain the output. The model returns a structured QueryPlan, not free-form SQL. The plan carries the primary query, up to three extra views, assumptions, and clarifying questions.
4. Verify before execution. A JSqlParser-based gate rejects anything that is not a single read-only SELECT against allowlisted tables and columns, with a LIMIT, no dangerous functions, no unconstrained cross joins, and no write-shaped tricks.
5. Execute safely. A dialect-aware JDBC adapter opens a short-lived read-only connection, enforces a 30-second statement timeout and a 1,000-row hard cap, strips hidden columns, and normalizes driver-specific types.
6. Shape the answer. Results are returned as sections. Tables above 200 rows are withheld in favor of an exact count, so the user gets an answer rather than a download.
7. Explain when asked. For explanatory questions, a narrator receives at most 40 rows, 120 characters per cell, and is instructed to say plainly when the data does not determine a cause.
8. Keep the work. Answers can be pinned to a dashboard, organized into chat sessions, or pinned as widgets on a board that can mix manual tables, SQL, and natural-language questions.

The platform is built as a modular monolith with an event-driven spine. Spring Modulith enforces 15 module boundaries; Kafka carries the four-stage query pipeline so no HTTP thread ever waits on an LLM; PostgreSQL is the durable source of truth for jobs, results, credentials, schemas, chats, boards, and dashboard cards; pgvector stores schema memory and validated exemplars.

The result is a system that behaves less like a chatbot and more like a compiler with a human in the loop: natural language goes in, an intermediate representation comes out, static analysis checks it, a runtime executes it with limits, and the system explains what happened.

CapabilitiesCapabilities

The product surfacesThe product surfaces

End-to-end journeyEnd-to-end journey

1. Register. Email and password; the account is PENDING.
2. Verify. A six-digit OTP is emailed; five attempts are allowed, the code lives five minutes, and resends are limited to three per hour per email and per IP.
3. Connect. Add a PostgreSQL or MySQL source. The backend validates the URL, blocks internal hosts in cloud mode, opens a live connection, and encrypts the credentials.
4. Introspect. The source's schema is read from the real catalog, filtered to tables the connecting principal can actually SELECT.
5. Ask. The question becomes a durable job with an owner, a data source, and an optional chat session.
6. Generate. The model receives schema context, dialect hints, retrieved notes, and validated exemplars — never result rows.
7. Validate and execute. Each section is parsed, checked, and run read-only with time and row limits.
8. Analyze. For why/how questions, the narrator reads the evidence and writes a constrained explanation.
9. Complete. The result envelope, plan, assumptions, and narrative are stored; the subscription emits the terminal state.
10. Keep. Pin the answer, keep the thread, or turn the question into a board widget.

The model proposes. The system disposes.

WhyQL is designed around a simple division of labor. The LLM is good at ambiguity, language, and SQL-shaped intent. It is bad at guarantees. So the system never asks it to be a database, a security boundary, or a source of truth. It asks it to produce a plan, then treats that plan as untrusted input.

Five invariants follow from that division.

1. Schema is context; rows are evidence1. Schema is context; rows are evidence

The generation prompt contains schema text: table names, columns, types, nullability, primary keys, foreign keys, and hidden flags. It does not contain result rows. This is both a security decision and a quality decision.

• Security: result rows can contain PII, secrets, or attacker-controlled text. Feeding them back into a prompt creates a prompt-injection channel and a data-leak channel.
• Quality: a model that sees sample rows starts reasoning from anecdotes. A model that sees only structure is forced to write a query that generalizes.

The schema itself is treated as untrusted data. The prompt explicitly says so.

2. The query plan is the contract2. The query plan is the contract

The model does not return raw SQL. It returns a QueryPlan:

{
  "engine": "POSTGRES",
  "queryText": "SELECT ... LIMIT 200",
  "explanation": "Revenue is grouped by month and channel...",
  "assumptions": ["Revenue means the paid amount, not gross bookings"],
  "clarifyingQuestions": [],
  "sections": [
    { "title": "Monthly trend", "queryText": "SELECT ... LIMIT 200" }
  ]
}

This intermediate representation is the most important design decision in the system. It gives the platform:

• a stable contract to validate and persist,
• a place to carry assumptions and clarifying questions,
• a way to support multi-section answers without asking the model for one giant query,
• and a clean boundary for repair: blank primaries can be promoted, malformed sections can be dropped, duplicates can be removed.

The plan is the compiler IR. Everything downstream reads it; nothing downstream trusts it.

3. Validation is a gate, not a suggestion3. Validation is a gate, not a suggestion

The generated SQL is parsed into an AST and checked against a policy. A failure is a failure. The production pipeline does not quietly execute a suspicious query because the model seemed confident.

The validator checks:

• exactly one statement,
• SELECT only,
• resolvable SELECT shape (simple or compound; unknown shapes fail closed),
• a LIMIT on the statement or on at least one component (the executor's hard row cap remains the backstop),
• every referenced table exists in the schema snapshot,
• every referenced column exists and is not hidden,
• no dangerous functions (pg_sleep, file access, COPY, INTO OUTFILE, and similar),
• no write-shaped SELECT constructs,
• no unconstrained cross joins,
• bounded derived-table depth.

4. The job is the unit of work4. The job is the unit of work

A question is not an HTTP request. It is a job with a durable state machine:

QUEUED → GENERATING → VALIDATING → EXECUTING → ANALYZING → COMPLETED
                                   ↘ FAILED
              ↘ NEEDS_CLARIFICATION

The job row is the source of truth. Kafka carries the transitions; GraphQL subscriptions carry them to the browser; PostgreSQL remembers them if the browser, the WebSocket, or the whole backend restarts.

This is what allows the UI to show a live stage timeline instead of a spinner, and what allows a failed generation to be inspected later instead of vanishing into a log line.

5. Enrichment degrades; the answer survives5. Enrichment degrades; the answer survives

Several parts of the system are valuable but non-essential: RAG retrieval, schema indexing, chat recording, exemplar memorization, narrative analysis. Each is wrapped so that its failure is absorbed.

• If RAG retrieval fails, generation proceeds without retrieved context.
• If schema indexing fails, the ask still runs.
• If chat recording fails, the job still completes.
• If exemplar memorization fails, the answer is unaffected.
• If the narrator fails, the user gets the plain result table.

The inverse is also true: security and correctness checks do not degrade. Validation fails closed. Execution limits are not optional. This asymmetry is deliberate.

The compiler analogyThe compiler analogy

Natural language
      │
      ▼
 Intent router ──────────────► general answer / schema answer
      │
      ▼
 Schema + RAG + exemplars
      │
      ▼
 LLM  ──►  QueryPlan (intermediate representation)
      │
      ▼
 Static analysis (JSqlParser + policy)
      │
      ▼
 Read-only runtime (JDBC, limits, normalization)
      │
      ▼
 ResultEnvelope (sections, sizes, counts)
      │
      ▼
 Optional narrator (grounded in rows)
      │
      ▼
 Answer + evidence (plan persisted for audit)

A compiler does not trust the source program; it parses it, checks it, and runs it in a defined environment. WhyQL applies the same posture to model output.

System contextSystem context

┌─────────────────────────────────────────────────────────────────────┐
│                            Browser                                  │
│  Next.js 16 App Router · React 19 · Apollo Client · ECharts         │
│  HTTP GraphQL + graphql-transport-ws subscription                   │
└───────────────────────────────┬─────────────────────────────────────┘
                                │ HTTPS / WSS
┌───────────────────────────────▼─────────────────────────────────────┐
│                     Spring Boot modular monolith                    │
│                                                                     │
│  api ──► pipeline ──► jobs ──► Kafka ──► pipeline consumers         │
│   │         │           │                                           │
│   │         ├──► query ──► datasource adapters ──► user database    │
│   │         ├──► rag ──► pgvector                                   │
│   │         ├──► schema ──► snapshots / sandbox                     │
│   │         ├──► chat / dashboard / board                           │
│   │         └──► auth ──► Cacheify (Redis protocol or HTTP)         │
│                                                                     │
│  PostgreSQL: identity, data sources, snapshots, jobs, results,      │
│              chats, boards, dashboard cards, vector store           │
└───────────────────────────────┬─────────────────────────────────────┘
                                │
        ┌───────────────────────┼─────────────────────────┐
        ▼                       ▼                         ▼
  PostgreSQL + pgvector     Kafka (KRaft)          Cacheify / Redis
  (app state, vectors)      (stage transport)      (OTP, refresh, limits)
                                │
                                ▼
                    User PostgreSQL / MySQL
                    (short-lived read-only connections)

Why a modular monolith, not microservicesWhy a modular monolith, not microservices

The system has clear domain boundaries but a small operational surface. Splitting it into fifteen deployable services on day one would buy independence the product does not need yet and cost distributed transactions, network failures, deployment coordination, and observability complexity.

Instead:

• Spring Modulith enforces boundaries at build time through a structure test. Each module declares its allowed dependencies; ModularityTests fails the build if a module reaches into another module's internals.
• Modules expose named interfaces (domain, services, events) and keep repositories, adapters, and execution internals private.
• Kafka appears only at the pipeline seams, where durability and independent scaling genuinely matter. Everything else is an in-process call.
• The database is a single PostgreSQL instance, so job state, results, and chat records can be written transactionally.

This is a deliberate "monolith first, extract later" architecture. The extraction path is already visible: pipeline consumers are already isolated behind Kafka topics; boards, chat, and auth have clean service interfaces; and the event contracts are explicit.

The bounded contextsThe bounded contexts

The backend is organized into 15 Spring Modulith modules plus the GraphQL api module: 12 substantive modules and three thin or reserved seams.

Calling out the reserved modules is important: a portfolio document should distinguish between what is implemented and what is a deliberate extension point.

API surfaceAPI surface

The canonical contract is backend/src/main/resources/graphql/schema.graphqls. It is intentionally small and typed.

Queries

• dataSources — owner-scoped sources, never credentials
• job(id) — status, result, sections, analysis, clarifying questions
• dashboard — pinned cards
• boards, board(id) — board and widget state
• schemaTables(dataSourceId) — table names for the board builder
• chatSessions(page, size), chatMessages(sessionId, page, size) — paged history

Mutations

• addDataSource, askQuestion
• saveToDashboard, removeDashboardCard
• createBoard, removeBoard, saveBoardWidget, removeBoardWidget
• createChatSession, renameChatSession, deleteChatSession

Subscription

• jobUpdates(jobId) — initial snapshot plus every transition until a terminal state

Authentication and credential operations live on REST endpoints because they are lifecycle flows, not graph queries: register, verify OTP, resend OTP, login, refresh, logout, OAuth callbacks, datasource connection test, credential reveal, and credential update.

Why GraphQL?

• One typed contract for a UI with many related entities.
• Subscriptions map naturally to job-stage streaming.
• The client can request exactly the result shape it renders, including heterogeneous Json cells.
• The trade-off is real: caching is harder than REST, authorization must be enforced per field/resolver rather than per route, and WebSocket authentication needs its own bridge. Those costs were accepted because the live job timeline is central to the product.

The event pipelineThe event pipeline

Every question is an event-driven state machine. Each stage topic has a symmetric dead-letter topic.

Every stage also writes to *.dlq if a publish fails after retries. The point is not ceremony; it is that a failed publish must never silently strand a job.

Data modelData model

Schema ownership belongs to Flyway. Hibernate is set to validate only, so the application can never drift the database implicitly.

Two details matter:

• Results are stored as JSON in jobs.result_json and board_widgets.result_json. This keeps the write path simple and preserves exactly what the user saw. The trade-off is that result data is not relationally queryable and will eventually need retention and archival policy.
• Ownership is denormalized (owner_user_id) on every user-facing aggregate. That makes owner scoping cheap and explicit, at the cost of needing discipline on every query. The repositories and resolvers enforce it consistently today; a team/org model would require a second dimension.

Deployment shapeDeployment shape

The development topology is defined in backend/compose.yaml:

• PostgreSQL 16 with pgvector,
• a single-node Kafka broker in KRaft mode (pinned image, not latest),
• MailHog for OTP email inspection,
• no local Redis by design: Cacheify is the Redis-compatible store, reachable over the RESP protocol or its HTTP data plane.

The backend runs via ./mvnw spring-boot:run; the frontend via npm run dev. Docker Compose is auto-started by Spring Boot in development.

A production deployment would keep the same shape but replace each development dependency with a managed service, run multiple stateless backend replicas behind a load balancer, separate pipeline workers from the API if LLM load grows, and put a CDN in front of the Next.js frontend. The single-instance WebSocket hub is the main piece that must change first; see the system-design section.

5.1 Schema acquisition5.1 Schema acquisition

WhyQL can learn a schema in five ways, all converging on a stored SchemaSnapshot:

1. Live introspection. The datasource adapter opens the connection and reads the catalog.
2. SQL upload. DDL and optional seed INSERTs are parsed, then loaded into an isolated sandbox_<12hex> schema inside the application database.
3. Prisma. A focused parser reads model blocks, scalar fields, @map, and @relation foreign keys.
4. Liquibase XML. A parser reads changelog files.
5. JPA / zip / GitHub. A zip walker routes files by extension and path — .sql, .java, .prisma, Liquibase XML — and merges the results. A GitHub repository is downloaded as a zip through fixed GitHub hosts with strict owner/repo/ref validation.

The zip path is hardened against the obvious attacks: a 50 MB total limit, 2,000 entries maximum, 5 MB per entry, and path-prefix scoping for monorepos.

A subtle but important behavior: later files refine earlier definitions rather than erasing them. A Java entity can add a column that the SQL migration did not mention; a Prisma relation can add a foreign key the DDL omitted. The snapshot is a merged model, not a winner-take-all parse.

For live PostgreSQL, generic JDBC metadata was not good enough. Hosted providers such as Neon, Supabase, and RDS can expose schemas belonging to other tenants, and search_path behavior can hide tables the user actually needs. The PostgreSQL profile therefore reads the real catalog, enumerates non-system schemas, and filters to tables on which the connecting principal holds SELECT privilege. Discovery reflects least privilege, not just execution.

5.2 Schema memory and RAG5.2 Schema memory and RAG

Two kinds of memory live in pgvector:

• Schema documents. One document per visible table and one per visible column, describing names, types, nullability, keys, and relationships.
• Validated exemplars. Completed question-to-SQL pairs, stored only after a successful execution, retrieved as few-shot examples for similar future questions.

At ask time, the engine retrieves the top five schema documents and the top three exemplars for the data source. Retrieval is scoped by data source metadata, so one tenant's schema memory cannot bleed into another's.

The embeddings are the interesting constraint. Groq exposes no embeddings endpoint. Rather than block the feature on a second vendor, WhyQL ships a local, deterministic 512-dimensional lexical embedding model: feature hashing with FNV-1a, term-frequency weights, word features, and character trigrams, normalized to unit length. It costs nothing, runs in-process, and is fully reproducible.

The trade-off is explicit: lexical overlap handles "orders" versus "orders table" well, but synonym-level semantics ("sales" versus "orders") are weak. The embedding model is behind Spring AI's EmbeddingModel interface, so replacing it with a local ONNX model or a hosted embedding provider is a bean swap, not a rewrite.

Re-indexing is idempotent per data source: schema documents are removed by metadata filter before the new set is written, while exemplar memory is left untouched.

5.3 Prompt construction5.3 Prompt construction

The prompt is assembled from four ingredients:

• System rules: read-only single SELECT, only schema tables and columns, never hidden columns, always a LIMIT, no unconstrained joins, no dangerous functions, no system schemas, a JSON QueryPlan output contract.
• Dialect guidance: PostgreSQL multi-schema naming and string/date rules; MySQL's single-database model and syntax differences.
• Schema context: tables, columns, types, nullability, primary keys, foreign keys, hidden flags.
• Retrieved context: schema notes and validated exemplars, clearly labeled as data rather than instructions.

The current date is injected so "today", "this month", and "last year" resolve correctly. Temperature is zero.

The prompt also encodes the product's most important behavioral rule:

If the question asks WHY or HOW, SQL cannot explain — it supplies EVIDENCE: select the rows and columns that make the situation visible, so the analysis stage can explain the pattern.

There is no toolCallbacks wiring in the generation call. That is deliberate; the reason is one of the case studies later in this document.

5.4 The query plan contract5.4 The query plan contract

The model returns JSON matching QueryPlan. The parser is tolerant in exactly the right places:

• A blank primary queryText with non-empty sections promotes the first section to primary.
• Malformed sections are dropped, not fatal.
• Sections beyond three are truncated.
• Duplicate sections equal to the primary are removed later, before execution.

This is repair at the boundary, not trust. The model can be sloppy; the system still produces a coherent plan.

Clarifying questions are a first-class outcome. If the model cannot resolve a critical ambiguity, it returns questions and no query. The job becomes NEEDS_CLARIFICATION and the UI asks the user rather than guessing.

5.5 Static validation5.5 Static validation

SqlValidator is the safety gate. It uses JSqlParser to parse the statement into an AST, then applies policy:

• Shape: one statement, SELECT only.
• Resolution: resolve the statement into leaf PlainSelects, including UNION/INTERSECT/EXCEPT components and parenthesized bodies. Unknown shapes fail closed.
• Limit: a LIMIT must exist on the statement or on at least one component; the executor's 1,000-row cap is the backstop.
• Tables: every referenced table must exist in the snapshot; CTE aliases are exempt because they are defined by the query.
• Columns: every referenced column must exist and must not be hidden.
• Functions: reject pg_sleep, file reads/writes, COPY, LOAD_FILE, INTO OUTFILE, xp_cmdshell, and similar.
• Write shapes: reject SELECT ... INTO, data-modifying CTEs, and other constructs that look like a query but mutate.
• Joins: reject unconstrained cross joins.
• Depth: cap derived-table nesting at 16.

The validator also handles the subtle bypass class: a dangerous function hidden inside a FROM-side subquery. Checking only the top-level select would miss SELECT * FROM (SELECT pg_sleep(5)) q. The leaf-walking design closes that gap.

Hidden columns are enforced twice: the validator rejects SQL that references them, and the executor strips them from result sets. A prompt instruction alone would not be a security control.

5.6 Execution5.6 Execution

Execution is deliberately boring:

• Open a short-lived connection through DriverManager, not a pool. Connections are per data source and used once; the adapter interface allows a pooled implementation later.
• Set the connection read-only.
• Apply a statement timeout (default 30 seconds).
• Cap rows at 1,000.
• Use the engine profile for driver class, URL validation, namespace rules, and catalog queries.
• Normalize every cell to a JSON-friendly scalar.

The adapter is engine-generic; PostgreSQL and MySQL differ only through their EngineProfile and dialect hints. This is what makes "add another engine" a profile-shaped task rather than a rewrite.

The 1,000-row cap protects the database and the JVM. The 200-row display policy is separate; see below.

5.7 Result shaping and the display policy5.7 Result shaping and the display policy

The executor's 1,000-row cap is not the same as a good answer. A 900-row table is technically a result and practically a failure.

ResultSizePolicy sets the user-facing table limit at 200 rows. For each section:

• If the result has 200 rows or fewer, return the rows.
• If it has more, withhold the rows and run a COUNT(*) probe over the same query, with a 15-second timeout.
• Return a TOO_LARGE section with the true count, or ">200 rows" if the count probe fails.

The result is a ResultEnvelope containing sections, each with a title, size state, row count, and optional table. This is why the UI can say "This answer has 4,812 rows — ask for an aggregate or narrow the question" instead of rendering a wall of data.

On the client, chart inference is similarly bounded: up to 500 rows, 30 categories, 4 series, and 8 pie slices. Beyond that, the table is the honest view.

5.8 Grounded narrative5.8 Grounded narrative

AnalysisNarrator is the answer to the "why" promise.

• It receives the question and the primary result, capped at 40 rows and 120 characters per cell.
• It is told that the rows are the only evidence allowed.
• It must explain patterns it can see and say plainly when the data does not determine an answer.
• It never receives the schema, the SQL, or any other data source.
• Failure returns null, and the user keeps the plain result table.

This is not a general-purpose analyst. It is a constrained explanation layer over evidence the user can inspect. That constraint is what makes it defensible.

5.9 Routing and graceful fallbacks5.9 Routing and graceful fallbacks

The QuestionRouter makes one small LLM call and returns DATA_QUERY, QUERY_AND_EXPLAIN, or GENERAL. Its failure policy is to return DATA_QUERY. That is the safe degradation: the normal pipeline either succeeds or reports a friendly error.

The schema fast path is even cheaper. Questions such as "how many tables are there" are answered directly from live schema metadata with no LLM and no generated SQL. This is a small feature with a large perceived-quality effect: the system feels instant for the questions it can answer instantly.

A phrase-based WhyQuestions classifier remains as a fallback for jobs created before the router existed, and as a second signal when the router's route is null.

5.10 Workspace features5.10 Workspace features

Chat. Each question can belong to a durable session. The pipeline records the user message when generation starts and the assistant message when the job completes. Recording is best-effort by contract: an unknown or foreign session id is a logging issue, not a job failure. History is paged, so no surface loads an unbounded conversation.

Dashboard. Pinning a completed job creates a card with a snapshot of the result. A unique constraint on (owner_user_id, job_id) makes re-saving idempotent.

Boards. A board holds up to six widgets, each in one of three modes:

• MANUAL — pick a table and render it,
• SQL — validated SQL,
• QUESTION — a natural-language question.

Each widget is re-run when saved and stores its own result snapshot. A board can mix data sources, which is useful for cross-functional reviews.

Security is not a chapter added at the end. It is the reason the product can exist: the moment an AI system holds database credentials, every boundary matters.

Threat model and controlsThreat model and controls

Identity lifecycleIdentity lifecycle

• Registration creates a PENDING user and emails a six-digit OTP.
• OTPs are hashed with the password encoder before storage; plaintext exists only in the email.
• Verification activates the account.
• Login returns a 15-minute access token and a rotating refresh token.
• Refresh consumes the old token and returns a new pair. Reuse of a consumed token fails.
• Logout revokes the refresh token.
• OAuth accounts auto-link only when the provider's verified email matches an existing user.
• OAuth tokens are returned in the URL fragment, copied to storage by the callback page, and never exposed to a server request.

The frontend treats token refresh as a distributed state machine, not a fetch wrapper. That story is worth its own case study later.

Credential handlingCredential handling

The datasource module is the only place plaintext credentials exist. It encrypts them before persistence and returns non-secret views everywhere else. The reveal flow exists because users legitimately need to rotate or copy a connection string; it is protected by:

• the account password,
• a per-user rate limit shared with credential updates,
• a live connection test before any update is persisted,
• a warning when the username looks privileged,
• explicit rejection for sandbox sources that have no credentials.

The error messages deliberately include only the host, never the full JDBC URL, because URLs can embed passwords.

What the security model does not claimWhat the security model does not claim

• It does not claim SOC 2, HIPAA, or GDPR certification.
• It does not redact PII from result rows; it gives users a way to exclude columns from AI-generated SQL and relies on read-only database permissions for the rest.
• It does not provide row-level security inside the target database; that must be configured by the database owner.
• It does not yet have organization-level roles, SSO, or audit export.
• It does not guarantee that a syntactically valid query is semantically correct; it guarantees that the query is read-only, bounded, schema-consistent, and persisted for audit on data queries.

Those are honest boundaries, and they belong in a portfolio document because they show where the engineering ends and the product/compliance work begins.

Design principlesDesign principles

1. Correctness over fluency. A useful "I don't know" beats a confident wrong answer.
2. Bounded work. Every LLM call, query, upload, and result has a limit.
3. Explicit contracts. QueryPlan, ResultEnvelope, JobStatus, JobEvent, and the GraphQL schema are named, versionable shapes.
4. Fail closed at security, fail open at enrichment. Validation and authorization never degrade; RAG, chat recording, and narration do.
5. Durable state. The job row is the truth; events and subscriptions are delivery mechanisms.
6. Least privilege everywhere. Read-only database connections, SELECT-only SQL, hidden columns, owner-scoped queries, filtered discovery.
7. Observable by default. Every job has a status, timestamps, attempt counts, an error message, and a route.

Why the async pipeline is shaped this wayWhy the async pipeline is shaped this way

A synchronous request would have to hold an HTTP thread for the duration of an LLM call, a validation pass, one or more database queries, and possibly a second LLM call. That is a recipe for timeouts, retries that duplicate work, and a UI that cannot show progress.

The pipeline instead:

• returns a jobId immediately,
• persists every transition,
• uses Kafka to decouple stages,
• streams progress over a subscription,
• and lets the browser reconnect and recover the current state from the job query.

The stage boundaries are also fault-isolation boundaries. A generation failure does not re-run execution; an analysis failure does not invalidate a completed result.

Consistency and delivery semanticsConsistency and delivery semantics

Kafka gives at-least-once delivery, not exactly-once. The current design reduces the blast radius by re-reading the job before acting and by keying each event to the job id, but it does not yet have a deduplication table or a transactional outbox; a redelivered stage event can re-run that stage.

The honest assessment:

• Dual-write risk: saving the job and publishing the event are separate operations. A crash between them can leave a job stuck in a non-terminal state.
• Duplicate delivery: a redelivered stage event can re-run a query.
• Ordering: events for one job are keyed by jobId, so a single partition preserves order, but consumer restarts and retries still need idempotency.

The production-grade fix is a transactional outbox: write the event to an outbox table in the same transaction as the job state, then relay it to Kafka. Combined with a (job_id, stage, attempt) deduplication key, this would make the pipeline effectively exactly-once in behavior.

Scaling modelScaling model

Failure modesFailure modes

ObservabilityObservability

The system exposes health, info, metrics, and Prometheus endpoints. Every stage logs the job id and transition. The job row carries status, attempt, route, timestamps, and error message, which makes it possible to reconstruct a question's lifecycle without a separate tracing system.

What is missing:

• OpenTelemetry traces spanning HTTP → Kafka → LLM → JDBC,
• per-stage latency histograms,
• LLM token and cost metrics per tenant,
• query failure classification,
• alerting on DLQ depth and stuck jobs.

These are not nice-to-haves at scale; they are how you know whether the product is healthy.

Testing strategyTesting strategy

The test suite is risk-based rather than coverage theater:

• Cross-engine execution: Testcontainers runs PostgreSQL and MySQL, proving the adapter is not accidentally Postgres-only.
• Pipeline behavior: consumer unit tests cover stage transitions, clarification, validation failure, duplicate-section removal, and the oversized-result policy.
• Security gates: SSRF, SQL validation, credential encryption, hidden columns, auth flows.
• Structure: the Modulith test fails on illegal cross-module dependencies.
• Frontend: TypeScript compilation and production builds; there is no automated frontend test suite yet, so the browser flows are verified manually.

286 backend test methods across 50 backend test classes is a meaningful signal for a project of this size. The suite mixes real-infrastructure integration tests with focused unit tests, and the integration tests are the ones that catch environment-specific failures.

What I would do next at 10xWhat I would do next at 10x

1. Transactional outbox + stage idempotency. Close the dual-write and duplicate-delivery gaps.
2. Multi-node subscriptions. Fan out job events through Kafka or Redis so any replica can serve any WebSocket.
3. Per-tenant quotas and cost budgets. Make LLM spend a first-class, observable control.
4. Connection pooling per data source. Protect target databases from connection storms.
5. Real embeddings. Swap the lexical model for ONNX or a hosted embedding model behind the existing interface.
6. Result retention and archival. Keep PostgreSQL lean as history grows.
7. Audit trail. Who asked what, which SQL ran, which data source, and what was returned.
8. Organization and roles. Move from a single owner to teams, roles, and SSO.
9. A semantic layer. Let users define metrics and join paths so repeated business logic is consistent.
10. True repair loop. Re-prompt with validator feedback, capped and measured.

A portfolio document that only lists strengths is a marketing document. These are the real gaps.

EngineeringEngineering

ProductProduct

BusinessBusiness

The roadmap is deliberately ordered by risk reduction, not by feature count: correctness and delivery guarantees first, then scale and observability, then semantic depth, then enterprise readiness.

This is the part of the project that matters most for a portfolio, because it is where the system stops being a diagram and starts being engineering.

8.1 The model tried to call tools that did not exist8.1 The model tried to call tools that did not exist

Symptom. Generation failed with an error that the model attempted to call a tool named json which was not in the request.

Root cause. gpt-oss models on Groq can hallucinate tool calls when tools are present, even when the prompt already contains everything needed. The generation call had tool callbacks wired because the framework supports them, not because the task required them.

Fix. The production generation path deliberately omits toolCallbacks. The prompt already carries the full schema and the output contract; the model does not need to inspect anything at runtime. The tool class remains in the codebase for future interactive flows, but it is not attached to the hot path.

Lesson. Every capability you expose to a model is also a new failure mode. A tool-free prompt with a strict JSON contract is more reliable than a tool-enabled agent for a bounded task.

8.2 A valid SELECT crashed the next stage

Symptom. Some generated queries passed the initial check but caused a ClassCastException later in validation.

Root cause. The validator assumed every Select resolved to a PlainSelect. JSqlParser can produce other body shapes, especially with parenthesized or compound queries. The code cast first and asked questions later.

Fix. The validator now resolves the statement into leaf PlainSelects, handles simple and compound selects, peels parentheses, and fails closed on any shape it cannot resolve. It also walks derived tables and caps nesting depth at 16.

Lesson. A parser gives you an AST, not a policy. The policy layer must define what it accepts and reject everything else explicitly. "I didn't expect that shape" is not a security posture.

8.3 Hosted PostgreSQL exposed tables the user could not read8.3 Hosted PostgreSQL exposed tables the user could not read

Symptom. Schema introspection on hosted Postgres showed tables from other tenants, or missed tables the user actually needed.

Root cause. Generic JDBC metadata and a naive search_path assumption do not reflect the privileges of the connecting principal. Managed providers routinely expose multiple schemas; some are visible in the catalog but unreadable by the user.

Fix. A PostgreSQL-specific profile enumerates every non-system schema and filters tables with has_table_privilege(..., 'SELECT'). It also handles multi-schema name qualification so two tables with the same name are distinguishable in the prompt and validator.

Lesson. Discovery must reflect the same least-privilege boundary as execution. Showing a table the user cannot read is both a UX bug and an information leak.

8.4 The UI printed [object Object]

Symptom. Some result cells rendered as [object Object] instead of values.

Root cause. PostgreSQL's PGobject wraps citext, jsonb, and inet; java.sql.Array wraps array columns. Jackson serialized the wrappers as objects, and the frontend stringified them.

Fix. JdbcAdapter.normalizeCell converts driver wrappers into JSON-friendly scalars: PGobject.getValue(), recursively normalized arrays, passthrough for strings, numbers, booleans, UUIDs, and temporal values, and a string fallback for anything unknown.

Lesson. Every boundary needs a canonical type. "The driver returns an Object" is not a data contract.

8.5 Kafka consumers could not read their own messages8.5 Kafka consumers could not read their own messages

Symptom. Consumers failed with Cannot deserialize String from START_OBJECT.

Root cause. Spring Boot's auto-configuration installed a JacksonJsonMessageConverter on the listener container. The producer wrote JSON strings; the consumer tried to type-map them into records.

Fix. An explicit ConcurrentKafkaListenerContainerFactory<String, String> with StringDeserializer and the bean name that @KafkaListener looks up by default. The JSON parsing happens explicitly in the consumer with the shared ObjectMapper.

Lesson. Framework auto-configuration is a hypothesis about your system, not a guarantee. Own the serialization boundary and test it with a real broker.

8.6 Subscriptions worked in tests and failed in the browser8.6 Subscriptions worked in tests and failed in the browser

Symptom. GraphQL WebSocket subscriptions were rejected with 403 from a browser, while header-less Node probes passed.

Root cause. The WebSocket handshake runs through an OriginHandshakeInterceptor, separate from the HTTP CORS mapping. Without an explicit GraphQL CORS allowlist, the handshake rejected any browser Origin and fell back to same-origin behavior.

Fix. spring.graphql.cors.allowed-origins feeds both the HTTP CORS mapping and the WebSocket handshake. The frontend origin is configured explicitly.

Lesson. Test the transport the user actually uses. A passing integration test with no Origin header does not prove the browser path works.

8.7 An uploaded zip could take down the server8.7 An uploaded zip could take down the server

Symptom. A schema upload path accepted arbitrarily large archives and entries.

Root cause. The parser trusted the archive: no total size limit, no entry count limit, no per-entry limit.

Fix. The zip walker enforces 50 MB total, 2,000 entries, and 5 MB per entry. SQL uploads are parsed first and loaded into an isolated sandbox_<12hex> schema, with only CREATE TABLE, CREATE INDEX, and INSERT allowed. The load runs in one transaction, so a failure leaves no residue.

Lesson. Uploaded schema is executable input. Treat it like hostile code, not like a configuration file.

8.8 A JDBC URL is an SSRF payload8.8 A JDBC URL is an SSRF payload

Symptom. A user could point a data source at an internal host, including the cloud metadata address.

Root cause. The URL is user-supplied and the backend resolves it from inside the network.

Fix. SsrfGuard resolves the host and rejects loopback, site-local, link-local (which covers 169.254.169.254), and multicast addresses in cloud mode. An explicit allowlist supports local development. Error messages include only the host, never the URL, because URLs can embed passwords.

Lesson. Connection strings are attack surface. Every outbound network primitive that accepts user input needs the same scrutiny as an HTTP proxy.

8.9 The model vendor had no embeddings endpoint8.9 The model vendor had no embeddings endpoint

Symptom. The RAG design assumed an embeddings API, but Groq does not expose one.

Root cause. Vendor capability gap.

Fix. A local 512-dimensional lexical embedding model using feature hashing, TF weights, and character trigrams, wired into pgvector through Spring AI's EmbeddingModel interface. It is instant, free, deterministic, and has no network dependency.

Lesson. Abstract the capability, not the vendor. The retrieval quality is lower than a real semantic model, but the product ships, and the replacement is a bean swap.

8.10 Users were logged out every fifteen minutes8.10 Users were logged out every fifteen minutes

Symptom. Sessions died shortly after login; multiple GraphQL requests could race to refresh; a transient backend outage could clear the session.

Root cause. A 15-minute access token with independent refresh calls from HTTP and WebSocket links. Each caller could consume the rotating refresh token, invalidating the others.

Fix. A single-flight refresh promise shared by all callers, proactive renewal 90 seconds before expiry, a single "session expired" exit, and a rule that a network/backend failure must not clear tokens. Only a definitive auth failure logs the user out.

Lesson. Authentication in a browser is a distributed state machine. The token refresh path needs the same concurrency thinking as a backend transaction.

8.11 The model returned a blank primary query and duplicate sections8.11 The model returned a blank primary query and duplicate sections

Symptom. Some multi-view answers rendered the same data twice, or failed because the primary query was empty.

Root cause. The model sometimes put every query in sections and left queryText blank; other times it re-listed the primary as its first section.

Fix. At generation, a blank primary promotes the first section. At execution, sections identical to the primary are dropped, and the primary always runs first. Sections are capped at three. Malformed sections are discarded rather than failing the plan.

Lesson. Treat model output as untrusted input and repair it locally. A tolerant boundary is better than a brittle prompt that insists on perfection.

8.12 "Why" questions cannot be answered by SQL alone8.12 "Why" questions cannot be answered by SQL alone

Symptom. A fluent narrative that was not grounded in the data, or a result table with no explanation.

Root cause. Conflating evidence gathering with interpretation.

Fix. The router sends explanatory questions to QUERY_AND_EXPLAIN. The generation prompt asks for evidence rows, not conclusions. The narrator receives only those rows, capped at 40 rows and 120 characters per cell, and is instructed to admit when the data does not determine a cause.

Lesson. Separate the system that finds evidence from the system that interprets it. The separation is what makes the explanation auditable.

8.13 The "self-correction" loop was not really correcting8.13 The "self-correction" loop was not really correcting

Symptom. A validation retry path appended an error comment to the existing SQL and tried again.

Root cause. It was a shortcut: re-prompting the model with validator feedback would cost another LLM call and a more complex state machine. Appending a comment looks like repair but does not change the query.

Fix (current state). The production Kafka pipeline does not pretend. It validates once per section and fails the job with a clear error. The flawed loop remains only in the older synchronous compatibility path.

Fix (next). A real repair loop would re-prompt with the validator's exact errors, cap attempts at two, include the failed SQL and schema context, and record the attempt in the job for observability.

Lesson. Do not ship a retry that only looks like one. Either repair the output or fail honestly; a fake retry burns time and hides defects.

8.14 Large results are not answers8.14 Large results are not answers

Symptom. A query returning thousands of rows produced an unusable table and unnecessary memory pressure.

Root cause. One limit was doing two jobs: protecting the database and shaping the user experience.

Fix. Two limits. The executor caps at 1,000 rows and 30 seconds to protect the database. The result policy caps display at 200 rows, withholds oversized sections, and probes for the true count with a 15-second timeout.

Lesson. Database safety and product quality are different budgets. Design both explicitly.

One-paragraph descriptionOne-paragraph description

I built WhyQL, a full-stack AI-native analytics platform that turns natural-language questions into validated, read-only SQL against a user's PostgreSQL or MySQL database. The backend is a 15-module Spring Modulith with a Kafka-driven, four-stage job pipeline, a JSqlParser-based SQL safety gate, pgvector schema memory and exemplars, AES-GCM credential encryption, SSRF protection, and GraphQL subscriptions for live progress. The frontend is a Next.js 16 / React 19 workspace with Apollo, ECharts, chat history, dashboards, and six-widget boards. The system is designed around a simple thesis: the model proposes, the system disposes. It treats LLM output as untrusted input, separates evidence from explanation, and degrades enrichment gracefully while failing closed on security.

Resume bulletsResume bullets

• Designed and built WhyQL, a full-stack natural-language analytics platform (Java 21, Spring Boot 4, Spring Modulith, Spring AI, Kafka, PostgreSQL/pgvector, Next.js 16, React 19), spanning 192 backend Java files and 24 frontend TypeScript files.
• Implemented a four-stage Kafka pipeline (query.requested → query.generated → query.executed → analysis.requested) with symmetric DLQs, durable job state, GraphQL WebSocket progress, and a queue-based architecture that keeps LLM latency off HTTP threads.
• Built a defense-in-depth SQL safety layer using JSqlParser: single-statement SELECT enforcement, table/column allowlists, hidden-column checks, LIMIT requirements, dangerous-function blocking, write-shape rejection, subquery-depth limits, and fail-closed handling of unknown AST shapes.
• Added schema-grounded RAG over pgvector with per-table/per-column documents, validated question-to-SQL exemplars, and a local 512-dimensional lexical embedding model built to work around the LLM provider's missing embeddings endpoint.
• Hardened the platform with AES-GCM-256 credential encryption, account-password-gated credential reveal, SSRF protection for user-supplied JDBC URLs, zip-bomb limits, sandboxed DDL loading, owner-scoped resolvers, OTP rate limits, and 15-minute JWTs with rotating refresh tokens.
• Built a Next.js 16 / Apollo workspace with single-flight token refresh, live job-stage subscriptions, resilient WebSocket auth, multi-section result rendering, ECharts inference, chat history, dashboards, and boards with manual/SQL/question widget modes.
• Wrote 286 backend tests across 50 classes, including Testcontainers-backed PostgreSQL, MySQL, and Kafka test infrastructure, plus Modulith boundary tests and security-focused unit tests.

Interview talking pointsInterview talking points

1. "Why not just ask the model for SQL?" Because SQL is executable. A plan is structured, validatable, persistable, and repairable. The plan is the contract.
2. "How do you make LLM output safe?" You do not trust it. You constrain the output shape, parse it, validate it against a schema and a policy, execute it read-only with limits, and never feed result rows back into the prompt.
3. "Why Kafka for a small product?" Because LLM work is slow, retryable, and bursty. The pipeline gives durable stage handoff, DLQs, and independent scaling without splitting the whole application into microservices.
4. "What was the hardest bug?" Either the hosted-Postgres introspection privilege mismatch or the browser token-refresh race. Both were cases where the happy path passed and the real environment failed.
5. "What would you change?" Add a transactional outbox and stage idempotency, make the subscription hub multi-node, replace lexical embeddings with a real model, and build a true validator-feedback repair loop.
6. "What is the business?" A self-service question layer for the long tail of analytics questions, sold to teams with PostgreSQL/MySQL and no dedicated data team. The moat is trust and workflow, not the model.

What this project demonstratesWhat this project demonstrates

• System design: bounded contexts, event-driven stages, durable state, failure isolation, graceful degradation, explicit trade-offs.
• Security engineering: least privilege, encryption, SSRF, injection defense, upload hardening, token lifecycle.
• AI engineering: prompt contracts, structured output, RAG, grounding, cost and latency control, vendor-gap abstraction.
• Product engineering: async UX, live progress, result-size policy, clarification, charts, boards, chat persistence.
• Business thinking: ICP, differentiation, pricing, unit economics, GTM, risks, and metrics.

The decisions that mattered mostThe decisions that mattered most

1. Structured plan instead of raw SQL. It made validation, repair, multi-section answers, assumptions, and clarification possible in one contract.
2. Async job pipeline instead of a blocking request. LLM latency is measured in seconds; HTTP timeouts, retries, and user patience are not.
3. Kafka only at the seams. The system gets durability and stage isolation without paying for a microservice per module.
4. Read-only by construction. The database connection is read-only, the SQL gate rejects writes, and the adapter refuses non-SELECT statements. Three layers, not one prompt.
5. Schema memory instead of fine-tuning. Per-tenant retrieval and exemplars improve answers without training a model or moving data.
6. Evidence-first explanations. The narrator sees rows, not the schema, and is told to admit uncertainty.