agentics_persistence/db/
solution_submissions.rs

1use chrono::{DateTime, Utc};
2use serde_json::Value;
3use sqlx::{PgPool, Postgres, Row, Transaction};
4
5use agentics_domain::models::challenge::{ChallengeBundleSpec, TargetAccelerator};
6use agentics_domain::models::evaluation::{
7    EvaluationDto, EvaluationJobPayload, EvaluationJobStatus, EvaluationStatus, MetricValue,
8    ScoringMode, SolutionArtifactMetadata, SolutionSubmissionStatus,
9};
10use agentics_domain::models::ids::{AgentId, EvaluationJobId, SolutionSubmissionId};
11use agentics_domain::models::names::{ChallengeName, TargetName};
12use agentics_domain::storage::StorageKey;
13use agentics_error::{Result, ServiceError};
14
15use super::evaluation_policy::{
16    ensure_challenge_supports_eval_type_tx, ensure_validation_uses_public_bundle,
17    lock_active_challenge_for_admission_tx,
18};
19use super::ids::{agent_id_from_row, challenge_name_from_row, solution_submission_id_from_row};
20use super::json::decode_optional_json;
21
22mod admission;
23mod rows;
24use admission::enforce_quota_admission;
25use rows::{
26    artifact_metadata_from_row, count_to_u64, optional_evaluation_job_id_from_row,
27    optional_evaluation_job_status_from_row, optional_evaluation_status_from_row,
28    optional_scoring_mode_from_row, optional_solution_submission_id_from_row, parse_eval_from_row,
29    solution_submission_status_from_row, storage_key_from_row, target_from_row, u64_to_i64,
30};
31
32/// Input for creating a solution submission and its initial evaluation job.
33#[derive(Debug, Clone)]
34pub struct CreateSolutionSubmissionInput {
35    pub solution_submission_id: SolutionSubmissionId,
36    pub job_id: EvaluationJobId,
37    pub agent_id: AgentId,
38    pub challenge_name: ChallengeName,
39    pub target: TargetName,
40    pub artifact_key: StorageKey,
41    pub artifact_metadata: SolutionArtifactMetadata,
42    pub note: String,
43    pub eval_type: ScoringMode,
44    pub explanation: String,
45    pub parent_solution_submission_id: Option<SolutionSubmissionId>,
46    pub credit_text: String,
47    pub quota_admission: SolutionSubmissionQuotaAdmission,
48}
49
50/// Admin solution-submission list row before DTO projection.
51#[derive(Debug, Clone)]
52pub struct AdminSolutionSubmissionListItemRecord {
53    pub id: SolutionSubmissionId,
54    pub challenge_name: ChallengeName,
55    pub challenge_title: String,
56    pub target: TargetName,
57    pub agent_id: AgentId,
58    pub agent_display_name: String,
59    pub status: SolutionSubmissionStatus,
60    pub note: String,
61    pub visible_after_eval: bool,
62    pub latest_job_id: Option<EvaluationJobId>,
63    pub latest_job_status: Option<EvaluationJobStatus>,
64    pub latest_job_eval_type: Option<ScoringMode>,
65    pub validation_status: Option<EvaluationStatus>,
66    pub official_status: Option<EvaluationStatus>,
67    pub created_at: DateTime<Utc>,
68    pub updated_at: DateTime<Utc>,
69}
70
71/// Public solution-submission list row before DTO projection/redaction.
72#[derive(Debug, Clone)]
73pub struct PublicSolutionSubmissionListItemRecord {
74    pub id: SolutionSubmissionId,
75    pub challenge_name: ChallengeName,
76    pub target: TargetName,
77    pub challenge_title: String,
78    pub agent_id: AgentId,
79    pub agent_display_name: String,
80    pub status: SolutionSubmissionStatus,
81    pub note: String,
82    pub explanation: String,
83    pub parent_solution_submission_id: Option<SolutionSubmissionId>,
84    pub credit_text: String,
85    pub official_metrics: Vec<MetricValue>,
86    pub created_at: DateTime<Utc>,
87    pub updated_at: DateTime<Utc>,
88}
89
90/// Aggregate public observer counters before transport projection.
91#[derive(Debug, Clone, Copy)]
92pub struct PublicObserverStatsRecord {
93    pub challenge_count: u64,
94    pub agent_count: u64,
95    pub public_completed_submission_count: u64,
96    pub total_solution_attempt_count: u64,
97}
98
99/// Authoritative quota limits applied inside the submission/job transaction.
100#[derive(Debug, Clone, Copy)]
101pub struct SolutionSubmissionQuotaAdmission {
102    pub window_seconds: i64,
103    pub per_agent_challenge_limit: i64,
104    pub challenge_lifetime_limit: Option<i64>,
105    pub max_active_official_jobs: Option<i64>,
106}
107
108/// Solution submission row with optional joined evaluation and job metadata.
109#[derive(Debug, Clone)]
110pub struct SolutionSubmissionRecord {
111    pub id: SolutionSubmissionId,
112    pub challenge_name: ChallengeName,
113    pub target: TargetName,
114    pub agent_id: AgentId,
115    pub agent_display_name: Option<String>,
116    pub challenge_title: Option<String>,
117    pub challenge_spec: ChallengeBundleSpec,
118    pub artifact_key: StorageKey,
119    pub artifact_metadata: Option<SolutionArtifactMetadata>,
120    pub note: String,
121    pub status: String,
122    pub explanation: String,
123    pub parent_solution_submission_id: Option<SolutionSubmissionId>,
124    pub credit_text: String,
125    pub visible_after_eval: bool,
126    pub created_at: DateTime<Utc>,
127    pub updated_at: DateTime<Utc>,
128    pub evaluation_job_id: Option<EvaluationJobId>,
129    pub evaluation_job_status: Option<String>,
130    pub evaluation: Option<EvaluationDto>,
131    pub validation_evaluation: Option<EvaluationDto>,
132    pub official_evaluation: Option<EvaluationDto>,
133}
134
135/// Create a solution submission and queue its first evaluation atomically.
136pub async fn create_solution_submission_with_job(
137    pool: &PgPool,
138    input: &CreateSolutionSubmissionInput,
139) -> Result<SolutionSubmissionRecord> {
140    let mut tx = pool.begin().await?;
141    let challenge = lock_active_challenge_for_admission_tx(&mut tx, &input.challenge_name).await?;
142    let spec: ChallengeBundleSpec = serde_json::from_value(challenge.spec_json.clone())
143        .map_err(|e| ServiceError::Internal(e.to_string()))?;
144    ensure_challenge_supports_eval_type_tx(
145        &mut tx,
146        &challenge.challenge_name,
147        &spec,
148        &input.target,
149        input.eval_type,
150        &input.agent_id,
151    )
152    .await?;
153    ensure_validation_uses_public_bundle(
154        input.eval_type,
155        &spec,
156        &challenge.bundle_key,
157        &challenge.public_bundle_key,
158    )?;
159    enforce_quota_admission(&mut tx, input).await?;
160    ensure_parent_solution_submission_matches_scope_tx(
161        &mut tx,
162        input.parent_solution_submission_id.as_ref(),
163        &input.agent_id,
164        &challenge.challenge_name,
165        &input.target,
166    )
167    .await?;
168
169    let row = sqlx::query(
170        r#"
171        INSERT INTO solution_submissions (
172            id, challenge_name, target, agent_id, artifact_key, note,
173            artifact_zip_bytes, artifact_uncompressed_bytes, artifact_file_count, artifact_sha256,
174            status, explanation, parent_solution_submission_id, credit_text, visible_after_eval
175        )
176        VALUES ($1::uuid, $2, $3, $4::uuid, $5, $6, $7, $8, $9, $10, 'pending', $11, $12::uuid, $13, FALSE)
177        RETURNING
178            id, challenge_name, target, agent_id, artifact_key, note,
179            artifact_zip_bytes, artifact_uncompressed_bytes, artifact_file_count, artifact_sha256,
180            status, explanation, parent_solution_submission_id, credit_text, visible_after_eval,
181            created_at, updated_at
182        "#,
183    )
184    .bind(input.solution_submission_id.as_str())
185    .bind(challenge.challenge_name.as_str())
186    .bind(input.target.as_str())
187    .bind(input.agent_id.as_str())
188    .bind(input.artifact_key.as_str())
189    .bind(&input.note)
190    .bind(u64_to_i64(
191        input.artifact_metadata.artifact_zip_bytes,
192        "artifact_zip_bytes",
193    )?)
194    .bind(u64_to_i64(
195        input.artifact_metadata.artifact_uncompressed_bytes,
196        "artifact_uncompressed_bytes",
197    )?)
198    .bind(u64_to_i64(
199        input.artifact_metadata.artifact_file_count,
200        "artifact_file_count",
201    )?)
202    .bind(input.artifact_metadata.artifact_sha256.to_string())
203    .bind(&input.explanation)
204    .bind(
205        input
206            .parent_solution_submission_id
207            .as_ref()
208            .map(SolutionSubmissionId::as_str),
209    )
210    .bind(&input.credit_text)
211    .fetch_one(&mut *tx)
212    .await?;
213
214    let payload = serde_json::to_value(EvaluationJobPayload {
215        artifact_key: input.artifact_key.clone(),
216        bundle_key: challenge.bundle_key.clone(),
217        public_bundle_key: challenge.public_bundle_key.clone(),
218        challenge_name: challenge.challenge_name.clone(),
219        target: input.target.clone(),
220    })
221    .map_err(|e| ServiceError::Internal(e.to_string()))?;
222
223    let priority = if input.eval_type == ScoringMode::Official {
224        10
225    } else {
226        0
227    };
228    let required_accelerator = required_accelerator_for_target(&spec, &input.target)?;
229
230    sqlx::query(
231        r#"
232        INSERT INTO evaluation_jobs (
233            id, solution_submission_id, challenge_name, target, required_accelerator, eval_type, status, priority, payload_json, scheduled_at
234        )
235        VALUES (
236            $1::uuid, $2::uuid, $3, $4, $5, $6, 'staged', $7, $8,
237            NOW()
238        )
239        "#,
240    )
241    .bind(input.job_id.as_str())
242    .bind(input.solution_submission_id.as_str())
243    .bind(challenge.challenge_name.as_str())
244    .bind(input.target.as_str())
245    .bind(required_accelerator.as_str())
246    .bind(input.eval_type.as_str())
247    .bind(priority)
248    .bind(&payload)
249    .execute(&mut *tx)
250    .await?;
251
252    tx.commit().await?;
253
254    Ok(SolutionSubmissionRecord {
255        id: solution_submission_id_from_row(&row, "id")?,
256        challenge_name: challenge.challenge_name,
257        target: target_from_row(&row, "target")?,
258        agent_id: agent_id_from_row(&row, "agent_id")?,
259        agent_display_name: None,
260        challenge_title: None,
261        challenge_spec: spec,
262        artifact_key: storage_key_from_row(&row, "artifact_key")?,
263        artifact_metadata: artifact_metadata_from_row(&row)?,
264        note: row.try_get("note")?,
265        status: row.try_get("status")?,
266        explanation: row.try_get("explanation")?,
267        parent_solution_submission_id: optional_solution_submission_id_from_row(
268            &row,
269            "parent_solution_submission_id",
270        )?,
271        credit_text: row.try_get("credit_text")?,
272        visible_after_eval: row.try_get("visible_after_eval")?,
273        created_at: row.try_get("created_at")?,
274        updated_at: row.try_get("updated_at")?,
275        evaluation_job_id: Some(input.job_id.clone()),
276        evaluation_job_status: Some("staged".to_string()),
277        evaluation: None,
278        validation_evaluation: None,
279        official_evaluation: None,
280    })
281}
282
283/// Verify that an optional parent submission belongs to the same agent and ranking scope.
284pub async fn ensure_parent_solution_submission_matches_scope(
285    pool: &PgPool,
286    parent_solution_submission_id: Option<&SolutionSubmissionId>,
287    agent_id: &AgentId,
288    challenge_name: &ChallengeName,
289    target: &TargetName,
290) -> Result<()> {
291    let mut tx = pool.begin().await?;
292    ensure_parent_solution_submission_matches_scope_tx(
293        &mut tx,
294        parent_solution_submission_id,
295        agent_id,
296        challenge_name,
297        target,
298    )
299    .await?;
300    tx.commit().await?;
301    Ok(())
302}
303
304/// Return the accelerator requirement declared by the selected challenge target.
305fn required_accelerator_for_target(
306    spec: &ChallengeBundleSpec,
307    target: &TargetName,
308) -> Result<TargetAccelerator> {
309    let target_spec = spec.target(target).ok_or_else(|| {
310        ServiceError::Internal(format!(
311            "challenge `{}` does not declare target `{target}` after admission validation",
312            spec.challenge_name
313        ))
314    })?;
315    Ok(target_spec.accelerator)
316}
317
318/// Enforce parent-submission lineage invariants inside a submission transaction.
319async fn ensure_parent_solution_submission_matches_scope_tx<'a>(
320    tx: &mut Transaction<'a, Postgres>,
321    parent_solution_submission_id: Option<&SolutionSubmissionId>,
322    agent_id: &AgentId,
323    challenge_name: &ChallengeName,
324    target: &TargetName,
325) -> Result<()> {
326    let Some(parent_solution_submission_id) = parent_solution_submission_id else {
327        return Ok(());
328    };
329
330    let row = sqlx::query(
331        r#"
332        SELECT agent_id, challenge_name, target, status, visible_after_eval
333        FROM solution_submissions
334        WHERE id = $1::uuid
335        LIMIT 1
336        "#,
337    )
338    .bind(parent_solution_submission_id.as_str())
339    .fetch_optional(&mut **tx)
340    .await?;
341    let Some(row) = row else {
342        return Err(ServiceError::BadRequest(
343            "parent_solution_submission_id does not reference an existing solution submission"
344                .to_string(),
345        ));
346    };
347
348    let parent_agent_id = agent_id_from_row(&row, "agent_id")?;
349    let parent_challenge_name = challenge_name_from_row(&row, "challenge_name")?;
350    let parent_target = target_from_row(&row, "target")?;
351    let parent_status: String = row.try_get("status")?;
352    let parent_visible: bool = row.try_get("visible_after_eval")?;
353
354    if &parent_agent_id != agent_id
355        || &parent_challenge_name != challenge_name
356        || &parent_target != target
357    {
358        return Err(ServiceError::BadRequest(
359            "parent_solution_submission_id must belong to the same agent, challenge_name, and target"
360                .to_string(),
361        ));
362    }
363    if parent_status != SolutionSubmissionStatus::Completed.as_str() || !parent_visible {
364        return Err(ServiceError::BadRequest(
365            "parent_solution_submission_id must reference a completed visible solution submission"
366                .to_string(),
367        ));
368    }
369
370    Ok(())
371}
372
373/// Delete a solution submission and its dependent jobs/evaluations.
374pub async fn delete_solution_submission(
375    pool: &PgPool,
376    solution_submission_id: &SolutionSubmissionId,
377) -> Result<()> {
378    sqlx::query("DELETE FROM solution_submissions WHERE id = $1::uuid")
379        .bind(solution_submission_id.as_str())
380        .execute(pool)
381        .await?;
382    Ok(())
383}
384
385/// Fetch one solution submission with latest job state and validation/official evaluations.
386pub async fn get_solution_submission_by_id(
387    pool: &PgPool,
388    solution_submission_id: &SolutionSubmissionId,
389) -> Result<Option<SolutionSubmissionRecord>> {
390    get_solution_submission_by_id_inner(pool, solution_submission_id, false).await
391}
392
393/// Fetch one public result-of-record submission with the latest completed official evaluation.
394pub async fn get_public_solution_submission_by_id(
395    pool: &PgPool,
396    solution_submission_id: &SolutionSubmissionId,
397) -> Result<Option<SolutionSubmissionRecord>> {
398    get_solution_submission_by_id_inner(pool, solution_submission_id, true).await
399}
400
401/// Fetch one solution submission while optionally restricting official evaluations to completed
402/// public result-of-record rows.
403async fn get_solution_submission_by_id_inner(
404    pool: &PgPool,
405    solution_submission_id: &SolutionSubmissionId,
406    completed_official_only: bool,
407) -> Result<Option<SolutionSubmissionRecord>> {
408    let row = sqlx::query(
409        r#"
410        SELECT
411            s.id, s.challenge_name, s.target, s.agent_id,
412            p.title AS challenge_title, p.spec_json AS challenge_spec_json,
413            a.display_name AS agent_display_name,
414            s.artifact_key, s.note, s.status, s.explanation,
415            s.artifact_zip_bytes, s.artifact_uncompressed_bytes, s.artifact_file_count, s.artifact_sha256,
416            s.parent_solution_submission_id, s.credit_text, s.visible_after_eval,
417            s.created_at, s.updated_at,
418            j.id AS latest_job_id, j.status AS latest_job_status,
419            pe.id AS validation_eval_id,
420            pe.target AS validation_eval_target,
421            pe.status AS validation_eval_status,
422            pe.eval_type AS validation_eval_eval_type,
423            pe.aggregate_metrics_json AS validation_eval_aggregate_metrics,
424            pe.run_metrics_json AS validation_eval_run_metrics,
425            pe.public_results_json AS validation_eval_public_results,
426            pe.validation_summary_json AS validation_eval_validation_summary,
427            pe.official_summary_json AS validation_eval_official_summary,
428            pe.runner_log_storage_key AS validation_eval_runner_log_storage_key,
429            pe.started_at AS validation_eval_started_at,
430            pe.finished_at AS validation_eval_finished_at,
431            oe.id AS official_eval_id,
432            oe.target AS official_eval_target,
433            oe.status AS official_eval_status,
434            oe.eval_type AS official_eval_eval_type,
435            oe.aggregate_metrics_json AS official_eval_aggregate_metrics,
436            oe.run_metrics_json AS official_eval_run_metrics,
437            oe.public_results_json AS official_eval_public_results,
438            oe.validation_summary_json AS official_eval_validation_summary,
439            oe.official_summary_json AS official_eval_official_summary,
440            oe.runner_log_storage_key AS official_eval_runner_log_storage_key,
441            oe.started_at AS official_eval_started_at,
442            oe.finished_at AS official_eval_finished_at
443        FROM solution_submissions s
444        JOIN agents a ON a.id = s.agent_id
445        JOIN challenges p ON p.challenge_name = s.challenge_name
446        LEFT JOIN LATERAL (
447            SELECT id, status FROM evaluation_jobs WHERE solution_submission_id = s.id ORDER BY created_at DESC LIMIT 1
448        ) j ON TRUE
449        LEFT JOIN LATERAL (
450            SELECT id, target, status, eval_type, aggregate_metrics_json, run_metrics_json, public_results_json, validation_summary_json, official_summary_json, runner_log_storage_key, started_at, finished_at
451            FROM evaluations WHERE solution_submission_id = s.id AND eval_type = 'validation' AND target = s.target ORDER BY created_at DESC LIMIT 1
452        ) pe ON TRUE
453        LEFT JOIN LATERAL (
454            SELECT id, target, status, eval_type, aggregate_metrics_json, run_metrics_json, public_results_json, validation_summary_json, official_summary_json, runner_log_storage_key, started_at, finished_at
455            FROM evaluations
456            WHERE solution_submission_id = s.id
457              AND eval_type = 'official'
458              AND target = s.target
459              AND (NOT $2::boolean OR status = 'completed')
460            ORDER BY created_at DESC
461            LIMIT 1
462        ) oe ON TRUE
463        WHERE s.id = $1::uuid
464        LIMIT 1
465        "#
466    )
467        .bind(solution_submission_id.as_str())
468        .bind(completed_official_only)
469        .fetch_optional(pool)
470        .await?;
471
472    let Some(r) = row else {
473        return Ok(None);
474    };
475
476    let validation_eval = parse_eval_from_row(&r, "validation_eval")?;
477    let official_eval = parse_eval_from_row(&r, "official_eval")?;
478    let challenge_spec_json: Value = r.try_get("challenge_spec_json")?;
479    let challenge_spec = serde_json::from_value::<ChallengeBundleSpec>(challenge_spec_json)
480        .map_err(|e| ServiceError::Internal(format!("stored challenge spec is invalid: {e}")))?;
481
482    Ok(Some(SolutionSubmissionRecord {
483        id: solution_submission_id_from_row(&r, "id")?,
484        challenge_name: challenge_name_from_row(&r, "challenge_name")?,
485        target: target_from_row(&r, "target")?,
486        agent_id: agent_id_from_row(&r, "agent_id")?,
487        agent_display_name: r.try_get::<Option<String>, _>("agent_display_name")?,
488        challenge_title: r.try_get::<Option<String>, _>("challenge_title")?,
489        challenge_spec,
490        artifact_key: storage_key_from_row(&r, "artifact_key")?,
491        artifact_metadata: artifact_metadata_from_row(&r)?,
492        note: r.try_get("note")?,
493        status: r.try_get("status")?,
494        explanation: r.try_get("explanation")?,
495        parent_solution_submission_id: optional_solution_submission_id_from_row(
496            &r,
497            "parent_solution_submission_id",
498        )?,
499        credit_text: r.try_get("credit_text")?,
500        visible_after_eval: r.try_get("visible_after_eval")?,
501        created_at: r.try_get("created_at")?,
502        updated_at: r.try_get("updated_at")?,
503        evaluation_job_id: optional_evaluation_job_id_from_row(&r, "latest_job_id")?,
504        evaluation_job_status: r.try_get::<Option<String>, _>("latest_job_status")?,
505        evaluation: official_eval.clone().or_else(|| validation_eval.clone()),
506        validation_evaluation: validation_eval,
507        official_evaluation: official_eval,
508    }))
509}
510
511/// List recent solution submissions for admin operations.
512pub async fn list_admin_solution_submissions(
513    pool: &PgPool,
514    limit: i64,
515) -> Result<Vec<AdminSolutionSubmissionListItemRecord>> {
516    let rows = sqlx::query(
517        r#"
518        SELECT
519            s.id,
520            s.challenge_name,
521            s.target,
522            p.title AS challenge_title,
523            s.agent_id,
524            a.display_name AS agent_display_name,
525            s.note,
526            s.status,
527            s.visible_after_eval,
528            s.created_at,
529            s.updated_at,
530            j.id AS latest_job_id,
531            j.status AS latest_job_status,
532            j.eval_type AS latest_job_eval_type,
533            ve.status AS validation_status,
534            oe.status AS official_status
535        FROM solution_submissions s
536        JOIN challenges p ON p.challenge_name = s.challenge_name
537        JOIN agents a ON a.id = s.agent_id
538        LEFT JOIN LATERAL (
539            SELECT id, status, eval_type
540            FROM evaluation_jobs
541            WHERE solution_submission_id = s.id
542            ORDER BY created_at DESC
543            LIMIT 1
544        ) j ON TRUE
545        LEFT JOIN LATERAL (
546            SELECT status
547            FROM evaluations
548            WHERE solution_submission_id = s.id AND eval_type = 'validation'
549            ORDER BY created_at DESC
550            LIMIT 1
551        ) ve ON TRUE
552        LEFT JOIN LATERAL (
553            SELECT status
554            FROM evaluations
555            WHERE solution_submission_id = s.id AND eval_type = 'official'
556            ORDER BY created_at DESC
557            LIMIT 1
558        ) oe ON TRUE
559        ORDER BY s.updated_at DESC, s.created_at DESC
560        LIMIT $1
561        "#,
562    )
563    .bind(limit)
564    .fetch_all(pool)
565    .await?;
566
567    rows.into_iter()
568        .map(|r| {
569            Ok(AdminSolutionSubmissionListItemRecord {
570                id: solution_submission_id_from_row(&r, "id")?,
571                challenge_name: challenge_name_from_row(&r, "challenge_name")?,
572                challenge_title: r.try_get("challenge_title")?,
573                target: target_from_row(&r, "target")?,
574                agent_id: agent_id_from_row(&r, "agent_id")?,
575                agent_display_name: r.try_get("agent_display_name")?,
576                note: r.try_get("note")?,
577                status: solution_submission_status_from_row(&r, "status")?,
578                visible_after_eval: r.try_get("visible_after_eval")?,
579                latest_job_id: optional_evaluation_job_id_from_row(&r, "latest_job_id")?,
580                latest_job_status: optional_evaluation_job_status_from_row(
581                    &r,
582                    "latest_job_status",
583                )?,
584                latest_job_eval_type: optional_scoring_mode_from_row(&r, "latest_job_eval_type")?,
585                validation_status: optional_evaluation_status_from_row(&r, "validation_status")?,
586                official_status: optional_evaluation_status_from_row(&r, "official_status")?,
587                created_at: r.try_get("created_at")?,
588                updated_at: r.try_get("updated_at")?,
589            })
590        })
591        .collect::<Result<Vec<_>>>()
592}
593
594/// List solution submissions for a challenge after an official evaluation makes them visible.
595pub async fn list_public_solution_submissions_for_challenge(
596    pool: &PgPool,
597    challenge_name: &ChallengeName,
598    target: &TargetName,
599    limit: i64,
600) -> Result<Vec<PublicSolutionSubmissionListItemRecord>> {
601    let rows = sqlx::query(
602        r#"
603        SELECT
604            s.id, s.challenge_name, s.target, p.title AS challenge_title,
605            s.agent_id, a.display_name AS agent_display_name, s.status, s.note, s.explanation,
606            s.parent_solution_submission_id, s.credit_text, s.created_at, s.updated_at,
607            COALESCE(oe.aggregate_metrics_json, '[]'::jsonb) AS official_metrics
608        FROM solution_submissions s
609        JOIN agents a ON a.id = s.agent_id
610        JOIN challenges p ON p.challenge_name = s.challenge_name
611        LEFT JOIN LATERAL (
612            SELECT aggregate_metrics_json, official_summary_json
613            FROM evaluations
614            WHERE solution_submission_id = s.id AND eval_type = 'official' AND status = 'completed' AND target = s.target
615            ORDER BY created_at DESC LIMIT 1
616        ) oe ON TRUE
617        WHERE p.challenge_name = $1
618          AND s.visible_after_eval = TRUE
619          AND s.target = $2
620        ORDER BY s.created_at DESC
621        LIMIT $3
622        "#,
623    )
624    .bind(challenge_name.as_str())
625    .bind(target.as_str())
626    .bind(limit)
627    .fetch_all(pool)
628    .await?;
629
630    rows.into_iter()
631        .map(|r| {
632            let official_metrics: Vec<MetricValue> = decode_optional_json(
633                r.try_get::<Option<Value>, _>("official_metrics")?,
634                "solution submission official metrics",
635            )?
636            .unwrap_or_default();
637            Ok(PublicSolutionSubmissionListItemRecord {
638                id: solution_submission_id_from_row(&r, "id")?,
639                challenge_name: challenge_name_from_row(&r, "challenge_name")?,
640                target: target_from_row(&r, "target")?,
641                challenge_title: r.try_get("challenge_title")?,
642                agent_id: agent_id_from_row(&r, "agent_id")?,
643                agent_display_name: r.try_get("agent_display_name")?,
644                status: solution_submission_status_from_row(&r, "status")?,
645                note: r.try_get("note")?,
646                explanation: r.try_get("explanation")?,
647                parent_solution_submission_id: optional_solution_submission_id_from_row(
648                    &r,
649                    "parent_solution_submission_id",
650                )?,
651                credit_text: r.try_get("credit_text")?,
652                official_metrics,
653                created_at: r.try_get("created_at")?,
654                updated_at: r.try_get("updated_at")?,
655            })
656        })
657        .collect::<Result<Vec<_>>>()
658}
659
660/// Count visible solution submissions for a challenge and target.
661pub async fn count_public_solution_submissions_for_challenge(
662    pool: &PgPool,
663    challenge_name: &ChallengeName,
664    target: &TargetName,
665) -> Result<i64> {
666    let count = sqlx::query_scalar::<_, i64>(
667        r#"
668        SELECT COUNT(*)::bigint
669        FROM solution_submissions s
670        WHERE s.challenge_name = $1
671          AND s.visible_after_eval = TRUE
672          AND s.target = $2
673        "#,
674    )
675    .bind(challenge_name.as_str())
676    .bind(target.as_str())
677    .fetch_one(pool)
678    .await?;
679
680    Ok(count)
681}
682
683/// Count aggregate currently public observer stats.
684pub async fn public_observer_stats(pool: &PgPool) -> Result<PublicObserverStatsRecord> {
685    let row = sqlx::query_as::<_, (i64, i64, i64, i64)>(
686        r#"
687        WITH public_challenges AS (
688            SELECT challenge_name, spec_json
689            FROM challenges
690            WHERE status = 'active'
691              AND spec_json IS NOT NULL
692        ),
693        public_submissions AS (
694            SELECT s.agent_id
695            FROM solution_submissions s
696            JOIN public_challenges c ON c.challenge_name = s.challenge_name
697            WHERE s.visible_after_eval = TRUE
698              AND s.status = 'completed'
699              AND (
700                c.spec_json #>> '{visibility,result_detail}' = 'submitter_live_public_live'
701                OR (
702                    c.spec_json #>> '{visibility,result_detail}' = 'submitter_live_public_after_close'
703                    AND (c.spec_json ->> 'closes_at')::timestamptz <= NOW()
704                )
705              )
706        ),
707        public_challenge_attempts AS (
708            SELECT s.id
709            FROM solution_submissions s
710            JOIN public_challenges c ON c.challenge_name = s.challenge_name
711        )
712        SELECT
713            (SELECT COUNT(*)::bigint FROM public_challenges) AS challenge_count,
714            (SELECT COUNT(DISTINCT agent_id)::bigint FROM public_submissions) AS agent_count,
715            (SELECT COUNT(*)::bigint FROM public_submissions) AS public_completed_submission_count,
716            (SELECT COUNT(*)::bigint FROM public_challenge_attempts) AS total_solution_attempt_count
717        "#,
718    )
719    .fetch_one(pool)
720    .await?;
721
722    Ok(PublicObserverStatsRecord {
723        challenge_count: count_to_u64("challenge_count", row.0)?,
724        agent_count: count_to_u64("agent_count", row.1)?,
725        public_completed_submission_count: count_to_u64(
726            "public_completed_submission_count",
727            row.2,
728        )?,
729        total_solution_attempt_count: count_to_u64("total_solution_attempt_count", row.3)?,
730    })
731}
agentics_persistence/db/solution_submissions.rs

agentics_persistence/db/
solution_submissions.rs
