Database Schema

Aero2 uses Cloudflare D1 (SQLite at the edge) for all persistent data. The schema is defined in migrations/0001_schema.sql as a single fresh-start migration.

Entity Model

The schema is organized into four groups: core platform, authentication & OAuth, RBAC, and email verification.

Every tenant-scoped table includes an app_id column referencing applications(id) with ON DELETE CASCADE. See Multi-Tenancy Design for isolation guarantees.

Entity Relationship Diagram

applications (platform root)
├── application_api_keys      (API access per app)
├── users                     (one user pool per app)
│   ├── user_identity_links   (links to external IdPs)
│   ├── user_sessions         (active sessions)
│   ├── user_roles            (RBAC assignments)
│   ├── authorization_codes   (OAuth auth codes)
│   └── refresh_tokens        (OAuth refresh tokens)
├── identity_providers        (external OAuth/OIDC configs)
├── oauth_clients             (registered OAuth clients)
├── oauth_state               (CSRF state for OAuth flows)
├── audit_logs                (security event log)
├── roles                     (RBAC role definitions)
│   └── role_permissions      (role → permission mappings)
├── permissions               (granular permission definitions)
└── email_verification_codes  (email code / magic link / password reset)

Core Tables

applications

Central multi-tenancy table. Each row represents one application served on its own subdomain.

Column	Type	Description
`id`	TEXT PK	UUID identifier
`slug`	TEXT UNIQUE NOT NULL	Auto-generated subdomain slug (e.g., `swift-maple` → `swift-maple.aero2.dev`)
`name`	TEXT NOT NULL	Display name
`owner_org_id`	TEXT	Owning developer team (organization in dashboard app)
`logo_url`	TEXT	Branding: logo URL
`favicon_url`	TEXT	Branding: favicon URL
`primary_color`	TEXT	Branding: primary color hex
`support_email`	TEXT	Support contact email
`homepage_url`	TEXT	Application homepage
`privacy_policy_url`	TEXT	Privacy policy link
`terms_url`	TEXT	Terms of service link
`settings`	TEXT NOT NULL DEFAULT ''	JSON: signup_mode, mfa_policy, session_ttl, auth_methods, etc.
`is_dashboard`	INTEGER NOT NULL DEFAULT 0	Whether this is the built-in dashboard app
`custom_domain`	TEXT UNIQUE	Custom domain (e.g., `auth.myapp.com`)
`custom_domain_verified`	INTEGER NOT NULL DEFAULT 0	Domain verification status
`cf_custom_hostname_id`	TEXT	Cloudflare Custom Hostname ID
`created_at`	DATETIME	Creation timestamp
`updated_at`	DATETIME	Last update timestamp

application_api_keys

API keys for programmatic access. Each key is stored as a prefix + HMAC hash.

Column	Type	Description
`id`	TEXT PK	UUID identifier
`app_id`	TEXT NOT NULL FK	Owning application
`key_type`	TEXT NOT NULL	`publishable` or `secret`
`key_prefix`	TEXT NOT NULL	First 12 chars for identification (e.g., `pk_live_a1b2`)
`key_hash`	TEXT NOT NULL	HMAC-SHA256 hash of full key
`name`	TEXT	Human-readable key name
`last_used_at`	DATETIME	Last API call timestamp
`expires_at`	DATETIME	Expiration (NULL = never expires)
`created_at`	DATETIME	Creation timestamp

users

Core user table. Each user belongs to exactly one application.

Column	Type	Description
`id`	TEXT PK	UUID identifier
`app_id`	TEXT NOT NULL FK	Owning application
`email`	TEXT NOT NULL	User email address
`email_verified`	INTEGER NOT NULL DEFAULT 0	Email verification status
`name`	TEXT	Display name
`given_name`	TEXT	First name
`family_name`	TEXT	Last name
`picture`	TEXT	Profile picture URL
`disabled`	INTEGER NOT NULL DEFAULT 0	Account disabled flag
`created_at`	DATETIME	Creation timestamp
`updated_at`	DATETIME	Last update timestamp

Unique constraint: UNIQUE(app_id, email) — same email can exist in different apps.

audit_logs

Immutable security event log for all authentication and administrative actions.

Column	Type	Description
`id`	TEXT PK	UUID identifier
`app_id`	TEXT NOT NULL FK	Application context
`user_id`	TEXT FK(users) ON DELETE SET NULL	Acting user (NULL for system/failed events)
`event_type`	TEXT NOT NULL	Event type (`login`, `logout`, `login_failed`, etc.)
`event_data`	TEXT	JSON with event-specific details
`ip_address`	TEXT	Client IP address
`user_agent`	TEXT	Client user agent
`created_at`	DATETIME	Event timestamp

Authentication & OAuth Tables

identity_providers

Configuration for external OAuth2/OIDC identity providers (GitHub, Google, etc.).

Column	Type	Description
`id`	TEXT PK	UUID identifier
`app_id`	TEXT NOT NULL FK	Owning application
`name`	TEXT NOT NULL	Provider name (e.g., `github`, `google`)
`type`	TEXT NOT NULL	`oauth2` or `oidc`
`authorization_endpoint`	TEXT NOT NULL	OAuth authorize URL
`token_endpoint`	TEXT NOT NULL	OAuth token URL
`userinfo_endpoint`	TEXT	UserInfo URL (optional for OIDC)
`jwks_uri`	TEXT	JWKS URL for OIDC token verification
`client_id`	TEXT NOT NULL	OAuth client ID
`client_secret`	TEXT NOT NULL	OAuth client secret (AES-256-GCM encrypted)
`scopes`	TEXT NOT NULL	JSON array of scopes
`enabled`	INTEGER NOT NULL DEFAULT 1	Whether provider is active
`created_at`	DATETIME	Creation timestamp
`updated_at`	DATETIME	Last update timestamp

Unique constraint: UNIQUE(app_id, name)

user_identity_links

Links users to external identity provider accounts. A user can be linked to multiple IdPs.

Column	Type	Description
`id`	TEXT PK	UUID identifier
`app_id`	TEXT NOT NULL FK	Application (explicit for query safety)
`user_id`	TEXT NOT NULL FK(users)	The local user
`idp_id`	TEXT NOT NULL FK(identity_providers)	The identity provider
`idp_user_id`	TEXT NOT NULL	User ID at the IdP
`idp_username`	TEXT	Username at the IdP
`access_token`	TEXT	Encrypted access token from IdP
`refresh_token`	TEXT	Encrypted refresh token from IdP
`created_at`	DATETIME	Creation timestamp

Unique constraint: UNIQUE(user_id, idp_id, idp_user_id)

oauth_clients

OAuth clients registered for the OIDC Provider functionality.

Column	Type	Description
`id`	TEXT PK	Internal UUID identifier
`app_id`	TEXT NOT NULL FK	Owning application
`client_id`	TEXT NOT NULL	Public OAuth client identifier (`client_` prefix)
`client_secret`	TEXT NOT NULL	PBKDF2-hashed secret
`name`	TEXT NOT NULL	Client display name
`redirect_uris`	TEXT NOT NULL	JSON array of allowed redirect URIs
`scopes`	TEXT NOT NULL	JSON array of allowed scopes
`grant_types`	TEXT NOT NULL	JSON array of allowed grant types
`enabled`	INTEGER NOT NULL DEFAULT 1	Whether client is active
`created_at`	DATETIME	Creation timestamp
`updated_at`	DATETIME	Last update timestamp

Unique constraint: UNIQUE(app_id, client_id)

authorization_codes

Short-lived authorization codes for the Authorization Code Flow (10-minute TTL).

Column	Type	Description
`code`	TEXT PK	The authorization code
`app_id`	TEXT NOT NULL FK	Application context
`client_id`	TEXT NOT NULL FK(oauth_clients.client_id)	Requesting client
`user_id`	TEXT NOT NULL FK(users)	Authenticated user
`redirect_uri`	TEXT NOT NULL	Redirect URI for this request
`scopes`	TEXT NOT NULL	JSON array of granted scopes
`code_challenge`	TEXT	PKCE code challenge
`code_challenge_method`	TEXT	`S256` or `plain`
`nonce`	TEXT	OIDC nonce for ID token
`expires_at`	DATETIME NOT NULL	Code expiration
`created_at`	DATETIME	Creation timestamp

refresh_tokens

Long-lived refresh tokens for token rotation.

Column	Type	Description
`token`	TEXT PK	HMAC-hashed token value
`app_id`	TEXT NOT NULL FK	Application context
`client_id`	TEXT NOT NULL FK(oauth_clients.client_id)	Owning client
`user_id`	TEXT NOT NULL FK(users)	Owning user
`scopes`	TEXT NOT NULL	JSON array of granted scopes
`expires_at`	DATETIME NOT NULL	Token expiration
`created_at`	DATETIME	Creation timestamp

user_sessions

Tracks active user sessions for management and revocation.

Column	Type	Description
`id`	TEXT PK	Session identifier
`app_id`	TEXT NOT NULL FK	Application context
`user_id`	TEXT NOT NULL FK(users)	Session owner
`token_hash`	TEXT NOT NULL	HMAC hash of session cookie
`idp_name`	TEXT	Which IdP authenticated this session
`ip_address`	TEXT	Client IP at session creation
`user_agent`	TEXT	Client user agent string
`created_at`	DATETIME	Session creation time
`expires_at`	DATETIME NOT NULL	Session expiration
`last_active_at`	DATETIME	Last activity timestamp
`revoked_at`	DATETIME	Revocation timestamp (NULL if active)

oauth_state

CSRF protection state for OAuth flows. Entries are consumed atomically on callback.

Column	Type	Description
`state`	TEXT PK	Random state value
`app_id`	TEXT NOT NULL FK	Application context
`idp_name`	TEXT NOT NULL	Target IdP name
`redirect_uri`	TEXT NOT NULL	Post-auth redirect URI
`created_at`	DATETIME	Creation timestamp
`expires_at`	DATETIME NOT NULL	State expiration (10 minutes)

RBAC Tables

roles

Role definitions. Each application has its own independent set of roles.

Column	Type	Description
`id`	TEXT PK	Role identifier (human-readable, e.g., `operator`)
`app_id`	TEXT NOT NULL FK	Owning application
`name`	TEXT NOT NULL	Role name
`description`	TEXT	Human-readable description
`created_at`	DATETIME	Creation timestamp
`updated_at`	DATETIME	Last update timestamp

Unique constraint: UNIQUE(app_id, name)

permissions

Granular permission definitions following a resource:action naming convention.

Column	Type	Description
`id`	TEXT PK	Permission identifier (e.g., `apps:create`)
`app_id`	TEXT NOT NULL FK	Owning application
`name`	TEXT NOT NULL	Permission name (same as id by convention)
`resource`	TEXT NOT NULL	Resource name (e.g., `apps`, `users`, `clients`)
`action`	TEXT NOT NULL	Action name (e.g., `read`, `write`, `delete`)
`description`	TEXT	Human-readable description
`created_at`	DATETIME	Creation timestamp

Unique constraint: UNIQUE(app_id, name)

role_permissions

Many-to-many mapping between roles and permissions.

Column	Type	Description
`app_id`	TEXT NOT NULL FK	Application (explicit for query safety)
`role_id`	TEXT NOT NULL FK(roles)	Role
`permission_id`	TEXT NOT NULL FK(permissions)	Permission
`created_at`	DATETIME	Assignment timestamp

Primary key: (role_id, permission_id)

user_roles

Many-to-many mapping between users and roles.

Column	Type	Description
`app_id`	TEXT NOT NULL FK	Application (explicit for query safety)
`user_id`	TEXT NOT NULL FK(users)	User
`role_id`	TEXT NOT NULL FK(roles)	Role
`granted_by`	TEXT	Who granted this role (`bootstrap`, `system`, or user ID)
`granted_at`	DATETIME	Assignment timestamp

Primary key: (user_id, role_id)

Email Verification

email_verification_codes

Supports email code login, magic links, and password reset flows.

Column	Type	Description
`id`	TEXT PK	UUID identifier
`app_id`	TEXT NOT NULL FK	Application context
`email`	TEXT NOT NULL	Target email address
`code_hash`	TEXT NOT NULL	HMAC hash of the verification code
`type`	TEXT NOT NULL	`email_code`, `magic_link`, or `password_reset`
`expires_at`	DATETIME NOT NULL	Code expiration
`used_at`	DATETIME	Consumption timestamp (NULL if unused)
`attempts`	INTEGER NOT NULL DEFAULT 0	Failed verification attempts (rate limiting)
`created_at`	DATETIME	Creation timestamp

Indexes

Indexes are designed around actual query patterns. UNIQUE constraints create implicit indexes and are not listed separately.

Hot-Path Indexes (Every Request)

Index	Columns	Purpose
`idx_user_sessions_token`	`(app_id, token_hash)`	Session cookie validation on every authenticated request
`idx_user_sessions_user`	`(app_id, user_id, revoked_at)`	Active session lookup for session management
`idx_user_roles_app_user`	`(app_id, user_id)`	RBAC permission check on every authorized request

Authentication Indexes

Index	Columns	Purpose
`idx_users_email`	`(email)`	Cross-app email lookup (admin search)
`idx_idps_app_enabled`	`(app_id, enabled)`	List active IdPs for login page
`idx_oauth_clients_app_enabled`	`(app_id, enabled)`	List active OAuth clients
`idx_user_identity_links_app_user`	`(app_id, user_id)`	User's linked identity providers
`idx_user_identity_links_idp`	`(idp_id)`	Find users by IdP (for IdP deletion checks)

Token & Code Indexes

Index	Columns	Purpose
`idx_auth_codes_client`	`(app_id, client_id)`	Authorization code lookup by client
`idx_auth_codes_expires`	`(expires_at)`	Expired code cleanup
`idx_refresh_tokens_client`	`(app_id, client_id)`	Refresh token lookup by client
`idx_refresh_tokens_user`	`(app_id, user_id)`	User's refresh tokens (revocation)
`idx_refresh_tokens_expires`	`(expires_at)`	Expired token cleanup
`idx_oauth_state_expires`	`(expires_at)`	Expired state cleanup
`idx_user_sessions_expires`	`(expires_at)`	Expired session cleanup

Admin & Dashboard Indexes

Index	Columns	Purpose
`idx_applications_dashboard`	`(is_dashboard)`	Find dashboard app quickly
`idx_api_keys_app`	`(app_id)`	List API keys for an app
`idx_api_keys_prefix`	`(key_prefix)`	Token validation by prefix
`idx_audit_logs_app_created`	`(app_id, created_at DESC)`	Paginated audit log listing
`idx_audit_logs_app_user`	`(app_id, user_id)`	Audit log filtered by user
`idx_audit_logs_app_type`	`(app_id, event_type)`	Audit log filtered by event type
`idx_role_permissions_app`	`(app_id)`	List permissions for an app's roles
`idx_user_roles_role`	`(role_id)`	Find users with a specific role

Email Verification Indexes

Index	Columns	Purpose
`idx_verification_codes_lookup`	`(app_id, email, type, used_at)`	Active code lookup
`idx_verification_codes_expires`	`(expires_at)`	Expired code cleanup

Bootstrap Process

On first HTTP request, ensureBootstrapped() runs idempotently (all inserts use INSERT OR IGNORE). It creates:

1. Dashboard Application

A built-in application with id = '00000000-0000-0000-0000-000000000000', slug = 'dashboard', and is_dashboard = 1. The schema seed also inserts this row.

2. Dashboard Roles

Role ID	Name	Description
`operator`	operator	Platform operator with full system access
`developer`	developer	Default role for developer accounts
`member`	member	Read-only team member

3. Dashboard Permissions

Platform operations (operator only): platform:manage, developers:read, developers:write, apps:read:all, apps:write:all

Developer operations: apps:create, apps:read, apps:write, apps:delete, apps:transfer, teams:read, teams:write

Everyone: profile:read, profile:write

Legacy compatibility (seeded on dashboard for endpoint compatibility): clients:read, clients:write, clients:delete, users:read, users:write, users:delete, idps:read, idps:write, idps:delete, roles:read, roles:write

4. Role-Permission Mappings

operator gets all permissions (dashboard + legacy)
developer gets: apps:create, apps:read, apps:write, apps:delete, apps:transfer, teams:read, teams:write, profile:read, profile:write, users:read
member gets: profile:read, profile:write

5. API Keys

If no API keys exist for the dashboard app, generates one publishable and one secret key.

Role Assignment

When a user first authenticates:

If their email matches BOOTSTRAP_ADMIN_EMAIL and email is verified → assigned operator role
Otherwise → assigned developer role

Database Access Layer

The TypeScript DB layer lives in src/backend/db/:

db/
├── index.ts          # createDb(d1) — composes all query modules
├── types.ts          # Row types matching SQLite (booleans as number 0/1)
├── users.ts          # createUserQueries(db)
├── clients.ts        # createClientQueries(db)
├── sessions.ts       # createSessionQueries(db)
├── idps.ts           # createIdPQueries(db)
├── rbac.ts           # createRbacQueries(db)
├── audit.ts          # createAuditQueries(db)
├── applications.ts   # createApplicationQueries(db)
├── api-keys.ts       # createApiKeyQueries(db)
└── email-codes.ts    # createEmailCodeQueries(db)

Route handlers access the DB via:

const db = createDb(c.env.DB);
const user = await db.users.getById(appId, userId);
const roles = await db.rbac.getUserRoles(appId, userId);

Type convention: DB row types use number for booleans (SQLite stores 0/1). Route handlers normalize to boolean before returning to the frontend.

Migration Files

The schema uses a single consolidated migration file:

migrations/
  0001_schema.sql    # Complete schema for fresh installs

See Database Migrations for the migration workflow.