Event Attendance System

The event attendance system records which users attended which events and allows team managers to assign each attendee a service station for the day. It is designed for simplicity: volunteers check themselves in with a single action, and managers handle assignments separately.

Overview

The attendance system supports:

• Self Check-in: Any volunteer (level 2+) can mark themselves present by posting to the attendance endpoint
• Idempotency: Duplicate check-ins for the same (user_id, event_id) pair are silently ignored
• Station Assignment: Team managers (level 3+) assign a service station to each attendee after check-in
• Event Count Tracking: Query all attendance records per user to count events attended
• Dual Provider Support: Identical behavior on Supabase (PostgreSQL) and Turso (LibSQL/SQLite)

🙋 Self Check-in

Volunteers log in and click “I’m here.” The API records their attendance automatically — no manual user ID required.

🗂️ Station Assignment

Team managers assign each attendee a station for the day: clothing, haircuts, showers, hygiene, front desk, trailer, shoe station, or other.

🔒 Role-Gated

Check-in requires volunteer role (level 2+). Assignment requires team_manager role (level 3+). Members cannot check in.

⚡ Dual Provider

Supabase uses PostgreSQL RLS + service role; Turso uses raw SQL with INSERT OR IGNORE. Both expose identical TypeScript method signatures.

---

Data Model

TypeScript Interfaces

import type {
  EventAttendance,
  EventAttendanceData,
  EventAttendanceQueryOptions,
  AttendanceAssignment,
  ATTENDANCE_ASSIGNMENTS,
} from '#libs/database-types'

EventAttendance — retrieved record

interface EventAttendance {
  id: string                           // UUID
  user_id: string                      // DB user id (not Clerk id)
  event_id: string                     // Event UUID
  attended_at: string                  // ISO timestamp of check-in
  created_by: string | null            // DB user id who recorded the check-in
  assignment: AttendanceAssignment | null  // Station assigned by a manager, or null
}

EventAttendanceData — input for check-in

interface EventAttendanceData {
  user_id: string
  event_id: string
  created_by?: string | null
}

AttendanceAssignment — valid station values

type AttendanceAssignment =
  | 'clothing'
  | 'haircuts'
  | 'showers'
  | 'hygiene'
  | 'front_desk'
  | 'trailer'
  | 'shoe_station'
  | 'other'

Note

assignment starts as null when a volunteer checks in. A team manager sets it afterward via the PATCH endpoint.

---

Database Schema

Migration 026

Both providers share the same schema. Supabase uses uuid columns; Turso uses TEXT.

Supabase (PostgreSQL)

CREATE TABLE IF NOT EXISTS event_attendance (
  id          uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  event_id    uuid        NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  attended_at timestamptz NOT NULL DEFAULT now(),
  created_by  uuid        REFERENCES users(id) ON DELETE SET NULL,
  assignment  text,
  UNIQUE (user_id, event_id)
);

Turso (LibSQL/SQLite)

CREATE TABLE IF NOT EXISTS event_attendance (
  id          TEXT NOT NULL PRIMARY KEY,
  user_id     TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  event_id    TEXT NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  attended_at TEXT NOT NULL DEFAULT (datetime('now')),
  created_by  TEXT,
  assignment  TEXT,
  UNIQUE(user_id, event_id)
);

Run the migration:

npm run db:migrate

---

Database Operations

All operations are accessed through the factory — never import providers directly.

import { getDatabase } from '#libs/database'
const db = getDatabase()

recordAttendance(data)

Records a user’s attendance at an event. Idempotent — calling it twice for the same pair returns the existing record’s id.

const id = await db.recordAttendance({
  user_id: 'db-user-uuid',
  event_id: 'event-uuid',
  created_by: 'db-user-uuid',  // typically same as user_id for self check-in
})

getAttendance(options?)

Returns attendance records with optional filtering and pagination.

// All attendees for an event
const records = await db.getAttendance({ event_id: 'event-uuid' })

// All events a specific user attended
const userHistory = await db.getAttendance({ user_id: 'db-user-uuid' })

// Count events attended by a user
const count = await db.getAttendanceCount({ user_id: 'db-user-uuid' })

assignAttendance(attendanceId, assignment)

Sets the station assignment on an existing attendance record. Called by team managers after check-in.

await db.assignAttendance('attendance-uuid', 'clothing')

deleteAttendance(id)

Removes an attendance record by id. Returns true on success.

await db.deleteAttendance('attendance-uuid')

---

API Endpoints

POST /api/events/:id/attendance — Self Check-in

Volunteers call this to record their own attendance. The user_id is derived from the authenticated session — it cannot be overridden in the request body.

Minimum role: volunteer (level 2)

Request body:

{
  "_csrf": "<csrf-token>"
}

Responses:

Status	Body
201	{ "success": true, "id": "attendance-uuid" }
401	{ "error": "Unauthorized" }
403	{ "error": "Forbidden", "details": "Volunteer role or higher required" }
403	{ "error": "Security validation failed" }
404	{ "error": "User not found" }
429	Rate limit exceeded
500	{ "error": "Failed to record attendance" }

Tip

The endpoint is idempotent. Calling it again for the same event returns 201 with the same attendance id.

---

GET /api/events/:id/attendance — List Attendees

Returns all attendance records for an event including assignment status.

Minimum role: any authenticated user

Response: EventAttendance[]

[
  {
    "id": "att-uuid",
    "user_id": "user-uuid",
    "event_id": "event-uuid",
    "attended_at": "2026-04-07T10:00:00Z",
    "created_by": "user-uuid",
    "assignment": "clothing"
  },
  {
    "id": "att-uuid-2",
    "user_id": "user-uuid-2",
    "event_id": "event-uuid",
    "attended_at": "2026-04-07T10:01:00Z",
    "created_by": "user-uuid-2",
    "assignment": null
  }
]

---

PATCH /api/events/:id/attendance/:attendanceId — Assign Station

Team managers use this to assign a service station to an attendee after they have checked in.

Minimum role: team_manager (level 3)

Request body:

{
  "assignment": "clothing",
  "_csrf": "<csrf-token>"
}

Valid assignment values: clothing, haircuts, showers, hygiene, front_desk, trailer, shoe_station, other

Responses:

Status	Body
200	{ "success": true }
400	{ "error": "Validation failed", "details": "..." }
401	{ "error": "Unauthorized" }
403	{ "error": "Forbidden", "details": "Team manager role or higher required" }
500	{ "error": "Failed to assign attendance" }

---

Role Hierarchy Summary

Action	Minimum Role	Level
View attendees (GET)	Any authenticated user	1+
Self check-in (POST)	volunteer	2+
Assign station (PATCH)	team_manager	3+

See Configurable Roles for the full role hierarchy.

---

Typical Flow

1. Event day begins. Volunteers arrive and log in.
2. Volunteer checks in. They tap Check in on the Service Day page (VolunteerCheckIn) — the UI calls POST /api/events/:id/attendance. The assignment field is null.
3. Manager views attendees. Calls GET /api/events/:id/attendance to see who has checked in and who still needs an assignment.
4. Manager assigns stations. For each attendee, calls PATCH /api/events/:id/attendance/:attendanceId with the chosen station.
5. Reporting. Call getAttendanceCount({ user_id }) to count events attended per volunteer.

---

See Also

• Events System — Creating and managing events
• Configurable Roles — Role levels and hierarchy
• Database Architecture — Provider abstraction layer