---
phase: 11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views
plan: "01"
type: execute
wave: 1
depends_on: []
files_modified:
  - .planning/phases/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/DESIGN.md
  - src/model.rs
autonomous: true
requirements:
  - DDL-03
  - DDL-04

must_haves:
  truths:
    - "DESIGN.md exists with before/after syntax comparison and rationale"
    - "model.rs has TableRef and JoinColumn structs with serde derive"
    - "SemanticViewDefinition has a tables field with serde default"
    - "Join has a join_columns field (Vec<JoinColumn>) with serde default"
    - "All existing model.rs unit tests still pass (cargo test)"
  artifacts:
    - path: ".planning/phases/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/DESIGN.md"
      provides: "Before/after syntax reference for all downstream plans"
    - path: "src/model.rs"
      provides: "TableRef, JoinColumn, updated Join + SemanticViewDefinition — contracts for parse_args.rs and expand.rs"
      exports:
        - "pub struct TableRef { pub alias: String, pub table: String }"
        - "pub struct JoinColumn { pub from: String, pub to: String }"
        - "Join.join_columns: Vec<JoinColumn>"
        - "SemanticViewDefinition.tables: Vec<TableRef>"
  key_links:
    - from: "src/model.rs"
      to: "src/ddl/parse_args.rs (Plan 02)"
      via: "TableRef, JoinColumn, SemanticViewDefinition imports"
    - from: "src/model.rs"
      to: "src/expand.rs (Plan 03)"
      via: "Join.join_columns used in SQL codegen"
---

<objective>
Establish the data model contracts and design document that all subsequent plans in Phase 11.1 build against.

Purpose: Creates the shared type definitions (TableRef, JoinColumn) and updates SemanticViewDefinition to support the new STRUCT/LIST DDL interface. The DESIGN.md serves as the canonical before/after reference so every executor knows exactly what changed and why.

Output: DESIGN.md (before/after syntax + rationale), updated src/model.rs with new structs added using serde defaults for backward compat.
</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/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/11.1-CONTEXT.md
@.planning/phases/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/11.1-RESEARCH.md

<interfaces>
<!-- Existing model.rs structures — executor extends these, does NOT replace them -->

Current src/model.rs key types:

```rust
pub struct Dimension {
    pub name: String,
    pub expr: String,
    #[serde(default)]
    pub source_table: Option<String>,
    #[serde(default, rename = "type")]
    pub dim_type: Option<String>,
    #[serde(default)]
    pub granularity: Option<String>,
}

pub struct Metric {
    pub name: String,
    pub expr: String,
    #[serde(default)]
    pub source_table: Option<String>,
}

pub struct Fact {
    pub name: String,
    pub expr: String,
    #[serde(default)]
    pub source_table: Option<String>,
}

pub struct Join {
    pub table: String,
    #[serde(default, skip_serializing_if = "String::is_empty")]
    pub on: String,            // LEGACY — kept for serde backward compat
    #[serde(default)]
    pub from_cols: Vec<String>, // Phase 11 format — deprecated in 11.1, kept for serde compat
}

pub struct SemanticViewDefinition {
    pub base_table: String,
    pub dimensions: Vec<Dimension>,
    pub metrics: Vec<Metric>,
    #[serde(default)]
    pub filters: Vec<String>,
    #[serde(default)]
    pub joins: Vec<Join>,
    #[serde(default)]
    pub facts: Vec<Fact>,
}
```

New types to ADD (Phase 11.1):

```rust
// New: table alias entry — for the `tables` DDL parameter
pub struct TableRef {
    pub alias: String,
    pub table: String,
}

// New: column-pair relationship — replaces parallel `from_cols` approach
pub struct JoinColumn {
    pub from: String,
    pub to: String,
}

// ADD to Join (existing `on` and `from_cols` remain for backward compat):
//   #[serde(default)]
//   pub join_columns: Vec<JoinColumn>,  // Phase 11.1: column-pair structs

// ADD to SemanticViewDefinition (with serde default):
//   #[serde(default)]
//   pub tables: Vec<TableRef>,          // Phase 11.1: multi-table alias registry
```
</interfaces>
</context>

<tasks>

<task type="auto">
  <name>Task 1: Write DESIGN.md with before/after syntax comparison</name>
  <files>.planning/phases/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/DESIGN.md</files>
  <action>
Create DESIGN.md in the phase directory. The document must contain:

**Section 1: Summary**
- One paragraph explaining: Phase 11.1 reshapes the DDL and query interface to align with Snowflake semantic view concepts. No new semantic capabilities — interface only.

**Section 2: Before/After — DDL**

BEFORE (old):
```sql
SELECT define_semantic_view('sales_view', '{
    "base_table": "orders",
    "joins": [{"table": "customers", "on": "orders.customer_id = customers.id"}],
    "dimensions": [{"name": "region", "expr": "o.region", "source_table": "customers"}],
    "metrics": [{"name": "revenue", "expr": "sum(o.amount)"}]
}');
```

AFTER (new):
```sql
SELECT define_semantic_view(
    'sales_view',
    [{'alias': 'o', 'table': 'orders'}, {'alias': 'c', 'table': 'customers'}],
    [{'from_table': 'o', 'to_table': 'c', 'join_columns': [{'from': 'customer_id', 'to': 'id'}]}],
    [{'name': 'region', 'expr': 'o.region', 'source_table': 'o'}],
    [{'name': 'order_date', 'expr': 'o.created_at', 'granularity': 'day'}],
    [{'name': 'revenue', 'expr': 'sum(o.amount)', 'source_table': 'o'}]
);
```

Also document the convenience 4-arg overload for simple single-table views:
```sql
SELECT define_semantic_view(
    'orders_view',
    [{'alias': 'o', 'table': 'orders'}],
    [],                                         -- relationships: empty
    [{'name': 'region', 'expr': 'o.region', 'source_table': 'o'}],
    [],                                         -- time_dimensions: empty
    [{'name': 'revenue', 'expr': 'sum(o.amount)', 'source_table': 'o'}]
);
```

**Section 3: Before/After — Query function**

BEFORE: `SELECT * FROM semantic_query('sales_view', dimensions := ['region'], metrics := ['revenue'])`
AFTER: `SELECT * FROM semantic_view('sales_view', dimensions := ['region'], metrics := ['revenue'])`

Also document table-qualified dimension names:
`SELECT * FROM semantic_view('sales_view', dimensions := ['o.region', 'c.tier'], metrics := ['revenue'])`

**Section 4: Parameter Types**

Document each positional parameter:
- arg 0: `VARCHAR` — view name
- arg 1: `LIST(STRUCT(alias VARCHAR, "table" VARCHAR))` — tables
- arg 2: `LIST(STRUCT(from_table VARCHAR, to_table VARCHAR, join_columns LIST(STRUCT("from" VARCHAR, "to" VARCHAR))))` — relationships
- arg 3: `LIST(STRUCT(name VARCHAR, expr VARCHAR, source_table VARCHAR))` — dimensions
- arg 4: `LIST(STRUCT(name VARCHAR, expr VARCHAR, granularity VARCHAR))` — time_dimensions (granularity: day|week|month|year)
- arg 5: `LIST(STRUCT(name VARCHAR, expr VARCHAR, source_table VARCHAR))` — metrics

Note: VScalar does not support named parameters — these are positional. Use `[]` for unused lists.

**Section 5: Internal Model Changes**

List new types: TableRef, JoinColumn. Note that `Join.on` and `Join.from_cols` are kept for backward-compat with stored JSON but new definitions write `join_columns` instead. Old stored definitions still load via serde defaults.

**Section 6: Snowflake Alignment**

One paragraph noting terminology alignment: `time_dimensions` (separate list), `expr`, `source_table`, `join_columns` with `from`/`to` (Snowflake uses `left_column`/`right_column` in `relationship_columns` — we use shorter names). Reference: https://docs.snowflake.com/en/user-guide/views-semantic/semantic-view-yaml-spec
  </action>
  <verify>
    <automated>test -f "/Users/paul/Documents/Dev/Personal/duckdb-semantic-views/.planning/phases/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/DESIGN.md" && grep -q "define_semantic_view" "/Users/paul/Documents/Dev/Personal/duckdb-semantic-views/.planning/phases/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/DESIGN.md" && grep -q "semantic_view" "/Users/paul/Documents/Dev/Personal/duckdb-semantic-views/.planning/phases/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/DESIGN.md" && echo "DESIGN.md OK"</automated>
  </verify>
  <done>DESIGN.md exists with both before/after DDL and query sections, parameter type table, and Snowflake alignment note</done>
</task>

<task type="auto" tdd="true">
  <name>Task 2: Add TableRef, JoinColumn to model.rs; update Join and SemanticViewDefinition</name>
  <files>src/model.rs</files>
  <behavior>
    - Test 1: `TableRef { alias: "o", table: "orders" }` serializes to `{"alias":"o","table":"orders"}` and deserializes back
    - Test 2: `JoinColumn { from: "customer_id", to: "id" }` serializes to `{"from":"customer_id","to":"id"}` and deserializes back
    - Test 3: `Join` with `join_columns` field round-trips correctly; old JSON without `join_columns` deserializes with `join_columns: []` (serde default)
    - Test 4: Old `Join` JSON with only `on` field still deserializes without error (backward compat)
    - Test 5: `SemanticViewDefinition` with `tables: [TableRef { alias: "o", table: "orders" }]` serializes to JSON including `"tables":[{"alias":"o","table":"orders"}]`
    - Test 6: Old `SemanticViewDefinition` JSON without `tables` field still deserializes with `tables: []` (serde default)
    - Test 7: ALL existing model.rs unit tests continue to pass — no regressions
  </behavior>
  <action>
Add the following to src/model.rs, keeping all existing code intact:

1. Add `TableRef` struct BEFORE `Dimension`:
```rust
/// A table alias entry for the `tables` DDL parameter.
/// Maps a short alias (e.g., `"o"`) to a physical table name (e.g., `"orders"`).
#[derive(Debug, Clone, Default, Serialize, Deserialize)]
#[cfg_attr(feature = "arbitrary", derive(arbitrary::Arbitrary))]
pub struct TableRef {
    pub alias: String,
    pub table: String,
}
```

2. Add `JoinColumn` struct BEFORE `Join`:
```rust
/// A column-pair relationship entry for composite or single FK declarations.
/// Used in the `relationships` DDL parameter's `join_columns` field.
#[derive(Debug, Clone, Default, Serialize, Deserialize)]
#[cfg_attr(feature = "arbitrary", derive(arbitrary::Arbitrary))]
pub struct JoinColumn {
    pub from: String,
    pub to: String,
}
```

3. Add `join_columns` field to the existing `Join` struct (keep `on` and `from_cols`):
```rust
    /// Phase 11.1: column-pair FK declarations. Replaces `from_cols` for new definitions.
    /// Old stored JSON without this field deserializes with empty Vec.
    #[serde(default)]
    pub join_columns: Vec<JoinColumn>,
```

4. Add `tables` field to `SemanticViewDefinition` (keep all existing fields):
```rust
    /// Phase 11.1: table alias registry for multi-table views.
    /// Old stored JSON without this field deserializes with empty Vec.
    #[serde(default)]
    pub tables: Vec<TableRef>,
```
Place the `tables` field after `base_table` and before `dimensions`.

5. Add new tests in a new `mod phase11_1_model_tests` block:
   - Test `TableRef` round-trip serialization
   - Test `JoinColumn` round-trip serialization
   - Test that old `Join` JSON (with only `on`) still loads with `join_columns: []`
   - Test new `Join` JSON with `join_columns` loads correctly
   - Test `SemanticViewDefinition` with `tables` field round-trips
   - Test old `SemanticViewDefinition` JSON without `tables` loads with `tables: []`

Run `cargo test` (no extension feature) to verify all tests pass before declaring done.
  </action>
  <verify>
    <automated>cd /Users/paul/Documents/Dev/Personal/duckdb-semantic-views && cargo test 2>&1 | tail -20</automated>
  </verify>
  <done>All existing model.rs tests pass plus new phase11_1_model_tests pass; TableRef, JoinColumn, Join.join_columns, SemanticViewDefinition.tables are present in model.rs with serde defaults</done>
</task>

</tasks>

<verification>
Run `cargo test` — all tests pass (no regressions). DESIGN.md exists and contains before/after sections for both DDL and query interfaces.
</verification>

<success_criteria>
- DESIGN.md written with complete before/after syntax and parameter type documentation
- `TableRef`, `JoinColumn` structs added to model.rs with serde derive and `#[cfg_attr(feature = "arbitrary")]`
- `Join.join_columns: Vec<JoinColumn>` added with `#[serde(default)]`
- `SemanticViewDefinition.tables: Vec<TableRef>` added with `#[serde(default)]`
- All existing tests in `cargo test` continue to pass
- New unit tests verify serde round-trips and backward compat with old JSON
</success_criteria>

<output>
After completion, create `.planning/phases/11.1-review-possible-ddl-and-query-syntax-options-and-bring-it-as-close-as-we-can-to-snowflake-semantic-views/11.1-01-SUMMARY.md`
</output>
