Database Schema Overview
The database uses PostgreSQL 16 with Prisma ORM 6. The schema contains 69+ models across approximately 1940 lines, located at Pulse/apps/api/prisma/schema.prisma.
Model Inventory by Domain
Core (Users & Workspaces) -- 5 Models
| Model | Purpose | Key Fields |
|---|---|---|
User | Platform users | id, email, name, passwordHash, isSuperAdmin |
OtpCode | One-time password codes for verification | userId, code, type, expiresAt |
Workspace | Multi-tenant workspace | id, name, slug, ownerId |
WorkspaceMember | User-workspace membership with role | userId, workspaceId, role (OWNER/ADMIN/EDITOR/VIEWER/CLIENT) |
WorkspaceInvitation | Pending workspace invitations | email, workspaceId, role, token |
Billing & Plans -- 6 Models
| Model | Purpose | Key Fields |
|---|---|---|
Plan | Subscription plan definitions | id, name, price, interval |
Feature | Gatable platform features | id, key, name |
PlanFeature | Feature-to-plan mapping | planId, featureId, limit |
Subscription | Active workspace subscriptions | workspaceId, planId, status, currentPeriodEnd |
Transaction | Payment transactions | workspaceId, amount, provider, status |
PaymentMethod | Stored payment methods | workspaceId, provider, last4 |
Social Accounts -- 1 Model
| Model | Purpose | Key Fields |
|---|---|---|
SocialAccount | Connected social platform accounts | workspaceId, platform, accessToken (encrypted), refreshToken (encrypted) |
Content & Publishing -- 5 Models
| Model | Purpose | Key Fields |
|---|---|---|
Post | Social media posts | workspaceId, caption, status, scheduledAt |
MediaFile | Uploaded media files | workspaceId, url, type, size |
PostMedia | Post-to-media join table | postId, mediaFileId, order |
PostPublication | Per-platform publication record | postId, platform, platformPostId, status |
PostMetric | Per-publication engagement metrics | publicationId, likes, comments, shares, reach, date |
Analytics & Predictions -- 10 Models
| Model | Purpose | Key Fields |
|---|---|---|
ABTest | A/B test definitions | workspaceId, name, status, startDate, endDate |
Schedule | Scheduled post triggers | postId, scheduledAt, status |
PostClassification | AI-classified post content type | postId, category, tags, confidence |
PostEmbedding | Vector embeddings for similarity | postId, embedding (vector), model |
IndustryBenchmark | Industry performance benchmarks | industry, metric, value, percentile |
HashtagScore | Hashtag performance scoring | hashtag, workspaceId, score, uses |
NicheBenchmark | Niche-specific benchmarks | niche, metric, value |
AlgorithmChangeDetection | Detected platform algorithm changes | platform, detectedAt, impact |
EngagementPrediction | AI engagement forecasts | postId, predictedLikes, confidence |
RevenuePrediction | AI revenue forecasts | workspaceId, predictedRevenue, period |
PredictionAccuracy | Prediction accuracy tracking | predictionId, actualValue, accuracy |
AI -- 5 Models
| Model | Purpose | Key Fields |
|---|---|---|
AiPromptLog | AI API call logging | workspaceId, promptType, tokens, model |
AiChatMessage | AI chat conversation history | workspaceId, role, content, threadId |
AiSuggestion | AI-generated content suggestions | workspaceId, type, content, status |
BrandVoice | Brand voice configuration | workspaceId, name, description, samples |
ContentCalendar | AI-generated content calendars | workspaceId, name, startDate, endDate |
CalendarEntry | Individual calendar post entries | calendarId, date, content, platform |
Workflows -- 3 Models
| Model | Purpose | Key Fields |
|---|---|---|
Workflow | Workflow definitions | workspaceId, name, nodes (JSON), edges (JSON), status |
WorkflowRun | Workflow execution records | workflowId, status, startedAt, completedAt, logs |
WorkflowOptimizationLog | Workflow performance optimization | workflowId, suggestion, applied |
Competitors -- 3 Models
| Model | Purpose | Key Fields |
|---|---|---|
Competitor | Tracked competitor profiles | workspaceId, name, platforms, socialUrls |
CompetitorSnapshot | Point-in-time competitor metrics | competitorId, followers, engagement, date |
CompetitorReport | Generated competitor comparison reports | workspaceId, competitorIds, analysis |
Ads -- 2 Models
| Model | Purpose | Key Fields |
|---|---|---|
AdCampaign | Ad campaign records | workspaceId, platform, budget, status, metrics |
AutoBoostRule | Automatic post boosting rules | workspaceId, conditions, budget, enabled |
E-Commerce -- 4 Models
| Model | Purpose | Key Fields |
|---|---|---|
EcommerceStore | Connected store (Shopify/WooCommerce/EasyOrders) | workspaceId, provider, storeUrl, credentials |
EcommerceProduct | Synced product catalog | storeId, name, price, externalId |
ProductPostTemplate | Templates linking products to posts | productId, templateContent |
Commerce & Revenue -- 5 Models
| Model | Purpose | Key Fields |
|---|---|---|
CommerceCustomer | Customer records from stores | workspaceId, email, name, totalOrders |
CommerceOrder | Order records | workspaceId, storeId, total, status |
OrderLineItem | Individual order line items | orderId, productId, quantity, price |
RevenueAttribution | Post-to-order revenue attribution | postId, orderId, amount, utmParams |
PostRevenue | Aggregated revenue per post | postId, totalRevenue, orderCount |
CouponGenerated | AI-generated discount coupons | workspaceId, code, discount, usageCount |
Agency & Collaboration -- 3 Models
| Model | Purpose | Key Fields |
|---|---|---|
ApprovalRequest | Content approval workflows | postId, requesterId, status, reviewerId |
ClientReport | Generated client reports | workspaceId, dateRange, data, generatedAt |
WorkspaceProfile | Public workspace profile/branding | workspaceId, logo, colors, description |
Audience & CRM -- 5 Models
| Model | Purpose | Key Fields |
|---|---|---|
AudienceNode | Audience member profiles in the graph | workspaceId, name, platform, engagementScore |
AudienceInteraction | Individual interactions with audience | nodeId, type, content, date |
AudienceSegment | Audience segments | workspaceId, name, rules, memberCount |
AudienceTag | Tags applied to audience members | nodeId, tag |
Conversations (ICE) -- 7 Models
| Model | Purpose | Key Fields |
|---|---|---|
ConversationThread | Conversation threads from social platforms | workspaceId, platform, status, assignedTo |
ConversationMessage | Individual messages in threads | threadId, direction, content, sentAt |
BotConfiguration | AI bot settings per workspace | workspaceId, enabled, autoReplyIntents, confidence |
EscalationRule | Rules for escalating to humans | workspaceId, conditions, assignTo, priority |
Escalation | Escalation records | threadId, ruleId, status, assignedTo |
ConversationMemory | 3-tier memory for conversation context | audienceNodeId, tier, data, expiresAt |
ReplyExperiment | A/B experiments on reply strategies | workspaceId, variants, metrics, status |
System -- 4 Models
| Model | Purpose | Key Fields |
|---|---|---|
WebhookConversation | Incoming webhook log | source, payload, processedAt |
Notification | User notifications | userId, type, title, read |
WorkspaceUsage | Monthly usage tracking per workspace | workspaceId, period, aiGenerations, postsPublished |
AuditLog | Audit trail for all actions | workspaceId, userId, action, resource, details |
Model Count Summary
| Domain | Model Count |
|---|---|
| Core (Users & Workspaces) | 5 |
| Billing & Plans | 6 |
| Social Accounts | 1 |
| Content & Publishing | 5 |
| Analytics & Predictions | 11 |
| AI | 6 |
| Workflows | 3 |
| Competitors | 3 |
| Ads | 2 |
| E-Commerce | 4 |
| Commerce & Revenue | 6 |
| Agency & Collaboration | 3 |
| Audience & CRM | 4 |
| Conversations (ICE) | 7 |
| System | 4 |
| Total | 69+ |
Cross-Reference
- ER Diagram -- visual entity relationship diagram
- Key Relations -- important relationships and indexes
- Migrations -- how to modify the schema
- Shared Types -- TypeScript types derived from these models