60 lines
2.2 KiB
MySQL
60 lines
2.2 KiB
MySQL
|
-- AP1.10b: Entity Field System (EFS) — Archetypen, Felddefinitionen, Werte
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS entity_archetypes (
|
||
|
|
archetype_key VARCHAR(64) PRIMARY KEY,
|
||
|
|
entity_type VARCHAR(64) NOT NULL,
|
||
|
|
label VARCHAR(255) NOT NULL,
|
||
|
|
description TEXT NOT NULL DEFAULT '',
|
||
|
|
is_system BOOLEAN NOT NULL DEFAULT TRUE,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_entity_archetypes_entity_type
|
||
|
|
ON entity_archetypes (entity_type);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS field_definitions (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
archetype_key VARCHAR(64) NOT NULL
|
||
|
|
REFERENCES entity_archetypes (archetype_key) ON DELETE CASCADE,
|
||
|
|
field_key VARCHAR(64) NOT NULL,
|
||
|
|
field_type VARCHAR(32) NOT NULL
|
||
|
|
CHECK (field_type IN (
|
||
|
|
'text', 'longtext', 'number', 'boolean', 'date', 'enum', 'actor_ref', 'url'
|
||
|
|
)),
|
||
|
|
label VARCHAR(255) NOT NULL,
|
||
|
|
required BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
searchable BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
|
|
validation_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE (archetype_key, field_key)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_field_definitions_archetype
|
||
|
|
ON field_definitions (archetype_key, sort_order);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS field_values (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
tenant_id UUID NOT NULL REFERENCES tenants (id) ON DELETE CASCADE,
|
||
|
|
entity_type VARCHAR(64) NOT NULL,
|
||
|
|
entity_id UUID NOT NULL,
|
||
|
|
field_key VARCHAR(64) NOT NULL,
|
||
|
|
value_json JSONB,
|
||
|
|
value_text TEXT NOT NULL DEFAULT '',
|
||
|
|
value_number NUMERIC,
|
||
|
|
value_boolean BOOLEAN,
|
||
|
|
value_date DATE,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE (tenant_id, entity_type, entity_id, field_key)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_field_values_entity
|
||
|
|
ON field_values (tenant_id, entity_type, entity_id);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_field_values_search
|
||
|
|
ON field_values (tenant_id, entity_type, field_key)
|
||
|
|
WHERE value_text <> '';
|