# Platform Service Data Model ## Overview All tables reside in the shared Aurora MySQL instance, managed via Prisma. Tables are grouped by concern. Every tenant-scoped table includes a `tenantId` foreign key. The schema supports ~55 tables across all phases; Phase 1 implements the core tables for RBAC, multi-tenancy, and audit. > **Phase annotations:** Tables marked **(Phase 2)** are defined in this document for design reference but do **not** yet exist in the Prisma schema. --- ## Multi-Tenancy ### Tenant The root entity. Every permission-related entity is scoped to a tenant. | Column | Type | Notes | | ------------------------ | ------------ | ---------------------------------------------------------- | | `id` | VarChar(36) | UUID primary key | | `name` | VarChar(255) | Display name | | `slug` | VarChar(100) | URL-safe identifier, unique | | `status` | Enum | `active`, `suspended`, `deactivated` | | `planId` | VarChar(36) | FK to Plan (denormalized for fast pipeline lookups) | | `dataResidency` | VarChar(50) | AWS region (default `us-east-1`) | | `primaryJurisdiction` | VarChar(10) | ISO 3166-1 alpha-2 (default `US`) | | `isTest` | Boolean | Test tenants allow product-level super admin impersonation | | `createdAt`, `updatedAt` | DateTime | Auto-managed | **Indexes:** `@@unique(slug)` ### TenantUser Links a user to a tenant. A user can belong to multiple tenants. | Column | Type | Notes | | ------------------ | ----------- | ----------------------------------------------- | | `id` | VarChar(36) | UUID primary key | | `tenantId` | VarChar(36) | FK to Tenant | | `userId` | VarChar(36) | FK to User | | `status` | Enum | `active`, `invited`, `suspended`, `deactivated` | | `userJurisdiction` | VarChar(10) | Optional override of tenant jurisdiction | | `departmentId` | VarChar(36) | Optional FK to Department | | `invitedBy` | VarChar(36) | Who invited this user | | `joinedAt` | DateTime | When the invitation was accepted | **Indexes:** `@@unique([tenantId, userId])`, `@@index([userId])` **Jurisdiction resolution:** `effectiveJurisdiction = mostProtective(tenant.primaryJurisdiction, user.userJurisdiction)`. Required by GDPR Article 3(2). ### Invitation (Phase 2) Tracks individual user invitations with a single-use token. | Column | Type | Notes | | ----------------------------------- | ----------- | -------------------------------------------- | | `id`, `tenantId`, `email`, `roleId` | - | Standard fields | | `token` | VarChar(64) | Cryptographically random, unique, single-use | | `status` | Enum | `pending`, `accepted`, `expired`, `revoked` | | `expiresAt` | DateTime | Default 7 days from creation | **Indexes:** `@@unique([tenantId, email, status])`, `@@index([token])`, `@@index([expiresAt])` Pending invitations count toward the seat quota to prevent over-inviting. ### Department (Phase 2) Hierarchical organizational unit (self-referencing tree). | Column | Type | Notes | | -------------------------------- | ----------- | -------------------------------- | | `id`, `tenantId`, `name`, `slug` | - | Standard fields | | `parentId` | VarChar(36) | Optional FK to parent Department | **Indexes:** `@@unique([tenantId, slug])` Used for ABAC `department` conditions. Distinct from roles (permission containers) and groups (collaboration containers). ### UserGroup / UserGroupMember (Phase 2) Flat collaboration containers for resource sharing. Groups do NOT grant permissions -- they are purely for resource access scoping. --- ## RBAC ### Module Organizes permissions into logical groups (e.g., `admin`, `tools`, `models`, `chat`). | Column | Type | Notes | | ---------------- | ------------ | ---------------- | | `id`, `tenantId` | VarChar(36) | Standard fields | | `name` | VarChar(255) | Display name | | `slug` | VarChar(100) | URL identifier | | `sortOrder` | Int | Display ordering | **Indexes:** `@@unique([tenantId, slug])` ### Permission An action within a module. Canonical name: `{module.slug}.{action}`. | Column | Type | Notes | | ------------- | ------------ | --------------------------------------------- | | `id` | VarChar(36) | Primary key | | `moduleId` | VarChar(36) | FK to Module | | `action` | VarChar(255) | Action name (e.g., `query`, `create`, `view`) | | `description` | Text | Human-readable description | **Indexes:** `@@unique([moduleId, action])` ### Role A named set of permissions, tenant-scoped, optionally system-defined. | Column | Type | Notes | | -------------------------------- | ------- | ----------------------------------------------------------- | | `id`, `tenantId`, `name`, `slug` | - | Standard fields | | `isSystem` | Boolean | System roles cannot be deleted or have permissions modified | **Indexes:** `@@unique([tenantId, slug])` ### RolePermission Many-to-many join: which permissions a role grants. | Column | Type | | -------------- | ----------- | | `roleId` | VarChar(36) | | `permissionId` | VarChar(36) | **Primary key:** `@@id([roleId, permissionId])` ### RoleInheritance Parent-child edges for role hierarchy. Child roles inherit all permissions from parent roles. | Column | Type | | -------------- | ----------- | | `parentRoleId` | VarChar(36) | | `childRoleId` | VarChar(36) | **Primary key:** `@@id([parentRoleId, childRoleId])` ### RoleExclusion Separation of duty constraints -- mutually exclusive role pairs. | Column | Type | Notes | | -------------------- | ----------- | -------------------------------- | | `id`, `tenantId` | VarChar(36) | Standard fields | | `roleIdA`, `roleIdB` | VarChar(36) | The two mutually exclusive roles | | `reason` | Text | Why these roles conflict | **Indexes:** `@@unique([tenantId, roleIdA, roleIdB])` ### UserRole Assigns a role to a user within a tenant. `tenantId` is denormalized from `Role.tenantId` for DB-level cross-tenant isolation enforcement. | Column | Type | Notes | | ------------------------------ | ----------- | --------------------- | | `userId`, `tenantId`, `roleId` | VarChar(36) | Composite primary key | | `grantedAt` | DateTime | When assigned | | `grantedBy` | VarChar(36) | Who assigned it | **Primary key:** `@@id([tenantId, userId, roleId])` ### UserPermission Direct permission overrides (grant or deny) for a specific user. Deny overrides implement deny-wins (step 8 of the pipeline). | Column | Type | Notes | | ------------------------------------------ | -------- | -------------------------------------- | | `id`, `tenantId`, `userId`, `permissionId` | - | Standard fields | | `type` | Enum | `grant` or `deny` | | `expiresAt` | DateTime | Optional expiration (null = permanent) | | `reason` | Text | Justification for the override | **Indexes:** `@@index([tenantId, userId])`, `@@index([expiresAt])` ### PolicyCondition ABAC conditions attached to permissions. All conditions on a permission are AND-ed. | Column | Type | Notes | | -------------------------------- | ---- | -------------------------------------------------------------------------------- | | `id`, `tenantId`, `permissionId` | - | Standard fields | | `conditionType` | Enum | `ownership`, `department`, `time_window`, `resource_state`, `ip_range`, `custom` | | `parameters` | Json | Condition-specific parameters | **Indexes:** `@@index([tenantId, permissionId])` --- ## Feature Gating ### Plan Immutable once published. Versioned by `familySlug` + `version`. | Column | Type | Notes | | ----------------------- | ------------ | ----------------------------------------------------- | | `id` | VarChar(36) | Primary key | | `familySlug` | VarChar(100) | Groups versions (e.g., `professional`) | | `version` | Int | Version number within family | | `slug` | VarChar(100) | Unique human-readable identifier | | `status` | Enum | `draft`, `active`, `deprecated`, `sunset`, `archived` | | `basePricePerSeatCents` | Int | Monthly per-seat price | | `creditsPerSeat` | Int | AI credits allocated per seat per month | | `overagePolicy` | Enum | `hard_cap`, `soft_overage`, `throttle` | | `minSeats`, `maxSeats` | Int | Seat range for the plan | | `migrationTargetId` | VarChar(36) | Recommended replacement when deprecated | **Indexes:** `@@unique(slug)`, `@@unique([familySlug, version])` **Lifecycle:** `draft` -> `active` -> `deprecated` -> `sunset` -> `archived` ### PlanFeature (Phase 2) Feature keys enabled by a plan (e.g., `tools.snowflake`, `models.opus`). | Column | Type | | -------------- | ------------ | | `id`, `planId` | VarChar(36) | | `featureKey` | VarChar(255) | **Indexes:** `@@unique([planId, featureKey])` ### TenantPlan (Phase 2) Authoritative record of a tenant's plan assignment with optional grandfathering overrides. | Column | Type | Notes | | -------------------------- | -------- | -------------------------------------- | | `id`, `tenantId`, `planId` | - | Standard fields | | `creditOverride` | Int | Overrides `Plan.creditsPerSeat` if set | | `priceOverrideCents` | Int | Custom pricing (enterprise contracts) | | `featureOverrides` | Json | Additional feature keys beyond plan | | `billingCycleAnchor` | DateTime | Start of billing cycle | | `seatCount` | Int | Current seat count | **Indexes:** `@@unique(tenantId)` -- one plan per tenant --- ## Compliance ### AuditLog Append-only record of every permission check and administrative action. PII fields are encrypted via crypto-shredding. | Column | Type | Notes | | ---------------- | ------------ | -------------------------------------------- | | `id`, `tenantId` | VarChar(36) | Standard fields | | `userId` | VarChar(36) | Encrypted via per-user key | | `action` | VarChar(255) | e.g., `tools.snowflake.query`, `chat.create` | | `resource` | VarChar(255) | Resource type | | `resourceId` | VarChar(255) | Specific resource instance | | `outcome` | Enum | `success`, `denied`, `error` | | `metadata` | Json | Additional context | | `ipAddress` | VarChar(45) | Encrypted | | `actingAsUserId` | VarChar(36) | Non-empty during impersonation | | `isSuperAdmin` | Boolean | Whether acting as super admin | **Indexes:** `@@index([tenantId, createdAt])`, `@@index([tenantId, userId, createdAt])`, `@@index([tenantId, action, createdAt])`, `@@index([tenantId, resource, createdAt])` ### PermissionChangeLog Tracks all permission-related changes for audit reconstruction. | Column | Type | Notes | | ------------------------------ | ----------- | ------------------------------------------- | | `id`, `tenantId` | VarChar(36) | Standard fields | | `targetUserId`, `targetRoleId` | VarChar(36) | What was changed | | `changeType` | VarChar(50) | e.g., `role_assigned`, `permission_granted` | | `detail` | Json | Before/after state | | `changedBy` | VarChar(36) | Who made the change | **Indexes:** `@@index([tenantId, createdAt])`, `@@index([tenantId, targetUserId, createdAt])` --- ## Security ### SuperAdmin Cross-tenant platform access for internal staff. | Column | Type | Notes | | -------------- | ----------- | ------------------------------------------------------ | | `id`, `userId` | VarChar(36) | Standard fields | | `level` | Enum | `full`, `read_only`, `support`, `product`, `analytics` | | `revokedAt` | DateTime | Null while active | ### Session Tracks active user sessions for revocation support. ### StepUpChallenge Step-up authentication challenges (password, MFA, manager approval). --- ## Cascade Behavior | Parent | Child | On Delete | | ------------- | ----------------------------------------------- | ---------------------------------------------------------------------- | | Tenant | TenantUser, Role, Module, AuditLog, ... | Application-level (no cascade -- tenant deletion is a lifecycle event) | | Module | Permission | Cascade | | Permission | RolePermission, UserPermission, PolicyCondition | Cascade | | Role | RolePermission, UserRole, RoleInheritance | Cascade | | ResourceShare | - | Hard delete on revocation (not soft-delete) **(Phase 2)** | `ResourceShare` (Phase 2) will use hard delete instead of soft-delete because MySQL cannot enforce partial unique indexes. The audit log captures revocation events for history. --- ## Naming Conventions - **Table names:** snake_case plural (e.g., `tenant_users`, `role_permissions`) - **Column names:** snake_case in DB (via `@map`), camelCase in Prisma schema - **Enum values:** snake_case (e.g., `soft_overage`, `time_window`) - **UUID primary keys:** all entity tables use UUID v4 via `@default(uuid())` - **Sentinel values:** `"*"` used instead of `NULL` in composite unique indexes (MySQL treats NULL != NULL, allowing duplicates)