agentmux_srv/backend/storage/

migrations.rs

// Copyright 2025-2026, AgentMux Corp.
// SPDX-License-Identifier: Apache-2.0

//! SQL schema setup for Store, FileStore, and the saga log.
//!
//! `objects.db` uses a **flat schema**: `run_object_schema` defines the
//! final table set directly in one idempotent `CREATE TABLE IF NOT EXISTS`
//! batch. It replaced an 11-step incremental migration chain
//! (`run_forge_v1` … `run_forge_v11`) — see
//! `docs/specs/SPEC_SCHEMA_FLATTENING_2026_05_19.md`. The chain was pure
//! historical accretion: per-version data dirs mean every new version is
//! born with a fresh `objects.db` and ran the whole chain top-to-bottom
//! anyway, so the intermediate states were never reachable in production.
//!
//! `filestore.db` and `sagas.db` were already single-DDL stores; they keep
//! their existing schema functions and gain only the `user_version`
//! tripwire (`check_schema_compat` + `stamp_version`).

use rusqlite::Connection;
use tracing::warn;

use super::error::StoreError;

/// `user_version` stamped into `~/.agentmux/shared/store.db` after
/// `run_shared_store_schema`. Versioned independently from `objects.db`.
///   v1 — initial: identity accounts/bundles/bindings/links, memory
///         bundles, drone definitions, muxbus credentials
///   v2 — db_cron_jobs: persistent scheduled injection jobs
///   v3 — Phase 4a of SPEC_PRESET_TO_BUNDLE_REFACTOR_2026_07_02.md: rename
///        db_identity_accounts -> db_accounts, db_memory_bundles -> db_bundles
///        (SPEC_ARMORY_PHASE4_STORAGE_RENAME_COMPLETION_2026_07_12.md).
///   v4 — db_agent_credentials: per-agent M2M Cognito client_id/secret +
///        cached access token, one row per locally-registered agent_id.
///        Lets cloud_subscriber use a credential bound to a specific agent
///        instead of the single shared per-account MUXBUS_TOKEN for every
///        /reactive/* call.
///   v5 — db_cron_jobs.max_age_secs: optional hard expiry bound (seconds since
///        created_at) alongside the existing max_fires bound. NULL = no expiry
///        (existing rows unaffected). See
///        docs/specs/SPEC_AGENT_POLLING_AND_WAKEUP_HARDENING_2026_08_04.md Phase 0.
///   v6 — db_agent_native_memory: durable, location-consistent mirror of
///        each agent's native memory files, keyed by (agent_id, filename).
///        See docs/specs/SPEC_NATIVE_MEMORY_DURABLE_SYNC_2026_08_07.md.
///   v7 — db_bundles.instructions_by_provider: JSON object of
///        {provider_id: content} instruction variants, additive to the
///        existing flat `instructions` column. ABF v0.2 §2.2, see
///        docs/specs/SPEC_ABF_V0_2_PROVIDER_AWARE_COMPONENTS_AND_NATIVE_MEMORY_2026_08_10.md.
///   v8 — db_agent_native_memory_versions: append-only version history for
///        native memory content, one row per `agent:memory:write_file`
///        call (plus reverts and, later, out-of-band-write detection) —
///        the mirror in v6 only ever holds the current value, with no way
///        to see what a file contained before its last write. See
///        docs/specs/SPEC_MEMORY_VERSION_CONTROL_AND_ARMORY_AUDIT_2026_08_19.md.
///   v9 — db_bundles.is_system: AgentMux-controlled, highest-priority
///        Global Bundle tier — see OBJECT_SCHEMA_VERSION's v27 doc
///        comment (objects.db, above run_object_schema) for the full
///        design; this store just needs schema parity.
///   v10 — db_bundle_versions: append-only version history for Global
///        Memory entry content — see OBJECT_SCHEMA_VERSION's v35 doc
///        comment for the full rationale
///        (SPEC_AGENT_FACING_GLOBAL_MEMORY_API_2026_09_15.md); this store
///        just needs schema parity, same as v9 above.
///   v11 — db_cron_jobs.target_uid: the target agent's UID (`db_agents.id`)
///        beside the slug column, resolved once at create time and
///        dual-written; nothing reads it yet, a job still fires by
///        `target`. Identity M1b of
///        SPEC_AGENT_IDENTITY_CARRIED_NOT_DERIVED_2026_09_23.md §9.1 —
///        spec §5.4: non-interactive ingress never resolves a name at fire
///        time, so the UID has to be captured while a human or model is
///        present. Empty when the name did not resolve; never guessed.
///   v12 — db_cron_jobs.created_by_uid and db_bundle_versions.written_by_uid:
///        the actor's UID beside each actor-name column, taken from the
///        request's `Caller` (its `X-Agent-Token`), never from a body field.
///        Empty for an Unattributed request; never guessed. Dual-written,
///        not yet read. Identity M4c-1 of
///        SPEC_AGENT_IDENTITY_CARRIED_NOT_DERIVED_2026_09_23.md §6.5.9.
pub const SHARED_STORE_SCHEMA_VERSION: i64 = 12;

/// `user_version` value stamped into `objects.db` after `run_object_schema`.
/// The flat schema reset the counter to 1 (the pre-flatten chain never set
/// `user_version`, so legacy files read 0). Bumped per additive migration:
///   v1 — flat schema baseline
///   v2 — db_agent_definitions.updated_at
///   v3 — db_agent_definitions.user_hidden (Phase 2 hide templates,
///        SPEC_AGENT_PICKER_TWO_TIER_2026_05_24.md Q2 Decision Y)
///   v4 — db_agents consolidation table (Phase 3a: every definition/
///        instance write dual-writes here). Reads moved over
///        piecemeal afterwards — the instance side in Phase 3b (#3080),
///        every `agent_def_*` method in Phase 3d (#3092, v31) — and both
///        legacy tables were dropped in v32.
///   v5 — db_agents.last_block_id (Phase 3c; latest launch's block, so the
///        consolidated read can find the session snapshot without joining
///        db_agent_instances)
///   v6 — container_image / container_volumes / container_name on both
///        db_agent_definitions and db_agents (Phase 0 of
///        SPEC_CONTAINER_PANE_SUPPORT_2026_06_11.md; host agents default
///        to '' / '[]' / '')
///   v7 — db_muxbus_credentials: global singleton for MuxBus cloud
///        Cognito PKCE tokens (access, refresh, id) + expiry + user email
///   v8 — db_memory_bundles.is_global: global-tier flag for Armory
///        bundles injected into every agent's CLAUDE.md at launch
///   v9 — db_memory_bundles.sort_order: explicit ordering for the Armory
///        global bundles (controls CLAUDE.md injection order). Existing
///        rows default to 0; the Armory Global section assigns positions via reorder.
///   v10 — db_skills, db_mcp_servers, db_agent_skills_ref, db_agent_mcp_ref:
///        standalone MCP Server and Skill primitives with per-agent ref tables
///        (v1 composable model, SPEC_V1_MCP_SKILLS_PRIMITIVES_2026_06_30.md).
///   v11 — Phase 4a of SPEC_PRESET_TO_BUNDLE_REFACTOR_2026_07_02.md: rename
///        db_identity_accounts -> db_accounts, db_memory_bundles -> db_bundles
///        (SPEC_ARMORY_PHASE4_STORAGE_RENAME_COMPLETION_2026_07_12.md).
///   v12 — use_ambient_login on both db_agent_definitions and db_agents:
///        explicit per-agent opt-in to the CLI's global (ambient) login when
///        no oauth-class account resolves at spawn (layer 3 of
///        SPEC_ACCOUNT_DELETE_DEAUTH_LAYERS_2_4_2026_07_14.md §2.3). Defaults
///        to 0 (fail-by-default); the m0017 data migration grandfathers
///        pre-existing linkless agents to 1.
///   v13 — db_agent_credentials: per-agent M2M muxbus credential, mirroring
///        db_muxbus_credentials' presence in both schemas — id_store falls
///        back to this (per-channel) schema on installs that haven't yet
///        applied 0011_shared_store_backfill, so cloud_subscriber's calls
///        must not assume the shared-schema copy (v4 there) is the one in use.
///   v14 — db_agent_native_memory: durable mirror of each agent's native
///        memory files, keyed by (agent_id, filename). Same both-schemas
///        duplication as db_agent_credentials, for the same id_store
///        fallback reason. See
///        docs/specs/SPEC_NATIVE_MEMORY_DURABLE_SYNC_2026_08_07.md.
///   v15 — model_vendor_base_url on both db_agent_definitions and db_agents:
///        redirects a harness (CLI) at a non-default model vendor backend
///        (e.g. ANTHROPIC_BASE_URL for a claude-provider agent) — the data-
///        model side of formalizing harness vs. model-vendor as distinct
///        concepts. Defaults to '' (use the harness's default vendor).
///        Only settable via `agent.define`, validated against the resolved
///        provider's `ProviderConfig::base_url_env_var`.
///   v16 — db_bundles.instructions_by_provider: JSON object of
///        {provider_id: content} instruction variants, additive to the
///        existing flat `instructions` column (which keeps meaning
///        "default"). ABF v0.2 storage half of provider-scoped
///        instructions — see
///        docs/specs/SPEC_ABF_V0_2_PROVIDER_AWARE_COMPONENTS_AND_NATIVE_MEMORY_2026_08_10.md
///        §2.2. Defaults to '{}' for existing rows; delivering a selected
///        variant into a running agent is explicitly out of scope for this
///        version (per that spec's non-goals) — this column only makes the
///        data storable/exportable/importable.
///   v17 — auto_continue_enabled on both db_agent_definitions and db_agents:
///        per-agent opt-in letting a Warden Supervisor watcher agent
///        auto-continue this agent's session on turn-end (subject to a
///        server-side consecutive-nudge ceiling). Defaults to 0 (opt-in
///        required — fail-by-default, same posture as use_ambient_login).
///        See docs/analysis/ANALYSIS_WARDEN_AUTO_CONTROLLER_CONTINUATION_WATCHER_2026_08_12.md.
///   v18 — db_agent_jekt_keys: per-agent HMAC-SHA256 signing key for
///        host-tier jekt sender verification. One row per agent_id, minted
///        on first use and injected into that agent's own MCP server
///        process env (AGENTMUX_JEKT_KEY) at spawn — never into any other
///        agent's env, so a signature can only be produced by the agent it
///        claims to be from. See
///        docs/specs/SPEC_JEKT_TRUST_LAYER_COMPLETION_2026_08_13.md §2.2.
///   v19 — db_agent_definitions.memory_id: the agent's own dedicated ABF
///        bundle, set once at creation and never updated thereafter
///        (agent_def_update's SET clause deliberately omits it, same
///        immutable-after-creation treatment as is_seeded/slug/parent_id).
///        Distinct from db_agent_instances.memory_id (a specific launch's
///        bundle, can still diverge on purpose). Mirrored into db_agents as
///        a SEPARATE default_memory_id column, not db_agents.memory_id —
///        that column is instance-only by existing convention (see
///        db_agents' CREATE TABLE comment). Defaults to '' for existing
///        rows; the m0021 migration
///        backfills every unbound definition to a freshly-provisioned
///        bundle. See ARCHITECTURE_MANDATORY_ABF_RETHINK_2026_08_14.md §3.1.
///   v20 — db_agent_lan_keys: per-agent Ed25519 keypair for LAN-tier jekt
///        sender verification — mirrors v18, asymmetric instead of HMAC
///        (LAN is multi-party: a receiving peer must verify without being
///        able to forge, which a shared secret can't provide). public_key
///        is distributed to LAN peers on demand (not secret); private_key
///        is injected into that one agent's own MCP process env
///        (AGENTMUX_LAN_KEY) at spawn, same never-over-RPC guarantee as
///        v18. See docs/specs/SPEC_JEKT_LAN_TIER_SIGNING_2026_08_15.md §2.1.
///   v21 — db_lan_peer_pubkey_pins: trust-on-first-use pin of a REMOTE
///        agent_id's LAN public key, as first observed from mDNS-discovered
///        peers. reagentx P0 on the LAN signing PR: peer discovery itself
///        is unauthenticated (any device can broadcast an mDNS instance and
///        register an agent under an existing agent's name), so trusting
///        "whichever peer answers first" for a pubkey lookup lets an
///        attacker's self-minted key be accepted as authoritative for a
///        victim's agent_id. Pinning the first-seen key and rejecting any
///        later mismatch (rather than silently accepting the newest
///        answer) is the standard SSH-host-key mitigation for exactly this
///        "no PKI available" situation — closes the always-spoofable case,
///        narrows it to a race on the very first lookup ever performed for
///        that agent_id. See
///        docs/specs/SPEC_JEKT_LAN_TIER_SIGNING_2026_08_15.md §2.2.
///   v22 — db_background_tasks: durable record of a declared long-running
///        (`run_in_background: true`) task attached to a block, so its
///        liveness survives past `DockSnapshotCache`'s 1-hour eviction and
///        a session reconnect/reload — the ephemeral client-transcript-
///        derived signal chain had no durable source of truth at all. Per-
///        channel (block ids are only meaningful within their own
///        channel), not the shared store. Renumbered from an earlier v20
///        to v22 when merged alongside the LAN-tier jekt signing PR, which
///        independently claimed v20/v21 for an unrelated table pair — see
///        docs/status/STATUS_ATTACHED_TASK_AXIS_AND_DEV_LOOP_2026_08_15.md,
///        issue #2492.
///   v23 — db_bundle_skills_ref, db_bundle_mcp_ref: bundle-level references
///        to the standalone skill/MCP-server primitives, mirroring the v10
///        agent-level ref tables but keyed by bundle_id — composable model
///        v2, step 1 of docs/specs/SPEC_BUNDLE_AS_CONTAINER_V2_2026_08_17.md
///        (GH issue #2024 item 3). A bundle's existing mcp_servers/skills
///        JSON columns are untouched by this — still the .abf export/import
///        format — these ref tables are the new live-resolution path.
///        Deliberately NO FK to db_bundles (unlike the agent-level ref
///        tables' FK to db_agent_definitions) — see this table's own doc
///        comment at its CREATE TABLE for why.
///   v24 — db_agent_native_memory_versions: append-only version history for
///        native memory content, mirroring the v14 table's own dual-
///        schema duplication (id_store per-channel fallback). See
///        docs/specs/SPEC_MEMORY_VERSION_CONTROL_AND_ARMORY_AUDIT_2026_08_19.md.
///   v25 — db_agent_groups: saved, named target sets for the fleet-control
///        feature (broadcast/bulk-stop across many agent panes at once —
///        docs/specs/SPEC_MULTI_AGENT_FLEET_CONTROL_2026_08_20.md). A
///        group is just a durable list of block ids under a name — the
///        Ansible-inventory-group lesson from that spec's research: a
///        saved group, not raw ephemeral selection, is the abstraction
///        that scales as a fleet grows. Per-channel (block ids are only
///        meaningful within their own channel), not the shared store.
///        Renumbered from an earlier v24 — merged alongside the native-
///        memory-version-history PR, which independently claimed v24 for
///        an unrelated table (see that entry above for the same class of
///        collision this codebase has hit before, e.g. v20→v22).
///   v26 — db_agent_definitions.conversation_visibility (TEXT, default
///        'private') + db_conversation_trust_grants: `muxspect` Phase B/C's
///        per-agent disclosure policy for an incoming cross-tier
///        `transcript_request` jekt (LAN/WAN conversation visibility —
///        docs/specs/SPEC_MUXSPECT_CROSS_TIER_CONVERSATION_VISIBILITY_2026_08_21.md,
///        jekt tier rules confirmed in
///        docs/specs/SPEC_JEKT_TRANSCRIPT_REQUEST_TIER_RULES_2026_08_22.md).
///        One of "private" (default, auto-deny)/"trusted_peers" (auto-
///        approve an allow-listed requester)/"ask" (force human
///        escalation). Also dual-written to `db_agents` (mirrors
///        `auto_continue_enabled`'s treatment exactly — a simple per-agent
///        opt-in-style setting, unlike `memory_id`/`model_vendor_base_url`,
///        which use a differently-named column there). Deliberately NOT
///        added to `DefinitionRecordV1`'s cross-channel wire format —
///        channel-local only, same precedent as those two fields; a
///        cross-channel-reopened agent starts back at the safe "private"
///        default. `db_conversation_trust_grants` mirrors
///        `db_lan_peer_pubkey_pins`'s exact shape (own module,
///        get/set-style methods, case-insensitive agent_id lookups).
///   v27 — db_bundles.is_system: an AgentMux-controlled, highest-priority
///        Global Bundle tier (INTEGER, default 0). A system row is always
///        also is_global=1 (enforced in code, not a CHECK constraint —
///        see `bundle_upsert_system`). Writable only through the
///        dedicated `upsertsystemmemory`/`deletesystemmemory` RPCs and
///        `Store::bundle_upsert_system`/`_delete_system` — the
///        generic `bundle_upsert`/`_delete`/`_reorder` all refuse
///        to touch an is_system=1 row. Injected first in
///        `format_global_bundle_block`'s output, wrapped in explicit
///        override wording, ahead of every ordinary Global Bundle
///        section. See docs/specs/SPEC_GLOBAL_MEMORY_SYSTEM_TIER_2026_08_24.md.
///   v28 — db_agent_activity_summaries: one-shot, on-demand Haiku-generated
///        activity summary per `definition_id`, used as the AgentPicker's
///        "My Agents" conversation-preview fallback when a row has no
///        structured `output.state.json` snapshot to preview (legacy rows
///        predating snapshot persistence — see `has_snapshot` in
///        `listrecentsessions`, `agent_handlers/session.rs`). Generated
///        from the instance's raw `"output"` filestore file (present even
///        when the newer structured snapshot isn't) via the same shared
///        Ambient Model Call gateway `dispatch_name`/`term:ambient_summary`
///        already use (`invoke_haiku`,
///        `docs/specs/SPEC_AMBIENT_MODEL_CALLS_FRAMEWORK_2026_07_03.md`).
///        Generated lazily (first picker load that needs it, not on every
///        turn) and cached forever once non-empty — mirrors
///        `SubAgent.display_name`'s cache-once posture, not
///        `term:ambient_summary`'s per-turn refresh. Renumbered from an
///        earlier v27 — merged alongside the Global Bundle system-tier PR
///        (#2782), which independently claimed v27 for an unrelated
///        column (same collision class as this file's own v20→v22 and
///        v24→v25 history, see those entries above). See
///        docs/reports/REPORT_AGENT_PICKER_FIELD_ORDER_SORT_AND_DATA_GAPS_AUDIT_2026_08_24.md §5a.
///   v29 — db_agents.session_id / status / started_at / ended_at: the
///        agent's LATEST launch state, the four `db_agent_instances`
///        columns the consolidated row never got (Phase 3a deliberately
///        left them "on the block", but the recent-sessions picker, the
///        pane close/reopen continuity write-back and the status-filtered
///        instance list all kept reading them from the legacy table — the
///        reason it could not be dropped). One row per agent ⇒ one launch
///        state per agent; a continuation moves it to the new block/session.
///        Backfilled from the legacy rows by
///        `m0025_agents_launch_state_backfill` (latest instance per
///        projection key), kept current by the instance dual-write, and
///        the read flip in `SPEC_AGENT_ARCHITECTURE_2026_05_27.md` Phase
///        3b retires the legacy reads on top of it. No unique index on
///        `db_agents(slug)` is added: template-launch projections copy
///        their template's slug, so uniqueness stays a definition-level
///        rule enforced by the slug collision scan in `agent_def_insert`.
///   v30 — `db_agent_content` / `db_agent_skills` / `db_agent_history` /
///        `db_agent_identity_links` / `db_agent_skills_ref` /
///        `db_agent_mcp_ref`: `agent_id`'s foreign key now targets
///        `db_agents(id)` instead of `db_agent_definitions(id)`. Every
///        existing row already satisfies the new target (its `agent_id` is
///        a definition id, and every definition has a `db_agents` mirror
///        at the same id) — what changes is that a `db_agents` row with NO
///        definition counterpart (a template launch) can now hold these
///        directly too, closing the gap `listrecentsessions`' template
///        fallback (Phase 3b, #3080) exists for. SQLite has no in-place FK
///        redefinition, so an existing store needs its six tables rebuilt;
///        `m0028_agent_child_tables_repoint_fk` does that (rename,
///        recreate, copy, drop, `PRAGMA foreign_key_check` before commit).
///        A fresh install gets the new FK directly from the CREATE TABLE
///        statements below — nothing to rebuild there. Phase 3c
///        (`SPEC_AGENT_ARCHITECTURE_2026_05_27.md`).
///   v31 — No DDL change. Every `agent_def_*` write now lands on
///        `db_agents` only — `db_agent_definitions` is no longer kept in
///        sync (Phase 3d, #3092). Bumped anyway (codex P1 on #3092):
///        `check_schema_compat` is a downgrade guard as much as a schema
///        guard, and this release changes what a NEWER binary's writes
///        mean to an OLDER one just as much as a column would. Without the
///        bump, rolling back to the immediately preceding binary after
///        this version has run would silently accept a store where new
///        agents exist only in `db_agents` — that binary's `agent_def_get`/
///        `update`/`delete` still target `db_agent_definitions` and would
///        404 on them, or replay stale legacy values back into `db_agents`
///        on edit. Stamping 31 makes that downgrade refuse to open instead.
///   v32 — `db_agent_definitions` and `db_agent_instances` are dropped.
///        Nothing has read or written either table since v31 (agent-concept
///        consolidation's definition and instance flips, #3080/#3092); this
///        is the final phase (Phase 3e,
///        `SPEC_AGENT_ARCHITECTURE_2026_05_27.md`) closing out the tracking
///        spec. `db_agent_instances`' `CREATE TABLE`/indexes and
///        `db_agent_definitions`' `CREATE TABLE` are gone from this function
///        — leaving either would silently recreate the tables (empty) on
///        the very next `Store::open()` after the drop migration runs,
///        since migrations execute before this function does on every
///        boot. `db_agent_definitions`' own `ALTER TABLE ADD COLUMN`
///        entries, by contrast, are KEPT — gated on the table still
///        existing (see that block's own comment below) — because several
///        pending migrations (m0007, m0025, m0026, m0028) call
///        `Store::open()` internally, running this function, before m0029
///        (numbered last) ever drops the table in the SAME upgrade pass; a
///        store old enough to be missing one of those columns would fail
///        those migrations' own reads with "no such column" otherwise
///        (Codex P1 on #3096). An existing store's tables are actually
///        removed by `m0029_drop_legacy_agent_tables`; a fresh install
///        never has them to begin with, so the gate is a no-op for it.
///        `adopt_legacy_table_names`'s
///        `db_forge_agents` → `db_agent_definitions` rename entry is left
///        in place deliberately: an install old enough to still carry
///        `db_forge_agents` reaches it during an EARLIER migration's own
///        `Store::open()` call (every migration in this chain lays down
///        schema that way), so the rename still fires before
///        `m0029` runs later in the same upgrade pass and correctly drops
///        what the rename just produced — no special-casing needed, and no
///        data is stranded under the old name.
///   v33 — `db_agent_project_instructions`: what each agent was last observed
///        to read as project instructions from its working directory, keyed
///        `(agent_id, path)`. Phase 3 of
///        `SPEC_INSTRUCTION_AND_MEMORY_PORTABILITY_2026_09_09.md`.
///
///        Records a content hash and an observation time, never the content:
///        this is a tracking table, not a mirror. The distinction from
///        `db_agent_native_memory` above is deliberate — that one stores
///        content because it is a durable copy of files AgentMux may have to
///        serve when the originals are unreachable, whereas these files
///        belong to the repository, are always readable from it, and are
///        never written by AgentMux. Storing their content would create a
///        second copy of somebody else's file with no one to keep it honest.
///
///        Purely additive: nothing reads it at launch, and an agent with no
///        row simply has no prior observation to compare against.
///   v34 — `db_agent_skills_ref` / `db_agent_mcp_ref` / `db_bundle_skills_ref`
///        / `db_bundle_mcp_ref` drop their `skill_id`/`mcp_id` FK to this
///        store's LOCAL `db_skills`/`db_mcp_servers`. Phase 2 of
///        `SPEC_DURABLE_BINDINGS_2026_09_10.md` §5.4 (Codex P1, PR #3182):
///        once catalog writes redirect to `identity_store`, this channel's
///        local catalog copies stop gaining new rows, so binding a
///        post-redirect skill/server would otherwise hit a hard `FOREIGN KEY
///        constraint failed` (`PRAGMA foreign_keys=ON` in production, see
///        `Store::configure_and_migrate`). The `agent_id → db_agents` FK on
///        the agent-level pair is kept verbatim — only the catalog-pointing
///        FK is dropped. Same shape `db_bundle_skills_ref`/`db_bundle_mcp_ref`
///        already used for `db_bundles` (see those tables' own doc comment
///        below): existence moves to the application layer
///        (`managed_bind_agent`/`managed_bind_bundle`'s new `catalog: &Store`
///        check). SQLite has no `ALTER TABLE ... DROP CONSTRAINT`, so an
///        existing store's four tables are rebuilt by
///        `m0032_drop_catalog_fk_from_ref_tables`; a fresh install gets the
///        trimmed FK directly from the CREATE TABLE statements below.
///   v35 — db_bundle_versions: append-only version history for Global
///        Memory entry content (`name` + `instructions`), one row per
///        write through the new agent-facing Global Memory API
///        (`docs/specs/SPEC_AGENT_FACING_GLOBAL_MEMORY_API_2026_09_15.md`
///        Phase 0) — mirrors `db_agent_native_memory_versions`'s shape
///        exactly, keyed by a single `bundle_id` instead of that table's
///        composite `(agent_id, filename)`, since a Bundle's identity is
///        already a single id. Global Memory (`db_bundles`) previously had
///        no audit trail at all — only `updated_at`, no way to see what
///        changed, when, or by which agent, unlike native memory which has
///        had this since v24. Opened specifically because Global Memory is
///        about to gain a write path an agent (not just a human at the
///        Armory UI) can reach. `written_by` is a TRUSTED identity column
///        (an agent's real `AGENTMUX_AGENT_ID`, or `"armory-ui"`),
///        deliberately separate from the caller-describable `source`/
///        `source_detail` pair — an agent could otherwise claim
///        `source: "human"` with nothing to contradict it (codex P2, PR
///        #3237, caught before this table ever shipped).
///   v36 — db_agent_wan_keys: per-agent Ed25519 keypair for general
///        agent-to-agent WAN-tier jekt signing (issue #2586's second half).
///        Mirrors v20's shape; a SEPARATE keypair from db_agent_lan_keys by
///        design, not a reuse — LAN public keys are permanently TOFU-pinned
///        by peers (v21), which is why they cannot rotate
///        (SPEC_JEKT_HOST_KEY_TTL_ROTATION_2026_09_14.md §4), whereas WAN
///        keys are served from muxbus's authoritative ownership registry and
///        are meant to. One key cannot have both lifecycles. `key_version`
///        is reserved for the publish-sync and rotation phases (that spec's
///        §3.2/§3.6) and is not yet read by anything.
///   v37 — db_agent_tokens: per-agent local identity token, keyed by the
///        agent's UID (`db_agents.id`) — identity M1 of
///        `SPEC_AGENT_IDENTITY_CARRIED_NOT_DERIVED_2026_09_23.md` (§6, §9.1).
///        Minted on first spawn (`agent_token_ensure`), injected into that
///        one agent's process env as `AGENTMUX_AGENT_TOKEN`, revoked on
///        delete via `purge_agent_dependents`. Unlike v18's jekt key and the
///        registry entry files, which are keyed by display name and so are
///        shared by two same-named agents (spec §6.3), this is keyed by a
///        `PRIMARY KEY` that is a UUID for every non-template row. Not read
///        by anything until M4 — mint-and-carry only, revertible by ignoring
///        the field. See `storage/agent_tokens.rs`.
///   v38 — db_agent_key_tombstones: the lowercased names (slug, display
///        name, instance name) of every deleted agent — identity M4a of
///        `SPEC_AGENT_IDENTITY_CARRIED_NOT_DERIVED_2026_09_23.md` §6.5.4.
///        Signing keys are keyed by name, so a later agent that takes a
///        deleted agent's name would inherit its keys; M4d copies name-keyed
///        keys to UIDs and must never copy a tombstoned name's. Written from
///        M4a on, before any copy exists, so the record is complete when M4d
///        reads it. Written only — nothing reads it until M4d.
///   v39 — db_bundle_versions.written_by_uid: the writer's UID beside the
///        `written_by` name, taken from the request's `Caller` (its
///        `X-Agent-Token`), never from a body field; empty for an
///        Unattributed write, the Armory UI and the seeders. Dual-written,
///        not yet read. Not only parity with the shared store: when that
///        store is unavailable `id_store` falls back to this one and writes
///        here. Identity M4c-1 of
///        `SPEC_AGENT_IDENTITY_CARRIED_NOT_DERIVED_2026_09_23.md` §6.5.9.
///   v40 — db_jekt_held: a jekt to a known agent of this channel that no
///        delivery tier could take (the agent was not running anywhere), held
///        for up to 24 h and replayed when it registers, with the trust
///        verdicts it was accepted with as explicit columns.
///        `SPEC_DURABLE_JEKT_DELIVERY_2026_09_24.md` Phase 1.
///   v41 — db_background_tasks.owner_tool_use_id: the `tool_use_id` of the
///        Agent call whose subagent launched this background task (the
///        `parent_tool_use_id` on the stream line that issued the Bash call);
///        NULL for the agent's own tasks. Joins a task to its subagent, whose
///        `meta.json` records the same id. Phase 2 of
///        `SPEC_BACKGROUND_TASK_STRUCTURED_FEED_AND_SWARM_OWNERSHIP_2026_09_27.md`.
///   v42 — db_agents.last_runtime: the Runtime menu choices (permission mode,
///        model, effort) the user last made for this agent, as a JSON object,
///        '' when none. Lets Continue / Reattach / a fresh open start where
///        the user left off instead of on the definition's defaults. Written
///        only when the user picked something. Item 2 of
///        `SPEC_RUNTIME_MENU_REMAINING_GAPS_2026_10_01.md`.
///   v43 — db_viewer_devices: the devices paired with this channel's viewer
///        listener, each with the SHA-256 of its viewer token (never the
///        token), its name, its optional public key and when it was paired
///        and last seen; plus db_agents.hide_from_devices, an agent's opt-out
///        from the viewer routes. agentmux-mobile's
///        SPEC_AGENT_STATUS_AND_LIVE_PANE_FEED_2026_10_07 §13.2, §4.3. See
///        `storage/viewer_devices.rs`.
///   v44 — db_mcp_server_reach: per catalog MCP server, whether it reaches
///        every agent (`all_agents`) and whether container agents get it
///        too (`in_containers`). A table of its own beside `db_mcp_servers`,
///        so nothing that copies catalog rows changes. Both default 0: a
///        catalog server reaches only the agents it is bound to.
///        `SPEC_CONNECTORS_MCP_WHAT_AGENTS_RUN_2026_10_10.md` §2.1.
pub const OBJECT_SCHEMA_VERSION: i64 = 44;
/// `user_version` value stamped into `filestore.db`.
pub const FILESTORE_SCHEMA_VERSION: i64 = 1;
/// `user_version` value stamped into `sagas.db`.
pub const SAGA_LOG_SCHEMA_VERSION: i64 = 1;

/// Object type table names matching the `db_<otype>` convention.
const WSTORE_OTYPES: &[&str] = &[
    "client",
    "window",
    "workspace",
    "tab",
    "layout",
    "block",
    "temp",
];

/// Legacy `objects.db` table names retired by the de-forge rename, paired
/// with their replacement. `adopt_legacy_table_names` renames any of these
/// it finds — the single surviving piece of the old migration chain (it
/// also subsumes the v11 `db_memories` rename).
const LEGACY_TABLE_RENAMES: &[(&str, &str)] = &[
    ("db_forge_agents", "db_agent_definitions"),
    ("db_forge_content", "db_agent_content"),
    ("db_forge_skills", "db_agent_skills"),
    ("db_forge_history", "db_agent_history"),
    ("db_forge_agent_identities", "db_agent_identity_links"),
    ("db_memories", "db_memory_bundles"),
    // Phase 4 of SPEC_PRESET_TO_BUNDLE_REFACTOR_2026_07_02.md — the
    // "bundle" naming collision fix (SPEC_ARMORY_PHASE4_STORAGE_RENAME_COMPLETION_2026_07_12.md).
    // Order matters: this entry runs AFTER the `db_memories` rename above
    // in the same pass, so a database old enough to still have `db_memories`
    // chains straight through to `db_bundles` in one migration run instead
    // of stopping at the intermediate name.
    ("db_identity_accounts", "db_accounts"),
    ("db_memory_bundles", "db_bundles"),
];

/// Legacy index names that must be dropped after their table is renamed —
/// `ALTER TABLE … RENAME` keeps indexes attached but under their old names,
/// which would collide with the flat DDL's `CREATE INDEX`. The flat DDL
/// recreates each under the new name.
const LEGACY_INDEX_DROPS: &[&str] = &[
    "idx_forge_agents_slug",
    "idx_forge_history_agent_date",
    "idx_forge_agent_identities_account",
    "idx_memories_is_blank",
    // Phase 4 rename (see LEGACY_TABLE_RENAMES above) — objects.db names,
    // then the shared store.db names (`idx_ss_*` prefix, see
    // run_shared_store_schema).
    "idx_identity_accounts_provider",
    "idx_memory_bundles_is_blank",
    "idx_ss_identity_accounts_provider",
    "idx_ss_memory_bundles_is_blank",
];

/// Tables retained by the old chain only for a downgrade path the flatten
/// abandons. Dropped from any legacy DB by the adopt step; never created
/// by the flat schema. `db_workflow_*` data was already copied into
/// `db_drone_*` by the old v10 migration, so dropping loses nothing.
///
/// `db_identities` (the pre-v11 name), `db_identity_bundles`, and
/// `db_identity_bindings` were dropped outright rather than renamed
/// forward in Phase 4c of SPEC_PRESET_TO_BUNDLE_REFACTOR_2026_07_02.md —
/// `db_agent_identity_links`/`db_accounts` is the sole credential-
/// resolution path (`identity/resolver/inject.rs::resolve_bindings_for_instance`),
/// confirmed via the already-applied `m0013`/`m0014` backfill migrations
/// (see SPEC_ARMORY_PHASE4_STORAGE_RENAME_COMPLETION_2026_07_12.md §6/§8a).
const DEAD_TABLE_DROPS: &[&str] = &[
    "db_workflow_definitions",
    "db_workflow_runs",
    "db_v10_migrated_legacy_defs",
    "db_v10_migrated_legacy_runs",
    "db_identities",
    "db_identity_bundles",
    "db_identity_bindings",
];

/// Initialize (or re-validate) the full `objects.db` schema.
///
/// Idempotent — safe on every srv startup. Steps:
///
/// 1. `adopt_legacy_table_names` — renames any pre-flatten forge/bundle
///    tables found (protects dev databases created before the flatten;
///    see the spec §3/§7) and drops the dead workflow/sentinel tables.
/// 2. The flat `CREATE TABLE IF NOT EXISTS` batch — the canonical schema.
/// 3. Seeds the blank Bundle singleton row.
///
/// A database stuck at a pre-v11 intermediate schema cannot be fully
/// adopted (its tables predate later columns). The adopt step still
/// renames what it finds; the first query referencing a missing column
/// then fails loudly with `no such column` — a hard error, not silent
/// empty state, with the data preserved on disk. Per-version data dirs
/// make that case unreachable for released builds.
pub fn run_object_schema(conn: &Connection) -> Result<(), StoreError> {
    adopt_legacy_table_names(conn)?;

    // ---- Generic StoreObj object tables ----
    for otype in WSTORE_OTYPES {
        conn.execute_batch(&format!(
            "CREATE TABLE IF NOT EXISTS db_{otype} (
                oid     TEXT PRIMARY KEY,
                version INTEGER NOT NULL DEFAULT 1,
                data    TEXT NOT NULL
            );"
        ))?;
    }

    // ---- Agent + identity + memory + drone schema ----
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS db_agent_content (
            agent_id     TEXT NOT NULL,
            content_type TEXT NOT NULL,
            content      TEXT NOT NULL DEFAULT '',
            updated_at   INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (agent_id, content_type),
            -- v30: targets db_agents(id), not db_agent_definitions(id) — see
            -- OBJECT_SCHEMA_VERSION's v30 doc comment above.
            FOREIGN KEY (agent_id) REFERENCES db_agents(id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS db_agent_skills (
            id          TEXT PRIMARY KEY,
            agent_id    TEXT NOT NULL,
            name        TEXT NOT NULL,
            trigger     TEXT NOT NULL DEFAULT '',
            skill_type  TEXT NOT NULL DEFAULT 'prompt',
            description TEXT NOT NULL DEFAULT '',
            content     TEXT NOT NULL DEFAULT '',
            created_at  INTEGER NOT NULL DEFAULT 0,
            -- v30: see OBJECT_SCHEMA_VERSION's v30 doc comment above.
            FOREIGN KEY (agent_id) REFERENCES db_agents(id) ON DELETE CASCADE
        );

        CREATE TABLE IF NOT EXISTS db_agent_history (
            id           INTEGER PRIMARY KEY AUTOINCREMENT,
            agent_id     TEXT NOT NULL,
            session_date TEXT NOT NULL,
            entry        TEXT NOT NULL,
            timestamp    INTEGER NOT NULL DEFAULT 0,
            -- v30: see OBJECT_SCHEMA_VERSION's v30 doc comment above.
            FOREIGN KEY (agent_id) REFERENCES db_agents(id) ON DELETE CASCADE
        );
        CREATE INDEX IF NOT EXISTS idx_agent_history_agent_date
            ON db_agent_history(agent_id, session_date);

        CREATE TABLE IF NOT EXISTS db_accounts (
            id           TEXT PRIMARY KEY,
            name         TEXT NOT NULL,
            provider     TEXT NOT NULL,
            kind         TEXT NOT NULL,
            display_name TEXT NOT NULL DEFAULT '',
            secret_ref   TEXT NOT NULL,
            context      TEXT NOT NULL DEFAULT '{}',
            status       TEXT NOT NULL DEFAULT 'unknown',
            created_at   INTEGER NOT NULL DEFAULT 0,
            updated_at   INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_accounts_provider
            ON db_accounts(provider);

        CREATE TABLE IF NOT EXISTS db_agent_identity_links (
            agent_id   TEXT NOT NULL,
            account_id TEXT NOT NULL,
            provider   TEXT NOT NULL,
            PRIMARY KEY (agent_id, provider),
            -- v30: see OBJECT_SCHEMA_VERSION's v30 doc comment above.
            FOREIGN KEY (agent_id)   REFERENCES db_agents(id) ON DELETE CASCADE,
            FOREIGN KEY (account_id) REFERENCES db_accounts(id) ON DELETE CASCADE
        );
        CREATE INDEX IF NOT EXISTS idx_agent_identity_links_account
            ON db_agent_identity_links(account_id);

        CREATE TABLE IF NOT EXISTS db_bundles (
            id            TEXT PRIMARY KEY,
            name          TEXT NOT NULL UNIQUE,
            description   TEXT NOT NULL DEFAULT '',
            is_blank      INTEGER NOT NULL DEFAULT 0,
            is_global     INTEGER NOT NULL DEFAULT 0,
            provider      TEXT NOT NULL DEFAULT '',
            model         TEXT NOT NULL DEFAULT '',
            instructions  TEXT NOT NULL DEFAULT '',
            instructions_by_provider TEXT NOT NULL DEFAULT '{}',
            context_files TEXT NOT NULL DEFAULT '[]',
            mcp_servers   TEXT NOT NULL DEFAULT '[]',
            skills        TEXT NOT NULL DEFAULT '[]',
            sort_order    INTEGER NOT NULL DEFAULT 0,
            created_at    INTEGER NOT NULL DEFAULT 0,
            updated_at    INTEGER NOT NULL DEFAULT 0,
            is_system     INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_bundles_is_blank
            ON db_bundles(is_blank);

        -- Phase 3a consolidation: `db_agents` collapses the legacy
        -- definitions + instances tables into one table per
        -- `docs/specs/SPEC_AGENT_CONCEPT_CONSOLIDATION_2026_05_24.md`.
        -- WRITE-ONLY in 3a: every old-table mutation dual-writes here, but
        -- every read still hits the old tables. Phase 3b migrates readers,
        -- Phase 3c drops the old tables. Column names align with the live
        -- `db_agent_definitions` shape (provider, working_directory, …) —
        -- NOT the inline draft in the spec (provider_id, cmd, cmd_args, …) —
        -- because the existing storage layer never grew the cmd-template
        -- columns the spec sketched; carrying the names we actually have
        -- avoids inventing data we don't store. See the PR body for the
        -- field-by-field mapping.
        CREATE TABLE IF NOT EXISTS db_agents (
            id                   TEXT PRIMARY KEY,
            name                 TEXT NOT NULL,
            icon                 TEXT NOT NULL DEFAULT '',
            description          TEXT NOT NULL DEFAULT '',

            -- Template vs user agent
            is_template          INTEGER NOT NULL DEFAULT 0,
            parent_template_id   TEXT NOT NULL DEFAULT '',

            -- Provider/cmd config (was on definition; named to match the
            -- live `db_agent_definitions` columns).
            provider             TEXT NOT NULL,
            provider_flags       TEXT NOT NULL DEFAULT '',
            shell                TEXT NOT NULL DEFAULT '',
            environment          TEXT NOT NULL DEFAULT '',
            agent_type           TEXT NOT NULL DEFAULT 'standalone',
            agent_bus_id         TEXT NOT NULL DEFAULT '',
            accounts             TEXT NOT NULL DEFAULT '',
            auto_start           INTEGER NOT NULL DEFAULT 0,
            restart_on_crash     INTEGER NOT NULL DEFAULT 0,
            idle_timeout_minutes INTEGER NOT NULL DEFAULT 0,
            slug                 TEXT NOT NULL DEFAULT '',
            branch_label         TEXT NOT NULL DEFAULT '',

            -- Bindings (was on instance — only meaningful when is_template=0).
            -- For template rows these stay empty.
            identity_id          TEXT NOT NULL DEFAULT '',
            memory_id            TEXT NOT NULL DEFAULT '',
            working_directory    TEXT NOT NULL DEFAULT '',
            github_context       TEXT NOT NULL DEFAULT '',
            instance_name        TEXT NOT NULL DEFAULT '',

            -- Latest launch's block: pointer to the most-recent session's
            -- block so the consolidated read can locate the conversation
            -- snapshot without joining db_agent_instances. Was the only
            -- per-launch field db_agents retained until v29 added the
            -- four launch-state columns right below it.
            last_block_id        TEXT NOT NULL DEFAULT '',

            -- Latest launch's state (v29, consolidation Phase 3b/3c). The
            -- original 3a design left status/session_id/started_at/ended_at
            -- to the block; in practice the recent-sessions picker, the
            -- pane close/reopen continuity guarantee
            -- (SPEC_PANE_CLOSE_REOPEN_CONTINUITY_GUARANTEE_2026_07_27.md §4.1)
            -- and the status-filtered instance list all still read them from
            -- db_agent_instances, which is what kept that table alive. One
            -- row per agent means one launch state per agent: a continuation
            -- moves these to the new block/session rather than adding a row.
            session_id           TEXT NOT NULL DEFAULT '',
            status               TEXT NOT NULL DEFAULT '',
            started_at           INTEGER NOT NULL DEFAULT 0,
            ended_at             INTEGER NOT NULL DEFAULT 0,

            -- Provenance
            created_at           INTEGER NOT NULL DEFAULT 0,
            updated_at           INTEGER NOT NULL DEFAULT 0,
            is_seeded            INTEGER NOT NULL DEFAULT 0,
            user_hidden          INTEGER NOT NULL DEFAULT 0,

            -- Container support (Schema v6 / Phase 0).
            -- Empty for host agents; populated by ContainerManager.
            container_image      TEXT NOT NULL DEFAULT '',
            container_volumes    TEXT NOT NULL DEFAULT '[]',
            container_name       TEXT NOT NULL DEFAULT '',

            -- Explicit per-agent opt-in to the CLI's global (ambient) login
            -- when no oauth-class account resolves at spawn (schema v12,
            -- SPEC_ACCOUNT_DELETE_DEAUTH_LAYERS_2_4_2026_07_14.md §2.3).
            use_ambient_login    INTEGER NOT NULL DEFAULT 0,

            -- Redirects this agent's harness at a non-default model vendor
            -- backend (schema v15) — see db_agent_definitions' column doc.
            model_vendor_base_url TEXT NOT NULL DEFAULT '',

            -- Warden Supervisor auto-continue opt-in (schema v17) — see
            -- db_agent_definitions' column doc.
            auto_continue_enabled INTEGER NOT NULL DEFAULT 0,

            -- The DEFINITION's own dedicated ABF bundle (schema v19) —
            -- mirrors db_agent_definitions.memory_id. Deliberately a
            -- DIFFERENT column from memory_id above: that one is
            -- instance-scoped (only meaningful when is_template=0,
            -- written exclusively by the instance dual-write helpers) and
            -- this migration doesn't touch its semantics. default_memory_id
            -- is what agent_def_list's db_agents-backed read actually
            -- surfaces as AgentDefinition.memory_id (aliased in the SELECT
            -- below) -- readers that already consult db_agents for listing
            -- need this to see the definition-level binding at all.
            default_memory_id    TEXT NOT NULL DEFAULT ''
        );
        CREATE INDEX IF NOT EXISTS idx_agents_is_template
            ON db_agents(is_template);
        CREATE INDEX IF NOT EXISTS idx_agents_parent_template_id
            ON db_agents(parent_template_id);
        CREATE INDEX IF NOT EXISTS idx_agents_is_seeded
            ON db_agents(is_seeded);

        CREATE TABLE IF NOT EXISTS db_drone_definitions (
            id          TEXT PRIMARY KEY,
            name        TEXT NOT NULL,
            description TEXT NOT NULL DEFAULT '',
            graph       TEXT NOT NULL DEFAULT '{\"nodes\":[],\"edges\":[]}',
            viewport    TEXT NOT NULL DEFAULT '{\"x\":0,\"y\":0,\"zoom\":1}',
            created_at  INTEGER NOT NULL DEFAULT 0,
            updated_at  INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_drone_definitions_updated
            ON db_drone_definitions(updated_at DESC);

        CREATE TABLE IF NOT EXISTS db_drone_runs (
            id           TEXT PRIMARY KEY,
            drone_id     TEXT NOT NULL,
            status       TEXT NOT NULL DEFAULT 'running',
            started_at   INTEGER NOT NULL DEFAULT 0,
            ended_at     INTEGER NOT NULL DEFAULT 0,
            block_states TEXT NOT NULL DEFAULT '{}',
            output       TEXT NOT NULL DEFAULT '',
            error        TEXT NOT NULL DEFAULT '',
            FOREIGN KEY (drone_id) REFERENCES db_drone_definitions(id) ON DELETE CASCADE
        );
        CREATE INDEX IF NOT EXISTS idx_drone_runs_drone_started
            ON db_drone_runs(drone_id, started_at DESC);
        CREATE INDEX IF NOT EXISTS idx_drone_runs_status
            ON db_drone_runs(status);

        -- v10: Standalone skill and MCP server primitives (composable model).
        CREATE TABLE IF NOT EXISTS db_skills (
            id          TEXT PRIMARY KEY,
            name        TEXT NOT NULL,
            trigger     TEXT NOT NULL DEFAULT '',
            skill_type  TEXT NOT NULL DEFAULT 'prompt',
            description TEXT NOT NULL DEFAULT '',
            content     TEXT NOT NULL DEFAULT '',
            is_global   INTEGER NOT NULL DEFAULT 0,
            created_at  INTEGER NOT NULL DEFAULT 0,
            updated_at  INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_skills_is_global ON db_skills(is_global);

        CREATE TABLE IF NOT EXISTS db_mcp_servers (
            id          TEXT PRIMARY KEY,
            name        TEXT NOT NULL,
            transport   TEXT NOT NULL DEFAULT 'stdio',
            config      TEXT NOT NULL DEFAULT '{}',
            is_global   INTEGER NOT NULL DEFAULT 0,
            created_at  INTEGER NOT NULL DEFAULT 0,
            updated_at  INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_mcp_servers_is_global ON db_mcp_servers(is_global);
        -- v44: who a catalog server reaches beyond its
        -- binds — see OBJECT_SCHEMA_VERSION's v44 doc comment.
        CREATE TABLE IF NOT EXISTS db_mcp_server_reach (
            mcp_id        TEXT PRIMARY KEY,
            all_agents    INTEGER NOT NULL DEFAULT 0,
            in_containers INTEGER NOT NULL DEFAULT 0,
            updated_at    INTEGER NOT NULL DEFAULT 0
        );

        -- v34: skill_id's FK to this store's LOCAL db_skills is gone — see
        -- OBJECT_SCHEMA_VERSION's v34 doc comment above. agent_id's FK to
        -- db_agents (v30) is unchanged.
        CREATE TABLE IF NOT EXISTS db_agent_skills_ref (
            agent_id TEXT NOT NULL,
            skill_id TEXT NOT NULL,
            PRIMARY KEY (agent_id, skill_id),
            FOREIGN KEY (agent_id) REFERENCES db_agents(id) ON DELETE CASCADE
        );

        -- v34: mcp_id's FK to this store's LOCAL db_mcp_servers is gone —
        -- see OBJECT_SCHEMA_VERSION's v34 doc comment above. agent_id's FK
        -- to db_agents (v30) is unchanged.
        CREATE TABLE IF NOT EXISTS db_agent_mcp_ref (
            agent_id TEXT NOT NULL,
            mcp_id   TEXT NOT NULL,
            PRIMARY KEY (agent_id, mcp_id),
            FOREIGN KEY (agent_id) REFERENCES db_agents(id) ON DELETE CASCADE
        );

        -- v23: Bundle-level MCP/skill references (composable model v2,
        -- docs/specs/SPEC_BUNDLE_AS_CONTAINER_V2_2026_08_17.md) — same shape
        -- as the agent-level ref tables above, keyed by bundle_id instead of
        -- agent_id. A bundle's existing `mcp_servers`/`skills` JSON columns
        -- stay untouched (still consumed by .abf export/import); these ref
        -- tables are the new live-resolution path, wired into launch-time
        -- config generation alongside the agent-level refs.
        --
        -- reagentx P0 review on PR #2639: NO foreign key to db_bundles here,
        -- unlike the agent-level ref tables' FK to db_agents (v30: was
        -- db_agent_definitions — see OBJECT_SCHEMA_VERSION's v30 doc comment).
        -- Bundles are authoritatively written through `id_store` (the
        -- shared store in a normal production install — see
        -- `bundle.rs::register_bundle_upsert`), not `mstore`/objects.db,
        -- where this table lives (it must live here to FK to
        -- db_mcp_servers/db_skills, which used to be mstore-local too (now
        -- authoritatively identity_store as of Phase 2 — see
        -- OBJECT_SCHEMA_VERSION's v34 doc comment). mstore's own copy of
        -- db_bundles is a schema-compatible but essentially always-
        -- empty local mirror in that case — an FK against it would make
        -- every real bind fail. Same no-FK-to-a-table-living-in-the-wrong-
        -- store reasoning as `db_agent_identity_links.account_id`'s
        -- deliberate lack of an account FK (see that table's own doc
        -- comment). Existence is checked at the application layer instead
        -- — see `Store::bundle_skill_bind`'s `id_store` parameter.
        --
        -- v34: skill_id's FK to db_skills is ALSO gone now (it was
        -- mstore-local before v34; see OBJECT_SCHEMA_VERSION's v34 doc
        -- comment) — this table has never had a bundle_id FK, so it is now
        -- FK-free entirely, existence checked purely at the application
        -- layer for both columns.
        CREATE TABLE IF NOT EXISTS db_bundle_skills_ref (
            bundle_id TEXT NOT NULL,
            skill_id  TEXT NOT NULL,
            PRIMARY KEY (bundle_id, skill_id)
        );

        -- v34: mcp_id's FK to db_mcp_servers is gone — see the
        -- db_bundle_skills_ref comment above; same reasoning, MCP side.
        -- No longer written or read: a bundle carries no MCP servers, and
        -- m0036_drop_bundle_mcp emptied it. Still created, because an older
        -- build sharing the channel expects it.
        CREATE TABLE IF NOT EXISTS db_bundle_mcp_ref (
            bundle_id TEXT NOT NULL,
            mcp_id    TEXT NOT NULL,
            PRIMARY KEY (bundle_id, mcp_id)
        );

        -- v7: MuxBus cloud connectivity — global singleton PKCE token store.
        CREATE TABLE IF NOT EXISTS db_muxbus_credentials (
            id             TEXT PRIMARY KEY DEFAULT 'global',
            cognito_domain TEXT NOT NULL DEFAULT '',
            client_id      TEXT NOT NULL DEFAULT '',
            access_token   TEXT NOT NULL DEFAULT '',
            refresh_token  TEXT NOT NULL DEFAULT '',
            id_token       TEXT NOT NULL DEFAULT '',
            expires_at     INTEGER NOT NULL DEFAULT 0,
            user_email     TEXT NOT NULL DEFAULT '',
            user_sub       TEXT NOT NULL DEFAULT ''
        );

        -- v13: per-agent M2M muxbus credential — see db_agent_credentials in
        -- run_shared_store_schema's doc comment for the full rationale.
        CREATE TABLE IF NOT EXISTS db_agent_credentials (
            agent_id       TEXT PRIMARY KEY,
            client_id      TEXT NOT NULL DEFAULT '',
            client_secret  TEXT NOT NULL DEFAULT '',
            token_endpoint TEXT NOT NULL DEFAULT '',
            access_token   TEXT NOT NULL DEFAULT '',
            expires_at     INTEGER NOT NULL DEFAULT 0,
            created_at     INTEGER NOT NULL DEFAULT 0
        );

        -- v14: per-agent native-memory durable mirror — see
        -- db_agent_native_memory in run_shared_store_schema's doc comment for
        -- the full rationale (SPEC_NATIVE_MEMORY_DURABLE_SYNC_2026_08_07.md).
        -- No FK to db_agent_definitions: this table is duplicated into the
        -- shared store below (same pattern as db_agent_credentials, for
        -- id_store's per-channel fallback before 0011_shared_store_backfill),
        -- and SQLite can't enforce a FK across separate database files.
        -- Orphan rows after an agent's deleted are inert: every RPC handler
        -- that would read this table first 404s on agent_def_get, so a
        -- dangling mirror row is never reached, let alone surfaced.
        CREATE TABLE IF NOT EXISTS db_agent_native_memory (
            agent_id           TEXT NOT NULL,
            filename           TEXT NOT NULL,
            content            TEXT NOT NULL,
            metadata_type      TEXT NOT NULL DEFAULT '',
            size_bytes         INTEGER NOT NULL DEFAULT 0,
            updated_at         INTEGER NOT NULL DEFAULT 0,
            last_seen_path     TEXT NOT NULL DEFAULT '',
            last_seen_mtime_ms INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (agent_id, filename)
        );

        -- v33: what each agent was last observed to READ as project
        -- instructions. Hash and timestamp only, never content — these files
        -- belong to the repository and are always readable from it, unlike
        -- db_agent_native_memory above, which mirrors content precisely
        -- because the originals can become unreachable. See
        -- SPEC_INSTRUCTION_AND_MEMORY_PORTABILITY_2026_09_09.md Phase 3.
        CREATE TABLE IF NOT EXISTS db_agent_project_instructions (
            agent_id      TEXT NOT NULL,
            path          TEXT NOT NULL,
            content_hash  TEXT NOT NULL DEFAULT '',
            size_bytes    INTEGER NOT NULL DEFAULT 0,
            owner         TEXT NOT NULL DEFAULT 'foreign',
            existed       INTEGER NOT NULL DEFAULT 0,
            observed_at   INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (agent_id, path)
        );

        -- v24: append-only version history for native memory content — see
        -- db_agent_native_memory_versions in run_shared_store_schema's doc
        -- comment for the full rationale
        -- (SPEC_MEMORY_VERSION_CONTROL_AND_ARMORY_AUDIT_2026_08_19.md).
        -- Duplicated here for the same id_store-per-channel-fallback reason
        -- as db_agent_native_memory itself, immediately above.
        CREATE TABLE IF NOT EXISTS db_agent_native_memory_versions (
            id                 TEXT PRIMARY KEY,
            agent_id           TEXT NOT NULL,
            filename           TEXT NOT NULL,
            content            TEXT NOT NULL,
            content_hash       TEXT NOT NULL,
            parent_version_id  TEXT,
            source             TEXT NOT NULL DEFAULT 'agent_inferred',
            source_detail      TEXT NOT NULL DEFAULT '{}',
            session_id         TEXT NOT NULL DEFAULT '',
            created_at         INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_native_memory_versions_lookup
            ON db_agent_native_memory_versions(agent_id, filename, created_at);

        -- v35: append-only version history for Global Memory entry content
        -- — see OBJECT_SCHEMA_VERSION's own doc comment above for the full
        -- rationale (SPEC_AGENT_FACING_GLOBAL_MEMORY_API_2026_09_15.md).
        -- Single `bundle_id` key, not a composite one — a Bundle's identity
        -- is already a single id, unlike native memory's (agent_id, filename).
        CREATE TABLE IF NOT EXISTS db_bundle_versions (
            id                 TEXT PRIMARY KEY,
            bundle_id          TEXT NOT NULL,
            name               TEXT NOT NULL,
            instructions       TEXT NOT NULL,
            content_hash       TEXT NOT NULL,
            parent_version_id  TEXT,
            source             TEXT NOT NULL DEFAULT 'agent_inferred',
            source_detail      TEXT NOT NULL DEFAULT '{}',
            written_by         TEXT NOT NULL DEFAULT '',
            created_at         INTEGER NOT NULL DEFAULT 0,
            -- v39 (identity M4c-1): the writer's UID from the request's
            -- Caller, beside the written_by name. Empty when unattributed.
            written_by_uid     TEXT NOT NULL DEFAULT ''
        );
        CREATE INDEX IF NOT EXISTS idx_bundle_versions_lookup
            ON db_bundle_versions(bundle_id, created_at);

        -- v18: per-agent HMAC-SHA256 signing key for host-tier jekt sender
        -- verification (SPEC_JEKT_TRUST_LAYER_COMPLETION_2026_08_13.md §2.2).
        -- hmac_key is base64-encoded, 32 random bytes, minted on first use
        -- (agent_jekt_key_ensure). As of
        -- SPEC_JEKT_HOST_KEY_TTL_ROTATION_2026_09_14.md, a key past
        -- JEKT_KEY_TTL_SECS (24h) old is rotated the next time
        -- agent_jekt_key_ensure runs for it (that agent's next spawn) — not
        -- invalidated in place, so no expires_at column is needed; TTL is
        -- computed from created_at at read time. Local to this instance's
        -- data dir only — not synced anywhere, not the same secret as any
        -- Armory/GitHub credential.
        CREATE TABLE IF NOT EXISTS db_agent_jekt_keys (
            agent_id   TEXT PRIMARY KEY,
            hmac_key   TEXT NOT NULL,
            created_at INTEGER NOT NULL DEFAULT 0
        );

        -- v20: per-agent Ed25519 keypair for LAN-tier jekt sender
        -- verification (SPEC_JEKT_LAN_TIER_SIGNING_2026_08_15.md §2.1).
        -- public_key is not secret (distributed to LAN peers on demand);
        -- private_key is base64, 32-byte seed, minted on first use
        -- (agent_lan_key_ensure) and never rotated automatically — and,
        -- unlike db_agent_jekt_keys, deliberately NOT given a TTL by
        -- SPEC_JEKT_HOST_KEY_TTL_ROTATION_2026_09_14.md §4: rotating a LAN
        -- key would desync it from any peer's permanent
        -- db_lan_peer_pubkey_pins trust-on-first-use pin of the OLD key,
        -- turning every future signature into an unconditional forced-
        -- sensitive 'mismatch' for that peer with no re-pin mechanism to
        -- recover. Same local-to-this-instance-only guarantee as
        -- db_agent_jekt_keys otherwise.
        CREATE TABLE IF NOT EXISTS db_agent_lan_keys (
            agent_id    TEXT PRIMARY KEY,
            public_key  TEXT NOT NULL,
            private_key TEXT NOT NULL,
            created_at  INTEGER NOT NULL DEFAULT 0
        );

        -- v36: per-agent Ed25519 keypair for general agent-to-agent WAN-tier
        -- jekt signing.
        -- Same local-to-this-instance guarantee as db_agent_jekt_keys and
        -- db_agent_lan_keys: the private half leaves only by being injected
        -- into that ONE agent's own MCP process env (AGENTMUX_WAN_KEY) at
        -- spawn, never over any RPC. The public half is not secret — it is
        -- what gets published to muxbus's agent-ownership registry so a
        -- WAN counterpart can verify this agent's signatures.
        --
        -- Deliberately NOT a reuse of db_agent_lan_keys: see the v36 doc
        -- comment above for why the two tiers' keys must have independent
        -- lifecycles. `key_version` starts at 1 and is reserved for the
        -- version-synchronised publish (§3.2) and rotation (§3.6) phases;
        -- nothing reads it yet.
        CREATE TABLE IF NOT EXISTS db_agent_wan_keys (
            agent_id    TEXT PRIMARY KEY,
            public_key  TEXT NOT NULL,
            private_key TEXT NOT NULL,
            key_version INTEGER NOT NULL DEFAULT 1,
            created_at  INTEGER NOT NULL DEFAULT 0
        );

        -- v37: per-agent local identity token, keyed by the agent's UID
        -- (db_agents.id) rather than by display name -- identity M1,
        -- SPEC_AGENT_IDENTITY_CARRIED_NOT_DERIVED_2026_09_23.md §6/§9.1.
        -- Minted on first spawn, injected as AGENTMUX_AGENT_TOKEN into that
        -- one agent's process, revoked by purge_agent_dependents on delete.
        -- Not read by anything until M4; see storage/agent_tokens.rs.
        -- No FOREIGN KEY on purpose: a token must be mintable in the same
        -- critical section as the row it belongs to without ordering the
        -- two inserts, and purge_agent_dependents already owns cleanup.
        CREATE TABLE IF NOT EXISTS db_agent_tokens (
            agent_id   TEXT PRIMARY KEY,
            token      TEXT NOT NULL,
            created_at INTEGER NOT NULL DEFAULT 0
        );

        -- v38: names of deleted agents, lowercased -- identity M4a,
        -- SPEC_AGENT_IDENTITY_CARRIED_NOT_DERIVED_2026_09_23.md §6.5.4. A
        -- name-keyed signing key under a tombstoned name is never carried to
        -- a new agent's UID (M4d); the reuser gets a fresh keypair. Keyed by
        -- name because that is what the legacy key tables are keyed by.
        CREATE TABLE IF NOT EXISTS db_agent_key_tombstones (
            name        TEXT PRIMARY KEY,
            deleted_uid TEXT NOT NULL,
            deleted_at  INTEGER NOT NULL DEFAULT 0
        );

        -- Identity M4d-2: each agent's LAN and WAN signing keypairs filed
        -- under its UID (db_agents.id), copied from the name-keyed tables on
        -- ownership evidence or minted fresh (storage/agent_uid_keys.rs,
        -- SPEC_AGENT_IDENTITY_CARRIED_NOT_DERIVED_2026_09_23.md §6.5.10).
        -- copied_from is the name a key was copied from, '' when minted.
        -- Purged by UID in purge_agent_dependents. Additive, applied on
        -- every open, so no version bump.
        CREATE TABLE IF NOT EXISTS db_agent_lan_keys_by_uid (
            uid         TEXT PRIMARY KEY,
            public_key  TEXT NOT NULL,
            private_key TEXT NOT NULL,
            created_at  INTEGER NOT NULL DEFAULT 0,
            copied_from TEXT NOT NULL DEFAULT ''
        );
        CREATE TABLE IF NOT EXISTS db_agent_wan_keys_by_uid (
            uid         TEXT PRIMARY KEY,
            public_key  TEXT NOT NULL,
            private_key TEXT NOT NULL,
            created_at  INTEGER NOT NULL DEFAULT 0,
            copied_from TEXT NOT NULL DEFAULT ''
        );
        -- Identity M4d-6: a remote agent's UID key, pinned per (name, uid) on
        -- first sight beside a matching name pin (storage/lan_peer_uid_pins.rs).
        -- Additive, applied on every open, so no version bump.
        CREATE TABLE IF NOT EXISTS db_lan_peer_uid_pins (
            agent_id      TEXT NOT NULL,
            uid           TEXT NOT NULL,
            public_key    TEXT NOT NULL,
            first_seen_at INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (agent_id, uid)
        );

        -- Identity M4d-1: every display / instance name an agent has had.
        -- Keys are filed under names (and their fallback ids), and every
        -- write path that renames an agent — rename, the continuation fold,
        -- anything later — leaves keys under the old name, so the purge must
        -- still find them. Recorded by trigger so no write path can miss it;
        -- never capped; deleted with the row. Additive, applied on every
        -- open, so no version bump.
        CREATE TABLE IF NOT EXISTS db_agent_former_names (
            agent_id    TEXT NOT NULL,
            name        TEXT NOT NULL,
            recorded_at INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (agent_id, name),
            FOREIGN KEY (agent_id) REFERENCES db_agents(id) ON DELETE CASCADE
        );
        CREATE TRIGGER IF NOT EXISTS trg_agents_former_name
            AFTER UPDATE OF name ON db_agents
            WHEN old.is_template = 0 AND old.name <> new.name AND trim(old.name) <> ''
        BEGIN
            INSERT OR IGNORE INTO db_agent_former_names (agent_id, name, recorded_at)
            VALUES (old.id, old.name, CAST(strftime('%s', 'now') AS INTEGER) * 1000);
        END;
        CREATE TRIGGER IF NOT EXISTS trg_agents_former_instance_name
            AFTER UPDATE OF instance_name ON db_agents
            WHEN old.is_template = 0
             AND COALESCE(old.instance_name, '') <> COALESCE(new.instance_name, '')
             AND trim(COALESCE(old.instance_name, '')) <> ''
        BEGIN
            INSERT OR IGNORE INTO db_agent_former_names (agent_id, name, recorded_at)
            VALUES (old.id, old.instance_name, CAST(strftime('%s', 'now') AS INTEGER) * 1000);
        END;

        -- v40: jekts held for an absent agent of this channel
        -- (SPEC_DURABLE_JEKT_DELIVERY_2026_09_24.md). Verdict columns are
        -- NULL (unchecked), 0 (failed) or 1 (verified).
        CREATE TABLE IF NOT EXISTS db_jekt_held (
            request_id       TEXT PRIMARY KEY,
            target_uid       TEXT NOT NULL,
            target_agent     TEXT NOT NULL DEFAULT '',
            source_agent     TEXT NOT NULL DEFAULT '',
            audit_source_uid TEXT NOT NULL DEFAULT '',
            message          TEXT NOT NULL,
            priority         TEXT NOT NULL DEFAULT '',
            jekt_tier        TEXT NOT NULL DEFAULT '',
            delivery_tier    TEXT NOT NULL DEFAULT '',
            sig_verified     INTEGER,
            reagent_verified INTEGER,
            lan_verified     INTEGER,
            channel_verified INTEGER,
            is_transcript_request              INTEGER NOT NULL DEFAULT 0,
            transcript_request_escalate_forced INTEGER NOT NULL DEFAULT 0,
            sent_at_ms       INTEGER NOT NULL,
            expires_at_ms    INTEGER NOT NULL,
            attempts         INTEGER NOT NULL DEFAULT 0,
            last_error       TEXT NOT NULL DEFAULT ''
        );
        CREATE INDEX IF NOT EXISTS idx_jekt_held_target ON db_jekt_held (target_uid, sent_at_ms);

        -- v43: devices paired with the viewer listener. See
        -- OBJECT_SCHEMA_VERSION's v43 doc comment above.
        CREATE TABLE IF NOT EXISTS db_viewer_devices (
            device_id    TEXT PRIMARY KEY,
            token_hash   TEXT NOT NULL UNIQUE,
            device_name  TEXT NOT NULL,
            device_key   TEXT NOT NULL DEFAULT '',
            created_ms   INTEGER NOT NULL,
            last_seen_ms INTEGER NOT NULL DEFAULT 0
        );

        -- The bundles picked for an agent, in order; its own bundle
        -- (db_agents.default_memory_id) is not listed here
        -- (SPEC_RENAME_KNOWLEDGE_TO_MEMORY_2026_10_06.md §3.6). No FK: a
        -- bundle lives in the identity store, and an agent's rows go with
        -- it in purge_agent_dependents. Additive, applied on every open,
        -- so no version bump.
        CREATE TABLE IF NOT EXISTS db_agent_bundles (
            agent_id  TEXT NOT NULL,
            bundle_id TEXT NOT NULL,
            position  INTEGER NOT NULL,
            PRIMARY KEY (agent_id, bundle_id)
        );

        -- A widget package's `storage` (SPEC_USER_WIDGETS_AND_WIDGET_API_2026_10_09.md §6.3), JSON values; additive, no version bump.
        CREATE TABLE IF NOT EXISTS db_widget_storage (widget_id TEXT NOT NULL, key TEXT NOT NULL, value TEXT NOT NULL, PRIMARY KEY (widget_id, key));

        -- v21: trust-on-first-use pin of a remote agent_id's LAN public key
        -- (SPEC_JEKT_LAN_TIER_SIGNING_2026_08_15.md §2.2, reagentx P0).
        -- Distinct from db_agent_lan_keys (this instance's OWN agents'
        -- keypairs, private half included) — this table holds only public
        -- keys OBSERVED from LAN peers for agent_ids this instance does not
        -- itself host, pinned on first sight so a later, different key
        -- claiming the same agent_id is treated as a mismatch rather than
        -- silently trusted.
        CREATE TABLE IF NOT EXISTS db_lan_peer_pubkey_pins (
            agent_id     TEXT PRIMARY KEY,
            public_key   TEXT NOT NULL,
            first_seen_at INTEGER NOT NULL DEFAULT 0
        );

        -- v22: durable declared-long-running-task registry — see
        -- OBJECT_SCHEMA_VERSION's v22 doc comment above. `id` mirrors the
        -- frontend's dock node_id (usually the originating tool_use_id) so
        -- rows join cleanly with DockSnapshotCache/ActivityDock data.
        -- `status` is one of 'running' | 'done' | 'error' | 'stopped',
        -- matching the vocabulary tool-adapter.ts already uses for a
        -- background launch's terminal states.
        CREATE TABLE IF NOT EXISTS db_background_tasks (
            id            TEXT PRIMARY KEY,
            block_id      TEXT NOT NULL,
            label         TEXT NOT NULL,
            pid           INTEGER,
            started_at_ms INTEGER NOT NULL,
            status        TEXT NOT NULL DEFAULT 'running',
            last_seen_ms  INTEGER NOT NULL,
            ended_at_ms   INTEGER
        );
        CREATE INDEX IF NOT EXISTS idx_background_tasks_block
            ON db_background_tasks(block_id);
        CREATE INDEX IF NOT EXISTS idx_background_tasks_status
            ON db_background_tasks(status);

        -- v25: saved fleet-control target groups — see OBJECT_SCHEMA_VERSION's
        -- v25 doc comment above. member_ids is a JSON array of block ids
        -- (Swarm's native per-agent selection unit); resolved to concrete
        -- ids by the frontend before either fleet.broadcast or
        -- fleet.bulk-stop executes, so a group's membership can't drift
        -- between when a bulk action is confirmed and when it runs.
        CREATE TABLE IF NOT EXISTS db_agent_groups (
            id         TEXT PRIMARY KEY,
            name       TEXT NOT NULL,
            member_ids TEXT NOT NULL DEFAULT '[]',
            created_at INTEGER NOT NULL DEFAULT 0
        );

        -- v26: allowlist for a responding agent's 'trusted_peers'
        -- conversation_visibility mode — see OBJECT_SCHEMA_VERSION's v26
        -- doc comment above. Mirrors db_lan_peer_pubkey_pins's exact shape
        -- (own module, get/set-style methods, case-insensitive agent_id
        -- lookups). `agent_id` is the RESPONDING agent (whose transcript
        -- may be disclosed); `granted_peer_agent_id` is the requester it
        -- trusts; `tier` records which delivery tier the grant applies to
        -- ('lan'/'wan') since a grant for one tier's cryptographic
        -- identity guarantee should not be silently assumed to hold for a
        -- weaker one.
        CREATE TABLE IF NOT EXISTS db_conversation_trust_grants (
            agent_id             TEXT NOT NULL,
            granted_peer_agent_id TEXT NOT NULL,
            tier                 TEXT NOT NULL,
            granted_at           INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (agent_id, granted_peer_agent_id, tier)
        );

        -- v28: one-shot, on-demand activity summary per definition — see
        -- OBJECT_SCHEMA_VERSION's v28 doc comment above. `summary` is empty
        -- only transiently (a row is only ever inserted once generation
        -- succeeds); there is no 'pending' state stored here — an absent
        -- row IS the pending state.
        CREATE TABLE IF NOT EXISTS db_agent_activity_summaries (
            definition_id TEXT PRIMARY KEY,
            summary       TEXT NOT NULL DEFAULT '',
            updated_at    INTEGER NOT NULL DEFAULT 0
        );",
    )?;

    // ---- Additive column migrations (schema v2+) ----
    // The flat CREATE batch above covers fresh databases. These ALTERs
    // carry an existing database (e.g. a developer's dev DB that persists
    // across builds) forward. Idempotent: the "duplicate column" error is
    // swallowed. New additive columns append here + bump OBJECT_SCHEMA_VERSION.
    //
    // db_agent_definitions' own v2/v3+ ALTERs (updated_at, user_hidden,
    // container_image/volumes/name, use_ambient_login, model_vendor_base_url,
    // auto_continue_enabled, memory_id, conversation_visibility) are run
    // separately below, conditionally on the table still existing — see that
    // block's own comment for why they can't simply be deleted even though
    // the table itself is dropped as of v32 (OBJECT_SCHEMA_VERSION's v32 doc
    // comment).
    // v5: db_agents.last_block_id — most-recent launch's block (Phase 3c).
    //     Defaults to '' for existing rows; the dual-write populates it on
    //     the next launch/continuation. Read side (3b.1b) treats '' as
    //     "no snapshot" (same as the current empty-block_id fallback).
    // v6: Container support (Phase 0 of SPEC_CONTAINER_PANE_SUPPORT_2026_06_11.md).
    //     container_image / container_volumes / container_name on both tables.
    //     Host-agent rows default to '', '[]', '' respectively — ContainerManager
    //     populates them on first container spawn.
    // v7: create db_muxbus_credentials if it doesn't exist yet (existing DBs
    //     won't have it since it's a new table, not an added column).
    // v10: standalone skill + MCP server tables (new tables, handled via
    //     CREATE TABLE IF NOT EXISTS in the flat batch above; no ALTER needed).
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS db_muxbus_credentials (
            id             TEXT PRIMARY KEY DEFAULT 'global',
            cognito_domain TEXT NOT NULL DEFAULT '',
            client_id      TEXT NOT NULL DEFAULT '',
            access_token   TEXT NOT NULL DEFAULT '',
            refresh_token  TEXT NOT NULL DEFAULT '',
            id_token       TEXT NOT NULL DEFAULT '',
            expires_at     INTEGER NOT NULL DEFAULT 0,
            user_email     TEXT NOT NULL DEFAULT '',
            user_sub       TEXT NOT NULL DEFAULT ''
        )",
    )?;

    // db_agent_definitions' own additive columns, conditional on the table
    // still existing. Removing these outright (rather than gating them) broke
    // a real upgrade path: several pending migrations (m0007, m0025, m0026,
    // m0028) call Store::open() internally as part of their own up() logic —
    // which runs THIS function, under the CURRENT binary's code — before
    // m0029 (numbered last) ever drops the table in the same pass. A store
    // old enough to still be missing one of these columns (e.g. a genuine
    // pre-v2 install jumping straight to the latest binary) would hit a live
    // "no such column" error from m0007's consolidate pass or m0025/m0028's
    // frozen_repair_def_gaps/project_template_launch_row — both of which
    // SELECT several of these columns from db_agent_definitions — well
    // before m0029 ever runs. Gating on table existence keeps this a no-op
    // once the table is gone (fresh v32+ installs never create it at all;
    // upgraded stores lose it only after m0029 runs later in the same pass)
    // without reintroducing an unconditional dependency on a table this
    // release otherwise no longer declares. Found in review of PR #3096
    // (Codex P1).
    let legacy_defs_exist: bool = conn.query_row(
        "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='db_agent_definitions'",
        [],
        |row| row.get::<_, i64>(0),
    )? == 1;
    if legacy_defs_exist {
        for stmt in &[
            "ALTER TABLE db_agent_definitions ADD COLUMN updated_at INTEGER NOT NULL DEFAULT 0",
            "ALTER TABLE db_agent_definitions ADD COLUMN user_hidden INTEGER NOT NULL DEFAULT 0",
            "ALTER TABLE db_agent_definitions ADD COLUMN container_image TEXT NOT NULL DEFAULT ''",
            "ALTER TABLE db_agent_definitions ADD COLUMN container_volumes TEXT NOT NULL DEFAULT '[]'",
            "ALTER TABLE db_agent_definitions ADD COLUMN container_name TEXT NOT NULL DEFAULT ''",
            "ALTER TABLE db_agent_definitions ADD COLUMN use_ambient_login INTEGER NOT NULL DEFAULT 0",
            "ALTER TABLE db_agent_definitions ADD COLUMN model_vendor_base_url TEXT NOT NULL DEFAULT ''",
            "ALTER TABLE db_agent_definitions ADD COLUMN auto_continue_enabled INTEGER NOT NULL DEFAULT 0",
            "ALTER TABLE db_agent_definitions ADD COLUMN memory_id TEXT NOT NULL DEFAULT ''",
            "ALTER TABLE db_agent_definitions ADD COLUMN conversation_visibility TEXT NOT NULL DEFAULT 'private'",
        ] {
            if let Err(e) = conn.execute_batch(stmt) {
                let msg = e.to_string();
                if !msg.contains("duplicate column") {
                    return Err(e.into());
                }
            }
        }
    }

    for stmt in &[
        "ALTER TABLE db_agents ADD COLUMN last_block_id TEXT NOT NULL DEFAULT ''",
        "ALTER TABLE db_agents ADD COLUMN container_image TEXT NOT NULL DEFAULT ''",
        "ALTER TABLE db_agents ADD COLUMN container_volumes TEXT NOT NULL DEFAULT '[]'",
        "ALTER TABLE db_agents ADD COLUMN container_name TEXT NOT NULL DEFAULT ''",
        // NOTE: targets db_bundles, not db_memory_bundles — adopt_legacy_table_names
        // (called at the top of this function) has already renamed the table
        // by the time this runs, on every DB that goes through the Phase 4
        // rename. See SPEC_ARMORY_PHASE4_STORAGE_RENAME_COMPLETION_2026_07_12.md.
        "ALTER TABLE db_bundles ADD COLUMN is_global INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE db_bundles ADD COLUMN sort_order INTEGER NOT NULL DEFAULT 0",
        // v12: explicit per-agent ambient-login opt-in (fail-by-default spawn
        // gating — SPEC_ACCOUNT_DELETE_DEAUTH_LAYERS_2_4_2026_07_14.md §2.3).
        "ALTER TABLE db_agents ADD COLUMN use_ambient_login INTEGER NOT NULL DEFAULT 0",
        // v15: model vendor override — redirects a harness at a non-default
        // backend (e.g. ANTHROPIC_BASE_URL). Formalizes harness vs. model
        // vendor as distinct concepts; see ProviderConfig::base_url_env_var.
        "ALTER TABLE db_agents ADD COLUMN model_vendor_base_url TEXT NOT NULL DEFAULT ''",
        // v16: provider-scoped bundle instructions (ABF v0.2 §2.2) — JSON
        // object of {provider_id: content} variants, additive to the
        // existing flat `instructions` column.
        "ALTER TABLE db_bundles ADD COLUMN instructions_by_provider TEXT NOT NULL DEFAULT '{}'",
        // v17: Warden Supervisor auto-continue opt-in (fail-by-default,
        // same posture as use_ambient_login) — see
        // ANALYSIS_WARDEN_AUTO_CONTROLLER_CONTINUATION_WATCHER_2026_08_12.md.
        "ALTER TABLE db_agents ADD COLUMN auto_continue_enabled INTEGER NOT NULL DEFAULT 0",
        // v19: the agent's own dedicated ABF bundle, set once at creation.
        // db_agents gets a SEPARATE default_memory_id column (not the
        // existing instance-scoped memory_id) — see its own CREATE TABLE
        // comment for why, and OBJECT_SCHEMA_VERSION's v19 entry.
        "ALTER TABLE db_agents ADD COLUMN default_memory_id TEXT NOT NULL DEFAULT ''",
        // v26: muxspect Phase B/C per-agent conversation-disclosure policy —
        // see OBJECT_SCHEMA_VERSION's v26 doc comment above. Dual-written
        // to db_agents under the SAME column name (a simple opt-in-style
        // setting, like auto_continue_enabled above — not a differently-
        // named-on-db_agents case like memory_id/default_memory_id).
        "ALTER TABLE db_agents ADD COLUMN conversation_visibility TEXT NOT NULL DEFAULT 'private'",
        // v27: AgentMux-controlled, highest-priority Global Bundle tier —
        // see OBJECT_SCHEMA_VERSION's v27 doc comment above.
        "ALTER TABLE db_bundles ADD COLUMN is_system INTEGER NOT NULL DEFAULT 0",
        // v29: the agent's latest launch state on db_agents — the four
        // db_agent_instances columns the consolidated row still lacked. See
        // OBJECT_SCHEMA_VERSION's v29 doc comment above and the column doc in
        // db_agents' CREATE TABLE. Backfilled from db_agent_instances by
        // m0025_agents_launch_state_backfill; kept current by the instance
        // dual-write until the legacy table is dropped.
        "ALTER TABLE db_agents ADD COLUMN session_id TEXT NOT NULL DEFAULT ''",
        "ALTER TABLE db_agents ADD COLUMN status TEXT NOT NULL DEFAULT ''",
        "ALTER TABLE db_agents ADD COLUMN started_at INTEGER NOT NULL DEFAULT 0",
        "ALTER TABLE db_agents ADD COLUMN ended_at INTEGER NOT NULL DEFAULT 0",
        // v39: identity M4c-1 — the Global Memory writer's UID beside its
        // name. See OBJECT_SCHEMA_VERSION's v39 doc comment above.
        "ALTER TABLE db_bundle_versions ADD COLUMN written_by_uid TEXT NOT NULL DEFAULT ''",
        // v41: the subagent that owns a background task. See
        // OBJECT_SCHEMA_VERSION's v41 doc comment above.
        "ALTER TABLE db_background_tasks ADD COLUMN owner_tool_use_id TEXT",
        // v42: the Runtime menu choices the user last made for the agent. See
        // OBJECT_SCHEMA_VERSION's v42 doc comment above.
        "ALTER TABLE db_agents ADD COLUMN last_runtime TEXT NOT NULL DEFAULT ''",
        // v43: an agent hidden from paired devices. See
        // OBJECT_SCHEMA_VERSION's v43 doc comment above.
        "ALTER TABLE db_agents ADD COLUMN hide_from_devices INTEGER NOT NULL DEFAULT 0",
    ] {
        if let Err(e) = conn.execute_batch(stmt) {
            let msg = e.to_string();
            if !msg.contains("duplicate column") {
                return Err(e.into());
            }
        }
    }

    // ---- Seed blank Bundle singleton ----
    // The launch UI renders this as the default option in its Bundle
    // dropdown. Fixed id so tests + dev seed data can hard-code
    // references.
    conn.execute_batch(
        "INSERT OR IGNORE INTO db_bundles
            (id, name, description, is_blank, created_at, updated_at)
         VALUES ('blank', '__blank__', 'Vanilla CLI — no instructions, no context', 1, 0, 0);",
    )?;

    // Channel-scoped migration tracking (parallel to the global db_migrations
    // in store.db). MigrationScope::Channel migrations record completion here
    // so each channel tracks its own migration state independently.
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS db_migrations (
            id          TEXT PRIMARY KEY,
            applied_at  TEXT NOT NULL,
            duration_ms INTEGER NOT NULL DEFAULT 0,
            scope       TEXT NOT NULL DEFAULT 'channel'
        );",
    )?;

    Ok(())
}

/// Rename any pre-flatten `objects.db` tables to their de-forged names and
/// drop the dead workflow/sentinel tables. Idempotent — on a fresh or
/// already-flat database every check is a no-op.
///
/// This is the single surviving fragment of the old v1–v11 chain: it
/// exists only to carry a developer's pre-flatten `objects.db` (always at
/// the post-v11 schema, since v11 is merged) forward without data loss.
/// SQLite ≥ 3.25 auto-updates foreign-key references in child tables when
/// a parent table is renamed, so the agent/identity cascades survive.
fn adopt_legacy_table_names(conn: &Connection) -> Result<(), StoreError> {
    for (legacy, current) in LEGACY_TABLE_RENAMES {
        let legacy_exists: i64 = conn.query_row(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?1",
            [legacy],
            |row| row.get(0),
        )?;
        if legacy_exists == 0 {
            continue;
        }
        let current_exists: i64 = conn.query_row(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?1",
            [current],
            |row| row.get(0),
        )?;
        if current_exists == 1 {
            // Both present — only reachable on a deliberate
            // downgrade-roundtrip dev DB (flat build → pre-flatten build,
            // which re-creates the legacy name → flat build again). The
            // flatten abandons the downgrade path, but the legacy table
            // may hold rows the downgraded build wrote. Do NOT drop it —
            // that would be silent data loss (the bug class behind PR
            // #933's Codex P1). Leave it on disk and warn loudly so the
            // developer can recover or delete it manually.
            warn!(
                legacy_table = *legacy,
                current_table = *current,
                "objects.db has both a legacy table and its de-forged \
                 replacement — this only happens after a downgrade to a \
                 pre-flatten build; the legacy table is left untouched \
                 for manual recovery and is otherwise unused",
            );
        } else {
            conn.execute_batch(&format!("ALTER TABLE {legacy} RENAME TO {current};"))?;
        }
    }

    // Drop indexes orphaned by the renames — the flat DDL recreates them
    // under the new names.
    for idx in LEGACY_INDEX_DROPS {
        conn.execute_batch(&format!("DROP INDEX IF EXISTS {idx};"))?;
    }

    // Drop tables retained only for the abandoned downgrade path.
    for table in DEAD_TABLE_DROPS {
        conn.execute_batch(&format!("DROP TABLE IF EXISTS {table};"))?;
    }

    Ok(())
}

/// Initialize (or re-validate) the `~/.agentmux/shared/store.db` schema.
///
/// Contains only the durable user content that must survive across
/// channels/versions: identity accounts, agent→account links, memory
/// bundles, drone definitions, and muxbus credentials. Session state
/// (`db_block`, `db_tab`, etc.) and drone run history stay in the
/// per-channel `objects.db`.
///
/// `db_agent_identity_links` drops the FK to `db_agent_definitions` here
/// (cross-DB FK enforcement is impossible in SQLite); application code
/// enforces referential integrity instead.
///
/// Idempotent — safe to call on every startup.
pub fn run_shared_store_schema(conn: &Connection) -> Result<(), StoreError> {
    // Rename any legacy table names (including the Phase 4 accounts/bundles
    // rename — LEGACY_TABLE_RENAMES entries that don't apply to this
    // connection's tables are safely skipped) before the idempotent
    // CREATE TABLE IF NOT EXISTS batch below, same as run_object_schema.
    adopt_legacy_table_names(conn)?;

    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS db_accounts (
            id           TEXT PRIMARY KEY,
            name         TEXT NOT NULL,
            provider     TEXT NOT NULL,
            kind         TEXT NOT NULL,
            display_name TEXT NOT NULL DEFAULT '',
            secret_ref   TEXT NOT NULL,
            context      TEXT NOT NULL DEFAULT '{}',
            status       TEXT NOT NULL DEFAULT 'unknown',
            created_at   INTEGER NOT NULL DEFAULT 0,
            updated_at   INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_ss_accounts_provider
            ON db_accounts(provider);

        CREATE TABLE IF NOT EXISTS db_agent_identity_links (
            agent_id   TEXT NOT NULL,
            account_id TEXT NOT NULL,
            provider   TEXT NOT NULL,
            PRIMARY KEY (agent_id, provider),
            FOREIGN KEY (account_id) REFERENCES db_accounts(id) ON DELETE CASCADE
        );
        CREATE INDEX IF NOT EXISTS idx_ss_agent_identity_links_account
            ON db_agent_identity_links(account_id);

        CREATE TABLE IF NOT EXISTS db_bundles (
            id            TEXT PRIMARY KEY,
            name          TEXT NOT NULL UNIQUE,
            description   TEXT NOT NULL DEFAULT '',
            is_blank      INTEGER NOT NULL DEFAULT 0,
            is_global     INTEGER NOT NULL DEFAULT 0,
            provider      TEXT NOT NULL DEFAULT '',
            model         TEXT NOT NULL DEFAULT '',
            instructions  TEXT NOT NULL DEFAULT '',
            instructions_by_provider TEXT NOT NULL DEFAULT '{}',
            context_files TEXT NOT NULL DEFAULT '[]',
            mcp_servers   TEXT NOT NULL DEFAULT '[]',
            skills        TEXT NOT NULL DEFAULT '[]',
            sort_order    INTEGER NOT NULL DEFAULT 0,
            created_at    INTEGER NOT NULL DEFAULT 0,
            updated_at    INTEGER NOT NULL DEFAULT 0,
            is_system     INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_ss_bundles_is_blank
            ON db_bundles(is_blank);

        CREATE TABLE IF NOT EXISTS db_drone_definitions (
            id          TEXT PRIMARY KEY,
            name        TEXT NOT NULL,
            description TEXT NOT NULL DEFAULT '',
            graph       TEXT NOT NULL DEFAULT '{\"nodes\":[],\"edges\":[]}',
            viewport    TEXT NOT NULL DEFAULT '{\"x\":0,\"y\":0,\"zoom\":1}',
            created_at  INTEGER NOT NULL DEFAULT 0,
            updated_at  INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_ss_drone_definitions_updated
            ON db_drone_definitions(updated_at DESC);

        CREATE TABLE IF NOT EXISTS db_muxbus_credentials (
            id             TEXT PRIMARY KEY DEFAULT 'global',
            cognito_domain TEXT NOT NULL DEFAULT '',
            client_id      TEXT NOT NULL DEFAULT '',
            access_token   TEXT NOT NULL DEFAULT '',
            refresh_token  TEXT NOT NULL DEFAULT '',
            id_token       TEXT NOT NULL DEFAULT '',
            expires_at     INTEGER NOT NULL DEFAULT 0,
            user_email     TEXT NOT NULL DEFAULT '',
            user_sub       TEXT NOT NULL DEFAULT ''
        );

        CREATE TABLE IF NOT EXISTS db_migrations (
            id          TEXT PRIMARY KEY,
            applied_at  TEXT NOT NULL,
            duration_ms INTEGER NOT NULL DEFAULT 0,
            scope       TEXT NOT NULL DEFAULT 'global'
        );

        CREATE TABLE IF NOT EXISTS db_cron_jobs (
            id            TEXT PRIMARY KEY,
            name          TEXT NOT NULL,
            expression    TEXT NOT NULL,
            prompt        TEXT NOT NULL,
            target        TEXT NOT NULL,
            created_by    TEXT NOT NULL DEFAULT '',
            enabled       INTEGER NOT NULL DEFAULT 1,
            last_fired    INTEGER,
            fire_count    INTEGER NOT NULL DEFAULT 0,
            max_fires     INTEGER,
            created_at    INTEGER NOT NULL DEFAULT 0,
            max_age_secs  INTEGER,
            -- v11 (identity M1b): the target's UID (db_agents.id) beside the
            -- slug, resolved ONCE at create time (spec §5.4), dual-written,
            -- not yet read -- a job still fires by `target`. Empty when the
            -- name did not resolve; never guessed.
            target_uid    TEXT NOT NULL DEFAULT '',
            -- v12 (identity M4c-1): the creator's UID from the request's
            -- Caller, beside the created_by name. Empty when unattributed.
            created_by_uid TEXT NOT NULL DEFAULT ''
        );
        CREATE INDEX IF NOT EXISTS idx_ss_cron_jobs_enabled
            ON db_cron_jobs(enabled);

        -- v4: per-agent M2M credential for muxbus Tier-4 binding (see
        -- SHARED_STORE_SCHEMA_VERSION's doc comment above). client_secret is
        -- a Cognito app client secret, not a user password — same trust
        -- tier as MUXBUS_TOKEN in db_muxbus_credentials above, stored
        -- unencrypted in the same shared store.db for the same reason.
        CREATE TABLE IF NOT EXISTS db_agent_credentials (
            agent_id       TEXT PRIMARY KEY,
            client_id      TEXT NOT NULL DEFAULT '',
            client_secret  TEXT NOT NULL DEFAULT '',
            token_endpoint TEXT NOT NULL DEFAULT '',
            access_token   TEXT NOT NULL DEFAULT '',
            expires_at     INTEGER NOT NULL DEFAULT 0,
            created_at     INTEGER NOT NULL DEFAULT 0
        );

        -- v6: durable mirror of each agent's native (Claude Code) memory
        -- files, keyed by the stable AgentDefinition.id rather than any live
        -- filesystem path — the live path is channel-relative by design
        -- (per-build-channel filesystem isolation), so the same agent opened
        -- from two channels resolves two different on-disk memory dirs. This
        -- table is what makes native memory durable and location-consistent
        -- across channels: agent:memory:list/read_file upsert into it on
        -- every read, and merge the live-FS view with it in the response —
        -- see docs/specs/SPEC_NATIVE_MEMORY_DURABLE_SYNC_2026_08_07.md.
        -- Duplicated into run_object_schema (same both-schemas pattern as
        -- db_agent_credentials above) for id_store's per-channel fallback
        -- before 0011_shared_store_backfill has run.
        CREATE TABLE IF NOT EXISTS db_agent_native_memory (
            agent_id           TEXT NOT NULL,
            filename           TEXT NOT NULL,
            content            TEXT NOT NULL,
            metadata_type      TEXT NOT NULL DEFAULT '',
            size_bytes         INTEGER NOT NULL DEFAULT 0,
            updated_at         INTEGER NOT NULL DEFAULT 0,
            last_seen_path     TEXT NOT NULL DEFAULT '',
            last_seen_mtime_ms INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (agent_id, filename)
        );

        -- v8: append-only version history for native memory content — one
        -- row per agent:memory:write_file call (plus reverts), so a prior
        -- value is never lost the way the mirror above loses it on every
        -- overwrite. See docs/specs/SPEC_MEMORY_VERSION_CONTROL_AND_ARMORY_AUDIT_2026_08_19.md.
        CREATE TABLE IF NOT EXISTS db_agent_native_memory_versions (
            id                 TEXT PRIMARY KEY,
            agent_id           TEXT NOT NULL,
            filename           TEXT NOT NULL,
            content            TEXT NOT NULL,
            content_hash       TEXT NOT NULL,
            parent_version_id  TEXT,
            source             TEXT NOT NULL DEFAULT 'agent_inferred',
            source_detail      TEXT NOT NULL DEFAULT '{}',
            session_id         TEXT NOT NULL DEFAULT '',
            created_at         INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_ss_native_memory_versions_lookup
            ON db_agent_native_memory_versions(agent_id, filename, created_at);

        -- v10: append-only version history for Global Memory entry content
        -- — see SHARED_STORE_SCHEMA_VERSION's own doc comment for the full
        -- rationale (SPEC_AGENT_FACING_GLOBAL_MEMORY_API_2026_09_15.md).
        CREATE TABLE IF NOT EXISTS db_bundle_versions (
            id                 TEXT PRIMARY KEY,
            bundle_id          TEXT NOT NULL,
            name               TEXT NOT NULL,
            instructions       TEXT NOT NULL,
            content_hash       TEXT NOT NULL,
            parent_version_id  TEXT,
            source             TEXT NOT NULL DEFAULT 'agent_inferred',
            source_detail      TEXT NOT NULL DEFAULT '{}',
            written_by         TEXT NOT NULL DEFAULT '',
            created_at         INTEGER NOT NULL DEFAULT 0,
            -- v12 (identity M4c-1): the writer's UID from the request's
            -- Caller, beside the written_by name. Empty when unattributed.
            written_by_uid     TEXT NOT NULL DEFAULT ''
        );
        CREATE INDEX IF NOT EXISTS idx_ss_bundle_versions_lookup
            ON db_bundle_versions(bundle_id, created_at);",
    )?;

    // Seed the blank Bundle singleton — same fixed id as objects.db so
    // cross-version reads never see a missing blank row.
    conn.execute_batch(
        "INSERT OR IGNORE INTO db_bundles
            (id, name, description, is_blank, created_at, updated_at)
         VALUES ('blank', '__blank__', 'Vanilla CLI — no instructions, no context', 1, 0, 0);",
    )?;

    // ---- Additive column migrations (schema v5+) ----
    // Same idempotent pattern as run_object_schema's ALTER-TABLE loop below:
    // the flat CREATE batch above covers fresh DBs; these carry an existing
    // shared store forward. "duplicate column" is swallowed so this is safe
    // to run on every startup regardless of whether the column already exists.
    //
    // v5: db_cron_jobs.max_age_secs — optional hard expiry bound (seconds
    //     since created_at), alongside the existing max_fires bound. NULL
    //     for all existing rows = no behavior change for jobs created before
    //     this column existed.
    for stmt in &[
        "ALTER TABLE db_cron_jobs ADD COLUMN max_age_secs INTEGER",
        // v7: provider-scoped bundle instructions (ABF v0.2 §2.2).
        "ALTER TABLE db_bundles ADD COLUMN instructions_by_provider TEXT NOT NULL DEFAULT '{}'",
        // v9: AgentMux-controlled, highest-priority Global Bundle tier —
        // see OBJECT_SCHEMA_VERSION's v27 doc comment above.
        "ALTER TABLE db_bundles ADD COLUMN is_system INTEGER NOT NULL DEFAULT 0",
        // v11: identity M1b — the cron target's UID beside its slug.
        "ALTER TABLE db_cron_jobs ADD COLUMN target_uid TEXT NOT NULL DEFAULT ''",
        // v12: identity M4c-1 — the actor's UID beside each actor name.
        "ALTER TABLE db_cron_jobs ADD COLUMN created_by_uid TEXT NOT NULL DEFAULT ''",
        "ALTER TABLE db_bundle_versions ADD COLUMN written_by_uid TEXT NOT NULL DEFAULT ''",
    ] {
        if let Err(e) = conn.execute_batch(stmt) {
            let msg = e.to_string();
            if !msg.contains("duplicate column") {
                return Err(e.into());
            }
        }
    }

    Ok(())
}

/// `user_version` stamped into the new, permanently-global
/// `~/.agentmux/shared/identity-store.db` (distinct file from
/// `store.db` — see [`run_identity_store_schema`]'s own doc comment for
/// why). Independent counter from `SHARED_STORE_SCHEMA_VERSION`.
///   v1 — initial: agent→account links, bundles, drone
///        definitions, muxbus credentials, per-agent M2M credentials,
///        native memory mirror, cron jobs. Everything
///        `run_shared_store_schema` already held EXCEPT `db_accounts` —
///        see docs/specs/SPEC_IDENTITY_STORE_SPLIT_2026_08_17.md §3.1.
///   v2 — db_accounts, as a FALLBACK MIRROR only (reagentx P0 review on
///        PR #2632): the link resolves via this store now, but the
///        account row it points to could still only exist in the
///        per-channel-isolatable `id_store`, which is empty on a fresh
///        channel — the reported bug wasn't actually fixed end to end
///        without this. `id_store` remains the PRIMARY, authoritative,
///        write path (so Armory's disposable-test-account isolation is
///        unchanged); this copy is consulted only when `id_store` doesn't
///        have the row. See `identity::resolver::inject`'s account
///        resolution for the read-with-fallback + write-back-to-source-store
///        logic this enables.
///   v3 — db_agent_native_memory_versions, for schema parity with the v1
///        native memory mirror this store already carries. Same as that
///        table, this store's copy is not an actively-written duplicate
///        (id_store/store.db remains the authoritative write path — see
///        v2's note above) — added here only so a future always-global
///        read path (mirroring what v2 did for db_accounts) is not blocked
///        on a missing table. See
///        docs/specs/SPEC_MEMORY_VERSION_CONTROL_AND_ARMORY_AUDIT_2026_08_19.md.
///   v4 — db_bundles.is_system, for schema parity with OBJECT_SCHEMA_VERSION
///        v27 / SHARED_STORE_SCHEMA_VERSION v9 (same column, same reasoning
///        as v3 above — this store's `db_bundles` copy is not an actively-
///        written duplicate either). Real ALTER TABLE ADD COLUMN added to
///        `run_identity_store_schema` in the same change, so this counter
///        MUST bump alongside it — an unbumped counter would leave an
///        existing identity-store.db stamped at the old user_version
///        forever, silently defeating check_schema_compat/stamp_version's
///        forward-compat lock for this one store (reagent P1, PR #2782).
///        See docs/specs/SPEC_GLOBAL_MEMORY_SYSTEM_TIER_2026_08_24.md.
/// v5: `db_work_queue` — the Muxqueue backlog
///        (`docs/reports/REPORT_UNIVERSAL_AGENT_WORK_QUEUE_2026_09_01.md`).
///        A NEW table, so no ALTER is involved, but the counter still bumps:
///        an identity-store.db stamped v4 that a v5 binary opens must be
///        recognized as older, not "already current" (same reagent P1 on
///        PR #2782 reasoning recorded for v4 above).
///
///        Deliberately in THIS store, not the per-channel one. A queue whose
///        rows are per-channel means "any agent IN THIS CHANNEL can pick it
///        up", which is close to useless given every local/dev/portable build
///        is its own channel — see the report's §6.2, and
///        `docs/analysis/ANALYSIS_PER_CHANNEL_AUTH_BYPASSES_2026_08_31.md`
///        for what the per-channel/global seam has already cost once.
/// v6: `db_skills`, `db_mcp_servers` — Phase 2 of
///        `SPEC_DURABLE_BINDINGS_2026_09_10.md`. These become the
///        AUTHORITATIVE catalogs as of this version; the per-channel copies
///        in `run_object_schema` keep their declarations (never removed —
///        see that spec's §5.1 and §8 item 3) and continue to exist only as
///        the same best-effort degraded-mode fallback
///        `bootstrap/stores.rs` already substitutes for every other
///        identity-store table (`mstore` in
///        place of `identity_store` when the identity store can't be
///        resolved, created, or opened). Deliberately NOT declared with the
///        ref tables (`db_agent_skills_ref`, `db_bundle_skills_ref`, and
///        the MCP equivalents) — those stay channel-scoped until Phase 3/6
///        promotes them; adding them here now, unpaired with a live writer,
///        would be schema for a phase that hasn't shipped.
/// v7: partial UNIQUE indexes on `db_skills(name, skill_type)` and
///        `db_mcp_servers(name)`, each `WHERE is_global = 1` — global rows
///        only; two different owners' private rows sharing a name is still
///        fine. Closes a real race in
///        `m0031_carry_skills_and_mcp_servers_to_identity_store`: two
///        channels' migrations can run concurrently (multiple AgentMux
///        instances is an explicitly supported scenario), both read "no
///        matching global row yet" before either has inserted, and both
///        proceed to insert under different ids — the promised first-wins
///        cross-channel dedup silently fails without a DB-level constraint
///        to arbitrate the race (Codex P1, PR #3181). The migration's
///        insert now handles the resulting `UNIQUE constraint failed` by
///        re-querying for whichever row actually won.
///   v8 — `idx_ids_skills_global_name` replaces `idx_ids_skills_global_name_type`:
///        narrows the skill global-uniqueness index from `(name, skill_type)`
///        to `name` alone, matching the invariant `skill_upsert_unique_global`'s
///        own dup-check has always enforced (`WHERE name = ?1 AND id <> ?2
///        AND is_global = 1` — no `skill_type` filter). Codex P2, PR #3182,
///        `SPEC_DURABLE_BINDINGS_2026_09_10.md` §5.4: the v7 index was
///        narrower than the invariant it was meant to back, so a
///        same-name-different-`skill_type` race between two channels could
///        insert two rows the application itself considers duplicates. MCP
///        servers don't have this mismatch — `idx_ids_mcp_servers_global_name`
///        is already name-only — so this bump is skills-only.
///        `m0033_narrow_skill_global_uniqueness_index` defensively collapses
///        any existing same-name/different-type pair `m0031`'s own
///        `(name, skill_type)`-keyed dedup may already have produced, to the
///        earlier-`created_at` row, before the new index is created.
///   v9 — db_bundle_versions, for schema parity with OBJECT_SCHEMA_VERSION
///        v35 / SHARED_STORE_SCHEMA_VERSION v10 (same table, same reasoning
///        as v3 above — this store's copy is not an actively-written
///        duplicate either). See
///        docs/specs/SPEC_AGENT_FACING_GLOBAL_MEMORY_API_2026_09_15.md.
///   v10 — db_work_queue.target_agent_uid / claimed_by_uid, and
///        db_cron_jobs.target_uid (parity with SHARED_STORE_SCHEMA_VERSION
///        v11): the UID (`db_agents.id`) beside each slug column, dual-written
///        and not yet read. Identity M1b of
///        SPEC_AGENT_IDENTITY_CARRIED_NOT_DERIVED_2026_09_23.md §9.1.
///        `target_agent_uid` is resolved ONCE at enqueue (spec §5.4);
///        `claimed_by_uid` is CARRIED from the claimer's own
///        `AGENTMUX_AGENT_UID` (M1a, #3548), falling back to resolving its
///        `agent_id` with the fallback counted (spec §9.2). Empty when
///        unknown; never guessed.
///   v11 — db_work_queue.created_by_uid, and db_cron_jobs.created_by_uid /
///        db_bundle_versions.written_by_uid (parity with
///        SHARED_STORE_SCHEMA_VERSION v12): the actor's UID beside each
///        actor-name column, taken from the request's `Caller` (its
///        `X-Agent-Token`), never from a body field. Empty for an
///        Unattributed request; never guessed. Dual-written, not yet read.
///        Identity M4c-1 of
///        SPEC_AGENT_IDENTITY_CARRIED_NOT_DERIVED_2026_09_23.md §6.5.9.
///
/// Added WITHOUT a bump: `db_mcp_server_reach` (OBJECT_SCHEMA_VERSION v44
/// has the same table). The catalog's reach switches live with the catalog,
/// here, so they survive an upgrade where an agent bind
/// (`db_agent_mcp_ref`, in `objects.db`) does not. This store is one file
/// shared by every AgentMux on the computer, installed and dev builds alike,
/// and `check_schema_compat` makes any build that speaks an older version
/// refuse it. So a bump here locks every older AgentMux on the computer out
/// as soon as one newer build starts: a dev build of a branch with a bump
/// did exactly that on 2026-10-11. A table an older build never reads needs
/// no lock: an older build ignores it, a newer one creates it if missing.
/// Bump only for a change an older build would misread or corrupt.
/// `SPEC_CONNECTORS_MCP_WHAT_AGENTS_RUN_2026_10_10.md` §2.1.
pub const IDENTITY_STORE_SCHEMA_VERSION: i64 = 11;

/// Initialize (or re-validate) the `~/.agentmux/shared/identity-store.db`
/// schema — the permanently-global store introduced by
/// `docs/specs/SPEC_IDENTITY_STORE_SPLIT_2026_08_17.md` to fix
/// conversation-continuity-across-a-version-switch
/// (`docs/specs/REPORT_HISTORY_CONTINUITY_ACROSS_VERSION_UPGRADE_2026_08_17.md`).
///
/// This is a **near-superset** of [`run_shared_store_schema`]'s tables
/// (v2): every one of them, INCLUDING `db_accounts` as of v2 — but
/// `db_accounts` here is a read-through FALLBACK MIRROR only, not the
/// authoritative write path. Unlike agent→account links, bundles,
/// drone definitions, muxbus credentials, and native memory,
/// `db_accounts` has one genuine, intentional isolation need (Armory's
/// disposable delete-account testing flow): a real per-account
/// creation-time scope split (SPEC §3.2) hasn't shipped yet, so
/// `id_store`/`resolve_shared_store_path()` stays the primary read/write
/// path for accounts, preserving Armory's isolation exactly as before.
/// This store's copy exists ONLY so a link resolved here (always global)
/// doesn't dead-end on an account row that's stuck in a fresh, per-
/// channel-isolated `id_store` — see `identity::resolver::inject`'s
/// account resolution (falls back to this store's `identity_get` only
/// when `id_store`'s own lookup misses) and
/// `migrations::m0022_identity_store_links_backfill` (backfills this
/// mirror the same way it backfills links). `db_agent_identity_links.account_id`
/// still has NO foreign key here (same reasoning `run_shared_store_schema`
/// already uses to drop its FK to `db_agent_definitions`: cross-database
/// FK enforcement is impossible in SQLite, and this table's `db_accounts`
/// is itself just a mirror, not guaranteed complete — application code
/// enforces referential integrity, tolerating a miss, instead).
///
/// A dedicated store (not a subset of the existing shared store reached
/// via a new always-global path) is deliberate, not just a naming choice:
/// it makes "can this table ever be reached from a per-channel-isolated
/// codepath" a structural fact (a totally separate file/connection)
/// rather than a convention someone could violate by calling
/// `resolve_shared_store_path()` for a new call site later — the exact
/// failure mode `docs/specs/CHECKLIST_AGENT_DATA_SCOPE_ROUTING_2026_08_17.md`
/// items 2-4 already document as having bitten this codebase more than
/// once.
///
/// Idempotent — safe to call on every startup.
pub fn run_identity_store_schema(conn: &Connection) -> Result<(), StoreError> {
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS db_agent_identity_links (
            agent_id   TEXT NOT NULL,
            account_id TEXT NOT NULL,
            provider   TEXT NOT NULL,
            PRIMARY KEY (agent_id, provider)
        );
        CREATE INDEX IF NOT EXISTS idx_ids_agent_identity_links_account
            ON db_agent_identity_links(account_id);

        -- v2: fallback mirror only — see this function's own doc comment
        -- above. id_store stays the authoritative read/write path.
        CREATE TABLE IF NOT EXISTS db_accounts (
            id           TEXT PRIMARY KEY,
            name         TEXT NOT NULL,
            provider     TEXT NOT NULL,
            kind         TEXT NOT NULL,
            display_name TEXT NOT NULL DEFAULT '',
            secret_ref   TEXT NOT NULL,
            context      TEXT NOT NULL DEFAULT '{}',
            status       TEXT NOT NULL DEFAULT 'unknown',
            created_at   INTEGER NOT NULL DEFAULT 0,
            updated_at   INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_ids_accounts_provider
            ON db_accounts(provider);

        CREATE TABLE IF NOT EXISTS db_bundles (
            id            TEXT PRIMARY KEY,
            name          TEXT NOT NULL UNIQUE,
            description   TEXT NOT NULL DEFAULT '',
            is_blank      INTEGER NOT NULL DEFAULT 0,
            is_global     INTEGER NOT NULL DEFAULT 0,
            provider      TEXT NOT NULL DEFAULT '',
            model         TEXT NOT NULL DEFAULT '',
            instructions  TEXT NOT NULL DEFAULT '',
            instructions_by_provider TEXT NOT NULL DEFAULT '{}',
            context_files TEXT NOT NULL DEFAULT '[]',
            mcp_servers   TEXT NOT NULL DEFAULT '[]',
            skills        TEXT NOT NULL DEFAULT '[]',
            sort_order    INTEGER NOT NULL DEFAULT 0,
            created_at    INTEGER NOT NULL DEFAULT 0,
            updated_at    INTEGER NOT NULL DEFAULT 0,
            is_system     INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_ids_bundles_is_blank
            ON db_bundles(is_blank);

        CREATE TABLE IF NOT EXISTS db_drone_definitions (
            id          TEXT PRIMARY KEY,
            name        TEXT NOT NULL,
            description TEXT NOT NULL DEFAULT '',
            graph       TEXT NOT NULL DEFAULT '{\"nodes\":[],\"edges\":[]}',
            viewport    TEXT NOT NULL DEFAULT '{\"x\":0,\"y\":0,\"zoom\":1}',
            created_at  INTEGER NOT NULL DEFAULT 0,
            updated_at  INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_ids_drone_definitions_updated
            ON db_drone_definitions(updated_at DESC);

        CREATE TABLE IF NOT EXISTS db_muxbus_credentials (
            id             TEXT PRIMARY KEY DEFAULT 'global',
            cognito_domain TEXT NOT NULL DEFAULT '',
            client_id      TEXT NOT NULL DEFAULT '',
            access_token   TEXT NOT NULL DEFAULT '',
            refresh_token  TEXT NOT NULL DEFAULT '',
            id_token       TEXT NOT NULL DEFAULT '',
            expires_at     INTEGER NOT NULL DEFAULT 0,
            user_email     TEXT NOT NULL DEFAULT '',
            user_sub       TEXT NOT NULL DEFAULT ''
        );

        CREATE TABLE IF NOT EXISTS db_migrations (
            id          TEXT PRIMARY KEY,
            applied_at  TEXT NOT NULL,
            duration_ms INTEGER NOT NULL DEFAULT 0,
            scope       TEXT NOT NULL DEFAULT 'global'
        );

        CREATE TABLE IF NOT EXISTS db_cron_jobs (
            id            TEXT PRIMARY KEY,
            name          TEXT NOT NULL,
            expression    TEXT NOT NULL,
            prompt        TEXT NOT NULL,
            target        TEXT NOT NULL,
            created_by    TEXT NOT NULL DEFAULT '',
            enabled       INTEGER NOT NULL DEFAULT 1,
            last_fired    INTEGER,
            fire_count    INTEGER NOT NULL DEFAULT 0,
            max_fires     INTEGER,
            created_at    INTEGER NOT NULL DEFAULT 0,
            max_age_secs  INTEGER,
            -- v10 (identity M1b): UID beside the slug, resolved at create
            -- time, dual-written, not yet read. Parity with the shared-store
            -- copy of this table, which is the one the handlers use today.
            target_uid    TEXT NOT NULL DEFAULT '',
            -- v11 (identity M4c-1): the creator's UID, parity with the
            -- shared-store copy.
            created_by_uid TEXT NOT NULL DEFAULT ''
        );
        CREATE INDEX IF NOT EXISTS idx_ids_cron_jobs_enabled
            ON db_cron_jobs(enabled);

        -- Muxqueue: the universal agent work queue. One row = one unit of work
        -- that ANY agent may claim. See
        -- docs/reports/REPORT_UNIVERSAL_AGENT_WORK_QUEUE_2026_09_01.md.
        --
        -- Deliberately placed directly after db_cron_jobs, because it is that
        -- table's sibling: cron is this row with a TIME trigger, this is the
        -- same row with a READINESS trigger. `not_before` makes the
        -- relationship literal.
        --
        -- It belongs in THIS schema (the always-global identity store) and not
        -- in run_shared_store_schema. A per-channel queue would only ever mean
        -- any agent IN THIS CHANNEL can pick it up, which is close to useless
        -- when every local/dev/portable build is its own channel -- see the
        -- report's section 6.2.
        --
        -- Editing note: this whole schema is ONE Rust string literal passed to
        -- execute_batch, so an UNESCAPED double quote anywhere in it --
        -- including inside an SQL comment like this one -- terminates that
        -- literal and breaks the build with errors that point at prose rather
        -- than at code. Escaped ones are fine and already appear above (see
        -- db_drone_definitions default JSON). Apostrophes are fine too: they
        -- are ordinary characters inside a Rust double-quoted string, and
        -- SQLite does not lex quotes inside a comment.
        CREATE TABLE IF NOT EXISTS db_work_queue (
            id            TEXT PRIMARY KEY,
            title         TEXT NOT NULL,
            payload       TEXT NOT NULL,
            kind          TEXT NOT NULL DEFAULT '',
            target_agent  TEXT NOT NULL DEFAULT '',
            target_group  TEXT NOT NULL DEFAULT '',
            priority      INTEGER NOT NULL DEFAULT 0,
            state         TEXT NOT NULL DEFAULT 'open',
            claimed_by    TEXT NOT NULL DEFAULT '',
            claim_expires INTEGER,
            attempts      INTEGER NOT NULL DEFAULT 0,
            max_attempts  INTEGER NOT NULL DEFAULT 3,
            created_by    TEXT NOT NULL DEFAULT '',
            created_at    INTEGER NOT NULL DEFAULT 0,
            updated_at    INTEGER NOT NULL DEFAULT 0,
            not_before    INTEGER,
            result        TEXT NOT NULL DEFAULT '',
            -- v10 (identity M1b): the UID (db_agents.id) beside each slug
            -- column, dual-written, not yet read. target_agent_uid is
            -- resolved ONCE at enqueue (spec §5.4: resolution happens at
            -- authoring time, when a human or model was present, never at
            -- claim time); claimed_by_uid is CARRIED from the claimer's own
            -- AGENTMUX_AGENT_UID. Empty when unknown -- never guessed.
            target_agent_uid TEXT NOT NULL DEFAULT '',
            claimed_by_uid   TEXT NOT NULL DEFAULT '',
            -- v11 (identity M4c-1): the enqueuer's UID from the request's
            -- Caller, beside the created_by name. Empty when unattributed.
            created_by_uid   TEXT NOT NULL DEFAULT ''
        );
        -- The claim query's exact predicate: state + readiness, ordered by
        -- priority then age. Without this the claim UPDATE degrades to a table
        -- scan under contention, which is when it matters most.
        CREATE INDEX IF NOT EXISTS idx_ids_work_queue_claimable
            ON db_work_queue(state, not_before, priority DESC, created_at);
        -- The reaper's predicate (expired leases).
        CREATE INDEX IF NOT EXISTS idx_ids_work_queue_claim_expires
            ON db_work_queue(claim_expires)
            WHERE claim_expires IS NOT NULL;

        CREATE TABLE IF NOT EXISTS db_agent_credentials (
            agent_id       TEXT PRIMARY KEY,
            client_id      TEXT NOT NULL DEFAULT '',
            client_secret  TEXT NOT NULL DEFAULT '',
            token_endpoint TEXT NOT NULL DEFAULT '',
            access_token   TEXT NOT NULL DEFAULT '',
            expires_at     INTEGER NOT NULL DEFAULT 0,
            created_at     INTEGER NOT NULL DEFAULT 0
        );

        CREATE TABLE IF NOT EXISTS db_agent_native_memory (
            agent_id           TEXT NOT NULL,
            filename           TEXT NOT NULL,
            content            TEXT NOT NULL,
            metadata_type      TEXT NOT NULL DEFAULT '',
            size_bytes         INTEGER NOT NULL DEFAULT 0,
            updated_at         INTEGER NOT NULL DEFAULT 0,
            last_seen_path     TEXT NOT NULL DEFAULT '',
            last_seen_mtime_ms INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (agent_id, filename)
        );

        -- v3: schema parity with the v1 native memory mirror above — see
        -- IDENTITY_STORE_SCHEMA_VERSION's own doc comment for why this
        -- store's copy is not (yet) an actively-written duplicate.
        CREATE TABLE IF NOT EXISTS db_agent_native_memory_versions (
            id                 TEXT PRIMARY KEY,
            agent_id           TEXT NOT NULL,
            filename           TEXT NOT NULL,
            content            TEXT NOT NULL,
            content_hash       TEXT NOT NULL,
            parent_version_id  TEXT,
            source             TEXT NOT NULL DEFAULT 'agent_inferred',
            source_detail      TEXT NOT NULL DEFAULT '{}',
            session_id         TEXT NOT NULL DEFAULT '',
            created_at         INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_ids_native_memory_versions_lookup
            ON db_agent_native_memory_versions(agent_id, filename, created_at);

        -- v9: schema parity with the db_bundle_versions table above (own
        -- store) — see IDENTITY_STORE_SCHEMA_VERSION's own doc comment.
        CREATE TABLE IF NOT EXISTS db_bundle_versions (
            id                 TEXT PRIMARY KEY,
            bundle_id          TEXT NOT NULL,
            name               TEXT NOT NULL,
            instructions       TEXT NOT NULL,
            content_hash       TEXT NOT NULL,
            parent_version_id  TEXT,
            source             TEXT NOT NULL DEFAULT 'agent_inferred',
            source_detail      TEXT NOT NULL DEFAULT '{}',
            written_by         TEXT NOT NULL DEFAULT '',
            created_at         INTEGER NOT NULL DEFAULT 0,
            -- v11 (identity M4c-1): parity with the shared-store copy.
            written_by_uid     TEXT NOT NULL DEFAULT ''
        );
        CREATE INDEX IF NOT EXISTS idx_ids_bundle_versions_lookup
            ON db_bundle_versions(bundle_id, created_at);

        -- v6: authoritative skill/MCP-server catalogs — see this constant's
        -- v6 doc comment above. Same column shapes as run_object_schema's
        -- copies; no ref tables here yet (Phase 3/6), so no FK targets exist
        -- to reference them from this store.
        CREATE TABLE IF NOT EXISTS db_skills (
            id          TEXT PRIMARY KEY,
            name        TEXT NOT NULL,
            trigger     TEXT NOT NULL DEFAULT '',
            skill_type  TEXT NOT NULL DEFAULT 'prompt',
            description TEXT NOT NULL DEFAULT '',
            content     TEXT NOT NULL DEFAULT '',
            is_global   INTEGER NOT NULL DEFAULT 0,
            created_at  INTEGER NOT NULL DEFAULT 0,
            updated_at  INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_ids_skills_is_global ON db_skills(is_global);
        -- v8: name-only, matching skill_upsert_unique_global's own dup-check
        -- — see this constant's v8 doc comment. Replaces
        -- idx_ids_skills_global_name_type (dropped just below for an
        -- existing store; a fresh store never creates it in the first
        -- place, since this whole batch runs as one execute_batch).
        CREATE UNIQUE INDEX IF NOT EXISTS idx_ids_skills_global_name
            ON db_skills(name) WHERE is_global = 1;
        DROP INDEX IF EXISTS idx_ids_skills_global_name_type;

        CREATE TABLE IF NOT EXISTS db_mcp_servers (
            id          TEXT PRIMARY KEY,
            name        TEXT NOT NULL,
            transport   TEXT NOT NULL DEFAULT 'stdio',
            config      TEXT NOT NULL DEFAULT '{}',
            is_global   INTEGER NOT NULL DEFAULT 0,
            created_at  INTEGER NOT NULL DEFAULT 0,
            updated_at  INTEGER NOT NULL DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_ids_mcp_servers_is_global ON db_mcp_servers(is_global);
        CREATE UNIQUE INDEX IF NOT EXISTS idx_ids_mcp_servers_global_name
            ON db_mcp_servers(name) WHERE is_global = 1;
        -- Who a catalog server reaches beyond its binds. Added without a
        -- version bump — see IDENTITY_STORE_SCHEMA_VERSION's doc comment.
        CREATE TABLE IF NOT EXISTS db_mcp_server_reach (
            mcp_id        TEXT PRIMARY KEY,
            all_agents    INTEGER NOT NULL DEFAULT 0,
            in_containers INTEGER NOT NULL DEFAULT 0,
            updated_at    INTEGER NOT NULL DEFAULT 0
        );",
    )?;

    // Seed the blank Bundle singleton — same fixed id as objects.db/store.db
    // so cross-version reads never see a missing blank row.
    conn.execute_batch(
        "INSERT OR IGNORE INTO db_bundles
            (id, name, description, is_blank, created_at, updated_at)
         VALUES ('blank', '__blank__', 'Vanilla CLI — no instructions, no context', 1, 0, 0);",
    )?;

    // Additive columns added after this store's tables were first created —
    // same idempotent pattern as run_shared_store_schema's own ALTER loop.
    for stmt in &[
        // db_bundles.is_system: see OBJECT_SCHEMA_VERSION's v27 doc comment.
        "ALTER TABLE db_bundles ADD COLUMN is_system INTEGER NOT NULL DEFAULT 0",
        // v10: identity M1b — UID columns beside the slug columns, dual-written.
        "ALTER TABLE db_work_queue ADD COLUMN target_agent_uid TEXT NOT NULL DEFAULT ''",
        "ALTER TABLE db_work_queue ADD COLUMN claimed_by_uid TEXT NOT NULL DEFAULT ''",
        "ALTER TABLE db_cron_jobs ADD COLUMN target_uid TEXT NOT NULL DEFAULT ''",
        // v11: identity M4c-1 — the actor's UID beside each actor name.
        "ALTER TABLE db_work_queue ADD COLUMN created_by_uid TEXT NOT NULL DEFAULT ''",
        "ALTER TABLE db_cron_jobs ADD COLUMN created_by_uid TEXT NOT NULL DEFAULT ''",
        "ALTER TABLE db_bundle_versions ADD COLUMN written_by_uid TEXT NOT NULL DEFAULT ''",
    ] {
        if let Err(e) = conn.execute_batch(stmt) {
            let msg = e.to_string();
            if !msg.contains("duplicate column") {
                return Err(e.into());
            }
        }
    }

    Ok(())
}

/// Initialize the FileStore schema. Creates the wave_file and file_data
/// tables. Already a flat single-DDL store — unaffected by the
/// `objects.db` flattening.
pub fn run_filestore_migrations(conn: &Connection) -> Result<(), StoreError> {
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS db_wave_file (
            zoneid TEXT NOT NULL,
            name TEXT NOT NULL,
            size INTEGER NOT NULL DEFAULT 0,
            createdts INTEGER NOT NULL DEFAULT 0,
            modts INTEGER NOT NULL DEFAULT 0,
            opts TEXT NOT NULL DEFAULT '{}',
            meta TEXT NOT NULL DEFAULT '{}',
            PRIMARY KEY (zoneid, name)
        );

        CREATE TABLE IF NOT EXISTS db_file_data (
            zoneid TEXT NOT NULL,
            name TEXT NOT NULL,
            partidx INTEGER NOT NULL,
            data BLOB NOT NULL,
            PRIMARY KEY (zoneid, name, partidx)
        );",
    )?;
    // Line counter (Phase 5a-2, SPEC_AGENT_PANE_BOUNDED_LIVE_WINDOW_MIGRATION_
    // 2026_09_23.md §6.3.7; see `filestore/counter.rs`). Additive and
    // nullable, and deliberately WITHOUT a FILESTORE_SCHEMA_VERSION bump: the
    // global transcript store is shared by every srv instance on the machine,
    // and a bump would make older builds refuse to open it. Older builds
    // insert rows without these columns (NULL = not counted) and write
    // without maintaining them, which the counter detects. "duplicate
    // column" is expected when another instance added them first.
    for stmt in &[
        "ALTER TABLE db_wave_file ADD COLUMN gen TEXT",
        "ALTER TABLE db_wave_file ADD COLUMN lines INTEGER",
        "ALTER TABLE db_wave_file ADD COLUMN lines_size INTEGER",
        "ALTER TABLE db_wave_file ADD COLUMN lines_tail INTEGER",
        "ALTER TABLE db_wave_file ADD COLUMN lines_modts INTEGER",
        // Bumped (by the triggers below) by every write that changes bytes
        // already in the file — not by appends — and the value an epoch was
        // counted at. An epoch is valid only while they are equal.
        "ALTER TABLE db_wave_file ADD COLUMN rev INTEGER",
        "ALTER TABLE db_wave_file ADD COLUMN lines_rev INTEGER",
        // A random identity per row, set by the insert trigger below: a file
        // deleted and re-created is a different incarnation even if every
        // other column (and the creation millisecond) comes out the same.
        "ALTER TABLE db_wave_file ADD COLUMN incarnation BLOB",
    ] {
        if let Err(e) = conn.execute_batch(stmt) {
            if !e.to_string().contains("duplicate column") {
                return Err(e.into());
            }
        }
    }
    // `rev` is maintained by triggers, not by this code's writes, because
    // triggers are part of the database: they fire for every connection,
    // including older builds that know nothing about the counter. Any write
    // that changes existing bytes bumps it:
    //   - a part deleted (write_file's replace, a delete);
    //   - a part written over with bytes that aren't a pure extension of it
    //     (REPLACE INTO / UPDATE). Appending extends the last part, which
    //     keeps it a prefix, so appends — ours or an older build's — don't.
    // REPLACE INTO doesn't fire DELETE triggers (recursive_triggers is off,
    // SQLite's default; nothing here enables it), so an append can't reach
    // the delete trigger. A trigger on a deleted row's parts finds no row.
    conn.execute_batch(
        "-- Versioned name: CREATE TRIGGER IF NOT EXISTS never updates an
         -- existing definition, so a changed rule gets a new name and the
         -- old one is dropped.
         DROP TRIGGER IF EXISTS db_file_data_rev_insert;
         CREATE TRIGGER IF NOT EXISTS db_file_data_rev_insert_v2
         BEFORE INSERT ON db_file_data
         WHEN EXISTS (
             SELECT 1 FROM db_file_data d
             WHERE d.zoneid = NEW.zoneid AND d.name = NEW.name AND d.partidx = NEW.partidx
               AND substr(NEW.data, 1, length(d.data)) IS NOT d.data
         )
         -- A NEW part inside the range the file already claims: bytes the
         -- file declared (and a reader may have read as missing) are being
         -- filled in, not appended. Every writer inserts appended parts
         -- before raising `size`, so an append's new parts always start at
         -- or past it. Older builds' write_file raises `size` first and
         -- inserts after (Codex on #3631). 65536 is PART_DATA_SIZE.
         OR (
             NOT EXISTS (
                 SELECT 1 FROM db_file_data d
                 WHERE d.zoneid = NEW.zoneid AND d.name = NEW.name AND d.partidx = NEW.partidx
             )
             AND NEW.partidx * 65536 < (
                 SELECT COALESCE(MAX(w.size), 0) FROM db_wave_file w
                 WHERE w.zoneid = NEW.zoneid AND w.name = NEW.name
             )
         )
         BEGIN
             UPDATE db_wave_file SET rev = COALESCE(rev, 0) + 1
             WHERE zoneid = NEW.zoneid AND name = NEW.name;
         END;

         CREATE TRIGGER IF NOT EXISTS db_file_data_rev_update
         BEFORE UPDATE OF data ON db_file_data
         WHEN substr(NEW.data, 1, length(OLD.data)) IS NOT OLD.data
         BEGIN
             UPDATE db_wave_file SET rev = COALESCE(rev, 0) + 1
             WHERE zoneid = OLD.zoneid AND name = OLD.name;
         END;

         CREATE TRIGGER IF NOT EXISTS db_file_data_rev_delete
         AFTER DELETE ON db_file_data
         BEGIN
             UPDATE db_wave_file SET rev = COALESCE(rev, 0) + 1
             WHERE zoneid = OLD.zoneid AND name = OLD.name;
         END;

         -- Every row, whoever inserts it (older builds included), gets 64
         -- random bits of identity. A delete + re-create in the same
         -- millisecond with the same size and bytes is still a different
         -- incarnation (Codex on #3631).
         CREATE TRIGGER IF NOT EXISTS db_wave_file_incarnation
         AFTER INSERT ON db_wave_file
         BEGIN
             UPDATE db_wave_file SET incarnation = randomblob(8)
             WHERE zoneid = NEW.zoneid AND name = NEW.name;
         END;

         UPDATE db_wave_file SET incarnation = randomblob(8) WHERE incarnation IS NULL;",
    )?;
    Ok(())
}

/// Initialize the saga durability schema (`saga` + `saga_step` tables and
/// their indexes). See `docs/specs/SPEC_SAGA_DURABILITY_2026-05-01.md` §2.2.
/// Already a flat single-DDL store.
pub fn run_saga_log_migrations(conn: &Connection) -> Result<(), StoreError> {
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS saga (
            saga_id        INTEGER PRIMARY KEY,
            name           TEXT NOT NULL,
            state          TEXT NOT NULL,
            started_at     INTEGER NOT NULL,
            terminal_at    INTEGER,
            failure_reason TEXT,
            input_json     TEXT NOT NULL
        );

        CREATE TABLE IF NOT EXISTS saga_step (
            saga_id     INTEGER NOT NULL REFERENCES saga(saga_id),
            step_index  INTEGER NOT NULL,
            name        TEXT NOT NULL,
            state       TEXT NOT NULL,
            cmd_json    TEXT NOT NULL,
            output_json TEXT,
            started_at  INTEGER NOT NULL,
            ended_at    INTEGER,
            PRIMARY KEY (saga_id, step_index)
        );

        CREATE INDEX IF NOT EXISTS saga_state_idx
            ON saga(state) WHERE state IN ('running', 'compensating');
        CREATE INDEX IF NOT EXISTS saga_terminal_idx
            ON saga(terminal_at);",
    )?;
    Ok(())
}

/// `PRAGMA user_version` tripwire (AUDIT_SQLITE_SYSTEMS §8.5).
///
/// Compare the file's `user_version` to `current` and refuse to open
/// the database if it was written by a NEWER binary.
///
/// This is the forward-compat **safety lock** from the channels design
/// (`SPEC_DATA_CHANNELS_2026_05_24.md` §3.3). Within a channel,
/// multiple released AgentMux versions share one data dir; the lock
/// keeps an older binary from writing into a schema laid down by a
/// newer binary. Same discipline as Chrome's profile-too-new check
/// and Postgres's catalog version mismatch.
///
/// **Must be called BEFORE `run_*_schema` / `run_*_migrations`.** The
/// `run_*` functions include mutating steps (legacy-table renames,
/// seed inserts, `CREATE TABLE` for new tables the older binary
/// doesn't have), so if we ran them first and only then checked the
/// version, a downgraded binary could still alter a newer database
/// before the error fires — breaking the "reject without touching
/// disk" invariant the lock is meant to guarantee (codex P1 on PR
/// #1029).
///
/// Read-only: only `PRAGMA user_version` query, no writes.
/// [`stamp_version`] does the corresponding write AFTER migrations
/// complete successfully.
///
/// Recovery for the user when this returns `SchemaTooNew`: upgrade
/// AgentMux to a version ≥ `found`, or set
/// `AGENTMUX_CHANNEL=<other>` to land in a different channel dir.
/// The data on disk is preserved either way — this function never
/// modifies the database on the rejected path.
pub fn check_schema_compat(
    conn: &Connection,
    current: i64,
    db_label: &str,
) -> Result<(), StoreError> {
    let found: i64 = conn.query_row("PRAGMA user_version", [], |row| row.get(0))?;
    if found > current {
        warn!(
            db = db_label,
            found, expected = current,
            "database user_version is newer than this build — refusing \
             to open. Upgrade AgentMux or switch channels.",
        );
        return Err(StoreError::SchemaTooNew {
            db: db_label.to_string(),
            found,
            expected: current,
        });
    }
    Ok(())
}

/// Stamp the database's `user_version` PRAGMA to `current`. Called
/// AFTER `run_*_schema` succeeds, paired with a prior
/// [`check_schema_compat`] that gated the migrations on the
/// caller-binary speaking a compatible (or newer) schema version.
///
/// Splitting the read from the write makes the safety-lock order
/// explicit at every call site:
///
/// ```ignore
/// check_schema_compat(&conn, OBJECT_SCHEMA_VERSION, "objects.db")?;
/// run_object_schema(&conn)?;
/// stamp_version(&conn, OBJECT_SCHEMA_VERSION)?;
/// ```
pub fn stamp_version(conn: &Connection, current: i64) -> Result<(), StoreError> {
    conn.execute_batch(&format!("PRAGMA user_version = {current};"))?;
    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;

    /// Every table the flat `objects.db` schema must contain.
    const EXPECTED_TABLES: &[&str] = &[
        "db_client",
        "db_window",
        "db_workspace",
        "db_tab",
        "db_layout",
        "db_block",
        "db_temp",
        "db_agent_content",
        "db_agent_skills",
        "db_agent_history",
        "db_accounts",
        "db_agent_identity_links",
        "db_bundles",
        "db_agents",
        "db_drone_definitions",
        "db_drone_runs",
    ];

    fn table_exists(conn: &Connection, name: &str) -> bool {
        let count: i64 = conn
            .query_row(
                "SELECT count(*) FROM sqlite_master WHERE type='table' AND name=?1",
                [name],
                |row| row.get(0),
            )
            .unwrap();
        count == 1
    }

    fn index_exists(conn: &Connection, name: &str) -> bool {
        let count: i64 = conn
            .query_row(
                "SELECT count(*) FROM sqlite_master WHERE type='index' AND name=?1",
                [name],
                |row| row.get(0),
            )
            .unwrap();
        count == 1
    }

    fn column_exists(conn: &Connection, table: &str, column: &str) -> bool {
        let mut stmt = conn.prepare(&format!("PRAGMA table_info({table})")).unwrap();
        let names: Vec<String> = stmt
            .query_map([], |row| row.get::<_, String>(1))
            .unwrap()
            .filter_map(Result::ok)
            .collect();
        names.iter().any(|n| n == column)
    }

    #[test]
    fn test_object_schema_creates_all_tables_and_singletons() {
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
        run_object_schema(&conn).unwrap();

        for table in EXPECTED_TABLES {
            assert!(table_exists(&conn, table), "{table} should exist");
        }
        // De-forged + bundle indexes.
        for idx in &[
            "idx_agent_history_agent_date",
            "idx_agent_identity_links_account",
            "idx_accounts_provider",
            "idx_bundles_is_blank",
            "idx_agents_is_template",
            "idx_agents_parent_template_id",
            "idx_agents_is_seeded",
            "idx_drone_definitions_updated",
            "idx_drone_runs_status",
        ] {
            assert!(index_exists(&conn, idx), "{idx} should exist");
        }

        // Blank singleton seeded.
        let mem_blank: i64 = conn
            .query_row(
                "SELECT count(*) FROM db_bundles WHERE id='blank' AND is_blank=1",
                [],
                |row| row.get(0),
            )
            .unwrap();
        assert_eq!(mem_blank, 1, "blank Bundle singleton should be seeded");
    }

    #[test]
    fn test_object_schema_idempotent() {
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
        run_object_schema(&conn).unwrap();
        run_object_schema(&conn).unwrap(); // second pass must not error

        // Singleton stays unique.
        let mem_count: i64 = conn
            .query_row(
                "SELECT count(*) FROM db_bundles WHERE id='blank'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert_eq!(mem_count, 1);
    }

    #[test]
    fn test_object_schema_has_model_vendor_base_url_on_db_agents() {
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
        run_object_schema(&conn).unwrap();
        // Second pass: the ALTER TABLE array's duplicate-column guard must
        // not error when the column already exists (fresh installs get it
        // via CREATE TABLE; the ALTER is for upgrading pre-v15 databases).
        // Was "on_both_tables" — db_agent_definitions is gone as of v32, so
        // this is db_agents-only now (see OBJECT_SCHEMA_VERSION's v32 doc
        // comment).
        run_object_schema(&conn).unwrap();

        assert!(column_exists(&conn, "db_agents", "model_vendor_base_url"));
    }

    #[test]
    fn test_object_schema_has_auto_continue_enabled_on_db_agents() {
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
        run_object_schema(&conn).unwrap();
        // Second pass: the ALTER TABLE array's duplicate-column guard must
        // not error when the column already exists (fresh installs get it
        // via CREATE TABLE; the ALTER is for upgrading pre-v17 databases).
        // Was "on_both_tables" — see the v15 test just above for why.
        run_object_schema(&conn).unwrap();

        assert!(column_exists(&conn, "db_agents", "auto_continue_enabled"));
    }

    #[test]
    fn test_db_bundles_has_instructions_by_provider_in_both_schemas() {
        // ABF v0.2 §2.2 (v16): db_bundles lives in both objects.db (via
        // run_object_schema) and the shared store.db (via
        // run_shared_store_schema) — mirrors the db_agent_credentials /
        // db_agent_native_memory both-schemas precedent (v13/v14).
        let object_conn = Connection::open_in_memory().unwrap();
        object_conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
        run_object_schema(&object_conn).unwrap();
        run_object_schema(&object_conn).unwrap(); // idempotent second pass
        assert!(column_exists(&object_conn, "db_bundles", "instructions_by_provider"));

        let shared_conn = Connection::open_in_memory().unwrap();
        shared_conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
        run_shared_store_schema(&shared_conn).unwrap();
        run_shared_store_schema(&shared_conn).unwrap();
        assert!(column_exists(&shared_conn, "db_bundles", "instructions_by_provider"));
    }

    #[test]
    fn test_db_skills_and_mcp_servers_exist_in_both_object_and_identity_schemas() {
        // Phase 2 of SPEC_DURABLE_BINDINGS_2026_09_10.md (v6): the identity
        // store gains its own db_skills/db_mcp_servers, becoming
        // authoritative — but the channel-store copies (run_object_schema)
        // are NOT removed, per that spec's §5.1/§8 item 3. Pins both halves
        // of that claim, not just the new one: it would be easy to
        // accidentally satisfy only the identity-store half of this and
        // silently drop the channel-store fallback.
        let object_conn = Connection::open_in_memory().unwrap();
        run_object_schema(&object_conn).unwrap();
        assert!(table_exists(&object_conn, "db_skills"), "channel store must keep db_skills");
        assert!(table_exists(&object_conn, "db_mcp_servers"), "channel store must keep db_mcp_servers");

        let identity_conn = Connection::open_in_memory().unwrap();
        run_identity_store_schema(&identity_conn).unwrap();
        run_identity_store_schema(&identity_conn).unwrap(); // idempotent second pass
        assert!(table_exists(&identity_conn, "db_skills"), "identity store must have db_skills");
        assert!(table_exists(&identity_conn, "db_mcp_servers"), "identity store must have db_mcp_servers");
        assert!(index_exists(&identity_conn, "idx_ids_skills_is_global"));
        assert!(index_exists(&identity_conn, "idx_ids_mcp_servers_is_global"));

        // No ref tables here yet — those are Phase 3/6, not Phase 2. Schema
        // for a phase that hasn't shipped would be its own kind of drift.
        assert!(!table_exists(&identity_conn, "db_agent_skills_ref"));
        assert!(!table_exists(&identity_conn, "db_bundle_skills_ref"));
        assert!(!table_exists(&identity_conn, "db_agent_mcp_ref"));
        assert!(!table_exists(&identity_conn, "db_bundle_mcp_ref"));
    }

    #[test]
    fn test_object_schema_omits_dead_tables() {
        let conn = Connection::open_in_memory().unwrap();
        run_object_schema(&conn).unwrap();
        for dead in DEAD_TABLE_DROPS {
            assert!(!table_exists(&conn, dead), "{dead} must not be created");
        }
        // Legacy forge names are never created either.
        for (legacy, _) in LEGACY_TABLE_RENAMES {
            assert!(
                !table_exists(&conn, legacy),
                "legacy {legacy} must not be created by the flat schema"
            );
        }
    }

    #[test]
    fn test_adopt_legacy_renames_forge_tables() {
        // Simulate a pre-flatten (post-v11) dev DB: legacy forge table
        // names + a dead workflow table, with seeded rows.
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
        conn.execute_batch(
            "CREATE TABLE db_forge_agents (
                id TEXT PRIMARY KEY, slug TEXT NOT NULL DEFAULT '', name TEXT NOT NULL,
                icon TEXT NOT NULL DEFAULT '✦', provider TEXT NOT NULL,
                description TEXT NOT NULL DEFAULT '', working_directory TEXT NOT NULL DEFAULT '',
                shell TEXT NOT NULL DEFAULT '', provider_flags TEXT NOT NULL DEFAULT '',
                auto_start INTEGER NOT NULL DEFAULT 0, restart_on_crash INTEGER NOT NULL DEFAULT 0,
                idle_timeout_minutes INTEGER NOT NULL DEFAULT 0,
                agent_type TEXT NOT NULL DEFAULT 'standalone', environment TEXT NOT NULL DEFAULT '',
                agent_bus_id TEXT NOT NULL DEFAULT '', is_seeded INTEGER NOT NULL DEFAULT 0,
                accounts TEXT NOT NULL DEFAULT '',
                parent_id TEXT NOT NULL DEFAULT '', branch_label TEXT NOT NULL DEFAULT '',
                created_at INTEGER NOT NULL DEFAULT 0
            );
            CREATE UNIQUE INDEX idx_forge_agents_slug ON db_forge_agents(slug);
            INSERT INTO db_forge_agents (id, slug, name, provider)
                VALUES ('a1', 'coder', 'Coder', 'claude');

            CREATE TABLE db_workflow_definitions (id TEXT PRIMARY KEY);",
        )
        .unwrap();

        run_object_schema(&conn).unwrap();

        // Renamed, data preserved.
        assert!(table_exists(&conn, "db_agent_definitions"));
        assert!(!table_exists(&conn, "db_forge_agents"));
        let name: String = conn
            .query_row(
                "SELECT name FROM db_agent_definitions WHERE id='a1'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert_eq!(name, "Coder");
        // Old index dropped. No new index takes its place — db_agent_definitions
        // is dropped whole as of v32 (OBJECT_SCHEMA_VERSION's v32 doc comment),
        // so the flat schema no longer declares a unique index for it; the
        // renamed table exists only transiently, until the drop migration
        // that runs later in the same upgrade removes it.
        assert!(!index_exists(&conn, "idx_forge_agents_slug"));
        // Dead table dropped.
        assert!(!table_exists(&conn, "db_workflow_definitions"));
    }

    #[test]
    fn test_adopt_legacy_is_noop_on_fresh_db() {
        let conn = Connection::open_in_memory().unwrap();
        run_object_schema(&conn).unwrap();
        // Re-running schema (which re-runs adopt) on the already-flat DB
        // leaves the de-forged tables intact and creates no legacy names.
        // db_agent_definitions itself is gone from the flat schema as of
        // v32, so a live de-forged table that's still declared (db_agents)
        // stands in for "the flat schema is present, nothing to adopt".
        run_object_schema(&conn).unwrap();
        assert!(table_exists(&conn, "db_agents"));
        assert!(!table_exists(&conn, "db_forge_agents"));
    }

    #[test]
    fn test_adopt_legacy_both_tables_present_is_non_destructive() {
        // Downgrade-roundtrip: a flat DB (db_agent_definitions) where a
        // pre-flatten build later re-created db_forge_agents and wrote a
        // row. The adopt step must NOT drop the legacy table — silent
        // data loss is the bug class behind PR #933's Codex P1.
        let conn = Connection::open_in_memory().unwrap();
        run_object_schema(&conn).unwrap();
        // db_agent_definitions is gone from the flat schema as of v32, so
        // this fixture has to construct the "downgrade-era db already has
        // the de-forged table too" scenario by hand — minimal shape, this
        // test only cares about existence + data survival, not full column
        // parity.
        conn.execute_batch(
            "CREATE TABLE db_agent_definitions (id TEXT PRIMARY KEY, name TEXT NOT NULL);
             CREATE TABLE db_forge_agents (id TEXT PRIMARY KEY, name TEXT NOT NULL);
             INSERT INTO db_forge_agents (id, name) VALUES ('downgrade-era', 'Recover Me');",
        )
        .unwrap();

        run_object_schema(&conn).unwrap();

        // Legacy table left intact — data recoverable, not dropped.
        assert!(table_exists(&conn, "db_forge_agents"));
        let name: String = conn
            .query_row(
                "SELECT name FROM db_forge_agents WHERE id='downgrade-era'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert_eq!(name, "Recover Me");
        // Flat table still present and authoritative.
        assert!(table_exists(&conn, "db_agent_definitions"));
    }

    #[test]
    fn test_adopt_legacy_fk_cascade_survives_rename() {
        // A renamed parent must keep cascading into renamed children.
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
        conn.execute_batch(
            "CREATE TABLE db_forge_agents (
                id TEXT PRIMARY KEY, slug TEXT NOT NULL DEFAULT '', name TEXT NOT NULL,
                icon TEXT NOT NULL DEFAULT '✦', provider TEXT NOT NULL,
                description TEXT NOT NULL DEFAULT '', working_directory TEXT NOT NULL DEFAULT '',
                shell TEXT NOT NULL DEFAULT '', provider_flags TEXT NOT NULL DEFAULT '',
                auto_start INTEGER NOT NULL DEFAULT 0, restart_on_crash INTEGER NOT NULL DEFAULT 0,
                idle_timeout_minutes INTEGER NOT NULL DEFAULT 0,
                agent_type TEXT NOT NULL DEFAULT 'standalone', environment TEXT NOT NULL DEFAULT '',
                agent_bus_id TEXT NOT NULL DEFAULT '', is_seeded INTEGER NOT NULL DEFAULT 0,
                accounts TEXT NOT NULL DEFAULT '',
                parent_id TEXT NOT NULL DEFAULT '', branch_label TEXT NOT NULL DEFAULT '',
                created_at INTEGER NOT NULL DEFAULT 0
            );
            CREATE TABLE db_forge_content (
                agent_id TEXT NOT NULL, content_type TEXT NOT NULL,
                content TEXT NOT NULL DEFAULT '', updated_at INTEGER NOT NULL DEFAULT 0,
                PRIMARY KEY (agent_id, content_type),
                FOREIGN KEY (agent_id) REFERENCES db_forge_agents(id) ON DELETE CASCADE
            );
            INSERT INTO db_forge_agents (id, name, provider) VALUES ('a1', 'Coder', 'claude');
            INSERT INTO db_forge_content (agent_id, content_type, content)
                VALUES ('a1', 'soul', 'hello');",
        )
        .unwrap();

        run_object_schema(&conn).unwrap();

        conn.execute("DELETE FROM db_agent_definitions WHERE id='a1'", [])
            .unwrap();
        let remaining: i64 = conn
            .query_row(
                "SELECT count(*) FROM db_agent_content WHERE agent_id='a1'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert_eq!(
            remaining, 0,
            "FK cascade must survive the forge→agent table rename"
        );
    }

    #[test]
    fn legacy_column_added_to_a_still_present_db_agent_definitions_via_alter() {
        // Codex P1 on #3096: the flat schema no longer CREATEs
        // db_agent_definitions (dropped as of v32), but several pending
        // migrations (m0007, m0025, m0028) call Store::open() internally —
        // running run_object_schema — before m0029 (numbered last) actually
        // drops the table in the same upgrade pass. A store old enough to
        // still carry the table but missing a column those migrations read
        // (e.g. user_hidden) must still get it ALTERed in, or their own
        // reads fail with "no such column" before they ever reach m0029.
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
        conn.execute_batch(
            "CREATE TABLE db_agent_definitions (
                id TEXT PRIMARY KEY, slug TEXT NOT NULL DEFAULT '',
                name TEXT NOT NULL, icon TEXT NOT NULL DEFAULT '✦',
                provider TEXT NOT NULL,
                description TEXT NOT NULL DEFAULT '',
                working_directory TEXT NOT NULL DEFAULT '',
                shell TEXT NOT NULL DEFAULT '',
                provider_flags TEXT NOT NULL DEFAULT '',
                auto_start INTEGER NOT NULL DEFAULT 0,
                restart_on_crash INTEGER NOT NULL DEFAULT 0,
                idle_timeout_minutes INTEGER NOT NULL DEFAULT 0,
                agent_type TEXT NOT NULL DEFAULT 'standalone',
                environment TEXT NOT NULL DEFAULT '',
                agent_bus_id TEXT NOT NULL DEFAULT '',
                is_seeded INTEGER NOT NULL DEFAULT 0,
                accounts TEXT NOT NULL DEFAULT '',
                parent_id TEXT NOT NULL DEFAULT '',
                branch_label TEXT NOT NULL DEFAULT '',
                created_at INTEGER NOT NULL DEFAULT 0
            );
            INSERT INTO db_agent_definitions (id, name, provider, is_seeded)
                VALUES ('pre-existing', 'Old Template', 'claude', 1);",
        )
        .unwrap();

        run_object_schema(&conn).unwrap();
        // Idempotent — second pass must not error and must not
        // re-default existing rows.
        run_object_schema(&conn).unwrap();

        let hidden: i64 = conn
            .query_row(
                "SELECT user_hidden FROM db_agent_definitions WHERE id='pre-existing'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert_eq!(
            hidden, 0,
            "ALTER must default existing rows to 0 (visible), never to 1",
        );
    }

    #[test]
    fn legacy_alter_is_skipped_once_db_agent_definitions_is_gone() {
        // The other half of the gate: once the table doesn't exist at all
        // (a fresh v32+ install, or an upgraded store after m0029 has
        // already dropped it), the conditional ALTER block must not even
        // attempt the statement — "no such table" is not "duplicate
        // column" and would not be swallowed.
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch("PRAGMA foreign_keys=ON;").unwrap();
        run_object_schema(&conn).unwrap();
        assert!(!table_exists(&conn, "db_agent_definitions"));
    }

    #[test]
    fn stamp_version_writes_pragma() {
        let conn = Connection::open_in_memory().unwrap();
        stamp_version(&conn, OBJECT_SCHEMA_VERSION).unwrap();
        let v: i64 = conn
            .query_row("PRAGMA user_version", [], |r| r.get(0))
            .unwrap();
        assert_eq!(v, OBJECT_SCHEMA_VERSION);
    }

    #[test]
    fn check_schema_compat_refuses_newer_db_without_writing() {
        // `SPEC_DATA_CHANNELS_2026_05_24.md` §3.3 safety lock — if the
        // DB on disk was stamped by a newer AgentMux binary, this one
        // MUST refuse to open it. The split into
        // check_schema_compat + stamp_version (codex P1 on #1029)
        // ensures the check runs BEFORE any migration side effects:
        // legacy-table rename + seed-insert in `run_object_schema` are
        // mutating, and if we ran them first we'd partially alter a
        // newer DB before the error fired.
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch("PRAGMA user_version = 99;").unwrap();
        let err = check_schema_compat(&conn, OBJECT_SCHEMA_VERSION, "objects.db")
            .expect_err("expected refusal");
        match err {
            StoreError::SchemaTooNew { db, found, expected } => {
                assert_eq!(db, "objects.db");
                assert_eq!(found, 99);
                assert_eq!(expected, OBJECT_SCHEMA_VERSION);
            }
            other => panic!("expected SchemaTooNew, got {other:?}"),
        }
        // Crucially, `check_schema_compat` made NO writes. The
        // `user_version` is still 99. (Before the split — when the
        // single function combined the check with the write — this
        // invariant held only by virtue of the early `return Err`
        // skipping the stamp; with the split the function is
        // structurally read-only, eliminating the risk that a
        // future refactor reintroduces a downgrade-corrupt path.)
        let v: i64 = conn
            .query_row("PRAGMA user_version", [], |r| r.get(0))
            .unwrap();
        assert_eq!(v, 99, "rejected DB must not have its user_version stamp overwritten");
    }

    #[test]
    fn check_schema_compat_accepts_equal_or_lower_without_writing() {
        // check_schema_compat must NEVER write — it just gates
        // migrations. The on-disk `user_version` is untouched by it,
        // regardless of whether the verdict is accept or reject.
        // stamp_version is the only thing that writes, and only after
        // migrations succeed.

        // Equal: check passes silently.
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch(&format!("PRAGMA user_version = {};", OBJECT_SCHEMA_VERSION))
            .unwrap();
        check_schema_compat(&conn, OBJECT_SCHEMA_VERSION, "objects.db").unwrap();
        let v: i64 = conn
            .query_row("PRAGMA user_version", [], |r| r.get(0))
            .unwrap();
        assert_eq!(v, OBJECT_SCHEMA_VERSION);

        // Lower (forward-migration path): check passes, version stays
        // at the OLD value — stamp_version is what bumps it after
        // migrations.
        let conn = Connection::open_in_memory().unwrap();
        conn.execute_batch("PRAGMA user_version = 1;").unwrap();
        check_schema_compat(&conn, OBJECT_SCHEMA_VERSION, "objects.db").unwrap();
        let v: i64 = conn
            .query_row("PRAGMA user_version", [], |r| r.get(0))
            .unwrap();
        assert_eq!(v, 1, "check_schema_compat must not write to user_version");

        // Then stamp_version bumps it as the post-migration step.
        stamp_version(&conn, OBJECT_SCHEMA_VERSION).unwrap();
        let v: i64 = conn
            .query_row("PRAGMA user_version", [], |r| r.get(0))
            .unwrap();
        assert_eq!(v, OBJECT_SCHEMA_VERSION);
    }

    #[test]
    fn test_filestore_migrations_idempotent() {
        let conn = Connection::open_in_memory().unwrap();
        run_filestore_migrations(&conn).unwrap();
        run_filestore_migrations(&conn).unwrap();
        assert!(table_exists(&conn, "db_wave_file"));
        assert!(table_exists(&conn, "db_file_data"));
    }

    #[test]
    fn test_saga_log_migrations_idempotent() {
        let conn = Connection::open_in_memory().unwrap();
        run_saga_log_migrations(&conn).unwrap();
        run_saga_log_migrations(&conn).unwrap();
        assert!(table_exists(&conn, "saga"));
        assert!(table_exists(&conn, "saga_step"));
    }

    /// Identity M4c-1: each store's actor-UID columns exist on a fresh
    /// store, and an existing store at the previous version gains them on
    /// reopen — through each store's real `open`, so the flat CREATE, the
    /// ALTER loop and the version bump are all exercised. A row written
    /// before the upgrade reads the column's default, `''` (unknown).
    #[test]
    fn m4c1_actor_uid_columns_on_fresh_and_upgraded_stores() {
        use super::super::store::Store;
        type Open = fn(&std::path::Path) -> Result<Store, StoreError>;
        let cases: [(&str, Open, i64, i64, &[(&str, &str)]); 3] = [
            (
                "identity-store.db",
                Store::open_identity_store,
                10,
                IDENTITY_STORE_SCHEMA_VERSION,
                &[
                    ("db_work_queue", "created_by_uid"),
                    ("db_cron_jobs", "created_by_uid"),
                    ("db_bundle_versions", "written_by_uid"),
                ],
            ),
            (
                "store.db",
                Store::open_shared,
                11,
                SHARED_STORE_SCHEMA_VERSION,
                &[
                    ("db_cron_jobs", "created_by_uid"),
                    ("db_bundle_versions", "written_by_uid"),
                ],
            ),
            (
                "objects.db",
                Store::open,
                38,
                OBJECT_SCHEMA_VERSION,
                &[("db_bundle_versions", "written_by_uid")],
            ),
        ];
        let dir = tempfile::tempdir().unwrap();
        for (file, open, old, new, cols) in cases {
            // M4c-1 was one bump each; later bumps (objects v40, durable
            // jekt) keep the upgrade from `old` covered.
            assert!(new > old, "{file}: the current version is past M4c-1's base");
            let user_version = |c: &Connection| -> i64 {
                c.query_row("PRAGMA user_version", [], |r| r.get(0))
                    .unwrap()
            };

            // Fresh.
            let fresh = dir.path().join(format!("fresh-{file}"));
            drop(open(&fresh).unwrap());
            let conn = Connection::open(&fresh).unwrap();
            for (table, col) in cols {
                assert!(
                    column_exists(&conn, table, col),
                    "fresh {file}: {table}.{col}"
                );
            }
            assert_eq!(user_version(&conn), new);
            drop(conn);

            // Upgraded: the same store as the previous version left it —
            // the columns absent, stamped `old` — with a version row in it.
            let path = dir.path().join(file);
            drop(open(&path).unwrap());
            let conn = Connection::open(&path).unwrap();
            for (table, col) in cols {
                // Rebuilt without the column: SQLite's `DROP COLUMN` fails
                // to re-parse these tables' commented CREATE text.
                let keep: Vec<String> = conn
                    .prepare(&format!("PRAGMA table_info({table})"))
                    .unwrap()
                    .query_map([], |r| r.get::<_, String>(1))
                    .unwrap()
                    .map(Result::unwrap)
                    .filter(|c| c != col)
                    .collect();
                conn.execute_batch(&format!(
                    "CREATE TABLE m4c1_old AS SELECT {} FROM {table};
                     DROP TABLE {table};
                     ALTER TABLE m4c1_old RENAME TO {table};",
                    keep.join(", ")
                ))
                .unwrap();
                assert!(!column_exists(&conn, table, col));
            }
            conn.execute_batch(&format!(
                "PRAGMA user_version = {old};
                 INSERT INTO db_bundle_versions
                     (id, bundle_id, name, instructions, content_hash)
                 VALUES ('v-old', 'b', 'n', 'i', 'h');"
            ))
            .unwrap();
            drop(conn);

            drop(open(&path).unwrap());
            let conn = Connection::open(&path).unwrap();
            for (table, col) in cols {
                assert!(
                    column_exists(&conn, table, col),
                    "upgraded {file}: {table}.{col}"
                );
            }
            assert_eq!(user_version(&conn), new, "{file}");
            let uid: String = conn
                .query_row(
                    "SELECT written_by_uid FROM db_bundle_versions WHERE id = 'v-old'",
                    [],
                    |r| r.get(0),
                )
                .unwrap();
            assert_eq!(
                uid, "",
                "{file}: a pre-upgrade row is unknown, never guessed"
            );
        }
    }
}