HH/DEPARTMENT_HIERARCHY_HANDOFF.md

Department & Complaint Hierarchy Migration - Full Technical Handoff

Overview

PX360 is a hospital patient experience management system (Django + SQLite). We are migrating from a 3-level legacy hierarchy (Location → MainSection → SubSection) to a new hierarchy (Department → Section → Sub-Section) with proper org structure, role-based contact persons, and department-centric complaint routing.

The legacy models are not deleted — they are renamed with Legacy* prefix and db_table preserved. The new Department model has 7 role-holder FKs (all FK to Staff), and complaints/inquiries/observations can be routed to departments with mandatory champion or manager validation.

---

Current State

Metric	Value
Total complaints	2,160
Complaints with department_id set	2,151 (99.6%)
Complaints with legacy_subsection_id	2,150
Complaints without any department	9
Total departments (new)	66
Total sections (OrgSubSection)	152
Total sub-sections (OrgSubSubSection)	17
LegacyHierarchyMapping rows	88

---

Database Schema

New Models (created via migrations 0001-0006)

organizations_department (UUID PK, 30 columns)

class Department(models.Model):
    # Location fields
    hospital        = FK(Hospital, CASCADE, related_name="departments")
    area            = CharField(100)          # High-level area
    main_section    = CharField(200)          # Main section name
    name            = CharField(200)
    name_en         = CharField(200, blank=True)
    name_ar         = CharField(200, blank=True)
    code            = CharField(100, db_index=True)
    category        = CharField(30, choices=DepartmentCategory, db_index=True)
    parent          = FK(self, SET_NULL, null=True)
    location_type   = CharField(20)           # OP, IP, ER, GENERAL
    sub_location    = CharField(200)
    floor           = CharField(50)

    # Role holders (all FK to Staff)
    manager         = FK(accounts.User, SET_NULL, null=True)
    champion        = FK(Staff, SET_NULL, null=True, related_name="champion_departments")
    manager_1st     = FK(Staff, SET_NULL, null=True, related_name="dept_manager_1st")
    manager_2nd     = FK(Staff, SET_NULL, null=True, related_name="dept_manager_2nd")
    manager_3rd     = FK(Staff, SET_NULL, null=True, related_name="dept_manager_3rd")
    deputy_manager  = FK(Staff, SET_NULL, null=True, related_name="dept_deputy_manager")
    supervisor      = FK(Staff, SET_NULL, null=True, related_name="dept_supervisor")
    deputy_supervisor = FK(Staff, SET_NULL, null=True, related_name="dept_deputy_supervisor")

    # Contact / legacy
    champion_email  = EmailField(200, blank=True)
    old_name_en     = CharField(200, blank=True)  # Legacy English name from Excel
    old_name_ar     = CharField(200, blank=True)  # Legacy Arabic name from Excel
    phone           = CharField(20, blank=True)
    email           = EmailField(blank=True)
    location        = CharField(200, blank=True)   # Free text (NOT a FK)
    status          = CharField(20, default="active")

    ROLE_FIELDS = [
        ("champion", "Champion"),
        ("manager_1st", "1st Manager"),
        ("manager_2nd", "2nd Manager"),
        ("manager_3rd", "3rd Manager"),
        ("deputy_manager", "Deputy Manager"),
        ("supervisor", "Supervisor"),
        ("deputy_supervisor", "Deputy Supervisor"),
    ]

    def get_role_holders(self):
        """Returns list of dicts: {staff, staff_id, name, email, role_field, role_label}"""

    def is_valid_contact_person(self, staff_id):
        """Validates a staff_id against all 7 role FKs. Returns holder dict or None."""

organizations_orgsubsection (UUID PK, 152 rows)

class OrgSubSection(models.Model):
    department      = FK(Department, CASCADE, related_name="org_subsections")
    name_en         = CharField(200)
    name_ar         = CharField(200, blank=True)
    code            = CharField(100, blank=True)
    location_type   = CharField(20)
    sub_location    = CharField(200)
    floor           = CharField(50)
    point_of_contact = FK(Staff, SET_NULL, null=True)
    old_name_en     = CharField(200, blank=True)
    old_name_ar     = CharField(200, blank=True)
    status          = CharField(20, default="active")

    class Meta:
        unique_together = [("department", "code")]

organizations_orgsubsubsection (UUID PK, 17 rows)

class OrgSubSubSection(models.Model):
    subsection      = FK(OrgSubSection, CASCADE, related_name="sub_subsections")
    name_en         = CharField(200)
    name_ar         = CharField(200, blank=True)
    code            = CharField(100, blank=True)
    status          = CharField(20, default="active")

    class Meta:
        unique_together = [("subsection", "code")]

organizations_legacyhierarchymapping (UUID PK, 88 rows)

class LegacyHierarchyMapping(models.Model):
    old_location_ar      = CharField(200, db_index=True)
    old_main_section_ar  = CharField(200, db_index=True)
    old_subsection_ar    = CharField(200, db_index=True)
    old_location_en      = CharField(200, blank=True)
    old_main_section_en  = CharField(200, blank=True)
    old_subsection_en    = CharField(200, blank=True)
    main_section         = FK(Department, SET_NULL, null=True)       # Maps to Department
    subsection           = FK(OrgSubSection, SET_NULL, null=True)
    sub_subsection       = FK(OrgSubSubSection, SET_NULL, null=True)

    class Meta:
        unique_together = [("old_location_ar", "old_main_section_ar", "old_subsection_ar")]

Legacy Models (renamed, db_table preserved, backward-compat aliases)

class LegacyLocation(models.Model):       # db_table = "organizations_location"
    id = models.IntegerField(primary_key=True)
    name_ar, name_en
    ACTIVE_IDS = [48, 49, 82, 110]

class LegacyMainSection(models.Model):    # db_table = "organizations_mainsection"
    id = models.IntegerField(primary_key=True)
    name_ar, name_en

class LegacySubSection(models.Model):     # db_table = "organizations_subsection"
    internal_id = models.IntegerField(primary_key=True)
    name_ar, name_en
    location = FK(LegacyLocation)
    main_section = FK(LegacyMainSection)

Complaint Model Hierarchy Fields

class Complaint(models.Model):
    # NEW hierarchy
    department      = FK(Department, SET_NULL, null=True, related_name="complaints")
    section         = FK(OrgSubSection, PROTECT, null=True, related_name="complaints")
    sub_subsection  = FK(OrgSubSubSection, PROTECT, null=True, related_name="complaints")

    # LEGACY FKs (kept for backward compatibility)
    legacy_location      = FK(LegacyLocation, PROTECT, null=True, related_name="complaints")
    legacy_main_section  = FK(LegacyMainSection, PROTECT, null=True, related_name="complaints")
    legacy_subsection    = FK(LegacySubSection, PROTECT, null=True, related_name="complaints")

    # Raw text (for mapping/audit)
    old_location_raw     = CharField(200, blank=True, db_index=True)
    old_main_section_raw = CharField(200, blank=True, db_index=True)
    old_subsection_raw   = CharField(200, blank=True, db_index=True)

Inquiry and Observation models have the same pattern of new + legacy + raw fields.

---

URL Structure

New hierarchy dropdown APIs (in apps/organizations/urls.py)

/dropdowns/departments-by-category/?category=<cat>  → api_departments_by_category
/dropdowns/sections/<uuid:department_id>/            → api_sections_by_department
/dropdowns/sub-subsections/<uuid:section_id>/        → api_sub_subsections_by_section

Role management

/department-contacts/<uuid:dept_id>/  → api_department_contacts  (GET, returns role holders JSON)
/set-role/                             → set_department_role      (POST, sets/clears a role on dept)

Complaint send/escalate (AJAX endpoints)

/complaints/<uuid:pk>/send-to/      → complaint_send_to      (POST)
/complaints/<uuid:pk>/escalate/     → complaint_escalate     (POST)
/complaints/<uuid:pk>/send-to-department/ → complaint_send_to (POST, same endpoint)
/inquiries/<uuid:pk>/escalate/      → inquiry_escalate       (POST)
/inquiries/<uuid:pk>/send-to/       → inquiry_send_to        (POST)
/observations/<uuid:pk>/escalate/   → observation_escalate   (POST)
/observations/<uuid:pk>/send-to/    → observation_send_to    (POST)

Legacy dropdown APIs (still active, used by internal forms)

/dropdowns/locations/                     → api_locations
/ajax/main-sections/?location=<id>       → ajax_main_sections
/ajax/subsections/?location=<id>&main_section=<id> → ajax_subsections

---

Key Code Files

Models

File	Content
apps/organizations/models.py:151-279	Department model (30 fields, ROLE_FIELDS, get_role_holders, is_valid_contact_person)
apps/organizations/models.py:697-726	OrgSubSection model
apps/organizations/models.py:729-747	OrgSubSubSection model
apps/organizations/models.py:750-779	LegacyHierarchyMapping model
apps/organizations/models.py:651-694	LegacyLocation, LegacyMainSection, LegacySubSection
apps/complaints/models.py:260-378	Complaint model (department, section, sub_subsection, legacy_id, old_raw)

Views (UI + API)

File	Lines	Content
apps/organizations/ui_views.py:1876-2187	department_detail — tabbed view with roles, complaints, inquiries, etc.
apps/organizations/ui_views.py:3503-3545	set_department_role — POST to set/clear one of 7 role FKs
apps/organizations/views.py:838-869	4 new dropdown/contacts API endpoints
apps/complaints/ui_views.py:294-413	complaint_list — filters by department_id (line 398)
apps/complaints/ui_views.py:954-1140	complaint_send_to — validates champion/manager, contact person
apps/complaints/ui_views.py:1584-1682	complaint_escalate — manual escalation to specific staff
apps/complaints/ui_views.py:3128-3195	inquiry_escalate
apps/complaints/ui_views.py:3200-3348	inquiry_send_to
apps/complaints/ui_views.py:6243-6296	government_ticket_list — filters by legacy_main_section_id
apps/complaints/ui_views.py:6300-6315	government_ticket_detail
apps/complaints/ui_views.py:6628-6659	government_ticket_export
apps/observations/views.py:1160-1236	observation_escalate
apps/observations/views.py:1241-1401	observation_send_to
apps/observations/views.py:1032-1155	observation_send_to_department

Services

File	Content
apps/complaints/services/complaint_service.py	ComplaintService.send_to_department
apps/complaints/tasks.py:672-679	escalate_complaint_auto — DISABLED (no-op, returns immediately)
apps/complaints/tasks.py:682-867	_escalate_complaint_auto_original — preserved but not dispatched
apps/complaints/tasks.py:870-958	escalate_after_reminder — effectively disabled
apps/complaints/tasks.py:358-390	check_overdue_complaints — still runs, calls disabled auto-escalate
apps/observations/services.py	ObservationService.create_observation — accepts section, sub_subsection

Forms (using legacy hierarchy — NOT yet migrated)

File	Form Class	Fields	Lines
apps/complaints/forms.py:58	PublicComplaintForm	location, main_section, subsection (required=True)	58-378
apps/complaints/forms.py:379	ComplaintForm	location, main_section, subsection	379-611
apps/complaints/forms.py:612	InquiryForm	location, main_section, subsection (required=False)	612-1220
apps/complaints/forms.py:1221	GovernmentTicketForm	legacy_location, legacy_main_section, legacy_subsection	1221-end

All 4 forms use LegacyLocation.active_locations(), LegacyMainSection, LegacySubSection with server-side cascading in __init__.

Templates — Migrated to New Hierarchy

Template	What Changed
templates/complaints/public_complaint_form.html	Category → Department → Section → Sub-Section cascade (JS via new APIs)
templates/core/public_submit.html	4 forms: complaint, observation, inquiry, appreciation — all switched to new hierarchy
templates/components/send_to_modal.html	Shared modal with contact person dropdown (calls api_department_contacts)
templates/organizations/department_detail.html	Roles tab with #roleModal, all 7 role fields

Templates — Still Using Old Hierarchy

Template	Fields Displayed
templates/complaints/complaint_form.html	location, main_section, subsection (via ajax_* endpoints)
templates/complaints/inquiry_form.html	location, main_section, subsection
templates/complaints/government_ticket_form.html	location, main_section, subsection
templates/complaints/government_ticket_list.html:166	ticket.legacy_main_section.name_en
templates/complaints/government_ticket_detail.html:128-136	legacy_location, legacy_main_section, legacy_subsection
templates/complaints/complaint_detail.html	Displays legacy_* fields
templates/complaints/complaint_pdf.html	Displays legacy_* fields

Dashboard/Analytics Services — Still Using Old Hierarchy

File	Lines	Issue
apps/dashboard/services/complaint_monthly_service.py:54	select_related(..., "location", "main_section", ...)	Uses location FK
apps/dashboard/services/complaint_quarterly_service.py:238-255	_compute_location_breakdown()	IP/OP/ER based on location__name_en string matching
apps/dashboard/services/complaint_quarterly_service.py:593-626	get_chart_data()	c.location.name_en
apps/dashboard/services/complaint_monthly_export.py:204	Export row	c.location.name
apps/analytics/services/kpi_service.py:948-978	_create_location_breakdowns()	Queries both location and main_section FKs
apps/analytics/services/kpi_service.py:1540	AI analysis	select_related(..., "location", "main_section", ...)
apps/analytics/services/kpi_service.py:1621-1624	Location counts	c.location.name_en

Management Commands

File	Content
apps/organizations/management/commands/import_departments_excel.py (286 lines)	Excel import: matches by hospital+name_en, creates Dept/OrgSubSection/OrgSubSubSection/LegacyHierarchyMapping. Dry-run support.
apps/organizations/management/commands/backfill_department_hierarchy.py (284 lines)	5-level cascade matching: manual override → LegacyHierarchyMapping → exact name → normalized → contains. 136-entry MANUAL_MAP dict.

---

What's Been Completed

1. Schema reconciliation: DB tables/columns created via raw SQL (all old Django migration records deleted, all new migration files faked-applied). 3 new tables, 100+ new columns.

2. Legacy FK backfill on complaints: legacy_location_id, legacy_main_section_id, legacy_subsection_id populated from old location_id, main_section_id, subsection_id for all 2,160 complaints. Raw text values (old_*_raw) also populated.

3. Excel import: 66 departments, 152 sections, 17 sub-sections, 88 legacy hierarchy mappings created.

4. Backfill command: 2,151/2,160 complaints now have department_id set (99.6%).

5. Patient-facing complaint form: Switched from Location→MainSection→SubSection to Category→Department→Section→SubSection.

6. Public submit forms (4 forms): All switched to new hierarchy cascade.

7. Observation service: Updated to accept section/sub_subsection params.

8. Send-to-department: Validates champion or manager_1st exists, validates contact person against 7 role FKs.

9. Contact person selection: When sending to department, user picks from role holders. Champion pre-selected if available.

10. Department detail: Roles tab with single reusable modal for all 7 roles.

11. Manual escalation: Implemented for complaint, inquiry, and observation. Auto-escalation disabled (code preserved).

12. Dropdown APIs: 3 new endpoints for department→section→subsection cascade, contacts API.

---

What Remains (Not Yet Migrated)

Critical

1. Government ticket list view (ui_views.py:6265): Filters by legacy_main_section_id. Should filter by department.
2. Dashboard quarterly IP/OP/ER (complaint_quarterly_service.py:238-255): Uses location__name_en string matching. Should derive from department.location_type.
3. Dashboard monthly export (complaint_monthly_export.py:204): Uses c.location.name. Should use department.
4. KPI service (kpi_service.py:948-978): Queries both location and main_section FKs.

Medium

5. Internal staff forms: complaint_form.html, inquiry_form.html, government_ticket_form.html — still use old Location→MainSection→SubSection cascade with ajax_main_sections/ajax_subsections endpoints.
6. 31 departments without category: Need category values assigned from the Excel data.

Low

7. Detail templates: complaint_detail.html, complaint_pdf.html, inquiry_detail.html, government_ticket_detail.html — still display legacy_* fields.
8. Stop writing legacy FKs: New complaints could stop writing legacy_* FKs (or auto-populate from department).

---

Key Design Decisions

• Contact person = one of 7 department role holders (champion, manager_1st-3rd, deputy_manager, supervisor, deputy_supervisor). Champion pre-selected if available.
• Department cannot receive complaint without champion OR manager_1st.
• Auto-escalation disabled but code preserved for future use.
• Legacy models kept with backward-compat aliases and preserved db_table.
• Old API endpoints kept functional for internal staff forms (gradual migration).
• Import matches by hospital+name_en to preserve existing Department IDs (critical for complaint FK integrity).
• Hardcoded MANUAL_MAP dict maps 136 legacy subsection PKs to department name strings for unmappable cases.
• DB schema applied via raw SQL, not Django migrations (production DB reconciliation).

---

API Endpoints (New Hierarchy)

GET /organizations/dropdowns/departments-by-category/?category=medical
  → Returns: [{"id": "uuid", "name_en": "Internal Medicine", ...}, ...]

GET /organizations/dropdowns/sections/<uuid:department_id>/
  → Returns: [{"id": "uuid", "name_en": "Cardiology", ...}, ...]

GET /organizations/dropdowns/sub-subsections/<uuid:section_id>/
  → Returns: [{"id": "uuid", "name_en": "ECG Lab", ...}, ...]

GET /organizations/department-contacts/<uuid:dept_id>/
  → Returns: [{"staff_id": "uuid", "name": "Dr. X", "email": "...", "role_field": "champion", "role_label": "Champion"}, ...]

POST /organizations/set-role/
  Body: {department_id, role_field, staff_id (optional)}
  → Sets/clears a role FK on Department

---

Migration Status

organizations
 [X] 0001_initial
 [X] 0002_rename_respondent_to_champion
 [X] 0003_alter_department_champion
 [X] 0004_legacylocation_legacymainsection_and_more
 [X] 0005_alter_legacylocation_table_and_more
 [X] 0006_alter_department_code_alter_orgsubsection_code_and_more

All migrations faked. DB schema in sync. Run with --skip-checks flag.

---

136-Entry Manual Mapping (MANUAL_MAP in backfill command)

The backfill command contains a hardcoded dict mapping 136 LegacySubSection.internal_id values to Department.name_en strings. These are legacy subsections that could not be automatically mapped via LegacyHierarchyMapping or name matching. Key distributions:

• Internal Medicine: ~14 entries
• Surgeries: ~11 entries
• Outpatient Department: ~10 entries
• Nursing Department: ~16 entries
• Emergency Department: ~6 entries
• Inpatient Department: ~7 entries
• Medical Ancillary Services: ~10 entries
• Others (Pediatric, Critical Care, OB/GYN, Pharmacy, IT, Security, Finance, etc.): 1-3 each

---

File Tree Summary

apps/
├── organizations/
│   ├── models.py                          # Department, OrgSubSection, OrgSubSubSection, LegacyHierarchyMapping, Legacy*
│   ├── views.py                           # 4 new dropdown/contacts APIs + legacy ajax_* endpoints
│   ├── ui_views.py                        # department_detail, set_department_role
│   ├── urls.py                            # All URL patterns
│   ├── serializers.py                     # DepartmentSerializer, OrgSubSectionSerializer, OrgSubSubSectionSerializer
│   ├── management/commands/
│   │   ├── import_departments_excel.py    # Excel import (286 lines)
│   │   └── backfill_department_hierarchy.py # Backfill with MANUAL_MAP (284 lines)
│   └── migrations/
│       ├── 0001_initial.py
│       ├── 0002_rename_respondent_to_champion.py
│       ├── 0003_alter_department_champion.py
│       ├── 0004_legacylocation_legacymainsection_and_more.py
│       ├── 0005_alter_legacylocation_table_and_more.py
│       └── 0006_alter_department_code_alter_orgsubsection_code_and_more.py
├── complaints/
│   ├── models.py                          # Complaint (104 columns), with department, section, sub_subsection, legacy_*
│   ├── forms.py                           # PublicComplaintForm, ComplaintForm, InquiryForm, GovernmentTicketForm (legacy cascade)
│   ├── ui_views.py                        # complaint_list, complaint_detail, complaint_send_to, complaint_escalate, inquiry_escalate, government_ticket_*
│   ├── services/complaint_service.py      # ComplaintService.send_to_department
│   └── tasks.py                           # Auto-escalation disabled, check_overdue still active
├── observations/
│   ├── views.py                           # observation_escalate, observation_send_to
│   └── services.py                        # create_observation (accepts section, sub_subsection)
├── dashboard/services/
│   ├── complaint_monthly_service.py       # Uses location, main_section FK
│   ├── complaint_quarterly_service.py     # IP/OP/ER via location__name_en string matching
│   └── complaint_monthly_export.py        # Exports c.location.name
├── analytics/services/
│   └── kpi_service.py                     # Queries location + main_section FKs
└── core/views.py                          # public_inquiry_submit, public_observation_submit

templates/
├── complaints/
│   ├── public_complaint_form.html         # MIGRATED: Category→Department→Section→SubSection
│   ├── complaint_form.html                # NOT MIGRATED: Still uses old Location→MainSection→SubSection
│   ├── inquiry_form.html                  # NOT MIGRATED: Still uses old hierarchy
│   ├── government_ticket_form.html        # NOT MIGRATED: Still uses old hierarchy
│   ├── government_ticket_list.html        # NOT MIGRATED: Displays legacy_main_section.name_en
│   ├── government_ticket_detail.html      # NOT MIGRATED: Displays legacy_* fields
│   ├── complaint_detail.html              # NOT MIGRATED: Displays legacy_* fields
│   ├── complaint_pdf.html                 # NOT MIGRATED: Displays legacy_* fields
│   └── inquiry_detail.html                # NOT MIGRATED: Displays legacy_* fields
├── core/
│   └── public_submit.html                 # MIGRATED: 4 forms switched to new hierarchy
├── components/
│   └── send_to_modal.html                # MIGRATED: Contact person dropdown from role holders
└── organizations/
    └── department_detail.html             # MIGRATED: Roles tab with #roleModal

DB: db.sqlite3 (production copy)

---

Troubleshooting

If showmigrations shows pending migrations

python manage.py showmigrations --skip-checks --list

If new tables are missing

Tables must be created manually (not via Django migrations):

# Check if tables exist
from django.db import connection
cursor = connection.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'organizations_%'")

If backfill shows 0 candidates

Check that legacy_subsection_id is set on complaints. Run the legacy FK backfill first.

If import doesn't match existing departments

The import matches by hospital + name_en. Check the Excel column headers match what the command expects.

System check errors

There are 4 pre-existing system check errors from accounts.User.groups vs auth.User.groups reverse accessor clash. These are NOT related to the hierarchy migration.