Schema Evolution and Lazy Migration

-- Single table with version tracking
CREATE TABLE items (
  id UUID PRIMARY KEY,
  schema_version TEXT NOT NULL,
  content JSONB NOT NULL,
  status TEXT NOT NULL,
  valid_from TIMESTAMP,
  valid_until TIMESTAMP
);

-- Query specific schema version
SELECT * FROM items
WHERE schema_version = '1.0'
  AND status = 'valid';

-- Lazy migration: update one item at a time
UPDATE items SET
  schema_version = '2.0',
  content = jsonb_set(content, '{category}', '"default"')
WHERE id = ? AND schema_version = '1.0';

-- Version 1 schema
CREATE TABLE items_v1 (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT
);

-- Version 2 schema (adds category)
CREATE TABLE items_v2 (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  category TEXT NOT NULL  -- New required field
);

-- Query at version 1
SELECT * FROM items_v1 WHERE id = ?;

-- Migrate on-demand
INSERT INTO items_v2 (id, name, description, category)
SELECT id, name, description, 'default'
FROM items_v1 WHERE id = ?;

// Schema change events
const events = [
  { type: 'schema_defined', version: '1.0',
    fields: ['name', 'description'] },
  { type: 'schema_defined', version: '2.0',
    fields: ['name', 'description', 'category'] },
  { type: 'item_created', id: '123',
    schemaVersion: '1.0', data: {...} },
  { type: 'item_migrated', id: '123',
    fromVersion: '1.0', toVersion: '2.0' }
];

// Reconstruct item at any schema version
function getItemAtVersion(id, targetVersion) {
  // Apply events up to target version
  // Return migrated state if migration exists
}