> ## Documentation Index
> Fetch the complete documentation index at: https://docs.snackbase.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Multi-Tenancy Model

> Account isolation, two-tier architecture, and tenant data management

SnackBase uses a **shared database, row-level isolation** multi-tenancy model. This guide explains how accounts work, how data is isolated, and what you need to know when building multi-tenant applications.

## Overview

SnackBase enables **Software-as-a-Service (SaaS)** applications by allowing multiple independent tenants (accounts) to coexist in a single database while maintaining complete data isolation.

### Key Characteristics

| Characteristic           | Description                                                   |
| ------------------------ | ------------------------------------------------------------- |
| **Isolation Type**       | Row-level isolation via `account_id` column                   |
| **Database Model**       | Shared database, shared tables                                |
| **Account Scope**        | All data (users, collections, records) scoped to `account_id` |
| **Cross-Account Access** | Not possible by design (enforced at database and API levels)  |

## Account Model

### What is an Account?

An **Account** (also called a "tenant" or "organization") represents an isolated workspace containing:

* Users who belong to the account
* Collections (data schemas) defined for the account
* Records (actual data) created by the account's users
* Roles and permissions specific to the account
* Groups for organizing users
* Configuration overrides for providers (auth, email, storage)

### Account Hierarchy

```
SnackBase Instance
│
├── System Account (SY0000)
│   ├── Superadmin users
│   └── Manages all accounts
│
├── Account AB1001 (Acme Corp)
│   ├── Users: alice@acme.com, bob@acme.com
│   ├── Collections: posts, products, orders
│   ├── Roles: admin, editor, viewer
│   └── Records: (all scoped to account_id = "550e8400-...")
│
├── Account XY2048 (Globex Inc)
│   ├── Users: jane@globex.com
│   ├── Collections: customers, tickets
│   ├── Roles: support, manager
│   └── Records: (all scoped to account_id = "aabbccdd-...")
│
└── Account ZZ9999 (StartUp Co)
    └── ... (completely isolated)
```

## Data Isolation

### How Isolation Works

Most tables in SnackBase include an `account_id` column that references the `accounts` table:

```sql theme={null}
-- Example: users table
┌─────────────┬──────────────────┬─────────────────────┐
│ id          │ email            │ account_id          │
├─────────────┼──────────────────┼─────────────────────┤
│ user_abc123 │ alice@acme.com   │ 550e8400-e29b-...   │
│ user_def456 │ bob@acme.com     │ 550e8400-e29b-...   │
│ user_ghi789 │ jane@globex.com  │ aabbccdd-1234-...   │
└─────────────┴──────────────────┴─────────────────────┘

-- Example: Dynamic collection table (col_posts)
┌─────────────┬─────────────────────┬─────────────┬─────────────────────┐
│ id          │ title               │ content     │ account_id          │
├─────────────┼─────────────────────┼─────────────┼─────────────────────┤
│ post_001    │ Hello World         │ Welcome...  │ 550e8400-e29b-...   │
│ post_002    │ Acme News           │ Latest...   │ 550e8400-e29b-...   │
│ post_003    │ Globex Update       │ News...     │ aabbccdd-1234-...   │
└─────────────┴─────────────────────┴─────────────┴─────────────────────┘
```

### Tables WITHOUT account\_id (Global Tables)

The following tables **do not have** an `account_id` column because they define global structures shared by all accounts:

| Table         | Why No account\_id?                                          |
| ------------- | ------------------------------------------------------------ |
| `accounts`    | Defines accounts themselves (cannot be scoped to an account) |
| `roles`       | Roles are global definitions shared by all accounts          |
| `permissions` | Permissions are global rules shared by all accounts          |
| `collections` | Collection schemas are global definitions (data is isolated) |
| `macros`      | Macros are global SQL snippets shared by all accounts        |
| `migrations`  | Migrations are global and affect all accounts                |

### Automatic Filtering

SnackBase **automatically filters** all queries by `account_id`. Users never see data from other accounts.

**Example API Request:**

```bash theme={null}
# User from AB1001 requests all posts
GET /api/v1/posts

# SQL executed (simplified):
SELECT * FROM col_posts WHERE account_id = '550e8400-e29b-41d4-a716-446655440000'
```

The user doesn't need to specify `account_id`—it's automatically added based on their authentication context.

### Enforcement Layers

Isolation is enforced at **multiple layers** for defense-in-depth:

| Layer                 | Mechanism                                          | Details                                    |
| --------------------- | -------------------------------------------------- | ------------------------------------------ |
| **Database**          | `account_id` column with foreign key to accounts   | Row-level filtering at SQL level           |
| **Hook**              | `account_isolation_hook` (priority -200)           | Automatically injects `account_id` filters |
| **Repository**        | All repositories enforce `account_id` in queries   | Cannot bypass without explicit override    |
| **API Middleware**    | Authorization middleware validates account context | Checks permissions before execution        |
| **Superadmin Bypass** | Superadmin can pass `account_id=None`              | Allows cross-account visibility for admins |

## Two-Tier Architecture

SnackBase uses a **two-tier table architecture** that's critical to understand:

### Tier 1: Core System Tables

These tables define the platform structure and are shared across all accounts:

| Table         | Purpose                       | Has account\_id?      | Schema Changes |
| ------------- | ----------------------------- | --------------------- | -------------- |
| `accounts`    | Account/tenant definitions    | No (defines accounts) | Releases only  |
| `users`       | User identities (per-account) | Yes                   | Releases only  |
| `roles`       | Role definitions              | No (global)           | Releases only  |
| `permissions` | Permission rules              | No (global)           | Releases only  |
| `collections` | Collection schema definitions | No (global)           | Releases only  |
| `macros`      | SQL macro definitions         | No (global)           | Releases only  |
| `migrations`  | Database migration history    | No (global)           | Automatic      |

**Important**: Schema changes to these tables only happen via SnackBase releases.

### Tier 2: User-Created Collections

User collections are **single physical tables** shared by ALL accounts:

| Physical Table | Collection Name | Contains                   |
| -------------- | --------------- | -------------------------- |
| `col_posts`    | "posts"         | All accounts' post data    |
| `col_products` | "products"      | All accounts' product data |
| `col_orders`   | "orders"        | All accounts' order data   |

**Critical Concept**: When you create a collection named "posts", you're creating:

1. A **schema definition** in the `collections` table (metadata)
2. A **physical table** named `col_posts` (if it doesn't exist)
3. All accounts' post data goes into this **single shared table**

### Physical Table Naming Convention

Collection tables are **prefixed with `col_`** to avoid conflicts with system tables:

| Collection Name | Physical Table Name | Example Query                                          |
| --------------- | ------------------- | ------------------------------------------------------ |
| `posts`         | `col_posts`         | `SELECT * FROM col_posts WHERE account_id = ?`         |
| `products`      | `col_products`      | `SELECT * FROM col_products WHERE account_id = ?`      |
| `user_profiles` | `col_user_profiles` | `SELECT * FROM col_user_profiles WHERE account_id = ?` |

This prefix:

* Prevents naming conflicts with system tables
* Makes it clear which tables are user-created collections
* Allows easy identification of collection tables in database dumps

### Why This Architecture?

| Approach               | Description                                                               | SnackBase Choice                            |
| ---------------------- | ------------------------------------------------------------------------- | ------------------------------------------- |
| **Separate Tables**    | Each account gets their own `col_posts_AB1001`, `col_posts_XY2048` tables | ❌ Not scalable (thousands of tables)        |
| **Separate Databases** | Each account gets their own database                                      | ❌ Complex operations and migrations         |
| **Shared Tables**      | All accounts share one `col_posts` table with `account_id`                | ✅ **Chosen for scalability and simplicity** |

## Account Identifiers

Accounts have **three distinct identifiers** that serve different purposes:

### Identifier Comparison

| Field          | Format           | Purpose                             | Example                                | Uniqueness      |
| -------------- | ---------------- | ----------------------------------- | -------------------------------------- | --------------- |
| `id`           | UUID (36 chars)  | Primary key, foreign key references | `550e8400-e29b-41d4-a716-446655440000` | Globally unique |
| `account_code` | XX#### (6 chars) | Human-readable identifier           | `AB1234`                               | Globally unique |
| `slug`         | URL-friendly     | Login and URL routing               | `acme-corp`                            | Globally unique |
| `name`         | Free text        | Display name only                   | `Acme Corporation`                     | Not unique      |

### Account ID (UUID)

The **internal primary key** for accounts is a standard UUID:

```
Format: 8-4-4-4-12 hexadecimal characters
Example: 550e8400-e29b-41d4-a716-446655440000
```

* **Purpose**: Primary key, used in foreign key references
* **Format**: Standard UUID v4 (36 characters)
* **Used by**: `account_id` columns in all tenant-scoped tables
* **Human-readable**: No (designed for systems, not humans)

### Account Code (XX####)

The **human-readable identifier** for accounts:

```
XX#### = 2 letters + 4 digits

Examples:
├── SY0000  (System account - reserved)
├── AB1001  (Acme Corp)
├── XY2048  (Globex Inc)
└── ZZ9999  (StartUp Co)
```

* **Letters (XX)**: Random uppercase letters A-Z
* **Digits (####)**: Sequential number starting from 0001
* **Total Capacity**: 6,760,000 unique codes (26×26×10,000)
* **Reserved Range**: SY#### (skipped during generation)

### Account Code Generation

Account codes are generated **sequentially** from the highest existing code:

```python theme={null}
# Generation logic
1. Find highest existing account code (e.g., AB2345)
2. Increment numeric portion (AB2346)
3. Skip SY#### range (reserved for system)
4. Assign to new account
```

**Important Notes**:

* Codes are never reused
* Sequential generation ensures predictability
* SY#### range is permanently reserved
* System account uses SY0000

### Identifier Usage

| Identifier        | Used In...                         | Example                                                |
| ----------------- | ---------------------------------- | ------------------------------------------------------ |
| **id** (UUID)     | Foreign keys, `account_id` columns | `WHERE account_id = '550e8400-...'`                    |
| **account\_code** | Admin UI, support, logs            | "Account AB1234"                                       |
| **slug**          | Login URLs, subdomain routing      | `ab1234.snackbase.dev` or `/api/v1/accounts/acme-corp` |
| **name**          | UI display, emails                 | "Welcome to Acme Corporation"                          |

## System Account vs User Accounts

### System Account (SY0000)

The **system account** is a special reserved account for superadmin operations:

| Attribute        | Value                                                      |
| ---------------- | ---------------------------------------------------------- |
| **ID**           | `00000000-0000-0000-0000-000000000000` (nil UUID)          |
| **Account Code** | `SY0000` (fixed)                                           |
| **Name**         | "System"                                                   |
| **Purpose**      | Superadmin operations, system-level configuration          |
| **Access**       | Superadmin users can operate across ALL accounts           |
| **Data**         | Contains minimal data (mostly metadata and system configs) |

**Superadmin users** are linked to the system account and have:

* Access to ALL accounts
* Ability to create/manage accounts
* Ability to manage global collections
* System-wide visibility (can pass `account_id=None` to see all data)

### User Accounts

**User accounts** are regular tenant accounts created by superadmins:

| Attribute        | Value                                                              |
| ---------------- | ------------------------------------------------------------------ |
| **ID**           | Auto-generated UUID (e.g., `550e8400-e29b-41d4-a716-446655440000`) |
| **Account Code** | Auto-generated (e.g., `AB1001`)                                    |
| **Name**         | User-defined (e.g., "Acme Corporation")                            |
| **Purpose**      | Regular tenant operations                                          |
| **Access**       | Users can only access THEIR account                                |
| **Data**         | Contains all tenant data (users, collections, records)             |

**Regular users** (even with "admin" role) are linked to a specific account and have:

* Access ONLY to their account
* No cross-account visibility
* Full CRUD within their account (based on permissions)

## Multi-Account Users

### Enterprise Multi-Account Model

SnackBase supports **enterprise multi-account scenarios** where a single user can belong to multiple accounts with different roles and permissions.

### User Identity

A user's identity is defined by the **(email, account\_id) tuple**:

```
┌────────────────────┬─────────────────────┬──────────────┐
│ email              │ account_id          │ role         │
├────────────────────┼─────────────────────┼──────────────┤
│ alice@acme.com     │ 550e8400-e29b-...   │ admin        │
│ alice@acme.com     │ aabbccdd-1234-...   │ viewer       │
│ bob@acme.com       │ 550e8400-e29b-...   │ editor       │
│ jane@globex.com    │ aabbccdd-1234-...   │ admin        │
└────────────────────┴─────────────────────┴──────────────┘
```

**Key Point**: The same email (`alice@acme.com`) can exist in multiple accounts with different roles.

### Password Scope

**Passwords are per-account**, not per-email.

This means:

* `alice@acme.com` in account `AB1001` has password `Password1!`
* `alice@acme.com` in account `XY2048` has password `Password2!`
* These are **different credentials** even though the email is the same

### Login Flow

When logging in, users must specify their account:

**Option 1: Account in URL**

```
POST /api/v1/auth/login
Host: ab1001.snackbase.dev  # Account in subdomain
{
  "email": "alice@acme.com",
  "password": "Password1!"
}
```

**Option 2: Account in Request Body**

```
POST /api/v1/auth/login
{
  "account": "acme-corp",  # Account slug
  "email": "alice@acme.com",
  "password": "Password1!"
}
```

## Configuration Hierarchy

SnackBase uses a **hierarchical configuration model** for provider settings (authentication, email, storage, etc.):

### Two-Level Hierarchy

```
System-Level Configuration
├── account_id: 00000000-0000-0000-0000-000000000000 (nil UUID)
├── Purpose: Default configs for all accounts
└── Applied when: No account-level override exists

Account-Level Configuration
├── account_id: <specific account UUID>
├── Purpose: Per-account custom settings
└── Priority: Always overrides system defaults
```

### Configuration Resolution

When resolving a provider configuration:

1. **Check account-level** config for the specific account
2. **If not found**, use system-level default
3. **Merge** with fallback values for any missing keys

```python theme={null}
# Example: Email provider resolution
config = config_registry.get_config(
    account_id="550e8400-e29b-41d4-a716-446655440000",
    provider_name="email"
)

# Returns:
# - Account-specific config if exists
# - System-level config if no account override
# - Cached for 5 minutes
```

### Use Cases

| Configuration Type   | System-Level        | Account-Level           |
| -------------------- | ------------------- | ----------------------- |
| **SMTP Settings**    | Default SMTP server | Custom SMTP per account |
| **OAuth Providers**  | Available to all    | Custom app credentials  |
| **Storage Backends** | Default S3 bucket   | Per-account buckets     |
| **Auth Providers**   | Default providers   | Custom provider config  |

### Key Points

* System-level configs use the **nil UUID** (`00000000-0000-0000-0000-000000000000`)
* Account-level configs use the **account's UUID** as `account_id`
* Resolution is cached for **5 minutes** for performance
* Built-in providers are marked with `is_builtin` flag (cannot be deleted)

## Implications for Developers

### When Building Applications

Understanding multi-tenancy is critical when building on SnackBase:

### 1. Never Store Account ID Manually

```python theme={null}
# ❌ DON'T: Manual account_id
def create_post(title: str, account_id: str):
    post = Post(title=title, account_id=account_id)
    # Error-prone, security risk

# ✅ DO: Let the framework handle it
def create_post(title: str, context: Context):
    post = Post(title=title, account_id=context.account_id)
    # Automatic, secure
```

### 2. Account Isolation is Automatic

You don't need to write WHERE clauses for account filtering:

```python theme={null}
# ❌ DON'T: Manual filtering
def get_posts(account_id: str):
    return db.query(Post).filter(Post.account_id == account_id).all()

# ✅ DO: Use the repository
def get_posts(context: Context):
    return posts_repo.find_all(context)  # Automatically filters by account_id
```

### 3. Cross-Account Queries Are Impossible

By design, you cannot query across accounts:

```python theme={null}
# ❌ This will NEVER return results
def get_all_posts_from_all_accounts():
    return db.query(Post).all()  # Only returns current account's posts
```

**Superadmin Exception**: Superadmins can explicitly pass `account_id=None` to bypass filtering:

```python theme={null}
# ✅ Superadmin-only cross-account query
def get_all_posts_as_superadmin():
    return posts_repo.find_all(context, account_id=None)  # Returns ALL posts
```

### 4. Collections Are Global

When creating a collection, remember:

* The collection schema is shared across ALL accounts
* The physical table (`col_<name>`) is shared across ALL accounts
* Each account only sees their own data (via `account_id` filtering)

```python theme={null}
# Creating "posts" collection creates ONE global table
collections_service.create("posts", fields=[...])
# Result: col_posts table created (if not exists)
#         All accounts can use "posts", but see only their data
```

### 5. Migrations Affect All Accounts

Database migrations affect ALL accounts simultaneously:

```python theme={null}
# ⚠️ CAUTION: This affects ALL accounts
alembic revision --autogenerate -m "Add index to col_posts"
# Result: ALL accounts' posts data is affected
```

Always test migrations thoroughly before deploying!

### 6. Use Account Code for Display

When displaying account identifiers in UI or logs:

```python theme={null}
# ✅ DO: Use account_code for display
account_code = account.account_code  # "AB1234"
print(f"Processing account {account_code}")

# ❌ DON'T: Use UUID for display
account_id = account.id  # "550e8400-e29b-41d4-a716-446655440000"
print(f"Processing account {account_id}")  # Hard to read!
```

## Summary

| Concept                     | Key Takeaway                                                                             |
| --------------------------- | ---------------------------------------------------------------------------------------- |
| **Account Model**           | Accounts are isolated tenants with their own users, collections, and data                |
| **Account Identifiers**     | UUID (id) for system, account\_code (XX####) for humans, slug for URLs, name for display |
| **Data Isolation**          | Row-level isolation via `account_id` column, enforced at multiple layers                 |
| **Global Tables**           | accounts, roles, permissions, collections, macros, migrations have no account\_id        |
| **Two-Tier Architecture**   | Core system tables (release-only schema) + user collections (shared col\_\* tables)      |
| **System Account**          | Uses nil UUID for ID, SY0000 for account\_code, reserved for superadmin operations       |
| **Multi-Account Users**     | Same email can exist in multiple accounts with different passwords                       |
| **Configuration Hierarchy** | System-level (nil UUID) defaults + account-level overrides                               |
| **Developer Implications**  | Never handle `account_id` manually; isolation is automatic; collections are global       |