Member Associations
Data Entity
Description
Junction entity that links users to one or more local chapters/organizations they belong to, supporting multi-association membership (e.g. NHF members who belong to up to 5 local chapters). Each row represents a single user-to-organization membership with role context and activity attribution rules, preventing double-counting in Bufdir reports.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key identifier for the member association record | PKrequiredunique |
user_id |
uuid |
Foreign key reference to the user holding this membership | required |
organization_id |
uuid |
Foreign key reference to the local chapter/organization the user is associated with | required |
role_id |
uuid |
Foreign key reference to the role held within this specific organization (a user can have different roles in different chapters) | required |
is_primary |
boolean |
Whether this is the user's primary association used as default for activity attribution and Bufdir reporting when no chapter is explicitly selected | required |
status |
enum |
Current state of the membership | required |
joined_at |
datetime |
Timestamp when the user joined this organization | required |
left_at |
datetime |
Timestamp when the user left this organization (null if still active) | - |
invited_by |
uuid |
User ID of the coordinator or org admin who created this association | - |
metadata |
json |
Org-specific extension data (e.g. local member number, chapter-specific labels, regional codes) | - |
created_at |
datetime |
Record creation timestamp | required |
updated_at |
datetime |
Last modification timestamp | required |
Database Indexes
idx_member_associations_user_org
Columns: user_id, organization_id
idx_member_associations_user
Columns: user_id
idx_member_associations_org
Columns: organization_id
idx_member_associations_status
Columns: status
idx_member_associations_user_primary
Columns: user_id, is_primary
Validation Rules
unique_user_organization_pair
error
Validation failed
valid_user_reference
error
Validation failed
valid_organization_reference
error
Validation failed
valid_role_reference
error
Validation failed
status_enum_check
error
Validation failed
left_at_after_joined_at
error
Validation failed
left_at_requires_inactive_status
error
Validation failed
Business Rules
max_five_associations_per_user
A user may belong to at most 5 local chapters concurrently (NHF constraint from §3.2)
exactly_one_primary_association
Each user with at least one active association must have exactly one association flagged is_primary=true; setting a new primary unsets the previous one
prevent_duplicate_activity_attribution
Activities registered by a user with multi-chapter membership must be attributed to exactly one organization to prevent double-counting in Bufdir reports
role_scoped_to_organization
The role_id must be valid within the context of the linked organization_id; a user can be a Coordinator in chapter A and a Peer Mentor in chapter B
soft_delete_preserves_history
Removing an association sets status=inactive and populates left_at rather than hard-deleting, preserving historical activity attribution
audit_membership_changes
All create/update/delete operations on member associations are logged to the audit trail per organization
tenant_isolation
Org Admins may only manage associations involving their own organization; Global Admins require time-bounded support access flag