11 · System Design Round

The likely prompt: “Design a system that lets users query their databases in natural language.” You have built one, which is an advantage — but only if you drive the conversation rather than describing your implementation.

Drive it in this order

1. Requirements — functional, then non-functional, then scale. Never skip.
2. API contract — what a client sends and receives.
3. High-level components — boxes and arrows.
4. The critical path — walk one request end to end.
5. Deep dive — let them pick, but have opinions ready on retrieval, safety and cost.
6. Scale — bottleneck by bottleneck, with numbers.
7. Failure modes — what breaks, what the user sees.

1 · Requirements

Functional

• Connect a database (multiple engines)
• Ask a question in natural language
• Get an answer, the SQL, the rows, a chart
• Stream the answer
• Multi-tenant

Non-functional

• p95 under 5 s for an uncached query
• Never mutate the user’s data
• Tenant isolation
• Cost per query bounded
• Graceful degradation when the model provider is down

Questions to ask them — asking these is the signal:

• Read-only, or should we support writes? (Assume read-only; it removes an enormous class of risk.)
• How large are the schemas? (Changes retrieval from “paste it” to “retrieve it”.)
• Is data allowed to leave their network? (Decides cloud vs on-premise models.)
• What accuracy is acceptable, and what happens when we’re wrong?

2 · API contract

POST /v1/sources            register a database
POST /v1/sources/{id}/sync  discover schema  → job id
POST /v1/query              ask a question
POST /v1/query/stream       ask, streamed (SSE)
GET  /v1/jobs/{id}          poll background work

Say why sync is a job: schema discovery on a large warehouse takes minutes, so it cannot be a request.

3 · Components

   Client
     │
  [API Gateway] ── rate limit (per key), auth (bcrypt-hashed keys)
     │
  [Query Service] ── authorise → pipeline
     │
     ├─ [Cache]        Redis, keyed on (source_id, normalised question)
     ├─ [Retrieval]    schema store → select ~8 tables → compress
     ├─ [LLM Gateway]  provider abstraction, retries, quota classification
     ├─ [Validator]    sqlglot AST, read-only allow-list
     └─ [Executor]     user's DB, row cap + timeout
     │
  [Job Queue] ── schema discovery, insights, forecasts
     │
  [Metadata DB] Postgres: keys, sources, snapshots, jobs, query logs

4 · The critical path

Walk it with latencies — numbers make it concrete:

cache lookup            ~5 ms    → hit? return (~10 ms total)
schema retrieval       ~10 ms    from stored snapshot, no DB round trip
SQL generation       ~1500 ms    ← dominant cost
validation              ~5 ms    local AST work
execution             ~200 ms    on the user's DB
answer generation     ~800 ms    ← second model call
chart                   ~1 ms    deterministic
                     ─────────
                      ~2500 ms

Two observations that show you understand the system:

• Cost and latency are ~90% the two model calls. Every optimisation targets those.
• Cache hit is 250× faster. So cache design matters more than any micro-optimisation.

5 · Deep dives to have ready

Retrieval

The context window is a budget. 400 tables do not fit, and irrelevant tables measurably hurt accuracy even when they do.

• Keyword scoring — cheap, no embedding call, fails on vocabulary mismatch.
• Embeddings — handles paraphrase; needs an embedding per table and a vector store.
• Hybrid — keyword for exact matches, embeddings for paraphrase, union, then follow foreign keys.

Always mention FK traversal: orders and products score, but order_items is what joins them and no keyword surfaces it.

Safety

Layer it explicitly:

1. Application validation — parse, allow-list root shape, walk the AST for write nodes.
2. A read-only database user — enforced by the database, fails closed.
3. Row caps and statement timeouts — bound the blast radius of an expensive query.
4. Per-tenant credentials, encrypted at rest.

The strongest version of this answer names the read-only user as the primary control and the validator as defence in depth — not the other way round.

Cost

• Cache aggressively; consider semantic near-miss matching.
• Route by difficulty: small model for simple aggregations, escalate on validation failure.
• Keep deterministic work deterministic — charts, intent.
• Compress the schema.
• Cap rows into the answer prompt.

6 · Scaling to 10,000 tenants

Handle bottlenecks in order, with numbers.

Assume

• 10,000 tenants, 100 queries/day each → 1M queries/day ≈ 12 QPS average, ~50 QPS peak
• 40% cache hit rate → ~600k model-backed queries/day
• Two model calls each → 1.2M model calls/day ≈ 14/s sustained

Bottleneck 1 — the model provider

This is what breaks first, long before your API does.

• Provider rate limits are the hard ceiling. Get quota raised, then shard across providers and keys.
• Route by difficulty; most queries are simple aggregations that a small model handles.
• Raise the cache hit rate — the cheapest call is the one you skip.
• Queue and shed load: a per-tenant concurrency cap prevents one tenant starving the rest.

Bottleneck 2 — API compute

Stateless, so scale horizontally behind a load balancer. Requests are I/O-bound, so async gives high concurrency per instance. Watch connection count: each in-flight request holds a Postgres connection while waiting on a model call — that is what a row cap and timeout protect.

Bottleneck 3 — the metadata database

• Read replicas for the read-heavy paths (source and key lookups).
• query_logs is the growth problem — 1M rows/day. Partition by month, move to columnar storage or object storage for analytics, and set a retention policy.
• Cache key verification: bcrypt is deliberately slow, so verifying on every request is costly. Cache the verified key ID in Redis with a short TTL.

Bottleneck 4 — cache

• Redis Cluster, sharded by source_id so a tenant’s entries stay together.
• Memory bound: cap cached payload size; large result sets go to object storage with a pointer in Redis.
• allkeys-lru eviction, since every entry is recomputable.

Bottleneck 5 — the user’s database

You are running arbitrary generated SQL against production databases you do not control.

• Per-tenant connection pools with hard caps, so one tenant cannot exhaust their own database.
• Statement timeouts, always.
• EXPLAIN before execution to reject plans above a cost threshold.
• Ideally, query a read replica.

Multi-tenancy at scale

• Shared database with owner_key_id filtering is fine to a point.
• Beyond that: shard by tenant, or give large customers dedicated instances.
• Noisy-neighbour control: per-tenant rate limits and concurrency caps, not just global ones.

7 · Failure modes

What “good” sounds like

• Ask about accuracy expectations. Most candidates don’t, and it reframes the product.
• Name the two model calls as the cost centre, then optimise around them.
• Treat generated SQL as untrusted, and name the read-only user as the real control.
• Say what happens when each thing fails, not just the happy path.
• Quantify. “1.2M model calls a day at 14/s” beats “it scales”.
• Volunteer trade-offs. “Caching for seven days makes stale answers possible; that’s right for a demo and wrong for live inventory.”

Extensions they may push toward

“What if the user wants to write data?” Different product. Needs confirmation flows, transactions, an audit log and a rollback story. I’d keep read-only as a separate, safer tier.

“Multi-turn conversation?” Needs conversation state, references to prior results (“now break that down by region”), and question rewriting — turning a follow-up into a standalone question before retrieval.

“How do you handle ambiguity?” Detect low retrieval confidence or multiple plausible interpretations and ask a clarifying question instead of guessing. Requires a confidence signal, which keyword scoring gives only crudely — another argument for embeddings.

“Can you explain why an answer is right?” Show the SQL (already done), show row counts, and link back to source tables. Provenance is the trust mechanism.