84 lines
3.9 KiB
SQL
84 lines
3.9 KiB
SQL
|
INSERT INTO osint.exhibit_types (id,name) VALUES ('party','Party');
|
||
|
|
|
||
|
|
CREATE TABLE osint.party_exhibits (
|
||
|
|
exhibit_id UUID PRIMARY KEY REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
||
|
|
party_kind TEXT NOT NULL CHECK (party_kind IN ('person','organization')),
|
||
|
|
display_name TEXT NOT NULL,
|
||
|
|
summary TEXT NOT NULL DEFAULT ''
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE osint.person_parties (
|
||
|
|
exhibit_id UUID PRIMARY KEY REFERENCES osint.party_exhibits(exhibit_id) ON DELETE CASCADE,
|
||
|
|
given_name TEXT,
|
||
|
|
family_name TEXT
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE osint.organization_parties (
|
||
|
|
exhibit_id UUID PRIMARY KEY REFERENCES osint.party_exhibits(exhibit_id) ON DELETE CASCADE,
|
||
|
|
organization_kind TEXT NOT NULL DEFAULT 'business'
|
||
|
|
CHECK (organization_kind IN ('business','public_body','association','informal_group','other'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE osint.party_aliases (
|
||
|
|
id UUID PRIMARY KEY,
|
||
|
|
party_exhibit_id UUID NOT NULL REFERENCES osint.party_exhibits(exhibit_id) ON DELETE CASCADE,
|
||
|
|
alias TEXT NOT NULL,
|
||
|
|
sort_order INTEGER NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
|
||
|
|
UNIQUE (party_exhibit_id,alias)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE osint.party_evidence (
|
||
|
|
board_id UUID NOT NULL REFERENCES osint.boards(id) ON DELETE CASCADE,
|
||
|
|
party_exhibit_id UUID NOT NULL REFERENCES osint.party_exhibits(exhibit_id) ON DELETE CASCADE,
|
||
|
|
evidence_exhibit_id UUID NOT NULL REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
||
|
|
sort_order INTEGER NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
|
||
|
|
note TEXT,
|
||
|
|
PRIMARY KEY (party_exhibit_id,evidence_exhibit_id),
|
||
|
|
CHECK (party_exhibit_id <> evidence_exhibit_id),
|
||
|
|
FOREIGN KEY (board_id,party_exhibit_id) REFERENCES osint.exhibits(board_id,id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (board_id,evidence_exhibit_id) REFERENCES osint.exhibits(board_id,id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE osint.party_relationship_types (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
name TEXT NOT NULL UNIQUE,
|
||
|
|
directed BOOLEAN NOT NULL DEFAULT TRUE
|
||
|
|
);
|
||
|
|
INSERT INTO osint.party_relationship_types (id,name,directed) VALUES
|
||
|
|
('employment','Employment',TRUE), ('ownership','Ownership',TRUE),
|
||
|
|
('membership','Membership',TRUE), ('control','Control',TRUE),
|
||
|
|
('representation','Representation',TRUE), ('associated','Associated',FALSE);
|
||
|
|
|
||
|
|
CREATE TABLE osint.party_relationships (
|
||
|
|
id UUID PRIMARY KEY,
|
||
|
|
board_id UUID NOT NULL REFERENCES osint.boards(id) ON DELETE CASCADE,
|
||
|
|
relationship_type_id TEXT NOT NULL REFERENCES osint.party_relationship_types(id),
|
||
|
|
from_party_exhibit_id UUID NOT NULL REFERENCES osint.party_exhibits(exhibit_id) ON DELETE CASCADE,
|
||
|
|
to_party_exhibit_id UUID NOT NULL REFERENCES osint.party_exhibits(exhibit_id) ON DELETE CASCADE,
|
||
|
|
note TEXT,
|
||
|
|
CHECK (from_party_exhibit_id <> to_party_exhibit_id),
|
||
|
|
FOREIGN KEY (board_id,from_party_exhibit_id) REFERENCES osint.exhibits(board_id,id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (board_id,to_party_exhibit_id) REFERENCES osint.exhibits(board_id,id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE osint.level_briefs (
|
||
|
|
board_id UUID PRIMARY KEY REFERENCES osint.boards(id) ON DELETE CASCADE,
|
||
|
|
body TEXT NOT NULL DEFAULT ''
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE osint.brief_concepts (
|
||
|
|
id UUID PRIMARY KEY,
|
||
|
|
board_id UUID NOT NULL REFERENCES osint.boards(id) ON DELETE CASCADE,
|
||
|
|
origin_concept_id UUID REFERENCES osint.brief_concepts(id) ON DELETE SET NULL,
|
||
|
|
label TEXT NOT NULL,
|
||
|
|
context_text TEXT NOT NULL DEFAULT '',
|
||
|
|
sort_order INTEGER NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
|
||
|
|
expected_party_kind TEXT CHECK (expected_party_kind IN ('person','organization')),
|
||
|
|
resolved_party_exhibit_id UUID REFERENCES osint.party_exhibits(exhibit_id) ON DELETE SET NULL,
|
||
|
|
UNIQUE (board_id,label),
|
||
|
|
FOREIGN KEY (board_id,resolved_party_exhibit_id) REFERENCES osint.exhibits(board_id,id)
|
||
|
|
);
|
||
|
|
|
||
|
|
COMMENT ON TABLE osint.brief_concepts IS 'Named concepts in the level brief which may be classified into Party exhibits by the investigator.';
|
||
|
|
COMMENT ON COLUMN osint.brief_concepts.expected_party_kind IS 'Author-only expected classification; omitted from play-mode API projections.';
|