Local Association
Data Entity
Description
The lowest tier of the organizational hierarchy, representing a local chapter or branch beneath a region. User memberships are held at local association level, and activities are attributed upward through local associations for Bufdir reporting.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key. Stable identifier used across RLS policies, foreign keys in user_organization_memberships, and Bufdir attribution queries. | PKrequiredunique |
name |
string |
Human-readable display name of the local association as used by the member organization (e.g. 'NHF Oslo', 'Blindeforbundet Trondheim'). Shown in org tree, membership screens, and coordinator dashboards. | required |
region_id |
uuid |
Foreign key to regions. Establishes the mid-tier parent in the hierarchy. All activity attribution and coordinator-scoped queries traverse upward via this link. | required |
organization_id |
uuid |
Direct foreign key to organizations (top-level). Denormalized from the region for efficient RLS policy evaluation — avoids a join to regions on every row access. All Bufdir report scoping and RLS policies reference this field directly. | required |
external_id |
string |
Organization-specific identifier used in external member systems (Cornerstone, Consio). Enables bidirectional sync without UUID exposure. Null when no external system integration is configured for this association. | - |
is_active |
boolean |
Soft-delete and lifecycle flag. Inactive local associations are excluded from membership selection, coordinator dashboards, and new activity attribution. Existing historical data is retained for Bufdir audit purposes. | required |
activity_count |
integer |
Denormalized count of all activities attributed to this local association. Maintained by a Supabase database trigger on the activities table. Used by the activity attribution service for fast rollup without expensive aggregation at report time. | required |
member_count |
integer |
Denormalized count of active user_organization_memberships referencing this local association. Updated by trigger on membership create/deactivate. Surfaced in org tree and coordinator dashboards. | required |
description |
text |
Optional free-text description of the local association — its geographic coverage, focus area, or other administrative notes. Visible to coordinators and admins in the org tree detail view. | - |
slug |
string |
URL-safe lowercase slug derived from name, used for deep links and referral link generation scoped to this association. Auto-generated on create; unique within the same organization. | required |
metadata |
json |
Extensible key-value store for organization-specific configuration not captured by fixed columns (e.g. Bufdir reporting category codes, regional coordinator assignment, custom Bufdir sub-category IDs). Queried by Bufdir report generator service. | - |
created_at |
datetime |
Timestamp of record creation. Set by Supabase default (now()). Used in audit logs and org structure import history. | required |
updated_at |
datetime |
Timestamp of last modification. Updated via trigger on any column change. Used for sync conflict resolution in member system integrations (Cornerstone, Consio). | required |
Database Indexes
idx_local_associations_region_id
Columns: region_id
Supports hierarchy traversal queries — fetching all local associations under a region for the org tree and coordinator scoping.
idx_local_associations_organization_id
Columns: organization_id
Supports RLS policy evaluation — all row-level security policies filter by organization_id to enforce tenant isolation.
idx_local_associations_organization_id_is_active
Columns: organization_id, is_active
Composite index for the most common query pattern: fetch all active local associations for an organization (membership selection, org tree rendering, Bufdir scoping).
idx_local_associations_slug_organization_id
Columns: slug, organization_id
Enforces slug uniqueness within an organization and supports fast lookup by slug for referral link resolution.
idx_local_associations_external_id
Columns: external_id
Supports member system sync lookups (Cornerstone, Consio) that join on external_id to identify the correct local association record.
Validation Rules
name_not_empty
error
Validation failed
name_max_length
error
Validation failed
name_unique_within_region
error
Validation failed
region_id_references_valid_region
error
Validation failed
organization_id_consistent_with_region
error
Validation failed
activity_count_non_negative
error
Validation failed
member_count_non_negative
error
Validation failed
external_id_format
error
Validation failed
slug_format
error
Validation failed
metadata_valid_json
error
Validation failed
Business Rules
region_organization_consistency
The organization_id on a local association must match the organization_id of its parent region. Prevents cross-tenant hierarchy corruption and ensures RLS consistency.
no_delete_with_active_memberships
A local association cannot be hard-deleted if any user_organization_memberships reference it as active. Admin must deactivate memberships first or use soft-delete (is_active = false) to preserve historical data for Bufdir audits.
no_delete_with_activity_history
A local association with activity_count > 0 cannot be hard-deleted, only deactivated. Activity records must remain traceable to their source association for Bufdir grant compliance and historical reporting.
deactivation_cascades_membership_visibility
When is_active is set to false, the local association is excluded from all new membership selections and coordinator dashboards. Existing memberships are not automatically deactivated — coordinators must act on them explicitly.
activity_count_maintained_by_trigger
activity_count is a denormalized field maintained exclusively by a Supabase database trigger on the activities table. Application code must never write this field directly. All reporting reads from this field to avoid expensive aggregations at scale (NHF: up to 1,400 local associations).
member_count_maintained_by_trigger
member_count is a denormalized field maintained by a Supabase trigger on user_organization_memberships. Application code must not write this field directly.
bufdir_attribution_scope
For Bufdir reporting, activities are attributed to the local association at the time of activity creation (via user_organization_memberships.local_association_id). Changing a local association's region or deactivating it does not retroactively alter attribution of historical activities.
rls_organization_scoping
All reads and writes to local_associations are restricted by Supabase RLS to rows where organization_id matches the authenticated user's org claim in the JWT. Global admins bypass this restriction. This is the primary data isolation mechanism.
csv_import_validation
When local associations are bulk-imported via CSV (org-csv-import-screen), each row must resolve to a valid region within the same organization. Rows with unresolvable region references are rejected before any records are written.
CRUD Operations
Storage Configuration
Entity Relationships
Local associations are owned by a top-level organization for RLS scoping and Bufdir attribution
Local associations belong to a regional tier within the organizational hierarchy
Each membership is associated with a specific local association within the organization