LLM Semantic Layer Engine Technical Whitepaper v2.0

Document version: v2.0

Position: additional capabilities after v1.0. This version continues the Foggy semantic-layer engine evolution without deprecating v1.0 or replacing its foundational syntax references.

Implementation policy: this document focuses on Java-engine capabilities covered by implementation, tests, audit coverage, or acceptance sign-off. Planned, prompt-only, and unsigned experimental behavior is not presented as stable functionality.

Boundary: this is a public semantic contract and Java-first engine boundary, not a Maven, Python, Odoo, or product-UI version number.

Foreword

The v1.0 question was: what contract should connect an LLM and a database?

The v2.0 question is broader: when the LLM must perform period comparison, funnels, pivots, cross-result alignment, secondary analysis, and reusable experience plans—not only one filter/group/aggregate query—how does the semantic engine keep its governance boundary?

Foggy v2.0 does not answer with free SQL or more prompt rules. It adds narrow execution contracts in the Java engine that can be signed, validated, and replayed:

• Java MCP orchestration handles route calibration, redispatch, and trace.
• DSL_CTE handles staged governed analysis.
• Relation result-stage expressions accept a signed formula subset.
• Memory Grid handles bounded secondary analysis.
• Pivot handles governed multi-dimensional views.
• Experience Recipe handles governed discovery and reuse of analysis experience.

Together these capabilities move Foggy from an “LLM semantic query engine” toward an “LLM semantic analysis engine” while retaining fail-closed execution, in-policy access, and reviewable evidence.

---

Part I: From Semantic Query to Semantic Analysis

1. v1.0 foundations remain valid

v2.0 does not redefine Foggy's foundation:

• TM/QM turn physical tables, fields, relationships, and metrics into business-semantic contracts.
• The LLM submits structured requests through JSON Query DSL or MCP tools.
• The engine resolves fields, injects policy, translates dialects, executes, and returns evidence.
• Permissions are not implemented in prompts, and a bare database schema is not handed to the model.
• Invisible fields, unauthorized access, and unsupported capabilities fail closed.

Every v2.0 capability builds on these rules. It expands analysis within the governance boundary instead of bypassing DSL and policy.

2. Overall layering

v2.0 adds five layers above v1.0:

Layer	Role	Key boundary
Java MCP orchestration	Route calibration, redispatch, trace, and tool chain	Do not execute an expired plan or use trace as a policy bypass
DSL_CTE staged execution	Aggregation, derivation, windows, post-filtering, ordering, and limited bridges	No arbitrary SQL CTE
Governed Expression	Signed small formulas over prior-stage aliases	No physical fields or arbitrary expression language
Memory Grid	Bounded secondary analysis and cross-model alignment	No unbounded detail or arbitrary in-memory SQL API
Pivot / Recipe	Multi-dimensional views, drilldown contracts, and reusable experience	Not a complete BI product or experience marketplace

This layering prevents complex analysis from collapsing into one giant SQL statement or a single physical query generated by the LLM. Each layer owns its input, output, validation, and rejection boundary.

3. Java MCP orchestration: from error return to real redispatch

A common failure in an LLM query system is that an initial route or plan is wrong but the old plan continues executing. v2.0 adds calibrated-route redispatch: when calibration requires replanning, the Java orchestration layer can clear stale results, trigger a new planner/tool path, and expose the redispatch evidence.

The goal is not to make the LLM always correct; it is to make the execution path actually change when the route must change.

v2.0 also adds non-blocking trace correlation. For a normal query that does not need replanning, the engine can record the model catalog, planner output, tool calls, validator result, and query-result capture status under one trace. Trace supports diagnosis, review, and replay; it does not change query semantics.

Boundaries:

• Redispatch remains constrained by model, policy, validator, and tool schema.
• Free-form text cannot replace structured query results.
• Trace is audit/debug evidence, not a new data-access entry point.

4. DSL_CTE: staged governed analysis

v1.0 JSON Query DSL expresses a single semantic query. v2.0 narrows a class of complex analysis into staged execution:

• aggregate: filter, group, and aggregate metrics through a governed model;
• derive: create a derived field from aliases emitted by the prior stage;
• window_derive: calculate cumulative, ranking, or adjacent results within a signed window contract;
• postSlice: filter result-stage fields;
• orderBy / limit: order and truncate by output, grouping fields, or signed aliases;
• join_align: bounded alignment of governed results in signed scenarios.

The point is not to let the LLM write a CTE. It generates a structured stage plan, and the Java engine verifies that every stage references visible fields and signed aliases. Whether the backend uses SQL CTE is an implementation detail, not free SQL exposed to the LLM.

Boundaries:

• Physical table names, physical columns, and free Join conditions are not plan inputs.
• Unsigned stage types cannot masquerade as executable plans.
• Result-stage filters are not silently pushed down into source filtering.
• Missing time semantics, alignment keys, grain, or policy evidence is a clarification or rejection case.

5. Governed Expression: signed formulas, not arbitrary expressions

v2.0 expands relation result-stage expressions through a signed formula subset rather than a free expression language.

The signed Java-engine surface includes metric ratio, metric difference, metric delta ratio, absolute metric delta ratio, single-threshold CASE label, ordered numeric bucket, same-stage alias DAG auto-layering, and narrow result-stage signed ranking.

These formulas cover common business calculations—share, difference, deviation, bands, ranking, and post-filtering. Safety comes from two constraints:

1. Expressions reference only visible aliases from the prior stage or a signed acyclic same-stage layer.
2. The form must match a signed template recognized by the Java compiler/validator.

Explicitly rejected are physical-field references, fields absent from the prior output, arbitrary SQL/functions/CASE, free aggregate or window embedding, and attempts to bypass field policy or result grain.

6. Period comparison, funnels, and attribution: template sign-off

Period comparison, funnels, and attribution are frequent but high-risk enterprise scenarios. v2.0 signs them one business template at a time instead of opening generic capability.

Typical Java-first signed examples include month/quarter period-over-period templates, a single-model CRM lead funnel using governed markers such as convertedOpportunityId and convertedOrderId, a limited cross-model CRM target-event window bridge, a target-month matched-bucket attribution bridge, and priority-aware, tie-aware, or composite-numerator SLA rates for service tickets.

Each template fixes the model, fields, numerator, denominator, time window, grouping, and formula. The LLM cannot invent a funnel definition or execute complex attribution with missing context.

Boundaries:

• No generic cross-model funnel.
• No automatic inference of arbitrary period comparisons, fiscal, natural, or custom calendars.
• No generic order, amount, quality, or stage-drop attribution.
• Missing template parameters require clarification; an unsigned template is rejected or moved to manual modeling.

7. Memory Grid: controlled secondary analysis of result sets

v1.0 Compose supports multi-step queries. v2.0 narrows multi-step result analysis into a bounded, validated, auditable boundary.

The Java engine provides:

• an opaque result handle for a governed query result;
• a minimal handle store/storage/resolver loop;
• resolver checks for row limit, cell limit, input grain, join keys, and resource limits;
• handle lifecycle evidence for inspect, cleanup, expired, and invalidated states;
• a guard descriptor for backend capability, bounded input, unsupported shapes, and fail-closed codes;
• an alignment contract binding input roles, match keys, grain, version, or scenario for cross-model work.

Memory Grid is for small, controlled, reviewable secondary analysis—not for importing a database into memory and letting the LLM query freely.

Unsigned capabilities include unbounded detail, arbitrary DuckDB or in-memory SQL, full outer/multi-key join, unsigned windows/nested expressions, long-lived durable backends, complete cross-service auth replay, and generic external API exposure.

8. Pivot: governed multi-dimensional views

v2.0 moves Pivot from ordinary aggregate display to a signed Java-engine multi-dimensional contract.

Implemented capabilities include a tree axis, axis windows and pagination boundaries, controlled axis-domain selection, cascade drilldown contract evidence, derived metrics within signed scope, and weekday dialect parity across SQLite, MySQL8, PostgreSQL, and narrow SQL Server weekday evidence.

The key is to treat multi-dimensional output as an engine contract, not frontend assembly. Contract evidence should identify signed and unsigned shapes.

Boundaries:

• No arbitrary interactive expand/collapse semantics.
• Unsigned shapes are not executable capability.
• No complete frontend pivot product, drag-and-drop report designer, or BI dashboard.
• SQL Server evidence is narrow (for example weekday parity), not a complete release gate.

9. Experience Recipe: from experience document to governed asset

Complex analysis needs reusable knowledge: available models, stable field combinations, required clarifications, and rejection cases. v2.0 moves Experience Recipe from offline documents/evaluation samples to a minimal Java/MCP registry.

Core capabilities include exact registry lookup; active/namespace/tenant/permission/owner filtering; lifecycle and publication gates; evidence artifact references; artifact hash/URI validation; signature-verifier SPI with strict fail-closed mode; and governance-context injection from the MCP request.

The value is not blindly applying templates. A signed experience is discoverable only under the right namespace, tenant, permission, and owner context, and remains auditable when context conflicts or required parameters are missing.

Boundaries:

• No complete Recipe marketplace.
• No claim that vector, full-text, or hybrid retrieval is on the mainline.
• No claim of completed KMS/trust root, key rotation, remote object permission, or management console.
• A Recipe cannot bypass TM/QM, policy, validator, or signed contract.

10. Database dialects and evidence boundary

Dialect differences remain an engine responsibility, not an LLM responsibility. With complex analysis, differences affect windows, dates, weekdays, Pivot column windows, and derived metrics in addition to basic SQL.

Current Java evidence covers SQLite fixtures, MySQL8/PostgreSQL Pivot parity, PostgreSQL P2 DSL_CTE/Pivot environment-gated evidence, and SQL Server weekday parity.

Narrow evidence is not a complete database release gate. In particular, v2.0 should not claim that a complete SQL Server Maven profile is a release gate merely because weekday parity has evidence.

11. Observability and reviewability

Complex analysis needs stronger evidence. A result involving redispatch, a stage plan, a Memory Grid handle, a Pivot contract, or a Recipe hit should expose the corresponding evidence:

• route calibration and actual redispatch state;
• trace ID and tool-call chain;
• query model, fields, policy, and validator result;
• DSL_CTE stages and aliases emitted at each stage;
• signed formula template and alias dependencies;
• Memory Grid handle, limits, and guard descriptor;
• Pivot contract, unsupported combinations, and unsigned shapes;
• Recipe hit, governance filtering, and artifact state.

Evidence does not replace human judgment. It makes it possible for people and systems to review what the engine executed, what it rejected, and which capability boundary applied.

---

Part II: Capability Matrix

12. v2.0 additions

The matrix includes only capabilities with Java-engine implementation evidence. “Signed off” means runtime/validator/fixture/CI/acceptance evidence; “narrow contract” means a signed template, shape, model, or dialect scope; “follow-up” is not stable v2.0 capability.

Capability	State	Suitable for	Not suitable for
Calibrated-route redispatch	Signed off	Re-entering the planner/tool path when the initial route must change	Ignoring policy or executing a free plan
Non-blocking trace correlation	Signed off	Debugging, replay, and audit of successful queries	Treating trace as a data API
DSL_CTE stage plan	Narrow contract	Staged aggregation, derivation, windows, ordering, post-filtering	Arbitrary SQL CTE or physical Join
Governed expression subset	Narrow contract	Ratios, differences, deviations, labels, buckets, limited ranking	Arbitrary functions, CASE, or expressions
Period-over-period templates	Narrow contract	Defined month/quarter comparisons	Generic calendar inference or arbitrary period comparison
Funnel/attribution templates	Narrow contract	CRM lead funnel, target-event window, target-month matched bucket	Generic cross-model funnel or complex attribution
Memory Grid	Signed off	Bounded secondary analysis and cross-model alignment	Unbounded detail, arbitrary in-memory SQL, durable backend
Pivot	Narrow contract	Tree axis, axis window, drilldown, signed derived metrics	Complete BI, arbitrary shape, unsigned interaction
Experience Recipe registry	Signed off	Exact governed discovery and reuse	Marketplace, vector search, contract bypass
Multi-database evidence	Narrow contract	SQLite/MySQL8/PostgreSQL parity and SQL Server weekday parity	Complete SQL Server release gate

13. Capabilities not promised by v2.0

• Direct bare SQL execution by an LLM.
• Arbitrary Semantic SQL or CTE.
• Arbitrary cross-model Join.
• An arbitrary expression language, function set, or CASE.
• Unsigned Pivot shapes.
• Unbounded Memory Grid or a complete DuckDB API.
• A complete AI analytics workbench, BI product, SQL notebook, or report designer.
• Synchronized Python parity for every Java-first capability.

---

Part III: Adoption Guidance

14. How integrators should read v2.0

Existing v1.0 integrations do not need to replace their models or query path:

1. Keep TM/QM and JSON Query DSL as the base query surface.
2. Introduce signed DSL_CTE, Memory Grid, Pivot, or Recipe contracts one recurring scenario at a time.
3. Add model fields, policy cases, positive tests, and rejection tests for every contract.
4. Display trace, stage plan, Pivot contract, and Recipe evidence in the UI or agent.
5. Clarify or reject unsigned scenarios; never downgrade a failed governed request to free SQL.

15. How business teams should evaluate v2.0

Do not ask only whether the system can answer a complex question. Check whether:

• metric definitions come from QM or a signed formula;
• time range, calendar semantics, and grouping grain are explicit;
• cross-model work has an alignment contract;
• Pivot output identifies signed and unsigned shapes;
• Recipe discovery satisfies namespace, tenant, permission, and owner filters;
• the result has trace and execution evidence.

When these questions cannot be answered, the system should clarify or reject instead of returning a complete-looking but unreproducible answer.

16. Conclusion

Foggy v2.0 moves LLM data access from governed query toward governed complex analysis. It retains v1.0 semantic modeling, structured DSL, permission governance, and evidence principles while adding finer execution contracts, clearer rejection boundaries, and stronger observability in the Java engine.

Treat v2.0 as the follow-on capability version: it describes new Java-first engine behavior without deprecating v1.0 or packaging unsigned generalizations as stable functionality.

Evidence sources

The capability position is based on internal sign-off and implementation records including:

• docs/v3.8/acceptance/version-signoff.md;
• docs/v3.8/README.md;
• docs/v3.8/P0-engine-capability-continuation-plan.md;
• docs/v3.8/coverage/v3.8-engine-capability-coverage-audit-20260528.md;
• docs/v3.6/P29-relation-expression-compiler-boundary-refactor-report.md;
• docs/v3.0/acceptance/*;
• docs/v3.0/coverage/*.

These records constrain the public boundary: only capabilities with Java runtime, validator, fixture, CI, or acceptance evidence enter the stable v2.0 description.