🏠 Overview › ✅ Implementation Tasks ›✅ epic-organizational-hierarchy-management-foundation-task-002

Design user_unit_assignments junction table

epic-organizational-hierarchy-management-foundation-task-002 — Create the user_unit_assignments junction table schema with columns for id, user_id, unit_id, is_primary, assigned_at, assigned_by, and revoked_at. Add unique constraints to enforce one primary assignment per user. Include foreign keys to auth.users and organization_units with appropriate cascade behavior.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

Table user_unit_assignments exists in Supabase with columns: id (uuid, primary key, default gen_random_uuid()), user_id (uuid, not null), unit_id (uuid, not null), is_primary (boolean, not null, default false), assigned_at (timestamptz, not null, default now()), assigned_by (uuid, not null), revoked_at (timestamptz, nullable)
Foreign key user_id references auth.users(id) with ON DELETE CASCADE behavior
Foreign key unit_id references organization_units(id) with ON DELETE RESTRICT behavior to prevent orphan assignments
Foreign key assigned_by references auth.users(id) with ON DELETE RESTRICT
Unique partial index enforces at most one active primary assignment per user: CREATE UNIQUE INDEX uq_user_primary_assignment ON user_unit_assignments(user_id) WHERE is_primary = true AND revoked_at IS NULL
Index exists on user_id for fast lookup of all assignments for a given user
Index exists on unit_id for fast lookup of all users in a given unit
Row Level Security (RLS) policies are enabled: users can read their own assignments; coordinators can read assignments for units they manage; admins have full access
Migration file is idempotent (uses IF NOT EXISTS / CREATE OR REPLACE patterns)
Schema is documented in the project migration directory with a descriptive comment block

Technical Requirements

frameworks
Supabase (PostgreSQL migrations)
apis
Supabase Management API (for migration deployment)
Supabase Auth (auth.users reference)
data models
UserUnitAssignment
OrganizationUnit
auth.users
performance requirements
Lookup of all active assignments for a user must complete in under 10ms (index on user_id + revoked_at IS NULL)
Lookup of all active members of a unit must complete in under 10ms (index on unit_id + revoked_at IS NULL)
Primary assignment check must be enforced at database level via partial unique index, not only in application code
security requirements
Row Level Security must be enabled on the table before any data is inserted
RLS policy: authenticated users may SELECT rows where user_id = auth.uid()
RLS policy: coordinators may SELECT rows for unit_ids they are assigned to manage
RLS policy: only service role or admin role may INSERT, UPDATE, DELETE
revoked_at must never be updated back to NULL once set — enforce via CHECK or trigger
assigned_by must always reference a valid auth.users record to maintain audit trail

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Use a partial unique index rather than a plain unique index so that a user can have multiple non-primary (is_primary = false) assignments to the same or different units, while only ever having one active primary. The partial index predicate should be: WHERE is_primary = true AND revoked_at IS NULL. Do NOT enforce primary uniqueness via application logic alone — the database constraint is the authoritative guard. For cascade behavior: auth.users deletion should cascade to assignments (user is gone, assignments are irrelevant); organization_unit deletion should be RESTRICT to force explicit cleanup first (prevents accidental data loss).

Use timestamptz for all timestamps to ensure correct handling across Norwegian timezones (CET/CEST). The assigned_by column provides an audit trail required for Norwegian volunteer management compliance. When writing the RLS policy for coordinators, join against user_unit_assignments itself to identify which units the requesting user manages — avoid hardcoding role checks that bypass the hierarchy. Keep the migration file version-prefixed (e.g., 20260329_create_user_unit_assignments.sql) following the existing project migration naming convention.

Testing Requirements

Write Supabase integration tests using the supabase-js client in a test environment with a seeded database. Test scenarios: (1) Insert a valid assignment and confirm it is retrievable. (2) Attempt to insert a second active primary assignment for the same user and confirm the unique partial index raises a constraint violation. (3) Revoke an assignment (set revoked_at) and confirm a new primary assignment can then be created for that user.

(4) Attempt to delete a referenced organization_unit and confirm ON DELETE RESTRICT blocks the operation. (5) Confirm RLS: a user querying the table only sees their own rows. (6) Confirm RLS: a coordinator sees assignments for their managed units but not others. Cover both positive paths and constraint violation paths.

Integration tests should run against a local Supabase instance (supabase start).

Component
Unit Assignment Repository
data medium
Dependencies (1)
Design and implement the recursive adjacency-list database schema for the organization_units table in Supabase. Include columns for id, parent_id (self-referential FK), name, unit_type (national/region/chapter), org_id, is_active, deleted_at (soft-delete), and created_at. Add cascade constraints and indexes for tree traversal performance. epic-organizational-hierarchy-management-foundation-task-001
Epic Risks (3)
high impact medium prob technical

Recursive CTE queries for large hierarchies (1,400+ nodes) may exceed Supabase query timeouts or produce unacceptably slow responses, degrading tree load time beyond the 1-second target.

Mitigation & Contingency

Mitigation: Implement Supabase RPC functions for subtree fetches rather than client-side recursive calls. Use materialized path or closure table as a supplemental index for depth-first traversal. Benchmark with realistic NHF data volumes during development.

Contingency: Fall back to a pre-computed flat unit list stored in the hierarchy cache with client-side tree reconstruction, trading freshness for speed. Add a background refresh job to keep the cache warm.

medium impact low prob technical

Concurrent writes from multiple admin sessions could cause cache staleness, leading to stale tree views and incorrect ancestor path computations that corrupt aggregation results.

Mitigation & Contingency

Mitigation: Use optimistic versioning on cache entries with a short TTL (5 minutes) as a safety net. Subscribe to Supabase Realtime on the organization_units table to push invalidation events to all connected clients.

Contingency: Provide a manual 'Refresh Hierarchy' action in the admin portal that forces a full cache bust, and display a staleness warning banner when the cache age exceeds the TTL.

high impact low prob security

Persisting the flat unit list to local storage may expose organization structure data if the device is compromised or the storage is not properly encrypted, violating data protection requirements.

Mitigation & Contingency

Mitigation: Use flutter_secure_storage (AES-256 backed by Keychain/Keystore) for the local unit list cache rather than SharedPreferences. Include only unit IDs, names, and types — no member PII.

Contingency: Disable local-storage persistence entirely and rely on in-memory cache only. Accept the trade-off of no offline hierarchy access for the security guarantee.

Quick Links
All Tasks Execution Plan