How I Built a Multi-Tenant SaaS Time Tracker with Flask and PostgreSQL

Why Schema-Per-Tenant?

When building a multi-tenant SaaS product, you face a fundamental architectural decision: how do you isolate tenant data? The three main approaches are shared database with a tenant column, separate databases per tenant, and schema-per-tenant. After evaluating all three for CorgiCafe — a time tracker for 5 cafe locations in Barcelona, I chose PostgreSQL's schema-per-tenant approach.

Schema isolation gives you the best of both worlds: true data isolation without the operational overhead of managing separate databases. Each tenant gets their own PostgreSQL schema with identical table structures, but everything lives in one database.

Setting Up PostgreSQL Schemas

The foundation is creating a schema per tenant when they sign up:

-- Create a new schema for a tenant
CREATE SCHEMA tenant_sagrada;

-- Create tables within the tenant schema
SET search_path TO tenant_sagrada;

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    role VARCHAR(20) DEFAULT 'worker',
    hourly_rate DECIMAL(8,2),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE work_logs (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    check_in TIMESTAMP NOT NULL,
    check_out TIMESTAMP,
    overtime_minutes INTEGER DEFAULT 0
);

Flask Middleware for Tenant Routing

The key piece is middleware that identifies the tenant from the request and sets the PostgreSQL search_path accordingly:

from flask import g, request
import psycopg2

def get_tenant_schema():
    host = request.host.split('.')[0]
    return f'tenant_{host}'

@app.before_request
def set_tenant_schema():
    schema = get_tenant_schema()
    g.schema = schema
    db = get_db()
    db.execute(f"SET search_path TO {schema}, public")

@app.teardown_request
def reset_schema(exception=None):
    db = get_db()
    if db:
        db.execute("SET search_path TO public")

Blueprint Structure

Flask blueprints keep the code organized. Each major feature gets its own blueprint:

app/
+-- __init__.py
+-- tenants/
|   +-- middleware.py     # Schema routing
|   +-- management.py    # Create/delete tenants
+-- employees/
|   +-- routes.py         # CRUD endpoints
|   +-- models.py
+-- timelogs/
|   +-- routes.py         # Check-in/out, reports
|   +-- export.py         # Excel generation
+-- auth/
    +-- routes.py          # Login, roles

Data Isolation Verification

One critical step many developers skip: verifying that tenant data is truly isolated. I wrote a test suite that attempts cross-tenant data access:

def test_tenant_isolation():
    # Create employee in tenant_sagrada
    with tenant_context('tenant_sagrada'):
        emp = create_employee(name='Carlos', role='barista')

    # Try to access from tenant_gotico
    with tenant_context('tenant_gotico'):
        employees = get_all_employees()
        assert emp.id not in [e.id for e in employees]

    print("Tenant isolation verified!")

Migration Strategy

Schema migrations need to run per tenant. I built a custom management command:

def migrate_all_tenants():
    tenants = get_all_tenant_schemas()
    for schema in tenants:
        print(f"Migrating {schema}...")
        db.execute(f"SET search_path TO {schema}")
        run_migrations()
    print(f"Migrated {len(tenants)} tenants")

Real-Time Features

For the check-in/check-out system, I used WebSocket connections for live dashboard updates. When an employee clocks in at the Sagrada location, the manager dashboard updates instantly — no page refresh needed. The overtime detection runs as a background check every minute, flagging shifts that exceed 8 hours.

Excel Export for Payroll

The most-requested feature was one-click Excel export for payroll processing. Using openpyxl, each month's data exports with proper formatting, overtime calculations, and per-employee subtotals. This alone saved the cafe owner 15+ hours per month.

Lessons Learned

Connection pooling matters — with 5 tenants, connection overhead was manageable, but at 50+ you need PgBouncer
Always validate the schema name — SQL injection through schema names is a real risk
Index per schema — PostgreSQL indexes are schema-scoped, so each tenant gets its own set
Shared tables go in public — things like plans, pricing, and global config live in the public schema

If you're building a multi-tenant SaaS, schema-per-tenant is an excellent middle ground. Check out my deep dive on PostgreSQL schema-per-tenant architecture for more implementation details, or get in touch if you need help building something similar.