59 lines
2.5 KiB
SQL
59 lines
2.5 KiB
SQL
CREATE TABLE osint.board_view_types (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE
|
|
);
|
|
INSERT INTO osint.board_view_types (id,name) VALUES ('timeline','Timeline');
|
|
|
|
CREATE TABLE osint.board_views (
|
|
id UUID PRIMARY KEY,
|
|
board_id UUID NOT NULL REFERENCES osint.boards(id) ON DELETE CASCADE,
|
|
view_type_id TEXT NOT NULL REFERENCES osint.board_view_types(id),
|
|
origin_view_id UUID REFERENCES osint.board_views(id) ON DELETE SET NULL,
|
|
placement_mode TEXT NOT NULL DEFAULT 'docked' CHECK (placement_mode IN ('docked','canvas','window')),
|
|
dock_edge TEXT CHECK (dock_edge IN ('top','right','bottom','left')),
|
|
xpos DOUBLE PRECISION,
|
|
ypos DOUBLE PRECISION,
|
|
width DOUBLE PRECISION,
|
|
height DOUBLE PRECISION NOT NULL DEFAULT 112 CHECK (height > 0),
|
|
z_index INTEGER NOT NULL DEFAULT 0,
|
|
visible BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (board_id,view_type_id),
|
|
CHECK (
|
|
(placement_mode = 'docked' AND dock_edge IS NOT NULL)
|
|
OR (placement_mode IN ('canvas','window') AND xpos IS NOT NULL AND ypos IS NOT NULL AND width IS NOT NULL)
|
|
)
|
|
);
|
|
CREATE INDEX board_views_board_idx ON osint.board_views (board_id,z_index,created_at);
|
|
|
|
CREATE TABLE osint.timeline_views (
|
|
view_id UUID PRIMARY KEY REFERENCES osint.board_views(id) ON DELETE CASCADE,
|
|
range_mode TEXT NOT NULL DEFAULT 'auto' CHECK (range_mode IN ('auto','fixed')),
|
|
range_start DATE,
|
|
range_end DATE,
|
|
CHECK (
|
|
(range_mode = 'auto' AND range_start IS NULL AND range_end IS NULL)
|
|
OR (range_mode = 'fixed' AND range_start IS NOT NULL AND range_end IS NOT NULL AND range_end > range_start)
|
|
)
|
|
);
|
|
|
|
WITH created_views AS (
|
|
INSERT INTO osint.board_views (id,board_id,view_type_id,placement_mode,dock_edge,height)
|
|
SELECT gen_random_uuid(),b.id,'timeline','docked','bottom',112
|
|
FROM osint.boards b
|
|
RETURNING id,board_id
|
|
)
|
|
INSERT INTO osint.timeline_views (view_id,range_mode,range_start,range_end)
|
|
SELECT v.id,
|
|
CASE WHEN settings.board_id IS NULL THEN 'auto' ELSE 'fixed' END,
|
|
settings.range_start,
|
|
settings.range_end
|
|
FROM created_views v
|
|
LEFT JOIN osint.board_timeline_settings settings ON settings.board_id=v.board_id;
|
|
|
|
DROP TABLE osint.board_timeline_settings;
|
|
|
|
COMMENT ON TABLE osint.board_views IS 'Persistent frontend projections and workspace apparatus. Views are not investigation-domain exhibits.';
|
|
COMMENT ON TABLE osint.timeline_views IS 'Timeline projection settings; temporal markers remain derived from exhibits and are never duplicated here.';
|