Database Schema - GatePass

GatePass uses Prisma ORM with PostgreSQL (production) and SQLite (development) to manage the database schema and relationships.

Overview

The database schema is defined in src/packages/database/schema.prisma and consists of 8 main models that handle users, events, tickets, orders, check-ins, notifications, analytics, and ticket tiers.

Users

User accounts with multiple auth methods

Events

Event details and configuration

Tickets

NFT tickets with blockchain data

Orders

Purchase orders and payment info

Check-Ins

Event attendance and POA NFTs

Notifications

User notifications

Analytics

Event metrics and insights

Ticket Tiers

Multiple pricing tiers per event

Data Models

User Model

Stores user account information with support for multiple authentication methods. Location: schema.prisma:14-39

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  password  String?  // Null for OAuth/wallet users
  name      String?
  walletAddress String? @unique
  googleId      String? @unique
  twitterId     String? @unique
  avatar    String?
  role      String @default("USER")
  
  // Session & security
  refreshToken     String?
  resetToken       String?
  resetTokenExpiry DateTime?
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  organizedEvents Event[]
  orders         Order[]
  checkIns       CheckIn[]
  notifications  Notification[]
}

Fields

String

Unique identifier (CUID)

String

User’s email (unique, required)

password

String?

Hashed password (optional for OAuth/wallet users)

walletAddress

String?

Web3 wallet address (unique, optional)

googleId

String?

Google OAuth ID (unique, optional)

twitterId

String?

Twitter OAuth ID (unique, optional)

role

String

User role (default: “USER”)

Relations

organizedEvents: Events created by this user
orders: Purchase orders made by this user
checkIns: Check-ins performed by this user
notifications: Notifications for this user

Event Model

Stores comprehensive event information including venue, pricing, blockchain data, and settings. Location: schema.prisma:55-113

model Event {
  id          String   @id @default(cuid())
  title       String
  description String?
  venue       String
  address     String?
  city        String?
  country     String?
  latitude    Float?
  longitude   Float?
  source      String   @default("gatepass")
  externalUrl String?
  
  // Dates
  eventDate   DateTime
  saleStart   DateTime
  saleEnd     DateTime
  
  // Ticket info
  totalSupply    Int
  ticketPrice    Float
  currency       String  @default("ETH")
  maxPerWallet   Int     @default(5)
  
  // Blockchain
  contractAddress String?
  chainId         Int     @default(137) // Polygon
  
  // Metadata
  imageUrl       String?
  metadataUri    String?
  category       String @default("MUSIC")
  tags           String // Comma separated
  
  // Settings
  isPublic       Boolean @default(true)
  allowTransfers Boolean @default(true)
  requireKYC     Boolean @default(false)
  
  status         String @default("DRAFT")
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  organizer   User     @relation(fields: [organizerId], references: [id])
  organizerId String
  
  orders      Order[]
  tickets     Ticket[]
  checkIns    CheckIn[]
  analytics   EventAnalytics[]
  tiers       TicketTier[]
}

Key Fields

contractAddress

String?

Deployed smart contract address for NFT tickets

chainId

Int

Blockchain network ID (default: 137 for Polygon)

ticketPrice

Float

Base ticket price (supports crypto decimals)

currency

String

Payment currency (e.g., “ETH”, “MATIC”, “USD”)

status

String

Event status: “DRAFT”, “PUBLISHED”, “CANCELLED”, “COMPLETED”

Indexes

@@index([eventDate, category])
@@index([latitude, longitude])

Optimizes queries for event discovery and location-based searches.

Ticket Model

Represents NFT tickets with blockchain data and metadata. Location: schema.prisma:115-149

model Ticket {
  id       String @id @default(cuid())
  tokenId  Int
  
  // Blockchain data
  contractAddress String
  chainId         Int
  txHash          String?
  blockNumber     Int?
  
  // Metadata
  metadataUri String?
  seatNumber  String?
  section     String?
  tier        String?
  
  // Status
  isUsed      Boolean @default(false)
  usedAt      DateTime?
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  event   Event  @relation(fields: [eventId], references: [id])
  eventId String
  
  order   Order  @relation(fields: [orderId], references: [id])
  orderId String
  
  checkIn CheckIn?

  @@unique([contractAddress, tokenId])
}

The @@unique([contractAddress, tokenId]) constraint ensures each NFT ticket is unique across the blockchain.

Order Model

Tracks ticket purchases and payment information across multiple payment providers. Location: schema.prisma:151-189

model Order {
  id     String @id @default(cuid())
  
  // Payment info
  totalAmount    Float
  quantity       Int
  currency       String
  paymentMethod  String
  paymentStatus  String @default("PENDING")
  
  // Payment processor data
  stripePaymentId    String?
  coinbaseChargeId   String?
  blockchainTxHash   String?
  paystackReference  String?
  flutterwaveReference String?
  mpesaCheckoutRequestId String?
  paymentTxId        String?
  
  // Customer info
  customerEmail  String
  customerName   String?
  billingAddress String? // JSON string
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relations
  user    User   @relation(fields: [userId], references: [id])
  userId  String
  
  event   Event  @relation(fields: [eventId], references: [id])
  eventId String
  
  tickets Ticket[]

  @@index([paymentStatus])
}

The Order model supports multiple payment gateways: Stripe, Coinbase Commerce, Paystack, Flutterwave, M-Pesa, and blockchain transactions.

CheckIn Model

Records event attendance and Proof of Attendance (POA) NFTs. Location: schema.prisma:191-214

model CheckIn {
  id        String   @id @default(cuid())
  
  checkedInAt DateTime @default(now())
  checkedInBy String? // Staff member ID
  location    String? // Gate/entrance name
  
  // POA NFT info
  poaTokenId      Int?
  poaContractAddr String?
  poaTxHash       String?

  // Relations
  ticket   Ticket @relation(fields: [ticketId], references: [id])
  ticketId String @unique
  
  event    Event  @relation(fields: [eventId], references: [id])
  eventId  String
  
  user     User   @relation(fields: [userId], references: [id])
  userId   String
}

POA (Proof of Attendance) NFTs can be minted when a user checks in to an event, creating a permanent on-chain record of attendance.

Notification Model

User notifications for events, sales, and system messages. Location: schema.prisma:41-53

model Notification {
  id        String   @id @default(cuid())
  userId    String
  user      User     @relation(fields: [userId], references: [id])
  title     String
  message   String
  type      String   @default("INFO")
  read      Boolean  @default(false)
  createdAt DateTime @default(now())

  @@index([userId])
}

EventAnalytics Model

Stores event performance metrics and insights. Location: schema.prisma:216-243

model EventAnalytics {
  id        String   @id @default(cuid())
  date      DateTime @default(now())
  
  // Sales metrics
  ticketsSold    Int @default(0)
  revenue        Float @default(0)
  uniqueBuyers   Int @default(0)
  
  // Check-in metrics
  checkIns       Int @default(0)
  checkInRate    Float @default(0) // Percentage
  noShows        Int @default(0)
  
  // Geographic data (JSON strings)
  topCountries   String?
  topCities      String?
  
  // Time-based data
  hourlyBreakdown String?

  event   Event  @relation(fields: [eventId], references: [id])
  eventId String

  @@unique([eventId, date])
}

TicketTier Model

Supports multiple pricing tiers per event (VIP, Regular, Early Bird, etc.). Location: schema.prisma:245-263

model TicketTier {
  id               String   @id @default(cuid())
  name             String
  description      String?
  price            Float
  availableQuantity Int
  maxPerPerson     Int      @default(5)
  saleStart        DateTime
  saleEnd          DateTime
  createdAt        DateTime @default(now())
  updatedAt        DateTime @updatedAt

  event   Event   @relation(fields: [eventId], references: [id])
  eventId String

  @@index([eventId])
}

Entity Relationship Diagram

Database Configuration

Prisma Schema Config

Location: schema.prisma:4-12

generator client {
  provider = "prisma-client-js"
  output   = "./generated/client"
}

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

The Prisma Client is generated to src/packages/database/generated/client for easy import.

Prisma Commands

Manage your database with these Prisma commands:

cd src/packages/database
npm run generate
# or
npx prisma generate

Working with the Database

Import Prisma Client

import { PrismaClient } from '@passmint/database';

const prisma = new PrismaClient();

// Use in your code
const users = await prisma.user.findMany();

Example Queries

const event = await prisma.event.create({
  data: {
    title: 'Tech Conference 2024',
    description: 'Annual tech conference',
    venue: 'Convention Center',
    eventDate: new Date('2024-06-15'),
    saleStart: new Date('2024-04-01'),
    saleEnd: new Date('2024-06-14'),
    totalSupply: 500,
    ticketPrice: 99.99,
    currency: 'USD',
    category: 'TECHNOLOGY',
    tags: 'tech,conference,networking',
    organizerId: userId,
  },
});

Database Migrations

Creating a Migration

When you modify the schema, create a migration:

cd src/packages/database
npx prisma migrate dev --name add_ticket_tiers

This will:

Generate SQL migration files
Apply the migration to your database
Regenerate the Prisma Client

Migration Files

Migrations are stored in:

src/packages/database/prisma/migrations/
├── 20240101000000_initial/
│   └── migration.sql
├── 20240102000000_add_ticket_tiers/
│   └── migration.sql
└── migration_lock.toml

Best Practices

Use Transactions

Wrap related operations in transactions to ensure data consistency.

await prisma.$transaction(async (tx) => {
  const order = await tx.order.create({...});
  const tickets = await tx.ticket.createMany({...});
});

Include Relations

Use include or select to fetch related data efficiently.

const event = await prisma.event.findUnique({
  where: { id },
  include: {
    organizer: true,
    tiers: true,
  },
});

Index Queries

Add indexes for frequently queried fields.

@@index([eventDate, category])
@@index([userId])

Handle Errors

Catch and handle Prisma errors properly.

try {
  await prisma.user.create({...});
} catch (e) {
  if (e.code === 'P2002') {
    // Unique constraint violation
  }
}

Troubleshooting

Prisma Client Not Found

Regenerate the Prisma Client:

cd src/packages/database
npx prisma generate

Migration Conflicts

If migrations are out of sync:

# Reset database (WARNING: deletes all data)
npx prisma migrate reset

# Or resolve conflicts manually
npx prisma migrate resolve

Database Connection Errors

Check your DATABASE_URL in .env:

# For SQLite (development)
DATABASE_URL="file:./dev.db"

# For PostgreSQL (production)
DATABASE_URL="postgresql://user:pass@host:5432/db"

Next Steps

Local Setup

Set up your development environment

Testing

Learn about testing the database

API Reference

Explore API endpoints using the database

Prisma Docs

Official Prisma documentation

Get Started

Features

Guides

Integration

Development

​Overview

Users

Events

Tickets

Orders

Check-Ins

Notifications

Analytics

Ticket Tiers

​Data Models

​User Model

​Event Model

​Ticket Model

​Order Model

​CheckIn Model

​Notification Model

​EventAnalytics Model

​TicketTier Model

​Entity Relationship Diagram

​Database Configuration

​Prisma Schema Config

​Prisma Commands

​Working with the Database

​Import Prisma Client

​Example Queries

​Database Migrations

​Creating a Migration

​Migration Files

​Best Practices

Use Transactions

Include Relations

Index Queries

Handle Errors

​Troubleshooting

​Next Steps

Local Setup

Testing

API Reference

Prisma Docs

Build docs developers (and LLMs) love

Overview

Data Models

User Model

Event Model

Ticket Model

Order Model

CheckIn Model

Notification Model

EventAnalytics Model

TicketTier Model

Entity Relationship Diagram

Database Configuration

Prisma Schema Config

Prisma Commands

Working with the Database

Import Prisma Client

Example Queries

Database Migrations

Creating a Migration

Migration Files

Best Practices

Troubleshooting

Next Steps