Handoff: RTSport Database Queries + Business Logic Implementation

What: Implement actual database queries, business logic, and wire endpoints to real data. Move from scaffold to working backend.

Why: Scaffold is complete, frontend is aligned. Now the backend needs real SQL queries, business rules, and the sideline-entry auto-case-creation logic.

Files:
- /home/hoffmann_admin/.openclaw/shared/build-20260501/backend/app/ — Your scaffold (modify these)
- /home/hoffmann_admin/.openclaw/shared/build-20260501/docs/contract.md — Source of truth
- /home/hoffmann_admin/.openclaw/shared/build-20260501/integration.md — Update as you work

What's Already Done:
- ✅ Pydantic schemas (schemas.py)
- ✅ SQLAlchemy models (models.py)
- ✅ FastAPI app factory (main.py)
- ✅ Router stubs (api/roster.py, api/cases.py, api/events.py)
- ✅ ID generation (short numeric format matching contract)
- ✅ Case reopening fields (reopened_from_case_id, reopened_at)

What You Need to Implement:

1. Database Queries (CRUD Operations)

Roster (api/roster.py):
- GET /api/v1/roster — Query with filters (school_id, sport, team)
- AT role: full roster
- Coach role: team-scoped only
- Parent role: no access (return 403)
- GET /api/v1/roster/{athlete_id} — Single athlete with cached status

Cases (api/cases.py):
- GET /api/v1/cases/{athlete_id} — Active + resolved cases
- Coach: active status only (no severity, no clinical details)
- Parent: own child only
- AT: full access
- GET /api/v1/cases/{case_id}/timeline — Events with FERPA filtering
- Strip clinical_notes from EventContent for non-AT roles
- Filter by visibility array
- GET /api/v1/cases/{case_id}/milestones — All milestones (all roles can read)

Events (api/events.py):
- POST /api/v1/events/sideline-entry — The 3-tap rapid entry
- Auto-create Case if no active case for athlete + body_part
- Create initial Event
- Update athlete.current_status via trigger/hook
- Return SidelineEntryResponse

2. Business Logic

Sideline Entry Auto-Case Creation:

# Pseudo-logic:
1. Look for active case where case.athlete_id == athlete_id 
   AND case.title contains body_part substring
2. If found: use existing case
3. If not found: create new Case with:
   - title = f"{body_part} Injury"
   - severity = from request
   - attention_level = from request
   - status = "active"
   - opened_at = now
4. Create Event with event_type based on removed_from_play:
   - True: "restriction_added" (sets athlete to "out")
   - False: "note" (sets athlete to "restricted" if severity >= moderate)
5. Update athlete.current_status via DB trigger
6. Return SidelineEntryResponse

Case Reopening (30-day window):

# Pseudo-logic:
1. On new sideline entry for same athlete + body_part
2. Check if resolved case exists within 30 days
3. If yes: create new Case with reopened_from_case_id = original_case.id
4. If no: create fresh Case

FERPA Gate (API Layer):

# Pseudo-logic for every endpoint:
1. Extract role from request (stub for now — auth layer deferred)
2. If role != "at":
   - Strip "clinical_notes" key from EventContent
   - Filter events where "at" not in visibility
3. If role == "coach":
   - Cases: return only active status (hide severity, attention_level)
   - Roster: team-scoped only
4. If role == "parent":
   - Cases: only for own child (check parent_ids contains user_id)
   - Events: only where "parent" in visibility

3. Database Trigger (Fix Current Stub)

The update_athlete_status_on_event trigger in models.py has a bug — it tries to update Athlete using target.case_id instead of looking up the athlete_id from the Case. Fix this.

4. Validation & Error Handling

• Validate enums (severity, attention_level, event_type, status)
• Return 404 for missing resources
• Return 403 for unauthorized access
• Return 400 for invalid payloads

Scope:
- ✅ DO: Implement all CRUD queries
- ✅ DO: Implement sideline entry business logic
- ✅ DO: Fix the DB trigger
- ✅ DO: Add FERPA filtering (API layer, auth stubbed)
- ❌ DON'T: Implement real auth/JWT (deferred)
- ❌ DON'T: Write tests yet
- ❌ DON'T: Change frontend templates

Success Criteria:
- All endpoints return real data from PostgreSQL
- Sideline entry creates cases + events automatically
- FERPA filtering strips clinical_notes for non-AT
- DB trigger updates athlete.current_status correctly
- No mock data — all real queries

ETA: TBD — provide your own after review

Coordination:
- Frontend is aligned and stable — don't break contract
- Update shared/build-20260501/integration.md with progress
- Questions? Ping via shared directory or Wadsworth will relay