craft2prints/DATABASE_OPTIMIZATION.md

Database Optimization Guide for Craft2Prints

Overview

This guide covers strategies to optimize your PostgreSQL database for handling large datasets efficiently.

---

1. INDEXING STRATEGY

Add Composite Indexes

Add these indexes to your Prisma schema:

model Order {
  // ... existing fields
  @@index([customerId, createdAt])  // For customer order history sorted by date
  @@index([status, createdAt])      // For filtering orders by status
  @@index([email])                  // For quick email lookups
}

model OrderItem {
  @@index([orderId, productId])     // For order detail pages
  @@index([productId])              // For product sales reporting
}

model DesignApproval {
  @@index([customerEmail, status])  // For customer design lookups
  @@index([status, createdAt])      // For pending designs view
  @@index([productId])              // For product-specific designs
}

model Review {
  @@index([productId, approved])    // For public product reviews
  @@index([customerId])             // For customer review history
}

model Product {
  @@index([category, showOnProducts])  // For category filtering
  @@index([showOnPersonalised])        // For made-to-order listings
}

Create Migration

npx prisma migrate dev --name add_performance_indexes

---

2. QUERY OPTIMIZATION

Problem: N+1 Queries

❌ Bad:

const orders = await prisma.order.findMany();
// This runs 1 query
for (const order of orders) {
  const items = await prisma.orderItem.findMany({where: {orderId: order.id}});
  // This runs N more queries (N+1 problem)
}

✅ Good:

const orders = await prisma.order.findMany({
  include: { items: true }  // Fetches everything in 1 query
});

Problem: Fetching Unnecessary Fields

❌ Bad:

const products = await prisma.product.findMany();
// Fetches ALL fields including large JSON fields (colors, colorPhotos)

✅ Good:

const products = await prisma.product.findMany({
  select: {
    id: true,
    name: true,
    slug: true,
    basePrice: true,
    // Only fetch what you need
  }
});

Recommended Query Optimizations for Your Routes

Orders List Page:

const orders = await prisma.order.findMany({
  where: { customerId },
  select: {
    id: true,
    total: true,
    status: true,
    createdAt: true,
    items: { select: { productName: true, quantity: true } }
  },
  orderBy: { createdAt: 'desc' },
  skip: (page - 1) * 20,
  take: 20
});

Design Approvals List:

const designs = await prisma.designApproval.findMany({
  where: { status: 'PENDING' },
  select: {
    id: true,
    title: true,
    customerEmail: true,
    product: { select: { name: true } },
    createdAt: true
  },
  orderBy: { createdAt: 'desc' },
  skip: (page - 1) * 50,
  take: 50
});

---

3. PAGINATION (Critical for Large Datasets)

Implement Offset-Based Pagination

// pages/admin/orders/page.tsx
import { prisma } from '@/lib/prisma';

export default async function OrdersPage({ 
  searchParams 
}: { 
  searchParams: { page?: string } 
}) {
  const page = Math.max(1, parseInt(searchParams.page || '1'));
  const pageSize = 20;
  const skip = (page - 1) * pageSize;

  const [orders, total] = await Promise.all([
    prisma.order.findMany({
      skip,
      take: pageSize,
      orderBy: { createdAt: 'desc' }
    }),
    prisma.order.count() // Get total count for pagination
  ]);

  const totalPages = Math.ceil(total / pageSize);

  return (
    // Render orders with pagination controls
  );
}

Cursor-Based Pagination (Better for Real-Time Data)

const orders = await prisma.order.findMany({
  take: 20,
  skip: 1, // Skip the cursor itself
  cursor: lastOrderId ? { id: lastOrderId } : undefined,
  orderBy: { id: 'desc' }
});

---

4. CACHING STRATEGY

Enable Redis/In-Memory Caching

// lib/cache.ts
import NodeCache from 'node-cache';

const cache = new NodeCache({ stdTTL: 600 }); // 10 min default

export async function getCachedProducts() {
  const cached = cache.get('all_products');
  if (cached) return cached;

  const products = await prisma.product.findMany();
  cache.set('all_products', products);
  return products;
}

export function invalidateProductCache() {
  cache.del('all_products');
}

Cache Invalidation

// When updating products
await updateProduct(id, data);
invalidateProductCache(); // Clear cache on update

---

5. DATABASE MAINTENANCE

Connection Pooling (Already handled by Prisma)

The .env already has connection pooling. Verify:

DATABASE_URL="postgresql://user:pass@localhost:5432/db?schema=public"
# Prisma uses connection pooling by default

Regular Maintenance

Weekly Tasks:

-- Vacuum and analyze (PostgreSQL auto-vacuums, but manual is faster)
VACUUM ANALYZE;

-- Check index bloat
SELECT schemaname, tablename, indexname, idx_blks_read, idx_blks_hit
FROM pg_statio_user_indexes
ORDER BY idx_blks_read DESC;

-- Check slow queries (enable slow query log)
ALTER SYSTEM SET log_min_duration_statement = 1000; -- Log queries > 1 second
SELECT pg_reload_conf();

Monthly Tasks:

# Backup database
pg_dump -U username database_name > backup.sql

# Monitor table sizes
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) 
FROM pg_tables 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

---

6. QUERY TIMEOUTS & SETTINGS

Add Query Timeout in Prisma

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  directUrl = env("DIRECT_URL") // For migrations
  
  // Connection settings
  // Prisma automatically handles these, but you can configure:
  // prismaClient options
}

Environment Variables

# .env
DATABASE_URL="postgresql://user:pass@host:5432/craft2prints?schema=public&connect_timeout=10&statement_timeout=30000"
DIRECT_URL="postgresql://user:pass@host:5432/craft2prints" # For migrations

---

7. DATA ARCHIVING

Archive Old Orders (After 2 Years)

// scripts/archive-old-orders.ts
async function archiveOldOrders() {
  const twoYearsAgo = new Date();
  twoYearsAgo.setFullYear(twoYearsAgo.getFullYear() - 2);

  // Copy to archive table
  const oldOrders = await prisma.order.findMany({
    where: { 
      createdAt: { lt: twoYearsAgo },
      status: 'DELIVERED'
    }
  });

  // Store in archive database or S3
  // Delete from main database
  await prisma.order.deleteMany({
    where: {
      id: { in: oldOrders.map(o => o.id) }
    }
  });
}

---

8. MONITORING & ALERTS

Add Monitoring to Your App

// lib/db-monitoring.ts
export async function monitorDatabaseHealth() {
  const result = await prisma.$queryRaw`
    SELECT 
      NOW() as current_time,
      pg_database.datname,
      pg_size_pretty(pg_database_size(pg_database.datname)) AS size,
      (SELECT count(*) FROM pg_stat_activity WHERE datname = pg_database.datname) AS active_connections
    FROM pg_database
    WHERE datname = 'craft2prints';
  `;
  
  return result;
}

---

9. SPECIFIC OPTIMIZATIONS FOR CRAFT2PRINTS

Admin Orders Page

// Current: Very slow with large orders
const orders = await prisma.order.findMany({
  include: { 
    items: true,
    customer: true  // N+1 problem
  }
});

// Optimized:
const orders = await prisma.order.findMany({
  select: {
    id: true,
    email: true,
    total: true,
    status: true,
    createdAt: true,
    items: { 
      select: { 
        id: true,
        productName: true,
        quantity: true 
      }
    }
  },
  skip: (page - 1) * 50,
  take: 50,
  orderBy: { createdAt: 'desc' }
});

Product List Page (Admin)

// Don't fetch colorPhotos JSON
const products = await prisma.product.findMany({
  select: {
    id: true,
    slug: true,
    name: true,
    category: true,
    basePrice: true,
    showOnProducts: true,
    showOnPersonalised: true,
    // Skip: colorPhotos, colors (large JSON)
  },
  orderBy: { name: 'asc' },
  skip: (page - 1) * 50,
  take: 50
});

Gallery Page (Public)

// Only fetch approved reviews
const reviews = await prisma.review.findMany({
  where: { 
    productId,
    approved: true 
  },
  select: {
    id: true,
    rating: true,
    title: true,
    comment: true,
    customerName: true,
    createdAt: true
  },
  orderBy: { createdAt: 'desc' },
  take: 10 // Limit to 10 recent reviews
});

---

10. QUICK WINS (Do These First!)

1. ✅ Add pagination to all list pages - Immediately reduces query size
2. ✅ Add indexes - Run migration for composite indexes
3. ✅ Use select instead of full includes - Reduce data transfer
4. ✅ Enable Redis caching - Cache frequently accessed data
5. ✅ Set query timeouts - Prevent long-running queries
6. ✅ Archive old data - Keep active tables lean

---

Performance Benchmarks (Expected After Optimization)

Operation	Before	After
Orders list (10k orders)	5-10s	200-500ms
Product admin (5k products)	3-5s	100-300ms
Design approvals	2-3s	50-150ms
Public product page	1-2s	200-500ms

---

Monitoring Script

# Run weekly to check health
npm run monitor:db

# Add to package.json:
"scripts": {
  "monitor:db": "node --require ts-node/register scripts/monitor-db.ts"
}

---

Next Steps

1. Add indexes from section 1
2. Apply query optimizations to your most-used routes
3. Implement pagination on list pages
4. Set up caching for static data
5. Monitor database performance weekly
6. Archive old data quarterly