​
Overview

WhatsApp Team Inbox uses PostgreSQL to store conversations, messages, team members, and configuration. This guide covers database setup for both local development and production deployment.
PostgreSQL 14 or higher is recommended for optimal performance and features.

​
Database Options

1

Create Supabase Project

  1. Go to supabase.com
  2. Click Start your project
  3. Sign in with GitHub (recommended)
  4. Click New project
  5. Fill in project details:
    • Name: whatsapp-team-inbox
    • Database Password: Generate a strong password (save it!)
    • Region: Choose closest to your users
    • Pricing Plan: Free (to start)
  6. Click Create new project
  7. Wait 2-3 minutes for provisioning
2

Get Database Credentials

  1. Go to Project Settings > Database
  2. Find Connection string section
  3. Copy the URI connection string: 
    postgresql://postgres:[YOUR-PASSWORD]@db.xxxxx.supabase.co:5432/postgres
  4. Replace [YOUR-PASSWORD] with your actual password
Add to your .env file:
DATABASE_URL=postgresql://postgres:your-password@db.xxxxx.supabase.co:5432/postgres
Keep your database password secure! Never commit it to version control.
3

Enable Connection Pooling

For production, enable connection pooling:
  1. In Supabase dashboard, go to Database > Connection Pooling
  2. Enable Session Mode or Transaction Mode
  3. Copy the pooler connection string: 
    postgresql://postgres.xxxxx:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:6543/postgres
Update your .env:
# Use pooler for production
DATABASE_URL=postgresql://postgres.xxxxx:your-password@aws-0-us-east-1.pooler.supabase.com:6543/postgres
Connection pooling prevents “too many connections” errors under load.
4

Configure Database Settings

Optimize for Team Inbox workload:
  1. Go to Database > Settings
  2. Update these settings:
    • Statement timeout: 30000ms (30 seconds)
    • Max connections: 100 (free tier)
    • Shared buffers: Default (managed by Supabase)
For Pro tier and above, consider:
  • Increasing max connections to 200-500
  • Enabling point-in-time recovery
5

Run Database Migrations

Initialize the database schema:
# Navigate to your project directory
cd /path/to/whatsapp-team-inbox

# Install dependencies if not already done
npm install

# Run migrations
npm run db:migrate

# Or using Prisma directly
npx prisma migrate deploy
This creates all necessary tables:
  • users - Team members
  • conversations - WhatsApp conversations
  • messages - Individual messages
  • contacts - Customer contact information
  • assignments - Conversation assignments
  • notifications - User notifications
  • webhooks - Webhook configurations
6

Seed Initial Data (Optional)

Add sample data for testing:
npm run db:seed
This creates:
  • Demo admin user
  • Sample conversations
  • Test contacts

​
Self-Hosted PostgreSQL Setup

1

Install PostgreSQL

  • Ubuntu/Debian
  • macOS
  • Docker
  • Windows
# Update package list
sudo apt update

# Install PostgreSQL
sudo apt install postgresql postgresql-contrib

# Start PostgreSQL service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Check status
sudo systemctl status postgresql
2

Create Database and User

# Connect to PostgreSQL as postgres user
sudo -u postgres psql

# In PostgreSQL prompt:
CREATE DATABASE team_inbox;
CREATE USER team_inbox_user WITH ENCRYPTED PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE team_inbox TO team_inbox_user;

# For PostgreSQL 15+, also grant schema privileges
\c team_inbox
GRANT ALL ON SCHEMA public TO team_inbox_user;

# Exit
\q
Configure your .env:
DATABASE_URL=postgresql://team_inbox_user:your_secure_password@localhost:5432/team_inbox
3

Configure PostgreSQL

Edit PostgreSQL configuration for production:
# Find config file location
sudo -u postgres psql -c 'SHOW config_file'

# Edit postgresql.conf
sudo nano /etc/postgresql/14/main/postgresql.conf
Recommended settings:
# Connection Settings
max_connections = 200
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 1310kB
min_wal_size = 1GB
max_wal_size = 4GB

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'ddl'
log_min_duration_statement = 1000  # Log queries slower than 1s
Restart PostgreSQL:
sudo systemctl restart postgresql
4

Set Up Backups

Create automated backup script:
# Create backup directory
sudo mkdir -p /var/backups/postgresql

# Create backup script
sudo nano /usr/local/bin/backup-postgres.sh
Add this content:
#!/bin/bash
BACKUP_DIR="/var/backups/postgresql"
DB_NAME="team_inbox"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$TIMESTAMP.sql.gz"

# Create backup
pg_dump -U team_inbox_user $DB_NAME | gzip > $BACKUP_FILE

# Keep only last 7 days of backups
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

# Upload to S3 (optional)
# aws s3 cp $BACKUP_FILE s3://your-bucket/backups/
Make executable and schedule:
sudo chmod +x /usr/local/bin/backup-postgres.sh

# Add to crontab (daily at 2 AM)
sudo crontab -e
# Add this line:
0 2 * * * /usr/local/bin/backup-postgres.sh

​
Database Schema

The Team Inbox uses these main tables:

​
Users Table

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL, -- 'admin', 'agent', 'viewer'
  avatar_url TEXT,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

​
Conversations Table

CREATE TABLE conversations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  whatsapp_contact_id VARCHAR(255) NOT NULL,
  contact_name VARCHAR(255),
  contact_phone VARCHAR(50) NOT NULL,
  status VARCHAR(50) DEFAULT 'open', -- 'open', 'closed', 'pending'
  assigned_to UUID REFERENCES users(id),
  last_message_at TIMESTAMP,
  unread_count INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

​
Messages Table

CREATE TABLE messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID REFERENCES conversations(id) ON DELETE CASCADE,
  whatsapp_message_id VARCHAR(255) UNIQUE,
  sender_type VARCHAR(50) NOT NULL, -- 'customer', 'agent'
  sender_id UUID REFERENCES users(id), -- NULL for customer messages
  content TEXT NOT NULL,
  message_type VARCHAR(50) DEFAULT 'text', -- 'text', 'image', 'audio', 'video', 'document'
  media_url TEXT,
  status VARCHAR(50) DEFAULT 'sent', -- 'sent', 'delivered', 'read', 'failed'
  created_at TIMESTAMP DEFAULT NOW()
);

​
Contacts Table

CREATE TABLE contacts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  phone VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(255),
  email VARCHAR(255),
  tags TEXT[],
  notes TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

​
Database Migrations

The application uses Prisma for database migrations:

​
Create a New Migration

# After changing prisma/schema.prisma
npx prisma migrate dev --name add_new_feature

​
Apply Migrations in Production

npx prisma migrate deploy

​
Reset Database (Development Only)

# WARNING: This deletes all data!
npx prisma migrate reset

​
View Migration Status

npx prisma migrate status

​
Performance Optimization

1

Add Database Indexes

Critical indexes for Team Inbox:
-- Speed up conversation queries
CREATE INDEX idx_conversations_assigned_to ON conversations(assigned_to);
CREATE INDEX idx_conversations_status ON conversations(status);
CREATE INDEX idx_conversations_last_message ON conversations(last_message_at DESC);

-- Speed up message queries
CREATE INDEX idx_messages_conversation ON messages(conversation_id, created_at DESC);
CREATE INDEX idx_messages_whatsapp_id ON messages(whatsapp_message_id);

-- Speed up contact lookups
CREATE INDEX idx_contacts_phone ON contacts(phone);
CREATE INDEX idx_contacts_email ON contacts(email);

-- Speed up user queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(is_active);
2

Enable Query Logging

Monitor slow queries:
# In .env file
DATABASE_LOGGING=true
DATABASE_LOG_SLOW_QUERIES=true
DATABASE_SLOW_QUERY_THRESHOLD=1000  # 1 second
3

Configure Connection Pool

Optimize database connections:
# In .env file
DATABASE_POOL_MIN=2
DATABASE_POOL_MAX=10
DATABASE_POOL_IDLE_TIMEOUT=10000  # 10 seconds
DATABASE_POOL_CONNECTION_TIMEOUT=2000  # 2 seconds
4

Implement Caching

Use Redis for frequently accessed data:
# Cache conversation lists
REDIS_URL=redis://localhost:6379
CACHE_ENABLED=true
CACHE_TTL=300  # 5 minutes

​
Monitoring and Maintenance

Monitor Connection Count

SELECT count(*) FROM pg_stat_activity;

Check Database Size

SELECT pg_size_pretty(pg_database_size('team_inbox'));

Find Slow Queries

SELECT query, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Vacuum Database

VACUUM ANALYZE;

​
Backup and Recovery

​
Automated Backups (Supabase)

Supabase provides automatic daily backups on Pro plan:
  1. Go to Database > Backups
  2. View available backups
  3. Restore from backup:
    • Click Restore on desired backup
    • Confirm restoration
    • Wait for completion (5-10 minutes)

​
Manual Backup

# Create backup
pg_dump -U team_inbox_user -h localhost team_inbox > backup.sql

# Compress backup
gzip backup.sql

# Restore from backup
gunzip backup.sql.gz
psql -U team_inbox_user -h localhost team_inbox < backup.sql

​
Backup to Cloud Storage

# Backup to AWS S3
pg_dump team_inbox | gzip | aws s3 cp - s3://your-bucket/backups/team_inbox-$(date +%Y%m%d).sql.gz

# Backup to Google Cloud Storage
pg_dump team_inbox | gzip | gsutil cp - gs://your-bucket/backups/team_inbox-$(date +%Y%m%d).sql.gz

​
Troubleshooting

Causes:
  • PostgreSQL not running
  • Wrong host/port
  • Firewall blocking connections
Solutions:
# Check if PostgreSQL is running
sudo systemctl status postgresql

# Start PostgreSQL
sudo systemctl start postgresql

# Check port
sudo netstat -plnt | grep postgres

# Test connection
psql -U team_inbox_user -h localhost -d team_inbox
Error: FATAL: sorry, too many clients alreadySolutions:
  1. Enable connection pooling
  2. Increase max_connections in postgresql.conf
  3. Fix connection leaks in application code
  4. Use PgBouncer for connection pooling
# Check current connections
SELECT count(*) FROM pg_stat_activity;

# Kill idle connections
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND state_change < current_timestamp - INTERVAL '5 minutes';
Solutions:
  1. Add missing indexes
  2. Analyze query execution plan
  3. Optimize queries
  4. Increase database resources
-- Analyze query
EXPLAIN ANALYZE SELECT * FROM conversations WHERE assigned_to = 'user-id';

-- Find missing indexes
SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE schemaname = 'public'
ORDER BY abs(correlation) DESC;
Solutions:
  1. Clean up old data
  2. Archive old conversations
  3. Run VACUUM
  4. Increase disk size
-- Check table sizes
SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Archive old closed conversations
DELETE FROM conversations
WHERE status = 'closed'
AND updated_at < NOW() - INTERVAL '6 months';

-- Reclaim space
VACUUM FULL;

​
Security Best Practices

Use SSL Connections

Enable SSL for database connections in production

Restrict Access

Use firewall rules to limit database access

Regular Backups

Automate daily backups with retention policy

Strong Passwords

Use long, complex passwords for database users
# Enable SSL in connection string
DATABASE_URL=postgresql://user:pass@host:5432/db?sslmode=require

# For self-signed certificates
DATABASE_URL=postgresql://user:pass@host:5432/db?sslmode=require&sslcert=/path/to/cert

​
Next Steps

Environment Variables

Configure all application settings

Going Live

Production deployment checklist

Monitoring

Set up monitoring and alerts

Security Settings

Configure security and access control