Moved: This document is now at docs/database/postgres.md. The content below is kept for backwards compatibility with existing links.

PostgreSQL Schema

Engine: PostgreSQL 16 ORM: Prisma ORM (type-safe schema with migrations and auto-generated client) Package: packages/db — schema, migrations, and the shared db singleton all live here

Which data goes here vs MongoDB? See Database Overview for the decision guide.

Base Record Fields

Every table inherits a standard set of fields via Prisma schema composition.

Field Definitions

ref_id (ULID): Exposed in APIs, URLs, and webhooks. Sortable by creation time without a secondary sort column. The internal id (UUID) is used only for PostgreSQL foreign keys.

Prisma Schema

model BaseRecord {
  id        String   @id @default(cuid())
  refId     String   @unique
  tenantId  String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?
}

Which Records Get Which Fields

Tenants & Users

tenants

users

contracts

One contract record per tenant. Tracks the generated PDF and the tenant’s acceptance.

Flow:

1. On tenant creation, the master contract template (contracts/master-contract.md) is read, placeholders replaced with tenant details, rendered to PDF via Puppeteer
2. PDF binary is stored in MongoDB contract_pdfs collection; pdf_ref points to it
3. On first login, the Dashboard shows a full-screen contract acceptance modal (link to PDF + “I agree” checkbox)
4. On acceptance: accepted_on, accepted_by, accepted_ip are set; tenants.contract_accepted_on is updated for fast auth checks

Acceptance gate: The auth middleware checks tenants.contract_accepted_on IS NOT NULL. If NULL, all Dashboard routes redirect to the contract acceptance screen. Only the contract acceptance endpoint is accessible until accepted.

Tenants table additions (to support fast auth-level gate):

Contract supersession: If the master contract template is updated (new version), existing accepted contracts remain valid. Tenants are not asked to re-accept unless the platform decides to force re-acceptance (managed manually by super admin in Manage app).

Onboarding & Strategy

onboarding_runs

Tracks the Onboarding Agent’s research run for a tenant.

strategies

The marketing strategy generated from the context file.

goals

High-level outcomes the strategy aims to achieve.

deliverables

Fixed recurring commitments per goal. Defines what is produced each month.

deliverable_periods

Monthly (or quarterly) tracking record for each deliverable. One row per deliverable per period.

Activities

activities

The primary work unit. Every piece of work — agent task or human review — is an activity.

CREATE INDEX ON activities(tenant_id, status);
CREATE INDEX ON activities(deliverable_period_id, status);
CREATE INDEX ON activities(assignee_user_id, status) WHERE assignee_type = 'human';
CREATE INDEX ON activities(assignee_agent_role, status) WHERE assignee_type = 'agent';
CREATE INDEX ON activities(parent_activity_id);
CREATE INDEX ON activities(goal_id, status);

activity_runs

Each execution attempt of an agent activity (retries create new runs).

activity_templates

Pipeline definitions per deliverable type. Used by the Activity Planner to spawn activity pipelines at the start of each deliverable period.

recurring_task_templates

Predefined standalone tasks that recur on a schedule, not tied to a deliverable pipeline. Global templates are copied to each tenant on creation.

Key behaviour:

• On tenant creation: all global templates (tenant_id IS NULL) are copied with tenant_id set and source_template_id pointing to the original
• After copy, tenant admin fills in required variable_values (prompted during onboarding or setup checklist)
• Activities are auto-created based on trigger + periodicity
• Changes to a global template do NOT propagate to existing tenant copies (tenants own their copy)

Agents & Sessions

agent_configs

Tenant-specific agent configuration.

sessions

LLM session continuity records.

Billing & Costs

llm_calls

Full audit log of every LLM request.

-- Partition by month for scale
CREATE TABLE llm_calls (...)
PARTITION BY RANGE (created_on);

tool_calls

Every integration API call made by an agent.

billing_events

Payment and subscription lifecycle events from Razorpay.

Channels & Content

channels

OAuth-connected external platforms per tenant. Stores encrypted credentials used by agents to publish content.

Token security: Access/refresh tokens encrypted AES-256-GCM. Key managed via Doppler. Decrypted in memory only for API calls.

Token refresh cron: Runs hourly. Refreshes tokens expiring within 1 hour. On failure: status = 'expired', alert activity created.

CREATE UNIQUE INDEX idx_channels_tenant_platform
  ON channels(tenant_id, platform, external_id);
 
CREATE INDEX idx_channels_expiry
  ON channels(token_expires_at)
  WHERE status = 'connected';

channel_scores

Historical score records per channel per scoring period.

blog_requests

A request to create a blog post. Content is not created until the request is approved.

On approval: A BlogPost record is auto-created with status = 'draft' and linked to this request.

blog_posts

The actual blog post content entity, created when a BlogRequest is approved.

social_media_requests

A request to create social post(s). One request can target multiple channels.

On approval: One SocialPost record created per channel in target_channel_ids.

social_posts

One record per platform per approved social media request.

SEO & Backlinks

backlinks

Tracks all backlinks built for a tenant. Periodically checked for live/indexed status via DataForSEO.

Periodic status checks: Cron runs daily per tenant. On link going down: creates alert activity for DM reviewer.

CREATE INDEX idx_backlinks_tenant_status
  ON backlinks(tenant_id, outreach_status, is_live);
 
CREATE INDEX idx_backlinks_check_schedule
  ON backlinks(last_checked_on, is_live)
  WHERE outreach_status = 'published';

CRM

leads

Contacts captured from any source.

CREATE INDEX idx_leads_tenant_status
  ON leads(tenant_id, status, created_on);
 
CREATE INDEX idx_leads_source
  ON leads(tenant_id, source, source_campaign_id);

lead_activities

Timeline of touchpoints and actions on a lead.

Paid Advertising

ad_campaigns

Synced from Google Ads and Meta Ads. Upserted on each sync run.

CREATE UNIQUE INDEX idx_ad_campaigns_external
  ON ad_campaigns(tenant_id, platform, external_campaign_id);

ad_keywords

Keywords in Google Ads ad groups. Synced from Google Ads API.

search_term_reports

Raw search terms from Google Ads search term reports.

search_term_classifications

Agent-classified search terms, pending human review before being pushed to Google Ads.

ad_optimization_recommendations

Agent-generated optimization recommendations. Pending human review before being applied.

Critical Indexes

-- Activity queue for agents
CREATE INDEX idx_activities_agent_queue
  ON activities(tenant_id, assignee_agent_role, status)
  WHERE assignee_type = 'agent' AND status IN ('created', 'assigned');
 
-- DM Portal human queue
CREATE INDEX idx_activities_human_queue
  ON activities(tenant_id, assignee_user_id, status)
  WHERE assignee_type = 'human' AND status IN ('created', 'awaiting_approval');
 
-- Deliverable period progress
CREATE INDEX idx_deliverable_periods_active
  ON deliverable_periods(tenant_id, status)
  WHERE status IN ('not_started', 'in_progress', 'at_risk');
 
-- Goal progress tracking
CREATE INDEX idx_goals_active
  ON goals(tenant_id, strategy_id, status);
 
-- Cost aggregation
CREATE INDEX idx_llm_calls_cost
  ON llm_calls(tenant_id, created_on);

Useful Queries

Monthly deliverable progress for a tenant

SELECT
  d.name,
  d.type,
  dp.target_count,
  dp.completed_count,
  dp.status,
  ROUND((dp.completed_count::numeric / dp.target_count) * 100, 1) AS pct_complete
FROM deliverable_periods dp
JOIN deliverables d ON d.id = dp.deliverable_id
WHERE dp.tenant_id = :tenantId
  AND dp.period_start = DATE_TRUNC('month', CURRENT_DATE)
ORDER BY dp.status, d.name;

Human’s pending activity queue

SELECT
  a.id, a.title, a.type, a.priority,
  a.is_approval_gate,
  d.name AS deliverable_name,
  g.name AS goal_name,
  a.due_date
FROM activities a
JOIN deliverable_periods dp ON dp.id = a.deliverable_period_id
JOIN deliverables d         ON d.id  = dp.deliverable_id
JOIN goals g                ON g.id  = a.goal_id
WHERE a.tenant_id     = :tenantId
  AND a.assignee_user_id = :userId
  AND a.status IN ('created', 'awaiting_approval')
ORDER BY a.is_approval_gate DESC, a.priority, a.due_date;

Goal progress across all goals for a strategy

SELECT
  g.name,
  g.metric,
  g.baseline_value,
  g.current_value,
  g.target_value,
  ROUND(((g.current_value - g.baseline_value) / NULLIF(g.target_value - g.baseline_value, 0)) * 100, 1) AS progress_pct,
  g.status
FROM goals g
WHERE g.strategy_id = :strategyId
ORDER BY g.priority;

Performance

Required Indexes

Every tenant-scoped table must have tenant_id as the leading column in its primary access indexes. The following indexes are mandatory — Prisma migrations fail CI if they are absent.

Composite indexes (high-traffic query paths):

-- Activity list: tenant + status filter (approval queue, activity list)
CREATE INDEX idx_activities_tenant_status
  ON activities(tenant_id, status);
 
-- Activity list: tenant + deliverable (deliverable detail panel)
CREATE INDEX idx_activities_tenant_deliverable
  ON activities(tenant_id, deliverable_id);
 
-- Activity list: tenant + assignee (human task inbox)
CREATE INDEX idx_activities_tenant_assignee
  ON activities(tenant_id, assignee_user_id)
  WHERE assignee_user_id IS NOT NULL;
 
-- LLM cost rollup: tenant + date (cost dashboard aggregation)
CREATE INDEX idx_llm_calls_tenant_created
  ON llm_calls(tenant_id, created_on DESC);
 
-- Campaign list: tenant + status (campaign overview)
CREATE INDEX idx_campaigns_tenant_status
  ON campaigns(tenant_id, status);
 
-- Goal progress: tenant + strategy (strategy detail)
CREATE INDEX idx_goals_tenant_strategy
  ON goals(tenant_id, strategy_id);

Partial indexes (hot sub-sets only):

-- Pending approvals only (resolved/expired approvals are cold)
CREATE INDEX idx_approvals_pending
  ON approvals(tenant_id, expires_at)
  WHERE status = 'pending';
 
-- Active + running agents only (terminated agents never queried by workers)
CREATE INDEX idx_agent_configs_active
  ON agent_configs(tenant_id, role)
  WHERE status NOT IN ('terminated', 'paused');
 
-- Undeleted activities (soft-deleted records excluded from all worker queries)
CREATE INDEX idx_activities_active
  ON activities(tenant_id, status, created_on DESC)
  WHERE deleted_on IS NULL;

Connection Pooling (PgBouncer)

All application services connect to PostgreSQL via PgBouncer in transaction mode, not directly:

apps (Fastify, Next.js)
    │ POSTGRES_URL=postgresql://.../leadmetrics (points to PgBouncer)
    ▼
PgBouncer :5433   ← pool size: 25 per app instance, transaction mode
    │
    ▼
PostgreSQL :5432  ← max_connections: 100

PgBouncer is added to Docker Compose as a separate service in production/staging. In local development, apps connect directly to PostgreSQL (acceptable because only one developer connects at a time).

Pool size guidance: pool_size = (num_app_instances × 10). A 3-instance API deployment uses pool_size 30.

Cursor Pagination (No OFFSET)

All list endpoints use cursor-based pagination. OFFSET is banned — it causes full table scans when offset is large. Cursors are ULID-based (already sorted by creation time):

-- ✅ Correct: cursor pagination
SELECT * FROM activities
WHERE tenant_id = :tenantId
  AND deleted_on IS NULL
  AND ref_id > :cursor          -- ULID lexicographic order = creation order
ORDER BY ref_id ASC
LIMIT :limit;
 
-- ❌ Wrong: OFFSET pagination
SELECT * FROM activities
WHERE tenant_id = :tenantId
OFFSET 200 LIMIT 25;            -- full scan to skip 200 rows

Query Constraints

These rules are enforced by ESLint and enforced in code review:

Slow Query Monitoring

pg_stat_statements is enabled in all environments:

-- Top 10 slowest queries (run weekly in production)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Queries exceeding 100 ms mean execution time trigger a Grafana alert. The alert includes the query text and an EXPLAIN ANALYZE plan for diagnosis.

Security

Encrypted Columns

Sensitive values are encrypted at the application layer with AES-256-GCM before storage. The encryption key is injected via Doppler (ENCRYPTION_KEY) and never stored in the database:

Columns containing encrypted values are typed TEXT in PostgreSQL (not a custom type). The application decrypts on read; the DB user cannot read the plaintext. If the database is exfiltrated, these values remain ciphertext.

Tenant Isolation — Schema Enforcement

tenant_id is NOT NULL with a foreign key to tenants(id) on every tenant-scoped table. This is enforced in the Prisma schema — any migration that omits this FK fails the CI schema validator:

// ✅ Correct — notNull() enforced in base schema
tenantId: uuid('tenant_id').references(() => tenants.id).notNull(),
 
// ❌ Wrong — nullable FK allows orphaned records
tenantId: uuid('tenant_id').references(() => tenants.id),

Database User Privileges

The application database user has only the privileges it needs:

The application never runs DROP TABLE, TRUNCATE, or DDL statements — only migrator_user can.

Immutable Records

Tables marked immutable in the schema (e.g. llm_calls, activity_runs) have UPDATE and DELETE revoked from app_user at the database level:

REVOKE UPDATE, DELETE ON llm_calls      FROM app_user;
REVOKE UPDATE, DELETE ON activity_runs  FROM app_user;

This enforces append-only semantics at the DB layer, independent of application code.