Data Model

Database Requirements

PostgreSQL extensions:

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

ltree is used for materialized hierarchy paths. pgcrypto is useful for UUID generation and cryptographic helpers, though application-level cryptography should remain explicit and reviewed.

Naming Conventions

Recommended database naming:

• table names: plural snake_case,
• column names: snake_case,
• primary key: id,
• foreign keys: <thing>_id,
• timestamps: created_at, updated_at, deleted_at,
• active records: deleted_at IS NULL.

Recommended TypeScript naming:

• table objects: camelCase plural, such as entities,
• columns: camelCase in Drizzle mapping, such as parentEntityId,
• DTOs: EntityDto, CreateEntityInput, EntityPathNodeDto,
• services: EntityService, HierarchyService.

Core Tables

entity_type_definitions

Defines the available entity types.

CREATE TABLE entity_type_definitions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key text NOT NULL UNIQUE,
  name text NOT NULL,
  description text,
  icon text,
  is_system boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

Rules:

• key is stable and machine-readable.
• name is user-facing.
• system types should not be deleted by ordinary users.
• soft-deleted types should not be available for new entities.

Example keys:

customer
site
building
room
asset
credential
tag
group
connection_method
vendor

entities

Universal identity and hierarchy table.

CREATE TABLE entities (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_type_id uuid NOT NULL REFERENCES entity_type_definitions(id) ON DELETE RESTRICT,
  parent_entity_id uuid REFERENCES entities(id) ON DELETE RESTRICT,
  path ltree NOT NULL,
  path_label text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

Recommended indexes:

CREATE INDEX idx_entities_parent
ON entities(parent_entity_id)
WHERE deleted_at IS NULL;

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;

Rules:

• id is the platform identity.
• path_label is an immutable label used in the ltree path.
• path is derived from ancestor path labels.
• parent_entity_id and path must be updated together when reparenting.
• parent and child must not form cycles.

entity_relationships

Associative graph relationships.

CREATE TABLE entity_relationships (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  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,
  metadata jsonb NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

Recommended indexes:

CREATE INDEX idx_entity_relationships_source
ON entity_relationships(source_entity_id)
WHERE deleted_at IS NULL;

CREATE INDEX idx_entity_relationships_target
ON entity_relationships(target_entity_id)
WHERE deleted_at IS NULL;

CREATE INDEX idx_entity_relationships_type
ON entity_relationships(relationship_type)
WHERE deleted_at IS NULL;

CREATE UNIQUE INDEX uq_entity_relationships_active
ON entity_relationships(source_entity_id, target_entity_id, relationship_type)
WHERE deleted_at IS NULL;

Rules:

• Do not store containment relationships here.
• Graph relationships may be directional.
• Relationship types should be validated by service logic or a future relationship type definition table.

entity_groups

Group-specific metadata for group entities.

CREATE TABLE entity_groups (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_id uuid NOT NULL UNIQUE REFERENCES entities(id) ON DELETE RESTRICT,
  key text,
  name text NOT NULL,
  description text,
  allows_mixed_types boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

entity_group_members

Membership table for groups.

CREATE TABLE entity_group_members (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  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 DEFAULT now(),
  deleted_at timestamptz
);

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

Rules:

• group entity must be of type group or an allowed group subtype.
• membership is not hierarchy.
• a group may contain entities from different hierarchy branches.

property_groups

Groups related property definitions.

CREATE TABLE property_groups (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key text NOT NULL UNIQUE,
  name text NOT NULL,
  description text,
  display_order integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

property_definitions

Defines typed properties.

CREATE TABLE property_definitions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_group_id uuid REFERENCES property_groups(id) ON DELETE RESTRICT,
  key text NOT NULL UNIQUE,
  name text NOT NULL,
  description text,
  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 '{}',
  default_value jsonb,
  display_order integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

Recommended value_type values:

text
long_text
number
integer
boolean
date
datetime
json
ip_address
mac_address
url
email
secret_reference
single_select
multi_select
entity_reference

entity_property_values

Stores actual property values for entities.

CREATE TABLE entity_property_values (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  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 DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

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

Rules:

• validate value according to the property definition.
• if is_sensitive = true, the value should normally be a Vault reference, not plaintext/ciphertext directly.
• update operations may either modify the active row or version through a separate history table.

tag_groups

Organizes tags.

CREATE TABLE tag_groups (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key text NOT NULL UNIQUE,
  name text NOT NULL,
  description text,
  selection_mode text NOT NULL DEFAULT 'multi',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

selection_mode values:

single
multi

tags

Tag-specific data for tag entities.

CREATE TABLE tags (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_id uuid NOT NULL UNIQUE REFERENCES entities(id) ON DELETE RESTRICT,
  tag_group_id uuid REFERENCES tag_groups(id) ON DELETE RESTRICT,
  key text NOT NULL,
  name text NOT NULL,
  color text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

CREATE UNIQUE INDEX uq_tags_group_key_active
ON tags(tag_group_id, key)
WHERE deleted_at IS NULL;

entity_tags

Assigns tags to entities.

CREATE TABLE entity_tags (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  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 DEFAULT now(),
  deleted_at timestamptz
);

CREATE UNIQUE INDEX uq_entity_tags_active
ON entity_tags(entity_id, tag_entity_id)
WHERE deleted_at IS NULL;

entity_templates

Defines reusable metadata shapes.

CREATE TABLE entity_templates (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key text NOT NULL UNIQUE,
  name text NOT NULL,
  description text,
  entity_type_id uuid NOT NULL REFERENCES entity_type_definitions(id) ON DELETE RESTRICT,
  is_system boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

entity_template_property_groups

Associates templates with property groups.

CREATE TABLE entity_template_property_groups (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  template_id uuid NOT NULL REFERENCES entity_templates(id) ON DELETE RESTRICT,
  property_group_id uuid NOT NULL REFERENCES property_groups(id) ON DELETE RESTRICT,
  display_order integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

entity_template_tag_groups

Associates templates with tag groups.

CREATE TABLE entity_template_tag_groups (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  template_id uuid NOT NULL REFERENCES entity_templates(id) ON DELETE RESTRICT,
  tag_group_id uuid NOT NULL REFERENCES tag_groups(id) ON DELETE RESTRICT,
  display_order integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

user_root_entity_scopes

Maps users to root entities they are allowed to operate within.

CREATE TABLE user_root_entity_scopes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  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 DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

Recommended indexes:

CREATE INDEX idx_user_root_entity_scopes_user
ON user_root_entity_scopes(user_id)
WHERE deleted_at IS NULL;

CREATE INDEX idx_user_root_entity_scopes_root
ON user_root_entity_scopes(root_entity_id)
WHERE deleted_at IS NULL;

entity_events

Immutable audit/event trail.

CREATE TABLE entity_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_id uuid REFERENCES entities(id) ON DELETE RESTRICT,
  actor_user_id uuid,
  event_type text NOT NULL,
  payload jsonb NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now()
);

Rules:

• events should not be updated or soft-deleted in normal operation.
• sensitive payload data should be avoided or redacted.

Foreign Key Policy

Default rule:

ON DELETE RESTRICT

Do not use ON DELETE CASCADE for normal domain lifecycle. Soft delete and purge should be explicit service operations.

Soft Delete Policy

Mutable tables should include:

deleted_at timestamptz null

Queries should filter active rows by default:

WHERE deleted_at IS NULL

When To Add Domain Tables

Use metadata/property tables for ordinary configurable data.

Add a domain table when the concept has specialized behavior, lifecycle, security, or integration needs.

Good reasons to add a domain table:

• Vault secret versions,
• remote sessions,
• site agents,
• integration external references,
• immutable audit events,
• operational work sessions.

Poor reasons to add a domain table:

• a new text field,
• a new category,
• a simple editable attribute,
• a display section that could be a property group.