262 lines
11 KiB
SQL
262 lines
11 KiB
SQL
-- Canonical exhibit model. The POC data predating this migration is disposable:
|
|
-- there is deliberately no compatibility view, backfill, or playthrough overlay.
|
|
DROP TABLE IF EXISTS osint.playthrough_widget_relation_state CASCADE;
|
|
DROP TABLE IF EXISTS osint.playthrough_widget_relations CASCADE;
|
|
DROP TABLE IF EXISTS osint.playthrough_widget_state CASCADE;
|
|
DROP TABLE IF EXISTS osint.playthrough_connections CASCADE;
|
|
DROP TABLE IF EXISTS osint.playthrough_widgets CASCADE;
|
|
DROP TABLE IF EXISTS osint.playthroughs CASCADE;
|
|
DROP TABLE IF EXISTS osint.level_connections CASCADE;
|
|
DROP TABLE IF EXISTS osint.widget_regions CASCADE;
|
|
DROP TABLE IF EXISTS osint.widget_relations CASCADE;
|
|
DROP TABLE IF EXISTS osint.widgets CASCADE;
|
|
DROP TABLE IF EXISTS osint.assets CASCADE;
|
|
DROP TABLE IF EXISTS osint.levels CASCADE;
|
|
DROP TABLE IF EXISTS osint.cases CASCADE;
|
|
|
|
CREATE TABLE osint.boards (
|
|
id UUID PRIMARY KEY,
|
|
board_kind TEXT NOT NULL CHECK (board_kind IN ('level', 'template_version')),
|
|
revision BIGINT NOT NULL DEFAULT 0 CHECK (revision >= 0),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE osint.assets (
|
|
id UUID PRIMARY KEY,
|
|
original_name TEXT NOT NULL,
|
|
mime_type TEXT NOT NULL,
|
|
byte_size BIGINT NOT NULL CHECK (byte_size >= 0),
|
|
content BYTEA NOT NULL,
|
|
checksum_sha256 TEXT NOT NULL CHECK (checksum_sha256 ~ '^[0-9a-f]{64}$'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (checksum_sha256, byte_size)
|
|
);
|
|
|
|
CREATE TABLE osint.level_templates (
|
|
id UUID PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
current_version_id UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE osint.level_template_versions (
|
|
id UUID PRIMARY KEY,
|
|
template_id UUID NOT NULL REFERENCES osint.level_templates(id) ON DELETE CASCADE,
|
|
version INTEGER NOT NULL CHECK (version > 0),
|
|
board_id UUID NOT NULL UNIQUE REFERENCES osint.boards(id) ON DELETE RESTRICT,
|
|
title TEXT NOT NULL,
|
|
subtitle TEXT NOT NULL DEFAULT '',
|
|
created_from_level_id UUID,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (template_id, version)
|
|
);
|
|
|
|
CREATE TABLE osint.levels (
|
|
id UUID PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
board_id UUID NOT NULL UNIQUE REFERENCES osint.boards(id) ON DELETE CASCADE,
|
|
source_template_version_id UUID REFERENCES osint.level_template_versions(id) ON DELETE RESTRICT,
|
|
title TEXT NOT NULL,
|
|
subtitle TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'complete', 'archived')),
|
|
viewport_x DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
viewport_y DOUBLE PRECISION NOT NULL DEFAULT 28,
|
|
viewport_zoom DOUBLE PRECISION NOT NULL DEFAULT 0.7 CHECK (viewport_zoom > 0),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
ALTER TABLE osint.level_templates
|
|
ADD CONSTRAINT level_templates_current_version_fk
|
|
FOREIGN KEY (current_version_id) REFERENCES osint.level_template_versions(id) ON DELETE SET NULL;
|
|
ALTER TABLE osint.level_template_versions
|
|
ADD CONSTRAINT level_template_versions_source_level_fk
|
|
FOREIGN KEY (created_from_level_id) REFERENCES osint.levels(id) ON DELETE SET NULL;
|
|
|
|
CREATE TABLE osint.exhibit_types (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
is_spatial BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
INSERT INTO osint.exhibit_types (id, name) VALUES
|
|
('folder', 'Folder'), ('document', 'Document'), ('note', 'Note'), ('event', 'Event');
|
|
|
|
CREATE TABLE osint.exhibits (
|
|
id UUID PRIMARY KEY,
|
|
board_id UUID NOT NULL REFERENCES osint.boards(id) ON DELETE CASCADE,
|
|
exhibit_type_id TEXT NOT NULL REFERENCES osint.exhibit_types(id),
|
|
origin_exhibit_id UUID REFERENCES osint.exhibits(id) ON DELETE SET NULL,
|
|
xpos DOUBLE PRECISION NOT NULL DEFAULT 100,
|
|
ypos DOUBLE PRECISION NOT NULL DEFAULT 100,
|
|
width DOUBLE PRECISION NOT NULL DEFAULT 240 CHECK (width > 0),
|
|
height DOUBLE PRECISION NOT NULL DEFAULT 160 CHECK (height > 0),
|
|
rotation DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
z_index INTEGER NOT NULL DEFAULT 0,
|
|
hidden BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (board_id, id)
|
|
);
|
|
CREATE INDEX exhibits_board_idx ON osint.exhibits (board_id, z_index, created_at);
|
|
|
|
CREATE TABLE osint.folder_exhibits (
|
|
exhibit_id UUID PRIMARY KEY REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
label_text TEXT NOT NULL DEFAULT '',
|
|
is_open BOOLEAN NOT NULL DEFAULT FALSE
|
|
);
|
|
|
|
CREATE TABLE osint.document_types (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE
|
|
);
|
|
INSERT INTO osint.document_types (id, name) VALUES
|
|
('image', 'Image'), ('pdf', 'PDF'), ('web_capture', 'Web capture'),
|
|
('email', 'Email'), ('article', 'Article'), ('filing', 'Filing'),
|
|
('price_list', 'Price list'), ('text', 'Text'), ('file', 'Generic file');
|
|
|
|
CREATE TABLE osint.document_exhibits (
|
|
exhibit_id UUID PRIMARY KEY REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
|
document_type_id TEXT NOT NULL REFERENCES osint.document_types(id),
|
|
asset_id UUID REFERENCES osint.assets(id) ON DELETE RESTRICT,
|
|
title TEXT NOT NULL,
|
|
published_at TIMESTAMPTZ,
|
|
captured_at TIMESTAMPTZ,
|
|
source_uri TEXT
|
|
);
|
|
CREATE INDEX document_exhibits_published_idx ON osint.document_exhibits (published_at);
|
|
|
|
CREATE TABLE osint.image_documents (
|
|
exhibit_id UUID PRIMARY KEY REFERENCES osint.document_exhibits(exhibit_id) ON DELETE CASCADE,
|
|
pixel_width INTEGER CHECK (pixel_width > 0),
|
|
pixel_height INTEGER CHECK (pixel_height > 0),
|
|
alt_text TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE osint.note_exhibits (
|
|
exhibit_id UUID PRIMARY KEY REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
note_text TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE osint.event_exhibits (
|
|
exhibit_id UUID PRIMARY KEY REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
narrative_text TEXT NOT NULL DEFAULT '',
|
|
occurred_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
CREATE TABLE osint.document_content_blocks (
|
|
id UUID PRIMARY KEY,
|
|
document_exhibit_id UUID NOT NULL REFERENCES osint.document_exhibits(exhibit_id) ON DELETE CASCADE,
|
|
sort_order INTEGER NOT NULL CHECK (sort_order >= 0),
|
|
content TEXT NOT NULL,
|
|
UNIQUE (document_exhibit_id, sort_order)
|
|
);
|
|
|
|
CREATE TABLE osint.document_regions (
|
|
id UUID PRIMARY KEY,
|
|
document_exhibit_id UUID NOT NULL REFERENCES osint.document_exhibits(exhibit_id) ON DELETE CASCADE,
|
|
region_key TEXT NOT NULL,
|
|
label TEXT NOT NULL,
|
|
excerpt TEXT NOT NULL,
|
|
occurred_at TIMESTAMPTZ,
|
|
sort_order INTEGER NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
|
|
UNIQUE (document_exhibit_id, region_key)
|
|
);
|
|
|
|
CREATE TABLE osint.folder_memberships (
|
|
board_id UUID NOT NULL REFERENCES osint.boards(id) ON DELETE CASCADE,
|
|
folder_exhibit_id UUID NOT NULL REFERENCES osint.folder_exhibits(exhibit_id) ON DELETE CASCADE,
|
|
child_exhibit_id UUID NOT NULL REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
|
sort_order INTEGER NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
|
|
PRIMARY KEY (folder_exhibit_id, child_exhibit_id),
|
|
UNIQUE (board_id, child_exhibit_id),
|
|
CHECK (folder_exhibit_id <> child_exhibit_id)
|
|
);
|
|
|
|
CREATE TABLE osint.event_evidence (
|
|
board_id UUID NOT NULL REFERENCES osint.boards(id) ON DELETE CASCADE,
|
|
event_exhibit_id UUID NOT NULL REFERENCES osint.event_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 (event_exhibit_id, evidence_exhibit_id),
|
|
CHECK (event_exhibit_id <> evidence_exhibit_id)
|
|
);
|
|
|
|
CREATE TABLE osint.connection_types (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
directed BOOLEAN NOT NULL DEFAULT FALSE
|
|
);
|
|
INSERT INTO osint.connection_types (id, name) VALUES ('thread', 'Red thread');
|
|
|
|
CREATE TABLE osint.exhibit_connections (
|
|
id UUID PRIMARY KEY,
|
|
board_id UUID NOT NULL REFERENCES osint.boards(id) ON DELETE CASCADE,
|
|
connection_type_id TEXT NOT NULL REFERENCES osint.connection_types(id),
|
|
from_exhibit_id UUID NOT NULL REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
|
to_exhibit_id UUID NOT NULL REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
|
label TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CHECK (from_exhibit_id <> to_exhibit_id)
|
|
);
|
|
|
|
CREATE TABLE osint.exhibit_sources (
|
|
exhibit_id UUID PRIMARY KEY REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
|
source_document_exhibit_id UUID NOT NULL REFERENCES osint.document_exhibits(exhibit_id) ON DELETE CASCADE,
|
|
source_region_id UUID REFERENCES osint.document_regions(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE osint.metadata_fields (
|
|
id UUID PRIMARY KEY,
|
|
board_id UUID NOT NULL REFERENCES osint.boards(id) ON DELETE CASCADE,
|
|
field_key TEXT NOT NULL,
|
|
label TEXT NOT NULL,
|
|
value_type TEXT NOT NULL CHECK (value_type IN ('text', 'timestamp', 'number', 'boolean')),
|
|
UNIQUE (board_id, field_key)
|
|
);
|
|
CREATE TABLE osint.exhibit_metadata_text_values (
|
|
exhibit_id UUID NOT NULL REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
|
field_id UUID NOT NULL REFERENCES osint.metadata_fields(id) ON DELETE CASCADE,
|
|
value TEXT NOT NULL,
|
|
PRIMARY KEY (exhibit_id, field_id)
|
|
);
|
|
CREATE TABLE osint.exhibit_metadata_timestamp_values (
|
|
exhibit_id UUID NOT NULL REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
|
field_id UUID NOT NULL REFERENCES osint.metadata_fields(id) ON DELETE CASCADE,
|
|
value TIMESTAMPTZ NOT NULL,
|
|
PRIMARY KEY (exhibit_id, field_id)
|
|
);
|
|
CREATE TABLE osint.exhibit_metadata_number_values (
|
|
exhibit_id UUID NOT NULL REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
|
field_id UUID NOT NULL REFERENCES osint.metadata_fields(id) ON DELETE CASCADE,
|
|
value NUMERIC NOT NULL,
|
|
PRIMARY KEY (exhibit_id, field_id)
|
|
);
|
|
CREATE TABLE osint.exhibit_metadata_boolean_values (
|
|
exhibit_id UUID NOT NULL REFERENCES osint.exhibits(id) ON DELETE CASCADE,
|
|
field_id UUID NOT NULL REFERENCES osint.metadata_fields(id) ON DELETE CASCADE,
|
|
value BOOLEAN NOT NULL,
|
|
PRIMARY KEY (exhibit_id, field_id)
|
|
);
|
|
|
|
-- Relationships carry board_id so cross-board references can be rejected by FKs.
|
|
ALTER TABLE osint.folder_memberships
|
|
ADD CONSTRAINT folder_membership_folder_board_fk FOREIGN KEY (board_id, folder_exhibit_id) REFERENCES osint.exhibits(board_id, id) ON DELETE CASCADE,
|
|
ADD CONSTRAINT folder_membership_child_board_fk FOREIGN KEY (board_id, child_exhibit_id) REFERENCES osint.exhibits(board_id, id) ON DELETE CASCADE;
|
|
ALTER TABLE osint.event_evidence
|
|
ADD CONSTRAINT event_evidence_event_board_fk FOREIGN KEY (board_id, event_exhibit_id) REFERENCES osint.exhibits(board_id, id) ON DELETE CASCADE,
|
|
ADD CONSTRAINT event_evidence_evidence_board_fk FOREIGN KEY (board_id, evidence_exhibit_id) REFERENCES osint.exhibits(board_id, id) ON DELETE CASCADE;
|
|
ALTER TABLE osint.exhibit_connections
|
|
ADD CONSTRAINT exhibit_connections_from_board_fk FOREIGN KEY (board_id, from_exhibit_id) REFERENCES osint.exhibits(board_id, id) ON DELETE CASCADE,
|
|
ADD CONSTRAINT exhibit_connections_to_board_fk FOREIGN KEY (board_id, to_exhibit_id) REFERENCES osint.exhibits(board_id, id) ON DELETE CASCADE;
|
|
|
|
COMMENT ON TABLE osint.exhibits IS 'Canonical domain objects; frontend widgets are projections selected by exhibit_type_id.';
|
|
COMMENT ON TABLE osint.level_template_versions IS 'Immutable template snapshots. Application code must clone, never update, their boards.';
|
|
COMMENT ON TABLE osint.assets IS 'Immutable shared binary content referenced by document exhibits.';
|