Phase 2: Multi-Department + RBAC + Resident Access

Overview

Duration: ~3.5 weeks (120-140 hours) Goal: Multiple departments with proper access control. PDs see their department, residents see only themselves. Demo: PD logs in → sees OB-GYN dashboard with form type toggle. Resident logs in → sees only their own data.

Prerequisites: Phase 1 complete (OB-GYN data imported, schema in place)

Timeline

Week 2D4      Week 3                        Week 4                        Week 5
├─────────────┼─────────────────────────────┼─────────────────────────────┼─────────────────────────────┤
│ Day 4-5     │ Day 1-2  │ Day 3-4 │ Day 5 │ Day 1-2  │ Day 3-4 │ Day 5 │ Day 1-2  │ Day 3-4 │ Day 5  │
│ Users +     │ RBAC     │ Auth    │ Data  │ Dynamic  │ Form    │ EM    │ Resident │ Resident│ Test   │
│ Mappings    │ Design   │ Middle  │ Filter│ UI       │ Selector│ Migr  │ Dashboard│ Polish  │ Demo   │
├─────────────┼──────────┼─────────┼───────┼──────────┼─────────┼───────┼──────────┼─────────┼────────┤
│    12h      │    8h    │   12h   │  8h   │   16h    │   8h    │  12h  │   16h    │   8h    │  12h   │
└─────────────┴──────────┴─────────┴───────┴──────────┴─────────┴───────┴──────────┴─────────┴────────┘
                                                                                               ▲
                                                                                          DEMO 2

User Stories

ID	Story	Acceptance Criteria
US-2.1	As an admin, I can upload a CSV mapping learner names to user emails	Mappings appear in `learner_mappings` table
US-2.2	As a program director, I can log in and see all residents in MY department only	PD sees full OB-GYN data, cannot see EM data
US-2.3	As a resident, I can log in and see only MY OWN evaluation data	Resident sees personal evaluations, not others
US-2.4	As a super admin, I can see data across all departments	Super admin has no department filter
US-2.5	As a dept admin, I can manage users and learner mappings for my department	Can create users, upload mappings for own dept
US-2.6	As a PD viewing OB-GYN, I can toggle between Milestones and Procedures views	Dropdown/toggle switches displayed data

Epic 2.0: User & Identity Foundation

Estimated Time: 12 hours

Task ID	Task	Description	Hours	Dependencies
2.0.1	Users Table	Create `users` table (email, name, role, department_id)	3h	Phase 1
2.0.2	Learner Mappings Table	Create `learner_mappings` table (learner_name → user_id)	3h	2.0.1
2.0.3	CSV Mapping Importer	Parse learner-to-email CSV and load into `learner_mappings`	3h	2.0.2
2.0.4	CSV Upload Endpoint	`POST /admin/upload/mappings` - accepts CSV	3h	2.0.3

Schema Additions

-- Users (for authentication and RBAC)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(200) NOT NULL UNIQUE,
    name VARCHAR(200),
    role VARCHAR(50) NOT NULL,           -- super_admin, dept_admin, program_director, resident
    department_id INTEGER REFERENCES departments(id),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Learner Mappings (links learner names from data to user accounts)
CREATE TABLE learner_mappings (
    id SERIAL PRIMARY KEY,
    department_id INTEGER REFERENCES departments(id),
    learner_name VARCHAR(200) NOT NULL,  -- From source data (Excel)
    user_id INTEGER REFERENCES users(id),
    grad_year INTEGER,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(department_id, learner_name)
);

-- Add optional user references to evaluation_responses
ALTER TABLE evaluation_responses
    ADD COLUMN evaluator_user_id INTEGER REFERENCES users(id),
    ADD COLUMN learner_user_id INTEGER REFERENCES users(id);

CSV Mapping Format

learner_name,email,grad_year

Epic 2.1: RBAC Foundation

Estimated Time: 28 hours

Task ID	Task	Description	Hours	Dependencies
2.1.1	Role Permissions Matrix	Document what each role can do	4h	None
2.1.2	Roles Implementation	Add role validation logic	4h	2.1.1, 2.0.1
2.1.3	Auth Middleware Refactor	Replace hardcoded email list with DB lookup	8h	2.0.1
2.1.4	Role Decorator	Create `@require_role('program_director')` decorator	4h	2.1.3
2.1.5	Data Filtering Layer	Queries auto-filter by user’s role + department	8h	2.1.3, 2.1.4, 2.0.2

Role Permissions Matrix

Permission	super_admin	dept_admin	program_director	resident
View all departments	✅	❌	❌	❌
View own department (all learners)	✅	✅	✅	❌
View own data only	✅	✅	✅	✅
Upload data	✅	✅	❌	❌
Manage users (own dept)	✅	✅	❌	❌
Manage users (all depts)	✅	❌	❌	❌
Upload learner mappings	✅	✅	❌	❌
Download ILP	✅	✅	✅	✅ (own)

Auth Flow

Request with x-goog-authenticated-user-email header
                    │
                    ▼
        ┌───────────────────┐
        │ Lookup user by    │
        │ email in DB       │
        └─────────┬─────────┘
                  │
        ┌─────────┴─────────┐
        │                   │
        ▼                   ▼
   User Found          User Not Found
        │                   │
        ▼                   ▼
   Set request.user    Return 403
   with role, dept     (not authorized)
        │
        ▼
   Route handler with
   @require_role checks

Epic 2.2: Multi-Department Support

Estimated Time: 20 hours

Task ID	Task	Description	Hours	Dependencies
2.2.1	Department Parameter	Update endpoints to accept/filter by `dept_id`	6h	Phase 1
2.2.2	Department Switcher	UI dropdown for users with multi-dept access	4h	2.1.3
2.2.3	EM Data Migration	Migrate `medhub_eval_response` → unified schema	8h	Phase 1 schema
2.2.4	Seed OB-GYN Department	Create OB-GYN department record, link forms	2h	Phase 1

EM Migration Strategy

Existing: medhub_eval_response (EM-specific)
              │
              │ Migration script
              ▼
New: evaluation_responses (unified)
              │
              │ Keep old table until verified
              ▼
Cutover: Update EM routes to use new table
              │
              │ After validation period
              ▼
Cleanup: Drop old table (future)

Epic 2.3: Dynamic UI

Estimated Time: 36 hours

Task ID	Task	Description	Hours	Dependencies
2.3.1	Dynamic Form Definitions API	Endpoint returns form structure for department	4h	Phase 1
2.3.2	Frontend Dynamic Columns	Update JS to render columns from API, not hardcoded	12h	2.3.1
2.3.3	Dynamic Reports	Update histogram, trending for dynamic data	8h	2.3.2
2.3.4	Answer Type Handling	Handle numeric, enum, text, comments in UI	4h	2.3.2
2.3.5	Form Type Selector	Dropdown to switch between Milestones/Procedures	6h	2.3.2
2.3.6	Comments Display	Handle comments filtering and display	2h	2.3.4

Form Type Selector UI

┌─────────────────────────────────────────────────────────────────┐
│  OB-GYN Department Dashboard                                    │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  View: [ Milestones ▼ ]    Date Range: [2024-01-01] - [2025-01] │
│        ┌─────────────┐                                          │
│        │ Milestones  │  ← Currently viewing                     │
│        │ Procedures  │                                          │
│        └─────────────┘                                          │
│                                                                 │
│  ┌─────────────────────────────────────────────────────────┐    │
│  │  Resident  │ PROF-3 │ ICS-3 │ PC-13 │ PC-1 │ ... │ Avg  │    │
│  ├────────────┼────────┼───────┼───────┼──────┼─────┼──────┤    │
│  │  Resident  │  4.2   │  4.5  │  3.8  │ 4.0  │ ... │ 4.1  │    │
│  │  Resident  │  3.9   │  4.1  │  4.2  │ 3.7  │ ... │ 4.0  │    │
│  └─────────────────────────────────────────────────────────┘    │
└─────────────────────────────────────────────────────────────────┘

Epic 2.4: Resident Self-Service (AIM 1 - Basic)

Estimated Time: 28 hours

Task ID	Task	Description	Hours	Dependencies
2.4.1	Resident Login Flow	Detect role, redirect to appropriate dashboard	4h	2.1.3
2.4.2	Personal Data API	`GET /api/me/evaluations` filtered to logged-in learner	8h	2.1.5, 2.0.2
2.4.3	Resident Dashboard Page	Personal view template and styling	12h	2.4.2
2.4.4	No-Data Handling	Graceful handling when learner has no mapping	4h	2.4.2

How Resident Data Filtering Works

1. Resident logs in with U-M L2
                    │
                    ▼
2. Auth middleware looks up user in `users` table
   → user.id = 42, user.role = 'resident', user.department_id = 1
                    │
                    ▼
3. Request hits /api/me/evaluations
                    │
                    ▼
4. Look up learner_mapping where user_id = 42
   → learner_name = "Name"
                    │
                    ▼
5. Query evaluation_responses WHERE learner_name = "Name"
   AND department_id = 1
                    │
                    ▼
6. Return only this resident's evaluations

Resident Dashboard Wireframe

┌─────────────────────────────────────────────────────────────────┐
│  Mi-TRAC - Resident name (PGY-3, OB-GYN)              [Logout]  │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  My Evaluations                                                 │
│  ───────────────                                                │
│  View: [ Milestones ▼ ]                                         │
│                                                                 │
│  ┌─────────────────────────────────────────────────────────┐   │
│  │  Date       │ Evaluator    │ Form    │ Score │ Details  │   │
│  ├─────────────┼──────────────┼─────────┼───────┼──────────┤   │
│  │  2025-01-05 │ Dr. One      │ PROF-3  │  4.5  │  [View]  │   │
│  │  2025-01-03 │ Dr. Two      │ PC-13   │  4.0  │  [View]  │   │
│  │  2024-12-28 │ Dr. Threee   │ C-Sect  │  4.2  │  [View]  │   │
│  └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│  Total Evaluations: 47  │  Average Score: 4.1                  │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Epic 2.5: Future/Budget Items

Task ID	Task	Description	Priority
2.5.1	Cohort Comparison	Anonymized comparison to PGY cohort	Future
2.5.2	TRUE SCORE Calculation	True score for dynamic forms	TBD
2.5.3	Additional OB-GYN Forms	Teaching, M&M, Journal Club, etc.	Stretch
2.5.4	User Self-Registration	New users request access, admin approves	Future
2.5.5	Individual Mapping Editor	UI for editing learner mappings one-by-one	Future

Deliverables

Deliverable	Description	Format
Users & Mappings Tables	User accounts and learner identity linking	Alembic migrations
CSV Mapping Upload	Endpoint for bulk learner-email mapping	Flask route
RBAC System	Role-based access control with decorators	Python middleware
Multi-Dept Support	Department filtering on all queries	Python/SQL
Dynamic UI	Frontend handles any form type	JavaScript
Form Type Selector	Dropdown for Milestones vs Procedures	UI component
Resident Dashboard	Personal data view for residents	HTML/JS template
EM Migration	Existing data moved to unified schema	Migration script

Demo Script (End of Week 5)

Demo 2A: Admin - User & Mapping Setup

Upload learner mappings CSV: POST to /admin/upload/mappings
Show mappings created: Query learner_mappings table
Create PD user: Add program director to users table
Create resident user: Add resident to users table

Demo 2B: Program Director View

Login as OB-GYN PD: Navigate to app, authenticate
Show department dashboard: All OB-GYN residents visible
Toggle form type: Switch from Milestones to Procedures
Show data changes: Different columns, different scores
Attempt to view EM: Should be blocked or not visible