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, andmake db-history - Docker: Compose sets
DB_URL=sqlite:////app/state/chats.dband persists the database through the./chats.dbbind 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.