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.
Python agentagent-osaiai-agentsai-partnerchatopsclaudeclaude-codecodexdevtoolsdiscord-botlark-botllmlocal-firstlong-term-memoryopencodeslack-botvibe-codingvibeywechat
from __future__ import annotations
import os
import sqlite3
import logging
import threading
from importlib import import_module
from pathlib import Path
from types import SimpleNamespace
from alembic import command
from alembic.config import Config
from alembic.script import ScriptDirectory
from config import paths
from storage.backups import (
begin_sqlite_backup_migration,
complete_sqlite_backup_migration,
create_sqlite_migration_backup,
prune_state_backups,
)
from storage.db import create_sqlite_engine, sqlite_url
from storage.lock import MigrationFileLock, migration_lock_path_for
logger = logging.getLogger(__name__)
# Alembic's EnvironmentContext installs module-level proxies while an upgrade
# runs. Concurrent store initialization can otherwise tear down another
# thread's proxy and surface errors such as KeyError("config"). This owns that
# hazard and nothing else: the proxies are per interpreter, not per database, so
# it must stay global, and it says nothing about other processes.
_MIGRATION_LOCK = threading.RLock()
INITIAL_REVISION = "20260501_0001"
# Stamp FLOOR for head-shaped unversioned databases, deliberately NOT head: after
# `_stamp_existing_initial_schema` stamps it, `_run_migrations_locked` upgrades to head,
# so every revision after this one replays on a schema that already looks like head.
# Each such migration must therefore stay idempotent/replay-safe against head shape
# (e.g. `drop index if exists` before `create index`) rather than assume a virgin table.
LATEST_SCHEMA_REVISION = "20260622_0023"
REMOVE_LEGACY_DEFAULT_AGENT_REVISION = "20260530_0008"
ALLOW_DEV_STATE_MIGRATION_ENV = "AVIBE_ALLOW_DEV_STATE_MIGRATION"
INITIAL_TABLES = {
"state_meta",
"agents",
"scopes",
"scope_settings",
"auth_codes",
"agent_sessions",
"runtime_records",
}
class UnsafeDefaultStateMigrationError(RuntimeError):
"""Raised when source-checkout code would migrate the user's default state."""
HEAD_TABLES = INITIAL_TABLES | {
"run_definitions",
"agent_runs",
"show_pages",
"show_page_access_entries",
"messages",
"message_deliveries",
"session_turns",
"session_turn_replies",
"agent_events",
"session_digests",
"skill_usage_daily",
"show_session_events",
"media_objects",
"media_object_references",
"web_push_subscriptions",
"project_access_policies",
"project_access_bindings",
"remote_access_authorizations",
"vault_secrets",
"vault_requests",
"vault_grants",
"vault_audit",
"vault_auth_factors",
"vault_operation_challenges",
"resource_access_policies",
"resource_access_groups",
}
PRE_SHOW_SESSION_EVENTS_HEAD_TABLES = INITIAL_TABLES | {
"run_definitions",
"agent_runs",
"show_pages",
"messages",
}
PRE_SHOW_SESSION_EVENTS_REQUIRED_COLUMNS = {
"agents": {"enabled"},
"scope_settings": {"agent_name"},
"agent_sessions": {"agent_id", "agent_name", "visibility", "pinned"},
"messages": {"type", "source"},
"run_definitions": {
"deleted_at",
"definition_type",
"agent_name",
"session_policy",
"message",
"message_payload_json",
"last_run_id",
"retired_at",
},
"agent_runs": {
"definition_id",
"source_kind",
"source_actor",
"parent_run_id",
"agent_name",
"agent_id",
"agent_backend",
"model",
"reasoning_effort",
"session_policy",
"message",
"message_payload_json",
"result_text",
"result_payload_json",
"message_ids_json",
"callback_session_id",
"callback_status",
"callback_error",
"callback_run_id",
"callback_completed_at",
"callback_terminal_turn_id",
"cancel_requested",
"cancel_requested_at",
},
}
HEAD_REQUIRED_COLUMNS = PRE_SHOW_SESSION_EVENTS_REQUIRED_COLUMNS | {
"run_definitions": PRE_SHOW_SESSION_EVENTS_REQUIRED_COLUMNS["run_definitions"]
| {"retirement_reason"},
"agent_sessions": PRE_SHOW_SESSION_EVENTS_REQUIRED_COLUMNS["agent_sessions"]
| {
"composer_draft_text",
"composer_draft_updated_at",
},
"message_deliveries": {
"message_id",
"priority",
"state",
"dispatch_text",
"delivery_history_json",
"version",
},
"messages": PRE_SHOW_SESSION_EVENTS_REQUIRED_COLUMNS["messages"] | {"context_seq"},
"agent_events": {"context_seq"},
"session_turns": {
"initial_delivery_id",
"state",
"native_turn_id",
"control_state",
"terminal_outcome",
"settled_by",
"version",
},
}
HEAD_ONLY_REQUIRED_COLUMNS = {
"vault_secrets": {"fields"},
"show_pages": {"access_mode", "access_revision", "share_id", "offline_at"},
"show_page_access_entries": {
"page_id",
"kind",
"value",
"organization_id",
"created_at",
},
"web_push_subscriptions": {"device_id", "provider_invalidated_at"},
"remote_access_authorizations": {
"instance_id",
"subject",
"email",
"scope_kind",
"scope_ref",
"authorization_state",
"claims_json",
"expires_at",
"created_at",
"last_checked_at",
"updated_at",
},
"vault_requests": {"callback_status"},
"vault_grants": {"agent_ready", "agent_ready_at"},
"vault_auth_factors": {"credential_id", "public_key", "alg", "sign_count"},
"vault_operation_challenges": {"challenge_hash", "rp_id", "origin", "expires_at", "consumed_at"},
"resource_access_policies": {
"organization_id",
"owner_user_id",
"owner_email",
"access_level",
"policy_revision",
"last_applied_control_plane_revision",
"created_by_user_id",
"updated_by_user_id",
},
"resource_access_groups": {"organization_id", "group_id"},
}
UNRELEASED_OLD_INITIAL_TABLES = [
"session_messages",
"chat_sessions",
"channel_settings",
"guild_settings",
"guild_policies",
"user_settings",
"bind_codes",
"agent_session_bindings",
"active_threads",
"active_polls",
"processed_messages",
"discovered_chats",
"scopes",
"schema_meta",
"alembic_version",
]
# The members above that no release could have produced, which is what makes the presence
# of one evidence of an unreleased build. `scopes` is in INITIAL_TABLES and every stamped
# database has `alembic_version` -- the check below requires it a few lines earlier -- so
# testing the full list proved nothing: any database short of INITIAL_TABLES matched, and
# a released one then had its `scopes` and its stamp dropped out from under it. Derived
# rather than written out, so a table added to INITIAL_TABLES leaves this correct.
UNRELEASED_ONLY_INITIAL_TABLES = [
table
for table in UNRELEASED_OLD_INITIAL_TABLES
if table not in INITIAL_TABLES and table != "alembic_version"
]
def alembic_dir() -> Path:
return Path(__file__).resolve().parent / "alembic"
def set_literal_main_option(cfg: Config, name: str, value: str) -> None:
"""Store `value` in Alembic's config so it reads back unchanged.
Alembic keeps main options in a ConfigParser with `%` interpolation, so a raw
`%` is rejected on write, or, shaped like `%(here)s`, silently replaced on
read with a different path. Paths reach here verbatim: a directory can contain a
literal `%`, and SQLAlchemy 2.1 percent-encodes every non-ASCII character when
rendering a URL, so a state directory under a Chinese user name became
`%E9%...` and migrations failed before they started. Escaping is the
documented way to pass a literal through; interpolation undoes it on read.
"""
cfg.set_main_option(name, value.replace("%", "%%"))
def alembic_config(db_path: Path | None = None) -> Config:
cfg = Config()
set_literal_main_option(cfg, "script_location", str(alembic_dir()))
set_literal_main_option(cfg, "sqlalchemy.url", sqlite_url(db_path or paths.get_sqlite_state_path()))
return cfg
def run_migrations(
db_path: Path | None = None,
*,
revision: str = "head",
prune_backups_after_upgrade: bool = True,
) -> None:
"""Bring one SQLite state database to `revision`, one migrator at a time.
Exclusion belongs here rather than at the call sites, because "at most one
migrator per database" is a property of the database, and a call site can
only promise it for itself. It was previously promised by `ensure_sqlite_state`
alone, which left every other entry point -- the discovery helpers, a store
constructed with an explicit `db_path`, the background-table bootstrap --
running `command.upgrade` against a file another process could be upgrading
at the same moment. The controller and the Web UI are separate processes on
one state directory, so that pairing is the ordinary case, not a corner.
Both locks are load-bearing and neither substitutes for the other: the file
lock is machine-wide and per database, and `_MIGRATION_LOCK` is per
interpreter and global, guarding Alembic's module-level proxies.
The file lock is taken first, and always first, which is what makes the pair
deadlock-free. Taking the global one first would also let a thread hold every
database's migrations in this process while it waits on a foreign process --
a remote stall propagating into unrelated local work.
The wait is deliberately unbounded. A file lock is released by the OS when
its holder dies, so the only way to wait forever is for a live process to
still be migrating -- and a migration's duration is bounded by the data, not
by anything we could name here. Any deadline would be a guess that turns a
slow, correct upgrade into a failed startup for every other entry point.
Waiting is logged with the holder's pid so the wait stays diagnosable.
"""
target_db = (db_path or paths.get_sqlite_state_path()).expanduser().resolve()
guard_source_checkout_default_state_migration(target_db)
with MigrationFileLock(migration_lock_path_for(target_db), timeout_seconds=None):
with _MIGRATION_LOCK:
_run_migrations_locked(
target_db,
revision=revision,
prune_backups_after_upgrade=prune_backups_after_upgrade,
)
def _run_migrations_locked(
target_db: Path,
*,
revision: str,
prune_backups_after_upgrade: bool,
) -> None:
cfg = alembic_config(target_db)
backup_revisions = _migration_backup_revisions(target_db, cfg, revision)
backup_attempt: str | None = None
if backup_revisions is not None:
_current_revisions, target_revisions = backup_revisions
backup_path = create_sqlite_migration_backup(target_db, to_revisions=target_revisions)
backup_attempt = begin_sqlite_backup_migration(target_db, backup_path)
logger.info("Pre-migration SQLite rollback point at %s", backup_path)
_reset_unreleased_initial_schema_drift(target_db)
_repair_unreleased_head_schema_drift(target_db)
_stamp_existing_initial_schema(target_db, cfg)
command.upgrade(cfg, revision)
if backup_attempt is not None:
try:
complete_sqlite_backup_migration(target_db, backup_attempt)
except OSError:
logger.warning("SQLite migration completed without expiry evidence; retaining backups", exc_info=True)
if prune_backups_after_upgrade:
prune_state_backups(target_db.parent / "backups", json_retention=None)
def _migration_backup_revisions(db_path: Path, cfg: Config, revision: str) -> tuple[set[str], set[str]] | None:
if not db_path.exists() or db_path.stat().st_size == 0:
return None
with sqlite3.connect(db_path) as conn:
tables = _table_names(conn)
if not tables - {"alembic_version"}:
return None
current_revisions = (
{str(row[0]) for row in conn.execute("select version_num from alembic_version")}
if "alembic_version" in tables
else set()
)
script = ScriptDirectory.from_config(cfg)
target_revisions = {item.revision for item in script.get_revisions(revision)}
if current_revisions == target_revisions:
return None
return current_revisions, target_revisions
def background_tables_ready(db_path: Path | None = None) -> bool:
target_db = (db_path or paths.get_sqlite_state_path()).expanduser().resolve()
if not target_db.exists():
return False
with sqlite3.connect(target_db) as conn:
tables = _table_names(conn)
return _head_schema_ready(conn, tables)
def initialize_background_tables(db_path: Path | None = None) -> None:
target_db = db_path or paths.get_sqlite_state_path()
run_migrations(target_db)
def ensure_background_indexes(db_path: Path | None = None) -> None:
"""Repair head indexes even when the tables already pass readiness checks.
``metadata.create_all`` can produce every required table and column without
migration-owned indexes. Such a database correctly skips schema migration,
so store construction must still enter the index repair path. Correct indexes
are detected byte-for-byte against their owning migration DDL and are not
rebuilt on ordinary opens.
"""
target_db = (db_path or paths.get_sqlite_state_path()).expanduser().resolve()
if not target_db.exists():
return
with sqlite3.connect(target_db) as conn:
tables = _table_names(conn)
if {"run_definitions", "agent_runs"}.issubset(tables) and not _agent_runs_managed_indexes_ready(conn):
_ensure_agent_runs_managed_indexes(conn)
conn.commit()
def guard_source_checkout_default_state_migration(db_path: Path) -> None:
if _env_flag_enabled(ALLOW_DEV_STATE_MIGRATION_ENV):
return
if not _running_from_source_checkout():
return
if not _is_default_user_state_db(db_path):
return
source_root = Path(__file__).resolve().parents[1]
raise UnsafeDefaultStateMigrationError(
"Refusing to run SQLite migrations from an Avibe source checkout "
f"against the default user state DB at {db_path.expanduser().resolve()}. "
f"Source root: {source_root}. "
"Set AVIBE_HOME to an isolated development directory before running "
"worktree/source CLI commands. If you intentionally need to migrate the "
f"default local state from source, set {ALLOW_DEV_STATE_MIGRATION_ENV}=1."
)
def guard_source_checkout_default_state_bootstrap() -> None:
guard_source_checkout_default_state_migration(paths.get_sqlite_state_path())
def _running_from_source_checkout() -> bool:
source_root = Path(__file__).resolve().parents[1]
return (source_root / "pyproject.toml").exists() and (source_root / "storage" / "migrations.py").exists()
def _is_default_user_state_db(db_path: Path) -> bool:
target = db_path.expanduser().resolve()
return target in _default_user_state_db_candidates()
def _default_user_state_db_candidates() -> set[Path]:
home = Path.home().expanduser().resolve()
return {
(home / paths.AVIBE_HOME_DIRNAME / "state" / "vibe.sqlite").resolve(),
(home / paths.LEGACY_HOME_DIRNAME / "state" / "vibe.sqlite").resolve(),
}
def _env_flag_enabled(name: str) -> bool:
value = os.environ.get(name)
if value is None:
return False
return value.strip().lower() in {"1", "true", "yes", "on"}
def _reset_unreleased_initial_schema_drift(db_path: Path) -> None:
path = db_path.expanduser().resolve()
if not path.exists():
return
with sqlite3.connect(path) as conn:
tables = _table_names(conn)
if INITIAL_TABLES.issubset(tables):
return
if "alembic_version" not in tables:
return
version = conn.execute("select version_num from alembic_version").fetchone()
if version != (INITIAL_REVISION,):
return
if not any(table in tables for table in UNRELEASED_ONLY_INITIAL_TABLES):
return
conn.execute("PRAGMA foreign_keys = OFF")
for table in UNRELEASED_OLD_INITIAL_TABLES:
if table in tables:
conn.execute(f'drop table if exists "{table}"')
conn.commit()
def _repair_unreleased_head_schema_drift(db_path: Path) -> None:
path = db_path.expanduser().resolve()
if not path.exists():
return
with sqlite3.connect(path) as conn:
tables = _table_names(conn)
_rename_legacy_background_tables(conn, tables)
tables = _table_names(conn)
if not (tables <= {"alembic_version", "agents"}):
_ensure_agents_table(conn, tables)
tables = _table_names(conn)
_repair_initial_required_columns(conn, tables)
tables = _table_names(conn)
if not PRE_SHOW_SESSION_EVENTS_HEAD_TABLES.issubset(tables):
return
if "alembic_version" not in tables:
return
version = conn.execute("select version_num from alembic_version").fetchone()
if version not in {("20260515_0002",), ("20260522_0003",), ("20260523_0004",)}:
return
if not _pre_show_session_events_head_schema_ready(conn, tables) and _repair_head_required_columns(conn, tables):
conn.commit()
def _stamp_existing_initial_schema(db_path: Path, cfg: Config) -> None:
path = db_path.expanduser().resolve()
if not path.exists():
return
with sqlite3.connect(path) as conn:
tables = _table_names(conn)
_rename_legacy_background_tables(conn, tables)
tables = _table_names(conn)
if not (tables <= {"alembic_version"}):
_ensure_agents_table(conn, tables)
tables = _table_names(conn)
_repair_initial_required_columns(conn, tables)
tables = _table_names(conn)
if not tables:
return
if not (tables - {"alembic_version", "agents"}):
return
if "alembic_version" in tables:
version = conn.execute("select version_num from alembic_version").fetchone()
if version is not None and version[0]:
return
missing_initial_tables = INITIAL_TABLES - tables
if not (tables & (INITIAL_TABLES - {"agents"})) and (tables & {"run_definitions", "agent_runs"}):
return
if missing_initial_tables and (tables & INITIAL_TABLES):
missing = ", ".join(sorted(missing_initial_tables))
raise RuntimeError(f"existing SQLite schema is incomplete; missing initial tables: {missing}")
if not INITIAL_TABLES.issubset(tables):
return
if HEAD_TABLES.issubset(tables):
if not _head_schema_ready(conn, tables):
_repair_head_required_columns(conn, tables)
conn.commit()
tables = _table_names(conn)
if not _head_schema_ready(conn, tables):
missing = _missing_head_schema_description(conn, tables)
raise RuntimeError(f"existing SQLite head schema is incomplete; missing: {missing}")
_ensure_head_indexes(conn, tables)
_delete_historical_message_tool_calls(conn, tables)
conn.commit()
_run_remove_legacy_default_agent_migration(db_path)
command.stamp(cfg, LATEST_SCHEMA_REVISION)
_run_post_stamp_data_migrations(db_path)
return
if PRE_SHOW_SESSION_EVENTS_HEAD_TABLES.issubset(tables):
if not _pre_show_session_events_head_schema_ready(conn, tables):
_repair_head_required_columns(conn, tables)
conn.commit()
tables = _table_names(conn)
if not _pre_show_session_events_head_schema_ready(conn, tables):
missing = _missing_pre_show_session_events_head_schema_description(conn, tables)
raise RuntimeError(f"existing SQLite head schema is incomplete; missing: {missing}")
_run_remove_legacy_default_agent_migration(db_path)
command.stamp(cfg, REMOVE_LEGACY_DEFAULT_AGENT_REVISION)
_run_post_stamp_data_migrations(db_path)
return
command.stamp(cfg, INITIAL_REVISION)
def _run_remove_legacy_default_agent_migration(db_path: Path) -> None:
revision_module = import_module("storage.alembic.versions.20260530_0008_remove_legacy_default_agent")
engine = create_sqlite_engine(db_path)
try:
with engine.begin() as conn:
original_op = revision_module.op
revision_module.op = SimpleNamespace(get_bind=lambda: conn)
try:
revision_module.upgrade()
finally:
revision_module.op = original_op
finally:
engine.dispose()
def _run_post_stamp_data_migrations(db_path: Path) -> None:
from storage.importer import _run_sqlite_data_migrations
engine = create_sqlite_engine(db_path)
try:
with engine.begin() as conn:
_run_sqlite_data_migrations(conn)
finally:
engine.dispose()
def _table_names(conn: sqlite3.Connection) -> set[str]:
return {
row[0]
for row in conn.execute(
"select name from sqlite_master where type = 'table'",
)
}
def _column_names(conn: sqlite3.Connection, table: str) -> set[str]:
return {row[1] for row in conn.execute(f'pragma table_info("{table}")')}
def _head_schema_ready(conn: sqlite3.Connection, tables: set[str]) -> bool:
if not HEAD_TABLES.issubset(tables):
return False
required = HEAD_REQUIRED_COLUMNS | HEAD_ONLY_REQUIRED_COLUMNS
return all(required_columns.issubset(_column_names(conn, table)) for table, required_columns in required.items())
def _pre_show_session_events_head_schema_ready(conn: sqlite3.Connection, tables: set[str]) -> bool:
if not PRE_SHOW_SESSION_EVENTS_HEAD_TABLES.issubset(tables):
return False
return all(
required_columns.issubset(_column_names(conn, table))
for table, required_columns in PRE_SHOW_SESSION_EVENTS_REQUIRED_COLUMNS.items()
)
def _repair_head_required_columns(conn: sqlite3.Connection, tables: set[str]) -> bool:
if not PRE_SHOW_SESSION_EVENTS_HEAD_TABLES.issubset(tables):
return False
changed = False
changed = _repair_initial_required_columns(conn, tables) or changed
definition_columns = _column_names(conn, "run_definitions")
if "definition_type" not in definition_columns and "task_type" in definition_columns:
conn.execute('alter table "run_definitions" rename column "task_type" to "definition_type"')
changed = True
definition_columns = _column_names(conn, "run_definitions")
for column, column_type in {
"deleted_at": "VARCHAR",
"agent_name": "VARCHAR",
"session_policy": "VARCHAR",
"message": "TEXT",
"message_payload_json": "TEXT",
"last_run_id": "VARCHAR",
"retired_at": "VARCHAR",
"retirement_reason": "VARCHAR",
}.items():
if column not in definition_columns:
conn.execute(f'alter table "run_definitions" add column "{column}" {column_type}')
changed = True
definition_columns = _column_names(conn, "run_definitions")
if "message" in definition_columns and "prompt" in definition_columns:
conn.execute('update "run_definitions" set message = prompt where message is null')
if "session_policy" in definition_columns:
conn.execute(
'update "run_definitions" set session_policy = '
'case '
'when session_id is not null and session_id != "" then "existing" '
'when legacy_session_key is not null and legacy_session_key != "" then "existing" '
"else null end "
'where session_policy is null'
)
run_columns = _column_names(conn, "agent_runs")
if "definition_id" not in run_columns and "task_id" in run_columns:
conn.execute('alter table "agent_runs" rename column "task_id" to "definition_id"')
changed = True
run_columns = _column_names(conn, "agent_runs")
for column, column_type in {
"source_kind": "VARCHAR",
"source_actor": "TEXT",
"parent_run_id": "VARCHAR",
"agent_name": "VARCHAR",
"agent_id": "VARCHAR",
"agent_backend": "VARCHAR",
"model": "VARCHAR",
"reasoning_effort": "VARCHAR",
"session_policy": "VARCHAR",
"message": "TEXT",
"message_payload_json": "TEXT",
"result_text": "TEXT",
"result_payload_json": "TEXT",
"message_ids_json": "TEXT",
"callback_session_id": "VARCHAR",
"callback_status": "VARCHAR",
"callback_error": "TEXT",
"callback_run_id": "VARCHAR",
"callback_completed_at": "VARCHAR",
"callback_terminal_turn_id": "VARCHAR",
"cancel_requested": "INTEGER not null default 0",
"cancel_requested_at": "VARCHAR",
}.items():
if column not in run_columns:
conn.execute(f'alter table "agent_runs" add column "{column}" {column_type}')
changed = True
run_columns = _column_names(conn, "agent_runs")
if "message" in run_columns and "prompt" in run_columns:
conn.execute('update "agent_runs" set message = prompt where message is null')
# messages.type (20260531_0009): add + backfill, mirroring the migration, so
# a drifted/unversioned head schema reaches readiness instead of leaving
# messages_service.append writing a column that doesn't exist.
if "messages" in tables and "type" not in _column_names(conn, "messages"):
conn.execute('alter table "messages" add column "type" VARCHAR not null default \'assistant\'')
conn.execute(
"""
update messages set type = case
when author = 'user' then 'user'
when json_extract(content_json, '$.kind') = 'notify' then 'notify'
when json_extract(content_json, '$.kind') = 'result' then 'result'
when json_extract(content_json, '$.kind') in ('toolcall', 'tool_call') then 'tool_call'
else 'assistant'
end
"""
)
changed = True
# messages.source (20260531_0010): origin (user/agent/harness), distinct
# from author. Mirror the migration's add + backfill for drifted heads.
if "messages" in tables and "source" not in _column_names(conn, "messages"):
conn.execute('alter table "messages" add column "source" VARCHAR')
conn.execute(
"""
update messages set source = case
when author = 'user' then 'user'
when author = 'agent' then 'agent'
when author = 'system' then 'agent'
else null
end
"""
)
changed = True
if "web_push_subscriptions" in tables and "device_id" not in _column_names(conn, "web_push_subscriptions"):
conn.execute('alter table "web_push_subscriptions" add column "device_id" VARCHAR')
changed = True
if "web_push_subscriptions" in tables and "provider_invalidated_at" not in _column_names(conn, "web_push_subscriptions"):
conn.execute('alter table "web_push_subscriptions" add column "provider_invalidated_at" VARCHAR')
changed = True
# context_seq (20261002_0063): the Avibe Agent's model-context order key.
for table in ("messages", "agent_events"):
if table in tables and "context_seq" not in _column_names(conn, table):
conn.execute(f'alter table "{table}" add column "context_seq" INTEGER')
changed = True
if "vault_secrets" in tables and "fields" not in _column_names(conn, "vault_secrets"):
conn.execute('alter table "vault_secrets" add column "fields" TEXT')
changed = True
_ensure_head_indexes(conn, tables)
return changed
def _rename_legacy_background_tables(conn: sqlite3.Connection, tables: set[str]) -> bool:
changed = False
if "background_tasks" in tables and "run_definitions" not in tables:
conn.execute('alter table "background_tasks" rename to "run_definitions"')
changed = True
if "background_runs" in tables and "agent_runs" not in tables:
conn.execute('alter table "background_runs" rename to "agent_runs"')
changed = True
return changed
def _ensure_agents_table(conn: sqlite3.Connection, tables: set[str]) -> bool:
if "agents" in tables:
return False
conn.execute(
"""
create table agents (
id varchar primary key,
name varchar not null,
normalized_name varchar not null,
description text,
backend varchar not null,
model varchar,
reasoning_effort varchar,
system_prompt text,
enabled integer not null default 1,
source varchar not null,
source_ref text,
metadata_json text not null,
archived_at varchar,
created_at varchar not null,
updated_at varchar not null,
constraint uq_agents_normalized_name unique (normalized_name)
)
"""
)
conn.execute('create index if not exists ix_agents_backend on agents (backend)')
conn.execute('create index if not exists ix_agents_updated on agents (updated_at)')
return True
def _repair_initial_required_columns(conn: sqlite3.Connection, tables: set[str]) -> bool:
changed = False
if "agents" in tables:
columns = _column_names(conn, "agents")
if "enabled" not in columns:
conn.execute('alter table "agents" add column "enabled" INTEGER not null default 1')
changed = True
if "archived_at" not in columns:
conn.execute('alter table "agents" add column "archived_at" VARCHAR')
changed = True
if "scope_settings" in tables:
columns = _column_names(conn, "scope_settings")
if "agent_name" not in columns:
conn.execute('alter table "scope_settings" add column "agent_name" VARCHAR')
changed = True
if "agent_sessions" in tables:
columns = _column_names(conn, "agent_sessions")
if "agent_id" not in columns:
conn.execute('alter table "agent_sessions" add column "agent_id" VARCHAR')
changed = True
if "agent_name" not in columns:
conn.execute('alter table "agent_sessions" add column "agent_name" VARCHAR')
changed = True
if "visibility" not in columns:
conn.execute(
'alter table "agent_sessions" add column "visibility" '
"VARCHAR not null default 'foreground'"
)
changed = True
if "pinned" not in columns:
conn.execute(
'alter table "agent_sessions" add column "pinned" '
"INTEGER not null default 0"
)
changed = True
return changed
def _ensure_new_background_indexes(conn: sqlite3.Connection) -> None:
conn.execute('create index if not exists ix_run_definitions_type_enabled on run_definitions (definition_type, enabled)')
conn.execute('create index if not exists ix_run_definitions_session on run_definitions (session_id)')
conn.execute('create index if not exists ix_run_definitions_agent on run_definitions (agent_name)')
conn.execute('create index if not exists ix_run_definitions_updated on run_definitions (updated_at)')
conn.execute('create index if not exists ix_agent_runs_definition_created on agent_runs (definition_id, created_at)')
conn.execute('create index if not exists ix_agent_runs_status_created on agent_runs (status, created_at)')
conn.execute('create index if not exists ix_agent_runs_type_status_created on agent_runs (run_type, status, created_at)')
conn.execute('create index if not exists ix_agent_runs_session_created on agent_runs (session_id, created_at)')
conn.execute('create index if not exists ix_agent_runs_agent_created on agent_runs (agent_name, created_at)')
conn.execute('create index if not exists ix_agent_runs_callback_status on agent_runs (callback_status, completed_at)')
conn.execute('create index if not exists ix_agent_runs_updated on agent_runs (updated_at)')
live_index = import_module("storage.alembic.versions.20261018_0070_run_definitions_live_index")
live_row = conn.execute(
"select sql from sqlite_master where type = 'index' and name = ?",
(live_index._INDEX,),
).fetchone()
if live_row is None or _normalized_index_sql(live_row[0]) != _normalized_index_sql(live_index.CREATE_INDEX_SQL):
conn.execute(live_index.DROP_INDEX_SQL)
conn.execute(live_index.CREATE_INDEX_SQL)
if not _agent_runs_managed_indexes_ready(conn):
_ensure_agent_runs_managed_indexes(conn)
#: The ``agent_runs`` index migrations whose DDL the head-schema repair path must also
#: install, oldest first. 0040 is deliberately absent: 0041 drops the index 0040 created
#: and recreates it under its own two-expression definition, so 0040's shape is never a
#: valid end state and replaying it here would only build an index 0041 has to replace.
_AGENT_RUNS_INDEX_REVISION_MODULES = (
"storage.alembic.versions.20260728_0039_agent_runs_settled_at_index",
"storage.alembic.versions.20260728_0041_agent_runs_owed_notice_backoff_index",
"storage.alembic.versions.20260729_0042_agent_runs_definition_streak_index",
"storage.alembic.versions.20260811_0051_callback_terminal_turn_identity",
"storage.alembic.versions.20261015_0066_agent_runs_callback_parent_index",
)
#: Columns the managed indexes read. A head-shaped database has all of them, but
#: this helper is also reached from ``_repair_head_required_columns`` on older drifted
#: shapes, and ``create index`` on a missing column raises rather than skipping.
_AGENT_RUNS_INDEX_COLUMNS = frozenset(
{
"definition_id",
"created_at",
"completed_at",
"metadata_json",
"callback_terminal_turn_id",
"session_id",
"run_type",
"source_kind",
"parent_run_id",
"source_actor",
"id",
}
)
def _normalized_index_sql(value: object) -> str:
return " ".join(str(value or "").split()).casefold()
def _agent_runs_managed_indexes_ready(conn: sqlite3.Connection) -> bool:
if not _AGENT_RUNS_INDEX_COLUMNS.issubset(_column_names(conn, "agent_runs")):
return True
for module_name in _AGENT_RUNS_INDEX_REVISION_MODULES:
revision_module = import_module(module_name)
row = conn.execute(
"select sql from sqlite_master where type = 'index' and name = ?",
(revision_module._INDEX,),
).fetchone()
if row is None or _normalized_index_sql(row[0]) != _normalized_index_sql(revision_module.CREATE_INDEX_SQL):
return False
return True
def _ensure_agent_runs_managed_indexes(conn: sqlite3.Connection) -> None:
"""Install migration-owned ``agent_runs`` indexes on a head-shaped database.
``_ensure_head_indexes`` promises that a head-shaped unversioned database ends up
with every index head has. It once silently lagged the three performance indexes;
the same repair path now owns the callback identity index too.
Two lanes make that gap reachable without any migration ever running: a database
born from ``metadata.create_all`` satisfies ``background_tables_ready`` (tables and
columns only — never indexes), so ``SQLiteBackgroundTaskStore`` accepts it as ready
and never calls ``initialize_background_tables``; and the day
``LATEST_SCHEMA_REVISION`` moves past 0038 the stamp floor stops replaying them for
everyone. Both leave the two-second owed-notice tick, the health window and the
streak read scanning run history with nothing to repair them.
The DDL is IMPORTED from the revision that owns it, never retyped. The owed-notice
index is an expression index and SQLite matches an index expression against a query
expression by text, so a second copy of that string is an index that gets built and
silently ignored — the failure 0040 and 0041 were each written to correct. Importing
the revision module is the same technique ``_run_remove_legacy_default_agent_migration``
already uses; ``storage.background`` (which holds the query-side twins) cannot be
imported here because it imports this module.
Drop-then-create rather than ``create index if not exists``: a database carrying the
superseded 0040 shape of ``ix_agent_runs_owed_notice`` has the NAME already taken by
an index missing the backoff expression, and ``if not exists`` would leave that
weaker definition in place. Recreating unconditionally also makes the resulting
``sqlite_master`` SQL byte-identical to the migration's, whichever route a database
took to head.
"""
if not _AGENT_RUNS_INDEX_COLUMNS.issubset(_column_names(conn, "agent_runs")):
return
for module_name in _AGENT_RUNS_INDEX_REVISION_MODULES:
revision_module = import_module(module_name)
conn.execute(revision_module.DROP_INDEX_SQL)
conn.execute(revision_module.CREATE_INDEX_SQL)
def _ensure_messages_query_indexes(conn: sqlite3.Connection, tables: set[str]) -> None:
if "agent_sessions" in tables:
conn.execute(
"create index if not exists ix_agent_sessions_visibility "
"on agent_sessions (visibility)"
)
conn.execute(
"create index if not exists ix_agent_sessions_scope_status_activity "
"on agent_sessions (scope_id, status, last_active_at, created_at, id)"
)
conn.execute(
"create index if not exists ix_agent_sessions_scope_status_pinned_activity "
"on agent_sessions (scope_id, status, pinned, last_active_at, created_at, id)"
)
status_index = import_module(
"storage.alembic.versions.20261016_0068_agent_sessions_agent_status_index"
)
status_row = conn.execute(
"select sql from sqlite_master where type = 'index' and name = ?",
(status_index._INDEX,),
).fetchone()
if status_row is None or _normalized_index_sql(status_row[0]) != _normalized_index_sql(
status_index.CREATE_INDEX_SQL
):
conn.execute(status_index.DROP_INDEX_SQL)
conn.execute(status_index.CREATE_INDEX_SQL)
if "messages" not in tables:
return
transcript_index = import_module(
"storage.alembic.versions.20260804_0046_message_transcript_order"
)
transcript_row = conn.execute(
"select sql from sqlite_master where type = 'index' and name = ?",
(transcript_index._INDEX,),
).fetchone()
if (
transcript_row is None
or _normalized_index_sql(transcript_row[0])
!= _normalized_index_sql(transcript_index.CREATE_INDEX_SQL)
):
conn.execute(transcript_index.DROP_INDEX_SQL)
conn.execute(transcript_index.CREATE_INDEX_SQL)
# Read expression/partial-index DDL from its owning revision: SQLite matches
# these predicates syntactically, so a retyped copy may be silently ignored.
turn_index = import_module("storage.alembic.versions.20261012_0064_messages_turn_id_index")
recovery_index = import_module("storage.alembic.versions.20261016_0067_vibey_recovery_input_index")
indexes = [
(turn_index._INDEX, turn_index.DROP_INDEX_SQL, turn_index.CREATE_INDEX_SQL),
(recovery_index._INDEX, recovery_index.DROP_INDEX_SQL, recovery_index.CREATE_INDEX_SQL),
]
if "message_deliveries" in tables:
indexes.append((
recovery_index._DELIVERY_INDEX,
recovery_index.DROP_DELIVERY_INDEX_SQL,
recovery_index.CREATE_DELIVERY_INDEX_SQL,
))
for index_name, drop_sql, create_sql in indexes:
row = conn.execute(
"select sql from sqlite_master where type = 'index' and name = ?", (index_name,)
).fetchone()
if row is None or _normalized_index_sql(row[0]) != _normalized_index_sql(create_sql):
conn.execute(drop_sql)
conn.execute(create_sql)
conn.execute('create index if not exists ix_messages_session_created_id on messages (session_id, created_at, id)')
conn.execute('create index if not exists ix_messages_session_type_created_id on messages (session_id, type, created_at, id)')
conn.execute('create index if not exists ix_messages_platform_session_created_id on messages (platform, session_id, created_at, id)')
conn.execute(
'create index if not exists ix_messages_unread_session '
'on messages (platform, type, author, read_at, session_id)'
)
for index_name, index_sql in transcript_index._INDEX_SQL.items():
if index_name == transcript_index._INDEX:
continue
row = conn.execute(
"select sql from sqlite_master where type = 'index' and name = ?",
(index_name,),
).fetchone()
if row is not None and _normalized_index_sql(row[0]) == _normalized_index_sql(
index_sql
):
continue
conn.execute(f"drop index if exists {index_name}")
conn.execute(index_sql)
def _ensure_agent_events_indexes(conn: sqlite3.Connection, tables: set[str]) -> None:
if "agent_events" not in tables:
return
conn.execute(
"create index if not exists ix_agent_events_session_created_id "
"on agent_events (session_id, created_at, id)"
)
conn.execute(
"create index if not exists ix_agent_events_session_type_created_id "
"on agent_events (session_id, event_type, created_at, id)"
)
conn.execute(
"create index if not exists ix_agent_events_scope_created_id "
"on agent_events (scope_id, created_at, id)"
)
conn.execute(
"create index if not exists ix_agent_events_turn_sequence_id "
"on agent_events (turn_id, sequence, id)"
)
# The model-context order key of both tables (20261002_0063).
for table in ("messages", "agent_events"):
conn.execute(
f"create unique index if not exists uq_{table}_session_context_seq "
f"on {table} (session_id, context_seq) where context_seq is not null"
)
def _ensure_vault_authz_indexes(conn: sqlite3.Connection, tables: set[str]) -> None:
if "vault_auth_factors" in tables:
conn.execute(
"create index if not exists ix_vault_auth_factors_kind_rp "
"on vault_auth_factors (kind, rp_id, disabled_at)"
)
if "vault_operation_challenges" in tables:
conn.execute(
"create index if not exists ix_vault_operation_challenges_lookup "
"on vault_operation_challenges (operation, secret_name, expires_at)"
)
conn.execute(
"create index if not exists ix_vault_operation_challenges_consumed "
"on vault_operation_challenges (consumed_at, expires_at)"
)
def _ensure_resource_access_indexes(conn: sqlite3.Connection, tables: set[str]) -> None:
if "resource_access_policies" in tables:
conn.execute(
"create index if not exists ix_resource_access_policies_org_level "
"on resource_access_policies (organization_id, access_level, resource_kind)"
)
conn.execute(
"create index if not exists ix_resource_access_policies_owner "
"on resource_access_policies (owner_user_id, resource_kind)"
)
if "resource_access_groups" in tables:
conn.execute(
"create index if not exists ix_resource_access_groups_group "
"on resource_access_groups (organization_id, group_id, resource_kind)"
)
def _delete_historical_message_tool_calls(conn: sqlite3.Connection, tables: set[str]) -> None:
if "messages" not in tables:
return
if "show_session_events" in tables:
conn.execute(
"""
update show_session_events
set message_id = null
where message_id in (select id from messages where type = 'tool_call')
"""
)
if "media_objects" in tables:
conn.execute(
"""
update media_objects
set message_id = null
where message_id in (select id from messages where type = 'tool_call')
"""
)
conn.execute("delete from messages where type = 'tool_call'")
def _ensure_head_indexes(conn: sqlite3.Connection, tables: set[str]) -> None:
if {"run_definitions", "agent_runs"}.issubset(tables):
_ensure_new_background_indexes(conn)
_ensure_messages_query_indexes(conn, tables)
_ensure_agent_events_indexes(conn, tables)
_ensure_vault_authz_indexes(conn, tables)
_ensure_resource_access_indexes(conn, tables)
def _missing_head_schema_description(conn: sqlite3.Connection, tables: set[str]) -> str:
missing_parts = [f"tables {', '.join(sorted(HEAD_TABLES - tables))}"] if not HEAD_TABLES.issubset(tables) else []
for table, required_columns in (HEAD_REQUIRED_COLUMNS | HEAD_ONLY_REQUIRED_COLUMNS).items():
if table not in tables:
continue
missing_columns = required_columns - _column_names(conn, table)
if missing_columns:
missing_parts.append(f"{table}.{', '.join(sorted(missing_columns))}")
return "; ".join(missing_parts) or "unknown head schema drift"
def _missing_pre_show_session_events_head_schema_description(conn: sqlite3.Connection, tables: set[str]) -> str:
missing_parts = (
[f"tables {', '.join(sorted(PRE_SHOW_SESSION_EVENTS_HEAD_TABLES - tables))}"]
if not PRE_SHOW_SESSION_EVENTS_HEAD_TABLES.issubset(tables)
else []
)
for table, required_columns in PRE_SHOW_SESSION_EVENTS_REQUIRED_COLUMNS.items():
if table not in tables:
continue
missing_columns = required_columns - _column_names(conn, table)
if missing_columns:
missing_parts.append(f"{table}.{', '.join(sorted(missing_columns))}")
return "; ".join(missing_parts) or "unknown head schema drift"