Building a Multi-Tenant POS: Lessons from the Trenches

When we started building our POS system, we faced a fundamental question: how do we serve a duka in Githurai and a supermarket chain in Westlands with the same system?

The answer was multi-tenancy. But not just any multi-tenancy - a two-layer isolation model that handles both tenant separation and store-level data.

The Two-Layer Model

Most SaaS applications have one isolation boundary: the tenant. But retail is different. A single business (tenant) might have multiple stores, and each store needs its own:

• Inventory levels
• Sales data
• Till sessions
• Staff access

So we built two layers:

Tenant (Business)
├── Store 1 (Westlands)
│   ├── Inventory: 50 units of Sugar
│   ├── Today's Sales: 45,000 KES
│   └── Staff: John, Mary
├── Store 2 (Githurai)
│   ├── Inventory: 30 units of Sugar
│   ├── Today's Sales: 28,000 KES
│   └── Staff: Peter, Jane
└── Store 3 (Mombasa)
    └── ...

Database Strategy: Shared Schema

We chose shared schema (all tenants in one database) over isolated databases. Here's why:

Approach	Pros	Cons
Shared Schema	Simple, cost-effective, easy migrations	Requires careful query filtering
Schema per Tenant	Better isolation	Complex migrations, higher costs
Database per Tenant	Maximum isolation	Expensive, operational nightmare

For a bootstrapped startup serving SMEs, shared schema was the pragmatic choice.

The Implementation

Base Models

Every model inherits from either TenantScopedModel or StoreScopedModel:

class TenantScopedModel(BaseModel):
    """Data isolated by tenant (e.g., Products, Suppliers)"""
    tenant_id = Column(UUID, ForeignKey('tenants.id'), nullable=False)

class StoreScopedModel(TenantScopedModel):
    """Data isolated by store within tenant (e.g., Sales, Inventory)"""
    store_id = Column(UUID, ForeignKey('stores.id'), nullable=False)

Automatic Query Filtering

The magic happens in SQLAlchemy event listeners:

@event.listens_for(Session, "do_orm_execute")
def filter_by_tenant(execute_state):
    if hasattr(g, 'tenant_id'):
        # Automatically inject WHERE tenant_id = :tenant_id
        execute_state.statement = execute_state.statement.filter(
            Model.tenant_id == g.tenant_id
        )

This means developers can write:

# This query automatically filters by tenant
products = Product.query.all()

Instead of:

# No need to remember this every time
products = Product.query.filter_by(tenant_id=g.tenant_id).all()

Middleware Chain

Every request goes through two middleware layers:

# 1. Tenant Middleware
@app.before_request
def set_tenant_context():
    tenant_id = extract_tenant_from_jwt(request)
    g.tenant_id = tenant_id
    g.tenant = Tenant.query.get(tenant_id)

# 2. Store Middleware (optional)
@app.before_request
def set_store_context():
    store_id = request.headers.get('X-Store-ID')
    if store_id:
        # Verify store belongs to tenant
        store = Store.query.filter_by(
            id=store_id,
            tenant_id=g.tenant_id
        ).first_or_404()
        g.store_id = store_id
        g.store = store

What Lives Where?

Understanding data scope is crucial:

Data	Scope	Example
Products	Tenant	"Sugar 1kg" exists for the whole business
Inventory	Store	Westlands has 50 units, Githurai has 30
Sales	Store	Each store has its own sales records
Suppliers	Tenant	Shared across all stores
Users	Tenant	Can access multiple stores based on permissions
Till Sessions	Store	Each store has its own cash management

The Hard Parts

1. Cross-Store Reporting

When the owner wants to see total sales across all stores:

# Must explicitly query across stores
total_sales = db.session.query(func.sum(Sale.total))\
    .filter(Sale.tenant_id == g.tenant_id)\
    .filter(Sale.date >= today)\
    .scalar()

We built a reporting layer that bypasses store filtering when needed.

2. Stock Transfers

Moving inventory between stores requires special handling:

class StockTransfer(TenantScopedModel):
    from_store_id = Column(UUID, ForeignKey('stores.id'))
    to_store_id = Column(UUID, ForeignKey('stores.id'))
    # Not StoreScopedModel - belongs to tenant, not a single store

3. User Access Control

A user might be:

• Owner: Access all stores
• Manager: Access specific stores
• Cashier: Access one store only

class UserStoreAccess(BaseModel):
    user_id = Column(UUID, ForeignKey('users.id'))
    store_id = Column(UUID, ForeignKey('stores.id'))
    role = Column(String)  # 'manager', 'cashier', etc.

4. Data Leakage Prevention

The biggest risk with shared schema is accidentally exposing one tenant's data to another. We mitigate this with:

1. Automatic filtering - ORM events inject tenant_id
2. API validation - Middleware verifies store ownership
3. Audit logging - Every data access is logged
4. Testing - Integration tests verify isolation

def test_tenant_isolation():
    # Create data for tenant A
    with tenant_context(tenant_a):
        product = Product.create(name="Secret Product")

    # Verify tenant B cannot see it
    with tenant_context(tenant_b):
        products = Product.query.all()
        assert product not in products

Simple Mode vs Enterprise Mode

The same multi-tenant architecture serves both segments:

Duka Mode (Simple)

• Single store
• Owner is the only user
• No approval workflows
• Quick stock adjustments

Enterprise Mode

• Multiple stores
• Role-based access control
• Approval workflows for stock changes
• Full audit trails

The difference is configuration, not code:

class TenantSettings(TenantScopedModel):
    simple_mode = Column(Boolean, default=True)
    require_stock_approval = Column(Boolean, default=False)
    enable_batch_tracking = Column(Boolean, default=False)

Performance Considerations

Indexing

Every tenant_id and store_id column needs an index:

__table_args__ = (
    Index('ix_products_tenant', 'tenant_id'),
    Index('ix_sales_store_date', 'store_id', 'sale_date'),
)

Query Patterns

Most queries are tenant-scoped, so the tenant_id index is heavily used. We monitor slow queries and add composite indexes as needed.

Connection Pooling

With shared database, connection pooling is critical:

SQLALCHEMY_ENGINE_OPTIONS = {
    'pool_size': 20,
    'pool_recycle': 300,
    'pool_pre_ping': True,
}

Lessons Learned

1. Start with tenant isolation from day one - Retrofitting is painful
2. Use automatic filtering - Don't trust developers to remember
3. Test isolation explicitly - Write tests that verify data boundaries
4. Plan for cross-tenant operations - Reporting, migrations, support access
5. Log everything - Audit trails save you during incidents

The Payoff

With this architecture, we can:

• Onboard a new tenant in seconds (just create tenant + store records)
• Run database migrations once for all tenants
• Share infrastructure costs across customers
• Support both single-store dukas and multi-store chains
• Scale horizontally when needed (move large tenants to dedicated resources)

Multi-tenancy done right is invisible to users but transformative for the business.

---

Building multi-tenant systems? Start with clear isolation boundaries and automatic enforcement. The complexity you add upfront saves debugging nightmares later.