Make your computer a home for your AI partner. Avibe is the local-first Agent OS: Vibey, its built-in AI partner, works out of the box, and Claude Code, Codex & OpenCode run unchanged beside it — reach them from your browser, phone, or any chat app.
SQLite State Migration Design
Status
- Branch:
feature/sqlite-state-migration-plan - Scope: migrate Settings and Sessions from JSON files to SQLite.
- Source compatibility: keep existing
SettingsStoreandSessionsStorePython APIs during the first pass. - Release assumption: the previous 14-table draft has not shipped, so the initial Alembic revision is rewritten to the final six-table model instead of adding a migration from an unreleased schema.
Product Intent
This migration is not a mechanical JSON-to-table conversion. The goal is to separate durable product state from user-editable files and establish a small data model that can support Web UI Chat, sub-agents, session recovery, and future querying without inheriting the accidental shape of the old JSON files.
The durable model recognizes six concepts:
scopes: external IM objects that Vibe Remote can configure or observe.scope_settings: current default configuration for a scope.agent_sessions: Vibe Remote-owned agent session handles.runtime_records: typed restart/dedup/recovery records.auth_codes: user binding code lifecycle.state_meta: database-level migration and compatibility markers.
Storage Boundary
SQLite becomes the source of truth for:
- channel, guild, platform policy, and user settings;
- bound user roles;
- bind codes;
- agent session mappings;
- active poll/thread recovery state;
- processed-message dedup records;
- discovered chat identity metadata.
These remain file-based for now:
config/config.json, because it contains global runtime/platform/backend config and secrets;- project-level agent/skill config, because users should be able to inspect and version it in their repository;
- task/watch persistence, because volume is low and the current queue semantics are already clear;
- process runtime artifacts such as pid/status files.
Database File
Default path:
~/.vibe_remote/state/vibe.sqlite
Connection setup:
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
WAL allows the Web UI process and service process to share the same SQLite file
with concurrent readers. busy_timeout keeps short write races from failing
immediately.
Table Design
scopes
scopes represents a long-lived external target in an IM platform. A scope is
not a thread. Thread and flat-chat differences are represented by
agent_sessions.session_anchor.
Fields:
id: semantic primary key, formatted as<platform>::<scope_type>::<native_id>, for exampleslack::channel::C123. This is a readable DB identity, not something runtime code should parse to recover the platform ID.platform:slack,telegram,discord,lark,wechat, or future platform name.scope_type:channel,user,guild, orplatform.native_id: the original platform ID used by IM adapters, for example a Slack channel ID or Telegram chat ID.parent_scope_id: optional parent scope, such as a Discord channel pointing to its guild scope.display_name: UI name such as#general,Core Forum, orAlex.native_type: platform-native kind such as Telegramsupergroup.is_private: private channel/chat/DM marker.supports_threads: normalized thread/topic capability.metadata_json: platform-observed metadata that is not a primary query dimension.first_seen_at,last_seen_at,updated_at: lifecycle timestamps.
Constraints and indexes:
- Primary key:
id. - Unique:
(platform, scope_type, native_id), because one external IM object must map to one scope. - Index:
(platform, scope_type)for platform UI lists. - Index:
parent_scope_idfor parent/child navigation.
scope_settings
scope_settings stores the current default configuration attached to a scope.
Channel settings, user settings, guild settings, and platform-level guild policy
share one table. Stable query dimensions are columns; volatile or nested details
stay in JSON.
Fields:
scope_id: primary key and foreign key toscopes.id.enabled: whether the scope is enabled.role: user permission role, currentlyadminormember; only meaningful for user scopes.workdir: default working directory for this scope.agent_backend: deprecated legacy scope backend field; ignored by current Agent-first routing.agent_variant: default agent/sub-agent name.model: default model for this scope.reasoning_effort: default reasoning effort when the backend supports it.require_mention: channel/platform mention policy override; null means inherit.settings_version: version ofsettings_json.settings_json: non-primary-query details such asshow_message_types, bound DM chat ID, and backend-specific temporary settings.created_at,updated_at: lifecycle timestamps.
Constraints and indexes:
- Primary key:
scope_id. One scope has one current default settings row. - Index:
rolefor admin/user listing. - Index:
workdirfor project-level grouping and management. - Legacy index:
(agent_backend, model)may remain until schema cleanup.
agent_sessions
agent_sessions stores Vibe Remote-owned agent session instances. The ID is a
short random handle intended to be injected into prompts, shown in logs, and
passed back to CLI commands.
Fields:
id: primary key, formatted assesplus 10 lowercase base32-like characters, for examplesesk8m4q2p7x. It has no symbols, is not purely numeric, and is short enough for agents to copy.scope_id: optional foreign key toscopes.id.agent_backend: backend used to create the native session.agent_variant: routing agent or sub-agent name; defaults todefaultfor future APIs.model: model used when the native session was created.reasoning_effort: reasoning effort used when the native session was created.session_anchor: conversation anchor. A Slack thread uses thread/message ID; a flat Telegram chat uses the chat itself.workdir: working directory used for the session.native_session_id: underlying backend session ID, encoded as a string while preserving legacy non-string values.title: optional Web UI title.status:active,stale,archived, or future state.metadata_json: compatibility and backend-specific details.created_at,updated_at,last_active_at: lifecycle timestamps.
Constraints and indexes:
- Primary key:
id. - No business unique constraint on
scope_id + session_anchor + workdir. Future Web UI Chat, forks, sub-agents, and parallel agents can create multiple sessions under the same context. - Index:
(scope_id, session_anchor, workdir)to restore/list sessions for a context. - Index:
(agent_backend, agent_variant)for backend/agent filtering. - Index:
(status, last_active_at)for "recent active session" queries. - Index:
native_session_idfor reverse lookup and compatibility.
runtime_records
runtime_records keeps restartable operational state and idempotency markers
without splitting every low-level runtime concept into its own table.
Fields:
id: primary key, formatted asruntime::<record_type>::<record_key>.record_type:active_poll,active_thread,processed_message, or future runtime record type.record_key: type-local stable key.scope_id: optional foreign key toscopes.id.session_anchor: optional conversation anchor for cleanup or listing.workdir: optional project directory for diagnostics and cleanup.payload_json: type-specific recovery payload.expires_at: optional cleanup time.created_at,updated_at: lifecycle timestamps.
Constraints and indexes:
- Primary key:
id. - Unique:
(record_type, record_key), becauserecord_keyis defined as the type-local business identity. - Index:
(record_type, scope_id, expires_at)for recovery and cleanup. - Index:
(scope_id, session_anchor)for context-level runtime lookup. - Index:
workdirfor project-level cleanup and diagnostics.
auth_codes
auth_codes stores bind-code lifecycle. It is independent from scopes because
codes can be created before any user scope exists.
Fields:
code: primary key and user-entered token.type:one_timeorexpiring.is_active: whether the code can still be used.expires_at: optional expiration.used_by_json: list of user scope keys or legacy user IDs that consumed it.created_at,updated_at: lifecycle timestamps.
Constraints:
- Primary key:
code. - No extra unique constraint.
state_meta
state_meta stores database-level metadata.
Fields:
key: primary key.value_json: JSON-encoded value.updated_at: timestamp.
Current keys:
json_import_completed_atsessions_last_activity
JSON Payload Policy
SQLite JSON functions exist, but this design does not rely on JSON as the main query interface. If a field is expected to be filtered, sorted, joined, or used for authorization, it becomes a column.
Column fields:
roleworkdiragent_variantmodelreasoning_effortnative_typeis_privatesupports_threadssession_anchorstatus
JSON fields:
show_message_types, because current code reads it with settings as a whole;- backend-specific temporary routing details;
- platform raw metadata such as username or flags;
- active poll internals such as baseline message IDs and seen tool calls.
Migration Flow
- Resolve target DB and state directory.
- Acquire
migration.lock. - Run Alembic migrations.
- If
state_meta.json_import_completed_atexists, skip JSON import. - Clear previously imported SQLite rows if a previous import failed before writing the marker.
- Back up
settings.json,sessions.json, anddiscovered_chats.jsontostate/backups/sqlite-state-migration-<timestamp>/. - Parse JSON from temporary copies so source JSON is never rewritten during import.
- Import settings through
SQLiteSettingsService. - Import sessions through
SQLiteSessionsService. - Import discovered chats into
scopes. - Run
PRAGMA integrity_check. - Write
state_meta.json_import_completed_at.
The old JSON files are not deleted and are no longer written after the stores switch to SQLite. Later startup decisions rely on the SQLite marker, not on whether JSON files still exist.
Compatibility Notes
- Existing
SettingsStoremethods continue returningChannelSettings,GuildSettings,UserSettings, andBindCode. - Existing
SessionsStoremethods continue returningSessionStateandActivePollInfo. - Settings writes upsert missing scopes but do not erase observed scope metadata such as private/thread capability flags gathered from discovered chats.
- Legacy session values that were not strings are stored with a JSON prefix and decoded back to their original shape.
- Legacy raw session scope keys are preserved in
agent_sessions.metadata_jsonso directSessionsStore.save()/ reload behavior does not rename keys. - Platform backfill for old sessions still requires a primary platform when the platform cannot be inferred.
Validation
Focused validation for this migration:
uv run pytest \
tests/test_sqlite_state_migration.py \
tests/test_sqlite_settings_store.py \
tests/test_sqlite_sessions_store.py \
tests/test_sqlite_state_startup.py
The test coverage checks:
- Alembic creates and stamps the initial schema;
- old JSON files are backed up and not rewritten;
- import marker is only written after successful import;
- invalid JSON can be fixed and retried;
- custom state directories do not create default home directories;
- Settings and Sessions stores reload external SQLite writes;
- legacy session mappings and non-string session values round-trip.