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. ER diagrams for all tables: See SQL Entity Relations.

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

registration_sessions

Persists partial state for the 4-step self-registration wizard. The tenant and user records are not created until Step 4 (payment) succeeds — until then, all collected data lives here.

Why a separate table: Registration takes multiple page loads across multiple steps, possibly across multiple sessions (user closes tab and returns). Data must be persisted without creating a real tenant account prematurely.

Lifecycle:

1. Row created when user submits Step 1 (Contact); session_token set in HttpOnly cookie
2. Each step submission updates contact_data/company_data/location_data and increments current_step
3. Returning user (same browser): cookie re-identifies session → resumed at current_step
4. Payment success (Razorpay webhook): contact_data, company_data, location_data, billing_data extracted → tenants + users records created → registration_sessions row deleted
5. Expired rows: cron job deletes rows where expires_at < NOW()

Security: passwordHash in contact_data is bcrypt-hashed before storage. panNumber and gstNumber are stored as plaintext in JSONB (non-sensitive Indian tax IDs). session_token is compared with crypto.timingSafeEqual.

CREATE INDEX idx_registration_sessions_token
  ON registration_sessions(session_token);
 
CREATE INDEX idx_registration_sessions_expiry
  ON registration_sessions(expires_at);

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)

approvals

Content and strategy approvals that route through the HITL pipeline. Each approval is linked to an activity and expires after 72 hours if unresolved, triggering escalation.

CREATE INDEX idx_approvals_pending
  ON approvals(tenant_id, expires_at)
  WHERE status = 'pending';
 
CREATE INDEX idx_approvals_activity
  ON approvals(activity_id);

Agents & Sessions

agent_configs

Tenant-specific agent configuration.

sessions

LLM session continuity records.

llm_providers

LLM provider configurations — stores connection details and API credentials for all AI providers available on the platform. Referenced by agent configs and the Security section.

CREATE INDEX idx_llm_providers_active
  ON llm_providers(provider_type, is_active)
  WHERE is_active = true;

Billing & Costs

credit_balances

Live credit position per tenant. One row per tenant, updated atomically on every reservation, consumption, release, and monthly reset.

Effective available balance = credits_available - credits_reserved.

credit_ledger

Immutable append-only log of every credit movement. Source of truth — credit_balances is a materialised view of this table.

CREATE INDEX credit_ledger_tenant_idx ON credit_ledger(tenant_id, created_at DESC);
CREATE INDEX credit_ledger_run_idx    ON credit_ledger(activity_run_id);

credit_topup_orders

Top-up bundle purchases via Razorpay.

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.

reports

Performance reports generated by the Report Writer agent or manually uploaded, covering a specific deliverable period.

CREATE INDEX idx_reports_tenant_period
  ON reports(tenant_id, period_start DESC);

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.

email_templates

Transactional email templates used by the system for notifications, reports, and client communications. Supports global system templates and per-tenant overrides.

CREATE UNIQUE INDEX idx_email_templates_slug_tenant
  ON email_templates(slug, COALESCE(tenant_id, '00000000-0000-0000-0000-000000000000'::uuid));

integrations

API-key based third-party tool and data integrations (SEMrush, DataForSEO, etc.). Distinct from OAuth channels — these are tool/data integrations, not publishing channels.

CREATE UNIQUE INDEX idx_integrations_tenant_type
  ON integrations(tenant_id, integration_type);

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.

Notifications

Related: Notifications Service

notifications

In-app notification records shown in the Dashboard notification centre. One row per notification per user.

CREATE INDEX notifications_user_unread_idx
  ON notifications(user_id, tenant_id, read, created_at DESC)
  WHERE read = false;

notification_providers

Per-tenant notification provider configuration. One row per channel per tenant. If no verified row exists for a channel, the platform default provider is used automatically.

ALTER TABLE notification_providers
  ADD CONSTRAINT notification_providers_tenant_channel_unique UNIQUE (tenant_id, channel);

notification_dispatch_log

Append-only audit log of every outbound notification dispatch attempt across all channels (email, SMS, WhatsApp, web).

CREATE INDEX notification_dispatch_tenant_idx
  ON notification_dispatch_log(tenant_id, created_at DESC);
CREATE INDEX notification_dispatch_job_idx
  ON notification_dispatch_log(notification_job_id);

Knowledge Base (RAG)

Related: RAG Architecture

rag_datasets

Dataset containers — one per knowledge domain (e.g. client_docs, website_content, published_content, competitor_content). Each dataset maps to one Qdrant collection named ds_{ref_id}.

CREATE INDEX rag_datasets_tenant_idx
  ON rag_datasets(tenant_id);

rag_files

Individual files uploaded or crawled into a dataset. Each file is processed through the ingestion pipeline (parse → chunk → embed → index into Qdrant).

CREATE INDEX rag_files_dataset_idx
  ON rag_files(dataset_id);
 
CREATE INDEX rag_files_tenant_idx
  ON rag_files(tenant_id);

rag_crawl_jobs

Website crawl run records. Tracks progress and results for each crawl triggered against a dataset.

CREATE INDEX rag_crawl_jobs_dataset_idx
  ON rag_crawl_jobs(dataset_id);

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.

Raw SQL / SQL Injection Prevention

Rule: never write inline SQL. Always use Prisma ORM methods.

Raw SQL bypasses Prisma’s type safety and parameterization, opening the door to SQL injection if a variable is ever interpolated directly into the query string.

Use Prisma ORM — not raw queries

// ✅ Correct — Prisma ORM (safe, type-checked)
await db.emailTemplate.deleteMany({ where: { slug: { startsWith: prefix } } });
await db.tenant.findMany({ where: { status: "active" } });
 
// ❌ Wrong — raw SQL with string interpolation
await db.$executeRaw`DELETE FROM "email_template" WHERE slug LIKE ${"prefix" + ts + "-%"}`;
await db.$executeRawUnsafe(`SELECT * FROM tenants WHERE status = '${status}'`);

When raw SQL is unavoidable

A small number of operations have no ORM equivalent (e.g. PostgreSQL DDL: CREATE DATABASE, DROP DATABASE). In those cases:

1. Use CLI tools instead — dropdb, createdb, psql -c via execSync. No Prisma raw query needed.
2. If you must use $queryRaw / $executeRaw, use the backtick tagged-template form only. Values must be passed as ${} placeholders — Prisma sends them as bound parameters, not string interpolation.
3. Never use $queryRawUnsafe or $executeRawUnsafe — these skip parameterization entirely.

Banned patterns

The production codebase had all four of these patterns removed in April 2026. If a new raw query is needed, use the Prisma ORM equivalent or open a discussion before writing raw SQL.