Unified Query Layer: Logical Table Names, Tab Cache, and Source Run

Companion ③: Humans and Agents share the same DuckDB query pipeline. For the semantic contract of Run / Source Run / validate / run_query, see Prompt and Context Engineering §7. For cache and Source Run usage, see User Guide §7–8.

Series Position

For the full introduction, see Series Introduction and Product Positioning.

1. Background: Why We Need a Unified Query Layer

What users and Agents reference in SQL are logical table names (filenames, cache tab names, MCP materialized tables, and alias.schema.table after ATTACH), which the backend rewrites at execution time into DuckDB-readable paths or TEMP TABLEs. The goal of the unified query layer is simple: humans and Agents write the same kind of SQL, go through the same pipeline, and are bound by the same read-only and materialization rules. Specifically:

• SQL in the editor and in the conversation is syntactically consistent;
• permissions and read-only rules are enforced in one place;
• caches created by the Agent and caches from a user's manual Run share the same lineage.

For the SQL-first methodology (why the metric definitions and data retrieval use reviewable SQL as the primary contract), see Data Analysis Workflow in the Agent Era §3. This article explains how the above principles are carried by the unified query layer (logical table names, materialization, lineage, Source Run).

2. Logical Table Names vs Actual Sources

User / Agent writes
   SELECT … FROM "monthly_revenue"
              │
              ▼
        sqlglot parse + rewrite
              │
     ┌────────┼────────┐
     ▼        ▼        ▼
  local file  TEMP cache  ATTACH / MCP

Table names must be quoted with double quotes (filenames include .csv, etc.). Excel is "file.xlsx"."SheetName". External libraries are alias.schema.table or alias.table.

3. Query Pipeline Overview

A typical path:

1. Parse the SQL (sqlglot AST).
2. Replace table names with paths, or keep cache / ATTACH names.
3. Read-only guard (reject DDL/DML).
4. DuckDB execution.
5. Optionally: materialize into a TEMP TABLE and register with TabCacheRegistry; or write into ResultManager for the results-panel pagination.

§7 expands on the analysis-ready layer; §8 expands on cache and Source Run; §11 expands on the ResultManager lifecycle.

4. sqlglot Rewriting

String replacement cannot safely handle quotes, subqueries, and multi-table JOINs. AST traversal can:

• recognize whether a table name is a registered cache → skip path rewriting;
• recognize the ATTACH catalog / db prefix → hand it to DuckDB;
• map local files to read_csv_auto / read_parquet, etc.

5. Read-only Safety

Neither user SQL nor Agent SQL may modify the original files or external library content through the query pipeline. CREATE TABLE and similar can appear only in the controlled path of "materializing a cache," not in user-authored free-form DDL.

6. Local File Mapping

Files and Excel worksheets registered in the workspace data.json are rewritten at the parse stage into DuckDB-readable expressions. The sidebar schema and the show_tables output are consistent with this registration.

7. Analysis-ready Layer (Engineering Definition)

In the Medallion metaphor of Series Introduction §1 "A Day in the Life of an Analyst", Lantide's Silver corresponds to the analysis-ready / intermediate results within the workspace—this article defines its engineering implementation, not enterprise-grade ETL.

The difference from Julius-style "in-session transforms" is that the intermediate result is a named, queryable, Source-Run-able artifact, rather than an implicit variable inside a sandbox (for comparison, see Series Introduction §1 and Workflow §3).

But the analysis-ready layer is not a mandatory path for every analysis. A one-off, clearly defined multi-table JOIN can be executed directly; only when the intermediate result needs to be reused, reviewed, or re-run is a persist tab / Source Run the better choice.

8. Cache Tables and Source Run

8.1 Single Run

When a persistent tab executes a query (POST /query or the tool run_sql_tab), the result is materialized into a DuckDB TEMP TABLE and registered in TabCacheRegistry, usually with a name matching the tab title. Other SQL can directly:

SELECT category, SUM(amount)
FROM "clean_orders"
GROUP BY category;

The cache lives in the DuckDB connection of the current workspace in memory; switching workspaces or restarting the app clears it — consistent with the "analysis intermediate result" positioning, not long-term warehousing.

Agent caches (run_query conditional materialization) are also registered as queryable tables, but semantically they are for proxy analysis and do not participate in the Source Run DAG (see Prompt and Context Engineering (Query Execution Model)).

Conditional Materialization Strategy (run_query)

When inference conflicts with the LLM passing materialize=false, auto_warn is used: it still materializes, status=ok, and attaches warnings.

Here, "conditional materialization" is a data-reuse strategy of run_query, and is not the same as blocking a query. The foreground run_sql_tab's blocking complexity gate is currently reserved only for overly complex, hard-to-review shapes (e.g., 3+ CTEs); a pure multi-entity table JOIN is allowed to execute and, at most, returns a non-blocking optimization_hint reminding you to consider a persist tab as appropriate.

source_sql and the Viewing API

The CachedTableState of TabCacheRegistry can store source_sql, result_id, and source_kind (persist | agent). On agent materialization, the SQL is written; for persist, it can be lazily loaded from the tab content.

GET /api_v1/cache/{tab_id}/source

Resolution order: registry → persist tab content → ResultManager → workspace ledger scan. It returns sql_source (registry | tab_content | result_manager | ledger | unavailable). The UI right-click View SQL does not depend on conversation_id.

8.2 Dependency Resolution and DAG

When SQL references the cache name of another persistent tab, the system resolves the dependency edge, forms a DAG, and performs cycle detection and topological sorting. The implementation is centered in the tab cache's dependency module: it extracts the quoted persistent table names in FROM from sqlglot and compares them with the registered caches.

Rule summary:

• A dependency edge = a referenced persistent cache table name;
• Self-referencing your own cache name is forbidden;
• Temporary tabs do not enter Source Run.

8.3 Source Run and SSE

Source Run on the target persistent tab: it first recomputes all upstreams in order per the DAG, then executes the target SQL. Via POST /source_run/stream, it pushes events such as plan, node_start, node_done, and complete over SSE; the frontend shows flow-diagram progress. On failure, it cleans up the related cache state.

The Agent's corresponding tool is source_run_sql_tab (UICommand + backend adapter), same source as a human pressing the toolbar Source Run. For usage, see §8.

8.4 Deep lineage

Check Lineage can build a deep lineage graph without executing the query: Physical (files), Cached (upstream tab caches), Target (current tab), and Unknown. It is used to understand where data comes from, and then decide whether to Source Run.

8.5 run_id and abort

An in-progress interactive query and Source Run can be registered with a run_id; when the user hits Abort, interrupt() is called on the DuckDB connection, and cancellation errors are identified in a unified way, so that the Tab/result state converges. Interactive queries and Source Run have aligned abort semantics.

9. External Database ATTACH

PostgreSQL / MySQL / SQLite are mounted via DuckDB ATTACH, so cross-source JOINs and local files are completed in the same SQL. The parser does not perform "pull back to local path" rewriting for an already-ATTACHed alias. The connection lifecycle is managed by the Connection Manager (test connection, persistence, schema enumeration).

10. MCP Materialized Parquet

Results returned by MCP tools are materialized into Parquet within the workspace and registered into the query layer in the form mcp_alias__table. When stale, they are refreshed by the MCP tool, rather than implicitly reaching the external network inside SQL. This is the typical path for pulling Bronze (remote / API raw sources) into the workspace's Silver (locally queryable tables).

11. ResultManager Lifecycle

An interactive query's result panel is bound to a query_result_id: it supports pagination, sorting, and export. Its division of labor differs from the TEMP cache table — the cache serves downstream SQL references; the ResultManager serves human reading and export. The Agent's sql_tab_result can read the cache result corresponding to a persistent tab. A Run path may materialize into the Registry (for downstream FROM) or write into the ResultManager (for the result panel), depending on the execution entry point; compare with the materialization branch of the §3 pipeline diagram.

12. Humans and Agents Share the Pipeline

The prompt-layer Query Execution Model (Prompt and Context Engineering §7) and the tool matrix (AI Agent Architecture) must be consistent with this layer's semantics; otherwise the model will write SQL that cannot Source Run, or confuse agent caches.

13. Error Design

Error messages point to a fixable item (table name not quoted, dependency tab not executed, circular dependency, read-only violation). When a query fails, the Context can send the error + triggering SQL together to the Agent (see §12.11).

14. Engineering Decision Overview

15. Conclusion

The unified query layer makes "the SQL an analyst writes" and "the SQL an Agent writes" land on the same set of materialization and lineage rules—it is the engineering landing point for SQL-first and the analysis-ready layer.

For methodology, see Workflow §3; for the product story of Execute and persistent tabs, see §4 and §7 of the same article; for when the model may call source_run_sql_tab, see AI Agent Architecture.