Overview

The TendSocial database is designed with multi-tenancy at its core. All data is scoped to a Company (tenant), and strict isolation is enforced at the application level using Prisma Client Extensions.

Core Principles

1. Multi-Tenancy: Every major entity belongs to a Company.
2. Soft Deletion: Critical data supports soft deletion or archiving.
3. Audit Trails: Publishing and sync actions are logged.
4. Polymorphism: Usage and metrics link to multiple content types (Posts, Blogs, Scripts).
5. Single Source of Truth: Major entities (BrandProfile, AppSettings, Campaign Context) use a single JSON structure shared across Frontend, Backend, and Database via @tendsocial/shared-types.
6. Zero Mapping: Application code avoids manual mapping between DB fields and API responses by using validated JSON blobs.

Entity Relationship Diagram (ERD)

erDiagram
    Company ||--o{ User : "has members"
    Company ||--o{ CompanyApiKey : "owns"
    Company ||--o{ CompanyUsage : "tracks usage"
    Company ||--o{ CompanyPost : "owns content"
    Company ||--o{ CompanyBrandProfile : "has profile"
    Company ||--o{ CompanyVideoScript : "owns scripts"
    Company ||--o{ CompanyBlogPost : "owns blogs"
    Company ||--o{ CompanyAsset : "owns assets"
    Company ||--o{ CompanyAnalyticsConnection : "has connections"
    Company ||--o{ CompanySettings : "has settings"

    User ||--o{ UserSocialAccount : "connects"
    User ||--o{ CompanyPost : "authors"
    User ||--o{ CompanyVideoScript : "authors"
    User ||--o{ CompanyBlogPost : "authors"
    User ||--o{ CompanyAsset : "uploads"

    UserSocialAccount ||--o{ UserTokenRefreshJob : "refreshes"
    UserSocialAccount ||--o{ CompanyPost : "publishes to"
    UserSocialAccount ||--o{ CompanyAccountAnalytics : "tracks metrics"

    CompanyPost ||--o{ CompanyPostMedia : "contains"
    CompanyPost ||--o{ CompanyPublishLog : "logs"

    CompanyAsset ||--o{ CompanyAssetUsage : "used in"
    CompanyAsset ||--o{ CompanyAssetTag : "tagged with"
    CompanyAsset ||--o{ CompanyAssetFolder : "organized in"

    CompanyScheduledPost ||--o{ CompanyPost : "generates"
    CompanyScheduledPost ||--o{ CompanyPublishLog : "logs"

    CompanyBlogPost ||--o{ CompanyBlogPublishLog : "logs"
    CompanyBlogDestination ||--o{ CompanyBlogPublishLog : "logs"

    CompanyContentMetrics ||--o{ CompanyMetricsSnapshot : "has history"

NOTE

Model Naming Convention: Models use tier prefixes for clarity:

• Company* - Company-scoped resources (multi-tenant data)
• User* - User-scoped resources (personal connections, tokens)
• No prefix - Platform-level resources (SystemSettings, PackageConfig)

Migration: 20251210010413_rename_models_to_tier_prefixes

Models

Core Multi-Tenant Models

Company

The root tenant entity.

• slug: Unique URL-friendly identifier.
• subscriptionTier: Controls feature access.
• billingStatus: Manages access based on payment.

CompanySettings

Global application settings for the company.

• data: Typed JSON containing AppSettings (schedules, cadences, AI preferences).

User

A member of a company.

• role: Tenant-level role (owner, admin, member).
• isSuperAdmin: Platform-level admin flag.
• supabaseId: Link to external auth provider.

Content Models

CompanyPost

A social media post owned by a company.

• platform: Target platform (LinkedIn, Twitter, etc.).
• content: The post text.
• mediaAssetIds: References to CompanyAsset library.
• scheduledPostId: Link to scheduler if applicable.

CompanyBlogPost

A canonical blog article.

• markdownBody: Source content.
• htmlBody: Pre-rendered content for preview/sync.
• frontmatter: Metadata (SEO, tags).
• status: Draft, Scheduled, Published.

CompanyVideoScript

A script for video content.

• scriptType: Long-form (YouTube) or Short-form (TikTok/Reels).
• longFormScript / shortFormScript: JSON structures for script sections.

Asset Library

CompanyAsset

A file (image, video, doc) owned by a company.

• s3Key: Path in object storage.
• s3KeyOptimized: Path to WebP version.
• contentHash: SHA-256 for deduplication.
• usageCount: Tracks active references to prevent deletion.

Analytics

ContentMetrics

Unified metrics for any content type.

• contentType: Polymorphic link (BLOG_POST, SOCIAL_POST).
• platform: Where the metrics are from.
• impressions, reach, engagementRate: Standardized metrics.

CompanyAccountAnalytics

Daily snapshots of account-level metrics.

• socialAccountId: Link to connected social account (UserSocialAccount).
• date: Metric snapshot date.
• followers, followerChange: Growth tracking.
• engagements, engagementRate: Account-level engagement.
• platformMetrics: Platform-specific JSON (e.g., YouTube watch time).

CompanyBestTimeToPost

Calculated optimal posting times per platform.

• companyId, platform: Scoped to company and platform.
• dayOfWeek, hour: Time slot identifier.
• score, avgEngagement: Engagement score for this slot.
• postCount: Sample size used for calculation.

CompanyAnalyticsConnection

Configuration for external analytics providers (GA4, Plausible).

Reporting

CompanyReport

Saved report configurations for scheduled or on-demand generation.

• companyId: Tenant ownership.
• name: User-defined report name.
• reportType: Type of report (performance, growth, engagement).
• dateRange: JSON configuration for time period.
• platforms: Array of platforms to include.
• socialAccountIds: Specific accounts to include (optional).
• metrics: Array of metrics to include.
• schedule: JSON configuration for automatic generation (frequency, recipients).
• lastGeneratedAt: Timestamp of last generation.
• lastReportUrl: S3/R2 URL of last generated report.

ReportExport

Individual export instances/history.

• reportId: Link to parent Report configuration (optional, for ad-hoc exports).
• format: Output format (PDF, CSV, XLSX).
• status: Generation status (pending, processing, completed, failed).
• s3Key: Path in object storage.
• expiresAt: Signed URL expiration (optional).
• generatedBy: User who triggered generation.

System & Infrastructure

SystemSettings

Singleton for global configuration (e.g., default trial length).

CompanyApiKey

Company-level API keys for external access.

UserTokenRefreshJob

Background job tracking for OAuth token rotation.