1. Problem Diagnosis - The endpoint performs three independent database queries for every dashboard request. - "Promise.all()" reduces sequential application wait time but does not reduce the amount of database work. - The notification query is potentially expensive because it filters by "userId" and "read", then sorts by "createdAt". - The order query filters by "userId", sorts by "createdAt", and returns only 20 records. Without a suitable composite index, PostgreSQL may scan and sort many rows. - The notification query has no "take" limit, so a user with a large number of unread notifications could cause a very large response and expensive query. - The exact execution plan is not provided, so the absence of indexes is a likely root cause rather than a confirmed fact. - The first validation step should be PostgreSQL "EXPLAIN (ANALYZE, BUFFERS)" for the order and notification queries. Confirmed: - Database CPU is high. - Connection-pool pressure is high. - The endpoint performs three database operations. - The notification query has no result limit. Assumption: - Appropriate composite indexes are missing or not being used efficiently. 2. Solution Strategy Recommended approach: 1. Add composite indexes matching the query filters and ordering. 2. Select only the fields actually required by the API. 3. Add a reasonable notification limit if the API contract allows it. 4. Keep the three independent queries concurrent. 5. Add structured error handling. 6. Verify the indexes with PostgreSQL execution plans. Recommended indexes: CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_created_at ON orders (user_id, created_at DESC); CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_notifications_user_read_created_at ON notifications (user_id, read, created_at DESC); Tradeoffs: - Indexes improve reads but increase storage and write overhead. - "select" reduces database/network work but requires knowing which fields clients actually need. - Limiting notifications improves scalability but can change behavior if clients currently expect every unread notification. Security: - Continue deriving "userId" from authenticated request context. - Never accept a user ID from an untrusted request parameter for this endpoint. - Avoid exposing internal database errors directly to clients. Performance: - Composite indexes should allow PostgreSQL to efficiently locate rows belonging to the user and retrieve them in the requested order. - "Promise.all()" is appropriate for independent queries, provided the connection pool is sized appropriately. 3. Implementation File: "src/routes/dashboard.ts" app.get("/api/dashboard", async (req, res) => { const userId = req.user.id; try { const [user, orders, notifications] = await Promise.all([ prisma.user.findUnique({ where: { id: userId }, select: { id: true, name: true, email: true } }), prisma.order.findMany({ where: { userId }, orderBy: { createdAt: "desc" }, take: 20, select: { id: true, status: true, total: true, createdAt: true } }), prisma.notification.findMany({ where: { userId, read: false }, orderBy: { createdAt: "desc" }, take: 50, select: { id: true, title: true, message: true, createdAt: true } }) ]); if (!user) { return res.status(404).json({ error: "User not found" }); } return res.json({ user, orders, notifications }); } catch (error) { console.error("Dashboard query failed", { userId, error }); return res.status(500).json({ error: "Unable to load dashboard" }); } }); Prisma schema indexes model Order { id String @id userId String status String total Decimal createdAt DateTime @@index([userId, createdAt(sort: Desc)]) } model Notification { id String @id userId String read Boolean title String message String createdAt DateTime @@index([userId, read, createdAt(sort: Desc)]) } The exact fields in "select" should be adjusted to match the existing API contract. The "take: 50" notification limit should only be introduced if the product/API contract permits limiting unread notifications. 4. Testing & Validation Unit tests Verify that: - The authenticated user's ID is used. - The user query is executed. - Only the latest 20 orders are requested. - Orders are sorted by newest first. - Only unread notifications are requested. - Database errors return HTTP 500. - A missing user returns HTTP 404. Integration tests Example: it("returns the dashboard for an authenticated user", async () => { const response = await request(app) .get("/api/dashboard") .set("Authorization", `Bearer ${token}`); expect(response.status).toBe(200); expect(response.body).toHaveProperty("user"); expect(response.body).toHaveProperty("orders"); expect(response.body).toHaveProperty("notifications"); expect(response.body.orders.length).toBeLessThanOrEqual(20); }); Error-condition test it("returns 500 when the database fails", async () => { jest .spyOn(prisma.user, "findUnique") .mockRejectedValueOnce(new Error("Database unavailable")); const response = await request(app) .get("/api/dashboard") .set("Authorization", `Bearer ${token}`); expect(response.status).toBe(500); expect(response.body.error).toBe("Unable to load dashboard"); }); Database validation Run: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 'USER_ID' ORDER BY created_at DESC LIMIT 20; And: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM notifications WHERE user_id = 'USER_ID' AND read = false ORDER BY created_at DESC LIMIT 50; Expected result: - PostgreSQL should use the relevant indexes. - Rows examined should decrease substantially. - Sorting should be minimized or eliminated. - Database execution time should decrease. - P95 API latency should improve under realistic production load. 5. Code Quality & Improvement Audit Critical - Do not expose raw database errors to API clients. - Confirm authorization middleware guarantees that "req.user.id" belongs to the authenticated user. High - Validate the actual database execution plan before assuming indexes are the primary bottleneck. - Prevent unbounded notification queries. - Monitor connection-pool utilization after deployment. Medium - Select only fields required by the response. - Add structured logging around query latency. - Consider pagination for notifications if the product requires more than a fixed limit. Low - Extract dashboard data access into a service/repository layer if the codebase follows that architecture. - Add performance regression tests or endpoint benchmarks. 6. Implementation Checklist - Step 1: Capture "EXPLAIN (ANALYZE, BUFFERS)" for the slow queries. - Step 2: Add composite indexes for orders and notifications. - Step 3: Restrict Prisma queries to required fields. - Step 4: Add bounded notification retrieval if compatible with the API contract. - Step 5: Run unit, integration, and performance tests. Files to change - "src/routes/dashboard.ts" - "prisma/schema.prisma" - Prisma migration files - Relevant test files Dependencies to install - No additional dependency is required for the proposed optimization. Configuration changes - Review Prisma/PostgreSQL connection-pool configuration if exhaustion continues. Environment variables - No new environment variables are required. Database/migration requirements - Add the two composite indexes. - Prefer a production-safe migration strategy for large tables, such as concurrent index creation where supported by the deployment/migration process. Testing requirements - Unit tests - Integration tests - Database query-plan validation - Load/performance testing Deployment considerations - Deploy indexes safely before or alongside the application change. - Monitor database CPU, query latency, connection-pool usage, and API P95/P99 latency. Rollback considerations - Application changes can be rolled back independently. - Indexes can be removed later if necessary, but should not be removed casually because other queries may depend on them. Final recommended solution: Keep the concurrent "Promise.all()" approach, add query-aligned composite indexes, eliminate unnecessary column retrieval, and prevent unbounded notification results. Main technical risk: Changing the notification result limit could alter existing client behavior, so confirm the API contract before enforcing it. Most important validation step: Compare PostgreSQL "EXPLAIN (ANALYZE, BUFFERS)" results and production-like P95/P99 latency before and after the indexes.