rdbms-playground/docs/plans/20260525-adr-0035-sql-ddl-4a3.md

# Plan: ADR-0035 Phase 4, sub-phase 4a.3 — table-level / multi-column `CHECK`

The constraint slice's second (and final) half. Adds, to advanced-mode
SQL `CREATE TABLE`, the one constraint that needs a **new `__rdbms_*`
metadata table**: a **table-level `CHECK (<expr>)`** that can reference
several columns, e.g. `CREATE TABLE t (a int, b int, CHECK (a < b))`.
SQLite exposes **no PRAGMA for CHECK constraints**, so a table-level
CHECK cannot be read back from the engine and must live in metadata as
its source of truth (the ADR-0012/0013 pattern). Builds directly on the
4a/4a.2 `SqlCreateTable` command + grammar.

## 1. Baseline

- Tests: **1752 passing, 0 failing, 0 skipped, 1 ignored** (the
  `friendly/mod.rs` ` ```ignore ` doctest); clippy clean
  (`cargo clippy --all-targets -- -D warnings`). Branch `main`, last
  commit `1991fb4` (handoff-37). 4a.3 starts here.

## 2. Decisions locked with the user (do not re-litigate)

1. **New metadata table — `__rdbms_playground_table_checks`** (user
   confirmed 2026-05-25), focused/minimal, purpose-named like the
   existing metadata tables:

   ```sql
   __rdbms_playground_table_checks (
       table_name TEXT NOT NULL,
       seq        INT  NOT NULL,   -- declaration order
       check_expr TEXT NOT NULL,
       PRIMARY KEY (table_name, seq)
   ) STRICT;
   ```

   It is the **source of truth** for table-level CHECKs; `read_schema`
   reads them from here, not PRAGMA. Auto-filtered from `list_tables`
   by the `__rdbms_` prefix. A constraint `name` column is **not** added
   now — 4g's `ADD CONSTRAINT <name>` will add it when actually needed.
2. **Composite `UNIQUE` stays PRAGMA-detected** (user confirmed,
   2026-05-25): the PRAGMA/metadata split is principled — engine-
   reportable (UNIQUE, PK, FK, indexes) → PRAGMA; not reportable (CHECK,
   column + table) → metadata. No churn to shipped 4a.2 code.
3. **Stored as raw SQL text**, like 4a.2's column CHECK: `sql_expr` is
   validate-only (no `Expr` AST), so the builder captures the inner
   expression text by byte span via `capture_parenthesised_span`.
4. **One undo step**; structural execution reuses `do_create_table`,
   which writes the metadata rows inside its existing transaction.
5. **FK stays rejected** (4b). Only the table-level CHECK shape is
   lifted from the 4a "not yet supported" parse rejection.

## 3. Phase 1 — Requirements checklist (4a.3)

### Functional

- [ ] Table element `CHECK (<sql_expr>)` parses (advanced mode), in any
  position among the elements, accepting the full `sql_expr` surface.
- [ ] The builder distinguishes a **table-level** CHECK (no column-def
  open in the current element) from a **column-level** CHECK (after a
  column's type — 4a.2, unchanged). Depth-aware element-boundary
  detection (§4.2).
- [ ] Multiple table-level CHECKs in one statement, preserved in
  declaration order (the `seq` column).
- [ ] A table-level CHECK is **enforced** by the engine (a violating
  insert fails) and **survives a rebuild** (the part-D proof).
- [ ] The 4a/4a.2 "table-level CHECK not yet supported" parse-rejection
  is **lifted**; **FK stays rejected** (4b).
- [ ] Engine-neutral errors; `STRICT` preserved; one undo step.

### Cross-cutting / round-trip

- [ ] A table with one or more table-level CHECKs survives save → load
  → rebuild (DDL + enforcement). The new metadata table is the source
  of truth on read; `schema_to_ddl` re-emits the clauses on rebuild.
- [ ] `project.yaml` round-trips the CHECKs
  (`TableSchema.check_constraints`, YAML `#[serde(default)]`, optional
  on read — mirrors `unique_constraints`).
- [ ] `history.log` / replay unchanged (part of the same `create`
  write command).

### Testing (ADR-0008 four tiers)

- [ ] **Tier 1** (builder, `sql_create_table.rs`): table CHECK captured
  verbatim, distinct from a column CHECK; multiple table CHECKs ordered;
  table CHECK after a length-arg column + column CHECK (the depth probe);
  table CHECK after a table-level PK/UNIQUE; nested parens balanced;
  FK still rejected (update `table_level_check_and_fk_still_rejected`).
- [ ] **Tier 3** (`tests/sql_create_table.rs`): worker round-trip —
  table CHECK enforced (violating insert fails), **survives rebuild**;
  multiple CHECKs all enforced.
- [ ] **YAML round-trip unit test** for the metadata field.

## 4. Architecture & design

### 4.1 Grammar (`src/dsl/grammar/sql_create_table.rs`)

Add a table-level CHECK element, mirroring `TABLE_UNIQUE`:

```rust
static TABLE_CHECK_NODES: &[Node] = &[
    Node::Word(Word::keyword("check")),
    Node::Punct('('),
    Node::Subgrammar(&sql_expr::SQL_OR_EXPR),
    Node::Punct(')'),
];
const TABLE_CHECK: Node = Node::Seq(TABLE_CHECK_NODES);
```

Extend `ELEMENT_CHOICES`:
`&[TABLE_PK, TABLE_UNIQUE, TABLE_CHECK, COLUMN_DEF]`. Order note: a
column literally named `check` is already unavailable (it is a keyword
in the column-constraint set); `TABLE_CHECK` before `COLUMN_DEF` keeps
the table-level form winning at element start. (`COLUMN_DEF`'s own
`CHECK` lives inside `COL_CONSTRAINT_SUFFIX`, so the two never compete
for the same position.)

### 4.2 Builder distinguisher (the load-bearing mechanism)

`MatchedKind::Word` carries **no role or node provenance** (only
`Ident` carries `role`), so the table-level and column-level `check`
keywords are indistinguishable by kind. Distinguish by **element
position**, depth-aware:

- Track `depth` over the top-level item stream: `+1` on each `Punct('(')`
  that reaches the loop, `-1` on each `Punct(')')`. The column-list
  interior is `depth == 1`. (Parens consumed inside the `check` /
  `default` capture helpers and the table-`unique` sub-loop never reach
  the loop, so they don't perturb `depth`; the outer list parens, type
  length-args `(10, 2)`, and table-`PRIMARY KEY (a, b)` parens **do**,
  and balance.)
- Track `column_open: bool` — set `true` when a `col_type` / `double`
  finalises a column; reset `false` on a `Punct(',')` at `depth == 1`
  (an element separator).
- On `MatchedKind::Word("check")`: capture the parenthesised span as
  today; then **route by `column_open`** — `true` → column-level
  (`columns.last_mut().check_sql`, 4a.2 behaviour); `false` →
  table-level (push raw text onto `check_constraints`).

This is **verified by probe tests**, not reasoning — in particular the
`a numeric(10,2) check (a>0)` case (a naive "reset on any comma" would
misclassify it because the length-arg comma is at `depth == 2`).

The capture for a table-level CHECK reuses `capture_parenthesised_span`
(`src/dsl/grammar/ddl.rs`) unchanged — `CHECK ( … )` is paren-bounded.

### 4.3 Command AST (`src/dsl/command.rs`)

`Command::SqlCreateTable` gains `check_constraints: Vec<String>` (raw
inner SQL texts, declaration order), peer to `unique_constraints`.

### 4.4 Worker / DDL — both generators in lockstep (`src/db.rs`)

The two DDL generators **must** emit the table CHECK clauses
identically (the §6.1 rule; the 4a `serial` bug is the cautionary tale):

- **`do_create_table`** gains a `check_constraints: &[String]` param;
  emits `, CHECK (expr)` table clauses (after composite UNIQUE, before
  `STRICT`), and **writes the `__rdbms_playground_table_checks` rows**
  (one per CHECK, `seq` = index) inside its existing transaction.
- **`schema_to_ddl`** emits the same clauses from
  `ReadSchema.check_constraints`.
- **`configure_connection`** creates the new metadata table alongside
  the existing `__rdbms_*` tables.
- **`read_schema`** + **`read_schema_snapshot`** read the CHECKs from
  the metadata table (ordered by `seq`) into `ReadSchema` /
  `SchemaSnapshot` (→ `TableSchema.check_constraints`).
- **`Request::SqlCreateTable`** dispatch passes the new field through to
  `do_create_table`; `snapshot_then` wrapping unchanged (one undo step).
- A table drop / rebuild must clear/repopulate the metadata rows — verify
  the existing drop path clears `__rdbms_*` rows for the table (it does
  for columns/relationships); extend it to the new table.

### 4.5 Persistence round-trip

- `TableSchema.check_constraints: Vec<String>` (`src/persistence/mod.rs`).
- `RawTable.check_constraints` with `#[serde(default)]`;
  `write_table` emits only when non-empty; `parse_schema` maps it —
  all mirroring `unique_constraints` exactly.

### 4.6 Friendly catalog / keys

Update the `ddl.sql_create_table` help body and
`parse.usage.sql_create_table` usage skeleton to show the table-level
`CHECK (…)` form. No new keys expected (the parse error for a still-
rejected shape reuses existing keys); if any new diagnostic key is
added, keep `keys.rs` ↔ `en-US.yaml` in lockstep (the validator test)
and engine-neutral (the vocab audit).

## 5. Out of 4a.3 scope

- FK (4b); `DROP` (4c); indexes (4d); `ALTER` (4e–4h).
- `CONSTRAINT <name> CHECK (…)` (named constraints) → 4g (adds a `name`
  column to the metadata table then).

## 6. Open items / implementer calls

1. **Builder distinguisher** (§4.2) — depth-aware; settle by the probe
   tests in step 2 before relying on it.
2. **Drop / rebuild cleanup of the new metadata rows** — confirm by
   test that dropping (and rebuilding) a table leaves no orphan CHECK
   rows and repopulates correctly.
3. **CHECK column-validation at create time** — table CHECKs reference
   columns being defined (not yet in the schema cache); confirm by test
   they raise no spurious unknown-column `[ERR]` (mirror the 4a.2
   column-CHECK finding; it was fine there).

## 7. Devil's Advocate review of this plan

- **Why a new table at all — is CHECK really unreportable?** Yes;
  SQLite has no PRAGMA for CHECK (column or table). 4a.2's *column*
  CHECK only round-tripped because it was already stored in
  `__rdbms_playground_columns.check_expr` for the same reason. A
  table-level CHECK has no column to hang on, hence the new table. ✓
- **Two DDL generators in sync?** The plan emits the CHECK clauses in
  **both** `do_create_table` and `schema_to_ddl` and adds a
  survives-rebuild test — the exact safety net the 4a `serial` drift
  proved necessary. ✓
- **Distinguisher robust?** The naive comma reset is explicitly
  rejected (length-arg / table-PK inner commas); depth-aware detection
  is probe-tested, including the `numeric(10,2) check(...)` trap. ✓
- **Silent scope creep?** FK stays rejected (4b); named CHECK is 4g;
  composite UNIQUE deliberately stays on PRAGMA (user-confirmed). ✓
- **Round-trip detectable on read?** The metadata table is read by both
  `read_schema` and `read_schema_snapshot`; YAML mirrors
  `unique_constraints`. ✓
- **Tests first?** §8 orders failing tests before code at every step. ✓

## 8. Implementation sequence (test-first)

1. **Builder probe + Tier-1** — write the distinguisher tests (table
   CHECK captured + ordered; the `numeric(10,2) check(...)` depth trap;
   table CHECK after table PK/UNIQUE; nested parens; column CHECK still
   column-level; update `table_level_check_and_fk_still_rejected` so
   table CHECK is accepted and **FK stays rejected**) → red → add
   `TABLE_CHECK` grammar + the depth-aware builder branch +
   `Command.check_constraints` → green. *(Compiles once the worker
   dispatch threads the new field; steps 1–2 land together.)*
2. **Worker + metadata table** — write Tier-3 (`tests/sql_create_table.rs`):
   table CHECK enforced (violating insert fails); multiple CHECKs; the
   metadata rows present → red → add `__rdbms_playground_table_checks`
   in `configure_connection`, the `do_create_table` emission + metadata
   writes, the `read_schema` / `read_schema_snapshot` reads, and the
   drop-path cleanup → green.
3. **Round-trip** — extend `TableSchema` + YAML + `schema_to_ddl` →
   Tier-3 survives-rebuild test + a YAML round-trip unit test → green.
4. **Catalog** — update help/usage bodies; run
   `keys_validate_against_catalog` + the vocab audit → green.
5. **Full sweep** — `cargo test` (no regressions from 1752) +
   `cargo clippy --all-targets -- -D warnings`.
6. **Docs** — ADR §13 already records 4a.3; update `requirements.md`
   Q1 note; flip nothing (ADR already Accepted). Propose the commit
   message; wait for approval.

## 9. Exit gate

- All §3 checklist items satisfied; four tiers green, zero skips; no
  regression from the 1752 baseline; written DA pass on the delivered
  slice; clippy clean.