com.blockether.vis.ext.persistance-sqlite.core — com.blockether/vis-persistance-sqlite 0.1.13

SQLite store - V1 schema implementation.

Every public defn in this file is dispatched dynamically by vis-sdk.core/defdelegate via ns-resolve; clj-kondo never sees the call sites. The ns-level config above silences :unused-public-var for the whole file. The actual call surface is verified through the storage facade tests.

Tables (V1__schema.sql): session_soul, session_state, session_turn_soul, session_turn_state, session_turn_iteration, llm_routing_event, extension_aggregate, log

Connection lifecycle: (db-open! db-spec) -> {:datasource ds :path ...} (db-close! store) -> idempotent dispose

->date clj

->id clj

->json clj

->kw clj

->kw-back clj

->ref clj

->uuid clj

<-json clj

<-json-lazy clj

db-adopt-and-reorder-project-sessions! clj

(db-adopt-and-reorder-project-sessions! db-info project-id session-ids)

Persist a manual tab order for project-id in one atomic transaction. Named sessions with no project are adopted first; sessions owned by another project remain guests and are never stolen. Every target-project member is then renumbered exactly as in db-reorder-project-sessions!. Returns the member count.

db-claim-session! clj

(db-claim-session! db-info session-id)

Mark an unclaimed warm-pool soul as a real conversation. Idempotent.

db-close! clj

(db-close! store)

Idempotent dispose. Closes the Hikari pool when we own it; for :external mode (caller-supplied DataSource) it's a no-op - the caller still owns the handle they passed in.

db-create-extension-aggregate! clj

(db-create-extension-aggregate! db-info opts)

db-create-project! clj

(db-create-project! db-info
                    {:keys [name color owner-id position workspace-root]})

Create a project (always cross-channel). :name is required (non-blank). :owner-id defaults to "local". :position, when omitted, appends after the owner's current projects. Returns the created project (canonical shape).

db-delete-extension-aggregates! clj

(db-delete-extension-aggregates! db-info opts)

db-delete-project! clj

(db-delete-project! db-info project-id)

Delete a project. Member souls are scattered back to project-less by the ON DELETE SET NULL FK - conversations are NEVER deleted.

db-delete-session-tree! clj

(db-delete-session-tree! db-info session-soul-id)

db-error->user-message clj

(db-error->user-message e)

Translate SQLite persistence exceptions into actionable user text. Return nil for non-SQLite errors so the facade can try other adapters or fall back to (ex-message e).

db-find-session-by-external clj

(db-find-session-by-external db-info channel external-id)

db-fork-session! clj

(db-fork-session! db-info
                  session-id
                  {:keys [system-prompt provider model title workspace-id]})

Fork a session. Creates a new session_state with parent_state_id pointing to the current latest state. The forked state gets a '(fork)' suffix when no title is supplied. The parent state gets a '[forked]' suffix to mark the divergence point. Returns the new state UUID.

Required opt: :workspace-id — every session_state must be pinned to exactly one workspace. Callers (screen handlers, cli) call workspace/spawn-branch! or workspace/ensure-trunk! first and pass the returned id here.

db-fork-session-at-turn! clj

(db-fork-session-at-turn! db-info
                          session-id
                          {:keys [title workspace-id through-turn-id]})

Fork a session UP TO AND INCLUDING the turn whose session_turn_soul id is :through-turn-id, into a brand-new INDEPENDENT session.

Unlike db-fork-session! (which adds a new session_state UNDER the same soul, so the soul's latest-leaf chain keeps ALL prior turns), this mints a fresh session_soul + root session_state (version 0, parent_state_id NULL) and DEEP-COPIES every transcript turn from the start through :through-turn-id into it — each turn's session_turn_soul, its LATEST session_turn_state, that state's session_turn_iteration rows, and the turn's session_attachment rows (ids remapped). The SOURCE session is left completely untouched; the fork is a separate tab/session whose history is exactly those turns and which continues fresh from there.

Copies rows generically (SELECT * → re-insert with remapped id/FK columns), so it stays correct as columns evolve. Required opt: :workspace-id. Returns the new SESSION SOUL UUID (for resume-session, which resolves sessions by session_soul.id), or nil when :through-turn-id is not a turn of the source session (or the env has no datasource).

db-get-extension-aggregate clj

(db-get-extension-aggregate db-info opts)

db-get-project clj

(db-get-project db-info project-id)

Return one project (canonical shape, with live :session-count) or nil.

db-get-project-by-root clj

(db-get-project-by-root db-info owner-id root)

Return the project bound to canonical workspace root for owner-id (default "local"), or nil. Backs the TUI's launch-dir -> project (tab set) resolution. root matches the stored project.workspace_root exactly (callers pass a canonical path).

db-get-session clj

(db-get-session db-info session-id)

db-get-session-model-pref clj

(db-get-session-model-pref db-info session-id)

The persisted model preference for a session (soul id) as {:provider p :model m}, or nil for the router default. Channel-neutral: web + TUI route through the same value.

db-latest-session-state-id clj

(db-latest-session-state-id db-info session-id)

Return the latest session_state.id UUID for the soul behind session-id (which is the soul id). Used by the iteration loop to bind TURN_SESSION_STATE_ID so the agent can reference the exact session_state row this turn was attached to. Returns nil when the session is unknown or the env has no datasource.

db-list-extension-aggregates clj

(db-list-extension-aggregates db-info opts)

db-list-iteration-attachments clj

(db-list-iteration-attachments db-info iteration-id)

Ordered OUTBOUND tool artifacts persisted for ONE session_turn_iteration (its id, not the soul - the tool rail only). Returns [{:id :source :tool-call-id :position :kind :media-type :filename :size :base64}] - base64-encoded inline bytes matching the shape the prompt assembler + web history re-render consume - ordered by (tool_call_id, position), or [] when none / no datasource.

db-list-iterations-attachments clj

(db-list-iterations-attachments db-info iteration-ids)

Batch variant of db-list-iteration-attachments: OUTBOUND tool artifacts for MANY session_turn_iteration ids in ONE query, grouped as {iteration-id-string [{:id :source :tool-call-id :position :kind :media-type :filename :size :base64} …]} (each vector ordered by (tool_call_id, position)). Missing ids are simply absent. Safe: no ids / no datasource -> {}. Lets a history replay hydrate a whole conversation's generated images without an N+1 per-iteration query.

db-list-projects clj

(db-list-projects db-info {:keys [owner-id include-archived?]})

List projects for owner-id (default "local"), each with a live :session-count, ordered by (position, created_at). Projects are CROSS-CHANNEL by construction — every channel sees the same set. Archived projects are hidden unless :include-archived? is truthy.

db-list-session-attachments clj

(db-list-session-attachments db-info session-id)

EVERY attachment across a whole SESSION - user images AND tool artifacts - scoped to the active branch's state chain (root->leaf). ONE join through session_turn_soul scopes session_attachment by the session's states; both rails answer because tool rows denormalize the soul. Returns [{:id :source :turn-soul-id :iteration-id :tool-call-id :position :kind :media-type :filename :size :storage-uri :base64}] ordered by (turn position, source [user first], position) so callers split by :source (:user / :tool) or slice per :turn-soul-id - or [] when none / no datasource. The whole-session roll-up sibling of db-list-turn-all-attachments (one turn) and db-list-iteration-attachments (one iteration); replaces the introspection reader's hand-rolled per-turn N+1 walk.

db-list-session-states clj

(db-list-session-states db-info session-id)

List every session_state row for the soul behind session-id, oldest version first. Each row maps to {:state-id :version :parent-state-id :title :system-prompt :provider :model :created-at :turn-count} - the raw fork tree of one session soul.

The trunk is :version 0 with :parent-state-id nil. A fork is any row whose :parent-state-id points at another :state-id in the same vector; group-by :parent-state-id to walk the tree.

:turn-count is the number of session_turn_soul rows hanging off that specific state - cheap to compute, useful when triaging which branch is active.

Returns [] (never nil) when the session is unknown or the env has no datasource.

db-list-session-turn-iterations clj

(db-list-session-turn-iterations db-info session-turn-id)

db-list-session-turn-states clj

(db-list-session-turn-states db-info session-turn-id)

List every session_turn_state row (i.e. every retry version) for the soul behind session-turn-id, oldest version first. Each row maps to {:state-id :version :forked-from-session-turn-state-id :status :prior-outcome :provider :model :created-at :iteration-count}.

Version 0 with :forked-from-session-turn-state-id nil is the original run; any higher version is a retry, with :forked-from-session-turn-state-id pointing at the previous :state-id. :iteration-count is the number of iteration rows attached to that specific state - retries get their own iteration trace.

Returns [] (never nil) when the turn is unknown or the env has no datasource.

db-list-session-turns clj

(db-list-session-turns db-info session-id)

db-list-session-turns-by-status clj

(db-list-session-turns-by-status db-info status)

db-list-sessions clj

(db-list-sessions db-info channel)

Top-level sessions, newest-first.

channel filters by the session_soul.channel column; pass :all (or nil) for the CROSS-CHANNEL view — every channel's sessions in one list. This is the shared view web + TUI use so a conversation started in one surface is visible and resumable from the other.

db-list-turn-all-attachments clj

(db-list-turn-all-attachments db-info session-turn-soul-id)

EVERY attachment hanging off one TURN - user images AND tool artifacts together - in ONE indexed filter (session_turn_soul_id = ?), the roll-up that single-table unification makes trivial. Tool rows denormalize the soul, so both rails answer the same WHERE. Returns [{:id :source :tool-call-id :position :kind :media-type :filename :size :base64}] ordered by (source, position) so user images lead, then tool artifacts - or [] when none / no datasource. Callers split by :source (:user / :tool) as needed.

db-list-turn-attachments clj

(db-list-turn-attachments db-info session-turn-soul-id)

Ordered INBOUND user images persisted for one session_turn_soul (the user rail only - session_turn_iteration_id IS NULL). Returns [{:id :source :position :kind :media-type :filename :size :base64}] - :id is the bare row uuid for read-back via db-read-attachment, base64-encoded bytes match the shape the prompt assembler + web history re-render consume - or [] when none / no datasource. For user+tool combined see db-list-turn-all-attachments.

db-list-turns-attachments clj

(db-list-turns-attachments db-info session-turn-soul-ids)

Batch variant of db-list-turn-attachments: INBOUND user images for MANY session_turn_soul ids in ONE query (the user rail only - session_turn_iteration_id IS NULL), grouped as {soul-id-string [{:id :source :position :kind :media-type :filename :size :base64} …]} (each vector ordered by :position). Missing ids are simply absent from the map. Safe: no ids / no datasource -> {}. Lets the gateway hydrate a whole session's user images without an N+1 per-turn query.

db-load-ctx-history clj

(db-load-ctx-history db-info session-id)

Return a sorted-by-turn vec of [turn-n ctx-map] pairs for the session. Each ctx-map is the Nippy-decoded :session/turn_state.ctx for the latest version of that turn. Used by introspect-* verbs to reach archived entries and replay any past turn snapshot through the engine's pure-fn introspect-spec / -task / -fact / -archived / -ctx-at helpers.

The session_state chain is honoured (forks see ancestor snapshots ordered by parent_state -> child_state and then by turn position).

db-load-latest-ctx clj

(db-load-latest-ctx db-info session-id)

Load the CTX snapshot (Nippy BLOB) from the latest session_turn_state that has a non-NULL ctx column, scoped to this session_state. Returns the decoded CTX map or nil when the session has no persisted CTX yet. STRING-KEYED: ctx blobs are frozen string-keyed (strings-only end to end).

This is the resume path: on a new turn, the loop reads this back into the ctx-atom so the model picks up where (done …) left off. The cursor is intentionally NOT restored — it's iter-local and gets stamped fresh by the renderer.

db-log! clj

(db-log! db-info entry)

db-native-result-ids-for-session clj

(db-native-result-ids-for-session db-info session-id)

List every persisted NATIVE tool_use id (:svar/tool-call-id) in THIS session branch — all prior turns AND all earlier iterations of the current turn — NEWEST first, de-duped. Only forms carrying a :result (a real native tool call) are included; print-only python_execution forms (they carry :stdout, not :result) are skipped, exactly as db-native-results-for-tool-ids skips them on lookup.

Backs iteration over ntr (keys/items/values/len/iter): the sandbox can DISCOVER what's in the store instead of needing every id up front.

db-native-result-index-for-session clj

(db-native-result-index-for-session db-info session-id)

Browseable index behind ntr.describe(): [{:id :tool :gist}] for every persisted NATIVE tool result in this session branch, NEWEST first, de-duped by id. :tool is the tool that ran, :gist its op-card summary; either is absent when the stored form carried none.

Costs no payload thaw at all — the sandbox can label a whole window of opaque toolu_… ids WITHOUT fetching a single result.

db-native-results-for-tool-ids clj

(db-native-results-for-tool-ids db-info session-id tool-ids)

Batched read for ntr[tool_id]: given a SET of provider tool_use ids (:svar/tool-call-id, e.g. Anthropic toolu_…, OpenAI Chat call_…, OpenAI Responses composite call_…|fc_…), return {tool-id -> result} for every id whose persisted form is found in THIS session's iterations.

The branch index resolves each wanted id to the ONE iteration row that holds it (newest wins if an id somehow appeared twice), so only those rows are fetched and thawed — a fetch of an OLD id no longer decodes the whole branch on the way to it.

Only ids in tool-ids are decoded/returned; an id with no matching form (a hallucinated or python_execution-only id — those carry :stdout, not :result) is simply ABSENT from the returned map, so the caller can raise a clean KeyError-style miss. A form present but with no :result key (a print-only python_execution form) is also absent by construction.

db-open! clj

(db-open! db-spec)

db-put-extension-aggregate! clj

(db-put-extension-aggregate! db-info opts)

db-read-attachment clj

(db-read-attachment db-info attachment-id)

Read ONE persisted attachment by its bare row id (as returned by the db-list-*-attachments listers). There is exactly ONE session_attachment table, so this is a single indexed lookup - no source prefix, no dispatch, no cross-table fallback. :source is derived from the row (:tool when it carries an iteration, else :user). Returns {:id :source :tool-call-id :position :kind :media-type :filename :size :storage-uri :base64} ( :tool-call-id nil for user images) or nil when the id is absent / no datasource. The read-back twin of the listers: a tool re-fetches an artifact it (or an earlier turn, or the user) produced.

db-reorder-project-sessions! clj

(db-reorder-project-sessions! db-info project-id session-ids)

Persist a manual order for the sessions inside project-id. session-ids is the desired leading order; any members not named are kept and appended in their current order. Every member receives a gap-free, 0-based project_position. Only souls already belonging to project-id are touched. Returns the member count.

db-repo-focus-get clj

(db-repo-focus-get db-info repo-id)

Return the workspace_id currently pinned as the focus pointer for repo-id. Nil when no entry exists yet.

db-repo-focus-set! clj

(db-repo-focus-set! db-info repo-id workspace-id)

Upsert the per-repo focus pointer to workspace-id. Updates updated_at_ms to now. Returns the new pointer map.

db-resolve-session-id clj

(db-resolve-session-id db-info selector)

db-retry-session-turn! clj

(db-retry-session-turn! db-info
                        session-turn-soul-id
                        {:keys [status provider model]})

Create a new session_turn_state (version N+1) for an existing session_turn_soul. Used when re-running a turn with a different provider/model or settings. Returns the new session-turn-state UUID.

db-search clj

(db-search db-info query)

(db-search db-info query {:keys [limit owner-table field]})

Search persisted prompts, answers, and thinking through SQLite FTS5.

db-search-session-ids clj

(db-search-session-ids db-info channel query)

Soul ids whose TRANSCRIPT text matches query.

Thin id-only projection of db-search-session-matches (see it for the where-it-hit tags and snippets). channel filters like db-list-sessions (:all/nil = cross-channel). Blank query returns [].

db-search-session-matches clj

(db-search-session-matches db-info channel query)

Sessions whose TRANSCRIPT text matches query, each carrying WHERE it hit and up to transcript-hits-per-session MATCH SNIPPETS:

{:id uuid :in-request? bool :in-reply? bool :request-snippet str-or-nil :reply-snippet str-or-nil :hits [{:side :request|:reply :snippet str :at Date}]}

:in-request? = the user's request (session_turn_soul.user_request) matched; :in-reply? = assistant iteration text matched (llm_assistant_prose / llm_thinking). The raw provider envelope (llm_assistant_message) is deliberately NOT searched — it is wire JSON, not what was said. Both can be true.

Served by the V1 FTS5 indexes (transcript_request_fts / transcript_reply_fts) — single-digit milliseconds instead of the ~400ms full LIKE '%q%' scan of the whole corpus, which is what made TUI/app session search feel frozen. Matching is token PREFIX (dia finds dialogs); a query with NO alphanumeric token at all (???) matches nothing. Newest hits first, sessions ordered by their newest hit.

This is the SERVER-side half of transcript search: the assistant text never crosses the wire, only these snippet windows. channel filters like db-list-sessions (:all/nil = cross-channel). Blank query returns [].

db-session-state-list-for-workspace clj

(db-session-state-list-for-workspace db-info workspace-id)

List session_state rows whose workspace_id = workspace-id, newest first. The 1:1 invariant guarantees at most one ACTIVE row per workspace, but historical version rows (from forks) may exist so the projection is a vec, not a single row. Returns canonical {:id :session-soul-id :title :version :created-at …} shape.

db-session-state-set-workspace! clj

(db-session-state-set-workspace! db-info session-state-id workspace-id)

Pin session-state-id to workspace-id. Caller guarantees the target session_state row exists and has no other workspace pinned (UNIQUE on session_state.workspace_id enforces 1:1).

db-session-turn-stats clj

(db-session-turn-stats db-info)

(db-session-turn-stats db-info session-id)

Per-session turn aggregates: {:turn-count n :latest-turn-at Date :first-request str-or-absent}.

1-arity: the WHOLE store in ONE grouped query, keyed by soul-id string — {soul-id-str stats}. Powers the session picker summaries (turn_count + modified_at folded into list-sessions) without an N+1 per-session db-list-session-turns hydration.

2-arity: the SAME aggregate for ONE session, returned unwrapped (nil when the session has no state rows). GET /v1/sessions/:id needs these two facts as much as the list does — without them a client cannot tell that a session moved — and a detail poll must not scan every session to learn them.

:first-request is the RAW user_request of the session's EARLIEST turn — what the session opened with, so a picker row can show the actual ask beside a generated title. It rides a second grouped query that leans on SQLite's bare-column/min() pairing (exactly ONE aggregate per row, so the pairing is well defined). Callers bound/clean it for display; it is stored text, not a preview.

Counts every turn soul across ALL of a session's states (forks included) — an upper bound of the chain view, but exact for has any turns? and for latest-activity ordering.

db-set-session-model-pref! clj

(db-set-session-model-pref! db-info session-id provider model)

Persist (or clear, with both nil/blank) the PROVIDER + MODEL preference for a session (soul id). Lives on the stable soul, so it survives turn forks/retries.

db-set-session-project! clj

(db-set-session-project! db-info session-id project-id)

Assign the soul behind session-id to project-id; a nil project-id clears membership (removes it from its project AND resets its now-meaningless project_position to 0, so no stale ordinal is left behind). Moving INTO a project the soul does NOT already belong to APPENDS it (its project_position becomes max+1 within that project) so project sessions stay MOVABLE; re-assigning a soul ALREADY in the project is idempotent and keeps its current position. Returns the soul id.

db-store-iteration! clj

(db-store-iteration! db-info
                     {:keys [session-turn-id thinking assistant-prose answer
                             llm-full-duration-ms error llm-routing
                             cache-created-tokens llm-provider llm-model
                             llm-assistant-message llm-returned-empty-code?
                             tokens cost-usd attachments]
                      :as opts})

Store one iteration row in a single SQLite transaction.

The executed tool-call records travel on the iteration row's tool_calls BLOB; there is no sandbox-var sidecar persistence. Cross-turn references go through :session/facts and introspect-iter / introspect-turn (DB reads against session_turn_iteration.tool_calls).

Returns the iteration UUID.

db-store-session! clj

(db-store-session! db-info
                   {:keys [channel external-id title system-prompt provider
                           model workspace-id parent-state-id claimed? owner-id]
                    :or {claimed? true}})

Create session_soul + initial session_state (version 0). Returns the session-soul UUID.

Required opt: :workspace-id — session_state.workspace_id is NOT NULL after V1, enforcing the 1:1 invariant. The caller (loop/create-environment) calls workspace/ensure-trunk! to mint a trunk workspace before invoking this fn.

Session identity and LLM root defaults are first-class columns: session_soul.channel / external_id session_state.system_prompt / llm_root_provider / llm_root_model session_state.title.

db-store-session-turn! clj

(db-store-session-turn! db-info
                        {:keys [parent-session-id user-request status
                                attachments]})

Create session_turn_soul + initial session_turn_state (version 0).

:attachments - EVERY validated image attached to the turn [{:media-type :base64 :size :filename}], both INLINE (web/API) uploads and terminal-drop disk images - persist as session_attachment BLOB rows (user rail: NULL iteration) so resume + history re-render survive a restart even after the source file moves or is deleted.

Returns the session-turn-soul UUID.

db-store-stale? clj

(db-store-stale? store db-spec)

True when a persistent SQLite store no longer matches the requested db-spec or the file at the same path was replaced under this JVM. When true, the facade closes the old shared pool and opens a new one. Paths compare CANONICALIZED on both sides.

db-swap-extension-aggregate! clj

(db-swap-extension-aggregate! db-info opts f args)

db-turn-history clj

(db-turn-history db-info session-id)

Per-turn history rows with canonical typed :content.

db-update-project! clj

(db-update-project! db-info
                    project-id
                    {:keys [name color position archived? workspace-root]
                     :as opts})

Patch a project: any of :name (non-blank), :color, :position, :archived? (true stamps archived_at=now, false clears it). Returns the updated project (canonical shape) or nil when nothing to change.

db-update-session-title! clj

(db-update-session-title! db-info session-id title)

db-update-session-turn! clj

(db-update-session-turn! db-info
                         session-turn-id
                         {:keys [content iteration-count duration-ms status
                                 tokens cost prior-outcome ctx error]})

Update the latest session_turn_state with final outcome.

When :prior-outcome is provided (one of :complete, :cancelled, :error), it lands in the dedicated prior_outcome column so the next turn's handover digest can read it without scanning every iteration. The column is bounded by a CHECK constraint at the schema level.

db-workspace-for-session clj

(db-workspace-for-session db-info session-state-id)

Return the workspace pinned to session-state-id, or nil. Always non-nil after step-4 wiring (1:1 invariant); nil only during the transitional window where session_state may not yet have a workspace.

db-workspace-get clj

(db-workspace-get db-info workspace-id)

db-workspace-insert! clj

(db-workspace-insert! db-info
                      {:keys [id repo-id repo-root root label fork-ms
                              apply-fork-ms workspace-kind workspace-backend
                              parent-workspace-id state]})

Insert a workspace row. Returns the inserted record (canonical shape).

Required: :repo-id :repo-root :root Optional: :id (defaults to a new UUID), :label, :fork-ms, :apply-fork-ms, :workspace-kind, :workspace-backend, :parent-workspace-id, :state (defaults to :active)

db-workspace-list-by-repo clj

(db-workspace-list-by-repo db-info repo-id)

(db-workspace-list-by-repo db-info repo-id state-set)

List workspaces in repo-id, optionally filtered to a state-set of keywords (e.g. #{:active :merging}). Newest first.

db-workspace-list-drafts clj

(db-workspace-list-drafts db-info)

Every draft workspace across all repos, newest first. Housekeeping reads this to decide which draft clones on disk have gone stale; it is deliberately repo-agnostic because the drafts store is a single ~/.vis/drafts tree shared by every trunk.

db-workspace-touch-focus! clj

(db-workspace-touch-focus! db-info workspace-id)

Stamp last_focused_at_ms to now-ms on the workspace row. Called by workspace/focus!. Returns the updated record.

db-workspace-update-label! clj

(db-workspace-update-label! db-info workspace-id label)

Set the human-friendly :label override. Pass nil to clear the label and fall back to the heuristic. Returns the updated record.

db-workspace-update-state! clj

(db-workspace-update-state! db-info workspace-id new-state)

Transition workspace-id to new-state (:active | :discarded). Stamps discarded_at on :discarded. Returns the updated record.

ds clj

execute! clj

(execute! db-info q)

new-id clj

(new-id)

new-uuid clj

normalize-status clj

now-ms clj

query! clj

(query! db-info q)

Run a HoneySQL map and return rows with unqualified lower-case keys.

query-one! clj

(query-one! db-info q)