Deploy Development / deploy (push) Successful in 23s
Anmeldung, Datenraeume, Akten, Vorgaenge, gemeinsame Notizen, Dateien, manuelle Paperless-Referenzen, Aufgaben und unabhaengige Fristen laufen damit in der Entwicklungsumgebung. Co-authored-by: Cursor <cursoragent@cursor.com>
357 lines
14 KiB
SQL
357 lines
14 KiB
SQL
-- Erster Fachschnitt: Identität, Struktur, Inhalte, Nachverfolgung, eigene Dateien.
|
|
-- Finanz-, Zusammenstellungs- und Paperless-Abgleichstabellen gehören nicht in diese Lieferung.
|
|
|
|
CREATE TABLE auth_accounts (
|
|
id uuid PRIMARY KEY,
|
|
email text NOT NULL UNIQUE,
|
|
display_name text NOT NULL,
|
|
password_hash text NOT NULL,
|
|
status text NOT NULL DEFAULT 'active',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT auth_accounts_status CHECK (status IN ('active', 'disabled')),
|
|
CONSTRAINT auth_accounts_email_len CHECK (char_length(email) <= 320),
|
|
CONSTRAINT auth_accounts_name_len CHECK (char_length(display_name) BETWEEN 1 AND 120)
|
|
);
|
|
|
|
CREATE TABLE data_rooms (
|
|
id uuid PRIMARY KEY,
|
|
kind text NOT NULL,
|
|
name text NOT NULL,
|
|
status text NOT NULL DEFAULT 'active',
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT data_rooms_kind CHECK (kind IN ('personal', 'organization')),
|
|
CONSTRAINT data_rooms_status CHECK (status IN ('active', 'archived')),
|
|
CONSTRAINT data_rooms_name_len CHECK (char_length(name) BETWEEN 1 AND 160)
|
|
);
|
|
|
|
CREATE TABLE auth_sessions (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
account_id uuid NOT NULL REFERENCES auth_accounts (id),
|
|
secret_hash text NOT NULL UNIQUE,
|
|
csrf_token text NOT NULL,
|
|
scope_kind text NOT NULL,
|
|
scope_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
epoch integer NOT NULL DEFAULT 1,
|
|
expires_at timestamptz NOT NULL,
|
|
revoked_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT auth_sessions_scope_kind CHECK (scope_kind IN ('personal', 'organization'))
|
|
);
|
|
|
|
CREATE INDEX auth_sessions_account ON auth_sessions (account_id);
|
|
|
|
CREATE TABLE auth_invitations (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email text NOT NULL,
|
|
room_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
token_hash text NOT NULL UNIQUE,
|
|
invited_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
expires_at timestamptz NOT NULL,
|
|
accepted_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE room_memberships (
|
|
room_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
subject_id uuid NOT NULL REFERENCES auth_accounts (id),
|
|
role text NOT NULL,
|
|
status text NOT NULL DEFAULT 'active',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (room_id, subject_id),
|
|
CONSTRAINT room_memberships_role CHECK (role IN ('member', 'manager')),
|
|
CONSTRAINT room_memberships_status CHECK (status IN ('active', 'removed'))
|
|
);
|
|
|
|
CREATE TABLE cases (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
home_scope_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
owner_subject_id uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
version integer NOT NULL DEFAULT 1,
|
|
title text NOT NULL,
|
|
parent_case_id uuid,
|
|
rights_mode text NOT NULL DEFAULT 'explicit',
|
|
record_status text NOT NULL DEFAULT 'open',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
deleted_at timestamptz,
|
|
CONSTRAINT cases_title_len CHECK (char_length(title) BETWEEN 1 AND 200),
|
|
CONSTRAINT cases_rights_mode CHECK (rights_mode IN ('inherit_parent', 'explicit')),
|
|
CONSTRAINT cases_status CHECK (record_status IN ('open', 'closed')),
|
|
CONSTRAINT cases_root_explicit CHECK (
|
|
(parent_case_id IS NULL AND rights_mode = 'explicit')
|
|
OR parent_case_id IS NOT NULL
|
|
),
|
|
UNIQUE (id, home_scope_id)
|
|
);
|
|
|
|
ALTER TABLE cases
|
|
ADD CONSTRAINT cases_parent_same_scope
|
|
FOREIGN KEY (parent_case_id, home_scope_id) REFERENCES cases (id, home_scope_id);
|
|
|
|
CREATE INDEX cases_owner ON cases (owner_subject_id) WHERE deleted_at IS NULL;
|
|
CREATE INDEX cases_home ON cases (home_scope_id) WHERE deleted_at IS NULL;
|
|
|
|
CREATE TABLE matters (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
home_scope_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
owner_subject_id uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
version integer NOT NULL DEFAULT 1,
|
|
title text NOT NULL,
|
|
parent_matter_id uuid,
|
|
record_status text NOT NULL DEFAULT 'open',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
deleted_at timestamptz,
|
|
CONSTRAINT matters_title_len CHECK (char_length(title) BETWEEN 1 AND 200),
|
|
CONSTRAINT matters_status CHECK (record_status IN ('open', 'closed')),
|
|
UNIQUE (id, home_scope_id)
|
|
);
|
|
|
|
ALTER TABLE matters
|
|
ADD CONSTRAINT matters_parent_same_scope
|
|
FOREIGN KEY (parent_matter_id, home_scope_id) REFERENCES matters (id, home_scope_id);
|
|
|
|
CREATE INDEX matters_owner ON matters (owner_subject_id) WHERE deleted_at IS NULL;
|
|
|
|
CREATE TABLE chapters (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
home_scope_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
case_id uuid,
|
|
matter_id uuid,
|
|
title text NOT NULL,
|
|
position integer NOT NULL DEFAULT 0,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT chapters_title_len CHECK (char_length(title) BETWEEN 1 AND 200),
|
|
CONSTRAINT chapters_one_container CHECK ((case_id IS NULL) <> (matter_id IS NULL))
|
|
);
|
|
|
|
ALTER TABLE chapters
|
|
ADD CONSTRAINT chapters_case_same_scope
|
|
FOREIGN KEY (case_id, home_scope_id) REFERENCES cases (id, home_scope_id);
|
|
|
|
ALTER TABLE chapters
|
|
ADD CONSTRAINT chapters_matter_same_scope
|
|
FOREIGN KEY (matter_id, home_scope_id) REFERENCES matters (id, home_scope_id);
|
|
|
|
CREATE TABLE matter_case_links (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
matter_id uuid NOT NULL REFERENCES matters (id),
|
|
case_id uuid NOT NULL REFERENCES cases (id),
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (matter_id, case_id)
|
|
);
|
|
|
|
CREATE INDEX matter_case_links_case ON matter_case_links (case_id);
|
|
|
|
CREATE TABLE case_link_blocks (
|
|
link_id uuid NOT NULL REFERENCES matter_case_links (id) ON DELETE CASCADE,
|
|
element_id uuid NOT NULL,
|
|
PRIMARY KEY (link_id, element_id)
|
|
);
|
|
|
|
CREATE TABLE matter_cross_links (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
from_matter_id uuid NOT NULL REFERENCES matters (id),
|
|
to_matter_id uuid NOT NULL REFERENCES matters (id),
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (from_matter_id, to_matter_id),
|
|
CONSTRAINT matter_cross_links_distinct CHECK (from_matter_id <> to_matter_id)
|
|
);
|
|
|
|
CREATE TABLE elements (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
home_scope_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
owner_subject_id uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
version integer NOT NULL DEFAULT 1,
|
|
title text NOT NULL,
|
|
kind text NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
deleted_at timestamptz,
|
|
CONSTRAINT elements_title_len CHECK (char_length(title) BETWEEN 1 AND 200),
|
|
CONSTRAINT elements_kind CHECK (kind IN ('note', 'file', 'external_ref')),
|
|
UNIQUE (id, home_scope_id)
|
|
);
|
|
|
|
CREATE INDEX elements_owner ON elements (owner_subject_id) WHERE deleted_at IS NULL;
|
|
|
|
CREATE TABLE note_bodies (
|
|
element_id uuid PRIMARY KEY REFERENCES elements (id),
|
|
document_json jsonb NOT NULL,
|
|
version integer NOT NULL DEFAULT 1,
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE correspondence_dates (
|
|
element_id uuid PRIMARY KEY REFERENCES elements (id),
|
|
document_on date,
|
|
sent_on date,
|
|
received_on date
|
|
);
|
|
|
|
CREATE TABLE external_references (
|
|
element_id uuid PRIMARY KEY REFERENCES elements (id),
|
|
source text NOT NULL,
|
|
external_id text NOT NULL,
|
|
CONSTRAINT external_references_source CHECK (source IN ('paperless', 'url')),
|
|
CONSTRAINT external_references_id_len CHECK (char_length(external_id) BETWEEN 1 AND 200)
|
|
);
|
|
|
|
CREATE TABLE bindings (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
element_id uuid NOT NULL REFERENCES elements (id),
|
|
context_kind text NOT NULL,
|
|
context_id uuid NOT NULL,
|
|
chapter_id uuid REFERENCES chapters (id),
|
|
position integer NOT NULL DEFAULT 0,
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (element_id, context_kind, context_id),
|
|
CONSTRAINT bindings_context_kind CHECK (context_kind IN ('case', 'matter'))
|
|
);
|
|
|
|
CREATE INDEX bindings_context ON bindings (context_kind, context_id);
|
|
|
|
CREATE TABLE context_annotations (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
binding_id uuid NOT NULL REFERENCES bindings (id) ON DELETE CASCADE,
|
|
body text NOT NULL,
|
|
author_subject_id uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT context_annotations_body CHECK (char_length(body) BETWEEN 1 AND 4000)
|
|
);
|
|
|
|
CREATE TABLE access_grants (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
grantee_subject_id uuid REFERENCES auth_accounts (id),
|
|
resource_kind text NOT NULL,
|
|
resource_id uuid NOT NULL,
|
|
context_kind text,
|
|
context_id uuid,
|
|
can_read boolean NOT NULL DEFAULT false,
|
|
can_edit boolean NOT NULL DEFAULT false,
|
|
can_share boolean NOT NULL DEFAULT false,
|
|
can_delete boolean NOT NULL DEFAULT false,
|
|
is_exception boolean NOT NULL DEFAULT false,
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT access_grants_kind CHECK (resource_kind IN ('case', 'matter', 'element', 'task', 'deadline')),
|
|
CONSTRAINT access_grants_context_pair CHECK ((context_kind IS NULL) = (context_id IS NULL)),
|
|
CONSTRAINT access_grants_context_kind CHECK (context_kind IS NULL OR context_kind IN ('case', 'matter')),
|
|
CONSTRAINT access_grants_grantee CHECK (is_exception OR grantee_subject_id IS NOT NULL),
|
|
CONSTRAINT access_grants_exception_flag CHECK (
|
|
NOT is_exception OR can_read OR can_edit OR can_share OR can_delete
|
|
)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX access_grants_identity ON access_grants (
|
|
resource_kind,
|
|
resource_id,
|
|
COALESCE(grantee_subject_id, '00000000-0000-0000-0000-000000000000'::uuid),
|
|
COALESCE(context_kind, ''),
|
|
COALESCE(context_id, '00000000-0000-0000-0000-000000000000'::uuid),
|
|
is_exception
|
|
);
|
|
|
|
CREATE INDEX access_grants_grantee ON access_grants (grantee_subject_id);
|
|
CREATE INDEX access_grants_resource ON access_grants (resource_kind, resource_id);
|
|
|
|
CREATE TABLE tasks (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
home_scope_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
owner_subject_id uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
version integer NOT NULL DEFAULT 1,
|
|
title text NOT NULL,
|
|
matter_id uuid REFERENCES matters (id),
|
|
assignee_subject_id uuid REFERENCES auth_accounts (id),
|
|
due_on date,
|
|
task_status text NOT NULL DEFAULT 'open',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
deleted_at timestamptz,
|
|
CONSTRAINT tasks_title_len CHECK (char_length(title) BETWEEN 1 AND 200),
|
|
CONSTRAINT tasks_status CHECK (task_status IN ('open', 'done'))
|
|
);
|
|
|
|
CREATE INDEX tasks_open ON tasks (task_status) WHERE deleted_at IS NULL;
|
|
|
|
CREATE TABLE deadlines (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
home_scope_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
owner_subject_id uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
version integer NOT NULL DEFAULT 1,
|
|
title text NOT NULL,
|
|
matter_id uuid REFERENCES matters (id),
|
|
element_id uuid REFERENCES elements (id),
|
|
assignee_subject_id uuid REFERENCES auth_accounts (id),
|
|
due_on date NOT NULL,
|
|
deadline_status text NOT NULL DEFAULT 'open',
|
|
fulfilled_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
deleted_at timestamptz,
|
|
CONSTRAINT deadlines_title_len CHECK (char_length(title) BETWEEN 1 AND 200),
|
|
CONSTRAINT deadlines_status CHECK (deadline_status IN ('open', 'fulfilled'))
|
|
);
|
|
|
|
CREATE INDEX deadlines_open ON deadlines (deadline_status) WHERE deleted_at IS NULL;
|
|
|
|
CREATE TABLE task_deadline_links (
|
|
task_id uuid NOT NULL REFERENCES tasks (id),
|
|
deadline_id uuid NOT NULL REFERENCES deadlines (id),
|
|
created_by uuid NOT NULL REFERENCES auth_accounts (id),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (task_id, deadline_id)
|
|
);
|
|
|
|
CREATE TABLE media_assets (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
element_id uuid NOT NULL UNIQUE REFERENCES elements (id),
|
|
owner_subject_id uuid NOT NULL REFERENCES auth_accounts (id),
|
|
home_scope_id uuid NOT NULL REFERENCES data_rooms (id),
|
|
original_filename text NOT NULL,
|
|
content_type text NOT NULL,
|
|
byte_size bigint NOT NULL,
|
|
sha256 text NOT NULL,
|
|
revision integer NOT NULL DEFAULT 1,
|
|
status text NOT NULL DEFAULT 'active',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT media_assets_status CHECK (status IN ('active', 'purging', 'purged')),
|
|
CONSTRAINT media_assets_filename CHECK (char_length(original_filename) BETWEEN 1 AND 240)
|
|
);
|
|
|
|
CREATE TABLE media_operations (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
element_id uuid,
|
|
asset_id uuid,
|
|
phase text NOT NULL,
|
|
status text NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE domain_events (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
actor_subject_id uuid REFERENCES auth_accounts (id),
|
|
scope_id uuid,
|
|
action text NOT NULL,
|
|
resource_kind text NOT NULL,
|
|
resource_id uuid NOT NULL,
|
|
context_kind text,
|
|
context_id uuid,
|
|
occurred_on date,
|
|
occurred_at timestamptz NOT NULL DEFAULT now(),
|
|
result text NOT NULL DEFAULT 'ok'
|
|
);
|
|
|
|
CREATE INDEX domain_events_resource ON domain_events (resource_kind, resource_id);
|
|
CREATE INDEX domain_events_context ON domain_events (context_kind, context_id);
|