Organization Unit
Data Entity
Description
Represents a node in the organizational hierarchy such as a local chapter, regional office, or national association. Units form a recursive parent-child tree. NHF has the deepest hierarchy with 12 national associations, 9 regions, and 1,400 local chapters. All activity and contact data is scoped to units for reporting and RLS access control.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable surrogate primary key generated on insert. | PKrequiredunique |
organization_id |
uuid |
Foreign key to the owning organization (tenant). All hierarchy nodes are fully isolated per organization; this is the primary RLS partition key. | required |
parent_id |
uuid |
Self-referential FK to the immediate parent unit. NULL indicates this is a root node for the organization. Must belong to the same organization_id. | - |
name |
string |
Human-readable display name of the unit (e.g., 'Oslo Lokallag', 'Region Vest', 'Norges Blindeforbund'). Must be non-empty and unique within the parent scope. | required |
level_type |
enum |
Semantic classification of the unit's position in the hierarchy. Drives display labels, aggregation depth logic, and Bufdir reporting roll-up rules. | required |
path |
string |
Materialized ancestor path stored as a dot-separated sequence of unit IDs (e.g., 'root_id.region_id.chapter_id'). Enables efficient subtree queries and ancestor lookups without recursive CTEs on every read. Maintained automatically on insert and reparent operations. | requiredunique |
depth |
integer |
Zero-based depth of this node in the hierarchy tree. Root nodes have depth 0. Stored as a denormalized column to avoid recursive depth calculation on read. Updated on reparent. | required |
is_active |
boolean |
Soft-delete / deactivation flag. Inactive units are hidden from selection UIs and excluded from new assignments, but retained for historical reporting and audit purposes. | required |
display_order |
integer |
Optional sort index used to control sibling ordering in tree views and chapter-switcher lists. Defaults to 0. Gaps are allowed; sorted ascending. | - |
metadata |
json |
Extensible JSON bag for organization-specific unit attributes such as geographic coordinates, contact email, external system IDs (Dynamics unit code, Xledger cost centre), or NHF-specific samisk region flag. Schema is not enforced at database level. | - |
created_at |
datetime |
UTC timestamp of record creation. Set once on insert; never updated. | required |
updated_at |
datetime |
UTC timestamp of last modification. Updated automatically by a trigger on any column change. | required |
Database Indexes
idx_organization_unit_pkey
Columns: id
idx_organization_unit_org_id
Columns: organization_id
idx_organization_unit_parent_id
Columns: parent_id
idx_organization_unit_org_parent
Columns: organization_id, parent_id
idx_organization_unit_path
Columns: path
idx_organization_unit_path_prefix
Columns: path
idx_organization_unit_org_level
Columns: organization_id, level_type
idx_organization_unit_org_active
Columns: organization_id, is_active
idx_organization_unit_name_unique
Columns: organization_id, parent_id, name
Validation Rules
name_not_empty
error
Validation failed
name_max_length
error
Validation failed
name_unique_within_parent_scope
error
Validation failed
organization_id_exists
error
Validation failed
parent_id_exists_when_provided
error
Validation failed
level_type_valid_enum
error
Validation failed
path_format
error
Validation failed
depth_non_negative
error
Validation failed
metadata_valid_json
error
Validation failed
Business Rules
no_circular_parent_reference
A unit may not be set as its own ancestor. Before any insert or reparent update, the candidate parent_id must not appear in the current unit's descendant set, and must not equal the unit's own id.
parent_same_organization
If parent_id is non-null, the referenced parent unit must share the same organization_id. Cross-organization parent links are forbidden.
max_depth_per_organization
Each organization may configure a maximum allowed hierarchy depth. NHF requires at least 3 levels (national → region → local_chapter). The system enforces a hard cap (default: 5 levels) to prevent runaway recursion. The configured max is read from the organization's settings at write time.
path_and_depth_consistency
The path column must exactly reflect the ordered list of ancestor IDs from root to this node. The depth column must equal the number of dot-separators in the path. Both are recomputed synchronously on insert and on any parent_id change; they are never set directly by callers.
deactivate_before_delete
Physical deletion of a unit is only permitted when the unit has no active child units, no active unit_assignments, no activities, and no contacts scoped to it. The safe path is soft-deletion via is_active = false. Hard delete is exposed only to super-admin roles via the hierarchy admin portal.
root_node_uniqueness_per_org
Each organization may have at most one root unit (parent_id IS NULL). Attempting to create a second root for an organization is rejected.
orphan_prevention_on_delete
Deleting a unit that has child units is forbidden unless all children are first deleted or reparented. This prevents orphaned subtrees that cannot be reached via the tree traversal.
rls_data_scoping
All activity, contact, scenario_rule, unit_assignment, and recurring_activity_template records are associated with an organization_unit. RLS policies on those tables use the authenticated user's unit assignment set (derived from unit_assignment rows) to restrict read and write access. The access_scope_service computes the allowed unit ID list from the JWT session claims and unit_assignment records on every query.
cache_invalidation_on_hierarchy_change
Any write to the organization_units table (insert, update, delete) must invalidate the in-memory hierarchy cache for the affected organization_id so that subsequent reads reflect the new structure.
level_type_consistency_with_depth
As a soft rule enforced at the service layer, the level_type should be consistent with the unit's depth relative to the organization's configured hierarchy levels (e.g., depth 0 → national, depth 1 → region, depth 2 → local_chapter for NHF). Mismatches generate a warning but do not block saves, to allow flexible configurations.
CRUD Operations
Storage Configuration
Entity Relationships
Each contact-chapter membership links to a specific organization unit (local chapter) in the hierarchy
An organization owns its entire hierarchy of units (chapters, regions, national associations) as root-level entries
Activities are recorded within a specific chapter context for chapter-level Bufdir aggregation and coordinator scope filtering
Organization units form a recursive parent-child hierarchy (national → region → local chapter) via a self-referential parent_id
Recurring activity templates are scoped to a chapter so coordinators only see templates relevant to their operational unit
Scenario notification rules are configured per chapter so coordinators can enable or disable scenarios for their specific unit
A chapter or regional unit has multiple users (coordinators, peer mentors) assigned to it for scoped data access