Observability Schema & Migration

This document describes the versioning strategy for observability data schemas: the SQLite database schema, the Python query API envelope, and the migration path when backward-incompatible changes are required.

---

1. SQLite Database Schema

The gateway admin SQLite database (gateway_admin.sqlite) is defined by a single canonical DDL source at:

crates/dcc-mcp-db/src/infra/gateway_admin_schema.rs

The DDL is embedded as a const &str and executed with CREATE TABLE IF NOT EXISTS, making it safe to run on every gateway startup.

Current version: 2

Schema version 2 introduced tool_calls, sessions, and session_events tables alongside the original v1 tables (traces, audits, skill_paths_custom, deregistered_instances, skill_loaded_state, skill_active_groups, agent_memory).

Full table inventory

Table	Version added	Purpose
traces	v1	Dispatch trace ring buffer mirror
audits	v1	Audit call record mirror
skill_paths_custom	v1	Admin UI custom skill discovery roots
deregistered_instances	v1	Deregistered gateway instance log
skill_loaded_state	v1	Per-DCC skill load state mirror
skill_active_groups	v1	Per-DCC active skill groups
agent_memory	v1	Agent memory key-value store
tool_calls	v2	Structured tool-call events
sessions	v2	Session lifecycle tracking
session_events	v2	Session lifecycle event log

Index inventory

All indexes use CREATE INDEX IF NOT EXISTS and are idempotent. Adding a new index is a non-breaking change and does not require a schema version bump.

---

2. Query API Versioning

The ObservabilityQuery Python class wraps every response in a versioned envelope defined in python/dcc_mcp_core/observability_query.py:

API_VERSION = "v1"

Envelope

{
  "api_version": "v1",
  "query_type": "session_stats",
  "timestamp_ms": 1700000000000,
  "data": { ... },
  "query_params": { ... },
  "warnings": [ ... ]
}

Version policy

The API_VERSION constant is incremented when:

1. A field is removed or renamed in the data payload.
2. A field type changes (e.g., integer → string).
3. A field's semantics change in a way that would silently break an existing consumer.

The version is NOT incremented for:

• Adding new optional fields to data.
• Adding new query_type values.
• Adding new optional envelope fields (warnings, query_params).
• Performance or correctness changes that preserve the response shape.

Consumer guidance

Consumers that parse the envelope should:

1. Read api_version and hard-fail if it matches an unknown version.
2. Use query_type to dispatch to the correct parser.
3. Ignore unknown fields in data (forward compatibility).
4. Surface warnings when present.

---

3. Migration Procedures

3.1 Adding a new table

1. Add the CREATE TABLE statement to GATEWAY_ADMIN_SQLITE_DDL in gateway_admin_schema.rs.
2. Use CREATE TABLE IF NOT EXISTS — the statement is idempotent.
3. Add matching indexes with CREATE INDEX IF NOT EXISTS.
4. The new table appears on the next gateway restart. Existing data in other tables is unaffected.
5. Update ObservabilityQuery methods to query the new table.
6. Update the metric dictionary and this schema document.

Rollback: Remove the DDL statement and restart the gateway. The table remains on disk (harmless) but is no longer written to. Drop it manually if desired: DROP TABLE IF EXISTS <name>.

3.2 Adding a column to an existing table

1. Write an ALTER TABLE ... ADD COLUMN statement guarded by a schema version check.
2. Alternatively, add the column to the CREATE TABLE IF NOT EXISTS statement — it has no effect on existing tables but ensures new databases include the column from the start.
3. For write paths: the gateway must tolerate the column being absent on old databases (check-and-default before insert).

Preferred pattern — alter on startup when the column is absent:

// Example: adding `llm_usage` to traces
let has_column = db.query_row(
    "SELECT llm_usage FROM traces LIMIT 1",
    [],
    |_| Ok(()),
).is_ok();
if !has_column {
    db.execute_batch("ALTER TABLE traces ADD COLUMN llm_usage TEXT;")?;
}

Rollback: Remove the ALTER TABLE statement and any writes to the new column. The column remains on disk; old gateway versions ignore it.

3.3 Changing a column type

1. Do not use ALTER TABLE ... RENAME COLUMN — SQLite's column rename requires a full table rebuild.
2. Instead, add a new column with the desired type, dual-write both columns for one release cycle, then remove the old column.

Example dual-write cycle:

Release	Action
N	Add duration_ms_new INTEGER, write both duration_ms (TEXT) and duration_ms_new. Readers prefer duration_ms_new when non-NULL.
N+1	Drop duration_ms, rename duration_ms_new → duration_ms, update readers.

3.4 Removing a table or column

1. Stop writing to the deprecated table/column (release N).
2. Remove all reader code (release N+1).
3. Optionally drop the table/column in a schema migration (release N+2). Dropping preserves IF NOT EXISTS safety for rollbacks.

---

4. Query API Migration

Adding a new query type

1. Add a new method to ObservabilityQuery.
2. Use a unique query_type string (e.g., "new_analytics").
3. Register the method in CLI or Admin UI consumers.

No API_VERSION bump needed — existing consumers ignore unknown query_type values if they follow the forward-compatibility guidance above.

Deprecating a field

1. Add the field to warnings list in the response envelope:

   warnings=["field 'old_field' is deprecated, use 'new_field' instead"]

2. Keep the deprecated field in data for at least one release cycle.

3. Remove it on the next API_VERSION bump.

Breaking a backward-incompatible change

1. Increment API_VERSION.
2. Update all first-party consumers (CLI, Admin UI, agent skills) to handle the new version.
3. Document the change in the CHANGELOG with the old/new shapes.

---

5. Migration when the gateway starts

On startup, the gateway:

1. Runs GATEWAY_ADMIN_SQLITE_DDL (idempotent — adds missing tables/indexes).
2. Seads the in-memory trace/audit ring buffers from SQLite (if the admin-persist-sqlite feature is enabled).
3. Writes fresh rows to both the in-memory ring and the SQLite lane.

Because DDL is IF NOT EXISTS, rolling upgrades are safe: old gateways that do not know about new tables simply ignore them, and new gateways create missing tables automatically on first startup.

---

6. Observability API Version History

API version	Schema version	Release	Changes
v1	1	0.19.x	Original: traces, audits, skill paths, deregistered instances, agent memory
v1	2	0.20.x	Added tool_calls, sessions, session_events tables. Query API unchanged.