Brokle Migration Workflow

Advanced CLI Commands

# Run migrations for specific databases
go run cmd/migrate/main.go -db postgres up
go run cmd/migrate/main.go -db clickhouse up
go run cmd/migrate/main.go up  # All databases

# Check detailed migration status
go run cmd/migrate/main.go -db postgres status
go run cmd/migrate/main.go -db clickhouse status
go run cmd/migrate/main.go status  # Both with health check

# Rollback migrations (requires confirmation)
go run cmd/migrate/main.go -db postgres down
go run cmd/migrate/main.go -db clickhouse down
go run cmd/migrate/main.go down  # Both databases

# Create new migrations
go run cmd/migrate/main.go -db postgres -name create_users_table create
go run cmd/migrate/main.go -db clickhouse -name create_metrics_table create

# Destructive operations (requires 'yes' confirmation)
go run cmd/migrate/main.go -db postgres drop
go run cmd/migrate/main.go -db clickhouse drop

# Granular step control
go run cmd/migrate/main.go -db postgres -steps 2 up      # Run 2 forward
go run cmd/migrate/main.go -db postgres -steps -1 down   # Rollback 1

# Force operations (DANGEROUS - use only when dirty)
go run cmd/migrate/main.go -db postgres -version 0 force
go run cmd/migrate/main.go -db clickhouse -version 5 force

# Information and debugging
go run cmd/migrate/main.go info
go run cmd/migrate/main.go -dry-run up  # Preview without executing

Database Schema Guidelines

PostgreSQL (Transactional Data)

• Users, authentication, sessions
• Organizations, projects, API keys
• Gateway configurations
• Billing and subscriptions

ClickHouse (Analytics)

Primary tables in migrations/clickhouse/:

• traces - Distributed tracing data
• spans - LLM call spans with ZSTD compression
• quality_scores - Model performance metrics
• blob_storage_file_log - File storage metadata

TTL: All tables use 365-day retention Reference: Check migrations/clickhouse/*.up.sql for exact schema, TTL configuration, and indexes

Creating PostgreSQL Migration

-- migrations/000042_add_feature_table.up.sql
-- +migrate Up
CREATE TABLE IF NOT EXISTS feature_table (
    id VARCHAR(26) PRIMARY KEY,
    organization_id VARCHAR(26) NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_feature_table_organization_id ON feature_table(organization_id);
CREATE INDEX idx_feature_table_status ON feature_table(status);

-- +migrate Down
DROP INDEX IF EXISTS idx_feature_table_status;
DROP INDEX IF EXISTS idx_feature_table_organization_id;
DROP TABLE IF EXISTS feature_table;

Creating ClickHouse Migration

-- migrations/clickhouse/000005_add_metrics_table.up.sql
-- +migrate Up
CREATE TABLE IF NOT EXISTS metrics (
    id String,
    organization_id String,
    project_id String,
    metric_name String,
    metric_value Float64,
    timestamp DateTime64(3),
    metadata String,  -- JSON stored as String
    created_at DateTime64(3) DEFAULT now64(3)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (organization_id, project_id, timestamp)
TTL toDateTime(timestamp) + INTERVAL 365 DAY
SETTINGS index_granularity = 8192;

-- +migrate Down
DROP TABLE IF EXISTS metrics;

ClickHouse Best Practices

OTEL-Native Schema Pattern

CREATE TABLE spans (
    -- Core fields
    id String,
    trace_id String,

    -- OTEL-native: All attributes in one field with namespace prefixes
    attributes String,  -- {"brokle.model": "gpt-4", "brokle.tokens": 150}

    -- OTEL metadata
    metadata String,  -- {"resourceAttributes": {...}, "instrumentationScope": {...}}

    -- Timestamps
    start_time DateTime64(3),
    end_time Nullable(DateTime64(3)),

    -- ZSTD compression for large fields
    input Nullable(String) CODEC(ZSTD),
    output Nullable(String) CODEC(ZSTD),

    -- Application version for A/B testing
    version Nullable(String),

    created_at DateTime64(3) DEFAULT now64(3)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(start_time)
ORDER BY (trace_id, start_time)
TTL start_time + INTERVAL 90 DAY
SETTINGS index_granularity = 8192;

Performance Patterns

1. Partitioning: PARTITION BY toYYYYMM(timestamp) for time-series
2. Ordering: ORDER BY for query optimization
3. TTL: TTL toDateTime(timestamp) + INTERVAL 365 DAY for automatic retention
4. Compression: CODEC(ZSTD) for large text fields (78% cost reduction)
5. Index Granularity: index_granularity = 8192 for optimal performance
6. Engine: ReplacingMergeTree(event_ts, is_deleted) for soft-delete support

Data Types

• IDs: String (not UUID)
• Timestamps: DateTime64(3) for millisecond precision
• JSON: String (store as JSON string, query with JSON functions)
• Optional: Nullable(Type)

Seeding Data

Seed File Structure (seeds/)

# seeds/dev.yaml