Skip to content

data-model.md

Latest commit

 

History

History
401 lines (346 loc) · 31.1 KB

data-model.md

File metadata and controls

401 lines (346 loc) · 31.1 KB
Raw

Data model

Everything the desktop app knows lives in one SQLite file, surfsense.db, and in the files under data/; the job queues live apart in huey.db. The schema is a subset of the hosted SurfSense domain model, keeping the words users know (workspace, document, chunk, chat thread, artifact) and fixing the names that had drifted. The models are the source of truth, Alembic is the only thing that creates schema, and every revision is written by hand, because the database is one user's only copy.

Code: shared/db.py, shared/migrations.py, alembic/versions/, the models.py in each folder of modules/ Decisions: ADR 0005, ADR 0003, ADR 0006, ADR 0007, ADR 0036, ADR 0018

Principles

  1. Same domain language. Workspace, document, chunk, artifact, chat thread and chat message mean what they mean in hosted SurfSense.
  2. Subset. Tables and columns the desktop app does not ship are left out: users, memberships, connectors, Zero, the git knowledge base, LangGraph state.
  3. Fix on paste. Stale new_* prefixes, redundant columns and JSONB status blobs become the local shapes below.
  4. No auth. There is no users table, no memberships and no tokens.
  5. SQLite. One file, with JSON columns only where they earn their keep: document metadata, message content, artifact metadata.
  6. Queue mechanics stay in huey.db. User-visible job state is documents.status, and plugin_runs.status for a plugin's run.

The conventions every table follows are in shared/db.py:

  • Enums are text behind a CHECK constraint. SQLite has no enum type, so text_enum() stores the values and names a CHECK that rejects anything else.
  • Constraints are named by a fixed naming convention, because SQLite lets them stay unnamed and Alembic's batch mode cannot drop what it cannot name.
  • Timestamps are UTC wall time. SQLite keeps no offset; values are written in UTC and stamped UTC when read.
  • Every connection turns foreign keys on, uses WAL and a 5-second busy timeout, loads sqlite-vec, and begins transactions with BEGIN IMMEDIATE.
  • Every model is imported at startup (import_models()), because relationships name their targets as strings.

Naming: keep vs fix

Concept Hosted Local Notes
Workspace workspaces workspaces keep
Document documents documents keep
Chunk chunks chunks keep
Chat thread new_chat_threads chat_threads drop the stale new_ prefix
Chat message new_chat_messages chat_messages drop the stale new_ prefix
Message to thread thread_id chat_thread_id explicit on chat_messages and artifacts
Document status JSONB {"state": ...} status text pending, processing, ready, failed, cancelled
Dedup key unique_identifier_hash dedup_key a SHA-256 of the file's bytes, not of its name
Body text content and source_markdown content one markdown body
Artifact sidecar artifacts artifacts keep, in the ADR 0003 shape
Artifact metadata a column named metadata artifact_metadata no alias

Tables

workspaces

Column Notes
id, name, created_at, updated_at a name is 1 to 200 characters after trimming
cloud_id nullable, unique: the hosted workspace an import came from, so re-importing the same bundle reuses the row (import.md)
has_unkeyed_imported_threads true only when a message signature identifies chat history imported before hosted thread ids were retained; re-import claims a uniquely matching legacy thread and otherwise leaves that history untouched

The API seeds one workspace, "My Workspace", at startup when none exists. Deleting a workspace first stops its plugin runs (stop_workspace_runs.py), then cascades its documents, threads, artifacts and runs.

documents

Column Notes
id, workspace_id foreign key to workspaces, cascading
title at most 500 characters when set through the document routes
document_type FILE (uploaded or imported), NOTE (written in the app), ARTIFACT (a Studio body)
status pending by default; cancelled arrived in 0011
error_message why a job failed, at most 500 characters; cleared by retry and by a later success
content the markdown body: extracted text, the note itself, or an artifact's body
content_hash SHA-256 of a file's bytes, equal to dedup_key for an uploaded or imported file; 0025 copied it from dedup_key; null for notes and artifacts
dedup_key SHA-256 of an uploaded or imported file's bytes; null for notes and artifacts
document_metadata JSON: mime_type, size_bytes, suffix for files; import adds folder_path, source and the hosted ids; a recording's ingest adds transcription, {language, duration_s, model} once transcribed or {awaiting: true} while it waits for a speech model (documents.md)
embedding_index_id foreign key to embedding_indexes: the index its chunks were embedded into; null until its first ingest
folder_id foreign key to folders, ON DELETE SET NULL, from 0025: every FILE and NOTE is filed; null is an unfiled artifact, or a source the backstop unfiled, which scopes and the tree treat as the top of the Library
created_at, updated_at

documents_workspace indexes workspace_id and documents_folder indexes folder_id. documents_folder_dedup_key is unique on (workspace_id, folder_id, dedup_key) where dedup_key IS NOT NULL, so dedup is per folder: a copied folder keeps a file that already sits in another one, and rows without a key stay out of it. Routes and behaviour are in documents.md.

source_roots and folders

Table Columns Notes
source_roots id, workspace_id, kind, name, name_key, disk_path, state, created_at, updated_at kind is managed or linked; each workspace has one managed root, the Library (partial unique index), made by 0025 for existing workspaces and on first use otherwise. Linked roots are designed, not built (proposal); state already holds every value they will need, so adding one never rebuilds the table
folders id, workspace_id, root_id, parent_id, name, name_key, disk_name, role, state, trash_kind, trashed_at, created_at, updated_at a root's own folder has no parent and is depth 0; Library folders go 1 to 8 deep. name_key is the name in NFC, case-folded in Python, unique among live siblings; role is one of evidence, target, playbook, library. state is ready, or deleting while a delete runs, which takes the subtree out of every scope at once; placeholder, trashed and trash_kind are for linked roots and the Trash, not built

Both cascade with their workspace, and folders with its root and its parent.

chunks

Column Notes
id, document_id foreign key to documents, cascading
position order within the document; unique with document_id
content the passage text
embedding float32 BLOB, kept so the vector table can be rebuilt without re-embedding
start_line, end_line the passage's line span in the markdown body, for citations

The index tables and their triggers

Table Kind Purpose
chunks_fts FTS5, external content over chunks (content_rowid='id') the BM25 keyword leg; stores no text of its own
chunk_vectors sqlite-vec vec0(embedding float[D]), rowid = chunks.id the nearest-neighbour leg; named by the active row of embedding_indexes

Three triggers on chunks keep the keyword index in step. chunks_after_insert adds the text to chunks_fts; chunks_after_update deletes the old text and adds the new; chunks_after_delete deletes the text from chunks_fts and the row from chunk_vectors. An external-content table keeps no copy, so a delete must hand it the old text or it goes on matching. The triggers fire on cascade too, which is how every real delete arrives, since the user removes a document or a workspace, never a chunk. Nothing else could reach these tables: a virtual table takes no foreign key.

Ingest writes the chunk_vectors row itself, because only ingest holds the vector. 0001 creates the table at 384, bge-small-en-v1.5's width; on a fresh install, finishing onboarding drops the still empty table and creates it again at the chosen model's width (lock.py), the one place outside migrations that emits DDL. upgrade_to_head() reads the declared width back from sqlite_master and refuses to start when it differs from the active index's spec: vectors from another model are unrelated numbers, not merely the wrong shape. How the two legs are queried is in search.md.

embedding_indexes

Column Notes
id
spec JSON snapshot of the embedder's spec: repo, pinned revision and file hashes, width, pooling, normalisation, prefixes, max_tokens, semantic_weight. A later edit to the app's copy of a spec cannot change what an existing index means
vector_table the vec0 table holding this index's vectors; checked against a fixed name pattern before it reaches SQL
state active; building and retired are reserved for changing the model, which is not built
created_at

One active row, enforced in code rather than by a singleton constraint, because a second row is how changing the model would start (proposal). A fresh install has no row until onboarding finishes; 0022 writes a bge-small row for a library that already existed, pointing at its chunk_vectors, and stamps every document with chunks. Nothing is re-embedded.

chat_threads and chat_messages

Table Columns Notes
chat_threads id, workspace_id, cloud_id, title, opencode_session_id, source_scope, history_start_message_id, created_at, updated_at cloud_id is the nullable, unique hosted thread id. title is nullable; the API defaults it to "New chat". opencode_session_id, from 0021, is set on a thread the agent answers, whose turns live in that opencode session rather than in chat_messages (chat.md). source_scope, JSON from 0024, holds the sources ticked for the thread; null means every source (chat.md). history_start_message_id, from 0026, is the oldest message the model is still sent once history was trimmed, and NULL until it first was (chat.md)
chat_messages id, chat_thread_id, role, content, created_at, completed_at role is user, assistant or system; completed_at arrived in 0002

content is JSON: {"text"} for a user turn, plus images: [{"key", "mime", "size_bytes", "sha256"}] when it carried any, and source_scope with resolved: {"count", "ids_sha256"} when it was grounded on a scope, and {"text", "citations"} for an assistant turn, whose text carries [citation:<chunk_id>] markers. The server reads text to build the model's history, and a user turn's images for the newest turn that carried some; the citations are for the UI. Imported user turns also carry an empty citations list. Messages are indexed on (chat_thread_id, created_at) and cascade with their thread. Visibility, authorship, cloning, turn ids, token usage and LangGraph checkpoints are left out. See chat.md.

artifacts and artifact_files

An artifact's searchable body is a Document with document_type = ARTIFACT; artifacts is a sidecar that owns no title, path, body or indexing state (ADR 0003). The tables shipped in 0001.

Column Notes
document_id foreign key, unique and cascading: one sidecar per document
workspace_id foreign key, cascading
chat_thread_id foreign key, set null: clearing a chat must not delete what it produced; set on each version the agent renders
format text, not an enum
generation integer, CHECK (generation > 0), bumped by each regenerate
created_by_tool_call_id, updated_by_tool_call_id provenance; a REST job passes none
artifact_metadata JSON: the source ids, prompt and options the job was created with, the source_scope it was resolved from and the grounded_document_ids that reached the model, quiz or flashcard progress, and for a Word document or PDF its spec, version and recipe (studio.md); for a revised copy of the user's file its revision, which also records the clean and external Word files kept beside the primary in the artifact's folder, since artifact_files.role allows only primary and preview (studio.md)

artifacts has no status column; its status is its document's. artifact_files keeps one immutable blob per role: role (primary or preview), storage_key (the path relative to the data directory), original_filename, mime_type, size_bytes (CHECK > 0) and checksum_sha256, unique on (artifact_id, role) and on storage_key. There is no storage_backend column, since there is one backend. See studio.md.

provider_connections, selected_models and onboarding_completion

Table Columns Notes
provider_connections id, label, provider, base_url, catalog_provider, api_key_ciphertext, timestamps, and since 0023 auth_kind, oauth_ciphertext and token_version label unique case-insensitively; provider must be openai_compatible; catalog_provider is a remote manifest provider id or custom, since 0014; the key is Fernet ciphertext since 0007; since 0023, auth_kind is api_key or chatgpt, oauth_ciphertext the Fernet-encrypted token set of a chatgpt row (NULL when signed out), and token_version the counter that keeps refreshes from racing (chatgpt-subscription.md)
selected_models model_type, provider, connection_id, name, params_b, vendor, line, settings, updated_at one row per model type: text_gen, image_gen, image_edit, video_gen, audio_gen or speech_to_text; settings is JSON for what the user set that no endpoint states, keyed by the slice that owns each entry (a server voice model's voices, the speech model's transcription language), and cleared when the slot takes another model, all but chat_modes: the mode last picked for each model, keyed <provider>/<connection id>/<name>, which stays so a model's pick comes back with it (model capabilities)
onboarding_completion id, completed_at a singleton (CHECK id = 1) whose presence means onboarding is done
  • A CHECK on selected_models allows llamacpp, sdcpp and audiocpp only without a connection and openai_compatible only with one, and a second, local_runtime_type, lets llamacpp hold only text_gen, sdcpp only image_gen, image_edit and video_gen, and audiocpp only audio_gen and speech_to_text. No CHECK keeps a connection out of speech_to_text; selection does (selection). connection_id cascades, so deleting a connection clears exactly the selections that used it.
  • params_b, vendor and line (flagship or small) are the model's fingerprint, recorded when it is chosen. They feed the prompt tier, which is computed on read, so retuning a threshold needs no migration.
  • Choosing a model never writes onboarding_completion; POST /llm/onboarding does, once a text_gen selection exists.
  • Remote /models answers, the local catalog, hardware profiles and fit estimates are not stored; they are recomputed or fetched live.

Connections are in connections.md; selection and onboarding in local-models/selection.md.

license_state and egress_destinations

Table Columns Notes
license_state id, certificate, imported_at, clock_watermark a singleton; plan and expiry are re-derived from the certificate on every read, and clock_watermark is the highest instant ever seen
egress_destinations destination, enabled, last_call_at one row per host; enabled defaults to false

A destination is host:<hostname>: host:huggingface.co for model search and downloads, and one per remote host, shared by every connection to it; a loopback endpoint needs none. Revision 0027 removes rows under the earlier names model_download, model_search and image_model_pull, carrying a grant to host:huggingface.co only where search and a download were both allowed and the host had no answer of its own. See license/app.md and egress.md.

plugin_runs

One row per run of a plugin's action. What the run produced is not here: the plugin wrote it through the API while it ran (the plugins proposal).

Column Notes
id, workspace_id foreign key to workspaces, cascading: a run goes with the workspace it was started in
plugin_id, version, action which installed version ran, and which of its actions
inputs JSON: what the user gave the action
status queued by default, then running, then succeeded or failed; cancelled from either of the first two, when the user cancels or deletes the workspace
error why a run failed: exit <code> when the plugin's process ended with anything but 0, timeout when its action's timeout_seconds ran out, interrupted when the app quit during it
log_tail the last 16 KiB the plugin printed, stdout and stderr together
created_at, started_at, finished_at

On-disk layout

<data dir>/                       ~/.surfsense by default
├── surfsense.db
├── backups/<from>-<to>.db       the database as it was before a migration; the newest two are kept
├── huey.db                       three queues, ingest, studio and plugins, in one file
└── data/
    └── workspaces/<workspace_id>/
        ├── documents/<document_id>/
        │   ├── <file name>       an uploaded or imported file, under its own name
        │   ├── figures/          the pictures ingest kept, and figures.json
        │   │   └── work/         the runs a figures-only pass that gave way resumes from
        │   ├── docling/work/     a long Docling reading's batches, until it ends
        │   └── transcript/       a recording's: segments.json once transcribed;
        │       └── work/         the decoded audio, plan and windows a job resumes from
        ├── chats/<thread_id>/
        │   └── <sha256>.<ext>    an image a turn carried, normalised to PNG or JPEG
        ├── artifacts/<artifact_id>/
        │   └── primary.<ext>     the rendered file; a preview would sit beside it
        └── agent/                the agent's working files (hosts with opencode staged)
            ├── text/<document_id>-<sha16>.md   one per version of a source's text
            └── threads/<thread_id>/            an agent thread's sources/ and outputs/

Directories are keyed by row id. An upload keeps its sanitized filename inside its document's directory, and no row stores that name: the directory holds the one file (documents.md). Directories written earlier hold original.<ext> and an unread extracted.md, and are left as they are. An artifact's files are named by role, with an extension when the MIME type is one the Studio worker knows. A chat image is named by its content hash, so one picture attached twice in a thread is one file (chat.md). Deleting a workspace removes its whole directory after the commit, and deleting a document, an artifact or a thread removes its own directory, an agent thread's folder under agent/threads/ included. agent/text/ holds no row's file: each thread's sources/ hard-links into it, and a file no thread links is swept (agent.md). The rest of the data directory is described in overview.md.

Entity graph

erDiagram
  workspaces ||--o{ documents : contains
  workspaces ||--o{ chat_threads : contains
  workspaces ||--o{ artifacts : contains
  workspaces ||--o{ plugin_runs : contains
  documents ||--o{ chunks : "split into"
  documents ||--o| artifacts : "body of"
  chunks ||--|| chunks_fts : "rowid, by trigger"
  chunks ||--|| chunk_vectors : "rowid, by ingest"
  embedding_indexes |o--o{ documents : "embedded"
  chat_threads ||--o{ chat_messages : contains
  chat_threads |o--o{ artifacts : "produced, set null"
  artifacts ||--o{ artifact_files : stores
  provider_connections |o--o{ selected_models : "selected through"

  workspaces {
    int id PK
    text name
    int cloud_id UK
    datetime created_at
    datetime updated_at
  }
  documents {
    int id PK
    int workspace_id FK
    text title
    text document_type "FILE, NOTE, ARTIFACT"
    text status "pending, processing, ready, failed, cancelled"
    text error_message
    text content
    text content_hash
    text dedup_key
    json document_metadata
    int embedding_index_id FK
    datetime created_at
    datetime updated_at
  }
  embedding_indexes {
    int id PK
    json spec
    text vector_table
    text state "active, building, retired"
    datetime created_at
  }
  chunks {
    int id PK
    int document_id FK
    int position
    text content
    blob embedding
    int start_line
    int end_line
  }
  chunks_fts {
    text content "FTS5, external content"
  }
  chunk_vectors {
    blob embedding "vec0, the active spec's width"
  }
  chat_threads {
    int id PK
    int workspace_id FK
    text title
    int history_start_message_id
    datetime created_at
    datetime updated_at
  }
  chat_messages {
    int id PK
    int chat_thread_id FK
    text role
    json content
    datetime created_at
    datetime completed_at
  }
  artifacts {
    int id PK
    int document_id FK, UK
    int workspace_id FK
    int chat_thread_id FK
    text format
    int generation
    text created_by_tool_call_id
    text updated_by_tool_call_id
    json artifact_metadata
    datetime created_at
    datetime updated_at
  }
  artifact_files {
    int id PK
    int artifact_id FK
    text role "primary, preview"
    text storage_key UK
    text original_filename
    text mime_type
    int size_bytes
    text checksum_sha256
    datetime created_at
    datetime updated_at
  }
  provider_connections {
    int id PK
    text label UK
    text provider
    text base_url
    text catalog_provider
    blob api_key_ciphertext
    datetime created_at
    datetime updated_at
  }
  selected_models {
    text model_type PK
    text provider
    int connection_id FK
    text name
    real params_b
    text vendor
    text line
    json settings
    datetime updated_at
  }
  onboarding_completion {
    int id PK
    datetime completed_at
  }
  license_state {
    int id PK
    text certificate
    datetime imported_at
    datetime clock_watermark
  }
  egress_destinations {
    text destination PK
    bool enabled
    datetime last_call_at
  }
  plugin_runs {
    int id PK
    int workspace_id FK
    text plugin_id
    text version
    text action
    json inputs
    text status "queued, running, succeeded, failed, cancelled"
    text error
    text log_tail
    datetime created_at
    datetime started_at
    datetime finished_at
  }
Loading

Revisions

Revision File Change
0001 0001_initial_schema.py workspaces, documents, chunks with chunks_fts, chunk_vectors and their triggers, chat threads and messages, artifacts and artifact files, a generation-only selected_models, provider_credentials
0002 0002_add_chat_message_completed_at.py chat_messages.completed_at
0003 0003_add_onboarding_completion.py onboarding_completion
0004 0004_openai_compatible_connections.py provider_connections; selected_models rebuilt with connection_id, the image_generation role and the cascading foreign key, keeping only Ollama generation selections; provider_credentials dropped
0005 0005_workspace_cloud_id.py workspaces.cloud_id, unique
0006 0006_license_state.py license_state
0007 0007_encrypt_provider_api_keys.py plaintext api_key dropped, api_key_ciphertext added
0008 0008_egress_destinations.py egress_destinations
0009 0009_local_image_provider.py selected_models rebuilt so sdcpp may hold a role without a connection
0010 0010_selected_model_fingerprint.py params_b, vendor and line on selected_models
0011 0011_document_cancelled_status.py cancelled added to documents.status
0012 0012_llamacpp_provider.py selected_models rebuilt with llamacpp in place of ollama, clearing Ollama selections rather than remapping them; an ollama_pull egress grant becomes model_download
0013 0013_selection_by_model_type.py selected_models rebuilt keyed by model_type: generation becomes text_gen and image_generation becomes image_gen, and the local_runtime_type CHECK is added; downgrading drops a selection in the three types the old key cannot hold
0014 0014_connection_catalog_provider.py provider_connections.catalog_provider, custom for every existing connection; downgrading drops the column in place, because a table rebuild would cascade into selected_models
0015 0015_image_selection_by_build.py a local image_gen selection is renamed from the old list's name to its curated build's id (stable-diffusion-1.5 to v1-5-pruned_Q4_0, and the two SDXL models); the map is frozen in the migration, and downgrading reverses it
0016 0016_local_audio_provider.py selected_models rebuilt so audiocpp may hold audio_gen, and only that, without a connection; downgrading drops an audiocpp selection
0017 0017_keyword_index_keeps_combining_marks.py the keyword index rebuilt from chunks with combining marks kept inside a word
0018 0018_sdcpp_edit_and_video.py selected_models rebuilt so sdcpp may hold image_edit and video_gen; downgrading drops such a selection
0019 0019_selection_settings.py selected_models.settings, a nullable JSON column added in place; its first entry is a server audio model's voices
0020 0020_plugin_runs.py plugin_runs
0022 0022_embedding_indexes.py embedding_indexes and documents.embedding_index_id; an existing library gets a bge-small row and its documents are stamped. The column is added by a plain ALTER TABLE, since a batch rebuild of documents would cascade to every chunk
0024 0024_thread_source_scope.py chat_threads.source_scope, by a plain ALTER TABLE; no row is backfilled, so every older thread uses every source
0025 0025_source_roots_and_folders.py source_roots and folders; a Library root and root folder per workspace; documents.folder_id by a plain ALTER TABLE; every FILE and NOTE filed, an imported folder_path becoming a chain of folders (case and normalization variants merged, levels past 8 joined with " / " into the eighth); content_hash filled from dedup_key; the dedup index swapped to per folder by DROP and CREATE INDEX
0026 0026_chat_history_start.py chat_threads.history_start_message_id, by a plain ALTER TABLE; no row is backfilled, so a thread sends its whole history until it first outgrows its budget
0027 0027_retire_pre_host_egress_grants.py egress rows under model_download, model_search and image_model_pull removed; host:huggingface.co allowed only where search and a download were both allowed and the host had no row; downgrading changes nothing
0029 0029_chat_thread_cloud_id.py chat_threads.cloud_id, nullable and unique; workspaces.has_unkeyed_imported_threads, marking workspaces with older unkeyed imported threads
0030 0030_speech_to_text_slot.py selected_models rebuilt so speech_to_text is a type and audiocpp may hold it beside audio_gen, carrying every column, 0019's settings included, which 0018's rebuild predates; downgrading drops a speech_to_text selection
  • Before the API applies a pending revision to an existing database, upgrade_to_head writes a VACUUM INTO copy to <data>/backups/<from>-<to>.db (via .partial, renamed once complete) and keeps the newest two (migration_snapshot.py). A new database or one at head writes none. If the copy fails, nothing migrates and the API start fails with the reason. To restore: quit SurfSense, replace surfsense.db with the snapshot, and start the app.
  • Migrations run on every API start and are idempotent. Autogenerate is off: it renders a rename as a drop plus an add, which deletes a column's data silently, and env.py carries no target_metadata, so it cannot be used by accident.
  • SQLite cannot alter a CHECK constraint in place, so 0004, 0009, 0012, 0013, 0016, 0018 and 0030 copy selected_models into a new table. A copy names every column the table has by then: one written from an older rebuild drops what later revisions added.
  • A revision that touches a table already holding rows should read the live schema first (op.get_bind(), sa.inspect) rather than assume its shape.
  • tests/integration/test_migrations.py fails when the models and the migration history disagree, when a second upgrade is not a no-op, and when a failed migration leaves anything behind. test_migration_0012.py, test_migration_0013.py, test_migration_0014.py, test_migration_0027.py, test_migration_0029.py and test_migration_0030.py beside it test what those revisions change.

Known gaps

  • Deleting a folder is permanent: the Trash is designed, not built (proposal).
  • The snapshot needs free disk about the size of surfsense.db, and nothing checks for it first: when the copy fails, the API does not start and the app closes with no message (overview).
  • A release cannot open a database a later release migrated, since its migrations do not know the newer revision, so its API does not start. Going back a version means restoring the snapshot by hand, as going from 2.2.0 to 2.1.0 takes backups/0023-0027.db.