Make Codex and Claude Code subscriptions go further: a local model router that gives routine agent work to economical models and keeps your strongest model for the hard parts.
Rust ai-agentsai-gatewayclaude-codecodexcoding-agentcost-optimizationdeepseekllm-gatewayllm-routerlocal-firstmodel-routingrusttask-routingtauri
use hiroute_domain::{CanonicalDigest, ValueLedgerEntryV1};
use rusqlite::{Connection, Transaction, params};
use crate::writer::ObservationStoreError;
pub(super) const STORE_SCHEMA_VERSION: &str = "5";
const LEGACY_AGENT_PLAN_ID: &str = "legacy/unknown";
pub(super) fn migrate(connection: &mut Connection) -> Result<(), ObservationStoreError> {
migrate_inner(connection, true)
}
#[cfg(test)]
pub(super) fn migrate_legacy(connection: &mut Connection) -> Result<(), ObservationStoreError> {
migrate_inner(connection, false)
}
fn migrate_inner(connection: &mut Connection, compact: bool) -> Result<(), ObservationStoreError> {
if connection
.query_row(
"SELECT value FROM observation_meta WHERE key='schema_version'",
[],
|r| r.get::<_, String>(0),
)
.ok()
.as_deref()
== Some("5")
{
let tables:u64=connection.query_row("SELECT COUNT(*) FROM sqlite_schema WHERE type='table' AND name IN('logical_requests','observation_streams_v5','observation_events','observation_checkpoints','observation_feedback_v2','conversation_content_events_v2','content_blobs_v2','content_instances_v2')",[],|r|r.get(0)).map_err(|_|ObservationStoreError::Corrupt)?;
if tables != 8 {
return Err(ObservationStoreError::Corrupt);
}
connection
.execute_batch(
"PRAGMA journal_mode=WAL; PRAGMA synchronous=FULL; PRAGMA foreign_keys=ON;",
)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
return create_schema_v5(connection);
}
connection
.execute_batch(
r#"
PRAGMA journal_mode = WAL;
PRAGMA synchronous = FULL;
PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS observation_meta (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);
INSERT OR IGNORE INTO observation_meta(key, value) VALUES
('schema_version', '4'),
('store_revision', '0');
"#,
)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
let version: String = connection
.query_row(
"SELECT value FROM observation_meta WHERE key='schema_version'",
[],
|row| row.get(0),
)
.map_err(|_| ObservationStoreError::Corrupt)?;
let result = match version.as_str() {
"1" => {
migrate_v1_to_v2(connection)?;
migrate_v2_to_v3(connection)?;
migrate_v3_to_v4(connection)
}
"2" => {
migrate_v2_to_v3(connection)?;
migrate_v3_to_v4(connection)
}
"3" => migrate_v3_to_v4(connection),
"4" => create_schema_v4(connection),
STORE_SCHEMA_VERSION => Ok(()),
_ => Err(ObservationStoreError::Corrupt),
};
result?;
if !compact || version == "5" {
return Ok(());
}
let populated: bool = connection.query_row("SELECT EXISTS(SELECT 1 FROM observation_events) OR EXISTS(SELECT 1 FROM conversation_content_events_v2) OR EXISTS(SELECT 1 FROM observation_feedback_v2)",[],|r|r.get(0)).map_err(|_|ObservationStoreError::Corrupt)?;
if populated {
return Err(ObservationStoreError::Corrupt);
}
connection.execute_batch("DROP TABLE observation_events; DROP TABLE conversation_content_events_v2; DROP TABLE observation_feedback_v2;").map_err(|_|ObservationStoreError::ActivityUnavailable)?;
connection
.execute_batch(super::records::SCHEMA)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
super::records::install_reference_triggers(connection)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
connection
.execute(
"UPDATE observation_meta SET value='5' WHERE key='schema_version'",
[],
)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
Ok(())
}
fn create_schema_v5(connection: &Connection) -> Result<(), ObservationStoreError> {
fn omit(mut sql: String, header: &str) -> String {
let start = sql.find(header).expect("known legacy definition");
let end = start + sql[start..].find(';').expect("known complete definition") + 1;
sql.replace_range(start..end, "");
sql
}
let auxiliary = omit(
AUXILIARY_SCHEMA_V2.to_owned(),
"CREATE TABLE IF NOT EXISTS observation_events",
);
let content = omit(
CONVERSATION_CONTENT_SCHEMA_V4.to_owned(),
"CREATE TABLE IF NOT EXISTS conversation_content_events_v2",
);
let content = omit(
content,
"CREATE TABLE IF NOT EXISTS observation_feedback_v2",
);
let content = omit(
content,
"CREATE INDEX IF NOT EXISTS observation_feedback_v2_lookup",
);
for ddl in [
BASE_SCHEMA_V2,
super::agent_attribution::SCHEMA,
VALUE_SCHEMA_V2,
auxiliary.as_str(),
EXECUTION_FACT_SCHEMA_V3,
super::records::SCHEMA,
content.as_str(),
] {
connection
.execute_batch(ddl)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
}
super::records::install_reference_triggers(connection)
.map_err(|_| ObservationStoreError::ActivityUnavailable)
}
fn create_schema_v2(connection: &Connection) -> Result<(), ObservationStoreError> {
connection
.execute_batch(BASE_SCHEMA_V2)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
connection
.execute_batch(super::agent_attribution::SCHEMA)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
connection
.execute_batch(VALUE_SCHEMA_V2)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
connection
.execute_batch(AUXILIARY_SCHEMA_V2)
.map_err(|_| ObservationStoreError::ActivityUnavailable)
}
fn create_schema_v3(connection: &Connection) -> Result<(), ObservationStoreError> {
create_schema_v2(connection)?;
connection
.execute_batch(EXECUTION_FACT_SCHEMA_V3)
.map_err(|_| ObservationStoreError::ActivityUnavailable)
}
fn create_schema_v4(connection: &Connection) -> Result<(), ObservationStoreError> {
create_schema_v3(connection)?;
ensure_gap_reason_column(connection)?;
connection
.execute_batch(CONVERSATION_CONTENT_SCHEMA_V4)
.map_err(|_| ObservationStoreError::ActivityUnavailable)
}
fn migrate_v3_to_v4(connection: &mut Connection) -> Result<(), ObservationStoreError> {
let transaction = connection
.transaction()
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
retire_empty_legacy_content_contract(&transaction)?;
ensure_gap_reason_column(&transaction)?;
transaction
.execute_batch(CONVERSATION_CONTENT_SCHEMA_V4)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
transaction
.execute(
"UPDATE observation_meta SET value='4' WHERE key='schema_version'",
[],
)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
transaction
.commit()
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
create_schema_v4(connection)
}
fn retire_empty_legacy_content_contract(
transaction: &Transaction<'_>,
) -> Result<(), ObservationStoreError> {
const LEGACY_TABLES: &[&str] = &[
"content_streams",
"content_blobs",
"message_instances",
"transcript_roots",
];
for table in LEGACY_TABLES {
if table_exists(transaction, table)?
&& transaction
.query_row(
&format!("SELECT EXISTS(SELECT 1 FROM {table} LIMIT 1)"),
[],
|row| row.get::<_, bool>(0),
)
.map_err(|_| ObservationStoreError::Corrupt)?
{
// The fake per-content contract has no direction/fork stream, Gateway digest, or
// rich-feedback coordinates. Rewriting populated rows would invent frozen v2 facts;
// keep the v3 database untouched and require an explicit data disposition instead.
return Err(ObservationStoreError::Corrupt);
}
}
for table in [
"observation_events",
"observation_checkpoints",
"observation_gaps",
] {
if table_exists(transaction, table)?
&& transaction
.query_row(
&format!(
"SELECT EXISTS(SELECT 1 FROM {table} WHERE channel='content' LIMIT 1)"
),
[],
|row| row.get::<_, bool>(0),
)
.map_err(|_| ObservationStoreError::Corrupt)?
{
return Err(ObservationStoreError::Corrupt);
}
}
transaction
.execute_batch(
"DROP TABLE IF EXISTS content_streams;
DROP TABLE IF EXISTS content_blobs;
DROP TABLE IF EXISTS message_instances;
DROP TABLE IF EXISTS transcript_roots;",
)
.map_err(|_| ObservationStoreError::ActivityUnavailable)
}
fn table_exists(transaction: &Transaction<'_>, table: &str) -> Result<bool, ObservationStoreError> {
transaction
.query_row(
"SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE type='table' AND name=?1)",
[table],
|row| row.get(0),
)
.map_err(|_| ObservationStoreError::Corrupt)
}
fn ensure_gap_reason_column(connection: &Connection) -> Result<(), ObservationStoreError> {
let has_reason = {
let mut statement = connection
.prepare("PRAGMA table_info(observation_gaps)")
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
let columns = statement
.query_map([], |row| row.get::<_, String>(1))
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
let columns = columns
.collect::<Result<Vec<_>, _>>()
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
columns.iter().any(|column| column == "reason")
};
if !has_reason {
connection
.execute("ALTER TABLE observation_gaps ADD COLUMN reason TEXT", [])
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
}
Ok(())
}
fn migrate_v2_to_v3(connection: &mut Connection) -> Result<(), ObservationStoreError> {
let transaction = connection
.transaction()
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
transaction
.execute_batch(EXECUTION_FACT_SCHEMA_V3)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
transaction
.execute(
"UPDATE observation_meta SET value='3' WHERE key='schema_version'",
[],
)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
transaction
.commit()
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
create_schema_v3(connection)
}
fn migrate_v1_to_v2(connection: &mut Connection) -> Result<(), ObservationStoreError> {
let transaction = connection
.transaction()
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
transaction
.execute_batch(
r#"
ALTER TABLE value_ledger_entries RENAME TO value_ledger_entries_v1;
ALTER TABLE daily_value_rollups RENAME TO daily_value_rollups_v1;
"#,
)
.map_err(|_| ObservationStoreError::Corrupt)?;
transaction
.execute_batch(VALUE_SCHEMA_V2)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
let legacy_entries = {
let mut statement = transaction
.prepare(
"SELECT body_json FROM value_ledger_entries_v1 ORDER BY workspace_id, request_id",
)
.map_err(|_| ObservationStoreError::Corrupt)?;
statement
.query_map([], |row| row.get::<_, String>(0))
.map_err(|_| ObservationStoreError::Corrupt)?
.collect::<Result<Vec<_>, _>>()
.map_err(|_| ObservationStoreError::Corrupt)?
};
for body in legacy_entries {
let entry = migrate_legacy_entry(&body)?;
insert_migrated_entry(&transaction, &entry)?;
}
// V1 rollups irreversibly mixed plans and currencies and have no per-session provenance.
// Keeping or guessing those axes would expose a false authoritative ledger and would make
// exact session deletion impossible. Detail entries above retain every attributable fact;
// only the already-conflated derived rows are retired during the upgrade.
transaction
.execute_batch(
r#"
DROP TABLE value_ledger_entries_v1;
DROP TABLE daily_value_rollups_v1;
CREATE INDEX IF NOT EXISTS value_time
ON value_ledger_entries(workspace_id, agent_plan_id, currency, frozen_at_ms);
UPDATE observation_meta SET value='2' WHERE key='schema_version';
"#,
)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
transaction
.commit()
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
create_schema_v2(connection)
}
fn migrate_legacy_entry(body: &str) -> Result<ValueLedgerEntryV1, ObservationStoreError> {
let mut body: serde_json::Value =
serde_json::from_str(body).map_err(|_| ObservationStoreError::Corrupt)?;
let frozen = body
.get_mut("frozen")
.and_then(serde_json::Value::as_object_mut)
.ok_or(ObservationStoreError::Corrupt)?;
let chosen = take_legacy_u64(frozen, "api_equivalent_micros")?;
let actual = take_legacy_u64(frozen, "incremental_cost_micros")?;
let legacy_savings = take_legacy_u64(frozen, "savings_micros")?;
let entitlement = signed_difference(chosen, actual)?;
if i64::try_from(legacy_savings).map_err(|_| ObservationStoreError::Corrupt)? != entitlement {
return Err(ObservationStoreError::Corrupt);
}
frozen.insert(
"agent_plan_id".to_owned(),
serde_json::Value::String(LEGACY_AGENT_PLAN_ID.to_owned()),
);
frozen.insert(
"baseline_api_equivalent_cost_micros".to_owned(),
serde_json::Value::Null,
);
frozen.insert(
"chosen_api_equivalent_cost_micros".to_owned(),
serde_json::Value::from(chosen),
);
frozen.insert(
"actual_incremental_cost_micros".to_owned(),
serde_json::Value::from(actual),
);
frozen.insert("routing_savings_micros".to_owned(), serde_json::Value::Null);
frozen.insert(
"entitlement_savings_micros".to_owned(),
serde_json::Value::from(entitlement),
);
frozen.insert(
"estimated_total_savings_micros".to_owned(),
serde_json::Value::Null,
);
let entry: ValueLedgerEntryV1 =
serde_json::from_value(body).map_err(|_| ObservationStoreError::Corrupt)?;
entry
.validate()
.map_err(|_| ObservationStoreError::Corrupt)?;
Ok(entry)
}
fn take_legacy_u64(
object: &mut serde_json::Map<String, serde_json::Value>,
key: &str,
) -> Result<u64, ObservationStoreError> {
object
.remove(key)
.and_then(|value| value.as_u64())
.ok_or(ObservationStoreError::Corrupt)
}
fn signed_difference(left: u64, right: u64) -> Result<i64, ObservationStoreError> {
let left = i64::try_from(left).map_err(|_| ObservationStoreError::Corrupt)?;
let right = i64::try_from(right).map_err(|_| ObservationStoreError::Corrupt)?;
left.checked_sub(right)
.ok_or(ObservationStoreError::Corrupt)
}
fn insert_migrated_entry(
transaction: &Transaction<'_>,
entry: &ValueLedgerEntryV1,
) -> Result<(), ObservationStoreError> {
let body = serde_json::to_string(entry).map_err(|_| ObservationStoreError::Corrupt)?;
let digest = CanonicalDigest::of(entry).map_err(|_| ObservationStoreError::Corrupt)?;
let frozen = &entry.frozen;
transaction
.execute(
"INSERT INTO value_ledger_entries
(workspace_id, request_id, session_id, receipt_id, body_json, body_digest,
agent_plan_id, currency, billing_unit, price_version, price_override_revision,
input_tokens, output_tokens, cache_read_tokens, cache_write_tokens, reasoning_tokens,
baseline_api_equivalent_cost_micros, chosen_api_equivalent_cost_micros,
actual_incremental_cost_micros, routing_savings_micros,
entitlement_savings_micros, estimated_total_savings_micros,
facts_completeness, frozen_at_ms)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14,
?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22, ?23, ?24)",
params![
entry.workspace_id.as_str(),
entry.request_id.as_str(),
entry.session_id.as_str(),
entry.receipt_id.as_str(),
body,
digest.as_str(),
frozen.agent_plan_id.as_str(),
frozen.currency,
frozen.billing_unit,
frozen.price_version,
frozen.price_override_revision,
to_sql_u64(entry.usage.input_tokens)?,
to_sql_u64(entry.usage.output_tokens)?,
to_sql_u64(entry.usage.cache_read_tokens)?,
to_sql_u64(entry.usage.cache_write_tokens)?,
to_sql_u64(entry.usage.reasoning_tokens)?,
optional_sql_u64(frozen.baseline_api_equivalent_cost_micros)?,
optional_sql_u64(frozen.chosen_api_equivalent_cost_micros)?,
optional_sql_u64(frozen.actual_incremental_cost_micros)?,
frozen.routing_savings_micros,
frozen.entitlement_savings_micros,
frozen.estimated_total_savings_micros,
enum_name(entry.facts_completeness)?,
entry.frozen_at_ms,
],
)
.map_err(|_| ObservationStoreError::ActivityUnavailable)?;
Ok(())
}
fn to_sql_u64(value: u64) -> Result<i64, ObservationStoreError> {
value.try_into().map_err(|_| ObservationStoreError::Corrupt)
}
fn optional_sql_u64(value: Option<u64>) -> Result<Option<i64>, ObservationStoreError> {
value.map(to_sql_u64).transpose()
}
fn enum_name<T: serde::Serialize>(value: T) -> Result<String, ObservationStoreError> {
serde_json::to_value(value)
.ok()
.and_then(|value| value.as_str().map(str::to_owned))
.ok_or(ObservationStoreError::Corrupt)
}
const BASE_SCHEMA_V2: &str = r#"
CREATE TABLE IF NOT EXISTS sessions (
workspace_id TEXT NOT NULL,
session_id TEXT NOT NULL,
agent_id TEXT NOT NULL DEFAULT '',
correlation TEXT NOT NULL DEFAULT 'unproven',
started_at_ms INTEGER NOT NULL,
updated_at_ms INTEGER NOT NULL,
facts_completeness TEXT NOT NULL DEFAULT 'unknown',
content_completeness TEXT NOT NULL DEFAULT 'unknown',
tombstone_reason TEXT,
PRIMARY KEY(workspace_id, session_id)
);
CREATE INDEX IF NOT EXISTS sessions_updated
ON sessions(workspace_id, updated_at_ms DESC);
CREATE TABLE IF NOT EXISTS turns (
workspace_id TEXT NOT NULL,
turn_id TEXT NOT NULL,
session_id TEXT NOT NULL,
started_at_ms INTEGER NOT NULL,
PRIMARY KEY(workspace_id, turn_id)
);
CREATE INDEX IF NOT EXISTS turns_session ON turns(workspace_id, session_id, started_at_ms);
CREATE TABLE IF NOT EXISTS logical_requests (
workspace_id TEXT NOT NULL,
request_id TEXT NOT NULL,
session_id TEXT NOT NULL,
turn_id TEXT NOT NULL,
session_scope TEXT,
correlation_provenance TEXT,
traffic_kind TEXT NOT NULL DEFAULT 'normal',
route_json TEXT,
outcome TEXT,
receipt_id TEXT,
started_at_ms INTEGER NOT NULL,
finished_at_ms INTEGER,
PRIMARY KEY(workspace_id, request_id)
);
CREATE INDEX IF NOT EXISTS requests_session
ON logical_requests(workspace_id, session_id, started_at_ms);
CREATE TABLE IF NOT EXISTS attempts (
workspace_id TEXT NOT NULL,
request_id TEXT NOT NULL,
attempt_id TEXT NOT NULL,
ordinal INTEGER NOT NULL,
body_json TEXT NOT NULL,
PRIMARY KEY(workspace_id, request_id, attempt_id),
UNIQUE(workspace_id, request_id, ordinal)
);
CREATE TABLE IF NOT EXISTS routing_receipts (
workspace_id TEXT NOT NULL,
receipt_id TEXT NOT NULL,
session_id TEXT NOT NULL,
turn_id TEXT NOT NULL,
request_id TEXT NOT NULL,
body_json TEXT NOT NULL,
body_digest TEXT NOT NULL,
frozen_at_ms INTEGER NOT NULL,
PRIMARY KEY(workspace_id, receipt_id),
UNIQUE(workspace_id, request_id)
);
"#;
const AUXILIARY_SCHEMA_V2: &str = r#"
CREATE TABLE IF NOT EXISTS observation_events (
channel TEXT NOT NULL,
producer_id TEXT NOT NULL,
producer_epoch TEXT NOT NULL,
stream_id TEXT NOT NULL,
sequence INTEGER NOT NULL,
event_id TEXT NOT NULL,
payload_digest TEXT NOT NULL,
PRIMARY KEY(channel, producer_id, producer_epoch, stream_id, sequence),
UNIQUE(channel, producer_id, producer_epoch, stream_id, event_id)
);
CREATE TABLE IF NOT EXISTS observation_checkpoints (
channel TEXT NOT NULL,
producer_id TEXT NOT NULL,
producer_epoch TEXT NOT NULL,
stream_id TEXT NOT NULL,
highest_contiguous_sequence INTEGER NOT NULL,
highest_accounted_sequence INTEGER NOT NULL,
PRIMARY KEY(channel, producer_id, producer_epoch, stream_id)
);
CREATE TABLE IF NOT EXISTS observation_gaps (
gap_id INTEGER PRIMARY KEY AUTOINCREMENT,
channel TEXT NOT NULL,
producer_id TEXT NOT NULL,
producer_epoch TEXT NOT NULL,
stream_id TEXT NOT NULL,
first_sequence INTEGER NOT NULL,
last_sequence INTEGER NOT NULL,
known_loss INTEGER NOT NULL,
workspace_id TEXT NOT NULL,
session_id TEXT,
reason TEXT
);
CREATE TABLE IF NOT EXISTS observation_tombstones (
workspace_id TEXT NOT NULL,
session_id TEXT NOT NULL,
reason TEXT NOT NULL,
delete_scope TEXT NOT NULL,
deleted_at_ms INTEGER NOT NULL,
PRIMARY KEY(workspace_id, session_id, reason, delete_scope)
);
CREATE INDEX IF NOT EXISTS observation_gaps_scope
ON observation_gaps(workspace_id, session_id, channel);
"#;
const EXECUTION_FACT_SCHEMA_V3: &str = r#"
CREATE TABLE IF NOT EXISTS execution_fact_events (
workspace_id TEXT NOT NULL,
session_id TEXT NOT NULL,
turn_id TEXT NOT NULL,
request_id TEXT NOT NULL,
producer_id TEXT NOT NULL,
producer_epoch TEXT NOT NULL,
stream_id TEXT NOT NULL,
sequence INTEGER NOT NULL,
event_id TEXT NOT NULL,
envelope_json TEXT NOT NULL,
envelope_digest TEXT NOT NULL,
PRIMARY KEY(producer_id, producer_epoch, stream_id, sequence),
UNIQUE(producer_id, producer_epoch, stream_id, event_id)
);
CREATE INDEX IF NOT EXISTS execution_facts_request
ON execution_fact_events(workspace_id, request_id, sequence);
CREATE INDEX IF NOT EXISTS execution_facts_request_payload
ON execution_fact_events(workspace_id, request_id, envelope_digest, sequence);
CREATE INDEX IF NOT EXISTS execution_facts_request_identity
ON execution_fact_events(workspace_id, request_id, envelope_digest, sequence,
producer_id, producer_epoch, stream_id, event_id);
CREATE INDEX IF NOT EXISTS execution_facts_session
ON execution_fact_events(workspace_id, session_id, sequence);
"#;
const CONVERSATION_CONTENT_SCHEMA_V4: &str = r#"
CREATE TABLE IF NOT EXISTS conversation_content_streams_v2 (
workspace_id TEXT NOT NULL,
producer_id TEXT NOT NULL,
producer_epoch TEXT NOT NULL,
stream_id TEXT NOT NULL,
request_id TEXT NOT NULL,
direction TEXT NOT NULL,
fork_id TEXT NOT NULL,
conversation_id TEXT NOT NULL,
session_scope TEXT NOT NULL,
correlation_provenance TEXT NOT NULL,
turn_id TEXT NOT NULL,
attempt_id TEXT,
parent_transcript_root TEXT,
result_transcript_root TEXT,
state TEXT NOT NULL,
next_chunk_ordinal INTEGER NOT NULL,
active_content_id TEXT,
begin_sequence INTEGER NOT NULL,
terminal_sequence INTEGER,
completeness TEXT NOT NULL,
updated_at_unix_nanos INTEGER NOT NULL,
PRIMARY KEY(producer_id, producer_epoch, stream_id, request_id, direction, fork_id)
);
CREATE INDEX IF NOT EXISTS content_stream_v2_request
ON conversation_content_streams_v2(workspace_id, request_id, direction, fork_id);
CREATE TABLE IF NOT EXISTS conversation_content_events_v2 (
producer_id TEXT NOT NULL,
producer_epoch TEXT NOT NULL,
stream_id TEXT NOT NULL,
sequence INTEGER NOT NULL,
event_id TEXT NOT NULL,
workspace_id TEXT NOT NULL,
request_id TEXT NOT NULL,
direction TEXT NOT NULL,
fork_id TEXT NOT NULL,
phase TEXT NOT NULL,
metadata_json TEXT NOT NULL,
canonical_chunk_object_ref TEXT,
canonical_chunk_byte_offset INTEGER,
canonical_chunk_byte_count INTEGER,
envelope_digest TEXT NOT NULL,
PRIMARY KEY(producer_id, producer_epoch, stream_id, sequence),
UNIQUE(producer_id, producer_epoch, stream_id, event_id)
);
CREATE INDEX IF NOT EXISTS content_events_v2_request
ON conversation_content_events_v2(workspace_id, request_id, direction, fork_id, sequence);
CREATE TABLE IF NOT EXISTS content_message_instances_v2 (
workspace_id TEXT NOT NULL,
message_instance_id TEXT NOT NULL,
conversation_id TEXT NOT NULL,
request_id TEXT NOT NULL,
direction TEXT NOT NULL,
fork_id TEXT NOT NULL,
message_ordinal INTEGER NOT NULL,
message_role TEXT NOT NULL,
occurred_at_unix_nanos INTEGER NOT NULL,
PRIMARY KEY(workspace_id, message_instance_id),
UNIQUE(workspace_id, request_id, direction, fork_id, message_ordinal)
);
CREATE TABLE IF NOT EXISTS content_instances_v2 (
workspace_id TEXT NOT NULL,
content_id TEXT NOT NULL,
message_instance_id TEXT NOT NULL,
request_id TEXT NOT NULL,
direction TEXT NOT NULL,
fork_id TEXT NOT NULL,
part_ordinal INTEGER NOT NULL,
content_kind TEXT NOT NULL,
canonical_media_type TEXT NOT NULL,
content_blob_digest TEXT NOT NULL,
expected_byte_count INTEGER,
has_content_ref INTEGER NOT NULL,
transport_frame_id TEXT,
downstream_delivery TEXT,
accumulated_byte_count INTEGER NOT NULL,
state TEXT NOT NULL,
created_at_unix_nanos INTEGER NOT NULL,
PRIMARY KEY(workspace_id, content_id)
);
CREATE INDEX IF NOT EXISTS content_instances_v2_request
ON content_instances_v2(workspace_id, request_id, direction, fork_id, part_ordinal);
CREATE INDEX IF NOT EXISTS content_instances_v2_live_blob
ON content_instances_v2(workspace_id, content_blob_digest) WHERE state='complete';
-- Cover live-search metadata without loading every content occurrence.
-- The implicit rowid preserves the first eligible occurrence per request.
CREATE INDEX IF NOT EXISTS content_instances_v2_search_blob
ON content_instances_v2(workspace_id, content_blob_digest,
content_kind, canonical_media_type, request_id) WHERE state='complete';
CREATE TABLE IF NOT EXISTS content_chunks_v2 (
workspace_id TEXT NOT NULL,
content_id TEXT NOT NULL,
chunk_ordinal INTEGER NOT NULL,
object_path TEXT NOT NULL,
byte_offset INTEGER NOT NULL,
byte_count INTEGER NOT NULL,
event_id TEXT NOT NULL,
PRIMARY KEY(workspace_id, content_id, chunk_ordinal)
);
CREATE TABLE IF NOT EXISTS content_blobs_v2 (
workspace_id TEXT NOT NULL,
blob_digest TEXT NOT NULL,
object_path TEXT NOT NULL,
byte_count INTEGER NOT NULL,
media_type TEXT NOT NULL,
state TEXT NOT NULL,
created_at_unix_nanos INTEGER NOT NULL,
PRIMARY KEY(workspace_id, blob_digest)
);
CREATE INDEX IF NOT EXISTS content_blobs_v2_workspace_bytes
ON content_blobs_v2(workspace_id, byte_count);
CREATE TABLE IF NOT EXISTS request_content_refs_v2 (
workspace_id TEXT NOT NULL,
request_id TEXT NOT NULL,
direction TEXT NOT NULL,
fork_id TEXT NOT NULL,
message_instance_id TEXT NOT NULL,
content_id TEXT NOT NULL,
message_ordinal INTEGER NOT NULL,
part_ordinal INTEGER NOT NULL,
blob_digest TEXT NOT NULL,
PRIMARY KEY(workspace_id, request_id, direction, fork_id, message_ordinal, part_ordinal)
);
CREATE TABLE IF NOT EXISTS transcript_roots_v2 (
workspace_id TEXT NOT NULL,
transcript_root TEXT NOT NULL,
conversation_id TEXT NOT NULL,
request_id TEXT NOT NULL,
direction TEXT NOT NULL,
fork_id TEXT NOT NULL,
parent_transcript_root TEXT,
state TEXT NOT NULL,
acknowledged_at_unix_nanos INTEGER NOT NULL,
PRIMARY KEY(workspace_id, transcript_root)
);
CREATE TABLE IF NOT EXISTS observation_feedback_v2 (
feedback_id INTEGER PRIMARY KEY AUTOINCREMENT,
channel TEXT NOT NULL,
producer_id TEXT NOT NULL,
producer_epoch TEXT NOT NULL,
stream_id TEXT NOT NULL,
rejected_or_acked_sequence INTEGER NOT NULL,
event_id TEXT NOT NULL,
envelope_digest TEXT NOT NULL,
feedback_kind TEXT NOT NULL,
feedback_json TEXT NOT NULL,
created_at_unix_nanos INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS transcript_roots_v2_request
ON transcript_roots_v2(workspace_id, request_id, direction, state);
CREATE INDEX IF NOT EXISTS observation_feedback_v2_lookup
ON observation_feedback_v2(channel, producer_id, producer_epoch, stream_id,
rejected_or_acked_sequence, event_id, envelope_digest,
feedback_id DESC);
CREATE TABLE IF NOT EXISTS observation_gap_heartbeats_v1 (
channel TEXT NOT NULL,
producer_id TEXT NOT NULL,
producer_epoch TEXT NOT NULL,
stream_id TEXT NOT NULL,
sequence INTEGER NOT NULL,
event_id TEXT NOT NULL,
heartbeat_json TEXT NOT NULL,
PRIMARY KEY(channel, producer_id, producer_epoch, stream_id, sequence),
UNIQUE(channel, producer_id, producer_epoch, stream_id, event_id)
);
"#;
const VALUE_SCHEMA_V2: &str = r#"
CREATE TABLE IF NOT EXISTS value_ledger_entries (
workspace_id TEXT NOT NULL,
request_id TEXT NOT NULL,
session_id TEXT NOT NULL,
receipt_id TEXT NOT NULL,
body_json TEXT NOT NULL,
body_digest TEXT NOT NULL,
agent_plan_id TEXT NOT NULL,
currency TEXT NOT NULL,
billing_unit TEXT NOT NULL,
price_version TEXT NOT NULL,
price_override_revision TEXT,
input_tokens INTEGER NOT NULL,
output_tokens INTEGER NOT NULL,
cache_read_tokens INTEGER NOT NULL,
cache_write_tokens INTEGER NOT NULL,
reasoning_tokens INTEGER NOT NULL,
baseline_api_equivalent_cost_micros INTEGER,
chosen_api_equivalent_cost_micros INTEGER,
actual_incremental_cost_micros INTEGER,
routing_savings_micros INTEGER,
entitlement_savings_micros INTEGER,
estimated_total_savings_micros INTEGER,
facts_completeness TEXT NOT NULL,
frozen_at_ms INTEGER NOT NULL,
PRIMARY KEY(workspace_id, request_id)
);
CREATE INDEX IF NOT EXISTS value_time
ON value_ledger_entries(workspace_id, agent_plan_id, currency, frozen_at_ms);
CREATE TABLE IF NOT EXISTS session_value_rollup_contributions (
workspace_id TEXT NOT NULL,
session_id TEXT NOT NULL,
agent_plan_id TEXT NOT NULL,
day_number INTEGER NOT NULL,
currency TEXT NOT NULL,
billing_unit TEXT NOT NULL,
input_tokens INTEGER NOT NULL,
output_tokens INTEGER NOT NULL,
cache_read_tokens INTEGER NOT NULL,
cache_write_tokens INTEGER NOT NULL,
reasoning_tokens INTEGER NOT NULL,
baseline_api_equivalent_cost_micros INTEGER,
chosen_api_equivalent_cost_micros INTEGER,
actual_incremental_cost_micros INTEGER,
routing_savings_micros INTEGER,
entitlement_savings_micros INTEGER,
estimated_total_savings_micros INTEGER,
price_version_refs_json TEXT NOT NULL,
price_override_revision_refs_json TEXT NOT NULL,
facts_completeness TEXT NOT NULL,
PRIMARY KEY(workspace_id, session_id, agent_plan_id, day_number, currency, billing_unit)
);
CREATE INDEX IF NOT EXISTS rollup_contributions_bucket
ON session_value_rollup_contributions(
workspace_id, agent_plan_id, day_number, currency, billing_unit
);
CREATE TABLE IF NOT EXISTS daily_value_rollups (
workspace_id TEXT NOT NULL,
agent_plan_id TEXT NOT NULL,
day_number INTEGER NOT NULL,
currency TEXT NOT NULL,
billing_unit TEXT NOT NULL,
input_tokens INTEGER NOT NULL,
output_tokens INTEGER NOT NULL,
cache_read_tokens INTEGER NOT NULL,
cache_write_tokens INTEGER NOT NULL,
reasoning_tokens INTEGER NOT NULL,
baseline_api_equivalent_cost_micros INTEGER,
chosen_api_equivalent_cost_micros INTEGER,
actual_incremental_cost_micros INTEGER,
routing_savings_micros INTEGER,
entitlement_savings_micros INTEGER,
estimated_total_savings_micros INTEGER,
price_version_refs_json TEXT NOT NULL,
price_override_revision_refs_json TEXT NOT NULL,
facts_completeness TEXT NOT NULL,
PRIMARY KEY(workspace_id, agent_plan_id, day_number, currency, billing_unit)
);
"#;