← all skills

transactional-state-migrations

/home/avalon/.hermes/skills/software-development/transactional-state-migrations/SKILL.md · raw

Transactional State Migrations

Use this skill when an application stores mutable registries in JSON or other whole-file documents and needs a transactional embedded database without changing product behavior or risking live state.

The governing principle is kernel first, adapters second, runtime cutover last. Never combine live-data migration, business-logic rewrites, and deployment into one unverifiable step.

When evolving an atomic file-backed operation ledger into crash-recoverable per-effect execution state, follow references/durable-effect-journals.md. It covers schema-on-mutation migration, store-owned authority, acknowledgement ambiguity, reconciliation, proxy-safe validation, atomic terminal state, and the single-process queue boundary.

When a central account tier must cross a signed handoff into separately deployed tenant runtimes, follow references/versioned-tenant-access-contracts.md. Version the wire contract per tenant, preserve legacy behavior by default, keep product tiers separate from local roles, bootstrap first-handoff state atomically, and enforce hidden scopes server-side.

When tenant data predates current project/connection/profile/OAuth boundaries, follow references/legacy-tenant-contract-modernization.md. Preserve strict modern authorization invariants, audit both persisted relationships and stale callers, keep credential status truthful, split obsolete combined authorities, and prove the exact user-facing path after an idempotent rehearsal.

When an existing tenant must be cleared and rebuilt around current contracts while preserving its identity, access, channels, and managed provider runtime, follow references/clean-tenant-rebootstrap.md. Extract canonical brand/product sources before deletion, stage a genuinely empty current-schema replacement, rebuild through current domain lifecycles, reprovision database-bound agent credentials, reset stale conversation state selectively, and distinguish staged success from a completed live cutover.

Legacy tenant contract modernization

Treat legacy compatibility as a data-and-caller forward migration, not permission to weaken the current runtime:

  1. Decide whether the new server invariant is a real authorization boundary. If so, keep it strict.
  2. Audit the legacy entity graph for missing or foreign project ownership, not just the row named in the error.
  3. Classify intentionally portfolio-shared resources with an explicit allowlist; scope every other connection to its real business.
  4. Audit every UI/CLI caller for the current parameter names and required authority dimensions. A corrected database can still fail through an obsolete ?project= link.
  5. Do not copy encrypted credentials as ordinary rows. If no usable token exists, preserve historical identity but normalize the connection, surface, and dependent account statuses to reconnect/unavailable states.
  6. Split legacy combined provider authorities when the modern runtime uses separate flows—for example, Facebook/Meta versus direct Instagram.
  7. Rehearse dry-run → apply → second apply on an exact copy. Require unchanged dry-run hash and zero changes on the second apply.
  8. After live cutover, authenticate through the real session path and probe the exact previously failing request. For OAuth starts, intercept and validate the redirect, then deterministically remove the unused test state.

Do not force unrelated profile/chat cutovers merely to make an inventory look modern. If the runtime already creates isolated current profiles and safely shadows legacy threads, preserve that boundary until its own canary succeeds.

Staged workflow

1. Inventory the state contract

For every mutable registry, record:

Capture hashes of live source files before testing. Tests and worktrees must use disposable state directories and must prove those hashes remain unchanged.

2. Build the database kernel independently

Before wiring application classes, implement a focused store with:

For whole-document registries, a versioned document table is a valid first cut when it preserves behavior and makes migration reversible. Normalize later only when query or integrity requirements justify it.

3. Prove atomic import before runtime wiring

The importer must:

  1. Require an explicit complete source map or source directory.
  2. Read and parse every source before opening the write transaction.
  3. Canonicalize documents and calculate both source-byte and canonical-document SHA-256 hashes.
  4. Reject malformed, unreadable, unknown, or conflicting inputs before writes. Keep strict complete import as the default. If inventory proves a registry is optional/lazily created, expose a separate explicit bootstrap operation that substitutes its exact legacy empty schema only for ENOENT; never reinterpret malformed input as missing.
  5. Import or explicitly bootstrap every registry in one transaction.
  6. Record provenance, counts, checksums, and an aggregate import run.
  7. Treat an identical checksum rerun as idempotent.
  8. Refuse initialized-state overwrite unless an explicit recovery operation requests it.
  9. Support an injected failure test proving all writes roll back.

4. Build deterministic rollback export

Export must target a caller-provided disposable directory and:

Rollback proof should separate two different guarantees:

  1. Compatibility: parse source and exported JSON and compare structural values. Canonical export may sort object keys or normalize trailing newlines, so raw source-byte equality is not the right compatibility assertion.
  2. Determinism: export the same database twice to separate disposable directories and compare exported hashes byte-for-byte.

Also hash the original live source files before and after the entire disposable proof to demonstrate that migration testing did not mutate them.

See references/sqlite-json-cutover.md for a concrete Node/SQLite implementation pattern and recovery checklist.

5. Convert adapters without changing product APIs

Retain the old JSON backend until rollback confidence is established. Inject the new backend through constructors or a narrow repository interface.

Critical rule: a mutation must not become load() followed by an independent put(). Use one database transaction around the complete read-modify-write operation:

store.update('registryKey', (current) => {
  const next = normalize(current);
  // Apply the existing business operation synchronously.
  return next;
});

If the public method must return a derived record, capture that result inside the transaction callback and return it after commit. Preserve validation, redaction, timestamps, sorting, caps, and error status codes.

6. Wire runtime state deliberately

Add one explicit database path contract, normally derived from the isolated state directory. In test mode, fail closed if it resolves into live state.

At startup or during an explicit cutover command:

Keep the database connection lifecycle explicit and close it during graceful shutdown.

See references/partial-legacy-bootstrap.md for the optional-missing registry classification, provenance, and rollback test pattern.

7. Verification gates

Minimum focused tests:

Then run the repository's complete unit, security, readiness, and production-build gates. Run browser tests only in an environment with enough resources; do not weaken them to accommodate a constrained shared host.

Worktree and dependency pitfall

Git worktrees change relative filesystem location. Local package dependencies such as file:../sibling can resolve under the worktree parent instead of the canonical application parent. Before accepting unrelated module-resolution test failures:

  1. Inspect the dependency's actual resolved target.
  2. Install dependencies in the worktree.
  3. If the repository intentionally consumes canonical sibling checkouts, repair only ignored node_modules links to those checkouts or use the project's documented worktree bootstrap.
  4. Rerun the unchanged test suite.

Do not modify tests or package manifests merely to hide a worktree-local resolution problem.

Live Entity Consolidation (SQLite)

Use this pattern when an operator corrects a product model after launch: two projects, tenants, accounts, or brands that should have been one must become one without losing the live operating record.

Decide the survivor from evidence, not the prettier name

Before changing anything, inventory both entities and select the record that owns the operational truth: live provider connections, payment rails, contracts, review/audit history, deployed infrastructure, and external identifiers. Rename that record to the canonical business name; absorb the other record’s assets, brand kit, collections, and historical rows. This avoids breaking every foreign reference merely to preserve an ID/name pairing.

Required safeguards

  1. Create a timestamped online SQLite backup before mutation and verify its SHA-256 plus PRAGMA integrity_check.
  2. Write a migration script with an explicit --dry-run and --apply; require exact expected source/target IDs and names so it refuses a wrong database.
  3. Inventory every table with project_id / entity foreign keys, plus indirect references such as chat scopes, canvas layout scopes, module-connection rows, and JSON metadata.
  4. Run the move in one transaction. Handle unique constraints deliberately (for example, merge duplicate module types before deleting the absorbed project).
  5. Preserve actual third-party identity facts. Re-contextualize internal mappings to the canonical business, but do not claim an external provider account/page/ad-account was renamed unless a verified provider-side action succeeded. Store the last externally observed name separately when useful.
  6. Normalize future defaults too: update seed/bootstrap code so a fresh deployment does not recreate the old mistaken identity.
  7. After the transaction, verify: absorbed entity absent; every moved table has zero source rows; survivor has expected connections/products/assets; foreign-key check has no new violations compared with the preflight backup; browser/API show the canonical business exactly once.

Common pitfalls

Completion criteria

A migration is not complete until: