LLM SQL Guard Architecture: Parser, Catalog, Policy Engine, Audit Log

Length: About 4,000 words · Reading time: about 18–22 minutes

An LLM SQL Guard architecture is a deterministic safety layer between a Text-to-SQL model and the database. It checks generated SQL before execution by parsing the query, resolving tables and columns against catalog metadata, applying user and field-level policies, scoring query risk, returning an allow, warn, deny, or repair decision, and recording an audit log.

This architecture matters because production Text-to-SQL systems cannot rely on prompts alone. A model can generate SQL that is syntactically valid but semantically wrong, too expensive, non-compliant, or unauthorized for the requesting user. The guard is the layer that turns generated SQL from “plausible text” into a governed database operation.

Short Answer

A production LLM SQL Guard usually has seven core parts:

1. SQL parser — turns generated SQL text into a structured representation.
2. Catalog binding — resolves table names, column names, aliases, CTEs, functions, and dialect-specific syntax against real metadata.
3. Policy engine — checks user, role, table, field, row, purpose, and environment rules.
4. Sensitive-field and lineage analysis — detects direct and derived use of restricted fields.
5. Risk scoring — estimates the operational and compliance risk of running the query.
6. Decision and repair loop — returns allow, warn, deny, or repair with structured feedback.
7. Audit log — records the prompt, generated SQL, decision, policy hits, metadata context, and execution outcome.

In practice, the guard should run synchronously before database execution. If it cannot understand the SQL, it should fail safely with a clear diagnostic instead of silently allowing the query.

Key Takeaways

• Text-to-SQL security needs a deterministic control layer, not only prompt instructions.
• A SQL parser is necessary but not sufficient; production validation also needs catalog binding, permissions, sensitive-field metadata, lineage, risk scoring, and audit output.
• The guard should make explicit decisions: allow, warn, deny, or repair.
• Catalog-aware validation catches problems that syntax checks miss, including hallucinated columns, ambiguous references, wrong joins, and unsupported dialect features.
• Field-level permissions are essential because sensitive fields can appear in projections, filters, joins, aggregations, derived columns, and downstream lineage.
• Audit logs turn Text-to-SQL from an opaque model action into a reviewable enterprise workflow.

Why Architecture Matters for Text-to-SQL Security

Many teams start Text-to-SQL with a simple pattern:

User question → LLM → generated SQL → database

That path is useful for a demo, but risky for production. The database sees only the final SQL. It does not know whether the query came from a user, a model, an agent, a dashboard, or a scheduled workflow. It also does not know whether the model misunderstood the request, invented a column, selected restricted data, or created a query that is too expensive for an interactive session.

A safer architecture inserts a guard before execution:

User question
  ↓
LLM generates SQL
  ↓
LLM SQL Guard
  ├─ parse SQL
  ├─ resolve catalog metadata
  ├─ validate tables and columns
  ├─ check permissions and sensitive fields
  ├─ inspect lineage and dependency roles
  ├─ estimate risk and cost
  ├─ return allow / warn / deny / repair
  └─ write audit log
  ↓
Database execution, repair loop, approval, or rejection

The goal is not to make the model perfect. The goal is to ensure the system never treats generated SQL as trusted just because it looks reasonable.

Reference Architecture

A practical LLM SQL Guard can be implemented as an API service, library, middleware component, or gateway in front of SQL execution. The exact deployment model varies, but the logical architecture is similar.

This design separates concerns. The parser should not be responsible for user permissions. The policy engine should not parse SQL using string matching. The audit layer should not infer meaning after the fact. Each layer should receive structured facts from the previous layer and produce explicit output for the next layer.

Component 1: Request Context

The guard needs more than SQL text. The same query can be acceptable for one user and blocked for another. A finance analyst may be allowed to query revenue by region. A customer support agent may be allowed to view a customer record but not export all customer emails. A developer may run broader queries in staging but not in production.

A good request envelope includes:

{
  "request_id": "req_2026_05_03_001",
  "user": {
    "id": "u_12345",
    "roles": ["sales_ops_analyst"],
    "department": "sales_operations"
  },
  "purpose": "interactive_chatbi",
  "environment": "production_readonly",
  "dialect": "postgresql",
  "natural_language_request": "Show quarterly pipeline by region for this year.",
  "generated_sql": "SELECT region, DATE_TRUNC('quarter', close_date) AS quarter, SUM(amount) AS pipeline FROM opportunities GROUP BY region, DATE_TRUNC('quarter', close_date);"
}

This context lets the guard answer questions that SQL alone cannot answer:

• Who is requesting the data?
• Is this interactive analysis, a scheduled job, or an agent action?
• Is the target environment production or staging?
• Which SQL dialect should be used?
• Should the query be read-only?
• Which data domains and policy rules apply?

Without request context, a guard can only validate the query in isolation. Enterprise Text-to-SQL needs user-aware and purpose-aware validation.

Component 2: SQL Parser

The SQL parser is the first deterministic step. It checks whether the generated text is SQL and converts the query into a structured representation that downstream systems can inspect.

A parser should identify:

• statement type: SELECT, INSERT, UPDATE, DELETE, DDL, procedure call, and so on;
• clauses: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT;
• expressions, functions, aliases, subqueries, CTEs, set operations, and window functions;
• dialect-specific syntax;
• parse errors with locations and messages.

For security, the parser should fail closed on unsupported or dangerous statements. For example, many Text-to-SQL systems should reject or require special approval for:

DROP TABLE customers;
DELETE FROM orders;
UPDATE users SET role = 'admin';
CREATE TABLE temp_export AS SELECT * FROM customer_pii;

However, parsing is only the beginning. A parser can tell that customer_email appears in a query. It cannot, by itself, know whether that column exists, whether it is sensitive, or whether the user is allowed to access it. That is why the next layer is catalog binding.

Component 3: Catalog Binding

Catalog binding connects SQL text to the real database environment. It resolves each table and column reference against metadata.

For example, consider:

SELECT
  c.name,
  o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE '2026-01-01';

A catalog-aware guard should resolve:

This step catches errors a syntax checker cannot catch:

• table does not exist;
• column does not exist;
• unqualified column is ambiguous;
• CTE output column does not match later references;
• function or type is invalid for the chosen dialect;
• generated SQL uses a development schema instead of production schema;
• the model used a plausible metric name that is not in the catalog.

A validation result might look like this:

{
  "semantic_status": "invalid",
  "errors": [
    {
      "code": "UNKNOWN_COLUMN",
      "reference": "customers.lifetime_value",
      "message": "Column lifetime_value does not exist in sales.customers.",
      "repair_hint": "Use customer_metrics.ltv_usd or ask the user to choose a lifetime value metric."
    }
  ]
}

This is especially important for LLM-generated SQL because hallucinated columns often sound correct. The model may generate customer_lifetime_value, is_active_customer, or net_revenue even when the real schema uses different names or requires a join to a metric table.

Component 4: Semantic Validation

Semantic validation goes beyond object existence. It asks whether the SQL meaning is valid and safe for the use case.

Examples of semantic checks include:

• Does every selected column have a clear source?
• Are aliases and scopes resolved correctly across CTEs and subqueries?
• Are join keys plausible for the intended relationship?
• Does an aggregation mix row-level and aggregate fields incorrectly?
• Does the query use required business filters, such as tenant, region, or active status?
• Does the query use dialect-specific functions correctly?
• Is SELECT * prohibited for this environment?
• Does a row limit apply to interactive queries?

Consider this query:

SELECT
  c.region,
  SUM(o.amount) AS revenue
FROM customers c
JOIN orders o ON c.name = o.customer_name
GROUP BY c.region;

The query may parse and the columns may exist. But the join may be semantically risky if the real relationship should use customer_id, not customer name. Depending on metadata, the guard might return a warning:

{
  "decision": "warn",
  "risk_level": "medium",
  "warnings": [
    {
      "code": "NON_KEY_JOIN",
      "message": "The query joins customers to orders using names instead of customer_id.",
      "suggested_join": "customers.customer_id = orders.customer_id"
    }
  ]
}

Semantic validation should be careful not to overclaim. Some checks require business metadata that may not exist yet. The architecture should support a gradual path: start with table and column binding, ambiguity detection, read-only enforcement, restricted statements, and sensitive-field checks; then add richer business rules as metadata improves.

How Natural-Language Intent Maps to Catalog Fields

There is one important boundary: a SQL parser alone does not know that the phrase “active customers” means customers.status = 'active', or that “net revenue” means SUM(orders.amount) - SUM(refunds.amount). Those mappings require a separate semantic layer.

A practical implementation usually combines several techniques:

In other words, an LLM can help interpret the user’s natural-language intent, but it should be treated as an optional upstream assistant, not as a required dependency of the guard. The guard itself can remain deterministic and model-agnostic:

Optional upstream Text-to-SQL / intent layer
  ↓
Generated SQL + optional structured intent
  ↓
SQL Guard Core
  ├─ parse and bind SQL
  ├─ compare SQL facts with catalog / policy / metric definitions
  ├─ produce allow / warn / deny / repair / approval_required
  └─ write audit log

If a deployment has a semantic layer or metric store, the guard can compare the generated SQL against those approved definitions. If a deployment also has an LLM-based intent extractor, the guard can consume its structured intent as input. But the enforcement decision should not depend on the guard calling an LLM internally.

A precise warning such as “Net revenue requires subtracting refunds” should not come from the parser by itself. It should come from an approved metric definition or business glossary entry. The parser and semantic binder only prove what the SQL actually does. The metric layer says what the SQL should have done. The guard compares the two.

Component 5: Policy Engine

The policy engine decides whether the bound SQL is allowed for the requesting user and purpose. It should consume structured SQL facts, not raw strings.

A useful policy model can include:

• statement policy — read-only only, or allow controlled writes in approved workflows;
• table policy — which users or roles can access each table;
• field policy — which columns are restricted, masked, aggregated, or approval-gated;
• row policy — required tenant, region, owner, or department filters;
• purpose policy — different rules for dashboarding, ad hoc analysis, export, model training, or agent actions;
• environment policy — stricter rules for production than staging;
• query-shape policy — no SELECT *, required LIMIT, no Cartesian joins, no high-risk functions.

A simple policy rule might say:

Users with role sales_ops_analyst may query opportunities.amount and opportunities.region,
but may not query customers.email, customers.phone, or customers.ssn unless the purpose is approved_customer_support_case.

If the generated SQL is:

SELECT
  c.name,
  c.email,
  c.phone,
  SUM(o.amount) AS total_spend
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name, c.email, c.phone;

The guard should not only say “this is a valid query.” It should identify policy violations:

{
  "decision": "deny",
  "risk_level": "high",
  "policy_violations": [
    {
      "code": "FIELD_ACCESS_DENIED",
      "field": "customers.email",
      "reason": "Email is labeled PII and is not allowed for role sales_ops_analyst."
    },
    {
      "code": "FIELD_ACCESS_DENIED",
      "field": "customers.phone",
      "reason": "Phone is labeled PII and is not allowed for role sales_ops_analyst."
    }
  ],
  "repair_hint": "Remove direct identifiers or aggregate by non-PII dimensions such as region or customer_segment."
}

This is where Text-to-SQL security becomes an enterprise governance problem. The system must know not just what the query says, but who is asking, why they are asking, and which data they are allowed to use.

Component 6: Sensitive-Field and Lineage Analysis

Sensitive fields are not always visible in the final SELECT list. They can influence a result through filters, joins, derived expressions, aggregates, or intermediate CTEs.

For example:

WITH vip_customers AS (
  SELECT customer_id
  FROM customers
  WHERE annual_income > 250000
)
SELECT
  o.region,
  COUNT(*) AS vip_orders
FROM orders o
JOIN vip_customers v ON o.customer_id = v.customer_id
GROUP BY o.region;

The final output does not show annual_income. But the result depends on it. If customers.annual_income is sensitive, the guard should know that it influenced the result through a filter inside the CTE.

This is why field-level dependency analysis matters. The guard should classify dependency roles such as:

For sensitive data, filter and join dependencies can matter as much as projection dependencies. A system that checks only selected columns may miss indirect disclosure risks.

Component 7: Risk Scoring

Not every issue requires the same response. Some queries should be blocked. Some should be allowed with a warning. Some should be repaired automatically. Some should be routed to human approval.

Risk scoring helps the system make consistent decisions.

A simple scoring model can consider:

• statement type: read-only vs write/DDL;
• sensitive fields: direct or indirect use;
• permission violations;
• unknown tables or columns;
• ambiguous references;
• missing required filters;
• estimated scan size or cost;
• absence of LIMIT for interactive queries;
• cross-domain joins;
• export intent;
• production vs staging environment;
• model confidence or number of repair attempts.

A decision table might look like this:

The risk score should be explainable. A black-box “high risk” label is not enough for enterprise review. The output should include the reasons, affected fields, policy IDs, and repair options.

Component 8: Decision and Repair Loop

A good LLM SQL Guard does not only block queries. It should help the application recover safely when possible.

The decision model can be:

For LLM applications, repair is especially useful. Instead of returning a raw database error, the guard can provide precise feedback:

{
  "decision": "repair",
  "risk_level": "medium",
  "errors": [
    {
      "code": "UNKNOWN_COLUMN",
      "reference": "orders.revenue",
      "message": "orders.revenue does not exist."
    },
    {
      "code": "MISSING_LIMIT",
      "message": "Interactive queries must include LIMIT 1000 or less."
    }
  ],
  "repair_instructions_for_model": [
    "Use orders.amount instead of orders.revenue.",
    "Add LIMIT 1000.",
    "Do not include PII fields."
  ]
}

The application can pass this feedback to the model and request a corrected query. The repaired query should go through the guard again. The system should cap repair attempts to avoid loops.

Component 9: Audit Log

The audit log is what makes Text-to-SQL reviewable. Without it, teams may know that a model ran SQL, but not why a query was allowed, denied, or repaired.

An audit event should capture:

• request ID and timestamp;
• user, role, tenant, and application;
• natural-language request;
• generated SQL;
• SQL dialect and environment;
• parsed tables, columns, and dependency roles;
• policy rules evaluated;
• violations, warnings, and risk score;
• decision and repair instructions;
• final SQL executed, if any;
• database execution status;
• row count or cost metadata when available.

Example:

{
  "event_type": "llm_sql_guard_decision",
  "request_id": "req_2026_05_03_001",
  "user_id": "u_12345",
  "dialect": "postgresql",
  "environment": "production_readonly",
  "decision": "deny",
  "risk_level": "high",
  "tables": ["customers", "orders"],
  "columns": [
    {"name": "customers.email", "role": "projection", "labels": ["pii"]},
    {"name": "orders.amount", "role": "aggregation_input", "labels": []}
  ],
  "policy_violations": ["FIELD_ACCESS_DENIED"],
  "executed": false
}

Audit logs are useful for security reviews, compliance evidence, debugging, product analytics, and improving model prompts. They also help teams understand which guard rules are too strict, too loose, or missing metadata.

End-to-End Example

Suppose a user asks:

Show me the top customers by revenue this quarter, including email, so the sales team can contact them.

The model generates:

SELECT
  c.customer_id,
  c.name,
  c.email,
  SUM(o.amount) AS revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE '2026-04-01'
GROUP BY c.customer_id, c.name, c.email
ORDER BY revenue DESC
LIMIT 50;

The SQL is syntactically valid. It has a reasonable join, aggregation, order, and limit. But the guard should evaluate more than syntax.

A possible result:

{
  "decision": "repair",
  "risk_level": "high",
  "semantic_status": "valid",
  "tables": ["customers", "orders"],
  "column_dependencies": [
    {"column": "customers.customer_id", "role": "projection"},
    {"column": "customers.name", "role": "projection"},
    {"column": "customers.email", "role": "projection", "labels": ["pii"]},
    {"column": "orders.amount", "role": "aggregation_input"},
    {"column": "orders.order_date", "role": "filter"},
    {"column": "orders.customer_id", "role": "join"}
  ],
  "policy_violations": [
    {
      "code": "PII_PROJECTION_NOT_ALLOWED",
      "field": "customers.email",
      "message": "The requesting role may rank customers by revenue but may not return direct contact identifiers."
    }
  ],
  "repair_instructions_for_model": [
    "Remove customers.email from SELECT and GROUP BY.",
    "If outreach is required, return customer_id and route the result to an approved CRM workflow."
  ]
}

The important point is that the guard did not simply reject a valid analytical question. It preserved the business intent while changing the execution path to avoid exposing direct identifiers.

Deployment Patterns

There are several ways to deploy an LLM SQL Guard.

Pattern 1: In-Application Middleware

The application calls the model, receives SQL, sends it to the guard, and executes only if allowed.

ChatBI app → LLM → SQL Guard → database

This pattern is simple and works well for a single application team. The risk is that other applications may bypass the guard unless the organization standardizes the pattern.

Pattern 2: Central SQL Guard Service

Multiple applications call a shared guard API before execution.

ChatBI app
Agent workflow       → SQL Guard API → database or approval path
BI assistant

This pattern is better for enterprise platforms because policies, metadata, audit logs, and repair behavior can be managed centrally.

Pattern 3: Database Proxy or Query Gateway

The guard sits close to the database access layer. It can enforce controls even if different applications generate SQL.

Apps and agents → SQL query gateway → guard decision → database

This provides stronger enforcement but requires more careful engineering around latency, connection handling, supported protocols, and failure modes.

Pattern 4: Offline Review and CI

Teams can also use SQL validation outside the request path, for example in prompt testing, agent evaluation, dbt model review, or pull request checks.

Generated SQL test set → guard validation → regression report

This pattern helps teams improve prompts and policies before production traffic reaches the database.

What to Build First

A complete SQL governance system can be large, but the first useful guard does not need to solve everything. A practical starting scope is:

1. Parse generated SQL for the target dialect.
2. Reject destructive statements for Text-to-SQL flows.
3. Bind tables, columns, aliases, CTEs, and subqueries against catalog metadata.
4. Detect unknown and ambiguous references.
5. Enforce read-only and no-SELECT * rules.
6. Check sensitive field labels in projection, filters, joins, and derived outputs.
7. Require row limits or cost controls for interactive queries.
8. Return structured allow, warn, deny, or repair decisions.
9. Log every decision.

This starting point creates immediate value because it catches the most common failures: hallucinated schema, unsafe statements, PII exposure, ambiguous references, and unbounded queries.

More advanced capabilities can follow:

• row-level policy checks;
• purpose-based access;
• query cost estimation;
• business metric validation;
• human approval workflows;
• lineage export to catalog systems;
• model evaluation and prompt regression tests;
• cross-dialect policy normalization.

Common Questions

Is an LLM SQL Guard the same as a SQL parser?

No. A SQL parser is one component of the guard. The parser understands SQL structure. The guard uses that structure with catalog metadata, user permissions, sensitive-field labels, risk rules, and audit requirements to decide whether the generated SQL should run.

Can prompt engineering replace a SQL Guard?

No. Prompts can guide generation, but they are not enforcement. A model can ignore, misunderstand, or be manipulated around instructions. A SQL Guard applies deterministic checks after SQL is generated and before it reaches the database.

Should the guard run before or after query execution?

The main guard decision should run before execution. Some telemetry, such as row count or actual cost, is available only after execution, but permission, safety, semantic validation, and risk checks should happen before the database runs the query.

What should happen when the guard cannot understand a query?

For production Text-to-SQL, the safest default is to fail closed with an unsupported or repair decision. The response should explain what was unsupported, such as a dialect construct, dynamic SQL, stored procedure call, or ambiguous reference.

Does this architecture require a data catalog?

It requires catalog-like metadata. That can come from a data catalog, database information schema, dbt artifacts, manually curated schema files, or a metadata service. The guard needs reliable information about tables, columns, labels, relationships, and policies.

How does column-level lineage help SQL Guard decisions?

Column-level lineage shows which source fields affect outputs, filters, joins, groups, and derived columns. This helps detect indirect sensitive-field use, explain why a query was blocked, and produce an audit trail that reviewers can understand.

Quick Reference

Summary

An LLM SQL Guard architecture gives enterprise Text-to-SQL systems a deterministic safety layer. The model can propose SQL, but the guard decides whether that SQL is valid, permitted, low-risk, repairable, or blocked.

The essential design is straightforward: parse the SQL, bind it to catalog metadata, validate its meaning, apply policy, inspect sensitive-field dependencies, score risk, return a clear decision, and write an audit log. The details matter, but the architectural principle is simple: generated SQL should never reach production data without a structured pre-execution check.

For teams building ChatBI, AI analytics agents, or internal Text-to-SQL workflows, this architecture provides a practical path from prototype to production. Start with deterministic parsing, catalog-aware validation, field-level policy checks, and audit logs. Then expand into richer lineage, risk scoring, approval workflows, and governance integrations as the system matures.

Try SQL Guard-Style Validation

If you are evaluating Text-to-SQL or ChatBI for enterprise use, try SQL Guard-style validation with a generated query and review what should be checked before execution:

Test an LLM-generated SQL query

For architecture review or a production pilot, prepare a small set of representative SQL examples, including safe queries, hallucinated columns, sensitive-field access, joins, aggregations, and large scans. These examples make it much easier to evaluate whether a guard is ready for your environment.