Prisma schema and migration plan

This is the schema implementation starts from. It includes gallery approval history, payment-event deduplication, and an immutable reward ledger. Apply it before booking, payment, or gallery code is written.

The schema file will live at server/prisma/schema.prisma when the API project is created. It is specified here so the database can be reviewed before that project exists.

Migration order

1. Create the PostgreSQL 15+ database and set DATABASE_URL to that database. This is the only connection string.
2. Add the Prisma schema below and run prisma migrate against DATABASE_URL. Then run prisma generate. The API and the worker import that generated client for every query.
3. Apply the SQL migration after the Prisma migration with prisma db execute against the same DATABASE_URL. Prisma does not express partial unique indexes, check constraints, or append-only triggers.
4. Run the seed script in non-production environments through the generated client.
5. From then on, change the schema only through a new Prisma migration plus a SQL follow-up when a rule needs a check, a partial index, or a trigger.

Write order inside later transactions is part of the schema contract:

• Set referrals.qualifying_order_id before inserting referral_rewards, because a trigger requires them to match.
• Insert payment_webhook_events in the same transaction as the payment and booking updates.
• Insert a reward_ledger credit only when a reward becomes AVAILABLE.
• Insert a redemption and its negative ledger entry in the same transaction that sets orders.discount_cents.

Prisma schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

enum UserRole {
  CUSTOMER
  ADMIN
}

enum UserStatus {
  PENDING_SETUP
  ACTIVE
  SUSPENDED
  DISABLED
}

enum BookingStatus {
  PENDING_PAYMENT
  PENDING_APPROVAL
  CONFIRMED
  REJECTED
  CANCELLED
  COMPLETED
}

enum OrderStatus {
  PENDING_PAYMENT
  PAID
  PAYMENT_FAILED
  REFUND_PENDING
  PARTIALLY_REFUNDED
  REFUNDED
  CANCELLED
}

enum PaymentProvider {
  STRIPE
}

enum PaymentStatus {
  PENDING
  SUCCEEDED
  FAILED
  REFUND_PENDING
  PARTIALLY_REFUNDED
  REFUNDED
  CANCELLED
}

enum GalleryPublicationStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

enum GalleryAccessStatus {
  PENDING_APPROVAL
  APPROVED
  REVOKED
}

enum GalleryAccessDecision {
  APPROVED
  REJECTED
  REVOKED
}

enum PhotoStatus {
  PENDING_UPLOAD
  PROCESSING
  READY
  FAILED
  REMOVED
}

enum ReferralStatus {
  ATTRIBUTED
  QUALIFIED
  REWARDED
  REJECTED
  REVERSED
}

enum RewardStatus {
  PENDING
  AVAILABLE
  REDEEMED
  REVERSED
  EXPIRED
}

enum LedgerEntryType {
  CREDIT
  REDEMPTION
  REVERSAL
  EXPIRATION
}

enum RedemptionStatus {
  RESERVED
  APPLIED
  RELEASED
  REVERSED
}

enum NotificationChannel {
  EMAIL
  IN_APP
}

enum DeliveryStatus {
  PENDING
  SENT
  FAILED
  CANCELLED
}

enum JobStatus {
  PENDING
  RUNNING
  SUCCEEDED
  FAILED
  CANCELLED
}

model User {
  id        String     @id @default(uuid()) @db.Uuid
  fullName  String     @map("full_name")
  email     String     @unique
  phone     String?
  role      UserRole
  status    UserStatus @default(PENDING_SETUP)
  createdAt DateTime   @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt DateTime   @updatedAt @map("updated_at") @db.Timestamptz(6)

  credentials         UserCredential?
  refreshTokens       RefreshToken[]
  setupTokens         AccountSetupToken[]
  passwordResetTokens PasswordResetToken[]
  mfaFactors          UserMfaFactor[]
  bookings            Booking[]             @relation("BookingCustomer")
  approvedBookings    Booking[]             @relation("BookingApprover")
  orders              Order[]
  galleries           Gallery[]             @relation("GalleryCustomer")
  approvedGalleries   Gallery[]             @relation("GalleryApprover")
  galleryReviews      GalleryAccessReview[]
  photoRatings        PhotoRating[]
  referralCode        ReferralCode?
  referralsMade       Referral[]            @relation("Referrer")
  referralReceived    Referral?             @relation("ReferredUser")
  ledgerEntries       RewardLedger[]
  redemptions         RewardRedemption[]
  notifications       Notification[]
  auditLogs           AuditLog[]
  idempotencyKeys     IdempotencyKey[]

  @@index([role, status])
  @@map("users")
}

model UserCredential {
  userId            String   @id @map("user_id") @db.Uuid
  passwordHash      String   @map("password_hash")
  passwordChangedAt DateTime @default(now()) @map("password_changed_at") @db.Timestamptz(6)
  user              User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("user_credentials")
}

model RefreshToken {
  id        String    @id @default(uuid()) @db.Uuid
  userId    String    @map("user_id") @db.Uuid
  tokenHash String    @unique @map("token_hash")
  expiresAt DateTime  @map("expires_at") @db.Timestamptz(6)
  createdAt DateTime  @default(now()) @map("created_at") @db.Timestamptz(6)
  revokedAt DateTime? @map("revoked_at") @db.Timestamptz(6)
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@index([expiresAt])
  @@map("refresh_tokens")
}

model AccountSetupToken {
  id        String    @id @default(uuid()) @db.Uuid
  userId    String    @map("user_id") @db.Uuid
  tokenHash String    @unique @map("token_hash")
  expiresAt DateTime  @map("expires_at") @db.Timestamptz(6)
  usedAt    DateTime? @map("used_at") @db.Timestamptz(6)
  createdAt DateTime  @default(now()) @map("created_at") @db.Timestamptz(6)
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@map("account_setup_tokens")
}

model PasswordResetToken {
  id        String    @id @default(uuid()) @db.Uuid
  userId    String    @map("user_id") @db.Uuid
  tokenHash String    @unique @map("token_hash")
  expiresAt DateTime  @map("expires_at") @db.Timestamptz(6)
  usedAt    DateTime? @map("used_at") @db.Timestamptz(6)
  createdAt DateTime  @default(now()) @map("created_at") @db.Timestamptz(6)
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@map("password_reset_tokens")
}

model UserMfaFactor {
  id               String    @id @default(uuid()) @db.Uuid
  userId           String    @map("user_id") @db.Uuid
  factorType       String    @map("factor_type")
  secretCiphertext String    @map("secret_ciphertext")
  enabledAt        DateTime? @map("enabled_at") @db.Timestamptz(6)
  createdAt        DateTime  @default(now()) @map("created_at") @db.Timestamptz(6)
  user             User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@map("user_mfa_factors")
}

model Service {
  id          String           @id @default(uuid()) @db.Uuid
  name        String
  description String?
  active      Boolean          @default(true)
  createdAt   DateTime         @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt   DateTime         @updatedAt @map("updated_at") @db.Timestamptz(6)
  packages    ServicePackage[]

  @@map("services")
}

model ServicePackage {
  id              String      @id @default(uuid()) @db.Uuid
  serviceId       String      @map("service_id") @db.Uuid
  name            String
  priceCents      Int         @map("price_cents")
  currency        String      @default("USD") @db.Char(3)
  durationMinutes Int         @map("duration_minutes")
  active          Boolean     @default(true)
  createdAt       DateTime    @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt       DateTime    @updatedAt @map("updated_at") @db.Timestamptz(6)
  service         Service     @relation(fields: [serviceId], references: [id], onDelete: Restrict)
  bookings        Booking[]
  orderItems      OrderItem[]

  @@index([serviceId])
  @@map("service_packages")
}

model Booking {
  id           String         @id @default(uuid()) @db.Uuid
  customerId   String         @map("customer_id") @db.Uuid
  packageId    String         @map("package_id") @db.Uuid
  scheduledAt  DateTime       @map("scheduled_at") @db.Timestamptz(6)
  location     String
  status       BookingStatus  @default(PENDING_PAYMENT)
  approvedBy   String?        @map("approved_by") @db.Uuid
  approvedAt   DateTime?      @map("approved_at") @db.Timestamptz(6)
  decisionNote String?        @map("decision_note")
  createdAt    DateTime       @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt    DateTime       @updatedAt @map("updated_at") @db.Timestamptz(6)
  customer     User           @relation("BookingCustomer", fields: [customerId], references: [id], onDelete: Restrict)
  approver     User?          @relation("BookingApprover", fields: [approvedBy], references: [id], onDelete: SetNull)
  package      ServicePackage @relation(fields: [packageId], references: [id], onDelete: Restrict)
  order        Order?
  gallery      Gallery?

  @@index([customerId])
  @@index([packageId])
  @@index([approvedBy])
  @@index([scheduledAt])
  @@index([status])
  @@map("bookings")
}

model Order {
  id                 String             @id @default(uuid()) @db.Uuid
  customerId         String             @map("customer_id") @db.Uuid
  bookingId          String             @unique @map("booking_id") @db.Uuid
  orderNumber        String             @unique @map("order_number")
  subtotalCents      Int                @map("subtotal_cents")
  discountCents      Int                @default(0) @map("discount_cents")
  totalCents         Int                @map("total_cents")
  currency           String             @default("USD") @db.Char(3)
  status             OrderStatus        @default(PENDING_PAYMENT)
  createdAt          DateTime           @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt          DateTime           @updatedAt @map("updated_at") @db.Timestamptz(6)
  customer           User               @relation(fields: [customerId], references: [id], onDelete: Restrict)
  booking            Booking            @relation(fields: [bookingId], references: [id], onDelete: Restrict)
  items              OrderItem[]
  payments           Payment[]
  redemptions        RewardRedemption[]
  qualifyingReferral Referral?          @relation("QualifyingOrder")
  rewards            ReferralReward[]   @relation("RewardQualifyingOrder")

  @@index([customerId])
  @@index([status])
  @@map("orders")
}

model OrderItem {
  id             String         @id @default(uuid()) @db.Uuid
  orderId        String         @map("order_id") @db.Uuid
  packageId      String         @map("package_id") @db.Uuid
  quantity       Int
  unitPriceCents Int            @map("unit_price_cents")
  currency       String         @default("USD") @db.Char(3)
  description    String
  order          Order          @relation(fields: [orderId], references: [id], onDelete: Cascade)
  package        ServicePackage @relation(fields: [packageId], references: [id], onDelete: Restrict)

  @@index([orderId])
  @@index([packageId])
  @@map("order_items")
}

model Payment {
  id                String          @id @default(uuid()) @db.Uuid
  orderId           String          @map("order_id") @db.Uuid
  provider          PaymentProvider
  providerPaymentId String?         @map("provider_payment_id")
  amountCents       Int             @map("amount_cents")
  currency          String          @default("USD") @db.Char(3)
  status            PaymentStatus   @default(PENDING)
  createdAt         DateTime        @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt         DateTime        @updatedAt @map("updated_at") @db.Timestamptz(6)
  order             Order           @relation(fields: [orderId], references: [id], onDelete: Restrict)

  @@index([orderId])
  @@index([status])
  @@map("payments")
}

model PaymentWebhookEvent {
  id          String          @id @default(uuid()) @db.Uuid
  provider    PaymentProvider
  eventId     String          @map("event_id")
  payloadHash String          @map("payload_hash")
  processedAt DateTime?       @map("processed_at") @db.Timestamptz(6)
  receivedAt  DateTime        @default(now()) @map("received_at") @db.Timestamptz(6)

  @@unique([provider, eventId])
  @@map("payment_webhook_events")
}

model IdempotencyKey {
  id           String   @id @default(uuid()) @db.Uuid
  userId       String?  @map("user_id") @db.Uuid
  scope        String
  key          String
  requestHash  String   @map("request_hash")
  responseCode Int      @map("response_code")
  responseBody Json     @map("response_body")
  createdAt    DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  expiresAt    DateTime @map("expires_at") @db.Timestamptz(6)
  user         User?    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([scope, key])
  @@index([userId])
  @@index([expiresAt])
  @@map("idempotency_keys")
}

model Gallery {
  id                String                   @id @default(uuid()) @db.Uuid
  customerId        String                   @map("customer_id") @db.Uuid
  bookingId         String                   @unique @map("booking_id") @db.Uuid
  title             String
  publicationStatus GalleryPublicationStatus @default(DRAFT) @map("publication_status")
  accessStatus      GalleryAccessStatus      @default(PENDING_APPROVAL) @map("access_status")
  approvedBy        String?                  @map("approved_by") @db.Uuid
  approvedAt        DateTime?                @map("approved_at") @db.Timestamptz(6)
  createdAt         DateTime                 @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt         DateTime                 @updatedAt @map("updated_at") @db.Timestamptz(6)
  customer          User                     @relation("GalleryCustomer", fields: [customerId], references: [id], onDelete: Restrict)
  approver          User?                    @relation("GalleryApprover", fields: [approvedBy], references: [id], onDelete: SetNull)
  booking           Booking                  @relation(fields: [bookingId], references: [id], onDelete: Restrict)
  photos            Photo[]
  accessReviews     GalleryAccessReview[]

  @@index([customerId])
  @@index([approvedBy])
  @@index([accessStatus])
  @@index([publicationStatus])
  @@map("galleries")
}

model GalleryAccessReview {
  id         String                @id @default(uuid()) @db.Uuid
  galleryId  String                @map("gallery_id") @db.Uuid
  reviewedBy String                @map("reviewed_by") @db.Uuid
  decision   GalleryAccessDecision
  reason     String?
  createdAt  DateTime              @default(now()) @map("created_at") @db.Timestamptz(6)
  gallery    Gallery               @relation(fields: [galleryId], references: [id], onDelete: Restrict)
  reviewer   User                  @relation(fields: [reviewedBy], references: [id], onDelete: Restrict)

  @@index([galleryId, createdAt])
  @@index([reviewedBy])
  @@map("gallery_access_reviews")
}

model Photo {
  id           String        @id @default(uuid()) @db.Uuid
  galleryId    String        @map("gallery_id") @db.Uuid
  storageKey   String        @map("storage_key")
  thumbnailKey String?       @map("thumbnail_key")
  status       PhotoStatus   @default(PENDING_UPLOAD)
  contentType  String?       @map("content_type")
  byteSize     Int?          @map("byte_size")
  createdAt    DateTime      @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt    DateTime      @updatedAt @map("updated_at") @db.Timestamptz(6)
  gallery      Gallery       @relation(fields: [galleryId], references: [id], onDelete: Restrict)
  ratings      PhotoRating[]

  @@index([galleryId])
  @@index([status])
  @@map("photos")
}

model PhotoRating {
  id         String   @id @default(uuid()) @db.Uuid
  photoId    String   @map("photo_id") @db.Uuid
  customerId String   @map("customer_id") @db.Uuid
  rating     Int
  feedback   String?
  createdAt  DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt  DateTime @updatedAt @map("updated_at") @db.Timestamptz(6)
  photo      Photo    @relation(fields: [photoId], references: [id], onDelete: Cascade)
  customer   User     @relation(fields: [customerId], references: [id], onDelete: Restrict)

  @@unique([photoId, customerId])
  @@index([customerId])
  @@map("photo_ratings")
}

model ReferralCode {
  id        String   @id @default(uuid()) @db.Uuid
  userId    String   @unique @map("user_id") @db.Uuid
  code      String   @unique
  active    Boolean  @default(true)
  createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("referral_codes")
}

model Referral {
  id                String           @id @default(uuid()) @db.Uuid
  referrerId        String           @map("referrer_id") @db.Uuid
  referredUserId    String           @unique @map("referred_user_id") @db.Uuid
  qualifyingOrderId String?          @unique @map("qualifying_order_id") @db.Uuid
  status            ReferralStatus   @default(ATTRIBUTED)
  createdAt         DateTime         @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt         DateTime         @updatedAt @map("updated_at") @db.Timestamptz(6)
  referrer          User             @relation("Referrer", fields: [referrerId], references: [id], onDelete: Restrict)
  referredUser      User             @relation("ReferredUser", fields: [referredUserId], references: [id], onDelete: Restrict)
  qualifyingOrder   Order?           @relation("QualifyingOrder", fields: [qualifyingOrderId], references: [id], onDelete: Restrict)
  rewards           ReferralReward[]

  @@index([referrerId])
  @@map("referrals")
}

model RewardPolicy {
  id                    String           @id @default(uuid()) @db.Uuid
  percentageBasisPoints Int              @map("percentage_basis_points")
  rewardValidityDays    Int?             @map("reward_validity_days")
  active                Boolean          @default(false)
  validFrom             DateTime         @map("valid_from") @db.Timestamptz(6)
  validUntil            DateTime?        @map("valid_until") @db.Timestamptz(6)
  createdAt             DateTime         @default(now()) @map("created_at") @db.Timestamptz(6)
  rewards               ReferralReward[]

  @@index([active, validFrom])
  @@map("reward_policies")
}

model ReferralReward {
  id                    String         @id @default(uuid()) @db.Uuid
  referralId            String         @map("referral_id") @db.Uuid
  qualifyingOrderId     String         @unique @map("qualifying_order_id") @db.Uuid
  policyId              String         @map("policy_id") @db.Uuid
  eligibleSubtotalCents Int            @map("eligible_subtotal_cents")
  basisPointsApplied    Int            @map("basis_points_applied")
  amountCents           Int            @map("amount_cents")
  currency              String         @default("USD") @db.Char(3)
  status                RewardStatus   @default(PENDING)
  expiresAt             DateTime?      @map("expires_at") @db.Timestamptz(6)
  createdAt             DateTime       @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt             DateTime       @updatedAt @map("updated_at") @db.Timestamptz(6)
  referral              Referral       @relation(fields: [referralId], references: [id], onDelete: Restrict)
  qualifyingOrder       Order          @relation("RewardQualifyingOrder", fields: [qualifyingOrderId], references: [id], onDelete: Restrict)
  policy                RewardPolicy   @relation(fields: [policyId], references: [id], onDelete: Restrict)
  ledgerEntries         RewardLedger[]

  @@unique([referralId, qualifyingOrderId])
  @@index([referralId])
  @@index([policyId])
  @@index([status])
  @@map("referral_rewards")
}

model RewardLedger {
  id           String             @id @default(uuid()) @db.Uuid
  userId       String             @map("user_id") @db.Uuid
  rewardId     String?            @map("reward_id") @db.Uuid
  redemptionId String?            @map("redemption_id") @db.Uuid
  entryType    LedgerEntryType    @map("entry_type")
  amountCents  Int                @map("amount_cents")
  currency     String             @default("USD") @db.Char(3)
  createdAt    DateTime           @default(now()) @map("created_at") @db.Timestamptz(6)
  user         User               @relation(fields: [userId], references: [id], onDelete: Restrict)
  reward       ReferralReward?    @relation(fields: [rewardId], references: [id], onDelete: Restrict)
  redemption   RewardRedemption?  @relation(fields: [redemptionId], references: [id], onDelete: Restrict)

  @@index([userId, createdAt])
  @@index([rewardId])
  @@index([redemptionId])
  @@map("reward_ledger")
}

model RewardRedemption {
  id            String           @id @default(uuid()) @db.Uuid
  userId        String           @map("user_id") @db.Uuid
  orderId       String           @map("order_id") @db.Uuid
  amountCents   Int              @map("amount_cents")
  currency      String           @default("USD") @db.Char(3)
  status        RedemptionStatus @default(RESERVED)
  createdAt     DateTime         @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt     DateTime         @updatedAt @map("updated_at") @db.Timestamptz(6)
  user          User             @relation(fields: [userId], references: [id], onDelete: Restrict)
  order         Order            @relation(fields: [orderId], references: [id], onDelete: Restrict)
  ledgerEntries RewardLedger[]

  @@index([userId, status])
  @@index([orderId])
  @@map("reward_redemptions")
}

model Notification {
  id         String                 @id @default(uuid()) @db.Uuid
  userId     String                 @map("user_id") @db.Uuid
  type       String
  payload    Json
  readAt     DateTime?              @map("read_at") @db.Timestamptz(6)
  createdAt  DateTime               @default(now()) @map("created_at") @db.Timestamptz(6)
  user       User                   @relation(fields: [userId], references: [id], onDelete: Cascade)
  deliveries NotificationDelivery[]

  @@index([userId, readAt])
  @@map("notifications")
}

model NotificationDelivery {
  id             String              @id @default(uuid()) @db.Uuid
  notificationId String              @map("notification_id") @db.Uuid
  channel        NotificationChannel
  status         DeliveryStatus      @default(PENDING)
  attemptCount   Int                 @default(0) @map("attempt_count")
  lastAttemptAt  DateTime?           @map("last_attempt_at") @db.Timestamptz(6)
  createdAt      DateTime            @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt      DateTime            @updatedAt @map("updated_at") @db.Timestamptz(6)
  notification   Notification        @relation(fields: [notificationId], references: [id], onDelete: Cascade)

  @@index([notificationId])
  @@index([status])
  @@map("notification_deliveries")
}

model BackgroundJob {
  id           String    @id @default(uuid()) @db.Uuid
  jobType      String    @map("job_type")
  payload      Json
  status       JobStatus @default(PENDING)
  availableAt  DateTime  @default(now()) @map("available_at") @db.Timestamptz(6)
  attemptCount Int       @default(0) @map("attempt_count")
  lastError    String?   @map("last_error")
  lockedAt     DateTime? @map("locked_at") @db.Timestamptz(6)
  createdAt    DateTime  @default(now()) @map("created_at") @db.Timestamptz(6)
  updatedAt    DateTime  @updatedAt @map("updated_at") @db.Timestamptz(6)

  @@index([status, availableAt])
  @@map("background_jobs")
}

model AuditLog {
  id           String   @id @default(uuid()) @db.Uuid
  actorUserId  String?  @map("actor_user_id") @db.Uuid
  action       String
  resourceType String   @map("resource_type")
  resourceId   String   @map("resource_id") @db.Uuid
  metadata     Json?
  createdAt    DateTime @default(now()) @map("created_at") @db.Timestamptz(6)
  actor        User?    @relation(fields: [actorUserId], references: [id], onDelete: SetNull)

  @@index([actorUserId])
  @@index([resourceType, resourceId])
  @@index([createdAt])
  @@map("audit_logs")
}

referral_rewards has both a unique qualifying_order_id and a unique pair (referral_id, qualifying_order_id). The pair is the stated business rule. The single-column unique key stops one order from funding two rewards. referrals.referred_user_id is unique, so a customer is attributed once.

SQL constraints and triggers

Save this as the next migration after Prisma's initial migration. PostgreSQL enum labels match the Prisma enum values.

ALTER TABLE users
  ADD CONSTRAINT users_email_normalized_chk
  CHECK (email = lower(btrim(email)));

ALTER TABLE referrals
  ADD CONSTRAINT referrals_no_self_chk
  CHECK (referrer_id <> referred_user_id);

ALTER TABLE photo_ratings
  ADD CONSTRAINT photo_ratings_scale_chk
  CHECK (rating BETWEEN 1 AND 5);

ALTER TABLE reward_policies
  ADD CONSTRAINT reward_policies_rate_chk
  CHECK (
    percentage_basis_points BETWEEN 0 AND 10000
    AND (reward_validity_days IS NULL OR reward_validity_days > 0)
  );

ALTER TABLE referral_rewards
  ADD CONSTRAINT referral_rewards_rate_chk
  CHECK (
    basis_points_applied BETWEEN 0 AND 10000
    AND eligible_subtotal_cents >= 0
    AND amount_cents >= 0
    AND currency = 'USD'
  );

ALTER TABLE service_packages
  ADD CONSTRAINT service_packages_usd_chk
  CHECK (price_cents >= 0 AND currency = 'USD' AND duration_minutes > 0);

ALTER TABLE orders
  ADD CONSTRAINT orders_usd_totals_chk
  CHECK (
    currency = 'USD'
    AND subtotal_cents >= 0
    AND discount_cents >= 0
    AND total_cents >= 0
    AND discount_cents <= subtotal_cents
    AND total_cents = subtotal_cents - discount_cents
  );

ALTER TABLE order_items
  ADD CONSTRAINT order_items_usd_chk
  CHECK (quantity > 0 AND unit_price_cents >= 0 AND currency = 'USD');

ALTER TABLE payments
  ADD CONSTRAINT payments_usd_chk
  CHECK (amount_cents >= 0 AND currency = 'USD');

ALTER TABLE reward_redemptions
  ADD CONSTRAINT reward_redemptions_usd_chk
  CHECK (amount_cents > 0 AND currency = 'USD');

ALTER TABLE reward_ledger
  ADD CONSTRAINT reward_ledger_sign_chk
  CHECK (
    currency = 'USD'
    AND (
      (entry_type = 'CREDIT' AND amount_cents > 0)
      OR (entry_type = 'REDEMPTION' AND amount_cents < 0)
      OR (entry_type = 'EXPIRATION' AND amount_cents < 0)
      OR (entry_type = 'REVERSAL' AND amount_cents <> 0)
    )
  );

ALTER TABLE bookings
  ADD CONSTRAINT bookings_confirmed_actor_chk
  CHECK (
    status <> 'CONFIRMED'
    OR (approved_by IS NOT NULL AND approved_at IS NOT NULL)
  );

ALTER TABLE galleries
  ADD CONSTRAINT galleries_approved_actor_chk
  CHECK (
    access_status <> 'APPROVED'
    OR (approved_by IS NOT NULL AND approved_at IS NOT NULL)
  );

CREATE UNIQUE INDEX payments_provider_reference_key
  ON payments (provider, provider_payment_id)
  WHERE provider_payment_id IS NOT NULL;

CREATE UNIQUE INDEX account_setup_tokens_one_unused_key
  ON account_setup_tokens (user_id)
  WHERE used_at IS NULL;

CREATE UNIQUE INDEX password_reset_tokens_one_unused_key
  ON password_reset_tokens (user_id)
  WHERE used_at IS NULL;

CREATE UNIQUE INDEX reward_policies_one_active_key
  ON reward_policies ((true))
  WHERE active;

CREATE OR REPLACE FUNCTION reject_mutation()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE EXCEPTION '% is append-only', TG_TABLE_NAME;
END;
$$;

CREATE TRIGGER reward_ledger_append_only
  BEFORE UPDATE OR DELETE ON reward_ledger
  FOR EACH ROW
  EXECUTE FUNCTION reject_mutation();

CREATE TRIGGER gallery_access_reviews_append_only
  BEFORE UPDATE OR DELETE ON gallery_access_reviews
  FOR EACH ROW
  EXECUTE FUNCTION reject_mutation();

CREATE TRIGGER audit_logs_append_only
  BEFORE UPDATE OR DELETE ON audit_logs
  FOR EACH ROW
  EXECUTE FUNCTION reject_mutation();

CREATE OR REPLACE FUNCTION referral_reward_matches_referral()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  referral_order uuid;
BEGIN
  SELECT qualifying_order_id
    INTO referral_order
  FROM referrals
  WHERE id = NEW.referral_id;

  IF referral_order IS DISTINCT FROM NEW.qualifying_order_id THEN
    RAISE EXCEPTION 'reward qualifying order must match the referral';
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER referral_rewards_match_referral
  BEFORE INSERT OR UPDATE ON referral_rewards
  FOR EACH ROW
  EXECUTE FUNCTION referral_reward_matches_referral();

The webhook unique key UNIQUE(provider, event_id) is already declared in Prisma. The partial unique index on provider payment ids is only in this SQL migration, because Prisma would treat a normal unique constraint as forbidding more than one null id.

Seed plan

Seed non-production data only. Do not put a real administrator password, a payment secret, or a live package price in the repository.

Seed	Contents
Services	Portrait, Wedding, Family, Travel, Modeling, Photography, Video, and Design, matching the public marketing pages.
Packages	One sample package per service. Prices are placeholders, marked inactive until the photographer sets the real USD price.
Reward policy	One active policy at 500 basis points, which is 5 percent. reward_validity_days is null until a business expiry is chosen.
Administrator	Created out of band. The seed does not insert a password hash.

A package used in the acceptance example is 20000 cents, $200.00, at a 5 percent reward, which credits 1000 cents, $10.00. That pair is fixture data for tests. It is not the studio rate card.

What the first migration does not do

• It does not call Stripe.
• It does not upload photographs.
• It does not create a spendable balance by itself. A credit appears only when a later transaction confirms a qualifying booking.
• It does not store image bytes.

Those behaviors are specified in the workflows and are implemented in later phases on top of this schema.