upsert_indexer_categories executes DELETE then loop INSERTs without a transaction

[forkwright]

Finding

upsert_indexer_categories issues a DELETE to remove all existing category rows for an indexer, then immediately begins a loop of individual INSERT OR REPLACE statements — none of this is wrapped in a transaction. Any failure after the DELETE (an insert error, a process kill, or a connection drop) leaves the indexer with zero categories in the database. A concurrent read during the loop window sees a partially-populated or empty category table for that indexer, causing typed searches to silently exclude it. refresh_caps (search.rs:195) calls this function as the middle step of a three-step non-transactional sequence.

Evidence

crates/zetesis/src/repo.rs:166-197:

sqlx::query("DELETE FROM indexer_categories WHERE indexer_id = ?")
    .bind(indexer_id)
    .execute(pool)
    .await
    .context(…)?;
// …
for (cat_id, name) in flat {
    sqlx::query("INSERT OR REPLACE INTO indexer_categories …")
        .execute(pool).await
        .context(…)?;   // returns Err here leaves table empty
}

Why this matters

A partially-applied caps refresh permanently removes category metadata for an indexer with no diagnostic. On a counter-surveillance device, the failure is silent: a search that should route through a given indexer is quietly dropped, and the operator has no signal that their query coverage has narrowed. The three-step refresh_caps sequence (update caps JSON → upsert categories → update status) compounds this — interruption after step 1 leaves caps JSON and category rows mismatched, so the persisted state no longer describes a real indexer configuration.

Desired correction

Wrap the entire upsert_indexer_categories body in a BEGIN … COMMIT transaction (via pool.begin().await?), committing only after the final insert succeeds. Wrap the three-step refresh_caps sequence in a single transaction as well so the caps JSON, category rows, and status update apply or roll back together.

Done when: upsert_indexer_categories and the caller refresh_caps each execute their mutations atomically; a failure mid-sequence rolls back all changes and leaves the previous state intact.