IHNYC Games Data Model

Source: ihnyc-games/infra/d1/schema.sql

---

Tables

events

• id (TEXT, PK)
• title (TEXT)
• sport (TEXT)
• description (TEXT)
• location (TEXT)
• start_at (TEXT)
• end_at (TEXT)
• capacity (INTEGER)
• form_url (TEXT)
• source_label (TEXT)
• created_at (TEXT)

results

• id (TEXT, PK)
• event_id (TEXT, FK → events.id)
• building (TEXT: north|south)
• floor_number (INTEGER)
• placement (INTEGER)
• points (INTEGER)
• medal (TEXT: gold|silver|bronze|NULL)
• created_at (TEXT)

import_runs

• id (TEXT, PK)
• created_at (TEXT)
• rows_read (INTEGER)
• inserted (INTEGER)
• skipped (INTEGER)
• errors_json (TEXT)

signup_submissions

• id (TEXT, PK)
• import_run_id (TEXT, FK → import_runs.id)
• name (TEXT)
• email (TEXT)
• room (TEXT)
• building (TEXT: north|south|NULL)
• floor_number (INTEGER)
• response_at (TEXT)
• raw_json (TEXT)
• created_at (TEXT)

submission_event_choices

• id (TEXT, PK)
• submission_id (TEXT, FK → signup_submissions.id)
• event_id (TEXT, FK → events.id)
• source_label (TEXT)
• created_at (TEXT)

---

Import matching rules

• Parse choices from “Which event(s) do you want to participate in?” only.
• Strip day/time from choices (keep the sport name).
• Ignore “Any other sports/games you’d like to play?”
• Match by exact events.source_label or exact events.title.
• If no exact match, fallback to prefix match on events.title.
• On import, keep only the latest submission per room (older rows are deleted).
• Roster signup counts are computed on distinct submissions (not event choice rows).
• Room parsing strips notes and uses the first alphanumeric token for building/floor inference.
• All raw submissions are stored, even if mapping fails.

---

Indexes

• events(start_at)
• results(event_id), results(floor_number)
• import_runs(created_at)
• signup_submissions(name, room, floor_number)
• submission_event_choices(submission_id, event_id)