## 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:**
```python
# 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):**
```python
# 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):**
```python
# 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