# Speaker Sessions Manager — Design Document

**Date:** 2026-03-04
**Status:** Approved

## Overview

Add a Speaker Sessions manager to the career fair event system, enabling admin staff to create and manage session schedules (time slots, titles, companies, speakers, Zoom links) for career fair events. Employers can view the schedule and manage their own speaker details via the portal. Admin can email the full schedule to all participating employers.

## Requirements (from brainstorming)

- Sessions are simple time slots (no session-type distinction)
- Usually one speaker per session, but support multiple when needed
- Tied to a specific career fair event (company_id)
- Single-day events with start_time / end_time per session
- Admin manages the master schedule; employers can view and update their speaker details via portal
- Email full event schedule to all participating employers
- Speaker confirmation tracking (pending/confirmed/declined)
- Speaker bio & headshot collection

## Architecture Decision

**Two-table relational model** (`event_sessions` + `event_session_speakers`). Chosen over single-table-with-JSON because:
- Clean querying by employer ("show me all sessions for employer X")
- Independent speaker add/remove without touching session record
- Matches existing pattern (event_attendees is a child of resume_recommendation_clients)

## Database Tables (career_center)

### event_sessions

| Column | Type | Purpose |
|--------|------|---------|
| session_id | INT AUTO_INCREMENT PK | |
| company_id | INT NOT NULL | FK to hbcu_central.resume_recommendation_clients |
| session_title | VARCHAR(255) NOT NULL | e.g. "Careers in Tech Panel" |
| start_time | TIME NOT NULL | e.g. 10:00:00 |
| end_time | TIME NOT NULL | e.g. 10:45:00 |
| session_url | VARCHAR(500) | Zoom/meeting link |
| description | TEXT | Optional session description |
| sort_order | INT DEFAULT 0 | Manual ordering within event |
| active | TINYINT(1) DEFAULT 1 | Soft delete |
| created_at | DATETIME DEFAULT NOW() | |
| created_by | VARCHAR(50) | Admin login_id |

Indexes: `idx_company (company_id)`, `idx_active (company_id, active)`

### event_session_speakers

| Column | Type | Purpose |
|--------|------|---------|
| speaker_id | INT AUTO_INCREMENT PK | |
| session_id | INT NOT NULL | FK to event_sessions |
| employer_id | INT NOT NULL | FK to career_center.employer_data |
| speaker_name | VARCHAR(255) NOT NULL | |
| speaker_email | VARCHAR(255) | |
| speaker_title | VARCHAR(255) | Job title/role |
| speaker_bio | TEXT | Bio for marketing materials |
| headshot_url | VARCHAR(500) | Public URL to headshot |
| headshot_path | VARCHAR(500) | Server file path |
| confirmed | TINYINT(1) DEFAULT 0 | 0=pending, 1=confirmed, 2=declined |
| confirmed_date | DATETIME | When confirmed/declined |
| active | TINYINT(1) DEFAULT 1 | Soft delete |
| created_at | DATETIME DEFAULT NOW() | |

Indexes: `idx_session (session_id)`, `idx_employer (employer_id)`, `idx_session_active (session_id, active)`

## File Changes

### events.pl — New shared functions

```
getEventSessions($company_id)                    # All active sessions with speakers joined
getSessionSpeakers($session_id)                  # Speakers for one session
getEmployerSessions($company_id, $employer_id)   # Sessions where employer has a speaker
saveEventSession($data)                          # Insert or update session
deleteEventSession($session_id)                  # Soft delete (active=0)
saveSessionSpeaker($data)                        # Insert or update speaker
deleteSessionSpeaker($speaker_id)                # Soft delete (active=0)
updateSpeakerHeadshot($speaker_id, $url, $path)  # Headshot columns
buildScheduleEmailHTML($company_id, $event)      # Render schedule as email HTML
```

### event_sponsor_manager.cgi — New "Sessions" tab

- Tab added to `printEventSubNav()` in events.pl
- Sessions table: Time, Title, Company, Speaker(s), Confirmed?, Zoom Link
- Add/Edit Session modal:
  - Session title, start time, end time, Zoom URL, description
  - Company dropdown (from getParticipatingEmployers)
  - Speaker: name, email, title, bio, headshot upload
  - "Add Another Speaker" for multi-speaker sessions
- Per-speaker actions: confirm/decline, send reminder email
- Bulk actions: "Email Schedule to All", "Email to Selected Employer"
- Sort by time (default) or drag-to-reorder via sort_order

### portal.cgi — New "Sessions" tab

- Full schedule view (all sessions for the event, read-only)
- Employer's own sessions highlighted
- Editable for their speakers only: bio, headshot upload, confirmation toggle
- Download schedule button (styled HTML)

### New AJAX actions

**event_sponsor_manager.cgi:**
- `save_session` — create/update session + primary speaker
- `delete_session` — soft delete
- `add_speaker` — add additional speaker to session
- `delete_speaker` — remove speaker
- `confirm_speaker` — toggle confirmation status
- `send_speaker_reminder` — email one speaker
- `email_schedule` — email full schedule to one or all employers

**portal.cgi:**
- `update_speaker` — employer updates their speaker's bio/confirmation
- `upload_speaker_headshot` — employer uploads headshot
- `get_sessions` — fetch sessions JSON for portal display

## File Storage

```
events/assets/{company_id}/speakers/headshot_{speaker_id}.{ext}      # Full-size
events/assets/{company_id}/speakers/headshot_{speaker_id}_thumb.{ext} # 150x150 thumbnail
```

## Email Templates

### Full Schedule Email
- Sent to all participating employers (or selected ones)
- Shows: event name, date, full session list with times, titles, speakers, Zoom links
- Uses standard HBCU Connect email template (600px card layout, all inline CSS)
- CTA button: "View Full Schedule" linking to portal

### Speaker Confirmation Request
- Sent to individual speaker email
- Shows: their session title, time, event name
- CTA: "Confirm Your Session" (links to portal if employer has access, or reply-to-confirm)

### Speaker Reminder
- Nudge for unconfirmed speakers
- Similar to confirmation request with "Reminder:" prefix

## Not In Scope (YAGNI)

- Session types/categories (keynote vs panel vs breakout)
- Room/stage assignments
- Public-facing schedule page (can add later)
- Calendar file (.ics) generation
- Drag-and-drop reordering (use sort_order field, manual entry)
- Speaker self-registration (speakers are added by admin or employer)