difflore_cli/hooks/session_banner/

query.rs

//! SQL for "new rules since `prev_ts`, scoped to this repo".
//!
//! Reads canonical rules from `~/.difflore/data.db` (the global rule
//! ledger; per-project context indexes are a separate concern that
//! the banner doesn't need). We filter `source_repo` against the
//! current repo's aliases so a user in `acme/billing` doesn't get
//! spammed with rules learned in `acme/notifier`.
//!
//! The query is deliberately narrow:
//!   * `installed_at > prev_ts` (or all rows when `prev_ts` is None)
//!   * `LOWER(source_repo)` in (alias set)
//!   * `status = 'active'`            — pending rules aren't yet useful
//!   * ordered by `installed_at` DESC — most recent first
//!   * `LIMIT ?` — banner shape ceiling
//!
//! `created_at` in the `rule_events` stream is one floor lower (it
//! captures every state change); for "new since last session" the
//! right grain is *rule creation*, which lives on `skills.installed_at`.
//! Using `rule_events` would surface confidence-bumps as "new rules"
//! and make the banner noisy.

use sqlx::Row;

/// One row in the banner. Title + provenance fragment ("from PR review
/// by alice", "from PR merge signature", "manual `difflore rules add`").
/// The render module is free to truncate either field — both are
/// passed through verbatim from the DB and may be long.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct NewRule {
    /// Human-readable rule name as stored in `skills.name`. Often a
    /// sentence-case headline like "Wrap context cancellation in errgroup".
    pub title: String,
    /// One of the `origin` enum values: `manual`, `conversation`,
    /// `pr_review`, `extracted`. Drives the provenance phrase in the
    /// rendered banner.
    pub origin: String,
    /// `source_repo` value, used only for the optional "← from {repo}"
    /// suffix when a rule was cross-loaded from another repo. Today
    /// the query filters to *this* repo's aliases so this is mostly
    /// the same as the current repo, but the column is preserved for
    /// future cross-repo widening.
    pub source_repo: Option<String>,
}

/// Query `data.db` for up to `limit` rules whose `installed_at` is later
/// than `prev_ts_ms` (millis since epoch) AND whose `source_repo`
/// matches one of `repo_aliases` (case-insensitive). When `prev_ts_ms`
/// is `None`, the time filter is dropped and we return the `limit`
/// most recent rules for the repo — that's the "first session ever"
/// case where every rule learned to date is genuinely new to the user.
///
/// Returns a Vec ordered newest-first. SQL errors propagate as
/// `Err(String)` so the caller can fold into the "swallow and emit
/// nothing" branch.
pub async fn new_rules_since(
    db: &difflore_core::SqlitePool,
    prev_ts_ms: Option<i64>,
    repo_aliases: &[String],
    limit: usize,
) -> Result<Vec<NewRule>, String> {
    if repo_aliases.is_empty() {
        // No repo identity → we'd select rules from every repo on
        // the user's machine, which is the exact noise this filter
        // exists to prevent.
        return Ok(Vec::new());
    }

    // Pre-serialise the alias set into a JSON array sqlx can bind as a
    // single parameter, then unfold via `json_each` inside the SQL.
    // Matches the pattern used by `commands::status::queries` so
    // sqlite's plan cache reuses the same shape.
    let repos_json = serde_json::to_string(repo_aliases).map_err(|e| format!("aliases: {e}"))?;

    // Convert the watermark millis into a SQLite-compatible UTC
    // ISO-8601 string. `skills.installed_at` is stored via
    // `datetime('now')`, which produces `YYYY-MM-DD HH:MM:SS` (no
    // millisecond precision). Comparing them via `datetime(…)` on
    // both sides normalises the format. When `prev_ts_ms` is `None`
    // we pass the unix epoch (1970-01-01), which keeps the SQL
    // single-shape (no branching) and trivially matches every row.
    let watermark_iso = prev_ts_ms
        .and_then(chrono::DateTime::<chrono::Utc>::from_timestamp_millis)
        .map_or_else(|| "1970-01-01T00:00:00Z".to_owned(), |dt| dt.to_rfc3339());

    let limit_i64 = i64::try_from(limit).unwrap_or(5);

    // The query:
    //   * `status = 'active'` keeps the banner free of pending rules
    //     (those are unverified and could embarrass the user when
    //     surfaced before they hit the review gate).
    //   * `LOWER(source_repo)` matches the alias set generated by
    //     `repo_aliases_for` (which lowercases).
    //   * Sorting by `installed_at` DESC + `id` DESC keeps ties
    //     deterministic (ULID id ordering tracks insertion time, so
    //     the tiebreak surfaces the truly-latest insert when two rules
    //     share a `installed_at` second).
    let rows = sqlx::query(
        r"SELECT name, origin, source_repo
          FROM skills
          WHERE status = 'active'
            AND source_repo IS NOT NULL
            AND TRIM(source_repo) <> ''
            AND LOWER(source_repo) IN (SELECT value FROM json_each(?1))
            AND datetime(installed_at) > datetime(?2)
          ORDER BY datetime(installed_at) DESC, id DESC
          LIMIT ?3",
    )
    .bind(repos_json)
    .bind(watermark_iso)
    .bind(limit_i64)
    .fetch_all(db)
    .await
    .map_err(|e| format!("query skills: {e}"))?;

    let mut out = Vec::with_capacity(rows.len());
    for row in rows {
        let title: String = row.try_get("name").unwrap_or_default();
        let origin: String = row
            .try_get("origin")
            .unwrap_or_else(|_| "manual".to_owned());
        let source_repo: Option<String> = row.try_get("source_repo").ok();
        if title.trim().is_empty() {
            // Defensive: a corrupted row with no `name` is unhelpful
            // in the banner. Skip rather than render an empty bullet.
            continue;
        }
        out.push(NewRule {
            title,
            origin,
            source_repo,
        });
    }
    Ok(out)
}