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 |
| 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 |
| 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 | |
| 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#
- Unified Schema Strategy: Migrate to Prisma for type safety and developer experience
- Database Per Domain: Each domain (Isis, Sophia, Hathor, Bellona) gets its own database
- Shared Auth Database: Oshun Auth maintains user/session tables accessed by all domains
- Event Sourcing: Use events for cross-domain data synchronization
- 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 |