fix(agent-org): preserve safe downgrade across runtime schema redesign

[ShiboSheng]

Effort

Delivery boundary	P50	P90	Substantive files
Separate the canonical runtime store from the legacy downgrade surface, isolate definition files, and verify upgrade → downgrade → re-upgrade	700 review lines	1,350 review lines	30–38

Problem

The Agent Org redesign changes the persisted shape of agent_org_runs and the Team definition file. A user can upgrade from an official v1.0.x–v1.2.5 build to a redesigned build, use Agent Org, and later run v1.2.5 again against the same ~/.orgii data directory.

The old binary has hard-coded SQL for the public 15-column agent_org_runs schema, including completed_at. When the same table now contains the redesigned schema, v1.2.5 repeatedly fails during sidebar aggregation:

[RPC:session_aggregate_list] no such column: completed_at

The inverse mismatch also occurs when a redesigned binary reads the old schema and expects fields such as activation_generation.

This is a data-format downgrade compatibility bug. Ordinary Rust and CLI Sessions remain valid and must not be deleted or modified.

A second collision exists in ~/.orgii/agent-orgs.json: old releases expect a recursive JSON array, while the redesign writes a schema-versioned flat-definition envelope. Opening old settings can therefore reject or overwrite the redesigned definition file.

Root cause

Two incompatible generations share the same authoritative persistence names:

• agent_org_runs is treated as canonical by both old and redesigned binaries, despite incompatible columns, status values, indexes, snapshots, and lifecycle semantics.
• agent-orgs.json is treated as canonical by both generations, despite incompatible recursive and flat formats.
• Official old binaries cannot recognize a new feature flag or version marker before running their hard-coded SQL.
• An empty legacy run table is insufficient: v1.2.5 does not exclude retained Agent Org Coordinator Sessions from standalone SDE pagination. Without a root classification row, orphan Coordinators can consume page capacity and produce empty Retry pages with hasMore=true.
• A compatibility view is insufficient because v1.2.5 executes CREATE INDEX ... ON agent_org_runs; SQLite cannot create indexes on views.

Product decision

Support a safe downgrade, not bidirectional Agent Org runtime compatibility:

• Ordinary Rust/CLI Sessions and non-Agent-Org data remain usable in the old binary.
• Agent Org runs created by the redesigned binary remain authoritative and intact for re-upgrade.
• While downgraded, redesigned Agent Org runtime is inert/read-only: it must not start, resume, recover, resend, mutate, or invoke a Provider.
• Returning to the redesigned version restores normal use of the preserved canonical runtime.
• Do not migrate redesigned flat runs back into the old recursive runtime model.

Proposed solution

1. Give the redesigned runtime a clean canonical table

Rename the redesigned authoritative table to agent_org_runtime_runs.

All redesigned production readers, writers, foreign keys, lifecycle transitions, recovery, materialization, quiescence, snapshot loading, inbox/task guards, sidebar projection, and deletion paths must use this table exclusively. The redesigned runtime must never read the downgrade compatibility table as canonical state.

Do not introduce an agent_org_runs_v2 name.

2. Reserve agent_org_runs as a physical downgrade compatibility table

Keep agent_org_runs as a real table with the exact official v1.0.x–v1.2.5 15-column schema and legacy index names.

Store only minimal compatibility tombstones, not the redesigned roster, TaskGraph, grants, communication links, receipts, or lifecycle state. For each redesigned run root—and for retained historical Coordinator roots after the legacy runtime reset—materialize an old-parseable row that:

• preserves root_session_id so v1.2.5 classifies it into the Agent Org stream instead of standalone SDE pagination;
• uses terminal legacy status cancelled, preventing old startup recovery and direct-message activation;
• contains a minimal old-format recursive snapshot stub sufficient for old context loading;
• contains no user transcript, task, inbox, capability, or Provider credential data.

Synchronize canonical run creation/deletion and its tombstone in the same database transaction. Do not scan all Sessions on every startup; historical inventory is a one-time migration.

3. Make old Agent Org writes fail closed

Install compatibility-table guards so old binaries cannot turn tombstones into active runtime:

• reject old running inserts used to create a Team;
• reject arbitrary updates;
• reject deletes except through a narrowly controlled redesigned internal retirement path;
• preserve the legacy terminal row used for sidebar classification;
• reject before any Provider call or durable Agent Org mutation.

Verify that v1.2.5's failed create path cleans up its temporary Coordinator Session. A stale/direct resend attempt must make no Provider request and must not mutate the redesigned canonical run.

Because the old frontend is already published, a downgraded user may receive a generic “Agent Org unavailable after downgrade” error rather than a new polished disabled-state UI.

4. Isolate Team definition files

Move redesigned flat definitions to a new canonical path such as:

~/.orgii/agent-org-definitions.json

Keep the old recursive ~/.orgii/agent-orgs.json untouched for old releases.

On first redesigned upgrade, read the official old file through the existing exact legacy backup/reset policy and write only the new path. All redesigned definition consumers must use the canonical store/parser for the new envelope; remove direct duplicated array parsing. Saves in either generation must not overwrite the other generation's file.

5. Transactional and fail-closed migration

For the exact official 15-column legacy fingerprint only:

1. Begin an immediate transaction.
2. Inventory legacy Agent Org root Session IDs needed for compatibility tombstones.
3. Reset only the accepted obsolete Agent Org runtime envelope.
4. Create the clean canonical runtime table and dependent canonical tables/indexes.
5. Recreate the exact legacy compatibility table/indexes and terminal tombstones.
6. Commit only after every DDL and tombstone write succeeds.

Unknown schemas must not trigger destructive reset. Do not add local/unreleased 22-column development artifacts to the production migration contract. Do not delete or modify ordinary Session, CLI Session, message, usage, Project, WorkItem, Routine, or unrelated data.

Expected downgrade experience

Redesigned version → v1.2.5

• The application and ordinary Rust/CLI Session sidebar load normally; no missing-column loop and no empty Retry pagination.
• Redesigned Team roots remain visible in the Agent Org history stream.
• Their history can be viewed, but Agent Org operational actions fail closed.
• Creating a new old-format Team fails without leaving a temporary Coordinator Session or invoking a Provider.
• Old settings read/write only the old recursive definition file.

v1.2.5 → redesigned version again

• Canonical redesigned runs, immutable flat snapshots, stable Member IDs, receipts, grants, and links are unchanged.
• Old definition edits are ignored by the redesigned store.
• Completed/Idle Teams remain unchanged.
• Interrupted Starting/Working runs use the redesigned recovery contract and immutable snapshot without generating duplicate Member identities.
• New Teams can be created normally.

Scope

In scope:

• Runtime table ownership and exact legacy compatibility surface.
• Same-transaction tombstone synchronization and old-write guards.
• Exact official legacy-schema migration.
• Team definition path isolation and canonical parser ownership.
• Sidebar classification/pagination behavior across downgrade.
• Upgrade → downgrade → re-upgrade packaged verification.

Out of scope:

• Back-porting the redesign into v1.2.5.
• Making old Agent Org runs executable after downgrade.
• Converting flat snapshots into recursive hierarchy.
• PR7 Writer activation or PR9 peer messaging.
• Deleting historical Coordinator/Member Sessions, messages, Tasks, Inbox, usage, WorkItem, Routine, or other residue.
• Supporting unknown/internal development schemas.
• RPC/wire/UI redesign or unrelated cleanup.

Acceptance criteria

• Official v1.0.x–v1.2.5 SELECT statements and index initialization succeed against the compatibility table.
• The redesigned runtime exclusively owns agent_org_runtime_runs; no production read or mutation treats compatibility rows as canonical.
• Downgrading after using the redesign produces no completed_at/activation_generation missing-column errors.
• Ordinary Rust and CLI Session sentinels remain byte-for-byte/field-for-field unchanged.
• Redesigned Team roots remain out of standalone SDE pagination and continue to enter the Agent Org stream.
• Standalone pagination remains contiguous and cursor/hasMore values are correct.
• Old Team create, update, delete, resend, recovery, and Provider activation fail closed without mutating canonical state.
• A failed old Team create removes its temporary Coordinator Session.
• Redesigned canonical create/delete synchronizes the compatibility tombstone atomically.
• Migration failure rolls back without leaving half-created schemas or partial tombstones.
• Unknown table fingerprints are preserved and initialization returns a diagnostic error.
• Old and redesigned Team definition files cannot overwrite each other.
• Re-upgrade restores canonical definitions and runtime snapshots unchanged.
• No new polling, timer, startup-wide recurring scan, resident cache, or dependency is introduced.

Verification

Automated:

• Exact legacy/fresh/current/unknown schema fixtures.
• Canonical and compatibility table/index/trigger fingerprints.
• Transaction rollback injection at drop/create/index/tombstone stages.
• Old-query compatibility and old-write rejection.
• Root classification plus standalone cursor/hasMore pagination.
• Canonical create/update/delete, immutable snapshot, materialization, quiescence, and crash recovery.
• Definition-file upgrade, isolation, restart round-trip, and old-file byte preservation.
• Ordinary Rust/CLI Session sentinels.

Packaged matrix:

1. Start from v1.2.5 data with ordinary Sessions and an old Team.
2. Upgrade to the redesigned build and create/run a 3-Member Team.
3. Downgrade to the official v1.2.5 binary.
4. Verify sidebar load, ordinary Sessions, Agent Org history classification, rejected Team create/resend, and zero Provider calls.
5. Re-upgrade to the redesigned build.
6. Verify definitions, stable Member IDs, grants, links, receipts, snapshots, Starting → Working → Idle, and crash recovery.
7. Repeat with restart boundaries and a 50-Member/1,225-edge definition without launching 50 Provider runtimes.

Risks and rollback

• This intentionally preserves only downgrade visibility/classification, not old-runtime operability.
• Guard behavior must be tested with the exact released v1.2.5 binary because its create/resend cleanup order is outside the new codebase.
• Tombstone retirement must remain transactional with canonical deletion or stale classification rows may persist.
• The one-time historical Coordinator inventory must be narrowly defined to avoid classifying ordinary Sessions.
• Rollback is to restore the pre-migration database copy and run the redesigned version; deleting compatibility tombstones is not required to restore canonical data.