Reports retention: files beyond the newest 50 runs off the default bookmark expire 20b8fe85888b

-- Report retention (oxygenhub#127, docs/reports.md §3.3): when a report's

-- files expired. Its metrics and summary stay; its page says so.

ALTER TABLE reports ADD COLUMN files_expired_ms INTEGER;

    // Report files past retention first (#127), so this run prunes them.

    match crate::reports::expire(&app.db).await {

        Ok(0) => {}

        Ok(n) => tracing::info!("gc: {n} reports' files expired under retention"),

        Err(e) => tracing::warn!("gc: expiring report files: {e}"),

    }

    /// When its files expired under retention (§3.3); its metrics stay.

    pub files_expired_ms: Option<i64>,

/// Runs of a report on a bookmark other than the default whose files are

/// kept (docs/reports.md §3.3); older runs keep their metrics only.

pub const KEEP_OTHER_RUNS: i64 = 50;

/// Expire report files under retention (docs/reports.md §3.3): every run on

/// a repository's default bookmark keeps them; on any other bookmark (an

/// upload for a change has none) the newest [`KEEP_OTHER_RUNS`] runs of each

/// report do. An expired report keeps its metrics and summary and loses

/// its `report_files` rows, so garbage collection may prune the blobs.

/// Answers how many reports expired.

pub async fn expire(pool: &Pool) -> sqlx::Result<u64> {

    let mut tx = crate::db::begin_write(pool).await?;

    let ids: Vec<i64> = sqlx::query_scalar(

        "SELECT r.id FROM reports r JOIN repositories p ON p.id = r.repo_id \

         WHERE r.files_expired_ms IS NULL AND r.bookmark != p.default_bookmark \

         AND (SELECT COUNT(*) FROM reports s WHERE s.repo_id = r.repo_id AND s.name = r.name AND s.bookmark = r.bookmark \

              AND (s.created_ms > r.created_ms OR (s.created_ms = r.created_ms AND s.id > r.id))) >= ?",

    )

    .bind(KEEP_OTHER_RUNS)

    .fetch_all(&mut *tx)

    .await?;

    let now = now_ms();

    for id in &ids {

        sqlx::query("DELETE FROM report_files WHERE report_id = ?").bind(id).execute(&mut *tx).await?;

        sqlx::query("UPDATE reports SET files_expired_ms = ? WHERE id = ?").bind(now).bind(id).execute(&mut *tx).await?;

    }

    tx.commit().await?;

    Ok(ids.len() as u64)

}

/// oxygenhub#127 §3.3: report files expire beyond the newest 50 runs of a

/// report on a bookmark other than the default; the default keeps all,

/// and an expired report keeps its metrics.

#[tokio::test(flavor = "multi_thread")]

async fn report_files_expire_under_retention() {

    let h = hub().await;

    create(&h.app, "andy", "demo").await;

    let repo = db::repos::get(&h.app.db, "andy", "demo").await.unwrap().unwrap();

    let add = |bookmark: &'static str, at: i64| {

        let db = h.app.db.clone();

        async move {

            let id: i64 = sqlx::query_scalar(

                "INSERT INTO reports (repo_id, name, kind, title, summary, context, job, bookmark, state, files, bytes, created_ms) \

                 VALUES (?, 'perf', 'performance', 'Perf', '', '{}', 'perf', ?, 's', 1, 1, ?) RETURNING id",

            )

            .bind(repo.id)

            .bind(bookmark)

            .bind(at)

            .fetch_one(&db)

            .await

            .unwrap();

            sqlx::query("INSERT INTO report_files (report_id, path, blob, size) VALUES (?, 'report.toml', ?, 1)")

                .bind(id)

                .bind(format!("{:064x}", id))

                .execute(&db)

                .await

                .unwrap();

            sqlx::query("INSERT INTO report_metrics (report_id, key, value, unit, better) VALUES (?, 'ms', 1, '', '')")

                .bind(id)

                .execute(&db)

                .await

                .unwrap();

            id

        }

    };

    let main_old = add("main", 1).await;

    add("main", 2).await;

    let mut feature = Vec::new();

    for at in 0..52 {

        feature.push(add("feature", 10 + at).await);

    }

    assert_eq!(oxygenhub::reports::expire(&h.app.db).await.unwrap(), 2, "the two oldest feature runs");

    let files = |id: i64| {

        let db = h.app.db.clone();

        async move {

            sqlx::query_scalar::<_, i64>("SELECT COUNT(*) FROM report_files WHERE report_id = ?").bind(id).fetch_one(&db).await.unwrap()

        }

    };

    assert_eq!((files(feature[0]).await, files(feature[1]).await, files(feature[2]).await), (0, 0, 1));

    assert_eq!(files(main_old).await, 1, "the default bookmark keeps every run");

    assert_eq!(oxygenhub::reports::metrics(&h.app.db, feature[0]).await.unwrap().len(), 1, "metrics are kept");

    assert_eq!(oxygenhub::reports::expire(&h.app.db).await.unwrap(), 0, "once");

    let (_, page) = call(&h.app, "GET", &format!("/andy/demo/reports/perf?id={}", feature[0]), None, None).await;

    assert!(page.contains("files have expired"), "the page says so");

}

`data/` from each run's capability URL. Retention (§3.3) runs before each garbage

collection: every run on the default bookmark keeps its files, and the

newest 50 runs of each report on any other bookmark (proposal and tag

rules, and a per-repository setting, follow); an expired run keeps its

metrics and summary, and its page says its files expired. `oh report`

(4) follows.

{% if report.files_expired_ms.is_some() %}<p class="strip">This run's files have expired under retention: its metrics and summary are kept, its page and data are not. Runs on {{ repo.default_bookmark }} keep their files, and the newest {{ crate::reports::KEEP_OTHER_RUNS }} runs on any other bookmark.</p>{% endif %}