Database Configuration

Set up PostgreSQL, run migrations, and understand the multi-tenant database architecture

SaaS Starter Vue uses PostgreSQL as the primary database with a multi-tenant architecture powered by Stancl/Tenancy. Each tenant gets their own isolated database.

Database Architecture

The application uses a multi-database tenancy approach:

• Central Database: Stores system-wide data (tenants, plans, subscriptions, domains)
• Tenant Databases: Each tenant gets their own PostgreSQL database for isolated data storage

This architecture provides complete data isolation between tenants and makes backup/restore operations per-tenant easier.

PostgreSQL Setup

Local Development

Install PostgreSQL on your system:

macOS (Homebrew)

brew install postgresql@16
brew services start postgresql@16

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

docker run --name saas-postgres \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=saas_starter \
  -p 5432:5432 \
  -d postgres:16

Create the central database:

# Connect to PostgreSQL
psql -U postgres

# Create database and user
CREATE DATABASE saas_starter;
CREATE USER saas_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE saas_starter TO saas_user;

# Grant schema permissions
\c saas_starter
GRANT ALL ON SCHEMA public TO saas_user;

Environment Configuration

Configure your database connection in .env:

DB_CONNECTION · string · default: "pgsql" · required

The database driver to use. For this application, use pgsql for PostgreSQL.

DB_HOST · string · default: "127.0.0.1"

The database server hostname or IP address.

DB_PORT · integer · default: "5432"

The PostgreSQL server port.

DB_DATABASE · string · required

The name of your central database.

DB_USERNAME · string · required

Database username with permissions to create databases (required for tenant database creation).

DB_PASSWORD · string · required

Database password for authentication.

DB_CHARSET · string · default: "utf8"

Character set for the database connection.

DB_SSLMODE · string · default: "prefer"

SSL mode for PostgreSQL connections. Options: disable, allow, prefer, require, verify-ca, verify-full

Example Configuration

Local Development

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=saas_starter
DB_USERNAME=saas_user
DB_PASSWORD=secret

DB_CONNECTION=pgsql
DB_HOST=your-db-host.rds.amazonaws.com
DB_PORT=5432
DB_DATABASE=saas_prod
DB_USERNAME=saas_prod_user
DB_PASSWORD=secure-password-here
DB_SSLMODE=require

DB_CONNECTION=pgsql
DB_HOST=postgres
DB_PORT=5432
DB_DATABASE=saas_starter
DB_USERNAME=postgres
DB_PASSWORD=secret

Running Migrations

Central Database Migrations

Run migrations for the central database (tenants, plans, subscriptions):

php artisan migrate

This creates the following central tables:

• tenants - Tenant information and metadata
• domains - Custom domains for tenants
• plans - Subscription plans
• subscriptions - Tenant subscription data
• features - Available features for plans
• activity_logs - System activity tracking
• settings - Global application settings

Tenant Database Migrations

Create and migrate a tenant database:

# Create a new tenant with their database
php artisan tenants:create tenant-slug

# Run migrations for all tenant databases
php artisan tenants:migrate

# Run migrations for a specific tenant
php artisan tenants:migrate --tenants=tenant-id

Tenant migrations are located in database/migrations/tenant/ and run automatically when new tenants are created.

Database Schema Overview

Central Database Tables

Tenants Table

Stores all tenant information:

table('tenants')
  - id (string, primary) - UUID identifier
  - name (string) - Tenant name
  - tenancy_db_name (string, unique) - Database name for this tenant
  - plan_id (foreign) - Current subscription plan
  - owner_name (string) - Tenant owner name
  - owner_email (string) - Tenant owner email
  - owner_password (string) - Tenant owner password
  - status (string) - Trial, Active, Suspended, Canceled
  - subscription_ends_at (timestamp)
  - trial_ends_at (timestamp)
  - canceled_at (timestamp)
  - is_active (boolean)
  - data (json) - Additional metadata
  - created_at, updated_at

Domains Table

Manages custom domains for tenants:

table('domains')
  - id (increments)
  - domain (string, unique) - Full domain name
  - tenant_id (foreign) - References tenants.id
  - created_at, updated_at

Plans Table

Defines available subscription plans:

table('plans')
  - id (increments)
  - name (string) - Plan name
  - slug (string, unique) - URL-friendly identifier
  - description (text) - Plan description
  - price (decimal) - Plan price
  - currency (string) - Default: USD
  - duration_in_days (integer) - Billing cycle length
  - is_free (boolean) - Free plan flag
  - is_active (boolean) - Plan availability
  - created_at, updated_at, deleted_at

Subscriptions Table

Tracks tenant subscriptions:

table('subscriptions')
  - id (increments)
  - tenant_id (foreign) - References tenants.id
  - plan_id (foreign) - References plans.id
  - stripe_id (string) - Stripe subscription ID
  - stripe_status (string) - Stripe subscription status
  - stripe_price (string) - Stripe price ID
  - quantity (integer)
  - trial_ends_at (timestamp)
  - ends_at (timestamp)
  - created_at, updated_at

Features Table

Defines available features:

table('features')
  - id (increments)
  - name (string) - Feature name
  - code (string, unique) - Feature identifier
  - description (text) - Feature description
  - created_at, updated_at

Tenant Database Tables

Each tenant database contains:

• users - Tenant-specific users
• password_reset_tokens - Password reset tokens
• sessions - User sessions
• personal_access_tokens - API tokens
• jobs - Background job queue
• cache - Cache storage
• settings - Tenant-specific settings

Tenancy Configuration

The multi-tenant behavior is configured in config/tenancy.php:

'database' => [
    'central_connection' => env('DB_CONNECTION', 'central'),
    'prefix' => 'tenant',
    'suffix' => '',
],

Tenant databases are named: tenant{tenant_id}

For example, a tenant with ID abc123 gets database: tenantabc123

Database Management Commands

Tenant Operations

# Create a new tenant
php artisan tenants:create

# List all tenants
php artisan tenants:list

# Run migrations for all tenants
php artisan tenants:migrate

# Rollback tenant migrations
php artisan tenants:migrate:rollback

# Seed tenant databases
php artisan tenants:seed

# Run artisan command for all tenants
php artisan tenants:run artisan-command

Database Maintenance

# Refresh central database (DESTRUCTIVE)
php artisan migrate:fresh

# Seed central database
php artisan db:seed

# Create database backup
pg_dump -U saas_user saas_starter > backup.sql

# Restore database
psql -U saas_user saas_starter < backup.sql

Production Considerations

Database User Permissions: The database user must have CREATEDB privilege to create tenant databases automatically.

Grant Database Creation Rights

ALTER USER saas_user CREATEDB;

Connection Pooling

For production, use connection pooling with PgBouncer:

# Install PgBouncer
sudo apt install pgbouncer

# Configure in .env
DB_HOST=127.0.0.1
DB_PORT=6432  # PgBouncer port

Performance Tuning

Optimize PostgreSQL for Laravel:

-- config/database.php already includes:
'prefix_indexes' => true,
'search_path' => 'public',
'sslmode' => env('DB_SSLMODE', 'prefer'),

Backup Strategy

1. Central Database: Daily automated backups
2. Tenant Databases: Per-tenant backup schedules
3. Point-in-Time Recovery: Enable WAL archiving

# Backup all tenant databases
for db in $(psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datname LIKE 'tenant%'"); do
    pg_dump -U saas_user $db > "backup_${db}_$(date +%Y%m%d).sql"
done

Troubleshooting

Connection Issues

# Test connection
php artisan tinker
>>> DB::connection()->getPdo();

# Check PostgreSQL is running
pg_isready -h 127.0.0.1 -p 5432

Permission Errors

-- Grant all permissions
GRANT ALL PRIVILEGES ON DATABASE saas_starter TO saas_user;
GRANT ALL ON SCHEMA public TO saas_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO saas_user;

Tenant Database Issues

# List tenant databases
php artisan tenants:list

# Check specific tenant database
psql -U saas_user -d tenantabc123 -c "\dt"

Next Steps

• Configure email settings for tenant notifications
• Learn about tenant management features
• Set up monitoring and logging for database performance