rdbms-playground/docs/handoff/20260507-handoff-1.md

# Session handoff — 2026-05-07 (1)

This is the first handover note from the project. The session
took the project from an empty repository through five
feature-bearing iterations to a working DSL playground for
relational database concepts. The next session should be able
to pick up cleanly from `CLAUDE.md` + this file + the linked
ADRs.

## State at handoff

**Branch:** `main`. Working tree clean. Six commits on top of an
empty starting tree:

```
305e508 INSERT/UPDATE/DELETE + value model + auto-show, with polish
1650682 Foreign-key relationships, rebuild-table, polish round
c1e5292 DSL parser, async DB worker, types, history, metadata, polish
25a0f12 TUI walking skeleton (Phase 4)
aebfc7d Add Phase 1 requirements checklist with NFRs
3a0c03d Initial planning docs: CLAUDE.md and ADRs 0000-0008
```

**Tests:** 200 passing (183 lib + 17 integration), 0 skipped.
**Clippy:** clean with `nursery` lints enabled.
**Release build:** ~5MB single binary.

The user's terminal is a real TTY; the TUI runs cleanly there
but cannot be exercised from a non-TTY environment. Use the
`cargo test` tiers for confident automated checks (Tier 4 PTY
tests are configured per ADR-0008 but not yet wired with
`expectrl`).

## What's implemented

**TUI shell:**
- Three-region layout: items list (left), output + input + hint
  (right), bottom status bar with mode-aware shortcuts.
- Light/dark themes with `--theme` CLI flag and `COLORFGBG`
  auto-detect.
- Simple/Advanced input modes; `:` one-shot escape with prompt
  reaction (border colour + label flip); auto-inserted space
  after a leading `:` in simple mode.
- In-line cursor editing (Left/Right/Home/End/Delete/Backspace,
  UTF-8 boundary aware).
- In-memory command history (Up/Down with draft preservation,
  consecutive-duplicate dedup).
- PageUp/PageDown output scrolling, wrap-aware (renderer
  reports both visible-row count and total wrapped-row count
  to App).

**Database (in-memory only this iteration; track 2 brings file
backing):**
- SQLite via `rusqlite` 0.39 with `STRICT` tables and
  `PRAGMA foreign_keys = ON`.
- Dedicated worker thread (ADR-0010): `mpsc::Sender<Request>`
  in / per-request `oneshot` reply out. App holds a
  `Database` handle (cheap to clone).
- Internal metadata tables (ADR-0012, ADR-0013):
  - `__rdbms_playground_columns(table_name, column_name,
    user_type)` for round-tripping user-facing column types.
  - `__rdbms_playground_relationships(name, parent_table,
    parent_column, child_table, child_column, on_delete,
    on_update)` for named FKs.
- Internal-table convention: prefix `__rdbms_*`; filtered out
  of `list_tables`. Future internal tables follow this rule.
- DDL: `create_table`, `add_column`, `drop_table`,
  `add_relationship`, `drop_relationship`, `query_data`,
  `insert`, `update`, `delete`.
- `rebuild_table` primitive following SQLite's
  ALTER-via-rebuild recipe (ADR-0013): `PRAGMA foreign_keys =
  OFF` outside tx, copy-by-name, `foreign_key_check` before
  commit, atomic metadata updates. Reusable for B2's pending
  column-drop/rename/type-change work.
- FK error enrichment lists both outbound (INSERT/UPDATE
  relevance) and inbound (DELETE/UPDATE on parent relevance)
  relationships from the metadata.

**DSL grammar (chumsky):**
- `create table <Name> with pk [<name>:<type>[,<name>:<type>...]]`
  — single PK or compound; `with pk` alone defaults to
  `id:serial`; bare `create table X` errors with friendly
  hint.
- `add column [to table] <Name>: <ColName> (<type>)` — `to
  table` optional (just verbose).
- `drop table <Name>` — refuses if other tables still
  reference this one (lists the offending relationships).
- `add 1:n relationship [as <name>] from <P>.<col> to
  <C>.<col> [on delete <action>] [on update <action>]
  [--create-fk]` — auto-name format
  `<Parent>_<pcol>_to_<Child>_<ccol>`. `[as <name>]` requires
  the `as` keyword to be unambiguous.
- `drop relationship <name>` or `drop relationship from
  <P>.<col> to <C>.<col>`.
- `show table <Name>` — re-renders structure.
- `show data <Name>` — renders data view.
- `insert into <T> [(cols)] values (vals)` and
  `insert into <T> (vals)` (short form omitting `values`,
  disambiguated by literal-vs-identifier content).
- `update <T> set <c>=<v>[,<c>=<v>...] where <c>=<v> | --all-rows`.
- `delete from <T> where <c>=<v> | --all-rows`.

App-level commands (always available in both modes per
ADR-0003): `quit`/`q`, `mode simple|advanced`. The rest of the
canonical list (`save`, `load`, `export`, etc.) lands with the
features they belong to.

**Type system (ADR-0005):**
All ten user-facing types implemented:
`text` / `int` / `real` / `decimal` / `bool` / `date` /
`datetime` / `blob` / `serial` / `shortid`.
- `serial` → `INTEGER PRIMARY KEY`, auto-filled by SQLite.
- `shortid` → `TEXT`, auto-generated by us (10-char base58,
  no ambiguous glyphs); explicit values validated against the
  same alphabet/length range.
- `decimal` / `date` / `datetime` stored as TEXT; per-type
  format validation at INSERT/UPDATE time with friendly
  errors naming the column.
- `Type::fk_target_type()` (ADR-0011): `serial → int`,
  `shortid → text`, others identity. Used at FK declaration
  to validate the FK column type.

**Auto-show after writes:** INSERT/UPDATE/DELETE return typed
result structs (`InsertResult`, `UpdateResult`, `DeleteResult`).
INSERT auto-shows the just-inserted row (via
`last_insert_rowid`). UPDATE captures matching rowids up-front
so the post-update display still finds the rows even if WHERE
column changed. DELETE reports per-relationship cascade effects
via row-count diffing of inbound child tables. UPDATE-side
cascades are not detected (would need value diffing).

## ADR index (read these before touching the related areas)

```
0000 Record architecture decisions (process)
0001 Language and TUI framework (Rust + Ratatui)
0002 Database engine (SQLite STRICT)
0003 Input modes and command dispatch
0004 Project file format (deferred — track 2)
0005 Column type vocabulary (ten types)
0006 Undo snapshots and replay log (deferred)
0007 Sharing and export (deferred — depends on track 2)
0008 Testing approach (four tiers)
0009 DSL command syntax conventions
0010 Database access via worker thread
0011 FK column type compatibility
0012 Internal metadata for user-facing column types
0013 Relationships, naming, and rebuild-table strategy
0014 Data operations, value literals, and auto-show
```

## What's pending — proposed next moves (in order of my recommendation)

Several natural directions; ranking is my read at handoff.

### 1. Project storage — track 2 (highest user impact)

The largest UX friction left: every quit loses everything. The
ADR-0004 design is fully in place — `project.yaml` + `data/<table>.csv`
+ `history.log`, with `playground.db` as a derived artifact.

What's needed:

- File-backed projects: `Database::open(path)` already accepts
  any path (currently always `:memory:` from runtime).
- Auto-temp project on startup, stored in
  `~/.rdbms-playground/projects/temp-<name>` (P1).
- `save` / `save as` (P2).
- Auto-save on every change (P3).
- `load` picker (P4).
- `playground.db` rebuild from `project.yaml` + `data/`
  (P5) — confirmation when `.db` exists.
- YAML schema serialiser/deserialiser. The metadata tables
  (ADR-0012, ADR-0013) are already the source of truth.
- CSV per-table data round-trip.
- Persistent command history (the in-memory I2 becomes
  durable).
- `.gitignore` template (F2) and the format-version
  migration framework (F3).
- The `export`/`import` app-level commands (E1, ADR-0007).

Estimated scope: substantial — 800–1500 lines + tests +
ADR(s). Probably wants its own design ADR before coding.

### 2. Complex WHERE expressions (C5a)

The user explicitly flagged this as the bridge between DSL
fluency and real SQL. AND/OR/comparison operators (`<`, `>`,
`!=`)/`LIKE`/`IS NULL` in UPDATE/DELETE/show-data filters.
Builds on the existing parser. Requires its own ADR for the
expression grammar.

Estimated scope: moderate — 400–600 lines + tests + ADR.

### 3. Indexes (C3 partial) + EXPLAIN QUERY PLAN rendering (QA1)

Indexes are conceptually simple but unlock the most powerful
teaching demo we haven't done yet: showing how `EXPLAIN QUERY
PLAN` output changes when an index is added. ADR-0008 plus the
SQLite EXPLAIN docs are the relevant references.

Estimated scope: moderate — 300–500 lines + tests + ADR.

### 4. B2 column drops/renames/type changes

The `rebuild_table` primitive already exists (ADR-0013). What's
needed is grammar (`drop column from T: c`, `rename column on
T: old to new`, `change column on T: c (new_type)`) and the
executor wiring.

Estimated scope: moderate — 400 lines + tests.

### 5. Friendly error layer (H1)

Promote `DbError::friendly_message()` from passthrough to a
real translator: rewrite SQLite's terse messages to learner-
friendly ones, identify exact violating columns/rows, suggest
fixes ("did you mean…").

Pairs nicely with H1a (parse-error syntax-help that points at
missing keywords/clauses).

Estimated scope: moderate — 300–500 lines + tests + ADR.

### 6. Session log + Markdown export (V4 — the bigger UX project)

The output panel as a scrollable per-session journal with
inline rich rendering (smart table-vs-vertical decision based
on dimensions); Markdown export of the log. Largest single
piece of UX work outstanding.

Estimated scope: large — 800+ lines + tests + ADR.

### 7. m:n convenience command (C4)

Auto-generates a junction table from `create m:n relationship
from <T1> to <T2>`; pulls primary keys; assigns a compound PK
on the junction. Depends on relationships being solid (they
are).

Estimated scope: small/moderate once the design is settled —
200–400 lines + tests + ADR.

### 8. Smaller polish

- Readline shortcuts I1b (Ctrl-A/Ctrl-E and friends).
- Multi-line input I1 (Enter inserts newline, Ctrl-Enter
  submits).
- Tab completion I3.
- Syntax highlighting I4.
- CI (TT5) — the test infrastructure exists; the workflow
  file does not.

## Sharp edges and subtleties

Things that took thought during this session and might trip up
a new agent:

- **`update()` is pure-sync; the runtime is async.** The Elm
  pattern (ADR-0001 / ADR-0010) is a hard rule. Don't make
  `update()` async or do DB calls inside it; emit `Action`s
  and let the runtime dispatch.
- **Schema mutations always update metadata.** Both
  `__rdbms_playground_columns` and `__rdbms_playground_relationships`
  are in transactions with the DDL they belong to. A new DDL
  operation that touches columns or relationships and forgets
  to update metadata will produce subtly wrong `describe_table`
  output later.
- **`rebuild_table` is the load-bearing primitive for any FK
  change.** SQLite cannot ALTER FK constraints in place. The
  primitive runs `PRAGMA foreign_keys = OFF` *outside* a
  transaction (the pragma is a no-op inside one), then
  performs the swap.
- **Wrap-aware scroll math.** The renderer reports both
  visible-row count and total wrapped-row count to App via
  `note_output_viewport`. Writing scroll-related code without
  understanding this will produce off-by-many bugs (the user
  hit one twice in this session).
- **Command::target_table vs display_subject.** They differ
  for relationship commands: `target_table()` is the parent
  (drives the post-action description); `display_subject()`
  prints the `from .. to ..` form for the `[ok]` line.
- **Ratatui's `Paragraph::line_count` is unstable.** We
  approximate via character-count division
  (`approximate_wrapped_rows_from_output` in `ui.rs`). Off-by-
  one at boundaries is acceptable for scroll capping.
- **`set_default` is not implemented.** ADR-0014 commits to
  the four other actions (`no action`, `restrict`, `set null`,
  `cascade`). When DEFAULT constraints land (C3 partial),
  `set default` joins the family.
- **The user has expressed strong preferences several times.**
  Reread the relevant ADRs (especially 0009 on syntax
  conventions; 0011 on type compatibility; 0013 on
  relationship naming) before making any DSL grammar change.

## How to take over

1. Read this file.
2. Read `CLAUDE.md` for the working-style rules and current
   layout.
3. Read `docs/requirements.md` for granular progress.
4. Skim `docs/adr/README.md`; read any ADR you'll touch.
5. Run `cargo test` to confirm the 200-test green baseline.
6. `cargo run --release` to see the app in action — try the
   smoke test below.

### End-to-end smoke test

Verifies the playground works from create-table through
relationships through writes through cascade:

```
create table Customers with pk id:serial
add column Customers: Name (text)
create table Orders with pk id:serial
add column Orders: CustId (int)
add 1:n relationship from Customers.id to Orders.CustId on delete cascade
insert into Customers ('Alice')
insert into Customers ('Bob')
insert into Orders (CustId) values (1)
show data Customers
show data Orders
delete from Customers where id=1
show data Orders                              -- empty (cascaded)
quit
```

If anything in that sequence fails, something is wrong. The
sequence exercises auto-PK, auto-shortid (no, wait — Customers
is serial, not shortid; for shortid replace `pk id:serial`
with `pk id:shortid`), FK compatibility (Customers.id is
serial → Orders.CustId must be int), the rebuild-table dance
when adding the relationship, the insert short form, and
cascade delete.