Database Schema: Shell
This document details the database structure for the Shell — the central source of truth for the VENI-AI platform.
1. Overview
The Shell database manages multi-tenant isolation, identity, RBAC, billing, and the global service registry. It uses PostgreSQL with Drizzle ORM and a domain-driven schema design.
Multi-tenancy model: "Shared Database, Shared Schema." Every tenant-specific table contains an organization_id column. Isolation is enforced at the application/ORM layer.
The schema is divided into five logical domains:
| Domain | Tables |
|---|---|
| Identity | users, user_credentials, user_sessions, identity_providers, oauth_accounts |
| Organizations | organizations, organization_plan_history, onboarding_requests |
| Authorization | roles, user_roles, permissions, role_permissions |
| Commercials | subscription_plans, plan_apps, plan_limits, plan_usage |
| Registry | apps, app_health_checks |
2. Core Domain Model
3. Identity Domain
identity_providers
External authentication providers (OAuth2, SAML, LDAP).
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
providerId | varchar(100) | Unique provider slug (e.g., google, keycloak). |
type | varchar(50) | oauth2, saml, or ldap. |
clientId | varchar(255) | OAuth client ID. |
clientSecret | text | Encrypted OAuth client secret. |
authUrl | varchar(500) | Authorization endpoint URL. |
tokenUrl | varchar(500) | Token endpoint URL. |
config | jsonb | Additional provider-specific config. Default: {}. |
status | varchar(20) | ACTIVE or INACTIVE. Default: ACTIVE. |
createdAt | timestamptz |
users
All individuals across all organizations.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. Global user identifier (sub in JWT). |
email | varchar(320) | Unique primary email. |
username | varchar(100) | Unique username. |
firstName | varchar(100) | First name. |
lastName | varchar(100) | Last name. |
providerId | uuid | FK to identity_providers.id. |
organizationId | uuid | FK to organizations.id. |
status | varchar(20) | ACTIVE, INACTIVE, or SUSPENDED. Default: ACTIVE. |
preferences | jsonb | UI preferences (theme, language, etc.). Default: {}. |
securitySettings | jsonb | Security configuration. Default: {}. |
createdBy | uuid | |
updatedBy | uuid | |
createdAt | timestamptz | |
updatedAt | timestamptz |
user_credentials
Password and MFA credentials for local-login users.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
userId | uuid | FK to users.id (unique). |
passwordHash | varchar(255) | Bcrypt password hash. |
passwordHistory | jsonb | Previous hashes to prevent reuse. |
failedLoginCount | integer | Failed login attempts. Default: 0. |
lockedUntil | timestamptz | Account lockout expiry. |
lastLoginAt | timestamptz | |
mfaStatus | varchar(20) | ACTIVE or DISABLED. |
mfaSecret | varchar(255) | TOTP secret. |
mfaBackupCodes | jsonb | One-time backup codes. |
recoveryEmail | varchar(320) | Account recovery email. |
createdAt | timestamptz | |
updatedAt | timestamptz |
oauth_accounts
Links users to their OAuth provider accounts.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
userId | uuid | FK to users.id. |
providerId | uuid | FK to identity_providers.id. |
providerUserId | varchar(255) | User ID from the provider. |
accessToken | text | Encrypted access token. |
refreshToken | text | Encrypted refresh token. |
tokenExpiresAt | timestamptz | |
createdAt | timestamptz |
user_sessions
Active user sessions and refresh tokens.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
userId | uuid | FK to users.id. |
sessionToken | varchar(128) | Unique session identifier. |
refreshToken | varchar(128) | Unique refresh token. |
deviceInfo | jsonb | Browser/device metadata. |
ipAddress | inet | Client IP address. |
status | varchar(20) | ACTIVE or REVOKED. Default: ACTIVE. |
expiresAt | timestamptz | Session expiry. |
lastActivityAt | timestamptz | |
createdAt | timestamptz | |
updatedAt | timestamptz |
4. Organizations Domain
organizations
The root entity for every tenant on the platform.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
name | varchar(255) | Legal name. |
slug | varchar(100) | Unique URL-friendly identifier. |
domain | varchar(253) | Corporate email domain (for auto-provisioning). |
type | varchar(50) | ENTERPRISE, STARTUP, INDIVIDUAL, NON_PROFIT, GOVERNMENT. Default: ENTERPRISE. |
subscriptionPlan | varchar(50) | Current plan code: HOBBY, PRO, ENTERPRISE. Default: HOBBY. |
subscriptionStatus | varchar(50) | Stripe subscription status. Default: active. |
billingInterval | varchar(20) | monthly or yearly. Default: monthly. |
trialStartDate | timestamptz | Trial start date. |
trialEndDate | timestamptz | Trial end date. |
stripeCustomerId | varchar(255) | Stripe customer ID. |
stripeSubscriptionId | varchar(255) | Stripe subscription ID. |
settings | jsonb | Org-specific configuration. Default: {}. |
complianceData | jsonb | Compliance and legal metadata. Default: {}. |
status | varchar(20) | ACTIVE, INACTIVE, SUSPENDED. Default: ACTIVE. |
createdBy | uuid | |
updatedBy | uuid | |
createdAt | timestamptz | |
updatedAt | timestamptz |
organization_plan_history
Immutable audit trail of all plan changes.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
organizationId | uuid | FK to organizations.id. |
previousPlan | varchar(50) | Plan code before change. |
newPlan | varchar(50) | Plan code after change. |
previousInterval | varchar(20) | Billing interval before. |
newInterval | varchar(20) | Billing interval after. |
reason | varchar(50) | upgrade, downgrade, trial_start, trial_end, stripe_checkout, admin_override, cancellation. |
metadata | jsonb | Additional context. |
changedBy | uuid | User who triggered the change. |
createdAt | timestamptz |
onboarding_requests
B2B enterprise onboarding pipeline.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
orgName | varchar(255) | Requested organization name. |
orgType | varchar(50) | Organization type. Default: ENTERPRISE. |
contactEmail | varchar(320) | Contact email. |
contactPhone | varchar(50) | Optional phone number. |
firstName | varchar(100) | Applicant first name. |
lastName | varchar(100) | Applicant last name. |
applicantEmail | varchar(320) | Applicant email. |
companySize | integer | Number of employees. |
ssoInterest | boolean | Whether SSO integration is requested. |
subscriptionPlan | varchar(50) | Requested plan. |
status | varchar(30) | PENDING, APPROVED, REJECTED. Default: PENDING. |
rejectionReason | text | Reason if rejected. |
organizationId | uuid | FK to organizations.id (set on approval). |
userId | uuid | FK to users.id (set on approval). |
reviewedBy | uuid | Admin who reviewed. |
reviewedAt | timestamptz | |
createdAt | timestamptz | |
updatedAt | timestamptz |
5. Authorization Domain
roles
Role definitions scoped to platform or specific services.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
name | varchar(100) | Role name (e.g., admin, member). |
displayName | varchar(255) | Human-readable label. |
description | text | Role description. |
serviceScope | varchar(50) | PLATFORM or a service slug. Default: PLATFORM. |
isSystem | boolean | If true, cannot be deleted. Default: false. |
isDefault | boolean | If true, auto-assigned to new users. Default: false. |
organizationId | uuid | FK to organizations.id (null for platform-wide roles). |
status | varchar(20) | ACTIVE or INACTIVE. Default: ACTIVE. |
createdAt | timestamptz | |
updatedAt | timestamptz |
user_roles
Junction table assigning roles to users.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
userId | uuid | FK to users.id. |
roleId | uuid | FK to roles.id. |
organizationId | uuid | Org scope of this assignment. |
assignedAt | timestamptz | When the role was assigned. |
expiresAt | timestamptz | Optional role expiry. |
permissions
Fine-grained permission codes in resource:action format.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
code | varchar(255) | Unique code (e.g., drive:files:read). |
appId | uuid | FK to apps.id (null for platform permissions). |
type | varchar(10) | p (policy) or g (group). Default: p. |
resource | varchar(100) | Resource name. |
action | varchar(100) | Action name. |
isSystem | boolean | If true, cannot be deleted. Default: false. |
description | text | |
createdAt | timestamptz |
role_permissions
Junction table assigning permissions to roles.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
roleId | uuid | FK to roles.id. |
permissionId | uuid | FK to permissions.id. |
6. Commercials Domain
subscription_plans
Product catalog for the pricing page and quota enforcement.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
code | varchar(50) | Unique plan code (e.g., HOBBY, PRO, ENTERPRISE). |
name | varchar(100) | Display name. |
tagline | varchar(255) | Short marketing description. |
monthlyPrice | real | Price in USD per month. |
yearlyPrice | real | Price in USD per year. |
priceUnit | varchar(20) | Price unit label (e.g., per seat). |
isRecommended | boolean | Highlight on pricing page. Default: false. |
section | varchar(20) | individual or business. Default: individual. |
ctaLabel | varchar(100) | Call-to-action button label. |
ctaLink | varchar(255) | Call-to-action URL. |
displayOrder | integer | Sort order on pricing page. Default: 0. |
features | jsonb | Feature list: [{ "label": "...", "included": true }]. |
status | varchar(20) | active, hidden, or deprecated. Default: active. |
stripeProductId | varchar(255) | Stripe product ID. |
stripeMonthlyPriceId | varchar(255) | Stripe price ID for monthly billing. |
stripeYearlyPriceId | varchar(255) | Stripe price ID for yearly billing. |
createdAt | timestamptz | |
updatedAt | timestamptz |
plan_apps
Junction table mapping which apps are included in each plan.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
planId | uuid | FK to subscription_plans.id. |
appId | uuid | FK to apps.id. |
createdAt | timestamptz |
plan_limits
Quota definitions per plan (e.g., max storage, max members).
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
planId | uuid | FK to subscription_plans.id. |
limitKey | varchar(100) | Quota key (e.g., storage_gb, members). |
limitValue | integer | Maximum allowed value. |
period | varchar(20) | monthly or lifetime. Default: monthly. |
createdAt | timestamptz | |
updatedAt | timestamptz |
plan_usage
Rolling usage counters per organization per billing period.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
organizationId | uuid | FK to organizations.id. |
limitKey | varchar(100) | Matches a plan_limits.limitKey. |
periodKey | varchar(20) | Billing period (e.g., 2026-03). |
count | integer | Current usage count. Default: 0. |
updatedAt | timestamptz |
7. Registry Domain
apps
All registered SCS satellite applications.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
slug | varchar(255) | Unique URL-friendly identifier (e.g., drive). |
name | varchar(255) | Display name. |
type | varchar(50) | SERVICE or other types. Default: SERVICE. |
apiUrl | text | Internal API base URL. |
uiUrl | text | Module Federation entry URL. |
icon | varchar(255) | Icon URL or identifier. |
description | text | App description. |
category | varchar(100) | App category. |
status | varchar(20) | AVAILABLE, MAINTENANCE, DISABLED. Default: AVAILABLE. |
metadata | jsonb | Arbitrary metadata. Default: {}. |
createdBy | uuid | |
updatedBy | uuid | |
createdAt | timestamptz | |
updatedAt | timestamptz |
app_health_checks
Health check results for registered apps.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
appId | uuid | FK to apps.id. |
statusCode | integer | HTTP status code returned. |
responseTimeMs | integer | Response time in milliseconds. |
success | boolean | Whether the check passed. |
checkedAt | timestamptz | When the check was performed. |
8. Audit
audit_log
Partitioned table for all platform events. Partitioned by createdAt.
| Field | Type | Description |
|---|---|---|
id | uuid | |
userId | uuid | Actor. |
organizationId | uuid | Org context. |
eventType | varchar(100) | Event category (e.g., USER_LOGIN). |
action | varchar(100) | Specific action performed. |
resource | varchar(100) | Target resource type. |
resourceId | varchar(100) | Target resource ID. |
eventData | jsonb | Additional event payload. |
ipAddress | inet | Client IP. |
userAgent | text | Client user agent. |
severity | varchar(20) | info, warning, critical. Default: info. |
createdAt | timestamptz | Partition key. |