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