schema-versioning.md

ctx0 Schema Versioning System

This document describes the planned schema versioning and migration system for self-hosted ctx0 users.

Overview

The schema versioning system provides automatic updates for self-hosted ctx0 installations:

  1. Version Tracking - Track applied migrations in the database
  2. Update Detection - Daemon periodically checks for new migrations
  3. Desktop Notifications - Alert users when updates are available
  4. User-Approved Migration - Apply migrations only with user consent

Architecture

┌─────────────────────────────────────────────────────────────────┐
│                     Desktop App                                  │
│  ┌────────────────────────────────────────────────────────────┐ │
│  │  Schema Update Notification Banner                          │ │
│  │  "A new ctx0 schema update is available (v2 → v3)"         │ │
│  │  [View Changes]  [Apply Update]  [Dismiss]                  │ │
│  └────────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
                              ▲
                              │ IPC: schema:updateAvailable
                              │
┌─────────────────────────────────────────────────────────────────┐
│                        Daemon                                    │
│  ┌────────────────────────────────────────────────────────────┐ │
│  │  SchemaVersionManager                                       │ │
│  │  - checkForUpdates()  → compares local vs DB version        │ │
│  │  - applyMigration()   → runs SQL with user approval         │ │
│  │  - getChangeLog()     → returns migration descriptions      │ │
│  └────────────────────────────────────────────────────────────┘ │
│                              │                                   │
│  ┌────────────────────────────────────────────────────────────┐ │
│  │  Scheduler (setInterval)                                    │ │
│  │  - Runs every 6 hours when ctx0 self-hosted is configured   │ │
│  │  - Emits 'schema:updateAvailable' when new version found    │ │
│  └────────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│                    Supabase (Self-Hosted)                        │
│  ┌────────────────────────────────────────────────────────────┐ │
│  │  ctx0_schema_versions                                       │ │
│  │  - version: 1, 2, 3...                                      │ │
│  │  - applied_at: timestamp                                    │ │
│  │  - migration_name: "001_ctx0_schema"                        │ │
│  └────────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────┘

Schema Version Tracking Table

A new table ctx0_schema_versions tracks which migrations have been applied:

sql
CREATE TABLE IF NOT EXISTS ctx0_schema_versions (
  id SERIAL PRIMARY KEY,
  version INT NOT NULL UNIQUE,
  migration_name TEXT NOT NULL,
  description TEXT,
  applied_at TIMESTAMPTZ DEFAULT NOW(),
  applied_by TEXT DEFAULT 'bot0-daemon'
);

Migration File Structure

packages/ctx0/migrations/
├── 001_ctx0_schema.sql           # Initial schema
├── 002_schema_versioning.sql     # Version tracking table
├── 003_future_migration.sql      # Future migrations...
└── manifest.json                 # Migration metadata

manifest.json

The manifest describes all available migrations:

json
{
  "currentVersion": 2,
  "migrations": [
    {
      "version": 1,
      "name": "001_ctx0_schema",
      "description": "Initial ctx0 schema with entries, sessions, queries",
      "file": "001_ctx0_schema.sql"
    },
    {
      "version": 2,
      "name": "002_schema_versioning",
      "description": "Added schema version tracking table",
      "file": "002_schema_versioning.sql"
    }
  ]
}

Daemon Components

SchemaVersionManager

The SchemaVersionManager class handles version checking and migration execution:

typescript
class SchemaVersionManager {
  constructor(config: { supabaseUrl: string; supabaseServiceKey: string });

  // Get current schema version from database (0 if table doesn't exist)
  async getCurrentVersion(): Promise<number>;

  // Get latest available version from bundled migrations
  getLatestVersion(): number;

  // Check if updates are available
  async checkForUpdates(): Promise<{
    hasUpdates: boolean;
    currentVersion: number;
    latestVersion: number;
    pendingMigrations: SchemaVersion[];
  }>;

  // Apply a specific migration
  async applyMigration(version: number): Promise<{ success: boolean; error?: string }>;

  // Apply all pending migrations in order
  async applyAllPending(): Promise<{ success: boolean; applied: number[]; error?: string }>;
}

Scheduler

The daemon runs a scheduler when ctx0 is configured in self-hosted mode:

  • Immediate check on daemon startup
  • Periodic check every 6 hours
  • Broadcasts schema:updateAvailable event to desktop when updates found

IPC Methods

schema:checkUpdates

Returns information about available schema updates.

Response:

typescript
{
  hasUpdates: boolean;
  currentVersion: number;
  latestVersion: number;
  pendingMigrations: Array<{
    version: number;
    migration_name: string;
    description: string;
  }>;
}

schema:applyUpdates

Applies all pending migrations in order.

Response:

typescript
{
  success: boolean;
  applied: number[];  // Version numbers that were applied
  error?: string;
}

Desktop UI

Schema Update Banner

When updates are available, the desktop app displays a notification banner:

┌─────────────────────────────────────────────────────────────────┐
│ ● ctx0 schema update available (v1 → v2)                        │
│                                                                  │
│                    [View Changes]  [Apply Update]  [×]          │
└─────────────────────────────────────────────────────────────────┘

Features:

  • Shows current and target version
  • "View Changes" expands to show migration descriptions
  • "Apply Update" runs migrations with loading state
  • Dismiss button to hide (until next check)

Expanded View

┌─────────────────────────────────────────────────────────────────┐
│ ● ctx0 schema update available (v1 → v2)                        │
│                                                                  │
│   │ v2: Added schema version tracking table                     │
│                                                                  │
│                    [Hide Changes]  [Apply Update]  [×]          │
└─────────────────────────────────────────────────────────────────┘

User Flow

  1. User sets up self-hosted ctx0 during onboarding
  2. User runs the initial schema migration (001_ctx0_schema.sql)
  3. Daemon starts and detects ctx0 is configured
  4. Daemon checks current version vs available migrations
  5. If updates available, daemon broadcasts to desktop
  6. Desktop shows notification banner
  7. User clicks "Apply Update"
  8. Daemon executes migrations in order
  9. Success message shown, banner dismissed

Implementation Phases

Phase 1: Database Schema

  • Create 002_schema_versioning.sql migration
  • Create manifest.json with migration metadata

Phase 2: Daemon Backend

  • Create SchemaVersionManager class
  • Add IPC handlers for schema methods
  • Add scheduler to daemon startup

Phase 3: Desktop IPC Layer

  • Add schema methods to daemon-client
  • Expose schema API in preload
  • Forward daemon broadcasts to renderer

Phase 4: UI Component

  • Create SchemaUpdateBanner component
  • Integrate into Terminal with state management
  • Style to match dark theme

Future Considerations

  • Rollback Support - Ability to rollback migrations (complex)
  • Migration Lock - Prevent concurrent migration execution
  • Backup Reminder - Prompt user to backup before major migrations
  • Change Preview - Show SQL diff before applying
  • Bytespace Sync - Coordinate with hosted version updates