Skip to content

Database Schema Reference

This reference documents the SQLModel tables defined in src/numi_chat/data/models.py. The default database is SQLite, and Alembic manages schema revisions in migrations/versions/.

Connection and migrations

  • Default URL: sqlite:///chats.db
  • Override: DB_URL
  • CLI targets: make db-migrate, make db-downgrade, make db-current, and make db-history
  • Docker: Compose sets DB_URL=sqlite:////app/state/chats.db and persists the database through the ./chats.db bind mount.

Application startup runs pending Alembic migrations automatically. Back up your database before upgrading, and always use Alembic migrations rather than SQLModel.metadata.create_all() on existing databases. See Database migrations.

Tables

user

User account, authentication state, and memory controls.

Column Type Notes
id integer Primary key
auth_subject string Unique JWT subject UUID, indexed
username string Login name; unique case-insensitively (ix_user_username_lower)
hashed_password string Bcrypt password hash; plaintext passwords are never stored
is_admin boolean Grants access to admin pages and /api/v1/admin/* endpoints
tier string, nullable Access tier ID; NULL uses the configured default tier
memory_reset_at datetime, nullable Privacy boundary preventing older messages from rebuilding cleared memory
memory_enabled boolean Enables background memory extraction and recall for this account (default true)
last_seen_at datetime, nullable Last app visit, written at most hourly; drives the notification idle pause
notifications_paused_at datetime, nullable Set when notifications paused after an absence; cleared only by resuming
avatar_token string Unique token used in cache-busted avatar URLs
created_at datetime Account creation timestamp

Relationships: one user owns chats, memories, settings, tool-usage records, and one userprofile, all configured with database-level ON DELETE CASCADE.

refreshtoken

Revocable refresh-token handles. Only the token hash is stored in the database; the raw token is held in an HttpOnly cookie by the browser.

Column Type Notes
id integer Primary key
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed
session_id string Logical session identifier shared across token rotations, indexed
token_hash string Unique SHA-256 hash of the refresh token
user_agent string, nullable Browser User-Agent string used to label the device in Settings
expires_at datetime Token expiration timestamp, indexed
revoked_at datetime, nullable Timestamp when the token or session was revoked
created_at datetime Row creation timestamp
started_at datetime, nullable Initial sign-in timestamp carried forward across rotations

auththrottlebucket

Database-backed login rate-limiting state. Account names and IP addresses are hashed before storage.

Column Type Notes
id integer Primary key
scope string Throttle scope (account or ip), indexed
key_hash string Hashed identifier, indexed; unique with scope
attempt_count integer Failed attempts in the current window
window_started_at datetime Start of the current tracking window
blocked_until datetime, nullable Temporary lockout expiration timestamp
updated_at datetime Last update timestamp, indexed

chat

Conversation metadata, model selection, and organization flags.

Column Type Notes
id integer Internal primary key
uuid string Unique external UUID used in URLs and API paths
title string Conversation title (defaults to New Chat)
model string Selected catalog or chatgpt:<slug> model ID
reasoning_level string Per-chat reasoning setting (default medium)
enabled_tools_json text, nullable JSON list of optional custom tools enabled for this chat
declined_tool_groups_json text, nullable JSON list of optional tool groups explicitly declined in this chat
pinned boolean Pins the chat to the top of the sidebar (default false)
archived boolean Moves the chat to the archived list (default false)
temporary boolean Marks a temporary chat excluded from normal lists and memory extraction (default false)
continued_from string, nullable Source chat.uuid when created via Continue with another model (ON DELETE SET NULL)
created_at datetime Creation timestamp
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed

chatversion

Tracks regenerated or edited response branches at a given point in a chat so the user can switch between earlier and newer versions of a reply.

Column Type Notes
id integer Primary key
chat_id integer Foreign key to chat.id (ON DELETE CASCADE), indexed
anchor_id integer Anchor message ID identifying the branch point, indexed
user_message_id integer, nullable User message ID associated with this version
started_after_id integer Message ID preceding this branch (default 0)
message_ids_json text JSON array of message IDs belonging to this version
selected boolean Whether this version is currently active in the conversation

chatqueuedmessage

Pending user input queued while a turn is already running. Rows enter message only when accepted by the turn producer.

Column Type Notes
id integer Primary key (defines queue order)
chat_id integer Foreign key to chat.id (ON DELETE CASCADE), indexed
request_id string Unique client request UUID for deduplication
payload_json text Serialized message text, attachments, deep_research flag, and timing
timing string next_step or after_turn
created_at datetime Enqueue timestamp

message

Conversation messages, including multimodal parts, reasoning traces, tool calls, citations, and usage metadata.

Column Type Notes
id integer Primary key
chat_id integer Foreign key to chat.id (ON DELETE CASCADE), indexed
role string system, user, assistant, or tool
content text, nullable Message text or serialized tool output
content_parts_json text, nullable Attachment parts, time_note, request_id, research_mode, or compaction_checkpoint
reasoning_content text, nullable Plain-text reasoning trace or summary
reasoning_details_json text, nullable Structured provider reasoning blocks (including encrypted items)
tool_calls_json text, nullable Assistant tool-call array
citation_sources_json text, nullable Normalized [web:N] citation source metadata
usage_json text, nullable Token counts, cache statistics, and inference/web cost breakdown
tool_call_id string, nullable Correlates a tool result row with its assistant tool call
created_at datetime Creation timestamp, indexed
deleted_at datetime, nullable Soft-deletion timestamp set by truncation or branch switching

Compaction checkpoints

When auto-compaction summarizes older turns, it writes an internal system message in message. Its content holds the summary text and its content_parts_json stores a compaction_checkpoint object (through_id, failures, retry_after). Original messages remain intact in the table.

scheduledtask

A message Numi sends on a timetable. See Schedule chats.

Column Type Notes
id integer Primary key
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed
chat_id integer Owning chat, foreign key to chat.id (ON DELETE CASCADE), indexed
prompt text Message sent on every run
new_chat boolean Start a new chat per run, copying the owning chat's model, reasoning level and tools
deep_research boolean Run in deep research mode
time_of_day string Local HH:MM in timezone
weekdays string Run days as digits, 0 = Monday (for example 01234)
interval_hours integer, nullable When set, run every N hours instead of at time_of_day
timezone string IANA time zone
enabled boolean Paused when false
next_run_at datetime Next run in UTC, indexed; advanced before a run starts so a restart never repeats it
last_run_at datetime, nullable Start of the latest run
last_status string, nullable running, ok, error, cancelled, skipped, or interrupted
last_error text, nullable Error of the latest failed run
last_chat_uuid string, nullable Chat of the latest run, foreign key to chat.uuid (ON DELETE SET NULL)
created_at datetime Creation timestamp

pushsubscription

One browser's Web Push endpoint.

Column Type Notes
id integer Primary key
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed
endpoint string Unique push service URL; removed when the service answers 404 or 410
p256dh string Browser public key for payload encryption
auth string Browser authentication secret
created_at datetime Creation timestamp

chatshare

Frozen, read-only public snapshots created when sharing a chat.

Column Type Notes
id integer Primary key
token string Unique 32-character URL-safe share token, indexed
chat_id integer Foreign key to chat.id (ON DELETE CASCADE), indexed
title string Chat title at the moment the link was created
snapshot_json text Sanitized public message array
file_names_json text, nullable JSON allowlist of generated filenames referenced by the snapshot
message_count integer Count of visible user and assistant messages in the snapshot
created_at datetime Creation timestamp
expires_at datetime, nullable Optional expiration timestamp, indexed

setting

Per-user key/value settings (such as personal_preferences).

Column Type Notes
id integer Primary key
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed
key string Setting key
value text Setting value

appsetting

Instance-wide key/value settings managed from the Admin pages (registration toggle, background model overrides, encrypted API key overrides, web provider priorities, access tiers, ChatGPT connection state, and skill switches).

Column Type Notes
id integer Primary key
key string Unique setting key, indexed
value text Setting value

toolusage

Records invocations of metered tools (such as generate_image or deep research) to enforce per-tier rolling usage limits.

Column Type Notes
id integer Primary key
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed
tool_name string Name of the metered tool or capability
created_at datetime Invocation timestamp, indexed

memory

Explicit durable facts saved via remember_fact.

Column Type Notes
id integer Primary key
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed
content text Fact text
created_at datetime Creation timestamp

userprofile

Per-user memory version and token-count metadata. The actual memory subjects and details live in memoryentry and memoryentryitem.

Column Type Notes
user_id integer Primary key and foreign key to user.id (ON DELETE CASCADE)
version integer Monotonically increasing memory version
token_count integer Estimated token count of the rendered memory block
updated_at datetime Last update timestamp

Memory metadata also tracks userprofile.maintained_at and userprofile.aggregated_at as job execution times, separate from fact dates.

memoryentry

One named subject in a user's memory. Profile and Preferences (category you) always exist; area, topic, and person entries are created as chats introduce them or when added manually.

Column Type Notes
id integer Primary key
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed
category string you, area, topic, or person, indexed
name string Display name
normalized_name string Lowercased name, indexed; unique per user_id
summary text Single-line summary used in the memory index
source string extraction, aggregation, manual, migration, or system
status string active or archived, indexed
created_at datetime Creation timestamp
updated_at datetime Last update timestamp

memoryentryembedding

Rebuildable optional semantic index. entry_id is the primary key and references memoryentry.id with ON DELETE CASCADE; ownership is checked through that entry before ranking. model and content_hash identify the exact indexed content. vector stores little-endian float32 bytes. Vectors are never prompt text or public-share data.

memoryentryitem

One dated detail bullet belonging to a memoryentry, with source provenance and an independent expiry window.

Column Type Notes
id integer Primary key
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed
entry_id integer Foreign key to memoryentry.id (ON DELETE CASCADE), indexed
content text Detail text
durability string permanent, slow, or seasonal
status string active, superseded, or stale, indexed
source string extraction, aggregation, manual, migration, or legacy
first_seen_at datetime First observation timestamp
last_confirmed_at datetime Most recent confirmation timestamp
expires_at datetime, nullable Expiration timestamp (NULL for permanent and manual items)
source_chat_id integer, nullable Provenance chat ID
source_message_id integer, nullable Provenance user message ID
source_context_json text, nullable Provenance snippet captured during extraction

When expires_at passes, the item's status changes to stale rather than being deleted: it drops out of the automatic prompt block but remains searchable through recall_memory.

userprofilesnapshot

Immutable version history of a user's memory for auditing and review.

Column Type Notes
id integer Primary key
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed
version integer Memory version captured by this snapshot, indexed
token_count integer Estimated token count at snapshot creation
source string consolidation, aggregation, maintenance, or manual_edit
reason text Human-readable reason for the snapshot
entries_json text, nullable Serialized entries and items at this version
observations_json text, nullable Serialized observations folded into this version
created_at datetime Snapshot timestamp
identity, communication_style, top_of_mind, recent_history, earlier_context, long_term_background text Legacy columns retained so pre-entry snapshots remain readable

chatsummary

Per-chat extraction state and rolling summary used by the background memory pipeline (distinct from in-chat compaction checkpoints).

Column Type Notes
chat_id integer Primary key and foreign key to chat.id (ON DELETE CASCADE)
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed
summary text Summary of the conversation
topics_json text, nullable JSON array of topic tags used by cross-chat aggregation
turn_count integer Number of complete turns covered
last_message_id integer, nullable Final assistant message ID covered
source_hash string, nullable Content fingerprint; changes make a resumed chat eligible for re-extraction
extracted_at datetime, nullable Timestamp of the last extraction pass
updated_at datetime Last update timestamp

memoryobservation

Candidate observations extracted from an idle chat, queued for consolidation into memoryentry and memoryentryitem.

Column Type Notes
id integer Primary key
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed
chat_id integer Foreign key to chat.id (ON DELETE CASCADE), indexed
content text Extracted observation text
entry_category string Target entry category (you, area, topic, person)
entry_name string Target entry name
entry_summary text Proposed summary if the entry is new
durability string permanent, slow, or seasonal
importance integer Confidence score (1 to 5)
consolidated boolean Whether consolidation has processed this row, indexed
source_message_id integer, nullable Supporting user message ID
source_context_json text, nullable Supporting quote/context JSON
disposition string, nullable add, update, confirm, skip, rejected, or superseded
item_id integer, nullable Resulting memoryentryitem.id when applied
created_at datetime Extraction timestamp

document

Metadata for PDFs uploaded to a chat.

Column Type Notes
id integer Primary key
chat_id integer Foreign key to chat.id (ON DELETE CASCADE), indexed
user_id integer Foreign key to user.id (ON DELETE CASCADE), indexed
filename string Original filename
file_hash string SHA-256 content hash
status string processing, ready, or error
page_count integer, nullable Number of extracted pages
created_at datetime Upload timestamp

documentchunk

Extracted per-page text for a document, injected in page order.

Column Type Notes
id integer Primary key
document_id integer Foreign key to document.id (ON DELETE CASCADE), indexed
page_number integer 1-based page number
content text Extracted page text
created_at datetime Creation timestamp

Deletion and retention

Deleting a chat cascades to its messages, versions, queued messages, summary, share links, documents, document chunks, and on-disk uploaded and generated files. Deleting a user account cascades across all user-owned tables and files. Truncating or regenerating a message sets deleted_at on the superseded rows so they are excluded from active history and memory extraction.