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

> Optimize prompts.chat database performance with strategic indexing. Learn how B-tree indexes ensure millisecond-level prompt retrieval and user authentication. Read our guide.

- Repository: [Fatih Kadir Akın/prompts.chat](https://github.com/f/prompts.chat)
- Tags: performance
- Published: 2026-04-02

---

**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:

```prisma
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`](https://github.com/f/prompts.chat/blob/main/src/pages/api/mcp.ts) and [`src/app/api/prompts/search/route.ts`](https://github.com/f/prompts.chat/blob/main/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`](https://github.com/f/prompts.chat/blob/main/src/pages/api/mcp.ts), the query:

```typescript
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`](https://github.com/f/prompts.chat/blob/main/src/app/api/prompts/search/route.ts) combines single-column indexes on `authorId` and `isPrivate` with text search conditions. The query structure:

```typescript
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`](https://github.com/f/prompts.chat/blob/main/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`:

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

```

Apply the migration via the Prisma CLI:

```bash
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`:

```sql
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`](https://github.com/f/prompts.chat/blob/main/src/pages/api/mcp.ts) and [`src/app/api/prompts/search/route.ts`](https://github.com/f/prompts.chat/blob/main/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.