Region
Data Entity
Description
Mid-level organizational unit grouping multiple local associations under a national association. Regions aggregate activity data for intermediate reporting and support coordinator-scoped dashboard views.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Surrogate primary key, generated server-side as a UUID v4 | PKrequiredunique |
name |
string |
Human-readable region name, e.g. 'Region Vest'. Must be unique within the parent national association. | required |
national_association_id |
uuid |
Foreign key referencing the national_associations row this region belongs to. Determines reporting hierarchy and scopes all child local_associations. | required |
organization_id |
uuid |
Foreign key referencing the top-level organizations row. Redundant denormalization used to support efficient RLS policy evaluation without traversing the full hierarchy on every query. | required |
code |
string |
Optional short alphanumeric identifier for the region used in Bufdir exports and CSV imports, e.g. 'VEST', 'OST'. Unique within organization scope. | - |
description |
text |
Free-text description of the region's geographic coverage or administrative scope. | - |
is_active |
boolean |
Soft-delete flag. Inactive regions are hidden from coordinator dashboards and org-tree UIs but retained for historical activity attribution and Bufdir reporting. | required |
created_at |
datetime |
Timestamp of record creation, set server-side by Supabase and never modified. | required |
updated_at |
datetime |
Timestamp of last modification, updated automatically via a Supabase trigger on every row change. | required |
metadata |
json |
Extensible JSON bag for organization-specific regional configuration such as geographic bounding boxes, contact officer details, or feature flag overrides. Not indexed; accessed only on detail views. | - |
Database Indexes
idx_regions_national_association_id
Columns: national_association_id
idx_regions_organization_id
Columns: organization_id
idx_regions_name_national
Columns: name, national_association_id
idx_regions_code_organization
Columns: code, organization_id
idx_regions_active
Columns: organization_id, is_active
Validation Rules
name_not_empty
error
Validation failed
name_max_length_200
error
Validation failed
national_association_id_exists
error
Validation failed
organization_id_exists
error
Validation failed
code_alphanumeric_max_20
error
Validation failed
code_unique_within_organization
error
Validation failed
is_active_boolean
error
Validation failed
metadata_valid_json
error
Validation failed
Business Rules
region_belongs_to_one_national_association
Every region must be assigned to exactly one national_associations record. A region cannot exist without a parent national association, enforcing the four-tier hierarchy: organization → national_association → region → local_association.
organization_id_consistent_with_national_association
The region's organization_id must equal the organization_id of its parent national_association. This consistency constraint prevents cross-organization hierarchy contamination and ensures RLS policies remain correct.
no_delete_with_active_local_associations
A region cannot be deleted while it has active local_associations children. The admin must first reassign or deactivate all child local associations. Enforced via database-level RESTRICT constraint and service-layer pre-flight check.
soft_delete_preserves_historical_attribution
Regions are never physically deleted if they have any associated activity_logs records. Setting is_active=false is the only permitted deactivation path to preserve Bufdir reporting integrity.
name_unique_within_national_association
Region name must be unique within its parent national_association. This prevents coordinator confusion in dashboards and report filters that list regions by name within a national association context.
activity_data_rolls_up_through_region
All activity_logs records attributed to local_associations within this region must be aggregatable at the region tier for coordinator dashboards and Bufdir intermediate reporting. The activity-attribution-service walks the hierarchy upward using organization_id and national_association_id joins.
rls_scoped_to_organization
All Supabase RLS read policies on the regions table use organization_id to scope access. Users may only read regions belonging to their own organization(s) as determined by user_organization_memberships. Admin writes are gated by user_roles containing an admin or global_admin role.
CRUD Operations
Storage Configuration
Entity Relationships
Local associations belong to a regional tier within the organizational hierarchy
Regions aggregate under a national association within the hierarchy
Regions are scoped to a top-level organization for RLS enforcement