Status: 📋 Reference Document Last Updated: November 28, 2025

Overview

This document tracks the evolution of TendSocial's database schema as new features are implemented. It serves as a reference for understanding how different features integrate at the database level.

Schema Evolution Strategy

TendSocial uses a unified schema approach where new features extend existing models rather than creating parallel structures. This prevents data fragmentation and enables cross-feature queries (e.g., analytics across all content types).

Core Principles

1. Polymorphic References - Content-agnostic models use contentType + contentId instead of separate tables per type
2. Unified Metrics - All analytics use CompanyContentMetrics model regardless of source (social, blog, video)
3. Asset Centralization - All media (images, videos, docs) stored in CompanyAsset model
4. Multi-Tenancy - All tables scoped by companyId with proper indexes

Major Schema Additions

November 2025 - Social Media Integration & Analytics Foundation

New Models:

• UserSocialAccount - OAuth credentials for social platforms
• CompanyContentMetrics - Unified analytics for all content types
• CompanyMetricsSnapshot - Time-series metrics for trend charts
• CompanyAnalyticsConnection - GA4 and third-party analytics connections
• CompanyAnalyticsSyncJob - Background sync job tracking

Enhanced Models:

• CompanyPost - Added remoteId, remoteUrl, socialAccountId, publishedAt
• Company - Added relations to new models
• User - Added social post creation tracking

Migration: 20251128045023_schema_enhancements_and_super_admin

Key Features Enabled:

• Multi-platform social publishing
• Automatic token refresh
• Unified analytics dashboard
• Cross-platform performance comparison

Planned - Asset Library (Q3 2025)

New Models:

• CompanyAsset - Centralized media storage
• CompanyAssetFolder - Hierarchical organization
• CompanyAssetTag - Categorization system
• CompanyAssetUsage - Track where assets are used
• PlatformAsset - Track uploads to destination platforms

Enhanced Models:

• CompanyBlogPost - Link to cover image asset
• CompanyVideoScript - Link to thumbnail asset
• CompanyPost - Reference media assets

Features Enabled:

• Duplicate detection (content hashing)
• AI Vision auto-tagging
• Asset lineage tracking (regenerated images)
• Storage quota management
• Multi-platform asset tracking

Planned - Blog Publishing (Q2 2025)

New Models:

• CompanyBlogDestination - WordPress, Ghost, 11ty connections
• CompanyBlogPublishLog - Audit trail per destination
• RemotePostRef - Track remote post IDs and URLs

Enhanced Models:

• CompanyBlogPost - Add destination configuration
• CompanyAsset - Platform-specific upload tracking

Features Enabled:

• Multi-destination publishing
• Git-based publishing (11ty, Astro)
• Image upload to platform asset stores
• Canonical URL management

Model Relationships

Content → Metrics Flow

CompanyBlogPost ──┐
           ├──► CompanyContentMetrics ──► CompanyMetricsSnapshot
CompanyPost ──────┤     (polymorphic)       (time-series)
           │
CompanyVideoScript┘

    ▲
    │
    └─── CompanyAnalyticsSyncJob (updates metrics)

Asset → Content Flow

CompanyAsset ──┐
        ├──► CompanyAssetUsage ──► CompanyBlogPost
        │                   CompanyPost
        │                   CompanyVideoScript
        │
        └──► PlatformAsset (WordPress, LinkedIn, etc.)
             (tracks where uploaded)

Social Publishing Flow

User ──► UserSocialAccount ──► CompanyPost ──► CompanyContentMetrics
         (OAuth tokens)   (published)  (analytics)

Schema Design Patterns

1. Polymorphic References

Instead of:

model BlogMetrics { blogPostId String }
model SocialMetrics { postId String }

We use:

model CompanyContentMetrics {
  contentType ContentType  // BLOG_POST, SOCIAL_POST, VIDEO_SCRIPT
  contentId   String       // ID of any content type
}

Benefits:

• Single query for all content analytics
• Unified dashboard code
• Easier to add new content types

2. Multi-Destination Tracking

Content published to multiple platforms uses destinationKey to differentiate:

model CompanyContentMetrics {
  contentType    ContentType
  contentId      String
  platform       String   // "wordpress", "linkedin"
  destinationKey String?  // "vulpine-wp", "personal-linkedin"

  @@unique([contentType, contentId, platform, destinationKey])
}

Example: A blog post published to WordPress and Ghost = 2 CompanyContentMetrics records.

3. Encrypted Sensitive Data

OAuth tokens and API keys encrypted at rest:

model UserSocialAccount {
  accessToken  String @db.Text  // Encrypted before storage
  refreshToken String? @db.Text // Encrypted before storage
}

Encryption handled in application layer (AES-256).

4. Cascading Deletes

Proper cleanup on deletion:

model CompanyAsset {
  companyId String
  company   Company @relation(fields: [companyId], references: [id], onDelete: Cascade)
}

When a company is deleted, all assets, posts, metrics are automatically deleted.

Index Strategy

Indexes optimized for common query patterns:

Query Pattern	Index
List company's assets	@@index([companyId])
Get post metrics	@@index([contentType, contentId])
Find stale metrics	@@index([lastSyncedAt])
Filter by platform	@@index([platform])
Social account lookup	@@unique([userId, platform, profileType])

Migration Best Practices

Adding New Fields

1. Make new fields nullable initially
2. Backfill data if needed
3. Make required after data populated

Adding New Tables

1. Create table with indexes
2. Set up foreign keys with proper onDelete behavior
3. Update seed data
4. Generate new Prisma client

Deprecating Models

1. Create migration path (e.g., SocialAnalytics → CompanyContentMetrics)
2. Run data migration script
3. Verify data integrity
4. Drop old table in separate migration

Data Retention

Model	Retention Policy
CompanyPost, CompanyBlogPost, CompanyVideoScript	Indefinite (user content)
CompanyContentMetrics	Current state always kept
CompanyMetricsSnapshot	18 months minimum
CompanyAsset	Until deleted by user or archived
CompanyAnalyticsSyncJob	30 days (rolling cleanup)
UserSocialAccount	Until user disconnects

Future Considerations

Potential Additions

1. Content Versioning - Track revisions of blog posts and scripts
2. A/B Testing - Store test variants and results
3. Team Collaboration - Comments, approvals, assignments
4. Workflow States - Draft → Review → Approved → Published
5. Content Templates - Reusable post/blog templates

Scaling Considerations

1. Partitioning MetricsSnapshot - By date range for faster queries
2. Archiving old AnalyticsSyncJob records - Move to cold storage after 30 days
3. Caching frequently accessed data - Popular posts, account settings
4. Read replicas - For analytics dashboards

Related Documentation

• Database Schema Reference
• Analytics Foundation
• Social Media Publishing

Source Documents

This document synthesizes information from:

• docs/planning/Schema_Evolution_Plan.md
• docs/planning/schema-enhancements.md
• docs/planning/social-media-integration-plan.md
• docs/planning/Analytics_Foundation.md