use super::*;
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub(super) enum ChunksUniqueShape {
TenantScoped,
Legacy,
Other,
}
pub(super) fn detect_chunks_unique_shape(conn: &Connection) -> Result<ChunksUniqueShape> {
let mut list_stmt = conn.prepare(
"SELECT name FROM pragma_index_list('chunks') \
WHERE \"unique\" = 1 AND origin = 'u'",
)?;
let index_names: Vec<String> = list_stmt
.query_map([], |row| row.get::<_, String>(0))?
.collect::<std::result::Result<Vec<_>, rusqlite::Error>>()?;
let mut has_tenant_scoped = false;
let mut has_legacy = false;
for name in &index_names {
let mut info_stmt =
conn.prepare("SELECT name FROM pragma_index_info(?1) ORDER BY seqno")?;
let cols: Vec<String> = info_stmt
.query_map([name.as_str()], |row| row.get::<_, String>(0))?
.collect::<std::result::Result<Vec<_>, rusqlite::Error>>()?;
match cols.as_slice() {
[a, b, c] if a == "tenant_id" && b == "segment_id" && c == "ordinal" => {
has_tenant_scoped = true;
}
[a, b] if a == "segment_id" && b == "ordinal" => {
has_legacy = true;
}
_ => {}
}
}
if has_tenant_scoped {
Ok(ChunksUniqueShape::TenantScoped)
} else if has_legacy {
Ok(ChunksUniqueShape::Legacy)
} else {
Ok(ChunksUniqueShape::Other)
}
}
impl SqliteMetadataStore {
pub(super) fn init_schema(&self) -> Result<()> {
let conn = self.pool.get();
conn.execute(
"CREATE TABLE IF NOT EXISTS chunks (
chunk_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
project_id TEXT,
segment_id INTEGER NOT NULL,
ordinal INTEGER NOT NULL,
chunk_type TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'final',
timestamp_created INTEGER NOT NULL,
hash TEXT NOT NULL,
source_uri TEXT,
index_state TEXT NOT NULL DEFAULT 'indexed',
index_attempts INTEGER NOT NULL DEFAULT 0,
index_last_error TEXT,
indexed_at_ms INTEGER,
index_updated_at_ms INTEGER NOT NULL DEFAULT 0,
tier TEXT NOT NULL DEFAULT 'long_term',
supersedes TEXT,
superseded_by TEXT,
expires_at_ms INTEGER,
review_after_ms INTEGER,
lifecycle_updated_at_ms INTEGER NOT NULL DEFAULT 0,
canonical_text TEXT,
ingestion_mode TEXT NOT NULL DEFAULT 'document',
UNIQUE(tenant_id, segment_id, ordinal)
)",
[],
)?;
Self::ensure_index_columns(&conn)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_chunks_tenant
ON chunks(tenant_id, status)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_chunks_tenant_type
ON chunks(tenant_id, chunk_type, timestamp_created DESC)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_chunks_segment
ON chunks(tenant_id, segment_id)",
[],
)?;
Self::init_feedback_schema(&conn)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS usage_events (
id INTEGER PRIMARY KEY,
ts_unix_ms INTEGER NOT NULL,
op TEXT NOT NULL,
tenant TEXT,
project TEXT,
outcome TEXT NOT NULL,
chunk_count INTEGER,
bytes INTEGER,
detail TEXT
)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_usage_events_ts
ON usage_events(ts_unix_ms)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_usage_events_op
ON usage_events(op)",
[],
)?;
Self::init_consolidation_schema(&conn)?;
Self::init_outcome_schema(&conn)?;
Self::init_task_schema(&conn)?;
Ok(())
}
pub(super) fn init_feedback_schema(conn: &Connection) -> Result<()> {
let exists = conn.query_row(
"SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE type='table' AND name='feedback')",
[],
|row| row.get::<_, i64>(0),
)? != 0;
let legacy_query_column = if exists {
let mut statement = conn.prepare("PRAGMA table_info(feedback)")?;
let columns = statement
.query_map([], |row| row.get::<_, String>(1))?
.collect::<std::result::Result<Vec<_>, _>>()?;
columns.iter().any(|column| column == "query")
} else {
false
};
if legacy_query_column {
conn.execute_batch(
"DROP INDEX IF EXISTS idx_feedback_tenant_query;
DROP INDEX IF EXISTS idx_feedback_tenant_chunk;
ALTER TABLE feedback RENAME TO feedback_legacy_raw_query;
CREATE TABLE feedback (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tenant_id TEXT NOT NULL,
project_id TEXT,
query_hash TEXT NOT NULL,
chunk_id TEXT NOT NULL,
relevance INTEGER NOT NULL,
timestamp_ms INTEGER NOT NULL
);",
)?;
let rows = {
let mut statement = conn.prepare(
"SELECT f.id, f.tenant_id, c.project_id, f.query,
f.chunk_id, f.relevance, f.timestamp_ms
FROM feedback_legacy_raw_query f
LEFT JOIN chunks c
ON c.tenant_id = f.tenant_id AND c.chunk_id = f.chunk_id",
)?;
let rows = statement
.query_map([], |row| {
Ok((
row.get::<_, i64>(0)?,
row.get::<_, String>(1)?,
row.get::<_, Option<String>>(2)?,
row.get::<_, String>(3)?,
row.get::<_, String>(4)?,
row.get::<_, i64>(5)?,
row.get::<_, i64>(6)?,
))
})?
.collect::<std::result::Result<Vec<_>, _>>()?;
rows
};
for (id, tenant_id, project_id, query, chunk_id, relevance, timestamp_ms) in rows {
let query_hash = crate::store::stable_query_hash(&normalize_query(&query));
conn.execute(
"INSERT INTO feedback (
id, tenant_id, project_id, query_hash, chunk_id, relevance, timestamp_ms
) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
rusqlite::params![
id,
tenant_id,
project_id,
query_hash,
chunk_id,
relevance,
timestamp_ms
],
)?;
}
conn.execute("DROP TABLE feedback_legacy_raw_query", [])?;
conn.execute_batch("PRAGMA wal_checkpoint(TRUNCATE); VACUUM;")?;
}
conn.execute(
"CREATE TABLE IF NOT EXISTS feedback (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tenant_id TEXT NOT NULL,
project_id TEXT,
query_hash TEXT NOT NULL,
chunk_id TEXT NOT NULL,
relevance INTEGER NOT NULL,
timestamp_ms INTEGER NOT NULL
)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_feedback_tenant_query_hash
ON feedback(tenant_id, query_hash, timestamp_ms DESC)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_feedback_tenant_chunk
ON feedback(tenant_id, chunk_id)",
[],
)?;
Ok(())
}
pub(super) fn init_consolidation_schema(conn: &Connection) -> Result<()> {
conn.execute_batch(
"CREATE TABLE IF NOT EXISTS consolidation_runs (
run_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
project_id TEXT,
input_hash TEXT NOT NULL,
state TEXT NOT NULL,
consolidator TEXT NOT NULL,
prompt_hash TEXT,
response_hash TEXT,
validation_result TEXT,
error TEXT,
sparse_cleanup_done INTEGER NOT NULL DEFAULT 0,
consolidator_command TEXT,
consolidator_model TEXT,
consolidator_version TEXT,
audit_artifact_path TEXT,
promotion_requested INTEGER NOT NULL DEFAULT 0,
created_at_ms INTEGER NOT NULL,
updated_at_ms INTEGER NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_consolidation_runs_input_active
ON consolidation_runs(tenant_id, COALESCE(project_id, ''), input_hash)
WHERE state NOT IN ('rejected', 'rolled_back');
CREATE INDEX IF NOT EXISTS idx_consolidation_runs_recovery
ON consolidation_runs(state, updated_at_ms);
CREATE TABLE IF NOT EXISTS consolidation_entries (
run_id TEXT NOT NULL,
entry_index INTEGER NOT NULL,
candidate_chunk_id TEXT,
source_set_hash TEXT NOT NULL,
state TEXT NOT NULL,
validation_error TEXT,
created_at_ms INTEGER NOT NULL,
updated_at_ms INTEGER NOT NULL,
PRIMARY KEY (run_id, entry_index),
UNIQUE (candidate_chunk_id),
FOREIGN KEY (run_id) REFERENCES consolidation_runs(run_id)
);
CREATE INDEX IF NOT EXISTS idx_consolidation_entries_state
ON consolidation_entries(run_id, state);
CREATE TABLE IF NOT EXISTS memory_lineage (
run_id TEXT NOT NULL,
tenant_id TEXT NOT NULL,
project_id TEXT,
source_chunk_id TEXT NOT NULL,
result_chunk_id TEXT NOT NULL,
relation TEXT NOT NULL,
created_at_ms INTEGER NOT NULL,
PRIMARY KEY (run_id, source_chunk_id, result_chunk_id, relation),
FOREIGN KEY (run_id) REFERENCES consolidation_runs(run_id)
);
CREATE INDEX IF NOT EXISTS idx_memory_lineage_source
ON memory_lineage(tenant_id, source_chunk_id, relation);
CREATE INDEX IF NOT EXISTS idx_memory_lineage_result
ON memory_lineage(tenant_id, result_chunk_id, relation);",
)?;
let mut statement = conn.prepare("PRAGMA table_info(consolidation_runs)")?;
let columns = statement
.query_map([], |row| row.get::<usize, String>(1))?
.collect::<std::result::Result<std::collections::HashSet<_>, _>>()?;
Self::ensure_index_column(
conn,
&columns,
"sparse_cleanup_done",
"ALTER TABLE consolidation_runs ADD COLUMN sparse_cleanup_done INTEGER NOT NULL DEFAULT 0",
)?;
Self::ensure_index_column(
conn,
&columns,
"consolidator_command",
"ALTER TABLE consolidation_runs ADD COLUMN consolidator_command TEXT",
)?;
Self::ensure_index_column(
conn,
&columns,
"consolidator_model",
"ALTER TABLE consolidation_runs ADD COLUMN consolidator_model TEXT",
)?;
Self::ensure_index_column(
conn,
&columns,
"consolidator_version",
"ALTER TABLE consolidation_runs ADD COLUMN consolidator_version TEXT",
)?;
Self::ensure_index_column(
conn,
&columns,
"audit_artifact_path",
"ALTER TABLE consolidation_runs ADD COLUMN audit_artifact_path TEXT",
)?;
Self::ensure_index_column(
conn,
&columns,
"promotion_requested",
"ALTER TABLE consolidation_runs ADD COLUMN promotion_requested INTEGER NOT NULL DEFAULT 0",
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_consolidation_runs_sparse_cleanup
ON consolidation_runs(state, sparse_cleanup_done, updated_at_ms)",
[],
)?;
Ok(())
}
pub(super) fn init_outcome_schema(conn: &Connection) -> Result<()> {
conn.execute_batch(
"CREATE TABLE IF NOT EXISTS retrieval_episodes (
episode_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
project_id TEXT,
query_hash TEXT NOT NULL,
query_mode TEXT NOT NULL,
requested_k INTEGER NOT NULL,
fetched_k INTEGER NOT NULL,
rendered_k INTEGER NOT NULL,
policy_version TEXT NOT NULL,
policy_mode TEXT NOT NULL,
task_id TEXT,
thread_id TEXT,
created_at_ms INTEGER NOT NULL,
expires_at_ms INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_retrieval_episodes_scope_created
ON retrieval_episodes(tenant_id, project_id, created_at_ms DESC);
CREATE INDEX IF NOT EXISTS idx_retrieval_episodes_expiry
ON retrieval_episodes(expires_at_ms);
CREATE INDEX IF NOT EXISTS idx_retrieval_episodes_query_hash
ON retrieval_episodes(tenant_id, project_id, query_hash, created_at_ms DESC);
CREATE TABLE IF NOT EXISTS retrieval_episode_items (
episode_id TEXT NOT NULL,
chunk_id TEXT NOT NULL,
origin_tenant_id TEXT NOT NULL,
origin_project_id TEXT,
original_rank INTEGER NOT NULL,
original_score REAL NOT NULL,
lane_scores_json TEXT NOT NULL,
outcome_adjustment REAL NOT NULL,
served_rank INTEGER,
shadow_rank INTEGER,
rendered INTEGER NOT NULL,
source_dedup_group TEXT,
PRIMARY KEY (episode_id, chunk_id),
UNIQUE (episode_id, original_rank),
FOREIGN KEY (episode_id) REFERENCES retrieval_episodes(episode_id)
);
CREATE INDEX IF NOT EXISTS idx_retrieval_episode_items_chunk
ON retrieval_episode_items(chunk_id, episode_id);
CREATE INDEX IF NOT EXISTS idx_retrieval_episode_items_rendered
ON retrieval_episode_items(episode_id, rendered, served_rank);
CREATE TABLE IF NOT EXISTS outcome_events (
event_id TEXT PRIMARY KEY,
episode_id TEXT NOT NULL,
outcome TEXT NOT NULL,
verifier_type TEXT NOT NULL,
used_chunk_ids_json TEXT NOT NULL,
harmful_chunk_ids_json TEXT NOT NULL,
evidence_reference TEXT,
ranking_eligible INTEGER NOT NULL,
timestamp_ms INTEGER NOT NULL,
FOREIGN KEY (episode_id) REFERENCES retrieval_episodes(episode_id)
);
CREATE INDEX IF NOT EXISTS idx_outcome_events_episode_time
ON outcome_events(episode_id, timestamp_ms DESC);
CREATE INDEX IF NOT EXISTS idx_outcome_events_retention
ON outcome_events(timestamp_ms);",
)?;
Ok(())
}
pub(super) fn init_task_schema(conn: &Connection) -> Result<()> {
conn.execute(
"CREATE TABLE IF NOT EXISTS task_artifacts (
artifact_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
project_id TEXT,
task_id TEXT NOT NULL,
parent_task_id TEXT,
artifact_kind TEXT NOT NULL,
status TEXT,
artifact_role TEXT,
challenge_id TEXT,
thread_id TEXT,
reply_to_artifact_id TEXT,
agent_id TEXT,
session_id TEXT,
goal TEXT,
summary TEXT,
tool_name TEXT,
tool_version TEXT,
requested_action TEXT,
verification_status TEXT,
promotion_state TEXT NOT NULL DEFAULT 'raw',
digest_key TEXT,
source_updated_at_ms INTEGER,
canonical_json TEXT NOT NULL,
timestamp_created INTEGER NOT NULL,
timestamp_observed INTEGER
)",
[],
)?;
Self::ensure_task_artifact_columns(conn)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_task_artifacts_tenant_task
ON task_artifacts(tenant_id, task_id, artifact_kind, timestamp_created DESC)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_task_artifacts_tool
ON task_artifacts(tenant_id, tool_name, timestamp_created DESC)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_task_artifacts_thread
ON task_artifacts(tenant_id, thread_id, timestamp_created DESC)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_task_artifacts_challenge
ON task_artifacts(tenant_id, challenge_id, timestamp_created DESC)",
[],
)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS tasks (
task_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
project_id TEXT,
parent_task_id TEXT,
agent_id TEXT,
session_id TEXT,
status TEXT,
goal TEXT,
scientific_question TEXT,
hypothesis TEXT,
last_artifact_id TEXT NOT NULL,
started_at_ms INTEGER,
finished_at_ms INTEGER,
updated_at_ms INTEGER NOT NULL
)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_tasks_tenant_status
ON tasks(tenant_id, status, updated_at_ms DESC)",
[],
)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS task_events (
artifact_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
task_id TEXT NOT NULL,
artifact_kind TEXT NOT NULL,
status TEXT,
summary TEXT,
timestamp_created INTEGER NOT NULL,
timestamp_observed INTEGER
)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_task_events_tenant_task
ON task_events(tenant_id, task_id, timestamp_created DESC)",
[],
)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS runs (
artifact_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
task_id TEXT NOT NULL,
tool_name TEXT,
status TEXT,
command TEXT,
metrics_json TEXT,
timestamp_created INTEGER NOT NULL
)",
[],
)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS evidence (
artifact_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
task_id TEXT NOT NULL,
summary TEXT,
supports_claim INTEGER,
metrics_json TEXT,
timestamp_created INTEGER NOT NULL
)",
[],
)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS datasets (
dataset_key TEXT PRIMARY KEY,
dataset_name TEXT NOT NULL,
dataset_version TEXT,
description TEXT
)",
[],
)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS entities (
entity_key TEXT PRIMARY KEY,
entity_name TEXT NOT NULL,
entity_type TEXT NOT NULL,
role TEXT
)",
[],
)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS task_datasets (
artifact_id TEXT NOT NULL,
tenant_id TEXT NOT NULL,
task_id TEXT NOT NULL,
dataset_key TEXT NOT NULL,
dataset_name TEXT NOT NULL,
dataset_version TEXT,
PRIMARY KEY (artifact_id, dataset_key)
)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_task_datasets_tenant_task
ON task_datasets(tenant_id, task_id, dataset_name, dataset_version)",
[],
)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS task_entities (
artifact_id TEXT NOT NULL,
tenant_id TEXT NOT NULL,
task_id TEXT NOT NULL,
entity_key TEXT NOT NULL,
entity_name TEXT NOT NULL,
entity_type TEXT NOT NULL,
PRIMARY KEY (artifact_id, entity_key)
)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_task_entities_tenant_task
ON task_entities(tenant_id, task_id, entity_name, entity_type)",
[],
)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS artifact_links (
artifact_id TEXT NOT NULL,
chunk_id TEXT NOT NULL,
link_kind TEXT NOT NULL,
projection_kind TEXT,
PRIMARY KEY (artifact_id, chunk_id, link_kind)
)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_artifact_links_chunk
ON artifact_links(chunk_id)",
[],
)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS artifact_relations (
artifact_id TEXT NOT NULL,
tenant_id TEXT NOT NULL,
related_artifact_id TEXT NOT NULL,
relation_kind TEXT NOT NULL,
PRIMARY KEY (artifact_id, related_artifact_id, relation_kind)
)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_artifact_relations_tenant_related
ON artifact_relations(tenant_id, related_artifact_id, relation_kind)",
[],
)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS artifact_contributors (
artifact_id TEXT NOT NULL,
tenant_id TEXT NOT NULL,
contributor_id TEXT NOT NULL,
display_name TEXT,
role TEXT,
contribution TEXT,
PRIMARY KEY (artifact_id, contributor_id)
)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_artifact_contributors_tenant
ON artifact_contributors(tenant_id, contributor_id)",
[],
)?;
conn.execute(
"CREATE TABLE IF NOT EXISTS challenges (
challenge_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
project_id TEXT,
summary TEXT,
status TEXT,
updated_at_ms INTEGER NOT NULL
)",
[],
)?;
Ok(())
}
pub(super) fn ensure_task_artifact_columns(conn: &Connection) -> Result<()> {
let mut stmt = conn.prepare("PRAGMA table_info(task_artifacts)")?;
let rows = stmt.query_map([], |row| row.get::<usize, String>(1))?;
let mut column_names = std::collections::HashSet::new();
for name in rows {
column_names.insert(name?);
}
Self::ensure_index_column(
conn,
&column_names,
"tool_name",
"ALTER TABLE task_artifacts ADD COLUMN tool_name TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"tool_version",
"ALTER TABLE task_artifacts ADD COLUMN tool_version TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"artifact_role",
"ALTER TABLE task_artifacts ADD COLUMN artifact_role TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"challenge_id",
"ALTER TABLE task_artifacts ADD COLUMN challenge_id TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"thread_id",
"ALTER TABLE task_artifacts ADD COLUMN thread_id TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"reply_to_artifact_id",
"ALTER TABLE task_artifacts ADD COLUMN reply_to_artifact_id TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"requested_action",
"ALTER TABLE task_artifacts ADD COLUMN requested_action TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"verification_status",
"ALTER TABLE task_artifacts ADD COLUMN verification_status TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"promotion_state",
"ALTER TABLE task_artifacts ADD COLUMN promotion_state TEXT NOT NULL DEFAULT 'raw'",
)?;
Self::ensure_index_column(
conn,
&column_names,
"digest_key",
"ALTER TABLE task_artifacts ADD COLUMN digest_key TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"source_updated_at_ms",
"ALTER TABLE task_artifacts ADD COLUMN source_updated_at_ms INTEGER",
)?;
Ok(())
}
pub(super) fn ensure_index_columns(conn: &Connection) -> Result<()> {
let mut stmt = conn.prepare("PRAGMA table_info(chunks)")?;
let rows = stmt.query_map([], |row| row.get::<usize, String>(1))?;
let mut column_names = std::collections::HashSet::new();
for name in rows {
column_names.insert(name?);
}
Self::ensure_index_column(
conn,
&column_names,
"index_state",
"ALTER TABLE chunks ADD COLUMN index_state TEXT NOT NULL DEFAULT 'indexed'",
)?;
Self::ensure_index_column(
conn,
&column_names,
"index_attempts",
"ALTER TABLE chunks ADD COLUMN index_attempts INTEGER NOT NULL DEFAULT 0",
)?;
Self::ensure_index_column(
conn,
&column_names,
"index_last_error",
"ALTER TABLE chunks ADD COLUMN index_last_error TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"indexed_at_ms",
"ALTER TABLE chunks ADD COLUMN indexed_at_ms INTEGER",
)?;
Self::ensure_index_column(
conn,
&column_names,
"index_updated_at_ms",
"ALTER TABLE chunks ADD COLUMN index_updated_at_ms INTEGER NOT NULL DEFAULT 0",
)?;
Self::ensure_index_column(
conn,
&column_names,
"tier",
"ALTER TABLE chunks ADD COLUMN tier TEXT NOT NULL DEFAULT 'long_term'",
)?;
Self::ensure_index_column(
conn,
&column_names,
"supersedes",
"ALTER TABLE chunks ADD COLUMN supersedes TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"superseded_by",
"ALTER TABLE chunks ADD COLUMN superseded_by TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"expires_at_ms",
"ALTER TABLE chunks ADD COLUMN expires_at_ms INTEGER",
)?;
Self::ensure_index_column(
conn,
&column_names,
"review_after_ms",
"ALTER TABLE chunks ADD COLUMN review_after_ms INTEGER",
)?;
Self::ensure_index_column(
conn,
&column_names,
"lifecycle_updated_at_ms",
"ALTER TABLE chunks ADD COLUMN lifecycle_updated_at_ms INTEGER NOT NULL DEFAULT 0",
)?;
Self::ensure_index_column(
conn,
&column_names,
"canonical_text",
"ALTER TABLE chunks ADD COLUMN canonical_text TEXT",
)?;
Self::ensure_index_column(
conn,
&column_names,
"ingestion_mode",
"ALTER TABLE chunks ADD COLUMN ingestion_mode TEXT NOT NULL DEFAULT 'document'",
)?;
Self::migrate_chunks_unique_to_tenant_scoped(conn)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_chunks_expiry
ON chunks(tenant_id, expires_at_ms) WHERE expires_at_ms IS NOT NULL",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_chunks_supersedes
ON chunks(tenant_id, supersedes) WHERE supersedes IS NOT NULL",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_chunks_tier_status
ON chunks(tenant_id, tier, status)",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_chunks_canonical
ON chunks(tenant_id, project_id, canonical_text) WHERE canonical_text IS NOT NULL",
[],
)?;
conn.execute(
"CREATE INDEX IF NOT EXISTS idx_chunks_content_hash
ON chunks(tenant_id, project_id, chunk_type, hash, timestamp_created DESC)
WHERE status = 'final' AND tier != 'history'",
[],
)?;
Ok(())
}
pub(super) fn ensure_index_column(
conn: &Connection,
column_names: &std::collections::HashSet<String>,
column_name: &str,
alter_sql: &str,
) -> Result<()> {
if !column_names.contains(column_name) {
conn.execute(alter_sql, [])?;
}
Ok(())
}
pub(super) fn migrate_chunks_unique_to_tenant_scoped(conn: &Connection) -> Result<()> {
match detect_chunks_unique_shape(conn)? {
ChunksUniqueShape::TenantScoped => return Ok(()),
ChunksUniqueShape::Other => return Ok(()),
ChunksUniqueShape::Legacy => {}
}
conn.execute_batch(
"BEGIN;
CREATE TABLE chunks_migration_tenant_scoped (
chunk_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
project_id TEXT,
segment_id INTEGER NOT NULL,
ordinal INTEGER NOT NULL,
chunk_type TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'final',
timestamp_created INTEGER NOT NULL,
hash TEXT NOT NULL,
source_uri TEXT,
index_state TEXT NOT NULL DEFAULT 'indexed',
index_attempts INTEGER NOT NULL DEFAULT 0,
index_last_error TEXT,
indexed_at_ms INTEGER,
index_updated_at_ms INTEGER NOT NULL DEFAULT 0,
tier TEXT NOT NULL DEFAULT 'long_term',
supersedes TEXT,
superseded_by TEXT,
expires_at_ms INTEGER,
review_after_ms INTEGER,
lifecycle_updated_at_ms INTEGER NOT NULL DEFAULT 0,
canonical_text TEXT,
ingestion_mode TEXT NOT NULL DEFAULT 'document',
UNIQUE(tenant_id, segment_id, ordinal)
);
INSERT INTO chunks_migration_tenant_scoped (
chunk_id, tenant_id, project_id, segment_id, ordinal,
chunk_type, status, timestamp_created, hash, source_uri,
index_state, index_attempts, index_last_error, indexed_at_ms,
index_updated_at_ms, tier, supersedes, superseded_by,
expires_at_ms, review_after_ms, lifecycle_updated_at_ms,
canonical_text, ingestion_mode
)
SELECT
chunk_id, tenant_id, project_id, segment_id, ordinal,
chunk_type, status, timestamp_created, hash, source_uri,
index_state, index_attempts, index_last_error, indexed_at_ms,
index_updated_at_ms, tier, supersedes, superseded_by,
expires_at_ms, review_after_ms, lifecycle_updated_at_ms,
canonical_text, ingestion_mode
FROM chunks;
DROP TABLE chunks;
ALTER TABLE chunks_migration_tenant_scoped RENAME TO chunks;
COMMIT;",
)?;
Ok(())
}
}