---
phase: 29-facts-clause-hierarchies
plan: 01
type: execute
wave: 1
depends_on: []
files_modified:
  - src/body_parser.rs
  - src/model.rs
  - src/parse.rs
  - src/graph.rs
  - src/ddl/define.rs
autonomous: true
requirements:
  - FACT-01
  - FACT-03
  - FACT-04
  - HIER-01
  - HIER-02

must_haves:
  truths:
    - "FACTS clause with alias.name AS expr entries is accepted in DDL"
    - "HIERARCHIES clause with name AS (dim1, dim2, dim3) entries is accepted in DDL"
    - "Clause ordering is enforced: TABLES, RELATIONSHIPS, FACTS, HIERARCHIES, DIMENSIONS, METRICS"
    - "Fact cycles are rejected at define time with clear error message"
    - "References to non-existent facts are rejected at define time"
    - "Hierarchies referencing non-existent dimensions are rejected at define time"
    - "Facts source_table aliases are validated against declared tables"
  artifacts:
    - path: "src/body_parser.rs"
      provides: "FACTS and HIERARCHIES clause parsing"
      contains: "parse_hierarchies_clause"
    - path: "src/model.rs"
      provides: "Hierarchy struct with serde derives"
      contains: "pub struct Hierarchy"
    - path: "src/parse.rs"
      provides: "Facts and hierarchies wired from KeywordBody to SemanticViewDefinition"
      contains: "keyword_body.facts"
    - path: "src/graph.rs"
      provides: "Fact DAG validation (cycles, unknown refs, source table reachability)"
      contains: "validate_facts"
    - path: "src/ddl/define.rs"
      provides: "Fact and hierarchy validation calls in bind()"
      contains: "validate_facts"
  key_links:
    - from: "src/body_parser.rs"
      to: "src/parse.rs"
      via: "KeywordBody struct fields (facts, hierarchies)"
      pattern: "keyword_body\\.facts"
    - from: "src/parse.rs"
      to: "src/model.rs"
      via: "SemanticViewDefinition fields (facts, hierarchies)"
      pattern: "facts:\\s*keyword_body\\.facts"
    - from: "src/ddl/define.rs"
      to: "src/graph.rs"
      via: "validate_facts and validate_hierarchies calls"
      pattern: "validate_facts|validate_hierarchies"
---

<objective>
Parse FACTS and HIERARCHIES clauses in DDL and validate fact/hierarchy correctness at define time.

Purpose: Enable users to declare named row-level sub-expressions (facts) and drill-down paths (hierarchies) in semantic view DDL. Facts are the prerequisite for metric-to-fact inlining (Plan 02). Hierarchies are pure metadata validated against declared dimensions.

Output: body_parser.rs parses both new clauses; model.rs has Hierarchy struct; parse.rs wires into SemanticViewDefinition; graph.rs validates fact DAG (cycles, unknown refs); define.rs calls validation at CREATE time.
</objective>

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

<context>
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/phases/29-facts-clause-hierarchies/29-RESEARCH.md

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

From src/model.rs:
```rust
// Fact struct already exists (scaffolded in Phase 11)
pub struct Fact {
    pub name: String,
    pub expr: String,
    pub source_table: Option<String>,
}

pub struct SemanticViewDefinition {
    // ... existing fields ...
    pub facts: Vec<Fact>,          // already exists, default empty
    // hierarchies field MUST be added
}
```

From src/body_parser.rs:
```rust
pub struct KeywordBody {
    pub tables: Vec<TableRef>,
    pub relationships: Vec<Join>,
    pub dimensions: Vec<Dimension>,
    pub metrics: Vec<Metric>,
    // facts and hierarchies fields MUST be added
}

const CLAUSE_KEYWORDS: &[&str] = &["tables", "relationships", "dimensions", "metrics"];
const CLAUSE_ORDER: &[&str] = &["tables", "relationships", "dimensions", "metrics"];

// parse_qualified_entries returns Vec<(source_alias, bare_name, expr)>
// FACTS clause reuses this same parser since syntax is identical: alias.name AS expr
pub(crate) fn parse_qualified_entries(body: &str, base_offset: usize)
    -> Result<Vec<(String, String, String)>, ParseError>;
```

From src/parse.rs (rewrite_ddl_keyword_body, lines 466-476):
```rust
let def = crate::model::SemanticViewDefinition {
    base_table,
    tables: keyword_body.tables,
    dimensions: keyword_body.dimensions,
    metrics: keyword_body.metrics,
    joins: keyword_body.relationships,
    filters: vec![],
    facts: vec![],              // currently hardcoded to empty
    column_type_names: vec![],
    column_types_inferred: vec![],
};
```

From src/graph.rs:
```rust
pub fn validate_graph(def: &SemanticViewDefinition) -> Result<RelationshipGraph, String>;
// Uses Kahn's algorithm for toposort, detect cycles as leftover nodes
```

From src/ddl/define.rs (bind, line 117):
```rust
crate::graph::validate_graph(&def).map_err(|e| Box::<dyn std::error::Error>::from(e))?;
// Fact and hierarchy validation calls must be added AFTER graph validation
```
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: Parse FACTS and HIERARCHIES clauses in body_parser + wire through parse.rs</name>
  <files>src/body_parser.rs, src/model.rs, src/parse.rs</files>
  <behavior>
    - Test: parse_keyword_body with FACTS clause produces Fact entries with correct name, expr, source_table
    - Test: parse_keyword_body with HIERARCHIES clause produces Hierarchy entries with correct name and levels
    - Test: FACTS clause reuses parse_qualified_entries (same alias.name AS expr pattern as dims/metrics)
    - Test: HIERARCHIES syntax: name AS (dim1, dim2, dim3) -- parentheses required
    - Test: clause ordering enforced: FACTS must come after RELATIONSHIPS and before HIERARCHIES; HIERARCHIES before DIMENSIONS
    - Test: empty FACTS () and empty HIERARCHIES () clauses produce empty vecs (not errors)
    - Test: FACTS and HIERARCHIES are optional clauses (existing DDL without them still works)
    - Test: multiple facts with trailing comma parse correctly
    - Test: hierarchy with single level: name AS (dim1) works
    - Test: fact without source table prefix is rejected (parse_qualified_entries requires alias.name)
  </behavior>
  <action>
1. **model.rs** -- Add Hierarchy struct:
   ```rust
   #[derive(Debug, Clone, Default, Serialize, Deserialize)]
   #[cfg_attr(feature = "arbitrary", derive(arbitrary::Arbitrary))]
   pub struct Hierarchy {
       pub name: String,
       pub levels: Vec<String>,
   }
   ```
   Add `hierarchies: Vec<Hierarchy>` field to SemanticViewDefinition with `#[serde(default, skip_serializing_if = "Vec::is_empty")]` for backward-compatible JSON.

2. **body_parser.rs** -- Extend clause parsing:
   - Add "facts" and "hierarchies" to CLAUSE_KEYWORDS: `&["tables", "relationships", "facts", "hierarchies", "dimensions", "metrics"]`
   - Add "facts" and "hierarchies" to CLAUSE_ORDER in same position (after relationships, before dimensions)
   - Update suggest_clause_keyword to cover new keywords (automatic since it iterates CLAUSE_KEYWORDS)
   - Add `facts: Vec<Fact>` and `hierarchies: Vec<Hierarchy>` fields to KeywordBody struct
   - Add `use crate::model::{Fact, Hierarchy}` to imports (Dimension, Join, Metric, TableRef already imported)
   - In parse_keyword_body, after parsing relationships and before parsing dimensions:
     - If "facts" clause found, call `parse_qualified_entries` on its body to get `Vec<(alias, name, expr)>`, map to `Vec<Fact>` with `Fact { name, expr, source_table: Some(alias) }`
     - If "facts" clause not found, set `facts: vec![]`
     - If "hierarchies" clause found, call new `parse_hierarchies_clause` on its body
     - If "hierarchies" clause not found, set `hierarchies: vec![]`
   - Add `parse_hierarchies_clause(body: &str, base_offset: usize) -> Result<Vec<Hierarchy>, ParseError>`:
     - Use `split_at_depth0_commas(body)` to split entries
     - For each entry, find case-insensitive "AS" keyword
     - After AS, expect `(dim1, dim2, dim3)` -- find opening `(`, split contents at commas, trim each level name
     - If no opening `(` after AS, return ParseError: "expected '(' after AS in hierarchy entry"
     - Return `Hierarchy { name: trimmed_name, levels: vec_of_trimmed_level_names }`

3. **parse.rs** -- Wire facts and hierarchies from KeywordBody to SemanticViewDefinition:
   - In `rewrite_ddl_keyword_body`, change `facts: vec![]` to `facts: keyword_body.facts`
   - Add `hierarchies: keyword_body.hierarchies` to the SemanticViewDefinition construction

4. **Add unit tests** in body_parser.rs tests module:
   - parse_keyword_body with FACTS clause (single fact, multiple facts)
   - parse_keyword_body with HIERARCHIES clause (single hierarchy, multiple hierarchies)
   - parse_keyword_body with both FACTS and HIERARCHIES
   - parse_keyword_body with FACTS/HIERARCHIES in wrong order (rejected)
   - Hierarchy with empty parens rejected
   - Hierarchy without parens rejected
   - Existing tests must still pass (backward compat)

5. **Add unit tests** in model.rs tests module:
   - Hierarchy roundtrip serialization
   - SemanticViewDefinition with hierarchies field roundtrips
   - Old JSON without hierarchies field deserializes with empty vec
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo test body_parser::tests:: -- --nocapture 2>&1 | tail -5 && cargo test model::tests:: -- --nocapture 2>&1 | tail -5</automated>
  </verify>
  <done>
    - FACTS clause parsed via parse_qualified_entries producing Vec of Fact with name, expr, source_table
    - HIERARCHIES clause parsed via new parse_hierarchies_clause producing Vec of Hierarchy with name, levels
    - KeywordBody includes facts and hierarchies fields
    - parse.rs wires facts/hierarchies from KeywordBody into SemanticViewDefinition (not hardcoded empty)
    - Hierarchy struct in model.rs with Serialize/Deserialize and backward-compatible serde attributes
    - Clause ordering enforced: tables, relationships, facts, hierarchies, dimensions, metrics
    - All existing body_parser and model tests still pass
  </done>
</task>

<task type="auto" tdd="true">
  <name>Task 2: Define-time validation for facts (DAG, cycles, unknown refs) and hierarchies (unknown dims)</name>
  <files>src/graph.rs, src/ddl/define.rs</files>
  <behavior>
    - Test: validate_facts with no facts returns Ok
    - Test: validate_facts with single fact referencing valid source table returns Ok
    - Test: validate_facts with fact referencing non-existent source table returns Err with "unknown source table" and fuzzy suggestion
    - Test: validate_facts with two independent facts returns Ok
    - Test: validate_facts with fact A referencing fact B (valid chain) returns Ok with topological order
    - Test: validate_facts with fact cycle (A refs B, B refs A) returns Err with "cycle detected in facts"
    - Test: validate_facts with fact referencing non-existent fact name returns Err with descriptive message
    - Test: validate_hierarchies with no hierarchies returns Ok
    - Test: validate_hierarchies with hierarchy referencing valid dimensions returns Ok
    - Test: validate_hierarchies with hierarchy referencing non-existent dimension returns Err with "unknown dimension" and fuzzy suggestion
  </behavior>
  <action>
1. **graph.rs** -- Add fact validation functions:

   `validate_facts(def: &SemanticViewDefinition) -> Result<(), String>`:
   - If `def.facts.is_empty()`, return `Ok(())`
   - Check each fact's `source_table` is in `def.tables` aliases (case-insensitive). Use `suggest_closest` for fuzzy suggestion on unknown alias.
   - Build a fact dependency DAG: for each fact, scan its `expr` for references to other fact names (using word-boundary matching -- check char before/after is not `[a-zA-Z0-9_]`). Build adjacency list: fact_name -> vec of referenced fact names.
   - Run Kahn's algorithm on the fact DAG. If leftover nodes after toposort, report cycle: "cycle detected in facts: A -> B -> A"
   - Check that any fact name referenced in another fact's expr actually exists in `def.facts`. If not, error: "unknown fact 'X' referenced in fact 'Y'" with `suggest_closest` against fact names.

   `validate_hierarchies(def: &SemanticViewDefinition) -> Result<(), String>`:
   - If `def.hierarchies.is_empty()` (or the field for hierarchies doesn't exist), return `Ok(())`
   - Collect all dimension names from `def.dimensions` (lowercased)
   - For each hierarchy, check every level name exists in the dimension names set (case-insensitive). If not, error: "unknown dimension 'X' in hierarchy 'Y'" with `suggest_closest` against dimension names.

   Add helper `fn is_word_boundary_char(c: u8) -> bool` -- returns true if char is NOT `[a-zA-Z0-9_]`. This is used for fact-reference scanning.

   Add helper `fn find_fact_references(expr: &str, fact_names: &[&str]) -> Vec<String>` -- scans `expr` for word-boundary matches of any fact name. Returns list of referenced fact names. Used by the DAG builder above.

2. **define.rs** -- Wire validation into DefineFromJsonVTab bind():
   - After the existing `crate::graph::validate_graph(&def)` call (line 117), add:
     ```rust
     crate::graph::validate_facts(&def)
         .map_err(|e| Box::<dyn std::error::Error>::from(e))?;
     crate::graph::validate_hierarchies(&def)
         .map_err(|e| Box::<dyn std::error::Error>::from(e))?;
     ```

3. **Add unit tests** in graph.rs tests module:
   - Test validate_facts with various scenarios (see behavior block above)
   - Test validate_hierarchies with valid and invalid cases
   - Test find_fact_references with word boundary edge cases: "net_price" should NOT match in "net_price_total", should match in "SUM(net_price)", "net_price + tax", "(net_price)"
   - Test fact cycle detection with 2-node and 3-node cycles
   - Test fact source table validation with fuzzy suggestion
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo test graph::tests:: -- --nocapture 2>&1 | tail -5 && cargo test -- --nocapture 2>&1 | tail -5</automated>
  </verify>
  <done>
    - validate_facts checks source table reachability, fact cycles (Kahn's algorithm), and unknown fact references
    - validate_hierarchies checks all hierarchy levels exist in declared dimensions
    - Both functions use suggest_closest for fuzzy "did you mean" suggestions on errors
    - DefineFromJsonVTab bind() calls both validation functions after graph validation
    - Word-boundary matching correctly avoids substring collisions (net_price vs net_price_total)
    - All existing graph.rs tests still pass
    - Full cargo test passes
  </done>
</task>

</tasks>

<verification>
After both tasks:
- `cargo test` passes (all unit tests including new body_parser, model, graph tests)
- DDL with FACTS and HIERARCHIES clauses is parseable (confirmed by unit tests)
- Invalid fact cycles, unknown fact refs, and unknown hierarchy dims are rejected (confirmed by unit tests)
- Existing DDL without FACTS/HIERARCHIES still works (backward compatibility confirmed by existing tests passing)
</verification>

<success_criteria>
1. FACTS clause entries parsed via existing parse_qualified_entries pattern (alias.name AS expr)
2. HIERARCHIES clause entries parsed via new parse_hierarchies_clause (name AS (dim1, dim2, dim3))
3. Both clauses wired from KeywordBody through parse.rs into SemanticViewDefinition
4. Fact DAG validation (cycles, unknown refs, source table reachability) at define time
5. Hierarchy validation (unknown dimensions) at define time
6. All existing tests pass unchanged (backward compatibility)
7. `cargo test` green
</success_criteria>

<output>
After completion, create `.planning/phases/29-facts-clause-hierarchies/29-01-SUMMARY.md`
</output>
