# Aphrodite Domain - Database Schemas

> **Migration Status:** Eve → Aphrodite (Oshun Domain)
>
> This document provides a complete inventory of all database schemas, data
> models, and storage patterns used in the Eve platform for migration planning.

---

## Table of Contents

1. [Database Overview](#database-overview)
2. [PostgreSQL (Prisma)](#postgresql-prisma)
3. [MongoDB Collections](#mongodb-collections)
4. [ClickHouse Analytics](#clickhouse-analytics)
5. [Redis Data Structures](#redis-data-structures)
6. [Data Flow Relationships](#data-flow-relationships)
7. [Migration Considerations](#migration-considerations)

---

## Database Overview

The Eve platform uses a polyglot persistence architecture with databases
optimized for specific workloads:

| Database       | Engine        | Purpose                           | Data Volume    |
| -------------- | ------------- | --------------------------------- | -------------- |
| **PostgreSQL** | Prisma ORM    | Transactional data, subscriptions | ~10M+ records  |
| **MongoDB**    | Native driver | Real-time chat, notifications     | ~100M+ records |
| **ClickHouse** | Native        | Time-series analytics             | ~1B+ events    |
| **Redis**      | ioredis       | Caching, pub/sub                  | ~1GB+          |

### Database Distribution by Service

```
┌─────────────────────────────────────────────────────────────────┐
│                        SERVICE LAYER                            │
├─────────────┬─────────────┬─────────────┬─────────────────────┤
│    User     │    Chat     │   Stream    │      Payment        │
│   Service   │   Service   │   Service   │      Service        │
└──────┬──────┴──────┬──────┴──────┬──────┴──────────┬──────────┘
       │             │             │                 │
       ▼             ▼             ▼                 ▼
┌─────────────┬─────────────┬─────────────┬─────────────────────┐
│ PostgreSQL  │   MongoDB   │   MongoDB   │    PostgreSQL       │
│ (Users,     │ (Messages,  │ (Streams,   │  (Transactions,     │
│  Profiles)  │  DMs)       │  Tickets)   │   Subscriptions)    │
└─────────────┴─────────────┴─────────────┴─────────────────────┘
       │             │             │                 │
       └─────────────┴─────────────┴─────────────────┘
                           │
                           ▼
              ┌─────────────────────────┐
              │    Redis (Caching,      │
              │    Socket.io Adapter)   │
              └─────────────────────────┘
                           │
                           ▼
              ┌─────────────────────────┐
              │   ClickHouse (Analytics │
              │   Time-series Data)     │
              └─────────────────────────┘
```

---

## PostgreSQL (Prisma)

**Location:** `eve/packages/database/prisma/schema.prisma`

**Engine:** PostgreSQL with pgvector extension

### Core User Models

#### User

```prisma
model User {
  id             String              @id @default(uuid())
  email          String              @unique
  createdAt      DateTime            @default(now())

  // Relations
  streams        Stream[]
  transactions   Transaction[]
  contentItems   ContentItem[]       @relation("CreatorContent")
  photoSets      PhotoSet[]          @relation("CreatorPhotoSets")
  purchases      ContentPurchase[]   @relation("BuyerPurchases")
  downloads      DownloadTrack[]
  tiers          SubscriptionTier[]  @relation("CreatorTiers")
  subscriptions  Subscription[]      @relation("SubscriberSubs")
  creatorSubs    Subscription[]      @relation("CreatorSubs")
  fanClubPosts   FanClubPost[]       @relation("CreatorPosts")
  postLikes      PostLike[]
  postComments   PostComment[]
}
```

**Purpose:** Core user identity and authentication

---

#### Stream

```prisma
model Stream {
  id        String   @id @default(uuid())
  title     String
  createdAt DateTime @default(now())
  userId    String

  user      User     @relation(fields: [userId], references: [id])

  @@index([userId])
}
```

**Purpose:** Streaming session records

---

#### Transaction

```prisma
model Transaction {
  id        String   @id @default(uuid())
  amount    Int
  createdAt DateTime @default(now())
  userId    String

  user      User     @relation(fields: [userId], references: [id])
}
```

**Purpose:** Financial transaction logging

---

### Content Store Models

#### ContentItem

```prisma
model ContentItem {
  id            String   @id @default(uuid())
  creatorId     String
  title         String
  description   String?
  type          String   // photo | video | audio
  fileUrl       String
  thumbnailUrl  String?
  previewUrl    String?
  fileSize      Int?
  mimeType      String?
  duration      Int?     // seconds (for video/audio)
  resolution    String?  // e.g., "1920x1080"
  price         Int      @default(0)  // in tokens
  isForSale     Boolean  @default(false)
  isFree        Boolean  @default(false)
  tags          String[] @default([])
  isExplicit    Boolean  @default(false)  // sensual-tier flag (music-video cap)
  watermarked   Boolean  @default(true)
  status        String   @default("active")  // active | hidden | deleted
  publishedAt   DateTime?
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  // Relations
  creator       User             @relation("CreatorContent", fields: [creatorId], references: [id])
  photoSetId    String?
  photoSet      PhotoSet?        @relation(fields: [photoSetId], references: [id])
  purchases     ContentPurchase[]
  downloads     DownloadTrack[]
  license       ContentLicense?

  @@index([creatorId])
  @@index([type])
  @@index([status])
  @@index([publishedAt])
}
```

**Purpose:** User-generated content (photos, videos, audio)

---

#### PhotoSet

```prisma
model PhotoSet {
  id            String   @id @default(uuid())
  creatorId     String
  title         String
  description   String?
  coverImageUrl String?
  price         Int      @default(0)
  isForSale     Boolean  @default(false)
  itemCount     Int      @default(0)
  status        String   @default("active")
  publishedAt   DateTime?
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  creator       User          @relation("CreatorPhotoSets", fields: [creatorId], references: [id])
  items         ContentItem[]

  @@index([creatorId])
  @@index([status])
}
```

**Purpose:** Collection of media items as a bundle

---

#### ContentPurchase

```prisma
model ContentPurchase {
  id            String    @id @default(uuid())
  buyerId       String
  contentItemId String
  amount        Int
  transactionId String?
  status        String    @default("completed")  // completed | refunded
  refundedAt    DateTime?
  refundReason  String?
  expiresAt     DateTime?
  downloadLimit Int?
  downloadCount Int       @default(0)
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt

  buyer         User        @relation("BuyerPurchases", fields: [buyerId], references: [id])
  contentItem   ContentItem @relation(fields: [contentItemId], references: [id])

  @@unique([buyerId, contentItemId])
  @@index([buyerId])
  @@index([contentItemId])
  @@index([status])
}
```

**Purpose:** Track content purchases and access

---

#### ContentLicense

```prisma
model ContentLicense {
  id              String   @id @default(uuid())
  contentItemId   String   @unique
  type            String   // personal | commercial | exclusive
  allowDownload   Boolean  @default(true)
  allowShare      Boolean  @default(false)
  allowCommercial Boolean  @default(false)
  maxDownloads    Int?
  expiryDays      Int?
  geoRestrictions String[] @default([])
  termsText       String?
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  contentItem     ContentItem @relation(fields: [contentItemId], references: [id])
}
```

**Purpose:** Define licensing terms for content

---

#### DownloadTrack

```prisma
model DownloadTrack {
  id            String   @id @default(uuid())
  userId        String
  contentItemId String
  ipAddress     String?
  userAgent     String?
  downloadedAt  DateTime @default(now())
  fileSize      Int?
  downloadSpeed Int?     // bytes per second
  completed     Boolean  @default(true)

  user          User        @relation(fields: [userId], references: [id])
  contentItem   ContentItem @relation(fields: [contentItemId], references: [id])

  @@index([userId])
  @@index([contentItemId])
  @@index([downloadedAt])
}
```

**Purpose:** Track content downloads and access analytics

---

### Subscription & Monetization Models

#### SubscriptionTier

```prisma
model SubscriptionTier {
  id             String   @id @default(uuid())
  creatorId      String
  name           String
  description    String?
  price          Int      // monthly price in tokens
  color          String?  // hex color for UI
  icon           String?  // icon name or URL
  priority       Int      @default(0)  // display order
  isActive       Boolean  @default(true)
  isPublic       Boolean  @default(true)
  maxSubscribers Int?
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

  creator        User               @relation("CreatorTiers", fields: [creatorId], references: [id])
  perks          SubscriptionPerk[]
  subscriptions  Subscription[]

  @@unique([creatorId, name])
  @@index([creatorId])
  @@index([isActive])
}
```

**Purpose:** Define creator subscription tiers (Bronze, Silver, Gold, etc.)

---

#### SubscriptionPerk

```prisma
model SubscriptionPerk {
  id          String   @id @default(uuid())
  tierId      String
  type        String   // badge | exclusive_content | discount | priority_chat | custom
  name        String
  description String?
  value       Json?    // type-specific config
  icon        String?
  priority    Int      @default(0)
  isActive    Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  tier        SubscriptionTier @relation(fields: [tierId], references: [id], onDelete: Cascade)

  @@index([tierId])
  @@index([type])
}
```

**Purpose:** Define benefits/perks for subscription tiers

---

#### Subscription

```prisma
model Subscription {
  id                  String    @id @default(uuid())
  subscriberId        String
  creatorId           String
  tierId              String
  status              String    @default("active")  // active | cancelled | expired | paused
  startedAt           DateTime  @default(now())
  expiresAt           DateTime?
  cancelledAt         DateTime?
  cancellationReason  String?
  currentPeriodStart  DateTime  @default(now())
  currentPeriodEnd    DateTime
  nextBillingDate     DateTime?
  billingCycle        String    @default("monthly")  // monthly | yearly
  price               Int
  totalPaid           Int       @default(0)
  billingCount        Int       @default(0)
  lastPaymentAt       DateTime?
  lastPaymentId       String?
  autoRenew           Boolean   @default(true)
  renewalFailures     Int       @default(0)
  createdAt           DateTime  @default(now())
  updatedAt           DateTime  @updatedAt

  subscriber          User                 @relation("SubscriberSubs", fields: [subscriberId], references: [id])
  creator             User                 @relation("CreatorSubs", fields: [creatorId], references: [id])
  tier                SubscriptionTier     @relation(fields: [tierId], references: [id])
  billingHistory      SubscriptionBilling[]

  @@unique([subscriberId, creatorId])
  @@index([subscriberId])
  @@index([creatorId])
  @@index([tierId])
  @@index([status])
  @@index([nextBillingDate])
}
```

**Purpose:** Track creator subscriptions with billing and auto-renewal

---

#### SubscriptionBilling

```prisma
model SubscriptionBilling {
  id             String    @id @default(uuid())
  subscriptionId String
  amount         Int
  status         String    @default("pending")  // pending | completed | failed | refunded
  transactionId  String?
  paymentMethod  String?
  periodStart    DateTime
  periodEnd      DateTime
  billedAt       DateTime?
  failureReason  String?
  retryCount     Int       @default(0)
  nextRetryAt    DateTime?
  refundedAt     DateTime?
  refundAmount   Int?
  refundReason   String?
  createdAt      DateTime  @default(now())
  updatedAt      DateTime  @updatedAt

  subscription   Subscription @relation(fields: [subscriptionId], references: [id], onDelete: Cascade)

  @@index([subscriptionId])
  @@index([status])
  @@index([billedAt])
  @@index([nextRetryAt])
}
```

**Purpose:** Track individual billing transactions for subscriptions

---

### Fan Club & Community Models

#### FanClubPost

```prisma
model FanClubPost {
  id             String    @id @default(uuid())
  creatorId      String
  content        String
  mediaUrls      String[]  @default([])
  mediaType      String?   // photo | video | mixed
  subscriberOnly Boolean   @default(false)
  tierIds        String[]  @default([])  // required tier IDs for access
  likeCount      Int       @default(0)
  commentCount   Int       @default(0)
  viewCount      Int       @default(0)
  status         String    @default("published")  // draft | published | archived
  pinnedUntil    DateTime?
  createdAt      DateTime  @default(now())
  updatedAt      DateTime  @updatedAt
  publishedAt    DateTime?

  creator        User          @relation("CreatorPosts", fields: [creatorId], references: [id])
  likes          PostLike[]
  comments       PostComment[]

  @@index([creatorId])
  @@index([status])
  @@index([subscriberOnly])
  @@index([createdAt])
}
```

**Purpose:** Creator posts in fan club section

---

#### PostLike

```prisma
model PostLike {
  id        String   @id @default(uuid())
  userId    String
  postId    String
  createdAt DateTime @default(now())

  user      User        @relation(fields: [userId], references: [id])
  post      FanClubPost @relation(fields: [postId], references: [id], onDelete: Cascade)

  @@unique([userId, postId])
  @@index([postId])
  @@index([userId])
}
```

**Purpose:** Track likes on fan club posts

---

#### PostComment

```prisma
model PostComment {
  id        String   @id @default(uuid())
  userId    String
  postId    String
  content   String
  isEdited  Boolean  @default(false)
  isDeleted Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  user      User        @relation(fields: [userId], references: [id])
  post      FanClubPost @relation(fields: [postId], references: [id], onDelete: Cascade)

  @@index([postId])
  @@index([userId])
  @@index([createdAt])
}
```

**Purpose:** Comments on fan club posts

---

## MongoDB Collections

### Connection Configuration

MongoDB databases are organized by service tier with different connection pool
configurations:

| Tier     | Database                        | Connections | Idle Timeout | Queue Timeout |
| -------- | ------------------------------- | ----------- | ------------ | ------------- |
| CRITICAL | `eve_chat`, `eve_notifications` | 30-200      | 45s          | 8s            |
| HIGH     | `eve_stream`                    | 20-150      | 60s          | 10s           |
| MEDIUM   | `eve_payments`, `eve_users`     | 10-80       | 60s          | 10s           |
| LOW      | `eve_analytics`                 | 5-20        | 90s          | 15s           |

### Chat Service Collections

#### chat_messages

```typescript
interface ChatMessage {
  _id: ObjectId;
  roomId: string;
  userId: string;
  username: string;
  avatarUrl?: string;
  message: string;
  type: 'text' | 'tip' | 'system';
  timestamp: Date;
  amount?: number; // for tip messages
  metadata?: Record<string, unknown>;
  mentions?: string[];
  createdAt: Date;
}

// Indexes
db.chat_messages.createIndex({ roomId: 1, timestamp: -1 });
db.chat_messages.createIndex({ userId: 1 });
```

**Purpose:** Real-time chat messages

---

#### conversations

```typescript
interface Conversation {
  _id: ObjectId;
  participants: string[]; // [userId1, userId2]
  createdAt: Date;
  updatedAt: Date;
  lastMessageAt?: Date;
  lastMessage?: {
    content: string;
    senderId: string;
    type: string;
  };
  blockedBy: string[]; // users who blocked the conversation
}

// Indexes
db.conversations.createIndex({ participants: 1 });
db.conversations.createIndex({ updatedAt: -1 });
```

**Purpose:** Direct message conversations

---

#### dm_messages

```typescript
interface DMMessage {
  _id: ObjectId;
  conversationId: ObjectId;
  senderId: string;
  content: string;
  messageType: 'text' | 'media' | 'tip' | 'system';
  mediaUrl?: string;
  mediaType?: 'image' | 'video' | 'audio';
  readBy: string[];
  deletedBy: string[];
  createdAt: Date;
  updatedAt: Date;
  isDeleted: boolean;
}

// Indexes
db.dm_messages.createIndex({ conversationId: 1, createdAt: -1 });
db.dm_messages.createIndex({ senderId: 1 });
```

**Purpose:** Direct messages with read receipts

---

### Notification Service Collections

#### notifications

```typescript
interface Notification {
  _id: ObjectId;
  userId: string;
  type: string; // follower, tip, subscription, etc.
  title: string;
  message: string;
  status: 'pending' | 'sent' | 'read' | 'failed';
  priority: 'low' | 'normal' | 'high' | 'urgent';
  data?: Record<string, unknown>;
  createdAt: Date;
  sentAt?: Date;
  readAt?: Date;
}

// Indexes
db.notifications.createIndex({ userId: 1, createdAt: -1 });
db.notifications.createIndex({ userId: 1, status: 1 });
```

**Purpose:** User notifications with delivery tracking

---

#### notification_queue

```typescript
interface NotificationQueueItem {
  _id: ObjectId;
  notificationId: ObjectId;
  userId: string;
  channel: 'push' | 'email' | 'sms' | 'in_app';
  priority: number;
  status: 'pending' | 'processing' | 'sent' | 'failed';
  attempts: number;
  maxAttempts: number;
  scheduledFor: Date;
  nextRetryAt?: Date;
  error?: string;
  createdAt: Date;
}

// Indexes
db.notification_queue.createIndex({ status: 1, nextRetryAt: 1 });
db.notification_queue.createIndex({ priority: -1 });
```

**Purpose:** Queue for pending notifications

---

### Stream Service Collections

#### streams

```typescript
interface Stream {
  _id: ObjectId;
  creatorId: string;
  creatorUsername: string;
  creatorAvatarUrl?: string;
  title: string;
  description?: string;
  status: 'scheduled' | 'live' | 'ended';
  viewerCount: number;
  thumbnailUrl?: string;
  category?: string;
  tags: string[];
  scheduledStart?: Date;
  startedAt?: Date;
  endedAt?: Date;
  createdAt: Date;
}

// Indexes
db.streams.createIndex({ creatorId: 1 });
db.streams.createIndex({ status: 1, createdAt: -1 });
db.streams.createIndex({ viewerCount: -1 });
```

**Purpose:** Stream metadata and status tracking

---

#### ticket_shows

```typescript
interface TicketShow {
  id: string;
  creatorId: string;
  title: string;
  description?: string;
  thumbnailUrl?: string;
  scheduledStartTime: Date;
  estimatedDuration: number; // minutes
  totalCapacity?: number;
  ticketsSold: number;
  currentViewers: number;
  generalTicketPrice: number;
  vipTicketPrice?: number;
  premiumTicketPrice?: number;
  status: 'scheduled' | 'live' | 'ended' | 'cancelled';
  totalRevenue: number;
  refundedAmount: number;
  allowRecording: boolean;
  createdAt: Date;
}
```

**Purpose:** Ticket show configuration

---

#### tickets

```typescript
interface Ticket {
  id: string;
  showId: string;
  viewerId: string;
  viewerUsername: string;
  tier: 'general' | 'vip' | 'premium';
  price: number;
  status: 'purchased' | 'used' | 'refunded' | 'expired';
  perks: string[];
  purchasedAt: Date;
  attendedAt?: Date;
  refundedAt?: Date;
}

// Indexes
db.tickets.createIndex({ showId: 1, viewerId: 1 });
db.tickets.createIndex({ showId: 1, status: 1 });
```

**Purpose:** Individual ticket records

---

### User Service Collections

#### users (Extended Profile)

```typescript
interface UserProfile {
  _id: ObjectId;
  username: string;
  email: string;
  avatarUrl?: string;
  bio?: string;
  isVerified: boolean;
  followerCount: number;
  followingCount: number;
  streamCount: number;
  totalViews: number;
  createdAt: Date;
  passwordHash?: string;
  resetToken?: string;
  emailVerificationToken?: string;
}

// Indexes
db.users.createIndex({ username: 1 }, { unique: true });
db.users.createIndex({ email: 1 }, { unique: true });

// Projection presets
const PROJECTIONS = {
  minimal: { _id: 1, username: 1 },
  basic: { _id: 1, username: 1, avatarUrl: 1, isVerified: 1 },
  profile: {
    _id: 1,
    username: 1,
    avatarUrl: 1,
    bio: 1,
    isVerified: 1,
    followerCount: 1,
  },
  safe: { passwordHash: 0, resetToken: 0, emailVerificationToken: 0 },
};
```

**Purpose:** Extended user profile data

---

## ClickHouse Analytics

**Location:** `eve/apps/analytics-service/src/analytics/`

**Engine:** MergeTree family with automatic partitioning

### Tables

#### analytics_events

```sql
CREATE TABLE analytics_events (
  id            String,
  type          String,
  timestamp     DateTime64(3),
  user_id       Nullable(String),
  creator_id    Nullable(String),
  stream_id     Nullable(String),
  properties    String,  -- JSON
  ip_address    Nullable(String),
  user_agent    Nullable(String),
  country       Nullable(String),
  city          Nullable(String),
  referrer      Nullable(String),
  date          Date MATERIALIZED toDate(timestamp),
  hour          UInt8 MATERIALIZED toHour(timestamp)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (type, creator_id, timestamp)
TTL date + INTERVAL 90 DAY;
```

**Event Types:**

- `stream.started`, `stream.ended`
- `viewer.joined`, `viewer.left`
- `tip.sent`, `message.sent`
- `subscription.created`, `subscription.renewed`, `subscription.cancelled`
- `content.purchased`, `content.viewed`
- `follower.added`, `follower.removed`

---

#### stream_metrics

```sql
CREATE TABLE stream_metrics (
  stream_id         String,
  creator_id        String,
  date              Date,
  total_viewers     UInt32,
  unique_viewers    UInt32,
  peak_viewers      UInt32,
  avg_view_duration Float32,
  total_tips        UInt32,
  total_tip_amount  Float32,
  total_messages    UInt32,
  stream_duration   UInt32
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (creator_id, date, stream_id)
TTL date + INTERVAL 365 DAY;
```

---

#### creator_daily_metrics

```sql
CREATE TABLE creator_daily_metrics (
  creator_id        String,
  date              Date,
  total_streams     UInt32,
  total_stream_time UInt32,
  total_viewers     UInt32,
  unique_viewers    UInt32,
  total_earnings    Float32,
  total_tips        UInt32,
  new_followers     UInt32,
  new_subscribers   UInt32
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (creator_id, date)
TTL date + INTERVAL 730 DAY;
```

---

#### viewer_metrics

```sql
CREATE TABLE viewer_metrics (
  viewer_id        String,
  date             Date,
  total_watch_time UInt32,
  streams_watched  UInt32,
  total_tipped     Float32,
  messages_count   UInt32
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (viewer_id, date)
TTL date + INTERVAL 365 DAY;
```

---

### Materialized Views

#### stream_metrics_mv

```sql
CREATE MATERIALIZED VIEW stream_metrics_mv
TO stream_metrics
AS SELECT
  stream_id,
  creator_id,
  toDate(timestamp) AS date,
  countIf(type = 'viewer.joined') AS total_viewers,
  uniqExact(user_id) AS unique_viewers,
  avgIf(
    JSONExtractFloat(properties, 'duration'),
    type = 'viewer.left'
  ) AS avg_view_duration,
  countIf(type = 'tip.sent') AS total_tips,
  sumIf(
    JSONExtractFloat(properties, 'amount'),
    type = 'tip.sent'
  ) AS total_tip_amount,
  countIf(type = 'message.sent') AS total_messages,
  maxIf(
    JSONExtractUInt(properties, 'duration'),
    type = 'stream.ended'
  ) AS stream_duration
FROM analytics_events
WHERE stream_id IS NOT NULL
GROUP BY stream_id, creator_id, date;
```

---

#### creator_daily_metrics_mv

```sql
CREATE MATERIALIZED VIEW creator_daily_metrics_mv
TO creator_daily_metrics
AS SELECT
  creator_id,
  toDate(timestamp) AS date,
  countDistinctIf(stream_id, type = 'stream.started') AS total_streams,
  sumIf(
    JSONExtractUInt(properties, 'duration'),
    type = 'stream.ended'
  ) AS total_stream_time,
  countIf(type = 'viewer.joined') AS total_viewers,
  uniqExact(user_id) AS unique_viewers,
  sumIf(
    JSONExtractFloat(properties, 'earnings'),
    type IN ('tip.sent', 'subscription.created', 'content.purchased')
  ) AS total_earnings,
  countIf(type = 'tip.sent') AS total_tips,
  countIf(type = 'follower.added') AS new_followers,
  countIf(type = 'subscription.created') AS new_subscribers
FROM analytics_events
WHERE creator_id IS NOT NULL
GROUP BY creator_id, date;
```

---

## Redis Data Structures

### Key Patterns

| Pattern                   | Type          | TTL      | Purpose                 |
| ------------------------- | ------------- | -------- | ----------------------- |
| `query:<hash>`            | String (JSON) | Variable | Cached query results    |
| `query:tag:<name>`        | Set           | -        | Tag-based invalidation  |
| `session:<id>`            | Hash          | 24h      | User sessions           |
| `goal:<id>`               | Hash          | 1h       | Stream goal state       |
| `ratelimit:<user>:<room>` | String        | 60s      | Rate limiting           |
| `features:<user>`         | Hash          | 5m       | Recommendation features |

### Cache TTL Strategy

```typescript
const CACHE_TTL = {
  // Static data (long TTL)
  userProfile: 600, // 10 minutes
  creatorProfile: 600, // 10 minutes
  showDetails: 300, // 5 minutes
  categories: 3600, // 1 hour
  featureFlags: 300, // 5 minutes

  // Dynamic data (medium TTL)
  streamList: 10, // 10 seconds
  viewerCount: 5, // 5 seconds
  notifications: 30, // 30 seconds
  conversationList: 60, // 1 minute
  ticketAvailability: 10, // 10 seconds

  // Real-time data (no/minimal cache)
  chatMessages: 0, // no cache
  tokenBalance: 0, // no cache
  liveStreamStatus: 5, // 5 seconds
};
```

### Cache Entry Structure

```typescript
interface CacheEntry<T> {
  data: T;
  cachedAt: string; // ISO timestamp
  expiresAt: string; // ISO timestamp
  tags: string[]; // for invalidation
}
```

### Tag-Based Invalidation

```typescript
// Store cache with tags
async function cacheWithTags(
  key: string,
  data: unknown,
  tags: string[]
): Promise<void> {
  const entry: CacheEntry = { data, cachedAt: new Date().toISOString(), tags };
  await redis.set(key, JSON.stringify(entry), 'EX', ttl);

  // Add key to each tag set
  for (const tag of tags) {
    await redis.sadd(`query:tag:${tag}`, key);
  }
}

// Invalidate by tag
async function invalidateTag(tag: string): Promise<void> {
  const keys = await redis.smembers(`query:tag:${tag}`);
  if (keys.length > 0) {
    await redis.del(...keys);
  }
  await redis.del(`query:tag:${tag}`);
}
```

---

## Data Flow Relationships

### Financial Transaction Flow

```
User
  │
  ├──► Transaction ──► SubscriptionBilling
  │                          │
  ├──► ContentPurchase ◄─────┤
  │         │               Subscription
  │         ▼                    │
  │    DownloadTrack             ▼
  │                       SubscriptionPerk
  │
  └──► ContentLicense (rules)
```

### Content Access Flow

```
ContentItem ◄── PhotoSet
    │
    ▼
ContentPurchase ──► User
    │
    ▼
DownloadTrack
    │
    ▼
ContentLicense (rules)
```

### Analytics Flow

```
Raw Events (MongoDB/Services)
         │
         ▼
analytics_events (ClickHouse)
         │
         ├──► stream_metrics_mv ──► stream_metrics
         │
         ├──► creator_daily_metrics_mv ──► creator_daily_metrics
         │
         └──► viewer_metrics
```

---

## Migration Considerations

### Schema Mapping to Oshun

| Eve Schema       | Oshun Target    | Package                    |
| ---------------- | --------------- | -------------------------- |
| User             | User (extended) | `@aphrodite/database`      |
| ContentItem      | Asset           | `@oshun/types`             |
| SubscriptionTier | Tier            | `@aphrodite/subscriptions` |
| FanClubPost      | Post            | `@aphrodite/community`     |
| analytics_events | Event           | `@oshun/analytics`         |

### Migration Steps

1. **Export PostgreSQL schemas** to Prisma format compatible with Oshun
2. **Map MongoDB collections** to Oshun document stores
3. **Migrate ClickHouse** schemas with updated table prefixes
4. **Update Redis key patterns** to use `aphrodite:` prefix

### Data Retention Policies

| Data Type       | Current TTL | Recommended |
| --------------- | ----------- | ----------- |
| Raw events      | 90 days     | 90 days     |
| Stream metrics  | 365 days    | 365 days    |
| Creator metrics | 730 days    | 730 days    |
| Chat messages   | Forever     | 365 days    |
| Notifications   | Forever     | 90 days     |

---

## Document History

| Date       | Version | Author | Changes                           |
| ---------- | ------- | ------ | --------------------------------- |
| 2026-01-16 | 1.0.0   | Claude | Initial database schema inventory |
