Database Schema Reference

This reference follows the SQLModel tables in src/numi_chat/data/models.py. The default database is SQLite; Alembic applies schema changes from migrations/versions/.

Connection and migrations

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

Application startup runs migrations automatically. Use the commands above for an explicit upgrade or inspection, and back up an existing database before upgrading. Do not use SQLModel.metadata.create_all() as a substitute for the migration chain. See Database migrations.

Tables

user

Account and profile identity.

Column	Type	Notes
id	integer	Primary key
auth_subject	string	Unique JWT subject; generated UUID
username	string	Unique login name
hashed_password	string	Bcrypt hash; raw passwords are never stored
is_admin	boolean	Grants admin API access
memory_reset_at	datetime, nullable	Reset boundary preventing older memory jobs and source messages from rebuilding cleared memory
avatar_token	string	Unique token for avatar URLs
created_at	datetime	Creation timestamp

Relationships: one user owns chats, memories, settings, and one structured profile. User-owned rows use database-level cascade deletion where configured by the migration chain.

refreshtoken

Revocable refresh-token handles. Only a hash is persisted; the raw refresh token is returned to the client and kept in an HttpOnly cookie by the web UI.

Column	Type	Notes
id	integer	Primary key
user_id	integer	Foreign key to user.id, indexed
session_id	string	Session identifier, indexed
token_hash	string	Unique hash, never the raw token
expires_at	datetime	Expiry timestamp, indexed
revoked_at	datetime, nullable	Set when revoked
created_at	datetime	Creation timestamp

auththrottlebucket

Shared login-throttling state. Account and IP keys are hashed before storage.

Column	Type	Notes
id	integer	Primary key
scope	string	Throttle scope, indexed
key_hash	string	Hashed account or IP key, indexed
attempt_count	integer	Attempts in the current window
window_started_at	datetime	Current window start
blocked_until	datetime, nullable	Temporary block expiry
updated_at	datetime	Last update, indexed

scope and key_hash have a unique constraint.

chat

Chat metadata and ownership.

Column	Type	Notes
id	integer	Internal primary key
uuid	string	Unique external chat identifier
title	string	Defaults to New Chat
model	string	Catalog model ID
reasoning_level	string	Per-chat reasoning setting
enabled_tools_json	text, nullable	JSON list of chat-enabled custom tools
pinned	boolean	Pins chat to the top of the sidebar (default false)
archived	boolean	Moves chat to the archived tab/list (default false)
created_at	datetime	Creation timestamp
user_id	integer	Foreign key to user.id

A chat owns messages, an optional rolling summary, documents, and uploaded files. The enabled_tools property serializes the JSON column to a list. Chats can be pinned to keep important sessions at the top, or archived to declutter the active sidebar without deleting messages or artifacts.

message

Conversation records, including reasoning, multimodal parts, tool calls, and citations.

Column	Type	Notes
id	integer	Primary key
chat_id	integer	Foreign key to chat.id
role	string	system, user, assistant, or tool
content	text, nullable	Text content
content_parts_json	text, nullable	Stored attachment parts, request metadata, or internal checkpoint metadata
reasoning_content	text, nullable	Provider reasoning text
reasoning_details_json	text, nullable	Provider reasoning metadata
tool_calls_json	text, nullable	Assistant tool-call list
citation_sources_json	text, nullable	Normalized citation sources
tool_call_id	string, nullable	Tool-result correlation ID
created_at	datetime	Creation timestamp
deleted_at	datetime, nullable	Soft-deletion marker

The tool_calls, reasoning_details, and citation_sources properties parse and serialize their JSON columns. Deleted messages are excluded from normal history and memory extraction. Stored reasoning is retained independently of whether a model will accept it in later requests.

Compaction checkpoints

Auto-compaction uses an internal system message in this table. Its content holds the summary; content_parts_json contains a compaction_checkpoint object with through_id, failures, and retry_after. The boundary identifies the original messages summarized, and the retry timestamp controls failure cooldown. Original messages are not deleted by compaction.

This checkpoint is separate from the chatsummary table used by memory extraction. See Message Flow and Persistence for the difference between stored history and model context.

setting

Per-user key/value settings. The web UI currently stores personal_preferences here.

Column	Type	Notes
id	integer	Primary key
user_id	integer	Foreign key to user.id
key	string	Setting name
value	text	Setting value

appsetting

Application-wide key/value settings, including registration state.

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

memory

Explicit durable facts stored for a user by the memory tools.

Column	Type	Notes
id	integer	Primary key
user_id	integer	Foreign key to user.id
content	text	Fact text
created_at	datetime	Creation timestamp

userprofile

One structured adaptive-memory profile per user.

Column	Type	Notes
user_id	integer	Primary key and foreign key to user.id
identity	text	Identity facts
communication_style	text	Tone and formatting preferences
top_of_mind	text	Active priorities
recent_history	text	Recent context
earlier_context	text	Older milestones
long_term_background	text	Stable background
version	integer	Profile version
token_count	integer	Rendered profile estimate
updated_at	datetime	Last update

userprofilesnapshot

Immutable profile history used for review and auditing.

Column	Type	Notes
id	integer	Primary key
user_id	integer	Indexed foreign key to user.id
version	integer	Profile version represented, indexed
identity	text	Snapshot of the identity section
communication_style	text	Snapshot of the communication-style section
top_of_mind	text	Snapshot of active priorities
recent_history	text	Snapshot of recent context
earlier_context	text	Snapshot of earlier context
long_term_background	text	Snapshot of long-term background
token_count	integer	Profile token estimate at snapshot creation
source	string	Defaults to consolidation
reason	text	Reason recorded for the snapshot
observations_json	text, nullable	Serialized observations associated with this version
created_at	datetime	Snapshot creation timestamp

userprofilefact

Dated, individually managed facts rendered into profile sections.

Column	Type	Notes
id	integer	Primary key
user_id	integer	Indexed foreign key to user.id
section	string	Profile section name, indexed
content	text	Fact text
status	string	Defaults to active, indexed
source	string	consolidation or another source
first_seen_at	datetime	First observation time
last_confirmed_at	datetime	Last confirmation time
expires_at	datetime, nullable	Optional expiry

chatsummary

Memory-extraction state for one chat. chat_id is both the primary key and a foreign key to chat.id. The table retains a rolling-summary field and tracks which user-message content the extractor has processed; it is not the agent's auto-compaction checkpoint.

Column	Type	Notes
chat_id	integer	Primary/foreign key
user_id	integer	Indexed foreign key to user.id
summary	text	Rolling summary text
turn_count	integer	Number of turns fully processed by memory extraction
last_message_id	integer, nullable	Extraction cursor: final assistant message ID of the last completed source turn
user_message_offset	integer	Offset when processing a user message in chunks
user_message_fingerprint	string, nullable	Detects changes to partially processed source content
updated_at	datetime	Last update

memoryobservation

Observations extracted from user messages before profile consolidation. Source metadata lets consolidation check the original evidence before changing facts.

Column	Type	Notes
id	integer	Primary key
user_id	integer	Indexed foreign key to user.id
chat_id	integer	Foreign key to chat.id
content	text	Sanitized observation
bucket	string	Target profile section
importance	integer	Importance score
consolidated	boolean	Queue state, indexed
source_message_id	integer, nullable	Source user-message identifier
source_context_json	text, nullable	Source provenance captured during extraction
disposition	string, nullable	Consolidation decision: add, replace, confirm, ignore, or defer
fact_id	integer, nullable	Related profile-fact identifier
created_at	datetime	Extraction timestamp

source_message_id and fact_id store provenance identifiers, not database foreign-key constraints. Ownership is enforced through user_id and chat_id.

document

Uploaded PDF metadata.

Column	Type	Notes
id	integer	Primary key
chat_id	integer	Foreign key to chat.id
user_id	integer	Foreign key to user.id
filename	string	Original filename
file_hash	string	Content hash
status	string	processing, ready, or error
page_count	integer, nullable	Extracted page count
created_at	datetime	Upload timestamp

documentchunk

Per-page extracted PDF text. page_number preserves document order; retrieval is ordered context injection, not similarity search.

Column	Type	Notes
id	integer	Primary key
document_id	integer	Foreign key to document.id
page_number	integer	Source page number
content	text	Extracted page text
created_at	datetime	Creation timestamp

Deletion and retention

Chat deletion removes owned messages, summaries, documents, chunks, and uploaded and generated files through services and configured database cascades. Users can delete their own account after confirming their current password; administrators have a separate user-deletion endpoint. Message truncation is a soft delete via deleted_at, including the selected message and later rows. Those rows can remain in the database while disappearing from normal history. Logs and backups have separate operator-controlled retention.

Important invariants

• Passwords and refresh tokens are never stored in plaintext.
• Chat URLs use UUIDs. Message IDs are integers and are also exposed for pagination, search results, and truncation.
• JSON columns must be read through the model properties or equivalent validated JSON handling.
• Schema changes belong in Alembic migrations. Inspect generated migrations before applying them, especially for SQLite table rebuilds.