Persist

# persist — schema-first persistence Your `deftype` schema is the source of truth. `deft/persist` derives a storage layout from it (SQLite DDL today, via the `src/sqlite.dft` backend) and hands back typed accessors. Reads convert rows back through `schema/from-value`; writes validate through `schema/validate` first — the same rules govern construction, decoding, and the database's own constraints (`NOT NULL`, `CHECK`, `UNIQUE`). The API is async-first: pure functions (`persist/sql`, `persist/name-of`, `persist/repo`, the codecs) are synchronous, while every store operation returns a `Future` — `[await [persist/find …]]`, or `… |> await` mid-pipeline. Awaiting suspends only the calling coroutine, so TUI app threads keep rendering while the store works, and a remote backend (Postgres, …) fits the same surface without fighting the API. ## Quick Start

import "deft/persist" as persist

deftype Sale %{
    :id     %{ :type :number :primary-key true }
    :region %{ :type :string :required true :index true }
    :month  %{ :type :string :required true }
    :amount %{ :type :number :default 0 } }

set store [sqlite/open ".deft/app.db"]
[await [persist/ensure $store :Sale]]           # idempotent layout create
set sales [persist/repo $store :Sale]           # typed accessor (sync)

[await [persist/insert $sales %Sale{ :region "West" :month "jan" :amount 4 }]]
# => %Sale{:id 1 :region "West" :month "jan" :amount 4}

[await [persist/get $sales 1]]                  # → %Sale{...} or nil
[await [persist/update $sales %{:id 1 :amount 9}]]   # plain map = partial patch
[await [persist/delete $sales 1]]               # → rows changed

Everything is receiver-first, so await mid-pipeline and keep going:

set rows ([persist/find $sales %{ :where %{ :region "West" } :order %{ :month :asc } }]
    |> await
    |> filter {|s| ($s~amount > 2)}
    |> map {|s| "$s~month: $s~amount"}
    |> collect)

# Fan out across stores/queries — multi-await, or await-all for a list:
[await [persist/count $sales] [persist/count $sales %{ :where %{ :month "jan" } }]]

## Results Awaiting resolves to plain success values — vectors of typed records, single records, numbers, `nil` — so they flow straight into `|>` pipelines. Failure resolves to a `%Err{ :message … :path? }`; check with `err?` after the await. Validation failures keep the path-annotated shape of `schema/validate`, so you can point the user at the offending field:

set r [await [persist/insert $sales %{:region "West" :bogus 1}]]
if [err? $r] {
    echo "rejected:" $r~message          # unknown field 'bogus'
}

## API `types` accepts a single type, a vector, or a `%{type opts}` map for per-type overrides. Sync functions return values directly; async functions return a Future — await them. | Function | Returns | Purpose | |----------|---------|---------| | `[persist/sql :Type ?opts]` | string | Backend definition SQL — table DDL or `CREATE VIEW` (pure, inspectable) | | `[persist/name-of :Type]` | string | Storage name (`:HTTPServer` → `"http_server"`) | | `[persist/repo $store :Type ?opts]` | `%PersistRepo{}` | Typed accessor (pure binding) | | `[persist/view $store $view]` | `%PersistRepo{}` | Read-only typed accessor over a declarative view (pure binding) | | `[persist/ensure $store refs ?opts]` | Future → true | Create missing layout: tables and/or views (idempotent) | | `[persist/migrate $store refs ?opts]` | Future → report | Align layout additively + preflight data + create/recreate views | | `[persist/check $store refs ?opts]` | Future → report | Report rows the schema rejects (tables and views) | | `[persist/all $repo ?spec]` | Future → records | All rows as typed values | | `[persist/find $repo ?spec]` | Future → records | Filtered / sorted / paged rows | | `[persist/get $repo id]` | Future → record/nil | One row by primary key | | `[persist/insert $repo value]` | Future → record | Validate + INSERT → stored value | | `[persist/insert-many $repo values]` | Future → records | All-or-nothing batch insert | | `[persist/update $repo value]` | Future → record | Validate + UPDATE → stored value | | `[persist/upsert $repo value]` | Future → record | INSERT … ON CONFLICT(pk) DO UPDATE | | `[persist/delete $repo id]` | Future → number | DELETE by primary key → rows changed | | `[persist/count $repo ?spec]` | Future → number | Row count, with optional `:where` | | `[persist/query $repo sql ?params]` | Future → records | Raw SQL → typed rows | | `[persist/pivot $store $ref spec]` | Future → matrix | Cross-tabulation over a type or view | | `[persist/foreign-keys $store ?on]` | Future → true | Toggle FK enforcement on the connection (default ON) | | `[persist/meta-ensure $store]` | Future → true | Create the reserved `_persist_meta_` table | | `[persist/meta-read $store scope ?version]` | Future → entry/nil | Read a stored schema blob (current by default) | | `[persist/meta-history $store scope]` | Future → entries | All retained versions for a scope | | `[persist/meta-check-text $store scope version text]` | Future → `%{:ok …}`/`%Err` | Compare a raw blob without parsing the stored one | | `[persist/meta-write-text $store text opts]` | Future → entry | Write a raw blob (`:scope`, `:version`) | | `[persist/meta-record-text $store text scope version mode]` | Future → entry | `:if-missing`, `:auto` (bump on change), or `:strict` | | `[persist/meta-prune $store scope ?keep]` | Future → number | Drop old non-current versions | `%PersistRepo{}` implements the `Persistable` protocol, so postfix dispatch works too — the result is still a Future: `[await [$sales(all)]]`, `[await [$sales(get 1)]]`, `[await [$sales(insert-many $batch)]]`. ## Async, transactions, and backends - One handle, one mode. The first persist call flips its SQLite handle to async-only; raw `sqlite/*` sync functions on that handle then return `%Err`. Drive outer transactions with the async verbs (`sqlite/begin-async` / `commit-async` / `rollback-async`), use `persist/query` for raw reads, or open a second handle. - Ordering. All statements for a handle are FIFO on its executor thread, so multi-statement sequences (transactions, savepoints) stay in order; `insert-many`'s savepoint nests inside an outer async transaction. - Concurrency. Different handles run in parallel — await several futures at once with `[await $f1 $f2 …]` (or `await-all` from `deft/stdext/async`) to fan out across repos; a TUI coroutine awaiting a store keeps painting. - Backends. Every store operation funnels through the backend's `io-*` driver (`src/sqlite.dft`): `io-exec`, `io-query`, `io-query-one`, `io-scalar`, `io-query-cols`, all `@async`. A remote backend implements the same five with the same semantics and the facade (and every caller) is unchanged. Keep stores' raw SQL dialect access behind `persist/query`. ### Common options | Option | Applies to | Meaning | |--------|-----------|---------| | `:name "…"` | all | Storage name override | | `:id true/false` | all | Implicit `id` primary key column (default true) | | `:indexes @{…}` | ensure/sql/migrate | Composite index specs `%{:fields @{…} :name :unique}` | | `:append @{sql…}` | ensure/sql/migrate | Extra raw statements | | `:sample n` / `:all true` | migrate/check | Preflight scan size (default 1000) | | `:force true` | migrate | Apply CHECK-bearing columns despite invalid rows | | `:check true` | migrate | Run the preflight scan (default true) | ### Find specs `%{ :where %{field value} :order %{field :asc|:desc} :limit n :offset n }` — all keys optional. `:where` is equality only (`nil` → `IS NULL`), one `:order` field, values are parameterized. Unknown fields or bad directions error with `:path`. For anything richer use `persist/query`. ### Pivot specs

[await [persist/pivot $store :Sale %{
    :rows  @{:region}         # 1+ row-key fields
    :cols  @{:year :month}    # 1+ column-key levels
    :value :amount
    :agg   :sum               # sum|avg|min|max|count|first (default sum)
    :where "amount > 0"       # raw SQL filter, applied to both axes
    :col-order :asc
    :name "persist_pivot" }]]
# → %{ :name :cols @{"region" "2024.0 / jan" …} :rows @{%{…} …} }

Compiles to the built-in SQLite `crosstab` virtual table (rowsql built from the relation, column set via `SELECT DISTINCT`). `$store` position accepts a type or a `%PersistView{}` — crosstabs over views just work. Returns raw (key normalized) rows — pivot column names are dynamic, so values stay raw. ## Declarative views A view projects one or more deftypes into a real, persistent `CREATE VIEW` in the store — visible to any SQLite client, usable in `persist/query` and crosstabs, but read-only through persist. - The shape is a `deftype` (decoding/validation reuse the normal codec). - Joins are declared from the schema: a shape field with `:via @{:ref-field … final-field}` walks the base type's `:references` chain (each hop but the last must be a reference field), auto-generating LEFT JOINs. Multi-hop: `:via @{:person :org :label}`. - Fields without `:via`/`:from` are base columns; `:from @{:alias field}` picks a column from an explicit `%JoinSpec{}` alias. - The escape hatch for schemas without `:references`: `:joins @{ %JoinSpec{ :alias :u :type :Person :kind :left/:inner` :on @{ %{:left @{base-alias field} :right @{:u field}} } } }`.` Base alias = `:as` or snake(`:from`).

deftype Sale %{
    :id     %{:type :number :primary-key true}
    :region %{:type :string}
    :buyer  %{:type :number :references %{:type :User :field :id}} }

deftype SaleView %{
    :id     %{:type :number :primary-key true}
    :region %{}
    :amount %{}
    :buyer  %{:type :string :via @{:buyer :name}} }

def sales-by-buyer %PersistView{ :from :Sale :shape :SaleView }

[await [persist/ensure $store @{:Sale :User $sales-by-buyer}]]
# CREATE VIEW IF NOT EXISTS "sale_view" ("id", "region", "buyer") AS
#   SELECT "sale"."id", "sale"."region", "t1"."name"
#   FROM "sale" "sale" LEFT JOIN "user" "t1" ON "t1"."id" = "sale"."buyer"

set buyers [persist/view $store $sales-by-buyer]   # read-only repo
set rows [await [persist/all $buyers]]             # → @{%SaleView{…} …}

[await [persist/pivot $store $sales-by-buyer %{    # crosstab over the view
    :rows @{:region} :cols @{:buyer} :value :amount }]]

Spec: `%PersistView{ :from :shape :name? :join :left|:inner(dflt :left)` :as? :joins? :where? }` (`:where` is a raw SQL predicate). Shape field` keys: `:via`, `:from`, `:join` (per-field kind override; conflicting kinds on a shared path are an error). Semantics: writes through a view repo return `%Err` ("read-only"). `persist/migrate` compares each generated view against `sqlite_master.sql` and reuses unchanged definitions, dropping changed ones (reverse order) and recreating them (declaration order); `persist/check` scans view rows too (no rowids). Views hold no data, so recreation is always safe. v1 projects columns + joins only — aggregates/expressions use `persist/query` or explicit `:joins`. ## Field-spec storage keys (hybrid) Core mapping derives from the existing `deftype` vocabulary (`:type`, `:required`, `:default`, `:enum`); storage-specific keys can be declared on the field spec: | Key | Effect | |-----|--------| | `:primary-key true` | `PRIMARY KEY`; the repo's key for get/update/upsert/delete | | `:unique true` | `UNIQUE` column constraint | | `:index true` | Single-column index | | `:sql-type "INTEGER"` | Raw column type override | | `:sql-default "datetime('now')"` | Raw expression default (parenthesized; cannot be added by ALTER — migrate reports it blocked) | | `:references %{:type :User :field :id :on-delete :cascade}` | `REFERENCES` clause; the child-side column is auto-indexed (SQLite doesn't index FK columns itself) | No `:primary-key` → an implicit `id INTEGER PRIMARY KEY AUTOINCREMENT` column is added unless `:id false`. If a field is named `id` but not marked `:primary-key`, no implicit column is added (name clash) and keyed operations report "no primary key". ### Type mapping | Deft type | SQLite | Notes | |-----------|--------|-------| | `:string` | TEXT | | | `:number` | REAL | f64 — for money use INTEGER cents or TEXT and `:sql-type` | | `:boolean` | INTEGER | 1/0, decoded back to booleans | | `:keyword` | TEXT | bare name (`:open` → `"open"`) | | `:list` / `:set` / `:map` / `:any` | TEXT | `deft/stringify`, round-trips via `deft/parse` | | nested `deftype` | TEXT | stringified tagged map, decoded recursively | | `:many true` fields | TEXT | always stored stringified as a vector | Rows are decoded through `schema/from-value` with `:coerce true` — extra DB columns (including an implicit `id`) are preserved as extra keys on the typed map, so `$row~id` works even when `id` is not a declared field. ## Referential integrity SQLite enforces `REFERENCES` clauses only when `PRAGMA foreign_keys` is ON — and the pragma is per-connection, OFF by default. `persist/ensure` and `persist/migrate` switch it on for the connection before doing anything, so every write through a repo afterwards is checked (invalid parent references and forbidden parent deletes come back as `%Err` with SQLite's `FOREIGN KEY constraint failed`). Stores whose tables were created out of band should call `[await [persist/foreign-keys $store]]` (or the same `PRAGMA` via `sqlite/exec-async`) once. Two consequences worth knowing: - adding a `:references` column by `ALTER TABLE` requires a NULL default with FKs enabled — `persist/migrate` reports such columns as blocked; - SQLite does not index foreign-key child columns, so persist generates `CREATE INDEX IF NOT EXISTS "idx_<table>_<col>"` for every `:references` field automatically (on create and on migrate). ## Write semantics - `insert` validates strictly (unknown fields are errors), applies `:default`s and coercion, omits nil fields (DB defaults/NULL apply), and returns the stored row (generated key included). - `insert-many` takes a vector (or a single value) and inserts the whole batch inside one transaction — a savepoint, so it nests inside an outer `sqlite/begin-async`. All rows are validated/encoded up front, so a validation failure never touches the store; any failure (validation or SQL) rolls the batch back and returns the `%Err` with the failing row's 0-based `:index` (and the original error under `:error`). On success it returns the vector of stored rows. - `update` accepts two shapes: - a plain map → partial patch: fields are merged onto the current row first, so required fields and omitted columns keep their stored values; - a `%Type{…}` value → full state: defaults applied by the tagged literal are part of the write. - `upsert` = insert when the key is absent, else `ON CONFLICT DO UPDATE`. - `delete` returns rows changed. - Concurrency: updates are read-modify-write by key (no optimistic locking yet); wrap multi-statement work in `sqlite/begin-async` / `commit-async`. ## Schema metadata Every persist-managed database carries a reserved `_persist_meta_` table. It stores one canonical schema-descriptor blob per (`scope`, `version`); `is_current` marks the blob that produced the live data. A scope is a schema/model name (`"default"` for direct persist use). History is retained for diagnostics and explicit migrations. ``` _persist_meta_(scope, version, meta_version, persist_version, deft_version, schema_hash, is_current, types, created_at, updated_at) ```` - `types` is plain `deft/stringify` data — never executable source. - `meta_version` is the hard format gate; `persist_version` records the library version and is checked by major version. - `schema_hash` is SHA-256 over the canonical blob, so loads can verify a dataset without parsing a potentially large descriptor. - Same version + different hash is an error (bump the version); a newer version inserts a new row and keeps the old one current=0. - `persist/ensure` records a merged descriptor for direct use; schema-builder-generated `persist.dft` records the full schema blob (types, views, libraries, model graph), and `persist/migrate` updates it after a clean additive migration. - `_persist_meta_` is reserved: a type mapping to that storage name is rejected. The blob is data, not code: loaders still need the compiled `deftype`/model modules. It enables exact version checks, DB-to-schema regeneration, and safe diagnostics. ## Migration `persist/migrate` is additive by design: 1. missing tables are created (from the same `persist/sql` plan); 2. missing columns are added via `ALTER TABLE` when SQLite allows it — `PRIMARY KEY`, `UNIQUE`, NOT NULL-without-default and expression defaults are blocked and reported with a reason; 3. indexes are created (`IF NOT EXISTS`); 4. the preflight (`persist/check`) decodes a sample (default 1000, `:all true` for everything) and reports rows the schema rejects under `:invalid`; when any exist, CHECK-bearing new columns are blocked unless `:force true`; 5. extra DB columns and affinity drift are reported in `:missing` and `:changed` — never dropped or rewritten. `persist/check` runs the same scan standalone (audit after out-of-band edits). Schema removals and type changes are diagnostics only. ## Persisting app state A common use is an app saving its configuration/last state. For small shared preferences use `config/*`; for app-owned typed state (todos, documents, filters, selection, related records) keep a schema directory beside the app and drive it through the generated model:

from "todo.scm/model.dft" import new-todo

set m [new-todo]
set r [await [$m load ".deft/todo.db"]]   # :no-meta = first run
set todo [$m create %Todo{ :id "t1" :title "Buy milk" }]
[await [$m persist ".deft/todo.db"]]      # snapshot replace

`[$model persist]` is a snapshot, not an append: the current app state becomes the dataset, and the versioned schema blob is recorded in `_persist_meta_`. `[$model load]` refuses a mismatched schema/version until the generated `migrate` runs. See the runnable todo app (`packages/todo`) and the manual page `help/manual/03-Platform/10-app-state.md` for the full app-state pattern. ## Limits - SQLite backend only (v1); the async facade is store-neutral and the `io-*` driver is the seam a remote backend slots into. - Metadata stores the schema at the moment of persist/migrate; direct `ensure` calls only add missing type entries and do not reconcile changed columns. - Numbers are f64 (`:sql-type` for exact storage); BLOBs read as nil. - No relationships/joins beyond declarative views (use `persist/query` for anything richer), no optimistic locking, no bulk update/delete (loop or use `sqlite/exec-async` inside a transaction). - Views are read-only in SQLite (no INSTEAD OF triggers), project columns + joins only (no `:expr`/aggregates yet), and cannot wrap `temp` crosstab vtabs — but crosstab over a view works. - `:where` in pivot/view specs and `persist/query` are raw SQL escape hatches — parameterize or trust the source.