Law Student Tracker - Val.town Project

Automates attendance tracking and cold-call selection for both T&E and L&C classes.

Architecture

Two Google Spreadsheets:

Spreadsheet	Purpose	Env Var
Main	Rosters, Cold Call Log, derived tabs	`MAIN_SHEET_ID`
Student Forms	Student self-report Google Form responses	`STUDENT_FORMS_SHEET_ID`

Sarah submits reports via HTML form (no Google Form needed for her).

Vals

File	Type	Purpose
Sarah's Interface
`sarah-report-form.http.tsx`	HTTP	HTML form for Sarah to submit post-class reports
`post-class-report.http.tsx`	HTTP	API: receives reports, updates tracking, triggers alerts
T&E (Trusts & Estates)
`oncall-selection.http.tsx`	HTTP	Algorithmically selects 6 students for cold-calling
`oncall-notify.cron.tsx`	Cron	Emails on-call list before class (3 PM)
`te-report-prompt.cron.tsx`	Cron	Reminds Sarah to submit T&E report (6 PM)
L&C (Law & Capitalism)
`lc-oncall-selection.http.tsx`	HTTP	Looks up preset on-call from syllabus
`lc-oncall-notify.cron.tsx`	Cron	Emails on-call list before class (8 AM)
`lc-report-prompt.cron.tsx`	Cron	Reminds Sarah to submit L&C report (12 PM)
Utilities
`weekly-summary.cron.tsx`	Cron	Friday summary of students above thresholds
`data-integrity-check.http.tsx`	HTTP	Compares roster counts vs Cold Call Log
`refresh-watch-list.http.tsx`	HTTP	Manually refresh Watch-List tab
`assign-pools.http.tsx`	HTTP	Calculate optimal A-Z pool split for T&E
`utils.tsx`	Script	Shared utilities, constants, types

Environment Variables

Set in Val.town UI (Settings → Environment Variables):

Variable	Purpose
`GOOGLE_APPLICATION_CREDENTIALS`	Service account JSON (full string)
`REPORT_API_KEY`	Secret key for report submissions
`MAIN_SHEET_ID`	Main spreadsheet (rosters, logs)
`STUDENT_FORMS_SHEET_ID`	Student self-report form responses

Sheet Structure

Main Spreadsheet (MAIN_SHEET_ID)

Source of Truth (Sarah can edit to fix data):

Tab	Purpose
`T&E-Roster`	Student list + cumulative counters
`L&C-Roster`	Student list + cumulative counters
`Cold-Call-Log`	Audit trail of all cold calls
`L&C-Schedule`	Preset on-call assignments from syllabus

Derived (auto-generated, don't edit):

Tab	Purpose
`T&E-On-Call-Today`	Today's T&E selection (cached)
`L&C-On-Call-Today`	Today's L&C selection (cached)
`Watch-List`	Students approaching thresholds
`Pending-Review`	Unmatched names for manual review
`Cancelled-Classes`	Log of cancelled class dates

Roster Columns

student_name, last_name, pool (T&E only)
cold_call_count, last_called
absences, absences_announced, absences_unannounced
concerning_events, refused_count, bad_performance_count
last_absence_watch, last_absence_warning, last_absence_alert
last_concerning_watch, last_concerning_warning, last_concerning_alert

Student Forms Spreadsheet (STUDENT_FORMS_SHEET_ID)

Tab	Purpose
`Form Responses 1`	Student self-reports (Google Form writes here)

Alert Thresholds

Early Watch ([WATCH])

2 absences → Watch alert (early warning)
3 concerning events → Watch alert

Warning ([WARNING])

3 absences → Sarah warns student
4 concerning events → Sarah warns student

Alert ([ALERT])

4 absences → Contact dean + student
5 concerning events → Contact student

Concerning events = absences + refused + bad performance + unprepared

Sarah's Workflow

Before class: Receives on-call email automatically
After class: Opens HTML form, fills out report, submits
On alerts: Receives email with student details and required action

Form URL: https://sahar--{hash}.web.val.run (get from Val.town UI)

API Reference

POST /post-class-report

curl -X POST "https://sahar--{hash}.web.val.run" \
  -H "Content-Type: application/json" \
  -H "X-API-Key: $REPORT_API_KEY" \
  -d '{
    "date": "2026-01-12",
    "class_type": "te",
    "cold_calls": [
      {"student_name": "Alice Adams", "result": "completed", "performance": "great"},
      {"student_name": "Bob Baker", "result": "absent"},
      {"student_name": "Carol Chen", "result": "refused"}
    ],
    "absences": ["David Davis"]
  }'

cold_call results:

completed + performance (great/normal/bad)
absent (unannounced - didn't show up)
refused (present but declined)

absences: Students not on call list who were absent

Cron Schedules (UTC)

Val	EST Time	UTC Cron
`oncall-notify`	3 PM Mon/Wed	`0 20 * * 1,3`
`te-report-prompt`	6 PM Mon/Wed	`0 23 * * 1,3`
`lc-oncall-notify`	8 AM Mon/Wed	`0 13 * * 1,3`
`lc-report-prompt`	12 PM Mon/Wed	`0 17 * * 1,3`
`weekly-summary`	9 AM Fri	`0 14 * * 5`

Note: Adjust for DST. EST = UTC-5, EDT = UTC-4.

Development

# Deploy changes
~/.deno/bin/vt push

# Pull remote changes
~/.deno/bin/vt pull

sahar

law-student-tracker