Never Let the LLM Write the Joins

Key Takeaways

• This article details a two-phase text-to-SQL pipeline: Phase A deterministically plans (intent, entity resolution, joins, RBAC) and Phase B executes with bounded LLM calls.
• The subject graph caches entity mappings lazily, and security is enforced before the model sees any schema.

The Innovation — What the Source Reports

This article is Part 3 of a series on building a conversational analytics engine. It introduces a two-phase architecture that radically separates planning from execution to solve the seven walls that break text-to-SQL on real data. The key insight: never let the LLM write the joins.

Phase A: Deterministic Planning

Phase A is entirely deterministic except for a single LLM call for intent classification. It takes a user question like "Show orders for customer Bike World" and produces a frozen plan — no SQL exists yet. The stages:

1. User context assembly: Loads the user's role, territory, permissions, and vocabulary.
2. Intent classification: One LLM call returns a structured verdict (intent type, entity types, confidence).
3. Semantic resolution: A 2-pass vector search maps natural language terms to database entities by meaning.
4. Scope resolution: Turns phrases like "my team" into row-filter hints using pre-derived rules.
5. Routing: Maps entity types to real tables, assigns roles, finds join paths via the domain graph, and resolves entities.
6. RBAC Checkpoint 1: Drops any table the user may not see before the model ever sees the schema.

The Subject Graph

The subject graph resolves specific instances (e.g., "Bike World" → StoreID 620). It is a cache, not a copy — it starts empty and fills lazily as users ask about entities. Resolution runs a cascade: cache → exact match → fuzzy match → semantic match (placeholder) → source-DB lookup. Ambiguity returns a clarification question, not a coin flip.

Phase B: Bounded LLM Execution

Phase B is a small state machine with three nodes: agent (LLM reasons), tools (executes exactly one tool), and synthesize (writes final answer). It loops at most 10 reasoning iterations and 15 tool calls with a 30-second timeout. The LLM writes only the SELECT and WHERE clauses — joins, security, and cardinality are all handled by deterministic code.

Security Model

Two RBAC checkpoints exist: one in Phase A (drops tables before the model sees them) and one in Phase B (injects row-level security by rewriting SQL). The author states: "Security is never the model's job."

Honest Limitations

The conversation is currently single-shot — follow-up memory is built but not wired. The semantic match rung in the entity resolution cascade is a stub (placeholder).

Why This Matters for Retail & Luxury

Retail and luxury companies operate on complex, multi-source databases: CRM, POS, inventory, e-commerce, loyalty, and supply chain. A sales rep asking "How many orders did Bike World place last quarter in my territory?" is a real, daily request. This architecture directly addresses the failure modes that make most text-to-SQL systems unusable in production:

• Security: In retail, data access is granular — a regional manager should not see national margins, and a store associate should not see HR data. The dual RBAC gates ensure this without relying on the LLM's judgment.
• Join complexity: Retail schemas often have 10+ tables with non-obvious join paths (e.g., linking customer orders to inventory via product SKUs). The domain graph handles this deterministically.
• Entity ambiguity: "Bike World" could be a store name, a vendor, or a customer segment. The subject graph's cascade resolves this with confidence scores, returning "Did you mean the Seattle or Dallas store?" when ambiguous.

Business Impact

For a luxury retailer like Kering or Richemont, this architecture could:

• Reduce time-to-insight for regional sales managers from hours (waiting for BI reports) to seconds (direct conversational queries).
• Eliminate data leaks from LLM hallucinations on join paths or security rules.
• Enable self-service analytics for non-technical users (store managers, merchandisers) without requiring a data engineer to build every query.

The author does not provide quantified business metrics, but the design directly addresses the top failure modes reported by enterprises deploying text-to-SQL.

Implementation Approach

• Technical requirements: A domain graph (schema introspection + enrichment), a subject graph (entity cache), and a state machine for Phase B. Requires a vector database, an in-memory cache, and an LLM (any model that can follow structured intent classification prompts).
• Complexity: Medium-high. Building the domain graph and entity resolution cascade is non-trivial but well-scoped. The two-phase pipeline is modular and testable.
• Effort: Likely 2-3 months for a team of 2-3 engineers to adapt to an existing retail database, assuming schema documentation exists.

Governance & Risk Assessment

• Privacy: The subject graph caches only entity names and IDs, not row data. This is a strong privacy design — no customer PII is copied out of the source database.
• Bias: The deterministic planning phase reduces bias risk compared to end-to-end LLM approaches, since join paths and security rules are hard-coded. However, the single LLM call for intent classification could still reflect model bias.
• Maturity: The architecture is well-designed but the author notes it is single-shot (no multi-turn memory yet). It is production-ready for single-query analytics but not yet for conversational follow-ups.

Source: pub.towardsai.net