Indexing¶

How indexing is managed in the PostgreSQL schema for dichit-backend.

Current state¶

prisma/schema.prisma declares 341 indexes/uniques across 95 models (@@index / @@unique). Indexes are declared declaratively in the schema and become DDL in generated migrations — never hand-added SQL outside migrations.

Declaring an index¶

model AuctionBid {
  // ...
  auctionId String @map("auction_id")

  @@index([auctionId, createdAt])
}

Where indexes matter most¶

Hot path	Likely indexed key
Lookup by composite keys	`(parentId, createdAt)` on bids/events/audit logs
Uniqueness	`@@unique` on `(companyId, code)`, `(userId, programId)`-style constraints
WebSocket replay	`AuctionBidActivityEvent` sequence reads
Auth lookups	`UserAuthProvider.(userId, provider)`, sessions by token
Location cascades	`City.stateId`, `Pincode.cityId`, `PostOffice.pincodeId`
Notifications	`UserNotification.(userId, readAt)`, `NotificationQueue.status`
Soft-deleted lists	composite `(tenantId, deletedAt)` filters

Guidelines¶

Index for the query, not for the model. Confirm the slow query (see optimization.md) before adding an index.
Composite over single when filters are always combined.
Respect ordering — match the index column order to the WHERE/ORDER BY.
Watch selectivity — small tables and low-cardinality columns rarely need indexes.
Multi-column indexes can cover SELECT columns; use this instead of include of huge relations.
Unique constraints double as indexes — don't add a redundant @@index.

Adding an index safely¶

# 1. edit schema.prisma
# 2. create migration
pnpm migrate:local:create
# 3. review the generated SQL
# 4. apply
pnpm migrate:local:up

For very large tables in production, create indexes with CONCURRENTLY in a dedicated migration and verify lock behaviour.

Detecting missing indexes¶

LOG_PRISMA_SLOW_QUERY_MS flags slow queries (see debugging).
Check pg_stat_statements / EXPLAIN ANALYZE on the hot path.
Search for N+1 patterns first — they look like indexing problems (see optimization.md).