Database Deployment

Reportr uses PostgreSQL with Prisma ORM for data persistence. This guide covers production database setup, migrations, and best practices.

Database Requirements

PostgreSQL Version

Minimum: PostgreSQL 12
Recommended: PostgreSQL 14 or higher
Compatibility: Tested with PostgreSQL 12-16

Connection Pooling

Required for serverless environments (Vercel, AWS Lambda):

PgBouncer - Connection pooler for PostgreSQL
Supabase Pooler - Built-in connection pooling
Neon - Automatic connection pooling

Without connection pooling, you may hit PostgreSQL connection limits in serverless environments.

Database Providers

Choose a PostgreSQL provider based on your needs:

Vercel Postgres

Pros: Deep Vercel integration, automatic pooling, simple setupCons: More expensive, limited to Vercel ecosystemPricing:

0.25/GB stored +

0.02/GB transferredBest for: Vercel-first deployments

Neon

Pros: Serverless, autoscaling, generous free tier, branchingCons: Newer platform, limited regionsPricing: Free tier (0.5GB), $20/month for proBest for: Development and small-medium production

Supabase

Pros: Free tier (500MB), real-time features, backupsCons: Requires manual pooling configurationPricing: Free tier available, $25/month for proBest for: Projects needing real-time features

Railway

Pros: Simple setup, automatic backups, fair pricingCons: Less specialized for serverlessPricing: $5/month base + usageBest for: Developers wanting simplicity

Database Schema

Core Models

Reportr’s Prisma schema includes:

model User {
  id                   String    @id @default(cuid())
  email                String    @unique
  whiteLabelEnabled    Boolean   @default(false)
  companyName          String?
  primaryColor         String    @default("#8B5CF6")
  logo                 String?
  plan                 Plan      @default(FREE)
  subscriptionStatus   String    @default("free")
  // ... additional fields
  
  clients              Client[]
  reports              Report[]
  payments             Payment[]
}

model Client {
  id                           String    @id @default(cuid())
  name                         String
  domain                       String
  googleSearchConsoleConnected Boolean   @default(false)
  googleAnalyticsConnected     Boolean   @default(false)
  // ... Google API tokens (encrypted)
  
  userId                       String
  user                         User      @relation(fields: [userId], references: [id])
  reports                      Report[]
}

model Report {
  id                    String       @id @default(cuid())
  title                 String
  status                ReportStatus @default(PENDING)
  data                  Json?
  pdfUrl                String?
  aiInsights            Json?
  // ... additional fields
  
  clientId              String
  userId                String
  client                Client       @relation(fields: [clientId], references: [id])
  user                  User         @relation(fields: [userId], references: [id])
}

Key Features

User white-labeling: companyName, primaryColor, logo
Google API tokens: Encrypted storage for OAuth tokens
Report data: JSON fields for flexible data storage
AI insights: Structured AI-generated insights with metadata
Subscription management: PayPal integration for billing

Setup Instructions

1. Create Database

Vercel Postgres
Neon
Supabase
Railway

# Install Vercel CLI
npm i -g vercel

# Create database
vercel postgres create reportr-db

# Link to project
vercel link
vercel env pull .env.local

This automatically sets POSTGRES_URL and other connection variables.

Go to neon.tech
Create a new project
Copy the connection string:

DATABASE_URL="postgresql://user:[email protected]/reportr?sslmode=require"

Neon automatically provides connection pooling.

Go to supabase.com
Create a new project
Go to Settings → Database
Copy both connection strings:

# Direct connection (for migrations)
DATABASE_URL="postgresql://postgres:[email protected]:5432/postgres"

# Pooled connection (for application)
PRISMA_DATABASE_URL="postgresql://postgres:[email protected]:6543/postgres?pgbouncer=true"

Go to railway.app
Create a new project
Add PostgreSQL service
Copy connection string from variables:

DATABASE_URL="postgresql://postgres:[email protected]:5432/railway"

2. Configure Environment Variables

Set in your deployment platform (Vercel, etc.):

# Primary connection (for Prisma migrations)
DATABASE_URL="postgresql://user:password@host:5432/database"

# Pooled connection (for application queries)
PRISMA_DATABASE_URL="postgresql://user:password@host:6543/database?pgbouncer=true"

Use DATABASE_URL for migrations and PRISMA_DATABASE_URL for runtime queries.

3. Run Migrations

Generate Prisma Client

npx prisma generate

Push schema to database

For production (first time):

npx prisma db push

For development (with migration history):

npx prisma migrate dev

Verify database

npx prisma studio

Opens Prisma Studio at http://localhost:5555 to browse your database.

4. Seed Initial Data (Optional)

npm run db:seed

This populates the database with:

Sample app settings
Default email templates
Test user (in development)

Prisma Configuration

Connection Datasource

In prisma/schema.prisma:

datasource db {
  provider  = "postgresql"
  url       = env("PRISMA_DATABASE_URL")  // Pooled connection
  directUrl = env("DATABASE_URL")          // Direct connection for migrations
}

Client Configuration

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

Using Prisma Client

In your application code:

// src/lib/db.ts
import { PrismaClient } from '@prisma/client'

const globalForPrisma = global as unknown as { prisma: PrismaClient }

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

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

This prevents multiple Prisma Client instances in development (hot reload).

Database Migrations

Creating Migrations

# Create a new migration
npx prisma migrate dev --name add_custom_metrics

# Apply migrations in production
npx prisma migrate deploy

Migration Strategy

Development

Use prisma migrate dev to:

Create migration files
Apply to development database
Regenerate Prisma Client

Staging

Use prisma migrate deploy to:

Apply pending migrations
Skip migration generation
Safe for CI/CD pipelines

Production

Use prisma migrate deploy with:

Automatic backups before migration
Rollback plan prepared
Monitor for issues post-migration

Rollback Strategy

Prisma doesn’t have built-in rollback. Options:

Database snapshots: Take snapshot before migration
Manual rollback: Write down migration SQL to revert
Restore from backup: Use provider’s backup system

Performance Optimization

Indexes

Key indexes defined in schema:

model Report {
  // ... fields
  
  @@index([clientId])
  @@index([userId])
  @@index([status])
  @@index([createdAt])
}

model Client {
  // ... fields
  
  @@index([userId])
  @@index([domain])
}

Connection Pooling

Configure PgBouncer for optimal performance:

# Recommended connection string parameters
PRISMA_DATABASE_URL="postgresql://...?pgbouncer=true&connection_limit=1&pool_timeout=10"

Parameters:

pgbouncer=true: Enable PgBouncer mode
connection_limit=1: Limit connections per serverless function
pool_timeout=10: Timeout for acquiring connection (seconds)

Query Optimization

Use select to limit fields
Use include carefully
Paginate large queries

// Bad: Fetches all fields
const users = await prisma.user.findMany()

// Good: Only fetch needed fields
const users = await prisma.user.findMany({
  select: {
    id: true,
    email: true,
    companyName: true,
  },
})

// Bad: N+1 query problem
const clients = await prisma.client.findMany()
for (const client of clients) {
  const reports = await prisma.report.findMany({ where: { clientId: client.id } })
}

// Good: Single query with include
const clients = await prisma.client.findMany({
  include: {
    reports: {
      take: 10,
      orderBy: { createdAt: 'desc' },
    },
  },
})

// Bad: Fetch all reports
const reports = await prisma.report.findMany()

// Good: Paginate with cursor
const reports = await prisma.report.findMany({
  take: 20,
  skip: page * 20,
  orderBy: { createdAt: 'desc' },
})

Backup Strategy

Automated Backups

Vercel Postgres
Neon
Supabase
Railway

Automatic: Daily backups (retained 7 days)
Point-in-time recovery: Available on Pro plan
Configuration: No setup required

Manual Backup

# Export database to SQL file
pg_dump $DATABASE_URL > backup.sql

# Restore from backup
psql $DATABASE_URL < backup.sql

Monitoring & Maintenance

Database Monitoring

Monitor these metrics:

Connection count: Should stay below max connections
Query performance: Slow queries > 1 second
Database size: Monitor growth for cost planning
Error rate: Connection errors, timeouts

Prisma Logging

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')
})

Database Maintenance

Regular tasks:

Vacuum: Reclaim storage (automatic in most providers)
Analyze: Update query planner statistics
Reindex: Rebuild indexes if performance degrades

-- Run manually if needed
VACUUM ANALYZE;
REINDEX DATABASE reportr;

Security Best Practices

Use SSL connections

Always use sslmode=require in connection string:

DATABASE_URL="postgresql://...?sslmode=require"

Rotate database credentials

Update database password periodically:

# Update in database provider dashboard
# Update environment variables in Vercel/deployment platform
# Redeploy application

Use read-only connections for analytics

Create separate database user with read-only access:

CREATE USER analytics_readonly WITH PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_readonly;

Encrypt sensitive data

Encrypt Google API tokens and other sensitive fields before storing:

import crypto from 'crypto'

function encrypt(text: string): string {
  const cipher = crypto.createCipher('aes-256-cbc', process.env.ENCRYPTION_KEY!)
  return cipher.update(text, 'utf8', 'hex') + cipher.final('hex')
}

Troubleshooting

Connection pool exhausted

Error: Can't reach database serverSolution:

Enable connection pooling with PgBouncer
Set connection_limit=1 in connection string
Reduce concurrent function executions

Prisma Client not found

Error: @prisma/client did not initialize yetSolution:

npm run postinstall
# or
npx prisma generate

Migration conflicts

Error: Migration failed to applySolution:

# Reset shadow database
npx prisma migrate reset

# Or resolve manually
npx prisma migrate resolve --applied "migration_name"

Slow queries

Issue: Queries taking > 1 secondSolution:

Add indexes for frequently queried fields
Use select to limit returned fields
Implement pagination for large datasets
Use database query analyzer

Cost Estimation

Monthly Database Costs

Small (< 100 users)
Medium (100-1000 users)
Large (1000+ users)

Neon: Free tier (0.5GB)
Supabase: Free tier (500MB)
Railway: ~$5-10/month
Vercel Postgres: ~$10-20/month

Next Steps

Environment Variables

Configure all required environment variables

Vercel Deployment

Deploy your application to Vercel

Getting Started

Architecture

Core Systems

Deployment

​Database Deployment

​Database Requirements

​PostgreSQL Version

​Connection Pooling

​Database Providers

Vercel Postgres

Neon

Supabase

Railway

​Database Schema

​Core Models

​Key Features

​Setup Instructions

​1. Create Database

​2. Configure Environment Variables

​3. Run Migrations

​4. Seed Initial Data (Optional)

​Prisma Configuration

​Connection Datasource

​Client Configuration

​Using Prisma Client

​Database Migrations

​Creating Migrations

​Migration Strategy

​Rollback Strategy

​Performance Optimization

​Indexes

​Connection Pooling

​Query Optimization

​Backup Strategy

​Automated Backups

​Manual Backup

​Monitoring & Maintenance

​Database Monitoring

​Prisma Logging

​Database Maintenance

​Security Best Practices

​Troubleshooting

​Cost Estimation

​Monthly Database Costs

​Next Steps

Environment Variables

Vercel Deployment

Build docs developers (and LLMs) love

Database Deployment

Database Requirements

PostgreSQL Version

Connection Pooling

Database Providers

Database Schema

Core Models

Key Features

Setup Instructions

1. Create Database

2. Configure Environment Variables

3. Run Migrations

4. Seed Initial Data (Optional)

Prisma Configuration

Connection Datasource

Client Configuration

Using Prisma Client

Database Migrations

Creating Migrations

Migration Strategy

Rollback Strategy

Performance Optimization

Indexes

Connection Pooling

Query Optimization

Backup Strategy

Automated Backups

Manual Backup

Monitoring & Maintenance

Database Monitoring

Prisma Logging

Database Maintenance

Security Best Practices

Troubleshooting

Cost Estimation

Monthly Database Costs

Next Steps