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

Table	Purpose
tasks	Task 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
user_resources	Per-user folder mounts (folder) + internal shared_file organizer state
user_profiles	Per-user profile fields (display_name, timezone, channels, worker overrides, disabled_skills, disabled_modules, email_addresses, trusted_email_senders)
briefing_configs	Briefing schedule + delivery (cron, conversation_token, output, enabled flag). Content lives in the per-user briefings module DB, not here
secrets	Per-user encrypted credentials (Fernet over scrypt-derived ISTOTA_SECRET_KEY)
google_oauth_tokens	Google OAuth access/refresh token pairs (Fernet-encrypted at rest)
web_user_tokens	Retained 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_logs	Structured task-level observability
task_steers	Mid-run steering notes for a running task (!steer): per-task monotonic seq, pending / consumed / dropped, plus who steered and from which surface
istota_kv	Per-user key-value store for script runtime state
shared_kv	Cross-user namespaced JSON store. Reads are open; writes are admin-only and fail closed. Substrate for curated shared briefing content
_migration_state	Markers for one-time data migrations, so each runs exactly once

Messaging

Table	Purpose
talk_poll_state	Last message ID per Talk conversation
talk_messages	Poller-fed message cache for conversation context
processed_emails	Email dedup with RFC 5322 thread tracking
sent_emails	Outbound email tracking for emissary thread matching
trusted_email_senders	Per-user fnmatch allowlist for the email trust gate
task_events	Task-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)

Table	Purpose
web_chat_rooms	One 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_messages	Bot-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 (unified Talk/web)

The unified Talk/web room-sync model (defined in schema.sql) supersedes the de-facto tasks-as-history store with a surface-neutral room + message model.

Table	Purpose
rooms	Canonical room registry keyed on conversation_token; origin (talk|web), display name, archived flag, plus the standing per-room model / effort default applied by record_inbound
room_bindings	One row per (room, surface) exposing a room; maps canonical token to each surface's ref
messages	Canonical transcript (role user|assistant|system, task_id, origin_surface, external_ids mirror ledger)
room_members	Per-user membership of a shared room; web visibility resolves through this, not the single-owner rooms.user_id
room_dismissals	Per-user "hide this room" tombstone, cleared by the user's own next inbound
room_read_state	Per-surface, per-user read cursors driving unread badges
message_stars	Per-user starred messages (Talk has no per-message star API, so this is web-only)
message_deletions	Hard-delete ledger with its own stream cursor, so a reconnecting client learns what vanished while it was away. Pruned at 30 days

Scheduling

Table	Purpose
scheduled_jobs	Cron job definitions (synced from CRON.md)
briefing_configs	Briefing schedule + delivery per user
briefing_state	Last-run timestamps per briefing per user
shared_block_configs	Admin-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_state	Last-run timestamps for shared-block generation (global, not per user)
istota_file_tasks	Tasks sourced from TASKS.md files (content-hash identity)

Memory

Table	Purpose
sleep_cycle_state	Per-user nightly memory extraction state
channel_sleep_cycle_state	Per-channel memory extraction state
memory_chunks	Text 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_fts	FTS5 virtual table (trigger-synced from memory_chunks)
knowledge_facts	Temporal subject/predicate/object triples (freeform predicates, fuzzy dedup); valid_from / valid_until bound a fact's currency
knowledge_facts_audit	Append-only audit trail of KG fact add/invalidate/delete ops
user_skills_fingerprint	Skills version tracking for "what's new"

Monitoring

Table	Purpose
heartbeat_state	Per-check monitoring state (timestamps, consecutive errors)
reminder_state	Shuffle queue for briefing reminders

Tracking

Table	Purpose
monarch_synced_transactions	Monarch Money sync dedup
csv_imported_transactions	CSV import dedup

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. The framework copies listed above are the ones _process_deferred_tracking writes; the money-DB copies are internal to the module.

Feeds (per-user feeds.db)

Table	Purpose
feed_categories	User-defined feed categories
feeds	Subscribed RSS/Atom/Tumblr/Are.na sources + per-feed poll state
feed_entries	Aggregated feed content + read/star state, plus embed_url (an inline video player) and file_url (an attachment such as a PDF)
entry_images	Repeat-image index (entry_id, image_key, seen_ts) backing the reader's reblog-image suppression
schema_meta	Schema 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).

Table	Purpose
location_pings	Raw GPS data from Overland webhook
places	Named geofences
visits	Detected place visits
location_state	Per-user location tracking state
dismissed_clusters	Clusters the user chose not to save as places
schema_meta	Schema 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)

Table	Purpose
stats	Body stat time series (metric, value, unit, date, source)
panels	Bloodwork panels
biomarkers	Individual results linked to a panel
biomarker_explainers	Cached explainer text per (name, direction)
biomarker_refs	Bundled canonical reference ranges and aliases
encounters	Visits, procedures, screenings, hospitalizations
diagnoses	Conditions with status (active, resolved, chronic)
diagnosis_encounters	Which appointments a condition was seen at (many-to-many; real FKs, cascading)
immunizations	Vaccine administration records
immunization_refs	Bundled canonical vaccine list and schedules
documents	Stored paperwork, one row per blob, deduped by content hash
document_links	Polymorphic join: which records a document evidences
health_settings	Profile (DOB, height, sex) and unit display preferences
schema_meta	Schema 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.

Table	Purpose
briefing_blocks	The blocks a user's briefing is assembled from, in order
briefing_block_sources	Per-block source rows (kind + config)
briefing_items	Items gathered for a block
briefing_item_state	Per-item seen/dismissed state for next-run dedup
briefing_archive	Rendered briefing results
schema_meta	Schema 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.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

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.