rdbms-playground/docs/requirements.md

RDBMS Playground — Requirements (Phase 1)

This document is the consolidated Phase 1 requirements checklist for RDBMS Playground. It captures everything the project has committed to so far, derived from the design conversation and the ADRs in docs/adr/.

Purpose. Phase 5 verification at every milestone measures delivered work against this checklist. An item not on this list was not promised; an item silently dropped without confirmation is a process failure.

Scope. The list is intentionally coarse — each item is a unit of "satisfied / not satisfied" judgement. When an item is taken up for implementation, it is decomposed further in a backlog (initially in this repo, now tracked as Gitea issues).

Status legend

• [ ] — open, not yet started
• [/] — partial / in progress: some of it is built and tested, but named gaps remain. The entry states what works and what is still missing. (Distinct from [ ], which is genuinely untouched, and from [~], which is deliberately deferred pending an ADR rather than half-built.)
• [x] — satisfied (implemented + tested)
• [~] — deferred, awaiting an ADR or further design before any implementation
• [-] — explicitly out of scope (rationale at the bottom)

Reconciliation note (2026-06-07). A full audit of every [ ] item against the source found ~46 % of them mis-marked — overwhelmingly under-claimed (e.g. tab completion I3 and syntax highlighting I4 were shipped but marked "not yet implemented"). The binary legend was the root cause: a shipped feature, a half-built one, and an untouched one all wore the same [ ]. The [/] marker above was added to fix this, and the audited items were re-marked. When you implement against an item, move it [ ] → [/] → [x] rather than jumping straight to [x], and keep the gap note current.

Test baseline

After ADR-0022 Amendment 6 (the curated SQL function-name list — issues #15 tab-completion + #16 typing-time typo hint): 1538 lib unit tests passing, 0 failing, 1 ignored (cargo test --lib; the full cargo test across every binary is 2107 passing, 0 failing, 1 ignored — the one ignored is a long-standing ```ignore doc-test in src/friendly/mod.rs). Clippy clean with the nursery lint group enabled. (Earlier reference points — lib counts: 1131 after the ADR-0027 highlight / hint follow-up + the optional-trailing-flag / --resume manual-testing fixes; 1100 after ADR-0027's initial ship; 1079 after ADR-0026 (complex WHERE expressions); 1039 after ADR-0025 (indexes); 1006 after ADR-0024 + the handoff-14 cleanup; 449 after B2/C2. Note the intervening issue fixes #8/#13/#12/#7/#9 landed tests without a baseline bump; this is the first refresh since ADR-0027.)

---

Distribution and install

• D1 Cross-platform binaries: Linux, macOS, Windows on x86_64 and aarch64. (Done 2026-06-15 — CI produces all six. The four non-macOS targets (Linux musl + Windows gnu/gnullvm × x86_64/aarch64) are cross-built from the Linux runner with cargo-zigbuild on a v* tag (release.yaml); the two *-apple-darwin targets build natively on a Tart Apple-Silicon runner via the dispatched release-macos.yaml. All uploaded to the Gitea release with a .sha256 each. Decisions in docs/ci/adr/ (ADR-ci-001/002/003). Runtime-verified by the user: Linux x86_64 + Windows aarch64; the others are link-clean / valid format.)
• D2 Single static binary, no runtime dependencies. (Done 2026-06-15, per platform: Linux is fully static (musl + crt-static); Windows is a standalone .exe (Zig statically links libc — no mingw runtime DLLs); macOS links only system libraries (libSystem + the AppKit/Foundation frameworks — inherent on every Mac, never user-installed; the build rewrites the one nix-store libiconv path to /usr/lib and re-signs ad-hoc). No target requires anything the user must install. ADR-ci-003.)
• D3 Released via prebuilt binaries plus Homebrew, Scoop, winget, and cargo binstall. (Prebuilt binaries + checksums now published to Gitea releases (D1); the package-manager manifests (Homebrew / Scoop / winget / cargo binstall) remain to do. The asset naming rdbms-playground-<tag>-<target> is already binstall-friendly. Tracked under ADR-ci-003 "Deferred".)

TUI shell

• S1 Three-region layout: items list (left), output panel (right), input field (bottom). (Verified 2026-06-07: ui.rs:26-58 lays out a horizontal split — items panel left, right column subdivided into output panel / input field / hint panel; rendered every frame. ADR-0046 evolves this: the left items region becomes width-optional — hidden by default at ≤ 90 columns, peek-revealed via Ctrl-O navigation mode — so the three-region layout is the wide-terminal default, not an invariant.)
• S2 Items list shows tables and per-table indexes; designed to extend to additional element kinds (relations, views, etc.) without restructuring. (ADR-0025: the items panel renders a nested list — each table with its index names indented beneath it. The nested model is the extension point for future element kinds. ADR-0046 overrides the nesting approach for relationships: because relationships are cross-table rather than per-table, they get their own sibling panel stacked below the tables list, not nested items within it — user-confirmed 2026-06-10.)
• S3 Output panel renders a visualization of the currently selected item. (Satisfied: single-element structure visualisation renders (output_render.rs:82-180) — select a table, see its columns / types / keys. Multi-tab clause withdrawn 2026-06-11 (user decision): the original wording promised "and supports multiple tabs", but the output model is settling on the single scrollable V4 journal rather than switchable tabs, so the tab clause is dropped from tracked scope. A future return to tabbed output would be a fresh requirement, not this one. Same withdrawal as V2.)
• S4 Hint area below the input field, showing hints about the current input or last error. (Verified 2026-06-07: ui.rs:1088-1110 render_hint_panel / resolve_hint_lines — a dynamic 1–MAX_HINT_ROWS panel below the input showing ambient hints, candidates, or the last error. Correction (2026-06-10, ADR-0046): the original wording said the area was "keyboard-toggleable"; that was never implemented and is deliberately dropped — the panel became indispensable once completion moved into it (ADR-0022), so it is always on. ADR-0046 replaces its content-driven height with a geometry-driven one to stop the resize jump (#20); no toggle is added.)
• S5 Mode label and distinct border style on the input field communicate the current input mode at all times. (Verified 2026-06-07: ui.rs:896-934 render_input_panel — a coloured, bold mode label plus a mode-distinct border colour (border vs border_advanced), tracking the three-way EffectiveMode incl. the one-shot : state.)
• S6 Input-field validity indicator: a debounced [ERR] / [WRN] marker at the right edge of the input row, summarising — before submit — whether the current command would run. Backed by a walker diagnostics-severity model (ERROR / WARNING). Advisory only — never blocks submission. (ADR-0027: Severity / Diagnostic on WalkResult; input_verdict combines the parse outcome, schema-existence ERRORs — unknown table / column — and the ADR-0026 §7 expression WARNINGs — type mismatch, = NULL. The runtime debounces the indicator's display ~1 s; the rightmost six columns of the input row are reserved unconditionally. New warning theme colour. A follow-up pass completed §2's highlight + hint wiring — diagnostics overlaid on the input field and surfaced in the hint panel, with precise per-literal WARNING spans — and Amendment 1 adds a LIKE-on-numeric-column WARNING.)

Input field

• I1 Multi-line entry that auto-expands; Ctrl-Enter (or equivalent) submits, plain Enter inserts a newline.
• I1a In-line cursor editing in the input field: Left / Right arrows move the cursor by character (UTF-8 boundaries honoured), Home / End jump to the extremes, Delete removes the character at the cursor, Backspace removes the character before. Insertion happens at the cursor position. (Verified 2026-06-07: app.rs:973-1005 cursor_left / cursor_right (with is_char_boundary checks), Home/End, Delete, Backspace, insert_at_cursor. This is single-line cursor editing and is complete on its own terms; the separate multi-line entry goal is tracked under I1, which is genuinely not started.)
• I1b Readline-style cursor shortcuts: Ctrl-A / Ctrl-E as aliases for Home / End for users on keyboards without those keys (and for ergonomics in command-driven workflows). Likely followed by Ctrl-W (delete previous word), Ctrl-K (delete to end), Ctrl-U (delete to start). (Done 2026-06-12 — ADR-0049, issue #29: the full set — Esc-clear + Ctrl-A/E/W/K/U — wired in App::handle_key (src/app.rs) with helpers clear_input / delete_prev_word / kill_to_end / kill_to_start; Esc clears only when no completion memo is alive (the memo wins first, ADR-0022); cursor-only keys leave history navigation intact, kill keys end it; 22 Tier-1 tests. On-screen advertisement of these keys is issue #27's bottom-status-line work.)
• I2 Persistent navigable input history (project-scoped). (Implemented across Iterations 2 + 6: per-command append to history.log (Iter 2); on project open, the in-memory navigable history is hydrated from the tail of history.log up to the same in-memory cap (Iter 6). Global rolling history is out of scope per OOS-6 / N4.)
• I3 Tab completion for app commands, DSL keywords, table names, column names, and SQL keywords. (Verified 2026-06-07 — this was mis-marked [ ] despite being shipped: src/completion.rs is 2852 lines with ~100 tests; app.rs:898 binds Tab → completion_tab_forward (and BackTab → _backward) with forward/backward cycling through a candidate memo and a colour-coded candidate line in the hint panel (ui.rs:1125 render_candidate_line). All five candidate categories work — app commands (via REGISTRY), DSL keywords (walker Expectation), table names + column names (SchemaCache), and SQL keywords/functions in advanced mode. Refinement 2026-05-30, issue #15: SQL expression slots (sql_expr_ident) also offer a curated set of SQL function names — KNOWN_SQL_FUNCTIONS in src/dsl/sql_functions.rs, surfaced as CandidateKind::Function (ADR-0022 Amendment 6). The original "broad tab-completion" goal is met; any further polish is incremental, not a missing core.)
• I4 Syntax highlighting for both the DSL and SQL. (Verified 2026-06-07 — mis-marked [ ] despite being shipped: input_render.rs:64-113 lexes the input to styled byte-range runs (lex_to_runs_in_mode) and renders them per-mode (DSL in simple, SQL in advanced), with nine token classes in theme.rs (tok_keyword, tok_identifier, tok_string, tok_punct, tok_flag, tok_error, tok_function, tok_type) and diagnostics overlaid (error/warning spans). Both surfaces are highlighted; the core goal is met. Refinement 2026-05-29, issue #8: column data types now carry a dedicated HighlightClass::Type / tok_type colour, distinct from identifiers and clause keywords — ADR-0022 Amendment 4; a further refinement 2026-05-30, issue #15: SQL function-name candidates carry a dedicated tok_function colour (the ninth Theme token colour, ADR-0022 Amendment 6). The broad highlighting goal stays open.)
• I5 In-flight query/command cancellation (Ctrl-C in the output area or input field).

Input modes (per ADR-0003)

• M1 Simple mode is the default. It accepts DSL data commands and the canonical app-level commands; raw SQL is rejected with a friendly hint.
• M2 Advanced mode accepts SQL plus the canonical app-level commands without any sigil.
• M3 Prefixing a single line with : in simple mode is a one-shot advanced escape (with the prompt label updated). The mode simple / mode advanced command switches modes persistently.
• M4 Execution-time mode side-channel — implemented via ADR-0037 (the channel) and its motivating consumer ADR-0038 (the DSL → SQL teaching echo). Every command knows, at execution time, which of three modes it ran under — the three-way EffectiveMode { Simple, AdvancedPersistent, AdvancedOneShot } resolves at submit time and threads through Action::ExecuteDsl → runtime; the persistent Mode enum stays two-way (the transient one-shot : lives on the channel where it belongs, not in persistent state). The runtime gates the ADR-0030 §10 teaching echo on it: a DSL-form command run in advanced/one-shot mode renders the equivalent advanced-mode SQL beneath the command's echo line (the [ok] summary it once sat under was retired by ADR-0040, issue #9 — the echo line now carries a ✓/✗ completion marker instead); simple-mode and SQL-entered submissions stay silent. Echo coverage: ADR-0038 is feature-complete — every catalogue row in §7 round-trips per line through the advanced walker (the §1 copy-paste contract; §6 category 2 holds it per line), every §6 category-3 line surfaces, and the §4 de-emphasised styled-runs rendering polish (ADR-0028) is wired. Shipped across four feature commits: Phase 1 Bucket A — single-statement DDL + show data + --all-rows fall-throughs (90479cb); Phase 2 Bucket B — resolved-name + multi-line echoes (add index auto- and user-named, positional drop index, add/drop relationship in both selector forms, drop column --cascade, add relationship --create-fk) (275c726); Phase 3 category-3 prose — shortid generation and type-conversion transforms via the pre-existing client_side.auto_fill_* / client_side.transformed* notes, plus the new change column --dont-convert caveat (e6ad1ae); Phase 4 styled-runs polish — OutputKind::TeachingEcho custom rendering branch (dimmed Executing SQL: prefix + the SQL re-lexed via input_render::lex_to_runs_in_mode(Advanced) for token highlighting, same as the input echo), OutputStyleClass::Hint for every cat-3 prose line (caveat + the existing illuminating notes — broader scope, visually consistent) (2aab457).

App-level commands (per ADR-0003)

• A1 All canonical app-level commands implemented and available in both modes: save, save as, load, new, rebuild, export, import, seed, replay, undo, redo, mode, help, hint, quit. (Done 2026-06-15: the last command, hint, landed with H2 (ADR-0053). All 15 canonical app commands are now registered and available in both modes.)

DSL data commands

• C1 Table operations: create / drop / rename. (Verified 2026-06-07: create + drop done; rename done on the advanced surface — ALTER TABLE … RENAME TO, ADR-0035 §6 / 4h (do_rename_table, db.rs:4821). A simple-mode rename-table verb is deliberately not provided — table rename is advanced-mode only — so the requirement is satisfied as designed, not partial.)
• C2 Column operations: add / drop / rename / change type. drop column and rename column use SQLite native ALTER TABLE (3.35+ / 3.25+); change column routes through the rebuild-table primitive since ALTER doesn't support type changes. PK and relationship-involved columns are refused with friendly messages (drop the relationship first); SQLite STRICT enforces type compatibility on the data copy during a type change.
• C3 Schema constraints: primary key (single and compound), foreign key with ON DELETE / ON UPDATE referential actions, indexes, NOT NULL, UNIQUE, CHECK, DEFAULT. (PK including compound done at create-table time; FK with ON DELETE / ON UPDATE actions done (ADR-0013) — declared via add 1:n relationship; symmetric outbound + inbound view in the structure renderer; type compatibility validated at declaration via Type::fk_target_type(). Indexes done (ADR-0025) — add index / drop index, rebuild-preserving, persisted in project.yaml; UNIQUE indexes added on the advanced-mode SQL surface (CREATE UNIQUE INDEX, ADR-0035 §4d / ADR-0025 Amendment 1; simple-mode add unique index deferred). NOT NULL / UNIQUE / CHECK / DEFAULT done (ADR-0029) — a constraint suffix on create table / add column, plus add constraint / drop constraint on existing columns; populated-column additions are guarded by a pre-flight dry-run that refuses with a table of offending rows.)
• [~] C3a Modify relationship: modify relationship <name> [on delete <action>] [on update <action>]. Users can achieve the same via drop + add today; one-step modify is a small follow-up using the existing rebuild-table machinery. ADR pending.
• C4 Convenience: create m:n relationship from <T1> to <T2> produces an auto-named junction table the user can rename; pulls primary keys and FK definitions automatically. (Done 2026-06-10 via ADR-0045. create m:n relationship from <T1> to <T2> [as <name>] builds a junction table with one FK column per parent PK column ({table}_{pkcol}, typed via fk_target_type), a compound PK over them, and two CASCADE 1:n relationships — all in one do_create_table call = one undo step. Auto-named {T1}_{T2} (optional as), available in both modes, compound-parent PKs supported (ADR-0043). Self-referential m:n refused; PK-less parent refused. Wired across every surface — completion (m:n composite), hints, highlighting, help/usage, and the advanced-mode DSL→SQL teaching echo (the generated CREATE TABLE … FOREIGN KEY …). 9 integration + 7 typing-surface + echo/parse unit tests. The build surfaced — and fixed — two latent simple-mode dispatch/completion assumptions ("≤1 DSL form per entry word"), now generalized behaviour-preservingly.)
• C5 Data operations: insert / update / delete via DSL. (ADR-0014. INSERT short and long forms, UPDATE/DELETE with required WHERE plus --all-rows opt-in, show data <T>, per-column-type value-literal validation, FK enforcement with metadata-driven error enrichment, auto-show after writes. Bulk insert, complex WHERE expressions, and SELECT in advanced mode are explicitly tracked separately — see C5a below.)
• C5a Complex WHERE expressions (AND/OR, comparison operators, LIKE, IS NULL, IN, BETWEEN) for UPDATE/DELETE/ show-data filtering; show data also gains where and limit. (ADR-0026 steps 1–4: the stratified expression grammar reached through a new Subgrammar node, the recursive Expr AST + build_expr, wiring into update / delete / show data, and Expr → parameterised SQL with an implicit primary-key ORDER BY for limit. Type-mismatched WHERE comparisons are permissive — they run rather than being rejected (§7). The §7 advisory flagging of type mismatches / = NULL is the seam with ADR-0027's diagnostics-severity model and is tracked there — see ADR-0026 "As-built notes".)

SQL handling

• Q1 SQL parsed via sqlparser-rs; supported subset is defined (specifics deferred to a future ADR). *(Progress: the advanced-mode SQL surface is authored as grammar within the unified grammar tree (ADR-0030 / ADR-0024) and parsed by the existing walker — not a separate batch parser — so SQL gets the same completion / highlighting / hints as the DSL (ADR-0001's sqlparser-rs reservation is superseded). Implemented so far: full SELECT (ADR-0032), INSERT / UPDATE / DELETE (ADR-0033), and CREATE TABLE (ADR-0035, 2026-05-25 — executed structurally: columns
  • types + NOT NULL/UNIQUE/PRIMARY KEY + IF NOT EXISTS (4a), then per-column DEFAULT/CHECK (raw sql_expr text) and composite UNIQUE(a,b) (4a.2), then table-level/multi-column CHECK (4a.3 — round-trips via the new __rdbms_playground_table_checks metadata table, since the engine reports no CHECK constraints), then foreign keys (4b — inline REFERENCES + table-level FOREIGN KEY → ADR-0013 named relationships in the create transaction; self-references and bare REFERENCES <parent> supported), then DROP TABLE [IF EXISTS] (4c — reuses do_drop_table; IF EXISTS is a no-op-with-note), then CREATE [UNIQUE] INDEX / DROP INDEX [IF EXISTS] (4d — reuse do_add_index/do_drop_index; CREATE UNIQUE INDEX admitted in advanced mode via the IndexSchema.unique flag, ADR-0025 Amendment 1), then ALTER TABLE add/drop/rename column (4e — alter is advanced-only, runtime-decomposed to the existing column executors; ADD COLUMN reaches CREATE-TABLE constraint parity; drop/rename refuse a table-CHECK- referenced column), then ALTER TABLE … ALTER COLUMN TYPE (4f — runtime-decomposed to change_column_type with ForceConversion, the §7 advanced policy: lossy converts with a note, incompatible + static refusals (↔ blob, non-int → serial) refuse, int → serial allowed; the internal-__rdbms_* guard folded into do_change_column_type), then ALTER TABLE add/drop constraint + add FK (4g — ADD [CONSTRAINT <name>] (CHECK | UNIQUE | FOREIGN KEY) + DROP CONSTRAINT <name>; ADD = CHECK + composite UNIQUE + FK (PRIMARY KEY + named UNIQUE refused); table-CHECK/UNIQUE rebuild with a dry-run guard, FK reuses add_relationship; named table-CHECKs round-trip via a rebuild-only name column on __rdbms_playground_table_checks + a project.yaml check_constraints {expr, name} extension; the internal-table guard completed across do_add_constraint/do_add_relationship)). then ALTER TABLE … RENAME TO (4h — the one genuinely new low-level op, do_rename_table: native rename + one-transaction reconciliation of the CSV file and every metadata row naming the table, incl. rewriting CHECK text that qualifies a column with the old table name so a fresh rebuild round-trips; refuses same-name / existing-target / __rdbms_* / non-existent; auto-named indexes + relationships kept stale per §6 scope; one undo step), then the 4i verification sweep (shared-entry- word completion merge + simple/advanced completion colour; describe of table-level constraints; self-ref FK pre-submit indicator; CREATE-TABLE help/usage refresh). ADR-0035 Phase 4 (4a–4i) is complete.)*
• Q2 Non-standard syntax rejected with a clear message pointing at the supported subset. (Design done — ADR-0030 §8: out-of-subset statements are refused with an engine-neutral message naming the construct. Implementation pending.)
• Q3 User-facing simplified types map transparently to SQLite STRICT types in generated DDL. (All ten types implemented and tested.)
• Q4 Supported SQL subset specification — ADR-0030. Advanced mode is a standard-SQL surface, engine-neutral; the supported surface — SELECT (full query surface), INSERT / UPDATE / DELETE, CREATE / DROP / ALTER TABLE, CREATE / DROP INDEX — is authored as grammar in the unified tree. DDL routes through the typed Command executor (metadata + the playground type vocabulary preserved); DML and SELECT execute as validated SQL. Q1's implementation is now unblocked.

Database backend (per ADR-0002)

• B1 SQLite via rusqlite; all tables created STRICT; PRAGMA foreign_keys = ON per connection. (Database accessed through a dedicated worker thread per ADR-0010.)
• B2 Schema evolution uses the rebuild-table technique internally where SQLite ALTER TABLE cannot — currently the change-column-type code path. Add-column, drop-column, and rename-column take the simpler ALTER TABLE route since modern SQLite supports them natively; metadata sync into __rdbms_playground_columns and __rdbms_playground_relationships happens in the same transaction either way.
• B3 Query timeout and cancellation supported (no cartesian-join-of-doom can hang the app). (Progress: the worker-thread architecture is in place; the cancellation/timeout protocol on top of it is pending.)

Type system (per ADR-0005)

• T1 All ten user-facing types implemented: text, int, real, decimal, bool, date, datetime, blob, serial, shortid. (Mapping to SQLite STRICT covered by ADR-0005; FK target type rule by ADR-0011.)
• T2 shortid generation: base58, 10–12 characters, omits ambiguous characters; generated client-side at insert. (Implemented per ADR-0014; auto-fills omitted shortid columns and validates user-supplied values against the same alphabet and length range.)
• T3 Compound primary keys handled end-to-end (DSL, storage, display, FK reference). (Done 2026-06-09 via ADR-0043: the FK-reference leg now works on both surfaces — DSL add 1:n relationship from P.(a, b) to C.(x, y) and SQL FOREIGN KEY (a, b) REFERENCES P(x, y) (bare REFERENCES P auto-expands to the full PK). References the parent's full compound PK matched positionally, per-pair type-compat (ADR-0011); the FK is engine-enforced, persisted (columns: [a, b] in project.yaml, comma-joined in metadata), shown symmetrically by describe, and --create-fk creates one child column per parent PK column. The relationship model went list-based through all six layers, single-column behaviour preserved (commit b14f019); 12 integration tests in tests/it/compound_fk.rs plus the existing single-column suite as the regression net. The earlier-noted (2026-06-07) breakdown: compound-PK declaration (with pk a(int),b(int)), storage (primary_key: Vec<String>), and display were already present and tested. The FK-reference leg — once an ADR-first ~15–20-site change across the relationship model — is what ADR-0043 delivered (back-compat dropped by user decision, so no migration was needed). Subset / non-PK (UNIQUE-target) FK references stay out of scope.)

Visualizations

• V1 Single-element views render in the output pane: a selected table as its structure (columns, types, keys, constraints); a selected relationship as two tables joined by a line. (Done 2026-06-10 — ADR-0044. The table-structure half shipped earlier; the relationship-as-line-art half (ADR-0016 OOS-1) now renders as Style-A two-table connector diagrams wherever a relationship is the subject: show relationship <name> (full structure boxes), show table <T> and add/drop relationship echoes (focal box + compact stacked diagrams). Child-left / parent-right, n…1 cardinality, referential actions, bold title rows; rendered App-side and width-aware (side-by-side ↔ vertical fallback). Compound FKs route a shared bus + an explicit (a, b) ▶ P.(x, y) pairing line; self-referential FKs draw two same-named boxes. Incidental DDL echoes keep the prose form (the "relationship-relevant" reach). The §3 last-resort helper line was considered and rejected. Two /runda passes (design + implementation). Selection-nav and the broader journal direction remain in V4.)
• V2 SQL query results render as a dynamic table view in the output pane. (Satisfied: the table view is done — output_render.rs:38-72 render_data_table renders a box-drawing frame with aligned columns (numeric right, text left) and NULL/control-char sanitisation, for show data and after every write (ADR-0014). Multi-tab clause withdrawn 2026-06-11 (user decision): the original wording promised "with multiple result tabs supported"; retained multi-result output, if ever wanted, now belongs to the single scrollable V4 journal direction rather than switchable tabs, so the tab clause is dropped from tracked scope. A future return would be a new requirement. Same withdrawal as S3.)
• [~] V3 Full ER-diagram export (whole-database graph, viewed outside the TUI) — low priority; design and ADR pending.
• [~] V4 Output panel as a scrollable per-session log with inline rich rendering. Direction agreed in conversation: the output area is a chronological journal of operations and selections (e.g. a "selected table X" entry with the rendered structure underneath); structure renderings choose between a compact ASCII-table form and a vertical line-per-column form based on dimensions; the log is exportable to Markdown so learners can keep a record of their session. Design and ADR pending before any implementation. (Partial: PageUp / PageDown scrolling of the existing line buffer is in, with new output snapping the view to the most recent. The full V4 scope — smart structure rendering, log styling, Markdown export, scroll indicator — remains pending. As of 2026-06-11 this journal model is the sole tracked direction for evolving the output pane: the competing multi-tab output alternative (the trailing clauses of S3 and V2) was withdrawn from scope by user decision, so retained / multi-result output, if pursued, is folded into this journal rather than into switchable tabs.)
• V5 show <kind> [<name>] family of commands for redisplaying schema info on demand. (Done 2026-06-07: show table <name> + show data <Table> (single-item) plus the list-all family show tables / show relationships / show indexes — the latter three landed as Command::ShowList { kind } (one variant, grammar/data.rs), a read-only worker show_list formatting count-headed lists from the same helpers the items panel uses, with help + parse-usage entries and 10 integration tests (tests/it/show_list.rs). The one remaining member of the [<name>] clause — singular per-item detail for relationships/indexes — is split out as V5a below so it has a tracked home rather than living as a footnote here.)
• V5a Singular per-item detail views show relationship <name> / show index <name> — the [<name>] half of V5 for the relationship and index kinds (the table kind already has show table <name>). (Done 2026-06-07: folded into Command::ShowList { kind, name: Option<String> } — name: Some(_) is the singular form. Two grammar branches (relationship <name> / index <name>, reusing the Relationships/Indexes completion sources for the name slot), a worker do_show_one rendering a labelled detail block (endpoints + ON DELETE/UPDATE for a relationship; table, columns, uniqueness for an index) or a friendly "No relationship/ index named X." line, reusing the V5 ShowList render path. Help + parse-usage entries + two ADR-0042 near-miss matrix rows; 5 added integration tests. V5's [<name>] clause is now complete across all three kinds.)
• V6 Copy the output panel to the system clipboard (issue #11, ADR-0041). copy / copy all copy the whole panel; copy last copies the most recent command's output. Delivery is OSC 52 (SSH-friendly, no native dep) plus a best-effort native write (arboard), always both; the payload is the panel's plain text exactly as rendered. Removes the terminal-select-and-fight-wrapping friction of filing bug reports. (Complements V4's planned Markdown export — a different "get the session out" path.)

Project lifecycle (per ADR-0004)

• P1 Auto-named temp project on startup under <data-root>/projects/. OS-standard data root via directories crate; --data-dir overrides (Iteration 1).
• P2 save / save as elevate / copy + switch (Iteration 4b). save on a named project reports "already auto-saved".
• P3 Auto-save: per-command write-through to YAML + CSV + history.log inside the SQLite tx with commit-db-last ordering (Iteration 2). No dirty state.
• P4 load opens an in-TUI picker, sorted newest first, with [TEMP] markers and a b-to-browse path-entry sub-mode (Iteration 4b).
• P5 Existence-only load + explicit rebuild command with confirmation modal (Iterations 3 + 4a).
• P-NAME-1 Temp project directory naming pattern: <YYYYMMDD>-[temp]-<word>-<word>-<word> from a 161-word built-in list (Iterations 1 + 4b). Bracketed [temp] marker is unambiguous against user-named projects because validate_user_name rejects brackets.
• P-NAME-2 Display-name prettifier strips YYYYMMDD- AND [temp]-; splits kebab / snake / camel; title-cases each word.
• P-NAME-3 Status bar shows Project: [TEMP] <name> for temp projects, Project: <name> for named.
• P-CLEAN-1 Unmodified empty temp projects are auto-deleted on switch and quit, gated by safely_delete_temp_project's stacked guards (containment, symlink rejection, [temp] marker, contents allowlist).

Project file format (per ADR-0004)

• F1 project.yaml with version: 1 field carries schema (ordered tables + columns), relationships, and created_at. data/<table>.csv carries table data (UTF-8, header row, RFC 4180; NULL distinct from empty string) (Iteration 2). Empty tables produce no CSV.
• F2 .gitignore template (/playground.db, /.rdbms-playground.lock, /project.yaml.v*.bak) created in each new project (Iteration 1). Per ADR-0007 amendment 1, history.log is NOT in the template — user decides whether to commit it.
• F3 Migration framework scaffold (Iteration 6). MigratorRegistry + migrate_to_latest + ensure_project_yaml_migrated are wired into every project open; no migrators registered in v1 (the production registry is empty). The framework is exercised by tests that inject a fake v1→v2 migrator: registry plumbing, .v<N>.bak backup, version-bump sanity check, and newer-than-supported / malformed-version errors are all covered. The first real migrator (when v2 ships) is a one-file change.

Undo and replay (per ADR-0006)

• U1 Auto-snapshot before every data/schema mutation (DSL + SQL) into a persisted ring buffer (size N=50, tunable), per ADR-0006 Amendment 1 (single-step undo, superseding the original destructive-only model). Snapshot is a hybrid whole-project copy (database via online backup API + project.yaml / data/*.csv as files); staged before the mutation's transaction, finalised after the db commit (preserves ADR-0015 §6). A batch command (replay / future batch ops) records one boundary snapshot; import takes none. A --no-undo CLI flag disables snapshotting. (Implemented 2026-05-24, plan docs/plans/20260524-adr-0006-undo-snapshots.md: src/undo.rs ring + worker dispatch hook in src/db.rs; snapshots gated on a user command source so internal ops like open-time rebuild are not recorded; snapshot-bookkeeping failures are non-fatal, restore failures surface.)
• U2 undo restores the most recent snapshot (database + text, directly); redo re-applies (redo stack discarded on new work); both prompt for confirmation naming the command being undone / re-applied (Y confirms). (Implemented 2026-05-24: undo / redo app commands, Modal::UndoConfirm, runtime prepare→confirm→restore→refresh; --no-undo reports undo is off, empty stacks report "nothing to undo/redo". UX polish 2026-05-29, issue #13: the confirm dialog grows to fit its summary on one row, capitalises Snapshot / Yes / No, and renders the snapshot timestamp in local time, human-formatted (24 May 2026, 11:00) via the new chrono dependency.)
• U3 history.log records every submitted command in append-only form, tagged with its outcome (Iteration 2; broadened by ADR-0034). Format: <ISO-8601 Z>|<status>|<source> per ADR-0015 §5 / ADR-0034 §1 — status is ok for a successful command and err for one that failed to parse or execute. Hydration (cross-session recall) reads all records; replay reads ok only.
• U4 replay runs commands from a history.log or .commands file. (Implemented via ADR-0024 Phase E: runtime::run_replay parses each non-blank, non-#-comment line in advanced mode and dispatches it through the normal pipeline; stops at the first genuine error, no rollback. ADR-0034 §3: replay reads journal records (<ts>|<status>| <source>), running ok records and skipping non-ok, while still accepting bare-command scripts. ADR-0034 Amendment 1: replay re-applies only schema/data write commands and skips every app-lifecycle command + nested replay — all skips continue (a nested replay is now skipped, not refused), with a [skip] warning on import / nested-replay. Covered by tests/replay_command.rs.)

Sharing and export (per ADR-0007)

• E1 export produces a zip excluding playground.db AND history.log (per ADR-0007 amendment 1); default filename YYYYMMDD-<projectname>-export-NN.zip with a non-clobbering two-digit sequence under the active data root (Iteration 5). The zip preserves the project's directory name as a single top-level folder. import <zip> [as <t>] is the inverse: derive target name from the zip's top folder, auto-suffix -NN on collision (ADR-0015 §11 amendment), rebuild from text on open.
• E2 User documentation includes sharing recipes for git, email, and direct file transfer.

Sample data / seeding

• SD1 seed <table> [count] generates plausible fake data; junction tables are seeded with valid foreign-key references drawn from existing parent rows. (Done 2026-06-11 via ADR-0048 (commits 202e25a→fbd219b). Whole-row seed <table> [count] [--seed <n>] with realistic name-aware generation (fake crate + a type-gated heuristic catalogue, table-context name disambiguation, hand-rolled product generator, bounded dates), identifier + constraint uniqueness, junction tables seeded with valid FK references drawn from existing parent rows (distinct combinations, capped; empty-parent friendly error), IN-CHECK derivation, a required-column block guard, undo as one step, replay as a data write, a capped auto-show + enum/CHECK advisory, and an O(N) single-transaction path. The set override clause and <table>.<column> column-fill landed in SD2 Phase 2, below.)
• SD2 Detailed seeding rules (per-type generators, locale, determinism, override hooks). (Done 2026-06-11 via ADR-0048 (Phase 1 + Phase 2). Phase 1: type-gated name-aware per-type generators with a fake-backed catalogue + table-context disambiguation, --seed determinism (serial/FK/shortid all reproducible — D4 holds with no exceptions), English-only locale (X2). Phase 2 (the "override hooks" core): the set override clause — fixed value / pick-from-list / as <generator> / between range (numeric and quoted dates, type-aware; an override drops the column from the generic-fill advisory) — and the <table>.<column> column-fill form (an UPDATE over existing rows, refusing PK/autogen targets, empty-table no-op, FK/unique-respecting, one undo step). Adds the KNOWN_GENERATORS vocabulary (D9), a range Generator, and full completion / highlight / validity / help / parse-error-pedagogy wiring. Deferred SD2 increments: user-defined custom generators, NULL injection, multi-locale, recursive parent auto-seed. Later catalogue refinements: #33 year-as-int (year/*_year/published/founded) and #34 conventional choice sets (priority/severity/rating, status excluded) — ADR-0048 Amendment 1.)

Query analysis

• QA1 EXPLAIN QUERY PLAN is run on demand for queries; output is rendered as an annotated tree highlighting full scans, index use, and join order. (Implemented per ADR-0028: the explain prefix over show data / update / delete, with a span-styled plan tree. EXPLAIN QUERY PLAN never executes, so explaining a destructive update / delete is safe. Extended 2026-05-30, issue #7 / ADR-0039: explain now also wraps advanced-mode SQL — select / with / insert / update / delete — via a second Advanced explain CommandNode on the shared entry word, reusing the same plan-tree renderer.)
• QA2 Plan rendering specifics — tree layout, annotation taxonomy, colour scheme. Implemented per ADR-0028 (§3–§6): a box-drawing tree, the substring-pattern taxonomy, and the OutputLine styled-runs mechanism.

Hints, help, errors

• H1 Friendly error-rewriting layer translates engine error messages into learner-friendly equivalents (ADR-0019). (Done: the friendly::translate_error chokepoint is wired on the live failure path (runtime + app + DbError::friendly_message) and covers all five ADR-0019 §3 categories — UNIQUE, FOREIGN KEY (parent- and child-side), NOT NULL, CHECK, and type-mismatch — with operation×kind×verbosity catalog wording, the messages short|verbose verbosity command, and §6 row-pinpointing via runtime-resolved facts rendered through the bordered diagnostic table. Covered by 44 friendly unit tests + 12 full-stack friendly_enrichment integration tests. Remaining ADR-0019 scope is deferred and separately tracked: the §9 i18n migration sweep of all other user-facing strings, advanced-mode SQL-error sanitization (§OOS-2), and messages persistence (§OOS-3, awaits the settings ADR).)
• H1a Strong syntax-help in parse errors. When the user types something near-correct (e.g. insert into T ('Oli') — forgotten values; or update T set x=1 — missing WHERE), the error should name the missing keyword or clause rather than just point at the unexpected character. This is a separate effort from H1 (which targets database errors); it targets parser errors. (Done via the ADR-0042 systematic pass, 2026-06-06.) Built piecemeal first (e.g. values becoming optional in INSERT removes one such case; ADR-0024's typed value slots give per-column-type rejection wording; insert into T (col) with no values clause now flags "looks like Form A — add values (...)"; issue #1 / ADR-0033 Amendment 5, 2026-05-28 added two pedagogical lines that teach the INSERT Form B positional-VALUES contract — a simple-mode submit-time teaching note covering under-supply, over-supply, and extra values (insert.form_b_extra_values_note) and an advanced-mode dispatch-time pre-flight note for the same value-count class (insert.form_b_positional_count_mismatch_note), plus a walker-level diagnostic.insert_arity_mismatch_form_b ERROR that lights the [ERR] validity indicator at typing time; issue #2 / ADR-0022 Amendment 3, 2026-05-29 made the ambient-hint fallback rung schema-aware so the "Next:" prose names the schema-correct next token (, between values, ) after the last) instead of the type-blind close-paren, and so a wrong-arity closed tuple surfaces the real parse error rather than a misleading "submit with Enter"; issue #17 / ADR-0036 Amendment 2, 2026-05-29 then brought the §8.1 arity diagnostic to simple mode at parity with advanced — a wrong-count DSL insert (Form A/B/C) now fires the friendly "N value(s) for col…" message at typing time, counted against the user-fillable columns, with serial/shortid auto-fill named; new keys diagnostic.insert_arity_mismatch_form_b_simple / diagnostic.insert_arity_mismatch_all_auto). The ADR-0042 systematic pass then closed it: a per-command near-miss matrix (tests/it/parse_error_pedagogy.rs) locks every entry word's bare / missing-clause / wrong-token cases plus the committed multi-forms, in both modes; friendlier labels landed (add → 1:n relationship; bare select → "a projection: …"); the usage block became mode-aware (advanced shows the SQL forms plus the still-valid DSL fallback forms, SQL-first); with got its own CTE template; and cross join … on now teaches that a CROSS JOIN takes no ON clause. The advanced-SQL diagnostics that the survey thought missing (INSERT…SELECT count, RETURNING column scope) were verified already present. One low-priority residual is deferred by decision: at submit time a non-projection expression position (bare where , returning ) still shows the raw expression first-set — typing-time completion already offers the right candidates there, so the payoff is small.
• H2 hint provides contextual help for the current input or the most recent error. (Done 2026-06-15, ADR-0053: an F1 keybinding gives a tier-3 teaching hint for the live partial input (read-only overlay), and a submitted hint command expands on the most recent runtime error. A new hint.cmd.<form> / hint.err.<class> catalogue tier (what/example/concept) covers every command form + the 9 runtime error classes, enforced by a comprehensiveness coverage test. Deferred: the pre-submit-diagnostic route + diagnostic.* blocks (#38), clause-concept hints (#37).)
• H3 help provides general reference and per-command help. (Done 2026-06-07: the general reference is help (no arg) — intro + the full command list (REGISTRY × help_id, so new commands appear automatically) + the type reference + a footer pointing at the focused form. Per-command help is help <command> (H3's new piece): the HELP node took an optional single-word topic (BarePath), AppCommand::Help { topic }, and note_help_topic renders the block(s) of every command sharing that entry word — so help create covers both create forms — plus help types for the type reference and a friendly "no help for X" pointer for an unknown topic. Help/usage strings catalogued + key-registered; 9 integration tests (tests/it/help_command.rs). A richer narrative overview (modes, the : escape, syntax conventions) is reference-docs scope, tracked under DOC1 — not part of H3.)

CLI

• L1 Load a project via a positional CLI argument (Iteration 1). Plus --data-dir to override the data root and --help / -h for the usage banner.
• L1a --resume CLI flag opens the most recently used project (path tracked in <data-root>/last_project). Iteration 6: errors cleanly with a stderr banner above the shell prompt if no previous project is recorded or the recorded path is gone — no silent fallback; mutually exclusive with a positional path argument (ADR-0015 §7). last_project is rewritten on every successful project open (startup, load, new, save as, import).
• L1b Per-project input-mode restore (issue #14, ADR-0015 Amendment 1). The input mode is stored in project.yaml (project.mode:), restored on every open, and persisted on a mode change and on unload (quit / project switch) — so the mode you leave a project in is what reopens. A teacher can ship a project that opens in advanced mode; a learner's last-used mode is restored per project. New --mode simple|advanced CLI flag (precedence --mode > stored > simple; combines with --resume). Independent of the history.log input-history hydration piece of Iteration 6.
• [~] L2 Submit a command alongside project load — deferred, not v1.

Tutorials and lessons

• [~] TU1 Tutorial / lesson system — design and ADR pending before any implementation. Out of v1 unless an ADR is written.

Documentation

• [/] DOC1 User- and student-facing reference documentation under docs/: the DSL command surface, the type system, and the boundaries of simple mode. docs/simple-mode-limitations.md is the first piece — it doubles as student explanation and as detailed reference. Distinct from in-app help (H3), the interactive tutorial system (TU1), and the sharing recipes under E2. (Partial, verified 2026-06-07: docs/simple-mode-limitations.md exists (~55 lines, covers the WHERE-expression and table-creation boundaries). Missing: a DSL command-surface reference and a standalone type-system reference under docs/.)

Testing (per ADR-0008)

• TT1 Tier 1: cargo test + proptest covering pure-logic modules (parser, dispatcher, type mapping, project I/O, snapshot ring buffer, replay log).
• TT2 Tier 2: Ratatui TestBackend + insta snapshots for representative views.
• TT3 Tier 3: synthetic event-loop integration tests covering the user-facing flows in this checklist.
• [~] TT4 Tier 4: PTY-based end-to-end for the four critical flows named in ADR-0008 (cold launch → DDL → quit; save → reopen; export → import → rebuild; undo after DROP). (Verified 2026-06-07: nothing is wired — no portable-pty / expectrl / vt100 dependencies, no PTY test files; ADR-0008 §Tier-4 is a specification only. The Tier-3 tests/it/*_e2e.rs files are synthetic event-loop tests, not PTY. Correcting a stale CLAUDE.md line that read "Tier 4 is wired only for the listed critical flows" — it was not wired at all. Genuinely deferred.)
• [/] TT5 CI runs all tiers on Linux, macOS, and Windows on stable Rust. (Partial, 2026-06-15. CI is live on the self-hosted Gitea Actions (docs/ci/adr/): the gate runs clippy -D warnings + cargo test (Tiers 1–3) on the Linux runner for every branch push / PR, and release-macos runs the suite natively on the macOS runner. Windows is build-only — cross-compiled, not executed (no Windows runner). Tier 4 (PTY, TT4) is still unwired, so "all tiers" is not yet fully met. "Stable Rust" is satisfied by the flake's pinned 1.95.0 (a stable release, not nightly). Remaining for full TT5: a Windows execution runner and Tier-4 PTY in CI.)

Cross-cutting

• X1 Comprehensive logging via the project's logging infrastructure per CLAUDE.md (decision points, parameter values, fallback paths). (Done 2026-06-10 via a full-sweep instrumentation pass. The prior state (verified 2026-06-07) was a wired harness (src/logging.rs) but sparse instrumentation — failure-path heavy, nothing in db.rs/parser/executors. The sweep brought every layer to the "log liberally" bar under a documented level discipline (see the logging.rs module doc): db.rs gained entry-level debug! on all 34 do_* executors plus decision-point logs (rebuild-table primitive, insert auto-fill, delete cascade, FK resolution) — so the route through delegating executors is visible in the log sequence; persistence logs every yaml/CSV/history write (the silent-failure paths); runtime logs execute_command_typed dispatch; app.rs logs submit / app-command dispatch / render-mode choice; the parser logs parse begin/outcome at trace (it is a per-keystroke hot path). Levels: debug for per-command detail (off by default, RDBMS_PLAYGROUND_LOG=debug), info for lifecycle, warn for fallbacks, trace for hot paths. Emission verified end-to-end through the real worker thread + logging::init. ~75 → ~135 sites.)
• [~] X2 Language: English-only for v1; multi-language is an open question to revisit later.
• [~] X3 Accessibility: TUI screen-reader support is best-effort and not a v1 commitment; revisit if user need emerges.
• X4 Auto-fill semantics differ between simple and advanced mode — resolved 2026-05-27 (raised 2026-05-26). Was: simple-mode do_insert auto-fills an omitted non-PK serial column with MAX(col)+1, but the advanced-mode SQL insert auto-filled only shortid, leaving an omitted non-PK serial silently NULL — violating ADR-0018 §1's "auto-generated on every path" contract (the column is INTEGER UNIQUE, not NOT NULL, so SQLite permits the NULL). Confirmed by characterization, escalated, and fixed (decision: advanced mode matches simple mode): the advanced-mode auto-fill reconstruction (db.rs, renamed plan_shortid_autofill → plan_autogen_autofill) now also fills an omitted non-PK serial with MAX+1 per row (single- and multi-row), mirroring do_insert and the existing shortid fill. PK serial is excluded (rowid alias); Form B (no column list) still supplies every column. Covered by tests/sql_insert.rs::sql_insert_autofills_omitted_nonpk_serial. Honours ADR-0018 §1/§5; no ADR amendment needed (the contract already said "every path"). ADR-0036 was correct that it did not touch this.
• [~] X5 Framework cohesion / restructuring — strategic, revisit later (raised 2026-05-26). The grammar/execution framework (lexer → walker Nodes → unified grammar tree → typed Commands → executors; ADR-0023/0024 and everything layered on since) grew organically from the original grammar outline and now reuses + mixes elements across many levels without a cohesive written specification of what the framework comprises, which elements are meant to be reusable, and where the boundaries sit for the recurring "reuse vs create new" decision. A concrete symptom: commands are coupled tightly enough to their execution that reusing an execution behaviour tempts reusing the whole command (see the ADR-0036 discussion — the temptation to emit Command::Insert from the advanced path just to reuse do_insert). Desired end state (user-stated): unique commands for every unique case, with a clean, documented structure for reusable mechanics (so execution helpers are shared as library functions, not by collapsing command identity). Consider a dedicated specification + restructuring run (its own ADR) to map the framework and set the reuse-boundary rules, easing future maintenance and extension. Not scheduled.

---

Non-functional requirements

NFRs are quality bars rather than discrete features. Where a target is measurable, it is stated numerically; where it is necessarily qualitative, the criterion is named and the bar is "reviewer judgement against the criterion."

• NFR-1 Performance — startup. Cold launch to first rendered frame under 500ms on commodity hardware (developer laptop, mid-range desktop). Measured in CI on the Linux runner as a regression gate.
• NFR-2 Performance — input latency. Keystroke-to-render latency under 16ms during normal editing; long-running queries must execute off the UI thread so the interface remains responsive (typing, scrolling, mode switching) while a query is running.
• NFR-3 Performance — resource footprint. Idle memory under 50MB on the smallest target platform; no busy-loops; CPU near zero when waiting for input.
• NFR-4 Visual quality — distinctive design. Colour palette and typography are deliberate and consistent across views; layout uses Unicode box-drawing and symbols where they add clarity; rendering avoids the generic flat-default look that ships with most TUI frameworks. Criterion: a reviewer can identify the app from a screenshot of any view.
• NFR-5 Visual quality — colour use. Colour conveys information rather than decoration: mode indication, query result types (numeric vs text vs null), error severity, syntax highlighting categories. Foreground/background combinations meet WCAG-AA contrast (4.5:1 for normal text) even though we have not committed to broader accessibility.
• NFR-6 Cross-platform parity. Behaviour and visual quality are equivalent across Linux, macOS, and Windows on crossterm-supported terminals. Platform-specific divergence (e.g. font fallbacks) is documented, not silently tolerated.
• NFR-7 Light and dark background support. The colour scheme remains legible and visually coherent on both light and dark terminal backgrounds. The mechanism (auto-detect via terminal query, explicit user setting, or both) is an implementation choice, but the outcome is non-negotiable: no dark-on-dark or light-on-light readability failures on either background.

---

Explicitly out of scope

• [-] N1 Hosted publishing platform — per ADR-0007. Sharing is local-artifact based.
• [-] N2 Real UUID column type — per ADR-0005. The shortid type covers the pedagogical need at TUI-friendly width.
• [-] N3 Cross-emulator visual regression coverage — per ADR-0008. Crossterm abstracts terminals adequately; we revisit only if a real regression surfaces.
• [~] N4 Global rolling input history (cross-session, cross-project). Mentioned in I2's wording; deferred per ADR-0015 §12 — project-scoped history (via history.log) is the v1 surface. Revisit if real demand emerges.

---

Maintenance

This document is updated whenever:

• A new requirement is committed to (added as a new item with the next free ID in its section).
• A deferred item is taken up (status moves from [~] to [ ]).
• An item is satisfied (status moves to [x], with a reference to the commit, PR, or test that demonstrates it).
• An item moves out of scope (status moves to [-] with a rationale and a link to the decision).

IDs are stable: once assigned, they are not reused. Removing a requirement leaves a "withdrawn" entry referencing the decision.