Files
joshuaandClaude Opus 5.5 9dda0d1851 Feedback page: results, responses, who hasn't answered, reminders (2.1.0)
- Supervisor -> Feedback (supervisors and admins, campus-scoped): every
  event's form with answered of asked, response rate, average rating and
  last reminder; per event the results per question (with the AI summary),
  each response, and on named forms who was asked and who has answered.
- Remind those who haven't answered (or one person on a named form): a
  fresh link, plus the form to anyone eligible who hasn't had it; nobody gets
  the form or a reminder twice within 24 hours. The 3-day job is unchanged.
- Anonymous forms show no names and no list of who has answered.
- FeedbackInvite.reminderCount (migration 20261103120000_feedback_reminders);
  access log action feedback_responses; help page; dashboard and Ctrl+K links.
- Roadmap: the AI helpers on the privacy pages are dropped; 2.1.0 is the
  feedback page.
- Tests: tests/feedbackPage.test.ts, e2e/feedback.spec.ts.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-11-03 13:03:05 +02:00

2352 lines
90 KiB
Plaintext

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
}
enum UserRole {
admin
supervisor
staff
user
}
enum NotificationPreference {
email
whatsapp
both
}
enum RegistrationStatus {
pending
confirmed
partial_paid
paid
cancelled
}
enum CashupStatus {
open
closed
}
enum CashupAction {
closed
quick_closed
reopened
}
enum EventCostType {
once_off
per_item
}
enum SecurityEventType {
login
password_changed
password_reset
two_factor_enabled
two_factor_disabled
}
enum TwoFactorMethod {
email
whatsapp
}
model User {
id String @id @default(uuid())
name String
email String @unique
password String
createdAt DateTime @default(now())
isActive Boolean @default(true)
phoneNumber String? @unique
updatedAt DateTime @updatedAt
role UserRole @default(user)
failedAttempts Int @default(0)
lockUntil DateTime?
tokenVersion Int @default(0)
notificationPreference NotificationPreference @default(email)
twoFactorEnabled Boolean @default(false)
twoFactorMethod TwoFactorMethod?
// POPIA: opted in to "News & announcements" (marketing) bulk messages, per channel.
// Event updates to registrants don't depend on these. History in CommunicationConsent.
newsEmail Boolean @default(false)
newsWhatsApp Boolean @default(false)
// When the person last answered the news opt-in question. Null = ask at next login.
newsAskedAt DateTime?
// The privacy notice version they last accepted (0 = the built-in notice). Null = never
// recorded. Asked again when a version that needs re-acceptance is newer.
noticeVersionAccepted Int?
// Retention: when they last logged in (kept here because login history itself expires),
// and when they were warned that their inactive account is about to be deleted.
lastLoginAt DateTime?
inactivityWarnedAt DateTime?
registrations Registration[]
payments Payment[]
paymentsRecorded Payment[] @relation("PaymentRecordedBy")
tickets Ticket[]
ticketScans TicketUsage[]
passwordResets PasswordReset[]
Event Event[]
FormDraft FormDraft[]
eventsClosed Event[] @relation("EventClosedBy")
eventsReopened Event[] @relation("EventReopenedBy")
cashupsPerformed EventCashup[]
pendingResolved EventCashupPendingResolution[] @relation("PendingResolutionBy")
notifyForEvents Event[] @relation("EventNotifyRecipients")
personCashCountsFor EventCashupPersonCount[] @relation("EventCashupPersonCountFor")
personCashCountsEntered EventCashupPersonCount[] @relation("EventCashupPersonCountEnteredBy")
securityEvents SecurityEvent[]
adminAuditLogs AdminAuditLog[] @relation("AdminAuditActor")
couponsCreated Coupon[] @relation("CouponCreatedBy")
couponAccess CouponUser[]
couponRedemptions CouponRedemption[] @relation("CouponRedemptionUser")
couponsApplied CouponRedemption[] @relation("CouponRedemptionAppliedBy")
attendeeProfiles Attendee[]
heldTickets Ticket[] @relation("TicketHolder")
waitlistEntries WaitlistEntry[]
scanLocks ScanLock[] @relation("ScanLockOwner")
transfersTo TicketTransfer[] @relation("TransferRecipient")
transfersBy TicketTransfer[] @relation("TransferActor")
ledGroups AllocationGroup[] @relation("AllocationGroupLeader")
feedbackInvites FeedbackInvite[]
feedbackGiven FeedbackResponse[]
impersonationsDone ImpersonationSession[] @relation("ImpersonationAdmin")
transferRequests TicketTransferRequest[] @relation("TransferRequester")
impersonatedIn ImpersonationSession[] @relation("ImpersonationTarget")
scanLocksForced ScanLock[] @relation("ScanLockForcedBy")
syncReportsDone OfflineSyncReport[] @relation("SyncReportReviewer")
twoFactorChallenges TwoFactorChallenge[]
trustedDevices TrustedDevice[]
certificates Certificate[]
certificatesIssued Certificate[] @relation("CertificateIssuedBy")
communicationConsents CommunicationConsent[]
dataRequests DataRequest[]
dataRequestsHandled DataRequest[] @relation("DataRequestHandledBy")
noticesPublished PrivacyNotice[]
noticeAcceptances NoticeAcceptance[]
dataAccessLogs DataAccessLog[]
breachesRecorded SecurityBreach[]
// Multi-campus (only used while Site settings → Campuses is on).
homeCampusId String?
homeCampus Campus? @relation("UserHomeCampus", fields: [homeCampusId], references: [id], onDelete: SetNull)
campusSummaries Campus[] @relation("CampusNotifyRecipients")
// Campus-scoped staff (1.44.0): a supervisor/staff member limited to these campuses (their
// events plus events for all campuses). Empty = unscoped. Ignored for admins and while
// campuses are off (utils/campusScope.js).
scopedCampuses Campus[] @relation("UserCampusScope")
// Kids check-in (1.45.0): the family this person is a parent in (at most one).
familyMembership FamilyMember?
kidsCheckInsDone KidsCheckIn[] @relation("KidsCheckInBy")
kidsCheckOutsDone KidsCheckIn[] @relation("KidsCheckOutBy")
kidsAlertsRaised KidsAlert[]
kidsRoomDuties KidsRoomDuty[]
// Volunteer rosters (1.46.0)
volunteerSlots VolunteerSlot[] @relation("VolunteerSlotUser")
volunteersAssigned VolunteerSlot[] @relation("VolunteerSlotAssignedBy")
volunteersCheckedIn VolunteerSlot[] @relation("VolunteerSlotCheckedInBy")
seriesFollows SeriesFollow[]
seasonPassPurchases SeasonPassPurchase[]
savedFilters SavedFilter[]
aiCalls AiCall[]
swapsAsked VolunteerSwap[] @relation("VolunteerSwapFrom")
swapsTaken VolunteerSwap[] @relation("VolunteerSwapTo")
@@index([homeCampusId])
}
// A campus of the church (Site settings → Campuses, off by default). Events belong to one
// campus, or to all campuses when Event.campusId is null. Archived instead of deleted once
// it has events.
model Campus {
id String @id @default(uuid())
name String
slug String @unique
address String?
contactName String?
contactEmail String?
contactPhone String?
// Accent colour (#rrggbb) and logo shown with the campus on the public site.
colour String?
logoUrl String?
// Pre-fills Event.location when an event is created for this campus.
defaultLocation String?
order Int @default(0)
archivedAt DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
events Event[]
homeOf User[] @relation("UserHomeCampus")
// Get the daily summary for this campus's events (on top of each event's own recipients).
notifyRecipients User[] @relation("CampusNotifyRecipients")
scopedStaff User[] @relation("UserCampusScope")
@@index([order])
}
model Event {
id String @id @default(uuid())
title String
description String?
startDate DateTime
endDate DateTime
registrationDeadline DateTime?
goLiveAt DateTime @default(now())
price Float
picture String?
isActive Boolean @default(true)
isHidden Boolean @default(false)
requiresAuth Boolean @default(true)
requiresRegistration Boolean @default(true)
// Lets a payer name each person on a group registration (one named ticket each).
allowNamedAttendees Boolean @default(false)
// Youth event: every attendee gives a date of birth, and a parent/guardian must give
// consent for anyone under 18 before they can be checked in.
requiresParentalConsent Boolean @default(false)
consentText String?
consentTextVersion Int @default(1)
consentCollectMedical Boolean @default(false)
consentCollectEmergency Boolean @default(false)
consentCollectPhoto Boolean @default(false)
// Waitlist for sold-out options: when places free up, the next person is offered
// them for waitlistOfferHours. waitlistPartialOffers: offer what's free even if it's
// fewer places than asked for (otherwise skip to the next person who fits).
waitlistEnabled Boolean @default(false)
allowTicketTransfer Boolean @default(true)
collectGender Boolean @default(false)
// Sponsor a spot: public donations into a bursary pool for this event.
allowSponsorships Boolean @default(false)
sponsorshipGoalSpots Int?
showSponsorshipProgress Boolean @default(true)
feedbackForm FeedbackForm?
analyticsEvents AnalyticsEvent[]
analyticsDaily AnalyticsDaily[]
allocationSets AllocationSet[]
waitlistOfferHours Int @default(24)
waitlistPartialOffers Boolean @default(true)
contactName String?
contactPhone String?
contactEmail String?
location String?
// Multi-campus: null = all campuses (also every event while campuses are off).
campusId String?
campus Campus? @relation(fields: [campusId], references: [id], onDelete: Restrict)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
createdById String?
createdBy User? @relation(fields: [createdById], references: [id], onDelete: SetNull)
redirectUrl String? @unique
eventOptions EventOption[]
registrations Registration[]
payments Payment[]
tickets Ticket[]
attachments EventAttachment[]
form EventForm?
Section Section[]
cashupStatus CashupStatus @default(open)
cashupDraft Json?
closedAt DateTime?
closedById String?
closedBy User? @relation("EventClosedBy", fields: [closedById], references: [id], onDelete: SetNull)
reopenedAt DateTime?
reopenedById String?
reopenedBy User? @relation("EventReopenedBy", fields: [reopenedById], references: [id], onDelete: SetNull)
costs EventCost[]
cashups EventCashup[]
personCashCounts EventCashupPersonCount[]
// Users who should receive registration/payment/daily-summary notifications for this
// event. Falls back to `createdBy` when empty (see backend/src/utils/notifications.js).
notifyRecipients User[] @relation("EventNotifyRecipients")
coupons CouponEvent[]
consentRequests ConsentRequest[]
waitlistEntries WaitlistEntry[]
scanLocks ScanLock[]
bundles EventBundle[]
// Multi-day events: `sessions` is one event running on several dates (e.g. every
// Wednesday for 5 weeks). startDate/endDate are kept in sync with the first session's
// start and the last session's end (see utils/sessions.js), so code that only reads
// them keeps working.
scheduleType ScheduleType @default(single)
attendanceMode AttendanceMode @default(whole)
// Late joining: registration stays open until session `lateJoinUntilSessionId` starts
// (otherwise it closes when the first session starts). Late pricing applies to main
// tickets only.
lateJoinEnabled Boolean @default(false)
lateJoinUntilSessionId String?
latePricing LatePricing @default(full)
lateFixedPrice Float?
checkInWindow CheckInWindow @default(whole_day)
// null = off; { offsets: [minutes before start], channels: ["email", "whatsapp"] }
sessionReminders Json?
certificatesEnabled Boolean @default(false)
// { template, title, body, signatureName, signatureRole, signatureImage, rule: { type, value } }
certificateConfig Json?
sessions EventSession[]
certificates Certificate[]
// Kids check-in (1.45.0): children are checked in at the kids desk instead of buying
// tickets. Each child gets a free place on `kidsOptionId` (a main-ticket option of this
// event) the first time, so they show on the attendance register; rooms are the groups of
// the allocation set `kidsRoomSetId`. Plain ids (checked in utils/kids.js), not relations.
kidsCheckIn Boolean @default(false)
kidsOptionId String?
kidsRoomSetId String?
kidsPickupCodes KidsPickupCode[]
kidsCheckIns KidsCheckIn[]
kidsAlerts KidsAlert[]
// Volunteer rosters (1.46.0): serving roles for the event or one of its sessions. When
// `volunteerOptionId` is set (a main ticket of this event; off by default), each volunteer
// gets a free place on it. `volunteerCheckInToken` is in the check-in QR code volunteers scan
// on the day (made the first time the QR code is shown). Plain id, checked in utils/volunteers.js.
servingRoles ServingRole[]
volunteerOptionId String?
volunteerCheckInToken String? @unique
// Event series (1.47.1): separate events grouped on one series page (Camp 2025, Camp 2026).
// seriesAnnouncedAt: when followers were emailed that this event is out (once, when it goes live).
seriesId String?
series EventSeries? @relation(fields: [seriesId], references: [id], onDelete: SetNull)
seasonPassItems SeasonPassItem[]
seriesAnnouncedAt DateTime?
@@index([createdById])
@@index([closedById])
@@index([reopenedById])
@@index([campusId])
@@index([seriesId])
}
/// Separate events grouped together, e.g. every year's camp or the monthly men's breakfast.
/// Not tied to a campus: a series can have events at several campuses.
model EventSeries {
id String @id @default(uuid())
name String
slug String @unique
description String?
picture String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
events Event[]
followers SeriesFollow[]
passes SeasonPass[]
}
/// Someone who wants to hear when a new event in the series is out (sent as news).
model SeriesFollow {
id String @id @default(uuid())
seriesId String
userId String
createdAt DateTime @default(now())
series EventSeries @relation(fields: [seriesId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@unique([seriesId, userId])
@@index([userId])
}
/// A season pass (1.47.3): one price for a ticket at each of several events in a series. Buying
/// it registers the buyer for every event on it in one checkout; the price is split across the
/// events in proportion to their normal ticket prices (for each event's cashup).
model SeasonPass {
id String @id @default(uuid())
seriesId String
name String
description String?
price Float
isActive Boolean @default(true)
// Stops selling then (it always stops when the first event on it starts).
saleEndsAt DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
series EventSeries @relation(fields: [seriesId], references: [id], onDelete: Cascade)
items SeasonPassItem[]
purchases SeasonPassPurchase[]
@@index([seriesId])
}
/// One event on a pass: which ticket (and variant) the pass gives there.
model SeasonPassItem {
id String @id @default(uuid())
passId String
eventId String
eventOptionId String
variantId String?
order Int @default(0)
pass SeasonPass @relation(fields: [passId], references: [id], onDelete: Cascade)
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
eventOption EventOption @relation(fields: [eventOptionId], references: [id], onDelete: Cascade)
variant OptionVariant? @relation(fields: [variantId], references: [id], onDelete: Cascade)
@@unique([passId, eventId])
@@index([eventId])
@@index([eventOptionId])
@@index([variantId])
}
/// Someone's purchase of a pass: `quantity` passes, one registration per event.
model SeasonPassPurchase {
id String @id @default(uuid())
passId String
userId String
quantity Int
total Float
// pending | paid | cancelled
status String @default("pending")
// The online checkout started for it (our ProviderCheckout id).
checkoutId String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
pass SeasonPass @relation(fields: [passId], references: [id], onDelete: Restrict)
user User @relation(fields: [userId], references: [id], onDelete: Restrict)
registrations Registration[]
payments Payment[]
@@index([passId])
@@index([userId])
@@index([checkoutId])
}
model EventOption {
id String @id @default(uuid())
eventId String
name String
price Float
isMainTicket Boolean
stockLimit Int @default(0)
// Applied automatically for Staff and above instead of `price` (when lower). Null = no staff price.
staffPrice Float?
// Multi-day events in "pick sessions" mode: `pick` options are bought per session
// (price is per session, stockLimit is places per session); `all` options cover every session.
sessionMode OptionSessionMode @default(all)
event Event @relation(fields: [eventId], references: [id], onDelete: Restrict)
registrationOptions RegistrationOption[]
earlyBirdTiers EarlyBirdTier[]
SectionOption SectionOption[]
variants OptionVariant[]
costs EventCost[]
couponScopes CouponOption[]
waitlistEntries WaitlistEntry[]
bundleItems EventBundleItem[]
seasonPassItems SeasonPassItem[]
@@index([eventId])
}
model EarlyBirdTier {
id String @id @default(uuid())
eventOptionId String
variantId String?
deadline DateTime
price Float
order Int @default(0)
stockLimit Int @default(0)
eventOption EventOption @relation(fields: [eventOptionId], references: [id], onDelete: Cascade)
variant OptionVariant? @relation(fields: [variantId], references: [id], onDelete: Cascade)
registrationOptions RegistrationOption[]
tranches RegistrationOptionTranche[]
@@index([eventOptionId, deadline])
@@index([variantId])
}
model OptionVariant {
id String @id @default(uuid())
eventOptionId String
name String
price Float?
stockLimit Int @default(0)
// Staff price override for this variant (falls back to the option's staffPrice).
staffPrice Float?
order Int @default(0)
eventOption EventOption @relation(fields: [eventOptionId], references: [id], onDelete: Cascade)
registrationOptions RegistrationOption[]
earlyBirdTiers EarlyBirdTier[]
couponScopes CouponOption[]
waitlistEntries WaitlistEntry[]
bundleItems EventBundleItem[]
seasonPassItems SeasonPassItem[]
@@index([eventOptionId])
}
model Registration {
id String @id @default(uuid())
userId String
eventId String
status RegistrationStatus @default(pending)
checkoutId String? @unique
// Sum of this registration's discounts: bundle deals plus active coupon discounts,
// kept in step with bundleApplications and couponRedemptions. Stored here so every
// total-due calculation sees it no matter which relations the caller loaded (see
// utils/pricing.js computeRegistrationTotalDue).
discountTotal Float @default(0)
// The bundle-deal part of discountTotal (the rest is coupons).
bundleDiscount Float @default(0)
// Where named attendees' tickets go: all to the payer, or each to the attendee.
ticketDelivery TicketDelivery @default(payer)
// Corrections (1.52.0). The attendee form can come later: tickets don't wait for it ("Fill in
// later" at the door or in Manual registrations, or an admin in Corrections).
formWaivedAt DateTime?
formWaivedById String?
// An admin issued the tickets before it was paid ("pay later"): the balance is still owed.
ticketsReleasedAt DateTime?
ticketsReleasedById String?
// A balance an admin wrote off ("make it free"); part of discountTotal (utils/coupons.js).
complimentaryDiscount Float @default(0)
analyticsSessionId String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
user User @relation(fields: [userId], references: [id], onDelete: Restrict)
event Event @relation(fields: [eventId], references: [id], onDelete: Restrict)
registrationOptions RegistrationOption[]
payments Payment[]
formResponses FormResponse[]
formDrafts FormDraft[]
couponRedemptions CouponRedemption[]
bundleApplications RegistrationBundle[]
// Season pass (1.47.3): one registration per event on the pass, its line priced at that
// event's share of the pass price (never repriced, no coupons, options not editable).
seasonPassPurchaseId String?
seasonPassPurchase SeasonPassPurchase? @relation(fields: [seasonPassPurchaseId], references: [id], onDelete: SetNull)
attendees Attendee[]
waitlistEntries WaitlistEntry[]
volunteerSlots VolunteerSlot[]
@@index([userId])
@@index([eventId])
@@index([userId, status])
@@index([eventId, status])
@@index([createdAt, status])
}
model RegistrationOption {
id String @id @default(uuid())
registrationId String
eventOptionId String
quantity Int @default(1)
variantId String?
appliedTierId String?
priceSnapshot Float?
registration Registration @relation(fields: [registrationId], references: [id], onDelete: Restrict)
eventOption EventOption @relation(fields: [eventOptionId], references: [id], onDelete: Restrict)
variant OptionVariant? @relation(fields: [variantId], references: [id], onDelete: SetNull)
appliedTier EarlyBirdTier? @relation(fields: [appliedTierId], references: [id], onDelete: SetNull)
tickets Ticket[]
tranches RegistrationOptionTranche[]
attendees Attendee[]
// A "pick sessions" option: one line (and ticket) per session booked. Null for
// everything else.
sessionId String?
session EventSession? @relation(fields: [sessionId], references: [id], onDelete: Restrict)
// An all-sessions option on a "pick sessions" event: the sessions chosen (one line,
// one ticket, one price). Empty = every session.
sessionIds String[] @default([])
@@index([registrationId])
@@index([eventOptionId])
@@index([variantId])
@@index([appliedTierId])
@@index([sessionId])
}
// One row per purchase-at-a-price for a RegistrationOption. Never mutated after creation
// (mirrors the Payment model's append-only pattern) — this is what lets a single ticket
// type be bought in multiple batches at different early-bird prices without either batch's
// price bleeding into the other. RegistrationOption.quantity/priceSnapshot/appliedTierId
// stay in sync as an aggregate (quantity = sum of tranche quantities; priceSnapshot/appliedTierId
// mirror the most recently added tranche) for the many call sites that only need "how many"
// or a single display price.
model RegistrationOptionTranche {
id String @id @default(uuid())
registrationOptionId String
quantity Int
priceSnapshot Float
appliedTierId String?
createdAt DateTime @default(now())
registrationOption RegistrationOption @relation(fields: [registrationOptionId], references: [id], onDelete: Cascade)
appliedTier EarlyBirdTier? @relation(fields: [appliedTierId], references: [id], onDelete: SetNull)
@@index([registrationOptionId])
@@index([appliedTierId])
}
model Payment {
id String @id @default(uuid())
amount Float
method String
userId String
recordedById String?
registrationId String?
eventId String?
isDonation Boolean @default(false)
// A "sponsor a spot" donation (also isDonation): goes into the event's sponsorship pool.
isSponsorship Boolean @default(false)
sponsorName String?
sponsorMessage String?
sponsorAnonymous Boolean @default(false)
externalId String? @unique
status String?
originalPaymentId String?
// Online payments: the payment provider that took it (src/payments, e.g. "yoco"). Null for
// payments recorded by staff. Payment.method stays the wallet type (card, apple_pay, …).
provider String?
createdAt DateTime @default(now())
user User @relation(fields: [userId], references: [id], onDelete: Restrict)
recordedBy User? @relation("PaymentRecordedBy", fields: [recordedById], references: [id], onDelete: SetNull)
registration Registration? @relation(fields: [registrationId], references: [id], onDelete: SetNull)
event Event? @relation(fields: [eventId], references: [id], onDelete: SetNull)
originalPayment Payment? @relation("SplitPayments", fields: [originalPaymentId], references: [id], onDelete: SetNull)
splitPayments Payment[] @relation("SplitPayments")
providerTransactions ProviderTransaction[]
// Season pass: one provider payment is recorded as one Payment per event on the pass (its
// share), all linked to the purchase. Only the first carries the provider's externalId.
seasonPassPurchaseId String?
seasonPassPurchase SeasonPassPurchase? @relation(fields: [seasonPassPurchaseId], references: [id], onDelete: SetNull)
@@index([userId])
@@index([recordedById])
@@index([registrationId])
@@index([eventId])
@@index([createdAt])
@@index([originalPaymentId, isDonation])
}
model Ticket {
id String @id @default(uuid())
qrCode String @unique
registrationOptionId String
userId String
eventId String
quantity Int @default(1)
isUsed Boolean @default(false)
emailSent Boolean @default(false)
// Set on a named (group) ticket: the person it's for, and their account when it
// matched one. userId stays the payer.
attendeeId String? @unique
attendee Attendee? @relation(fields: [attendeeId], references: [id], onDelete: SetNull)
holderUserId String?
holder User? @relation("TicketHolder", fields: [holderUserId], references: [id], onDelete: SetNull)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
registrationOption RegistrationOption @relation(fields: [registrationOptionId], references: [id], onDelete: Restrict)
user User @relation(fields: [userId], references: [id], onDelete: Restrict)
event Event @relation(fields: [eventId], references: [id], onDelete: Restrict)
usages TicketUsage[]
transfers TicketTransfer[]
transferRequests TicketTransferRequest[]
certificates Certificate[]
@@index([holderUserId])
@@index([eventId])
@@index([userId])
@@index([registrationOptionId])
@@index([createdAt])
}
model TicketUsage {
id String @id @default(uuid())
ticketId String
scannedById String
scannedAt DateTime @default(now())
quantityRedeemed Int @default(1)
// Offline scanning: the device's id for the scan (makes syncing idempotent), and
// the offline lock it was made under.
clientScanId String? @unique
offline Boolean @default(false)
scanLockId String?
syncedAt DateTime?
// Multi-day events: the session this check-in counts for (null on single-date events).
sessionId String?
session EventSession? @relation(fields: [sessionId], references: [id], onDelete: Restrict)
// Ticked present by a supervisor from the attendance register, not scanned.
manual Boolean @default(false)
ticket Ticket @relation(fields: [ticketId], references: [id], onDelete: Restrict)
scannedBy User @relation(fields: [scannedById], references: [id], onDelete: Restrict)
scanLock ScanLock? @relation(fields: [scanLockId], references: [id], onDelete: SetNull)
@@index([ticketId])
@@index([scannedAt])
@@index([scannedById, scannedAt])
@@index([sessionId])
}
model PasswordReset {
id String @id @default(uuid())
userId String
token String @unique
expiresAt DateTime
used Boolean @default(false)
createdAt DateTime @default(now())
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([userId])
}
// A 6-digit code sent by email/WhatsApp, either as the second step of a login
// ('login') or to prove the channel works before 2FA is switched on ('enable').
// Only the HMAC of the code is stored (see backend/src/utils/twoFactor.js).
model TwoFactorChallenge {
id String @id @default(uuid())
userId String
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
purpose String // 'login' | 'enable'
method TwoFactorMethod
codeHash String
attempts Int @default(0)
lastSentAt DateTime @default(now())
expiresAt DateTime
usedAt DateTime?
createdAt DateTime @default(now())
@@index([userId])
}
// A browser that passed the 2FA code step with "Trust this device" ticked — logins
// presenting its token skip the code until it expires or is cleared.
model TrustedDevice {
id String @id @default(uuid())
userId String
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
tokenHash String @unique
device String?
createdAt DateTime @default(now())
lastUsedAt DateTime @default(now())
expiresAt DateTime
@@index([userId])
}
// Append-only "Account activity" log for the Profile & Security page (logins, password
// changes). Nullable FK with SetNull (not Cascade) so entries survive account
// close/anonymization, the same pattern EventCashup uses for its performedBy audit trail.
model SecurityEvent {
id String @id @default(uuid())
userId String?
user User? @relation(fields: [userId], references: [id], onDelete: SetNull)
type SecurityEventType
ip String?
device String?
createdAt DateTime @default(now())
@@index([userId, createdAt])
}
enum AdminAuditAction {
refund_created
donation_assigned
donation_unassigned
registration_created_manual
registration_cancelled
event_created
event_updated
event_deleted
settings_updated
coupon_created
coupon_updated
coupon_disabled
coupon_applied_manual
bundle_created
bundle_updated
bundle_deleted
consent_override
consent_rerequested
medical_list_viewed
scan_lock_acquired
scan_lock_released
scan_lock_force_released
offline_sync
ticket_transferred
impersonation_started
impersonation_ended
yoco_config_updated
yoco_webhook_registered
yoco_webhook_deleted
payment_providers_updated
payfast_config_updated
event_sessions_changed
attendance_marked
attendance_unmarked
attendee_form_waived
tickets_issued_override
registration_complimentary
checkin_corrected
certificate_issued
certificate_revoked
job_run_manual
data_request_completed
data_request_rejected
user_anonymised
privacy_notice_published
operator_created
operator_updated
operator_deleted
retention_policies_updated
retention_run
breach_recorded
breach_updated
hub_connected
hub_update_requested
hub_update_request_withdrawn
hub_update_snoozed
hub_update_unsnoozed
campuses_toggled
campus_created
campus_updated
campus_archived
campus_deleted
event_campus_changed
user_campus_scope_changed
kids_settings_updated
kids_checkout_override
kids_wrong_code
kids_custody_notes_changed
kids_offline_sync
volunteer_settings_updated
volunteer_roles_changed
volunteer_assigned
volunteer_removed
volunteer_checked_in_manual
series_created
series_updated
series_deleted
season_pass_changed
webhook_endpoint_changed
registrations_bulk
ai_settings_changed
}
// Append-only audit trail for admin/supervisor-initiated actions with money or
// data-integrity impact — separate from SecurityEvent (user-account-security-specific,
// fixed enum). Nullable FK with SetNull mirrors SecurityEvent's pattern so entries
// survive account close/anonymization.
model AdminAuditLog {
id String @id @default(uuid())
actorId String?
actor User? @relation("AdminAuditActor", fields: [actorId], references: [id], onDelete: SetNull)
actorRole String
action AdminAuditAction
targetType String
targetId String?
metadata Json?
ip String?
createdAt DateTime @default(now())
@@index([actorId, createdAt])
@@index([action, createdAt])
@@index([targetType, targetId])
}
model EventAttachment {
id String @id @default(uuid())
eventId String
originalName String
filename String
mimeType String
size Int
url String
createdAt DateTime @default(now())
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
@@index([eventId])
}
// Event registration forms
enum FormFieldType {
yes_no
text
date
numeric
statement
paragraph
rating
multiple_choice
}
model EventForm {
id String @id @default(uuid())
eventId String @unique
isRequired Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
fields EventFormField[]
}
model EventFormField {
id String @id @default(uuid())
formId String
type FormFieldType
label String
isRequired Boolean @default(false)
order Int @default(0)
helpText String?
options Json?
form EventForm @relation(fields: [formId], references: [id], onDelete: Cascade)
answers FormAnswer[]
@@index([formId])
}
model FormResponse {
id String @id @default(uuid())
registrationId String
createdAt DateTime @default(now())
registration Registration @relation(fields: [registrationId], references: [id], onDelete: Cascade)
attendeeId String?
attendee Attendee? @relation(fields: [attendeeId], references: [id], onDelete: SetNull)
answers FormAnswer[]
@@index([registrationId])
}
model FormAnswer {
id String @id @default(uuid())
responseId String
fieldId String
value String
response FormResponse @relation(fields: [responseId], references: [id], onDelete: Cascade)
field EventFormField @relation(fields: [fieldId], references: [id], onDelete: Cascade)
@@index([responseId])
@@index([fieldId])
}
// Draft storage for in-progress attendee forms
model FormDraft {
id String @id @default(uuid())
registrationId String
userId String
data Json
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
registration Registration @relation(fields: [registrationId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@unique([registrationId, userId])
}
// Stores all webhook events from Yoco. If a Payment is created (registration or donation),
// the record is marked reconciled and linked via paymentId. Otherwise, remains unreconciled.
// A transaction a payment provider told us about (by webhook). Matched to a Payment when it
// belongs to one of our checkouts; the rest wait on the Payments page to be reconciled.
model ProviderTransaction {
id String @id @default(uuid())
provider String @default("yoco") // payment provider id (src/payments)
externalId String // the provider's payment/event id
amount Int?
currency String?
createdDate DateTime?
checkoutId String?
methodType String? // paymentMethodDetails.type (e.g., card)
reconciled Boolean @default(false)
ignored Boolean @default(false)
paymentId String?
payment Payment? @relation(fields: [paymentId], references: [id], onDelete: SetNull)
raw Json?
// Why it wasn't recorded automatically and needs a look (e.g. paid a different amount
// from the checkout).
note String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@unique([provider, externalId])
@@index([paymentId])
}
// An online checkout we started: what it's for and how much, so the payment that comes back
// can be checked against it. Its id is the payer's link (/pay/<id>) and, for PayFast, the
// m_payment_id. `provider` is null until the payer picks one (when several are enabled).
model ProviderCheckout {
id String @id @default(uuid())
provider String?
// The provider's own checkout id (Yoco) and where it sends the payer.
externalId String?
redirectUrl String?
amountCents Int
currency String @default("ZAR")
// Who/what the payment is for: registrationId, userId, eventId, type, donationAmount, …
metadata Json
// Everything needed to start the checkout with any provider: lineItems, return URLs, payer.
request Json
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([provider, externalId])
}
model Section {
id String @id @default(uuid())
eventId String
name String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
allowedOptions SectionOption[]
scanLocks ScanLock[]
@@index([eventId])
}
model SectionOption {
id String @id @default(uuid())
sectionId String
eventOptionId String
section Section @relation(fields: [sectionId], references: [id], onDelete: Cascade)
eventOption EventOption @relation(fields: [eventOptionId], references: [id], onDelete: Cascade)
@@unique([sectionId, eventOptionId])
}
model AppSetting {
key String @id
value String
updatedAt DateTime @updatedAt
}
model EventCost {
id String @id @default(uuid())
eventId String
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
eventOptionId String?
eventOption EventOption? @relation(fields: [eventOptionId], references: [id], onDelete: Cascade)
label String
costType EventCostType
amount Float
paidFromMethod String? // 'cash' | 'card' | 'eft' | 'other' | null — null = not paid from event takings
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([eventId])
@@index([eventOptionId])
}
// Append-only audit trail: one row per close / quick-close / reopen. Never updated or deleted.
model EventCashup {
id String @id @default(uuid())
eventId String
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
action CashupAction
unallocatedDonationsTotal Float @default(0)
totalCosts Float @default(0)
totalExpectedRevenue Float @default(0)
totalActualRevenue Float?
notes String?
performedById String?
performedBy User? @relation(fields: [performedById], references: [id], onDelete: SetNull)
createdAt DateTime @default(now())
lines EventCashupLine[]
@@index([eventId, createdAt])
}
// Where one method's expected takings ended up. actualAmount = bank + on hand + pending
// (money held or on its way); write-offs explain money that's gone (fees, charges,
// shortfalls). expected - actual - writeOffTotal is what's still unaccounted for.
model EventCashupLine {
id String @id @default(uuid())
cashupId String
cashup EventCashup @relation(fields: [cashupId], references: [id], onDelete: Cascade)
method String
expectedAmount Float @default(0)
actualAmount Float?
variance Float?
notes String?
bankAmount Float?
onHandAmount Float?
pendingAmount Float?
// Card/Yoco report or POP reference for the pending amount.
pendingReference String?
writeOffTotal Float @default(0)
denominations EventCashupDenomination[]
writeOffs EventCashupWriteOff[]
pendingResolutions EventCashupPendingResolution[]
@@index([cashupId])
}
enum CashupWriteOffCategory {
card_fees
bank_charges
cash_shortfall
other
}
model EventCashupWriteOff {
id String @id @default(uuid())
lineId String
line EventCashupLine @relation(fields: [lineId], references: [id], onDelete: Cascade)
category CashupWriteOffCategory
amount Float
reason String?
createdAt DateTime @default(now())
@@index([lineId])
}
// Pending money settled after the event was closed: it reached the bank ('received') or
// never will ('written_off'). Append-only, like EventCashup — the line itself is never edited.
model EventCashupPendingResolution {
id String @id @default(uuid())
lineId String
line EventCashupLine @relation(fields: [lineId], references: [id], onDelete: Cascade)
outcome String // 'received' | 'written_off'
amount Float
category CashupWriteOffCategory?
notes String?
performedById String?
performedBy User? @relation("PendingResolutionBy", fields: [performedById], references: [id], onDelete: SetNull)
createdAt DateTime @default(now())
@@index([lineId])
@@index([performedById])
}
model EventCashupDenomination {
id String @id @default(uuid())
lineId String
line EventCashupLine @relation(fields: [lineId], references: [id], onDelete: Cascade)
value Float
count Int
@@index([lineId])
}
// Actual physical cash counted for one staff member's recorded cash payments, entered any
// time (not part of the close flow) purely for accountability — compared against the
// system-expected amount (computeCashAccountabilityByUser) to show a per-person variance.
model EventCashupPersonCount {
id String @id @default(uuid())
eventId String
userId String
enteredById String?
notes String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
user User @relation("EventCashupPersonCountFor", fields: [userId], references: [id], onDelete: Cascade)
enteredBy User? @relation("EventCashupPersonCountEnteredBy", fields: [enteredById], references: [id], onDelete: SetNull)
denominations EventCashupPersonCountDenomination[]
@@unique([eventId, userId])
@@index([eventId])
}
model EventCashupPersonCountDenomination {
id String @id @default(uuid())
countId String
value Float
count Int
personCount EventCashupPersonCount @relation(fields: [countId], references: [id], onDelete: Cascade)
@@index([countId])
}
enum CouponType {
percent
fixed
comp
}
model Coupon {
id String @id @default(uuid())
code String @unique // stored uppercase; matched case-insensitively
description String?
type CouponType
value Float @default(0) // percent (0-100) or rand amount; ignored for comp
stackEarlyBird Boolean @default(false) // true: discount the early-bird price; false: best price wins
validFrom DateTime?
validUntil DateTime?
maxUses Int?
maxUsesPerUser Int? @default(1)
minQuantity Int?
restrictRoles UserRole[]
restrictDomain String?
active Boolean @default(true)
createdById String?
createdBy User? @relation("CouponCreatedBy", fields: [createdById], references: [id], onDelete: SetNull)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
events CouponEvent[] // none = valid for every event
options CouponOption[] // none = whole event
allowedUsers CouponUser[]
redemptions CouponRedemption[]
@@index([createdById])
}
model CouponEvent {
id String @id @default(uuid())
couponId String
eventId String
coupon Coupon @relation(fields: [couponId], references: [id], onDelete: Cascade)
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
@@unique([couponId, eventId])
@@index([eventId])
}
// Limits a coupon to specific ticket options, or to one variant of an option.
model CouponOption {
id String @id @default(uuid())
couponId String
eventOptionId String
variantId String?
coupon Coupon @relation(fields: [couponId], references: [id], onDelete: Cascade)
eventOption EventOption @relation(fields: [eventOptionId], references: [id], onDelete: Cascade)
variant OptionVariant? @relation(fields: [variantId], references: [id], onDelete: Cascade)
@@index([couponId])
@@index([eventOptionId])
@@index([variantId])
}
model CouponUser {
id String @id @default(uuid())
couponId String
userId String
coupon Coupon @relation(fields: [couponId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@unique([couponId, userId])
@@index([userId])
}
model CouponRedemption {
id String @id @default(uuid())
couponId String
registrationId String
userId String
discountAmount Float // locked at apply time
appliedById String? // staff member, when applied on someone's behalf
createdAt DateTime @default(now())
releasedAt DateTime? // set when the registration is cancelled — frees the use
coupon Coupon @relation(fields: [couponId], references: [id], onDelete: Restrict)
registration Registration @relation(fields: [registrationId], references: [id], onDelete: Cascade)
user User @relation("CouponRedemptionUser", fields: [userId], references: [id], onDelete: Restrict)
appliedBy User? @relation("CouponRedemptionAppliedBy", fields: [appliedById], references: [id], onDelete: SetNull)
@@index([couponId, releasedAt])
@@index([registrationId])
@@index([userId])
@@index([appliedById])
}
// ─── Bundle deals ─────────────────────────────────────────────────────────────
// How a bundle is made up (see utils/bundles.js):
// exact: these tickets in these numbers, for `price`, e.g. 5 Adult + 3 Child for R500
// mix: any `setSize` of the qualifying tickets, for `price`
// free: any `setSize` of the qualifying tickets, the cheapest `freeCount` free
// group: `setSize` or more qualifying tickets, all at `price` each (or `price`% off)
enum BundleKind {
exact
mix
free
group
}
enum BundleGroupPricing {
per_ticket
percent
}
// Tickets sold together at a better price. Applied automatically, as many times as a
// registration's items allow (a group discount once), and only when it beats what the
// items would cost anyway (see utils/bundles.js).
model EventBundle {
id String @id @default(uuid())
eventId String
name String
description String?
kind BundleKind @default(exact)
// exact/mix: the set's price. group: the per-ticket price, or the percentage off.
price Float
setSize Int?
freeCount Int?
groupPricing BundleGroupPricing?
isActive Boolean @default(true)
validFrom DateTime?
validUntil DateTime?
order Int @default(0)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
items EventBundleItem[]
uses RegistrationBundle[]
@@index([eventId])
}
// One line of a bundle: for exact bundles a ticket and how many; for the others a
// qualifying ticket (quantity unused). variantId null = any variant of the option.
model EventBundleItem {
id String @id @default(uuid())
bundleId String
eventOptionId String
variantId String?
quantity Int
bundle EventBundle @relation(fields: [bundleId], references: [id], onDelete: Cascade)
eventOption EventOption @relation(fields: [eventOptionId], references: [id], onDelete: Cascade)
variant OptionVariant? @relation(fields: [variantId], references: [id], onDelete: Cascade)
@@index([bundleId])
@@index([eventOptionId])
@@index([variantId])
}
// How many times a bundle applies to a registration and what it saved. Rebuilt each
// time the registration's discounts are recalculated.
model RegistrationBundle {
id String @id @default(uuid())
registrationId String
bundleId String
count Int
discount Float
createdAt DateTime @default(now())
registration Registration @relation(fields: [registrationId], references: [id], onDelete: Cascade)
bundle EventBundle @relation(fields: [bundleId], references: [id], onDelete: Restrict)
@@index([registrationId])
@@index([bundleId])
}
// ─── Group tickets ────────────────────────────────────────────────────────────
enum TicketDelivery {
payer // every ticket goes to the person who registered
each // each named attendee gets their own (falls back to the payer)
}
// One named person on a group registration, for one main-ticket place. Gets their
// own quantity-1 ticket and their own attendee form response.
/// An admin viewing the site as another user (strictly read-only, at most 30 minutes).
model ImpersonationSession {
id String @id @default(uuid())
adminId String
targetUserId String
reason String
startedAt DateTime @default(now())
expiresAt DateTime
endedAt DateTime?
endedReason String? // 'ended' | 'expired'
admin User @relation("ImpersonationAdmin", fields: [adminId], references: [id], onDelete: Cascade)
target User @relation("ImpersonationTarget", fields: [targetUserId], references: [id], onDelete: Cascade)
requests ImpersonationRequestLog[]
@@index([adminId])
@@index([targetUserId])
}
/// Every page viewed and API call made during an impersonation session.
model ImpersonationRequestLog {
id String @id @default(uuid())
sessionId String
method String
path String
blocked Boolean @default(false)
createdAt DateTime @default(now())
session ImpersonationSession @relation(fields: [sessionId], references: [id], onDelete: Cascade)
@@index([sessionId, createdAt])
}
/// First-party funnel tracking (only after cookie consent): one row per anonymous
/// session, event and stage ('view' | 'start' | 'submit'). "Paid" comes from the
/// registration that carries the session id. Rows older than 90 days are rolled up
/// into AnalyticsDaily and deleted.
model AnalyticsEvent {
id String @id @default(uuid())
sessionId String
eventId String
stage String
source String?
device String?
createdAt DateTime @default(now())
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
@@unique([sessionId, eventId, stage])
@@index([eventId, createdAt])
}
/// Rolled-up funnel counts: sessions that reached `stage`, per day, source and device.
model AnalyticsDaily {
id String @id @default(uuid())
eventId String
day DateTime @db.Date
stage String
source String @default("direct")
device String @default("unknown")
sessions Int @default(0)
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
@@unique([eventId, day, stage, source, device])
@@index([eventId, day])
}
/// Post-event survey. Sent at sendAt to checked-in attendees (or everyone registered),
/// with one reminder 3 days later. anonymous responses aren't linked to a person.
model FeedbackForm {
id String @id @default(uuid())
eventId String @unique
sendAt DateTime?
audience String @default("checked_in") // 'checked_in' | 'all'
anonymous Boolean @default(false)
intro String?
sentAt DateTime?
reminderSentAt DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
fields FeedbackField[]
invites FeedbackInvite[]
responses FeedbackResponse[]
}
model FeedbackField {
id String @id @default(uuid())
formId String
type FormFieldType
label String
isRequired Boolean @default(false)
order Int @default(0)
helpText String?
options Json?
form FeedbackForm @relation(fields: [formId], references: [id], onDelete: Cascade)
answers FeedbackAnswer[]
@@index([formId])
}
/// Who was asked. tokenHash is the HMAC of the link token (the token itself isn't stored).
model FeedbackInvite {
id String @id @default(uuid())
formId String
userId String
tokenHash String @unique
sentAt DateTime @default(now())
remindedAt DateTime? // the last reminder (automatic or by hand)
reminderCount Int @default(0)
respondedAt DateTime?
form FeedbackForm @relation(fields: [formId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@unique([formId, userId])
@@index([userId])
}
model FeedbackResponse {
id String @id @default(uuid())
formId String
userId String? // null when the form is anonymous
createdAt DateTime @default(now())
form FeedbackForm @relation(fields: [formId], references: [id], onDelete: Cascade)
user User? @relation(fields: [userId], references: [id], onDelete: SetNull)
answers FeedbackAnswer[]
@@index([formId])
}
model FeedbackAnswer {
id String @id @default(uuid())
responseId String
fieldId String
value String
response FeedbackResponse @relation(fields: [responseId], references: [id], onDelete: Cascade)
field FeedbackField @relation(fields: [fieldId], references: [id], onDelete: Cascade)
@@index([responseId])
@@index([fieldId])
}
/// A way of splitting an event's attendees up: "Rooms", "Small groups", "Buses".
model AllocationSet {
id String @id @default(uuid())
eventId String
name String
showOnTicket Boolean @default(false)
order Int @default(0)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
groups AllocationGroup[]
assignments AllocationAssignment[]
@@index([eventId])
}
/// One room / group / bus in a set. gender ('male' | 'female') restricts who can join.
model AllocationGroup {
id String @id @default(uuid())
setId String
name String
capacity Int?
gender String?
leaderUserId String?
order Int @default(0)
// Kids rooms (1.45.0): the ages the room is for (whole years, inclusive) and how many
// children one leader may look after. All optional.
minAge Int?
maxAge Int?
ratio Int?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
set AllocationSet @relation(fields: [setId], references: [id], onDelete: Cascade)
leader User? @relation("AllocationGroupLeader", fields: [leaderUserId], references: [id], onDelete: SetNull)
assignments AllocationAssignment[]
kidsCheckIns KidsCheckIn[]
kidsAlerts KidsAlert[]
kidsDuties KidsRoomDuty[]
servingRoles ServingRole[]
@@index([setId])
@@index([leaderUserId])
}
/// An attendee placed in a group. One group per set.
model AllocationAssignment {
id String @id @default(uuid())
setId String
groupId String
attendeeId String
createdAt DateTime @default(now())
set AllocationSet @relation(fields: [setId], references: [id], onDelete: Cascade)
group AllocationGroup @relation(fields: [groupId], references: [id], onDelete: Cascade)
attendee Attendee @relation(fields: [attendeeId], references: [id], onDelete: Cascade)
@@unique([setId, attendeeId])
@@index([groupId])
@@index([attendeeId])
}
/// A transfer waiting for the new holder to accept it from the link sent to the email /
/// phone the sender typed. Nothing changes on the ticket until it's accepted. `details`
/// holds what the sender entered (date of birth, gender, parent details on youth events).
model TicketTransferRequest {
id String @id @default(uuid())
ticketId String
requestedById String
toName String
toEmail String?
toPhone String?
details Json?
tokenHash String @unique
status String @default("pending") // pending | accepted | declined | cancelled | expired
expiresAt DateTime
respondedAt DateTime?
createdAt DateTime @default(now())
ticket Ticket @relation(fields: [ticketId], references: [id], onDelete: Cascade)
requestedBy User @relation("TransferRequester", fields: [requestedById], references: [id], onDelete: Cascade)
@@index([ticketId, status])
}
/// A ticket handed to someone else. oldQrCode is the code that stopped working (null
/// when one place was split off a shared group ticket, whose code stays valid).
model TicketTransfer {
id String @id @default(uuid())
ticketId String
fromHolderName String
fromUserId String?
toName String
toUserId String?
byUserId String?
oldQrCode String?
createdAt DateTime @default(now())
ticket Ticket @relation(fields: [ticketId], references: [id], onDelete: Cascade)
toUser User? @relation("TransferRecipient", fields: [toUserId], references: [id], onDelete: SetNull)
byUser User? @relation("TransferActor", fields: [byUserId], references: [id], onDelete: SetNull)
@@index([ticketId])
@@index([oldQrCode])
}
model Attendee {
id String @id @default(uuid())
registrationId String
registrationOptionId String?
eventOptionId String
variantId String?
name String
email String?
phone String?
userId String? // existing account matched by email/phone
dateOfBirth DateTime?
gender String?
parentName String?
parentEmail String?
parentPhone String?
order Int @default(0)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
registration Registration @relation(fields: [registrationId], references: [id], onDelete: Cascade)
registrationOption RegistrationOption? @relation(fields: [registrationOptionId], references: [id], onDelete: SetNull)
user User? @relation(fields: [userId], references: [id], onDelete: SetNull)
ticket Ticket?
formResponses FormResponse[]
consentRequests ConsentRequest[]
allocations AllocationAssignment[]
@@index([registrationId])
@@index([registrationOptionId])
@@index([userId])
}
// ─── Parental consent ─────────────────────────────────────────────────────────
enum ConsentStatus {
pending
given
declined
}
// A request for a parent/guardian to consent for one under-18 attendee. Only the hash
// of the link token is stored. A new request (e.g. after the consent text changes)
// supersedes earlier ones for the same attendee; the latest one decides check-in.
model ConsentRequest {
id String @id @default(uuid())
attendeeId String
eventId String
tokenHash String @unique
status ConsentStatus @default(pending)
textVersion Int
consentText String // the exact text the parent was shown
parentName String?
parentEmail String?
parentPhone String?
signedName String?
ip String?
device String?
medical String? // encrypted JSON (utils/encryption.js); purged after the event
emergency String? // encrypted JSON; purged after the event
photoConsent Boolean?
respondedAt DateTime?
lastSentAt DateTime @default(now())
remindersSent Int @default(0)
expiresAt DateTime
medicalPurgedAt DateTime?
createdAt DateTime @default(now())
attendee Attendee @relation(fields: [attendeeId], references: [id], onDelete: Cascade)
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
@@index([attendeeId, createdAt])
@@index([eventId, status])
}
// ─── Waitlist ─────────────────────────────────────────────────────────────────
enum WaitlistStatus {
waiting
offered // places held for them until offerExpiresAt
claimed // they registered with the offer
expired // the offer ran out
removed // taken off by staff
left // they left the waitlist
}
model WaitlistEntry {
id String @id @default(uuid())
eventId String
eventOptionId String
variantId String?
// "Pick sessions" options: the session they're waiting for. Null for everything else.
sessionId String?
userId String
quantity Int
position Int
status WaitlistStatus @default(waiting)
offeredQuantity Int?
offerTokenHash String? @unique
offeredAt DateTime?
offerExpiresAt DateTime?
claimedAt DateTime?
registrationId String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
eventOption EventOption @relation(fields: [eventOptionId], references: [id], onDelete: Cascade)
variant OptionVariant? @relation(fields: [variantId], references: [id], onDelete: Cascade)
session EventSession? @relation(fields: [sessionId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
registration Registration? @relation(fields: [registrationId], references: [id], onDelete: SetNull)
@@index([eventId, status, position])
@@index([eventOptionId, status])
@@index([variantId])
@@index([sessionId])
@@index([userId])
@@index([registrationId])
}
// ─── Offline scanning ─────────────────────────────────────────────────────────
// Locks a section (or the whole event, sectionId null) to one device for offline
// scanning, so two devices can never redeem the same ticket while offline.
// One active (releasedAt null) lock per scope — enforced in utils/scanLocks.js.
model ScanLock {
id String @id @default(uuid())
eventId String
sectionId String?
deviceId String
deviceName String?
userId String
lockedAt DateTime @default(now())
snapshotAt DateTime?
lastSyncAt DateTime?
releasedAt DateTime?
forceReleasedById String?
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
section Section? @relation(fields: [sectionId], references: [id], onDelete: Cascade)
user User @relation("ScanLockOwner", fields: [userId], references: [id], onDelete: Cascade)
forceReleasedBy User? @relation("ScanLockForcedBy", fields: [forceReleasedById], references: [id], onDelete: SetNull)
usages TicketUsage[]
syncReports OfflineSyncReport[]
@@index([eventId, releasedAt])
@@index([userId])
@@index([sectionId])
@@index([forceReleasedById])
}
// One sync of queued offline scans; `conflicts` lists scans that needed a person to look.
model OfflineSyncReport {
id String @id @default(uuid())
scanLockId String
accepted Int @default(0)
duplicates Int @default(0)
conflicts Json @default("[]")
afterForceRelease Boolean @default(false)
createdAt DateTime @default(now())
reviewedAt DateTime?
reviewedById String?
scanLock ScanLock @relation(fields: [scanLockId], references: [id], onDelete: Cascade)
reviewedBy User? @relation("SyncReportReviewer", fields: [reviewedById], references: [id], onDelete: SetNull)
@@index([scanLockId])
@@index([reviewedById])
}
enum ScheduleType {
single
sessions
}
enum AttendanceMode {
whole
pick
}
enum LatePricing {
full
prorata
fixed
}
enum OptionSessionMode {
all
pick
}
enum CheckInWindow {
whole_day
session_only
}
// One date/time of a multi-day event.
model EventSession {
id String @id @default(uuid())
eventId String
label String?
startsAt DateTime
endsAt DateTime
location String?
// Cancelled sessions are kept (they may have check-ins) but hidden from the schedule
// and left out of attendance totals.
cancelledAt DateTime?
order Int @default(0)
// Optional limit on people at this session (picked places + whole-course tickets).
capacity Int?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
usages TicketUsage[]
reminders SessionReminderLog[]
registrationOptions RegistrationOption[]
waitlistEntries WaitlistEntry[]
servingRoles ServingRole[]
@@index([eventId, startsAt])
}
// Which session reminders have gone out, so the job sends each one once.
model SessionReminderLog {
id String @id @default(uuid())
sessionId String
// Minutes before the session start this reminder is for.
offset Int
sent Int @default(0)
sentAt DateTime @default(now())
session EventSession @relation(fields: [sessionId], references: [id], onDelete: Cascade)
@@unique([sessionId, offset])
}
// A completion certificate for one ticket (one person).
model Certificate {
id String @id @default(uuid())
eventId String
ticketId String
userId String?
name String
// Short code printed on the certificate and checked on the public verify page.
code String @unique
sessionsAttended Int @default(0)
totalSessions Int @default(0)
manual Boolean @default(false)
issuedById String?
issuedAt DateTime @default(now())
emailedAt DateTime?
revokedAt DateTime?
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
ticket Ticket @relation(fields: [ticketId], references: [id], onDelete: Cascade)
user User? @relation(fields: [userId], references: [id], onDelete: SetNull)
issuedBy User? @relation("CertificateIssuedBy", fields: [issuedById], references: [id], onDelete: SetNull)
@@unique([eventId, ticketId])
@@index([userId])
@@index([ticketId])
@@index([issuedById])
}
// ─── POPIA ────────────────────────────────────────────────────────────────────
// Every change to someone's "News & announcements" opt-in: who, which channel, on or off,
// when and how. Append-only — it's the proof of consent. `address` is set instead of
// userId for people without an account (an ad-hoc broadcast address that unsubscribed).
model CommunicationConsent {
id String @id @default(uuid())
userId String?
user User? @relation(fields: [userId], references: [id], onDelete: SetNull)
address String?
channel String // email | whatsapp
granted Boolean
// signup | privacy_centre | prompt | unsubscribe_link | whatsapp_stop | existing_account | admin
source String
ip String?
createdAt DateTime @default(now())
@@index([userId, createdAt])
@@index([address])
}
// Addresses (email, or phone as 27…) that unsubscribed from news without an account,
// e.g. ad-hoc broadcast recipients. News broadcasts skip them.
model MessagingOptOut {
id String @id @default(uuid())
channel String // email | whatsapp
address String
source String
createdAt DateTime @default(now())
@@unique([channel, address])
}
enum DataRequestType {
access
correction
deletion
}
enum DataRequestStatus {
open
completed
rejected
cancelled
}
// A POPIA request from a data subject. Name and email are copied in so the request
// still shows who made it after the account is anonymised.
model DataRequest {
id String @id @default(uuid())
type DataRequestType
status DataRequestStatus @default(open)
userId String?
user User? @relation(fields: [userId], references: [id], onDelete: SetNull)
name String
email String
details String?
// The admin's answer, sent to the requester when the request is closed.
response String?
// Made and fulfilled by the person themselves (e.g. "Download my data").
selfService Boolean @default(false)
dueAt DateTime
closedAt DateTime?
handledById String?
handledBy User? @relation("DataRequestHandledBy", fields: [handledById], references: [id], onDelete: SetNull)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([status, dueAt])
@@index([userId])
}
// A published version of the privacy notice (versions start at 1). With none published,
// the built-in notice from utils/privacyNotice.js is shown as version 0. Section bodies
// keep their {placeholders} (org name, Information Officer, operators…), filled in when
// shown, so the notice follows Site settings and the operator register.
model PrivacyNotice {
id String @id @default(uuid())
version Int @unique
sections Json // [{ id, title, body }]
changeSummary String?
// Everyone is asked to accept this version again after logging in.
requiresAcceptance Boolean @default(false)
publishedAt DateTime @default(now())
publishedById String?
publishedBy User? @relation(fields: [publishedById], references: [id], onDelete: SetNull)
}
// Someone accepted a version of the privacy notice. Append-only proof, like
// CommunicationConsent.
model NoticeAcceptance {
id String @id @default(uuid())
userId String?
user User? @relation(fields: [userId], references: [id], onDelete: SetNull)
version Int
source String // signup | prompt
ip String?
createdAt DateTime @default(now())
@@index([userId, createdAt])
@@index([version])
}
// A third party that processes personal information for the church (POPIA "operator"):
// payment providers, WhatsApp and email delivery, hosting… Active ones are listed in the
// privacy notice.
model Operator {
id String @id @default(uuid())
name String
purpose String
dataShared String
location String?
website String?
active Boolean @default(true)
order Int @default(0)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
// Someone exported or viewed personal information in bulk: report exports, the attendance
// register, certificate downloads, group lists, form answers, the consent and medical lists,
// backups, and viewing the site as another user.
model DataAccessLog {
id String @id @default(uuid())
actorId String?
actor User? @relation(fields: [actorId], references: [id], onDelete: SetNull)
actorRole String
// report_pdf | report_excel | report_email | report_whatsapp | attendance_export |
// certificates_download | group_lists | form_responses | consent_list | medical_list |
// backup_download | impersonation
action String
what String
eventId String?
records Int?
// Children's or special personal information (youth events, medical details).
sensitive Boolean @default(false)
ip String?
createdAt DateTime @default(now())
@@index([createdAt])
@@index([actorId, createdAt])
@@index([action, createdAt])
}
enum BreachStatus {
open
contained
closed
}
// POPIA s22: a security compromise — what happened, whose information, what was done, and
// when the Information Regulator and the people affected were told.
model SecurityBreach {
id String @id @default(uuid())
title String
status BreachStatus @default(open)
discoveredAt DateTime
occurredAt DateTime?
description String
dataAffected String
peopleAffected Int?
// Free text: who (groups of people, events…), since a breach can involve people without accounts.
whoAffected String?
consequences String?
actionsTaken String?
adviceToPeople String?
// Who caused it, if known (POPIA asks for this when notifying).
causedBy String?
regulatorNotifiedAt DateTime?
peopleNotifiedAt DateTime?
createdById String?
createdBy User? @relation(fields: [createdById], references: [id], onDelete: SetNull)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([status, discoveredAt])
}
// ─── Kids check-in (1.45.0) ───────────────────────────────────────────────────
// A household whose children are checked in at the kids desk. Kept between events (weekly
// kids church). `qrToken` is what the parent's family QR in My account → Kids holds.
model Family {
id String @id @default(uuid())
name String
qrToken String @unique
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
members FamilyMember[]
children Child[]
adults AuthorisedAdult[]
codes KidsPickupCode[]
}
// A parent account in a family. A person is a parent in one family at most.
model FamilyMember {
id String @id @default(uuid())
familyId String
userId String @unique
createdAt DateTime @default(now())
family Family @relation(fields: [familyId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([familyId])
}
// A child in a family. allergies, medicalNotes and custodyNotes are encrypted
// (utils/encryption.js). Deleted by the retention job after months without a check-in.
model Child {
id String @id @default(uuid())
familyId String
firstName String
lastName String?
dateOfBirth DateTime?
gender String?
allergies String?
medicalNotes String?
custodyNotes String?
lastCheckInAt DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
family Family @relation(fields: [familyId], references: [id], onDelete: Cascade)
checkIns KidsCheckIn[]
@@index([familyId])
@@index([lastCheckInAt])
}
// Someone the family allows to collect their children, who needn't have an account (a
// grandparent, a friend). Anyone with the pickup code can collect; this list is what the
// room leader checks the person against, and who the code can be sent to.
model AuthorisedAdult {
id String @id @default(uuid())
familyId String
name String
phone String?
relationship String?
createdAt DateTime @default(now())
family Family @relation(fields: [familyId], references: [id], onDelete: Cascade)
@@index([familyId])
}
// A pickup code: one per family per occasion (a session, or the day on a single-date event),
// shared by the children checked in together. Codes for offline desks are reserved ahead with
// `deviceId` and no family, and claimed when the desk syncs.
model KidsPickupCode {
id String @id @default(uuid())
eventId String
occasion String
code String
familyId String?
deviceId String?
createdAt DateTime @default(now())
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
family Family? @relation(fields: [familyId], references: [id], onDelete: SetNull)
checkIns KidsCheckIn[]
@@unique([eventId, occasion, code])
@@unique([eventId, occasion, familyId])
@@index([eventId, occasion, deviceId])
}
// One child checked in to a room for an occasion. childId is cleared when the child's profile
// is deleted; the attendance (attendee, ticket, ticket usage) stays.
model KidsCheckIn {
id String @id @default(uuid())
eventId String
occasion String
sessionId String?
childId String?
childName String
roomId String?
pickupCodeId String
attendeeId String?
ticketId String?
checkedInAt DateTime @default(now())
checkedInById String?
checkedOutAt DateTime?
checkedOutById String?
collectedBy String?
overrideReason String?
// Offline desks: the device's id for the check-in (makes syncing idempotent).
clientId String? @unique
offline Boolean @default(false)
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
child Child? @relation(fields: [childId], references: [id], onDelete: SetNull)
room AllocationGroup? @relation(fields: [roomId], references: [id], onDelete: SetNull)
pickupCode KidsPickupCode @relation(fields: [pickupCodeId], references: [id], onDelete: Cascade)
checkedInBy User? @relation("KidsCheckInBy", fields: [checkedInById], references: [id], onDelete: SetNull)
checkedOutBy User? @relation("KidsCheckOutBy", fields: [checkedOutById], references: [id], onDelete: SetNull)
alerts KidsAlert[]
@@unique([eventId, occasion, childId])
@@index([eventId, occasion])
@@index([roomId])
@@index([pickupCodeId])
}
enum KidsAlertKind {
wrong_code
page_parent
override
}
// Something the room needs to see: a wrong pickup code, a parent paged, an override.
model KidsAlert {
id String @id @default(uuid())
eventId String
occasion String
kind KidsAlertKind
roomId String?
checkInId String?
message String
createdById String?
createdAt DateTime @default(now())
resolvedAt DateTime?
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
room AllocationGroup? @relation(fields: [roomId], references: [id], onDelete: SetNull)
checkIn KidsCheckIn? @relation(fields: [checkInId], references: [id], onDelete: SetNull)
createdBy User? @relation(fields: [createdById], references: [id], onDelete: SetNull)
@@index([eventId, occasion, createdAt])
@@index([checkInId])
}
// A leader on duty in a kids room for an occasion ("I'm on duty"), for the ratio.
model KidsRoomDuty {
id String @id @default(uuid())
roomId String
occasion String
userId String
startedAt DateTime @default(now())
endedAt DateTime?
// Volunteer rosters (1.46.0): started by a rostered volunteer checking in, not "I'm on duty".
fromRoster Boolean @default(false)
room AllocationGroup @relation(fields: [roomId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([roomId, occasion])
@@index([userId])
}
// ─── Volunteer rosters (1.46.0) ───────────────────────────────────────────────
// A serving role (Welcome, Parking, Sound…) for one event — or, on a multi-session event, one
// session — with the number of people needed. Shift times are optional (null = the session's or
// event's times). A role linked to a kids room puts its volunteers on duty there when they check
// in, so they count for the room's ratio.
model ServingRole {
id String @id @default(uuid())
eventId String
sessionId String?
name String
description String?
needed Int @default(1)
startsAt DateTime?
endsAt DateTime?
kidsRoomId String?
order Int @default(0)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
event Event @relation(fields: [eventId], references: [id], onDelete: Cascade)
session EventSession? @relation(fields: [sessionId], references: [id], onDelete: Cascade)
kidsRoom AllocationGroup? @relation(fields: [kidsRoomId], references: [id], onDelete: SetNull)
slots VolunteerSlot[]
swaps VolunteerSwap[]
@@index([eventId, sessionId])
@@index([kidsRoomId])
}
// A person serving in a role. `assignedById` is the supervisor who put them there (null = they
// signed up). Withdrawing or being removed deletes the row; a swap moves it to the person who
// takes it over. `registrationId` is the free volunteer place they were given, if any.
model VolunteerSlot {
id String @id @default(uuid())
roleId String
userId String
assignedById String?
checkedInAt DateTime?
checkedInById String?
// 'qr' (the volunteer scanned the check-in QR code) or 'manual' (ticked by staff)
checkInMethod String?
remindedAt DateTime?
registrationId String?
createdAt DateTime @default(now())
role ServingRole @relation(fields: [roleId], references: [id], onDelete: Cascade)
user User @relation("VolunteerSlotUser", fields: [userId], references: [id], onDelete: Cascade)
assignedBy User? @relation("VolunteerSlotAssignedBy", fields: [assignedById], references: [id], onDelete: SetNull)
checkedInBy User? @relation("VolunteerSlotCheckedInBy", fields: [checkedInById], references: [id], onDelete: SetNull)
registration Registration? @relation(fields: [registrationId], references: [id], onDelete: SetNull)
swaps VolunteerSwap[]
@@unique([roleId, userId])
@@index([userId])
@@index([registrationId])
}
enum VolunteerSwapStatus {
open
accepted
cancelled
}
// "Can't make it": the volunteer asks someone to take their place. It happens as soon as someone
// accepts (the slot moves to them), and the event's notify recipients are told.
model VolunteerSwap {
id String @id @default(uuid())
slotId String?
roleId String
fromUserId String?
toUserId String?
note String?
status VolunteerSwapStatus @default(open)
createdAt DateTime @default(now())
resolvedAt DateTime?
slot VolunteerSlot? @relation(fields: [slotId], references: [id], onDelete: SetNull)
role ServingRole @relation(fields: [roleId], references: [id], onDelete: Cascade)
fromUser User? @relation("VolunteerSwapFrom", fields: [fromUserId], references: [id], onDelete: SetNull)
toUser User? @relation("VolunteerSwapTo", fields: [toUserId], references: [id], onDelete: SetNull)
@@index([roleId, status])
@@index([slotId])
}
/// Outbound webhooks (1.48.0): a URL that is sent what happens here (registrations, payments,
/// check-ins, new accounts, published events), signed with its own secret.
model WebhookEndpoint {
id String @id @default(uuid())
name String
url String
// HMAC secret, encrypted (utils/encryption.js); shown to the admin once, when made or rotated.
secret String
// Event types it gets, e.g. ["registration.paid", "ticket.checked_in"].
events String[]
isActive Boolean @default(true)
// The operator register entry made for it (POPIA), if any.
operatorId String?
lastSuccessAt DateTime?
lastFailureAt DateTime?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
deliveries WebhookDelivery[]
}
/// One message to one endpoint, with its retries. Kept 30 days.
model WebhookDelivery {
id String @id @default(uuid())
endpointId String
// The event's own id (the same for every endpoint it went to) and type.
eventId String
type String
payload Json
// pending | sending | retrying | succeeded | failed
status String @default("pending")
attempts Int @default(0)
nextAttemptAt DateTime @default(now())
lastStatusCode Int?
lastError String?
deliveredAt DateTime?
// Whose personal information is in it, so it can be removed with their account.
userId String?
createdAt DateTime @default(now())
endpoint WebhookEndpoint @relation(fields: [endpointId], references: [id], onDelete: Cascade)
@@index([status, nextAttemptAt])
@@index([endpointId, createdAt])
@@index([userId])
@@index([createdAt])
}
/// A person's saved filters on a list page (1.49.2): `page` is the list (registrations, users,
/// events, reports), `params` the filter values. At most one default per person and page.
model SavedFilter {
id String @id @default(uuid())
userId String
page String
name String
params Json
isDefault Boolean @default(false)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@unique([userId, page, name])
@@index([userId, page])
}
/// One AI helper call (2.0). The Hub meters the money; this copy is for Site settings → AI (use
/// per person, per helper and per day) and per-person daily limits. No prompt or answer text is
/// kept. `userId` is cleared when the account is anonymised; rows go after 13 months.
model AiCall {
id String @id @default(uuid())
userId String?
helper String
// ok, refused, incomplete, failed, cap_reached, unavailable
outcome String
// What the church was charged, in cents (fractions of a cent).
chargedCents Float @default(0)
createdAt DateTime @default(now())
user User? @relation(fields: [userId], references: [id], onDelete: SetNull)
@@index([createdAt])
@@index([userId, createdAt])
}