Database Overview

The system uses three storage layers. Each was chosen for a specific access pattern. This document explains the decision framework. For full schemas, see the dedicated docs.

Which Database for What

Rule of thumb:

• Relational data with counts, joins, or billing → PostgreSQL
• Variable-structure metadata, logs, append-only operational data → MongoDB
• Large binary files or large text blobs (PDFs, full article content, scraped text) → S3

Split Pattern: PostgreSQL + MongoDB + S3

Large content is split across all three stores. PostgreSQL holds structured metadata; MongoDB holds variable-structure metadata and operational logs; S3 holds the actual content bytes.

Access pattern for large content:

1. Fetch MongoDB or PostgreSQL metadata record (fast, indexed)
2. Extract the s3Key
3. Call s3.getSignedUrl(s3Key, { expiresIn: 900 }) for a 15-minute pre-signed URL
4. Return URL to client; client fetches content directly from S3

Key Relationships (PostgreSQL)

tenants
  └── strategies (one active at a time)
        └── goals (multiple per strategy)
              └── deliverables (multiple per goal)
                    └── deliverable_periods (one per month)
                          └── activities (pipeline steps)
                                └── activity_runs (execution attempts)
                                      └── llm_calls (cost audit)
                                      └── tool_calls (integration audit)
                                └── [output_ref → MongoDB activity_outputs]

tenants
  └── channels (OAuth-connected platforms)
        └── blog_requests → blog_posts (CMS content)
        └── social_media_requests → social_posts (social content)
        └── channel_scores (monthly health scores)
        └── ad_campaigns → ad_keywords
                        → search_term_reports → search_term_classifications
                        → ad_optimization_recommendations

tenants
  └── leads → lead_activities
  └── backlinks
  └── agent_configs
  └── sessions
  └── recurring_task_templates (copied from global templates)
  └── activity_templates (copied from global templates)

Base Record Fields

All records — in both PostgreSQL and MongoDB — follow a standard set of base fields. See PostgreSQL Schema → Base Record Fields for the Prisma schema composition pattern.

Naming conventions (PostgreSQL):

• All timestamp columns use _on suffix: created_on, updated_on, deleted_on
• FK columns for user references use _by suffix: created_by, reviewed_by
• External IDs use external_ prefix: external_campaign_id, external_keyword_id
• ref_id is the ULID, used in APIs/URLs. id (UUID) is used only for internal FK joins.

Engines Summary