Data Schema

Drizzle ORM

Schema source files: shell/api/src/schemas/*.schema.ts Migration files: shell/api/database/migrations/

bun run db:migrate   # apply pending migrations
bun run db:studio    # open Drizzle Studio (local DB browser)

---

Auth

users + credentials + sessions

users

Column	Type	Notes
id	uuid	PK
email	text	unique
firstName	text
lastName	text
username	text	unique
status	text	ACTIVE | INACTIVE | SUSPENDED
organizationId	uuid	FK → organizations
keycloakUserId	text	Keycloak user UUID
createdAt	timestamp
updatedAt	timestamp

user_credentials

Column	Type	Notes
id	uuid	PK
userId	uuid	FK → users
passwordHash	text	Bun.password (bcrypt)
mustChangePassword	boolean	Force reset on next login

user_sessions

Column	Type	Notes
id	uuid	PK
userId	uuid	FK → users
token	text	Shell JWT
expiresAt	timestamp
ipAddress	text
userAgent	text

oauth_accounts

Column	Type	Notes
id	uuid	PK
userId	uuid	FK → users
provider	text	keycloak, google, etc.
providerUserId	text	ID from the provider
accessToken	text	Last known access token

---

IAM

organizations + roles + permissions

organizations

Column	Type	Notes
id	uuid	PK
name	text
slug	text	unique
type	text	ENTERPRISE | STARTUP | INDIVIDUAL | NON_PROFIT | GOVERNMENT
status	text	ACTIVE | INACTIVE | SUSPENDED
planId	uuid	FK → subscription_plans
createdAt	timestamp

roles

Column	Type	Notes
id	uuid	PK
name	text	unique
description	text
status	text	ACTIVE | INACTIVE
isSystem	boolean	System roles cannot be deleted

permissions

Column	Type	Notes
id	uuid	PK
name	text	unique, e.g. users:write
resource	text	e.g. users
action	text	e.g. write
description	text

user_roles

Column	Type
userId	uuid FK → users
roleId	uuid FK → roles

role_permissions

Column	Type
roleId	uuid FK → roles
permissionId	uuid FK → permissions

---

Providers

identity_providers

identity_providers

Column	Type	Notes
id	uuid	PK
providerId	text	unique, e.g. google, keycloak
type	text	oauth2, oidc
clientId	text
clientSecret	text
authUrl	text	Authorization endpoint
tokenUrl	text	Token endpoint
config	jsonb	Extra config (scopes, etc.)
status	text	ACTIVE | INACTIVE

Google and Keycloak are auto-seeded on startup from env vars.

---

Registry

services

services

Column	Type	Notes
id	uuid	PK
name	text	Display name
slug	text	unique, URL-safe
description	text
entryUrl	text	Module Federation entry URL
type	text	SERVICE | SYSTEM_SERVICE
status	text	AVAILABLE | UNAVAILABLE | MAINTENANCE
createdAt	timestamp

---

Subscription

plans + limits + usage

subscription_plans

Column	Type	Notes
id	uuid	PK
name	text	Display name
slug	text	unique
price	decimal	Monthly price
interval	text	month | year
stripePriceId	text	Stripe Price ID
isActive	boolean	Show in public plan list

plan_services

Column	Type
planId	uuid FK → subscription_plans
serviceId	uuid FK → services

plan_limits

Column	Type	Notes
id	uuid	PK
planId	uuid	FK → subscription_plans
resource	text	e.g. employees, documents
limitValue	integer	-1 = unlimited

plan_usage

Column	Type	Notes
id	uuid	PK
organizationId	uuid	FK → organizations
planId	uuid	FK → subscription_plans
resource	text
usageValue	integer	Current usage count

organization_plan_history

Column	Type
id	uuid PK
organizationId	uuid FK → organizations
planId	uuid FK → subscription_plans
changedAt	timestamp

---

Audit & Onboarding

audit_logs + onboarding_requests

audit_logs

Column	Type	Notes
id	uuid	PK
userId	uuid	FK → users
action	text	e.g. user.created
resource	text	e.g. users
resourceId	text	ID of affected resource
metadata	jsonb	Additional context
createdAt	timestamp

onboarding_requests

Column	Type	Notes
id	uuid	PK
email	text	Applicant email
companyName	text
type	text	ENTERPRISE | STARTUP etc.
status	text	PENDING | APPROVED | REJECTED
rejectionReason	text	Set when rejected
createdAt	timestamp