Database & Prisma

Overview

BookWish uses PostgreSQL as the primary database and Prisma as the ORM (Object-Relational Mapper). Prisma provides type-safe database access, automatic migrations, and excellent TypeScript integration.

Technology Stack

• Database: PostgreSQL (14+)
• ORM: Prisma 5.8.0
• Client: @prisma/client 5.8.0

Database Schema

The schema is defined in /prisma/schema.prisma and organized into layers:

Layer 1: Core Loop

Core user-facing features for discovering and tracking books.

Models:

• User - User accounts (guest, free, premium, bookstore, admin)
• UserPreferences - User notification and privacy settings
• Book - Book catalog with metadata (ISBN, title, authors, etc.)
• Wishlist - User wishlists
• WishlistItem - Books in wishlists with priority and status
• Store - Independent bookstores
• Address - User shipping addresses
• Order - Customer orders
• OrderItem - Items in orders
• Notification - User notifications
• PushToken - FCM device tokens for push notifications
• StockAlert - User alerts for book availability

Layer 2: Store Operations

Bookstore management and commerce features.

Models:

• Inventory - Store inventory with pricing and stock levels
• TradeCreditAccount - Store credit balances per user/store
• TradeCreditTransaction - Credit transaction history
• TradeIn - Trade-in transactions
• TradeInItem - Books in trade-in transactions
• StoreStaff - Staff members with permissions
• POSSale - Point of sale transactions
• POSSaleItem - Items in POS sales

Layer 3: Social

Social features for book discovery and community.

Models:

• Line - Social posts about books ("lines" from books)
• Review - Book reviews with ratings
• Note - Private book notes
• Follow - User following relationships
• StoreFollow - Store following
• Like - Likes on lines and reviews
• UserBlock - User and store blocking
• Report - Content moderation reports

Layer 4: Programs

Book clubs and reading challenges.

Models:

• Club - Book clubs
• ClubBook - Books in clubs
• ClubMember - Club memberships
• Challenge - Reading challenges
• ChallengeBook - Books in challenges
• ChallengeParticipant - Challenge participants

Cross-Cutting: Economics & Subscriptions

Models:

• Subscription - Premium user subscriptions (RevenueCat/Stripe)
• PlatformTransaction - Transaction records for orders
• StorePayout - Store payout records
• IndiePoolDistribution - Indie bookstore pool distributions
• HomeStorePoolAccrual - Annual home store pool accruals
• HomeStorePoolPayout - Home store pool payouts
• AffiliateCommissionPayout - Affiliate commission payouts

Order Notification Digest System

Models:

• OrderDigestQueue - Pending store order notifications
• BookwishOpsDigestQueue - Pending BookWish ops notifications

Key Enums

enum UserTier {
  guest       // Temporary accounts
  free        // Free accounts
  premium     // Premium subscribers
  bookstore   // Bookstore owners
  admin       // BookWish admins
}

enum OrderStatus {
  pending
  confirmed
  processing
  ready_for_pickup
  shipped
  delivered
  cancelled
}

enum FulfillmentType {
  pickup      // In-store pickup
  ship        // Shipped to customer
  gift        // Gift order
}

enum BookCondition {
  new
  like_new
  good
  fair
}

enum NotificationType {
  order_update
  new_follower
  line_reply
  line_like
  review_like
  stock_alert
  club_invite
  club_book_added
  challenge_start
  challenge_ending
  premium_subscription_expiring
  payment_issue
}

Prisma Client Usage

Initialization

// src/config/database.ts
import { PrismaClient } from '@prisma/client';

export const prisma = new PrismaClient({
  log: process.env.NODE_ENV === 'development'
    ? ['query', 'error', 'warn']
    : ['error'],
});

Basic CRUD Operations

Create

const user = await prisma.user.create({
  data: {
    email: 'user@example.com',
    passwordHash: hashedPassword,
    displayName: 'John Doe',
    tier: 'free',
    isGuest: false,
  },
});

Read

// Find unique
const user = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    wishlists: true,
    homeStore: true,
  },
});

// Find many with filtering
const stores = await prisma.store.findMany({
  where: {
    isActive: true,
    city: 'San Francisco',
  },
  orderBy: {
    createdAt: 'desc',
  },
  take: 20,
});

Update

const updated = await prisma.user.update({
  where: { id: userId },
  data: {
    displayName: 'Jane Doe',
    avatarUrl: 'https://...',
  },
});

Delete

await prisma.wishlist.delete({
  where: { id: wishlistId },
});

Relations

// Create with relations
const wishlist = await prisma.wishlist.create({
  data: {
    userId,
    name: 'To Read',
    isPrimary: true,
    items: {
      create: [
        {
          bookId: 'book-123',
          priority: 'high',
          status: 'wish',
        },
      ],
    },
  },
  include: {
    items: {
      include: {
        book: true,
      },
    },
  },
});

// Query with nested includes
const order = await prisma.order.findUnique({
  where: { id: orderId },
  include: {
    items: {
      include: {
        book: true,
        inventory: true,
      },
    },
    shippingAddress: true,
    billingAddress: true,
    store: true,
  },
});

Transactions

// Multiple operations in transaction
const result = await prisma.$transaction(async (tx) => {
  // Create order
  const order = await tx.order.create({
    data: orderData,
  });

  // Reserve inventory
  for (const item of items) {
    await tx.inventory.update({
      where: { id: item.inventoryId },
      data: {
        reservedQuantity: { increment: item.quantity },
      },
    });
  }

  // Deduct trade credit
  if (tradeCreditCents > 0) {
    await tx.tradeCreditAccount.update({
      where: {
        userId_storeId: { userId, storeId },
      },
      data: {
        balanceCents: { decrement: tradeCreditCents },
      },
    });
  }

  return order;
});

Aggregations

// Count
const wishlistCount = await prisma.wishlist.count({
  where: { userId },
});

// Average rating
const result = await prisma.review.aggregate({
  where: { bookId },
  _avg: { rating: true },
  _count: true,
});

// Group by
const ordersByStore = await prisma.order.groupBy({
  by: ['storeId'],
  where: { status: 'confirmed' },
  _count: true,
  _sum: {
    totalCents: true,
  },
});

Raw Queries

For complex queries not supported by Prisma:

const results = await prisma.$queryRaw`
  SELECT s.*, COUNT(DISTINCT o.id) as order_count
  FROM stores s
  LEFT JOIN orders o ON o.store_id = s.id
  WHERE s.is_active = true
  GROUP BY s.id
  ORDER BY order_count DESC
  LIMIT 10
`;

Migrations

Creating Migrations

# Create a new migration
npm run prisma:migrate -- --name add_stock_alerts

# This generates:
# - prisma/migrations/TIMESTAMP_add_stock_alerts/migration.sql
# - Updates prisma/migrations/migration_lock.toml

Running Migrations

# Development: Create and apply migration
npm run prisma:migrate

# Production: Apply pending migrations
npx prisma migrate deploy

Migration Files

Example migration file (prisma/migrations/20240101120000_add_stock_alerts/migration.sql):

-- CreateTable
CREATE TABLE "stock_alerts" (
    "id" TEXT NOT NULL,
    "user_id" TEXT NOT NULL,
    "book_id" TEXT NOT NULL,
    "store_id" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "stock_alerts_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "stock_alerts_user_id_book_id_store_id_key"
ON "stock_alerts"("user_id", "book_id", "store_id");

-- AddForeignKey
ALTER TABLE "stock_alerts" ADD CONSTRAINT "stock_alerts_user_id_fkey"
FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "stock_alerts" ADD CONSTRAINT "stock_alerts_book_id_fkey"
FOREIGN KEY ("book_id") REFERENCES "books"("id") ON DELETE CASCADE ON UPDATE CASCADE;

Indexes

Key indexes for query performance:

model Line {
  // ...

  @@index([authorUserId])
  @@index([bookId])
  @@index([createdAt(sort: Desc)])
  @@index([storeId, createdAt(sort: Desc)])
  @@index([clubId, createdAt(sort: Desc)])
  @@index([parentLineId])
  @@index([parentLineId, createdAt(sort: Asc)])
}

model Notification {
  // ...

  @@index([userId, createdAt(sort: Desc)])
}

model Inventory {
  // ...

  @@index([storeId])
  @@index([bookId])
}

model Book {
  // ...

  @@index([isbn13])
  @@index([title])
}

Unique Constraints

model User {
  email    String? @unique
  username String? @unique
}

model Wishlist {
  @@unique([userId, slug])
}

model WishlistItem {
  @@unique([wishlistId, bookId])
}

model Inventory {
  @@unique([storeId, bookId, condition])
}

model StoreStaff {
  @@unique([storeId, userId])
}

Cascading Deletes

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

model WishlistItem {
  wishlist Wishlist @relation(fields: [wishlistId], references: [id], onDelete: Cascade)
}

model Inventory {
  store Store @relation(fields: [storeId], references: [id], onDelete: Cascade)
}

When a user is deleted, all their wishlists are automatically deleted. When a wishlist is deleted, all its items are deleted.

Database Seeding

// prisma/seed.ts
import { PrismaClient, UserTier } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  // Create admin user
  const admin = await prisma.user.create({
    data: {
      email: 'admin@bookwish.com',
      displayName: 'BookWish Admin',
      tier: UserTier.admin,
      isGuest: false,
    },
  });

  // Create sample store
  const store = await prisma.store.create({
    data: {
      ownerUserId: admin.id,
      name: 'Sample Bookstore',
      slug: 'sample-bookstore',
      city: 'San Francisco',
      state: 'CA',
      country: 'US',
      isActive: true,
    },
  });

  console.log({ admin, store });
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Run seed:

npm run prisma:seed

Best Practices

1. Use Transactions - For multi-step operations that must succeed/fail together
2. Index Frequently Queried Fields - Add indexes for common WHERE/ORDER BY clauses
3. Use select for Performance - Only fetch fields you need
4. Avoid N+1 Queries - Use include to fetch related data in one query
5. Use Enums - For fields with fixed values (status, type, etc.)
6. Soft Deletes When Needed - Use isDeleted flag instead of hard delete for audit trails
7. Use Unique Constraints - Prevent duplicate data at database level
8. Cascade Deletes Carefully - Ensure cascading deletes match business logic
9. Version Control Migrations - Commit migration files to git
10. Test Migrations - Test migrations on staging before production

Common Patterns

Cursor-Based Pagination

const limit = 20;
const items = await prisma.line.findMany({
  take: limit + 1,
  ...(cursor && {
    cursor: { id: cursor },
    skip: 1,
  }),
  orderBy: { createdAt: 'desc' },
});

const hasMore = items.length > limit;
const data = hasMore ? items.slice(0, limit) : items;
const nextCursor = hasMore ? data[data.length - 1].id : null;

Atomic Increments

await prisma.line.update({
  where: { id: lineId },
  data: {
    likeCount: { increment: 1 },
  },
});

Upsert (Create or Update)

await prisma.inventory.upsert({
  where: {
    storeId_bookId_condition: {
      storeId,
      bookId,
      condition: 'new',
    },
  },
  create: {
    storeId,
    bookId,
    quantity: 10,
    priceCents: 1999,
    condition: 'new',
  },
  update: {
    quantity: { increment: 10 },
  },
});

Conditional Queries

const where: any = {
  storeId,
};

if (query) {
  where.book = {
    OR: [
      { title: { contains: query, mode: 'insensitive' } },
      { authors: { hasSome: [query] } },
      { isbn13: query },
    ],
  };
}

const inventory = await prisma.inventory.findMany({ where });

Performance Monitoring

Enable query logging in development:

const prisma = new PrismaClient({
  log: [
    { level: 'query', emit: 'event' },
    { level: 'error', emit: 'stdout' },
    { level: 'warn', emit: 'stdout' },
  ],
});

prisma.$on('query', (e) => {
  console.log('Query: ' + e.query);
  console.log('Duration: ' + e.duration + 'ms');
});