Users_roles_per_herd
Table for storing user role assignments per herd with role-based access control
| Column Name | Data Type | Constraints |
|---|---|---|
| id | bigint | NOT NULL |
| inserted_at | timestamp with time zone | DEFAULT timezone('utc'::text, now()) NOT NULL |
| user_id | uuid | NOT NULL |
| herd_id | bigint | NOT NULL |
| herd_role_id | bigint | NOT NULL, FK herd_roles |
| lifecycle | public.entity_lifecycle (active, retired, deleted) | DEFAULT active NOT NULL |
| lifecycle_changed_at | timestamp with time zone | DEFAULT timezone('utc'::text, now()) NOT NULL |
| lifecycle_changed_by | uuid | |
| lifecycle_reason | text |
Unique on (user_id, herd_id). Only lifecycle = active rows grant abilities via has_herd_ability; users.lifecycle must also be active.
Security Policies (RLS)
RLS enabled. Herd scope: Herd from users_roles_per_herd.herd_id.
| Operation | has_herd_ability ability key | Other paths |
|---|---|---|
| SELECT | users_roles_per_herd.read | — |
| INSERT | users_roles_per_herd.write | — |
| UPDATE | users_roles_per_herd.write | — |
| DELETE | users_roles_per_herd.write | — |
Platform superadmin: FOR ALL bypass where defined.
Losing active membership revokes pending invitations sent by that user for that herd. RPCs leave_herd_for_current_user / remove_user_from_herd_for_admin also require users_roles_per_herd.write when removing the last holder.