ctx0 Drizzle Schema Management

This document describes the Drizzle ORM-based schema management system for ctx0. This is the source of truth for database schema definitions.

Overview

ctx0 uses Drizzle ORM for TypeScript-first schema management. The system provides:

1. Type-safe schema definitions - Tables defined in TypeScript with full type inference
2. Automatic migration generation - drizzle-kit generates SQL from schema changes
3. Smart migration command - ctx0 migrate handles both Bytespace and self-hosted setups
4. Schema diffing - ctx0 migrate --status shows exactly what needs to change

Package Structure

packages/ctx0/
├── src/
│   ├── schema/                    # Drizzle schema definitions (SOURCE OF TRUTH)
│   │   ├── index.ts               # Exports all tables + ctx0Tables array
│   │   ├── meta.ts                # Expected functions, triggers, extensions, buckets
│   │   ├── custom-types.ts        # pgvector custom type
│   │   ├── users.ts               # users, ctx0_schema_versions
│   │   ├── config.ts              # ctx0_user_config
│   │   ├── entries.ts             # ctx0_entries, ctx0_entry_versions
│   │   ├── sessions.ts            # ctx0_sessions, ctx0_session_messages
│   │   ├── memory.ts              # ctx0_working_memory, ctx0_learned_queries, ctx0_embedding_queue
│   │   ├── proxy.ts               # ctx0_api_keys, ctx0_llm_usage, ctx0_db_credentials
│   │   ├── devices.ts             # ctx0_devices, ctx0_daemons
│   │   ├── relations.ts           # Foreign key relations
│   │   └── types.ts               # Inferred TypeScript types
│   ├── db/                        # Database utilities
│   │   ├── index.ts               # Re-exports
│   │   ├── client.ts              # Drizzle client creation
│   │   ├── migrate.ts             # Migration runner + schema status
│   │   └── schema-diff.ts         # Schema comparison logic
│   └── migrations.ts              # Legacy SQL exports (for functions, triggers, RLS)
├── migrations/                    # Raw SQL for non-Drizzle things
│   ├── 000_users.sql              # Users table + helper functions
│   ├── 001_ctx0_schema.sql        # Core tables + triggers
│   ├── 002_storage_policies.sql   # Supabase storage policies
│   ├── 003_api_keys.sql           # API key functions
│   ├── 004_llm_usage.sql          # LLM usage tracking functions
│   ├── 005_db_credentials.sql     # DB credentials functions
│   ├── 007_devices.sql            # Device functions + triggers
│   └── 008_daemons.sql            # Daemon triggers
└── drizzle.config.ts              # Drizzle Kit configuration

Source of Truth

What Lives Where

The ctx0Tables Array

All ctx0 tables are listed in a single array in schema/index.ts:

// All ctx0 tables - THE SOURCE OF TRUTH for schema diffing
export const ctx0Tables: Table<any>[] = [
  ctx0SchemaVersions,
  ctx0UserConfig,
  ctx0Entries,
  ctx0EntryVersions,
  ctx0Sessions,
  ctx0SessionMessages,
  ctx0WorkingMemory,
  ctx0LearnedQueries,
  ctx0EmbeddingQueue,
  ctx0ApiKeys,
  ctx0LlmUsage,
  ctx0DbCredentials,
  ctx0Devices,
  ctx0Daemons,
];

The meta.ts File

Non-Drizzle database objects are tracked in schema/meta.ts:

// Extensions
export const REQUIRED_EXTENSIONS = ['uuid-ossp'] as const;
export const OPTIONAL_EXTENSIONS = ['vector'] as const;

// Functions (from SQL migration files)
export const EXPECTED_FUNCTIONS = [
  'ctx0_update_updated_at',
  'ctx0_semantic_search',
  'ctx0_log_llm_usage',
  // ... all function names
] as const;

// Triggers
export const EXPECTED_TRIGGERS = [
  'trigger_users_updated_at',
  'trigger_entries_updated_at',
  // ... all trigger names
] as const;

// Storage buckets
export const EXPECTED_STORAGE_BUCKETS = ['ctx0-vault'] as const;

Developer Workflows

Adding a New Table

1. Create or edit the schema file (e.g., schema/devices.ts):

   typescript

   export const ctx0NewTable = pgTable('ctx0_new_table', {
     id: uuid('id').primaryKey().default(sql`gen_random_uuid()`),
     userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
     // ... columns
   });

2. Add to ctx0Tables array in schema/index.ts:

   typescript

   import { ctx0NewTable } from './your-file';
   
   export const ctx0Tables: Table<any>[] = [
     // ... existing tables
     ctx0NewTable,  // Add here
   ];

3. Run pnpm db:status to verify it shows as missing

4. Run pnpm db:migrate to apply

Adding a New Column

1. Edit the table definition in the schema file
2. Run pnpm db:status to see the diff
3. Run pnpm db:migrate to apply

Adding a New Function

1. Add the SQL to the appropriate migration file in migrations/
2. Add the function name to EXPECTED_FUNCTIONS in schema/meta.ts
3. Run pnpm db:status to verify

Adding a New Trigger

1. Add the SQL to the appropriate migration file
2. Add the trigger name to EXPECTED_TRIGGERS in schema/meta.ts
3. Run pnpm db:status to verify

CLI Commands

Check Schema Status

pnpm db:status

Shows comprehensive database status including:

• Extensions (required and optional)
• Users table existence
• All ctx0 tables with column counts, indexes, RLS status
• Functions (found and missing)
• Triggers (found and missing)
• Vector columns
• Storage buckets
• Schema Changes Needed - shows exactly what columns AND constraints need to be added

Example output:

Schema Changes Needed:
  ⚠ ctx0_daemons:
     + Add columns: device_id
     + Add UNIQUE constraint on (device_id)
     + Add FK: device_id → ctx0_devices.device_id
  ✓ All other tables match expected schema

Summary:
  ⚠ Issues found:
    - 1 functions missing
    - 1 triggers missing
    - 2 constraints missing

Run `pnpm db:migrate` to apply fixes.

Verbose Status

pnpm db:status:verbose

Shows full column details, indexes, and more.

Dry Run Migration

pnpm db:migrate:dry

Shows what would be changed without applying.

Apply Migrations

pnpm db:migrate

Applies all pending schema changes:

• Missing columns are added via ALTER TABLE
• Missing UNIQUE and FK constraints are created
• Missing functions (ctx0_upsert_user) are auto-created
• Missing triggers (trigger_users_updated_at) are auto-created

Example output:

✓ Users table exists (not modified)
✔ Schema synced successfully

  ✓ Added UNIQUE constraint on ctx0_daemons.device_id
  ✓ Added FK constraint ctx0_daemons.device_id -> ctx0_devices.device_id
  ✓ Added trigger trigger_users_updated_at on users table
  ✓ Added function ctx0_upsert_user

Result:
  ✓ Database schema is up to date

How It Works

Smart Detection (Bytespace vs Self-Hosted)

The migrate command automatically detects the environment:

• Bytespace: Users table exists → skip creating it, only sync ctx0_* tables
• Self-hosted: No users table → create it first, then sync ctx0_* tables

Schema Diffing

The schema-diff.ts module provides comprehensive schema comparison:

1. Column Detection: Imports ctx0Tables from schema/index.ts and uses Drizzle's getTableColumns() to extract expected columns
2. Constraint Detection: Extracts UNIQUE constraints from column definitions (.unique()) and FK constraints from Drizzle's internal Symbol(drizzle:PgInlineForeignKeys)
3. Database Comparison: Queries information_schema.columns and information_schema.table_constraints for actual state
4. Diff Reporting: Compares and reports missing columns and constraints

import { getTableColumns } from 'drizzle-orm/utils';
import { getTableName } from 'drizzle-orm';
import { ctx0Tables } from '../schema';

// Extract expected schema including columns
export function getExpectedSchema(): ExpectedTable[] {
  return ctx0Tables.map((table) => {
    const tableName = getTableName(table);
    const columns = getTableColumns(table);
    return {
      name: tableName,
      columns: Object.values(columns).map((col) => ({
        name: col.name,
        nullable: !col.notNull,
        hasDefault: col.hasDefault,
        isUnique: col.isUnique ?? false,
      })),
    };
  });
}

// Extract expected constraints (UNIQUE and FK)
export function getExpectedConstraints(): ExpectedConstraint[] {
  // Extracts from column.isUnique and Symbol(drizzle:PgInlineForeignKeys)
}

Automatic Migration

The applySchemaChanges() function in migrate.ts handles:

1. Missing Columns: Adds columns with appropriate types and nullability
2. Missing UNIQUE Constraints: Creates constraint for columns marked .unique()
3. Missing FK Constraints: Creates foreign key references for columns with .references()
4. Missing Functions: Auto-creates essential functions like ctx0_upsert_user (schema-aware)
5. Missing Triggers: Auto-creates triggers like trigger_users_updated_at

The ctx0_upsert_user function is schema-aware - it dynamically detects available columns in your users table, making it compatible with both simple self-hosted schemas and complex Bytespace schemas.

TypeScript Types

All table types are automatically inferred from Drizzle schemas:

import {
  type DrizzleDevice,
  type DrizzleDaemon,
  type NewDevice,
  type NewDaemon,
} from '@bot0/ctx0';

// Select types (query results)
const device: DrizzleDevice = await db.query.ctx0Devices.findFirst();

// Insert types
const newDevice: NewDevice = {
  userId: user.id,
  deviceId: 'abc123',
  publicKey: 'key...',
  platform: 'macos',
  hardwareType: 'secure_enclave',
};

Why SQL Files Still Exist

Drizzle ORM handles tables, columns, and constraints, but cannot declaratively create:

• PostgreSQL functions
• Triggers
• RLS policies
• Extensions
• Supabase storage buckets

The SQL files in migrations/ contain reference implementations for CREATE FUNCTION, CREATE TRIGGER, etc. The meta.ts file tracks what should exist so ctx0 migrate --status can report missing items.

Auto-Created Objects

Some essential functions and triggers are automatically created by pnpm db:migrate:

This means for most setups, just running pnpm db:migrate will bring the database to a complete state.

Best Practices

1. Always run pnpm db:status before and after changes to verify the state
2. Add new tables to ctx0Tables - this is the single source of truth
3. Add new functions/triggers to meta.ts - otherwise status won't track them
4. Use the Drizzle schema as documentation - it's type-safe and always up to date
5. Test migrations on a staging database before production

Troubleshooting

"Table not showing in status"

Make sure it's added to the ctx0Tables array in schema/index.ts.

"Function shows as missing but exists"

Add the function name to EXPECTED_FUNCTIONS in schema/meta.ts.

"Column changes not detected"

Rebuild the ctx0 package: pnpm --filter @bot0/ctx0 build

"Migration fails"

Check the database connection URL. Run with --dry-run first to see what would change.