RTSport Build Integration Log

Build Date: 2026-05-01

Status

Integration Test Results (2026-05-02)

Database: SQLite (rtsport_test.db)
Seed Data: 1 school, 3 athletes, 2 cases, 3 milestones, 3 events

Tests Passed

FERPA Verification

Key Behaviors Verified

1. Auto-case-creation: New body_part → new Case + Event + status update
2. Case reopening: 30-day window checked via reopened_from_case_id
3. Status sync: DB trigger updates athlete.current_status on events
4. Visibility filtering: Events hidden based on visibility array
5. Clinical notes stripping: API layer removes for non-AT roles

SQLite Compatibility

Models use PostgreSQL types (JSONB, ARRAY) with SQLite fallback:
- JSONB → JSON (SQLite native)
- ARRAY → Custom type storing JSON string

Production: Set DATABASE_URL=postgresql://... to use native PostgreSQL types.

Next Steps

JWT Auth Implementation (2026-05-02)

New Files Created

Files Modified

JWT Token Format

Header:

{
  "alg": "HS256",
  "typ": "JWT"
}

Payload:

{
  "sub": "usr_001",           // user_id
  "role": "at",               // "at" | "coach" | "parent" | "admin"
  "school_id": "schl_001",
  "exp": 1719878400,          // expiration timestamp
  "iat": 1719874800,          // issued at
  "type": "access"
}

Signature: HMAC-SHA256(secret_key, base64(header) + "." + base64(payload))

API Endpoints Added

Protected Endpoints (All now require JWT)

All existing endpoints now require Authorization: Bearer <token> header:

Dependencies Added

python-jose[cryptography]==3.3.0
passlib[bcrypt]==1.7.4

Usage Examples

Login:

curl -X POST http://localhost:8000/api/v1/auth/login \
     -H "Content-Type: application/x-www-form-urlencoded" \
     -d "username=at@preble.k12.wi.us&password=testpass123"

Response:

{
  "access_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
  "token_type": "bearer",
  "role": "at",
  "school_id": "schl_001"
}

Use token:

curl -H "Authorization: Bearer <token>" \
     http://localhost:8000/api/v1/roster?school_id=schl_001

Backend Structure

backend/
├── app/
│   ├── main.py           # FastAPI app factory ✅
│   ├── config.py         # Settings management ✅
│   ├── database.py       # SQLAlchemy engine/session ✅
│   ├── schemas.py        # 5 Pydantic schemas ✅
│   ├── models.py         # 6 SQLAlchemy models ✅ (User + 5 domain models)
│   ├── auth.py           # JWT authentication ✅
│   └── api/
│       ├── auth.py       # POST /api/v1/auth/login ✅
│       ├── roster.py     # GET/POST/PATCH/DELETE /api/v1/roster ✅
│       ├── cases.py      # GET/POST/PATCH/DELETE /api/v1/cases/* ✅
│       └── events.py     # POST /api/v1/events/sideline-entry ✅
├── requirements.txt      # Dependencies ✅
├── .env.example          # Environment template ✅
└── README.md             # Setup instructions ✅

Implemented Schemas (Pydantic v2)

1. SchoolSchema - Multi-tenant boundary
2. AthleteSchema - Cached current_status
3. CaseSchema - severity vs attention_level distinction
4. MilestoneSchema - Forward-looking targets
5. EventSchema - FERPA visibility controls

Plus: SidelineEntryRequest/Response - 3-tap rapid entry

Implemented Endpoints

FERPA Implementation Notes

• visibility defaults to ["at"] in EventCreate
• clinical_notes in EventContent schema
• Role filtering via JWT claims (role, school_id)
• Parent access validated via parent_ids array on Athlete
• Tenant isolation enforced via school_id matching

Open Questions / Ambiguities — RESOLVED

Wadsworth Fixes Applied (2026-05-02 00:23 UTC)

1. ID Generation (app/models.py):
   - Replaced UUID with short numeric: ath_102938 format
   - Uses timestamp + random for uniqueness without DB sequence dependency

2. Case Reopening (app/models.py + app/schemas.py):
   - Added reopened_from_case_id (FK to cases.id, nullable)
   - Added reopened_at (datetime, nullable)
   - Added self-referential relationship original_case / reopened_cases
   - Updated CaseSchema + CaseUpdate to include reopening fields

Current Status (Socrates Implementation - 2026-05-02)

Completed Work

1. Database Queries (CRUD Operations)

Roster (api/roster.py):
- ✅ GET /api/v1/roster — Full implementation with role-based filtering
- AT role: full roster access
- Coach role: team-scoped only (via X-Coach-Teams header)
- Parent role: 403 forbidden
- Optional filters: sport, team
- ✅ GET /api/v1/roster/{athlete_id} — Single athlete lookup with team access check
- ✅ POST /api/v1/roster — Create athlete (AT only)
- ✅ PATCH /api/v1/roster/{athlete_id} — Update athlete (AT only)
- ✅ DELETE /api/v1/roster/{athlete_id} — Delete athlete (AT only)

Cases (api/cases.py):
- ✅ GET /api/v1/cases/athlete/{athlete_id} — Active + resolved cases with FERPA filtering
- Coach: active cases only (hides severity/attention_level)
- Parent: own child only
- AT: full access
- ✅ GET /api/v1/cases/{case_id}/timeline — Events with visibility + clinical_notes stripping
- ✅ GET /api/v1/cases/{case_id}/milestones — All milestones (all roles can read)
- ✅ POST /api/v1/cases — Create case (AT only) with 30-day reopening check
- ✅ PATCH /api/v1/cases/{case_id} — Update case (AT only)
- ✅ DELETE /api/v1/cases/{case_id} — Delete case (AT only)

Events (api/events.py):
- ✅ POST /api/v1/events/sideline-entry — 3-tap rapid entry with auto-case-creation
- ✅ POST /api/v1/events/general — Create general event (AT only)

2. Business Logic

Sideline Entry Auto-Case Creation:
- ✅ find_active_case_for_body_part() — Fuzzy title matching
- ✅ find_recent_resolved_case() — 30-day window check for reopening
- ✅ create_case_from_sideline_entry() — Case creation with reopen tracking
- ✅ Auto-update athlete.active_case_ids
- ✅ Status logic: removed_from_play → "out", severity ≥ moderate → "restricted"

Case Reopening (30-day window):
- ✅ Check for resolved case within 30 days
- ✅ Set reopened_from_case_id and reopened_at when reopening
- ✅ Create new case linked to original

FERPA Gate (API Layer):
- ✅ strip_clinical_notes() — Remove clinical_notes from responses
- ✅ filter_events_by_visibility() — Filter by visibility array
- ✅ Role-based access control in every endpoint
- ✅ Enum validation helpers

3. Database Trigger (FIXED)

Fixed update_athlete_status_on_event in models.py:
- ❌ Bug: Used target.case_id instead of looking up athlete_id
- ✅ Fix: Query Case table to get athlete_id before updating Athlete
- ✅ Applied to both clearance_granted and restriction_added event types

4. Validation + Error Handling

• ✅ Enum validation (severity, attention_level, status, event_type)
• ✅ 404 for missing resources (school, athlete, case)
• ✅ 403 for unauthorized access (role-based)
• ✅ 400 for invalid payloads (grade range, invalid enums)

5. Supporting Infrastructure

• ✅ Created app/utils/id_generator.py — Centralized ID generation
• ✅ Updated imports in all API modules
• ✅ Role extraction via headers (stub for auth layer)

Files Modified

Implementation Notes

Role-Based Access:
- Role extracted from X-Role header (defaults to "at" for development)
- User ID extracted from X-User-ID header
- Real auth/JWT deferred as specified

Stub Roles:
- AT: Full access
- Coach: Team-scoped roster, active cases only
- Parent: Own child only, roster forbidden

FERPA Compliance:
- clinical_notes stripped for non-AT roles
- Visibility filtering on timeline
- Parent access validated via parent_ids array

Status Summary

Next Steps

Frontend Alignment Summary (Daedalus - 2026-05-02)

Status: ✅ COMPLETE

What Was Aligned

All frontend templates have been updated to consume the real backend endpoints:

Frontend-Backend Mismatches Found & Resolved

Files Updated

• frontend/templates/at/dashboard.html — HTMX integration for roster and sideline entry form
• frontend/templates/coach/dashboard.html — HTMX roster loading with sport filters
• frontend/templates/parent/dashboard.html — HTMX timeline with role-based FERPA filtering
• frontend/templates/components/shared-timeline.html — Visibility badges, role selector demo
• frontend/static/js/mock-data.js — Schema-aligned mock data (AthleteSchema, CaseSchema, EventSchema, MilestoneSchema)

Key Schema Alignments Verified

AthleteSchema:
- ✅ current_status enum: cleared | restricted | out
- ✅ active_case_ids array displayed as case count badge

SidelineEntryRequest:
- ✅ school_id (hidden field)
- ✅ athlete_id (dropdown selection)
- ✅ body_part (text input for "Ankle - Right" format)
- ✅ severity (select: mild/moderate/severe)
- ✅ attention_level (select: urgent/warning/stable)
- ✅ removed_from_play (checkbox)
- ✅ notes (optional textarea)

EventSchema (FERPA):
- ✅ visibility array determines who sees each event
- ✅ clinical_notes only visible to AT role (handled by backend)
- ✅ Event types: note, status_change, restriction_added, clearance_granted

CaseSchema:
- ✅ severity vs attention_level distinction maintained
- ✅ reopened_from_case_id and reopened_at fields included

ETA

Completed: 2026-05-02 00:30 UTC

Coordination

• Frontend now ready for backend implementation
• HTMX attributes in place for live API integration
• Mock data matches real schema shapes for testing
• Contract remains source of truth at docs/contract.md