# ADR-088: IndexedDB Schema Versioning and Safe Upgrade Protocol

## Status

**Accepted** (2026-06-27)

## Status History

```yaml
- 2026-06-27:
    status: Accepted
    decision_maker: hkl
    rationale: IndexedDB schema management is critical to avoid silent data loss and app hangs when live users upgrade; standardizing the initDB() pattern across all PWAs ensures safety and consistency
    changed_via: adr-kit (360lm)
```

## Context

IndexedDB is the offline-first primary storage system mandated by ADR-020 for all field PWAs. Unlike SQL databases, IndexedDB schema changes happen via the `onupgradeneeded` event fired when the database version integer increments.

**Current state:**
- **Recce PWA** uses `RecceDB` v4 with upgrade logic embedded in its JS, but the pattern is not standardised
- **Other PWAs** (Activity, Counters, etc.) have varying approaches to versioning and schema migration
- **Problems without a standard:**
  1. **Missing stores at runtime:** A PWA requests a store that doesn't exist (old version user upgrades to new code) → runtime error, app breaks
  2. **Data loss via deletion:** Schema simplification deletes an old store without first migrating its data → live users lose offline work
  3. **Blocked event not handled:** User has multiple tabs of the same PWA open; first tab opens DB v5, second tab tries to open v4 → infinite hang, no error shown to user
  4. **No documented migration path:** Each PWA reinvents the wheel; inconsistent error handling, missing indices, deprecated stores left in place
  5. **Silent upgrades:** SW updates code + bumps DB version, but user never sees a message → upgrade fails, user unaware

**Related decisions:**
- **ADR-020:** IndexedDB is primary storage; server sync is secondary
- **ADR-005:** CACHE_VER string bumps force SW reload — tied to schema changes
- **ADR-013:** Each PWA is a single HTML file; no build pipeline to manage migrations
- **ADR-021:** Service worker uses cache-first strategy; DB updates must be coordinated with SW updates

## Decision

This ADR standardises IndexedDB initialization and schema versioning for all PWAs using offline-first storage.

### Database Naming Convention

Use the pattern `<PWAName>DB` (PascalCase + "DB"):
- `RecceDB`
- `ActivityDB`
- `CountersDB`
- `VehicleDB`
- `ExpenseDB`

Never use generic names like `db`, `appDB`, or lowercase. This makes debugging easier and prevents name collisions if multiple PWAs run in the same browser context.

### Version Management

1. **Start at version 1.** Do not use version 0.
2. **Increment by 1 for each schema change.** Never skip versions (e.g., jump from v2 to v4).
3. **Document each version in a comment** above the `initDB()` call.
4. **Lock the current version in a const:** `const DB_VERSION = 3;` — easier to audit and prevents off-by-one errors.

Example documentation:
```js
const DB_VERSION = 4;
// RecceDB schema history:
// v1: created Oct 2024 — stores: submissions, officials, submissions_cache
// v2: added Jan 2025 — added index on submissions(status, created_at) for faster filtering
// v3: added Mar 2025 — created new store 'drafts' for offline-first form saves
// v4: added Jun 2025 — added index on officials(official_id, brand_id); deprecated 'submissions_cache' (data migrated to submissions)
```

### Standard `initDB()` Pattern

```js
function initDB() {
  return new Promise((resolve, reject) => {
    const req = indexedDB.open('PWANameDB', DB_VERSION);
    
    req.onupgradeneeded = e => {
      const db = e.target.result;
      const oldV = e.oldVersion;
      
      // Migration blocks: each version is independent, idempotent, and additive
      // Use "if (oldV < N)" not "if (oldV === N-1)" to allow users to skip versions
      
      if (oldV < 1) {
        // v1: initial schema
        const store1 = db.createObjectStore('submissions', { keyPath: 'id', autoIncrement: true });
        store1.createIndex('by_created_at', 'created_at', { unique: false });
        
        const store2 = db.createObjectStore('officials', { keyPath: 'id' });
        store2.createIndex('by_status', 'status', { unique: false });
      }
      
      if (oldV < 2) {
        // v2: add compound index for faster queries
        req.transaction.objectStore('submissions').createIndex('by_status_date', ['status', 'created_at'], { unique: false });
      }
      
      if (oldV < 3) {
        // v3: new store for offline form drafts
        db.createObjectStore('drafts', { keyPath: 'localId', autoIncrement: true });
      }
      
      if (oldV < 4) {
        // v4: add index on officials table; delete deprecated submissions_cache
        req.transaction.objectStore('officials').createIndex('by_id_brand', ['official_id', 'brand_id'], { unique: false });
        if (db.objectStoreNames.contains('submissions_cache')) {
          db.deleteObjectStore('submissions_cache');
        }
      }
      // Each version block is independent and only runs once for that version
    };
    
    req.onblocked = () => {
      // MANDATORY: show user-visible warning
      showToast('Please close other tabs running this app. We\'re updating your offline data.');
      // Log to console for debugging
      console.warn('IndexedDB open blocked — another tab is holding the connection');
    };
    
    req.onsuccess = e => {
      const db = e.target.result;
      
      // Optional: run one-time data migrations after schema is ready
      if (oldV < 3) {
        transformOldData(db); // Custom function to migrate data from old format
      }
      
      resolve(db);
    };
    
    req.onerror = e => {
      console.error('IndexedDB open error:', e.target.error);
      reject(e.target.error);
    };
  });
}

function showToast(msg) {
  // Simple toast helper — implement per PWA's UI framework
  const el = document.createElement('div');
  el.className = 'toast';
  el.textContent = msg;
  document.body.appendChild(el);
  setTimeout(() => el.remove(), 3000);
}
```

### Rules for Safe Schema Changes

1. **Additive only in `onupgradeneeded`:**
   - Only call `createObjectStore()` and `createIndex()`
   - Never delete a store unless you FIRST migrate its data away (see rule 4)
   - Never rename a store or index (create new, migrate, delete old as separate versions)

2. **`onblocked` is mandatory:**
   - Must show a user-visible message (toast, modal, or notification)
   - Do NOT silently block — users will think the app is frozen
   - Include `console.warn()` for debugging multi-tab scenarios

3. **Each version block is independent:**
   - Use `if (oldV < N)` not `if (oldV === N-1)`
   - Allows users to jump multiple versions (e.g., from v1 to v4 in one upgrade)
   - No version-specific logic outside the block

4. **Data migration happens AFTER the schema is ready:**
   - Keep `onupgradeneeded` clean — only structural changes
   - Use a separate `onsuccess` callback or async function to transform data
   - Example: renaming a store requires (v1→v2) create new store, (onsuccess) copy data, (v3) delete old store

5. **Never delete a store without an intermediate version:**
   - If you want to remove `cache_store` in v5, first release v4 that marks it deprecated in comments
   - Users upgrade to v4 (data is still there), then v5 safely deletes it
   - This gives users a chance to recover data if needed

6. **Test with multiple browser states:**
   - **Fresh install (no DB):** oldV = 0, should create all stores up to current version
   - **Existing user (oldV = 2, new version 4):** should run v2, v3, v4 blocks only
   - **Multiple tabs:** first tab upgrades, second tab stays open — `onblocked` should fire on the second
   - **Offline then sync:** upgrade must not lose pending offline edits

### CACHE_VER Coordination (ADR-005)

Whenever you increment `DB_VERSION`, also bump `CACHE_VER`:

```js
const DB_VERSION = 4;
const CACHE_VER = 'v4.2'; // bumped when DB_VERSION changed

// In service worker fetch handler:
const cacheName = `RecceDB-${CACHE_VER}`;
```

**Why?** The service worker caches the HTML/JS that opens the database. If the HTML version is stale, the database upgrade will fail silently (old code trying to open new DB version). Bumping CACHE_VER forces all tabs to reload the new code before accessing the database.

### Error Handling

Wrap `initDB()` calls in error handling:

```js
async function bootstrap() {
  try {
    const db = await initDB();
    console.log('Database opened successfully');
    return db;
  } catch (err) {
    console.error('Failed to open database:', err);
    // Show user-visible error
    showToast('Could not load offline data. Please reload the page.');
    // Fallback: disable offline features, require network
    return null;
  }
}
```

## Implementation Notes

### Checklist for Updating a PWA's IndexedDB Schema

- [ ] **Read the current schema:** Inspect the `onupgradeneeded` block and version history comments
- [ ] **Increment DB_VERSION:** Update the const (e.g., from 3 to 4)
- [ ] **Add a new version block:**
  ```js
  if (oldV < 4) {
    // v4: [brief description]
    // [only createObjectStore, createIndex calls, or conditional deleteObjectStore]
  }
  ```
- [ ] **Update the schema history comment** at the top of initDB()
- [ ] **Test the upgrade path:**
  - [ ] Open DevTools → Application → IndexedDB → delete the database
  - [ ] Reload — should create fresh (oldV = 0)
  - [ ] Change DB_VERSION to next version, reload — should migrate successfully
  - [ ] Test with two tabs open; first upgrades, second should see `onblocked` message
- [ ] **Bump CACHE_VER** in the same commit
- [ ] **Test offline functionality:**
  - [ ] Go offline, create/edit data in the new stores
  - [ ] Go online, confirm sync still works
  - [ ] Verify no data loss from the upgrade
- [ ] **Update the memory file** (`dbt_[pwa].md`) with the new schema version and notes
- [ ] **Create a commit** with message: `[PWA]: bump DB schema to v[N] — [brief reason]`

### Debugging Multi-Tab Issues

If a user reports "app is frozen" or "stuck loading":

1. **Check for blocked open:**
   - Open DevTools Console → look for `IndexedDB open blocked` warning
   - Likely cause: multiple tabs, first tab is upgrading, second tab waiting indefinitely

2. **Workaround:**
   - User closes one tab (unblocks the first tab)
   - User reloads the remaining tab
   - Confirm the toast message appears during upgrade

3. **Prevention:**
   - Always include `onblocked` handler with user-visible message
   - Log `console.warn()` with version info: `"Blocked upgrading RecceDB from v2 to v4"`

### Handling Deprecated Stores

**Scenario:** A store is no longer used but still exists for old users.

**Timeline:**
- **v4:** Create `new_store`, migrate data in `onsuccess`, add comment "old_store deprecated"
- **v5:** Conditionally delete `old_store`:
  ```js
  if (oldV < 5) {
    // v5: remove deprecated old_store
    if (db.objectStoreNames.contains('old_store')) {
      db.deleteObjectStore('old_store');
    }
  }
  ```

This ensures users who skip from v3 to v5 will have `old_store` deleted cleanly.

### Performance Considerations

1. **Index creation is synchronous:** Large data migrations in `onupgradeneeded` can block the main thread. Keep them minimal; do heavy transforms in `onsuccess`.
2. **Compound indices:** Only add if you query by multiple fields together. Each index consumes storage.
3. **Validate data after upgrade:** Run a sanity check (e.g., count records) to detect corruption.

## Alternatives Considered

### 1. Use a separate "schema_version" table in IndexedDB

Store the version number in a dedicated table instead of using the database version integer.

**Rejected:**
- Adds complexity (extra read before every operation to check version)
- IndexedDB already provides version management — duplicating it is error-prone
- Browser API handles the version parameter; using a table adds a level of indirection without benefit

### 2. Delete and recreate the entire database on schema change

On each upgrade, drop all stores and rebuild from server.

**Rejected:**
- Loses offline data (field users may not have network connectivity during upgrade)
- Violates ADR-020 (offline-first primary storage)
- Requires server connectivity for every upgrade — defeats the purpose of offline-first
- User experience: data disappears during upgrade, breaking trust

### 3. Use a migration library (e.g., Dexie.js)

Adopt a third-party library to manage schema versioning.

**Rejected:**
- ADR-013 mandates single HTML files with no build pipeline or dependencies
- Adding a library violates YAGNI principle for this use case
- The native IndexedDB API is sufficient once standardised (this ADR is the standardisation)
- Debugging is harder when issues arise (library abstraction obscures the underlying DB state)

### 4. Auto-delete unused stores on every open

Check objectStoreNames and delete any stores not in a whitelist.

**Rejected:**
- Dangerous: could delete stores that are temporarily unused but contain valuable data
- No audit trail — hard to understand why a store was deleted
- Violates the principle of explicit data migration (rule 4 above)

## Consequences

### Positive

1. **Data safety:** Strict rules prevent accidental data loss during upgrades
2. **Multi-tab safety:** `onblocked` handler prevents silent hangs when multiple tabs are open
3. **Consistency:** All PWAs follow the same pattern — easier to debug and maintain
4. **Auditability:** Version history comments document why each schema change was made
5. **No dependency creep:** Native IndexedDB only — no build pipeline, no third-party library bloat
6. **Backward compatibility:** Users can skip versions; old devices don't break when upgrading to a new app version
7. **Offline resilience:** Data upgrades don't require network connectivity

### Trade-Offs

1. **Manual migration code:** Developers must write explicit migration logic for each version (no ORM magic)
2. **Storage overhead:** Deprecated stores remain until explicitly deleted in a future version; storage may grow temporarily
3. **Testing complexity:** Must test multiple upgrade paths (v1→v2, v1→v3, v1→v4) to ensure no data loss
4. **No rollback:** If a user upgrades to v4 and discovers a bug, they can't easily downgrade; data is in v4 format

### Risks and Mitigations

| Risk | Mitigation |
|---|---|
| Developer forgets to add `onblocked` handler → user sees infinite loading | Code review checklist: every initDB() must have `onblocked` with user-visible toast |
| Data migration logic is buggy; user loses offline edits during upgrade | Always test the upgrade path in DevTools; verify row counts before/after migration; log migration errors to console |
| A PWA's code opens the database on startup, blocking other tabs indefinitely | `onblocked` toast guides user to close other tabs; include version numbers in message for debugging |
| Developer deletes a store without intermediate version; live users lose data | Enforce rule 5 in code review; use git blame to understand historical store deletions |
| CACHE_VER not bumped when DB_VERSION changes; old code tries to use new schema | Couple DB_VERSION and CACHE_VER bumps in the same commit; add a pre-commit check (if possible) |
| Multiple users report "stuck on loading" after deployment | Check server logs for error messages; inspect IndexedDB in DevTools to see if upgrade is stuck; send push notification asking users to close other tabs or do a hard refresh |

## Related Decisions

- **ADR-020 (Offline-First IndexedDB Primary Storage):** This ADR operationalizes the schema management for the primary storage system mandated by ADR-020
- **ADR-005 (SW Cache Busting via CACHE_VER):** DB schema changes must be coordinated with SW cache busting to prevent stale code accessing new schema
- **ADR-021 (Service Worker Cache-First Strategy):** SW caching must be bumped when DB schema changes to force code reload
- **ADR-013 (Single HTML File):** No build pipeline means schema migrations are embedded directly in the HTML; this ADR provides the pattern for doing so safely
- **ADR-069 (Production Hotfix Protocol):** If a schema bug is discovered in production, follow the hotfix protocol with additional DB rollback planning

## References

- **RecceDB reference implementation:** `/var/www/360lm/recce/index.html` (contains initDB() for RecceDB v4 as of 2026-06-27)
- **ActivityDB reference:** `/var/www/360lm/activity/index.html`
- **CountersDB reference:** `/var/www/360lm/counters/index.html`
- **MDN: IndexedDB API:** https://developer.mozilla.org/en-US/docs/Web/API/IndexedDB_API
- **MDN: IDBVersionChangeEvent:** https://developer.mozilla.org/en-US/docs/Web/API/IDBVersionChangeEvent
- **Dexie.js (alternative library):** https://dexie.org/ — reference for migration patterns if manual approach proves insufficient in future

---

**Decision maker:** hkl  
**Changed via:** adr-kit (360lm)  
**Date:** 2026-06-27  
**Last reviewed:** 2026-06-27