Eve library

Aphrodite Domain - Database Schemas

Guide

Revision 1

On this page

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
  2. PostgreSQL (Prisma)
  3. MongoDB Collections
  4. ClickHouse Analytics
  5. Redis Data Structures
  6. Data Flow Relationships
  7. 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#

text
┌─────────────────────────────────────────────────────────────────┐
│                        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#

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

Content Access Flow#

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

Analytics Flow#

text
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