Data Model

Database

Eco Manager uses PostgreSQL with Drizzle ORM.

Required extensions:

CREATE EXTENSION IF NOT EXISTS ltree;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

Core Tables

entity_type_definitions

Defines available entity types.

id uuid primary key
key text unique not null
name text not null
description text null
icon text null
is_system boolean not null default false
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

entities

Universal registry table.

id uuid primary key
entity_type_id uuid not null references entity_type_definitions(id)
parent_entity_id uuid null references entities(id) on delete restrict
path ltree not null
path_label text unique not null
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

Recommended indexes:

CREATE INDEX idx_entities_parent ON entities(parent_entity_id);
CREATE INDEX idx_entities_path_gist ON entities USING GIST(path);
CREATE INDEX idx_entities_path_btree ON entities(path);
CREATE INDEX idx_entities_active_type ON entities(entity_type_id) WHERE deleted_at IS NULL;

entity_relationships

Associative graph relationships only. Do not store containment here.

id uuid primary key
source_entity_id uuid not null references entities(id) on delete restrict
target_entity_id uuid not null references entities(id) on delete restrict
relationship_type text not null
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

entity_groups

A group is represented by an entity, with group-specific configuration.

id uuid primary key
entity_id uuid unique not null references entities(id) on delete restrict
key text null
name text not null
description text null
allows_mixed_types boolean not null default true
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

entity_group_members

Membership table for entity groups.

id uuid primary key
group_entity_id uuid not null references entities(id) on delete restrict
member_entity_id uuid not null references entities(id) on delete restrict
created_at timestamptz not null
deleted_at timestamptz null

Unique active membership:

CREATE UNIQUE INDEX uq_entity_group_members_active
ON entity_group_members(group_entity_id, member_entity_id)
WHERE deleted_at IS NULL;

user_root_entity_scopes

Maps users to the root entities they are allowed to operate within. This is the application-level basis for tenant/customer scoping and future RLS.

id uuid primary key
user_id uuid not null
root_entity_id uuid not null references entities(id) on delete restrict
capabilities jsonb not null default '[]'
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

tenant_encryption_keys

Tracks tenant/root-level encryption key metadata. Actual key material should not be stored as plaintext in PostgreSQL.

id uuid primary key
root_entity_id uuid not null references entities(id) on delete restrict
key_provider text not null
key_reference text not null
status text not null
created_at timestamptz not null
updated_at timestamptz not null
retired_at timestamptz null
deleted_at timestamptz null

tenant_encryption_key_versions

Tracks individual versions of tenant/root keys for rotation and historical decryption.

id uuid primary key
tenant_encryption_key_id uuid not null references tenant_encryption_keys(id) on delete restrict
version integer not null
wrapped_key jsonb not null
algorithm text not null
created_at timestamptz not null
activated_at timestamptz null
retired_at timestamptz null
deleted_at timestamptz null

vault_secret_versions

Stores encrypted secret values. Secret ciphertext is physically separated from general entity property values.

id uuid primary key
secret_entity_id uuid not null references entities(id) on delete restrict
root_entity_id uuid not null references entities(id) on delete restrict
tenant_encryption_key_version_id uuid not null references tenant_encryption_key_versions(id) on delete restrict
version integer not null
algorithm text not null
nonce text not null
ciphertext bytea not null
auth_tag text not null
aad jsonb not null
created_by_user_id uuid not null
created_at timestamptz not null
reason text null
deleted_at timestamptz null

property_groups

Defines sections for property definitions.

id uuid primary key
key text unique 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

Defines typed properties.

id uuid primary key
property_group_id uuid null references property_groups(id) on delete restrict
key text unique 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
validation jsonb not null default '{}'
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

Example value_type values:

text
number
boolean
date
json
ip_address
url
secret_reference
single_select
multi_select

entity_property_values

Stores actual property values for entities.

id uuid primary key
entity_id uuid not null references entities(id) on delete restrict
property_definition_id uuid not null references property_definitions(id) on delete restrict
value jsonb not null
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

Unique active value:

CREATE UNIQUE INDEX uq_entity_property_values_active
ON entity_property_values(entity_id, property_definition_id)
WHERE deleted_at IS NULL;

tag_groups

Organises tags.

id uuid primary key
key text unique not null
name text not null
description text null
selection_mode text not null default 'multi'
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

tags

A tag is an entity with tag-specific data.

id uuid primary key
entity_id uuid unique not null references entities(id) on delete restrict
tag_group_id uuid null references tag_groups(id) on delete restrict
key text not null
name text not null
color text null
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

entity_tags

Assigns tags to entities.

id uuid primary key
entity_id uuid not null references entities(id) on delete restrict
tag_entity_id uuid not null references entities(id) on delete restrict
created_at timestamptz not null
deleted_at timestamptz null

admin_saved_views

Stores Admin Studio saved views and filters.

id uuid primary key
owner_user_id uuid null
root_entity_id uuid null references entities(id) on delete restrict
key text null
name text not null
description text null
view_type text not null
config jsonb not null default '{}'
created_at timestamptz not null
updated_at timestamptz not null
deleted_at timestamptz null

Domain Tables

Eco Manager may still use focused domain tables for behaviour-heavy concepts.

Examples:

• vault_secret_versions,
• tenant_encryption_keys,
• tenant_encryption_key_versions,
• user_root_entity_scopes,
• audit_events,
• remote_agents,
• remote_sessions,
• simpro_external_refs.

Avoid creating a new domain table just to store ordinary editable properties. Use property definitions and property values unless special behaviour is required.

Deletion Model

Normal deletion is soft deletion.

Each mutable table should include:

deleted_at timestamptz null

Hard deletion is performed only by explicit purge operations after retention rules are satisfied.

Foreign keys should normally use:

ON DELETE RESTRICT

Do not rely on database cascade delete for normal application behavior.