64 lines
3.2 KiB
SQL
64 lines
3.2 KiB
SQL
|
-- Claim-centred case reports. A Claim is a pinned board exhibit; red-thread
|
||
|
|
-- labels describe how connected source documents support it. Report acceptance
|
||
|
|
-- is level-owned history, while authored report configuration clones with boards.
|
||
|
|
|
||
|
|
INSERT INTO osint.exhibit_types (id,name,is_spatial)
|
||
|
|
VALUES ('claim','Claim',TRUE)
|
||
|
|
ON CONFLICT (id) DO NOTHING;
|
||
|
|
|
||
|
|
CREATE TABLE osint.claim_exhibits (
|
||
|
|
exhibit_id UUID PRIMARY KEY REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
||
|
|
statement TEXT NOT NULL CHECK (char_length(btrim(statement)) BETWEEN 1 AND 2000)
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE osint.document_exhibits
|
||
|
|
ADD COLUMN citation_text TEXT NOT NULL DEFAULT '';
|
||
|
|
|
||
|
|
CREATE TABLE osint.exhibit_citations (
|
||
|
|
board_id UUID NOT NULL REFERENCES osint.boards(id) ON DELETE CASCADE,
|
||
|
|
exhibit_id UUID NOT NULL REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
||
|
|
display_number INTEGER NOT NULL CHECK (display_number > 0),
|
||
|
|
PRIMARY KEY (board_id,exhibit_id),
|
||
|
|
UNIQUE (board_id,display_number),
|
||
|
|
FOREIGN KEY (board_id,exhibit_id) REFERENCES osint.exhibits(board_id,id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
INSERT INTO osint.exhibit_citations (board_id,exhibit_id,display_number)
|
||
|
|
SELECT board_id,id,ROW_NUMBER() OVER (PARTITION BY board_id ORDER BY created_at,id)::integer
|
||
|
|
FROM osint.exhibits
|
||
|
|
WHERE exhibit_type_id='document';
|
||
|
|
|
||
|
|
CREATE TABLE osint.case_reports (
|
||
|
|
board_id UUID PRIMARY KEY REFERENCES osint.boards(id) ON DELETE CASCADE,
|
||
|
|
title TEXT NOT NULL DEFAULT 'Case Report' CHECK (char_length(btrim(title)) BETWEEN 1 AND 200),
|
||
|
|
investigator_name TEXT NOT NULL DEFAULT '' CHECK (char_length(investigator_name) <= 300),
|
||
|
|
required_for_completion BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE osint.case_report_submissions (
|
||
|
|
id UUID PRIMARY KEY,
|
||
|
|
level_id UUID NOT NULL,
|
||
|
|
board_id UUID NOT NULL,
|
||
|
|
status TEXT NOT NULL CHECK (status IN ('evidence_insufficient','evidence_accepted_report_incomplete','accepted')),
|
||
|
|
investigator_name TEXT NOT NULL CHECK (char_length(btrim(investigator_name)) BETWEEN 1 AND 300),
|
||
|
|
feedback TEXT NOT NULL,
|
||
|
|
submitted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
FOREIGN KEY (level_id,board_id) REFERENCES osint.levels(id,board_id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
CREATE INDEX case_report_submissions_level_idx
|
||
|
|
ON osint.case_report_submissions (level_id,submitted_at DESC,id);
|
||
|
|
|
||
|
|
CREATE TABLE osint.case_report_submission_issues (
|
||
|
|
submission_id UUID NOT NULL REFERENCES osint.case_report_submissions(id) ON DELETE CASCADE,
|
||
|
|
issue_key TEXT NOT NULL CHECK (issue_key ~ '^[a-z][a-z0-9_.-]{0,63}$'),
|
||
|
|
PRIMARY KEY (submission_id,issue_key)
|
||
|
|
);
|
||
|
|
|
||
|
|
COMMENT ON TABLE osint.claim_exhibits IS 'Pinned propositions which can receive one or more supporting red-thread connections.';
|
||
|
|
COMMENT ON TABLE osint.exhibit_citations IS 'Stable board-local exhibit numbers used in reports independently of spatial or z-order.';
|
||
|
|
COMMENT ON TABLE osint.case_reports IS 'Clonable report configuration plus the mutable investigator byline for one board.';
|
||
|
|
COMMENT ON TABLE osint.case_report_submissions IS 'Immutable server evaluations of a mutable level report.';
|
||
|
|
COMMENT ON COLUMN osint.document_exhibits.citation_text IS 'Player-authored source/publication citation, distinct from the document title and optional source URI.';
|