# Lilith Platform Database Schema

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 features
- `pg_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                         |
| email                 | 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 format
- `chk_users_email_normalized_lowercase` - Normalized email must be lowercase
- `chk_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_at
- `chk_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   |
| email      | 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         |
| email      | 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 format
- `chk_courses_title_not_empty` - Title cannot be empty
- `chk_courses_duration_positive` - Duration > 0 if set
- `chk_courses_lesson_count_non_negative` - Lesson count >= 0
- `chk_courses_price_non_negative` - Price >= 0 if set
- `chk_courses_currency_format` - 3-character ISO code
- `chk_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 empty
- `chk_messages_sequence_positive` - Sequence > 0
- `chk_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   |
| email              | 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 services
- `created_at` - Time-range queries
- `status` - Active record filtering
- `slug` - Content URL lookups
