---
phase: 48-window-function-metrics
plan: 01
type: execute
wave: 1
depends_on: []
files_modified:
  - src/model.rs
  - src/body_parser.rs
  - src/render_ddl.rs
  - src/ddl/describe.rs
  - src/expand/test_helpers.rs
  - src/expand/sql_gen.rs
  - src/graph/test_helpers.rs
  - tests/expand_proptest.rs
autonomous: true
requirements: [WIN-01]
must_haves:
  truths:
    - "User can declare a window function metric with OVER (PARTITION BY EXCLUDING ...) in CREATE SEMANTIC VIEW DDL"
    - "Window metric definitions persist and load correctly via JSON serialization"
    - "GET_DDL reconstructs window metric OVER clause correctly for round-trip"
    - "DESCRIBE surfaces WINDOW_SPEC property for window metrics"
    - "Pre-Phase 48 stored views load without error (backward compatibility)"
  artifacts:
    - path: "src/model.rs"
      provides: "WindowSpec, WindowOrderBy structs with serde"
      contains: "pub struct WindowSpec"
    - path: "src/body_parser.rs"
      provides: "OVER clause parsing in metric expressions"
      contains: "window_spec"
    - path: "src/render_ddl.rs"
      provides: "OVER clause emission in GET_DDL"
      contains: "window_spec"
  key_links:
    - from: "src/body_parser.rs"
      to: "src/model.rs"
      via: "WindowSpec struct construction"
      pattern: "WindowSpec"
    - from: "src/render_ddl.rs"
      to: "src/model.rs"
      via: "WindowSpec field access for DDL emission"
      pattern: "window_spec"
---

<objective>
Add WindowSpec model types to Metric, parse the OVER (PARTITION BY EXCLUDING ...) clause from metric DDL expressions, emit it in GET_DDL, surface it in DESCRIBE, and ensure backward-compatible serde.

Purpose: Establishes the data model and parsing foundation that Plan 02 builds the expansion pipeline on top of. Without parsed WindowSpec data, the expansion engine cannot rewrite PARTITION BY EXCLUDING at query time.

Output: WindowSpec/WindowOrderBy structs in model.rs, OVER clause parsing in body_parser.rs, MetricEntry extended to 9-tuple, GET_DDL emission, DESCRIBE WINDOW_SPEC property, serde backward compatibility.
</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/48-window-function-metrics/48-RESEARCH.md
@.planning/phases/47-semi-additive-metrics/47-01-SUMMARY.md

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

From src/model.rs (Metric struct, current state):
```rust
pub struct Metric {
    pub name: String,
    pub expr: String,
    pub source_table: Option<String>,
    pub output_type: Option<String>,
    pub using_relationships: Vec<String>,
    pub comment: Option<String>,
    pub synonyms: Vec<String>,
    pub access: AccessModifier,
    pub non_additive_by: Vec<NonAdditiveDim>,
}
```

From src/model.rs (existing sort/nulls enums, to be reused):
```rust
pub enum SortOrder { Asc, Desc }
pub enum NullsOrder { Last, First }
```

From src/body_parser.rs (MetricEntry type alias, current):
```rust
type MetricEntry = (
    Option<String>,  // source_alias
    String,          // bare_name
    String,          // expr
    Vec<String>,     // using_relationships
    Option<String>,  // comment
    Vec<String>,     // synonyms
    AccessModifier,  // access
    Vec<NonAdditiveDim>, // non_additive_by
);
```

From src/body_parser.rs (key helpers):
```rust
pub(crate) fn split_at_depth0_commas(body: &str) -> Vec<(usize, &str)>;
fn extract_paren_content(s: &str) -> Option<&str>;
fn find_keyword_ci(upper_text: &str, keyword: &str) -> Option<usize>;
fn parse_non_additive_dims(content: &str, base_offset: usize) -> Result<Vec<NonAdditiveDim>, ParseError>;
```

From src/render_ddl.rs (metric emission pattern):
```rust
fn emit_metrics(out: &mut String, def: &SemanticViewDefinition) {
    // ... PRIVATE prefix, source_table.name, USING, NON ADDITIVE BY, AS expr ...
}
```
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: WindowSpec model types and MetricEntry extension</name>
  <files>src/model.rs, src/body_parser.rs, src/expand/test_helpers.rs, src/expand/sql_gen.rs, src/graph/test_helpers.rs, tests/expand_proptest.rs</files>
  <read_first>
    - src/model.rs (current Metric struct, SortOrder, NullsOrder, NonAdditiveDim for pattern reference)
    - src/body_parser.rs (current MetricEntry type alias at line 23, parse_single_metric_entry at line 1297)
    - src/expand/test_helpers.rs (Metric struct literals that need window_spec: None)
    - src/expand/sql_gen.rs (Metric struct literals that need window_spec: None)
    - src/graph/test_helpers.rs (Metric struct literals that need window_spec: None)
    - tests/expand_proptest.rs (Metric struct literals that need window_spec: None)
  </read_first>
  <behavior>
    - Test 1: WindowSpec serializes to JSON and deserializes back correctly (roundtrip)
    - Test 2: Metric with window_spec: None deserializes from JSON that lacks the field (backward compat)
    - Test 3: WindowSpec with excluding_dims, order_by, frame_clause all populated roundtrips correctly
    - Test 4: WindowOrderBy with non-default SortOrder/NullsOrder roundtrips correctly
    - Test 5: body_parser parse_metrics_clause parses a window metric with OVER (PARTITION BY EXCLUDING d1, d2 ORDER BY d1) and produces correct WindowSpec
    - Test 6: body_parser parse_metrics_clause parses a window metric with OVER (PARTITION BY EXCLUDING d1 ORDER BY d1 RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) and preserves the frame clause
    - Test 7: body_parser parse_metrics_clause parses LAG(metric, 30) OVER (...) with extra_args = ["30"]
    - Test 8: body_parser parse_metrics_clause rejects OVER clause on derived (unqualified) metrics with error
    - Test 9: Window metric combined with NON ADDITIVE BY produces error (mutually exclusive)
  </behavior>
  <action>
1. In src/model.rs, add after the NonAdditiveDim struct:

```rust
/// Parsed window function specification for window metrics.
/// Stored alongside the raw expression for expansion-time rewriting.
#[derive(Debug, Clone, Default, Serialize, Deserialize)]
#[cfg_attr(feature = "arbitrary", derive(arbitrary::Arbitrary))]
pub struct WindowSpec {
    /// The window function name (e.g., "AVG", "LAG", "SUM")
    pub window_function: String,
    /// The metric name referenced inside the window function
    pub inner_metric: String,
    /// Additional arguments after the inner metric (e.g., "30" in LAG(metric, 30))
    #[serde(default, skip_serializing_if = "Vec::is_empty")]
    pub extra_args: Vec<String>,
    /// Dimensions to EXCLUDE from partitioning (PARTITION BY EXCLUDING semantics)
    #[serde(default, skip_serializing_if = "Vec::is_empty")]
    pub excluding_dims: Vec<String>,
    /// ORDER BY clause entries (dimension/expression + direction + nulls)
    #[serde(default, skip_serializing_if = "Vec::is_empty")]
    pub order_by: Vec<WindowOrderBy>,
    /// Raw frame clause (e.g., "RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW")
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub frame_clause: Option<String>,
}

/// An ORDER BY entry in a window function specification.
#[derive(Debug, Clone, Default, Serialize, Deserialize)]
#[cfg_attr(feature = "arbitrary", derive(arbitrary::Arbitrary))]
pub struct WindowOrderBy {
    pub expr: String,
    #[serde(default, skip_serializing_if = "SortOrder::is_default")]
    pub order: SortOrder,
    #[serde(default, skip_serializing_if = "NullsOrder::is_default")]
    pub nulls: NullsOrder,
}
```

2. Add to the Metric struct a new field after `non_additive_by`:
```rust
    /// Window function specification for window metrics.
    /// When Some, this metric uses a window function wrapping another metric.
    /// Old stored JSON without this field deserializes to None.
    /// Not serialized when None to preserve backward-compatible JSON.
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub window_spec: Option<WindowSpec>,
```

3. Add `is_window()` helper method on Metric:
```rust
impl Metric {
    /// Returns true if this metric is a window function metric.
    #[must_use]
    pub fn is_window(&self) -> bool {
        self.window_spec.is_some()
    }
}
```

4. In src/body_parser.rs:
   - Extend MetricEntry from 8-tuple to 9-tuple by adding `Option<WindowSpec>` as the 9th element
   - In parse_single_metric_entry, AFTER extracting the expression via parse_trailing_annotations (line ~1324), detect and parse an OVER clause from the expression text:
     a. Find "OVER" keyword at word boundary in the uppercase expression (using depth-0 scanning to avoid matching inside string literals or nested parens)
     b. If found, extract the balanced parenthesized content after OVER
     c. Parse the OVER clause content: look for "PARTITION BY EXCLUDING", then ORDER BY, then frame clause
     d. Extract the window function name and arguments from the part before OVER (e.g., "AVG(total_qty)" -> window_function="AVG", inner_metric="total_qty"; "LAG(total_qty, 30)" -> window_function="LAG", inner_metric="total_qty", extra_args=["30"])
     e. Parse EXCLUDING dimensions as comma-separated identifiers (use split_at_depth0_commas on the content between EXCLUDING and ORDER BY / end-of-PARTITION-clause)
     f. Parse ORDER BY entries reusing the same ASC/DESC/NULLS FIRST/LAST pattern from parse_non_additive_dims
     g. The frame clause is everything after ORDER BY entries (after consuming all ORDER BY items) -- store as raw string
     h. Store the raw expression INCLUDING the OVER clause (for GET_DDL), plus the parsed WindowSpec
   - OVER clause on unqualified (derived) metrics produces ParseError: "OVER clause not allowed on derived metric '...'. Only qualified metrics (alias.name) can use OVER."
   - OVER clause combined with NON ADDITIVE BY produces ParseError: "Cannot combine OVER clause with NON ADDITIVE BY on metric '...'. Use one or the other."
   - The 9th tuple element is Some(WindowSpec) when OVER is detected, None otherwise
   - Update the MetricEntry -> Metric conversion at the call site (parse_keyword_body or wherever MetricEntry is consumed) to set `window_spec` on the Metric struct

5. Add `window_spec: None` to every existing Metric struct literal in:
   - src/expand/test_helpers.rs
   - src/expand/sql_gen.rs (any test Metric literals)
   - src/graph/test_helpers.rs
   - tests/expand_proptest.rs

6. Add a `with_window_spec` builder method in src/expand/test_helpers.rs following the pattern of `with_non_additive_by`.

7. Write unit tests in src/body_parser.rs tests module and src/model.rs (or inline) for behaviors listed above.
  </action>
  <verify>
    <automated>cargo test window_spec && cargo test parse_metrics</automated>
  </verify>
  <acceptance_criteria>
    - grep -c "pub struct WindowSpec" src/model.rs returns 1
    - grep -c "pub struct WindowOrderBy" src/model.rs returns 1
    - grep -c "pub window_spec: Option<WindowSpec>" src/model.rs returns 1
    - grep -c "fn is_window" src/model.rs returns 1
    - grep "type MetricEntry" src/body_parser.rs shows a 9-tuple (9 type elements)
    - grep -c "OVER clause not allowed on derived metric" src/body_parser.rs returns 1
    - grep -c "Cannot combine OVER clause with NON ADDITIVE BY" src/body_parser.rs returns 1
    - grep -c "with_window_spec" src/expand/test_helpers.rs returns at least 1
    - cargo test window_spec exits 0 with at least 4 passing tests
    - cargo test parse_metrics exits 0 with no failures
    - cargo test exits 0 (no regressions)
  </acceptance_criteria>
  <done>WindowSpec/WindowOrderBy model types added with serde(default). MetricEntry extended to 9-tuple. OVER clause parsing implemented in body_parser with EXCLUDING, ORDER BY, and frame clause extraction. Window+NON_ADDITIVE_BY mutual exclusion enforced. All existing Metric struct literals updated. Unit tests pass.</done>
</task>

<task type="auto">
  <name>Task 2: GET_DDL emission, DESCRIBE surfacing, and define-time validation</name>
  <files>src/render_ddl.rs, src/ddl/describe.rs, src/body_parser.rs</files>
  <read_first>
    - src/render_ddl.rs (emit_metrics function, NON ADDITIVE BY emission pattern at line ~158)
    - src/ddl/describe.rs (property row emission pattern, search for NON_ADDITIVE_BY)
    - src/body_parser.rs (define-time validation section -- search for "validate" near parse_keyword_body)
  </read_first>
  <action>
1. In src/render_ddl.rs emit_metrics function:
   - The window metric's raw expression already includes the OVER clause (stored in metric.expr), so the `AS {expr}` emission already handles it correctly for simple cases.
   - HOWEVER, to ensure consistent formatting and explicit NULLS placement in the OVER clause (same principle as NON ADDITIVE BY), reconstruct the OVER clause from the parsed WindowSpec rather than relying on the raw expression.
   - After the existing NON ADDITIVE BY emission block (line ~176) and before the `AS` keyword emission:
     - If `metric.window_spec.is_some()`, emit the expression in two parts:
       a. `AS {window_function}({inner_metric}{extra_args_comma_separated})` (the function call part)
       b. ` OVER (PARTITION BY EXCLUDING {excluding_dims}` (reconstructed from parsed spec)
       c. ` ORDER BY {order_by entries with explicit ASC/DESC and NULLS}` (if present)
       d. ` {frame_clause}` (if present)
       e. `)` (close OVER)
     - If `metric.window_spec.is_none()`, emit `AS {metric.expr}` as before
   - This ensures GET_DDL round-trips with normalized OVER clause formatting.

2. In src/ddl/describe.rs:
   - Add a WINDOW_SPEC property row for metrics that have `window_spec.is_some()`, following the NON_ADDITIVE_BY pattern.
   - Value format: `"{window_function}({inner_metric}) OVER (PARTITION BY EXCLUDING {dims} ORDER BY {entries})"` -- a human-readable summary.

3. In src/body_parser.rs (define-time validation in parse_keyword_body or the validation phase):
   - When a window metric references dimensions in EXCLUDING or ORDER BY, validate that those dimension names exist in the view's dimensions list (case-insensitive). Same validation pattern as NON ADDITIVE BY dimension reference validation.
   - Also validate that the inner_metric name references a metric that exists in the same view's metrics list (the referenced metric must be declared before or after the window metric in the METRICS clause).
   - Error messages follow existing format: "Window metric '{name}': EXCLUDING dimension '{dim}' not found in semantic view dimensions."

4. Write tests:
   - render_ddl test: round-trip a window metric through render_create_ddl and verify the OVER clause appears correctly
   - render_ddl test: round-trip a window metric with frame clause
   - DESCRIBE test: verify WINDOW_SPEC property row appears for a window metric
   - Validation test: EXCLUDING references non-existent dimension -> error
   - Validation test: inner_metric references non-existent metric -> error
  </action>
  <verify>
    <automated>cargo test render_ddl && cargo test describe && cargo test window</automated>
  </verify>
  <acceptance_criteria>
    - grep -c "WINDOW_SPEC" src/ddl/describe.rs returns at least 1
    - grep -c "window_spec" src/render_ddl.rs returns at least 2
    - grep "EXCLUDING dimension" src/body_parser.rs shows validation error message
    - cargo test render_ddl exits 0 with at least 2 window-related test passes
    - cargo test describe exits 0 with no failures
    - cargo test exits 0 (full suite, no regressions)
  </acceptance_criteria>
  <done>GET_DDL reconstructs OVER clause from parsed WindowSpec with explicit NULLS. DESCRIBE surfaces WINDOW_SPEC property. Define-time validation checks EXCLUDING dims and inner metric references. Round-trip and validation tests pass.</done>
</task>

</tasks>

<threat_model>
## Trust Boundaries

| Boundary | Description |
|----------|-------------|
| DDL input -> parser | User-supplied SQL DDL text parsed by body_parser.rs |

## STRIDE Threat Register

| Threat ID | Category | Component | Disposition | Mitigation Plan |
|-----------|----------|-----------|-------------|-----------------|
| T-48-01 | T (Tampering) | body_parser.rs OVER clause parsing | mitigate | Balanced paren extraction prevents injection; frame clause stored as raw string validated by DuckDB at query time |
| T-48-02 | D (Denial of Service) | body_parser.rs nested paren parsing | accept | Existing split_at_depth0_commas / extract_paren_content have O(n) complexity; no additional risk from OVER parsing |
| T-48-03 | I (Information Disclosure) | DESCRIBE WINDOW_SPEC output | accept | Window spec metadata is DDL-level information already visible to anyone with SHOW/GET_DDL access; no elevation of privilege |
</threat_model>

<verification>
After both tasks:
- `cargo test` passes with zero failures
- All new WindowSpec serde, parsing, render_ddl, DESCRIBE, and validation tests pass
- No regressions in existing semi-additive, metadata, wildcard, or fact tests
</verification>

<success_criteria>
- WindowSpec and WindowOrderBy structs exist in model.rs with serde(default)
- OVER (PARTITION BY EXCLUDING ...) parsed from metric DDL expressions
- GET_DDL reconstructs window metric OVER clause with explicit NULLS
- DESCRIBE shows WINDOW_SPEC property for window metrics
- Define-time validation rejects unknown EXCLUDING dims and inner metric refs
- Pre-Phase 48 stored views deserialize without error
- All `cargo test` pass
</success_criteria>

<output>
After completion, create `.planning/phases/48-window-function-metrics/48-01-SUMMARY.md`
</output>
