API Review Task: GET /events (with related transactions)
Context
You are reviewing an existing GET /events endpoint that:
-
Queries events from a relational database.
-
For each event, fetches its related transactions for further processing.
Assume a typical web stack (HTTP service + relational DB) and that events have many transactions via a foreign key (transactions.event_id → events.id).
Your Tasks
-
Identify problems in the current implementation across:
-
Readability and design: duplicate code, poor naming/structure, unclear responsibilities.
-
Security: SQL injection risks, input validation gaps, authentication/authorization, unsafe error handling, PII exposure.
-
Performance: N+1 queries, excessive round trips, lack of batching/joins, missing indexes, no pagination, lack of caching.
-
Propose concrete fixes, including:
-
Refactors to clarify layers and eliminate duplication.
-
Parameterized queries and schema-based input validation.
-
Enforce authentication and authorization (least-privilege access).
-
Implement pagination and proper HTTP status codes.
-
Batch/prefetch related data to avoid N+1 queries.
-
Add appropriate database indexes.
-
Safe API caching (ETag/Last-Modified, Cache-Control) where applicable.
-
Outline a test plan covering:
-
Unit and integration tests.
-
Security tests (injection attempts, authN/authZ checks, PII leakage).
-
Performance tests (query counts, latency, load).