Shell database
shell_db is one database with four concerns. Read it a cluster at a time:
- Identity — who a person is, and how they signed in.
- Organizations — the tenants they belong to.
- RBAC — what they're allowed to do.
- App registry — what shows up on the launchpad.
A satellite never reads shell_db. It gets identity from the service token and keeps its own tables — so the schema below is the Shell's private concern.
Identity
The users table is the centre: one row per person, provisioned on first sign-in. Everything about how they authenticate hangs off it — a password (1:1), live sessions, linked SSO identities, and API tokens. Each sign-in method is a row in identity_providers.
| Table | What it holds | Key columns |
|---|---|---|
users | Every platform user | email (unique), username, status; FK provider_id, organization_id (NULL = system-admin, no org) |
identity_providers | Configured sign-in methods (local, keycloak, google, org-scoped) | provider_id (unique), type, client_id / client_secret (encrypted), auth_url / token_url, status |
user_credentials | Password + MFA state — 1:1 with users | password_hash, mfa_status, failed_login_count, locked_until |
user_sessions | Active login sessions | session_token, refresh_token (hashed, unique), expires_at, ip_address |
oauth_accounts | Links a user to an external SSO identity | provider_user_id, access_token / refresh_token (encrypted); FKs user_id, provider_id |
personal_access_tokens | PATs for CLI / scripted access | token_hash (SHA-256, unique), name, expires_at, last_used_at |
Organizations
Organizations are the tenants. A user has one home org (users.organization_id) and can be a member of others through organization_members. Each org can carry its own IdP (idp_provider_id) and a vanity portal_domain, and invite people via user_invitations.
| Table | What it holds | Key columns |
|---|---|---|
organizations | Tenants | slug (unique), domain (email domain), portal_domain (vanity domain), subscription_plan, status; FK idp_provider_id → the org's own IdP |
organization_members | Join — users ↔ organizations | role (admin / member), status, joined_at; FKs user_id, organization_id (unique pair) |
user_invitations | Pending invites into an org | token (unique), email, role_ids (jsonb), expires_at, accepted_at; FKs organization_id, invited_by |
RBAC
Authorization is a classic role → permission model — made many-to-many by two join tables, and made multi-tenant by one column: user_roles.organization_id scopes an assignment to an org. These are the roles and permissions a satellite reads from its token.
| Table | What it holds | Key columns |
|---|---|---|
roles | RBAC roles, platform- or org-scoped | name, service_scope, is_default, is_system; FK organization_id (NULL = system-wide role) |
permissions | resource:action grants, optionally app-scoped | code (unique), resource, action; FK app_id (NULL = platform-wide, set = app-scoped) |
role_permissions | Join — roles ↔ permissions | FKs role_id, permission_id (unique pair) |
user_roles | Join — users ↔ roles, org-scoped | expires_at; FKs user_id, role_id, organization_id (NULL = system-wide) |
App registry
The apps table is the launchpad — one row per registered app, with its URLs and visibility. An app can scope its own permissions, expose dashboard widgets, and report health.
| Table | What it holds | Key columns |
|---|---|---|
apps | The launchpad registry | slug, name, api_url, ui_url, status, visibility flags, allowed_roles (jsonb); organization_id (NULL = platform app, set = org-owned, can shadow a platform slug) |
app_widgets | Dashboard widgets an app exposes | key, exposed_module, gating (permission, min_plan, feature_flag); FK app_id (unique per key) |
app_health_checks | Per-app health-check results | check status + timing; FK app_id |
Beyond the core
Present in shell_db but outside the four clusters above:
| Concern | Tables | Purpose |
|---|---|---|
| Billing & plans | subscription_plans, plan_apps, plan_limits, plan_usage, billing_invoices, organization_plan_history | Plan catalog, the apps each plan includes, per-plan limits, per-org usage, invoices, plan-change history |
| Stripe | stripe_webhook_events | Idempotency log for incoming Stripe webhooks |
| RAG | rag_documents, rag_token_usage | Embedded documents for search + embedding-token accounting |
| SCIM | scim_api_tokens | Bearer tokens for SCIM provisioning |
| Platform | audit_log, onboarding_requests, provider_credentials | Activity/audit history, signup requests, stored provider credentials |