Files
tsumugu/backend/migrations/20261018T090000Z_correspondence.sql
LarsandCursor 3c190c87b4
Deploy Development / deploy (push) Successful in 28s
Test Suite / pytest-backend (push) Successful in 1m37s
Test Suite / smoke-dev (push) Successful in 0s
Test Suite / frontend-build (push) Successful in 9s
Halte Anlagen und Versand als eigene Verwendung fest, damit die uebermittelte Fassung und ihre Nachweise spaeter nicht still mit der Arbeitsfassung verschmelzen.
Co-authored-by: Cursor <cursoragent@cursor.com>
2026-10-18 09:57:59 +02:00

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);