Skip to main content
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: 
brew install postgresql@16
brew services start postgresql@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

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=saas_starter
DB_USERNAME=saas_user
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

Build docs developers (and LLMs) love

Get started for freeTalk to us