Key Relations

This document covers the most important database relationships, multi-tenancy patterns, and performance indexes in the UniPulse schema.

Multi-Tenancy: Workspace Scoping

Nearly every model has a workspaceId foreign key. This is the primary isolation boundary for multi-tenancy. All database queries must include workspaceId to prevent data leaks between workspaces.

model Post {
  id          String    @id @default(cuid())
  workspaceId String
  workspace   Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade)
  caption     String
  status      PostStatus
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  @@index([workspaceId])
  @@index([workspaceId, createdAt])
}

Always Filter by workspaceId

Every service query must include where: { workspaceId }. The requireWorkspace middleware attaches the workspace to the request, and services receive it as a parameter. Forgetting this filter is a security vulnerability that exposes data across tenants.

User to Workspace (Many-to-Many via WorkspaceMember)

A user can belong to multiple workspaces, each with a different role:

model User {
  id          String            @id @default(cuid())
  email       String            @unique
  name        String
  memberships WorkspaceMember[]
}

model Workspace {
  id      String            @id @default(cuid())
  name    String
  slug    String            @unique
  ownerId String
  members WorkspaceMember[]
}

model WorkspaceMember {
  userId      String
  workspaceId String
  role        Role     // OWNER, ADMIN, EDITOR, VIEWER, CLIENT
  user        User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  workspace   Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade)
  joinedAt    DateTime  @default(now())

  @@id([userId, workspaceId])
}

enum Role {
  OWNER
  ADMIN
  EDITOR
  VIEWER
  CLIENT
}

Post to Publication (One-to-Many)

A single post can be published to multiple platforms. Each platform publication is tracked independently with its own metrics:

model Post {
  id           String            @id @default(cuid())
  workspaceId  String
  caption      String
  publications PostPublication[]
  media        PostMedia[]
  schedule     Schedule?
}

model PostPublication {
  id             String       @id @default(cuid())
  postId         String
  post           Post         @relation(fields: [postId], references: [id], onDelete: Cascade)
  platform       Platform     // FACEBOOK, INSTAGRAM, TIKTOK
  platformPostId String?      // External ID from the platform
  status         PublishStatus
  publishedAt    DateTime?
  metrics        PostMetric[]

  @@index([postId])
  @@index([platform, status])
}

model PostMetric {
  id            String          @id @default(cuid())
  publicationId String
  publication   PostPublication @relation(fields: [publicationId], references: [id], onDelete: Cascade)
  likes         Int             @default(0)
  comments      Int             @default(0)
  shares        Int             @default(0)
  reach         Int             @default(0)
  impressions   Int             @default(0)
  date          DateTime

  @@index([publicationId, date])
}

Subscription to Plan (with Feature Gating)

model Subscription {
  id               String    @id @default(cuid())
  workspaceId      String    @unique
  workspace        Workspace @relation(fields: [workspaceId], references: [id])
  planId           String
  plan             Plan      @relation(fields: [planId], references: [id])
  status           SubStatus // ACTIVE, CANCELLED, PAST_DUE
  currentPeriodEnd DateTime
}

model Plan {
  id       String        @id @default(cuid())
  name     String
  price    Float
  features PlanFeature[]
}

model PlanFeature {
  id        String  @id @default(cuid())
  planId    String
  plan      Plan    @relation(fields: [planId], references: [id])
  featureId String
  feature   Feature @relation(fields: [featureId], references: [id])
  limit     Int?    // null = unlimited

  @@unique([planId, featureId])
}

model Feature {
  id   String @id @default(cuid())
  key  String @unique  // e.g., "ai_suggestions", "publishing", "ads"
  name String
}

E-Commerce to Revenue Attribution

model RevenueAttribution {
  id        String       @id @default(cuid())
  postId    String
  post      Post         @relation(fields: [postId], references: [id])
  orderId   String
  order     CommerceOrder @relation(fields: [orderId], references: [id])
  amount    Float
  utmSource String?
  utmMedium String?
  utmCampaign String?
  createdAt DateTime     @default(now())

  @@index([postId])
  @@index([orderId])
}

Conversation Thread to Memory

model ConversationThread {
  id          String              @id @default(cuid())
  workspaceId String
  platform    Platform
  status      ThreadStatus        // OPEN, RESOLVED, ESCALATED
  assignedTo  String?
  messages    ConversationMessage[]
  escalations Escalation[]
}

model ConversationMemory {
  id              String       @id @default(cuid())
  audienceNodeId  String
  audienceNode    AudienceNode @relation(fields: [audienceNodeId], references: [id])
  tier            MemoryTier   // SHORT_TERM, MEDIUM_TERM, LONG_TERM
  data            Json
  expiresAt       DateTime?

  @@index([audienceNodeId, tier])
}

Key Indexes for Performance

Table	Index	Purpose
`Post`	`(workspaceId)`	List all posts in a workspace
`Post`	`(workspaceId, createdAt)`	Paginated post listing sorted by date
`PostMetric`	`(publicationId, date)`	Time-series analytics queries
`PostPublication`	`(postId)`	Find all publications for a post
`PostPublication`	`(platform, status)`	Filter by platform and publish status
`Schedule`	`(scheduledAt, status)`	Find posts due for publishing
`AudienceNode`	`(workspaceId, engagementScore)`	Audience sorted by engagement
`ConversationThread`	`(workspaceId, status)`	Inbox filtering
`ConversationMemory`	`(audienceNodeId, tier)`	Memory retrieval by tier
`RevenueAttribution`	`(postId)`	Revenue per post lookup
`RevenueAttribution`	`(orderId)`	Attribution per order
`AiPromptLog`	`(workspaceId, createdAt)`	AI usage tracking
`CompetitorSnapshot`	`(competitorId, date)`	Competitor trend analysis
`WorkspaceUsage`	`(workspaceId, period)`	Quota checking
`AuditLog`	`(workspaceId, createdAt)`	Audit log queries

Cascade Delete Strategy

Parent	Children	On Delete
`Workspace`	All workspace-scoped models	`Cascade`
`Post`	`PostPublication`, `PostMedia`, `Schedule`, `PostClassification`	`Cascade`
`PostPublication`	`PostMetric`	`Cascade`
`ConversationThread`	`ConversationMessage`, `Escalation`	`Cascade`
`EcommerceStore`	`EcommerceProduct`, `CommerceOrder`	`Cascade`
`Workflow`	`WorkflowRun`	`Cascade`
`User`	`WorkspaceMember` (membership removed, not user)	`Cascade`

Cascade Awareness

Deleting a workspace cascades to all its data (posts, analytics, conversations, etc.). This is intentional for clean workspace removal but means workspace deletion is irreversible. The API should require confirmation for this operation.

Cross-Reference

Schema Overview -- all 69+ models
ER Diagram -- visual relationships
Auth Flow -- RBAC roles and workspace isolation
Middleware -- requireWorkspace middleware

Multi-Tenancy: Workspace Scoping​

User to Workspace (Many-to-Many via WorkspaceMember)​

Post to Publication (One-to-Many)​

Subscription to Plan (with Feature Gating)​

E-Commerce to Revenue Attribution​

Conversation Thread to Memory​

Key Indexes for Performance​

Cascade Delete Strategy​