HelloJohn's database schema — global tables for the Control Plane and per-tenant tables for the Data Plane. Entity relationships, primary keys, and key indexes.

HelloJohn uses two database contexts: the global database (Control Plane) and per-tenant databases (Data Plane). Both are managed by embedded migrations that run automatically on startup.

Global database (Control Plane)

The global database stores tenant and operator-level data.

`cp_tenant`

Stores all tenants registered in this HelloJohn instance.

Column	Type	Description
`id`	`text` PK	Tenant ID: `tnt_01HABCDEF...`
`name`	`text`	Human-readable tenant name
`slug`	`text` UNIQUE	URL-safe identifier
`plan`	`text`	`free`, `starter`, `pro`, `enterprise`
`status`	`text`	`active`, `suspended`, `deleted`
`db_dsn`	`text`	Connection string for this tenant's DB (encrypted)
`created_at`	`timestamptz`

`cp_client`

OAuth2 clients (applications) registered under a tenant.

Column	Type	Description
`id`	`text` PK	Client ID: `cli_01HABCDEF...`
`tenant_id`	`text` FK → `cp_tenant.id`
`name`	`text`	Client name
`type`	`text`	`web`, `mobile`, `api`
`publishable_key`	`text` UNIQUE	`pk_live_...` — safe for browser
`secret_key_hash`	`text`	Hashed `sk_live_...` — never stored plaintext
`redirect_uris`	`text[]`	Allowed OAuth2 redirect URIs
`created_at`	`timestamptz`

`cp_admin`

Control Plane admin accounts.

Column	Type	Description
`id`	`text` PK	Admin ID: `adm_01HABCDEF...`
`email`	`text` UNIQUE
`password_hash`	`text`	Argon2id hash
`role`	`text`	`super_admin`, `admin`
`created_at`	`timestamptz`

Per-tenant database (Data Plane)

Each tenant has its own database (or schema) with the following tables.

`hj_user`

End users of the tenant's application.

Column	Type	Description
`id`	`text` PK	User ID: `usr_01HABCDEF...`
`email`	`text` UNIQUE
`email_verified`	`boolean`
`name`	`text`	Display name
`avatar_url`	`text`
`password_hash`	`text`	Argon2id hash (null if OAuth-only)
`role`	`text`	`member`, `admin`, `super_admin`
`public_metadata`	`jsonb`	Included in JWT
`private_metadata`	`jsonb`	NOT included in JWT
`status`	`text`	`active`, `suspended`, `deleted`
`created_at`	`timestamptz`
`updated_at`	`timestamptz`

`hj_session`

Active user sessions.

Column	Type	Description
`id`	`text` PK	Session ID: `ses_01HABCDEF...`
`user_id`	`text` FK → `hj_user.id`
`refresh_token_hash`	`text`	Hash of the opaque refresh token
`user_agent`	`text`	Browser/client user agent
`ip_address`	`inet`	Client IP at sign-in
`last_active_at`	`timestamptz`	Updated on each token refresh
`expires_at`	`timestamptz`
`revoked_at`	`timestamptz`	Set on logout
`created_at`	`timestamptz`

`hj_mfa_factor`

Enrolled MFA factors per user.

Column	Type	Description
`id`	`text` PK	Factor ID: `mfa_01HABCDEF...`
`user_id`	`text` FK → `hj_user.id`
`type`	`text`	`totp`, `webauthn`, `sms`, `email`
`name`	`text`	User-facing label (e.g. "iPhone 14")
`secret`	`text`	Encrypted TOTP secret / WebAuthn credential
`backup_codes_hash`	`text[]`	Hashed backup codes
`verified_at`	`timestamptz`	First successful verification
`last_used_at`	`timestamptz`
`created_at`	`timestamptz`

`hj_oauth_provider`

Linked OAuth providers per user.

Column	Type	Description
`id`	`text` PK
`user_id`	`text` FK → `hj_user.id`
`provider`	`text`	`google`, `github`, `apple`, etc.
`provider_user_id`	`text`	User ID from the OAuth provider
`access_token`	`text`	Encrypted OAuth access token (if stored)
`created_at`	`timestamptz`

`hj_organization`

Organizations within a tenant (B2B workspaces).

Column	Type	Description
`id`	`text` PK	Org ID: `org_01HABCDEF...`
`name`	`text`
`slug`	`text` UNIQUE
`metadata`	`jsonb`	Custom org data
`created_at`	`timestamptz`

`hj_org_member`

Membership records linking users to organizations.

Column	Type	Description
`id`	`text` PK
`org_id`	`text` FK → `hj_organization.id`
`user_id`	`text` FK → `hj_user.id`
`role`	`text`	`owner`, `admin`, `member`, `guest`
`joined_at`	`timestamptz`

Entity relationship diagram

cp_tenant (1) ──< cp_client (many)

cp_tenant (1) ──[per-tenant DB]──

hj_user (1) ──< hj_session (many)
hj_user (1) ──< hj_mfa_factor (many)
hj_user (1) ──< hj_oauth_provider (many)
hj_user (1) ──< hj_org_member (many)

hj_organization (1) ──< hj_org_member (many)

ID format

All HelloJohn IDs use a prefixed ULID format: {prefix}_{ulid}.

Prefix	Entity
`tnt_`	Tenant
`cli_`	Client
`adm_`	CP Admin
`usr_`	User
`ses_`	Session
`mfa_`	MFA Factor
`org_`	Organization

ULIDs are sortable by creation time, collision-resistant, and URL-safe.

Migrations

HelloJohn runs embedded SQL migrations automatically on startup. No manual ALTER TABLE commands needed. Migrations are additive-only — no destructive schema changes in patch releases.

# Check migration status
hjctl migrate status

# Run pending migrations manually
hjctl migrate up

# Roll back last migration (dev only)
hjctl migrate down

Always backup your database before upgrading HelloJohn to a new minor or major version.

Database Schema