# User organizational assignments Phase 1B.3 answers where a user belongs or operates. Assignments do not themselves grant permissions, imply a role, or change Django authentication behavior. Phase 1B.4 adds [access-owned role assignments](RBAC.md) referencing this canonical scope. ## Ownership and representation `organization.UserOrganizationAssignment` belongs to apps.organization. Organization owns the hierarchy and its lifecycle, so assignment cascades remain within that app. Its user FK references settings.AUTH_USER_MODEL; accounts imports no organization code, and User has no new scope/role fields. Django supplies the reverse `user.organization_assignments` relation. No new app was needed. The assignment implementation is in assignments.py, registered by models.py. It shares the small abstract TimeStampedModel with OrganizationRecord, without code, name or other master-record fields. This extraction adds no master-table changes. Each assignment has UUID v4 id, required user/company, optional region/service_center/ department, is_primary (default False), is_active (default True), and timestamps. All foreign keys use PROTECT, so referenced users/units cannot be deleted while assignment history exists. Multiple assignments per user, including across companies, are supported. An assignment cannot be transferred to another user after creation. Scope corrections are allowed through validated saves; this is not an immutable audit log. End an assignment and create another when recording a change of posting. Center assignments require an explicit matching region (approach B). Region is never silently inferred. Company-only, region-only within a company, department within a company, center with region, and combined department scopes are supported. Every supplied unit and the center's own region must belong to the assignment company. Active assignments require all referenced units to be active. Inactive history may reference inactive units but must still satisfy scope consistency. ## Primary and duplicate constraints - `org_assignment_primary_active`: primary implies active. - `org_assignment_center_region`: a center requires a non-null region. - `org_assignment_one_primary`: partial unique index on user for active primaries. - `org_assignment_active_scope_uniq`: partial unique index on user, company, region, service_center and department for active assignments, using NULLS NOT DISTINCT. NULLS NOT DISTINCT treats absent scope levels as equal, so two company-only active assignments or other exact nullable-scope duplicates cannot coexist. Different centers/departments/users remain distinct. Inactive duplicates are allowed as history; reactivation is rejected if an active duplicate exists. Constraint names and generated SQL were inspected, and tests exercise direct bulk writes as well as model validation. This requires PostgreSQL 15+; the project uses PostgreSQL 18. No SQLite fallback exists. See Django's [UniqueConstraint documentation](https://docs.djangoproject.com/en/5.2/ref/models/constraints/#uniqueconstraint). ## Supported write and query API ```python from apps.organization.models import UserOrganizationAssignment from apps.organization.assignment_services import ( create_assignment, deactivate_assignment, reactivate_assignment, set_primary_assignment, ) assignment = create_assignment(user=user, company=company, region=region, service_center=center, department=department) assignment = set_primary_assignment(assignment=assignment) assignment = deactivate_assignment(assignment=assignment) assignment = reactivate_assignment(assignment=assignment) active = UserOrganizationAssignment.objects.for_user(user).active() ``` Creating a primary never silently demotes an existing primary. Use set_primary_assignment for deliberate switching: demote the old primary and promote the selected active assignment atomically. An invalid target rolls back the demotion. Switching does not reactivate an inactive target. Deactivation clears is_primary and does not select a replacement. Reactivation is explicit and does not restore a former primary flag. Services reload current records, return the updated object and do not persist pending unsaved changes on input objects. Normal save()/objects.create()/admin forms validate scope, active parents, and all constraints. Partial saves validate the effective persisted combination as well as the in-memory record. Stale objects should be refreshed before direct editing. Assignment writes have no hidden cross-record cascade; switching belongs in services. ## Organization and user lifecycle Supported organization services end affected active assignments in the same atomic transaction, clear primary status and update timestamps: - Company: all assignments referencing the company. - Region: assignments referencing the region or a center currently beneath it. - ServiceCenter: assignments referencing that center. - Department: assignments referencing that department. Unrelated assignments stay active. Parent reactivation never reactivates assignments. Already inactive assignment timestamps/history remain untouched by cascades. Direct unit deactivation with active assignments is rejected and directs the caller to the lifecycle service/action, including for otherwise empty companies/regions and leaves. A center referenced by ANY assignment cannot change region through normal model or service writes, including when all assignments are inactive. Otherwise stored assignment.region would disagree with center.region, and silently rewriting history would obscure the original scope. Centers without assignment references retain the Phase 1B.2 move behavior. A future historical-placement design is needed before moves of assigned centers can be supported; no automatic history rewrite is implemented. Deactivating User does not deactivate units or assignments. Existing assignment history/state remains. Active assignments are not authorization, and Django's inactive user authentication rules remain unchanged. ## Transactions and locking Assignment writes lock the relevant User row using select_for_update() inside transaction.atomic(). User ownership is stable, allowing concurrent primary/scope writes for that user to serialize. Company rows for the proposed and stored scopes are locked FOR SHARE before validation/writing, in UUID order. FOR SHARE allows other assignment writers to take shared locks concurrently and conflicts with the exclusive company locks used by organization writes/lifecycle services. This prevents parent validation racing deactivation without exclusively locking the company for ordinary assignment writes. See [PostgreSQL locking documentation](https://www.postgresql.org/docs/18/explicit-locking.html). Assignment services lock the user, then share-lock the target and current-primary companies in sorted order before touching assignment rows. They reload state after waiting. Primary switching's controlled demotion updates and promotion share one transaction. Organization cascades hold the existing exclusive company lock, bulk end assignments, and do not acquire user locks. Database constraints remain the final defense against competing primary/duplicate writes, including writes bypassing services. Seven new TransactionTestCase concurrency tests use separate PostgreSQL connections: primary switch serialization; raw primary collision; raw nullable duplicate collision; compatible writers for different users; company/department deactivation versus assignment creation; and assignment creation followed by company deactivation. Blocking tests inspect pg_blocking_pids(), rather than inferring locking from timing. Rollback tests cover failed primary promotion and each organization's cascade. The protocol assumes the existing single default PostgreSQL database and normal Read Committed transactions. Arbitrary external transactions, pre-acquired locks or raw writes are not covered by a universal deadlock-free guarantee. Future operations combining organization writes and assignment writes must review lock order; do not upgrade a shared company lock after already writing assignments in an ad-hoc workflow. ## Admin and boundaries Admin lists user, company, region, center, department and primary/active flags, with search, filters, autocomplete and selected-related loading. Forms reject inconsistent scope. User is read-only on existing assignments. A single-selection primary action calls the switching service; the deactivation action calls the lifecycle service. Admin deletion is disabled to retain history. Ordinary validated scope correction and activation remain available; no custom frontend or permission evaluation exists. Model-level cross-table checks, stable user ownership, and parent-state validation can be bypassed by QuerySet.update(), bulk_create(), bulk_update(), fixtures or raw SQL. Those paths are unsupported for ordinary assignment changes. Database constraints still protect the single-row and uniqueness rules. The deliberate service bulk updates are scoped, timestamped and protected by the locking protocol. ORM/raw deletion is not globally disabled; admin and lifecycle services use deactivation as the normal operation. There is no generic soft-delete framework or audit/effective-date model. ## Migration and verification organization.0002_userorganizationassignment creates only the assignment table, foreign keys, indexes and constraints, with a swappable dependency on AUTH_USER_MODEL. No existing migration is modified and no data is seeded. ```powershell python manage.py check python manage.py makemigrations --check python manage.py migrate python manage.py test --noinput python manage.py showmigrations organization python manage.py sqlmigrate organization 0002 ``` The full suite contains 123 tests, including all 73 prior-phase tests unchanged. The seven assignment concurrency tests run automatically in the full suite, using Django's isolated PostgreSQL test database; no special configuration is needed beyond the existing test database privileges. They can also be run by the label `apps.organization.test_assignment_concurrency.AssignmentConcurrencyTests`. ## Phase 1B.4 lifecycle extension Supported assignment and organization deactivation now atomically end attached active UserRoleAssignments through synchronous lifecycle hooks. Direct assignment deactivation with active role assignments is rejected. Primary clearing and historical retention are unchanged, and reactivation never restores role assignments. Organization imports no access code: access registers the hook receivers. Assignment rows are locked before notifications so role creation cannot escape a cascade. See RBAC.md for the lock order and permission-query limitations. Existing Phase 1B.3 tests remain unchanged.