Schema Builder

# Schema Builder — visual deftype designer + persist backend manager A GL-host panel app (retro/hydra) for designing `deftype` schemas the way `deft/persist` sees them, generating a real schema module, creating and migrating SQLite backends from it, and browsing/querying compliant databases. It is a standard widget-tree app with a pixel surface embedded as a widget: the schema canvas is `deft/ui/components/surface` (custom pixel art in local coordinates, scissored to its slot), while the inspector, status bar, and data browser are ordinary UI components (`column`/`row`/`label`/`button`/`checkbox`/`text-input`/`dropdown`/`grid`/ `multiline-text`). The canvas needs a GL host; the widget chrome works anywhere. ## Launching ```sh retro app deft/schema-builder # empty canvas (Ctrl+O to open) retro workspace schema-builder # with workspace-example.dft copied in ```` ## Persist mode (global checkbox) The right dock starts with a persist checkbox: - on (default) — full persist schema: SQL storage options, foreign-key relations, declarative views, DDL preview, backend create/migrate/check, and Data mode. - off — pure type designer: the SQL-only UI (table mapping, SQL type/default, composite indexes/append, views, DDL/backend actions, Data mode) disappears. Base-type keys stay visible and are saved: `:primary-key`, `:unique`, `:index` and `:references` are part of the type even when nothing enforces them. SQL type/default mappings stay in the file but their editor rows are hidden. Views are hidden on the canvas; FK badges/edges remain. Saving keeps `types.dft` + `design.dft` + `schema-meta.dft` + `model.dft` (the design file still records canvas positions) and removes a stale `persist.dft` from an earlier persist save, with those field keys intact. Opening a types-only file (no sibling design) enters type-designer mode automatically; its base-type keys (including references) load as written. Toggling persist back on restores the hidden builder state in the current session. ## Two modes (`m`) ### Schema mode — the canvas Every type is a node (header + one row per field, inherited fields dimmed and shown parent-first); `defunion` types get their own color. New types and views are placed in the first free slot in a canvas-fitted grid, so they never land on top of existing nodes. Empty types still have a clickable body ("no fields — press A"). Edges: - inheritance (solid blue): child `<<` parent, - reference (dashed amber): a field's persist `:references` target, - view (dashed purple): a view and the base type it draws from. | Key / mouse | Action | |---|---| | `a` | add a type (prompt) | | `A` / `f` / Enter | add a field to the selected type | | `n` | rename the selected type/field/view | | `x` / Del | delete the selection (types confirm) | | `c` / `C` | set / clear the selected type's parent (`<<`) | | `v` | add an empty view (prompt for name) | | `g` | fit the canvas to the schema | | arrows / Shift+arrows | nudge the selected node (10 / 40 px) | | drag a node | move it (snaps to a 10 px grid) | | drag a field row onto a view | add that field as a view column (base/join created automatically) | | drag empty canvas | pan | | wheel | zoom at the cursor | | `p` | preview the persist DDL in the workspace editor (opens one if needed) | | `m` | switch to Data mode | | `d` | toggle the inline data panel under the canvas | | `Ctrl+S` / `Ctrl+O` / `Ctrl+N` | save / open / new | | `Ctrl+R` | regenerate database list + recompile the probe | | click a header/row | select the type/field | ### Views — drag-built queries `v` creates an empty view on the canvas. Drag field rows from any type node (including imported types) onto it: - the first field sets the view's base type and alias, - fields from other types add a LEFT join with alias = type name; ON pairs are discovered from `:references` when possible, - every dragged field becomes a column (duplicate names are prefixed with the source type). The right dock for a new-style view shows the base, the column list, each join's kind + ON text (`alias.field = alias.field; …`), the `where` clause, and preview/delete actions. Add joins with + Add join…; remove a join drops its columns. Legacy shape-based views keep their old `:from`/`:shape` inspector until the first field is dragged in; that drop converts them to a drag-built view and preserves their existing columns. ### Inline data panel Toggle data in the dock (or press `d`, or Data → Toggle data panel) to show a data grid in its own row beneath the canvas. It follows the current selection: a type shows its table, a view shows the live result of the view's SELECT (join/where included). Changing a dragged column, join, or where clause refreshes it after recompiling. When no database is open it selects the schema's `populate.db` by default (if present); other databases can be picked from the panel header. The full Data mode (`m`) remains available for ad-hoc SQL. ### Inspector (right dock) The dock is a standard widget column (clipped to its slot, fixed width): - text rows (name, type/of via dropdowns, default, description, min/max, pattern, enum, validate, sql type/default, table name, indexes, append, variants, view alias/where) edit inline — type in the input; - checkboxes (`required`, `many`, `coerce`, `primary-key`, `unique`, `index`, `if-not-exists`) toggle immediately; - buttons run actions (add field, preview DDL, check live, new view, delete, done); - `Set reference…` opens the reference wizard (target type → target field → on-delete). The target list hides the selected type, target fields are listed primary-key first, and the FK is stored on the selected field (shown as `FK` on its canvas row and in the type's `relations` list, where `select` jumps to the owning field). `Clear reference` appears when a field already has one. Diagnostics for the selection appear inline (compile/structural issues). ### Data mode — widget console - header: DB and Rel dropdowns (recursive `*.db` scan, configurable via `schema-builder/db-glob`); Design query… starts the visual query wizard; Schema returns to the canvas. - results grid: standard `grid` component — wheel / arrows / PgUp/PgDn / Home/End scroll, auto-fit columns, click to select a row. - SQL editor: `multiline-text`; click to focus and type, `Enter` runs, `Ctrl+Up/Down` walks history (the editor keeps its buffer across rebuilds). `m` switches back to the canvas when no input is focused. - Design query… walks: base type → columns → `where` conditions (`field = value; …`) → order (`field :desc`) → limit, assembles the SQL (using the type's persist table name), switches to Data mode, and runs it. The result stays in the editor for further edits. - Views are persist's declarative joins. Drag-built views live on a hidden `View_<name>` shape type; Preview view SQL opens the `CREATE VIEW` statement in the workspace editor (same editor handling as DDL preview) and ensure/migrate materializes it. Renaming a view creates the new SQL view on migrate; use Migrate’s stale-view prompt (or Schema → Drop stale views…) to remove the old database view. The inline data panel shows their rows before the view exists in the database. ## Persist workflows Drafts are compiled by a scratch child runtime (`sb-probe`) through the real compiler and `deft/persist` — diagnostics and DDL are ground truth, not approximations. Recompiles are debounced (`schema-builder/probe-debounce-ms`, default 300ms) after each edit. - Live diagnostics — compile errors show in the status bar; structural problems (unknown parents, unresolved references, duplicate names, unions in persist columns, references to custom-named tables) highlight nodes red/amber and list in the inspector. - Preview DDL (`p`) — the exact `CREATE TABLE`/`CREATE INDEX`/`CREATE` VIEW` statements `persist/ensure` would run, opened as` `schema-builder-preview.sql` in the current workspace's editor panel. If no editor is open, one is split into the workspace automatically; the buffer is refreshed on each preview. - Create backend… — pick a file; runs `persist/ensure` in the probe (idempotent, `PRAGMA foreign_keys` on) and opens it in Data mode. - Migrate… — additive `persist/migrate` with a per-type report (`created`, `added`, `blocked`, missing/changed diagnostics). After a successful migrate it offers to drop database views the schema no longer declares (renamed/removed views), showing the exact list first. - Drop stale views… — the same cleanup on demand for a chosen database: any non-system view whose name is not declared by the current schema is listed and, after confirmation, dropped. Useful after renaming a view: migrate creates the new name, then this removes the old one. - Check live… — `persist/check`: rows a database has that the schema rejects, with invalid rowids. - Every write action re-derives from the current draft — save first if you want the file to match the database. ## Saved layout — `<name>.scm` directories A schema is a directory named `<name>.scm` (for example `schema/orders.scm/`). It can live anywhere under the workspace; the parent directory is not fixed. Inside it are the schema files: ``` orders.scm/types.dft # types only — importable by ANY Deft program orders.scm/design.dft # design document: layout + type versions orders.scm/schema-meta.dft # full schema descriptor blob (types/views/model) orders.scm/persist.dft # persist opts + API (imports types, design.dft) orders.scm/model.dft # generated in-memory model API orders.scm/populate.dft # optional AI test-data seeder ```` In type-designer mode (persist unchecked) `types.dft`, `design.dft`, and `model.dft` are written (positions survive mode switches); a stale `persist.dft` from an earlier persist save is removed. The generated model is independent of persist mode. - `types.dft` contains only the document's imports and the generated `deftype` / `defunion` declarations (parents first, unions last). It has no persist dependency, so simple programs can use it just to define types. Field `:references` (foreign keys) live here only — the most generic module is the single source of truth for relations:

import "schema/orders.scm/types"

set u %User{ :name "Ada" }
[is? "User" $u]        # true; the types are registered by the import

- `design.dft` carries the `%SchemaDesign{}` data block: canvas positions, the model version, and per-type versions. It deliberately omits field references and SQL/storage options; opening it reloads those from the schema's `types.dft` / `persist.dft`. Importable anywhere the types plus layout are wanted (view rectangles are unused outside the builder). - `schema-meta.dft` is the canonical, tag-free schema descriptor used by persist metadata: every type/field/view/library plus the model graph (canvas layout excluded). It is emitted as a single string blob for hashing/version checks and exposes `schema-descriptor` (parsed), `schema-descriptor-text`, `schema-name`, and `schema-version`. `persist.dft` and `model.dft` import it; DB-to-schema tools can use it to regenerate the `.scm` sources. - `persist.dft` imports its siblings and `deft/persist`, and carries the storage definition plus the persist API. `ensure`/`migrate` also record the schema blob in `_persist_meta_`:

import "deft/persist" as persist
import "schema/shop.scm/persist.dft" as schema

set store [sqlite/open "shop.db"]
[await [schema/ensure $store]]              # create missing tables/views
set sales [schema/repo $store :Sale]        # honours :table-name etc
[await [persist/insert $sales %Sale{ :region "west" :amount 4 }]]

Its `%SchemaPersist{}` data block holds the editable refs (`:table-name`, composite indexes, `append`, …) and `%PersistView{}` maps; the getters (`refs`, `opts`, `views`) derive from it. In type-designer mode the file is absent — import `types.dft` (types) or `design.dft` (positions). `model.dft` is always generated; import it to use the in-memory model API. | Export | Purpose | |---|---| | `refs` / `opts` / `views` | persist refs argument / per-type TypeOpts / `%PersistView{}` maps | | `repo store type` | typed accessor honouring storage options | | `ensure store` | create missing tables, indexes, views | | `migrate store` | additive alignment; returns the per-type report | | `check store` | invalid-row scan | `schema-name` and `schema-version` are bumped automatically: the model version changes when any structured type/view/persist change is saved; a type version changes when that type's structure changes. Canvas moves do not bump versions. Persisting the same version with a different blob is rejected, so bump the version (or run the generated `migrate`) before writing a changed schema. Writes are atomic and fmt-canonical when the TUI formatter is available. Path rules. Save As defaults to `<name>.scm` in the configured start directory (`schema-builder/schema-dir`, default `schema/`). Choose `schema/shop.scm` (or any file inside it, or the legacy `schema/shop.dft` / `schema/shop`) and files land in `schema/shop.scm/`. The `.scm` parent can be anywhere and nested as you like. Opening a `.scm` directory or any of its files loads the builder document — the app resolves siblings from the directory. Legacy `<name>/<name>.dft` trees and `X_builder.dft` / `X_persist.dft` files still load; Save As uses the `.scm` layout by default. A types-only file with no sibling design opens as a type designer. The document's display name follows the directory base name on save. Finding schemas. File → Open schema… scans the workspace recursively for `<name>.scm` directories (config `schema-builder/schema-scan-root`, default `.`; engine/source trees such as `packages`/`ui`/`deft` are skipped) and lists them with a Browse file… fallback. Type-library pickers use the same scan, so a new schema becomes importable without any central registration. Loading never evaluates generated code: the data block is read with `deft/parse`, and a types-only file falls back to walking its `deftype`/`defunion` declarations with an auto layout, so hand-written schemas open too. ## Type libraries — shared types A schema can import types from other modules instead of redefining them. This is how a `customer` schema, a system-wide `types.dft`, or a package's type module is reused: - Add a library — `l`, or Schema → Add type library…. Discovered `<name>.scm` schema directories are listed automatically; Browse file… picks any `.dft` outside the scan root. The path is stored relative to the current schema directory. - Imported types appear on the canvas with an `[imported]` badge and a muted header. They are read-only: they can be used as field types, references, union variants, and `<<` parents, but the inspector offers no field/storage edits. If the library is itself a builder schema, Open source schema opens its design; otherwise edit the file directly. - Extend locally creates a local subtype (`LocalCustomer <<` Customer`) that can add fields and override inherited keys. This is the` way to adapt an external/package type without touching its source. `<<` cannot remove or rename inherited fields; define a separate type in that case. The subtype gets its own table — inheritance is structural, not SQL table inheritance. - Emission — `types.dft` starts with `import "../customers.scm/types"` and contains only this schema's declarations. `design.dft` stores `:libraries` plus positions for imported nodes. `persist.dft` keeps local refs only: each schema creates its own tables; imported tables must be created by their owning schema (the builder's probe/DDL likewise covers only local tables). - Collisions — a type name may come from only one place. Importing a duplicate name (or defining a local type with a library type's name) is rejected; remove the library from the empty inspector to undo an import. In the workspace example, `schema/customers.scm/` owns `Customer` while `schema/orders.scm/` imports `../customers.scm/customers.dft` and keeps only its own `Order`/`OrderItems` definitions. Limits: only direct declarations of a library file are drawn (a library's own imports still compile); package-mount imports are not resolved by the picker yet (import the package's source file by relative path); an imported type's custom `:table-name` is still not honored as an FK target (persist uses snake_case there). ## Populate — AI test data Schema → Populate with AI… (persist mode, saved schema) generates `populate.dft` next to the schema files and runs it in a child runtime. The script builds a schema-aware prompt, asks stdext AI for a JSON dataset, normalizes keyword/enum values, and inserts every seeded type through `persist/insert-many` in dependency order. - Imported types are seeded as fixtures too, so views and references work in the throwaway database without populating other schemas first. They are duplicated for this test DB only; each schema still owns its own tables for real use. - The DB is `<name>.scm/populate.db` (config `schema-builder/populate-db`). Rerunning recreates it — it is test data, not production storage. - The generated script imports `./persist.dft`, so it exercises the real persist schema: tables, indexes, foreign keys, and views. After a successful run the panel opens the DB and switches to Data mode, where views are browsable alongside tables. - Rerun from the schema directory with `deft run populate.dft`, or regenerate from the menu after changing the schema. The number of rows per type is `schema-builder/populate-rows` (default 8) at generation time. - The AI reply is saved before any database work: the extracted JSON goes to `populate.json` (same shape as the request: `{ "Type": [ {...} ] }`), and the raw reply/error goes to `populate.reply.txt`. A failed insert therefore leaves the dataset on disk for inspection, editing, and a retry. Once `populate.json` exists, reruns use it instead of calling AI; delete it to regenerate fresh data. Tests use this same fixture path. - Populate calls the model without tools (the MCP tool loop is skipped), so the model only returns data. - AI configuration follows the workspace: an `:ai` prelude map (via `ai/load-cfg`) wins, otherwise the `ai/provider` / `ai/model` keys in `.deft/config.dft` (this workspace: `:deepseek` / `"deepseek-v4-flash"`), otherwise `ai/default-cfg`. The API key comes from the provider's environment variable. The call uses temperature 0, thinking off, and no tools; empty responses are retried twice. The panel surfaces failures in a dialog and leaves the generated script and reply dump for inspection. - AI output is parsed as data only — never evaluated. Limits: imported fixtures use default storage names (custom `:table-name` on a library type is not applied here); self/cyclic references rely on the model ordering rows parent-first; a validation error identifies the type/row and leaves the script for editing. ## Generated model (`model.dft`) Every save also writes `<schema>.scm/model.dft` (in legacy layouts, `model.dft` beside the types file). It is an in-memory model layer generated from `types.dft`; it does not require persist mode.

from "shop.scm/model" import new-shop

set model [new-shop]

set c [$model create %Customer{ :id "c1" :name "Ada" }]
set a [$c create [Address --street "Main"]]
[$c list Address]
[$model list Customer]
[$model find Customer "c1"]
[$model dump]
[await [$model persist "shop.db"]]   # snapshot replace + schema blob
[await [$model load "shop.db"]]      # verify blob, rebuild the model

Relations are derived from both `:references` (association: child rows linked by FK) and fields typed as another record/union (composition: nested in the parent). Imported parent types referenced by local records also get relations, `ModelEntity` impls, and are included in model persistence, so an imported `Customer` can create and persist local `Order`s. A child type can have several parent relations; generic `create` raises when a child type matches more than one, while `list` combines them. `dump` returns a data snapshot; `persist` snapshot-replaces all model tables and writes the versioned schema blob in one transaction. `load` verifies the stored schema/version/hash against `schema-meta.dft`, then rebuilds the model atomically. See `deft/model`'s help for ids, limits, and the full API. The `todo` app (`packages/todo`, `retro app deft/todo`) is a complete app-state example: its own `todo.scm/` schema directory, a headless store, and a TUI panel that restores its filter/selection on launch. The manual page `help/manual/03-Platform/10-app-state.md` walks through it. The generated module only depends on `deft/model`; import it with `from` to bring the generated `new-<schema>` constructor into scope, then use the model handle for all top-level operations and the stored records for child operations. ## Module API (for scripts and tests) | Module | Provides | |---|---| | `deft/schema-builder/model` | `%SchemaDoc/Type/Field/View/Index`, `%SchemaDesign` / `%SchemaPersist` save shapes, library metadata (`external?`, `local-types`, `library-imports`), pure ops, `validate`, `topo-order`, persist conversion, query-builder SQL | | `deft/schema-builder/emit` | deterministic `types-text` / `design-text` / `persist-text` / `model-text` / `schema-text`, `eval-source` for the probe | | `deft/model` | generated-model runtime: `new-<schema>` construction, `ModelOps`/`ModelEntity`, dump, transactional persist | | `deft/schema-builder/parse` | `read-doc-block`, `parse-text`, `load` (resolves `:libraries`), `save`/`save-both`, `schema-paths` (`.scm` directories + optional schema-root), `resolve-design-path`, `find-schema-dirs`, `rel-path` (atomic multi-file writes) | | `deft/schema-builder/probe` | `refresh`, `ensure`, `migrate`, `check`, `find`, `count`, `view-sql` (child-runtime session) | | `deft/schema-builder/layout` | node measurement, hit-testing, pan/zoom/fit, edge routing | | `deft/schema-builder/query` | db discovery, relations/columns, `run`, grid shaping, query-builder previews | | `deft/schema-builder` | the panel: document ops, probe wrappers, widget-tree builders, menu handlers | | `deft/ui/components/surface` | the embedded pixel-surface widget used for the canvas (`draw-cb`/`event-cb`/`scroll-cb`) | ## Configuration | Key | Default | Purpose | |---|---|---| | `schema-builder/schema-dir` | `"schema"` | Default Save As / file-dialog start directory | | `schema-builder/schema-scan-root` | `"."` | Root scanned for `<name>.scm` schemas | | `schema-builder/db-glob` | `"*.db"` | Data-mode discovery glob (recursive) | | `schema-builder/snap` | `true` | Snap dragged nodes to a 10px grid | | `schema-builder/grid` | `true` | Draw the canvas grid | | `schema-builder/probe-debounce-ms` | `300` | Delay before recompiling the draft | | `schema-builder/populate-db` | `"populate.db"` | Test DB created by Populate (relative to the schema dir) | | `schema-builder/populate-rows` | `8` | Rows/type requested from AI when generating populate.dft | ## Tests From the workspace root: ``` deft test packages/schema-builder/test/test_model.dft # model + validation deft test packages/schema-builder/test/test_emit.dft # deterministic emission deft test packages/schema-builder/test/test_parse.dft # round-trips + foreign files deft test packages/schema-builder/test/test_populate.dft # populate.dft generation + ordering deft test packages/schema-builder/test/test_layout.dft # canvas geometry deft test packages/schema-builder/test/test_probe.dft # scratch runtime + persist ops deft test packages/schema-builder/test/test_query.dft # browser + builder helpers deft test packages/schema-builder/test/test_panel.dft # pixel UI state machine deft test packages/schema-builder/test/test_integration.dft # emitted module as a consumer deft test packages/schema-builder/test/test_model_emit.dft # generated model module deft test packages/schema-builder/test/test_model_integration.dft # model API + persist ```` ## Limits (v1) - GL hosts only (retro/hydra), like codegraf. - SQLite backends only (persist's `io-*` seam keeps other backends open). - Import from an existing database (DDL → schema) is not implemented yet. - Additive migrations only — persist never drops or retypes columns; `migrate` reports `blocked`/`missing`/`changed` instead. - No undo/redo, multi-select, or copy/paste on the canvas. - The inspector column clips when the window is too short (no dock scrolling yet). - Views edit `:from`/`:shape`/`:join`/`:as`/`:where`; explicit `%JoinSpec{}` join lists are preserved from loaded files but not editable yet.