148 lines
6.1 KiB
SQL
148 lines
6.1 KiB
SQL
-- Anlagenverwendung, dokumentierter Versand und Arbeitsansicht.
|
|
-- Bestehende Kapitel, Einbindungen und Dateien bleiben unverändert.
|
|
|
|
ALTER TABLE cases
|
|
ADD COLUMN content_view text NOT NULL DEFAULT 'structure';
|
|
|
|
ALTER TABLE cases
|
|
ADD CONSTRAINT cases_content_view
|
|
CHECK (content_view IN ('structure', 'chronology'));
|
|
|
|
ALTER TABLE matters
|
|
ADD COLUMN content_view text NOT NULL DEFAULT 'structure';
|
|
|
|
ALTER TABLE matters
|
|
ADD CONSTRAINT matters_content_view
|
|
CHECK (content_view IN ('structure', 'chronology'));
|
|
|
|
ALTER TABLE bindings
|
|
ADD COLUMN list_fold text;
|
|
|
|
ALTER TABLE bindings
|
|
ADD CONSTRAINT bindings_list_fold
|
|
CHECK (list_fold IS NULL OR list_fold IN ('attachment', 'evidence'));
|
|
|
|
CREATE TABLE attachment_uses (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
home_scope_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
context_kind text NOT NULL,
|
|
context_id uuid NOT NULL,
|
|
host_element_id uuid NOT NULL REFERENCES elements (id) ON DELETE CASCADE,
|
|
attachment_element_id uuid NOT NULL REFERENCES elements (id) ON DELETE CASCADE,
|
|
position integer NOT NULL,
|
|
label text,
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT attachment_uses_kind CHECK (context_kind IN ('case', 'matter')),
|
|
CONSTRAINT attachment_uses_not_self CHECK (host_element_id <> attachment_element_id),
|
|
CONSTRAINT attachment_uses_position CHECK (position >= 0),
|
|
CONSTRAINT attachment_uses_label CHECK (label IS NULL OR char_length(label) BETWEEN 1 AND 200),
|
|
CONSTRAINT attachment_uses_once UNIQUE (context_kind, context_id, host_element_id, attachment_element_id)
|
|
);
|
|
|
|
CREATE INDEX attachment_uses_host
|
|
ON attachment_uses (context_kind, context_id, host_element_id, position);
|
|
|
|
CREATE TABLE dispatches (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
home_scope_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
context_kind text NOT NULL,
|
|
context_id uuid NOT NULL,
|
|
host_element_id uuid NOT NULL REFERENCES elements (id) ON DELETE CASCADE,
|
|
channel text NOT NULL,
|
|
sent_on date,
|
|
counterpart text,
|
|
completeness text NOT NULL,
|
|
provenance text NOT NULL,
|
|
recorded_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
recorded_at timestamptz NOT NULL DEFAULT now(),
|
|
version integer NOT NULL DEFAULT 1,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT dispatches_kind CHECK (context_kind IN ('case', 'matter')),
|
|
CONSTRAINT dispatches_channel CHECK (channel IN ('email', 'post', 'other')),
|
|
CONSTRAINT dispatches_completeness CHECK (completeness IN ('secured', 'partial', 'gap')),
|
|
CONSTRAINT dispatches_provenance CHECK (provenance IN ('captured', 'reconstructed')),
|
|
CONSTRAINT dispatches_counterpart CHECK (counterpart IS NULL OR char_length(counterpart) BETWEEN 1 AND 200),
|
|
CONSTRAINT dispatches_version CHECK (version >= 1)
|
|
);
|
|
|
|
CREATE INDEX dispatches_host
|
|
ON dispatches (context_kind, context_id, host_element_id, recorded_at);
|
|
|
|
CREATE TABLE dispatch_items (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
dispatch_id uuid NOT NULL REFERENCES dispatches (id) ON DELETE CASCADE,
|
|
role text NOT NULL,
|
|
position integer NOT NULL,
|
|
element_id uuid NOT NULL REFERENCES elements (id) ON DELETE CASCADE,
|
|
title text NOT NULL,
|
|
document_on date,
|
|
filename text,
|
|
content_type text,
|
|
byte_size bigint,
|
|
content_sha256 text,
|
|
source_state text NOT NULL,
|
|
gap_reason text,
|
|
note_document jsonb,
|
|
external_source text,
|
|
external_id text,
|
|
CONSTRAINT dispatch_items_role CHECK (role IN ('host', 'attachment')),
|
|
CONSTRAINT dispatch_items_position CHECK (position >= 0),
|
|
CONSTRAINT dispatch_items_state CHECK (source_state IN ('snapshot', 'gap')),
|
|
CONSTRAINT dispatch_items_once UNIQUE (dispatch_id, element_id)
|
|
);
|
|
|
|
CREATE INDEX dispatch_items_dispatch ON dispatch_items (dispatch_id, position);
|
|
|
|
CREATE TABLE dispatch_snapshots (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
dispatch_item_id uuid UNIQUE REFERENCES dispatch_items (id) ON DELETE CASCADE,
|
|
evidence_id uuid,
|
|
home_scope_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
owner_subject_id uuid NOT NULL REFERENCES auth_accounts (id),
|
|
original_filename text NOT NULL,
|
|
content_type text NOT NULL,
|
|
byte_size bigint NOT NULL,
|
|
sha256 text NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT dispatch_snapshots_filename CHECK (char_length(original_filename) BETWEEN 1 AND 240),
|
|
CONSTRAINT dispatch_snapshots_target CHECK (
|
|
(dispatch_item_id IS NOT NULL AND evidence_id IS NULL)
|
|
OR (dispatch_item_id IS NULL AND evidence_id IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
CREATE TABLE dispatch_evidence (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
dispatch_id uuid NOT NULL REFERENCES dispatches (id) ON DELETE CASCADE,
|
|
element_id uuid NOT NULL REFERENCES elements (id) ON DELETE CASCADE,
|
|
role text NOT NULL,
|
|
source_state text NOT NULL,
|
|
gap_reason text,
|
|
message_id text,
|
|
identity_sha256 text,
|
|
comparisons jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
manual boolean NOT NULL DEFAULT true,
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT dispatch_evidence_role CHECK (role IN ('email', 'submission', 'delivery', 'other')),
|
|
CONSTRAINT dispatch_evidence_state CHECK (source_state IN ('identified', 'gap')),
|
|
CONSTRAINT dispatch_evidence_once UNIQUE (dispatch_id, element_id)
|
|
);
|
|
|
|
ALTER TABLE dispatch_snapshots
|
|
ADD CONSTRAINT dispatch_snapshots_evidence_fk
|
|
FOREIGN KEY (evidence_id) REFERENCES dispatch_evidence (id) ON DELETE CASCADE;
|
|
|
|
CREATE TABLE dispatch_corrections (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
dispatch_id uuid NOT NULL REFERENCES dispatches (id) ON DELETE CASCADE,
|
|
corrected_at timestamptz NOT NULL DEFAULT now(),
|
|
corrected_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
summary text NOT NULL,
|
|
CONSTRAINT dispatch_corrections_summary CHECK (char_length(summary) BETWEEN 1 AND 500)
|
|
);
|
|
|
|
CREATE INDEX dispatch_corrections_dispatch ON dispatch_corrections (dispatch_id, corrected_at);
|