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

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 now DISCOVER what's in the store instead of needing every id up front.

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.

ONE query loads every iteration's tool_calls Nippy BLOB across the whole session branch (all prior turns AND all earlier iterations of the current turn — both are already persisted by the time a later iteration runs, since the loop db-store-iteration!s each iteration before asking the model again). The Nippy :forms are decoded in Clojure and the form whose :svar/tool-call-id matches a requested id yields its :result.

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)

Per-session turn aggregates for the WHOLE store in ONE grouped query: {soul-id-str {:turn-count n :latest-turn-at Date}}. Powers the session picker summaries (turn_count + modified_at folded into list-sessions) without an N+1 per-session db-list-session-turns hydration. 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-set-filesystem-roots! clj

(db-workspace-set-filesystem-roots! db-info workspace-id roots)

Persist the workspace's extra filesystem roots as a JSON array of canonical path strings. roots is a coll of strings (deduped/canonicalized by the caller). Empty/nil stores NULL (no extra roots). Returns the updated record.

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-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)