# Can CCX Persist Metrics to SQLite? Implementation Guide and Configuration

> Yes CCX can persist metrics to SQLite using SQLiteStore. Learn how to configure retention periods, WAL mode, and automatic schema migrations in this implementation guide.

- Repository: [Benedict King/ccx](https://github.com/BenedictKing/ccx)
- Tags: how-to-guide
- Published: 2026-05-29

---

**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`](https://github.com/BenedictKing/ccx/blob/main/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`](https://github.com/BenedictKing/ccx/blob/main/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`](https://github.com/BenedictKing/ccx/blob/main/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`](https://github.com/BenedictKing/ccx/blob/main/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).

```go
// 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.

```go
// 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`](https://github.com/BenedictKing/ccx/blob/main/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`](https://github.com/BenedictKing/ccx/blob/main/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`](https://github.com/BenedictKing/ccx/blob/main/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.