Recipicity Database Architecture Review

Reviewer: database-reviewer (SQL/Database Specialist) Date: 2026-02-25 Scope: Prisma schema, query patterns, indexing, caching, transactions, pagination Files reviewed: schema.prisma, all route files in src/routes/, src/lib/database.ts, src/lib/redis.ts, src/services/

---

Executive Summary

The Recipicity database layer is built on PostgreSQL 15 via Prisma ORM with a PgBouncer-compatible configuration. The schema is well-structured with ~45 models covering core recipe functionality, social features, meal planning, subscriptions, grocery integrations, cooking sessions, AI generation, and privacy/compliance. However, there are significant performance concerns around N+1 query patterns, missing transactions for multi-step writes, near-zero use of Redis for query caching, and inefficient pagination at scale. The schema itself is solid with good index coverage on the Recipe model but has gaps elsewhere.

Severity Rating: MEDIUM-HIGH -- Performance will degrade noticeably as user count grows past a few thousand.

---

1. Schema Design Analysis

1.1 Strengths

• Well-normalized core models. User, Recipe, Tag, Like, Rating, Comment, Follow, Bookmark are cleanly separated with proper junction tables (e.g., RecipeTag with composite primary key).
• Good use of onDelete: Cascade throughout. Deleting a User cascades to recipes, likes, comments, etc. This prevents orphaned records.
• Composite unique constraints where appropriate: [userId, recipeId] on Like, Rating, Bookmark, CookedRecipe; [followerId, followingId] on Follow; [collectionId, recipeId] on RecipeCollectionItem.
• Recipe model has solid index coverage: @@index([authorId]), @@index([published]), @@index([createdAt]), @@index([authorId, published]), @@index([published, createdAt]), @@index([slug]), @@unique([authorId, slug]).
• CUID IDs used consistently -- good for distributed generation, no sequential ID leaking.
• @@map directives maintain snake_case table names while Prisma uses camelCase -- clean naming convention.

1.2 Weaknesses

CRITICAL: User model has no indexes beyond @unique on email/username/resetToken.

The User model is queried extensively (every auth check, every profile lookup, every follower/following count) but has zero @@index directives. Prisma creates indexes for @unique fields, but there are no indexes on: - active (filtered in user search, recent users) - verified (used in sort order for search results) - createdAt / updatedAt (used for ordering) - oauthProvider + oauthId (composite index needed for OAuth login lookups)

Recommendation: Add at minimum:

@@index([active])
@@index([oauthProvider, oauthId])
@@index([updatedAt])

MEDIUM: Notification model missing createdAt sort index for the common query pattern.

The model has @@index([userId, read]) and @@index([userId, createdAt]) which is actually good. However, there is no index on just createdAt for admin/system queries.

MEDIUM: GroceryList model has no userId index.

groceryList is always filtered by userId but has no @@index([userId]). This will become a full table scan as the table grows.

Recommendation: Add @@index([userId]) to GroceryList.

LOW: Report model has no indexes at all.

Missing @@index([reporterId]), @@index([targetId]), @@index([status]).

1.3 Schema Design Concerns

Duplicate notification preferences storage. There are TWO models for notification preferences: 1. NotificationSettings -- dedicated table with granular fields 2. NotificationPreference -- for push notification preferences 3. Plus emailPreferences JSON field on the User model

The notification-settings route in users.ts tries to upsert into NotificationSettings but falls back to the JSON field on User, with a try/catch for "table may not exist yet". This is fragile -- the table either exists or it doesn't after migrations. This suggests incomplete migration management.

Recommendation: Consolidate into a single NotificationSettings model and remove the JSON fallback pattern.

Overly broad User model. The User model has 30+ fields including security, CCPA, moderation, and 20+ relations. Consider splitting into: - UserProfile (display info) - UserSecurity (password, locks, failed attempts) - UserCompliance (CCPA fields)

This is a low-priority refactor but would improve query performance by reducing row width.

JSON fields for structured data. ingredients and instructions on Recipe are stored as Json type. This is acceptable for flexible schema but means: - No relational querying on individual ingredients - The common-ingredients endpoint uses raw SQL to unnest JSONB -- this works but is fragile - Full-text search on ingredients requires raw SQL (ingredients::text ILIKE)

For the current scale this is fine, but if ingredient-level queries become important, consider a normalized RecipeIngredient table.

---

2. N+1 Query Problems

2.1 CRITICAL: User Search and Recent Users

File: /opt/development/recipicity/staging/recipicity-api/src/routes/users.ts

Lines 354-396 (GET /recent):

const users = await prisma.user.findMany({ ... });
const usersWithCounts = await Promise.all(
  users.map(async (user) => {
    const [recipeCount, followerCount] = await Promise.all([
      prisma.recipe.count({ where: { authorId: user.id, published: true } }),
      prisma.follow.count({ where: { followingId: user.id } }),
    ]);
    return { ...user, _count: { recipes: recipeCount, followers: followerCount } };
  })
);

This fires 2 additional queries per user returned. With limit=24, that's 48 extra queries. The same pattern appears in: - GET /search (lines 470-485) -- up to 100 extra queries (50 users * 2 counts) - GET /profile (lines 97-101) -- 3 extra queries per profile view - GET /:username (lines 998-1001) -- 3 extra queries per profile view

Why Prisma _count was not used: The code comments say "FIXED: Manually count followers and following to ensure accuracy" suggesting Prisma's built-in _count was giving incorrect results. This is a known Prisma issue with certain relation configurations.

Recommendation: Replace with a single raw SQL query using subqueries:

SELECT u.*,
  (SELECT COUNT(*) FROM recipes r WHERE r."authorId" = u.id AND r.published = true) as recipe_count,
  (SELECT COUNT(*) FROM follows f WHERE f."followingId" = u.id) as follower_count
FROM users u
WHERE u.active = true AND EXISTS (SELECT 1 FROM recipes r WHERE r."authorId" = u.id AND r.published = true)
ORDER BY u."updatedAt" DESC
LIMIT $1

This collapses N+1 into a single query.

2.2 MEDIUM: Recipe List Fetches All Ratings

File: /opt/development/recipicity/staging/recipicity-api/src/routes/recipes.ts

Lines 319-323 (GET /):

ratings: {
  select: {
    rating: true,
  },
},

The recipe listing endpoint fetches ALL rating records for every recipe to calculate the average rating client-side. For a recipe with 500 ratings, this sends 500 rows per recipe. With 12 recipes per page, that could be 6,000 rating rows.

Recommendation: Use Prisma's _avg aggregation or a materialized/cached average:

// Option A: Use a raw query with AVG()
// Option B: Store averageRating and ratingCount directly on the Recipe model (denormalized)
// Option C: Use Prisma's aggregate in a subquery

Option B (denormalized averageRating + ratingCount on Recipe, updated on each rating write) is the most performant for listings.

2.3 MEDIUM: Collections List Eager-Loads All Items + Recipes + Authors

File: /opt/development/recipicity/staging/recipicity-api/src/routes/collections.ts

Lines 30-55 (GET /):

const collections = await prisma.recipeCollection.findMany({
  where: { userId },
  include: {
    items: {
      include: {
        recipe: {
          include: {
            author: { select: { ... } },
          },
        },
      },
      orderBy: { sortOrder: 'asc' },
    },
    _count: { select: { items: true } },
  },
});

The "list all collections" endpoint loads EVERY recipe in EVERY collection with full author details. A user with 10 collections averaging 20 recipes each would load 200 recipe records + 200 author joins.

Recommendation: The list endpoint should only return collection metadata + _count. Load items only when viewing a single collection (GET /:id).

---

3. Missing Transactions

3.1 CRITICAL: Zero $transaction Usage

There are zero uses of prisma.$transaction() in the entire codebase. Several operations require atomicity:

Bookmark creation (bookmarks.ts lines 176-206):

const bookmark = await prisma.bookmark.create({ ... });
// Then separately:
const savedRecipesCollection = await getOrCreateSavedRecipesCollection(userId);
await prisma.recipeCollectionItem.create({ ... });

If the collection item creation fails, the bookmark exists but the Saved Recipes collection is inconsistent. The code catches the error and logs it, but this leaves data in an inconsistent state.

Recipe creation with tags (recipes.ts lines 673-743):

const recipe = await prisma.recipe.create({ ... });
// Then in a loop:
for (const tagName of parsedTags) {
  const tag = await prisma.tag.upsert({ ... });
  await prisma.recipeTag.create({ ... });
}

If the tag creation loop fails partway through, the recipe exists with only some tags. This should be wrapped in a transaction.

Recommendation: Wrap multi-step writes in prisma.$transaction():

await prisma.$transaction(async (tx) => {
  const recipe = await tx.recipe.create({ ... });
  for (const tagName of parsedTags) {
    const tag = await tx.tag.upsert({ ... });
    await tx.recipeTag.create({ ... });
  }
  return recipe;
});

3.2 Recipe tag saving is sequential, not batched

Tags are saved in a for...of loop with individual upsert + create calls. With 10 tags, that's 20 queries. Use createMany or a transaction with batch operations.

---

4. Pagination Analysis

4.1 Offset Pagination Throughout

Every paginated endpoint uses offset-based pagination (skip/take):

const skip = (page - 1) * limit;
const recipes = await prisma.recipe.findMany({ skip, take: limit });
const total = await prisma.recipe.count({ where: ... });

Problems: - skip becomes expensive at high offsets (page 100+ scans 1200+ rows to discard) - Separate count query doubles the load for every paginated request - Pages become inconsistent during concurrent writes

Current impact: LOW at current scale, but will become MEDIUM as data grows.

Recommendation for high-traffic endpoints (recipe listing, feed): - Implement cursor-based pagination using createdAt or id - Cache total counts in Redis with short TTL (30s-60s) rather than querying every request - For the recipe feed, use a cursor like ?after=<last-recipe-id> instead of ?page=N

4.2 No Pagination on Followers/Following

File: users.ts lines 1165-1205 (GET /:username/followers) and lines 1236-1275 (GET /:username/following)

These endpoints load ALL followers/following with no pagination:

const followers = await prisma.follow.findMany({
  where: { followingId: user.id },
  include: { follower: { ... } },
  orderBy: { createdAt: 'desc' },
  // No skip/take!
});

A user with 10,000 followers would return all 10,000 in a single response.

Recommendation: Add pagination parameters.

---

5. Redis/Caching Strategy

5.1 Redis is Almost Unused for Query Caching

Redis (src/lib/redis.ts) is a well-implemented singleton with connection management, but is only used for: 1. OAuth token exchange in auth.ts (3 calls: set, get, del) 2. Email queue in emailQueue.service.ts 3. Health checks in server.ts

There is zero Redis caching for: - Recipe listings (the most expensive query) - Popular recipes (recomputed on every request) - Common ingredients (raw SQL aggregation on every call) - Cuisine list (raw SQL aggregation on every call) - User profile data - Follower/following counts - Tag lists

5.2 Rate Limiting is In-Memory, Not Redis-Based

The rateLimiter.ts uses express-rate-limit with the default in-memory store. In a Docker Swarm deployment with multiple API replicas, rate limits are per-container, not per-user. A user could hit 100 req/15min per container, effectively getting 100 * N requests across N replicas.

The RedisManager class actually has a rateLimit() method built out but it is not being used by the middleware.

Recommendation: 1. Use rate-limit-redis store for express-rate-limit to share limits across replicas 2. Implement Redis caching for expensive read endpoints: - GET /api/recipes -- cache paginated results for 30s - GET /api/recipes/popular -- cache for 5 minutes - GET /api/recipes/common-ingredients -- cache for 1 hour - GET /api/recipes/cuisines -- cache for 1 hour

---

6. Query Performance Concerns

6.1 Recipe Search Uses Two-Phase Query

File: recipes.ts lines 256-329

The search endpoint first runs a raw SQL query to find matching recipe IDs (including JSONB ingredient search), then passes those IDs to a Prisma findMany. This is actually a reasonable approach, but:

• The raw SQL has no index support for ingredients::text ILIKE -- this is a full table scan on the ingredients JSONB column
• The LOWER(title) LIKE pattern cannot use a B-tree index (needs gin_trgm_ops or LOWER() functional index)
• There is no caching of search results

Recommendation: - Add a GIN trigram index: CREATE INDEX idx_recipes_title_trgm ON recipes USING gin (LOWER(title) gin_trgm_ops); - Add a GIN index on ingredients JSONB: CREATE INDEX idx_recipes_ingredients_gin ON recipes USING gin (ingredients); - Consider PostgreSQL full-text search (tsvector) for better search quality

6.2 Popular Recipes Sorts by Like Count

orderBy: [{ likes: { _count: 'desc' } }, { createdAt: 'desc' }],

Prisma translates this to a LEFT JOIN + COUNT + ORDER BY which is expensive without a materialized count. With denormalized likeCount on Recipe, this becomes a simple indexed sort.

6.3 Slug Generation Has Potential Infinite Loop

File: database.ts lines 82-93

while (true) {
  const existing = await baseClient.recipe.findFirst({
    where: { authorId, slug },
  });
  if (!existing) break;
  counter++;
  slug = `${baseSlug}-${counter}`;
}

If there is a conflict storm (unlikely but possible), this could loop many times. Add a max iteration guard.

---

7. Data Integrity Gaps

7.1 No Check Constraints on String Enums

Many fields use free-text strings with documented enum values but no database-level enforcement: - Recipe.type: "food" | "drink" -- no constraint - Recipe.difficulty: "easy" | "medium" | "hard" -- no constraint - Recipe.visibility: "public" | "private" | "friends" | "custom" -- no constraint - Recipe.moderationStatus: multiple values -- no constraint - Report.status: "pending" | "reviewed" | "resolved" -- no constraint

Invalid values can be inserted and the application will silently work with them. Prisma does not support CHECK constraints natively, but they can be added via migrations.

7.2 Soft Delete Not Implemented Consistently

Users can be "banned" (bannedAt != null) or "locked" (lockedAt != null) but there is no consistent soft-delete pattern. Some queries filter by active: true, others don't. A banned user's recipes remain visible.

7.3 No Foreign Key on Report.targetId

Report.targetId is a free-text string pointing to a recipe, user, or comment ID. There is no foreign key constraint, and the type field determines what entity it references. This is a polymorphic association anti-pattern that prevents referential integrity.

---

8. Connection Management

8.1 PgBouncer-Compatible Configuration (GOOD)

The database.ts file reads DATABASE_URL from environment or Docker secrets and supports PRISMA_CONNECTION_LIMIT and PRISMA_POOL_TIMEOUT overrides. The singleton pattern prevents multiple Prisma Client instances.

8.2 Missing Connection Pool Monitoring

The getConnectionInfo() method exists but is not called anywhere on a schedule. Consider adding periodic logging of connection stats to detect pool exhaustion early.

---

9. Migration Management

9.1 Only 5 Migrations Exist

The prisma/migrations/ directory has only 5 migrations, suggesting the schema was created via prisma db push or manual SQL, with migrations added retroactively. This means migration history does not fully represent the schema evolution.

9.2 Migration Naming is Inconsistent

• 20241212_add_collection_type
• 20241214_add_import_tracking
• 20260218_add_ai_tracking_fields
• 20260223_add_cooked_recipes
• 20260225_add_notification_settings

The 2024 vs 2026 dates suggest this project has been running for over a year, but only 5 tracked migrations exist.

---

10. Top Recommendations (Prioritized)

P0 -- Fix Now

1. Add missing indexes on User model (active, oauthProvider+oauthId, updatedAt) and GroceryList (userId). Zero-risk, immediate query improvement.

2. Fix N+1 in user search/recent/profile -- Replace Promise.all(users.map(...)) pattern with a single query using Prisma _count includes or raw SQL with subqueries. This is the single biggest performance bottleneck.

3. Use Redis-backed rate limit store -- In a multi-replica Swarm deployment, in-memory rate limiting provides no protection. Switch to rate-limit-redis.

P1 -- Fix This Sprint

1. Wrap multi-step writes in transactions -- Bookmark+collection sync, recipe+tag creation, and any other multi-model writes need $transaction().

2. Stop fetching all ratings in recipe listings -- Denormalize averageRating and ratingCount onto the Recipe model, update on rating create/update/delete.

3. Add pagination to followers/following endpoints -- Unbounded result sets will cause OOM on popular profiles.

4. Cache expensive aggregation endpoints in Redis -- common-ingredients, cuisines, popular should be cached 5-60 minutes.

P2 -- Fix This Quarter

1. Migrate from offset to cursor-based pagination for the recipe feed and any endpoint expected to have deep pagination.

2. Add PostgreSQL full-text search (tsvector) for recipe search instead of ILIKE patterns on JSONB.

3. Consolidate notification preference storage into a single model, remove JSON fallback pattern.

4. Don't eager-load collection items in the list endpoint -- Return only metadata and counts.

5. Add GIN trigram indexes for case-insensitive text search on recipe title, cuisine, and user search fields.

---

Appendix: Index Coverage Summary

Model	Has @@index	Quality
User	No custom indexes	POOR -- needs active, oauthProvider+oauthId
Recipe	7 indexes	GOOD -- well covered
Tag	Unique on name, slug	OK
RecipeTag	On recipeId, tagId	GOOD
Like	On recipeId + unique constraint	GOOD
Rating	On recipeId + unique constraint	GOOD
Comment	On recipeId, parentId	GOOD
Follow	On followerId, followingId + unique	GOOD
Bookmark	On userId, recipeId + unique	GOOD
CookedRecipe	On userId, recipeId + unique	GOOD
RecipeCollection	On userId, userId+isPublic, slug + unique	GOOD
RecipeCollectionItem	On collectionId, recipeId + unique	GOOD
Notification	On userId+read, userId+createdAt	GOOD
MealPlan	On userId, userId+startDate+endDate	GOOD
PlannedMeal	On userId+date, userId+date+mealType	GOOD
GroceryList	NO INDEXES	POOR -- needs userId
Report	NO INDEXES	POOR -- needs reporterId, status
AiGenerationLog	6 indexes	GOOD
EmailLog	4 indexes	GOOD