Database Schema — Frontpics Image Marketplace

All tables use UUID primary keys generated by PostgreSQL (gen_random_uuid()). All timestamps are stored in UTC. Soft deletes (where present) use deleted_at nullable timestamp — hard deletes are not used on user-facing data.

Schema is defined in packages/database/prisma/schema.prisma (Prisma SDL), then applied to PostgreSQL via migrations.

---

Entity Relationship Overview

users
  ├── photographer_profiles (1:1)
  ├── agent_profiles (1:1)
  ├── credit_wallets (1:1)
  ├── images (1:N, as photographer)
  ├── orders (1:N, as buyer)
  ├── payouts (1:N, as photographer)
  └── audit_logs (1:N, as actor)

images
  ├── image_files (1:N variants)
  ├── image_tags (N:M via junction)
  ├── locations (N:1)
  ├── license_types (N:1)
  └── order_items (1:N)

orders
  └── order_items (1:N)

credit_wallets
  └── credit_transactions (1:N)

---

Prisma Schema

// packages/database/prisma/schema.prisma

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

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

// ─────────────────────────────────────────
// ENUMS
// ─────────────────────────────────────────

enum Role {
  buyer
  photographer
  admin
}

enum UserStatus {
  active
  suspended
  pending_verification
}

enum ImageStatus {
  draft            // photographer saved but not submitted
  pending_review   // submitted, waiting admin approval
  active           // live in marketplace
  rejected         // failed moderation
  archived         // hidden by photographer or admin
}

enum ImageVariant {
  original
  large
  medium
  thumbnail
  watermarked
}

enum TransactionType {
  purchase     // credits added to wallet (top-up)
  spend        // credits deducted (image purchase)
  refund       // credits returned on order refund
  payout       // credits deducted as payout to photographer
  adjustment   // manual admin correction
  bonus        // free credits
}

enum OrderStatus {
  pending
  completed
  refunded
  failed
}

enum PayoutStatus {
  pending
  processing
  completed
  failed
}

// ─────────────────────────────────────────
// USERS
// ─────────────────────────────────────────

model User {
  id                String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  email             String    @unique @db.VarChar(320)
  passwordHash      String?   @db.VarChar(255)       // nullable for future OAuth
  firstName         String    @db.VarChar(100)
  lastName          String    @db.VarChar(100)
  avatarUrl         String?   @db.VarChar(2048)
  role              Role      @default(buyer)
  status            UserStatus @default(pending_verification)
  emailVerifiedAt   DateTime?
  createdAt         DateTime  @default(now())
  updatedAt         DateTime  @updatedAt
  deletedAt         DateTime?                         // soft delete

  photographerProfile  PhotographerProfile?
  agentProfile         AgentProfile?
  wallet               CreditWallet?
  images               Image[]
  orders               Order[]
  payouts              Payout[]
  auditLogs            AuditLog[]

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

// ─────────────────────────────────────────
// PHOTOGRAPHER PROFILES
// ─────────────────────────────────────────

model PhotographerProfile {
  id              String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId          String    @unique @db.Uuid
  bio             String?   @db.Text
  websiteUrl      String?   @db.VarChar(2048)
  portfolioUrl    String?   @db.VarChar(2048)
  verified        Boolean   @default(false)
  verifiedAt      DateTime?
  payoutEmail     String?   @db.VarChar(320)         // PayPal / bank email for payouts
  totalCreditsEarned Int    @default(0)
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt

  user            User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("photographer_profiles")
}

// ─────────────────────────────────────────
// AGENT / BUYER PROFILES
// ─────────────────────────────────────────

model AgentProfile {
  id              String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId          String    @unique @db.Uuid
  agencyName      String?   @db.VarChar(200)
  licenseNumber   String?   @db.VarChar(100)
  phone           String?   @db.VarChar(30)
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt

  user            User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("agent_profiles")
}

// ─────────────────────────────────────────
// LOCATIONS
// ─────────────────────────────────────────

model Location {
  id              String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  addressLine1    String?   @db.VarChar(255)
  addressLine2    String?   @db.VarChar(255)
  city            String?   @db.VarChar(100)
  stateProvince   String?   @db.VarChar(100)
  postalCode      String?   @db.VarChar(20)
  countryCode     String    @db.Char(2)
  neighborhood    String?   @db.VarChar(100)
  latitude        Decimal?  @db.Decimal(10, 8)
  longitude       Decimal?  @db.Decimal(11, 8)
  // Raw PostGIS geography column managed via raw SQL migration:
  // coordinates GEOGRAPHY(POINT, 4326)  -- added in migration, not in Prisma SDL
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt

  images          Image[]

  @@index([city])
  @@index([countryCode])
  @@map("locations")
}

// ─────────────────────────────────────────
// TAGS
// ─────────────────────────────────────────

model Tag {
  id              String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name            String    @unique @db.VarChar(100)
  slug            String    @unique @db.VarChar(100)
  category        String?   @db.VarChar(50)           // 'property_type' | 'style' | 'feature'
  createdAt       DateTime  @default(now())

  imageTags       ImageTag[]

  @@map("tags")
}

// ─────────────────────────────────────────
// LICENSE TYPES
// ─────────────────────────────────────────

model LicenseType {
  id              String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name            String    @db.VarChar(100)
  slug            String    @unique @db.VarChar(100)
  description     String    @db.Text
  allowsWebUse    Boolean   @default(true)
  allowsPrintUse  Boolean   @default(false)
  allowsResale    Boolean   @default(false)
  allowsEditorial Boolean   @default(true)
  maxUses         Int?                                  // null = unlimited
  isExclusive     Boolean   @default(false)
  createdAt       DateTime  @default(now())

  images          Image[]
  orderItems      OrderItem[]

  @@map("license_types")
}

// ─────────────────────────────────────────
// IMAGES
// ─────────────────────────────────────────

model Image {
  id                  String      @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  photographerId      String      @db.Uuid
  title               String      @db.VarChar(255)
  description         String?     @db.Text
  locationId          String?     @db.Uuid
  status              ImageStatus @default(draft)
  isDrone             Boolean     @default(false)
  shotAt              DateTime?                         // when the photo was taken
  exifData            Json?                             // raw EXIF as JSONB
  basePriceCredits    Int                               // price in credits (e.g. 10 = 10 credits)
  licenseTypeId       String      @db.Uuid
  allowRelicense      Boolean     @default(false)
  downloadCount       Int         @default(0)
  viewCount           Int         @default(0)
  moderationNote      String?     @db.Text              // rejection reason from admin
  // tsvector column managed via raw SQL trigger:
  // search_vector TSVECTOR  -- updated by trigger on insert/update
  createdAt           DateTime    @default(now())
  updatedAt           DateTime    @updatedAt
  deletedAt           DateTime?

  photographer        User        @relation(fields: [photographerId], references: [id])
  location            Location?   @relation(fields: [locationId], references: [id])
  licenseType         LicenseType @relation(fields: [licenseTypeId], references: [id])
  files               ImageFile[]
  imageTags           ImageTag[]
  orderItems          OrderItem[]

  @@index([status])
  @@index([photographerId])
  @@index([locationId])
  @@index([isDrone])
  @@index([basePriceCredits])
  @@index([createdAt(sort: Desc)])
  @@map("images")
}

// ─────────────────────────────────────────
// IMAGE FILES (VARIANTS)
// ─────────────────────────────────────────

model ImageFile {
  id              String        @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  imageId         String        @db.Uuid
  variant         ImageVariant
  r2Key           String        @db.VarChar(1024)      // R2 object key
  r2Bucket        String        @db.VarChar(255)
  cdnUrl          String        @db.VarChar(2048)      // Cloudflare CDN URL
  mimeType        String        @db.VarChar(100)
  fileSizeBytes   BigInt
  width           Int?
  height          Int?
  createdAt       DateTime      @default(now())

  image           Image         @relation(fields: [imageId], references: [id], onDelete: Cascade)

  @@unique([imageId, variant])
  @@map("image_files")
}

// ─────────────────────────────────────────
// IMAGE TAGS (JUNCTION)
// ─────────────────────────────────────────

model ImageTag {
  imageId         String    @db.Uuid
  tagId           String    @db.Uuid

  image           Image     @relation(fields: [imageId], references: [id], onDelete: Cascade)
  tag             Tag       @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([imageId, tagId])
  @@map("image_tags")
}

// ─────────────────────────────────────────
// CREDIT WALLETS
// ─────────────────────────────────────────

model CreditWallet {
  id              String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  userId          String    @unique @db.Uuid
  balance         Int       @default(0)                // current balance in credits
  totalPurchased  Int       @default(0)                // lifetime credits purchased
  totalSpent      Int       @default(0)                // lifetime credits spent
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt

  user            User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  transactions    CreditTransaction[]

  @@map("credit_wallets")
}

// ─────────────────────────────────────────
// CREDIT TRANSACTIONS
// ─────────────────────────────────────────

model CreditTransaction {
  id              String          @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  walletId        String          @db.Uuid
  type            TransactionType
  amount          Int                                   // positive = credit, negative = debit
  balanceAfter    Int                                   // snapshot of balance after this tx
  referenceId     String?         @db.Uuid              // orderId or payoutId
  referenceType   String?         @db.VarChar(50)       // 'order' | 'payout' | 'adjustment'
  description     String          @db.VarChar(255)
  createdAt       DateTime        @default(now())

  wallet          CreditWallet    @relation(fields: [walletId], references: [id])

  @@index([walletId])
  @@index([referenceId])
  @@map("credit_transactions")
}

// ─────────────────────────────────────────
// ORDERS
// ─────────────────────────────────────────

model Order {
  id              String      @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  buyerId         String      @db.Uuid
  status          OrderStatus @default(pending)
  totalCredits    Int                                   // sum of order item prices
  stripePaymentId String?     @db.VarChar(255)         // for Phase 2 fiat payments
  createdAt       DateTime    @default(now())
  updatedAt       DateTime    @updatedAt

  buyer           User        @relation(fields: [buyerId], references: [id])
  items           OrderItem[]

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

// ─────────────────────────────────────────
// ORDER ITEMS
// ─────────────────────────────────────────

model OrderItem {
  id                  String      @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  orderId             String      @db.Uuid
  imageId             String      @db.Uuid
  licenseTypeId       String      @db.Uuid
  priceCredits        Int                               // price locked at time of purchase
  downloadKey         String?     @db.VarChar(1024)    // R2 key for the purchased variant
  downloadExpiresAt   DateTime?                         // signed URL expiry
  downloadedAt        DateTime?
  createdAt           DateTime    @default(now())

  order               Order       @relation(fields: [orderId], references: [id])
  image               Image       @relation(fields: [imageId], references: [id])
  licenseType         LicenseType @relation(fields: [licenseTypeId], references: [id])

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

// ─────────────────────────────────────────
// PAYOUTS
// ─────────────────────────────────────────

model Payout {
  id              String        @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  photographerId  String        @db.Uuid
  status          PayoutStatus  @default(pending)
  amountCredits   Int                                   // credits being converted to fiat
  amountFiat      Decimal?      @db.Decimal(10, 2)     // calculated by admin
  currency        String        @default("USD") @db.Char(3)
  paymentMethod   String        @db.VarChar(50)         // 'paypal' | 'bank_transfer'
  paymentRef      String?       @db.VarChar(255)         // external payment reference
  requestedAt     DateTime      @default(now())
  processedAt     DateTime?
  createdAt       DateTime      @default(now())
  updatedAt       DateTime      @updatedAt

  photographer    User          @relation(fields: [photographerId], references: [id])

  @@index([photographerId])
  @@index([status])
  @@map("payouts")
}

// ─────────────────────────────────────────
// AUDIT LOGS
// ─────────────────────────────────────────

model AuditLog {
  id              String    @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  actorId         String?   @db.Uuid                   // null for system actions
  actorRole       String?   @db.VarChar(50)
  action          String    @db.VarChar(100)            // e.g. 'image.approved'
  entityType      String?   @db.VarChar(50)             // e.g. 'Image', 'User'
  entityId        String?   @db.Uuid
  payload         Json?                                  // before/after or extra context
  ipAddress       String?   @db.VarChar(45)             // IPv4 or IPv6
  userAgent       String?   @db.VarChar(500)
  createdAt       DateTime  @default(now())

  actor           User?     @relation(fields: [actorId], references: [id], onDelete: SetNull)

  @@index([actorId])
  @@index([action])
  @@index([entityType, entityId])
  @@index([createdAt(sort: Desc)])
  @@map("audit_logs")
}

---

Raw SQL Additions (applied via Prisma raw migration)

These features require SQL not expressible in Prisma SDL:

1. PostGIS Geography Column on Locations

-- Migration: add_postgis_to_locations
ALTER TABLE locations
  ADD COLUMN coordinates GEOGRAPHY(POINT, 4326);

-- Populate from lat/lon on insert/update
CREATE OR REPLACE FUNCTION sync_location_coordinates()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.latitude IS NOT NULL AND NEW.longitude IS NOT NULL THEN
    NEW.coordinates := ST_MakePoint(NEW.longitude::float, NEW.latitude::float)::geography;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_location_coordinates
BEFORE INSERT OR UPDATE ON locations
FOR EACH ROW EXECUTE FUNCTION sync_location_coordinates();

CREATE INDEX idx_locations_coordinates
  ON locations USING GIST (coordinates);

2. Full-Text Search on Images

-- Migration: add_fts_to_images
ALTER TABLE images
  ADD COLUMN search_vector TSVECTOR;

-- Populate on every insert/update
CREATE OR REPLACE FUNCTION update_image_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.description, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_image_search_vector
BEFORE INSERT OR UPDATE OF title, description ON images
FOR EACH ROW EXECUTE FUNCTION update_image_search_vector();

-- Also update when tags change (via separate migration or function)

CREATE INDEX idx_images_search_vector
  ON images USING GIN (search_vector);

3. Check Constraints

-- Credit wallet balance never goes negative
ALTER TABLE credit_wallets
  ADD CONSTRAINT chk_wallet_balance_non_negative
  CHECK (balance >= 0);

-- Image base price must be positive
ALTER TABLE images
  ADD CONSTRAINT chk_image_price_positive
  CHECK (base_price_credits > 0);

-- Order total must be non-negative
ALTER TABLE orders
  ADD CONSTRAINT chk_order_total_non_negative
  CHECK (total_credits >= 0);

---

Credit System Design Notes

Unit: 1 credit = $0.10 USD (configurable)

• Simple mapping. Admins can change the exchange rate in platform settings.
• All prices stored as integer credits (no floating point currency bugs).
• Wallet balance = total purchased − total spent (always recalculated from transactions for correctness).

Atomic purchase transaction (single Prisma $transaction)

1. SELECT wallet FOR UPDATE (pessimistic lock prevents double-spend)
2. Assert wallet.balance >= image.basePriceCredits
3. INSERT order (status = pending)
4. INSERT order_item
5. UPDATE credit_wallets SET balance = balance - price, totalSpent = totalSpent + price  (buyer)
6. INSERT credit_transaction (type = spend, buyer)
7. UPDATE credit_wallets SET balance = balance + price, totalCreditsEarned = ... (photographer)
8. INSERT credit_transaction (type = purchase, photographer wallet)
9. UPDATE orders SET status = completed
10. COMMIT

Balance integrity

• balanceAfter on every CreditTransaction allows auditing and reconstruction.
• Periodic background job checks that wallet.balance == sum of transactions (integrity check, alerts on mismatch).

---

Indexes Summary

Table	Index	Purpose
users	email	Login lookup
users	role, status	Admin filtering
images	status	Active image listing
images	photographerId	Photographer's own images
images	search_vector GIN	Full-text search
images	createdAt DESC	Default sort
locations	coordinates GIST	Geo bounding box queries
locations	city	City filter
credit_transactions	walletId	Transaction history
orders	buyerId	Buyer's order history
audit_logs	action, entityType+entityId	Admin log filtering