04 — Data Model Extensions

This document describes additional tables needed to turn the Universal Entity Platform into a runtime-configurable platform with an Admin Console.

The base entity platform already includes:

• entities,
• entity_type_definitions,
• entity_relationships,
• property_groups,
• property_definitions,
• entity_property_values,
• tag_groups,
• tags,
• entity_groups,
• entity_group_members.

The Admin Console adds versioning, governance, definitions, and publishing support.

entity_model_versions

Tracks draft and published model versions.

id uuid primary key
version_number integer not null
status text not null
created_by_user_id uuid not null
created_at timestamptz not null
published_by_user_id uuid null
published_at timestamptz null
notes text null
deleted_at timestamptz null

Indexes:

CREATE UNIQUE INDEX uq_entity_model_versions_number
ON entity_model_versions(version_number)
WHERE deleted_at IS NULL;

CREATE UNIQUE INDEX uq_entity_model_versions_single_published
ON entity_model_versions(status)
WHERE status = 'published' AND deleted_at IS NULL;

entity_type_definitions

Admin-managed type definition.

id uuid primary key
model_version_id uuid not null references entity_model_versions(id) on delete restrict
key text not null
name text not null
description text null
icon text null
color text null
is_system boolean not null default false
is_root_allowed boolean not null default false
is_hierarchy_allowed boolean not null default true
can_have_children boolean not null default true
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

Unique key per model version:

CREATE UNIQUE INDEX uq_entity_type_definitions_key_version
ON entity_type_definitions(model_version_id, key)
WHERE deleted_at IS NULL;

entity_type_hierarchy_rules

Defines allowed parent-child combinations.

id uuid primary key
model_version_id uuid not null references entity_model_versions(id) on delete restrict
parent_entity_type_id uuid null references entity_type_definitions(id) on delete restrict
child_entity_type_id uuid not null references entity_type_definitions(id) on delete restrict
min_children integer null
max_children integer null
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

If parent_entity_type_id is null, the child type may be a root type if allowed.

property_groups

id uuid primary key
model_version_id uuid not null references entity_model_versions(id) on delete restrict
key text not null
name text not null
description text null
display_order integer not null default 0
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

property_definitions

id uuid primary key
model_version_id uuid not null references entity_model_versions(id) on delete restrict
property_group_id uuid null references property_groups(id) on delete restrict
key text not null
name text not null
description text null
value_type text not null
is_required boolean not null default false
is_sensitive boolean not null default false
is_searchable boolean not null default true
is_filterable boolean not null default true
is_unique_per_entity boolean not null default true
validation jsonb not null default '{}'
default_value jsonb null
display_order integer not null default 0
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

entity_type_property_groups

Maps property groups to entity types.

id uuid primary key
model_version_id uuid not null references entity_model_versions(id) on delete restrict
entity_type_id uuid not null references entity_type_definitions(id) on delete restrict
property_group_id uuid not null references property_groups(id) on delete restrict
is_required boolean not null default false
display_order integer not null default 0
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

relationship_definitions

Defines allowed graph relationship types.

id uuid primary key
model_version_id uuid not null references entity_model_versions(id) on delete restrict
key text not null
name text not null
description text null
source_entity_type_id uuid not null references entity_type_definitions(id) on delete restrict
target_entity_type_id uuid not null references entity_type_definitions(id) on delete restrict
cardinality text not null default 'many_to_many'
is_directional boolean not null default true
allow_self_reference boolean not null default false
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

Example cardinalities:

one_to_one
one_to_many
many_to_one
many_to_many

templates

Templates define reusable entity creation defaults.

id uuid primary key
model_version_id uuid not null references entity_model_versions(id) on delete restrict
key text not null
name text not null
description text null
entity_type_id uuid not null references entity_type_definitions(id) on delete restrict
configuration jsonb not null default '{}'
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

model_change_requests

Optional but recommended for governance.

id uuid primary key
model_version_id uuid not null references entity_model_versions(id) on delete restrict
change_type text not null
status text not null
summary text not null
created_by_user_id uuid not null
created_at timestamptz not null
reviewed_by_user_id uuid null
reviewed_at timestamptz null
payload jsonb not null default '{}'
deleted_at timestamptz null

model_validation_results

Stores validation output for drafts.

id uuid primary key
model_version_id uuid not null references entity_model_versions(id) on delete restrict
severity text not null
code text not null
message text not null
path text null
metadata jsonb not null default '{}'
created_at timestamptz not null

Severity values:

info
warning
error

model_audit_events

Records model administration changes.

id uuid primary key
model_version_id uuid null references entity_model_versions(id) on delete restrict
actor_user_id uuid not null
event_type text not null
entity_type text null
target_id uuid null
payload jsonb not null default '{}'
created_at timestamptz not null

Runtime Tables Still Matter

The model metadata describes what can exist.

Runtime tables store what does exist.

Do not confuse:

property_definitions

with:

entity_property_values

Definitions are metadata. Values are runtime data.