This document describes the database schema for all Lilith platform services.
Overview#
The Lilith platform uses PostgreSQL with the following extensions:
pgvector- Vector similarity search for AI/ML featurespg_trgm- Trigram matching for full-text search
Each service maintains its own set of tables with a dedicated migrations directory.
Services & Tables#
| Service | Tables | Description |
|---|---|---|
| auth | 11 | Authentication, users, sessions, OAuth |
| content | 13 | Courses, lessons, meditations, lectures, wisdoms |
| conversation | 5 | Chat threads, messages, citations, feedback |
| media | 5 | Assets, versions, transcripts, metadata |
| notification | 4 | Subscriptions, templates, device tokens, delivery |
| user-preferences | 5 | Preferences, favorites, history, achievements |
| payment-orchestrator | 9 | Customers, products, subscriptions, invoices |
Auth Service#
users#
Primary user account table.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK, default uuid_generate_v4() | Primary key |
| VARCHAR(255) | NOT NULL, UNIQUE | User email address | |
| email_normalized | VARCHAR(255) | NOT NULL, UNIQUE | Lowercase normalized email |
| password_hash | VARCHAR(255) | Argon2 password hash | |
| status | ENUM | NOT NULL, default 'pending' | pending, active, suspended, deleted |
| role | ENUM | NOT NULL, default 'user' | user, admin, moderator, creator |
| email_verified | BOOLEAN | NOT NULL, default false | Email verification status |
| failed_login_attempts | INTEGER | NOT NULL, default 0 | Failed login counter |
| lockout_until | TIMESTAMPTZ | Account lockout expiry | |
| last_login_at | TIMESTAMPTZ | Last successful login | |
| created_at | TIMESTAMPTZ | NOT NULL, default now() | Record creation time |
| updated_at | TIMESTAMPTZ | NOT NULL, default now() | Last update time |
Check Constraints:
chk_users_email_format- Email must be valid formatchk_users_email_normalized_lowercase- Normalized email must be lowercasechk_users_failed_login_attempts_non_negative- Failed attempts >= 0
user_profiles#
Extended user profile information.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Primary key |
| user_id | UUID | NOT NULL, FK -> users | User reference |
| display_name | VARCHAR(255) | Public display name | |
| avatar_url | VARCHAR(1000) | Profile picture URL | |
| bio | TEXT | User biography | |
| timezone | VARCHAR(50) | User timezone | |
| preferred_locale | VARCHAR(10) | Language preference | |
| date_of_birth | DATE | Birth date | |
| country | VARCHAR(2) | ISO country code |
user_sessions#
Active login sessions.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Session ID |
| user_id | UUID | NOT NULL, FK -> users | User reference |
| token_hash | VARCHAR(255) | NOT NULL | Hashed session token |
| device_info | JSONB | Device metadata | |
| ip_address | VARCHAR(45) | Client IP address | |
| user_agent | TEXT | Browser user agent | |
| expires_at | TIMESTAMPTZ | NOT NULL | Session expiry |
| created_at | TIMESTAMPTZ | NOT NULL | Session start time |
Check Constraints:
chk_user_sessions_expires_after_created- expires_at > created_atchk_user_sessions_ip_format- Valid IP format
refresh_tokens#
JWT refresh tokens for session renewal.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Token ID |
| user_id | UUID | NOT NULL, FK -> users | User reference |
| session_id | UUID | FK -> user_sessions | Associated session |
| token_hash | VARCHAR(255) | NOT NULL | Hashed token |
| family_id | UUID | Token rotation family | |
| expires_at | TIMESTAMPTZ | NOT NULL | Token expiry |
| revoked_at | TIMESTAMPTZ | Revocation time | |
| created_at | TIMESTAMPTZ | NOT NULL | Token creation time |
oauth_connections#
Third-party OAuth provider connections.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Connection ID |
| user_id | UUID | NOT NULL, FK -> users | User reference |
| provider | VARCHAR(50) | NOT NULL | OAuth provider name |
| provider_user_id | VARCHAR(255) | NOT NULL | External user ID |
| access_token | TEXT | Encrypted access token | |
| refresh_token | TEXT | Encrypted refresh token | |
| token_expires_at | TIMESTAMPTZ | Token expiry | |
| profile_data | JSONB | Provider profile data |
email_verification_tokens#
Email verification tokens.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Token ID |
| user_id | UUID | NOT NULL, FK -> users | User reference |
| VARCHAR(255) | NOT NULL | Email to verify | |
| token_hash | VARCHAR(255) | NOT NULL | Hashed token |
| expires_at | TIMESTAMPTZ | NOT NULL | Token expiry |
| used_at | TIMESTAMPTZ | Token usage time |
password_reset_tokens#
Password reset request tokens.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Token ID |
| user_id | UUID | NOT NULL, FK -> users | User reference |
| token_hash | VARCHAR(255) | NOT NULL | Hashed token |
| expires_at | TIMESTAMPTZ | NOT NULL | Token expiry |
| used_at | TIMESTAMPTZ | Token usage time |
magic_link_tokens#
Passwordless login magic links.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Token ID |
| VARCHAR(255) | NOT NULL | Target email | |
| token_hash | VARCHAR(255) | NOT NULL | Hashed token |
| expires_at | TIMESTAMPTZ | NOT NULL | Token expiry |
| used_at | TIMESTAMPTZ | Token usage time |
auth_audit_logs#
Security audit trail.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Log entry ID |
| user_id | UUID | FK -> users | User reference |
| event_type | VARCHAR(100) | NOT NULL | Event type |
| event_category | VARCHAR(50) | NOT NULL | Event category |
| ip_address | VARCHAR(45) | Client IP | |
| user_agent | TEXT | Browser info | |
| metadata | JSONB | Additional data | |
| created_at | TIMESTAMPTZ | NOT NULL | Event time |
webauthn_credentials#
FIDO2/WebAuthn passkey credentials.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Credential ID |
| user_id | UUID | NOT NULL, FK -> users | User reference |
| credential_id | BYTEA | NOT NULL | FIDO2 credential ID |
| public_key | BYTEA | NOT NULL | Public key |
| sign_count | INTEGER | NOT NULL, default 0 | Signature counter |
| device_name | VARCHAR(255) | Friendly device name | |
| transports | JSONB | Supported transports |
webauthn_challenges#
WebAuthn registration/authentication challenges.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Challenge ID |
| user_id | UUID | FK -> users | User reference |
| challenge | BYTEA | NOT NULL | Challenge bytes |
| type | ENUM | NOT NULL | registration, authentication |
| expires_at | TIMESTAMPTZ | NOT NULL | Challenge expiry |
Content Service#
courses#
Educational courses and programs.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Course ID |
| slug | VARCHAR(255) | NOT NULL, UNIQUE | URL-safe identifier |
| title | VARCHAR(500) | NOT NULL | Course title |
| description | TEXT | Course description | |
| instructor_id | UUID | Instructor user ID | |
| difficulty_level | ENUM | beginner, intermediate, advanced | |
| estimated_duration_minutes | INTEGER | Total duration | |
| lesson_count | INTEGER | NOT NULL, default 0 | Number of lessons |
| price | DECIMAL(10,2) | Price (null = free) | |
| currency | VARCHAR(3) | ISO currency code | |
| status | ENUM | NOT NULL, default 'draft' | draft, published, archived |
| average_rating | DECIMAL(2,1) | 0-5 star rating | |
| enrollment_count | INTEGER | NOT NULL, default 0 | Enrollments |
| completion_count | INTEGER | NOT NULL, default 0 | Completions |
| published_at | TIMESTAMPTZ | Publication date |
Check Constraints:
chk_courses_slug_format- URL-safe slug formatchk_courses_title_not_empty- Title cannot be emptychk_courses_duration_positive- Duration > 0 if setchk_courses_lesson_count_non_negative- Lesson count >= 0chk_courses_price_non_negative- Price >= 0 if setchk_courses_currency_format- 3-character ISO codechk_courses_rating_range- Rating between 0 and 5
lessons#
Individual course lessons.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Lesson ID |
| course_id | UUID | NOT NULL, FK -> courses | Parent course |
| slug | VARCHAR(255) | NOT NULL | Lesson slug |
| title | VARCHAR(500) | NOT NULL | Lesson title |
| content | TEXT | Lesson content | |
| duration_minutes | INTEGER | Lesson duration | |
| sort_order | INTEGER | NOT NULL, default 0 | Display order |
| media_id | UUID | Associated media | |
| status | ENUM | NOT NULL, default 'draft' | Status |
| view_count | INTEGER | NOT NULL, default 0 | View counter |
| completion_count | INTEGER | NOT NULL, default 0 | Completion counter |
meditations#
Guided meditation content.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Meditation ID |
| slug | VARCHAR(255) | NOT NULL, UNIQUE | URL-safe identifier |
| title | VARCHAR(500) | NOT NULL | Meditation title |
| description | TEXT | Description | |
| duration_minutes | INTEGER | NOT NULL | Duration in minutes |
| persona_id | UUID | FK -> personas | Guiding persona |
| media_id | UUID | Audio/video media | |
| theme | ENUM | calm, focus, sleep, stress, etc. | |
| status | ENUM | NOT NULL, default 'draft' | Status |
| play_count | INTEGER | NOT NULL, default 0 | Play counter |
| average_rating | DECIMAL(2,1) | 0-5 star rating |
lectures#
Educational lecture content.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Lecture ID |
| slug | VARCHAR(255) | NOT NULL, UNIQUE | URL-safe identifier |
| title | VARCHAR(500) | NOT NULL | Lecture title |
| description | TEXT | Description | |
| subject | VARCHAR(255) | NOT NULL | Subject area |
| speaker_id | UUID | Speaker user ID | |
| duration_minutes | INTEGER | Duration | |
| media_id | UUID | Associated media | |
| transcript | TEXT | Full transcript | |
| status | ENUM | NOT NULL, default 'draft' | Status |
| view_count | INTEGER | NOT NULL, default 0 | View counter |
| average_rating | DECIMAL(2,1) | 0-5 star rating |
wisdoms#
Wisdom teachings and insights.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Wisdom ID |
| slug | VARCHAR(255) | NOT NULL, UNIQUE | URL-safe identifier |
| title | VARCHAR(500) | NOT NULL | Wisdom title |
| content | TEXT | NOT NULL | Content text |
| source | VARCHAR(500) | Original source | |
| tradition | VARCHAR(100) | Spiritual tradition | |
| themes | JSONB | Theme tags | |
| status | ENUM | NOT NULL, default 'draft' | Status |
| view_count | INTEGER | NOT NULL, default 0 | View counter |
| share_count | INTEGER | NOT NULL, default 0 | Share counter |
quotes#
Inspirational quotes.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Quote ID |
| text | TEXT | NOT NULL | Quote text |
| author | VARCHAR(255) | NOT NULL | Quote author |
| source | VARCHAR(500) | Source attribution | |
| themes | JSONB | Theme tags | |
| view_count | INTEGER | NOT NULL, default 0 | View counter |
| share_count | INTEGER | NOT NULL, default 0 | Share counter |
reflections#
Journaling prompts and reflections.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Reflection ID |
| slug | VARCHAR(255) | NOT NULL, UNIQUE | URL-safe identifier |
| title | VARCHAR(500) | NOT NULL | Reflection title |
| prompt | TEXT | NOT NULL | Reflection prompt |
| suggested_duration_minutes | INTEGER | Suggested time | |
| themes | JSONB | Theme tags | |
| status | ENUM | NOT NULL, default 'draft' | Status |
| completion_count | INTEGER | NOT NULL, default 0 | Completions |
personas#
AI guide personas.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Persona ID |
| slug | VARCHAR(255) | NOT NULL, UNIQUE | URL-safe identifier |
| name | VARCHAR(255) | NOT NULL | Persona name |
| description | TEXT | Persona description | |
| avatar_url | VARCHAR(1000) | Avatar image | |
| voice_id | VARCHAR(100) | TTS voice ID | |
| voice_speed | DECIMAL(3,2) | Speech rate | |
| voice_pitch | DECIMAL(3,2) | Voice pitch | |
| personality_traits | JSONB | Personality config | |
| system_prompt | TEXT | LLM system prompt | |
| status | ENUM | NOT NULL, default 'draft' | Status |
| conversation_count | INTEGER | NOT NULL, default 0 | Conversations |
content_versions#
Content versioning for audit trail.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Version ID |
| content_id | UUID | NOT NULL | Content item ID |
| content_type | VARCHAR(50) | NOT NULL | course, lesson, etc. |
| version_number | INTEGER | NOT NULL | Version number |
| content_snapshot | JSONB | NOT NULL | Full content copy |
| change_summary | TEXT | Change description | |
| created_by | UUID | Author user ID | |
| created_at | TIMESTAMPTZ | NOT NULL | Version timestamp |
content_locales#
Localized content translations.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Locale entry ID |
| content_id | UUID | NOT NULL | Content item ID |
| content_type | VARCHAR(50) | NOT NULL | Content type |
| locale | VARCHAR(10) | NOT NULL | Locale code (en-US) |
| translated_fields | JSONB | NOT NULL | Translated content |
| translation_status | ENUM | pending, approved, etc. | |
| translation_quality_score | DECIMAL(3,2) | 0-1 quality score |
content_categories#
Hierarchical content categories.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Category ID |
| slug | VARCHAR(255) | NOT NULL, UNIQUE | URL-safe identifier |
| name | VARCHAR(255) | NOT NULL | Category name |
| description | TEXT | Description | |
| parent_id | UUID | FK -> content_categories | Parent category |
| depth | INTEGER | NOT NULL, default 0 | Hierarchy depth |
| path | LTREE | Materialized path | |
| course_count | INTEGER | NOT NULL, default 0 | Courses in category |
| total_content_count | INTEGER | NOT NULL, default 0 | All content count |
content_tags#
Content tagging system.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Tag ID |
| slug | VARCHAR(100) | NOT NULL, UNIQUE | URL-safe identifier |
| name | VARCHAR(100) | NOT NULL | Tag name |
| description | TEXT | Tag description | |
| usage_count | INTEGER | NOT NULL, default 0 | Usage count |
content_tag_assignments#
Many-to-many content-tag relationships.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Assignment ID |
| tag_id | UUID | NOT NULL, FK -> content_tags | Tag reference |
| content_id | UUID | NOT NULL | Content item ID |
| content_type | VARCHAR(50) | NOT NULL | Content type |
| relevance_score | DECIMAL(3,2) | 0-1 relevance |
Conversation Service#
threads#
Conversation threads.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Thread ID |
| user_id | UUID | NOT NULL | User reference |
| title | VARCHAR(500) | Thread title | |
| persona_id | UUID | AI persona | |
| content_id | UUID | Related content | |
| content_type | VARCHAR(50) | Content type | |
| status | ENUM | NOT NULL, default 'active' | active, archived, deleted |
| message_count | INTEGER | NOT NULL, default 0 | Total messages |
| user_message_count | INTEGER | NOT NULL, default 0 | User messages |
| assistant_message_count | INTEGER | NOT NULL, default 0 | AI messages |
| total_tokens_used | INTEGER | NOT NULL, default 0 | Token usage |
| last_message_at | TIMESTAMPTZ | Last activity |
Check Constraints:
chk_threads_message_counts_consistent- Sum of user + assistant <= total
messages#
Individual chat messages.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Message ID |
| thread_id | UUID | NOT NULL, FK -> threads | Parent thread |
| role | ENUM | NOT NULL | user, assistant, system |
| content | TEXT | NOT NULL | Message content |
| sequence_number | INTEGER | NOT NULL | Order in thread |
| parent_message_id | UUID | FK -> messages | Reply to message |
| model | VARCHAR(100) | LLM model used | |
| prompt_tokens | INTEGER | Input tokens | |
| completion_tokens | INTEGER | Output tokens | |
| total_tokens | INTEGER | Total tokens | |
| regeneration_count | INTEGER | NOT NULL, default 0 | Regenerations |
Check Constraints:
chk_messages_content_not_empty- Content cannot be emptychk_messages_sequence_positive- Sequence > 0chk_messages_prompt_tokens_non_negative- Tokens >= 0
message_citations#
Source citations in AI responses.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Citation ID |
| message_id | UUID | NOT NULL, FK -> messages | Parent message |
| citation_index | INTEGER | NOT NULL | Citation number |
| source_id | UUID | Source content ID | |
| content_type | VARCHAR(50) | Source type | |
| title | VARCHAR(500) | Source title | |
| url | VARCHAR(1000) | Source URL | |
| excerpt | TEXT | Cited excerpt | |
| relevance_score | DECIMAL(3,2) | 0-1 relevance | |
| confidence_score | DECIMAL(3,2) | 0-1 confidence | |
| start_offset | INTEGER | Start position | |
| end_offset | INTEGER | End position |
message_feedback#
User feedback on AI responses.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Feedback ID |
| message_id | UUID | NOT NULL, FK -> messages | Target message |
| user_id | UUID | NOT NULL | User reference |
| feedback_type | ENUM | NOT NULL | thumbs_up, thumbs_down, flag |
| rating_score | INTEGER | 1-5 rating | |
| comment | TEXT | User comment | |
| improvement_suggestion | TEXT | Improvement suggestion | |
| selection_start | INTEGER | Selected text start | |
| selection_end | INTEGER | Selected text end |
conversation_summaries#
AI-generated conversation summaries.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Summary ID |
| thread_id | UUID | NOT NULL, FK -> threads | Thread reference |
| summary | TEXT | NOT NULL | Summary text |
| key_points | JSONB | Key points array | |
| topics | JSONB | Discussed topics | |
| start_sequence | INTEGER | First message | |
| end_sequence | INTEGER | Last message | |
| message_count | INTEGER | NOT NULL | Messages covered |
| sentiment_score | DECIMAL(3,2) | -1 to 1 sentiment | |
| model | VARCHAR(100) | LLM model used | |
| tokens_used | INTEGER | Tokens consumed | |
| version | INTEGER | NOT NULL, default 1 | Summary version |
Media Service#
assets#
Media asset storage.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Asset ID |
| filename | VARCHAR(500) | NOT NULL | Stored filename |
| original_filename | VARCHAR(500) | NOT NULL | Original name |
| title | VARCHAR(500) | Display title | |
| description | TEXT | Description | |
| mime_type | VARCHAR(100) | NOT NULL | MIME type |
| file_size | BIGINT | NOT NULL | Size in bytes |
| bucket | VARCHAR(100) | NOT NULL | Storage bucket |
| storage_key | VARCHAR(1000) | NOT NULL | Storage path |
| width | INTEGER | Image/video width | |
| height | INTEGER | Image/video height | |
| duration_ms | INTEGER | Audio/video duration | |
| status | ENUM | NOT NULL, default 'processing' | Status |
| view_count | INTEGER | NOT NULL, default 0 | Views |
| download_count | INTEGER | NOT NULL, default 0 | Downloads |
| owner_id | UUID | NOT NULL | Owner user ID |
| owner_type | VARCHAR(50) | NOT NULL | user, system |
asset_versions#
Processed asset variants (thumbnails, transcodes).
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Version ID |
| asset_id | UUID | NOT NULL, FK -> assets | Parent asset |
| version_number | INTEGER | NOT NULL | Version number |
| variant_type | VARCHAR(50) | NOT NULL | original, thumbnail, transcode |
| filename | VARCHAR(500) | NOT NULL | Stored filename |
| mime_type | VARCHAR(100) | NOT NULL | MIME type |
| file_size | BIGINT | NOT NULL | Size in bytes |
| width | INTEGER | Variant width | |
| height | INTEGER | Variant height | |
| bitrate | INTEGER | Audio/video bitrate | |
| quality | VARCHAR(20) | low, medium, high, lossless |
transcripts#
Audio/video transcriptions.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Transcript ID |
| asset_id | UUID | NOT NULL, FK -> assets | Source asset |
| locale | VARCHAR(10) | NOT NULL | Transcript language |
| content | TEXT | NOT NULL | Full transcript |
| segments | JSONB | Timed segments | |
| confidence_score | DECIMAL(3,2) | 0-1 confidence | |
| transcription_service | VARCHAR(50) | Service used | |
| word_count | INTEGER | Total words | |
| processing_time_ms | INTEGER | Processing duration | |
| status | ENUM | NOT NULL | processing, completed, failed |
media_metadata#
Extended media metadata.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Metadata ID |
| asset_id | UUID | NOT NULL, FK -> assets | Parent asset |
| duration_seconds | DECIMAL(10,3) | Duration | |
| bit_rate | INTEGER | Overall bitrate | |
| video_codec | VARCHAR(50) | Video codec | |
| video_width | INTEGER | Video width | |
| video_height | INTEGER | Video height | |
| frame_rate | DECIMAL(6,3) | Frame rate | |
| audio_codec | VARCHAR(50) | Audio codec | |
| audio_sample_rate | INTEGER | Sample rate | |
| audio_channels | INTEGER | Channel count | |
| exif_data | JSONB | EXIF metadata | |
| gps_latitude | DECIMAL(9,6) | GPS latitude | |
| gps_longitude | DECIMAL(10,6) | GPS longitude | |
| nsfw_score | DECIMAL(3,2) | 0-1 NSFW score |
processing_jobs#
Media processing job queue.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Job ID |
| asset_id | UUID | NOT NULL, FK -> assets | Target asset |
| job_type | ENUM | NOT NULL | transcode, thumbnail, analyze |
| status | ENUM | NOT NULL, default 'pending' | Job status |
| priority | INTEGER | NOT NULL, default 0 | Priority level |
| progress | INTEGER | NOT NULL, default 0 | 0-100 progress |
| input_params | JSONB | Input parameters | |
| output_params | JSONB | Output results | |
| error_message | TEXT | Error details | |
| attempt_count | INTEGER | NOT NULL, default 0 | Retry count |
| max_attempts | INTEGER | NOT NULL, default 3 | Max retries |
| scheduled_at | TIMESTAMPTZ | Scheduled time | |
| started_at | TIMESTAMPTZ | Processing start | |
| completed_at | TIMESTAMPTZ | Completion time | |
| duration_ms | INTEGER | Processing time | |
| worker_id | VARCHAR(100) | Processing worker |
Notification Service#
notification_subscriptions#
User notification preferences.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Subscription ID |
| user_id | UUID | NOT NULL | User reference |
| channel | ENUM | NOT NULL | email, push, sms, in_app |
| category | ENUM | NOT NULL | marketing, transactional, etc. |
| is_subscribed | BOOLEAN | NOT NULL, default true | Subscription status |
| is_verified | BOOLEAN | NOT NULL, default false | Verified contact |
| contact_value | VARCHAR(500) | Email, phone, etc. | |
| frequency | VARCHAR(50) | instant, daily, weekly | |
| timezone | VARCHAR(50) | User timezone | |
| quiet_hours | JSONB | Do not disturb times | |
| subscribed_at | TIMESTAMPTZ | Subscription date | |
| unsubscribed_at | TIMESTAMPTZ | Unsubscription date |
notification_templates#
Notification message templates.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Template ID |
| slug | VARCHAR(255) | NOT NULL, UNIQUE | Template identifier |
| name | VARCHAR(255) | NOT NULL | Template name |
| channel | ENUM | NOT NULL | Target channel |
| category | ENUM | NOT NULL | Notification category |
| locale | VARCHAR(10) | NOT NULL, default 'en' | Template language |
| subject | VARCHAR(500) | Email/push title | |
| body | TEXT | Plain text body | |
| body_html | TEXT | HTML body | |
| variables | JSONB | Template variables | |
| status | ENUM | NOT NULL, default 'draft' | Status |
| version | INTEGER | NOT NULL, default 1 | Template version |
device_tokens#
Push notification device registrations.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Token ID |
| user_id | UUID | NOT NULL | User reference |
| token | TEXT | NOT NULL | Device token |
| token_type | VARCHAR(50) | NOT NULL | fcm, apns, web_push |
| platform | ENUM | NOT NULL | ios, android, web |
| device_name | VARCHAR(255) | Device name | |
| is_active | BOOLEAN | NOT NULL, default true | Token active |
| failure_count | INTEGER | NOT NULL, default 0 | Failed sends |
| last_used_at | TIMESTAMPTZ | Last notification |
delivery_log#
Notification delivery history.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Log entry ID |
| user_id | UUID | NOT NULL | User reference |
| template_id | UUID | FK -> notification_templates | Template used |
| channel | ENUM | NOT NULL | Delivery channel |
| category | ENUM | NOT NULL | Notification category |
| status | ENUM | NOT NULL, default 'pending' | Delivery status |
| provider | VARCHAR(100) | Delivery provider | |
| provider_message_id | VARCHAR(255) | Provider reference | |
| error_message | TEXT | Error details | |
| retry_count | INTEGER | NOT NULL, default 0 | Retry attempts |
| sent_at | TIMESTAMPTZ | Send time | |
| delivered_at | TIMESTAMPTZ | Delivery time | |
| opened_at | TIMESTAMPTZ | Open time | |
| clicked_at | TIMESTAMPTZ | Click time |
User Preferences Service#
preferences#
User settings and preferences.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Preferences ID |
| user_id | UUID | NOT NULL, UNIQUE | User reference |
| theme | VARCHAR(20) | NOT NULL, default 'system' | UI theme |
| locale | VARCHAR(10) | NOT NULL, default 'en' | Language |
| timezone | VARCHAR(50) | Timezone | |
| playback_speed | DECIMAL(3,2) | NOT NULL, default 1.0 | Media speed |
| default_volume | INTEGER | NOT NULL, default 80 | Volume 0-100 |
| auto_play | BOOLEAN | NOT NULL, default true | Auto-play media |
| captions_enabled | BOOLEAN | NOT NULL, default false | Show captions |
| font_size | VARCHAR(20) | NOT NULL, default 'medium' | Text size |
| reminder_enabled | BOOLEAN | NOT NULL, default true | Enable reminders |
| reminder_time | TIME | Daily reminder time | |
| onboarding_completed | BOOLEAN | NOT NULL, default false | Onboarding done |
favorites#
User saved/favorited content.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Favorite ID |
| user_id | UUID | NOT NULL | User reference |
| content_id | UUID | NOT NULL | Content item ID |
| content_type | VARCHAR(50) | NOT NULL | Content type |
| content_title | VARCHAR(500) | Content title | |
| folder_name | VARCHAR(100) | Organization folder | |
| notes | TEXT | User notes | |
| created_at | TIMESTAMPTZ | NOT NULL | Save time |
history#
Content consumption history.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | History ID |
| user_id | UUID | NOT NULL | User reference |
| content_id | UUID | NOT NULL | Content item ID |
| content_type | VARCHAR(50) | NOT NULL | Content type |
| progress_percent | INTEGER | NOT NULL, default 0 | 0-100 progress |
| position_ms | INTEGER | Playback position | |
| content_duration_ms | INTEGER | Total duration | |
| is_completed | BOOLEAN | NOT NULL, default false | Completion flag |
| completed_at | TIMESTAMPTZ | Completion time | |
| view_count | INTEGER | NOT NULL, default 1 | View count |
| total_time_spent_ms | INTEGER | NOT NULL, default 0 | Total time |
| last_accessed_at | TIMESTAMPTZ | NOT NULL | Last access |
achievements#
User badges and accomplishments.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Achievement ID |
| user_id | UUID | NOT NULL | User reference |
| achievement_type | VARCHAR(100) | NOT NULL | Achievement type |
| achievement_name | VARCHAR(255) | NOT NULL | Achievement name |
| description | TEXT | Description | |
| icon_url | VARCHAR(1000) | Badge icon | |
| progress_current | INTEGER | NOT NULL, default 0 | Current progress |
| progress_required | INTEGER | NOT NULL | Required progress |
| is_completed | BOOLEAN | NOT NULL, default false | Completion flag |
| completed_at | TIMESTAMPTZ | Completion time | |
| xp_reward | INTEGER | NOT NULL, default 0 | XP earned |
streaks#
Activity streak tracking.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Streak ID |
| user_id | UUID | NOT NULL, UNIQUE | User reference |
| streak_type | ENUM | NOT NULL, default 'daily' | Streak type |
| current_streak | INTEGER | NOT NULL, default 0 | Current streak |
| longest_streak | INTEGER | NOT NULL, default 0 | Best streak |
| last_activity_date | DATE | Last activity | |
| total_completions | INTEGER | NOT NULL, default 0 | All-time completions |
| freeze_days_remaining | INTEGER | NOT NULL, default 0 | Streak protections |
| daily_goal | INTEGER | Daily target | |
| weekly_goal | INTEGER | Weekly target |
Payment Orchestrator Service#
customers#
Billing customer profiles.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Customer ID |
| user_id | UUID | NOT NULL, UNIQUE | User reference |
| VARCHAR(255) | NOT NULL | Billing email | |
| name | VARCHAR(255) | Customer name | |
| stripe_customer_id | VARCHAR(100) | UNIQUE | Stripe customer |
| currency | VARCHAR(3) | NOT NULL, default 'USD' | Default currency |
| balance | DECIMAL(12,2) | NOT NULL, default 0 | Account balance |
| balance_currency | VARCHAR(3) | NOT NULL, default 'USD' | Balance currency |
| locale | VARCHAR(10) | NOT NULL, default 'en' | Locale |
| tax_id | VARCHAR(50) | Tax ID | |
| billing_address | JSONB | Address details | |
| tax_exempt | BOOLEAN | NOT NULL, default false | Tax exemption |
products#
Purchasable products.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Product ID |
| slug | VARCHAR(100) | NOT NULL, UNIQUE | Product identifier |
| name | VARCHAR(255) | NOT NULL | Product name |
| description | TEXT | Description | |
| stripe_product_id | VARCHAR(100) | UNIQUE | Stripe product |
| status | ENUM | NOT NULL, default 'active' | Status |
| features | JSONB | Feature list | |
| metadata | JSONB | Additional data | |
| sort_order | INTEGER | NOT NULL, default 0 | Display order |
prices#
Product pricing tiers.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Price ID |
| product_id | UUID | NOT NULL, FK -> products | Product |
| stripe_price_id | VARCHAR(100) | UNIQUE | Stripe price |
| unit_amount | INTEGER | NOT NULL | Amount in cents |
| currency | VARCHAR(3) | NOT NULL | Currency code |
| billing_type | ENUM | NOT NULL | one_time, recurring |
| interval | ENUM | month, year | |
| interval_count | INTEGER | Billing periods | |
| trial_period_days | INTEGER | Trial length | |
| status | ENUM | NOT NULL, default 'active' | Status |
subscriptions#
Active customer subscriptions.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Subscription ID |
| customer_id | UUID | NOT NULL, FK -> customers | Customer |
| price_id | UUID | NOT NULL, FK -> prices | Price tier |
| stripe_subscription_id | VARCHAR(100) | UNIQUE | Stripe subscription |
| status | ENUM | NOT NULL | active, canceled, etc. |
| current_period_start | TIMESTAMPTZ | NOT NULL | Period start |
| current_period_end | TIMESTAMPTZ | NOT NULL | Period end |
| cancel_at_period_end | BOOLEAN | NOT NULL, default false | Will cancel |
| canceled_at | TIMESTAMPTZ | Cancellation time | |
| trial_start | TIMESTAMPTZ | Trial start | |
| trial_end | TIMESTAMPTZ | Trial end |
invoices#
Billing invoices.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Invoice ID |
| customer_id | UUID | NOT NULL, FK -> customers | Customer |
| subscription_id | UUID | FK -> subscriptions | Subscription |
| stripe_invoice_id | VARCHAR(100) | UNIQUE | Stripe invoice |
| invoice_number | VARCHAR(50) | NOT NULL, UNIQUE | Invoice number |
| status | ENUM | NOT NULL | draft, open, paid, void |
| subtotal | INTEGER | NOT NULL | Subtotal in cents |
| tax | INTEGER | NOT NULL, default 0 | Tax amount |
| discount_amount | INTEGER | NOT NULL, default 0 | Discount |
| total | INTEGER | NOT NULL | Total in cents |
| amount_paid | INTEGER | NOT NULL, default 0 | Paid amount |
| amount_due | INTEGER | NOT NULL | Due amount |
| currency | VARCHAR(3) | NOT NULL | Currency |
| due_date | TIMESTAMPTZ | Payment due | |
| paid_at | TIMESTAMPTZ | Payment time |
payments#
Payment transactions.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Payment ID |
| customer_id | UUID | NOT NULL, FK -> customers | Customer |
| invoice_id | UUID | FK -> invoices | Related invoice |
| stripe_payment_intent_id | VARCHAR(100) | UNIQUE | Stripe intent |
| amount | INTEGER | NOT NULL | Amount in cents |
| currency | VARCHAR(3) | NOT NULL | Currency |
| status | ENUM | NOT NULL | pending, succeeded, failed |
| payment_method_type | VARCHAR(50) | card, bank_transfer | |
| failure_code | VARCHAR(100) | Failure reason | |
| failure_message | TEXT | Failure details |
refunds#
Refund transactions.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Refund ID |
| payment_id | UUID | NOT NULL, FK -> payments | Original payment |
| stripe_refund_id | VARCHAR(100) | UNIQUE | Stripe refund |
| amount | INTEGER | NOT NULL | Refund amount |
| currency | VARCHAR(3) | NOT NULL | Currency |
| status | ENUM | NOT NULL | pending, succeeded, failed |
| reason | ENUM | duplicate, fraudulent, requested | |
| description | TEXT | Refund reason |
discounts#
Discount codes and coupons.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Discount ID |
| code | VARCHAR(50) | NOT NULL, UNIQUE | Coupon code |
| name | VARCHAR(255) | NOT NULL | Discount name |
| type | ENUM | NOT NULL | percent_off, amount_off |
| percent_off | DECIMAL(5,2) | Percentage discount | |
| amount_off | INTEGER | Fixed amount off | |
| currency | VARCHAR(3) | Amount currency | |
| duration | ENUM | NOT NULL | once, repeating, forever |
| duration_in_months | INTEGER | Repeating months | |
| max_redemptions | INTEGER | Usage limit | |
| redemption_count | INTEGER | NOT NULL, default 0 | Times used |
| valid_from | TIMESTAMPTZ | Start date | |
| valid_until | TIMESTAMPTZ | End date | |
| minimum_amount | INTEGER | Minimum order | |
| is_active | BOOLEAN | NOT NULL, default true | Active status |
entitlements#
User access entitlements.
| Column | Type | Constraints | Description |
|---|---|---|---|
| id | UUID | PK | Entitlement ID |
| customer_id | UUID | NOT NULL, FK -> customers | Customer |
| subscription_id | UUID | FK -> subscriptions | Source subscription |
| entitlement_type | VARCHAR(100) | NOT NULL | Entitlement type |
| resource_type | VARCHAR(50) | Resource type | |
| resource_id | UUID | Specific resource | |
| quantity | INTEGER | NOT NULL, default 1 | Quantity |
| is_active | BOOLEAN | NOT NULL, default true | Active status |
| valid_from | TIMESTAMPTZ | NOT NULL | Start date |
| valid_until | TIMESTAMPTZ | Expiry date | |
| source | ENUM | NOT NULL | subscription, purchase, grant |
Migration Naming Convention#
Migrations follow the pattern: YYYYMMDDSSSSSS_description.ts
Where:
YYYYMMDD- Date (year, month, day)SSSSSS- 6-digit sequence number (service_id * 100000 + ordinal)description- Snake_case description
Service sequence prefixes:
- auth: 000001-099999
- content: 100001-199999
- conversation: 200001-299999
- media: 300001-399999
- notification: 400001-499999
- user-preferences: 500001-599999
- payment-orchestrator: 600001-699999
Check Constraints Summary#
All services implement comprehensive check constraints for data validation:
| Service | Tables | Constraints | Key Validations |
|---|---|---|---|
| auth | 11 | 23 | Email format, lowercase normalization, non-negative counts |
| content | 13 | 59 | URL-safe slugs, rating ranges 0-5, positive durations |
| conversation | 5 | 35 | Non-empty content, sequence ordering, token counts |
| media | 5 | 57 | Positive file sizes, GPS coordinate ranges, progress 0-100 |
| notification | 4 | 28 | Valid locales, email formats, timezone patterns |
| user-preferences | 5 | 32 | Volume 0-100, playback speed range, streak consistency |
| payment-orchestrator | 9 | 49 | Currency codes, positive amounts, date ordering |
Index Strategy#
All tables include:
- Primary key indexes (automatic with
PRIMARY KEY) - Foreign key indexes for join performance
- Composite indexes for common query patterns
- Partial indexes for status/active filters
Common indexed columns:
user_id- User lookups across all servicescreated_at- Time-range queriesstatus- Active record filteringslug- Content URL lookups