Database Indexing Strategies for Optimal Prompt and User Query Performance in prompts.chat

prompts.chat leverages PostgreSQL with Prisma ORM and implements strategic single-column and compound B-tree indexes in prisma/schema.prisma to eliminate sequential scans, enabling millisecond-level filtered prompt retrieval and user authentication even as the dataset scales.

The open-source prompts.chat repository manages complex query patterns across thousands of AI prompts, requiring efficient filtering by author, visibility status, and temporal ordering. By declaring indexes that mirror the exact where clauses and orderBy sequences used in the Next.js API routes, the application ensures that Prisma queries translate into optimized PostgreSQL execution plans with index-only scans.

Core Indexing Architecture in the Prisma Schema

The database schema defines indexes on high-cardinality foreign keys and low-cardinality status flags alike, covering the three primary access patterns: direct primary-key lookups, visibility-filtered listings, and relation traversal.

Single-Column Indexes for Direct Lookups

Single-column B-tree indexes accelerate equality filters on foreign keys and unique constraints. In prisma/schema.prisma, the Prompt model declares @@index([authorId]), @@index([categoryId]), @@index([type]), and @@index([slug]) to support queries that fetch prompts by specific authors or slugs without table scans.

The User model enforces constant-time authentication lookups through unique constraints on email, username, and apiKey, which PostgreSQL implements as unique indexes automatically.

Compound Indexes for Visibility and Sorting

The heaviest query pattern filters public prompts by visibility flags then orders by recency. The schema defines a critical compound index:

model Prompt {
  // ... fields ...
  @@index([isPrivate, isUnlisted, deletedAt, createdAt(sort: Desc)])
}

This index allows PostgreSQL to satisfy the WHERE isPrivate = false AND isUnlisted = false AND deletedAt IS NULL predicate and the ORDER BY createdAt DESC clause in a single index scan, avoiding a separate sort phase or heap lookups.

How API Routes Leverage Database Indexes

The application code in src/pages/api/mcp.ts and src/app/api/prompts/search/route.ts constructs Prisma queries that align precisely with the indexed columns, ensuring the query planner selects bitmap index scans over sequential heap scans.

Public Feed Optimization with Compound Scans

The MCP API endpoint queries public prompts using a filter object that matches the compound index columns. According to the source code at lines 85–90 in src/pages/api/mcp.ts, the query:

const prompts = await db.prompt.findMany({
  where: promptFilter,  // { isPrivate: false, isUnlisted: false, deletedAt: null }
  orderBy: { createdAt: "desc" },
  select: { id: true, slug: true, title: true, description: true },
});

This leverages the Prompt_isPrivate_isUnlisted_deletedAt_createdAt_idx compound index to return rows in sorted order directly from the index, eliminating the need for an in-memory SORT operation.

Search Endpoint Filtering

The search route at lines 24–57 in src/app/api/prompts/search/route.ts combines single-column indexes on authorId and isPrivate with text search conditions. The query structure:

const prompts = await db.prompt.findMany({
  where: {
    deletedAt: null,
    isUnlisted: false,
    AND: [
      ownerOnly && session?.user
        ? { authorId: session.user.id }
        : {
            OR: [{ isPrivate: false }, ...(session?.user ? [{ authorId: session.user.id }] : [])],
          },
      { OR: titleConditions },
    ],
  },
  orderBy: [{ isFeatured: "desc" }, { viewCount: "desc" }],
});

PostgreSQL uses the @@index([authorId]) and @@index([isPrivate]) entries to bitmap-index-scan and quickly eliminate rows the requesting user cannot access, then applies the remaining predicates.

Foreign Key Indexes for Related Data

Tables such as PromptVote, PromptVersion, Comment, and PromptConnection declare @@index([promptId]) to accelerate joins. When the MCP API counts votes at lines 33–40 in src/pages/api/mcp.ts using _count: { select: { votes: true } }, Prisma generates SQL that utilizes the prompt_votes_promptId_idx index for an index-only scan, keeping vote aggregation queries at O(log N) complexity.

Implementing and Verifying Custom Indexes

When adding features that query new column combinations, you must extend the schema with compound indexes that match the filter and sort order exactly.

Adding a Performance Index

To optimize a hypothetical "trending by category" feature, append a new compound index to prisma/schema.prisma:

model Prompt {
  // ... existing fields and indexes ...
  @@index([categoryId, isPrivate, viewCount(sort: Desc), createdAt(sort: Desc)])
}

Apply the migration via the Prisma CLI:

npx prisma migrate dev --name add_category_trending_index

Validating Index Usage with EXPLAIN

After deploying indexes, verify PostgreSQL uses them by running an analyzed query plan in psql:

EXPLAIN ANALYZE
SELECT "id", "title", "slug"
FROM "Prompt"
WHERE "isPrivate" = FALSE
  AND "isUnlisted" = FALSE
  AND "deletedAt" IS NULL
ORDER BY "createdAt" DESC
LIMIT 20;

The output should display Index Scan using Prompt_isPrivate_isUnlisted_deletedAt_createdAt_idx rather than Seq Scan, confirming the compound index serves both the visibility filters and the descending sort order.

Summary

• Compound indexes on isPrivate, isUnlisted, deletedAt, and createdAt allow the public feed and search endpoints to filter and sort prompts in a single index scan.
• Single-column indexes on foreign keys like authorId, categoryId, and promptId enable constant-time lookups for user profiles, prompt categories, and related entities like votes and comments.
• Unique constraints on User.email, username, and apiKey provide O(1) authentication lookups.
• Prisma queries in src/pages/api/mcp.ts and src/app/api/prompts/search/route.ts are intentionally structured to align with these indexes, ensuring PostgreSQL selects bitmap or index-only scan plans.

Frequently Asked Questions

Why does prompts.chat use a compound index for visibility filters rather than separate single-column indexes?

PostgreSQL can combine single-column indexes using bitmap scans, but a compound index on (isPrivate, isUnlisted, deletedAt, createdAt) is more efficient because it stores rows pre-sorted by createdAt within each visibility partition. This eliminates a separate SORT step when the query orders by newest first, reducing I/O and CPU overhead for the paginated public feed.

How do foreign key indexes improve the performance of prompt vote and version queries?

Tables like PromptVote and PromptVersion contain promptId foreign keys that are queried with findMany to aggregate counts or list history. The @@index([promptId]) declarations allow Prisma to generate SQL that performs index-only scans on these relations, avoiding full table scans when fetching related data for a specific prompt.

What is the performance impact of indexing low-cardinality columns like isFeatured or type?

Even though columns like type (e.g., "text" vs "image") have few distinct values, indexing them benefits multi-column filter queries. PostgreSQL uses these indexes for bitmap index scans that quickly eliminate large portions of the table before applying more selective predicates, particularly when combined with high-cardinality filters like authorId in the search endpoint.

How can I verify that PostgreSQL is actually using the indexes defined in the Prisma schema?

Run EXPLAIN (ANALYZE, BUFFERS) on your query in the PostgreSQL console or Prisma Studio. Look for Index Scan or Index Only Scan operations referencing the specific index name (e.g., Prompt_isPrivate_isUnlisted_deletedAt_createdAt_idx). If you see Seq Scan on large tables, the query planner is not using the index, indicating you may need to update statistics with ANALYZE or adjust the query structure to match the indexed column order.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}