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.

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)

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

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
}

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

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

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

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)
);

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

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

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

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
})

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' })

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

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

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

await db.deleteAttendance('attendance-uuid')

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:

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
  }
]

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:

See Configurable Roles for the full role hierarchy.

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.

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