bhtune_db/models/
tune_runs.rs

1use bhtune_core::{
2    ControllerDirection, ControllerType, DcsTemplate, LoopConfig, LoopTags, ProcessType,
3};
4use chrono::{DateTime, Utc};
5use sqlx::{QueryBuilder, Row, Sqlite, SqlitePool, sqlite::SqliteRow};
6
7use crate::{
8    convert::{enum_to_text, json_text, text_to_enum},
9    error::{DbError, DbResult},
10};
11
12use super::dcs_templates::TemplateOrigin;
13
14#[cfg(doc)]
15use super::{dcs_templates::DcsTemplateRow, loops::LoopRow, tune_results::TuneResultRow};
16
17// tune_runs {{{1
18
19/// Which [`crate`]-agnostic I/O driver a run used. Lives in `bhtune-db` rather than
20/// `bhtune-core` because it's a persistence/orchestration concept (which adapter drove this
21/// run), not a domain concept the pure MRFT engine itself needs to know about.
22#[derive(Debug, Clone, Copy, PartialEq, Eq, Hash, serde::Serialize, serde::Deserialize)]
23#[cfg_attr(feature = "utoipa", derive(utoipa::ToSchema))]
24#[serde(rename_all = "snake_case")]
25pub enum TuneDriver {
26    Opcda,
27    Simulator,
28    Replay,
29}
30
31/// A run's lifecycle state.
32#[derive(Debug, Clone, Copy, PartialEq, Eq, Hash, serde::Serialize, serde::Deserialize)]
33#[cfg_attr(feature = "utoipa", derive(utoipa::ToSchema))]
34#[serde(rename_all = "snake_case")]
35pub enum TuneOutcome {
36    Running,
37    Completed,
38    Failed,
39    Aborted,
40}
41
42/// The clock basis used for a run's persisted polling-cadence diagnostics.
43#[derive(Debug, Clone, Copy, PartialEq, Eq, Hash, serde::Serialize, serde::Deserialize)]
44#[cfg_attr(feature = "utoipa", derive(utoipa::ToSchema))]
45#[serde(rename_all = "snake_case")]
46pub enum TimingBasis {
47    /// Simulator process evolution and MRFT timestamps both advance by one exact configured
48    /// poll interval per successful PV sample.
49    SimulatedFixedStep,
50    /// Live OPC DA timestamps are UTC projections of monotonic elapsed time, preserving real
51    /// scheduling and driver delays without exposure to wall-clock adjustments.
52    LiveMonotonic,
53}
54
55/// Advisory assessment of whether the recorded samples provide enough observations per
56/// oscillation period for a trustworthy extrema measurement.
57#[derive(
58    Debug, Clone, Copy, Default, PartialEq, Eq, Hash, serde::Serialize, serde::Deserialize,
59)]
60#[cfg_attr(feature = "utoipa", derive(utoipa::ToSchema))]
61#[serde(rename_all = "snake_case")]
62pub enum SamplingAdequacy {
63    /// At least the documented minimum number of samples per measured oscillation period.
64    Adequate,
65    /// Fewer than the documented minimum number of samples per measured oscillation period.
66    Marginal,
67    /// No finite, positive oscillation period was available for this assessment.
68    #[default]
69    NotAssessed,
70}
71
72/// Summary statistics for one class of measured polling work.
73#[derive(Debug, Clone, Copy, PartialEq, serde::Serialize, serde::Deserialize)]
74#[cfg_attr(feature = "utoipa", derive(utoipa::ToSchema))]
75pub struct TimingSummary {
76    pub count: u64,
77    pub mean_ms: Option<f64>,
78    pub max_ms: Option<f64>,
79}
80
81/// Operation-latency diagnostics captured while a run is polling.
82///
83/// Each category counts only operations that completed successfully. A zero count with
84/// `None` mean/max means that the operation did not occur during the run. Categories can
85/// overlap: while a relay command is pending, one batched OPC read supplies both the PV sample
86/// and MV verification, so its elapsed duration may appear in both summaries and must not be
87/// added twice as independent I/O time.
88#[derive(Debug, Clone, Copy, PartialEq, serde::Serialize, serde::Deserialize)]
89#[cfg_attr(feature = "utoipa", derive(utoipa::ToSchema))]
90pub struct PollLatencyMetrics {
91    pub pv_read: TimingSummary,
92    pub mv_write: TimingSummary,
93    pub mv_verification: TimingSummary,
94    pub sample_persist: TimingSummary,
95    pub tick_work: TimingSummary,
96}
97
98/// Polling-cadence diagnostics captured over one run's successful PV samples.
99///
100/// The two optional gap fields are `None` when fewer than two samples were observed. The
101/// measured oscillation fields are populated only for a completed MRFT run. Sampling adequacy
102/// is advisory metadata and does not determine calculated-result validity or write eligibility.
103#[derive(Debug, Clone, Copy, PartialEq, serde::Serialize, serde::Deserialize)]
104#[cfg_attr(feature = "utoipa", derive(utoipa::ToSchema))]
105pub struct TimingMetrics {
106    pub basis: TimingBasis,
107    pub requested_interval_ms: u64,
108    pub sample_gap_count: u64,
109    pub mean_sample_gap_ms: Option<f64>,
110    pub max_sample_gap_ms: Option<f64>,
111    /// Number of adjacent sample gaps at least twice the requested interval. Each such gap
112    /// proves that at least one complete polling opportunity was missed.
113    pub missed_poll_opportunity_count: u64,
114    pub measured_oscillation_period_ms: Option<f64>,
115    pub approximate_samples_per_period: Option<f64>,
116    /// Assessment uses the observed samples-per-period value and a six-sample advisory
117    /// threshold. Old timing snapshots deserialize as `not_assessed`.
118    #[serde(default)]
119    pub sampling_adequacy: SamplingAdequacy,
120    /// Detailed operation timings. Old timing snapshots may not contain this field.
121    #[serde(default)]
122    pub poll_latency: Option<PollLatencyMetrics>,
123}
124
125/// Concrete tune timing values after configuration defaults have been resolved.
126///
127/// Stored as one compact snapshot because these values are consumed together and have no
128/// SQL-level filtering requirement. `None` on [`TuneRunRow::effective_tuning`] identifies
129/// runs created before this snapshot existed or callers that have not recorded it yet.
130#[derive(Debug, Clone, Copy, PartialEq, Eq, serde::Serialize, serde::Deserialize)]
131#[cfg_attr(feature = "utoipa", derive(utoipa::ToSchema))]
132pub struct EffectiveTuning {
133    pub mrft_delay_secs: u32,
134    pub poll_interval_ms: u64,
135    pub timeout_secs: u64,
136    pub op_timeout_secs: u64,
137    pub restore_timeout_secs: u64,
138}
139
140/// The outcome of a best-effort loop-restore attempt made after a run ended --
141/// `safety-restore-guard` (finding 3 of the live-plant safety review). Recorded via
142/// [`TuneRunRow::record_restore_status`]; `NULL` in the database (mapped to `None` on
143/// [`TuneRunRow::restore_status`]) means no restore was ever attempted -- either the run
144/// never mutated the loop at all, or it hasn't ended yet.
145#[derive(Debug, Clone, Copy, PartialEq, Eq, Hash, serde::Serialize, serde::Deserialize)]
146#[cfg_attr(feature = "utoipa", derive(utoipa::ToSchema))]
147#[serde(rename_all = "snake_case")]
148pub enum RestoreStatus {
149    /// `restore()` ran every applicable step to completion with no failures.
150    Confirmed,
151    /// A second Ctrl+C arrived, `[tuning].restore_timeout_secs` elapsed, or one or more
152    /// individual restore steps themselves failed, before the restore could be confirmed
153    /// complete. The loop may still be at a relay-test MV/mode -- see `restore_detail` for
154    /// what an operator (or `bhtune restore-loop`) needs to check by hand.
155    Incomplete,
156}
157
158/// The durable eligibility and outcome state for `bhtune restore-loop`.
159#[derive(Debug, Clone, Copy, PartialEq, Eq, Hash, serde::Serialize, serde::Deserialize)]
160#[serde(rename_all = "snake_case")]
161pub enum TuneRecoveryState {
162    Eligible,
163    Running,
164    Confirmed,
165    Incomplete,
166    NotRecoverable,
167}
168
169/// The initial-readings snapshot for a [`TuneRunRow`] — known only once the driver's initial
170/// read actually succeeds (`ReadInitialOPCvalues` in the legacy app); `None` for a run that
171/// failed before or during that step. Combines
172/// [`bhtune_core::mrft::InitialReadings`]/[`bhtune_core::range::PvRange`] with the
173/// resolved [`ControllerDirection`] `core-tuning-math` needs alongside them, as one bespoke
174/// type, since gluing the two existing structs together with one extra field isn't any
175/// simpler than a purpose-built one here.
176#[derive(Debug, Clone, PartialEq, serde::Serialize)]
177pub struct TuneRunInitialReadings {
178    pub pv_ini: f32,
179    pub mv_ini: f32,
180    pub mv_range_low: f32,
181    pub mv_range_high: f32,
182    pub pv_range_high: f32,
183    pub pv_range_low: f32,
184    pub controller_direction: ControllerDirection,
185    /// The controller mode tag's raw value at read time, before any mutation --
186    /// `None` when the template/loop has no mode tag at all. Persisted (rather than kept
187    /// only in-process) so a crashed run's restore intent survives the process dying
188    /// outright.
189    pub mode_raw: Option<String>,
190    /// The mode-attribute tag's raw value at read time, before any mutation -- `None` when
191    /// the template/loop has no mode-attribute tag at all. See `mode_raw`.
192    pub mode_attribute_raw: Option<String>,
193    /// The setpoint read while the loop was still in its original mode, captured only when
194    /// that mode was Auto (mirrors `SvValueIni` in the legacy app) -- `None` otherwise. Read
195    /// here rather than during the mode transition itself (unlike the legacy app), since a
196    /// plain read has no mutation risk and can safely happen before the loop is touched at
197    /// all. See `mode_raw`.
198    pub setpoint_ini: Option<f32>,
199}
200
201/// One row of `tune_runs`: a single MRFT (or future Step Test) execution against a loop.
202#[derive(Debug, Clone, PartialEq, serde::Serialize)]
203pub struct TuneRunRow {
204    pub id: i64,
205    pub loop_id: Option<i64>,
206    pub demo_session_id: Option<i64>,
207    pub loop_name: String,
208    pub driver: TuneDriver,
209    /// The OPC DA server ProgID this run actually used -- `None` for a non-opcda run, or for
210    /// any run started before [`TuneRunRow::record_connection`] is called (see that method's
211    /// doc comment). Flat and filterable rather than folded into `request_json`, since
212    /// `bhtune history revert` must know exactly which plant a past run touched.
213    pub opc_server: Option<String>,
214    /// The opcda-bridge gateway host this run actually used -- `None` for a non-opcda run,
215    /// or before [`TuneRunRow::record_connection`] is called. See `opc_server`.
216    pub bridge_host: Option<String>,
217    pub started_at: DateTime<Utc>,
218    pub completed_at: Option<DateTime<Utc>>,
219    pub outcome: TuneOutcome,
220    pub failure_reason: Option<String>,
221    /// Snapshot of the `LoopConfig` this run was started with — always known up front, since
222    /// it's user/schedule input rather than something read from the driver.
223    pub config: LoopConfig,
224    /// Where the snapshotted `template` below came from (see [`TemplateOrigin`]).
225    pub template_origin: TemplateOrigin,
226    /// Snapshot of the exact [`DcsTemplate`] this run was configured against, deserialized
227    /// from `template_snapshot_json`. Held as the full struct rather than just its `name` --
228    /// which is what makes a historical run stay interpretable once the template catalog
229    /// changes underneath it. There's no separate `template_name`
230    /// field here even though the table has a `template_name` column: `.name` on this field
231    /// already carries that value, and the column exists purely so it's filterable/indexable
232    /// without `json_extract` (see this module's own doc comment).
233    pub template: DcsTemplate,
234    /// Snapshot of the resolved [`LoopTags`] this run actually used, deserialized from
235    /// `tags_json`.
236    pub tags: LoopTags,
237    /// The complete run request exactly as submitted (CLI flags or the HTTP
238    /// `POST /api/runs` body), before any config-driven defaulting -- raw JSON rather than a
239    /// typed struct, since its shape is owned by the `bhtune`/`bhtune-server` adapters, not
240    /// `bhtune-db`. `"{}"` for any run started before
241    /// [`TuneRunRow::record_connection`] is called. It supports last-run prefill and
242    /// "duplicate this run"; never treat this as the source of truth for connection
243    /// facts -- that's `opc_server`/`bridge_host` above.
244    pub request_json: String,
245    /// Mutable operator notes for this run. `None` means no note is recorded.
246    pub notes: Option<String>,
247    pub initial_readings: Option<TuneRunInitialReadings>,
248    /// Whether this run permitted `Quality::Uncertain` OPC readings under the global
249    /// `allow_uncertain_quality` policy (`Quality::Bad` is never accepted regardless).
250    /// `false` for every run started before
251    /// [`TuneRunRow::record_allow_uncertain_quality`] is called -- see that method's doc
252    /// comment for why it's a separate post-`start()` update rather than a `start()`
253    /// parameter.
254    pub allow_uncertain_quality: bool,
255    /// Polling-cadence diagnostics collected from successful PV samples. `None` for runs
256    /// created before timing diagnostics existed or attempts that ended before polling began.
257    pub timing_metrics: Option<TimingMetrics>,
258    /// Concrete timing values used by this run after configuration defaults were resolved.
259    /// `None` for runs created before effective-tuning snapshots existed or until
260    /// [`TuneRunRow::record_effective_tuning`] is called.
261    pub effective_tuning: Option<EffectiveTuning>,
262    /// Observed opcda-bridge compatibility report, stored as the driver's JSON snapshot.
263    /// `None` for non-OPC runs, runs started before the snapshot existed, or until
264    /// [`TuneRunRow::record_gateway_compatibility`] is called. Kept as raw JSON because the
265    /// report type lives in `bhtune-driver`, which this crate does not depend on.
266    pub gateway_compatibility_json: Option<String>,
267    /// Outcome of the best-effort restore attempted after this run ended -- `None` if no
268    /// restore was ever attempted (the run never mutated the loop, or hasn't ended yet). See
269    /// [`RestoreStatus`] and [`TuneRunRow::record_restore_status`].
270    pub restore_status: Option<RestoreStatus>,
271    /// Set only alongside `restore_status = Some(RestoreStatus::Incomplete)`: what a second
272    /// Ctrl+C, `[tuning].restore_timeout_secs`, or an individual failed restore step
273    /// prevented from being confirmed.
274    pub restore_detail: Option<String>,
275    /// Explicit recovery lifecycle state; absent for pre-recovery runs and ordinary terminal
276    /// runs that never became eligible for restore-loop recovery.
277    pub recovery_state: Option<TuneRecoveryState>,
278    /// Structured evidence supporting the recovery state; never inferred from error text.
279    pub recovery_evidence_json: Option<String>,
280    pub created_at: DateTime<Utc>,
281}
282
283/// Filter criteria for [`TuneRunRow::list`]/[`TuneRunRow::count`]. Every field is optional;
284/// the all-`None` default matches every run. Build one with [`TuneRunFilter::default`] and
285/// the `with_*` methods, e.g. `TuneRunFilter::default().with_outcome(TuneOutcome::Failed)`.
286#[derive(Debug, Clone, Default, PartialEq)]
287pub struct TuneRunFilter {
288    pub loop_id: Option<i64>,
289    pub demo_session_id: Option<i64>,
290    pub process_type: Option<ProcessType>,
291    pub controller_type: Option<ControllerType>,
292    pub outcome: Option<TuneOutcome>,
293    pub driver: Option<TuneDriver>,
294    /// Exact match against the stored `opc_server` column. See
295    /// [`TuneRunRow::record_connection`].
296    pub opc_server: Option<String>,
297    /// Exact match against the stored `bridge_host` column. See
298    /// [`TuneRunRow::record_connection`].
299    pub bridge_host: Option<String>,
300    /// Matches runs with `started_at >= started_after` (inclusive).
301    pub started_after: Option<DateTime<Utc>>,
302    /// Matches runs with `started_at <= started_before` (inclusive).
303    pub started_before: Option<DateTime<Utc>>,
304    pub template_name: Option<String>,
305    pub template_origin: Option<TemplateOrigin>,
306}
307
308impl TuneRunFilter {
309    pub fn with_demo_session_id(mut self, demo_session_id: i64) -> TuneRunFilter {
310        self.demo_session_id = Some(demo_session_id);
311        self
312    }
313    pub fn with_loop_id(mut self, loop_id: i64) -> TuneRunFilter {
314        self.loop_id = Some(loop_id);
315        self
316    }
317
318    pub fn with_process_type(mut self, process_type: ProcessType) -> TuneRunFilter {
319        self.process_type = Some(process_type);
320        self
321    }
322
323    pub fn with_controller_type(mut self, controller_type: ControllerType) -> TuneRunFilter {
324        self.controller_type = Some(controller_type);
325        self
326    }
327
328    pub fn with_outcome(mut self, outcome: TuneOutcome) -> TuneRunFilter {
329        self.outcome = Some(outcome);
330        self
331    }
332
333    pub fn with_driver(mut self, driver: TuneDriver) -> TuneRunFilter {
334        self.driver = Some(driver);
335        self
336    }
337
338    pub fn with_opc_server(mut self, opc_server: impl Into<String>) -> TuneRunFilter {
339        self.opc_server = Some(opc_server.into());
340        self
341    }
342
343    pub fn with_bridge_host(mut self, bridge_host: impl Into<String>) -> TuneRunFilter {
344        self.bridge_host = Some(bridge_host.into());
345        self
346    }
347
348    pub fn with_started_after(mut self, started_after: DateTime<Utc>) -> TuneRunFilter {
349        self.started_after = Some(started_after);
350        self
351    }
352
353    pub fn with_started_before(mut self, started_before: DateTime<Utc>) -> TuneRunFilter {
354        self.started_before = Some(started_before);
355        self
356    }
357
358    pub fn with_template_name(mut self, template_name: impl Into<String>) -> TuneRunFilter {
359        self.template_name = Some(template_name.into());
360        self
361    }
362
363    pub fn with_template_origin(mut self, template_origin: TemplateOrigin) -> TuneRunFilter {
364        self.template_origin = Some(template_origin);
365        self
366    }
367}
368
369/// A page of [`TuneRunRow::list`] results: `limit` rows starting at `offset`, ordered newest
370/// first.
371#[derive(Debug, Clone, Copy, PartialEq)]
372pub struct Pagination {
373    pub limit: i64,
374    pub offset: i64,
375}
376
377impl Pagination {
378    pub fn new(limit: i64, offset: i64) -> Pagination {
379        Pagination { limit, offset }
380    }
381
382    /// The first `limit` rows.
383    pub fn first(limit: i64) -> Pagination {
384        Pagination { limit, offset: 0 }
385    }
386}
387
388impl Default for Pagination {
389    /// 50 rows, offset 0 — a reasonable default page size for a CLI/GUI run list.
390    fn default() -> Pagination {
391        Pagination {
392            limit: 50,
393            offset: 0,
394        }
395    }
396}
397
398impl TuneRunRow {
399    /// Starts a new run: inserts a `tune_runs` row with `outcome = 'running'` and no initial
400    /// readings yet (see [`Self::record_initial_readings`]). `now` is used for both
401    /// `started_at` and `created_at`, which are naturally the same instant for a run that's
402    /// only just begun. `loop_id` may be `None` for an ad-hoc run against tags that were
403    /// never saved as a reusable [`LoopRow`].
404    ///
405    /// `template_origin`/`template`/`tags` snapshot exactly what this run was configured
406    /// against, so a historical run stays interpretable even after the template catalog
407    /// changes underneath it. Serializing `template`/`tags` returns [`DbError::Serialize`]
408    /// if the JSON encoder rejects a value. Both are plain derived structures; a serialization
409    /// failure indicates a persistence contract regression rather than a plant condition.
410    #[allow(clippy::too_many_arguments)]
411    pub async fn start(
412        pool: &SqlitePool,
413        loop_id: Option<i64>,
414        loop_name: &str,
415        driver: TuneDriver,
416        config: LoopConfig,
417        template_origin: TemplateOrigin,
418        template: &DcsTemplate,
419        tags: &LoopTags,
420        now: DateTime<Utc>,
421    ) -> DbResult<TuneRunRow> {
422        Self::start_with_demo_session(
423            pool,
424            None,
425            loop_id,
426            loop_name,
427            driver,
428            config,
429            template_origin,
430            template,
431            tags,
432            now,
433        )
434        .await
435    }
436
437    #[allow(clippy::too_many_arguments)]
438    pub async fn start_with_demo_session(
439        pool: &SqlitePool,
440        demo_session_id: Option<i64>,
441        loop_id: Option<i64>,
442        loop_name: &str,
443        driver: TuneDriver,
444        config: LoopConfig,
445        template_origin: TemplateOrigin,
446        template: &DcsTemplate,
447        tags: &LoopTags,
448        now: DateTime<Utc>,
449    ) -> DbResult<TuneRunRow> {
450        let template_snapshot_json = json_text("template snapshot", template)?;
451        let tags_json = json_text("loop tags", tags)?;
452
453        let row = sqlx::query(
454            r"
455            INSERT INTO tune_runs (
456                loop_id, demo_session_id, loop_name, driver, started_at, outcome,
457                process_type, controller_type, relay_amp_percent, num_cycles_skip,
458                num_cycles_count, noise_protection_secs, mrft_delay_secs,
459                template_name, template_origin, template_snapshot_json, tags_json,
460                created_at
461            )
462            SELECT ?, ?, ?, ?, ?, 'running', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
463            WHERE ? IS NULL OR EXISTS (
464                SELECT 1
465                FROM demo_sessions
466                WHERE id = ? AND revoked_at IS NULL AND expires_at > ?
467            )
468            RETURNING *
469            ",
470        )
471        .bind(loop_id)
472        .bind(demo_session_id)
473        .bind(loop_name)
474        .bind(enum_to_text(&driver)?)
475        .bind(now)
476        .bind(enum_to_text(&config.process_type)?)
477        .bind(enum_to_text(&config.controller_type)?)
478        .bind(config.relay_amp_percent)
479        .bind(config.num_cycles_skip)
480        .bind(config.num_cycles_count)
481        .bind(config.noise_protection_secs)
482        .bind(config.mrft_delay_secs)
483        .bind(&template.name)
484        .bind(enum_to_text(&template_origin)?)
485        .bind(template_snapshot_json)
486        .bind(tags_json)
487        .bind(now)
488        .bind(demo_session_id)
489        .bind(demo_session_id)
490        .bind(now)
491        .fetch_optional(pool)
492        .await
493        .map_err(DbError::Query)?
494        .ok_or_else(|| DbError::Query(sqlx::Error::RowNotFound))?;
495
496        row_to_tune_run(row)
497    }
498
499    /// Starts a simulator run owned by a demo session. The database trigger rejects any
500    /// attempt to attach an owner to a live OPC DA run.
501    #[allow(clippy::too_many_arguments)]
502    pub async fn start_owned(
503        pool: &SqlitePool,
504        demo_session_id: i64,
505        loop_name: &str,
506        config: LoopConfig,
507        template_origin: TemplateOrigin,
508        template: &DcsTemplate,
509        tags: &LoopTags,
510        now: DateTime<Utc>,
511    ) -> DbResult<TuneRunRow> {
512        Self::start_with_demo_session(
513            pool,
514            Some(demo_session_id),
515            None,
516            loop_name,
517            TuneDriver::Simulator,
518            config,
519            template_origin,
520            template,
521            tags,
522            now,
523        )
524        .await
525    }
526
527    pub async fn get_for_demo_session(
528        pool: &SqlitePool,
529        run_id: i64,
530        demo_session_id: i64,
531    ) -> DbResult<Option<TuneRunRow>> {
532        let row = sqlx::query("SELECT * FROM tune_runs WHERE id = ? AND demo_session_id = ?")
533            .bind(run_id)
534            .bind(demo_session_id)
535            .fetch_optional(pool)
536            .await
537            .map_err(DbError::Query)?;
538        row.map(row_to_tune_run).transpose()
539    }
540
541    pub async fn list_for_demo_session(
542        pool: &SqlitePool,
543        demo_session_id: i64,
544        pagination: Pagination,
545    ) -> DbResult<Vec<TuneRunRow>> {
546        let rows = sqlx::query(
547            "SELECT * FROM tune_runs WHERE demo_session_id = ? \
548             ORDER BY started_at DESC, id DESC LIMIT ? OFFSET ?",
549        )
550        .bind(demo_session_id)
551        .bind(pagination.limit)
552        .bind(pagination.offset)
553        .fetch_all(pool)
554        .await
555        .map_err(DbError::Query)?;
556        rows.into_iter().map(row_to_tune_run).collect()
557    }
558
559    pub async fn newest_for_demo_session(
560        pool: &SqlitePool,
561        demo_session_id: i64,
562    ) -> DbResult<Option<TuneRunRow>> {
563        let row = sqlx::query(
564            "SELECT * FROM tune_runs WHERE demo_session_id = ? \
565             ORDER BY started_at DESC, id DESC LIMIT 1",
566        )
567        .bind(demo_session_id)
568        .fetch_optional(pool)
569        .await
570        .map_err(DbError::Query)?;
571        row.map(row_to_tune_run).transpose()
572    }
573
574    pub async fn count_for_demo_session(pool: &SqlitePool, demo_session_id: i64) -> DbResult<i64> {
575        sqlx::query_scalar("SELECT COUNT(*) FROM tune_runs WHERE demo_session_id = ?")
576            .bind(demo_session_id)
577            .fetch_one(pool)
578            .await
579            .map_err(DbError::Query)
580    }
581
582    /// Counts every current Demo-owned run across all sessions and outcomes. Full-mode rows have
583    /// `demo_session_id = NULL` and are excluded.
584    pub async fn count_demo_owned(pool: &SqlitePool) -> DbResult<i64> {
585        sqlx::query_scalar("SELECT COUNT(*) FROM tune_runs WHERE demo_session_id IS NOT NULL")
586            .fetch_one(pool)
587            .await
588            .map_err(DbError::Query)
589    }
590
591    /// Deletes terminal history beyond the newest `retain` rows for one Demo owner. Running
592    /// rows never consume a retention slot and are never deleted. Child samples, results,
593    /// writes, and MV-actuation evidence cascade with each deleted run.
594    pub async fn prune_terminal_for_demo_session(
595        pool: &SqlitePool,
596        demo_session_id: i64,
597        retain: u32,
598    ) -> DbResult<u64> {
599        let result = sqlx::query(
600            "DELETE FROM tune_runs \
601             WHERE id IN ( \
602                 SELECT id FROM tune_runs \
603                 WHERE demo_session_id = ? AND outcome <> 'running' \
604                 ORDER BY started_at DESC, id DESC \
605                 LIMIT -1 OFFSET ? \
606             )",
607        )
608        .bind(demo_session_id)
609        .bind(i64::from(retain))
610        .execute(pool)
611        .await
612        .map_err(DbError::Query)?;
613        Ok(result.rows_affected())
614    }
615
616    /// Applies [`Self::prune_terminal_for_demo_session`]'s retention rule independently to
617    /// every Demo owner in one statement. Intended for periodic global cleanup.
618    pub async fn prune_terminal_demo_owned(pool: &SqlitePool, retain: u32) -> DbResult<u64> {
619        let result = sqlx::query(
620            "WITH ranked AS ( \
621                 SELECT id, ROW_NUMBER() OVER ( \
622                     PARTITION BY demo_session_id ORDER BY started_at DESC, id DESC \
623                 ) AS retention_rank \
624                 FROM tune_runs \
625                 WHERE demo_session_id IS NOT NULL AND outcome <> 'running' \
626             ) \
627             DELETE FROM tune_runs \
628             WHERE id IN (SELECT id FROM ranked WHERE retention_rank > ?)",
629        )
630        .bind(i64::from(retain))
631        .execute(pool)
632        .await
633        .map_err(DbError::Query)?;
634        Ok(result.rows_affected())
635    }
636
637    pub async fn count_rows_for_demo_session(
638        pool: &SqlitePool,
639        demo_session_id: i64,
640    ) -> DbResult<i64> {
641        sqlx::query_scalar(
642            "SELECT
643                (SELECT COUNT(*) FROM tune_runs WHERE demo_session_id = ?)
644              + (SELECT COUNT(*) FROM tune_samples WHERE run_id IN
645                    (SELECT id FROM tune_runs WHERE demo_session_id = ?))
646              + (SELECT COUNT(*) FROM tune_results WHERE run_id IN
647                    (SELECT id FROM tune_runs WHERE demo_session_id = ?))
648              + (SELECT COUNT(*) FROM tune_writes WHERE run_id IN
649                    (SELECT id FROM tune_runs WHERE demo_session_id = ?))
650              + (SELECT COUNT(*) FROM tune_mv_actuations WHERE run_id IN
651                    (SELECT id FROM tune_runs WHERE demo_session_id = ?))",
652        )
653        .bind(demo_session_id)
654        .bind(demo_session_id)
655        .bind(demo_session_id)
656        .bind(demo_session_id)
657        .bind(demo_session_id)
658        .fetch_one(pool)
659        .await
660        .map_err(DbError::Query)
661    }
662
663    /// Records this run's connection provenance and the exact request it was started with:
664    /// the OPC DA server ProgID and opcda-bridge gateway host
665    /// actually used (`None`/`None` for a non-opcda run), and a JSON snapshot of the complete
666    /// submitted request (CLI flags or the HTTP `POST /api/runs` body), captured *before* any
667    /// config-driven defaulting so it reflects what the caller actually asked for.
668    ///
669    /// Recorded after `start()` so callers that do not have connection provenance can still
670    /// create a run without extra arguments. The production caller stores this immediately
671    /// after `start()` succeeds, before any driver I/O. `opc_server`/`bridge_host` default to
672    /// `NULL` and `request_json` defaults to `"{}"` (see the migration).
673    pub async fn record_connection(
674        pool: &SqlitePool,
675        run_id: i64,
676        opc_server: Option<&str>,
677        bridge_host: Option<&str>,
678        request_json: &str,
679    ) -> DbResult<TuneRunRow> {
680        let row = sqlx::query(
681            r"
682            UPDATE tune_runs SET opc_server = ?, bridge_host = ?, request_json = ?
683            WHERE id = ?
684            RETURNING *
685            ",
686        )
687        .bind(opc_server)
688        .bind(bridge_host)
689        .bind(request_json)
690        .bind(run_id)
691        .fetch_one(pool)
692        .await
693        .map_err(DbError::Query)?;
694
695        row_to_tune_run(row)
696    }
697
698    /// Records the concrete tune timing policy after all configuration defaults have been
699    /// resolved. This is a follow-up update rather than another [`Self::start`] parameter so
700    /// existing repository callers remain source-compatible. Production orchestration should
701    /// call it immediately after `start()` and before any driver I/O.
702    pub async fn record_effective_tuning(
703        pool: &SqlitePool,
704        run_id: i64,
705        effective_tuning: EffectiveTuning,
706    ) -> DbResult<TuneRunRow> {
707        let effective_tuning_json = json_text("effective tuning", &effective_tuning)?;
708        let row = sqlx::query(
709            r"
710            UPDATE tune_runs SET effective_tuning_json = ?
711            WHERE id = ?
712            RETURNING *
713            ",
714        )
715        .bind(effective_tuning_json)
716        .bind(run_id)
717        .fetch_one(pool)
718        .await
719        .map_err(DbError::Query)?;
720
721        row_to_tune_run(row)
722    }
723
724    /// Records the observed opcda-bridge compatibility snapshot for a live OPC DA run.
725    ///
726    /// `gateway_compatibility_json` must already be valid JSON. This is a follow-up update
727    /// rather than another [`Self::start`] parameter so existing repository callers remain
728    /// source-compatible.
729    ///
730    /// # Errors
731    ///
732    /// Returns [`DbError::Query`] when the row does not exist or the JSON fails the column
733    /// check.
734    pub async fn record_gateway_compatibility(
735        pool: &SqlitePool,
736        run_id: i64,
737        gateway_compatibility_json: &str,
738    ) -> DbResult<TuneRunRow> {
739        let row = sqlx::query(
740            r"
741            UPDATE tune_runs SET gateway_compatibility_json = ?
742            WHERE id = ?
743            RETURNING *
744            ",
745        )
746        .bind(gateway_compatibility_json)
747        .bind(run_id)
748        .fetch_one(pool)
749        .await
750        .map_err(DbError::Query)?;
751
752        row_to_tune_run(row)
753    }
754
755    /// Replaces this run's operator notes. Passing `None` clears the note, which is the
756    /// persistence-layer implementation of the GUI's delete-note action. This deliberately
757    /// has no lifecycle restriction: notes remain editable while a run is active and after it
758    /// reaches a terminal outcome.
759    pub async fn update_notes(
760        pool: &SqlitePool,
761        run_id: i64,
762        notes: Option<&str>,
763    ) -> DbResult<TuneRunRow> {
764        let row = sqlx::query(
765            r"
766            UPDATE tune_runs SET notes = ?
767            WHERE id = ?
768            RETURNING *
769            ",
770        )
771        .bind(notes)
772        .bind(run_id)
773        .fetch_one(pool)
774        .await
775        .map_err(DbError::Query)?;
776
777        row_to_tune_run(row)
778    }
779
780    /// Records the driver's initial-readings snapshot for an already-started run. Called at
781    /// most once per run, right after that read succeeds and before `transition_to_manual`'s
782    /// first mutating write. This durably preserves `mode_raw`/`mode_attribute_raw`/
783    /// `setpoint_ini` so a crashed run can be reconstructed and restored later via
784    /// `bhtune restore-loop`. A run that fails before or during the read instead goes
785    /// straight to [`Self::fail`] with `initial_readings` left `None`.
786    pub async fn record_initial_readings(
787        pool: &SqlitePool,
788        run_id: i64,
789        readings: TuneRunInitialReadings,
790    ) -> DbResult<TuneRunRow> {
791        let row = sqlx::query(
792            r"
793            UPDATE tune_runs SET
794                pv_ini = ?, mv_ini = ?, mv_range_low = ?, mv_range_high = ?,
795                pv_range_high = ?, pv_range_low = ?, controller_direction = ?,
796                mode_raw = ?, mode_attribute_raw = ?, setpoint_ini = ?
797            WHERE id = ?
798            RETURNING *
799            ",
800        )
801        .bind(readings.pv_ini)
802        .bind(readings.mv_ini)
803        .bind(readings.mv_range_low)
804        .bind(readings.mv_range_high)
805        .bind(readings.pv_range_high)
806        .bind(readings.pv_range_low)
807        .bind(enum_to_text(&readings.controller_direction)?)
808        .bind(readings.mode_raw)
809        .bind(readings.mode_attribute_raw)
810        .bind(readings.setpoint_ini)
811        .bind(run_id)
812        .fetch_one(pool)
813        .await
814        .map_err(DbError::Query)?;
815
816        row_to_tune_run(row)
817    }
818
819    /// Records whether this run permitted `Quality::Uncertain` OPC readings under the global
820    /// configuration policy. This is a separate post-`start()` update because the resolved
821    /// policy is available to the orchestration layer, not to every repository caller. The
822    /// column defaults to `0`/`false` (see the migration), and the production caller stores
823    /// the resolved value after `start()` succeeds.
824    pub async fn record_allow_uncertain_quality(
825        pool: &SqlitePool,
826        run_id: i64,
827        allow_uncertain_quality: bool,
828    ) -> DbResult<TuneRunRow> {
829        let row = sqlx::query(
830            r"
831            UPDATE tune_runs SET allow_uncertain_quality = ?
832            WHERE id = ?
833            RETURNING *
834            ",
835        )
836        .bind(allow_uncertain_quality)
837        .bind(run_id)
838        .fetch_one(pool)
839        .await
840        .map_err(DbError::Query)?;
841
842        row_to_tune_run(row)
843    }
844
845    /// Records the polling cadence observed while this run was active. Kept as one typed JSON
846    /// snapshot because these diagnostics are nested, evolve together, and have no SQL-level
847    /// filtering requirement. Normal completed/aborted tune orchestration uses
848    /// [`Self::complete_with_timing_metrics`] or [`Self::abort_with_timing_metrics`] so the
849    /// terminal outcome and diagnostics become visible atomically; this standalone update is
850    /// retained for non-terminal/failure paths and direct repository consumers.
851    pub async fn record_timing_metrics(
852        pool: &SqlitePool,
853        run_id: i64,
854        metrics: TimingMetrics,
855    ) -> DbResult<TuneRunRow> {
856        let metrics_json = json_text("timing metrics", &metrics)?;
857        let row = sqlx::query(
858            r"
859            UPDATE tune_runs SET timing_metrics_json = ?
860            WHERE id = ?
861            RETURNING *
862            ",
863        )
864        .bind(metrics_json)
865        .bind(run_id)
866        .fetch_one(pool)
867        .await
868        .map_err(DbError::Query)?;
869
870        row_to_tune_run(row)
871    }
872
873    /// Records the outcome of a best-effort loop-restore attempt made after this run ended.
874    /// Called once,
875    /// after `complete`/`fail`/`abort` (whichever applies) and after `attempt_restore` has
876    /// actually run -- never before, and never for a run that ended without ever mutating
877    /// the loop (nothing to restore, so nothing to record). `detail` should be `Some(..)`
878    /// whenever `status` is [`RestoreStatus::Incomplete`], naming what could not be
879    /// confirmed; pass `None` for [`RestoreStatus::Confirmed`]. A separate post-hoc update
880    /// rather than a `complete`/`fail`/`abort` parameter, matching
881    /// [`Self::record_allow_uncertain_quality`]'s precedent: the restore attempt always
882    /// happens strictly after one of those three, never alongside it.
883    pub async fn record_restore_status(
884        pool: &SqlitePool,
885        run_id: i64,
886        status: RestoreStatus,
887        detail: Option<&str>,
888    ) -> DbResult<TuneRunRow> {
889        let row = sqlx::query(
890            r"
891            UPDATE tune_runs SET restore_status = ?, restore_detail = ?
892            WHERE id = ?
893            RETURNING *
894            ",
895        )
896        .bind(enum_to_text(&status)?)
897        .bind(detail)
898        .bind(run_id)
899        .fetch_one(pool)
900        .await
901        .map_err(DbError::Query)?;
902
903        row_to_tune_run(row)
904    }
905
906    /// Marks a run `completed` — a full MRFT test that ran to its natural end. The calculated
907    /// results themselves are recorded separately via [`TuneResultRow::insert`].
908    pub async fn complete(
909        pool: &SqlitePool,
910        run_id: i64,
911        completed_at: DateTime<Utc>,
912    ) -> DbResult<TuneRunRow> {
913        let row = sqlx::query(
914            "UPDATE tune_runs SET outcome = 'completed', completed_at = ? WHERE id = ? RETURNING *",
915        )
916        .bind(completed_at)
917        .bind(run_id)
918        .fetch_one(pool)
919        .await
920        .map_err(DbError::Query)?;
921
922        row_to_tune_run(row)
923    }
924
925    /// Atomically marks a run completed and publishes its timing diagnostics. This prevents
926    /// readers that react to the terminal outcome (notably the SSE stream) from observing a
927    /// completed run before its timing snapshot is visible.
928    pub async fn complete_with_timing_metrics(
929        pool: &SqlitePool,
930        run_id: i64,
931        completed_at: DateTime<Utc>,
932        timing_metrics: Option<TimingMetrics>,
933    ) -> DbResult<TuneRunRow> {
934        Self::set_terminal_outcome_with_timing_metrics(
935            pool,
936            run_id,
937            completed_at,
938            TuneOutcome::Completed,
939            timing_metrics,
940            None,
941        )
942        .await
943    }
944
945    /// Marks a run `failed`, recording why. Valid whether or not
946    /// [`Self::record_initial_readings`] was ever called for this run — a run can fail before,
947    /// during, or after the initial read.
948    pub async fn fail(
949        pool: &SqlitePool,
950        run_id: i64,
951        completed_at: DateTime<Utc>,
952        failure_reason: &str,
953    ) -> DbResult<TuneRunRow> {
954        let row = sqlx::query(
955            r"
956            UPDATE tune_runs SET outcome = 'failed', completed_at = ?, failure_reason = ?
957            WHERE id = ?
958            RETURNING *
959            ",
960        )
961        .bind(completed_at)
962        .bind(failure_reason)
963        .bind(run_id)
964        .fetch_one(pool)
965        .await
966        .map_err(DbError::Query)?;
967
968        row_to_tune_run(row)
969    }
970
971    /// Marks a run `aborted` — stopped deliberately by a human or by the configured run
972    /// timeout rather than failing on its own.
973    pub async fn abort(
974        pool: &SqlitePool,
975        run_id: i64,
976        completed_at: DateTime<Utc>,
977    ) -> DbResult<TuneRunRow> {
978        let row = sqlx::query(
979            "UPDATE tune_runs SET outcome = 'aborted', completed_at = ? WHERE id = ? RETURNING *",
980        )
981        .bind(completed_at)
982        .bind(run_id)
983        .fetch_one(pool)
984        .await
985        .map_err(DbError::Query)?;
986
987        row_to_tune_run(row)
988    }
989
990    /// Atomically marks a run aborted and publishes any timing diagnostics collected before
991    /// the abort, so terminal-state readers cannot miss the final timing snapshot.
992    pub async fn abort_with_timing_metrics(
993        pool: &SqlitePool,
994        run_id: i64,
995        completed_at: DateTime<Utc>,
996        timing_metrics: Option<TimingMetrics>,
997    ) -> DbResult<TuneRunRow> {
998        Self::set_terminal_outcome_with_timing_metrics(
999            pool,
1000            run_id,
1001            completed_at,
1002            TuneOutcome::Aborted,
1003            timing_metrics,
1004            None,
1005        )
1006        .await
1007    }
1008
1009    /// Atomically marks a run aborted, publishes its timing diagnostics, and persists the
1010    /// operator-facing reason for the abort in the existing `failure_reason` column. This is
1011    /// used when an abort has a durable safety explanation (for example an MV command whose
1012    /// live readback did not reach its target), while preserving the database's existing
1013    /// [`TuneOutcome::Aborted`] value.
1014    pub async fn abort_with_timing_metrics_and_reason(
1015        pool: &SqlitePool,
1016        run_id: i64,
1017        completed_at: DateTime<Utc>,
1018        timing_metrics: Option<TimingMetrics>,
1019        reason: &str,
1020    ) -> DbResult<TuneRunRow> {
1021        Self::set_terminal_outcome_with_timing_metrics(
1022            pool,
1023            run_id,
1024            completed_at,
1025            TuneOutcome::Aborted,
1026            timing_metrics,
1027            Some(reason),
1028        )
1029        .await
1030    }
1031
1032    async fn set_terminal_outcome_with_timing_metrics(
1033        pool: &SqlitePool,
1034        run_id: i64,
1035        completed_at: DateTime<Utc>,
1036        outcome: TuneOutcome,
1037        timing_metrics: Option<TimingMetrics>,
1038        failure_reason: Option<&str>,
1039    ) -> DbResult<TuneRunRow> {
1040        let timing_metrics_json = timing_metrics
1041            .map(|metrics| json_text("timing metrics", &metrics))
1042            .transpose()?;
1043        let row = sqlx::query(
1044            r"
1045            UPDATE tune_runs
1046            SET outcome = ?, completed_at = ?, timing_metrics_json = ?, failure_reason = ?
1047            WHERE id = ?
1048            RETURNING *
1049            ",
1050        )
1051        .bind(enum_to_text(&outcome)?)
1052        .bind(completed_at)
1053        .bind(timing_metrics_json)
1054        .bind(failure_reason)
1055        .bind(run_id)
1056        .fetch_one(pool)
1057        .await
1058        .map_err(DbError::Query)?;
1059
1060        row_to_tune_run(row)
1061    }
1062
1063    /// Fetches one row by id, or `None` if it doesn't exist.
1064    pub async fn get(pool: &SqlitePool, id: i64) -> DbResult<Option<TuneRunRow>> {
1065        let row = sqlx::query("SELECT * FROM tune_runs WHERE id = ?")
1066            .bind(id)
1067            .fetch_optional(pool)
1068            .await
1069            .map_err(DbError::Query)?;
1070        row.map(row_to_tune_run).transpose()
1071    }
1072
1073    /// Lists runs matching `filter`, newest-started first, one `pagination` page at a time.
1074    /// See [`Self::count`] for the total number of rows `filter` matches across all pages.
1075    pub async fn list(
1076        pool: &SqlitePool,
1077        filter: &TuneRunFilter,
1078        pagination: Pagination,
1079    ) -> DbResult<Vec<TuneRunRow>> {
1080        let mut builder: QueryBuilder<Sqlite> = QueryBuilder::new("SELECT * FROM tune_runs");
1081        push_filter(&mut builder, filter)?;
1082        builder.push(" ORDER BY started_at DESC LIMIT ");
1083        builder.push_bind(pagination.limit);
1084        builder.push(" OFFSET ");
1085        builder.push_bind(pagination.offset);
1086
1087        let rows = builder
1088            .build()
1089            .fetch_all(pool)
1090            .await
1091            .map_err(DbError::Query)?;
1092        rows.into_iter().map(row_to_tune_run).collect()
1093    }
1094
1095    /// Counts every run matching `filter`, ignoring pagination — the total [`Self::list`]
1096    /// would page through.
1097    pub async fn count(pool: &SqlitePool, filter: &TuneRunFilter) -> DbResult<i64> {
1098        let mut builder: QueryBuilder<Sqlite> = QueryBuilder::new("SELECT COUNT(*) FROM tune_runs");
1099        push_filter(&mut builder, filter)?;
1100        builder
1101            .build_query_scalar::<i64>()
1102            .fetch_one(pool)
1103            .await
1104            .map_err(DbError::Query)
1105    }
1106
1107    /// Counts runs matching a deletion filter while preserving runs with an unresolved live
1108    /// recovery. This is the count used by retention previews so they match the actual sweep.
1109    pub async fn count_deletable_matching(
1110        pool: &SqlitePool,
1111        filter: &TuneRunFilter,
1112    ) -> DbResult<i64> {
1113        let mut builder: QueryBuilder<Sqlite> = QueryBuilder::new("SELECT COUNT(*) FROM tune_runs");
1114        push_filter(&mut builder, filter)?;
1115        push_deletable_recovery_guard(&mut builder);
1116        builder
1117            .build_query_scalar::<i64>()
1118            .fetch_one(pool)
1119            .await
1120            .map_err(DbError::Query)
1121    }
1122
1123    /// Deletes every run matching `filter` in one statement (SQLite treats a single
1124    /// statement as its own transaction, so no explicit `BEGIN`/`COMMIT` is needed). Returns
1125    /// the number of runs deleted. `tune_samples`/`tune_results`/`tune_writes`'s `ON DELETE
1126    /// CASCADE` foreign keys in the schema remove each deleted run's samples,
1127    /// results, and write-back audit rows automatically.
1128    ///
1129    /// Runs with recovery state `eligible`, `running`, or `incomplete` are retained until a
1130    /// recovery is confirmed. `count_deletable_matching` applies the same protection for
1131    /// retention previews.
1132    ///
1133    /// An empty `filter` (every field `None`) matches every deletable run in the table.
1134    /// Callers that mean to scope a deletion must build a `filter` that says so explicitly;
1135    /// unresolved live-recovery rows remain protected even when the filter is empty.
1136    pub async fn delete_matching(pool: &SqlitePool, filter: &TuneRunFilter) -> DbResult<u64> {
1137        let mut builder: QueryBuilder<Sqlite> = QueryBuilder::new("DELETE FROM tune_runs");
1138        push_filter(&mut builder, filter)?;
1139        push_deletable_recovery_guard(&mut builder);
1140        let result = builder
1141            .build()
1142            .execute(pool)
1143            .await
1144            .map_err(DbError::Query)?;
1145        Ok(result.rows_affected())
1146    }
1147
1148    /// Deletes exactly one run by id, as used by the browser history page's delete action.
1149    /// Returns whether a row was actually deleted -- `false` if no run has that id, letting the
1150    /// caller map that to a 404 rather than a silent no-op. Unlike
1151    /// [`DcsTemplateRow::delete`], ordinary run children are removed by `ON DELETE CASCADE`.
1152    /// An unresolved live recovery state is excluded atomically so a concurrent delete cannot
1153    /// release the recovery claim.
1154    pub async fn delete(pool: &SqlitePool, id: i64) -> DbResult<bool> {
1155        let result = sqlx::query(
1156            "DELETE FROM tune_runs WHERE id = ? AND (recovery_state IS NULL OR recovery_state NOT IN ('eligible', 'running', 'incomplete'))",
1157        )
1158            .bind(id)
1159            .execute(pool)
1160            .await
1161            .map_err(DbError::Query)?;
1162        Ok(result.rows_affected() > 0)
1163    }
1164
1165    pub async fn delete_for_demo_session(
1166        pool: &SqlitePool,
1167        id: i64,
1168        demo_session_id: i64,
1169    ) -> DbResult<bool> {
1170        let result = sqlx::query("DELETE FROM tune_runs WHERE id = ? AND demo_session_id = ?")
1171            .bind(id)
1172            .bind(demo_session_id)
1173            .execute(pool)
1174            .await
1175            .map_err(DbError::Query)?;
1176        Ok(result.rows_affected() > 0)
1177    }
1178}
1179
1180fn push_deletable_recovery_guard(builder: &mut QueryBuilder<Sqlite>) {
1181    builder.push(
1182        " AND (recovery_state IS NULL OR recovery_state NOT IN ('eligible', 'running', 'incomplete'))",
1183    );
1184}
1185
1186/// Appends `WHERE <conditions>` to `builder` for every `Some` field in `filter`, or nothing
1187/// at all if every field is `None`. Shared by [`TuneRunRow::list`]/[`TuneRunRow::count`] so
1188/// the two can never disagree about which rows match a given filter. Returns
1189/// [`DbError::Serialize`] if an enum filter value cannot be encoded.
1190fn push_filter(builder: &mut QueryBuilder<Sqlite>, filter: &TuneRunFilter) -> DbResult<()> {
1191    // `1=1` makes every real condition an unconditional `AND`, rather than needing to track
1192    // whether it's the first one (and therefore needs `WHERE` instead of `AND`).
1193    builder.push(" WHERE 1=1");
1194
1195    if let Some(loop_id) = filter.loop_id {
1196        builder.push(" AND loop_id = ").push_bind(loop_id);
1197    }
1198    if let Some(demo_session_id) = filter.demo_session_id {
1199        builder
1200            .push(" AND demo_session_id = ")
1201            .push_bind(demo_session_id);
1202    }
1203    if let Some(process_type) = filter.process_type {
1204        builder
1205            .push(" AND process_type = ")
1206            .push_bind(enum_to_text(&process_type)?);
1207    }
1208    if let Some(controller_type) = filter.controller_type {
1209        builder
1210            .push(" AND controller_type = ")
1211            .push_bind(enum_to_text(&controller_type)?);
1212    }
1213    if let Some(outcome) = filter.outcome {
1214        builder
1215            .push(" AND outcome = ")
1216            .push_bind(enum_to_text(&outcome)?);
1217    }
1218    if let Some(driver) = filter.driver {
1219        builder
1220            .push(" AND driver = ")
1221            .push_bind(enum_to_text(&driver)?);
1222    }
1223    if let Some(opc_server) = &filter.opc_server {
1224        builder
1225            .push(" AND opc_server = ")
1226            .push_bind(opc_server.clone());
1227    }
1228    if let Some(bridge_host) = &filter.bridge_host {
1229        builder
1230            .push(" AND bridge_host = ")
1231            .push_bind(bridge_host.clone());
1232    }
1233    if let Some(started_after) = filter.started_after {
1234        builder.push(" AND started_at >= ").push_bind(started_after);
1235    }
1236    if let Some(started_before) = filter.started_before {
1237        builder
1238            .push(" AND started_at <= ")
1239            .push_bind(started_before);
1240    }
1241    if let Some(template_name) = &filter.template_name {
1242        builder
1243            .push(" AND template_name = ")
1244            .push_bind(template_name.clone());
1245    }
1246    if let Some(template_origin) = filter.template_origin {
1247        builder
1248            .push(" AND template_origin = ")
1249            .push_bind(enum_to_text(&template_origin)?);
1250    }
1251    Ok(())
1252}
1253
1254fn row_to_tune_run(row: SqliteRow) -> DbResult<TuneRunRow> {
1255    let pv_ini: Option<f32> = row.try_get("pv_ini").map_err(DbError::Query)?;
1256    let initial_readings = match pv_ini {
1257        Some(pv_ini) => {
1258            let controller_direction: String = row
1259                .try_get("controller_direction")
1260                .map_err(DbError::Query)?;
1261            Some(TuneRunInitialReadings {
1262                pv_ini,
1263                mv_ini: row.try_get("mv_ini").map_err(DbError::Query)?,
1264                mv_range_low: row.try_get("mv_range_low").map_err(DbError::Query)?,
1265                mv_range_high: row.try_get("mv_range_high").map_err(DbError::Query)?,
1266                pv_range_high: row.try_get("pv_range_high").map_err(DbError::Query)?,
1267                pv_range_low: row.try_get("pv_range_low").map_err(DbError::Query)?,
1268                controller_direction: text_to_enum("controller_direction", &controller_direction)?,
1269                mode_raw: row.try_get("mode_raw").map_err(DbError::Query)?,
1270                mode_attribute_raw: row.try_get("mode_attribute_raw").map_err(DbError::Query)?,
1271                setpoint_ini: row.try_get("setpoint_ini").map_err(DbError::Query)?,
1272            })
1273        }
1274        None => None,
1275    };
1276
1277    let process_type: String = row.try_get("process_type").map_err(DbError::Query)?;
1278    let controller_type: String = row.try_get("controller_type").map_err(DbError::Query)?;
1279    let config = LoopConfig {
1280        process_type: text_to_enum("process_type", &process_type)?,
1281        controller_type: text_to_enum("controller_type", &controller_type)?,
1282        relay_amp_percent: row.try_get("relay_amp_percent").map_err(DbError::Query)?,
1283        num_cycles_skip: row.try_get("num_cycles_skip").map_err(DbError::Query)?,
1284        num_cycles_count: row.try_get("num_cycles_count").map_err(DbError::Query)?,
1285        noise_protection_secs: row
1286            .try_get("noise_protection_secs")
1287            .map_err(DbError::Query)?,
1288        mrft_delay_secs: row.try_get("mrft_delay_secs").map_err(DbError::Query)?,
1289    };
1290
1291    let driver: String = row.try_get("driver").map_err(DbError::Query)?;
1292    let outcome: String = row.try_get("outcome").map_err(DbError::Query)?;
1293
1294    let restore_status_text: Option<String> =
1295        row.try_get("restore_status").map_err(DbError::Query)?;
1296    let restore_status = restore_status_text
1297        .map(|text| text_to_enum("restore_status", &text))
1298        .transpose()?;
1299    let recovery_state_text: Option<String> =
1300        row.try_get("recovery_state").map_err(DbError::Query)?;
1301    let recovery_state = recovery_state_text
1302        .as_deref()
1303        .map(|text| text_to_enum("recovery_state", text))
1304        .transpose()?;
1305
1306    let template_origin: String = row.try_get("template_origin").map_err(DbError::Query)?;
1307    let template_snapshot_json: String = row
1308        .try_get("template_snapshot_json")
1309        .map_err(DbError::Query)?;
1310    let tags_json: String = row.try_get("tags_json").map_err(DbError::Query)?;
1311    let request_json: String = row.try_get("request_json").map_err(DbError::Query)?;
1312    let timing_metrics_json: Option<String> =
1313        row.try_get("timing_metrics_json").map_err(DbError::Query)?;
1314    let effective_tuning_json: Option<String> = row
1315        .try_get("effective_tuning_json")
1316        .map_err(DbError::Query)?;
1317    let gateway_compatibility_json: Option<String> = row
1318        .try_get("gateway_compatibility_json")
1319        .map_err(DbError::Query)?;
1320    let template: DcsTemplate =
1321        serde_json::from_str(&template_snapshot_json).map_err(|source| {
1322            DbError::InvalidJsonShape {
1323                column: "template_snapshot_json",
1324                source,
1325            }
1326        })?;
1327    let tags: LoopTags =
1328        serde_json::from_str(&tags_json).map_err(|source| DbError::InvalidJsonShape {
1329            column: "tags_json",
1330            source,
1331        })?;
1332    let timing_metrics = timing_metrics_json
1333        .map(|json| {
1334            serde_json::from_str(&json).map_err(|source| DbError::InvalidJsonShape {
1335                column: "timing_metrics_json",
1336                source,
1337            })
1338        })
1339        .transpose()?;
1340    let effective_tuning = effective_tuning_json
1341        .map(|json| {
1342            serde_json::from_str(&json).map_err(|source| DbError::InvalidJsonShape {
1343                column: "effective_tuning_json",
1344                source,
1345            })
1346        })
1347        .transpose()?;
1348
1349    Ok(TuneRunRow {
1350        id: row.try_get("id").map_err(DbError::Query)?,
1351        loop_id: row.try_get("loop_id").map_err(DbError::Query)?,
1352        demo_session_id: row.try_get("demo_session_id").map_err(DbError::Query)?,
1353        loop_name: row.try_get("loop_name").map_err(DbError::Query)?,
1354        driver: text_to_enum("driver", &driver)?,
1355        opc_server: row.try_get("opc_server").map_err(DbError::Query)?,
1356        bridge_host: row.try_get("bridge_host").map_err(DbError::Query)?,
1357        started_at: row.try_get("started_at").map_err(DbError::Query)?,
1358        completed_at: row.try_get("completed_at").map_err(DbError::Query)?,
1359        outcome: text_to_enum("outcome", &outcome)?,
1360        failure_reason: row.try_get("failure_reason").map_err(DbError::Query)?,
1361        config,
1362        template_origin: text_to_enum("template_origin", &template_origin)?,
1363        template,
1364        tags,
1365        request_json,
1366        notes: row.try_get("notes").map_err(DbError::Query)?,
1367        initial_readings,
1368        allow_uncertain_quality: row
1369            .try_get("allow_uncertain_quality")
1370            .map_err(DbError::Query)?,
1371        timing_metrics,
1372        effective_tuning,
1373        gateway_compatibility_json,
1374        restore_status,
1375        restore_detail: row.try_get("restore_detail").map_err(DbError::Query)?,
1376        recovery_state,
1377        recovery_evidence_json: row
1378            .try_get("recovery_evidence_json")
1379            .map_err(DbError::Query)?,
1380        created_at: row.try_get("created_at").map_err(DbError::Query)?,
1381    })
1382}
1383// }}}1
1384
1385#[cfg(test)]
1386mod tests {
1387    use super::*;
1388    use crate::convert::{enum_to_text, text_to_enum};
1389
1390    async fn sample_run() -> (crate::SqlitePool, i64) {
1391        let pool = crate::connect_in_memory().await.unwrap();
1392        let template = bhtune_core::built_in_templates().remove(0);
1393        let tags = bhtune_core::LoopTags::derive_from_pv_tag("Unit1.FIC101.PV", &template);
1394        let config = bhtune_core::LoopConfig {
1395            process_type: bhtune_core::ProcessType::Flow,
1396            controller_type: bhtune_core::ControllerType::Pi,
1397            relay_amp_percent: 5.0,
1398            num_cycles_skip: 1,
1399            num_cycles_count: 2,
1400            noise_protection_secs: 3,
1401            mrft_delay_secs: 0,
1402        };
1403        let run = TuneRunRow::start(
1404            &pool,
1405            None,
1406            "Unit1.FIC101.PV",
1407            TuneDriver::Simulator,
1408            config,
1409            TemplateOrigin::Builtin,
1410            &template,
1411            &tags,
1412            chrono::Utc::now(),
1413        )
1414        .await
1415        .unwrap();
1416        (pool, run.id)
1417    }
1418
1419    #[test]
1420    fn tune_driver_round_trips_and_matches_check_constraint() {
1421        let cases = [
1422            (TuneDriver::Opcda, "opcda"),
1423            (TuneDriver::Simulator, "simulator"),
1424            (TuneDriver::Replay, "replay"),
1425        ];
1426        for (variant, text) in cases {
1427            assert_eq!(enum_to_text(&variant).unwrap(), text);
1428            assert_eq!(text_to_enum::<TuneDriver>("driver", text).unwrap(), variant);
1429        }
1430    }
1431
1432    #[test]
1433    fn tune_outcome_round_trips_and_matches_check_constraint() {
1434        let cases = [
1435            (TuneOutcome::Running, "running"),
1436            (TuneOutcome::Completed, "completed"),
1437            (TuneOutcome::Failed, "failed"),
1438            (TuneOutcome::Aborted, "aborted"),
1439        ];
1440        for (variant, text) in cases {
1441            assert_eq!(enum_to_text(&variant).unwrap(), text);
1442            assert_eq!(
1443                text_to_enum::<TuneOutcome>("outcome", text).unwrap(),
1444                variant
1445            );
1446        }
1447    }
1448
1449    #[tokio::test]
1450    async fn record_restore_status_round_trips_both_status_shapes() {
1451        let (pool, run_id) = sample_run().await;
1452        let confirmed =
1453            TuneRunRow::record_restore_status(&pool, run_id, RestoreStatus::Confirmed, None)
1454                .await
1455                .unwrap();
1456        assert_eq!(confirmed.restore_status, Some(RestoreStatus::Confirmed));
1457        assert_eq!(confirmed.restore_detail, None);
1458
1459        let incomplete = TuneRunRow::record_restore_status(
1460            &pool,
1461            run_id,
1462            RestoreStatus::Incomplete,
1463            Some("MV restore failed"),
1464        )
1465        .await
1466        .unwrap();
1467        assert_eq!(incomplete.restore_status, Some(RestoreStatus::Incomplete));
1468        assert_eq!(
1469            incomplete.restore_detail.as_deref(),
1470            Some("MV restore failed")
1471        );
1472    }
1473
1474    #[tokio::test]
1475    async fn tune_run_delete_reports_both_existing_and_missing_ids() {
1476        let (pool, run_id) = sample_run().await;
1477        assert!(TuneRunRow::delete(&pool, run_id).await.unwrap());
1478        assert!(!TuneRunRow::delete(&pool, run_id).await.unwrap());
1479    }
1480
1481    #[tokio::test]
1482    async fn deletion_preserves_runs_with_unresolved_live_recovery() {
1483        for recovery_state in ["eligible", "running", "incomplete"] {
1484            let (pool, run_id) = sample_run().await;
1485            sqlx::query("UPDATE tune_runs SET outcome = 'failed', recovery_state = ? WHERE id = ?")
1486                .bind(recovery_state)
1487                .bind(run_id)
1488                .execute(&pool)
1489                .await
1490                .unwrap();
1491
1492            assert_eq!(
1493                TuneRunRow::count_deletable_matching(&pool, &TuneRunFilter::default())
1494                    .await
1495                    .unwrap(),
1496                0
1497            );
1498            assert_eq!(
1499                TuneRunRow::delete_matching(&pool, &TuneRunFilter::default())
1500                    .await
1501                    .unwrap(),
1502                0
1503            );
1504            assert!(!TuneRunRow::delete(&pool, run_id).await.unwrap());
1505            assert!(TuneRunRow::get(&pool, run_id).await.unwrap().is_some());
1506        }
1507    }
1508
1509    #[tokio::test]
1510    async fn malformed_tune_run_json_is_reported_with_the_respective_column() {
1511        for (column, expected) in [
1512            ("template_snapshot_json", "template_snapshot_json"),
1513            ("tags_json", "tags_json"),
1514            ("timing_metrics_json", "timing_metrics_json"),
1515            ("effective_tuning_json", "effective_tuning_json"),
1516        ] {
1517            let (pool, run_id) = sample_run().await;
1518            let query = match column {
1519                "template_snapshot_json" => {
1520                    sqlx::query("UPDATE tune_runs SET template_snapshot_json = ? WHERE id = ?")
1521                }
1522                "tags_json" => sqlx::query("UPDATE tune_runs SET tags_json = ? WHERE id = ?"),
1523                "timing_metrics_json" => {
1524                    sqlx::query("UPDATE tune_runs SET timing_metrics_json = ? WHERE id = ?")
1525                }
1526                "effective_tuning_json" => {
1527                    sqlx::query("UPDATE tune_runs SET effective_tuning_json = ? WHERE id = ?")
1528                }
1529                _ => unreachable!(),
1530            };
1531            query
1532                .bind("\"wrong-shape\"")
1533                .bind(run_id)
1534                .execute(&pool)
1535                .await
1536                .unwrap();
1537            let err = TuneRunRow::get(&pool, run_id).await.unwrap_err();
1538            assert!(matches!(
1539                err,
1540                DbError::InvalidJsonShape { column: actual, .. } if actual == expected
1541            ));
1542        }
1543    }
1544}
bhtune_db/models/tune_runs.rs

bhtune_db/models/
tune_runs.rs
