Entity Relations — PostgreSQL

Visual reference for all PostgreSQL tables and their relationships.

Full schema details (column definitions, indexes, queries): postgres.md

Core Hierarchy

The primary data hierarchy flows from a tenant down through strategy, goals, deliverables, activities, and execution records.

Rendering diagram…

Tenants & Users

Rendering diagram…

Strategy → Goals → Deliverables

Rendering diagram…

Activities & Execution

Rendering diagram…

Agent Configuration

Rendering diagram…

Channels & Content

Rendering diagram…

Paid Advertising

Rendering diagram…

CRM — Leads & Backlinks

Rendering diagram…

Billing

Rendering diagram…

Full Relationship Summary

Table	Key Foreign Keys
`users`	`tenant_id → tenants`
`contracts`	`tenant_id → tenants`, `accepted_by → users`
`onboarding_runs`	`tenant_id → tenants`, `reviewed_by → users`
`strategies`	`tenant_id → tenants`, `approved_by → users`
`goals`	`tenant_id → tenants`, `strategy_id → strategies`
`deliverables`	`tenant_id → tenants`, `goal_id → goals`, `strategy_id → strategies`
`deliverable_periods`	`tenant_id → tenants`, `deliverable_id → deliverables`
`activities`	`tenant_id → tenants`, `deliverable_period_id → deliverable_periods`, `goal_id → goals`, `parent_activity_id → activities`, `assignee_user_id → users`
`activity_runs`	`tenant_id → tenants`, `activity_id → activities`, `session_id → sessions`
`sessions`	`tenant_id → tenants`, `goal_id → goals`
`llm_calls`	`tenant_id → tenants`, `activity_run_id → activity_runs`
`tool_calls`	`tenant_id → tenants`, `activity_run_id → activity_runs`
`agent_configs`	`tenant_id → tenants`
`activity_templates`	`tenant_id → tenants`, `source_template_id → activity_templates` (self-ref)
`recurring_task_templates`	`tenant_id → tenants`, `source_template_id → recurring_task_templates` (self-ref)
`channels`	`tenant_id → tenants`
`channel_scores`	`tenant_id → tenants`, `channel_id → channels`
`blog_requests`	`tenant_id → tenants`, `deliverable_id → deliverables`, `activity_id → activities`, `target_channel_id → channels`
`blog_posts`	`tenant_id → tenants`, `blog_request_id → blog_requests`, `channel_id → channels`
`social_media_requests`	`tenant_id → tenants`, `deliverable_id → deliverables`, `activity_id → activities`
`social_posts`	`tenant_id → tenants`, `social_media_request_id → social_media_requests`, `channel_id → channels`
`backlinks`	`tenant_id → tenants`, `activity_id → activities`, `deliverable_id → deliverables`
`leads`	`tenant_id → tenants`, `source_channel_id → channels`, `source_campaign_id → ad_campaigns`, `assigned_to → users`
`lead_activities`	`tenant_id → tenants`, `lead_id → leads`
`ad_campaigns`	`tenant_id → tenants`, `channel_id → channels`
`ad_keywords`	`tenant_id → tenants`, `channel_id → channels`, `ad_campaign_id → ad_campaigns`
`search_term_reports`	`tenant_id → tenants`, `channel_id → channels`, `ad_campaign_id → ad_campaigns`
`search_term_classifications`	`tenant_id → tenants`, `search_term_report_id → search_term_reports`, `activity_id → activities`, `reviewed_by → users`
`ad_optimization_recommendations`	`tenant_id → tenants`, `channel_id → channels`, `ad_campaign_id → ad_campaigns`, `activity_id → activities`, `reviewed_by → users`
`billing_events`	`tenant_id → tenants`

Cross-Database References

PostgreSQL columns reference MongoDB or S3 via stored identifiers. These are application-level references — not DB-enforced constraints.

PostgreSQL → MongoDB

PostgreSQL column	References MongoDB	Collection
`activities.output_ref`	`ObjectId`	`activity_outputs` (metadata; content is in S3 via `activity_outputs.s3Key`)
`blog_posts.content_ref`	`ObjectId`	`activity_outputs`
`social_posts.content_ref`	`ObjectId`	`activity_outputs`
`onboarding_runs.context_file_id`	`ObjectId`	`skills` (and `onboarding_research`)
`tool_calls` (summary)	`ObjectId`	`tool_call_logs` (full payloads)

PostgreSQL → S3

PostgreSQL column	S3 key pattern
`contracts.pdf_s3_key`	`{tenantId}/contracts/{contractRefId}/v{templateVersion}.pdf`

PostgreSQL → MongoDB → S3 (two-hop)

For deliverable content, the path is: PostgreSQL holds a MongoDB ObjectId; the MongoDB document holds the S3 key.

PostgreSQL column	MongoDB collection	S3 content
`activities.output_ref`	`activity_outputs.s3Key`	Full Markdown or JSON output
`blog_posts.content_ref`	`activity_outputs.s3Key`	Full article Markdown
`social_posts.content_ref`	`activity_outputs.s3Key`	Post copy + hashtags