-- Invariants:
-- * Each run is only ever used on one cloud
-- * Each run only ever uses one version of Cockroach
CREATE TABLE runs (
    id INT8 PRIMARY KEY,
    crdb_version STRING DEFAULT 'master',
    INDEX (cloud) COVERING id, -- Get all runs by cloud
    INDEX (crdb_version) COVERING id -- Get all runs by version; e.g. roachperf wants master but SE tools want specific version
);

-- Metadata about each instance of the workload being run
CREATE TABLE test_details (
    runs_id INT8 REFERENCES runs (id),
		id SERIAL UNIQUE, -- simplify data_details lookups; UNIQUE optimizes all FK lookups
    test_name STRING,
    PRIMARY KEY(runs_id, id),
    INDEX(test_name) COVERING id -- Get all IDs by test name
);

-- Track the hardware used for the test, which we'll ultimately want for cluster sizing/SE work
CREATE TABLE infrastructure (
    test_detail_id INT8 REFERENCES test_details (id),
    instance_class STRING,
		disk_type STRING,
		zone STRING,
    quantity INT,
		PRIMARY KEY (test_detail_id, instance_class, disk_type, zone)
);

-- Overview of all benchmarks of a given name on a given platform
CREATE TABLE data_overview (
    test_detail_id INT8 REFERENCES test_details (id),
    date TIMESTAMP,
    data JSONB, -- possible to track CPU percentage here; the SE work will want to filter this on things under 50% average utilization
    PRIMARY KEY(test_detail_id, date)
);

-- Details of specific instance of benchmark/workload being run
CREATE TABLE data_details (
    test_detail_id INT8 REFERENCES test_details (id),
    ts TIMESTAMP,
    operation STRING,
    count INT8, -- This could be JSONB for the sake of consistency?
    p95 INT8, -- This could be JSONB for the sake of consistency?
    PRIMARY KEY(test, cloud, runid, ts, operation)
);