Skip to main content

Database Provisioning

Overview​

Each organization in LusterCMS gets its own isolated PostgreSQL database. This ensures complete data separation, individual backup capability, and compliance with data residency requirements.

How It Works​

When a registration request is approved and provisioned:

  1. Database Creation: A new PostgreSQL database is created with a unique name
  2. Schema Migration: All Alembic migrations are applied to the new database
  3. Initial Data: Default roles, permissions, and settings are seeded
  4. Organization Record: The organization is linked to the new database

Database Naming​

Databases are named using the pattern:

tenant_{organization_slug}_{timestamp}

Example: tenant_acme_corp_20241204

Provisioning Process​

# Simplified flow
def provision_organization(registration_request):
    # 1. Create database
    db_name = f"tenant_{slug}_{timestamp}"
    create_database(db_name)
    
    # 2. Run migrations
    run_alembic_migrations(db_name)
    
    # 3. Create Admin (supervisor)
    admin = create_admin(request.company_name)
    
    # 4. Create Organization
    org = create_organization(
        name=request.org_name,
        slug=request.org_slug,
        database_name=db_name,
        admin_id=admin.id
    )
    
    # 5. Create User
    user = create_user(
        email=request.email,
        password_hash=request.password_hash,
        organization_id=org.id,
        role="admin_client"
    )
    
    return org, user

Connection Routing​

The system uses middleware to route requests to the correct database:

Subdomain Routing​

acme-corp.lustercms.com → tenant_acme_corp database

Header-Based Routing​

For API access, superadmins and admins can switch organizations:

X-Organization-ID: 123

Configuration​

Environment Variables​

# Main database (stores platform data)
DATABASE_URL=postgresql://user:pass@host:5432/main_db

# Database server for tenant databases
TENANT_DB_HOST=localhost
TENANT_DB_PORT=5432
TENANT_DB_USER=postgres
TENANT_DB_PASSWORD=secret

Connection Pooling​

Each tenant database maintains its own connection pool:

# Default pool settings per tenant
pool_size = 5
max_overflow = 10
pool_timeout = 30

Backup & Recovery​

Per-Tenant Backup​

# Backup single tenant
pg_dump -h localhost -U postgres tenant_acme_corp > acme_corp_backup.sql

# Restore single tenant
psql -h localhost -U postgres tenant_acme_corp < acme_corp_backup.sql

Full Platform Backup​

# Backup main database
pg_dump -h localhost -U postgres main_db > main_backup.sql

# Backup all tenant databases
for db in $(psql -t -c "SELECT database_name FROM organizations"); do
    pg_dump -h localhost -U postgres $db > ${db}_backup.sql
done

Monitoring​

Check Tenant Databases​

-- List all tenant databases
SELECT 
    o.name as organization,
    o.database_name,
    o.status
FROM organizations o
WHERE o.database_name IS NOT NULL;

Database Size​

SELECT 
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) as size
FROM pg_database
WHERE datname LIKE 'tenant_%';

Troubleshooting​

Provisioning Failed​

If provisioning fails, check:

  1. Database permissions: Ensure the user can create databases
  2. Disk space: Verify sufficient space for new database
  3. Connection limits: Check max_connections in PostgreSQL
  4. Logs: Check provisioning_error field in registration request

Connection Issues​

# Test tenant database connection
from core.tenants.provisioning import test_tenant_connection

result = test_tenant_connection("tenant_acme_corp")
if not result["success"]:
    print(f"Error: {result['error']}")

Security Considerations​

  1. Isolation: Each tenant's data is completely isolated in separate databases
  2. Access Control: Middleware validates user's access to requested organization
  3. Credentials: Tenant databases use separate credentials (not shared with main DB)
  4. Encryption: Use SSL/TLS for database connections in production

Migration Management​

When deploying new migrations:

# 1. Apply to main database
alembic upgrade head

# 2. Apply to all tenant databases
python -c "from core.tenants.provisioning import migrate_all_tenants; migrate_all_tenants()"

Or use the admin CLI:

python manage.py migrate-tenants