patrick
9a24ea29e1
Git-Repository für bestehenden archivdms-Code initialisiert, Branch-/Commit-Konvention (feature/<ticket>-<slug>-Branches, Ticket-Prefix in Commit-Nachricht) etabliert.
45 lines
2.0 KiB
SQL
45 lines
2.0 KiB
SQL
-- PROJ: custom-fields
|
|
-- Doku-only (siehe README.md in diesem Verzeichnis) — die tatsächliche
|
|
-- Ausführung passiert idempotent über internal/storage/custom_fields.go
|
|
-- initCustomFieldsSchema(), aufgerufen aus (*Store).initSchema().
|
|
--
|
|
-- Benutzerdefinierte Felder (Custom Fields): tenant-skopierte Feld-
|
|
-- Definitionen, Zuordnung pro Dokumenttyp (required/visible/sort_order) und
|
|
-- die eigentlichen Werte pro Dokument. Regeln: enum-Wert liegt in value_text,
|
|
-- monetary in value_number (NUMERIC(14,2) semantisch).
|
|
|
|
CREATE TABLE IF NOT EXISTS custom_field_defs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
label TEXT NOT NULL,
|
|
field_type TEXT NOT NULL CHECK (field_type IN ('text','number','date','boolean','enum','monetary')),
|
|
enum_options JSONB,
|
|
currency TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE(tenant_id, name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS document_type_fields (
|
|
doc_type_id BIGINT NOT NULL REFERENCES document_types(id) ON DELETE CASCADE,
|
|
field_id BIGINT NOT NULL REFERENCES custom_field_defs(id) ON DELETE CASCADE,
|
|
required BOOLEAN NOT NULL DEFAULT false,
|
|
visible BOOLEAN NOT NULL DEFAULT true,
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (doc_type_id, field_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS document_field_values (
|
|
document_id BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
|
|
field_id BIGINT NOT NULL REFERENCES custom_field_defs(id) ON DELETE CASCADE,
|
|
tenant_id BIGINT NOT NULL,
|
|
value_text TEXT,
|
|
value_number NUMERIC,
|
|
value_date DATE,
|
|
value_bool BOOLEAN,
|
|
PRIMARY KEY (document_id, field_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_dfv_tenant_field ON document_field_values(tenant_id, field_id);
|
|
CREATE INDEX IF NOT EXISTS idx_dfv_field_text ON document_field_values(field_id, value_text);
|
|
CREATE INDEX IF NOT EXISTS idx_dfv_field_number ON document_field_values(field_id, value_number);
|