Skip to main content
SolBid uses PostgreSQL with Prisma ORM for database management. The database stores user accounts, game states, player data, and bid history to complement the on-chain Solana program data.

​
Database architecture

The database is structured to:
  • Store user authentication data (email/password and OAuth)
  • Track game state and synchronize with on-chain data
  • Maintain player statistics and bid history
  • Handle OTP verification for email authentication
  • Store off-chain metadata and relationships
The database works alongside the Solana program. Game-critical data is stored on-chain, while user accounts and metadata are stored off-chain in PostgreSQL.

​
Schema overview

The database schema is defined in next-app/prisma/schema.prisma and consists of six main models:

​
User model

Stores user account information and authentication data. 
model User {
  id       Int        @id @default(autoincrement())
  email    String     @unique
  name     String?    
  password String?
  imageUrl String?
  provider String?
  players  Player[]  
}
  • id: Auto-incrementing primary key
  • email: Unique email address (required)
  • name: Display name (optional)
  • password: Hashed password for email/password auth (optional, null for OAuth users)
  • imageUrl: Profile image URL from Cloudinary or OAuth provider
  • provider: OAuth provider name (“google”, null for email/password)
  • players: One-to-many relationship with Player model

​
OTP model

Temporary storage for one-time passwords used in email verification. 
model OTP {
  id        Int      @id @default(autoincrement())
  email     String
  otp       String
  createdAt DateTime @default(now())
}
OTP records should be cleaned up periodically. Consider implementing a cron job to delete records older than 10-15 minutes.

​
Game model

Stores game state synchronized with the on-chain Solana program. 
model Game {
  id                  Int      @id @default(autoincrement())
  gameId              String   @unique                    
  pda                 String
  initialBidAmount    Int                                  
  highestBid          Int                                  
  lastBidTime         DateTime                           
  totalBids           Int      @default(0)          
  lastBidderId        String                              
  prizePool           Int                                 
  platformFeePercent  Int                                  
  gameEnded           Boolean  @default(false)             
  createdAt           DateTime @default(now())
  updatedAt           DateTime @updatedAt
  players             Player[]                           
}
  • gameId: Unique game identifier
  • pda: Program Derived Address for the game on Solana
  • initialBidAmount: Starting bid amount in lamports
  • highestBid: Current highest bid amount
  • lastBidTime: Timestamp of the last bid
  • totalBids: Total number of bids placed
  • lastBidderId: Public key of the last bidder
  • prizePool: Total prize pool in lamports
  • platformFeePercent: Platform fee percentage
  • gameEnded: Whether the game has ended
  • players: One-to-many relationship with Player model

​
Player model

Tracks individual player participation in games. 
model Player {
  id               Int            @id @default(autoincrement())
  playerPubkey     String                              
  pda              String
  totalBidAmount   Int                                
  safe             Boolean        @default(false)           
  royaltyEarned    Int            @default(0)              
  bidCount         Int                                 
  gameId           Int                                  
  role             PlayerRole     @default(PLAYER)
  userId           Int                                  
  user             User           @relation(fields: [userId], references: [id]) 
  bid              Bid?                                   
  game             Game           @relation(fields: [gameId], references: [id]) 
  createdAt        DateTime       @default(now())
}
  • playerPubkey: Player’s Solana wallet public key
  • pda: Player’s Program Derived Address
  • totalBidAmount: Total amount bid by this player
  • safe: Whether the player is marked as “safe”
  • royaltyEarned: Royalties earned by the player
  • bidCount: Number of bids placed
  • role: Player role enum (PLAYER, WINNER, or FINISHER)
  • userId: Foreign key to User model
  • gameId: Foreign key to Game model
  • bid: One-to-one relationship with Bid model

​
Bid model

Stores individual bid transactions. 
model Bid {
  id           Int       @id @default(autoincrement())
  pda          String
  amount       Int                                  
  timestamp    DateTime                          
  playerId     Int       @unique                          
  player       Player    @relation(fields: [playerId], references: [id]) 
  txId         String? 
  createdAt    DateTime  @default(now())
}
  • pda: Bid’s Program Derived Address
  • amount: Bid amount in lamports
  • timestamp: When the bid was placed
  • playerId: Foreign key to Player (one-to-one relationship)
  • txId: Solana transaction ID (optional)

​
GameId model

Stores the current game ID counter. 
model GameId{
  id           Int      @id @default(autoincrement())
  currGameId   Int      @default(1)
}
This model maintains a single record that tracks the current game ID. It’s initialized with ID 1 during database seeding.

​
PlayerRole enum

enum PlayerRole {
  PLAYER
  WINNER
  FINISHER
}
Defines the three possible player roles in a game.

​
Running migrations

Prisma manages database schema changes through migrations.

​
Development migrations

1

Create a migration

When you modify schema.prisma, create a new migration:
cd next-app
npx prisma migrate dev --name description_of_changes
This will:
  • Create a new migration file in prisma/migrations/
  • Apply the migration to your database
  • Regenerate the Prisma client
2

Review migration SQL

Check the generated SQL in prisma/migrations/[timestamp]_description/migration.sql to ensure it’s correct.

​
Production migrations

Always review migrations carefully before applying them to production databases.
# Generate migration without applying
npx prisma migrate dev --create-only

# Deploy to production
npx prisma migrate deploy

​
Existing migrations

The project includes these migrations:
  • 20241014200533_restucture - Initial schema restructure
  • 20241030184217_add_txid - Added transaction ID to Bid model
  • 20241031194616_remove_name_unique_constrant - Removed unique constraint from User name

​
Seeding the database

The seed script initializes the GameId table with a starting value.

​
Seed script

// prisma/seed.ts
import prisma from "../lib/db"

async function main() {
  await prisma.gameId.upsert({
    where: { id: 1 },  
    update: {},  
    create: {
      currGameId: 1,
    },
  });
}

​
Running the seed

cd next-app
pnpm run seed
The seed script uses upsert to avoid duplicate entries. You can run it multiple times safely.

​
Prisma client

The Prisma client is automatically generated from the schema.

​
Regenerate the client

npx prisma generate
The client is automatically regenerated:
  • After running pnpm install (postinstall hook)
  • After running pnpm run build (prebuild hook)
  • After running prisma migrate dev
Manually regenerate if you modify schema.prisma without running migrations.

​
Using the client

The project includes a singleton Prisma client instance: 
// lib/db.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// Development-only: prevent multiple instances
if (process.env.NODE_ENV !== "production") 
  globalForPrisma.prisma = prisma;

export default prisma;
Use it in your code: 
import prisma from "@/lib/db";

const user = await prisma.user.findUnique({
  where: { email: "[email protected]" }
});

​
Prisma Studio

Prisma Studio provides a visual database browser. 
cd next-app
npx prisma studio
This opens a web interface at http://localhost:5555 where you can:
  • View and edit database records
  • Run queries
  • Explore relationships
  • Test data integrity

​
Database management tasks

Warning: This deletes all data!
npx prisma migrate reset
This will:
  1. Drop the database
  2. Create a new database
  3. Apply all migrations
  4. Run the seed script
npx prisma db pull
Introspects the database and updates schema.prisma to match.
npx prisma validate
Checks for syntax errors in schema.prisma.
npx prisma format
Formats the schema.prisma file.

​
Database connection

The database connection is configured via the DATABASE_URL environment variable in .env: 
DATABASE_URL="postgresql://username:password@localhost:5432/solbid"
See the Environment variables page for more details.

​
Best practices

  • Always create migrations for schema changes, never edit the database directly
  • Use descriptive migration names: add_user_avatar, not update1
  • Review generated SQL before applying to production
  • Back up production databases before running migrations
  • Use Prisma Studio for development debugging, not production data editing
  • Index frequently queried fields (Prisma handles some automatically)
  • Use transactions for operations that modify multiple tables

​
Next steps

Build docs developers (and LLMs) love

Get started for freeTalk to us