# Yemaya Database Schema

Yemaya is the Creative Studio platform. Its database is the central hub for user
accounts, organizations, teams, projects, asset libraries, collaboration,
billing/subscriptions, and the plugin marketplace. It also holds cross-domain
references to capabilities provided by Isis (generative AI), Hathor
(worldbuilding), Sophia (knowledge), and Bellona (build pipelines).

**Database:** PostgreSQL **ORM:** Prisma **Schema Location:**
`libs/yemaya/database/prisma/schema.prisma` **Environment Variable:**
`YEMAYA_DATABASE_URL` **Generated Client:**
`libs/yemaya/database/src/generated/client`

## ER Diagram

```
+------------------+       +-------------------+       +-------------------+
|      User        |       |   Organization    |       |     Team          |
|------------------|       |-------------------|       |-------------------|
| id (PK)          |1---*  | id (PK)           |1---*  | id (PK)           |
| email (UQ)       |   |   | name              |   |   | organizationId FK |
| passwordHash?    |   |   | slug (UQ)         |   |   | name              |
| firstName?       |   |   | plan              |   |   | slug              |
| lastName?        |   |   | settings (JSON)   |   |   | color?            |
| displayName?     |   |   +---+---+---+---+---+   |   +---+---+-----------+
| avatarUrl?       |   |       |   |   |   |       |       |   |
| status           |   |       |   |   |   +--1----+       |   |
| locale           |   |       |   |   |   |               |   |
+--+--+--+--+------+   |       |   |   |   |*              |   |
   |  |  |  |          |       |   |   | +-+----------+    |   |
   |  |  |  +-----*----+-------+   |   | |OrgInvitation|   |   |
   |  |  |  |OrgMember |           |   | +-------------+   |   |
   |  |  |  +----------+           |   |                    |   |
   |  |  |                         |   |1                   |   |
   |  |  +----------*--------------+   |                    |   |
   |  |  |ProjectMember|               |*                   |   |
   |  |  +-------------+          +----+------+             |   |
   |  |                           | Project   |*-----*------+   |
   |  +----------*                |-----------|  TeamProject    |
   |  | Account  |                | id (PK)   |                 |
   |  +----------+                | name      |                 |
   |                              | slug      |*------*---------+
   +----------*                   | type      | TeamMember
   | Session  |                   | status    |
   +----------+                   | visibility|
                                  | ownerId FK|
   +----------+                   | orgId? FK |
   |UserPref  |1--1 User          |-----------|
   +----------+                   | hathorWorldId?    | <-- Cross-domain: Hathor
                                  | isisWorkflowIds[] | <-- Cross-domain: Isis
   +----------+                   | sophiaPackIds[]   | <-- Cross-domain: Sophia
   | ApiKey   |*--1 User          +---+---+-----------+
   +----------+                       |   |
                                      |   |1
                                      |1  |
                                      |   |*
                                      |  ++---------+       +----------+
                                      |  | Folder    |       |   Tag    |
                                      |  |-----------|       |----------|
                                      |  | id (PK)   |       | id (PK)  |
                                      |  | projectId |       | name     |
                                      |  | parentId? | self  | slug     |
                                      |  +-----------+       +----+-----+
                                      |                           |
                                      |*                          |
                                 +----+------+               +----+-----+
                                 |   Asset   |*-----*------->| AssetTag |
                                 |-----------|               +----------+
                                 | id (PK)   |
                                 | projectId |         +-------------+
                                 | folderId? |         | ProjectTag  |
                                 | creatorId |         +-------------+
                                 | name      |
                                 | type      |
                                 | status    |
                                 | storageKey|
                                 | version   |
                                 |-----------|
                                 | isisGenerationId? | <-- Cross-domain: Isis
                                 | bellonaAssetId?   | <-- Cross-domain: Bellona
                                 +---+---+-----------+
                                     |   |
                                     |   |*
                                     |  ++----------+
                                     |  | AssetUsage |
                                     |  +------------+
                                     |*
                                +----+------+
                                |  Comment  | self-referential (replies)
                                |-----------|
                                | id (PK)   |
                                | assetId?  |
                                | authorId  |
                                | parentId? |
                                | content   |
                                | resolved  |
                                +-----------+

+------------------+       +-------------------+       +-------------------+
|  Subscription    |       |    Invoice        |       | InvoiceLineItem   |
|------------------|       |-------------------|       |-------------------|
| id (PK)          |1---*  | id (PK)           |1---*  | id (PK)           |
| orgId (FK, UQ)   |       | subscriptionId FK |       | invoiceId FK      |
| plan             |       | stripeInvoiceId?  |       | description       |
| status           |       | status            |       | quantity          |
| stripeCustomerId?|       | amount            |       | unitAmount        |
| stripeSub Id?    |       | currency          |       | amount            |
+---+---------+----+       +-------------------+       +-------------------+
    |         |
    |1        |1
    |         |
    |*        |1
+---+------+ ++----------+
|UsageRec  | |CreditBal  |1---*+------------------+
|----------| |-----------|     | CreditTransaction|
|id        | |id         |     |------------------|
|metric    | |balance    |     | id               |
|quantity  | |currency   |     | creditBalanceId  |
|timestamp | +------------+    | type             |
+----------+                   | amount           |
                               +------------------+

+-------------------+       +-------------------+       +-------------------+
|    Activity       |       |  Notification     |       |     Webhook       |
|-------------------|       |-------------------|       |-------------------|
| id (PK)           |       | id (PK)           |       | id (PK)           |
| projectId?        |       | userId FK         |       | orgId?            |
| userId FK         |       | type              |       | projectId?        |
| type              |       | title             |       | url               |
| action            |       | message?          |       | secret?           |
| entityId?         |       | read              |       | events[]          |
| details (JSON)    |       | readAt?           |       | enabled           |
+-------------------+       +-------------------+       +---+---------------+
                                                            |1
                                                            |*
                                                       +----+-----------+
                                                       |WebhookDelivery|
                                                       |---------------|
                                                       | id            |
                                                       | webhookId FK  |
                                                       | event         |
                                                       | payload       |
                                                       | status        |
                                                       | statusCode?   |
                                                       +---------------+

+-------------------+       +-------------------+       +-------------------+
|    Plugin         |       | PluginVersion     |       | PluginInstallation|
|-------------------|       |-------------------|       |-------------------|
| id (PK)           |1---*  | id (PK)           |       | id (PK)           |
| slug (UQ)         |       | pluginId FK       |       | pluginId FK       |
| name              |       | version           |       | orgId?            |
| category          |  1    | changelog?        |       | projectId?        |
| pricing           |  |    | downloadUrl       |       | userId?           |
| status            |  |    +-------------------+       | version           |
| featured          |  |                                | enabled           |
| verified          |  +---*+-------------------+       +-------------------+
| downloadCount     |       | PluginReview      |
| rating?           |       |-------------------|
| reviewCount       |       | id (PK)           |
+-------------------+       | pluginId FK       |
                            | userId            |
+-------------------+       | rating            |
|    AuditLog       |       | title?            |
|-------------------|       | content?          |
| id (PK)           |       +-------------------+
| orgId?            |
| userId?           |
| action            |
| entityType?       |
| entityId?         |
| oldValues?        |
| newValues?        |
| ipAddress?        |
| createdAt         |
+-------------------+
```

## Models

### User

Core user account with authentication, profile, and preferences.

| Field           | Type         | Default  | Description                  |
| --------------- | ------------ | -------- | ---------------------------- |
| `id`            | `String`     | auto     | Primary key (cuid)           |
| `email`         | `String`     | -        | Email (unique)               |
| `emailVerified` | `DateTime?`  | -        | Email verification timestamp |
| `passwordHash`  | `String?`    | -        | Bcrypt password hash         |
| `firstName`     | `String?`    | -        | First name                   |
| `lastName`      | `String?`    | -        | Last name                    |
| `displayName`   | `String?`    | -        | Display name                 |
| `avatarUrl`     | `String?`    | -        | Avatar URL                   |
| `bio`           | `String?`    | -        | Bio text                     |
| `locale`        | `String`     | `"en"`   | Locale                       |
| `timezone`      | `String`     | `"UTC"`  | Timezone                     |
| `status`        | `UserStatus` | `ACTIVE` | Account status               |
| `lastLoginAt`   | `DateTime?`  | -        | Last login timestamp         |

**Relations:** `accounts`, `sessions`, `memberships`, `ownedProjects`,
`projectMembers`, `assets`, `comments`, `activities`, `notifications`,
`preferences`, `apiKeys` **Table:** `users`

### Account

OAuth/social provider accounts linked to a user.

| Field               | Type      | Default | Description          |
| ------------------- | --------- | ------- | -------------------- |
| `id`                | `String`  | auto    | Primary key          |
| `userId`            | `String`  | -       | FK to User           |
| `type`              | `String`  | -       | Account type         |
| `provider`          | `String`  | -       | Provider name        |
| `providerAccountId` | `String`  | -       | Provider account ID  |
| `refreshToken`      | `String?` | -       | Refresh token        |
| `accessToken`       | `String?` | -       | Access token         |
| `expiresAt`         | `Int?`    | -       | Token expiry (epoch) |
| `tokenType`         | `String?` | -       | Token type           |
| `scope`             | `String?` | -       | OAuth scopes         |
| `idToken`           | `String?` | -       | ID token             |

**Unique:** `[provider, providerAccountId]` **Table:** `accounts`

### Session, ApiKey, UserPreference

Standard session management, API key management, and user preferences. See
schema for full field details.

### Organization

Multi-tenant organization with plan-based feature gating.

| Field         | Type               | Default | Description           |
| ------------- | ------------------ | ------- | --------------------- |
| `id`          | `String`           | auto    | Primary key           |
| `name`        | `String`           | -       | Organization name     |
| `slug`        | `String`           | -       | URL slug (unique)     |
| `description` | `String?`          | -       | Description           |
| `logoUrl`     | `String?`          | -       | Logo URL              |
| `website`     | `String?`          | -       | Website URL           |
| `plan`        | `OrganizationPlan` | `FREE`  | Subscription plan     |
| `settings`    | `Json`             | `{}`    | Organization settings |

**Relations:** `members`, `teams`, `projects`, `invitations`, `subscription`
**Table:** `organizations`

### Project

Creative project container with cross-domain capability references.

| Field             | Type            | Default   | Description                      |
| ----------------- | --------------- | --------- | -------------------------------- |
| `id`              | `String`        | auto      | Primary key                      |
| `name`            | `String`        | -         | Project name                     |
| `slug`            | `String`        | -         | URL slug                         |
| `description`     | `String?`       | -         | Description                      |
| `thumbnailUrl`    | `String?`       | -         | Thumbnail URL                    |
| `type`            | `ProjectType`   | `OTHER`   | Project type                     |
| `status`          | `ProjectStatus` | `DRAFT`   | Lifecycle status                 |
| `visibility`      | `Visibility`    | `PRIVATE` | Access level                     |
| `organizationId`  | `String?`       | -         | FK to Organization               |
| `ownerId`         | `String`        | -         | FK to User                       |
| `settings`        | `Json`          | `{}`      | Project settings                 |
| `metadata`        | `Json`          | `{}`      | Metadata                         |
| `hathorWorldId`   | `String?`       | -         | **Cross-domain:** Hathor world   |
| `isisWorkflowIds` | `Json`          | `[]`      | **Cross-domain:** Isis workflows |
| `sophiaPackIds`   | `Json`          | `[]`      | **Cross-domain:** Sophia packs   |

**Unique:** `[organizationId, slug]` **Relations:** `organization`, `owner`,
`members`, `teams`, `assets`, `folders`, `tags`, `activities` **Table:**
`projects`

### Cross-Domain References

The Project model contains three cross-domain reference fields that link to
entities in other domain databases via API (not foreign keys):

- **`hathorWorldId`** (`String?`) - References a `World.id` in the Hathor
  database. Connects a creative project to its worldbuilding universe for
  narrative design, quest systems, and simulations.

- **`isisWorkflowIds`** (`Json`, default `[]`) - Array of `Workflow.id` values
  from the Isis database. Associates the project with generative AI workflows
  (ComfyUI, Blender, etc.) for AI-powered asset creation.

- **`sophiaPackIds`** (`Json`, default `[]`) - Array of `KnowledgePack.id`
  values from the Sophia database. Links the project to research knowledge packs
  for reference material and RAG-powered content creation.

The Asset model also contains cross-domain references:

- **`isisGenerationId`** (`String?`) - References a `GenerationJob.id` in the
  Isis database. Tracks which AI generation job produced this asset.

- **`bellonaAssetId`** (`String?`) - References an asset cache entry in the
  Bellona database. Tracks the engine-processed version of this asset.

These references are resolved via API calls between services, not database-level
foreign keys, preserving domain database isolation.

### Asset

Files managed within a project's asset library.

| Field              | Type          | Default | Description                     |
| ------------------ | ------------- | ------- | ------------------------------- |
| `id`               | `String`      | auto    | Primary key                     |
| `projectId`        | `String`      | -       | FK to Project                   |
| `folderId`         | `String?`     | -       | FK to Folder                    |
| `creatorId`        | `String`      | -       | FK to User                      |
| `name`             | `String`      | -       | Asset name                      |
| `description`      | `String?`     | -       | Description                     |
| `type`             | `AssetType`   | -       | Asset type                      |
| `status`           | `AssetStatus` | `DRAFT` | Review status                   |
| `mimeType`         | `String?`     | -       | MIME type                       |
| `fileSize`         | `BigInt?`     | -       | File size in bytes              |
| `storageKey`       | `String`      | -       | Object storage key              |
| `storageUrl`       | `String?`     | -       | Direct URL                      |
| `thumbnailUrl`     | `String?`     | -       | Thumbnail URL                   |
| `previewUrl`       | `String?`     | -       | Preview URL                     |
| `metadata`         | `Json`        | `{}`    | Metadata                        |
| `version`          | `Int`         | `1`     | Version number                  |
| `checksum`         | `String?`     | -       | File checksum                   |
| `isisGenerationId` | `String?`     | -       | **Cross-domain:** Isis job      |
| `bellonaAssetId`   | `String?`     | -       | **Cross-domain:** Bellona asset |

**Relations:** `project`, `folder`, `creator`, `tags`, `usages`, `comments`
**Table:** `assets`

### Folder

Hierarchical folder structure for organizing assets within a project.
Self-referential parent-child relationship.

| Field       | Type      | Default | Description         |
| ----------- | --------- | ------- | ------------------- |
| `id`        | `String`  | auto    | Primary key         |
| `projectId` | `String`  | -       | FK to Project       |
| `parentId`  | `String?` | -       | FK to parent Folder |
| `name`      | `String`  | -       | Folder name         |
| `color`     | `String?` | -       | Display color       |
| `icon`      | `String?` | -       | Display icon        |
| `sortOrder` | `Int`     | `0`     | Sort order          |

**Relations:** `project`, `parent`, `children`, `assets` **Table:** `folders`

### Subscription

Organization subscription with Stripe integration.

| Field                | Type                 | Default  | Description                 |
| -------------------- | -------------------- | -------- | --------------------------- |
| `id`                 | `String`             | auto     | Primary key                 |
| `organizationId`     | `String`             | -        | FK to Organization (1:1)    |
| `plan`               | `SubscriptionPlan`   | `FREE`   | Plan tier                   |
| `status`             | `SubscriptionStatus` | `ACTIVE` | Subscription status         |
| `stripeCustomerId`   | `String?`            | -        | Stripe customer ID (UQ)     |
| `stripeSubId`        | `String?`            | -        | Stripe subscription ID (UQ) |
| `currentPeriodStart` | `DateTime?`          | -        | Billing period start        |
| `currentPeriodEnd`   | `DateTime?`          | -        | Billing period end          |
| `cancelAtPeriodEnd`  | `Boolean`            | `false`  | Pending cancellation        |
| `trialEndsAt`        | `DateTime?`          | -        | Trial end date              |

**Relations:** `organization`, `invoices`, `usageRecords`, `credits` **Table:**
`subscriptions`

### Plugin

Marketplace plugin with versioning, reviews, and installation tracking.

| Field              | Type             | Default   | Description       |
| ------------------ | ---------------- | --------- | ----------------- |
| `id`               | `String`         | auto      | Primary key       |
| `slug`             | `String`         | -         | URL slug (unique) |
| `name`             | `String`         | -         | Plugin name       |
| `shortDescription` | `String?`        | -         | Short description |
| `description`      | `String?`        | -         | Full description  |
| `category`         | `PluginCategory` | -         | Category          |
| `authorName`       | `String`         | -         | Author name       |
| `pricing`          | `PluginPricing`  | `FREE`    | Pricing model     |
| `price`            | `Int?`           | -         | Price (cents)     |
| `status`           | `PluginStatus`   | `PENDING` | Review status     |
| `featured`         | `Boolean`        | `false`   | Featured flag     |
| `verified`         | `Boolean`        | `false`   | Verified flag     |
| `downloadCount`    | `Int`            | `0`       | Download count    |
| `rating`           | `Float?`         | -         | Average rating    |
| `reviewCount`      | `Int`            | `0`       | Review count      |

**Relations:** `versions`, `installations`, `reviews` **Table:** `plugins`

### Comment

Threaded comments on assets with resolution tracking.

| Field      | Type      | Default | Description          |
| ---------- | --------- | ------- | -------------------- |
| `id`       | `String`  | auto    | Primary key          |
| `assetId`  | `String?` | -       | FK to Asset          |
| `authorId` | `String`  | -       | FK to User           |
| `parentId` | `String?` | -       | FK to parent Comment |
| `content`  | `String`  | -       | Comment content      |
| `resolved` | `Boolean` | `false` | Whether resolved     |
| `pinned`   | `Boolean` | `false` | Whether pinned       |
| `metadata` | `Json`    | `{}`    | Metadata             |

**Relations:** `asset`, `author`, `parent`, `replies` **Table:** `comments`

### Webhook / WebhookDelivery

Webhook configuration and delivery tracking for event notifications.

### Activity / Notification

Activity feed and notification system for users and projects.

### AuditLog

Audit trail for all entity changes scoped by organization.

| Field            | Type      | Default | Description        |
| ---------------- | --------- | ------- | ------------------ |
| `id`             | `String`  | auto    | Primary key        |
| `organizationId` | `String?` | -       | Organization scope |
| `userId`         | `String?` | -       | Actor user ID      |
| `action`         | `String`  | -       | Action performed   |
| `entityType`     | `String?` | -       | Entity type        |
| `entityId`       | `String?` | -       | Entity ID          |
| `oldValues`      | `Json?`   | -       | Previous state     |
| `newValues`      | `Json?`   | -       | New state          |
| `ipAddress`      | `String?` | -       | IP address         |
| `userAgent`      | `String?` | -       | User agent         |

**Table:** `audit_logs`

## Key Enums

### UserStatus

`ACTIVE`, `INACTIVE`, `SUSPENDED`, `PENDING_VERIFICATION`

### OrganizationPlan / SubscriptionPlan

`FREE`, `STARTER`, `PROFESSIONAL`, `ENTERPRISE`

### OrganizationRole

`OWNER`, `ADMIN`, `MEMBER`, `VIEWER`

### TeamRole

`LEAD`, `MEMBER`

### ProjectType

`FILM`, `GAME`, `ANIMATION`, `COMMERCIAL`, `MUSIC_VIDEO`, `DOCUMENTARY`, `OTHER`

### ProjectStatus

`DRAFT`, `ACTIVE`, `REVIEW`, `COMPLETED`, `ARCHIVED`

### ProjectRole

`OWNER`, `ADMIN`, `EDITOR`, `CONTRIBUTOR`, `VIEWER`

### ProjectAccess

`VIEW`, `EDIT`, `ADMIN`

### Visibility

`PRIVATE`, `TEAM`, `ORGANIZATION`, `PUBLIC`

### AssetType

`IMAGE`, `VIDEO`, `AUDIO`, `MODEL_3D`, `DOCUMENT`, `SCRIPT`, `STORYBOARD`,
`CHARACTER_DESIGN`, `ENVIRONMENT_DESIGN`, `ANIMATION`, `VFX`, `MATERIAL`,
`TEXTURE`, `FONT`, `OTHER`

### AssetStatus

`DRAFT`, `PROCESSING`, `REVIEW`, `APPROVED`, `REJECTED`, `ARCHIVED`

### SubscriptionStatus

`ACTIVE`, `PAST_DUE`, `CANCELLED`, `TRIALING`, `PAUSED`

### InvoiceStatus

`DRAFT`, `OPEN`, `PAID`, `VOID`, `UNCOLLECTIBLE`

### CreditTransactionType

`PURCHASE`, `USAGE`, `REFUND`, `BONUS`, `ADJUSTMENT`, `EXPIRATION`

### ActivityType

`PROJECT`, `ASSET`, `USER`, `TEAM`, `ORGANIZATION`, `SYSTEM`

### NotificationType

`MENTION`, `COMMENT`, `INVITATION`, `ASSIGNMENT`, `APPROVAL`, `SYSTEM`, `ALERT`

### WebhookDeliveryStatus

`PENDING`, `SUCCESS`, `FAILED`, `RETRYING`

### PluginCategory

`GENERATION`, `EDITING`, `EXPORT`, `INTEGRATION`, `UTILITY`, `COLLABORATION`,
`ANALYTICS`, `AUTOMATION`, `OTHER`

### PluginPricing

`FREE`, `PAID`, `FREEMIUM`, `SUBSCRIPTION`

### PluginStatus

`PENDING`, `APPROVED`, `REJECTED`, `PUBLISHED`, `DEPRECATED`, `REMOVED`
