---
phase: 55-materialization-routing-engine
plan: 01
type: execute
wave: 1
depends_on: []
files_modified:
  - src/expand/mod.rs
  - src/expand/materialization.rs
  - src/expand/sql_gen.rs
  - src/expand/test_helpers.rs
  - test/sql/phase55_materialization_routing.test
  - test/sql/TEST_LIST
autonomous: true
requirements: [MAT-02, MAT-03, MAT-04, MAT-05]
user_setup: []

must_haves:
  truths:
    - "A query whose dimensions and metrics exactly match a materialization reads from the materialization table instead of expanding raw sources"
    - "A query with no matching materialization falls back to raw table expansion with no error"
    - "A query involving semi-additive metrics always falls back to raw expansion regardless of materialization coverage"
    - "A query involving window function metrics always falls back to raw expansion regardless of materialization coverage"
    - "A view with no materializations produces identical query behavior to before this phase"
  artifacts:
    - path: "src/expand/materialization.rs"
      provides: "try_route_materialization() pure function and build_materialized_sql() helper"
      exports: ["try_route_materialization"]
    - path: "src/expand/mod.rs"
      provides: "mod materialization declaration"
      contains: "mod materialization"
    - path: "src/expand/sql_gen.rs"
      provides: "Routing call site in expand() after step 3"
      contains: "try_route_materialization"
    - path: "src/expand/test_helpers.rs"
      provides: "with_materialization() builder method on TestFixtureExt"
      contains: "with_materialization"
    - path: "test/sql/phase55_materialization_routing.test"
      provides: "End-to-end sqllogictest integration tests for routing"
      contains: "phase55"
    - path: "test/sql/TEST_LIST"
      provides: "Test runner discovery entry"
      contains: "phase55_materialization_routing"
  key_links:
    - from: "src/expand/sql_gen.rs"
      to: "src/expand/materialization.rs"
      via: "super::materialization::try_route_materialization() call in expand()"
      pattern: "super::materialization::try_route_materialization"
    - from: "src/expand/materialization.rs"
      to: "src/expand/resolution.rs"
      via: "quote_ident and quote_table_ref imports"
      pattern: "use super::resolution"
    - from: "src/expand/materialization.rs"
      to: "src/model.rs"
      via: "Dimension, Metric, SemanticViewDefinition imports"
      pattern: "use crate::model"
---

<objective>
Implement query-time materialization routing in the expansion engine so that queries are transparently redirected to pre-aggregated tables when a materialization exactly covers the requested dimensions and metrics.

Purpose: This is the core value delivery of the materialization feature -- the data model (Phase 54) is inert without a routing engine that actually uses it at query time. After this phase, users who declare materializations get transparent query acceleration.

Output: A new `src/expand/materialization.rs` submodule with `try_route_materialization()` pure function, integrated into `expand()` as an early-exit path. Unit tests covering all matching/fallback/exclusion cases. Sqllogictest integration tests proving end-to-end routing through the full DDL -> query pipeline.
</objective>

<execution_context>
@$HOME/.claude/get-shit-done/workflows/execute-plan.md
@$HOME/.claude/get-shit-done/templates/summary.md
</execution_context>

<context>
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/phases/55-materialization-routing-engine/55-RESEARCH.md
@.planning/phases/54-materialization-model-ddl/54-01-SUMMARY.md

<interfaces>
<!-- Key types and contracts the executor needs. Extracted from codebase. -->

From src/model.rs:
```rust
pub struct Materialization {
    pub name: String,
    pub table: String,
    #[serde(default, skip_serializing_if = "Vec::is_empty")]
    pub dimensions: Vec<String>,
    #[serde(default, skip_serializing_if = "Vec::is_empty")]
    pub metrics: Vec<String>,
}

pub struct Dimension {
    pub name: String,
    pub expr: String,
    pub source_table: Option<String>,
    pub output_type: Option<String>,
    // ... comment, synonyms
}

pub struct Metric {
    pub name: String,
    pub expr: String,
    pub source_table: Option<String>,
    pub output_type: Option<String>,
    pub non_additive_by: Vec<NonAdditiveDim>,
    pub window_spec: Option<WindowSpec>,
    // ... using_relationships, comment, synonyms, access
}

impl Metric {
    pub fn is_window(&self) -> bool { self.window_spec.is_some() }
}

pub struct SemanticViewDefinition {
    // ... many fields ...
    pub materializations: Vec<Materialization>,
    // ...
}
```

From src/expand/resolution.rs:
```rust
pub fn quote_ident(ident: &str) -> String;
pub fn quote_table_ref(table: &str) -> String;
```

From src/expand/sql_gen.rs (expand function signature and insertion point at line 356):
```rust
pub fn expand(
    view_name: &str,
    def: &SemanticViewDefinition,
    req: &QueryRequest,
) -> Result<String, ExpandError>;
// After step 3 (resolve metrics, line 356) and before step 4 (toposort_facts, line 360)
// is the insertion point for the routing call.
```

From src/expand/test_helpers.rs (TestFixtureExt trait -- existing methods):
```rust
pub(super) trait TestFixtureExt {
    fn with_base_table(self, base_table: &str) -> Self;
    fn with_dimension(self, name: &str, expr: &str, source_table: Option<&str>) -> Self;
    fn with_metric(self, name: &str, expr: &str, source_table: Option<&str>) -> Self;
    fn with_non_additive_by(self, metric_name: &str, dims: &[(&str, SortOrder, NullsOrder)]) -> Self;
    fn with_window_spec(self, metric_name: &str, spec: WindowSpec) -> Self;
    // ... other builders
}
```
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: Implement materialization routing module with unit tests</name>
  <files>
    src/expand/materialization.rs,
    src/expand/mod.rs,
    src/expand/sql_gen.rs,
    src/expand/test_helpers.rs
  </files>
  <read_first>
    src/expand/sql_gen.rs (full file -- understand expand() control flow, especially lines 267-400 for insertion point),
    src/expand/mod.rs (module declarations),
    src/expand/test_helpers.rs (TestFixtureExt trait and builder pattern),
    src/expand/resolution.rs (quote_ident and quote_table_ref signatures),
    src/model.rs (Materialization struct at line 213, Metric.non_additive_by at line 186, Metric.is_window at line 200, Dimension.output_type, Metric.output_type),
    .planning/phases/55-materialization-routing-engine/55-RESEARCH.md (Pattern 2 for try_route_materialization, Pattern 3 for test fixtures)
  </read_first>
  <behavior>
    - Test: empty materializations vec returns None (MAT-05 zero-cost fast path)
    - Test: exact match (dims + mets equal) returns Some(sql) with SELECT from mat table (MAT-02)
    - Test: case-insensitive matching works (dim "Region" in def matches "region" in query) (MAT-02)
    - Test: dimension superset in materialization does NOT match (exact only, not subset) (MAT-02)
    - Test: metric superset in materialization does NOT match (exact only) (MAT-02)
    - Test: dimension subset in materialization does NOT match (MAT-03)
    - Test: no materialization covers request returns None (MAT-03)
    - Test: semi-additive metric (non_additive_by non-empty) returns None even with matching mat (MAT-04)
    - Test: window metric (window_spec.is_some()) returns None even with matching mat (MAT-04)
    - Test: output_type on dimension produces CAST in materialized SQL (Pitfall 3)
    - Test: output_type on metric produces CAST in materialized SQL (Pitfall 3)
    - Test: multi-part table name (catalog.schema.table) is properly quoted in output
    - Test: first matching materialization wins when multiple could match (definition order)
    - Test: dimensions-only query (empty metrics) does NOT match mat with both dims and mets
    - Test: metrics-only materialization matches metrics-only query
    - Test: end-to-end via expand() -- matching materialization produces simple SELECT from mat table
    - Test: end-to-end via expand() -- no match produces standard expansion with GROUP BY
  </behavior>
  <action>
1. **Create `src/expand/materialization.rs`** with two functions:

   a. `pub(crate) fn try_route_materialization(def: &SemanticViewDefinition, resolved_dims: &[&Dimension], resolved_mets: &[&Metric]) -> Option<String>`:
      - Return `None` immediately if `def.materializations.is_empty()` (MAT-05 fast path)
      - Return `None` if any metric has `!m.non_additive_by.is_empty()` (MAT-04 semi-additive exclusion)
      - Return `None` if any metric has `m.is_window()` (MAT-04 window exclusion)
      - Build `req_dims: HashSet<String>` from `resolved_dims.iter().map(|d| d.name.to_ascii_lowercase())`
      - Build `req_mets: HashSet<String>` from `resolved_mets.iter().map(|m| m.name.to_ascii_lowercase())`
      - Iterate `def.materializations` in definition order. For each, build `mat_dims` and `mat_mets` HashSets (lowercased). If `mat_dims == req_dims && mat_mets == req_mets`, return `Some(build_materialized_sql(&mat.table, resolved_dims, resolved_mets))`
      - Return `None` if no match found

   b. `fn build_materialized_sql(table: &str, dims: &[&Dimension], mets: &[&Metric]) -> String`:
      - Build `SELECT\n` with items for each dim and met
      - For each dim: if `dim.output_type` is Some, emit `CAST("col" AS type) AS "col"`, else emit `"col"`
      - For each met: if `met.output_type` is Some, emit `CAST("col" AS type) AS "col"`, else emit `"col"`
      - Join items with `,\n`, append `\nFROM ` + `quote_table_ref(table)`
      - Use `quote_ident()` for all column names, `quote_table_ref()` for table name

   c. Add `use std::collections::HashSet;` and `use crate::model::{Dimension, Metric, SemanticViewDefinition};` and `use super::resolution::{quote_ident, quote_table_ref};`

   d. Add `#[cfg(test)] mod tests` block with all unit tests listed in `<behavior>` above. Import `super::*` and `crate::expand::test_helpers::{orders_view, TestFixtureExt}`.

2. **Update `src/expand/mod.rs`**: Add `mod materialization;` after `mod join_resolver;` (alphabetical order among the mod declarations).

3. **Update `src/expand/test_helpers.rs`**: Add `with_materialization` method to the `TestFixtureExt` trait and implementation:
   - Trait signature: `fn with_materialization(self, name: &str, table: &str, dimensions: &[&str], metrics: &[&str]) -> Self;`
   - Implementation: push a `Materialization { name: name.to_string(), table: table.to_string(), dimensions: dimensions.iter().map(|s| s.to_string()).collect(), metrics: metrics.iter().map(|s| s.to_string()).collect() }` to `self.materializations`
   - Add `Materialization` to the existing import from `crate::model` at line 7

4. **Update `src/expand/sql_gen.rs`**: Insert the routing call after step 3 (after line 356, the closing `)?;` of resolve_names for metrics) and before step 4 (the `toposort_facts` call at current line 360). Insert exactly:
   ```rust
   // Phase 55: Materialization routing.
   // Attempt to route to a pre-aggregated table if an exact match exists.
   // Returns None if no match, or if any metric is semi-additive / window.
   if let Some(routed_sql) = super::materialization::try_route_materialization(
       def,
       &resolved_dims,
       &resolved_mets,
   ) {
       return Ok(routed_sql);
   }
   ```
  </action>
  <verify>
    <automated>cargo test materialization -- --nocapture</automated>
  </verify>
  <acceptance_criteria>
    - `src/expand/materialization.rs` exists and contains `pub(crate) fn try_route_materialization(`
    - `src/expand/materialization.rs` contains `fn build_materialized_sql(`
    - `src/expand/materialization.rs` contains `#[cfg(test)]`
    - `src/expand/mod.rs` contains `mod materialization;`
    - `src/expand/sql_gen.rs` contains `super::materialization::try_route_materialization(`
    - `src/expand/test_helpers.rs` contains `fn with_materialization(`
    - `src/expand/test_helpers.rs` contains `Materialization` in its import from `crate::model`
    - `cargo test materialization` exits 0 with all tests passing
    - At least 15 unit tests exist in the `materialization::tests` module
    - Tests cover: empty materializations, exact match, case insensitivity, superset no-match, subset no-match, semi-additive exclusion, window exclusion, output_type cast, multi-part table name, first-match wins, dimensions-only no-match, metrics-only match, end-to-end expand match, end-to-end expand fallback
  </acceptance_criteria>
  <done>
    - try_route_materialization() returns Some(sql) for exact-match and None for no-match/excluded
    - Semi-additive and window metrics always return None (MAT-04)
    - Empty materializations vec returns None immediately (MAT-05)
    - Case-insensitive name matching works
    - output_type casts applied in materialized SQL
    - with_materialization() builder available in test_helpers
    - All unit tests pass via `cargo test materialization`
  </done>
</task>

<task type="auto">
  <name>Task 2: Sqllogictest integration tests and full suite verification</name>
  <files>
    test/sql/phase55_materialization_routing.test,
    test/sql/TEST_LIST
  </files>
  <read_first>
    test/sql/phase54_materializations.test (reference for DDL syntax, table setup, error patterns -- the Phase 54 tests set up materializations but note "Phase 55" for routing),
    test/sql/phase47_semi_additive.test (reference for semi-additive DDL syntax with NON ADDITIVE BY clause),
    test/sql/phase48_window_metrics.test (reference for window metric DDL syntax with OVER PARTITION BY EXCLUDING),
    test/sql/TEST_LIST (current list of test files -- append new entry),
    src/expand/materialization.rs (just-created routing module -- verify expected SQL output format for test assertions)
  </read_first>
  <action>
1. **Create `test/sql/phase55_materialization_routing.test`** with these sections:

   **Header:**
   ```
   # Phase 55: Materialization Routing Engine integration tests
   #
   # Verifies:
   #   MAT-02: Exact-match routing to materialization table
   #   MAT-03: No-match fallback to raw expansion
   #   MAT-04: Semi-additive and window metric exclusion
   #   MAT-05: Transparent behavior without materializations

   require semantic_views
   ```

   **Setup section:** Create base tables and pre-aggregated (materialization) tables:
   - `p55_orders` table: `id INTEGER PRIMARY KEY, region VARCHAR, status VARCHAR, amount DECIMAL(10,2), order_date DATE`
   - Insert 4 rows: `(1, 'East', 'shipped', 100.00, '2024-01-01'), (2, 'West', 'pending', 200.00, '2024-01-02'), (3, 'East', 'shipped', 150.00, '2024-01-03'), (4, 'West', 'shipped', 50.00, '2024-01-04')`
   - `p55_region_agg` table: `region VARCHAR, total_revenue DECIMAL(10,2), order_count BIGINT` -- pre-aggregated by region
   - Insert into p55_region_agg: `('East', 250.00, 2), ('West', 250.00, 2)` -- matching what raw expansion would produce
   - `p55_region_status_agg` table: `region VARCHAR, status VARCHAR, total_revenue DECIMAL(10,2)` -- aggregated by region + status
   - Insert: `('East', 'shipped', 250.00), ('West', 'pending', 200.00), ('West', 'shipped', 50.00)`

   **Section 1: Exact-match routing (MAT-02)**
   - Create semantic view `p55_routed` with TABLES, DIMENSIONS (region, status), METRICS (total_revenue AS SUM(amount), order_count AS COUNT(*)), and MATERIALIZATIONS declaring `region_agg` covering TABLE p55_region_agg, DIMENSIONS (region), METRICS (total_revenue, order_count)
   - Query `semantic_view('p55_routed', dimensions := ['region'], metrics := ['total_revenue', 'order_count'])` -- should route to p55_region_agg
   - Assert with `query IR` that results match the pre-aggregated data: East 250.00 2, West 250.00 2 (sorted by region)
   - Use `query I` with `SELECT count(*)` to verify row count is 2

   **Section 2: No-match fallback (MAT-03)**
   - Query `semantic_view('p55_routed', dimensions := ['region', 'status'], metrics := ['total_revenue'])` -- region+status+total_revenue is NOT covered by any materialization
   - Assert results come from raw expansion (3 rows: East/shipped, West/pending, West/shipped)
   - Verify with `query I` count = 3

   **Section 3: Dimensions-only query does NOT route (exact match requires both)**
   - Query `semantic_view('p55_routed', dimensions := ['region'])` -- dimensions-only, materialization has metrics too
   - Assert returns 2 distinct regions (from raw expansion, SELECT DISTINCT)

   **Section 4: View without materializations (MAT-05)**
   - Create semantic view `p55_no_mat` without MATERIALIZATIONS clause
   - Query it -- should produce standard results with no errors
   - Assert results match raw expansion

   **Section 5: Semi-additive metric exclusion (MAT-04)**
   - Create semantic view `p55_semi_add` with a semi-additive metric (e.g., `latest_amount AS SUM(amount) NON ADDITIVE BY (order_date DESC)`)
   - Add materialization covering region + latest_amount
   - Query with dimensions := ['region'], metrics := ['latest_amount']
   - Assert it does NOT route to materialization (falls back to raw CTE expansion with ROW_NUMBER)
   - Verify by checking row count from raw expansion

   **Section 6: Window metric exclusion (MAT-04)**
   - Create semantic view `p55_window` with a window metric (e.g., `running_total AS SUM(amount) OVER (PARTITION BY EXCLUDING (status), ORDER BY order_date)`)
   - Add materialization covering region + running_total
   - Query with dimensions := ['region'], metrics := ['running_total']
   - Assert it does NOT route to materialization (falls back to window expansion)

   **Section 7: Multiple materializations -- first match wins**
   - Create view with two materializations both covering same dims/mets but different tables
   - Query and verify it reads from the first table (by inserting different data into each)

   **Section 8: Case-insensitive matching**
   - Create view with materialization dimensions listed as 'Region' (capitalized)
   - Query with dimensions := ['region'] (lowercase)
   - Verify routing still works (reads from mat table)

   **Section 9: Cleanup**
   - DROP SEMANTIC VIEW IF EXISTS for all p55_* views
   - DROP TABLE for all p55_* tables

2. **Update `test/sql/TEST_LIST`**: Append `test/sql/phase55_materialization_routing.test` as the last entry.

3. **Build and run full test suite:**
   - Run `just build` to compile with the new routing code
   - Run `just test-all` to verify all tests pass (Rust unit + proptest + sqllogictest + DuckLake CI)
  </action>
  <verify>
    <automated>just test-all</automated>
  </verify>
  <acceptance_criteria>
    - `test/sql/phase55_materialization_routing.test` exists and contains `require semantic_views`
    - `test/sql/phase55_materialization_routing.test` contains sections for: exact-match routing, no-match fallback, dimensions-only no-route, no-materializations transparency, semi-additive exclusion, window exclusion, first-match-wins, case-insensitive matching
    - `test/sql/TEST_LIST` contains `test/sql/phase55_materialization_routing.test`
    - `just test-all` exits 0
    - Routing queries produce results from the materialization table (matching pre-inserted aggregated data), not from raw expansion
    - Fallback queries produce results from raw expansion (matching standard GROUP BY output)
    - Semi-additive and window metric queries do NOT route to materialization tables
  </acceptance_criteria>
  <done>
    - End-to-end materialization routing verified through full DDL -> query pipeline
    - Exact-match routing reads from materialization table with correct results
    - No-match queries fall back to standard expansion with no error
    - Semi-additive metrics excluded from routing (MAT-04)
    - Window metrics excluded from routing (MAT-04)
    - Views without materializations work identically to before (MAT-05)
    - All existing tests continue to pass (no regression)
    - `just test-all` passes: all Rust tests + sqllogictest + DuckLake CI green
  </done>
</task>

</tasks>

<threat_model>
## Trust Boundaries

| Boundary | Description |
|----------|-------------|
| stored definition -> SQL generation | Materialization table names from stored catalog JSON are used to generate SQL FROM clauses |
| query parameters -> name resolution | Requested dimension/metric names from user query are resolved against the definition |

## STRIDE Threat Register

| Threat ID | Category | Component | Disposition | Mitigation Plan |
|-----------|----------|-----------|-------------|-----------------|
| T-55-01 | Tampering | materialization.rs build_materialized_sql | mitigate | Table name from stored definition passed through `quote_table_ref()` which splits on '.' and wraps each part in double quotes. Column names passed through `quote_ident()`. No raw string interpolation. |
| T-55-02 | Information Disclosure | materialization.rs try_route_materialization | accept | Routing may expose that a materialization table exists (via DuckDB error if table is missing). This is acceptable -- materialization metadata is already visible via GET_DDL and DESCRIBE. DuckDB's own access control applies to the generated SQL. |
| T-55-03 | Denial of Service | try_route_materialization HashSet construction | accept | HashSet construction is O(n) where n = number of dims/mets/materializations. These are bounded by the semantic view definition (typically < 100 entries). No amplification vector. |
| T-55-04 | Elevation of Privilege | Routing to unauthorized table | accept | The extension generates a SELECT statement; DuckDB's own privilege system enforces access control on the materialization table. The extension does not bypass any DuckDB security boundary. |
</threat_model>

<verification>
1. `cargo test materialization` -- all unit tests pass (routing logic, exclusions, edge cases)
2. `just build` -- extension compiles with routing integration
3. `just test-sql` -- sqllogictest integration tests pass including phase55 tests
4. `just test-all` -- full suite green (Rust + sqllogictest + DuckLake CI)
5. Manual spot check: existing phase54 tests still pass (backward compat)
</verification>

<success_criteria>
- Queries matching a materialization read from the materialization table (MAT-02)
- Non-matching queries fall back to raw expansion with no error (MAT-03)
- Semi-additive and window metrics always fall back to raw expansion (MAT-04)
- Views without materializations produce identical behavior (MAT-05)
- All 833+ existing tests continue to pass
- `just test-all` exits 0
</success_criteria>

<output>
After completion, create `.planning/phases/55-materialization-routing-engine/55-01-SUMMARY.md`
</output>
