Skip to content

research: define a unified, versioned database migration strategy #1756

Open
Open
research: define a unified, versioned database migration strategy#1756
Assignees
Zxf-xufeng

Description

@PsiACE
PsiACE
opened on Sep 28, 2026

Goal

Define one versioned migration path for PowerContext databases.

PowerContext currently combines SQLAlchemy create_all(), several startup schema helpers, explicit maintenance migrations, and backend-specific DDL. PR #1716 adds table renames and constraint changes, which makes the need for a shared revision and execution model more visible.

Follow-up to #1716, especially this migration comment.

Scope

  • Inventory the existing schema and data migration paths and define their ownership and execution order.
  • Evaluate Alembic as the primary option, together with a small custom revision registry and SQL-first migration tools.
  • Define baselining for existing databases and initialization for clean databases.
  • Define migration locking, interruption recovery, verification, and multi-replica behavior for SQLite, seekDB, and OceanBase.
  • Separate schema revisions from long-running data migrations and projection rebuilds while preserving their dependencies.
  • Decide when migration is automatic and when operators must run an explicit command.
  • Add a CI check for schema changes without a corresponding migration.

Acceptance criteria

  • A short RFC records the selected approach, revision model, baseline policy, execution ownership, and recovery policy.
  • A prototype covers an additive change and a table rebuild or constraint change on every supported database profile.
  • Tests cover clean initialization, upgrade from a released schema, repeated execution, interruption, concurrent attempts, and a database newer than the running binary.
  • Existing migration helpers are mapped to the new model or documented as explicit exceptions.
  • The rollout plan does not require PR feat(dream): expand reviewed artifact and tag revisions #1716 to adopt the final framework.

Non-goals

Activity

  1. github-project-automation
    added this to PowerContexton Sep 28, 2026
  2. github-project-automation
    moved this to Todo in PowerContexton Sep 28, 2026
  3. PsiACE
    assigned
    Zxf-xufeng
    on Sep 28, 2026

  4. AlexStocks commented on Oct 21, 2026

    @AlexStocks
    AlexStocks
    on Oct 21, 2026
    Contributor

    Follow-up from #2084 R1/R13, checked against master 88899876b2ac7b38a11420f34f4ecf6da14dd65b.

    The complete Server still executes startup DDL/backfills through builtin/runtime/composition.py, including scope_search_schema.py, revision_metadata_schema.py, and tag/lexical projection helpers. SQLite holds BEGIN IMMEDIATE around initialization; the OceanBase composition has no equivalent startup coordinator. The existing explicit migration coordinator covers the registered four-table bundle, not these complete Server startup operations.

    Please include the following in this issue's existing inventory, startup gate, and multi-replica acceptance work:

    • Map every startup helper to an explicit migration/projection owner; distinguish schema revision, processing marker, and derived analyzer version rather than merging unrelated markers by name.
    • Reproduce concurrent upgrades on real seekDB/OceanBase, including two nodes adding the same missing column, DDL implicit commits, failure/restart, and lock timeout. Do not introduce GET_LOCK solely from SQLite or mocked evidence.
    • Establish the complete Server migration/readiness gate and supported recovery route. The current four-table ready result is not full Server readiness.
    • Measure warm startup with real Topic/chunk cardinality and concurrent API writes before moving invariant checks out of their transaction or removing checks.

    For scale context, a local CPython 3.14/Windows SQLite fixture with 500,000 unrelated Artifact rows plus matching heads held the startup write transaction for a median 160.105 ms (whole startup 203.447 ms, three runs). This confirms growth but did not reproduce a five-second timeout and excludes large Topic/chunk projections. Experience/Skill re-tokenization is already gated after #2095; #2084's unconditional-rebuild claim is obsolete.

    #2147 corrects the misleading migration-policy docstring and unavailable full-database-bundle error guidance. It does not implement the full migration gate or multi-node startup exclusion.

    Real named-lock primitive check

    On October 22, 2026, two independent client sessions against an isolated direct OceanBase CE 4.3.5.6 MySQL tenant verified all of the following with GET_LOCK, RELEASE_LOCK and a synthetic table:

    • A second session gets 0 from GET_LOCK(name, 0) while the first owns the lock.
    • The first session retains ownership through commit, rollback, and CREATE TABLE's implicit commit.
    • Explicit release lets the second acquire it.
    • Closing the owning session lets the second acquire it within the five-second request budget.

    The image was ghcr.io/oceanbase/oceanbase-ce:4.3.5.6-106000012026040916, digest sha256:31086a6900c21c479c2bcd942b6a28c53b17a51f4e9b9eb8eafcc596adfcd2e3. The synthetic table was removed; the owned container and initially stopped Docker daemon were stopped after testing. This establishes the direct-connection primitive only. It does not establish ODP session affinity, serialization of the complete Server initialization, multi-node upgrade recovery, or a supported production upgrade. No new startup lock has been inserted into runtime on this evidence alone.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions