🗄️ Tenant Databases

Isolated per-tenant data storage with user profiles, permissions, and settings.

🎯 Overview

Each tenant gets their own isolated database containing:

Model	Purpose
👤 `TenantUser`	Local user profiles linked to global identity
🔐 `TenantPermission`	Fine-grained resource permissions
⚙️ `TenantSettings`	Tenant-wide configuration

🏗️ Architecture

┌─────────────────────────────────────────────────────────────────┐
│                      🏠 HOST DATABASE                           │
│  ┌─────────────┐                                                │
│  │ TenantCatalog │ ──► Maps tenant_id → database URL           │
│  └──────┬──────┘                                                │
└─────────┼───────────────────────────────────────────────────────┘
          │
          │  get_or_create_tenant_db(tenant_id)
          ▼
┌─────────────────────────────────────────────────────────────────┐
│                    🗄️ TENANT DATABASE                           │
│                    (Isolated per tenant)                        │
├─────────────────────────────────────────────────────────────────┤
│  👤 TenantUser      → Local profile (links to GlobalUser.id)   │
│  🔐 TenantPermission → Resource-level access control           │
│  ⚙️ TenantSettings   → Timezone, currency, feature flags       │
│  🪝 WebhookEvent     → Idempotent webhook processing           │
│  🔑 WebhookSecret    → HMAC secrets per webhook source         │
├─────────────────────────────────────────────────────────────────┤
│  📊 [Your App Tables] → Transactions, budgets, etc.            │
└─────────────────────────────────────────────────────────────────┘

Key Principle: Tenant data is completely isolated → No cross-tenant data leakage possible

🔌 Tenant Connection

⚠️ Naming Convention: Use underscores (not hyphens) in tenant_id values.

Database names are derived from tenant_id (e.g., tenant_acme_001 → t_tenant_acme_001_db). PostgreSQL and SQLite identifiers work best with alphanumeric characters and underscores.

✅ Good: tenant_acme_001, tenant_finxplorer_prod ❌ Avoid: tenant-acme-001, tenant.finxplorer.prod

from nbdev.showdoc import show_doc

get_or_create_tenant_db() handles the full lifecycle:

🔍 Check if tenant exists in host database
🗄️ Create physical database if new (PostgreSQL)
📝 Register tenant in TenantCatalog
🔌 Return connection to tenant database

Parameter	Description
`tenant_id`	Unique tenant identifier (from `Membership`)
`tenant_name`	Optional display name (defaults to tenant_id)

Returns: Database connection to the tenant’s isolated database

source

get_or_create_tenant_db


def get_or_create_tenant_db(
    tenant_id:str, tenant_name:str=None
):

Get or create a tenant database handle by tenant ID.

The package caches tenant routing, SQLAlchemy engines, and reflected table metadata, but each call returns a fresh live connection bound to its own cloned metadata object. Callers should still close the returned connection when done.

📦 Core Tenant Models

These models provide the infrastructure every tenant needs. Your app-specific models (transactions, budgets, etc.) build on top of these.

source

TenantSettings


def TenantSettings(
    args:VAR_POSITIONAL, kwargs:VAR_KEYWORD
):

Tenant-wide configuration and feature flags.

source

TenantPermission


def TenantPermission(
    args:VAR_POSITIONAL, kwargs:VAR_KEYWORD
):

Fine-grained resource permission for a tenant user.

source

TenantUser


def TenantUser(
    args:VAR_POSITIONAL, kwargs:VAR_KEYWORD
):

Local user profile linked to GlobalUser in host database.

📝 Model Details

Model	Table Name	Primary Key	Description
`TenantUser`	`core_tenant_users`	`id`	Links to `GlobalUser.id`, stores local role & preferences
`TenantPermission`	`core_permissions`	`id`	Resource + action permissions (RBAC)
`TenantSettings`	`core_settings`	`id`	Timezone, currency, feature flags

🔧 Schema Initialization

init_tenant_core_schema() creates all infrastructure tables in a tenant database:

tenant_db = get_or_create_tenant_db("tenant_abc")
tables = init_tenant_core_schema(tenant_db)

# Access tables via returned dict
tables['tenant_users'].insert(user)
tables['settings'].insert(settings)

Returns: Dictionary of table accessors for all core models

source

init_tenant_core_schema


def init_tenant_core_schema(
    tenant_db:Database
):

Create all core tenant tables and return table accessors.

🚀 Quick Start

from fh_saas.db_tenant import get_or_create_tenant_db, init_tenant_core_schema, TenantUser
from fh_saas.db_host import gen_id, timestamp

# Get or create tenant database
tenant_db = get_or_create_tenant_db("tenant_abc123", "Acme Corp")

# Initialize core schema
tables = init_tenant_core_schema(tenant_db)

# Add a tenant user (linked to GlobalUser.id)
user = TenantUser(
    id="global_user_xyz",  # Must match GlobalUser.id
    display_name="John Doe",
    local_role="admin",
    created_at=timestamp()
)
tables['tenant_users'].insert(user)
tenant_db.conn.commit()

💡 Tip: The id field in TenantUser must match the GlobalUser.id from the host database to maintain identity linking.

🔐 Role-Based Access Control (RBAC)

Every tenant has a three-tier role system for controlling access:

Role Hierarchy

Role	Level	Description
`admin`	3	Full access to all resources and settings
`editor`	2	Can view and modify data, but not settings
`viewer`	1	Read-only access to data

Role Assignment Rules

Tenant owner (from host Membership.role='owner') → automatically gets admin role
Other users → assigned explicitly by admin via TenantUser.local_role
No defaults → admins must explicitly assign roles when inviting users

TenantUser.local_role

The local_role field in TenantUser stores the user’s role within the tenant:

# Example: Admin adds a new user as editor
new_user = TenantUser(
    id=global_user_id,      # Must match GlobalUser.id
    display_name="Jane Doe",
    local_role="editor",    # Assigned by admin
    created_at=timestamp()
)
tables['tenant_users'].insert(new_user)

Fine-Grained Permissions (Optional)

For advanced use cases, the core_permissions table allows resource-level control:

# Example: Grant user edit access to transactions only
permission = TenantPermission(
    id=gen_id(),
    user_id=tenant_user.id,
    resource="transactions",
    action="edit",
    granted=True,
    created_at=timestamp()
)
tables['permissions'].insert(permission)

Field	Description
`resource`	What the permission applies to (e.g., “transactions”, “budgets”)
`action`	What action is allowed (e.g., “view”, “edit”, “delete”)
`granted`	True = allowed, False = explicitly denied

💡 Tip: Most apps only need the local_role field. Use core_permissions when you need per-resource control (e.g., “User X can view transactions but not budgets”).