roboticus-db 0.11.4

//! Memory index — lightweight summaries pointing to full memory content.
//!
//! The index is always injected into the model's context (cheap: ~150 chars
//! per entry). Full content is fetched on demand via the `recall_memory` tool.
//! This replaces the "dump everything" approach with a bandwidth-aware
//! index-then-fetch pattern.

use crate::DbResultExt;
use roboticus_core::{Result, RoboticusError};

use crate::Database;

/// A single index entry.
#[derive(Debug, Clone)]
pub struct IndexEntry {
    pub id: String,
    pub summary: String,
    pub source_table: String,
    pub source_id: String,
    pub category: Option<String>,
    pub confidence: f64,
    pub created_at: String,
}

/// Upsert an index entry. If the source already has an entry, update it.
pub fn upsert_index_entry(
    db: &Database,
    source_table: &str,
    source_id: &str,
    summary: &str,
    category: Option<&str>,
) -> Result<String> {
    let conn = db.conn();
    let id = format!(
        "idx-{source_table}-{}",
        &source_id[..source_id.len().min(12)]
    );
    conn.execute(
        "INSERT INTO memory_index (id, summary, source_table, source_id, category, confidence)
         VALUES (?1, ?2, ?3, ?4, ?5, 1.0)
         ON CONFLICT(id) DO UPDATE SET summary = ?2, confidence = 1.0, last_verified = datetime('now')",
        rusqlite::params![id, summary, source_table, source_id, category],
    )
    .db_err()?;
    Ok(id)
}

/// Get the top N index entries by confidence, most confident first.
pub fn top_entries(db: &Database, limit: usize) -> Result<Vec<IndexEntry>> {
    let conn = db.conn();
    // Exclude low-value tool execution noise from context injection:
    // - Empty/error results ("Executed 'X': {}" or "Executed 'X': error:")
    // - System sentinels
    // - Dedup tombstones (confidence < 0)
    // Diversify across tiers by preferring semantic/relationship/procedural
    // over raw episodic tool_use entries.
    let mut stmt = conn
        .prepare(
            "SELECT id, summary, source_table, source_id, category, confidence, created_at
             FROM memory_index
             WHERE confidence > 0.1
               AND source_table != 'system'
               AND NOT (summary LIKE 'Executed %: {%' AND summary LIKE '%[]%')
               AND NOT (summary LIKE 'Executed %: error:%')
               AND NOT (summary LIKE 'Executed %: %\"count\": 0%')
             ORDER BY
               CASE source_table
                 WHEN 'semantic_memory' THEN 1
                 WHEN 'learned_skills' THEN 2
                 WHEN 'relationship_memory' THEN 3
                 WHEN 'procedural_memory' THEN 4
                 WHEN 'obsidian' THEN 5
                 WHEN 'episodic_memory' THEN 6
                 ELSE 7
               END,
               confidence DESC,
               created_at DESC
             LIMIT ?1",
        )
        .db_err()?;

    let rows = stmt
        .query_map([limit as i64], |row| {
            Ok(IndexEntry {
                id: row.get(0)?,
                summary: row.get(1)?,
                source_table: row.get(2)?,
                source_id: row.get(3)?,
                category: row.get(4)?,
                confidence: row.get(5)?,
                created_at: row.get(6)?,
            })
        })
        .db_err()?;

    rows.collect::<std::result::Result<Vec<_>, _>>().db_err()
}

/// Fetch full content from the source table for a given index entry.
/// On successful recall, reinforces the entry's confidence to 1.0 so
/// actively-used memories never decay.
pub fn recall_content(
    db: &Database,
    source_table: &str,
    source_id: &str,
) -> Result<Option<String>> {
    let conn = db.conn();
    let query = match source_table {
        "episodic_memory" => "SELECT content FROM episodic_memory WHERE id = ?1",
        "semantic_memory" => "SELECT value FROM semantic_memory WHERE id = ?1",
        "procedural_memory" => "SELECT steps FROM procedural_memory WHERE id = ?1",
        "relationship_memory" => {
            "SELECT COALESCE(interaction_summary, entity_name) FROM relationship_memory WHERE id = ?1"
        }
        "learned_skills" => "SELECT description FROM learned_skills WHERE id = ?1",
        "obsidian" => {
            // Obsidian notes live on disk — return a pointer, not content.
            return Ok(Some(format!(
                "[Obsidian note: {source_id}] — use obsidian_read to fetch full content"
            )));
        }
        _ => return Ok(None),
    };
    let result = match conn.query_row(query, [source_id], |row| row.get::<_, String>(0)) {
        Ok(content) => Ok(Some(content)),
        Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
        Err(e) => Err(RoboticusError::Database(format!(
            "recall_content failed: {e}"
        ))),
    };

    // Reinforce confidence on successful recall — actively-used memories never decay.
    if result.as_ref().is_ok_and(|opt| opt.is_some()) {
        let _ = conn.execute(
            "UPDATE memory_index SET confidence = 1.0, last_verified = datetime('now')
             WHERE source_table = ?1 AND source_id = ?2",
            rusqlite::params![source_table, source_id],
        );
    }

    result
}

/// Check if an episodic memory content string is a derivable tool output
/// that should not be indexed. These outputs can be re-queried by calling
/// the tool again, so storing them in the index just adds noise.
/// Check if an episodic memory content string is a derivable tool output
/// that should not be indexed. Covers both legacy ("Used tool '...'") and
/// current ("Executed '...'") format strings.
fn is_derivable_tool_output(content: &str) -> bool {
    const DERIVABLE_PREFIXES: &[&str] = &[
        "Executed 'list_directory'",
        "Executed 'list-subagent-roster'",
        "Executed 'get_subagent_status'",
        "Executed 'get_runtime_context'",
        "Executed 'get_memory_stats'",
        "Executed 'list-open-tasks'",
        "Executed 'list-available-skills'",
        "Executed 'task-status'",
        "Executed 'get_wallet_balance'",
        "Executed 'read_file'",
        "Executed 'search_files'",
        "Executed 'glob_files'",
        "Executed 'obsidian_search'",
        "Executed 'obsidian_read'",
        "Executed 'bash'",
        "Used tool 'list_directory'",
        "Used tool 'list-subagent-roster'",
        "Used tool 'get_subagent_status'",
        "Used tool 'get_runtime_context'",
        "Used tool 'get_memory_stats'",
        "Used tool 'list-open-tasks'",
        "Used tool 'list-available-skills'",
        "Used tool 'task-status'",
        "Used tool 'get_wallet_balance'",
        "Used tool 'read_file'",
        "Used tool 'search_files'",
        "Used tool 'glob_files'",
        "Used tool 'obsidian_search'",
        "Used tool 'obsidian_read'",
        "Used tool 'get_channel_health'",
        "Used tool 'echo'",
        "Used tool 'bash'",
    ];
    DERIVABLE_PREFIXES
        .iter()
        .any(|prefix| content.starts_with(prefix))
}

/// Backfill missing index entries for memories that were stored before
/// inline indexing was added. Returns the number of entries created.
/// `batch_size` limits per-table work (0 = unlimited for migration).
///
/// Uses bulk `INSERT ... SELECT` for the migration case (batch_size=0)
/// which is orders of magnitude faster than row-by-row inserts.
pub fn backfill_missing_index_entries(db: &Database, batch_size: usize) -> Result<usize> {
    let conn = db.conn();
    let mut total = 0usize;

    if batch_size == 0 {
        // Bulk mode: single INSERT ... SELECT per table (fast migration path).
        // SQLite's substr() handles the 150-char summary truncation.
        // Skip derivable tool outputs — their results can be re-queried and
        // they pollute the index with noise that causes the model to tool-call
        // instead of answering the user.
        total += conn.execute(
            "INSERT OR IGNORE INTO memory_index (id, summary, source_table, source_id, category, confidence)
             SELECT 'idx-episodic_memory-' || substr(id, 1, 12),
                    substr(content, 1, 150),
                    'episodic_memory', id, classification, 1.0
             FROM episodic_memory
             WHERE memory_state = 'active'
               AND id NOT IN (SELECT source_id FROM memory_index WHERE source_table = 'episodic_memory')
               AND content NOT LIKE 'Executed ''list_directory''%'
               AND content NOT LIKE 'Executed ''list-subagent-roster''%'
               AND content NOT LIKE 'Executed ''get_subagent_status''%'
               AND content NOT LIKE 'Executed ''get_runtime_context''%'
               AND content NOT LIKE 'Executed ''get_memory_stats''%'
               AND content NOT LIKE 'Executed ''list-open-tasks''%'
               AND content NOT LIKE 'Executed ''list-available-skills''%'
               AND content NOT LIKE 'Executed ''task-status''%'
               AND content NOT LIKE 'Executed ''get_wallet_balance''%'
               AND content NOT LIKE 'Executed ''read_file''%'
               AND content NOT LIKE 'Executed ''search_files''%'
               AND content NOT LIKE 'Executed ''glob_files''%'
               AND content NOT LIKE 'Executed ''obsidian_search''%'
               AND content NOT LIKE 'Executed ''obsidian_read''%'
               AND content NOT LIKE 'Used tool ''list_directory''%'
               AND content NOT LIKE 'Used tool ''list-subagent-roster''%'
               AND content NOT LIKE 'Used tool ''get_subagent_status''%'
               AND content NOT LIKE 'Used tool ''get_runtime_context''%'
               AND content NOT LIKE 'Used tool ''get_memory_stats''%'
               AND content NOT LIKE 'Used tool ''list-open-tasks''%'
               AND content NOT LIKE 'Used tool ''list-available-skills''%'
               AND content NOT LIKE 'Used tool ''task-status''%'
               AND content NOT LIKE 'Used tool ''get_wallet_balance''%'
               AND content NOT LIKE 'Used tool ''read_file''%'
               AND content NOT LIKE 'Used tool ''search_files''%'
               AND content NOT LIKE 'Used tool ''glob_files''%'
               AND content NOT LIKE 'Used tool ''obsidian_search''%'
               AND content NOT LIKE 'Used tool ''obsidian_read''%'
               AND content NOT LIKE 'Used tool ''get_channel_health''%'
               AND content NOT LIKE 'Used tool ''echo''%'
               AND content NOT LIKE 'Used tool ''bash''%'
               AND content NOT LIKE 'Executed ''bash''%'",
            [],
        ).db_err()?;

        total += conn.execute(
            "INSERT OR IGNORE INTO memory_index (id, summary, source_table, source_id, category, confidence)
             SELECT 'idx-semantic_memory-' || substr(id, 1, 12),
                    substr(key || ': ' || value, 1, 150),
                    'semantic_memory', id, category, 1.0
             FROM semantic_memory
             WHERE memory_state = 'active'
               AND id NOT IN (SELECT source_id FROM memory_index WHERE source_table = 'semantic_memory')",
            [],
        ).db_err()?;

        total += conn.execute(
            "INSERT OR IGNORE INTO memory_index (id, summary, source_table, source_id, category, confidence)
             SELECT 'idx-procedural_memory-' || substr(id, 1, 12),
                    'Tool: ' || name,
                    'procedural_memory', id, 'procedural', 1.0
             FROM procedural_memory
             WHERE id NOT IN (SELECT source_id FROM memory_index WHERE source_table = 'procedural_memory')",
            [],
        ).db_err()?;

        total += conn.execute(
            "INSERT OR IGNORE INTO memory_index (id, summary, source_table, source_id, category, confidence)
             SELECT 'idx-relationship_memory-' || substr(id, 1, 12),
                    'Entity: ' || COALESCE(entity_name, entity_id) || ' (trust: ' || CAST(ROUND(trust_score, 1) AS TEXT) || ')',
                    'relationship_memory', id, 'relationship', 1.0
             FROM relationship_memory
             WHERE id NOT IN (SELECT source_id FROM memory_index WHERE source_table = 'relationship_memory')",
            [],
        ).db_err()?;

        total += conn.execute(
            "INSERT OR IGNORE INTO memory_index (id, summary, source_table, source_id, category, confidence)
             SELECT 'idx-learned_skills-' || substr(id, 1, 12),
                    substr('Skill: ' || name || ' - ' || description, 1, 150),
                    'learned_skills', id, 'learned_skill', 1.0
             FROM learned_skills
             WHERE id NOT IN (SELECT source_id FROM memory_index WHERE source_table = 'learned_skills')",
            [],
        ).db_err()?;

        return Ok(total);
    }

    // Batched mode: collect rows while holding conn, drop conn, then upsert.
    // This avoids deadlock since upsert_index_entry also acquires db.conn().
    let limit = batch_size as i64;

    struct PendingIndex {
        source_table: &'static str,
        source_id: String,
        summary: String,
        category: String,
    }
    let mut pending: Vec<PendingIndex> = Vec::new();

    {
        // Episodic (skip stale — they were deliberately removed by dedup/tier-sync)
        let mut stmt = conn
            .prepare(
                "SELECT id, classification, content FROM episodic_memory
                 WHERE memory_state = 'active'
                   AND id NOT IN (SELECT source_id FROM memory_index WHERE source_table = 'episodic_memory')
                 LIMIT ?1",
            )
            .db_err()?;
        let rows = stmt
            .query_map([limit], |r| {
                Ok((
                    r.get::<_, String>(0)?,
                    r.get::<_, String>(1)?,
                    r.get::<_, String>(2)?,
                ))
            })
            .db_err()?;
        for row in rows.flatten() {
            // Skip derivable tool outputs — same policy as ingest_turn
            if is_derivable_tool_output(&row.2) {
                continue;
            }
            pending.push(PendingIndex {
                source_table: "episodic_memory",
                source_id: row.0,
                summary: row.2.chars().take(150).collect(),
                category: row.1,
            });
        }

        // Semantic
        let mut stmt = conn
            .prepare(
                "SELECT id, category, key, value FROM semantic_memory
                 WHERE memory_state = 'active'
                   AND id NOT IN (SELECT source_id FROM memory_index WHERE source_table = 'semantic_memory')
                 LIMIT ?1",
            )
            .db_err()?;
        let rows = stmt
            .query_map([limit], |r| {
                Ok((
                    r.get::<_, String>(0)?,
                    r.get::<_, String>(1)?,
                    r.get::<_, String>(2)?,
                    r.get::<_, String>(3)?,
                ))
            })
            .db_err()?;
        for row in rows.flatten() {
            pending.push(PendingIndex {
                source_table: "semantic_memory",
                source_id: row.0,
                summary: format!("{}: {}", row.2, row.3).chars().take(150).collect(),
                category: row.1,
            });
        }

        // Procedural
        let mut stmt = conn
            .prepare(
                "SELECT id, name FROM procedural_memory
                 WHERE id NOT IN (SELECT source_id FROM memory_index WHERE source_table = 'procedural_memory')
                 LIMIT ?1",
            )
            .db_err()?;
        let rows = stmt
            .query_map([limit], |r| {
                Ok((r.get::<_, String>(0)?, r.get::<_, String>(1)?))
            })
            .db_err()?;
        for row in rows.flatten() {
            pending.push(PendingIndex {
                source_table: "procedural_memory",
                source_id: row.0,
                summary: format!("Tool: {}", row.1),
                category: "procedural".into(),
            });
        }

        // Relationship
        let mut stmt = conn
            .prepare(
                "SELECT id, entity_name, trust_score FROM relationship_memory
                 WHERE id NOT IN (SELECT source_id FROM memory_index WHERE source_table = 'relationship_memory')
                 LIMIT ?1",
            )
            .db_err()?;
        let rows = stmt
            .query_map([limit], |r| {
                Ok((
                    r.get::<_, String>(0)?,
                    r.get::<_, String>(1)?,
                    r.get::<_, f64>(2)?,
                ))
            })
            .db_err()?;
        for row in rows.flatten() {
            pending.push(PendingIndex {
                source_table: "relationship_memory",
                source_id: row.0,
                summary: format!("Entity: {} (trust: {:.1})", row.1, row.2),
                category: "relationship".into(),
            });
        }

        // Learned skills
        let mut stmt = conn
            .prepare(
                "SELECT id, name, description FROM learned_skills
                 WHERE id NOT IN (SELECT source_id FROM memory_index WHERE source_table = 'learned_skills')
                 LIMIT ?1",
            )
            .db_err()?;
        let rows = stmt
            .query_map([limit], |r| {
                Ok((
                    r.get::<_, String>(0)?,
                    r.get::<_, String>(1)?,
                    r.get::<_, String>(2)?,
                ))
            })
            .db_err()?;
        for row in rows.flatten() {
            pending.push(PendingIndex {
                source_table: "learned_skills",
                source_id: row.0,
                summary: format!("Skill: {} - {}", row.1, row.2)
                    .chars()
                    .take(150)
                    .collect(),
                category: "learned_skill".into(),
            });
        }
    }
    drop(conn); // Release before upserting

    for p in &pending {
        upsert_index_entry(
            db,
            p.source_table,
            &p.source_id,
            &p.summary,
            Some(&p.category),
        )?;
        total += 1;
    }

    Ok(total)
}

/// Remove index entries whose source row no longer exists.
pub fn cleanup_orphaned_index_entries(db: &Database) -> Result<usize> {
    let conn = db.conn();
    let tables = [
        ("episodic_memory", "episodic_memory"),
        ("semantic_memory", "semantic_memory"),
        ("procedural_memory", "procedural_memory"),
        ("relationship_memory", "relationship_memory"),
        ("learned_skills", "learned_skills"),
    ];
    let mut total = 0usize;
    for (source_table, actual_table) in &tables {
        let removed = conn
            .execute(
                &format!(
                    "DELETE FROM memory_index WHERE source_table = ?1
                     AND source_id NOT IN (SELECT id FROM {actual_table})"
                ),
                [source_table],
            )
            .db_err()?;
        total += removed;
    }
    // Obsidian entries are checked separately by the consolidation worker
    // (requires filesystem access, not just DB).
    Ok(total)
}

/// Remove FTS entries whose rowid has no matching memory_index row.
/// These accumulate when memories are deleted or overwritten without
/// cleaning the FTS table.
pub fn cleanup_orphaned_fts(db: &Database) -> Result<usize> {
    let conn = db.conn();
    let removed = conn
        .execute(
            "DELETE FROM memory_fts WHERE rowid NOT IN (SELECT rowid FROM memory_index)",
            [],
        )
        .db_err()?;
    if removed > 0 {
        tracing::info!(removed, "cleaned orphaned FTS entries");
    }
    Ok(removed)
}

/// Decay confidence scores by a factor. Called periodically by the
/// consolidation worker. Entries below threshold are candidates for pruning.
pub fn decay_confidence(db: &Database, decay_factor: f64) -> Result<usize> {
    let conn = db.conn();
    let changed = conn
        .execute(
            "UPDATE memory_index SET confidence = confidence * ?1
             WHERE confidence > 0.1",
            [decay_factor],
        )
        .db_err()?;
    Ok(changed)
}

/// Remove index entries below a confidence threshold.
/// Remove index entries below a confidence threshold.
/// Entries with confidence < 0 are dedup tombstones — preserved so backfill
/// doesn't recreate them (INSERT OR IGNORE sees the existing row).
/// System sentinels (source_table = 'system') are also preserved.
pub fn prune_low_confidence(db: &Database, threshold: f64) -> Result<usize> {
    let conn = db.conn();
    let removed = conn
        .execute(
            "DELETE FROM memory_index
             WHERE confidence >= 0.0 AND confidence < ?1
               AND source_table != 'system'",
            [threshold],
        )
        .db_err()?;
    Ok(removed)
}

/// Format the index as a compact text block for context injection.
/// Each entry is one line: `[category] summary (confidence%)`
pub fn format_index_for_injection(entries: &[IndexEntry]) -> String {
    if entries.is_empty() {
        return String::new();
    }
    let mut text = String::from("[Memory Index — call recall_memory(id) for details]\n");
    for entry in entries {
        let cat = entry.category.as_deref().unwrap_or("general");
        let conf = (entry.confidence * 100.0) as u32;
        text.push_str(&format!(
            "- [{}] {} ({}% confidence, id: {})\n",
            cat, entry.summary, conf, entry.id,
        ));
    }
    text
}

#[cfg(test)]
mod tests {
    use super::*;

    fn test_db() -> Database {
        let db = Database::new(":memory:").unwrap();
        crate::schema::initialize_db(&db).unwrap();
        db
    }

    #[test]
    fn upsert_and_retrieve() {
        let db = test_db();
        upsert_index_entry(
            &db,
            "episodic_memory",
            "ep-001",
            "Workspace cleanup performed",
            Some("maintenance"),
        )
        .unwrap();
        let entries = top_entries(&db, 10).unwrap();
        assert_eq!(entries.len(), 1);
        assert!(entries[0].summary.contains("cleanup"));
        assert_eq!(entries[0].confidence, 1.0);
    }

    #[test]
    fn upsert_updates_existing() {
        let db = test_db();
        upsert_index_entry(&db, "episodic_memory", "ep-001", "Old summary", None).unwrap();
        upsert_index_entry(&db, "episodic_memory", "ep-001", "New summary", None).unwrap();
        let entries = top_entries(&db, 10).unwrap();
        assert_eq!(entries.len(), 1);
        assert!(entries[0].summary.contains("New summary"));
    }

    #[test]
    fn confidence_decay() {
        let db = test_db();
        upsert_index_entry(&db, "episodic_memory", "ep-001", "Test", None).unwrap();
        decay_confidence(&db, 0.8).unwrap();
        let entries = top_entries(&db, 10).unwrap();
        assert!((entries[0].confidence - 0.8).abs() < 0.01);
    }

    #[test]
    fn prune_removes_low_confidence() {
        let db = test_db();
        upsert_index_entry(&db, "episodic_memory", "ep-001", "Keep", None).unwrap();
        upsert_index_entry(&db, "episodic_memory", "ep-002", "Remove", None).unwrap();
        // Decay ep-002 below threshold
        {
            let conn = db.conn();
            conn.execute(
                "UPDATE memory_index SET confidence = 0.05 WHERE source_id = 'ep-002'",
                [],
            )
            .unwrap();
        }
        let removed = prune_low_confidence(&db, 0.1).unwrap();
        assert_eq!(removed, 1);
        let entries = top_entries(&db, 10).unwrap();
        assert_eq!(entries.len(), 1);
        assert!(entries[0].summary.contains("Keep"));
    }

    #[test]
    fn format_index_output() {
        let entries = vec![IndexEntry {
            id: "idx-ep-001".into(),
            summary: "Workspace cleaned".into(),
            source_table: "episodic_memory".into(),
            source_id: "ep-001".into(),
            category: Some("maintenance".into()),
            confidence: 0.85,
            created_at: "2026-03-31".into(),
        }];
        let text = format_index_for_injection(&entries);
        assert!(text.contains("[Memory Index"));
        assert!(text.contains("maintenance"));
        assert!(text.contains("85%"));
        assert!(text.contains("idx-ep-001"));
    }
}