# Database Schema Inventory Comprehensive database schema documentation for the Oshun platform. > **Note**: This inventory was originally generated from the Lilith and Yemaya > codebases during the initial monorepo migration. Additional domains (Iris, > Tara, Veritas, Psyche, Nyx, Aja, Aphrodite, and others) have since been added > and their schemas should be documented separately. Generated: 2026-01-10 | Last updated: 2026-02-14 --- ## Overview | Codebase | ORM/Query Builder | Architecture | Total Tables | | ---------- | ----------------------- | ------------------------- | ------------ | | **Yemaya** | Prisma (full ORM) | Centralized single schema | 36+ models | | **Lilith** | Knex.js (query builder) | Decentralized per-service | 40+ tables | --- ## Yemaya Database Schema (Prisma) **Location:** `/home/ubuntu/oshun/yemaya/packages/database/prisma/schema.prisma` **Database:** PostgreSQL with Prisma Client ### User & Authentication Models #### User | Field | Type | Constraints | Description | | ------------- | ------------- | ----------------------------------------- | ---------------------------- | | id | String (CUID) | PK | Primary identifier | | email | String | Unique | User email address | | emailVerified | DateTime? | | Email verification timestamp | | passwordHash | String? | | Hashed password | | firstName | String? | | First name | | lastName | String? | | Last name | | displayName | String? | | Display name | | avatarUrl | String? | | Profile picture URL | | bio | String? | | User biography | | locale | String | Default: "en" | Language preference | | timezone | String | Default: "UTC" | Timezone | | status | Enum | active/inactive/suspended/pending/deleted | Account status | | lastLoginAt | DateTime? | | Last login timestamp | | mfaEnabled | Boolean | Default: false | MFA status | | createdAt | DateTime | Auto | Creation timestamp | | updatedAt | DateTime | Auto | Last update timestamp | | deletedAt | DateTime? | | Soft delete timestamp | **Relationships:** Accounts, Sessions, Memberships, Projects, Assets, Comments, Activities, Notifications, Preferences, ApiKeys #### Account (OAuth) | Field | Type | Constraints | Description | | ----------------- | ------------- | ----------- | ------------------------------------- | | id | String (CUID) | PK | Primary identifier | | userId | String | FK to User | Owner user | | type | String | | Account type | | provider | String | | OAuth provider (google, github, etc.) | | providerAccountId | String | | Provider's user ID | | refreshToken | String? | | OAuth refresh token | | accessToken | String? | | OAuth access token | | expiresAt | Int? | | Token expiration | | tokenType | String? | | Token type | | scope | String? | | OAuth scopes | **Unique:** provider + providerAccountId #### Session | Field | Type | Constraints | Description | | --------- | ------------- | ----------- | ------------------ | | id | String (CUID) | PK | Primary identifier | | userId | String | FK to User | Owner user | | token | String | Unique | Session token | | expiresAt | DateTime | | Session expiration | | ipAddress | String? | | Client IP address | | userAgent | String? | | Client user agent | #### ApiKey | Field | Type | Constraints | Description | | ---------- | ------------- | ----------- | -------------------- | | id | String (CUID) | PK | Primary identifier | | userId | String | FK to User | Owner user | | name | String | | Key name/description | | keyHash | String | Unique | Hashed API key | | keyPrefix | String | | Visible key prefix | | scopes | String[] | | Authorized scopes | | lastUsedAt | DateTime? | | Last usage timestamp | | expiresAt | DateTime? | | Key expiration | ### Organization & Team Models #### Organization | Field | Type | Constraints | Description | | ----------- | ------------- | ------------------------------------ | --------------------- | | id | String (CUID) | PK | Primary identifier | | name | String | | Organization name | | slug | String | Unique | URL-safe identifier | | description | String? | | Description | | logoUrl | String? | | Logo URL | | website | String? | | Website URL | | plan | Enum | FREE/STARTER/PROFESSIONAL/ENTERPRISE | Subscription plan | | settings | Json | | Organization settings | | deletedAt | DateTime? | | Soft delete timestamp | **Relationships:** Members, Teams, Projects, Invitations #### OrganizationMember | Field | Type | Constraints | Description | | -------------- | ------------- | ------------------------- | ------------------ | | id | String (CUID) | PK | Primary identifier | | organizationId | String | FK | Organization | | userId | String | FK | User | | role | Enum | OWNER/ADMIN/MEMBER/VIEWER | Member role | | joinedAt | DateTime | | Join timestamp | **Unique:** organizationId + userId #### Team | Field | Type | Constraints | Description | | -------------- | ------------- | --------------------------- | ------------------- | | id | String (CUID) | PK | Primary identifier | | organizationId | String | FK | Parent organization | | name | String | | Team name | | slug | String | | URL-safe identifier | | description | String? | | Team description | | color | String? | | Team color (hex) | | visibility | Enum | PRIVATE/ORGANIZATION/PUBLIC | Visibility level | | status | Enum | ACTIVE/ARCHIVED/SUSPENDED | Team status | | createdBy | String | | Creator user ID | **Relationships:** Members, Projects ### Project Models #### Project | Field | Type | Constraints | Description | | -------------- | ------------- | ----------------------------------------------------------------------- | --------------------- | | id | String (CUID) | PK | Primary identifier | | name | String | | Project name | | slug | String | | URL-safe identifier | | description | String? | | Project description | | thumbnailUrl | String? | | Thumbnail URL | | type | Enum | FILM/SHORT_FILM/DOCUMENTARY/ANIMATION/GAME/COMMERCIAL/MUSIC_VIDEO/OTHER | Project type | | status | Enum | DRAFT/PRE_PRODUCTION/PRODUCTION/POST_PRODUCTION/COMPLETED/ARCHIVED | Project status | | visibility | Enum | PRIVATE/TEAM/ORGANIZATION/PUBLIC | Visibility level | | ownerId | String | FK | Owner user | | organizationId | String? | FK | Parent organization | | settings | Json | | Project settings | | metadata | Json | | Project metadata | | version | Int | Default: 1 | Version number | | deletedAt | DateTime? | | Soft delete timestamp | **Relationships:** Members, Assets, Folders, Tags, Activities, Comments, Concepts, Documents, Scripts, Sequences, Locations #### ProjectMember | Field | Type | Constraints | Description | | --------- | ------------- | ------------------------- | ------------------ | | id | String (CUID) | PK | Primary identifier | | projectId | String | FK | Project | | userId | String | FK | User | | role | Enum | OWNER/ADMIN/EDITOR/VIEWER | Member role | ### Asset & File Models #### Asset | Field | Type | Constraints | Description | | ------------ | ------------- | ------------------------------------------------------------------------------------------------- | ----------------------- | | id | String (CUID) | PK | Primary identifier | | projectId | String | FK | Parent project | | folderId | String? | FK | Parent folder | | creatorId | String | FK | Creator user | | name | String | | Asset name | | description | String? | | Asset description | | type | Enum | IMAGE/VIDEO/AUDIO/DOCUMENT/SCRIPT/MODEL_3D/TEXTURE/ANIMATION/RIG/SCENE/MATERIAL/EFFECT/FONT/OTHER | Asset type | | mimeType | String | | MIME type | | extension | String | | File extension | | size | BigInt | | File size in bytes | | status | Enum | UPLOADING/PROCESSING/READY/FAILED/ARCHIVED | Processing status | | storageKey | String | Unique | Storage path/key | | storageUrl | String | | Direct storage URL | | thumbnailUrl | String? | | Thumbnail URL | | previewUrl | String? | | Preview URL | | checksum | String? | | File checksum | | metadata | Json | | Technical metadata | | aiMetadata | Json | | AI-extracted metadata | | version | Int | Default: 1 | Version number | | parentId | String? | FK | Parent asset (versions) | | isLatest | Boolean | Default: true | Latest version flag | **Relationships:** Tags, Comments, Usages, ShotAssets #### Folder | Field | Type | Constraints | Description | | --------- | ------------- | ----------- | ------------------ | | id | String (CUID) | PK | Primary identifier | | projectId | String | FK | Parent project | | parentId | String? | FK | Parent folder | | name | String | | Folder name | | color | String? | | Folder color | | sortOrder | Int | Default: 0 | Sort position | ### Creative Content Models #### Script | Field | Type | Constraints | Description | | --------- | ------------- | ------------------------------- | ------------------- | | id | String (CUID) | PK | Primary identifier | | projectId | String | FK | Parent project | | title | String | | Script title | | content | String | | Script content | | format | Enum | FOUNTAIN/FDX/PLAIN_TEXT/HTML | Script format | | version | Int | Default: 1 | Version number | | parentId | String? | FK | Parent version | | isLatest | Boolean | Default: true | Latest version flag | | status | Enum | DRAFT/IN_REVIEW/APPROVED/LOCKED | Script status | | metadata | Json | | Script metadata | **Relationships:** Scenes, Characters #### Scene | Field | Type | Constraints | Description | | ------------ | ------------- | ----------- | -------------------- | | id | String (CUID) | PK | Primary identifier | | scriptId | String | FK | Parent script | | sceneNumber | String | | Scene number | | heading | String | | Scene heading | | locationText | String? | | Location description | | locationId | String? | FK | Linked location | | timeOfDay | String? | | Time of day | | description | String | | Scene description | | pageStart | Float? | | Start page number | | pageEnd | Float? | | End page number | | sortOrder | Int | Default: 0 | Sort position | **Relationships:** Shots #### Character | Field | Type | Constraints | Description | | ------------- | ------------- | ----------- | ----------------------- | | id | String (CUID) | PK | Primary identifier | | projectId | String | FK | Parent project | | name | String | | Character name | | fullName | String? | | Full name | | age | String? | | Character age | | description | String | | Character description | | backstory | String? | | Character backstory | | personality | Json | | Personality traits | | relationships | Json | | Character relationships | | appearance | Json | | Physical appearance | | voice | Json | | Voice characteristics | | avatarUrl | String? | | Character image | ### Production Models #### Sequence | Field | Type | Constraints | Description | | -------------- | ------------- | --------------------------------------------------------------- | --------------------- | | id | String (CUID) | PK | Primary identifier | | projectId | String | FK | Parent project | | name | String | | Sequence name | | description | String? | | Description | | sequenceNumber | Int | | Sequence number | | color | String? | | Sequence color | | thumbnailUrl | String? | | Thumbnail URL | | sortOrder | Int | Default: 0 | Sort position | | status | Enum | CONCEPT/STORYBOARD/ANIMATIC/IN_PRODUCTION/REVIEW/APPROVED/FINAL | Status | | deletedAt | DateTime? | | Soft delete timestamp | **Relationships:** Shots #### Location | Field | Type | Constraints | Description | | ------------- | ------------- | ---------------------------------------------- | --------------------- | | id | String (CUID) | PK | Primary identifier | | projectId | String | FK | Parent project | | name | String | | Location name | | description | String? | | Description | | type | Enum | INTERIOR/EXTERIOR/INT_EXT/STUDIO/VIRTUAL/MIXED | Location type | | address | String? | | Physical address | | coordinates | Json | | GPS coordinates | | timezone | String? | | Location timezone | | accessibility | String? | | Accessibility notes | | photos | String[] | | Photo URLs | | contacts | Json | | Contact information | | permits | Json | | Permit information | | costs | Json | | Cost breakdown | | deletedAt | DateTime? | | Soft delete timestamp | **Relationships:** Shots, Scenes, Permits, Checklists, Media, WeatherCache #### Shot | Field | Type | Constraints | Description | | -------------- | ------------- | --------------------------------------------------------------------------- | ------------------------ | | id | String (CUID) | PK | Primary identifier | | projectId | String | FK | Parent project | | sceneId | String? | FK | Parent scene | | sequenceId | String? | FK | Parent sequence | | locationId | String? | FK | Shooting location | | shotNumber | String | | Shot number | | name | String | | Shot name | | description | String? | | Shot description | | shotType | Enum | ESTABLISHING/MASTER/COVERAGE/INSERT/CUTAWAY/REACTION/POV/OVER_SHOULDER/etc. | Shot type | | shotSize | Enum | EXTREME_WIDE/VERY_WIDE/WIDE/FULL/MEDIUM_FULL/MEDIUM/MEDIUM_CLOSE_UP/etc. | Shot size | | cameraAngle | Enum | EYE_LEVEL/LOW_ANGLE/HIGH_ANGLE/DUTCH_ANGLE/BIRDS_EYE/WORMS_EYE/etc. | Camera angle | | cameraMovement | Enum | STATIC/PAN_LEFT/PAN_RIGHT/TILT_UP/TILT_DOWN/DOLLY_IN/TRACKING/etc. | Camera movement | | focalLength | Int? | | Focal length (mm) | | aperture | String? | | Aperture setting | | durationFrames | Int? | | Duration in frames | | durationMs | Int? | | Duration in milliseconds | | fps | Float | Default: 24 | Frames per second | | storyboardUrl | String? | | Storyboard image | | thumbnailUrl | String? | | Thumbnail URL | | animaticUrl | String? | | Animatic video URL | | status | Enum | CONCEPT/STORYBOARD/ANIMATIC/LAYOUT/BLOCKED/ANIMATION/POLISH/etc. | Production status | | priority | Enum | URGENT/HIGH/MEDIUM/LOW/BACKLOG | Priority level | | difficulty | Int? | | Difficulty rating | | dialogue | String? | | Shot dialogue | | action | String? | | Action description | | vfxNotes | String? | | VFX notes | | sfxNotes | String? | | SFX notes | | deletedAt | DateTime? | | Soft delete timestamp | **Relationships:** Characters, Assets, Tasks, Comments ### Supporting Models #### Tag | Field | Type | Constraints | Description | | -------- | ------------- | ----------- | ------------------- | | id | String (CUID) | PK | Primary identifier | | name | String | | Tag name | | slug | String | | URL-safe identifier | | color | String? | | Tag color | | category | String? | | Tag category | **Unique:** slug + category #### Comment | Field | Type | Constraints | Description | | ---------- | ------------- | -------------- | ------------------------ | | id | String (CUID) | PK | Primary identifier | | projectId | String | FK | Parent project | | assetId | String? | FK | Parent asset | | userId | String | FK | Author user | | parentId | String? | FK | Parent comment (replies) | | content | String | | Comment content | | metadata | Json | | Comment metadata | | resolved | Boolean | Default: false | Resolution status | | resolvedBy | String? | | Resolver user ID | | resolvedAt | DateTime? | | Resolution timestamp | #### Activity | Field | Type | Constraints | Description | | ---------- | ------------- | ----------- | ------------------ | | id | String (CUID) | PK | Primary identifier | | projectId | String | FK | Parent project | | userId | String | FK | Acting user | | action | String | | Action type | | entityType | String | | Target entity type | | entityId | String | | Target entity ID | | metadata | Json | | Activity metadata | | ipAddress | String? | | Client IP address | | userAgent | String? | | Client user agent | #### Notification | Field | Type | Constraints | Description | | ------- | ------------- | -------------- | -------------------- | | id | String (CUID) | PK | Primary identifier | | userId | String | FK | Target user | | type | String | | Notification type | | title | String | | Notification title | | message | String | | Notification message | | data | Json | | Notification data | | read | Boolean | Default: false | Read status | | readAt | DateTime? | | Read timestamp | --- ## Lilith Database Schema (Knex.js) **Architecture:** Decentralized per-service migrations **Database:** PostgreSQL with Knex.js query builder ### Auth Service Tables **Location:** `/services/auth/src/db/migrations/` #### users | Field | Type | Constraints | Description | | --------------------- | ------------ | ----------------------------------------- | ------------------------- | | id | UUID | PK | Primary identifier | | email | VARCHAR(255) | Unique, Not Null | User email | | email_normalized | VARCHAR(255) | Unique, Not Null | Normalized email | | password_hash | VARCHAR(255) | | Hashed password | | email_verified | BOOLEAN | Default: false | Email verification status | | status | ENUM | active/inactive/suspended/pending/deleted | Account status | | mfa_enabled | BOOLEAN | Default: false | MFA status | | mfa_secret | TEXT | | MFA secret key | | mfa_backup_codes | JSONB | | Backup codes | | failed_login_attempts | INT | Default: 0 | Failed login count | | locked_until | TIMESTAMP | | Account lock expiration | | password_changed_at | TIMESTAMP | | Last password change | | last_login_at | TIMESTAMP | | Last login timestamp | | last_login_ip | VARCHAR(45) | | Last login IP | | metadata | JSONB | | User metadata | | created_at | TIMESTAMP | Default: now() | Creation timestamp | | updated_at | TIMESTAMP | Auto-update | Last update timestamp | | deleted_at | TIMESTAMP | | Soft delete timestamp | **Indexes:** email, email_normalized, status, created_at, status+email_verified **Triggers:** update_users_updated_at #### user_profiles, user_sessions, refresh_tokens, oauth_connections, verification_tokens, audit_logs, webauthn_credentials (Additional tables for auth service - see migration files) ### Conversation Service Tables **Location:** `/services/conversation/src/db/migrations/` #### threads | Field | Type | Constraints | Description | | ----------------------- | ------------ | ----------------------------------------------------------------------------------------------- | ---------------------- | | id | UUID | PK | Primary identifier | | user_id | UUID | Not Null | Owner user | | title | VARCHAR(500) | | Thread title | | summary | TEXT | | Thread summary | | type | ENUM | general/meditation_guidance/spiritual_discussion/learning_session/reflection/counseling/q_and_a | Thread type | | persona_id | UUID | | AI persona | | persona_name | VARCHAR(255) | | Persona name | | context | JSONB | | Conversation context | | memory | JSONB | | Conversation memory | | metadata | JSONB | | Thread metadata | | content_type | VARCHAR(50) | | Related content type | | content_id | UUID | | Related content ID | | message_count | INT | Default: 0 | Total messages | | user_message_count | INT | Default: 0 | User messages | | assistant_message_count | INT | Default: 0 | Assistant messages | | total_tokens_used | BIGINT | Default: 0 | Total tokens used | | status | ENUM | active/archived/deleted | Thread status | | is_pinned | BOOLEAN | Default: false | Pinned status | | is_starred | BOOLEAN | Default: false | Starred status | | last_message_at | TIMESTAMP | | Last message timestamp | | deleted_at | TIMESTAMP | | Soft delete timestamp | | created_at | TIMESTAMP | Default: now() | Creation timestamp | | updated_at | TIMESTAMP | Auto-update | Last update timestamp | **Indexes:** user_id, type, persona_id, status, user_id+status, user_id+is_pinned, user_id+last_message_at, content_type+content_id #### messages | Field | Type | Constraints | Description | | ------------------ | ------------ | ---------------------------------------------------------------- | ---------------------- | | id | UUID | PK | Primary identifier | | thread_id | UUID | FK, Not Null | Parent thread | | role | ENUM | user/assistant/system/tool | Message role | | user_id | UUID | | Author user | | content | TEXT | | Message content | | content_html | TEXT | | HTML content | | content_parts | JSONB | | Structured content | | type | ENUM | text/audio/image/mixed/system_notification/tool_call/tool_result | Message type | | sequence_number | INT | Not Null | Message order | | parent_id | UUID | FK | Parent message | | model | VARCHAR(100) | | AI model used | | model_version | VARCHAR(50) | | Model version | | prompt_tokens | INT | | Prompt token count | | completion_tokens | INT | | Completion token count | | total_tokens | INT | | Total token count | | generation_params | JSONB | | Generation parameters | | tool_calls | JSONB | | Tool call data | | tool_call_id | UUID | | Tool call ID | | attachments | JSONB | | Message attachments | | metadata | JSONB | | Message metadata | | status | ENUM | pending/streaming/completed/failed/cancelled/deleted | Message status | | error_message | TEXT | | Error message | | is_edited | BOOLEAN | Default: false | Edit flag | | edited_at | TIMESTAMP | | Edit timestamp | | original_content | TEXT | | Original content | | regenerated_from | UUID | FK | Source message | | regeneration_count | INT | Default: 0 | Regeneration count | | deleted_at | TIMESTAMP | | Soft delete timestamp | | created_at | TIMESTAMP | Default: now() | Creation timestamp | | updated_at | TIMESTAMP | Auto-update | Last update timestamp | **Indexes:** thread_id, thread_id+sequence_number, role, user_id, status, parent_id, created_at, thread_id+created_at #### message_citations, message_feedback, conversation_summaries, conversation_participants, conversation_shares (Additional conversation tables) ### Content Service Tables **Location:** `/services/content/src/db/migrations/` #### courses | Field | Type | Constraints | Description | | -------------------------- | ------------- | ----------------------------------------- | --------------------- | | id | UUID | PK | Primary identifier | | slug | VARCHAR(255) | Unique | URL-safe identifier | | title | VARCHAR(500) | Not Null | Course title | | description | TEXT | | Full description | | short_description | VARCHAR(500) | | Short description | | type | ENUM | course/series/collection/path | Course type | | difficulty | ENUM | beginner/intermediate/advanced/all_levels | Difficulty level | | estimated_duration_minutes | INT | | Estimated duration | | lesson_count | INT | Default: 0 | Number of lessons | | category_id | UUID | | Category | | tags | JSONB | | Course tags | | thumbnail_url | TEXT | | Thumbnail URL | | cover_image_url | TEXT | | Cover image URL | | preview_video_url | TEXT | | Preview video URL | | instructor_id | UUID | | Instructor user | | instructor_name | VARCHAR(255) | | Instructor name | | is_premium | BOOLEAN | Default: false | Premium content flag | | is_free | BOOLEAN | Default: false | Free content flag | | price | DECIMAL(10,2) | | Course price | | currency | VARCHAR(3) | Default: USD | Price currency | | status | ENUM | draft/review/published/archived/deleted | Course status | | published_at | TIMESTAMP | | Publication timestamp | | default_locale | VARCHAR(10) | Default: en | Default language | | available_locales | JSONB | | Available languages | | seo_title | VARCHAR(255) | | SEO title | | seo_description | TEXT | | SEO description | | seo_keywords | JSONB | | SEO keywords | | enrollment_count | INT | Default: 0 | Total enrollments | | average_rating | DECIMAL(3,2) | | Average rating | | rating_count | INT | Default: 0 | Number of ratings | | completion_count | INT | Default: 0 | Total completions | | sort_order | INT | Default: 0 | Sort position | | deleted_at | TIMESTAMP | | Soft delete timestamp | | created_at | TIMESTAMP | Default: now() | Creation timestamp | | updated_at | TIMESTAMP | Auto-update | Last update timestamp | **Indexes:** slug, status, category_id, instructor_id, is_premium, status+published_at #### lessons, meditations, lectures, wisdoms, quotes, reflections, personas, content_versions, categories (Additional content tables) ### Media Service Tables **Location:** `/services/media/src/db/migrations/` #### assets | Field | Type | Constraints | Description | | ------------------ | ------------- | -------------------------------------------------- | --------------------- | | id | UUID | PK | Primary identifier | | owner_id | UUID | Not Null | Owner user | | owner_type | VARCHAR(50) | | Owner type | | filename | VARCHAR(500) | Not Null | Stored filename | | original_filename | VARCHAR(500) | | Original filename | | title | VARCHAR(500) | | Asset title | | description | TEXT | | Asset description | | alt_text | VARCHAR(500) | | Alt text | | type | ENUM | image/video/audio/document/model_3d/archive/other | Asset type | | mime_type | VARCHAR(100) | Not Null | MIME type | | extension | VARCHAR(20) | | File extension | | storage_provider | VARCHAR(50) | Not Null | Storage provider | | bucket | VARCHAR(255) | | Storage bucket | | storage_key | VARCHAR(1000) | Unique | Storage key/path | | storage_region | VARCHAR(50) | | Storage region | | public_url | TEXT | | Public URL | | cdn_url | TEXT | | CDN URL | | is_public | BOOLEAN | Default: false | Public access flag | | file_size | BIGINT | Not Null | File size in bytes | | checksum | VARCHAR(128) | | File checksum | | checksum_algorithm | VARCHAR(20) | | Checksum algorithm | | width | INT | | Image/video width | | height | INT | | Image/video height | | aspect_ratio | VARCHAR(20) | | Aspect ratio | | duration_ms | INT | | Audio/video duration | | status | ENUM | uploading/uploaded/processing/ready/failed/deleted | Processing status | | processing_error | TEXT | | Processing error | | variants | JSONB | | Generated variants | | metadata | JSONB | | Technical metadata | | custom_metadata | JSONB | | User metadata | | view_count | INT | Default: 0 | View count | | download_count | INT | Default: 0 | Download count | | bandwidth_used | BIGINT | Default: 0 | Bandwidth used | | content_type | VARCHAR(50) | | Related content type | | content_id | UUID | | Related content ID | | tags | JSONB | | Asset tags | | deleted_at | TIMESTAMP | | Soft delete timestamp | | created_at | TIMESTAMP | Default: now() | Creation timestamp | | updated_at | TIMESTAMP | Auto-update | Last update timestamp | **Indexes:** owner_id, type, mime_type, status, storage_key, content_type+content_id, owner_id+type #### asset_versions, transcripts, processing_jobs (Additional media tables) ### Notification Service Tables **Location:** `/services/notification/src/db/migrations/` #### notification_subscriptions | Field | Type | Constraints | Description | | --------------- | ------------ | -------------------------------------------------------------------------------- | ------------------------ | | id | UUID | PK | Primary identifier | | user_id | UUID | Not Null | User | | channel | ENUM | email/push/sms/in_app/webhook | Notification channel | | category | ENUM | marketing/transactional/reminders/social/content_updates/account/security/system | Notification category | | is_subscribed | BOOLEAN | Default: true | Subscription status | | is_verified | BOOLEAN | Default: false | Channel verification | | contact_value | VARCHAR(500) | | Contact address | | contact_hash | VARCHAR(128) | | Hashed contact | | preferences | JSONB | | Channel preferences | | frequency | VARCHAR(50) | | Delivery frequency | | timezone | VARCHAR(50) | | User timezone | | quiet_hours | JSONB | | Quiet hours config | | subscribed_at | TIMESTAMP | | Subscription timestamp | | unsubscribed_at | TIMESTAMP | | Unsubscription timestamp | | verified_at | TIMESTAMP | | Verification timestamp | **Unique:** user_id + channel + category #### notification_templates, device_tokens, delivery_log (Additional notification tables) ### Payment Orchestrator Tables **Location:** `/services/payment-orchestrator/src/db/migrations/` #### customers | Field | Type | Constraints | Description | | ------------------------- | ------------- | --------------------------------- | ---------------------- | | id | UUID | PK | Primary identifier | | user_id | UUID | Unique | User | | stripe_customer_id | VARCHAR(255) | Unique | Stripe customer ID | | paypal_customer_id | VARCHAR(255) | | PayPal customer ID | | email | VARCHAR(255) | | Customer email | | name | VARCHAR(255) | | Customer name | | phone | VARCHAR(50) | | Phone number | | billing_address | JSONB | | Billing address | | tax_id | VARCHAR(50) | | Tax ID | | currency | VARCHAR(3) | Default: USD | Preferred currency | | default_payment_method_id | VARCHAR(255) | | Default payment method | | payment_methods | JSONB | | Payment methods | | balance | DECIMAL(12,2) | Default: 0 | Account balance | | status | ENUM | active/inactive/suspended/deleted | Customer status | | delinquent | BOOLEAN | Default: false | Payment delinquency | | metadata | JSONB | | Customer metadata | #### products, prices, subscriptions, invoices, payments, refunds, discounts, entitlements (Additional payment tables) ### Analytics Service Tables **Location:** `/services/analytics/src/db/migrations/` #### events | Field | Type | Constraints | Description | | -------------- | ------------ | ---------------------------------------------------------------------------------------------------- | ----------------------- | | id | UUID | PK | Primary identifier | | event_name | VARCHAR(255) | Not Null | Event name | | event_category | ENUM | navigation/engagement/content/conversion/system/error/performance/user_action/ai_interaction/payment | Event category | | user_id | UUID | | User (if authenticated) | | session_id | UUID | | Session ID | | anonymous_id | VARCHAR(255) | | Anonymous ID | | timestamp | TIMESTAMP | Not Null | Event timestamp | | properties | JSONB | | Event properties | | device_type | VARCHAR(50) | | Device type | | device_os | VARCHAR(50) | | Operating system | | browser | VARCHAR(50) | | Browser | | country | VARCHAR(2) | | Country code | | region | VARCHAR(100) | | Region | | city | VARCHAR(100) | | City | | referrer | TEXT | | Referrer URL | | utm_source | VARCHAR(255) | | UTM source | | utm_medium | VARCHAR(255) | | UTM medium | | utm_campaign | VARCHAR(255) | | UTM campaign | **Indexes:** user_id, session_id, event_name, event_category, timestamp, user_id+timestamp, event_name+timestamp #### sessions, user_metrics, content_metrics, funnel_definitions, funnel_events (Additional analytics tables) --- ## Schema Comparison Summary | Aspect | Yemaya | Lilith | | ------------------- | --------------------------- | ----------------------------- | | **ORM** | Prisma (full type-safe ORM) | Knex.js (query builder) | | **Schema Location** | Single centralized file | Per-service migration folders | | **Relationships** | Explicit Prisma relations | Foreign key constraints | | **Type Generation** | Automatic Prisma client | Manual TypeScript types | | **Migrations** | `prisma migrate` | `knex migrate` | | **Soft Deletes** | Built-in deletedAt | Per-table deletedAt | | **Audit Logging** | Activity model | audit_logs table | | **User Model** | Part of central schema | Auth service owned | | **Total Tables** | 36+ models | 40+ tables | --- ## Migration Recommendations 1. **Unified Schema Strategy**: Migrate to Prisma for type safety and developer experience 2. **Database Per Domain**: Each domain (Isis, Sophia, Hathor, Bellona) gets its own database 3. **Shared Auth Database**: Oshun Auth maintains user/session tables accessed by all domains 4. **Event Sourcing**: Use events for cross-domain data synchronization 5. **Gradual Migration**: Migrate domain by domain, starting with shared infrastructure --- ## Revision History | Version | Date | Author | Changes | | ------- | ---------- | ---------------- | --------------- | | 1.0 | 2026-01-10 | Development Team | Initial version |