Can CCX Persist Metrics to SQLite? Implementation Guide and Configuration

Yes, CCX can persist metrics to SQLite using the SQLiteStore type which implements the PersistenceStore interface, supporting configurable retention periods, WAL mode, and automatic schema migrations.

CCX, an open-source proxy and load balancer, provides robust observability through SQLite-based metric persistence. This capability allows you to maintain long-term records of request-level metrics and circuit-breaker states across service restarts. The implementation leverages a dedicated storage layer in the Go backend that batches writes and enforces retention policies automatically.

How CCX Implements SQLite Metric Persistence

The persistence architecture in CCX follows a clean interface-based design that abstracts storage concerns from business logic.

The PersistenceStore Interface

All metric-related storage implementations satisfy the PersistenceStore interface defined in backend-go/internal/metrics/persistence.go. This contract exposes methods for adding records, loading historic data, cleaning up old entries, and managing circuit-breaker state.

SQLiteStore Implementation

The SQLiteStore type located in backend-go/internal/metrics/sqlite_store.go provides the concrete SQLite implementation. It opens a database at the default path .config/metrics.db in WAL (Write-Ahead Logging) mode for improved concurrency. The schema includes two primary tables: request_records and circuit_states, plus several indexes optimized for time-range queries. According to the source code, this implementation satisfies the PersistenceStore interface while adding SQLite-specific optimizations.

Configuration and Environment Setup

Persistence behavior is controlled through environment variables read during service initialization in backend-go/internal/config/env.go.

Key configuration variables:

• METRICS_PERSISTENCE_ENABLED – Toggles persistence globally (default: true)
• METRICS_RETENTION_DAYS – Bounded between 3 and 90 days
• METRICS_WINDOW_SIZE – Sliding window size in minutes for circuit-breaker calculations
• METRICS_FAILURE_THRESHOLD – Threshold for triggering circuit-breaker states

During service startup in backend-go/main.go, the application creates the store when persistence is enabled, runs schema migrations to upgrade legacy metric keys, and injects the store into a MetricsManager for each API type (messages, responses, gemini, chat, and images).

// Enable persistence via environment variables
// METRICS_PERSISTENCE_ENABLED=true
// METRICS_RETENTION_DAYS=30
// METRICS_WINDOW_SIZE=10
// METRICS_FAILURE_THRESHOLD=0.5

if envCfg.MetricsPersistenceEnabled {
    store, err := metrics.NewSQLiteStore(&metrics.SQLiteStoreConfig{
        DBPath:        ".config/metrics.db",
        RetentionDays: envCfg.MetricsRetentionDays,
    })
    if err != nil {
        log.Printf("[Metrics‑Init] SQLite init failed: %v, falling back to in‑memory", err)
    } else {
        // Migrate any legacy metric keys
        _ = store.MigrateMetricsKeysToIdentity(cfgManager.GetConfig())
        // Attach to a manager for the "messages" API
        messagesMgr := metrics.NewMetricsManagerWithPersistence(
            envCfg.MetricsWindowSize,
            envCfg.MetricsFailureThreshold,
            store,
            "messages",
        )
        // …
    }
}

Database Schema and Migrations

CCX handles schema evolution automatically through a migration system integrated into NewSQLiteStore.

Schema initialization includes:

• Version tracking via PRAGMA user_version for incremental migrations
• Forward compatibility through initSchema which creates tables on first start
• Column additions including the model column and failure_class column for enhanced observability

This migration strategy ensures that upgrading CCX does not require manual database maintenance, making the persistence layer forward-compatible.

Background Maintenance and Concurrency

The SQLiteStore runs two background goroutines to maintain database health without blocking request handling.

Flush Loop: Buffers incoming metric records in memory and periodically persisting them to SQLite in batches. This reduces write amplification and improves throughput during high-load scenarios.

Cleanup Loop: Automatically deletes records older than the configured retention period, ensuring the database file size remains bounded over time.

Both loops coordinate via the flushMu mutex to prevent race conditions during concurrent access, as implemented in the store's internal synchronization logic.

Recording and Querying Metrics

Once initialized, the store accepts PersistentRecord structs containing detailed request telemetry. You can manually flush the buffer before snapshots or shutdowns.

// Adding a metric record (called from request handling code)
rec := metrics.PersistentRecord{
    MetricsKey:          metrics.GenerateMetricsKey(baseURL, apiKey),
    BaseURL:             baseURL,
    KeyMask:             utils.MaskKey(apiKey),
    Timestamp:           time.Now(),
    Success:             true,
    FailureClass:        metrics.FailureNone,
    InputTokens:         123,
    OutputTokens:        456,
    CacheCreationTokens: 0,
    CacheReadTokens:     0,
    Model:               "gpt‑4o",
    APIType:             "messages",
}
metricsStore.AddRecord(rec)

// Query recent records (e.g., for a dashboard)
since := time.Now().Add(-24 * time.Hour)
records, _ := metricsStore.LoadRecords(since, "messages")
for _, r := range records {
    fmt.Printf("%s – %s – success=%v\n", r.Timestamp, r.MetricsKey, r.Success)
}

// Force a manual flush (useful before a snapshot)
metricsStore.Flush()

The LoadRecords method supports time-range queries filtered by API type, enabling efficient retrieval of historical data for dashboards or debugging purposes.

Summary

• CCX persists metrics to SQLite through the SQLiteStore implementation of the PersistenceStore interface in backend-go/internal/metrics/sqlite_store.go.
• Configuration occurs via METRICS_PERSISTENCE_ENABLED and METRICS_RETENTION_DAYS, parsed in backend-go/internal/config/env.go.
• Automatic migrations handle schema updates forward-compatibly using PRAGMA user_version and incremental column additions.
• Background processes manage batched writes and retention-based cleanup using flushMu for thread safety.
• Data model includes request_records and circuit_states tables with optimized indexes for time-series queries.

Frequently Asked Questions

Where does CCX store the SQLite database file?

By default, CCX creates the database at .config/metrics.db relative to the working directory. You can customize this path by passing a different DBPath value to NewSQLiteStore during initialization in main.go.

Can I disable metric persistence in CCX?

Yes. Set the environment variable METRICS_PERSISTENCE_ENABLED=false to disable SQLite persistence. When disabled, CCX falls back to in-memory storage provided by MetricsManager, though metrics will be lost on service restart.

How does CCX handle schema migrations for the metrics database?

The NewSQLiteStore constructor automatically triggers initSchema, which creates tables if missing and applies incremental migrations based on PRAGMA user_version. Recent migrations added the model and failure_class columns, ensuring the database evolves with new CCX versions without manual intervention.

What retention policies does CCX enforce for SQLite metrics?

CCX enforces a bounded retention window between 3 and 90 days via METRICS_RETENTION_DAYS. A background cleanup loop in SQLiteStore periodically deletes records older than this threshold, preventing unbounded database growth while maintaining compliance with the configured observability window.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}