Skip to main content

Database

Istota uses SQLite with WAL mode for concurrent access. All operations live in db.py. The schema is defined in schema.sql.

Tables​

Core​

TablePurpose
tasksTask queue with full lifecycle: id, status, source_type, user_id, prompt, conversation_token, talk_delivery_token, priority, attempts, last_heartbeat (worker-liveness ping for stuck-task reclaim, ISSUE-112), execution trace, model/effort overrides, plus skill / skill_args for skill-task dispatch. For a room turn, audience records who read the room (private | principals | mixed), guest_participant_id is set when the task answers a guest, and is_group_chat when the surface reported a group. about_room_token names the shared room a turn in a private room is linked to, and private_park marks a park whose question was asked privately (private replies)
user_resourcesPer-user folder mounts (folder) + internal shared_file organizer state
user_profilesPer-user profile fields (display_name, timezone, channels, worker overrides, disabled_skills, disabled_modules, email_addresses, trusted_email_senders, correspondent_scopes)
briefing_configsBriefing schedule + delivery (cron, conversation_token, output, enabled flag). Content lives in the per-user briefings module DB, not here
secretsPer-user encrypted credentials (Fernet over scrypt-derived ISTOTA_SECRET_KEY)
location_ingest_tokensOne row per device that may post to /webhooks/location: user_id, the SHA-256 token_hash (never the token), token_tail (last four characters), kind (app for a token the iOS tracker minted for its own device_id, code for one shown as a QR code or pasted into Overland), label, created_at, last_used_at. Unique on (user_id, device_id) where device_id is set, so setting a phone up again replaces its row; at most 20 rows per user. Not tied to a web session. In the framework DB because the receiver resolves the user from the token before it opens a location.db. See GPS location tracking
device_tokensOne row per (user_id, device_id) for the iOS shell's native actions: the SHA-256 token_hash (never the token), token_tail, scopes (space-separated, sorted; notes and voice), label, created_at, last_used_at (written at most once a minute). Minting again for a device replaces the token in place. Accepted only by POST /istota/api/workspace/notes, POST /istota/api/chat/voice and DELETE /istota/api/devices/self; removed with the user's profile. Settings → Devices lists it joined with the same phone's location_ingest_tokens row on device_id, and DELETE /istota/api/devices/phones/{device_id} removes both in one transaction. See istota.webui.device_tokens
quick_note_keysIdempotency keys for quick notes, (user_id, client_note_id) with the written file's path (NULL while the create is in flight). A retried create replays to the stored path. Pruned after 30 days on each create, and removed with the user's profile
google_oauth_tokensGoogle OAuth access/refresh token pairs (Fernet-encrypted at rest)
web_user_tokensRetained user-scoped Nextcloud OAuth pairs for post-as-user Talk mirroring; encrypted with the web-only ISTOTA_WEB_TOKEN_KEY (distinct salt + table from secrets, so "who can decrypt" stays greppable)
task_logsStructured task-level observability
task_steersMid-run steering notes for a running task (!steer): per-task monotonic seq, pending / consumed / dropped, plus who steered and from which surface
istota_kvPer-user key-value store for script runtime state
shared_kvCross-user namespaced JSON store. Reads are open; writes are admin-only and fail closed. Substrate for curated shared briefing content
_migration_stateMarkers for one-time data migrations, so each runs exactly once

Messaging​

TablePurpose
talk_poll_stateLast message ID per Talk conversation
talk_messagesPoller-fed message cache for conversation context
processed_emailsEmail dedup with RFC 5322 thread tracking
email_poll_stateInbound poll cursor, one row per polled folder: folder, uidvalidity, last_uid, updated_at. Each tick takes the oldest email_poll_batch_size UIDs above last_uid, so the batch is a boundary rather than a window and a backlog drains instead of truncating. A changed uidvalidity means the mailbox was recreated and UIDs restarted, so the cursor is meaningless and resets (ISSUE-250)
sent_emailsOutbound email tracking: threading replies onto the bot's sends
signup_tagsOne row per credential Istota created with a signup address: tag, user_id, slug, plus reserved_at, opened_at (null while the vault write is pending), task_minted_at and closed_at. UNIQUE(user_id, slug). Deleting the credential from the vault closes the tag. See signup addresses
signup_emailsMail filed against an open signup tag: tag, user_id, sender, subject, body, received_at. Never used as a task prompt; read through istota-skill email signup-inbox. Bodies are blanked after [email] signup_body_retention_days, rows kept. The matching processed_emails row has routing_method = 'signup'
trusted_email_sendersPer-user fnmatch allowlist for the email trust gate, read in both directions (inbound confirmation and outbound approval)
email_sender_flagsOne row per mail flagged as a suspected injection attempt: user_id, sender (the lowercased address), task_id, reason (from a fixed set), flagged_at. UNIQUE(user_id, task_id), so one mail counts once. Rows outlive their task, and those past email_sender_flag_window_days are pruned. No mail text or model wording is stored. See flagged senders
outbound_draftsOutbound mail held by the approval gate: recipients, subject, body, headers and origin surface snapshotted at hold time, plus status (pending | sending | sent | discarded), the room it belongs to and the task that composed it. Released rows carry sent_message_id. Never expired — see answering a held draft
task_eventsTask-event-streaming log: id, task_id, seq, kind, payload (JSON), created_at, UNIQUE(task_id, seq). One persisted, typed event stream per task feeding Talk / SSE / log / push consumers. seq is monotonic per task (writer-assigned, resumed across retries via get_max_task_event_seq); rows are deleted only by cleanup_old_tasks (retention)

Web chat (per-user rooms)​

TablePurpose
web_chat_roomsOne row per web chat room: id, user_id, token (channel id), name, archived, created_at, updated_at. One room = one conversation_token, each with its own CHANNEL.md
web_chat_messagesBot-delivered (unsolicited) room messages — alerts / logs / notifications routed to the web surface via WebTransport.deliver: id, user_id, token, role, title, text, created_at. Distinct from task-backed turns; merged into room history by time

Rooms​

The room model (defined in schema.sql) supersedes the de-facto tasks-as-history store with a surface-neutral room + message model. How the tables fit together for a room more than one person reads is in rooms and multi-user chat.

TablePurpose
roomsCanonical room registry keyed on conversation_token; origin (the surface it was created on: talk, web, email, sms or whatsapp), display name, archived flag, the standing per-room model / effort / brain defaults applied by record_inbound, and group_id for a group link
room_bindingsOne row per (room, surface) exposing a room; maps canonical token to each surface's ref. A ref is unique per surface
room_token_migrationPermanent forwarding from an old token (a pre-migration token, a phone room's pre-mint hash) to its room. Survives room deletion as a tombstone
messagesCanonical transcript (role user|assistant|system, task_id, origin_surface, external_ids mirror ledger). Who wrote a row is author_user_id for an Istota user, author_label for a sanitized outside sender, and author_participant_id for the room_participants row. about_room_token tags a private reply with the shared room it is about
room_membersPer-user sidebar membership; web visibility resolves through this, not the single-owner rooms.user_id
room_participantsEveryone seen in a room, Istota user or not: surface, surface_ref, user_id, kind (principal | guest | agent), display_name, joined_at, left_at. A history: leaving stamps left_at, rejoining inserts a new row, and the partial unique index covers present rows only
room_policyCreated when a room is first shared or a guest writes in it: host_user_id, speech_mode, disposition, guest_reply (off | held | direct), max_bot_turns_without_human, the veto state and announced_at (never set for an email thread room)
room_vetoesOne row per person who switched the bot off in a room, with agreed_at for their own !<bot> on. Emptied when the room comes back on
room_noticesVeto replies owed to a Talk conversation or WhatsApp group by a process that cannot post there itself, posted at most once by the scheduler's room-notices gate (which also posts the one-time announcement, tracked on room_policy.announced_at)
room_epochsAudience boundaries: who joined and the highest messages.id, tasks.id and Talk message id at that moment. epoch = 0 rows record a surface's first observed roster
speech_gate_decisionsThe speech gate's audit log: rung, whether it spoke, model, latency, the disposition in force, the reply kind (reply or ack) when it spoke, the kind of ack the classifier read (ack_type), whether an ack was answered with a reaction (reacted) and with which emoji (reaction), and a pointer to the message, never its text. Pruned after [speech_gate] decision_retention_days
room_dismissalsPer-user "hide this room" tombstone, cleared by the user's own next inbound
room_read_statePer-surface, per-user read cursors driving unread badges
message_starsPer-user starred messages (Talk has no per-message star API, so this is web-only)
message_deletionsHard-delete ledger with its own stream cursor, so a reconnecting client learns what vanished while it was away. Pruned at 30 days

Groups​

TablePurpose
groupsA named set of Istota users with a store of its own; group_id is also a directory under {mount}/Groups/. kind is display-only. Archived rather than deleted
group_membersMembership history: removal sets ended_at and a re-join inserts a new row
group_kvKey-value store shared by a group's members; written_by is provenance, never authorization

See groups.

Notifications​

TablePurpose
notificationsThe durable open set of what is waiting on a user — the inbox behind the app-bar bell. One row per thing needing attention: source (the registered producer), dedup_key (its stable identity), an optional object_type / object_id the resolver validates, severity, actionable, title / body / params, an in-app link checked against an allowlist on every read, occurrences, seen_at, and state (open | resolved | dismissed | stale). UNIQUE (user_id, source, dedup_key) is what makes a producer idempotent

Two things about this table are load-bearing and easy to undo by accident.

last_delivered_at records only a send that actually reached a destination. send_notification returns False when none is configured, and suppressing a re-delivery on the strength of a send that reached nobody is the exact failure the inbox exists to fix.

Ordering is updated_at DESC, not created_at DESC — hence idx_notifications_user_state. A reopen preserves created_at and refreshes updated_at; under created_at ordering an old row reopened today would deliver a push and bump the badge while sorting below fifty newer rows, so it would be unreachable in the panel. Never seen means never closed for an auto-resolving source.

idx_notifications_object leads with user_id, and so does resolve_by_object. Panel ids come from the per-user health module DB, where every user has a panel 12, so a close path keyed on (source, object_type, object_id) alone would resolve every other user's row when one user confirms theirs.

See notifications and the inbox.

Scheduling​

TablePurpose
scheduled_jobsCron job definitions (synced from CRON.md)
briefing_configsBriefing schedule + delivery per user
briefing_stateLast-run timestamps per briefing per user
shared_block_configsAdmin-managed definitions of module-owned shared briefing blocks (cron, render mode, trust flag, sources JSON). Seeded once from config, DB-authoritative thereafter
briefing_shared_block_stateLast-run timestamps for shared-block generation (global, not per user)
istota_file_tasksTasks sourced from TASKS.md files (content-hash identity)

Memory​

TablePurpose
sleep_cycle_statePer-user nightly memory extraction state
channel_sleep_cycle_statePer-channel memory extraction state
memory_chunksText chunks for hybrid search; carries valid_from / valid_until episode-window columns (ISSUE-109) so a chunk whose episode has closed self-suppresses from recall
memory_chunks_ftsFTS5 virtual table (trigger-synced from memory_chunks)
knowledge_factsTemporal subject/predicate/object triples (freeform predicates, fuzzy dedup); valid_from / valid_until bound a fact's currency
knowledge_facts_auditAppend-only audit trail of KG fact add/invalidate/delete ops
user_skills_fingerprintSkills version tracking for "what's new"

Monitoring​

TablePurpose
heartbeat_statePer-check monitoring state (timestamps, consecutive errors)
reminder_stateShuffle queue for briefing reminders

Usage​

TablePurpose
task_usageOne row per brain attempt — tokens, cost, timing, and the identity to attribute them
task_usage_modelsPer-model split of one task_usage row, where the brain reports one
code_review_callsRounds charged per task by the code_review skill's own model calls (task_id, calls, updated_at), counting rounds whether or not the response parsed. Cascades with the task

task_usage is deliberately not foreign-keyed to tasks. cleanup_old_tasks deletes tasks at task_retention_days (7) and a usage row must outlive that, at usage_retention_days (180), so task_id dangles afterwards. It is NULL outright for the daemon's own model calls, which have no task — the sleep cycle, shared briefing blocks, health OCR, code review — and origin names which. The denormalized identity columns (user_id, source_type, brain_kind, model) keep every row self-sufficient, so no aggregate ever joins tasks. tasks.id is AUTOINCREMENT, so a dangling task_id can never be reassigned.

Two traps in this table. Every date comparison against it uses the ISO-Z format created_at stores, not the datetime('now') idiom cleanup_old_tasks uses — ' ' (0x20) sorts below 'T' (0x54), so mixing the two silently drops boundary-day rows instead of raising. And every token aggregate must filter on has_totals: a run killed before its result frame has meaningless zeroes in the token columns.

The task_usage_models foreign key is documentation only. Istota never sets PRAGMA foreign_keys=ON, so prune_old_usage deletes children explicitly and parents second, on one connection.

See token usage and cost for what is recorded and how it is read back.

Tracking​

TablePurpose
monarch_synced_transactionsUnread. See below
csv_imported_transactionsUnread. See below

Invoice timing tables (invoice_schedule_state, invoice_overdue_notified) live in the per-user money DB (money/db.py), not the framework istota.db.

Watch the names: money/db.py creates its own monarch_synced_transactions, csv_imported_transactions, and kv_store in the per-user money DB. Those are the live ones. The two framework tables above predate the money module and have had no writer since it arrived with its own copy; the framework code that read and wrote them is gone (ISSUE-427). The tables stay declared because two empty tables cost nothing and dropping them is a migration on deployments that may hold rows. Write nothing new against them.

Feeds (per-user feeds.db)​

TablePurpose
feed_categoriesUser-defined feed categories
feedsSubscribed RSS/Atom/Tumblr/Are.na sources + per-feed poll state
feed_entriesAggregated feed content + read/star state, plus embed_url (an inline video player) and file_url (an attachment such as a PDF)
entry_imagesRepeat-image index (entry_id, image_key, seen_ts) backing the reader's reblog-image suppression
schema_metaSchema version, the global default poll interval, and feeds_settings.image_dedupe_window_days

A poll updates an entry it has already seen rather than discarding it, so a provider fix or a richer render reaches entries already on file instead of applying only to new ones. User state (status, starred, starred_at) and fetched_at are never overwritten — fetched_at is the first sighting, which keeps "recently added" ordering and the image-dedupe look-back stable — and a field is only overwritten by a non-empty value, so a sparser re-fetch cannot blank a title. The "N new entries" count still means newly inserted; refreshes are counted separately.

Location (per-user location.db)​

Location tables live in a per-user location.db, not in the framework DB. The module package at src/istota/location/ provides resolve_for_user(user_id, config).

TablePurpose
location_pingsRaw GPS data from Overland webhook
placesNamed geofences
visitsDetected place visits
location_statePer-user location tracking state
dismissed_clustersClusters the user chose not to save as places
schema_metaSchema version

The two Nominatim caches (geocode_cache, reverse_geocode_cache) remain in the framework istota.db for cross-user dedup.

Health (per-user health.db)​

TablePurpose
statsBody stat time series (metric, value, unit, date, source)
panelsLab panels (blood, urine, stool, saliva)
biomarkersIndividual results linked to a panel
biomarker_explainersCached explainer text per (name, direction)
biomarker_refsBundled canonical reference ranges and aliases
encountersVisits, procedures, screenings, hospitalizations
diagnosesConditions with status (active, resolved, chronic)
diagnosis_encountersWhich appointments a condition was seen at (many-to-many; real FKs, cascading)
immunizationsVaccine administration records
immunization_refsBundled canonical vaccine list and schedules
medicationsPrescriptions, OTC drugs and supplements; active marks the current regimen, a dose change is a new row
documentsStored paperwork, one row per blob, deduped by content hash
document_linksPolymorphic join: which records a document evidences
health_settingsProfile (DOB, height, sex) and unit display preferences
schema_metaSchema version

Only the .db is local; document and panel bytes stay in the workspace on the mount.

Briefings (per-user briefings.db)​

Blocks, their sources, and the archive of rendered results live in a per-user briefings.db. Schedule and delivery stay framework-owned in briefing_configs.

TablePurpose
briefing_blocksThe blocks a user's briefing is assembled from, in order
briefing_block_sourcesPer-block source rows (kind + config)
briefing_itemsItems gathered for a block
briefing_item_statePer-item seen/dismissed state for next-run dedup
briefing_archiveRendered briefing results
schema_metaSchema version
Archived results are pruned by [briefings] archive_retention_days on insert, and individually deletable from the web reader.

Module DB storage​

The framework istota.db and all five per-user module DBs (feeds, health, location, money, briefings) run WAL on local disk, at Config.module_db_path(user_id, module) — by default {db_path.parent}/modules/{user}/{module}.db. Only the .db files are local; user-facing workspace files (health uploads, money ledgers, feeds exports) stay on the Nextcloud mount. Module DBs were moved off the mount because WAL's mmap'd -shm file SIGBUSes on the rclone FUSE mount, which had forced them onto journal_mode=DELETE and left them with no reader/writer concurrency. python -m istota.maintenance.db_relocate is the one-time idempotent migrator; db_backup snapshots the now-local DBs back to dated directories on the mount for off-host durability, and db_restore copies them back.

Key operations​

Task lifecycle​

create_task(conn, prompt, user_id, source_type="cli", ...) # -> task_id
claim_task(conn, worker_id, user_id=None) # -> Task | None
update_task_status(conn, task_id, status, result=None, ...) # completed/failed
set_task_pending_retry(conn, task_id, error, delay_minutes) # exponential backoff
set_task_confirmation(conn, task_id, confirmation_prompt) # -> pending_confirmation
cancel_task(conn, task_id) # sets cancel_requested

Conversation history​

get_conversation_history(conn, token, exclude_task_id=None, limit=10)
# Returns: list[ConversationMessage(id, prompt, result, created_at, actions_taken)]

Cleanup​

expire_stale_confirmations(conn, timeout_minutes) # -> list of expired tasks
fail_ancient_pending_tasks(conn, fail_hours) # -> list of failed tasks
cleanup_old_tasks(conn, retention_days) # -> count deleted
prune_old_usage(conn, retention_days) # -> count deleted (children first)

Single source of truth for Task columns​

Every Task-returning helper (claim_task, get_task, get_pending_confirmation*, get_reply_parent_task, get_stale_pending_tasks, get_completed_*_since) routes its SELECT / RETURNING clause through a single _TASK_COLUMNS constant. Adding a column means editing one place; missing columns now raise IndexError rather than silently returning None.

WAL mode​

SQLite WAL mode allows concurrent reads from multiple threads (talk poller, workers, CLI) while the scheduler thread writes. Each worker creates fresh DB connections per call.

Schema initialization​

The schema is applied via schema.sql. The CLI command istota init creates the database and applies the schema.