Skip to content

armgit / avibe

Public Imported from GitHub
0

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.

65.2 KB
Raw Blame
from __future__ import annotations

from sqlalchemy import (
    CheckConstraint,
    Column,
    DDL,
    Float,
    ForeignKey,
    ForeignKeyConstraint,
    Index,
    Integer,
    MetaData,
    String,
    Table,
    Text,
    UniqueConstraint,
    event,
    func,
    text,
)

from storage.delivery_states import DELIVERY_STATES
from storage.vibey_recovery import VIBEY_RECOVERY_INPUT_PREDICATE
from vibe.message_types import build_partial_index_predicate

metadata = MetaData()


def _sql_string_set(values: tuple[str, ...]) -> str:
    return ", ".join(f"'{value}'" for value in values)


state_meta = Table(
    "state_meta",
    metadata,
    Column("key", String, primary_key=True),
    Column("value_json", Text, nullable=False),
    Column("updated_at", String, nullable=False),
)

agents = Table(
    "agents",
    metadata,
    Column("id", String, primary_key=True),
    Column("name", String, nullable=False),
    Column("normalized_name", String, nullable=False),
    Column("description", Text, nullable=True),
    Column("backend", String, nullable=False),
    Column("model", String, nullable=True),
    Column("reasoning_effort", String, nullable=True),
    Column("system_prompt", Text, nullable=True),
    Column("enabled", Integer, nullable=False),
    Column("source", String, nullable=False),
    Column("source_ref", Text, nullable=True),
    Column("metadata_json", Text, nullable=False),
    Column("archived_at", String, nullable=True),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    UniqueConstraint("normalized_name", name="uq_agents_normalized_name"),
    Index("ix_agents_backend", "backend"),
    Index("ix_agents_updated", "updated_at"),
)

scopes = Table(
    "scopes",
    metadata,
    Column("id", String, primary_key=True),
    Column("platform", String, nullable=False),
    Column("scope_type", String, nullable=False),
    Column("native_id", String, nullable=False),
    Column("parent_scope_id", String, ForeignKey("scopes.id", ondelete="SET NULL"), nullable=True),
    Column("display_name", Text, nullable=True),
    Column("native_type", String, nullable=True),
    Column("is_private", Integer, nullable=False),
    Column("supports_threads", Integer, nullable=False),
    Column("metadata_json", Text, nullable=False),
    Column("first_seen_at", String, nullable=False),
    Column("last_seen_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    UniqueConstraint("platform", "scope_type", "native_id", name="uq_scopes_platform_type_native"),
    Index("ix_scopes_platform_type", "platform", "scope_type"),
    Index("ix_scopes_parent", "parent_scope_id"),
)

scope_settings = Table(
    "scope_settings",
    metadata,
    Column("scope_id", String, ForeignKey("scopes.id", ondelete="CASCADE"), primary_key=True),
    Column("enabled", Integer, nullable=False),
    Column("role", String, nullable=True),
    Column("workdir", Text, nullable=True),
    Column("agent_name", String, nullable=True),
    Column("agent_backend", String, nullable=True),
    Column("agent_variant", String, nullable=True),
    Column("model", String, nullable=True),
    Column("reasoning_effort", String, nullable=True),
    Column("require_mention", Integer, nullable=True),
    Column("settings_version", Integer, nullable=False),
    Column("settings_json", Text, nullable=False),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    Index("ix_scope_settings_role", "role"),
    Index("ix_scope_settings_workdir", "workdir"),
    Index("ix_scope_settings_backend_model", "agent_backend", "model"),
)

project_access_policies = Table(
    "project_access_policies",
    metadata,
    Column("project_id", String, primary_key=True),
    Column("scope_id", String, ForeignKey("scopes.id", ondelete="CASCADE"), nullable=False),
    Column("organization_id", String, nullable=True),
    Column("mode", String, nullable=False, server_default="inherit"),
    Column("policy_revision", Integer, nullable=False, server_default="0"),
    Column("last_applied_control_plane_revision", Integer, nullable=False, server_default="0"),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    UniqueConstraint("scope_id", name="uq_project_access_policies_scope"),
    CheckConstraint("mode in ('inherit', 'restricted')", name="ck_project_access_policies_mode"),
    CheckConstraint("policy_revision >= 0", name="ck_project_access_policies_revision"),
    CheckConstraint(
        "last_applied_control_plane_revision >= 0",
        name="ck_project_access_policies_control_revision",
    ),
    Index("ix_project_access_policies_organization", "organization_id"),
)

project_access_bindings = Table(
    "project_access_bindings",
    metadata,
    Column(
        "project_id",
        String,
        ForeignKey("project_access_policies.project_id", ondelete="CASCADE"),
        primary_key=True,
    ),
    Column("principal_kind", String, primary_key=True),
    Column("principal_value", String, primary_key=True),
    Column("access_role", String, nullable=False),
    Column("created_at", String, nullable=False),
    CheckConstraint(
        "principal_kind in ('email', 'email_domain', 'organization_group')",
        name="ck_project_access_bindings_kind",
    ),
    CheckConstraint("access_role in ('editor', 'viewer')", name="ck_project_access_bindings_role"),
    Index(
        "ix_project_access_bindings_principal",
        "principal_kind",
        "principal_value",
    ),
)

remote_access_authorizations = Table(
    "remote_access_authorizations",
    metadata,
    Column("id", String, primary_key=True),
    Column("instance_id", String, nullable=False),
    Column("subject", String, nullable=False),
    Column("email", String, nullable=True),
    Column("scope_kind", String, nullable=True),
    Column("scope_ref", String, nullable=True),
    Column("authorization_state", String, nullable=True),
    Column("claims_json", Text, nullable=False),
    Column("expires_at", Integer, nullable=True),
    Column("created_at", Integer, nullable=False),
    Column("last_checked_at", Integer, nullable=True),
    Column("updated_at", Integer, nullable=True),
    Index("ix_remote_access_authorizations_expires", "expires_at"),
    Index(
        "ux_remote_access_authorizations_scope",
        "instance_id",
        "subject",
        "scope_kind",
        "scope_ref",
        unique=True,
        sqlite_where=text("scope_kind is not null and scope_ref is not null"),
    ),
)

auth_codes = Table(
    "auth_codes",
    metadata,
    Column("code", String, primary_key=True),
    Column("type", String, nullable=False),
    Column("is_active", Integer, nullable=False),
    Column("expires_at", String, nullable=True),
    Column("used_by_json", Text, nullable=False),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
)

agent_sessions = Table(
    "agent_sessions",
    metadata,
    Column("id", String, primary_key=True),
    Column("scope_id", String, ForeignKey("scopes.id", ondelete="SET NULL"), nullable=True),
    Column("agent_id", String, nullable=True),
    Column("agent_name", String, nullable=True),
    Column("agent_backend", String, nullable=False),
    Column("agent_variant", String, nullable=False),
    Column("model", String, nullable=True),
    Column("reasoning_effort", String, nullable=True),
    Column("session_anchor", String, nullable=False),
    Column("workdir", Text, nullable=True),
    Column("native_session_id", Text, nullable=False),
    Column("title", Text, nullable=True),
    Column("status", String, nullable=False),
    Column("visibility", String, nullable=False, server_default="foreground"),
    Column("pinned", Integer, nullable=False, server_default="0"),
    # Live agent-runtime status, distinct from the lifecycle ``status``
    # (active/archived). One of ``idle`` / ``running`` / ``failed`` —
    # ``running`` while a turn is in flight, ``failed`` when the most recent
    # turn errored, ``idle`` otherwise. Drives the workbench sidebar status dot.
    Column("agent_status", String, nullable=False, server_default="idle"),
    # Composer state is one value per Session, not a communication record.
    Column("composer_draft_text", Text, nullable=True),
    Column("composer_draft_updated_at", String, nullable=True),
    Column("metadata_json", Text, nullable=False),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    Column("last_active_at", String, nullable=True),
    # A thread is ONE session per (scope, anchor). The invariant shipped in the
    # Alembic revision only (20260601_0011), while ``SQLiteSessionsService.__init__``
    # calls ``metadata.create_all`` — so any DB born from models-only, including
    # tests, silently lacked it and could not reproduce the production collision.
    # Index name matches the revision's so the two agree on one object.
    Index("uq_agent_sessions_scope_anchor", "scope_id", "session_anchor", unique=True),
    Index("ix_agent_sessions_scope_anchor_workdir", "scope_id", "session_anchor", "workdir"),
    Index("ix_agent_sessions_backend_variant", "agent_backend", "agent_variant"),
    Index("ix_agent_sessions_status_activity", "status", "last_active_at"),
    # The Sessions projected running, for the startup projection repair (migration 0068).
    Index("ix_agent_sessions_agent_status", "agent_status", "status"),
    Index("ix_agent_sessions_visibility", "visibility"),
    Index("ix_agent_sessions_scope_status_activity", "scope_id", "status", "last_active_at", "created_at", "id"),
    Index(
        "ix_agent_sessions_scope_status_pinned_activity",
        "scope_id",
        "status",
        "pinned",
        "last_active_at",
        "created_at",
        "id",
    ),
    Index("ix_agent_sessions_native_session", "native_session_id"),
)

runtime_records = Table(
    "runtime_records",
    metadata,
    Column("id", String, primary_key=True),
    Column("record_type", String, nullable=False),
    Column("record_key", String, nullable=False),
    Column("scope_id", String, ForeignKey("scopes.id", ondelete="SET NULL"), nullable=True),
    Column("session_anchor", String, nullable=True),
    Column("workdir", Text, nullable=True),
    Column("payload_json", Text, nullable=False),
    Column("expires_at", String, nullable=True),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    UniqueConstraint("record_type", "record_key", name="uq_runtime_records_type_key"),
    Index("ix_runtime_records_type_scope_expiry", "record_type", "scope_id", "expires_at"),
    Index("ix_runtime_records_scope_anchor", "scope_id", "session_anchor"),
    Index("ix_runtime_records_workdir", "workdir"),
)

run_definitions = Table(
    "run_definitions",
    metadata,
    Column("id", String, primary_key=True),
    Column("definition_type", String, nullable=False),
    Column("name", Text, nullable=True),
    Column("agent_name", String, nullable=True),
    Column("session_policy", String, nullable=True),
    Column("session_id", String, nullable=True),
    Column("legacy_session_key", Text, nullable=True),
    Column("prompt", Text, nullable=True),
    Column("message", Text, nullable=True),
    Column("message_payload_json", Text, nullable=True),
    Column("schedule_type", String, nullable=True),
    Column("cron", Text, nullable=True),
    Column("run_at", String, nullable=True),
    Column("timezone", String, nullable=True),
    Column("command_json", Text, nullable=True),
    Column("shell_command", Text, nullable=True),
    Column("prefix", Text, nullable=True),
    Column("cwd", Text, nullable=True),
    Column("mode", String, nullable=True),
    Column("timeout_seconds", Float, nullable=True),
    Column("lifetime_timeout_seconds", Float, nullable=True),
    Column("retry_exit_codes_json", Text, nullable=True),
    Column("retry_delay_seconds", Float, nullable=True),
    Column("post_to", String, nullable=True),
    Column("deliver_key", Text, nullable=True),
    Column("enabled", Integer, nullable=False),
    Column("deleted_at", String, nullable=True),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    Column("last_started_at", String, nullable=True),
    Column("last_finished_at", String, nullable=True),
    Column("retired_at", String, nullable=True),
    Column("retirement_reason", String, nullable=True),
    Column("last_event_at", String, nullable=True),
    Column("last_run_at", String, nullable=True),
    Column("last_error", Text, nullable=True),
    Column("last_exit_code", Integer, nullable=True),
    Column("last_run_id", String, nullable=True),
    Column("metadata_json", Text, nullable=False),
    Index("ix_run_definitions_type_enabled", "definition_type", "enabled"),
    # Live definitions only (migration 0070): a removed one keeps ``enabled``.
    Index(
        "ix_run_definitions_live",
        "definition_type",
        "enabled",
        sqlite_where=text("deleted_at is null"),
    ),
    Index("ix_run_definitions_session", "session_id"),
    Index("ix_run_definitions_agent", "agent_name"),
    Index("ix_run_definitions_updated", "updated_at"),
)

agent_runs = Table(
    "agent_runs",
    metadata,
    Column("id", String, primary_key=True),
    Column("definition_id", String, nullable=True),
    Column("run_type", String, nullable=False),
    Column("status", String, nullable=False),
    Column("source_kind", String, nullable=True),
    Column("source_actor", Text, nullable=True),
    Column("parent_run_id", String, nullable=True),
    Column("agent_name", String, nullable=True),
    Column("agent_id", String, nullable=True),
    Column("agent_backend", String, nullable=True),
    Column("model", String, nullable=True),
    Column("reasoning_effort", String, nullable=True),
    Column("session_policy", String, nullable=True),
    Column("session_id", String, nullable=True),
    Column("legacy_session_key", Text, nullable=True),
    Column("post_to", String, nullable=True),
    Column("deliver_key", Text, nullable=True),
    Column("prompt", Text, nullable=True),
    Column("message", Text, nullable=True),
    Column("message_payload_json", Text, nullable=True),
    Column("result_text", Text, nullable=True),
    Column("result_payload_json", Text, nullable=True),
    Column("message_ids_json", Text, nullable=True),
    Column("delivery_id", String, nullable=True),
    Column("callback_session_id", String, nullable=True),
    Column("callback_status", String, nullable=True),
    Column("callback_error", Text, nullable=True),
    Column("callback_run_id", String, nullable=True),
    Column("callback_completed_at", String, nullable=True),
    Column("callback_terminal_turn_id", String, nullable=True),
    Column("cancel_requested", Integer, nullable=False, default=0),
    Column("cancel_requested_at", String, nullable=True),
    Column("pid", Integer, nullable=True),
    Column("exit_code", Integer, nullable=True),
    Column("error", Text, nullable=True),
    Column("stdout", Text, nullable=True),
    Column("stderr", Text, nullable=True),
    Column("created_at", String, nullable=False),
    Column("started_at", String, nullable=True),
    Column("completed_at", String, nullable=True),
    Column("updated_at", String, nullable=False),
    Column("metadata_json", Text, nullable=False),
    Index("ix_agent_runs_definition_created", "definition_id", "created_at"),
    Index("ix_agent_runs_status_created", "status", "created_at"),
    Index("ix_agent_runs_type_status_created", "run_type", "status", "created_at"),
    Index("ix_agent_runs_session_created", "session_id", "created_at"),
    Index(
        "uq_agent_runs_delivery",
        "delivery_id",
        unique=True,
        sqlite_where=text("delivery_id is not null"),
    ),
    Index("ix_agent_runs_agent_created", "agent_name", "created_at"),
    Index("ix_agent_runs_callback_status", "callback_status", "completed_at"),
    Index(
        "uq_agent_runs_callback_terminal_turn_session",
        "callback_terminal_turn_id",
        "session_id",
        unique=True,
        sqlite_where=text(
            "run_type = 'agent_run' and source_kind = 'callback' "
            "and callback_terminal_turn_id is not null and session_id is not null"
        ),
    ),
    # A callback child by the parent Run and actor it answers (migration 0066).
    Index(
        "ix_agent_runs_callback_parent",
        "parent_run_id",
        "source_actor",
        "created_at",
        "id",
        sqlite_where=text("run_type = 'agent_run' and source_kind = 'callback'"),
    ),
    # Leading-timestamp index for the run-graph window scan: updated_at bumps on
    # every state change, so it is the single column that scan filters on.
    Index("ix_agent_runs_updated", "updated_at"),
)

# Backwards-compatible Python aliases for legacy callers. The physical table
# names are the new domain names.
background_tasks = run_definitions
background_runs = agent_runs

show_pages = Table(
    "show_pages",
    metadata,
    Column("session_id", String, primary_key=True),
    Column("access_mode", String, nullable=False, server_default="private"),
    Column("access_revision", Integer, nullable=False, server_default="0"),
    Column("share_id", String, nullable=True),
    Column("offline_at", String, nullable=True),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    UniqueConstraint("share_id", name="uq_show_pages_share_id"),
    CheckConstraint(
        "access_mode in ('private', 'limited', 'public')",
        name="ck_show_pages_access_mode",
    ),
    CheckConstraint("access_revision >= 0", name="ck_show_pages_access_revision"),
    Index("ix_show_pages_share_id", "share_id"),
    Index("ix_show_pages_access_mode", "access_mode"),
)

# The Limited audience of a Show Page: one heterogeneous set of read-only
# grants, OR-ed at admission. ``email`` is instance-independent; ``group`` and
# ``organization`` only mean something relative to the organization that owns
# the page, so they carry that organization and cannot exist on a Personal
# instance. This table replaces the email-only ``show_page_authorized_emails``.
show_page_access_entries = Table(
    "show_page_access_entries",
    metadata,
    Column(
        "page_id",
        String,
        ForeignKey("show_pages.session_id", ondelete="CASCADE"),
        primary_key=True,
    ),
    Column("kind", String, primary_key=True),
    Column("value", String, primary_key=True),
    Column("organization_id", String, nullable=True),
    Column("created_at", String, nullable=False),
    CheckConstraint(
        "kind in ('email', 'group', 'organization')",
        name="ck_show_page_access_entries_kind",
    ),
    CheckConstraint(
        "length(value) between 1 and 320",
        name="ck_show_page_access_entries_value_length",
    ),
    CheckConstraint(
        "(kind = 'email' and organization_id is null) "
        "or (kind in ('group', 'organization') and organization_id is not null)",
        name="ck_show_page_access_entries_organization",
    ),
    # An organization entry IS the organization, so its value cannot name a
    # different one than the entry is scoped to.
    CheckConstraint(
        "kind <> 'organization' or value = organization_id",
        name="ck_show_page_access_entries_organization_value",
    ),
    # Admission resolves a visitor assertion to entries by (kind, value).
    Index("ix_show_page_access_entries_lookup", "kind", "value"),
    # "This organization may read" is one switch, not a list: at most one such
    # entry per page. The composite primary key cannot say that on its own,
    # because two organization rows would differ in ``value``.
    Index(
        "uq_show_page_access_entries_organization",
        "page_id",
        unique=True,
        sqlite_where=text("kind = 'organization'"),
    ),
)

show_session_events = Table(
    "show_session_events",
    metadata,
    Column("id", String, primary_key=True),
    Column("session_id", String, nullable=False),
    Column("event_type", String, nullable=False),
    Column("actor", String, nullable=False),
    Column("scope", String, nullable=False),
    Column("anchor_json", Text, nullable=False),
    Column("payload_json", Text, nullable=False),
    Column("transcript_text", Text, nullable=True),
    Column("message_id", String, ForeignKey("messages.id", ondelete="SET NULL"), nullable=True),
    Column("delivery_id", String, nullable=True),
    Column("created_at", String, nullable=False),
    Index("ix_show_session_events_session_created", "session_id", "created_at"),
    Index("ix_show_session_events_type_created", "event_type", "created_at"),
)

# Append-only agent trace log. Rows here are backend/process events, not chat
# messages. They can be inspected later without polluting the transcript,
# unread counters, or Inbox activity.
agent_events = Table(
    "agent_events",
    metadata,
    Column("id", String, primary_key=True),
    Column("scope_id", String, ForeignKey("scopes.id", ondelete="CASCADE"), nullable=True),
    Column("session_id", String, ForeignKey("agent_sessions.id", ondelete="SET NULL"), nullable=True),
    Column("turn_id", String, nullable=True),
    Column("run_id", String, nullable=True),
    Column("platform", String, nullable=False),
    Column("agent_name", String, nullable=True),
    Column("backend", String, nullable=True),
    Column("event_type", String, nullable=False),
    Column("visibility", String, nullable=False, server_default="trace"),
    Column("sequence", Integer, nullable=True),
    # C-5 model-context order (agent-core-contracts/transcript.md), shared with
    # ``messages.context_seq``: non-null exactly for rows in a Session's context.
    # Unrelated to the turn-local ``sequence``.
    Column("context_seq", Integer, nullable=True),
    Column("content_text", Text, nullable=True),
    Column("content_json", Text, nullable=False),
    Column("metadata_json", Text, nullable=False),
    Column("source", String, nullable=True),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    Index("ix_agent_events_session_created_id", "session_id", "created_at", "id"),
    Index("ix_agent_events_session_type_created_id", "session_id", "event_type", "created_at", "id"),
    Index("ix_agent_events_scope_created_id", "scope_id", "created_at", "id"),
    Index("ix_agent_events_turn_sequence_id", "turn_id", "sequence", "id"),
    Index(
        "uq_agent_events_session_context_seq",
        "session_id",
        "context_seq",
        unique=True,
        sqlite_where=text("context_seq is not null"),
    ),
    Index(
        "ix_agent_events_skill_created_id",
        "created_at",
        "id",
        sqlite_where=text(
            "visibility = 'trace' and event_type in ('skill.catalog_result', 'skill.load_result')"
        ),
    ),
    # Tool retention has its own allowlist, independent of Skill retention.
    # Keep this index aligned with storage/agent_events_retention.py.
    Index(
        "ix_agent_events_trace_retention",
        "created_at",
        sqlite_where=text(
            "event_type = 'tool_call' and visibility = 'trace' "
            "and datetime(created_at) is not null"
        ),
    ),
)

# A Vibey Session's latest memory, for other Sessions to recall: the mind, the memo, and
# the now of its latest checkpoint that holds any of them (context.md section 7). One row
# per Session, rewritten by primary key in the transaction that appends the
# ``context_compaction`` row; ``vibe data query`` reads it through its ``digests`` view.
session_digests = Table(
    "session_digests",
    metadata,
    Column("session_id", String, ForeignKey("agent_sessions.id", ondelete="CASCADE"), primary_key=True),
    Column("compaction_id", String, nullable=False),
    Column("updated_at", String, nullable=False),
    Column("mind", Text, nullable=True),
    Column("memo", Text, nullable=True),
    Column("now", Text, nullable=True),
)

skill_usage_daily = Table(
    "skill_usage_daily",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("day", Text, nullable=False),
    Column("scope_id", Text, ForeignKey("scopes.id", ondelete="CASCADE"), nullable=True),
    Column("session_id", Text, ForeignKey("agent_sessions.id", ondelete="CASCADE"), nullable=True),
    Column("skill_key", Text, nullable=False),
    Column("skill_name", Text, nullable=False),
    Column("source_kind", Text, nullable=False),
    Column("skill_revision", Text, nullable=False, server_default=""),
    Column("backend", Text, nullable=False, server_default=""),
    Column("model", Text, nullable=False, server_default=""),
    Column("trigger_kind", Text, nullable=False, server_default="unknown"),
    Column("platform", Text, nullable=False, server_default="unknown"),
    Column("avibe_version", Text, nullable=False),
    Column("catalog_offer_count", Integer, nullable=False, server_default=text("0")),
    Column("load_success_count", Integer, nullable=False, server_default=text("0")),
    Column("load_failure_count", Integer, nullable=False, server_default=text("0")),
    Column("load_duration_samples", Integer, nullable=False, server_default=text("0")),
    Column("load_duration_ms_sum", Integer, nullable=False, server_default=text("0")),
    Column("load_duration_ms_max", Integer, nullable=False, server_default=text("0")),
    Column("loaded_body_bytes_sum", Integer, nullable=False, server_default=text("0")),
    Column("first_observed_at", Text, nullable=False),
    Column("last_observed_at", Text, nullable=False),
    CheckConstraint(
        "day GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'",
        name="ck_skill_usage_day",
    ),
    CheckConstraint(
        "(scope_id is null or length(scope_id) > 0) and "
        "(session_id is null or length(session_id) > 0)",
        name="ck_skill_usage_ids",
    ),
    CheckConstraint(
        "length(skill_key) = 64 and skill_key not glob '*[^0-9a-f]*'",
        name="ck_skill_usage_key",
    ),
    CheckConstraint("length(skill_name) between 1 and 64", name="ck_skill_usage_name"),
    CheckConstraint(
        "source_kind in ('builtin', 'project', 'global', 'unresolved')",
        name="ck_skill_usage_source",
    ),
    CheckConstraint(
        "skill_revision = '' or (length(skill_revision) = 64 and skill_revision not glob '*[^0-9a-f]*')",
        name="ck_skill_usage_revision",
    ),
    CheckConstraint(
        "trigger_kind in ('human', 'task', 'watch', 'agent_run', 'callback', 'standalone', 'unknown')",
        name="ck_skill_usage_trigger",
    ),
    CheckConstraint(
        "typeof(catalog_offer_count) = 'integer' and catalog_offer_count >= 0 "
        "and typeof(load_success_count) = 'integer' and load_success_count >= 0 "
        "and typeof(load_failure_count) = 'integer' and load_failure_count >= 0 "
        "and catalog_offer_count + load_success_count + load_failure_count > 0 "
        "and (load_success_count = 0 or skill_revision <> '') "
        "and (catalog_offer_count = 0 or skill_revision = '')",
        name="ck_skill_usage_counts",
    ),
    CheckConstraint(
        "typeof(load_duration_samples) = 'integer' "
        "and load_duration_samples between 0 and load_success_count + load_failure_count "
        "and typeof(load_duration_ms_sum) = 'integer' and load_duration_ms_sum >= 0 "
        "and typeof(load_duration_ms_max) = 'integer' "
        "and load_duration_ms_max between 0 and load_duration_ms_sum "
        "and (load_duration_samples > 0 or load_duration_ms_sum = 0)",
        name="ck_skill_usage_duration",
    ),
    CheckConstraint(
        "typeof(loaded_body_bytes_sum) = 'integer' and loaded_body_bytes_sum >= 0 "
        "and (load_success_count > 0 or loaded_body_bytes_sum = 0)",
        name="ck_skill_usage_bytes",
    ),
    CheckConstraint(
        "length(first_observed_at) = 27 and length(last_observed_at) = 27 "
        "and substr(first_observed_at, 1, 10) = day "
        "and substr(last_observed_at, 1, 10) = day "
        "and first_observed_at <= last_observed_at",
        name="ck_skill_usage_observed",
    ),
    Index(
        "uq_skill_usage_daily_grain",
        "day", text("coalesce(scope_id, '')"), text("coalesce(session_id, '')"),
        "skill_key", "skill_revision", "backend", "model", "trigger_kind", "platform", "avibe_version",
        unique=True,
    ),
    Index("ix_skill_usage_daily_skill_day", "skill_key", "day"),
    Index("ix_skill_usage_daily_session_day", "session_id", "day"),
    Index("ix_skill_usage_daily_scope_day", "scope_id", "day"),
)

# Platform-agnostic chat message store. Every IM adapter (Slack, Discord,
# Telegram, Lark, WeChat, Avibe/Web UI) writes user+agent turns here so the
# workbench Inbox and per-session history can read from a single ORM
# surface instead of round-tripping the platform's own API. Native message
# identity is scoped to its conversation because some platforms reuse message
# ids across chats. ``read_at`` drives unread counts for the
# Inbox; legacy IM platforms ignore it.
messages = Table(
    "messages",
    metadata,
    Column("id", String, primary_key=True),
    Column("scope_id", String, ForeignKey("scopes.id", ondelete="CASCADE"), nullable=True),
    Column(
        "session_id",
        String,
        ForeignKey(
            "agent_sessions.id",
            ondelete="NO ACTION",
            deferrable=True,
            initially="DEFERRED",
            name="fk_messages_session_id_agent_sessions",
        ),
        nullable=True,
    ),
    Column("platform", String, nullable=False),
    Column("author", String, nullable=False),
    # Fine-grained accepted communication kind, distinct from coarse ``author``.
    # Operational queue/trace/draft concepts are forbidden from this ledger.
    Column("type", String, nullable=False, server_default="assistant"),
    Column("author_id", String, nullable=True),
    Column("author_name", Text, nullable=True),
    # Origin of the message (user / agent / harness), distinct from authorship.
    # Harness-triggered prompts use author/type/source='harness' so no automated
    # input can be represented as human-authored. ``author_name`` holds the
    # display name (username / agent_name / task|watch), ``author_id`` the precise
    # id.
    Column("source", String, nullable=True),
    Column("native_message_id", String, nullable=True),
    Column("parent_native_message_id", String, nullable=True),
    Column("content_text", Text, nullable=True),
    Column("content_json", Text, nullable=False),
    Column("metadata_json", Text, nullable=False),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    Column("delivered_at", String, nullable=True),
    Column("read_at", String, nullable=True),
    # C-5 model-context order, unique per Session across this table and
    # ``agent_events``. Null for display-only rows and for inputs the Avibe Agent
    # loop has not consumed.
    Column("context_seq", Integer, nullable=True),
    Index(
        "uq_messages_platform_scope_native",
        "platform",
        "scope_id",
        "native_message_id",
        unique=True,
        sqlite_where=text("scope_id is not null and native_message_id is not null"),
    ),
    Index(
        "uq_messages_platform_native_unscoped",
        "platform",
        "native_message_id",
        unique=True,
        sqlite_where=text("scope_id is null and native_message_id is not null"),
    ),
    Index(
        "uq_messages_session_context_seq",
        "session_id",
        "context_seq",
        unique=True,
        sqlite_where=text("context_seq is not null"),
    ),
    # T3 recovery only needs inputs that can still be consumed.  The hidden
    # Vibey retry ownership row stays durable with a null context_seq but is
    # deliberately outside this growing population.
    Index(
        "ix_messages_vibey_recovery_input",
        "context_seq",
        "session_id",
        "id",
        sqlite_where=text(VIBEY_RECOVERY_INPUT_PREDICATE),
    ),
    Index("ix_messages_session_created", "session_id", "created_at"),
    Index("ix_messages_session_created_id", "session_id", "created_at", "id"),
    Index("ix_messages_session_type_created_id", "session_id", "type", "created_at", "id"),
    Index("ix_messages_platform_session_created_id", "platform", "session_id", "created_at", "id"),
    Index("ix_messages_unread_session", "platform", "type", "author", "read_at", "session_id"),
    Index(
        "ix_messages_mark_read",
        "session_id",
        "author",
        "read_at",
        text("coalesce(delivered_at, created_at)"),
        "id",
    ),
    Index(
        "ix_messages_inbox_activity",
        "platform",
        "session_id",
        text("coalesce(delivered_at, created_at) desc"),
        text("id desc"),
        sqlite_where=text(build_partial_index_predicate("ix_messages_inbox_activity")),
    ),
    Index(
        "ix_messages_inbox_agent_reply",
        "platform",
        "session_id",
        text("coalesce(delivered_at, created_at) desc"),
        text("id desc"),
        sqlite_where=text(build_partial_index_predicate("ix_messages_inbox_agent_reply")),
    ),
    Index(
        "ix_messages_inbox_user_send",
        "platform",
        "session_id",
        text("coalesce(delivered_at, created_at) desc"),
        text("id desc"),
        sqlite_where=text(build_partial_index_predicate("ix_messages_inbox_user_send")),
    ),
    Index("ix_messages_scope_created", "scope_id", "created_at"),
    Index("ix_messages_scope_unread", "scope_id", "read_at"),
    Index("ix_messages_author_created", "author", "created_at"),
    CheckConstraint(
        "type not in ('queued', 'pending', 'draft', 'harness_dedupe', 'silent', 'tool_call')",
        name="ck_messages_communication_type",
    ),
)

#: The turn a Message row belongs to, as ``ix_messages_turn_id`` indexes it
#: (``20261012_0064``). A query must compile THIS text, through ``literal_column``,
#: for the planner to use the index: SQLite matches an index expression by text,
#: and a bound JSON path or a qualified column name is a different expression.
#: The ``CASE json_valid`` guard keeps a malformed ``metadata_json`` writable.
MESSAGE_TURN_ID_SQL = (
    "CASE WHEN (json_valid(metadata_json) = 1) "
    "THEN json_extract(metadata_json, '$.turn_id') END"
)

# Durable execution ownership for one logical Agent Turn per Session. The Turn
# references its initial Delivery even before native acceptance has materialized
# a Message. Accepted steer participants reference the Turn from their Delivery.
session_turns = Table(
    "session_turns",
    metadata,
    Column("id", String, primary_key=True),
    Column(
        "session_id",
        String,
        ForeignKey(
            "agent_sessions.id",
            ondelete="NO ACTION",
            deferrable=True,
            initially="DEFERRED",
        ),
        nullable=False,
    ),
    Column("initial_delivery_id", String, nullable=False),
    Column("state", String, nullable=False),
    Column("backend", String, nullable=False),
    Column("runtime_key", Text, nullable=True),
    Column("runtime_turn_id", Text, nullable=True),
    Column("native_turn_id", Text, nullable=True),
    Column("start_attempt_id", String, nullable=True),
    Column("start_receipt_outcome", String, nullable=True),
    Column("start_receipt_json", Text, nullable=False, server_default="{}"),
    Column("dispatch_text", Text, nullable=True),
    Column("dispatch_sha256", String, nullable=True),
    Column("terminal_outcome", String, nullable=True),
    Column("settled_by", String, nullable=True),
    Column("terminal_evidence_kind", String, nullable=True),
    Column("terminal_evidence_json", Text, nullable=False, server_default="{}"),
    # One coalesced control slot owns empty/content P0 against this exact Turn.
    Column("control_state", String, nullable=True),
    Column("control_mode", String, nullable=True),
    Column("control_attempt_id", String, nullable=True),
    Column("control_expected_native_turn_id", Text, nullable=True),
    Column("control_receipt_outcome", String, nullable=True),
    Column("control_receipt_json", Text, nullable=False, server_default="{}"),
    Column("control_successor_delivery_id", String, nullable=True),
    Column("control_successor_turn_id", String, nullable=True),
    Column("version", Integer, nullable=False, server_default="1"),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    Column("started_at", String, nullable=True),
    Column("terminal_at", String, nullable=True),
    ForeignKeyConstraint(
        ["initial_delivery_id"],
        ["message_deliveries.id"],
        ondelete="NO ACTION",
        deferrable=True,
        initially="DEFERRED",
        name="fk_session_turns_initial_delivery",
    ),
    ForeignKeyConstraint(
        ["control_successor_delivery_id"],
        ["message_deliveries.id"],
        ondelete="NO ACTION",
        deferrable=True,
        initially="DEFERRED",
        name="fk_session_turns_control_successor_delivery",
    ),
    ForeignKeyConstraint(
        ["control_successor_turn_id"],
        ["session_turns.id"],
        ondelete="NO ACTION",
        deferrable=True,
        initially="DEFERRED",
        name="fk_session_turns_control_successor_turn",
    ),
    CheckConstraint(
        "state in ('waiting', 'starting', 'active', 'terminal')",
        name="ck_session_turns_state",
    ),
    CheckConstraint(
        "terminal_outcome is null or terminal_outcome in "
        "('completed', 'failed', 'canceled', 'not_written')",
        name="ck_session_turns_terminal_outcome",
    ),
    CheckConstraint(
        "start_receipt_outcome is null or start_receipt_outcome in "
        "('accepted', 'not_written', 'unknown')",
        name="ck_session_turns_start_receipt_outcome",
    ),
    CheckConstraint(
        "(state = 'waiting' and start_attempt_id is null and dispatch_text is null "
        "and dispatch_sha256 is null and start_receipt_outcome is null) "
        "or (state = 'starting' and start_attempt_id is not null "
        "and dispatch_text is not null and dispatch_sha256 is not null "
        "and (start_receipt_outcome is null or start_receipt_outcome = 'unknown')) "
        "or (state = 'active' and start_attempt_id is not null "
        "and dispatch_text is not null and dispatch_sha256 is not null "
        "and start_receipt_outcome = 'accepted') "
        "or (state = 'terminal' and (((terminal_outcome <> 'not_written' "
        "and start_attempt_id is not null and dispatch_text is not null "
        "and dispatch_sha256 is not null and start_receipt_outcome = 'accepted') "
        "or (terminal_outcome = 'failed' and start_attempt_id is not null "
        "and dispatch_text is not null and dispatch_sha256 is not null "
        "and start_receipt_outcome = 'unknown')) "
        "or (terminal_outcome = 'not_written' and start_attempt_id is not null "
        "and dispatch_text is not null and dispatch_sha256 is not null "
        "and start_receipt_outcome = 'not_written') "
        "or (terminal_outcome = 'not_written' and start_attempt_id is null "
        "and dispatch_text is null and dispatch_sha256 is null)))",
        name="ck_session_turns_start_shape",
    ),
    CheckConstraint(
        "control_state is null or control_state in "
        "('pending', 'interrupting', 'waiting_terminal', 'reconciling', 'refused', 'settled')",
        name="ck_session_turns_control_state",
    ),
    CheckConstraint(
        "control_mode is null or control_mode in ('stop_only', 'replace')",
        name="ck_session_turns_control_mode",
    ),
    CheckConstraint(
        "(state = 'terminal' and terminal_outcome is not null and terminal_at is not null) "
        "or (state <> 'terminal' and terminal_outcome is null and terminal_at is null)",
        name="ck_session_turns_terminal_shape",
    ),
    CheckConstraint(
        "(control_mode = 'replace' and control_successor_delivery_id is not null "
        "and control_successor_turn_id is not null) "
        "or (control_mode = 'stop_only' and control_successor_delivery_id is null "
        "and control_successor_turn_id is null) "
        "or (control_mode is null and control_successor_delivery_id is null "
        "and control_successor_turn_id is null)",
        name="ck_session_turns_control_shape",
    ),
    Index("ix_session_turns_session_created", "session_id", "created_at", "id"),
    Index(
        "uq_session_turns_live_session",
        "session_id",
        unique=True,
        sqlite_where=text("state in ('starting', 'active')"),
    ),
    Index(
        "uq_session_turns_message_written_attempt",
        "initial_delivery_id",
        unique=True,
        sqlite_where=text("state <> 'terminal' or start_receipt_outcome = 'accepted'"),
    ),
    Index(
        "uq_session_turns_waiting_successor",
        "session_id",
        unique=True,
        sqlite_where=text("state = 'waiting'"),
    ),
    Index(
        "uq_session_turns_control_attempt",
        "control_attempt_id",
        unique=True,
        sqlite_where=text("control_attempt_id is not null"),
    ),
    Index(
        "uq_session_turns_start_attempt",
        "start_attempt_id",
        unique=True,
        sqlite_where=text("start_attempt_id is not null"),
    ),
)


# The Run copy of each Turn's last delivered reply, which a Turn that ends without a
# reply of its own settles with (docs/plans/reply-and-turn-end.md, rule 5). One row per
# Turn, written by the dispatcher by primary key when it delivers a reply and read at the
# Turn's end; a table of its own, so it is outside the Turn's compare-and-set by
# construction.
session_turn_replies = Table(
    "session_turn_replies",
    metadata,
    Column("turn_id", String, primary_key=True),
    Column("reply_text", Text, nullable=False),
    Column("updated_at", String, nullable=False),
    ForeignKeyConstraint(
        ["turn_id"],
        ["session_turns.id"],
        ondelete="CASCADE",
        deferrable=True,
        initially="DEFERRED",
        name="fk_session_turn_replies_turn",
    ),
)

# One durable operational owner per submitted content-bearing input. Before
# positive native acceptance the complete immutable Message candidate lives only
# here. Acceptance moves one Delivery snapshot, or one ordered merged batch,
# into ``messages`` and links every participating Delivery to that record.
message_deliveries = Table(
    "message_deliveries",
    metadata,
    Column("id", String, primary_key=True),
    Column(
        "session_id",
        String,
        ForeignKey(
            "agent_sessions.id",
            ondelete="NO ACTION",
            deferrable=True,
            initially="DEFERRED",
        ),
        nullable=False,
    ),
    Column("message_id", String, nullable=True),
    Column("priority", String, nullable=False),
    Column("state", String, nullable=False),
    Column("snapshot_json", Text, nullable=True),
    Column("snapshot_sha256", String, nullable=False),
    # Durable dispatch content is independent of Message display content.
    # Execution metadata is rendered on a request copy at the native write.
    Column("dispatch_text", Text, nullable=True),
    Column("dispatch_sha256", String, nullable=False),
    Column("dedupe_key", Text, nullable=True),
    Column("turn_id", String, nullable=True),
    Column("turn_role", String, nullable=True),
    Column("turn_position", Integer, nullable=True),
    Column("current_attempt_id", String, nullable=True),
    Column("current_attempt_kind", String, nullable=True),
    Column("current_target_turn_id", String, nullable=True),
    Column("current_expected_native_turn_id", Text, nullable=True),
    Column("current_receipt_outcome", String, nullable=True),
    Column("current_receipt_json", Text, nullable=False, server_default="{}"),
    Column("current_attempt_opened_at", String, nullable=True),
    Column(
        "delivery_history_json",
        Text,
        nullable=False,
        # Declared as an expression rather than a JSON literal on purpose: a literal
        # default containing ``:1`` is re-read as a bind parameter every time a table
        # rebuild reflects and recompiles it, which is how 20260811_0050 turned this
        # default into invalid JSON. json_object() has no colon to lose. See
        # 20260819_0057.
        server_default=text("(json_object('version', 1, 'events', json_array()))"),
    ),
    Column("version", Integer, nullable=False, server_default="1"),
    Column("submitted_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    Column("materialized_at", String, nullable=True),
    Column("retired_at", String, nullable=True),
    ForeignKeyConstraint(
        ["message_id"],
        ["messages.id"],
        ondelete="NO ACTION",
        deferrable=True,
        initially="DEFERRED",
        name="fk_message_deliveries_message",
    ),
    ForeignKeyConstraint(
        ["turn_id"],
        ["session_turns.id"],
        ondelete="NO ACTION",
        deferrable=True,
        initially="DEFERRED",
        name="fk_message_deliveries_turn",
    ),
    ForeignKeyConstraint(
        ["current_target_turn_id"],
        ["session_turns.id"],
        ondelete="NO ACTION",
        deferrable=True,
        initially="DEFERRED",
        name="fk_message_deliveries_current_target_turn",
    ),
    UniqueConstraint("dedupe_key", name="uq_message_deliveries_dedupe"),
    CheckConstraint("priority in ('p0', 'p1', 'p3')", name="ck_message_deliveries_priority"),
    CheckConstraint(
        f"state in ({_sql_string_set(DELIVERY_STATES)})",
        name="ck_message_deliveries_state",
    ),
    CheckConstraint(
        "current_attempt_kind is null or current_attempt_kind = 'steer'",
        name="ck_message_deliveries_current_attempt_kind",
    ),
    CheckConstraint(
        "json_valid(delivery_history_json) = 1 "
        "and json_extract(delivery_history_json, '$.version') = 1 "
        "and json_type(delivery_history_json, '$.events') = 'array'",
        name="ck_message_deliveries_history_json",
    ),
    CheckConstraint(
        "(state in ('steering', 'reconciling_steer') "
        "and current_attempt_id is not null and current_attempt_kind = 'steer' "
        "and current_target_turn_id is not null "
        "and current_expected_native_turn_id is not null) "
        "or (state = 'pending_steer' and current_attempt_id is not null "
        "and current_attempt_kind = 'steer' and current_target_turn_id is not null "
        "and current_expected_native_turn_id is null) "
        "or (state not in ('steering', 'reconciling_steer', 'pending_steer') "
        "and current_attempt_id is null "
        "and current_attempt_kind is null and current_target_turn_id is null "
        "and current_expected_native_turn_id is null)",
        name="ck_message_deliveries_current_attempt_shape",
    ),
    CheckConstraint(
        "(state = 'reconciling_steer' "
        "and current_receipt_outcome in ('accepted', 'unknown')) "
        "or (state <> 'reconciling_steer' "
        "and current_receipt_outcome is null)",
        name="ck_message_deliveries_current_receipt",
    ),
    CheckConstraint(
        "(state = 'accepted' and message_id is not null and turn_id is not null "
        "and turn_role in ('initial', 'steer') and turn_position is not null "
        "and materialized_at is not null and snapshot_json is null and dispatch_text is null "
        "and current_attempt_id is null and current_attempt_kind is null "
        "and current_target_turn_id is null and current_expected_native_turn_id is null "
        "and current_receipt_outcome is null and current_attempt_opened_at is null) "
        "or (state <> 'accepted' and message_id is null and materialized_at is null)",
        name="ck_message_deliveries_materialization",
    ),
    CheckConstraint(
        "(state in ('claimed', 'interrupt_waiting') and turn_id is not null "
        "and turn_role = 'initial' and turn_position is not null) "
        "or (state = 'accepted' and turn_id is not null and turn_role is not null "
        "and turn_position is not null) "
        "or (state not in ('claimed', 'interrupt_waiting', 'accepted') "
        "and turn_id is null and turn_role is null and turn_position is null)",
        name="ck_message_deliveries_turn_membership",
    ),
    Index(
        "ix_message_deliveries_session_order",
        "session_id",
        "submitted_at",
        "id",
        sqlite_where=text(
            "state in ('reserved', 'queued', 'claimed', 'pending_steer', 'steering', "
            "'reconciling_steer')"
        ),
    ),
    Index("ix_message_deliveries_session_state", "session_id", "state", "submitted_at", "id"),
    Index(
        "ix_message_deliveries_message",
        "message_id",
        sqlite_where=text("message_id is not null"),
    ),
    Index("ix_message_deliveries_turn", "turn_id", "turn_position"),
    Index(
        "uq_message_deliveries_turn_position",
        "turn_id",
        "turn_position",
        unique=True,
        sqlite_where=text("turn_id is not null"),
    ),
    Index("ix_message_deliveries_current_target_turn", "current_target_turn_id"),
    Index("ix_message_deliveries_current_attempt", "current_attempt_id"),
)

agent_runs.append_constraint(
    ForeignKeyConstraint(
        ["delivery_id"],
        ["message_deliveries.id"],
        ondelete="NO ACTION",
        deferrable=True,
        initially="DEFERRED",
        name="fk_agent_runs_delivery",
    )
)

# The Show event can exist before its input is accepted, so its durable anchor is
# the Delivery. The Message link is filled only on acceptance for history reads.
show_session_events.append_constraint(
    ForeignKeyConstraint(
        ["delivery_id"],
        ["message_deliveries.id"],
        ondelete="NO ACTION",
        deferrable=True,
        initially="DEFERRED",
        name="fk_show_session_events_delivery",
    )
)

# Opaque-token proxy for chat media. The workbench browser can't load
# ``file://`` and we deliberately neuter arbitrary remote images, so a local
# file referenced by an agent reply (or uploaded by the user) is registered
# here and served back over ``/api/media/<token>``. The URL carries only the
# opaque ``token`` — never a filesystem path, never a session — so it is stable
# within the referencing session and the browser can cache it. ``content_type`` /
# ``file_ext`` are stored so the response and the UI file card don't have to
# re-derive them; ``kind`` (image|file) selects inline-image vs download-card
# rendering; ``source`` distinguishes agent output from user uploads so one
# table serves both. ``size_bytes`` + ``mtime_ns`` are the content fingerprint:
# :func:`storage.media_service.register` reuses an existing token for the same
# session and (local_path, size_bytes, mtime_ns), while a different session or
# changed file mints a fresh token.
media_objects = Table(
    "media_objects",
    metadata,
    Column("token", String, primary_key=True),
    Column("scope_id", String, ForeignKey("scopes.id", ondelete="CASCADE"), nullable=True),
    Column("session_id", String, ForeignKey("agent_sessions.id", ondelete="SET NULL"), nullable=True),
    Column("message_id", String, ForeignKey("messages.id", ondelete="SET NULL"), nullable=True),
    Column("kind", String, nullable=False),
    Column("source", String, nullable=False),
    Column("local_path", Text, nullable=False),
    Column("file_name", Text, nullable=True),
    Column("content_type", String, nullable=True),
    Column("file_ext", String, nullable=True),
    Column("size_bytes", Integer, nullable=True),
    Column("mtime_ns", Integer, nullable=True),
    # Image pixel dimensions, read at registration when the file is a decodable
    # image (NULL for non-images / unknown). The UI uses them to reserve an
    # image's box before it loads so the transcript never shifts on scroll.
    Column("width_px", Integer, nullable=True),
    Column("height_px", Integer, nullable=True),
    Column("created_at", String, nullable=False),
    Column("expires_at", String, nullable=True),
    Column("revoked_at", String, nullable=True),
    Index("ix_media_objects_session", "session_id"),
    Index("ix_media_objects_scope_created", "scope_id", "created_at"),
    # Backs the fingerprint prefix of register()'s session-scoped dedup lookup.
    Index("ix_media_objects_dedup", "local_path", "size_bytes", "mtime_ns"),
)

# A legacy media token could be reused across multiple sessions before token
# dedup became session-scoped. Keep every trusted referencing session so those
# historical attachments remain readable without treating the opaque token as
# authorization for an arbitrary session.
media_object_references = Table(
    "media_object_references",
    metadata,
    Column("token", String, ForeignKey("media_objects.token", ondelete="CASCADE"), primary_key=True),
    Column(
        "session_id",
        String,
        ForeignKey("agent_sessions.id", ondelete="CASCADE"),
        primary_key=True,
    ),
    Column("created_at", String, nullable=False),
    Index("ix_media_object_references_session", "session_id"),
)

# Per-install browser Push API subscriptions for PWA Web Push. These are
# runtime/device endpoints, not user-authored config: one user may install the
# app on multiple devices, and endpoints can rotate or expire independently.
web_push_subscriptions = Table(
    "web_push_subscriptions",
    metadata,
    Column("id", String, primary_key=True),
    Column("user_key", String, nullable=False),
    Column("endpoint", Text, nullable=False),
    Column("p256dh", Text, nullable=False),
    Column("auth", Text, nullable=False),
    Column("device_id", String, nullable=True),
    Column("user_agent", Text, nullable=True),
    Column("device_label", Text, nullable=True),
    Column("enabled", Integer, nullable=False),
    Column("last_success_at", String, nullable=True),
    Column("last_failure_at", String, nullable=True),
    Column("provider_invalidated_at", String, nullable=True),
    Column("failure_count", Integer, nullable=False),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    UniqueConstraint("endpoint", name="uq_web_push_subscriptions_endpoint"),
    Index("ix_web_push_subscriptions_user_enabled", "user_key", "enabled"),
    Index("ix_web_push_subscriptions_user_device", "user_key", "device_id"),
)

# Resource ACLs are local enforcement state. The hosted control plane receives
# only safe metadata and desired revisions; it never stores local resource
# content or secret values.
resource_access_policies = Table(
    "resource_access_policies",
    metadata,
    Column("resource_kind", String, primary_key=True),
    Column("resource_id", String, primary_key=True),
    Column("organization_id", String, nullable=True),
    Column("owner_user_id", String, nullable=True),
    Column("owner_email", String, nullable=True),
    Column("access_level", String, nullable=False, server_default=text("'private'")),
    Column("created_by_user_id", String, nullable=True),
    Column("updated_by_user_id", String, nullable=True),
    Column("policy_revision", Integer, nullable=False, server_default=text("0")),
    Column("last_applied_control_plane_revision", Integer, nullable=True),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    CheckConstraint(
        "resource_kind in ('agent', 'vault_secret', 'skill', 'show_page')",
        name="ck_resource_access_policies_kind",
    ),
    CheckConstraint(
        "access_level in ('public', 'scope', 'private')",
        name="ck_resource_access_policies_access_level",
    ),
    Index(
        "ix_resource_access_policies_org_level",
        "organization_id",
        "access_level",
        "resource_kind",
    ),
    Index("ix_resource_access_policies_owner", "owner_user_id", "resource_kind"),
)

resource_access_groups = Table(
    "resource_access_groups",
    metadata,
    Column("resource_kind", String, primary_key=True),
    Column("resource_id", String, primary_key=True),
    Column("group_id", String, primary_key=True),
    Column("organization_id", String, nullable=False),
    Column("created_at", String, nullable=False),
    ForeignKeyConstraint(
        ["resource_kind", "resource_id"],
        ["resource_access_policies.resource_kind", "resource_access_policies.resource_id"],
        ondelete="CASCADE",
    ),
    Index("ix_resource_access_groups_group", "organization_id", "group_id", "resource_kind"),
)

# Vaults — secret management for agents.
# A named secret is referenced by a globally-unique env-style ``name``. Tags are
# the only grouping selector; skill association is stored as reserved
# ``skill:<name>`` tags. Values are envelope-encrypted at rest (storage/vault_crypto.py):
# standard tier under a machine key, protected tier under a password/passkey-derived key.
# ``ciphertext``/``nonce`` are base64 text and ``wrap_meta`` is a JSON blob of the
# wrapped DEK + scheme — there is deliberately no plaintext column. The
# ``vault_secrets`` table is denylisted in ``vibe data query``.
vault_secrets = Table(
    "vault_secrets",
    metadata,
    Column("id", String, primary_key=True),
    # Globally unique, case-preserving shell name ``^[A-Za-z_][A-Za-z0-9_]*$``.
    # The exact UNIQUE(name) keeps existing exact lookup semantics, while the
    # unique lower(name) expression index below atomically rejects case-only
    # duplicates so exact lookup stays unambiguous under concurrent creates.
    Column("name", String, nullable=False),
    Column("tags", Text, nullable=True),  # JSON array
    Column("kind", String, nullable=False, server_default="static"),  # static | keypair (P2)
    Column("protection", String, nullable=False, server_default="standard"),  # standard (P0) | protected (P1)
    Column("signer_kind", String, nullable=True),  # local | external | mpc:<provider> (P2, keypair only)
    Column("source", String, nullable=False, server_default="manual"),  # manual | imported:1password | op-reference
    # Envelope: AES-256-GCM ciphertext/nonce (base64 text); ``wrap_meta`` JSON holds the
    # wrapped DEK + scheme. All null for external/mpc/op-reference (no local key/value).
    Column("ciphertext", Text, nullable=True),
    Column("nonce", Text, nullable=True),
    Column("wrap_meta", Text, nullable=True),
    # JSON array of extra named field envelopes. The default field remains in the
    # legacy ciphertext/nonce/wrap_meta columns for released-version compatibility.
    Column("fields", Text, nullable=True),
    Column("public_meta", Text, nullable=True),  # JSON: desc / pubkey / address / op:// uri
    Column("policy", Text, nullable=True),  # JSON: allowed modes, allowed hosts, always_ask
    Column("last_used_at", String, nullable=True),
    Column("use_count", Integer, nullable=False, server_default="0"),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    UniqueConstraint("name", name="uq_vault_secrets_name"),
    Index("ix_vault_secrets_name_kind", "name", "kind"),
)
Index("uq_vault_secrets_name_folded", func.lower(vault_secrets.c.name), unique=True)

# One queue for everything that needs a human: P0 uses ``provision`` (dynamic ask via
# ``$<NAME>``); ``access``/``sign``/``proxy``/``keygen`` are P1+.
vault_requests = Table(
    "vault_requests",
    metadata,
    Column("id", String, primary_key=True),
    Column("request_type", String, nullable=False),  # provision | access | sign | proxy | keygen
    Column("secret_name", String, nullable=True),
    Column("requester", Text, nullable=True),  # JSON: session_id / agent / run
    Column("delivery", Text, nullable=True),  # JSON
    Column("status", String, nullable=False, server_default="pending"),
    Column("message_id", String, nullable=True),
    Column("created_at", String, nullable=False),
    Column("decided_at", String, nullable=True),
    Column("expires_at", String, nullable=True),
    # Auto-resume callback bookkeeping: set to "pending" when a request transitions to a
    # terminal state (approved/denied/fulfilled/failed/expired) so the daemon sweep enqueues
    # exactly one callback turn to the requesting session, then "sent"/"skipped". NULL = no
    # callback owed (e.g. rows created before this feature, or born-fulfilled).
    Column("callback_status", String, nullable=True),
    Index("ix_vault_requests_status_created", "status", "created_at"),
    Index("ix_vault_requests_callback_status", "callback_status", "decided_at"),
)
event.listen(
    vault_requests,
    "after_create",
    DDL(
        """
        create trigger if not exists trg_vault_requests_pending_provision_name_case_insert
        before insert on vault_requests
        when new.request_type = 'provision'
          and new.status = 'pending'
          and new.secret_name is not null
          and exists (
            select 1
            from vault_requests
            where request_type = 'provision'
              and status = 'pending'
              and secret_name is not null
              and lower(secret_name) = lower(new.secret_name)
              and secret_name <> new.secret_name
          )
        begin
          select raise(abort, 'vault pending provision name case conflict');
        end
        """
    ),
)
event.listen(
    vault_requests,
    "after_create",
    DDL(
        """
        create trigger if not exists trg_vault_requests_pending_provision_name_case_update
        before update of request_type, status, secret_name on vault_requests
        when new.request_type = 'provision'
          and new.status = 'pending'
          and new.secret_name is not null
          and exists (
            select 1
            from vault_requests
            where id <> new.id
              and request_type = 'provision'
              and status = 'pending'
              and secret_name is not null
              and lower(secret_name) = lower(new.secret_name)
              and secret_name <> new.secret_name
          )
        begin
          select raise(abort, 'vault pending provision name case conflict');
        end
        """
    ),
)

# Metadata + audit of active unlock grants. Key material is NEVER stored here;
# resident avault agent delivery material wires in later as process-local opaque
# state. The member set is frozen at grant time and keyed by the first-class grant id.
vault_grants = Table(
    "vault_grants",
    metadata,
    Column("id", String, primary_key=True),
    Column("member_snapshot", Text, nullable=False),  # JSON: frozen protected secret-name set
    Column("source_selector", Text, nullable=True),  # JSON: env/tag/skill source that produced the grant
    Column("request_id", String, nullable=True),
    Column("session_id", String, nullable=True),
    Column("purpose", String, nullable=False, server_default="run"),  # run | fetch | inject
    Column("status", String, nullable=False, server_default="active"),  # active | reserved | expired | revoked
    Column("one_shot", Integer, nullable=False, server_default=text("0")),
    Column("created_at", String, nullable=False),
    Column("expires_at", String, nullable=False),
    Column("revoked_at", String, nullable=True),
    Column("agent_ready", Integer, nullable=False, server_default=text("0")),
    Column("agent_ready_at", String, nullable=True),
    Index("ix_vault_grants_status_expires", "status", "expires_at"),
    Index("ix_vault_grants_request", "request_id"),
    Index("ix_vault_grants_session_purpose", "session_id", "purpose"),
)

vault_auth_factors = Table(
    "vault_auth_factors",
    metadata,
    Column("id", String, primary_key=True),
    Column("kind", String, nullable=False),
    Column("label", Text, nullable=True),
    Column("rp_id", String, nullable=False),
    Column("credential_id", Text, nullable=False),
    Column("public_key", Text, nullable=False),
    Column("alg", Integer, nullable=False),
    Column("sign_count", Integer, nullable=False, server_default=text("0")),
    Column("transports", Text, nullable=True),
    Column("created_at", String, nullable=False),
    Column("updated_at", String, nullable=False),
    Column("last_used_at", String, nullable=True),
    Column("disabled_at", String, nullable=True),
    UniqueConstraint("credential_id", name="uq_vault_auth_factors_credential_id"),
    Index("ix_vault_auth_factors_kind_rp", "kind", "rp_id", "disabled_at"),
)

vault_operation_challenges = Table(
    "vault_operation_challenges",
    metadata,
    Column("id", String, primary_key=True),
    Column("operation", String, nullable=False),
    Column("secret_name", String, nullable=True),
    Column("secret_id", String, nullable=True),
    Column("secret_updated_at", String, nullable=True),
    Column("challenge_hash", String, nullable=False),
    Column("rp_id", String, nullable=False),
    Column("origin", Text, nullable=False),
    Column("expires_at", String, nullable=False),
    Column("consumed_at", String, nullable=True),
    Column("factor_id", String, nullable=True),
    Column("created_at", String, nullable=False),
    Index("ix_vault_operation_challenges_lookup", "operation", "secret_name", "expires_at"),
    Index("ix_vault_operation_challenges_consumed", "consumed_at", "expires_at"),
)

# Append-only audit log. Secret VALUES never appear here — only names, requesters,
# and delivery summaries.
vault_audit = Table(
    "vault_audit",
    metadata,
    Column("id", String, primary_key=True),
    Column("ts", String, nullable=False),
    Column("event", String, nullable=False),  # created/updated/deleted/delivered/denied/granted/...
    Column("secret_name", String, nullable=True),
    Column("requester", Text, nullable=True),
    Column("delivery", Text, nullable=True),
    Column("request_id", String, nullable=True),
    Column("grant_id", String, nullable=True),
    Index("ix_vault_audit_ts", "ts"),
    Index("ix_vault_audit_secret_ts", "secret_name", "ts"),
)

imported_state_tables = [
    show_pages,
    background_runs,
    background_tasks,
    scope_settings,
    auth_codes,
    agent_sessions,
    runtime_records,
    scopes,
    messages,
    agent_events,
    show_session_events,
    web_push_subscriptions,
]