User Local Associations
Data Entity
Description
Junction table managing the many-to-many relationship between users and local associations. A single user (peer mentor or coordinator) can belong to multiple local associations (up to 5 for NHF), with exactly one designated as the primary association for reporting and activity attribution purposes. This entity is critical for preventing double-reporting across associations, scoping coordinator visibility, and ensuring correct Bufdir grant attribution.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for this membership record | PKrequiredunique |
user_id |
uuid |
Foreign key referencing the user who holds membership in the local association | required |
local_association_id |
uuid |
Foreign key referencing the local association the user belongs to | required |
is_primary |
boolean |
Whether this is the user's primary local association for reporting, activity attribution, and Bufdir grant allocation. Exactly one association per user must be marked as primary. | required |
role_in_association |
enum |
The user's functional role within this specific local association, which may differ from their platform-wide role | - |
joined_at |
datetime |
Timestamp when the user was added to this local association | required |
left_at |
datetime |
Timestamp when the user left or was removed from this local association. Null means active membership. | - |
is_active |
boolean |
Whether this membership is currently active. Set to false instead of deleting to preserve audit trail. | required |
added_by |
uuid |
User ID of the administrator or coordinator who created this membership | - |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Record last modification timestamp | required |
Database Indexes
idx_ula_user_association_unique
Columns: user_id, local_association_id
idx_ula_user_id
Columns: user_id
idx_ula_local_association_id
Columns: local_association_id
idx_ula_user_primary
Columns: user_id, is_primary
idx_ula_active_members
Columns: local_association_id, is_active
idx_ula_user_active
Columns: user_id, is_active
Validation Rules
valid_user_reference
error
Validation failed
valid_association_reference
error
Validation failed
unique_user_association_pair
error
Validation failed
valid_role_in_association
error
Validation failed
left_at_after_joined_at
error
Validation failed
added_by_must_have_admin_or_coordinator_role
error
Validation failed
cannot_reactivate_without_new_record
warning
Validation failed
Business Rules
exactly_one_primary_association
Each active user must have exactly one local association marked as is_primary=true. When a new primary is set, the previous primary must be unset in the same transaction. Users with no active memberships are exempt.
max_associations_per_user
A user may belong to a maximum of 5 active local associations simultaneously, based on NHF's organizational structure where members can participate across multiple local chapters.
association_within_same_organization
A user can only be associated with local associations that belong to the same organization as the user's account. Cross-organization memberships are not permitted due to tenant isolation requirements.
soft_delete_membership
Memberships are soft-deleted by setting is_active=false and populating left_at, rather than physically deleting records. This preserves the audit trail for historical reporting and Bufdir compliance.
prevent_duplicate_reporting
Activities submitted by a user are attributed to their primary association only. The system must flag potential duplicate registrations when the same activity could be counted across multiple associations. NHF operates across 1,400 local associations where this risk is highest.
coordinator_scope_enforcement
Coordinators can only view and manage peer mentors who share at least one active local association with them. This scoping is enforced on all coordinator actions including proxy reporting, bulk registration, and team statistics.
primary_must_be_active
The is_primary flag can only be true on an active membership (is_active=true). Deactivating a primary membership must trigger reassignment or leave the user without a primary until one is designated.
audit_membership_changes
All membership additions, removals, and primary designation changes must be recorded in the audit log with the acting administrator's identity for compliance and traceability.