Database schema

• Database schema
  • Design invariants
  • Entity relations
  • Metadata and content split
  • Tables
    • user
    • auth_token
    • session
    • session_message
    • session_message_content
    • session_routing_decision
    • session_context_reference
    • session_tool_call
    • session_tool_call_content
    • session_approval
    • session_run_state
    • session_run_input
    • session_run_input_content
    • session_plan
    • session_plan_content
    • session_diff
    • session_diff_content
    • trace_event
    • trace_event_content
    • user_memory
    • user_memory_content
    • context_memory
    • context_memory_content
  • Indexes
  • Cascade and retention

This document specifies the smista.ai storage schema as landed by issue #8 (storage domain entities and traits). It is the authoritative reference for the SurrealDB tables, their fields, and the relations between them.

The storage layer persists users, sessions, authentication tokens, session messages, routing decisions, context references, tool calls, approvals, plans, diffs, trace events, and memory. Application code depends on the storage traits and domain types, never on SurrealDB directly; SurrealDB-specific query logic is isolated in the storage implementation.

Design invariants

These rules hold for every table in the schema:

• Record id is the id. The SurrealDB record id (table:⟨key⟩, a RecordId) is the entity id. There is no redundant id column. The key is a UUIDv7 generated in Rust (Uuid::now_v7()), so ids are deterministic, portable, and time-sortable. Session ids are globally unique.
• Metadata and content are physically separated. Every entity that carries sensitive free-form content has a paired 1:1 <entity>_content table sharing the same record id. The base table holds queryable metadata; the _content table holds only the encryptable payload. This enables end-to-end encryption with no later migration: metadata stays queryable while content is stored either in clear or sealed as a ciphertext envelope. See End-to-end encryption for the full design.
• User ownership is enforced at the query boundary. Every session-scoped row stores user redundantly so ownership checks and scoped queries never need a join. A user can only ever reach rows they own.
• Secrets are never stored raw. Only hashes of API keys and tokens are persisted. Tool-call arguments/results and diffs are sanitised for secrets before persistence, subject to the active privacy policy.

Entity relations

Every entity maps to a dedicated table. Relations are explicit references: a session-scoped row points at its session and (redundantly) its owning user.

erDiagram
    user {
        uuid id PK
        string api_key_hash
        datetime created_at
        datetime updated_at
        datetime disabled_at "optional"
    }
    auth_token {
        uuid id PK
        uuid user FK
        string token_hash
        datetime created_at
        datetime expires_at
        datetime revoked_at "optional"
    }
    session {
        uuid id PK
        uuid user FK
        string title "optional"
        bool encrypted
        string key_id "optional"
        datetime created_at
        datetime updated_at
        datetime archived_at "optional"
    }
    session_message {
        uuid id PK
        uuid session FK
        uuid user FK
        enum role
    }
    session_routing_decision {
        uuid id PK
        uuid session FK
        uuid user FK
        enum task_type
    }
    session_context_reference {
        uuid id PK
        uuid session FK
        uuid user FK
        bool included
    }
    session_tool_call {
        uuid id PK
        uuid session FK
        uuid user FK
        enum status
    }
    session_approval {
        uuid id PK
        uuid session FK
        uuid user FK
        enum decision
    }
    session_run_state {
        uuid id PK
        uuid session FK
        uuid user FK
        string run_id
        int turn
        enum phase
        object active
    }
    session_plan {
        uuid id PK
        uuid session FK
        uuid user FK
        enum status
    }
    session_diff {
        uuid id PK
        uuid session FK
        uuid user FK
        enum status
    }
    trace_event {
        uuid id PK
        uuid session FK
        uuid user FK
        enum event_type
        enum task_type
        enum provider
        string model
        string matched_rule "optional"
    }
    user_memory {
        uuid id PK
        uuid user FK
        string key "optional"
    }
    context_memory {
        uuid id PK
        uuid session FK
        uuid user FK
        string key "optional"
    }

    user ||--o{ auth_token : "issues"
    user ||--o{ session : "owns"
    user ||--o{ user_memory : "owns"
    session ||--o{ session_message : "contains"
    session ||--o{ session_routing_decision : "records"
    session ||--o{ session_context_reference : "records"
    session ||--o{ session_tool_call : "records"
    session ||--o{ session_approval : "records"
    session ||--o| session_run_state : "tracks"
    session ||--o{ session_plan : "produces"
    session ||--o{ session_diff : "produces"
    session ||--o{ trace_event : "emits"
    session ||--o{ context_memory : "scopes"

Every session-scoped row above also carries a user reference (omitted from the relation edges for readability) used to enforce ownership without a join.

Metadata and content split

Content-bearing entities are split across two tables that share the same UUIDv7 record id; the identical key is the 1:1 join (session_message:⟨uuid⟩ ↔ session_message_content:⟨uuid⟩). The base table is always queryable; the _content table holds only the payload that may later be encrypted.

erDiagram
    session_message      ||--|| session_message_content      : "same id"
    session_tool_call    ||--|| session_tool_call_content    : "same id"
    session_plan         ||--|| session_plan_content         : "same id"
    session_diff         ||--|| session_diff_content         : "same id"
    trace_event          ||--|| trace_event_content          : "same id"
    user_memory          ||--|| user_memory_content          : "same id"
    context_memory       ||--|| context_memory_content       : "same id"
    session_run_input    ||--|| session_run_input_content    : "same id"

Metadata-only entities (user, auth_token, session, session_routing_decision, session_context_reference, session_approval, session_run_state) stay single-table.

Each encryptable payload field is typed SecretContent below. A SecretContent holds the value either in clear or sealed as a ciphertext envelope, and is stored as a flexible object in one of two shapes:

• Plaintext: { "Plaintext": "<text>" }, used by a non-encrypted session.
• Encrypted: { "Encrypted": { "version": int, "algorithm": string, "key_id": string, "nonce": string, "ciphertext": string } }, used by an encrypted session. The envelope is opaque to storage and the router; only a client holding the session key can open it.

Whether a session’s content is plaintext or encrypted is fixed by the owning session’s encrypted flag. user_memory_content is the one exception: it is user-scoped rather than session-scoped, so it is not yet encryptable and keeps a plain string. See End-to-end encryption.

Tables

Each table below lists its fields. The id field is the SurrealDB record id (UUIDv7 key); it is shown for completeness, not as a separate column. Owner references (user, session) are stored as explicit RecordId references.

Enum-typed fields are stored as their lowercase textual form. A Provider enum value is the provider identifier as it appears in a model reference: one of anthropic, openai, ollama, or openai-compat:<name> for a named OpenAI-compatible instance.

user

An identity that can own sessions. In local-first deployments a user may be created locally with no SaaS account; the same entity can later represent a remote account. The raw API key is never stored — only its hash. A user has a single API key.

auth_token

A short-lived authentication session for the router. The CLI uses an auth token after signing in with its user id and API key. The raw token is never stored; expired or revoked tokens are rejected and cleaned up over time.

session

A resumable user interaction with smista.ai. Each session belongs to exactly one user, who is the only identity allowed to access it.

encrypted is fixed when the session is created and never changed afterwards; flipping it would orphan content the router cannot re-key. When true, every paired _content row in the session stores a sealed payload and key_id names the per-session key that sealed it. The key itself never reaches storage. See End-to-end encryption.

scope is an opaque grouping key the router stores and matches verbatim without interpreting it. The CLI sets it from the working directory so sessions can be listed per project; another client may scope by repository or workspace id. It is kept in clear even for an encrypted session so that listings can filter on it.

session_message

A message exchanged during a session. Metadata stays queryable; the message body lives in session_message_content.

session_message_content

Paired 1:1 with session_message (same record id).

session_routing_decision

Records which provider/model pair was selected for a task and why. Metadata-only.

session_context_reference

Records what context was selected or excluded for a task. Stores references and metadata only — not full file contents. Restricted contents are not persisted unless policy allows. Metadata-only.

session_tool_call

Records a tool request and its execution result. Arguments, result, and error are sensitive and live in session_tool_call_content; they are sanitised for secrets before persistence.

session_tool_call_content

Paired 1:1 with session_tool_call (same record id).

session_approval

Records a user decision for an operation that required confirmation — a tool call, file write, shell command, network access, or remote-provider context disclosure. Metadata-only.

session_run_state

Persists the execution state machine of a session’s in-flight run, so the router can pause between protocol turns and resume on the next request without holding the run in memory. A session has at most one in-flight run, so this row is keyed by the session id and there is at most one per session; a write replaces it in place. phase is the durable checkpoint the run resumes from; active is the orthogonal processing lock, present only while a turn is being served. Because the lock never overwrites the checkpoint, a crash mid-turn loses nothing: startup clears active and the run resumes from phase. Metadata-only: the phase carries only references to rows already stored, never content, so the row is never encrypted. Reading no row means the run is idle. See Run state machine for the run lifecycle.

phase is one of: Idle (nothing outstanding), AwaitingDecrypt (blocked on the client opening one or more sealed records before the prompt can be built), AwaitingTool (blocked on client-run allow/ask calls), AwaitingApproval (blocked on a yes/no decision with no tool) and AwaitingEncrypt (blocked on the client sealing one or more records). Every Awaiting* variant also records a resume step naming where the run continues once the wait is answered, and carries only the references it needs, never content. There is no Running phase: an in-flight turn is the presence of active, not a checkpoint. The enum serializes to an object, so phase is stored as a flexible object, and active as an optional flexible object.

session_run_input

Persists a run’s request context — the policy, local preferences and workspace snapshot — so every turn of a multi-turn run can re-run the deterministic resolver without the client re-sending it. A continue carries only the pause answer, so this row is the cross-turn carrier of the original request. A session has at most one in-flight run, so the row is keyed by the session id and there is at most one per session; a write replaces it in place. The non-secret metadata below is stored in clear; the secret half (input text, attachments, instructions, skills and git diff) lives in session_run_input_content.

session_run_input_content

Paired 1:1 with session_run_input (same record id). Holds the secret half of the run context (the input text, attachments, instructions, skills and git diff), serialized as JSON and stored in clear or sealed for an encrypted session.

session_plan

Records a generated or approved execution plan. The plan snapshot lives in session_plan_content; the queryable hash and status stay in the base table.

session_plan_content

Paired 1:1 with session_plan (same record id).

session_diff

Records a proposed or applied file modification. The diff body lives in session_diff_content and is stored only after secret filtering, per the active privacy policy.

session_diff_content

Paired 1:1 with session_diff (same record id).

trace_event

A structured, append-only event recorded during task execution; the detailed history surfaced by /trace. The append/get ops live on the storage Database; the assembled read view is smista-core’s Trace. The routing fields (task_type, provider, model, matched_rule) carry the routing context of the task that emitted the event, so get_latest_trace assembles the Trace from trace events alone. The free-form payload lives in trace_event_content.

trace_event_content

Paired 1:1 with trace_event (same record id).

The payload is a JSON object serialized to a string, then wrapped as a SecretContent. The shapes below describe that plaintext string; in an encrypted session it is sealed in the envelope and only the holder of the session key can read it. Each shape is tagged with a type field equal to the owning event’s event_type, and carries the fields of the matching metadata entity. ? marks an optional field; int is a JSON number and a monetary cost is a decimal string (never a float).

On read, each event row (metadata + this payload) is mapped to one TraceEvent in the assembled smista-core Trace. The event’s payload is a TraceEventPayload: plaintext carries the parsed, tagged payload above for a normal session, while encrypted carries the sealed envelope for an end-to-end encrypted session, which only the client can open.

user_memory

A long-term, model-populated preference owned by a user. Untouched by session deletion; subject to retention. The fact lives in user_memory_content.

user_memory_content

Paired 1:1 with user_memory (same record id).

This is the one content payload that is not yet encryptable: user_memory is user-scoped, so the per-session encrypted flag cannot reach it. It keeps a plain string until a user-level scheme is designed.

context_memory

A per-session, model-populated memory owned by a session and a user. Cascades on session delete (no SurrealDB FK cascade — delete_session removes context_memory WHERE session = $session and its _content row). The fact lives in context_memory_content.

context_memory_content

Paired 1:1 with context_memory (same record id).

Indexes

Indexes are declared on the metadata tables only:

• Owner load — user_memory by user; context_memory by session. Used to load all memory for an owner before a task runs.
• Unique upsert-by-key — unique index on (user, key) for user_memory and on (session, key) for context_memory, applied to keyed rows only. Keyless rows (key is null) stay unconstrained, so multiple keyless facts can coexist while a keyed fact can be upserted in place.
• One run state per session — unique index on session for session_run_state, enforcing the single in-flight run a session may have.

Cascade and retention

• Deleting a session cascades to all session-scoped rows and their _content pairs, including context_memory and session_run_state. The cascade is explicit in delete_session, not enforced by SurrealDB. The retention purges clear the same rows for the sessions they remove.
• user_memory survives session deletion and falls under user retention settings.
• Expired and revoked auth_token rows are cleaned up over time.

Cascade/cleanup mechanics and retention policy are implemented in #10; this schema defines the substrate they operate on.