IndexedDB Schema Migration Framework with Versioned Model Snapshots and Rollback Support

[elizabetheonoja-art]

Problem Statement / Feature Objective

The application stores operational data (telemetry snapshots, tile blobs, user preferences, draft reports) in IndexedDB across multiple object stores. As the schema evolves with new features (e.g., adding a tariff_history store), existing databases must be migrated without data loss. A schema migration framework must support versioned model snapshots, per-version up/down migration functions, and a migration state tracker that logs each migration's duration and outcome. The framework must also handle migration rollback on failure and provide a dry-run mode for testing in staging.

Technical Invariants & Bounds

• Database name: UtilityDashboardDB (constant). Current version starts at 1; each new store or index increment bumps the version by 1.
• Migration function signature: (tx: IDBTransaction, db: IDBDatabase, log: (msg: string) => void) => Promise.
• Maximum migration runtime per version: 30 seconds. Exceeding this triggers a rollback to the previous version and an error report.
• Down migration: each version must provide a downgrade function that reverts the schema change. Down migrations are only executed in dry-run mode.
• Migration log: stored in a separate migrations object store with entries: { version, appliedAt, durationMs, outcome: 'success' | 'failure', error?: string }.
• Snapshot capture: before any migration, the framework captures a JSON snapshot of all data in the stores affected by the migration (max 5 MB per snapshot; truncated if exceeded).

Codebase Navigation Guide

• src/services/db.ts - IndexedDB open/close/createObjectStore logic; currently has hardcoded version handling. Must be refactored.
• src/services/migrations/ - Directory containing migration scripts: 001_initialSchema.ts, 002_addTariffHistory.ts, 003_addUserPreferences.ts, etc.
• src/utils/migrationRunner.ts - Core migration engine: connects to DB, checks current version, applies pending migrations sequentially.
• src/hooks/useMigrationStatus.ts - Hook exposing current DB version, pending migrations, and status (running, complete, failed).
• src/components/panels/MigrationStatusBanner.tsx - UI banner shown during migration, with progress bar and error details.
• src/types/db.ts - Database schema types: DbSchema, ObjectStoreDef, IndexDef.

Implementation Blueprint

1. Create the migrations directory structure: src/services/migrations/ with each file exporting { version, up(tx, db, log), down(tx, db, log), description }.
2. Implement src/utils/migrationRunner.ts: getCurrentVersion() reads db.version. If browser DB version < schema version, open DB with requested version = schema version. The onupgradeneeded handler iterates from current version to target version, calling each migration's up() function within the same transaction. Log results to migrations store. On failure, attempt down migration for the failed version (only if not in production to avoid data loss).
3. Refactor src/services/db.ts to import migrationRunner and delegate the onupgradeneeded logic. Remove all inline schema-creation code.
4. Write a snapshot utility: before starting a migration batch, query all existing stores via db.objectStoreNames, serialize to JSON, and store in a snapshots object store with key snapshot_{version}. Enforce the 5 MB limit by truncating large binary blobs with a placeholder.
5. Build useMigrationStatus hook that reads migration log from migrations store and determines pending migrations by comparing the last applied version with the current schema version constant.
6. MigrationStatusBanner renders during migration: a progress bar showing (currentVersion / targetVersion) and a log area with streaming messages from the migration runner.
7. Add a testing helper: migrationRunner.dryRun(mockDB: IDBDatabase) that runs migrations without committing, allowing verification in Playwright tests.

[dev-king-001]

Good day Sir, assign me this.

[grantfox-oss]

🦊 GrantFox — @dev-king-001 has been assigned to this issue as part of the Official Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #59)
2. Your PR will be reviewed by the Utility-Protocol maintainers

Good luck! Track your progress on GrantFox.