Database Schema Design (Phase 1 Design.3)

Repo: ROCm/quartz
Phase: 1 Design
Labels: quartz, phase-1, design, database
Blocked by: Phase 1 Design: Data Extraction Spec
Parallel with: Phase 1 Design: status.json Schema
Blocks: Phase 1 A2 (Database Setup), Phase 1 B+C (insert_therock_data.py)

Context

Finalize the ClickHouse schema for therock_workflow_runs and therock_workflow_jobs
based on the agreed data extraction spec. The schema must capture all fields needed
for Grafana dashboards — target metrics should be agreed here before deployment.

Reference: RFC-0011 (ROCm/TheRock#3728 / docs/rfcs/RFC0011-Quartz-CICD-Datahub.md in ROCm/TheRock once merged) — Phase 1, Section A; Database Design.

It needs the following rework before it can be used:

• Replace updated_at as the ReplacingMergeTree version column with version UInt64
  computed as run_attempt * 2 + signal_type (0 = START, 1 = FINISH)
• Only therock_workflow_* tables have been reviewed — other tables are still
  only AI-generated and need reviewing

Tasks

• Review the existing schema draft against the agreed data extraction spec
• Finalize column definitions and types for therock_workflow_runs and
  therock_workflow_jobs
• Confirm ReplacingMergeTree deduplication strategy (version UInt64)
• Agree on target Grafana metrics to ensure the right columns and granularity are
  captured
• Review and finalize or discard AI-generated tables beyond therock_workflow_*
• Produce final schema SQL to be deployed in Phase 1 A2 and added to Terraform
  later

Start by reading RFC-0011, especially Phase 1 Section A and the agreed data extraction spec, then review the existing schema draft for the therock_workflow_runs and therock_workflow_jobs tables. Resolve the version-based ReplacingMergeTree strategy and required Grafana metrics, review the other AI-generated tables, and finish with deployable schema SQL for Phase 1 A2 and later Terraform integration.