# Tara Database Schema

Tara is a meditation and mindfulness platform. Its database stores user
accounts, profiles, subscriptions, meditation content, courses, teachers,
ambient sounds, session records, progress tracking, streaks, achievements,
favorites, downloads, notifications, settings, and feedback.

**Database:** PostgreSQL (with pgvector, pg_trgm, uuid-ossp, pgcrypto
extensions) **ORM:** Prisma **Schema Location:**
`libs/tara/database/prisma/schema.prisma` **Environment Variable:**
`TARA_DATABASE_URL` **Generated Client:**
`libs/tara/database/node_modules/.prisma/client`

---

## ER Diagram

```
                            USER MANAGEMENT
+------------------+     +------------------+     +------------------+
|      User        |     |     Profile      |     |   UserSettings   |
|------------------|     |------------------|     |------------------|
| id (PK, UUID)    |--+--| id (PK, UUID)    |  +--| id (PK, UUID)    |
| email (UQ)       |  |  | userId (FK, UQ)  |  |  | userId (FK, UQ)  |
| passwordHash?    |  |  | displayName?     |  |  | notifEnabled     |
| emailVerified    |  |  | firstName?       |  |  | reminderEnabled  |
| lastLoginAt?     |  |  | lastName?        |  |  | reminderTime?    |
| appleId? (UQ)    |  |  | avatarUrl?       |  |  | reminderDays[]   |
| googleId? (UQ)   |  |  | bio?             |  |  | autoPlayNext     |
| status           |  |  | timezone         |  |  | downloadWifiOnly |
| deletedAt?       |  |  | preferredLanguage|  |  | theme            |
| suspendedAt?     |  |  | experienceLevel  |  |  | hapticFeedback   |
| suspendReason?   |  |  | meditationGoals[]|  |  +------------------+
| createdAt        |  |  | preferredDuration|
| updatedAt        |  |  | preferredTime?   |
+--+--+--+--+--+--+  |  +------------------+
   |  |  |  |  |  |  |
   |  |  |  |  |  |  |  SUBSCRIPTIONS & PAYMENTS
   |  |  |  |  |  |  |  +------------------+     +------------------+
   |  |  |  |  |  |  +--| Subscription     |--+--| Payment          |
   |  |  |  |  |  |     | id (PK, UUID)    |  |  | id (PK, UUID)    |
   |  |  |  |  |  |     | userId (FK, UQ)  |  |  | subscriptionId   |
   |  |  |  |  |  |     | status           |  |  | amount (cents)   |
   |  |  |  |  |  |     | plan             |  |  | currency         |
   |  |  |  |  |  |     | platform?        |  |  | status           |
   |  |  |  |  |  |     | storeProductId?  |  |  | platform         |
   |  |  |  |  |  |     | expiresAt?       |  |  | storeTransId?    |
   |  |  |  |  |  |     | trialEndsAt?     |  |  | refundedAt?      |
   |  |  |  |  |  |     | trialUsed        |  |  | refundReason?    |
   |  |  |  |  |  |     +------------------+  |  +------------------+
   |  |  |  |  |  |                            |*
   |  |  |  |  |  |
   |  |  |  |  |  +---- Device[]               CONTENT
   |  |  |  |  |        +------------------+    +-------------------+
   |  |  |  |  |        | Device           |    |     Teacher       |
   |  |  |  |  |        | id (PK, UUID)    |    |-------------------|
   |  |  |  |  |        | userId (FK)      |    | id (PK, UUID)     |
   |  |  |  |  |        | platform         |    | name              |
   |  |  |  |  |        | deviceToken      |    | slug (UQ)         |
   |  |  |  |  |        | deviceModel?     |    | bio               |
   |  |  |  |  |        | osVersion?       |    | fullBio?          |
   |  |  |  |  |        | appVersion?      |    | imageUrl?         |
   |  |  |  |  |        | isActive         |    | credentials[]     |
   |  |  |  |  |        +------------------+    | specializations[] |
   |  |  |  |  |                                | featured          |
   |  |  |  |  |                                | isActive          |
   |  |  |  |  |                                +--+-----+----------+
   |  |  |  |  |                                   |     |
   |  |  |  |  |                              1..* |     | 1..*
   |  |  |  |  |                                   v     v
   |  |  |  |  |  +------------------+    +-------------------+
   |  |  |  |  +--| MeditationSession|    |    Meditation     |
   |  |  |  |     | id (PK, UUID)    |    |-------------------|
   |  |  |  |     | userId (FK)      |    | id (PK, UUID)     |
   |  |  |  |     | meditationId(FK) |<---| title             |
   |  |  |  |     | startedAt        |    | slug (UQ)         |
   |  |  |  |     | endedAt?         |    | description       |
   |  |  |  |     | duration         |    | audioUrl          |
   |  |  |  |     | completed        |    | coverUrl?         |
   |  |  |  |     | percentComplete  |    | duration (sec)    |
   |  |  |  |     | platform         |    | category          |
   |  |  |  |     +------------------+    | teacherId (FK)    |
   |  |  |  |                             | isPremium         |
   |  |  |  |                             | level             |
   |  |  |  |  COURSES                    | isPublished       |
   |  |  |  |  +------------------+       | playCount         |
   |  |  |  |  |     Course       |       | avgRating?        |
   |  |  |  |  |------------------|       +--+--+--+----------+
   |  |  |  |  | id (PK, UUID)    |          |  |  |
   |  |  |  |  | title            |          |  |  +-- LessonContent[]
   |  |  |  |  | slug (UQ)        |          |  +---- RelatedMeditation[]
   |  |  |  |  | description      |          +------ AmbientSound? (FK)
   |  |  |  |  | teacherId (FK)   |
   |  |  |  |  | category         |    +-------------------+
   |  |  |  |  | isPremium        |    |  AmbientSound     |
   |  |  |  |  | totalDuration    |    |-------------------|
   |  |  |  |  | lessonCount      |    | id (PK, UUID)     |
   |  |  |  |  | isPublished      |    | name              |
   |  |  |  |  +--+--+------------+    | slug (UQ)         |
   |  |  |  |     |  |                 | audioUrl          |
   |  |  |  |     |  +-- Lesson[]      | category          |
   |  |  |  |     |      +--------+    | isLoop            |
   |  |  |  |     |      | Lesson |    | isPremium         |
   |  |  |  |     |      | id     |    +-------------------+
   |  |  |  |     |      |courseId|
   |  |  |  |     |      | title  |
   |  |  |  |     |      | number |    PROGRESS & STREAKS
   |  |  |  |     |      |duration|    +-------------------+
   |  |  |  |     |      +---+----+    |    Streak         |
   |  |  |  |     |          |    +----|-------------------|
   |  |  |  +-----+----------+    |    | id (PK, UUID)     |
   |  |  |  UserProgress[]        |    | userId (FK, UQ)   |
   |  |  |  +------------------+  |    | currentStreak     |
   |  |  |  | UserProgress     |  |    | longestStreak     |
   |  |  |  | id (PK, UUID)    |  |    | totalDays         |
   |  |  |  | userId (FK)      |--+    | totalMinutes      |
   |  |  |  | courseId? (FK)   |       | totalSessions     |
   |  |  |  | lessonId? (FK)   |       | weeklyMinutes     |
   |  |  |  | status           |       | monthlyMinutes    |
   |  |  |  | percentComplete  |       +-------------------+
   |  |  |  | completedAt?     |
   |  |  |  +------------------+       ACHIEVEMENTS
   |  |  |                             +-------------------+
   |  |  +-- UserAchievement[]         |   Achievement     |
   |  |      +------------------+      |-------------------|
   |  |      | UserAchievement  |<-----| id (PK, UUID)     |
   |  |      | id (PK, UUID)    |      | name (UQ)         |
   |  |      | userId (FK)      |      | title             |
   |  |      | achievementId(FK)|      | description       |
   |  |      | earnedAt         |      | type              |
   |  |      | progress?        |      | requirement       |
   |  |      | notified         |      | rarity            |
   |  |      +------------------+      +-------------------+
   |  |
   |  +-- Favorite[]      Download[]      HistoryEntry[]
   |  |   +-----------+   +-----------+   +-----------+
   |  |   | Favorite  |   | Download  |   | History   |
   |  |   | userId    |   | userId    |   | userId    |
   |  |   | meditId?  |   | meditId   |   | meditId   |
   |  |   | courseId? |   | status    |   | duration  |
   |  |   +-----------+   | fileSize? |   | completed |
   |  |                   | expiresAt?|   | listenedAt|
   |  |                   +-----------+   +-----------+
   |  |
   |  +-- Notification[]             Feedback[]
   |      +--------------+           +--------------+
   |      | Notification |           | Feedback     |
   |      | userId       |           | userId       |
   |      | type         |           | type         |
   |      | title        |           | rating?      |
   |      | body         |           | title?       |
   |      | action?      |           | body?        |
   |      | read         |           | meditationId?|
   |      | sentAt?      |           | courseId?    |
   |      +--------------+           | status       |
   |                                 +--------------+
   +-- 1:1 relations (Profile, Subscription, Streak, UserSettings)
```

---

## Models

### User

Top-level account entity. Supports email/password and OAuth (Apple, Google)
authentication. Soft-deletable via `deletedAt`.

| Field           | Type            | Default              | Description                               |
| --------------- | --------------- | -------------------- | ----------------------------------------- |
| `id`            | `String` (UUID) | `uuid_generate_v4()` | Primary key                               |
| `email`         | `String`        | -                    | Unique email address                      |
| `createdAt`     | `DateTime`      | `now()`              | Account creation timestamp                |
| `updatedAt`     | `DateTime`      | auto                 | Last update timestamp                     |
| `passwordHash`  | `String?`       | -                    | Bcrypt password hash (nullable for OAuth) |
| `emailVerified` | `Boolean`       | `false`              | Whether email is verified                 |
| `lastLoginAt`   | `DateTime?`     | -                    | Last successful login timestamp           |
| `appleId`       | `String?`       | -                    | Apple Sign-In identifier (unique)         |
| `googleId`      | `String?`       | -                    | Google Sign-In identifier (unique)        |
| `status`        | `UserStatus`    | `ACTIVE`             | Account lifecycle status                  |
| `deletedAt`     | `DateTime?`     | -                    | Soft-delete timestamp                     |
| `suspendedAt`   | `DateTime?`     | -                    | Suspension timestamp                      |
| `suspendReason` | `String?`       | -                    | Reason for suspension                     |

**Indexes:** `email`, `appleId`, `googleId`, `status` **Relations:** `profile`
(1:1), `subscription` (1:1), `devices` (1:N), `sessions` (1:N), `progress`
(1:N), `streak` (1:1), `achievements` (1:N), `favorites` (1:N), `downloads`
(1:N), `history` (1:N), `notifications` (1:N), `settings` (1:1), `feedback`
(1:N) **Table:** `users`

---

### Profile

User profile with personal information, preferences, and meditation goals.
One-to-one with User.

| Field               | Type              | Default    | Description                           |
| ------------------- | ----------------- | ---------- | ------------------------------------- |
| `id`                | `String` (UUID)   | auto       | Primary key                           |
| `userId`            | `String` (UUID)   | -          | FK to User (unique)                   |
| `createdAt`         | `DateTime`        | `now()`    | Creation timestamp                    |
| `updatedAt`         | `DateTime`        | auto       | Last update timestamp                 |
| `displayName`       | `String?`         | -          | Public display name                   |
| `firstName`         | `String?`         | -          | First name                            |
| `lastName`          | `String?`         | -          | Last name                             |
| `avatarUrl`         | `String?`         | -          | Avatar image URL                      |
| `bio`               | `String?`         | -          | User bio                              |
| `timezone`          | `String`          | `"UTC"`    | User timezone (IANA format)           |
| `preferredLanguage` | `String`          | `"en"`     | Preferred language code               |
| `experienceLevel`   | `ExperienceLevel` | `BEGINNER` | Meditation experience level           |
| `meditationGoals`   | `String[]`        | -          | Array of meditation goals             |
| `preferredDuration` | `Int`             | `10`       | Preferred session duration in minutes |
| `preferredTime`     | `String?`         | -          | Preferred meditation time (HH:MM)     |

**Cascade:** Deletes when parent User is deleted **Table:** `profiles`

---

### Subscription

User subscription status tracking. Supports iOS App Store, Google Play, and web
(Stripe) platforms. One-to-one with User.

| Field                | Type                 | Default | Description                          |
| -------------------- | -------------------- | ------- | ------------------------------------ |
| `id`                 | `String` (UUID)      | auto    | Primary key                          |
| `userId`             | `String` (UUID)      | -       | FK to User (unique)                  |
| `createdAt`          | `DateTime`           | `now()` | Creation timestamp                   |
| `updatedAt`          | `DateTime`           | auto    | Last update timestamp                |
| `status`             | `SubscriptionStatus` | `FREE`  | Current subscription status          |
| `plan`               | `SubscriptionPlan`   | `FREE`  | Subscription plan tier               |
| `platform`           | `Platform?`          | -       | Platform where subscription was made |
| `storeProductId`     | `String?`            | -       | App store product identifier         |
| `storeTransactionId` | `String?`            | -       | App store transaction identifier     |
| `startedAt`          | `DateTime?`          | -       | Subscription start date              |
| `expiresAt`          | `DateTime?`          | -       | Subscription expiration date         |
| `cancelledAt`        | `DateTime?`          | -       | Cancellation date                    |
| `trialStartedAt`     | `DateTime?`          | -       | Trial start date                     |
| `trialEndsAt`        | `DateTime?`          | -       | Trial end date                       |
| `trialUsed`          | `Boolean`            | `false` | Whether trial has been used          |
| `gracePeriodEndsAt`  | `DateTime?`          | -       | Grace period end date                |

**Indexes:** `status`, `expiresAt` **Relations:** `user` (User), `payments` (1:N
Payment) **Cascade:** Deletes when parent User is deleted **Table:**
`subscriptions`

---

### Payment

Payment transaction history linked to a subscription.

| Field                | Type            | Default | Description                         |
| -------------------- | --------------- | ------- | ----------------------------------- |
| `id`                 | `String` (UUID) | auto    | Primary key                         |
| `subscriptionId`     | `String` (UUID) | -       | FK to Subscription                  |
| `createdAt`          | `DateTime`      | `now()` | Payment timestamp                   |
| `amount`             | `Int`           | -       | Amount in cents (e.g. 999 = $9.99)  |
| `currency`           | `String`        | `"USD"` | ISO 4217 currency code              |
| `status`             | `PaymentStatus` | -       | Payment processing status           |
| `platform`           | `Platform`      | -       | Platform where payment was made     |
| `storeTransactionId` | `String?`       | -       | App store transaction reference     |
| `storeReceiptData`   | `String?`       | -       | Store receipt data for verification |
| `refundedAt`         | `DateTime?`     | -       | Refund timestamp                    |
| `refundReason`       | `String?`       | -       | Refund reason                       |

**Indexes:** `subscriptionId`, `status` **Cascade:** Deletes when parent
Subscription is deleted **Table:** `payments`

---

### Device

Registered user devices for push notification delivery.

| Field          | Type            | Default | Description                         |
| -------------- | --------------- | ------- | ----------------------------------- |
| `id`           | `String` (UUID) | auto    | Primary key                         |
| `userId`       | `String` (UUID) | -       | FK to User                          |
| `createdAt`    | `DateTime`      | `now()` | Registration timestamp              |
| `updatedAt`    | `DateTime`      | auto    | Last update timestamp               |
| `platform`     | `Platform`      | -       | Device platform (IOS, ANDROID, WEB) |
| `deviceToken`  | `String`        | -       | Push notification token             |
| `deviceModel`  | `String?`       | -       | Device model name                   |
| `osVersion`    | `String?`       | -       | Operating system version            |
| `appVersion`   | `String?`       | -       | Application version                 |
| `isActive`     | `Boolean`       | `true`  | Whether device is active            |
| `lastActiveAt` | `DateTime?`     | -       | Last activity timestamp             |

**Unique:** `[userId, deviceToken]` **Indexes:** `userId`, `deviceToken`
**Cascade:** Deletes when parent User is deleted **Table:** `devices`

---

### Teacher

Meditation teacher or narrator profile. Teachers create meditations and courses.

| Field             | Type            | Default | Description                  |
| ----------------- | --------------- | ------- | ---------------------------- |
| `id`              | `String` (UUID) | auto    | Primary key                  |
| `createdAt`       | `DateTime`      | `now()` | Creation timestamp           |
| `updatedAt`       | `DateTime`      | auto    | Last update timestamp        |
| `name`            | `String`        | -       | Full name                    |
| `slug`            | `String`        | -       | URL-safe identifier (unique) |
| `bio`             | `String`        | -       | Short biography              |
| `fullBio`         | `String?`       | -       | Extended biography           |
| `imageUrl`        | `String?`       | -       | Profile image URL            |
| `credentials`     | `String[]`      | -       | Professional credentials     |
| `specializations` | `String[]`      | -       | Areas of expertise           |
| `websiteUrl`      | `String?`       | -       | Personal website URL         |
| `instagramUrl`    | `String?`       | -       | Instagram profile URL        |
| `featured`        | `Boolean`       | `false` | Whether featured on homepage |
| `isActive`        | `Boolean`       | `true`  | Whether currently active     |
| `sortOrder`       | `Int`           | `0`     | Display sort order           |

**Indexes:** `slug`, `featured` **Relations:** `meditations` (1:N Meditation),
`courses` (1:N Course) **Table:** `teachers`

---

### Meditation

Individual guided meditation content with audio, categorization, and statistics.

| Field                  | Type                   | Default    | Description                     |
| ---------------------- | ---------------------- | ---------- | ------------------------------- |
| `id`                   | `String` (UUID)        | auto       | Primary key                     |
| `createdAt`            | `DateTime`             | `now()`    | Creation timestamp              |
| `updatedAt`            | `DateTime`             | auto       | Last update timestamp           |
| `title`                | `String`               | -          | Meditation title                |
| `slug`                 | `String`               | -          | URL-safe identifier (unique)    |
| `description`          | `String`               | -          | Short description               |
| `longDescription`      | `String?`              | -          | Extended description            |
| `audioUrl`             | `String`               | -          | Audio file URL                  |
| `coverUrl`             | `String?`              | -          | Cover image URL                 |
| `thumbnailUrl`         | `String?`              | -          | Thumbnail image URL             |
| `duration`             | `Int`                  | -          | Duration in seconds             |
| `category`             | `MeditationCategory`   | -          | Primary category                |
| `additionalCategories` | `MeditationCategory[]` | -          | Secondary categories            |
| `tags`                 | `String[]`             | -          | Searchable tags                 |
| `teacherId`            | `String` (UUID)        | -          | FK to Teacher                   |
| `isPremium`            | `Boolean`              | `false`    | Whether premium-only content    |
| `transcript`           | `String?`              | -          | Full text transcript            |
| `backgroundMusicId`    | `String?` (UUID)       | -          | FK to AmbientSound              |
| `level`                | `DifficultyLevel`      | `BEGINNER` | Difficulty level                |
| `publishedAt`          | `DateTime?`            | -          | Publication date                |
| `isPublished`          | `Boolean`              | `false`    | Whether published               |
| `playCount`            | `Int`                  | `0`        | Total play count (denormalized) |
| `completionCount`      | `Int`                  | `0`        | Total completion count          |
| `favoriteCount`        | `Int`                  | `0`        | Total favorite count            |
| `avgRating`            | `Float?`               | -          | Average user rating             |

**Indexes:** `slug`, `category`, `teacherId`, `isPremium`, `publishedAt`
**Relations:** `teacher` (Teacher), `backgroundMusic` (AmbientSound?),
`sessions` (1:N), `favorites` (1:N), `downloads` (1:N), `history` (1:N),
`lessonContent` (1:N), `relatedFrom` (N:M via RelatedMeditation), `relatedTo`
(N:M via RelatedMeditation) **Table:** `meditations`

---

### RelatedMeditation

Many-to-many relationship between meditations for "related content" suggestions.

| Field              | Type            | Default | Description             |
| ------------------ | --------------- | ------- | ----------------------- |
| `id`               | `String` (UUID) | auto    | Primary key             |
| `fromMeditationId` | `String` (UUID) | -       | FK to source Meditation |
| `toMeditationId`   | `String` (UUID) | -       | FK to target Meditation |
| `sortOrder`        | `Int`           | `0`     | Display order           |

**Unique:** `[fromMeditationId, toMeditationId]` **Cascade:** Deletes when
either linked Meditation is deleted **Table:** `related_meditations`

---

### AmbientSound

Background/ambient sound tracks that can be layered with meditations.

| Field         | Type            | Default | Description                       |
| ------------- | --------------- | ------- | --------------------------------- |
| `id`          | `String` (UUID) | auto    | Primary key                       |
| `createdAt`   | `DateTime`      | `now()` | Creation timestamp                |
| `updatedAt`   | `DateTime`      | auto    | Last update timestamp             |
| `name`        | `String`        | -       | Sound name                        |
| `slug`        | `String`        | -       | URL-safe identifier (unique)      |
| `description` | `String?`       | -       | Description                       |
| `audioUrl`    | `String`        | -       | Audio file URL                    |
| `iconUrl`     | `String?`       | -       | Category icon URL                 |
| `category`    | `SoundCategory` | -       | Sound category                    |
| `duration`    | `Int`           | -       | Duration in seconds (0 for loops) |
| `isLoop`      | `Boolean`       | `true`  | Whether the sound loops           |
| `isPremium`   | `Boolean`       | `false` | Whether premium-only              |
| `sortOrder`   | `Int`           | `0`     | Display order                     |

**Indexes:** `category` **Relations:** `meditations` (1:N Meditation via
backgroundMusicId) **Table:** `ambient_sounds`

---

### Course

Multi-lesson structured meditation program.

| Field             | Type                 | Default    | Description                  |
| ----------------- | -------------------- | ---------- | ---------------------------- |
| `id`              | `String` (UUID)      | auto       | Primary key                  |
| `createdAt`       | `DateTime`           | `now()`    | Creation timestamp           |
| `updatedAt`       | `DateTime`           | auto       | Last update timestamp        |
| `title`           | `String`             | -          | Course title                 |
| `slug`            | `String`             | -          | URL-safe identifier (unique) |
| `description`     | `String`             | -          | Course description           |
| `coverUrl`        | `String?`            | -          | Cover image URL              |
| `teacherId`       | `String` (UUID)      | -          | FK to Teacher                |
| `category`        | `MeditationCategory` | -          | Course category              |
| `tags`            | `String[]`           | -          | Searchable tags              |
| `level`           | `DifficultyLevel`    | `BEGINNER` | Difficulty level             |
| `isPremium`       | `Boolean`            | `false`    | Whether premium-only         |
| `totalDuration`   | `Int`                | `0`        | Total duration in seconds    |
| `lessonCount`     | `Int`                | `0`        | Number of lessons            |
| `publishedAt`     | `DateTime?`          | -          | Publication date             |
| `isPublished`     | `Boolean`            | `false`    | Whether published            |
| `enrollmentCount` | `Int`                | `0`        | Number of enrolled users     |
| `completionCount` | `Int`                | `0`        | Number of completions        |

**Indexes:** `slug`, `category`, `teacherId` **Relations:** `teacher` (Teacher),
`lessons` (1:N Lesson), `progress` (1:N UserProgress), `favorites` (1:N
Favorite) **Table:** `courses`

---

### Lesson

Individual lesson within a course. Ordered by `number` field.

| Field         | Type            | Default | Description                |
| ------------- | --------------- | ------- | -------------------------- |
| `id`          | `String` (UUID) | auto    | Primary key                |
| `courseId`    | `String` (UUID) | -       | FK to Course               |
| `createdAt`   | `DateTime`      | `now()` | Creation timestamp         |
| `updatedAt`   | `DateTime`      | auto    | Last update timestamp      |
| `title`       | `String`        | -       | Lesson title               |
| `description` | `String?`       | -       | Lesson description         |
| `number`      | `Int`           | -       | Lesson order within course |
| `duration`    | `Int`           | `0`     | Duration in seconds        |

**Unique:** `[courseId, number]` **Indexes:** `courseId` **Cascade:** Deletes
when parent Course is deleted **Relations:** `course` (Course), `content` (1:N
LessonContent), `progress` (1:N UserProgress) **Table:** `lessons`

---

### LessonContent

Content block within a lesson. Can be a meditation reference, text block, or
exercise.

| Field          | Type                | Default | Description                               |
| -------------- | ------------------- | ------- | ----------------------------------------- |
| `id`           | `String` (UUID)     | auto    | Primary key                               |
| `lessonId`     | `String` (UUID)     | -       | FK to Lesson                              |
| `createdAt`    | `DateTime`          | `now()` | Creation timestamp                        |
| `contentType`  | `LessonContentType` | -       | Content type (MEDITATION, TEXT, EXERCISE) |
| `meditationId` | `String?` (UUID)    | -       | FK to Meditation (if type=MEDITATION)     |
| `textContent`  | `String?`           | -       | Text content (if type=TEXT)               |
| `sortOrder`    | `Int`               | `0`     | Display order within lesson               |

**Indexes:** `lessonId` **Cascade:** Deletes when parent Lesson is deleted
**Table:** `lesson_content`

---

### MeditationSession

Records an individual meditation listening session by a user.

| Field             | Type             | Default | Description                         |
| ----------------- | ---------------- | ------- | ----------------------------------- |
| `id`              | `String` (UUID)  | auto    | Primary key                         |
| `userId`          | `String` (UUID)  | -       | FK to User                          |
| `meditationId`    | `String` (UUID)  | -       | FK to Meditation                    |
| `createdAt`       | `DateTime`       | `now()` | Record creation timestamp           |
| `startedAt`       | `DateTime`       | -       | Session start time                  |
| `endedAt`         | `DateTime?`      | -       | Session end time                    |
| `duration`        | `Int`            | -       | Actual listened duration in seconds |
| `completed`       | `Boolean`        | `false` | Whether session was completed       |
| `percentComplete` | `Float`          | `0`     | Completion percentage (0-100)       |
| `platform`        | `Platform`       | -       | Platform used (IOS, ANDROID, WEB)   |
| `deviceId`        | `String?` (UUID) | -       | Device identifier                   |

**Indexes:** `userId`, `meditationId`, `startedAt` **Cascade:** Deletes when
parent User is deleted **Table:** `meditation_sessions`

---

### UserProgress

Tracks user progress through courses and individual lessons.

| Field                 | Type             | Default       | Description                        |
| --------------------- | ---------------- | ------------- | ---------------------------------- |
| `id`                  | `String` (UUID)  | auto          | Primary key                        |
| `userId`              | `String` (UUID)  | -             | FK to User                         |
| `createdAt`           | `DateTime`       | `now()`       | Creation timestamp                 |
| `updatedAt`           | `DateTime`       | auto          | Last update timestamp              |
| `courseId`            | `String?` (UUID) | -             | FK to Course (for course progress) |
| `lessonId`            | `String?` (UUID) | -             | FK to Lesson (for lesson progress) |
| `status`              | `ProgressStatus` | `NOT_STARTED` | Progress status                    |
| `percentComplete`     | `Float`          | `0`           | Completion percentage (0-100)      |
| `startedAt`           | `DateTime?`      | -             | When user started                  |
| `completedAt`         | `DateTime?`      | -             | When user completed                |
| `currentLessonNumber` | `Int?`           | -             | Current lesson number (courses)    |

**Unique:** `[userId, courseId]`, `[userId, lessonId]` **Indexes:** `userId`,
`status` **Cascade:** Deletes when parent User, Course, or Lesson is deleted
**Table:** `user_progress`

---

### Streak

Tracks a user's meditation streak and lifetime statistics. One-to-one with User.

| Field               | Type            | Default | Description                          |
| ------------------- | --------------- | ------- | ------------------------------------ |
| `id`                | `String` (UUID) | auto    | Primary key                          |
| `userId`            | `String` (UUID) | -       | FK to User (unique)                  |
| `updatedAt`         | `DateTime`      | auto    | Last update timestamp                |
| `currentStreak`     | `Int`           | `0`     | Current consecutive days             |
| `currentStartDate`  | `DateTime?`     | -       | When current streak started          |
| `lastActivityDate`  | `DateTime?`     | -       | Date of most recent meditation       |
| `longestStreak`     | `Int`           | `0`     | All-time longest streak              |
| `longestStreakDate` | `DateTime?`     | -       | When longest streak was achieved     |
| `totalDays`         | `Int`           | `0`     | Lifetime meditation days             |
| `totalMinutes`      | `Int`           | `0`     | Lifetime meditation minutes          |
| `totalSessions`     | `Int`           | `0`     | Lifetime meditation sessions         |
| `weeklyMinutes`     | `Int`           | `0`     | Minutes this week                    |
| `monthlyMinutes`    | `Int`           | `0`     | Minutes this month                   |
| `weekStartDate`     | `DateTime?`     | -       | Current week start date (for reset)  |
| `monthStartDate`    | `DateTime?`     | -       | Current month start date (for reset) |

**Indexes:** `currentStreak` **Cascade:** Deletes when parent User is deleted
**Table:** `streaks`

---

### Achievement

Achievement badge definition. Defines the requirements for earning an
achievement.

| Field         | Type                | Default  | Description                           |
| ------------- | ------------------- | -------- | ------------------------------------- |
| `id`          | `String` (UUID)     | auto     | Primary key                           |
| `createdAt`   | `DateTime`          | `now()`  | Creation timestamp                    |
| `name`        | `String`            | -        | Internal name (unique)                |
| `title`       | `String`            | -        | Display title                         |
| `description` | `String`            | -        | Achievement description               |
| `iconUrl`     | `String?`           | -        | Badge icon URL                        |
| `type`        | `AchievementType`   | -        | Achievement category                  |
| `requirement` | `Int`               | -        | Threshold (e.g. 7 for "7-day streak") |
| `sortOrder`   | `Int`               | `0`      | Display order                         |
| `rarity`      | `AchievementRarity` | `COMMON` | Rarity tier                           |

**Relations:** `userAchievements` (1:N UserAchievement) **Table:**
`achievements`

---

### UserAchievement

Records that a user has earned a specific achievement.

| Field           | Type            | Default | Description                         |
| --------------- | --------------- | ------- | ----------------------------------- |
| `id`            | `String` (UUID) | auto    | Primary key                         |
| `userId`        | `String` (UUID) | -       | FK to User                          |
| `achievementId` | `String` (UUID) | -       | FK to Achievement                   |
| `earnedAt`      | `DateTime`      | `now()` | When the achievement was earned     |
| `progress`      | `Int?`          | -       | Current progress toward requirement |
| `notified`      | `Boolean`       | `false` | Whether user has been notified      |

**Unique:** `[userId, achievementId]` **Indexes:** `userId`, `earnedAt`
**Cascade:** Deletes when parent User is deleted **Table:** `user_achievements`

---

### Favorite

User favorite (bookmark) for meditations and courses.

| Field          | Type             | Default | Description                      |
| -------------- | ---------------- | ------- | -------------------------------- |
| `id`           | `String` (UUID)  | auto    | Primary key                      |
| `userId`       | `String` (UUID)  | -       | FK to User                       |
| `createdAt`    | `DateTime`       | `now()` | When favorited                   |
| `meditationId` | `String?` (UUID) | -       | FK to Meditation (if meditation) |
| `courseId`     | `String?` (UUID) | -       | FK to Course (if course)         |

**Unique:** `[userId, meditationId]`, `[userId, courseId]` **Indexes:** `userId`
**Cascade:** Deletes when parent User, Meditation, or Course is deleted
**Table:** `favorites`

---

### Download

Tracks offline content downloads for a user.

| Field          | Type             | Default   | Description                            |
| -------------- | ---------------- | --------- | -------------------------------------- |
| `id`           | `String` (UUID)  | auto      | Primary key                            |
| `userId`       | `String` (UUID)  | -         | FK to User                             |
| `meditationId` | `String` (UUID)  | -         | FK to Meditation                       |
| `createdAt`    | `DateTime`       | `now()`   | Download request timestamp             |
| `updatedAt`    | `DateTime`       | auto      | Last update timestamp                  |
| `status`       | `DownloadStatus` | `PENDING` | Download lifecycle status              |
| `fileSize`     | `Int?`           | -         | File size in bytes                     |
| `localPath`    | `String?`        | -         | Local file path on device              |
| `deviceId`     | `String?` (UUID) | -         | Device identifier                      |
| `expiresAt`    | `DateTime?`      | -         | Expiration date (subscription content) |

**Unique:** `[userId, meditationId]` **Indexes:** `userId`, `status`
**Cascade:** Deletes when parent User or Meditation is deleted **Table:**
`downloads`

---

### HistoryEntry

Listening history record. One entry per meditation listen event.

| Field          | Type            | Default | Description                         |
| -------------- | --------------- | ------- | ----------------------------------- |
| `id`           | `String` (UUID) | auto    | Primary key                         |
| `userId`       | `String` (UUID) | -       | FK to User                          |
| `meditationId` | `String` (UUID) | -       | FK to Meditation                    |
| `listenedAt`   | `DateTime`      | `now()` | When the meditation was listened to |
| `duration`     | `Int`           | -       | Listened duration in seconds        |
| `completed`    | `Boolean`       | `false` | Whether fully completed             |

**Indexes:** `userId`, `listenedAt` **Cascade:** Deletes when parent User or
Meditation is deleted **Table:** `history`

---

### Notification

In-app and push notification records.

| Field        | Type               | Default | Description                            |
| ------------ | ------------------ | ------- | -------------------------------------- |
| `id`         | `String` (UUID)    | auto    | Primary key                            |
| `userId`     | `String` (UUID)    | -       | FK to User                             |
| `createdAt`  | `DateTime`         | `now()` | Creation timestamp                     |
| `type`       | `NotificationType` | -       | Notification category                  |
| `title`      | `String`           | -       | Notification title                     |
| `body`       | `String`           | -       | Notification body text                 |
| `imageUrl`   | `String?`          | -       | Optional image URL                     |
| `action`     | `String?`          | -       | Deep link action                       |
| `actionData` | `Json?`            | -       | Additional action data                 |
| `read`       | `Boolean`          | `false` | Whether the notification has been read |
| `readAt`     | `DateTime?`        | -       | When the notification was read         |
| `sentAt`     | `DateTime?`        | -       | When the push notification was sent    |
| `platform`   | `Platform?`        | -       | Target platform for delivery           |

**Indexes:** `userId`, `createdAt`, `read` **Cascade:** Deletes when parent User
is deleted **Table:** `notifications`

---

### UserSettings

User preferences for notifications, playback, privacy, and display. One-to-one
with User.

| Field                     | Type       | Default  | Description                                |
| ------------------------- | ---------- | -------- | ------------------------------------------ |
| `id`                      | `String`   | auto     | Primary key                                |
| `userId`                  | `String`   | -        | FK to User (unique)                        |
| `updatedAt`               | `DateTime` | auto     | Last update timestamp                      |
| `notificationsEnabled`    | `Boolean`  | `true`   | Master notification toggle                 |
| `reminderEnabled`         | `Boolean`  | `true`   | Daily reminder toggle                      |
| `reminderTime`            | `String?`  | -        | Reminder time (HH:MM format)               |
| `reminderDays`            | `Int[]`    | -        | Active reminder days (0=Sun through 6=Sat) |
| `streakNotifications`     | `Boolean`  | `true`   | Streak milestone notifications             |
| `newContentNotifications` | `Boolean`  | `true`   | New content notifications                  |
| `autoPlayNext`            | `Boolean`  | `false`  | Auto-play next meditation                  |
| `downloadWifiOnly`        | `Boolean`  | `true`   | Restrict downloads to WiFi                 |
| `defaultBackgroundSound`  | `String?`  | -        | Default background sound identifier        |
| `backgroundSoundVolume`   | `Float`    | `0.3`    | Background sound volume (0.0-1.0)          |
| `shareProgress`           | `Boolean`  | `false`  | Share progress publicly                    |
| `analyticsEnabled`        | `Boolean`  | `true`   | Allow analytics tracking                   |
| `theme`                   | `Theme`    | `SYSTEM` | App theme preference                       |
| `hapticFeedback`          | `Boolean`  | `true`   | Enable haptic feedback                     |

**Cascade:** Deletes when parent User is deleted **Table:** `user_settings`

---

### Feedback

User feedback, bug reports, feature requests, and content ratings.

| Field          | Type             | Default   | Description                           |
| -------------- | ---------------- | --------- | ------------------------------------- |
| `id`           | `String` (UUID)  | auto      | Primary key                           |
| `userId`       | `String` (UUID)  | -         | FK to User                            |
| `createdAt`    | `DateTime`       | `now()`   | Submission timestamp                  |
| `type`         | `FeedbackType`   | -         | Feedback category                     |
| `rating`       | `Int?`           | -         | Rating (1-5 stars)                    |
| `title`        | `String?`        | -         | Feedback title                        |
| `body`         | `String?`        | -         | Feedback body text                    |
| `meditationId` | `String?` (UUID) | -         | Related meditation (if applicable)    |
| `courseId`     | `String?` (UUID) | -         | Related course (if applicable)        |
| `appVersion`   | `String?`        | -         | App version at time of feedback       |
| `platform`     | `Platform?`      | -         | Platform where feedback was submitted |
| `responseBody` | `String?`        | -         | Admin response text                   |
| `respondedAt`  | `DateTime?`      | -         | Admin response timestamp              |
| `respondedBy`  | `String?`        | -         | Admin user who responded              |
| `status`       | `FeedbackStatus` | `PENDING` | Feedback processing status            |

**Indexes:** `userId`, `type`, `status` **Cascade:** Deletes when parent User is
deleted **Table:** `feedback`

---

## Enums

### UserStatus

| Value                  | Description                 |
| ---------------------- | --------------------------- |
| `ACTIVE`               | Active account              |
| `SUSPENDED`            | Account suspended by admin  |
| `DELETED`              | Soft-deleted account        |
| `PENDING_VERIFICATION` | Awaiting email verification |

### ExperienceLevel

| Value          | Description                |
| -------------- | -------------------------- |
| `BEGINNER`     | New to meditation          |
| `INTERMEDIATE` | Some meditation experience |
| `ADVANCED`     | Experienced meditator      |

### SubscriptionStatus

| Value          | Description                           |
| -------------- | ------------------------------------- |
| `FREE`         | Free tier (no active subscription)    |
| `TRIAL`        | Free trial period                     |
| `ACTIVE`       | Active paid subscription              |
| `CANCELLED`    | Cancelled (still active until expiry) |
| `EXPIRED`      | Subscription has expired              |
| `GRACE_PERIOD` | Payment failed, in grace period       |

### SubscriptionPlan

| Value      | Description                |
| ---------- | -------------------------- |
| `FREE`     | Free tier                  |
| `MONTHLY`  | Monthly subscription       |
| `YEARLY`   | Annual subscription        |
| `LIFETIME` | One-time lifetime purchase |

### Platform

| Value     | Description    |
| --------- | -------------- |
| `IOS`     | Apple iOS      |
| `ANDROID` | Google Android |
| `WEB`     | Web browser    |

### PaymentStatus

| Value       | Description        |
| ----------- | ------------------ |
| `PENDING`   | Payment processing |
| `COMPLETED` | Payment successful |
| `FAILED`    | Payment failed     |
| `REFUNDED`  | Payment refunded   |

### MeditationCategory

| Value             | Description               |
| ----------------- | ------------------------- |
| `MEDITATION`      | General guided meditation |
| `SLEEP`           | Sleep-focused sessions    |
| `BREATHING`       | Breathing exercises       |
| `FOCUS`           | Concentration and focus   |
| `STRESS`          | Stress relief             |
| `MORNING`         | Morning routines          |
| `SELF_COMPASSION` | Self-compassion practices |
| `GRATITUDE`       | Gratitude exercises       |
| `BODY_SCANS`      | Body scan meditations     |
| `WALKING`         | Walking meditation        |
| `KIDS`            | Children's meditation     |
| `SOUNDS`          | Sound-based sessions      |

### DifficultyLevel

| Value          | Description                   |
| -------------- | ----------------------------- |
| `BEGINNER`     | Suitable for beginners        |
| `INTERMEDIATE` | Requires some experience      |
| `ADVANCED`     | For experienced practitioners |

### SoundCategory

| Value         | Description                |
| ------------- | -------------------------- |
| `NATURE`      | General nature sounds      |
| `RAIN`        | Rain sounds                |
| `OCEAN`       | Ocean and wave sounds      |
| `FOREST`      | Forest and woodland sounds |
| `AMBIENT`     | Ambient textures           |
| `WHITE_NOISE` | White noise                |
| `MUSIC`       | Instrumental music         |

### LessonContentType

| Value        | Description                     |
| ------------ | ------------------------------- |
| `MEDITATION` | A meditation audio session      |
| `TEXT`       | Text content (reading material) |
| `EXERCISE`   | Interactive exercise            |

### ProgressStatus

| Value         | Description           |
| ------------- | --------------------- |
| `NOT_STARTED` | Not yet started       |
| `IN_PROGRESS` | Currently in progress |
| `COMPLETED`   | Fully completed       |

### AchievementType

| Value               | Description                         |
| ------------------- | ----------------------------------- |
| `STREAK_DAYS`       | Consecutive meditation days         |
| `TOTAL_SESSIONS`    | Total number of sessions            |
| `TOTAL_MINUTES`     | Total meditation minutes            |
| `COURSES_COMPLETED` | Number of courses completed         |
| `CATEGORY_MASTERY`  | Mastery of a specific category      |
| `SPECIAL`           | Special or event-based achievements |

### AchievementRarity

| Value       | Description                        |
| ----------- | ---------------------------------- |
| `COMMON`    | Common (easily achievable)         |
| `UNCOMMON`  | Uncommon (moderate effort)         |
| `RARE`      | Rare (significant effort)          |
| `EPIC`      | Epic (major commitment)            |
| `LEGENDARY` | Legendary (exceptional dedication) |

### DownloadStatus

| Value         | Description                           |
| ------------- | ------------------------------------- |
| `PENDING`     | Download queued                       |
| `DOWNLOADING` | Download in progress                  |
| `COMPLETED`   | Download complete                     |
| `FAILED`      | Download failed                       |
| `EXPIRED`     | Download expired (subscription ended) |

### NotificationType

| Value         | Description                     |
| ------------- | ------------------------------- |
| `REMINDER`    | Daily meditation reminder       |
| `STREAK`      | Streak milestone notification   |
| `ACHIEVEMENT` | Achievement earned notification |
| `NEW_CONTENT` | New content available           |
| `PROMOTION`   | Promotional notification        |
| `SYSTEM`      | System notification             |

### Theme

| Value    | Description              |
| -------- | ------------------------ |
| `LIGHT`  | Light theme              |
| `DARK`   | Dark theme               |
| `SYSTEM` | Follow system preference |

### FeedbackType

| Value             | Description                                |
| ----------------- | ------------------------------------------ |
| `BUG_REPORT`      | Bug report                                 |
| `FEATURE_REQUEST` | Feature request                            |
| `CONTENT_RATING`  | Rating for a specific meditation or course |
| `APP_REVIEW`      | General app review                         |
| `GENERAL`         | General feedback                           |

### FeedbackStatus

| Value         | Description                |
| ------------- | -------------------------- |
| `PENDING`     | Not yet reviewed           |
| `REVIEWED`    | Reviewed by admin          |
| `IN_PROGRESS` | Being addressed            |
| `RESOLVED`    | Issue resolved             |
| `CLOSED`      | Closed (no further action) |

---

## Relationships Summary

| Parent Model | Child Model       | Cardinality | Cascade Delete |
| ------------ | ----------------- | ----------- | -------------- |
| User         | Profile           | 1:1         | Yes            |
| User         | Subscription      | 1:1         | Yes            |
| User         | Streak            | 1:1         | Yes            |
| User         | UserSettings      | 1:1         | Yes            |
| User         | Device            | 1:N         | Yes            |
| User         | MeditationSession | 1:N         | Yes            |
| User         | UserProgress      | 1:N         | Yes            |
| User         | UserAchievement   | 1:N         | Yes            |
| User         | Favorite          | 1:N         | Yes            |
| User         | Download          | 1:N         | Yes            |
| User         | HistoryEntry      | 1:N         | Yes            |
| User         | Notification      | 1:N         | Yes            |
| User         | Feedback          | 1:N         | Yes            |
| Subscription | Payment           | 1:N         | Yes            |
| Teacher      | Meditation        | 1:N         | No             |
| Teacher      | Course            | 1:N         | No             |
| AmbientSound | Meditation        | 1:N         | No             |
| Meditation   | MeditationSession | 1:N         | No             |
| Meditation   | Favorite          | 1:N         | Yes            |
| Meditation   | Download          | 1:N         | Yes            |
| Meditation   | HistoryEntry      | 1:N         | Yes            |
| Meditation   | LessonContent     | 1:N         | No             |
| Meditation   | RelatedMeditation | N:M (self)  | Yes            |
| Course       | Lesson            | 1:N         | Yes            |
| Course       | UserProgress      | 1:N         | Yes            |
| Course       | Favorite          | 1:N         | Yes            |
| Lesson       | LessonContent     | 1:N         | Yes            |
| Lesson       | UserProgress      | 1:N         | Yes            |
| Achievement  | UserAchievement   | 1:N         | No             |

---

## PostgreSQL Extensions

The schema requires the following PostgreSQL extensions (configured in the
datasource block):

| Extension   | Purpose                                  |
| ----------- | ---------------------------------------- |
| `uuid-ossp` | UUID generation (`uuid_generate_v4()`)   |
| `pgcrypto`  | Cryptographic functions                  |
| `vector`    | pgvector for embedding similarity search |
| `pg_trgm`   | Trigram-based fuzzy text search          |

---

## Migration Workflow

### Development

```bash
# Generate Prisma client after schema changes
pnpm nx run @tara/database:prisma:generate

# Create and apply a new migration
pnpm nx run @tara/database:prisma:migrate:dev

# Push schema changes without creating a migration (rapid iteration)
cd libs/tara/database && npx prisma db push

# Reset database (drops all data, re-applies migrations)
cd libs/tara/database && npx prisma migrate reset

# Open Prisma Studio for visual browsing
pnpm nx run @tara/database:prisma:studio
```

### Production

```bash
# Apply pending migrations (non-interactive)
pnpm nx run @tara/database:prisma:migrate:deploy
```

### Seeding

```bash
# Seed with standard content
pnpm nx run @tara/database:db:seed

# Seed with test fixtures
pnpm nx run @tara/database:db:seed:test
```

Seed scripts are located at:

- `libs/tara/database/prisma/seed.ts` - Standard content (teachers, meditations,
  courses, sounds)
- `libs/tara/database/prisma/seed-test.ts` - Test fixtures for integration
  testing
