Phase 4: Indexing Plan

Goal

Add targeted indexes based on measured TendSocial query patterns. Do not add speculative indexes without a query/report reason.

Prisma supports database indexes, unique constraints, compound indexes, sort order on indexes, and PostgreSQL-specific index access methods through schema attributes such as @@index and @@unique. Source: Prisma indexes.

Indexing strategy

TendSocial appears workspace/tenant scoped. Most hot queries should begin with one or more of:

workspaceId
userId
brandId
campaignId
socialAccountId
status
platform
scheduledAt
createdAt
updatedAt
deletedAt

The preferred pattern is:

tenant scope first, then filter/sort field

Examples:

@@index([workspaceId, updatedAt(sort: Desc)])
@@index([workspaceId, status, updatedAt(sort: Desc)])
@@index([workspaceId, scheduledAt])

Process

1. Run all operation tests.
2. Collect top slow/high-operation SQL queries.
3. Check query shape:
   • where
   • orderBy
   • take
   • joins/includes
   • selected fields
4. Add the smallest useful index.
5. Re-run tests.
6. Keep indexes that improve measured workloads.
7. Remove speculative indexes that do not help.

Candidate indexes by domain

Content / short-form posts

model ContentItem {
  // fields...

  @@index([workspaceId, createdAt(sort: Desc)])
  @@index([workspaceId, updatedAt(sort: Desc)])
  @@index([workspaceId, status, updatedAt(sort: Desc)])
  @@index([workspaceId, scheduledAt])
  @@index([workspaceId, platform, scheduledAt])
  @@index([workspaceId, campaignId])
  @@index([workspaceId, brandId])
  @@index([workspaceId, deletedAt])
}

Calendar / scheduling

model ScheduledContent {
  // fields...

  @@index([workspaceId, scheduledAt, status])
  @@index([workspaceId, status, scheduledAt])
  @@index([workspaceId, platform, scheduledAt])
}

Blog / long-form content

model BlogPost {
  // fields...

  @@index([workspaceId, updatedAt(sort: Desc)])
  @@index([workspaceId, status, updatedAt(sort: Desc)])
  @@index([workspaceId, slug])
  @@index([workspaceId, deletedAt])
}

Do not index long text/body columns unless there is a measured search requirement. If blog/content search becomes a product feature, evaluate PostgreSQL full-text search or a dedicated search service.

Workspace membership / auth linkage

model Account {
  // fields...

  @@unique([provider, providerAccountId])
  @@index([userId])
}

model WorkspaceMember {
  // fields...

  @@unique([workspaceId, userId])
  @@index([userId])
  @@index([workspaceId, role])
}

Social accounts / publishing

model SocialAccount {
  // fields...

  @@index([workspaceId, platform])
  @@index([workspaceId, platform, status])
  @@index([workspaceId, status])
}

model PublishJob {
  // fields...

  @@index([workspaceId, publishStatus, scheduledAt])
  @@index([status, runAt])
  @@index([type, status, runAt])
}

AI draft / generation state

model AiDraft {
  // fields...

  @@index([workspaceId, createdAt(sort: Desc)])
  @@index([workspaceId, status, updatedAt(sort: Desc)])
  @@index([workspaceId, contentItemId])
  @@index([workspaceId, campaignId])
}

Index anti-patterns

Avoid:

Anti-pattern	Why
Indexing every foreign key without measured usage	Write overhead and clutter.
Indexing low-cardinality boolean fields alone	Usually poor selectivity.
Indexing long text fields casually	Large indexes and poor fit for exact btree lookup.
Creating many overlapping compound indexes	Extra write cost and maintenance.
Adding indexes without before/after tests	No proof of benefit.

Validation queries

Add a DB report script that surfaces large/hot tables:

SELECT
  schemaname,
  relname AS table_name,
  n_live_tup AS approx_rows,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Use EXPLAIN ANALYZE for measured slow queries in staging/benchmark only.

Acceptance criteria

Item	Target
Index proposals tied to benchmark/query report	Yes
Hot list/calendar/schedule queries indexed	Yes
No unbounded full-table scans on core routes	Yes
Before/after report generated	Yes
Write-heavy tables not over-indexed	Yes
Index changes committed as Prisma migrations	Yes

Sources

• Prisma indexes
• Prisma query optimization