feat(migrations): complete the reusable upgrade framework from RFC #1771 and PR #1772 #2154

Priority

P0 — complete the shared upgrade framework and unblock supported upgrades of existing deployments.

Goal

Complete the production framework introduced by #1772 according to RFC #1771, so successive PowerContext releases use the same versioned upgrade path. The 1.3.0 upgrade is the first required end-to-end acceptance case, not the limit of the implementation.

Future schema and persisted-format changes must be integrated by registering immutable revisions, domain data/projection steps, compatibility requirements, and validators with the shared framework. They must not require another version-specific command, startup migrator, or special-case orchestration branch.

Problem

An attempted PowerContext 1.3.0 → 1.4.0 upgrade with embedded seekDB exposed an incomplete production upgrade path:

• Legacy Candidate storage causes startup and the Atomic Memory migration entry point to fail with migration_required.
• A database containing pc_schema_revision is rejected with migration_framework_not_ready.
• The installed migration bundle covers a limited set of objects and cannot adopt a complete historical Server database.

#1716 supplies Dream/Candidate transformation and verification components, but production registration is incomplete. #1772 supplies Phase A capabilities and parts of the unified interface. The remaining framework integration must provide a reusable path for subsequent releases, not only repair one source/target pair.

Required work

1. Versioned upgrade and registration contract

• Keep application versions, schema revisions, and API/task contracts distinct. Publish an explicit support matrix mapping supported source baselines and revisions to target releases, backend/configuration requirements, and any required intermediate release.
• Preserve the RFC's single linear Alembic revision chain and pc_schema_revision(version_num). Freeze released migration resources and checksums; add new revisions instead of editing historical migrations to match current metadata.
• Define the registration contract for affected/retained objects, schema prerequisites, ordered domain data/projection steps, preconditions, postconditions, compatibility rules, and recovery requirements.
• Build plans from the registered chain and dependencies. Allow supported adjacent and multi-revision upgrades through the same entry point, and reject missing paths, unknown/newer revisions, ambiguous layouts, and unsupported combinations before business writes.
• Retain fail-closed behavior. Neither manual stamping nor removal of startup gates is an implementation of an upgrade path.

2. Complete database lifecycle and domain integration

• Freeze supported complete historical Server baselines, including official persistent objects created by optional features, and adopt unversioned databases only after actual schema/data validation.
• Initialize new databases and upgrade existing ones through registered resources with equivalent required runtime capabilities. Account explicitly for registered archives and retained recovery objects.
• Integrate Dream/Candidate transformations and their dependencies on Processing and Atomic Memory migration.
• Coordinate expand revisions, domain data conversion, validation, contract revisions, and required projection rebuilds within one plan. Preserve domain receipts, idempotency, and data ownership.
• Inventory existing startup helpers and maintenance commands; migrate them to registered steps or read-only checks. Compatibility commands must delegate to the shared flow rather than independently modifying the same tables.
• Keep maintenance connections independent of business initialization so supported historical states can be inspected and transformed before they satisfy the target runtime's requirements.
• Share readiness criteria across final verification and Server/Worker/SDK database entry points, covering actual schema, required data invariants, legacy tasks, projections, and configuration compatibility.

3. Execution, recovery, and service lifecycle

• Reuse and complete feat(db): align migration prototype with single-table RFC #1772's target-bound plans, maintenance coordination, locks, dedicated connections, backup policies, and durable external evidence.
• Verify interruption recovery across supported revision boundaries and domain steps, including nontransactional DDL. Retain the original recovery point; unknown or unprovable states must remain blocked.
• Complete the supported local service lifecycle: suppress automatic restart, migrate and verify, switch to the confirmed executable, then check readiness. Preserve stopped state on migration failure and distinguish database success from service startup failure.
• Document shared-database coordination and real backend limitations without claiming automatic control of unknown writers, rolling upgrades, or unverified backup restoration.

4. Standard contribution and release integration

• Document how each future schema/data-format change supplies revisions, steps, compatibility declarations, and validators in the same PR, including bilingual operational guidance.
• Expose the support manifest and validation entry points needed by ci(release): gate supported upgrades on complete databases from actual prior releases #2161. Integrate the required schema-change and release checks so a breaking change cannot be advertised as upgradeable without a complete executable path and acceptance evidence.
• Define the framework activation point and remove competing migration ownership for managed objects. A completed prototype or merged feature PR must not stand in for release readiness.

#2161 owns the continuous CI/release-gate implementation; this issue owns the framework contracts and their integration. Enabling the framework as the standard upgrade process requires those checks to be wired in, rather than treating them as an optional follow-up.

Acceptance criteria

• Upgrade a complete database created and populated by the actual 1.3.0 release to the fixed release through public maintenance commands. Verify successful startup, core read/write, retrieval, authorization, Candidate approval, and background processing.
• Preserve or explicitly transform historical revisions, references, permissions, idempotency receipts, Source Cursors, and other critical domain state.
• Demonstrate reuse with a subsequent registered migration in an isolated acceptance fixture: the shared planner/executor handles it without a new command or release-specific execution branch. Clearly distinguish this extensibility fixture from real released-version acceptance.
• Verify supported adjacent and multi-revision paths, interrupted intermediate states, repeat execution, and rejection of unsupported paths. Direct and sequential paths must yield equivalent required state where both are declared supported.
• Verify fresh and upgraded database capabilities and independently reject incomplete schema/data/task/projection state at runtime entry points.
• Provide actual acceptance evidence for each claimed backend: SQLite, seekDB, and OceanBase. Missing environments or skipped tests are not evidence of support.
• Verify that released resources remain immutable and that future changes missing required migration/compatibility declarations fail the integrated checks.
• Installation and operational documentation accurately describe the supported version matrix, intermediate steps, maintenance requirements, and recovery boundaries.

Do not close this issue solely because 1.3.0 can be upgraded by a one-off script, a limited-scope verify succeeds, or the revision table reaches head. Completion requires reusable framework integration plus actual supported-version acceptance.

Boundaries

Supported version paths are explicit; this does not promise arbitrary historical-version direct upgrades. Continue using RFC #1771's thin execution layer, domain-owned scripts/receipts, and one Alembic version table. A generic persistent task scheduler, automatic destructive downgrade, cross-database transfer, and unverified online/mixed-version upgrades are outside scope.

Related work

• Implementation draft: feat(migrations): complete the reusable database upgrade framework #2168
• Design: RFC docs(rfc): define unified versioned database migrations #1771; tracking issue research: define a unified, versioned database migration strategy #1756
• Framework foundation: feat(db): align migration prototype with single-table RFC #1772
• Dream/Candidate contribution: feat(dream): expand reviewed artifact and tag revisions #1716
• Independent arithmetic CHECK defect: fix(migrations): parse arithmetic CHECK constraints and report actionable schema errors #2160
• Continuous upgrade release gates: ci(release): gate supported upgrades on complete databases from actual prior releases #2161