perf(memory): update_fidelity_tags issues N sequential SQLite UPDATEs per turn — consider batch UPDATE

[bug-ops]

Description

MessageStore::update_fidelity_tags in crates/zeph-memory/src/store/messages/mod.rs:401 executes one UPDATE messages SET fidelity_tag = ? WHERE id = ? per message in a loop inside a single transaction. For a context window with 100+ messages this is 100+ sequential execute calls.

Code

let mut tx = self.pool.begin().await?;
for &(id, tag) in updates {
    zeph_db::query(sql!("UPDATE messages SET fidelity_tag = ? WHERE id = ?"))
        .bind(i32::from(tag))
        .bind(id)
        .execute(&mut *tx)
        .await?;
}
tx.commit().await?;

Expected Behavior

A single SQL statement (e.g. UPDATE messages SET fidelity_tag = CASE id WHEN ? THEN ? ... END WHERE id IN (...)) or a temporary table join to batch all updates in one round-trip.

Actual Behavior

N round-trips to the SQLite pool per scoring pass. For a 200-message window this is 200 sequential awaits on the agent's main context-assembly path.

Impact

• Context assembly latency grows linearly with history length.
• persist_fidelity_tags is called unconditionally after every score_and_apply, including when most messages have unchanged tags.

Environment

• Version: 0.21.2 (397c3c8)
• Introduced in: CAM Phase 2-B (feat(context,memory): persist ContextFidelity per message for cross-turn floor invariant (#4550) #4615)
• File: crates/zeph-memory/src/store/messages/mod.rs:401
• Caller: crates/zeph-agent-context/src/service.rs:685, :1124

[bug-ops]

Fix Approach: Batch UPDATE with WHERE id IN (...)

Current code issues N sequential execute() calls inside a single transaction:

// crates/zeph-memory/src/store/messages/mod.rs:409
for &(id, tag) in updates {
    query("UPDATE messages SET fidelity_tag = ? WHERE id = ?")
        .bind(i32::from(tag))
        .bind(id)
        .execute(&mut *tx)
        .await?;
}

Each .await? is a separate round-trip to SQLite even within a transaction. For a 20-message window this is 20 sequential awaits.

Approach 1: Batch by fidelity_tag value (best for SQLite)

Group updates by tag value, then one UPDATE per distinct tag:

use std::collections::HashMap;

let mut by_tag: HashMap<i32, Vec<i64>> = HashMap::new();
for &(id, tag) in updates {
    by_tag.entry(i32::from(tag)).or_default().push(id.0);
}
for (tag, ids) in &by_tag {
    let placeholders = ids.iter().map(|_| "?").collect::<Vec<_>>().join(",");
    let sql = format!("UPDATE messages SET fidelity_tag = {tag} WHERE id IN ({placeholders})");
    let mut q = zeph_db::query(&sql);
    for id in ids {
        q = q.bind(id);
    }
    q.execute(&mut *tx).await?;
}

This reduces N round-trips to (distinct tag values) round-trips — typically 3–4 (Full/Compressed/Placeholder/Dropped).

Approach 2: Single CASE WHEN statement

If sqlx supports dynamic query building cleanly, one can issue a single UPDATE with a CASE expression. However Approach 1 is simpler and equally effective for SQLite.

Recommended: Approach 1

• File: crates/zeph-memory/src/store/messages/mod.rs:401
• Expected improvement: O(N) → O(distinct tags) awaits per turn — ~5-7x fewer for typical windows
• Note: transaction atomicity is preserved in both approaches