59 lines
2.5 KiB
PL/PgSQL
59 lines
2.5 KiB
PL/PgSQL
BEGIN;
|
|
|
|
SET ROLE work_role;
|
|
|
|
CREATE TABLE IF NOT EXISTS work.graph_result_candidates (
|
|
result_candidate_id TEXT PRIMARY KEY,
|
|
graph_id TEXT NOT NULL,
|
|
graph_revision BIGINT NOT NULL,
|
|
node_id TEXT NOT NULL,
|
|
author_run_id TEXT NOT NULL,
|
|
artifact_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
|
|
evidence_summary TEXT NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (result_candidate_id <> ''), CHECK (graph_id <> ''), CHECK (graph_revision > 0),
|
|
CHECK (node_id <> ''), CHECK (author_run_id <> ''), CHECK (evidence_summary <> ''), CHECK (summary <> ''),
|
|
FOREIGN KEY (graph_id, graph_revision) REFERENCES work.graphs(graph_id, revision)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS work.graph_conflicts (
|
|
conflict_id TEXT PRIMARY KEY,
|
|
graph_id TEXT NOT NULL,
|
|
graph_revision BIGINT NOT NULL,
|
|
node_id TEXT NOT NULL,
|
|
candidate_ids TEXT[] NOT NULL,
|
|
resolved BOOLEAN NOT NULL DEFAULT FALSE,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (conflict_id <> ''), CHECK (graph_id <> ''), CHECK (graph_revision > 0), CHECK (node_id <> ''),
|
|
CHECK (cardinality(candidate_ids) >= 2),
|
|
FOREIGN KEY (graph_id, graph_revision) REFERENCES work.graphs(graph_id, revision)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS work.graph_synthesis_candidates (
|
|
synthesis_id TEXT PRIMARY KEY,
|
|
graph_id TEXT NOT NULL,
|
|
graph_revision BIGINT NOT NULL,
|
|
node_id TEXT NOT NULL,
|
|
candidate_ids TEXT[] NOT NULL,
|
|
selected_candidate_id TEXT NOT NULL,
|
|
dissent_candidate_ids TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
|
|
verifier_run_id TEXT NOT NULL,
|
|
verifier_identity TEXT NOT NULL,
|
|
evidence_refs TEXT[] NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (synthesis_id <> ''), CHECK (graph_id <> ''), CHECK (graph_revision > 0), CHECK (node_id <> ''),
|
|
CHECK (cardinality(candidate_ids) >= 2), CHECK (selected_candidate_id <> ''), CHECK (verifier_run_id <> ''),
|
|
CHECK (verifier_identity <> ''), CHECK (cardinality(evidence_refs) >= 1), CHECK (summary <> ''), CHECK (state = 'VERIFIED'),
|
|
FOREIGN KEY (graph_id, graph_revision) REFERENCES work.graphs(graph_id, revision)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS graph_result_candidates_node_idx ON work.graph_result_candidates(graph_id, graph_revision, node_id);
|
|
CREATE INDEX IF NOT EXISTS graph_conflicts_node_idx ON work.graph_conflicts(graph_id, graph_revision, node_id);
|
|
CREATE INDEX IF NOT EXISTS graph_synthesis_node_idx ON work.graph_synthesis_candidates(graph_id, graph_revision, node_id);
|
|
|
|
RESET ROLE;
|
|
COMMIT;
|