# Roadmap: DuckDB Semantic Views

## Milestones

- ✅ **v0.1.0 MVP** -- Phases 1-7 (shipped 2026-02-28)
- ✅ **v0.2.0 Native DDL + Time Dimensions** -- Phases 8-14 (shipped 2026-03-03)
- ✅ **v0.3.0 Zero-Copy Query Pipeline** -- (shipped 2026-03-03)
- ✅ **v0.4.0 Simplified Dimensions** -- (shipped 2026-03-03)
- ✅ **v0.5.0 Parser Extension Spike** -- Phases 15-18 (shipped 2026-03-08)
- ✅ **v0.5.1 DDL Polish** -- Phases 19-23 (shipped 2026-03-09)
- ✅ **v0.5.2 SQL DDL & PK/FK Relationships** -- Phases 24-28 (shipped 2026-03-13)
- ✅ **v0.5.3 Advanced Semantic Features** -- Phases 29-32 (shipped 2026-03-15)
- ✅ **v0.5.4 Snowflake-Parity & Registry Publishing** -- Phases 33-36 (shipped 2026-03-27)
- ✅ **v0.5.5 SHOW/DESCRIBE Alignment & Refactoring** -- Phases 37-42 (shipped 2026-04-05)
- ✅ **v0.6.0 Snowflake SQL DDL Parity** -- Phases 43-50 (shipped 2026-04-14)
- ✅ **v0.7.0 YAML Definitions & Materialization Routing** -- Phases 51-57 (shipped 2026-04-24)
- ✅ **v0.8.0 Transactional DDL & Architectural Unification** -- Phases 58-62 (shipped 2026-05-06)
- 🚧 **v0.9.0 Read-Only Database LOAD Support + Quoted Identifier Bugfix** -- Phases 63-64 (Phase 63 complete; Phase 64 added pre-tag)

## Phases

<details>
<summary>v0.1.0 MVP (Phases 1-7) -- SHIPPED 2026-02-28</summary>

- [x] Phase 1: Scaffold (3/3 plans) -- completed 2026-02-24
- [x] Phase 2: Storage and DDL (4/4 plans) -- completed 2026-02-24
- [x] Phase 3: Expansion Engine (3/3 plans) -- completed 2026-02-25
- [x] Phase 4: Query Interface (3/3 plans) -- completed 2026-02-25
- [x] Phase 5: Hardening and Docs (2/2 plans) -- completed 2026-02-26
- [x] Phase 6: Tech Debt Code Cleanup (1/1 plan) -- completed 2026-02-26
- [x] Phase 7: Verification & Formal Closure (2/2 plans) -- completed 2026-02-27

Full details: [milestones/v1.0-ROADMAP.md](milestones/v1.0-ROADMAP.md)

</details>

<details>
<summary>v0.2.0 Native DDL + Time Dimensions (Phases 8-14) -- SHIPPED 2026-03-03</summary>

- [x] Phase 8: C++ Shim Infrastructure (2/2 plans) -- completed 2026-03-01
- [x] Phase 9: Time Dimensions (2/2 plans) -- completed 2026-03-01
- [x] Phase 10: pragma_query_t Catalog Persistence (3/3 plans) -- completed 2026-03-01
- [x] Phase 11: Scalar Function DDL (4/4 plans) -- completed 2026-03-02
- [x] Phase 11.1: Snowflake-aligned STRUCT/LIST DDL Syntax (5/5 plans) -- completed 2026-03-02
- [x] Phase 12: EXPLAIN + Typed Output (4/4 plans) -- completed 2026-03-02
- [x] Phase 13: Type-mapping + PBTs (2/2 plans) -- completed 2026-03-02
- [x] Phase 14: DuckLake Integration Test + CI (3/3 plans) -- completed 2026-03-02

Full details: [milestones/v0.2-ROADMAP.md](milestones/v0.2-ROADMAP.md)

</details>

<details>
<summary>v0.3.0 Zero-Copy Query Pipeline -- SHIPPED 2026-03-03</summary>

Replaced binary-read dispatch with zero-copy vector references (`duckdb_vector_reference_vector`).
Eliminated ~600 LOC of per-type read/write code. Type mismatches handled at SQL generation time
via `build_execution_sql` cast wrapper. Streaming chunk-by-chunk instead of collect-all-then-write.

</details>

<details>
<summary>v0.4.0 Simplified Dimensions -- SHIPPED 2026-03-03</summary>

Breaking change: removed `time_dimensions` DDL parameter and `granularities` query parameter.
Time truncation expressed via dimension `expr` directly (e.g., `date_trunc('month', created_at)`).
DDL simplified from 6 to 4 named params; query function from 3 to 2 named params.

</details>

<details>
<summary>v0.5.0 Parser Extension Spike (Phases 15-18) -- SHIPPED 2026-03-08</summary>

- [x] Phase 15: Entry Point POC (2/2 plans) -- completed 2026-03-07
- [x] Phase 16: Parser Hook Registration (1/1 plan) -- completed 2026-03-07
- [x] Phase 17: DDL Execution (1/1 plan) -- completed 2026-03-07
- [x] Phase 17.1: Python vtab crash investigation (2/2 plans) -- completed 2026-03-08
- [x] Phase 18: Verification and Integration (2/2 plans) -- completed 2026-03-08

Full details: [milestones/v0.5-ROADMAP.md](milestones/v0.5-ROADMAP.md)

</details>

<details>
<summary>v0.5.1 DDL Polish (Phases 19-23) -- SHIPPED 2026-03-09</summary>

- [x] Phase 19: Parser Hook Validation Spike (1/1 plan) -- completed 2026-03-09
- [x] Phase 20: Extended DDL Statements (2/2 plans) -- completed 2026-03-09
- [x] Phase 21: Error Location Reporting (3/3 plans) -- completed 2026-03-09
- [x] Phase 22: Documentation (1/1 plan) -- completed 2026-03-09
- [x] Phase 23: Parser Proptests and Caret Integration Tests (2/2 plans) -- completed 2026-03-09

Full details: [milestones/v0.5.1-ROADMAP.md](milestones/v0.5.1-ROADMAP.md)

</details>

<details>
<summary>v0.5.2 SQL DDL & PK/FK Relationships (Phases 24-28) -- SHIPPED 2026-03-13</summary>

- ~~Phase 24: PK/FK Model~~ -- CANCELLED (absorbed into Phase 25-01)
- [x] Phase 25: SQL Body Parser (4/4 plans) -- completed 2026-03-12
- [x] Phase 25.1: Parser Robustness & Security (2/2 plans) -- completed 2026-03-13
- [x] Phase 26: PK/FK Join Resolution (2/2 plans) -- completed 2026-03-13
- [x] Phase 27: Alias-Based Query Expansion (3/3 plans) -- completed 2026-03-13
- [x] Phase 28: Integration Testing & Docs (3/3 plans) -- completed 2026-03-13

Full details: [milestones/v0.5.2-ROADMAP.md](milestones/v0.5.2-ROADMAP.md)

</details>

<details>
<summary>v0.5.3 Advanced Semantic Features (Phases 29-32) -- SHIPPED 2026-03-15</summary>

- [x] Phase 29: FACTS Clause & Hierarchies (2/2 plans) -- completed 2026-03-14
- [x] Phase 30: Derived Metrics (2/2 plans) -- completed 2026-03-14
- [x] Phase 31: Fan Trap Detection (2/2 plans) -- completed 2026-03-14
- [x] Phase 32: Role-Playing Dimensions & USING RELATIONSHIPS (2/2 plans) -- completed 2026-03-14

Full details: [milestones/v0.5.3-ROADMAP.md](milestones/v0.5.3-ROADMAP.md)

</details>

<details>
<summary>v0.5.4 Snowflake-Parity & Registry Publishing (Phases 33-36) -- SHIPPED 2026-03-27</summary>

- [x] Phase 33: UNIQUE Constraints & Cardinality Inference (2/2 plans) -- completed 2026-03-15
- [x] Phase 34: DuckDB 1.5 Upgrade & LTS Branch (2/2 plans) -- completed 2026-03-16
- [x] Phase 34.1: DDL Surface Parity (3/3 plans) -- completed 2026-03-22
- [x] Phase 34.1.1: SHOW Command Filtering (1/1 plan) -- completed 2026-03-22
- [x] Phase 35: Documentation Site (1/1 plan) -- completed 2026-03-27
- [x] Phase 36: Registry Publishing & Maintainer Docs (2/2 plans) -- completed 2026-03-27

Full details: [milestones/v0.5.4-ROADMAP.md](milestones/v0.5.4-ROADMAP.md)

</details>

<details>
<summary>v0.5.5 SHOW/DESCRIBE Alignment & Refactoring (Phases 37-42) -- SHIPPED 2026-04-05</summary>

- [x] Phase 37: Extract Shared Utilities (1/1 plan) -- completed 2026-04-01
- [x] Phase 38: Module Directory Splits (2/2 plans) -- completed 2026-04-01
- [x] Phase 39: Metadata Storage (1/1 plan) -- completed 2026-04-02
- [x] Phase 40: SHOW Command Alignment (2/2 plans) -- completed 2026-04-02
- [x] Phase 41: DESCRIBE Rewrite (2/2 plans) -- completed 2026-04-02
- [x] Phase 42: Refactor & Test Reorg (3/3 plans) -- completed 2026-04-05

Full details: [milestones/v0.5.5-ROADMAP.md](milestones/v0.5.5-ROADMAP.md)

</details>

<details>
<summary>v0.6.0 Snowflake SQL DDL Parity (Phases 43-50) -- SHIPPED 2026-04-14</summary>

- [x] Phase 43: Metadata Foundation (2/2 plans) -- completed 2026-04-10
- [x] Phase 44: SHOW/DESCRIBE Metadata Surface + Enhancements (2/2 plans) -- completed 2026-04-11
- [x] Phase 45: ALTER COMMENT + GET_DDL (2/2 plans) -- completed 2026-04-11
- [x] Phase 46: Wildcard Selection + Queryable FACTS (2/2 plans) -- completed 2026-04-12
- [x] Phase 47: Semi-Additive Metrics (2/2 plans) -- completed 2026-04-12
- [x] Phase 48: Window Function Metrics (2/2 plans) -- completed 2026-04-12
- [x] Phase 49: Security & Correctness Hardening (2/2 plans) -- completed 2026-04-14
- [x] Phase 50: Code Quality & Test Coverage (2/2 plans) -- completed 2026-04-14

Full details: [milestones/v0.6.0-ROADMAP.md](milestones/v0.6.0-ROADMAP.md)

</details>

<details>
<summary>v0.7.0 YAML Definitions & Materialization Routing (Phases 51-57) -- SHIPPED 2026-04-24</summary>

- [x] Phase 51: YAML Parser Core (1/1 plans) -- completed 2026-04-18
- [x] Phase 52: YAML DDL Integration (1/1 plans) -- completed 2026-04-18
- [x] Phase 53: YAML File Loading (1/1 plans) -- completed 2026-04-19
- [x] Phase 54: Materialization Model & DDL (1/1 plans) -- completed 2026-04-19
- [x] Phase 55: Materialization Routing Engine (1/1 plans) -- completed 2026-04-19
- [x] Phase 56: YAML Export (1/1 plans) -- completed 2026-04-20
- [x] Phase 57: Introspection & Diagnostics (1/1 plans) -- completed 2026-04-21

Full details: [milestones/v0.7.0-ROADMAP.md](milestones/v0.7.0-ROADMAP.md)

</details>

<details>
<summary>✅ v0.8.0 Transactional DDL & Architectural Unification (Phases 58-62) -- SHIPPED 2026-05-06</summary>

- [x] Phase 58: Transactional DDL via parser_override (1/1 plans) -- completed 2026-05-02 (retroactive)
- [x] Phase 59: Architectural unification (1/1 plans) -- completed 2026-05-03 (retroactive)
- [x] Phase 60: Race guards & validation hardening (1/1 plans) -- completed 2026-05-03 (retroactive)
- [x] Phase 61: Bounded multi-DB isolation, RAII, tests & docs (1/1 plans) -- completed 2026-05-03 (retroactive)
- [x] Phase 62: Caret restoration + LRU removal (4/4 plans) -- completed 2026-05-06

Phases 58–61 are retroactive reconstructions: the work was originally completed ad-hoc on `milestone/v0.8.0` (PR #28) and a premature `milestone/v0.8.1` branch, then consolidated into a single v0.8.0 milestone on 2026-05-05 (no v0.8.1 tag was ever issued).

Full details: [milestones/v0.8.0-ROADMAP.md](milestones/v0.8.0-ROADMAP.md)

</details>

<details open>
<summary>🚧 v0.9.0 Read-Only Database LOAD Support + Quoted Identifier Bugfix (Phases 63-64) -- REOPENED 2026-05-17 (Phase 63 shipped 2026-05-17, Phase 64 added pre-tag)</summary>

- [x] Phase 63: Read-Only Database LOAD Support (4/4 plans) -- completed 2026-05-17
- [ ] Phase 64: Fix CREATE SEMANTIC VIEW quoted identifier handling

### Phase 63: Read-Only Database LOAD Support

**Goal:** Allow `LOAD semantic_views` on a read-only DuckDB database so that previously-defined semantic views can be queried, while DDL fails naturally with DuckDB's standard read-only error.

**Requirements:** RO-01, RO-02, RO-03, RO-04, RO-05, DOC-01, DOC-02, DOC-03, DOC-04, DOC-05, TEST-01, TEST-02, TEST-03, REL-01

**Approach:**
1. Detect read-only access mode at LOAD via `SELECT current_setting('access_mode')` on the in-scope connection at `src/lib.rs:359`. Stash as a `bool is_read_only`.
2. When read-only, skip both writes inside `init_catalog` (`CREATE SCHEMA/TABLE` and the v0.1.0 companion-file migration at `src/catalog.rs:25-64`).
3. When read-only, probe for `semantic_layer._definitions` via `information_schema.tables` once at LOAD; stash a `catalog_table_present: bool` on `CatalogReader`. When the flag is false, `prepared_lookup` / `execute_list_all` / `execute_list_names` (`src/catalog.rs:216-293`) short-circuit to "empty" / "not found" without hitting the DB.
4. DDL emissions in `src/parse.rs:1914-2199` are unchanged — INSERT/DELETE/UPDATE against `_definitions` runs on the caller's connection and DuckDB returns its standard read-only error.

**Success criteria:**
1. `LOAD semantic_views` succeeds on a fresh read-only DB (no `_definitions` exists) and on a previously-bootstrapped read-only DB.
2. `list_semantic_views()` returns the bootstrapped rows in case (b), zero rows in case (a) — never a raw catalog error.
3. `describe_semantic_view('x')` and `FROM semantic_view('x', ...)` return clean "does not exist" errors when `x` isn't defined (vs raw catalog errors).
4. `CREATE` / `DROP` / `ALTER SEMANTIC VIEW` on a read-only DB fails with DuckDB's standard "cannot write to read-only database" error.
5. New `test/sql/readonly_load.test` sqllogictest and `test/integration/test_readonly_load.py` integration test pass; `just test-all` and `just ci` green.

**Artifacts produced:**
- Code: changes to `src/lib.rs`, `src/catalog.rs`.
- Tests: `test/sql/readonly_load.test`, `test/integration/test_readonly_load.py`.
- Docs: `CHANGELOG.md`, `docs/explanation/transactional-ddl-and-limitations.rst`, `docs/reference/create-semantic-view.rst`, `docs/reference/drop-semantic-view.rst`, `docs/reference/alter-semantic-view.rst`, `README.md`.
- Example: `examples/readonly_load.py`.
- Release: `Cargo.toml`, `description.yml` bumped to `0.9.0`; tag `v0.9.0` after squash-merge.

**Plans:** 4 plans

- [x] 63-01-PLAN.md — Core read-only support in Rust (RO-01..RO-05): is_read_only detection, init_catalog short-circuit, catalog_table_present probe, CatalogReader reader-path short-circuits, 5 unit tests
- [x] 63-02-PLAN.md — Tests + just-recipe wiring (TEST-01..TEST-03): test/integration/test_readonly_load.py (3 scenarios), test/sql/readonly_load.test (writable smoke; runner lacks readonly directive), justfile test-readonly recipe + test-all
- [x] 63-03-PLAN.md — Docs + example (DOC-01..DOC-05): CHANGELOG `[0.9.0]` section, transactional-ddl-and-limitations.rst Read-only databases section, three reference notes, README Quick start callout, examples/readonly_load.py
- [x] 63-04-PLAN.md — Release (REL-01): bump Cargo.toml + description.yml to 0.9.0; final just ci. Tag creation deferred to maintainer (out of scope for execute-phase).

### Phase 64: Fix CREATE SEMANTIC VIEW quoted identifier handling

**Goal:** Normalize quoted identifiers in `CREATE SEMANTIC VIEW` so semantic_view() lookup works regardless of how the view was created (quoted FQN, partial quoting, or unquoted short name), and prevent expansion from re-quoting already-quoted identifiers.

**Bug summary:**
- **Primary:** `CREATE OR REPLACE SEMANTIC VIEW "memory"."main"."orders_sv" AS ...` registers the view under the literal quoted string (`"memory"."main"."orders_sv"`, quotes and all) as the lookup key. `semantic_view('orders_sv', ...)` then fails to resolve.
- **Secondary (double-quoting in table-reference expansion):** when the stored name already contains quotes, expansion wraps each part in another pair of quotes, producing strings like `"""memory"""."""main"""."""orders_sv"""`. The expansion path treats the incoming name as a raw string and re-quotes it instead of treating it as a parsed identifier (parts list). Fixing storage normalisation (primary fix) likely makes this go away naturally; if not, expansion needs to operate on parsed parts, not the raw stored string.

**Scope:**
- Strip surrounding quotes during DDL parsing; split FQN into catalog/schema/name components.
- Store the unquoted short name as the lookup key (matching unquoted CREATE behaviour).
- Verify expansion no longer produces double/triple-quoted SQL; if storage normalisation alone is insufficient, switch expansion to consume parsed identifier parts rather than re-quoting a raw stored string.
- Error messages reference the unquoted name.

**Requirements**: QID-01, QID-02, QID-03, QID-04, QID-05, QID-06, QID-07
**Depends on:** None
**Plans:** 4 plans

Plans:
- [x] 64-01-PLAN.md — Identifier normalisation core (src/ident.rs with parse_qualified_identifier + normalize_view_name + find_identifier_end; unit tests + round-trip proptest)
- [x] 64-02-PLAN.md — Wire normalisation into parse.rs capture sites (extract_name_only, validate_create_body, extract_ddl_name, rewrite_alter inc. RENAME target) + runtime semantic_view() arg
- [x] 64-03-PLAN.md — Fix expansion re-quoting (quote_table_ref delegates to ident::parse_qualified_identifier) + qualify_and_quote_table_ref structural is-qualified test
- [x] 64-04-PLAN.md — sqllogictest acceptance fixture + TEST_LIST + fuzz seeds + CHANGELOG Fixed bullet + REQUIREMENTS append + TECH-DEBT entry; quality gate (just test-all + just ci)

**Acceptance reproduction (sqllogictest):**
- (a) CREATE with fully-quoted FQN (`"memory"."main"."orders_sv"`) + `semantic_view('orders_sv', ...)` lookup by short name succeeds.
- (b) CREATE with partially-quoted forms (e.g. `main."orders_sv"`, `"main".orders_sv`) resolves identically.
- (c) Round-trip via `GET_DDL` preserves identifier shape where appropriate.
- (d) Error messages on missing/duplicate names reference the unquoted name.

</details>
