{
  "schema_version": 1,
  "skill_name": "backend-engineering",
  "evals": [
    {
      "id": "api-implementation-review",
      "prompt": "A teammate just implemented a REST endpoint to update a customer profile (PUT /customers/{id}). The handler parses the request body directly with no schema validation, writes the fields straight to the database from the handler function, returns 200 with an empty body on success, and catches every database error and turns it into a generic 500 with a stack trace in the response. Review this implementation against backend-engineering patterns and tell me what to change.",
      "expected_output": "An API implementation review that walks the endpoint from request to response: validate the request against an explicit schema before the handler runs and return 400 with a structured error body listing the offending fields; separate the HTTP layer from business logic so the handler delegates to a service layer instead of writing to the database directly; return a representation of the updated resource (200 with the updated entity, or 204 only for delete-style operations) with consistent content negotiation; map known failures to specific status codes (404 for a missing customer, 409 for a version conflict) and reserve 500 for unexpected errors, logging the stack trace server-side rather than echoing it to the client; and add an idempotency consideration for retried PUTs by supporting If-Match/ETag or a version field.",
      "assertions": [
        "The review requires request schema validation that returns a 400 with a structured body identifying the invalid fields",
        "The review separates the HTTP handler from business logic and moves database access into a service or repository layer",
        "The review requires a resource representation in the success response and maps known failures to specific 4xx status codes",
        "The review says stack traces must stay in server logs, not client responses, and 500 is reserved for unexpected errors",
        "The review adds a concurrency or idempotency mechanism such as If-Match with an ETag or a version field for updates"
      ]
    },
    {
      "id": "api-endpoint-resource-modeling",
      "prompt": "I am designing the API for a subscription billing system. I need endpoints for listing subscriptions, fetching a subscription with its invoices, changing a plan, and cancelling. How should I model the resources and endpoints, and how do I handle pagination, filtering, and the transition between plan states?",
      "expected_output": "A resource model with nouns and stable identifiers: /subscriptions for the collection, /subscriptions/{id} for a single subscription, and /subscriptions/{id}/invoices as a nested read-only collection with cursor or offset pagination, ordering, and filtering by status. State transitions such as plan changes and cancellation are expressed as explicit operations on the resource (PATCH with a status field, or purpose-specific actions) rather than inventing endpoints for verbs. The design covers idempotency keys for state-changing operations so retries cannot double-charge, a 404 versus 403 distinction for cross-tenant access, and versioning that keeps the existing client contract stable while the model evolves.",
      "assertions": [
        "The response models resources as nouns with nested read-only collections for related data such as invoices",
        "The response covers pagination, ordering, and filtering for collection endpoints",
        "The response expresses state transitions as operations on the resource rather than verb-only endpoints",
        "The response requires idempotency keys on state-changing operations such as plan changes and cancellation",
        "The response distinguishes 404 from 403 for access control and addresses API versioning"
      ]
    },
    {
      "id": "service-structure-review",
      "prompt": "Our order service started as a prototype and is now in production. All business logic lives in the route handlers, shared helpers are piling up in a 3,000-line utils.py, the database is accessed directly from handlers, and every feature branch touches the same files. I want to restructure it so it is testable and survives the next two years of features. Where do I start?",
      "expected_output": "A service structure plan that introduces layers with a strict dependency direction: transport (HTTP/gRPC handlers) at the edge, an application/service layer owning business rules and use cases, and a persistence layer behind a repository or data-access interface. Utils.py is decomposed into focused modules grouped by responsibility, and shared logic is extracted into the layer where its dependencies live. The plan defines ports and adapters at the boundaries (repository interface, message publisher, clock) so the service layer can be unit-tested with fakes, and it sequences the refactor: introduce the boundary interfaces first with the existing behavior as the contract, move business rules out of handlers feature by feature, and keep each step covered by tests.",
      "assertions": [
        "The response structures the service into transport, application/service, and persistence layers with a strict dependency direction",
        "The response decomposes the shared utils module into focused, responsibility-scoped modules",
        "The response uses ports and adapters (repository interface, message publisher, clock) so business logic is testable with fakes",
        "The response sequences the refactor starting from boundary interfaces with existing behavior as the contract",
        "The response requires test coverage at each step of the refactor"
      ]
    },
    {
      "id": "error-handling-design",
      "prompt": "Our new payments service needs consistent error handling across REST endpoints and background job processing. Today every handler invents its own error responses, retries are missing, and when a webhook fails we lose the event. Design the error-handling model for this service.",
      "expected_output": "An error-handling model with three parts: classification, representation, and recovery. Classification distinguishes client errors (validation, not found, conflict), transient server-side failures (timeouts, overload, dependency outages), and permanent server failures. The response format is structured and consistent — a stable error code, a human message, and a correlation ID — with the mapping from internal exceptions to codes owned in one place. Recovery is per failure class: retries with exponential backoff and jitter for transient failures, idempotency keys so retried operations are safe, dead-letter handling for background jobs that exhaust retries, and circuit breaking toward degraded dependencies. Every handled error carries enough context for observability (trace ID, request ID, service) so the handler does not need the stack trace.",
      "assertions": [
        "The response classifies errors into client, transient, and permanent failure classes",
        "The response defines a single structured error representation with a stable code, message, and correlation ID",
        "The response prescribes retry with exponential backoff and jitter for transient failures",
        "The response requires idempotency keys and dead-letter handling for jobs that exhaust retries",
        "The response ties error responses to observability correlation IDs rather than exposing stack traces"
      ]
    },
    {
      "id": "database-n-plus-one-detection",
      "prompt": "GET /orders returns a list of orders, and each order row is followed by a loop that fetches that order's line items and customer one at a time. The endpoint is fast with 10 orders and crawls with 500. Walk me through diagnosing and fixing this, and how I would catch the same problem in the next codebase.",
      "expected_output": "A diagnosis that names the N+1 query pattern: one query for the orders plus one query per order for line items and customer, so 500 orders produce 1,001 queries. The fix batches the data access: one query with a WHERE IN over the collected order ids for line items and one for customers, joining or grouping in memory, and indexing the foreign keys involved. The response also covers pagination so a page is bounded, and prevention: review loops that contain query calls (for example by running the n1-query-spotter script over the codebase), prefer ORM eager-loading or explicit batch queries, and add a query-count assertion to tests so a regression fails the suite.",
      "assertions": [
        "The response names the N+1 pattern and quantifies it as one query per row on top of the initial query",
        "The response fixes it by batching with WHERE IN queries or joins and indexing the foreign keys",
        "The response adds pagination so the result set is bounded",
        "The response mentions running the n1-query-spotter script or reviewing loops that contain query calls as a prevention step",
        "The response adds query-count assertions to tests so N+1 regressions fail CI"
      ]
    },
    {
      "id": "integration-retry-idempotency",
      "prompt": "We call a third-party inventory API from our order service. Occasionally the API times out or returns 503, and when that happens the whole request fails and the user retries manually, which sometimes creates duplicate inventory holds. Design the integration layer for this dependency.",
      "expected_output": "An integration layer design with a client wrapper that owns timeouts, retries with exponential backoff and jitter for transient statuses and timeouts, and a circuit breaker so a failing dependency does not stall every caller. Idempotency keys on the inventory-hold request let the client retry safely without duplicate holds, and the design handles the ambiguity case (timeout before response) by checking the hold status with a GET before retrying the mutation. The layer also defines what happens after retries are exhausted: the order request fails fast with a structured, classified error instead of hanging, and a fallback (queue the operation or surface a clear error) is chosen deliberately.",
      "assertions": [
        "The response wraps the dependency in a client with explicit timeouts and retry with exponential backoff and jitter",
        "The response adds a circuit breaker so a failing dependency does not stall all callers",
        "The response uses idempotency keys so retried inventory-hold requests cannot create duplicates",
        "The response resolves timeout ambiguity by querying the hold status before retrying the mutation",
        "The response defines failure behavior after retries are exhausted rather than hanging"
      ]
    },
    {
      "id": "event-driven-command-boundary",
      "prompt": "An order command updates the orders table and then publishes OrderConfirmed directly to the broker before the database transaction commits. A broker timeout causes the handler to retry, and some consumers send duplicate emails. Design the implementation flow and tests.",
      "expected_output": "A service implementation plan that separates domain, application, and infrastructure responsibilities; uses a unit of work to commit aggregate state and an outbox record atomically; publishes only after commit; assumes duplicate delivery; chooses one atomic inbox transaction design with a uniqueness constraint and acknowledges completed duplicates without claiming an unpersisted outcome; uses idempotent side-effect handling; classifies retryable versus permanent failures; and tests crash, duplicate, retry, quarantine, and replay behavior with lifecycle observability.",
      "assertions": [
        "The response places domain behavior, application coordination, and broker/database adapters behind explicit boundaries",
        "The response commits state and an outbox record in one local transaction and does not publish inside the transaction",
        "The response uses stable event identity plus consumer-scoped inbox deduplication, chooses one coherent atomic inbox transaction design, and acknowledges duplicates without claiming an unpersisted outcome",
        "The response distinguishes retryable, permanent, poison, and replay cases and prevents duplicate email side effects",
        "The response includes tests and metrics/logs/traces for outbox age, duplicates, retries, lag, and dead-letter handling"
      ]
    },
    {
      "id": "event-replay-and-failure",
      "prompt": "A payment consumer has a backlog after a deployment. Some events are from an older schema version, one handler bug creates poison messages, and the relay may have published before crashing. Give an implementation and recovery checklist, but do not redesign the public event contract.",
      "expected_output": "A bounded handler/replay checklist that treats delivery as at-least-once, deduplicates stable event identities, uses versioned translation or a repair path for old schemas, quarantines poison messages, records replay scope and handler version, retries only transient failures, and verifies side effects and observability. It uses the same consumer identity by default and warns that fresh-consumer replay defeats inbox deduplication unless the handler is side-effect-safe or the replay uses repair/compensation. It explicitly routes public contract semantics to api-design-and-evolution.",
      "assertions": [
        "The checklist assumes duplicate publication and requires consumer deduplication",
        "The response handles old schema versions through a versioned translator or repair path rather than silently applying changed rules",
        "The response quarantines poison messages and defines bounded retry and replay stop conditions",
        "The response records replay selection, handler version, operator/evidence, verifies side effects, and warns that a fresh consumer scope defeats deduplication unless side-effect-safe or repair/compensation handling is used",
        "The response does not redesign the event contract and routes contract semantics to api-design-and-evolution"
      ]
    },
    {
      "id": "migration-coexistence-handoff",
      "prompt": "We are moving invoice calculation from a monolith module to an approved service boundary. For two releases both paths must exist, the monolith currently owns writes, and a legacy status vocabulary differs from the new service's model. What should the backend implementation team build and measure?",
      "expected_output": "An implementation seam plan using an adapter and explicit anti-corruption translation, a selectable strangler handoff, one declared authority for each operation, comparison or shadow execution without duplicated irreversible side effects, and observable late-old-write detection. It proves the new path authoritative and accepting writes before disabling old writers, then names evidence-based removal conditions while routing decomposition and migration lifecycle decisions to their owners.",
      "assertions": [
        "The response uses an adapter and explicit translation boundary to keep legacy vocabulary out of the new domain/application model",
        "The response makes old and new paths selectable and keeps the monolith authoritative until comparison evidence supports handoff",
        "The response declares authority per operation/data field and addresses duplicate side effects, lag, disagreement, and late old writes",
        "The response sequences new-path authority and verified write acceptance before disabling old writers, then names measurable handoff and removal conditions including callers, queues/writes, flags, credentials, and recovery evidence",
        "The response routes target decomposition to software-architecture and cross-system migration lifecycle to migration-engineering"
      ]
    },
    {
      "id": "event-consumer-security-boundary",
      "prompt": "A service consumes signed order events from a broker. The handler currently deserializes the payload with a general-purpose object loader, trusts the producer_id and event_id fields, interpolates a payload field into a SQL query, and stores rejected messages by writing the raw payload into a shared quarantine table. Design the implementation changes and tests without redesigning the public event contract.",
      "expected_output": "An implementation plan that verifies producer authenticity and authorization before trusting event identity, uses safe data-only deserialization and treats fields as untrusted at every boundary, parameterizes database access and protects command/template/path sinks, stores quarantine material as bounded opaque or encrypted bytes with sanitized metadata and restricted access, and routes detailed threat modeling and security-control design to secure-software-engineering while keeping handler and boundary tests in backend-engineering.",
      "assertions": [
        "The response verifies producer authenticity and authorization before trusting producer-supplied event identity and routes the control design to secure-software-engineering",
        "The response requires safe data-only deserialization and treats deserialized fields as untrusted before domain mapping or use in SQL, commands, templates, or paths",
        "The response requires parameterized or otherwise safe handling at injection sinks",
        "The response stores quarantined content as bounded opaque or encrypted data with sanitized metadata and restricted access rather than blindly reparsing raw payloads",
        "The response includes backend boundary tests for forged identity, unsafe payloads, injection attempts, and quarantine handling without redesigning the public event contract"
      ]
    }
  ]
}

# API Patterns

## Endpoint Design

| Aspect | REST | gRPC | GraphQL |
|--------|------|------|---------|
| Resource modeling | Nouns as resources, verbs as methods | Services with RPC methods | Schema-defined types and queries |
| Request structure | Path params, query params, headers, body | Protobuf messages | Query/mutation with variables |
| Response structure | JSON with envelope | Protobuf messages | Shape matches query structure |
| Error reporting | HTTP status codes + error body | gRPC status codes + details | Errors in `errors` array |
| Versioning | URL path or header | Package version in proto | Schema evolution with deprecation |
| Pagination | Cursor-based preferred | Token-based in proto args | Connection/edges pattern (Relay) |

## Versioning Strategies

| Strategy | Mechanism | Breaking change handling |
|----------|-----------|------------------------|
| URL path | `/v1/resources`, `/v2/resources` | New path, old path maintained |
| Header | `Accept: application/vnd.api+json; version=2` | New accept header value |
| Query param | `?version=2` | New param value, old default maintained |
| No versioning | Evolve in place with additive changes | Only additive changes permitted |

Prefer URL path versioning for public APIs — it's the most visible and least ambiguous.

## Pagination

| Strategy | Pros | Cons | Best for |
|----------|------|------|----------|
| Cursor-based | Stable under writes, no offset drift | Opaque cursors, can't jump to page N | Real-time data, feeds |
| Offset-based | Simple, can jump to any page | Skips/duplicates on writes | Static datasets, admin UIs |
| Keyset | Fast, stable | Requires sort key, complex multi-column | Large datasets, ordered data |

Always include pagination metadata: `{data: [...], next_cursor: "...", has_more: true}`.

## Error Response Format

```json
{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "The request body is malformed.",
    "details": [
      {"field": "email", "reason": "must be a valid email address"},
      {"field": "age", "reason": "must be a positive integer"}
    ],
    "request_id": "req_abc123"
  }
}
```

Every error response should include: machine-readable code, human-readable message, request ID for tracing, and structured details for programmatic handling.

# Backend Engineering Methodology Reference

> Database access patterns & service-level testing — a comprehensive reference for backend engineering teams.
> Compiled: 2026-06-05

---

## Table of Contents

1. [Connection Pooling Configuration & Sizing](#1-connection-pooling-configuration--sizing)
2. [Query Optimization — Index Usage, Query Plans, EXPLAIN](#2-query-optimization--index-usage-query-plans-explain)
3. [N+1 Detection & Mitigation](#3-n1-detection--mitigation)
4. [Pagination Strategies — Cursor vs Offset vs Keyset](#4-pagination-strategies--cursor-vs-offset-vs-keyset)
5. [Transaction Boundary Design](#5-transaction-boundary-design)
6. [Read/Write Splitting](#6-readwrite-splitting)
7. [Replication Lag Handling](#7-replication-lag-handling)
8. [Service-Level Testing Overview](#8-service-level-testing-overview)
9. [Unit Testing Business Logic](#9-unit-testing-business-logic)
10. [Integration Testing — API Contracts, Testcontainers, WireMock](#10-integration-testing--api-contracts-testcontainers-wiremock)
11. [Contract Testing — Pact](#11-contract-testing--pact)
12. [Test Fixtures](#12-test-fixtures)
13. [CI Integration](#13-ci-integration)

---

## 1. Connection Pooling Configuration & Sizing

### The Problem

Creating a new TCP connection per request does not scale. At 10K+ RPS, the database is overwhelmed. PostgreSQL defaults to 100 simultaneous connections; exceeding that produces "sorry, too many clients already." Each new connection setup adds 20-50 ms of latency.

### The Solution

Connection pooling pre-establishes a fixed set of connections at application startup. Threads borrow a connection, execute queries, and return it to the pool.

```
┌──────────────┐     borrow     ┌──────────────────┐
│  App Thread  │ ─────────────→ │   Connection     │
│  (request)   │                │     Pool         │
│              │ ←───────────── │  (HikariCP/      │
│              │    return      │   pgBouncer)     │
└──────────────┘                └────────┬─────────┘
                                        │
                              ┌─────────▼─────────┐
                              │  Database Server   │
                              │  (PostgreSQL/MySQL)│
                              └───────────────────┘
```

### Pool Sizing Formula

The most commonly cited rule of thumb: **pool size = 2x (number of CPU cores)**.

However, the correct approach is empirical:

1. **Start small** — 20-30 connections for most services.
2. **Run load tests** with real traffic patterns. Monitor DB CPU, memory, connection wait times, and query latency.
3. **Add a 15-20% buffer** above measured peak usage.
4. **Consider multiple pools** for distinct workload patterns (e.g., small pool for admin queries, larger for user-facing traffic).

### Key Configuration Parameters

| Parameter | Description | Common Default |
|-----------|-------------|---------------|
| `maximumPoolSize` | Max connections in the pool | 10-30 |
| `minimumIdle` | Min idle connections to maintain | same as maxPoolSize |
| `connectionTimeout` | Max wait time for a connection (ms) | 30000 |
| `idleTimeout` | Max time a connection stays idle (ms) | 600000 (10 min) |
| `maxLifetime` | Max lifetime of a connection in pool (ms) | 1800000 (30 min) |

### Recommended Libraries

| Language | Library | Notes |
|----------|---------|-------|
| Java/Kotlin | **HikariCP** | Industry standard — fastest, lightest |
| Python | **psycopg2.pool / SQLAlchemy pool** | Built-in; tune pool_size and max_overflow |
| Node.js | **pg-pool** | Default pool for node-postgres |
| Go | **pgxpool** (/jackc/pgx) | High-performance Postgres driver |
| Ruby | **connection_pool** | Used by ActiveRecord internally |
| Rust | **deadpool-postgres** | Async pool for tokio-postgres |
| .NET | **Npgsql pooling (built-in)** | Connection pooling enabled by default |

### Proxy-Based Pooling (pgBouncer / PgCat)

For microservices or serverless, use a database proxy instead of app-level pooling:

```ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pool_mode = transaction    # transaction-level pooling
max_client_conn = 100
default_pool_size = 20
```

- **Transaction pooling** — connections are returned to pool after each transaction (most common).
- **Session pooling** — connection held for entire session (useful for prepared statements).
- **Statement pooling** — connection returned after each statement (rarest).

### Serverless Considerations

Serverless functions are short-lived and cannot maintain persistent pools. Use proxy-based solutions:

- **AWS RDS Proxy** (managed, IAM auth)
- **Cloudflare Hyperdrive**
- **Supabase Supavisor**
- **PgCat** (open-source proxy)

---

## 2. Query Optimization — Index Usage, Query Plans, EXPLAIN

### Index Types (PostgreSQL-focused)

| Index Type | Best For | Considerations |
|------------|----------|---------------|
| **B-Tree** (default) | Equality & range queries, ORDER BY, foreign keys | General-purpose; works for most cases |
| **Hash** | Equality lookups only | Single-column; not WAL-logged in older versions |
| **GIN** (Generalized Inverted Index) | Full-text search, arrays, JSONB containment | Larger than B-tree; slower to build |
| **GiST** (Generalized Search Tree) | Geometric data, full-text search (ranking) | Lossy; supports nearest-neighbor |
| **BRIN** (Block Range INdex) | Very large tables with naturally ordered data (time-series, logs) | Extremely compact; only good for correlated data |
| **SP-GiST** | Space-partitioned data (maps, network trees) | Niche; for clustered data |
| **Covering Index** (`INCLUDE` columns) | Index-only scans | Adds payload columns without affecting key sort order |

### Composite Index Guidelines

- **Order matters**: column(s) for equality first, then range/ORDER BY columns.
- **Leftmost prefix rule**: a query must use the leftmost columns in the index to benefit from it.
- Example: `CREATE INDEX idx_users_org_status ON users (organization_id, status, created_at);`
  - Helps `WHERE org_id = ? AND status = ? ORDER BY created_at`
  - Does NOT help `WHERE status = ?` alone.

### EXPLAIN Fundamentals

```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM orders WHERE user_id = 42;
```

**Key plan node types:**

| Node | Meaning |
|------|---------|
| **Seq Scan** | Full table scan — expensive on large tables |
| **Index Scan** | Index lookup + heap fetch |
| **Index Only Scan** | All needed columns in the index itself (fastest) |
| **Bitmap Heap Scan** | Multiple index matches combined into a bitmap |
| **Nested Loop** | For each row in outer, scan inner (good for small joins) |
| **Hash Join** | Build hash table on one side, probe with other |
| **Merge Join** | Sort both sides, merge (good for large sorted sets) |

### What to Look For in a Query Plan

1. **Sequential scans on large tables** — missing index.
2. **High `rows` vs `actual rows` discrepancy** — planner has stale statistics; run `ANALYZE`.
3. **`Sort` nodes with large memory** — consider pre-sorted index or increased `work_mem`.
4. **`Nested Loop` joining large row sets** — might need a different join strategy.
5. **`Bitmap Heap Scan` with many row versions** — vacuum might be needed.
6. **`Filter` after index scan** — index is missing a column used in WHERE.

### Index Maintenance

```sql
-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- unused indexes (candidates for removal)

-- Rebuild bloated indexes
REINDEX INDEX CONCURRENTLY idx_name;   -- non-blocking in PG 12+
```

### Common Anti-Patterns

- Indexing low-cardinality columns (e.g., boolean) alone — not selective enough.
- Over-indexing — each index adds write overhead (INSERT/UPDATE/DELETE slower).
- Missing composite indexes for common query patterns.
- `SELECT *` pulling columns not covered by the index, forcing heap lookups.
- Function calls on indexed columns (`WHERE LOWER(email) = 'x'`) unless using a functional index.

---

## 3. N+1 Detection & Mitigation

### What Is N+1?

The N+1 selects problem occurs when an application issues 1 query to fetch N parent rows, then issues N additional queries to fetch related data for each parent — N+1 total queries instead of a single efficient query.

### Example (ORM-level Pseudocode)

```python
# N+1: 1 query for users + N queries for orders
users = User.query.all()              # 1 query → 100 users
for user in users:
    orders = user.orders               # 100 queries!
    ...
```

```sql
-- Queries generated:
SELECT * FROM users;                                       -- 1
SELECT * FROM orders WHERE user_id = 1;                    -- 2
SELECT * FROM orders WHERE user_id = 2;                    -- ...
SELECT * FROM orders WHERE user_id = 100;                  -- 101
```

### Detection Techniques

1. **ORM query logging** — enable SQL logging and watch for repeated similar queries.
2. **APM tools** — Scout, New Relic, Datadog highlight N+1 patterns automatically.
3. **Manual EXPLAIN** — detect many identical queries in a short window.
4. **Static analysis** — Rails' `bullet` gem, Django's `nplusone`, Java's `jpa-nplusone`.
5. **Database-side analysis** — `pg_stat_statements` showing high call counts.

### Mitigation Strategies

| Strategy | ORM | How |
|----------|-----|-----|
| **Eager loading (JOIN)** | Django `select_related` / Rails `includes` / Hibernate `JOIN FETCH` | Single query with JOIN |
| **Batch loading** | Django `prefetch_related` / Rails `preload` / Hibernate `@BatchSize` | Separate query per table, batched with `WHERE IN` |
| **GraphQL DataLoader** | Any GraphQL stack | Per-request batching & deduplication |
| **Lazy + batch** | Common in ORMs | Delay execution until accessed, then batch |

```python
# Fix with eager loading (Django)
users = User.objects.select_related('profile').prefetch_related('orders').all()

# Fix with DataLoader (GraphQL)
from promise import Promise
from promise.dataloader import DataLoader

class OrderLoader(DataLoader):
    def batch_load_fn(self, user_ids):
        orders = Order.objects.filter(user_id__in=user_ids)
        return Promise.resolve([list(orders.filter(user_id=uid)) for uid in user_ids])
```

### When N+1 Is Acceptable

- Small, fixed N (e.g., < 10 related items).
- Admin panels or reports where latency is not critical.
- Cached results with low cache-miss volume.

---

## 4. Pagination Strategies — Cursor vs Offset vs Keyset

### Offset/Limit (Most Common, Least Scalable)

```sql
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 0;
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 20;
```

**Pros:**
- Simple to implement.
- Supports arbitrary page jumps (page 1, page 5, page 100).
- Intuitive for developers.

**Cons:**
- **Performance degrades with page depth** — OFFSET 100000 must scan/skip 100K rows.
- **Phantom reads / missing rows** — if rows are inserted/deleted between requests, items may appear on multiple pages or be skipped entirely.
- **Inconsistent under write load** — `OFFSET` changes meaning as data shifts.

### Cursor-Based Pagination (Most Scalable, API-First)

```sql
-- First page: no cursor
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;

-- Next page: use the last item's cursor value
SELECT * FROM orders
WHERE created_at < '2026-06-04T12:00:00Z'  -- cursor value
ORDER BY created_at DESC LIMIT 20;
```

```json
// API response shape
{
  "data": [...],
  "pagination": {
    "next_cursor": "eyJpZCI6MTIzNDUsImNyZWF0ZWRfYXQiOiIyMDI2LTA2LTA0VDEyOjAwOjAwWiJ9",
    "has_more": true
  }
}
```

**Pros:**
- **O(1) performance at any depth** — uses index seek, not scan+skip.
- **Consistent** — no phantom reads or missed rows; cursor marks a fixed position.
- **Resilient to write load** — insertion/deletion doesn't shift cursor position.

**Cons:**
- No arbitrary page jumping (only next/prev).
- Requires a unique, sortable column (usually an ID or timestamp).
- Cursor encoding/decoding overhead (base64, opaque tokens).

### Keyset Pagination (Seek Method)

```sql
-- Composite pagination on (created_at, id)
SELECT * FROM orders
WHERE (created_at, id) < ('2026-06-04T12:00:00Z', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;
```

- Uses a composite index on `(created_at, id)`.
- Similar performance to cursor-based — index seek on the tuple.
- Requires a compound comparison and a multi-column index.

### Comparison Table

| Aspect | Offset/Limit | Cursor | Keyset |
|--------|-------------|--------|--------|
| **Performance at depth** | O(n) — degrades | O(1) — constant | O(1) — constant |
| **Random page access** | Yes | No | No |
| **Phantom reads** | Yes | No | No |
| **Consistency** | Unstable | Stable | Stable |
| **Implementation complexity** | Trivial | Medium | Low-Medium |
| **Requires sortable unique column** | No | Yes | Yes |
| **Write-aware** | No | Yes | Yes |

### Recommendation

| Use Case | Strategy |
|----------|----------|
| **Admin panels, small datasets** | Offset/Limit (fine for < 10K rows) |
| **Public APIs, infinite scroll** | Cursor (REST/GraphQL best practice) |
| **Time-series, logs, audit trails** | Cursor or Keyset on timestamp + ID |
| **Internal tools with DB pagination** | Keyset (lowest complexity, no cursor encoding) |

---

## 5. Transaction Boundary Design

### ACID Properties

| Property | Meaning |
|----------|---------|
| **Atomicity** | All or nothing — transaction either completes fully or has no effect |
| **Consistency** | Transaction leaves DB in a valid state (constraints preserved) |
| **Isolation** | Concurrent transactions do not interfere with each other |
| **Durability** | Committed changes persist through failures |

### Isolation Levels

| Level | Dirty Read | Non-Repeatable Read | Phantom Read | Serialization Anomaly |
|-------|-----------|--------------------|-------------|---------------------|
| **Read Uncommitted** | Possible | Possible | Possible | Possible |
| **Read Committed** (default in PostgreSQL, SQL Server, Oracle) | Safe | Possible | Possible | Possible |
| **Repeatable Read** | Safe | Safe | Possible (PG: safe) | Possible |
| **Serializable** | Safe | Safe | Safe | Safe |

**PostgreSQL specifics:**
- Default is **Read Committed**.
- Repeatable Read also prevents phantom reads (uses snapshot isolation).
- Serializable uses Serializable Snapshot Isolation (SSI) — detects serialization conflicts and aborts.

### Choosing an Isolation Level

```sql
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- or for the session:
SET default_transaction_isolation = 'repeatable read';
```

| Level | When to Use |
|-------|------------|
| **Read Committed** | Default for most workloads. Good balance of consistency and performance. |
| **Repeatable Read** | Financial calculations, reporting — when you need consistent snapshots. |
| **Serializable** | Critical data integrity (ledgers, inventory allocation). Higher abort rate. |

### Transaction Retry Patterns

**Optimistic retry (for Serializable / Repeatable Read conflicts):**

```
RETRY_COUNT = 0
MAX_RETRIES = 3
BACKOFF = [50ms, 150ms, 500ms]

WHILE RETRY_COUNT <= MAX_RETRIES:
    BEGIN TRANSACTION
    TRY:
        -- business logic
        COMMIT
        BREAK
    CATCH serialization_failure:
        ROLLBACK
        SLEEP(BACKOFF[RETRY_COUNT])
        RETRY_COUNT += 1
    CATCH deadlock:
        ROLLBACK
        SLEEP(random 0-100ms)
        RETRY_COUNT += 1

IF RETRY_COUNT > MAX_RETRIES:
    RAISE "Transaction failed after retries"
```

**Best practices:**
- Use **exponential backoff** with jitter to avoid thundering herd.
- Keep transactions **short** — minimize lock duration.
- **Read before write** — detect conflicts early inside the transaction.
- Use **optimistic locking** (version column) for entity-level concurrency instead of pessimistic locks when possible.

### Distributed Transactions

| Pattern | Description | When to Use |
|---------|-------------|------------|
| **Two-Phase Commit (2PC)** | Coordinator prepares all participants, then commits | Within a single database system only |
| **Saga (Choreography)** | Each service publishes events; compensating actions roll back | Microservices, async boundaries |
| **Saga (Orchestration)** | Central orchestrator sends commands and handles compensation | Complex multi-service workflows |
| **Outbox Pattern** | Write events to an outbox table in the same DB transaction, then async publish | Event-driven architecture with exactly-once guarantees |
| **Idempotency Keys** | Unique key per operation prevents duplicate processing | Payment handling, any external API call |

### Transaction Anti-Patterns

- **Long-running transactions** that hold locks — split into smaller units.
- **Nested transactions** across service boundaries — use Sagas instead.
- **Transaction inside a loop** — batch the work into a single transaction.
- **Mixing heavy I/O inside a transaction** — external API calls should happen before or after.
- **Not handling retries** for serialization failures — every Serializable workload needs retry logic.

---

## 6. Read/Write Splitting

### Architecture

```
                    ┌─────────────────┐
                    │   Application    │
                    │  (ORM / Client)  │
                    └────┬────────┬───┘
                         │        │
                    Writes     Reads
                         │        │
                    ┌────▼──┐ ┌──▼────┐
                    │Primary│ │Replica│ ──→ (more replicas)
                    │(Write)│ │(Read) │
                    └───────┘ └───────┘
                        │          ↑
                        │  Async   │
                        │  Repl.   │
                        └──────────┘
```

### Implementation Approaches

| Approach | Mechanism | Pros | Cons |
|----------|-----------|------|------|
| **ORM-level** (`read_from=replica`) | Config in ORM (Django `DATABASES`, Rails `config`) | Simple; no infra change | Every service must configure manually |
| **Database Proxy** (ProxySQL, PgBouncer, PgCat) | Route based on query type | Centralized; no app changes | Extra hop; proxy becomes SPOF |
| **Middleware** (e.g., Spring `@Transactional(readOnly=true)`) | Annotation-driven routing | Fine-grained control; declarative | Framework-specific |
| **Client-side** (multi-DB driver config) | Connection string per role | Minimal infra | Deploy-time configuration |

### Query Routing Rules

```
Writes → Primary:
  - INSERT, UPDATE, DELETE, MERGE
  - DDL (CREATE TABLE, ALTER)
  - SELECT ... FOR UPDATE (needs primary)
  - SELECT inside a read-write transaction

Reads → Replica:
  - SELECT (no locking)
  - Read-only transactions (@Transactional(readOnly=true))
  - Reporting queries, analytics
```

### When NOT to Read from Replicas

- **Read-after-write** queries — data may not have replicated yet.
- **Strong consistency** requirements (ledgers, inventory).
- **Tightly coupled** workflows where the next read depends on the previous write.

### Spring Boot Example (ReadWriteSplit Routing)

```java
@Transactional(readOnly = true)
public OrderDTO getOrder(Long id) { ... }  // routed to replica

@Transactional
public OrderDTO createOrder(OrderDTO dto) { ... }  // routed to primary
```

Configure `AbstractRoutingDataSource` with a `@ReadOnlyRepository` annotation or AOP advice to switch between primary and replica `DataSource`.

---

## 7. Replication Lag Handling

### The Problem

Asynchronously replicated databases always have some lag between write on the primary and visibility on replicas. This causes:

- **Read-after-write inconsistency** — user creates a resource, then immediately gets a 404 reading from a stale replica.
- **Monotonic read violation** — user sees a newer version of data, then an older version (from a different replica).
- **Causality violations** — entity A's state depends on entity B, but B's update hasn't arrived yet.

### Handling Strategies

| Strategy | Description | Complexity |
|----------|-------------|------------|
| **Read-your-writes (RYW)** | Route reads for recently-written data to the primary | Low |
| **Monotonic reads** | Route a session's reads to the same replica | Low |
| **Bounded staleness** | Reject reads from replicas lagging beyond a threshold | Medium |
| **Causal consistency (GTID)** | Track which transaction IDs the client has seen; ensure replica applies those before serving reads | Medium |
| **Wait-for-replication** | After write, wait for replica to catch up before serving reads | Medium |
| **Synchronous replication** | Primary waits for N replicas before committing | High (latency cost) |

### Read-Your-Writes (RYW) Pattern

```python
class DatabaseRouter:
    def __init__(self):
        self.recent_writes = {}  # user_id → timestamp

    def execute_write(self, user_id, query, params):
        result = primary.execute(query, params)
        self.recent_writes[user_id] = time.now()
        return result

    def execute_read(self, user_id, query, params):
        last_write = self.recent_writes.get(user_id, 0)
        if time.now() - last_write < 5:  # 5-second window
            return primary.execute(query, params)  # use primary
        else:
            return replica.execute(query, params)  # use replica
```

### Monotonic Read Consistency (Shopify Pattern)

Route all related reads to the **same replica** using a hash-based sticky selection:

```sql
/* consistent_read_id:user_42 */ SELECT * FROM orders WHERE user_id = 42;
```

```
Hash("user_42") % NUM_REPLICAS = replica_index → always hits the same server
```

**Trade-off:** Simple and low-overhead; occasional inconsistency if that replica goes down.

### Wait-for-Replication

```python
def write_and_wait(data):
    primary.execute("INSERT INTO ...", data)
    # Wait for the write to arrive on at least one replica
    primary.execute("SELECT pg_current_wal_lsn()")  # Postgres
    # or use pg_stat_replication

def read_with_consistency(key):
    # Check that replica has caught up to a known LSN
    replica_lsn = replica.execute("SELECT pg_last_wal_replay_lsn()")
    if replica_lsn >= required_lsn:
        return replica.read(key)
    else:
        return primary.read(key)  # fallback to primary
```

### Strategies by Use Case

| Use Case | Recommended Strategy |
|----------|---------------------|
| **User-facing web app after form submit** | Read-your-writes (route to primary for 5-30s) |
| **Social feed, timeline** | Monotonic reads (sessions stick to one replica) |
| **Analytics, reporting** | Bounded staleness acceptable; lag of minutes is fine |
| **Inventory, financial ledger** | Always read from primary (strong consistency) |
| **Notifications** | Accept eventual consistency; timestamp-driven dedup |

---

## 8. Service-Level Testing Overview

```
                     Coverage ▲
                              │
                    ┌─────────┤
                    │  E2E    │   Few, slow, expensive
                ┌───┤  Tests  │
                │   └─────────┤
            ┌───┤            │
            │   │   Service  │   Medium count, medium speed
        ┌───┤   │  (Integ.) │
        │   │   └───────────┤
    ┌───┤   │              │
    │   │   │    Unit      │   Many, fast, cheap
    │   │   │    Tests     │
    └───┴───┴──────────────┘
```

The **Test Pyramid** recommends:
- **Unit tests**: ~70% — fast, deterministic, test business logic in isolation.
- **Integration tests**: ~20% — test boundaries (DB, external APIs).
- **Contract tests**: ~5% — verify API agreements between services.
- **E2E tests**: ~5% — happy-path critical flows.

---

## 9. Unit Testing Business Logic

### Principles

- **Test in isolation** — mock/stub all collaborators (DB, file system, network).
- **Focus on logic** — test business rules, transformations, validations, and state changes.
- **Deterministic** — no flaky tests. No external dependencies.
- **Fast** — individual tests complete in milliseconds.

### What to Unit Test

```python
# GOOD: Pure business logic — test this
class OrderService:
    def calculate_discount(self, order_total, customer_tier):
        if customer_tier == 'vip':
            return order_total * 0.20
        elif order_total > 1000:
            return order_total * 0.10
        else:
            return 0

# BAD: Impure — involves I/O, mock the boundary instead
class OrderController:
    def create_order(self, request):
        order = Order(...)
        db.save(order)          # this is an integration concern
        notification.send(order) # mock this in unit tests
        return order
```

### Repository/Data Layer Abstraction

Use the **Repository Pattern** to make business logic testable:

```java
// Business logic — unit testable with mock repository
public class OrderFulfillmentService {
    private final OrderRepository orderRepo;
    private final InventoryClient inventoryClient;

    public FulfillmentResult fulfillOrder(String orderId) {
        Order order = orderRepo.findById(orderId);
        if (order == null) return FulfillmentResult.notFound();

        boolean inStock = inventoryClient.checkStock(order.getSku(), order.getQuantity());
        if (!inStock) return FulfillmentResult.outOfStock();

        order.setStatus(OrderStatus.FULFILLED);
        orderRepo.save(order);
        return FulfillmentResult.success();
    }
}
// Unit test: Mock orderRepo and inventoryClient, test all branches
```

### Testing Patterns

| Pattern | Description |
|---------|-------------|
| **Given-When-Then** | Arrange → Act → Assert structure |
| **Parameterized tests** | Test many input combinations with one test method |
| **Property-based testing** | Generate random inputs, assert invariants hold |
| **State-based vs Interaction-based** | Prefer state assertions over verifying mock interactions |

---

## 10. Integration Testing — API Contracts, Testcontainers, WireMock

### Testcontainers

**What:** Library that provides lightweight, disposable containers for testing (PostgreSQL, Redis, Kafka, etc.) as JUnit `@Rule` / `@Container`.

**Why real containers instead of in-memory:**

| Approach | Issues |
|----------|--------|
| **H2 (in-memory)** | Different SQL dialect, missing features, different behavior under load |
| **SQLite** | No JSONB, no PostGIS, no full-text search, different type coercion |
| **Testcontainers** | Real PostgreSQL/MySQL — 100% behavior match |

**Example (Java / Spring Boot + Testcontainers):**

```java
@SpringBootTest
@Testcontainers
class UserRepositoryIntegrationTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16")
        .withDatabaseName("testdb")
        .withUsername("test")
        .withPassword("test");

    @DynamicPropertySource
    static void configureProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Autowired
    private UserRepository userRepository;

    @Test
    void shouldPersistAndRetrieveUser() {
        User user = new User("alice@example.com", "Alice");
        User saved = userRepository.save(user);

        Optional<User> found = userRepository.findByEmail("alice@example.com");
        assertThat(found).isPresent();
        assertThat(found.get().getName()).isEqualTo("Alice");
    }
}
```

**Testcontainers in other languages:**

| Language | Library |
|----------|---------|
| Python | `testcontainers` (pip) |
| Node.js | `testcontainers` (npm) |
| Go | `testcontainers-go` |
| .NET | `Testcontainers for .NET` |
| Rust | `testcontainers` (crate) |

### WireMock

**What:** HTTP-based API mock server. Stub external HTTP services during integration tests.

```java
@SpringBootTest
@WireMockTest(httpPort = 8089)
class PaymentServiceIntegrationTest {

    @Test
    void shouldProcessPaymentWhenGatewayRespondsSuccess() {
        // Arrange: stub the external payment gateway
        stubFor(post(urlEqualTo("/gateway/charge"))
            .willReturn(aResponse()
                .withStatus(200)
                .withHeader("Content-Type", "application/json")
                .withBody("""
                    { "status": "success", "transaction_id": "txn_123" }
                """)));

        // Act
        PaymentResult result = paymentService.charge(new Payment("user_1", 50.00));

        // Assert
        assertThat(result.isSuccess()).isTrue();
        assertThat(result.getTransactionId()).isEqualTo("txn_123");
    }

    @Test
    void shouldHandleGatewayTimeoutGracefully() {
        stubFor(post(urlEqualTo("/gateway/charge"))
            .willReturn(aResponse()
                .withStatus(504)));

        assertThrows(PaymentGatewayTimeoutException.class, () -> {
            paymentService.charge(new Payment("user_1", 50.00));
        });
    }
}
```

**WireMock capabilities:**
- Stub based on URL, HTTP method, headers, body.
- Simulate delays, timeouts, and network failures.
- Record/playback (proxying real APIs during development).
- Verify requests were made (assert on expected interactions).
- Fault injection (malformed responses, connection resets).

### Integration Test Best Practices

1. **Test the boundary** — Repository tests with Testcontainers, external API tests with WireMock.
2. **Keep tests independent** — each test gets its own transaction or container state.
3. **Clean up between tests** — truncate tables or use transactional rollback.
4. **Use realistic data** — edge cases that trigger unique constraints, nulls, long strings.
5. **Don't test the framework** — you don't need to test that Hibernate/JPA/ActiveRecord works.
6. **Name tests by behavior** — `shouldRejectOrderWhenInventoryExhausted()`, never `testOrder1()`.

---

## 11. Contract Testing — Pact

### What Is Contract Testing?

Contract testing verifies that two services (consumer and provider) can communicate correctly by testing each side independently against a shared contract — without deploying both services.

### Pact Workflow

```
1. Consumer writes expectations (Pact file)
   ┌──────────┐                 ┌──────────┐
   │ Consumer │ ── generates ──→│ Pact     │
   │  Tests   │                 │ File     │
   └──────────┘                 └────┬─────┘
                                     │
2. Provider verifies against Pact   │
   ┌──────────┐                      │
   │ Provider │ ←── verifies ────────│
   │  Tests   │                      │
   └──────────┘                      │
                                     │
3. Pact Broker stores & diff         │
   ┌──────────────┐                  │
   │ Pact Broker  │ ←── stores ──────│
   │ (versioned)  │                  │
   └──────┬───────┘                  │
          │                          │
4. Can-I-Deploy checks versions      │
   ┌──────────┐                      │
   │ CI/CD    │ ←── compatibility ───│
   └──────────┘                      │
```

### Consumer-Side Test (Pact)

```java
@ExtendWith(PactConsumerTestExt.class)
@PactTestFor(providerName = "PaymentProvider", port = "8080")
class OrderServiceConsumerPactTest {

    @Pact(consumer = "OrderService")
    public V4Pact createPact(PactDslWithProvider builder) {
        return builder
            .given("a payment method exists with ID 'pm_1'")
            .uponReceiving("a request to charge a payment")
                .path("/gateway/charge")
                .method("POST")
                .headers("Content-Type", "application/json")
                .body(new PactDslJsonBody()
                    .stringType("payment_method_id", "pm_1")
                    .decimalType("amount", 49.99)
                )
            .willRespondWith()
                .status(200)
                .headers("Content-Type", "application/json")
                .body(new PactDslJsonBody()
                    .stringType("status", "success")
                    .stringType("transaction_id", "txn_abc123")
                )
            .toPact();
    }

    @Test
    @PactTestFor(pactMethod = "createPact")
    void shouldChargePaymentSuccessfully(MockServer mockServer) {
        PaymentClient client = new PaymentClient(mockServer.getUrl());
        PaymentResponse response = client.charge("pm_1", 49.99);
        assertThat(response.getStatus()).isEqualTo("success");
    }
}
```

### Provider-Side Verification

```java
@Provider("PaymentProvider")
@PactBroker(url = "${pactbroker.url}")
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
class PaymentProviderPactVerificationTest {

    @LocalServerPort
    int port;

    @BeforeEach
    void setup(PactVerificationContext context) {
        context.setTarget(new HttpTestTarget("localhost", port));
    }

    @TestTemplate
    @ExtendWith(PactVerificationInvocationContextProvider.class)
    void pactVerificationTestTemplate(PactVerificationContext context) {
        context.verifyInteraction();
    }

    @State("a payment method exists with ID 'pm_1'")
    void setupPaymentMethod() {
        // Set up test data — this runs before the provider is called
        paymentMethodRepository.save(new PaymentMethod("pm_1", ...));
    }
}
```

### Pact Best Practices

- **Version both consumer and provider** — Pact Broker tracks compatibility matrix.
- **Use `can-i-deploy`** — the `pact-broker can-i-deploy` command checks if two versions are compatible before deploying.
- **Don't over-specify** — use matchers (`stringType`, `decimalType`) instead of exact values for most fields. Exact values should only be for fields where the value matters (e.g., status enums).
- **Tag pacts by environment** — tag pact versions with "prod", "staging" to gate deployments.
- **Run provider verification in CI** — not just locally. Break the build if a provider change breaks a consumer contract.

---

## 12. Test Fixtures

### What Are Test Fixtures?

Test fixtures are predefined data setups that provide a known baseline state before tests run. They reduce duplication and make tests readable.

### Fixture Strategies

| Strategy | Description | Best For |
|----------|-------------|----------|
| **Inline (test-local)** | Create data directly in the test method | Simple, focused tests |
| **Factory methods** | Helper functions that create objects with sensible defaults | Most cases — flexible, composable |
| **Factory Boy / build()** | Use a library to generate test objects | Complex object graphs |
| **Seed SQL files** | Pre-populated SQL inserts loaded before test suite | Integration + E2E tests |
| **JSON/YAML snapshots** | Load test data from fixture files | When data is complex and nested |

### Example: Factory Pattern (Python)

```python
# factories.py
class UserFactory:
    @staticmethod
    def create(
        email="test@example.com",
        name="Test User",
        tier="standard",
        balance=Decimal("100.00")
    ):
        return User(
            email=email,
            name=name,
            tier=tier,
            balance=balance
        )

# test_discount.py
def test_vip_discount():
    vip = UserFactory.create(tier="vip", balance=Decimal("500.00"))
    result = discount_service.calculate(vip, 200)
    assert result == Decimal("40.00")  # 20% VIP discount
```

### Factory Boy (Python) / Builders (Java)

```python
import factory

class OrderFactory(factory.Factory):
    class Meta:
        model = Order

    id = factory.Sequence(lambda n: n)
    user = factory.SubFactory(UserFactory)
    total = Decimal("100.00")
    status = OrderStatus.PENDING
    created_at = factory.Faker("date_time_this_year")

# Usage — only override what matters
order = OrderFactory.create(status=OrderStatus.FULFILLED)
assert order.user.email == "test@example.com"  # default from UserFactory
```

### Fixture Anti-Patterns

- **Shared mutable fixtures** — tests that mutate shared state cause flaky ordering dependencies.
- **Too much data** — loading 1000 rows for every test is slow; use the minimum needed.
- **Copy-paste fixtures** — leads to drift; use factories with default values.
- **Magic numbers** — use named constants: `UNIT_PRICE = Decimal("10.00")` instead of bare `10.00`.

---

## 13. CI Integration

### Test Execution in CI

```
┌─────────────┐     ┌─────────────┐     ┌─────────────┐     ┌──────────────┐
│   Lint &    │     │   Unit      │     │ Integration │     │   Contract   │
│   Static    │ ──→ │   Tests     │ ──→ │   Tests     │ ──→ │   Tests /    │
│   Analysis  │     │ (fast, par) │     │ (slower)    │     │   E2E Tests  │
└─────────────┘     └─────────────┘     └─────────────┘     └──────────────┘
     < 2 min           < 5 min            < 15 min            < 30 min
```

### Parallelization

- **Unit tests** — run in parallel across CPU cores (pytest-xdist, JUnit parallel).
- **Integration tests** — parallel by service/module; isolate with Testcontainers per test class.
- **Contract tests** — consumer tests in parallel; provider tests sequentially per pact file.

### CI Pipeline Example (GitHub Actions)

```yaml
name: CI
on: [push, pull_request]

jobs:
  unit-tests:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-java@v4
        with: { java-version: '21', distribution: 'temurin' }
      - run: ./gradlew test --parallel     # unit tests only

  integration-tests:
    needs: unit-tests
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_PASSWORD: test
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:
      - uses: actions/checkout@v4
      - run: ./gradlew integrationTest --tests *IntegrationTest
    # Alternatively, use Testcontainers which starts containers in-test

  contract-tests:
    needs: unit-tests
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: ./gradlew pactVerify          # provider-side verification
      - run: ./gradlew pactPublish         # publish to Pact Broker

  pact-can-i-deploy:
    needs: contract-tests
    runs-on: ubuntu-latest
    steps:
      - run: pact-broker can-i-deploy
          --pacticipant OrderService
          --version $(cat version.txt)
          --to-environment production

  e2e-tests:
    needs: [integration-tests, pact-can-i-deploy]
    runs-on: ubuntu-latest
    steps:
      - run: docker compose -f docker-compose.e2e.yml up --abort-on-container-exit
```

### CI Best Practices

| Practice | Rationale |
|----------|-----------|
| **Fail fast** | Run fastest tests first (unit → integration → E2E). |
| **Cache dependencies** | Maven/Gradle/npm/pip caches speed up repeat builds. |
| **Cache Docker layers** | Testcontainers pulls — pre-warm image caches. |
| **Isolate flaky tests** | Quarantine flaky tests; don't let them block the pipeline. |
| **Test against production-like databases** | Use Testcontainers with the same DB version as production. |
| **Run pact verification as a required check** | Never deploy a provider that breaks a consumer contract. |
| **Use test reports as artifacts** | Publish JUnit XML / HTML reports for debugging. |

### Test Run Optimization

- **Selective test execution** — only run tests for changed modules (gradle `--changed-latest`, `pytest --last-failed`).
- **Test splitting** — split integration tests across multiple CI runners (`--shard` flags).
- **Docker layer reuse** — Dockerfile changes cause full rebuilds; keep rarely-changed layers early.
- **Database migrations in CI** — run migrations once, snapshot the DB, restore for each test runner.

---

## References & Further Reading

- **PostgreSQL Documentation** — [EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html), [Transaction Isolation](https://www.postgresql.org/docs/current/transaction-iso.html)
- **HikariCP** — [GitHub](https://github.com/brettwooldridge/HikariCP) (connection pool sizing)
- **PgBouncer** — [Official docs](https://www.pgbouncer.org/) (transaction pooling)
- **Pact** — [Documentation](https://docs.pact.io/) (contract testing)
- **Testcontainers** — [Official site](https://testcontainers.com/) (integration testing)
- **WireMock** — [Official site](https://wiremock.org/) (API mocking)
- **Shopify Engineering** — [Read Consistency with Database Replicas](https://shopify.engineering/read-consistency-database-replicas)
- **Crunchy Data** — [Postgres Indexes for Newbies](https://www.crunchydata.com/blog/postgres-indexes-for-newbies)
- **Scout APM** — [Understanding N+1 Database Queries](https://www.scoutapm.com/blog/understanding-n1-database-queries)
- **AWS** — [RDS Proxy](https://aws.amazon.com/rds/proxy/) (serverless connection pooling)

# Error Handling

## Error Classification

| Category | HTTP analogue | What it means | Example |
|----------|--------------|---------------|---------|
| Validation | 400 | The client sent something wrong | Missing required field |
| AuthN/AuthZ | 401/403 | The caller can't do this | Expired token, insufficient permissions |
| Not Found | 404 | The resource doesn't exist | Invalid ID, deleted entity |
| Conflict | 409 | The operation can't complete due to state | Duplicate, stale version |
| Rate Limited | 429 | Too many requests | Quota exceeded |
| Internal | 500 | Something went wrong on the server | DB down, unhandled exception |
| Unavailable | 503 | The service can't handle the request right now | Circuit open, overloaded |

## Exception Handling Strategy

| Catch location | What to do | Example |
|---------------|------------|---------|
| Repository | Wrap DB errors in domain exceptions | `UserNotFoundException`, `DuplicateEmailError` |
| Service | Handle domain exceptions, orchestrate recovery | Retry on conflict, fallback on unavailable |
| Controller boundary | Map domain exceptions to error responses | `UserNotFoundException` → 404 with error body |
| Middleware boundary | Catch unhandled exceptions, log, return 500 | Global error handler, structured log + trace |

## Structured Logging Fields

Every log entry at service level should include:

- `request_id` — correlation ID from request header or generated at middleware
- `service` — service name
- `operation` — what operation was being performed
- `duration_ms` — how long it took
- `error_code` — if error, machine-readable code
- `caller` — function/module that produced the log

## Error Response Body

```json
{
  "error": {
    "code": "RATE_LIMITED",
    "message": "Too many requests. Please retry after the specified time.",
    "retry_after_seconds": 30,
    "request_id": "req_abc123"
  }
}
```

# Event-Driven Service Implementation

Load this reference when a service publishes domain facts, consumes messages, or
coordinates durable state with asynchronous work. It covers implementation seams;
use `api-design-and-evolution` for the event contract and delivery agreement.

## Boundaries And Flow

Keep three responsibilities visible, even when the project uses different names:

| Boundary | Owns | Must not know |
|---|---|---|
| Domain | Invariants, state transitions, and meaningful past-tense business facts | ORM types, broker clients, HTTP, serialization, retry policy |
| Application | Use-case coordination, unit-of-work scope, ports, and event dispatch intent | Vendor-specific connection or broker details |
| Infrastructure | Database mappings, transaction implementation, outbox relay, broker adapter, inbox store | Business decisions and domain invariants |

The normal command path is:

1. Translate the request into an application command at the transport boundary.
2. Open one unit of work, load the aggregate through a domain-facing repository, and
   invoke behavior that protects its invariants.
3. Collect domain events produced by the successful state transition. Events are facts,
   not instructions to make setters run; do not emit one for every field change.
4. Persist aggregate changes and an outbox row in the same local transaction. The row
   contains a stable event identity, type, schema version, aggregate identity/version,
   occurred-at value, payload, and trace/correlation context appropriate for the
   service's data policy.
5. Commit before performing broker I/O. A relay publishes committed rows and records a
   delivery result or retry state. It must tolerate a crash after publish and before
   marking the row complete, so consumers still need deduplication.

The unit of work is a logical application operation, normally one aggregate's
immediate invariants plus its outbox records. A broader transaction needs a concrete
invariant justification. Do not turn it into a distributed transaction by calling an
external service while the local transaction is open.

## Inbox And Idempotent Handlers

Assume at-least-once delivery unless the contract proves a narrower guarantee. An
inbox record or equivalent deduplication mechanism should be keyed by the consumer's
identity and the producer event identity, not merely by a business field that may be
reused. This reference uses a single atomic transaction design: the inbox insert,
handler effects, and completed state commit together. It does not use leases or
separate claim/completion transactions.

1. Validate the envelope and schema version before domain mapping. Treat event input
   as untrusted: verify producer authenticity and authorization before trusting the
   producer identity or event identity. Route the protocol, key, credential, and
   authorization design to `secure-software-engineering`.
2. Parse with a safe, data-only deserializer; never use pickle, eval, unsafe YAML, or
   gadget-prone formats. Treat every deserialized field as untrusted at downstream
   boundaries and use parameterized queries plus safe command, template, and path
   handling. The quarantine path must use bounded, access-controlled storage for
   opaque or encrypted raw bytes and sanitized metadata, not blindly persist and
   reparse executable or injection-bearing content. Route detailed injection and
   deserialization controls to `secure-software-engineering`.
3. Insert the inbox identity with a uniqueness constraint in the same transaction as
   the handler effects.
4. If the identity already completed, acknowledge without repeating side effects or
   claiming to return an outcome that was not persisted. A competing transaction
   observes the unique-conflict result after the owner commits; it does not wait on a
   lease or stale claim.
5. Apply a handler whose state transition is safe to repeat. Use a natural idempotency
   key or a version/precondition check for effects outside the local store.
6. Persist the handler's changes and completed-inbox state atomically, then acknowledge.

Do not call a handler "idempotent" because the final row looks unchanged. Check
emails, payments, downstream commands, counters, notifications, and external writes
for duplicate effects. Where an external effect cannot be made idempotent, persist a
durable intent and reconcile its status rather than guessing after a timeout.

## Retry, Replay, And Failure

Classify failures before choosing the response:

| Failure | Default action | Evidence or stop condition |
|---|---|---|
| Temporary broker/database/network issue | Bounded retry with backoff and jitter | Attempt count, age, and queue lag remain within budget |
| Concurrency or serialization conflict | Roll back the unit of work and retry the whole operation | Finite attempts; preserve the original correlation context |
| Invalid or unauthorized event | Do not retry; quarantine and alert the owner | Payload, producer, and reason are available without leaking secrets |
| Poison message or deterministic handler bug | Move to a dead-letter/quarantine path after policy limits | Repair or replacement is tested before replay |
| Unknown schema version or gap | Pause or route to compatibility handling | Contract owner resolves version/ordering decision |
| Publish succeeded but acknowledgement was lost | Republish safely; consumer deduplication resolves the duplicate | Stable event identity and duplicate metrics |

Replay is a controlled operation, not a blind retry loop. Record the source position
or event IDs, selection criteria, handler version, destination, operator, and expected
side effects. Use the same consumer identity and inbox semantics by default. A fresh
consumer scope deliberately defeats inbox deduplication and must be restricted to
handlers proven side-effect-safe or routed through a repair/compensation path; it is
not safe for payments, refunds, or other irreversible effects. Make the stop condition
explicit. If handlers are not deterministic across versions,
use a versioned translator or a new repair command instead of replaying old payloads
through changed rules.

## Observability And Tests

Instrument the lifecycle with stable dimensions such as service, event type, producer,
consumer, schema version, result class, and deployment version. Useful signals include
outbox age and backlog, publish attempts and failures, inbox duplicate rate, handler
latency, retry counts, dead-letter volume, replay volume, and consumer lag. Propagate
trace/correlation identifiers, but redact payloads, credentials, and sensitive fields.

Test the smallest useful unit at each boundary:

- Unit-test aggregate invariants and the exact domain facts emitted for successful
  transitions; no broker or database is needed.
- Integration-test that state and outbox rows commit together, and that a rollback
  leaves neither durable effect.
- Test relay crashes and duplicate publication, then verify consumer deduplication.
- Test handler retry, permanent failure, quarantine, schema mismatch, replay, and
  acknowledgement ordering against a real or production-compatible broker/store.
- Assert observability fields and alert inputs for lag, age, duplicate, and poison paths.

## Ownership Boundary

`programming-principles` owns bounded contexts, aggregates, domain language, domain
events as DDD concepts, repositories, and anti-corruption guidance. This reference
only explains how those decisions become application and infrastructure behavior.
`api-design-and-evolution` owns event names, envelopes, compatibility, ordering scope,
retention, and consumer-facing delivery semantics. `site-reliability-engineering`
owns SLOs and paging policy; `secure-software-engineering` owns threat modeling,
authorization, and secure logging.

# Integration Patterns

## External Call Resilience

| Pattern | What it protects against | Implementation |
|---------|------------------------|----------------|
| Retry with backoff | Transient failures | Exponential backoff + jitter, max retries |
| Circuit breaker | Sustained failures | Open after N failures, half-open after timeout |
| Timeout | Hanging connections | Connect + read + write timeouts per operation |
| Bulkhead | Cascading failures | Separate thread pool / connection pool per dependency |
| Idempotency key | Duplicate requests | Client-generated key, server deduplicates |

## Idempotency Key Pattern

```http
POST /api/payments
Idempotency-Key: 7c3d5e8f-a1b2-4c3d-8e5f-6a7b8c9d0e1f
```

| Aspect | Design |
|--------|--------|
| Key generation | Client UUID v4 |
| Storage | Key-value store with TTL (24h) |
| First request | Process normally, store result keyed by idempotency key |
| Duplicate request | Return stored result, no side effects |
| Expired key | Process as new request |
| In-flight request | Return 409 Conflict |

## Webhook Verification

| Mechanism | What it verifies | Implementation |
|-----------|-----------------|----------------|
| HMAC signature | Payload integrity, sender authenticity | Shared secret → HMAC of body → compare header |
| Webhook secret | Sender identity | Pre-shared secret, rotated periodically |
| Timestamp freshness | Replay prevention | Reject webhooks older than N minutes |

## Message Queue Consumer Patterns

| Pattern | When to use |
|---------|-------------|
| At-least-once delivery | Default — requires idempotent processing |
| Exactly-once (dedup) | When duplicates are unacceptable — requires dedup store |
| Batch processing | High throughput, latency-tolerant |
| Dead letter queue | Messages that can't be processed after max retries |

Every consumer should: acknowledge after processing, retry on transient failure, DLQ on permanent failure, and log at every stage.

# Migration Coexistence Patterns

Load this reference when an implementation must run beside an older path during an
authorized service or interface transition. It does not decide whether to decompose
the system or own the cross-system migration lifecycle. Use `migration-engineering`
for classification, compatibility windows, reconciliation, cutover, recovery,
deprecation, and cleanup; use `software-architecture` for the target boundary.

## Make The Seam Explicit

Before changing behavior, record the capability, current path, candidate path,
callers, data touched, invariant, authority, and interruption point. Choose a seam
that can be observed and selected without making both implementations the permanent
source of truth.

| Pattern | Implementation responsibility | Evidence to collect |
|---|---|---|
| Adapter | Translate the old request/response shape into the new port or the reverse | Translation rules, lost information, error mapping, and contract tests |
| Strangler handoff | Route a bounded operation or cohort to the new path while the old path remains selectable | Traffic, outcome, latency, and failure comparison by route/cohort |
| Anti-corruption boundary | Keep legacy or foreign vocabulary out of the local domain/application model | Explicit mapping, ownership of translation, and tests for unknown/obsolete states |
| Dual path | Execute or read through both paths only when side effects can be isolated or deduplicated | Authority, comparison tolerance, mismatch categories, and cost guardrail |

These patterns are different. An adapter translates; strangler routing selects; an
anti-corruption boundary protects a model; a dual path creates comparison evidence.
Combine them only when each role and failure behavior is named.

## Authority And Handoff

For every operation and important field, state one authority during each coexistence
phase. A read switch is not an ownership transfer. The handoff record should answer:

- Which path accepts writes, and how are old writes detected or rejected?
- Which store is authoritative, and are other values derived, cached, or shadow-only?
- Can the invariant be maintained without a distributed transaction?
- Are duplicate, out-of-order, missing, and deleted records represented?
- What does the non-authoritative path do on disagreement: compare, alert, serve, or
  remain unused?
- What customer-visible staleness, ordering, latency, or error change is accepted?
- Who can change the selector, and what evidence permits the next increment?

Prefer one writer. If a second representation must be kept current, use an explicit
outbox/CDC or other owned synchronization mechanism and reconcile it. Avoid a
permanent synchronous dual-write in request code when neither side is clearly
authoritative; it creates two failure-prone commits without removing the ownership
ambiguity.

## Safe Handoff Sequence

1. Add the port, adapter, selector, and characterization/contract tests while the old
   path remains the default.
2. Establish the new path's input/output contract and translation boundary. Do not
   hide a breaking consumer contract in an adapter; route that decision to
   `api-design-and-evolution`.
3. Populate or synchronize data using the migration owner's chosen backfill/CDC
   method. Track lag, duplicates, deletes, and reconciliation failures.
4. Compare shadow or parallel outcomes while the old path remains authoritative. Do
   not duplicate irreversible side effects merely to obtain a comparison.
5. Shift a bounded cohort or operation. Define a precondition, abort signal,
   customer-impact check, and recovery classification for the increment. Route
   rollout and flag mechanics to `release-engineering`.
6. Transfer authority explicitly: update the source-of-truth record, enable the new
   path, and verify that it accepts writes authoritatively before disabling old
   writers. Confirm old-path accesses are observable and denied or harmless in the
   same cutover step; abort and restore the prior selector if the new write path is
   not proven authoritative.
7. Remove adapters, selectors, old reads/writes, synchronization feeds, flags, and
   credentials only when their individual removal conditions pass. Keep a tested
   forward repair or restore path where rollback is no longer possible.

## Removal Conditions

Removal is justified by evidence, not elapsed time alone. Record conditions such as:

- no registered or observed callers use the old route for a defined observation window;
- new and old outcomes reconcile within an agreed tolerance across the required
  population and time range;
- the new path owns all writes and old writes are blocked or detected;
- queued, delayed, and replayed work is handled by the new consumer;
- dashboards, alerts, runbooks, support procedures, and security controls use the new
  path;
- the old data, contract, adapter, flag, and credentials have an owner-approved
  recovery classification before removal.

If any condition is unknown, keep the old path selectable and stop the handoff rather
than calling the coexistence complete. Once the old store or contract is removed,
do not promise rollback by default; classify roll-forward, restore, or irreversible
recovery with `migration-engineering` and `release-engineering`.

## Failure Cases To Exercise

- Selector points to a path whose dependency is unavailable.
- Adapter receives an unknown legacy enum or a response with missing fields.
- Both paths observe the same command and an external side effect is attempted twice.
- Old and new reads disagree because of lag or a mapping defect.
- A late old write arrives after authority transfer.
- A replayed event reaches both old and new consumers.
- Removal hides a caller that was not in the inventory.

For each case, specify whether to reject, queue, serve the authoritative result, alert,
or halt promotion. Include correlation IDs and path/version labels in the evidence,
without logging sensitive payloads.

# Service Patterns

## Architecture Styles

| Style | Separation axis | Best for | Tradeoff |
|-------|----------------|----------|----------|
| Layered | Technical layer (controller → service → repository) | Simple CRUD services, convention-based frameworks | Business logic leaks across layers |
| Clean Architecture | Dependency direction (outer → inner) | Complex business logic, long-lived projects | Boilerplate for interfaces |
| Hexagonal (Ports & Adapters) | External vs internal (ports as boundaries) | Services with multiple I/O sources | More interfaces upfront |
| Pipeline | Request flow through stages | Data processing, middleware-heavy services | Composable but hard to trace |

## Request Lifecycle

```
Request → Middleware 1 → Middleware N → Router → Controller → Service → Repository → Database
                                             ↓
                                        Response ← Middleware N ← Middleware 1 ←
```

Each layer has a distinct responsibility:

| Layer | Responsibility | Doesn't do |
|-------|---------------|------------|
| Middleware | Auth, logging, rate limiting, CORS, tracing | Business logic, data access |
| Controller | Request parsing, validation, response formatting | Business decisions, database queries |
| Service | Business rules, workflow orchestration, state mgmt | HTTP concerns, direct database access |
| Repository | Data access, query construction, result mapping | Business rules, request parsing |

## Domain, Application, And Infrastructure

Use the boundary that makes policy independent from delivery and storage details:

| Boundary | Responsibility | Dependency rule |
|---|---|---|
| Domain | Invariants, state transitions, value semantics, and meaningful business facts | No framework, database, broker, or vendor imports |
| Application | Use-case coordination, ports, unit-of-work scope, authorization handoff, and transaction intent | Depends on domain and interfaces it owns; does not construct infrastructure |
| Infrastructure | ORM/data mapping, transaction implementation, broker relay, HTTP clients, and framework wiring | Implements application ports; does not decide domain policy |

Organize by business capability or use case within the bounded context before falling
back to technical layers. Keep one logical commit boundary visible in the application
service. For state changes that publish facts, use the focused
[`event-driven-service-implementation.md`](event-driven-service-implementation.md)
reference for outbox/inbox coordination, replay, and failure behavior. The existing
DDD catalog in [`programming-principles`](../../programming-principles/SKILL.md)
owns bounded-context, aggregate, repository, and domain-modeling decisions; do not
duplicate it here.

## Background Job Processing

| Pattern | When to use | Concerns |
|---------|-------------|----------|
| In-process worker | Lightweight, no external deps | Memory, process lifecycle, scaling |
| Message queue | Reliable async processing | Queue management, retry, DLQ |
| Scheduled cron | Periodic batch work | Timing guarantees, overlap |
| Event-driven streaming | Real-time event processing | State management, ordering |

Every background job should be: idempotent, retryable, and have a defined failure path (dead letter or alert).

# Source Index

This skill is an original, task-centered synthesis. Sources inform concepts and
terminology; the repository does not reproduce protected prose, tables, diagrams,
examples, taxonomies, or chapter structures.

| Source | Use in this skill | URL |
|---|---|---|
| Agent Skills specification | Progressive disclosure, relative references, and skill structure | https://agentskills.io/specification |
| CloudEvents | Event identity and envelope boundary considerations | https://cloudevents.io/ |
| Martin Fowler, Event Sourcing | Distinguishing event history from ordinary event publication | https://martinfowler.com/eaaDev/EventSourcing.html |
| Martin Fowler, Strangler Fig Application | Incremental coexistence vocabulary | https://martinfowler.com/bliki/StranglerFigApplication.html |
| Martin Fowler, Parallel Change | Compatibility-first change sequencing | https://martinfowler.com/bliki/ParallelChange.html |
| PostgreSQL documentation | Transaction isolation and failure behavior | https://www.postgresql.org/docs/current/transaction-iso.html |
| OpenTelemetry documentation | Trace and metric instrumentation concepts | https://opentelemetry.io/docs/ |

## Ownership Notes

- DDD concepts and anti-corruption boundaries route to `programming-principles`.
- Event contract and delivery semantics route to `api-design-and-evolution`.
- Cross-system transition and recovery route to `migration-engineering`.
- Schema and pipeline operations route to `data-engineering`.
- Security controls and sensitive logging route to `secure-software-engineering`.
- Release promotion and rollback mechanics route to `release-engineering`.

The private architecture comparison report supplied for this task was used only as
safe synthesized research input. Purchased ebooks were not read or quoted. Future
edits should be rewritten from user tasks and public sources if wording or structure
resembles a source too closely.

#!/usr/bin/env python3
"""N+1 query spotter for backend-engineering.

Scans Python source files for the classic N+1 query pattern: a database query
or ORM fetch invoked inside a loop body. When a loop runs N iterations and
each iteration issues its own query, the code makes N+1 round trips instead
of one batched query — the fix is a WHERE IN batch, a join, or eager loading.

Detection is static and heuristic: any query-like call that appears inside a
for or while loop is flagged. A call that also references the loop variable
in one of its arguments is flagged with higher confidence, because the query
is almost certainly varying per iteration.

Input: one or more file paths. With no paths, Python source is read from
stdin. Output is one line per finding, or a JSON report with --json.

Exit codes:
  0  no potential N+1 patterns found
  1  one or more potential N+1 patterns found
  2  usage or I/O error (missing file, unparseable source, bad flags)
"""

import argparse
import ast
import json
import sys
from pathlib import Path

# Attribute method names (obj.NAME(...)) treated as database query / ORM fetch calls.
QUERY_METHODS = frozenset(
    {
        "query",
        "execute",
        "fetchall",
        "fetchone",
        "fetchmany",
        "fetch",
        "first",
        "one",
        "all",
        "get",
        "filter",
        "select",
        "find",
        "save",
        "create",
        "update",
        "delete",
        "insert",
        "commit",
        "persist",
        "bulk_create",
        "bulk_update",
    }
)

# Bare function names (NAME(...)) treated as query entry points.
QUERY_NAMES = frozenset({"query", "execute", "run", "fetch", "find", "select"})

_STDIN_LABEL = "<stdin>"


class N1Finding:
    """One suspected N+1 pattern: a query-like call inside a loop."""

    def __init__(self, line, column, call_text, loop_line, loop_targets, high_confidence):
        self.line = line
        self.column = column
        self.call_text = call_text
        self.loop_line = loop_line
        self.loop_targets = sorted(loop_targets)
        self.high_confidence = high_confidence

    def to_dict(self):
        return {
            "line": self.line,
            "column": self.column,
            "call": self.call_text,
            "loop_line": self.loop_line,
            "loop_targets": self.loop_targets,
            "confidence": "high" if self.high_confidence else "possible",
        }

    def render(self, source_path):
        confidence = "high confidence" if self.high_confidence else "possible"
        target_note = f", loop target {', '.join(self.loop_targets)}" if self.loop_targets else ""
        return (
            f"{source_path}:{self.line}:{self.column}: potential N+1: "
            f"{self.call_text!r} inside loop at line {self.loop_line}{target_note} ({confidence})"
        )


class N1Scanner(ast.NodeVisitor):
    """Walks one module, flagging query-like calls that sit inside a loop."""

    def __init__(self, extra_methods=(), extra_names=()):
        self.methods = QUERY_METHODS | set(extra_methods)
        self.names = QUERY_NAMES | set(extra_names)
        self.loop_stack = []  # (loop_node, loop_target_names)
        self.findings = []

    # -- loop tracking -----------------------------------------------------

    def _target_names(self, target):
        if isinstance(target, ast.Name):
            return {target.id}
        if isinstance(target, ast.Tuple):
            return {elt.id for elt in target.elts if isinstance(elt, ast.Name)}
        return set()

    def visit_For(self, node):
        self.loop_stack.append((node, self._target_names(node.target)))
        self.generic_visit(node)
        self.loop_stack.pop()

    def visit_While(self, node):
        self.loop_stack.append((node, set()))
        self.generic_visit(node)
        self.loop_stack.pop()

    # -- call inspection ---------------------------------------------------

    def _call_name(self, node):
        """Return (name, kind) for a call's callee, or None if not matched."""
        func = node.func
        if isinstance(func, ast.Attribute):
            return func.attr, "attribute"
        if isinstance(func, ast.Name):
            return func.id, "name"
        return None

    @staticmethod
    def _names_in_node(node):
        """All identifier names reachable inside an AST node."""
        return {name.id for name in ast.walk(node) if isinstance(name, ast.Name)}

    @classmethod
    def _references_loop_var(cls, node, names):
        """True if any argument of the call references a loop variable.

        Attribute access (user.id) and nested expressions count as references,
        since a per-iteration query keyed off the loop item is the N+1 signature.
        """
        for arg in node.args:
            if cls._names_in_node(arg) & names:
                return True
        for kw in node.keywords:
            if kw.arg in names:
                return True
            if kw.value is not None and cls._names_in_node(kw.value) & names:
                return True
        return False

    def visit_Call(self, node):
        if self.loop_stack:
            matched = self._call_name(node)
            if matched:
                call_name, kind = matched
                if (kind == "attribute" and call_name in self.methods) or (
                    kind == "name" and call_name in self.names
                ):
                    loop_node, targets = self.loop_stack[-1]
                    high = bool(targets) and self._references_loop_var(node, targets)
                    self.findings.append(
                        N1Finding(
                            line=node.lineno,
                            column=getattr(node, "col_offset", 0),
                            call_text=ast.unparse(node),
                            loop_line=loop_node.lineno,
                            loop_targets=targets,
                            high_confidence=high,
                        )
                    )
        self.generic_visit(node)


def scan_source(source, source_path, extra_methods=(), extra_names=()):
    """Parse source and return the list of N1Finding objects."""
    tree = ast.parse(source, filename=source_path)
    scanner = N1Scanner(extra_methods=extra_methods, extra_names=extra_names)
    scanner.visit(tree)
    return scanner.findings


def _split_csv(value):
    return [item.strip() for item in value.split(",") if item.strip()]


def build_parser():
    parser = argparse.ArgumentParser(
        prog="n1-query-spotter.py",
        description=(
            "Spot potential N+1 query patterns (query-like calls inside loops) in "
            "Python source files. With no FILE arguments, reads source from stdin."
        ),
        epilog="Exit codes: 0 no findings, 1 findings, 2 usage or I/O error.",
    )
    parser.add_argument("files", nargs="*", metavar="FILE", help="Python source files to scan")
    parser.add_argument(
        "--json",
        action="store_true",
        help="emit a machine-readable JSON report instead of human lines",
    )
    parser.add_argument(
        "--extra-methods",
        default="",
        metavar="NAME[,NAME...]",
        help="additional query-like attribute method names to detect, e.g. 'run_query,raw'",
    )
    parser.add_argument(
        "--extra-names",
        default="",
        metavar="NAME[,NAME...]",
        help="additional query-like bare function names to detect",
    )
    return parser


def _read_input(paths, errors):
    """Yield (label, source) pairs for every input; collect IO errors."""
    if not paths:
        yield _STDIN_LABEL, sys.stdin.read()
        return
    for raw in paths:
        path = Path(raw)
        try:
            yield str(path), path.read_text(encoding="utf-8")
        except OSError as exc:
            errors.append(f"{path}: cannot read: {exc.strerror}")


def main(argv=None):
    parser = build_parser()
    args = parser.parse_args(argv)
    errors = []
    all_findings = []
    for label, source in _read_input(args.files, errors):
        try:
            findings = scan_source(
                source,
                label,
                extra_methods=_split_csv(args.extra_methods),
                extra_names=_split_csv(args.extra_names),
            )
        except SyntaxError as exc:
            errors.append(f"{label}:{exc.lineno}: cannot parse source: {exc.msg}")
            continue
        for finding in findings:
            all_findings.append((label, finding))

    if errors:
        for message in errors:
            print(f"ERROR: {message}", file=sys.stderr)
        return 2

    if args.json:
        report = {
            "findings": [{"file": label, **finding.to_dict()} for label, finding in all_findings],
            "count": len(all_findings),
        }
        print(json.dumps(report, indent=2))
    else:
        for label, finding in all_findings:
            print(finding.render(label))
        if all_findings:
            print(
                f"{len(all_findings)} potential N+1 pattern(s) found; "
                "consider batch queries, joins, or eager loading.",
                file=sys.stderr,
            )
    return 1 if all_findings else 0


if __name__ == "__main__":
    sys.exit(main())

"""Tests for n1-query-spotter.py.

Covers: query call inside a loop (attribute and bare-name forms), no finding
when queries live outside loops, high-confidence vs possible classification,
nested loops, --json output, --extra-methods, stdin input, --help, and error
paths (missing file, unparseable source).

Discoverable by both pytest and unittest (unittest.TestCase classes).
"""

import json
import os
import subprocess
import sys
import tempfile
import unittest
from contextlib import suppress

SCRIPTS_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)))
SPOTTER = os.path.join(SCRIPTS_DIR, "n1-query-spotter.py")


def run_spotter(args, stdin_data=None):
    cmd = [sys.executable, SPOTTER, *args]
    proc = subprocess.run(
        cmd,
        input=stdin_data,
        capture_output=True,
        text=True,
        timeout=30,
    )
    return proc.returncode, proc.stdout, proc.stderr


def write_source(code):
    """Write Python source to a temp file; return its path."""
    with tempfile.NamedTemporaryFile(
        mode="w", suffix=".py", delete=False, encoding="utf-8"
    ) as handle:
        handle.write(code)
        path = handle.name
    return path


def cleanup(path):
    with suppress(OSError):
        os.unlink(path)


class TestN1SpotterFindings(unittest.TestCase):
    def test_flags_query_call_inside_loop(self):
        code = (
            "import db\n"
            "def list_orders(orders):\n"
            "    result = []\n"
            "    for order in orders:\n"
            "        result.append(db.query('SELECT * FROM items WHERE order_id=?', order.id))\n"
            "    return result\n"
        )
        path = write_source(code)
        try:
            rc, stdout, _ = run_spotter([path])
            self.assertEqual(rc, 1)
            self.assertIn("potential N+1", stdout)
            self.assertIn("inside loop at line 4", stdout)
        finally:
            cleanup(path)

    def test_flags_attribute_query_in_loop(self):
        code = (
            "def send_receipts(customers):\n"
            "    for customer in customers:\n"
            "        account = customer.accounts.get(account_id=customer.default_account_id)\n"
            "        email_receipt(account)\n"
        )
        path = write_source(code)
        try:
            rc, stdout, _ = run_spotter([path])
            self.assertEqual(rc, 1)
            self.assertIn("potential N+1", stdout)
            self.assertIn(".get(", stdout)
        finally:
            cleanup(path)

    def test_no_finding_when_query_outside_loop(self):
        code = (
            "import db\n"
            "def list_orders(order_ids):\n"
            "    placeholders = ','.join('?' for _ in order_ids)\n"
            "    return db.query(f'SELECT * FROM orders WHERE id IN ({placeholders})', *order_ids)\n"
        )
        path = write_source(code)
        try:
            rc, stdout, _ = run_spotter([path])
            self.assertEqual(rc, 0)
            self.assertEqual(stdout.strip(), "")
        finally:
            cleanup(path)

    def test_no_finding_without_query_calls(self):
        code = "def add(a, b):\n    return a + b\n"
        path = write_source(code)
        try:
            rc, stdout, _ = run_spotter([path])
            self.assertEqual(rc, 0)
            self.assertEqual(stdout.strip(), "")
        finally:
            cleanup(path)

    def test_nested_loop_detection(self):
        code = (
            "def flatten(teams):\n"
            "    for team in teams:\n"
            "        for member in team.members:\n"
            "            profile = profiles.find(member.profile_id)\n"
        )
        path = write_source(code)
        try:
            rc, stdout, _ = run_spotter([path])
            self.assertEqual(rc, 1)
            self.assertIn("potential N+1", stdout)
            self.assertIn("profiles.find", stdout)
        finally:
            cleanup(path)

    def test_while_loop_detection(self):
        code = (
            "def drain(queue):\n"
            "    while queue:\n"
            "        item = queue.pop()\n"
            "        row = db.execute('SELECT * FROM jobs WHERE id=?', item.id)\n"
        )
        path = write_source(code)
        try:
            rc, stdout, _ = run_spotter([path])
            self.assertEqual(rc, 1)
            self.assertIn("inside loop", stdout)
        finally:
            cleanup(path)


class TestN1SpotterConfidence(unittest.TestCase):
    def test_high_confidence_when_loop_var_referenced(self):
        code = (
            "def render(users):\n"
            "    for user in users:\n"
            "        posts = db.query(posts_by_author, user.id)\n"
        )
        path = write_source(code)
        try:
            _, stdout, _ = run_spotter([path, "--json"])
            report = json.loads(stdout)
            self.assertEqual(report["count"], 1)
            self.assertEqual(report["findings"][0]["confidence"], "high")
            self.assertEqual(report["findings"][0]["loop_targets"], ["user"])
        finally:
            cleanup(path)

    def test_possible_confidence_without_loop_var(self):
        code = "def process(rows):\n    for _ in rows:\n        db.query('SELECT 1')\n"
        path = write_source(code)
        try:
            _, stdout, _ = run_spotter([path, "--json"])
            report = json.loads(stdout)
            self.assertEqual(report["count"], 1)
            self.assertEqual(report["findings"][0]["confidence"], "possible")
        finally:
            cleanup(path)


class TestN1SpotterCli(unittest.TestCase):
    def test_help_exits_zero(self):
        rc, stdout, _ = run_spotter(["--help"])
        self.assertEqual(rc, 0)
        self.assertIn("potential N+1", stdout)

    def test_json_output_parseable(self):
        code = "def go(items):\n    for item in items:\n        row = db.get(item.id)\n"
        path = write_source(code)
        try:
            rc, stdout, _ = run_spotter([path, "--json"])
            self.assertEqual(rc, 1)
            report = json.loads(stdout)
            self.assertEqual(report["count"], 1)
            self.assertEqual(report["findings"][0]["file"], path)
        finally:
            cleanup(path)

    def test_stdin_input(self):
        code = "for row in rows:\n    fetch(row.id)\n"
        rc, stdout, _ = run_spotter([], stdin_data=code)
        self.assertEqual(rc, 1)
        self.assertIn("<stdin>", stdout)

    def test_extra_methods_flag(self):
        code = "def go(items):\n    for item in items:\n        engine.raw_query(item.id)\n"
        path = write_source(code)
        try:
            rc_without, _, _ = run_spotter([path])
            rc_with, stdout, _ = run_spotter([path, "--extra-methods", "raw_query"])
            self.assertEqual(rc_without, 0)
            self.assertEqual(rc_with, 1)
            self.assertIn("raw_query", stdout)
        finally:
            cleanup(path)

    def test_missing_file_exit_two(self):
        rc, _, stderr = run_spotter(["/nonexistent/nope.py"])
        self.assertEqual(rc, 2)
        self.assertIn("ERROR", stderr)

    def test_parse_error_exit_two(self):
        path = write_source("def broken(:\n    pass\n")
        try:
            rc, _, stderr = run_spotter([path])
            self.assertEqual(rc, 2)
            self.assertIn("cannot parse", stderr)
        finally:
            cleanup(path)

    def test_empty_file_exit_zero(self):
        path = write_source("")
        try:
            rc, stdout, _ = run_spotter([path])
            self.assertEqual(rc, 0)
            self.assertEqual(stdout.strip(), "")
        finally:
            cleanup(path)


if __name__ == "__main__":
    unittest.main()

# Error-Handling Taxonomy

Fill this taxonomy when designing or reviewing error handling for a service.
It makes the service's error behavior explicit, consistent, and reviewable in
one place. Every error path in the code should trace back to a row here.

## Error Classification

| Class | Meaning | Examples | Client-visible? | Recovery strategy |
|---|---|---|---|---|
| Client error | The request is wrong and will not succeed if retried unchanged | `[fill: invalid field, missing resource, conflict]` | Yes — structured 4xx | Fix the request; no retry |
| Transient failure | A dependency or resource was temporarily unavailable | `[fill: timeout, 503, connection reset]` | Sometimes — retryable signal | Retry with backoff and jitter |
| Permanent server failure | The service itself hit an unexpected state | `[fill: coding bug, corrupt state]` | Generic 5xx only | Alert; no automatic retry |

## Error Response Contract

- Response shape: `[fill: JSON/gRPC error structure — code, message, details, correlation id]`
- Stable error codes: `[fill: the enumerated codes clients can match on]`
- Status-code mapping table:

| Condition | HTTP status | Error code | Notes |
|---|---|---|---|
| Validation failed | `[fill: e.g. 400]` | `[fill: e.g. validation_error]` | `[fill: which fields and why]` |
| Resource not found | `[fill: e.g. 404]` | `[fill: code]` | `[fill: when this applies]` |
| State conflict / stale write | `[fill: e.g. 409]` | `[fill: code]` | `[fill: concurrency or idempotency context]` |
| Too many requests | `[fill: e.g. 429]` | `[fill: code]` | `[fill: rate-limit and retry-after header]` |
| Unexpected error | `[fill: e.g. 500]` | `[fill: code]` | `[fill: what is logged vs returned]` |

## Exception Handling Rules

- Where exceptions are caught: `[fill: boundary layers that map exceptions to responses]`
- What is logged at each layer: `[fill: context fields, stack traces only server-side]`
- What is never exposed to clients: `[fill: stack traces, SQL, internal paths, dependency details]`
- Correlation: `[fill: how request/trace IDs are attached to logs and error responses]`

## Retry and Idempotency

| Operation | Idempotency key? | Retry policy | Ambiguity handling |
|---|---|---|---|
| `[fill: operation]` | `[fill: yes/no and where the key comes from]` | `[fill: attempts, backoff, jitter, which statuses are retried]` | `[fill: how a timeout-before-response is resolved, e.g. GET to verify state]` |

## Background Jobs and Queues

- Retry policy per queue: `[fill: max attempts, backoff schedule, retryable error classes]`
- Dead-letter behavior: `[fill: where failed jobs land and who drains them]`
- Poison-message handling: `[fill: how a message that always fails is quarantined]`

## Testing the Error Paths

- Test cases to add: `[fill: one test per mapping row above — request, expected code, expected body]`
- Failure injection: `[fill: how transient failures are simulated in tests (e.g. a stub that returns 503)]`
- Verification: `[fill: how the error contract is asserted (contract tests, schema checks)]`

# Service Design Record

Fill this record when designing or restructuring a backend service, before
implementation begins. Keep it in the repository next to the service code so
reviewers and future maintainers can see the decisions that shaped the
architecture.

## Context

- Service name: `[fill: service name]`
- Owner team: `[fill: owning team]`
- Problem being solved: `[fill: what user or system problem does this service address]`
- Consumers: `[fill: which services, clients, or teams call this service]`
- Non-functional requirements: `[fill: latency target, throughput, availability, data-retention needs]`

## Service Structure

- Boundary style chosen (layered / hexagonal / clean): `[fill: which structure applies and why]`
- Layers or modules and their responsibility: `[fill: list each layer or module with one line of responsibility]`
- Dependency direction rule: `[fill: e.g. "transport may depend on service, service on persistence interfaces, never the reverse"]`
- Framework and language: `[fill: stack, and what framework-owned vs framework-agnostic code exists]`

## API Surface

| Endpoint / operation | Method | Purpose | Request validation | Success response | Failure response |
|---|---|---|---|---|---|
| `[fill: path or RPC name]` | `[fill: HTTP verb or gRPC method]` | `[fill: purpose]` | `[fill: schema/validation approach]` | `[fill: status + body]` | `[fill: error codes mapped to this operation]` |

## Data Access

- Storage: `[fill: database or store, and why this one]`
- Access pattern: `[fill: repository interface, ORM, raw SQL; batch queries and eager-loading strategy]`
- Transaction boundaries: `[fill: which operations need a transaction and its isolation level]`
- Pagination strategy: `[fill: cursor or offset, ordering key]`

## Error Handling

- Error classification: `[fill: how client vs transient vs permanent errors are distinguished]`
- Error response format: `[fill: shape of the error body, stable error codes, correlation IDs]`
- Retry policy for external dependencies: `[fill: backoff, jitter, max attempts, idempotency keys]`
- Failure fallback: `[fill: what happens when retries are exhausted]`

## Integrations

| External system | Interaction | Failure handling | Idempotency | Backpressure |
|---|---|---|---|---|
| `[fill: system]` | `[fill: sync call, webhook, queue]` | `[fill: retry/circuit breaker policy]` | `[fill: how duplicates are prevented]` | `[fill: queue limit, rate limit, load shedding]` |

## Event Flow

- Domain facts emitted: `[fill: meaningful completed business facts, not property changes]`
- Unit-of-work rule: `[fill: what state and outbox records commit atomically]`
- Outbox relay: `[fill: event identity, publish retry, crash-after-publish handling]`
- Incoming message deduplication: `[fill: consumer identity + event identity, inbox lease/state]`
- Handler idempotency: `[fill: repeated side effects and external-effect strategy]`
- Retry, quarantine, and replay: `[fill: classifications, limits, operator evidence, and stop rules]`

## Coexistence And Authority

- Old and new paths: `[fill: adapters, selector, strangler route, or dual path]`
- Authority by operation/data field: `[fill: exactly one authoritative writer or explain the exception]`
- Comparison evidence: `[fill: shadow/parallel comparison, tolerance, and mismatch action]`
- Handoff condition: `[fill: measurable evidence and owner who can transfer authority]`
- Removal condition: `[fill: callers, queues, old writes, flags, credentials, and recovery evidence]`

## Observability

- Structured logging fields: `[fill: request id, trace id, service, environment]`
- Metrics: `[fill: RED or USE metrics exposed and where]`
- Traces: `[fill: span coverage at service boundaries]`
- Alerts: `[fill: the alert rules tied to this service]`

## Testing Plan

- Unit tests: `[fill: business-logic cases and the fakes used for boundaries]`
- Integration tests: `[fill: API contract tests and how the stack is provisioned]`
- Contract tests: `[fill: consumer contract tests and their provider]`
- Query regression guard: `[fill: query-count assertions or N+1 checks]`

## Alternatives Considered

- Alternative 1: `[fill: option considered]` — rejected because `[fill: reason]`
- Alternative 2: `[fill: option considered]` — rejected because `[fill: reason]`

## Open Questions

- `[fill: any unresolved decision that needs input before implementation]`