Design: Multi-Tenancy (Phase 0)

This document describes the design for transforming Aero2 from a single-tenant auth service into a multi-tenant platform. Every subsequent feature depends on this.

Overview

The multi-tenancy model introduces an applications table and adds app_id foreign keys to all tenant-scoped tables. A single Aero2 deployment serves many applications, each with its own user pool, identity providers, OAuth clients, branding, and settings.

Hierarchy

Platform (single Aero2 deployment)
│
├── Dashboard Application (built-in, slug: "dashboard")
│   ├── Developers (Users of the dashboard app)
│   │   └── Developer Teams (Organizations in the dashboard app)
│   │       └── owns Applications
│   │
│   └── Uses Aero2's own auth (dogfooding)
│
├── Application "TaskFlow" (slug: "swift-maple", auto-generated)
│   ├── App Users (end-users of TaskFlow)
│   ├── App Organizations (customer teams within TaskFlow)
│   ├── App IdPs, Branding, Settings
│   └── Served at swift-maple.aero2.dev
│
├── Application "ShopEasy" (slug: "brave-falcon", auto-generated)
│   ├── App Users (completely separate from TaskFlow)
│   ├── App Organizations
│   └── Served at brave-falcon.aero2.dev

Key Principles

• The Dashboard is itself an Application — developers are just Users of the dashboard app
• Developer Teams are Organizations within the dashboard app
• Each Application gets its own subdomain, user pool, IdPs, branding, and settings
• Users in Application A cannot see or access Users in Application B
• app_id column on all tenant-scoped tables enforces isolation

Schema: Applications Table

CREATE TABLE applications (
  id TEXT PRIMARY KEY,
  slug TEXT UNIQUE NOT NULL,          -- URL-safe identifier, used as subdomain
  name TEXT NOT NULL,                 -- Human-readable display name
  owner_org_id TEXT,                  -- Developer Team (org in dashboard app) that owns this
  logo_url TEXT,
  favicon_url TEXT,
  primary_color TEXT,                 -- Hex color for theming
  support_email TEXT,
  homepage_url TEXT,
  privacy_policy_url TEXT,
  terms_url TEXT,
  settings TEXT NOT NULL DEFAULT '{}', -- JSON: signup_mode, mfa_policy, session_ttl, etc.
  is_dashboard BOOLEAN NOT NULL DEFAULT 0,
  custom_domain TEXT UNIQUE,
  custom_domain_verified BOOLEAN NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT (datetime('now')),
  updated_at DATETIME DEFAULT (datetime('now'))
);

Schema: API Keys

CREATE TABLE application_api_keys (
  id TEXT PRIMARY KEY,
  app_id TEXT NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
  key_type TEXT NOT NULL CHECK(key_type IN ('publishable', 'secret')),
  key_prefix TEXT NOT NULL,           -- First 8 chars for identification (e.g., "pk_live_")
  key_hash TEXT NOT NULL,             -- HMAC hash of the full key
  name TEXT,                          -- Optional label ("Production", "Staging")
  last_used_at DATETIME,
  expires_at DATETIME,                -- NULL = never expires
  created_at DATETIME DEFAULT (datetime('now'))
);
 
CREATE INDEX idx_api_keys_app ON application_api_keys(app_id);
CREATE INDEX idx_api_keys_prefix ON application_api_keys(key_prefix);

Tables Requiring app_id

All tenant-scoped tables get an app_id TEXT NOT NULL REFERENCES applications(id) column:

Composite Unique Constraints

UNIQUE(app_id, email)       -- on users (same email can exist in different apps)
UNIQUE(app_id, name)        -- on roles
UNIQUE(app_id, client_id)   -- on oauth_clients
UNIQUE(app_id, name)        -- on identity_providers

Migration Strategy

1. Create the dashboard application row first
2. Update all existing rows to set app_id = dashboard app ID
3. Add NOT NULL constraint after backfill

Bootstrap Process

Status: Implemented in src/backend/bootstrap.ts. All operations use INSERT OR IGNORE for idempotency.

On first request:

1. Create dashboard application with slug = 'dashboard', is_dashboard = true
2. Seed default roles for dashboard app (currently admin/user, to be updated to operator/developer/member — see Dashboard Role Model below)
3. Seed default permissions and role-permission mappings
4. Generate initial API keys (publishable pk_live_* + secret sk_live_*) if none exist
5. Platform operator is created on first OAuth login when email matches BOOTSTRAP_ADMIN_EMAIL

The bootstrap uses the DB layer methods (db.applications.ensureExists(), db.rbac.ensureRole(), db.rbac.ensurePermission(), db.rbac.ensureRolePermission()) rather than direct SQL to maintain the convention that all database access goes through src/backend/db/.

Subdomain Routing

See Subdomain Routing for full details on hostname-based request routing.

Application CRUD API

Dashboard-only endpoints for managing applications:

Slug Validation

• Lowercase alphanumeric + hyphens, 3-63 characters
• Reserved slugs rejected: dashboard, api, www, admin, auth, login, app, static, assets, health

API Key Management

Per-App Settings

Stored as JSON in applications.settings:

{
  "signup_mode": "open",           // open | invite_only | restricted
  "mfa_policy": "optional",       // off | optional | required
  "session_ttl": 86400,           // seconds
  "allowed_email_domains": [],    // empty = all allowed
  "blocked_email_domains": [],
  "auth_methods": ["oauth"]       // password | oauth | magic_link | passkey
}

Custom Domains

Uses Cloudflare for SaaS (Custom Hostnames):

1. Developer adds custom domain in dashboard
2. Platform calls Cloudflare API to create Custom Hostname (auto-provisions SSL)
3. Developer adds CNAME: auth.myapp.com → swift-maple.aero2.dev
4. Traffic flows through to the same Worker

Dashboard Role Model

The Dashboard requires its own role model, separate from the per-application RBAC. Authorization uses two layers: RBAC (what kind of action) + ownership (which resources).

Roles

Dashboard Permissions

These are platform-level permissions, separate from per-app permissions:

• platform:manage, developers:read, developers:write, apps:read:all, apps:write:all

• apps:create, apps:read, apps:write, apps:delete, apps:transfer, teams:read, teams:write

• profile:read, profile:write

Per-App Permission Template

The current 11 permissions (clients:read, clients:write, users:read, users:write, idps:read, idps:write, idps:delete, users:delete, clients:delete, roles:read, roles:write) are preserved as the default permission template that gets seeded when creating a new application. Each application can customize its own roles and permissions independently.

RBAC scoping rule: All RBAC joins must include app_id across user_roles, roles, role_permissions, and permissions. Role and permission IDs are not globally unique, so missing app_id filters can leak access across applications.

Sign-up and Role Assignment

When a developer signs up (first OAuth login on the Dashboard):

• rp.ts assignDefaultRole() checks the user's verified email
• If email matches BOOTSTRAP_ADMIN_EMAIL → assign operator role
• Otherwise → assign developer role

Per-App Management Authorization

When a developer accesses /api/applications/:slug/* endpoints:

1. RBAC check: Does the developer have apps:read or apps:write?
2. Ownership check: Does the developer's team own the target application?
3. The operator role bypasses the ownership check

Dashboard vs App Context: Dashboard requests are authenticated against the dashboard app but may operate on a target application if RBAC + ownership checks pass. This is the only supported cross-app access path.

Token and Session Scoping

• Sessions and refresh tokens are stored with app_id and must be validated against the current request's app_id.
• Tokens should carry app context via iss (per-app issuer) and/or an app_id claim so verifiers can reject cross-app tokens.

Implementation Checklist

• Migration 0002_multi_tenancy.sql (written, to be applied separately)
• Bootstrap process (src/backend/bootstrap.ts)
• Tenant middleware (src/backend/middleware/tenant.ts)
• DB layer updated with app_id parameters
• PLATFORM_DOMAIN env var added to wrangler configs
• Application and API key DB query modules
• Dashboard role model redesign (operator/developer/member)
• Application CRUD API (src/backend/applications.ts)
• API key management endpoints
• Custom domain management
• Update wrangler.json for wildcard routes
• Refactor all existing handlers to use c.get("appId") instead of DASHBOARD_APP_ID
• Per-app OIDC issuer
• Per-app CORS and cookies
• Two-layer authorization (RBAC + ownership)
• Dashboard frontend
• Integration tests for cross-app isolation