The data access layer decision record: why the repository pattern you chose determines your abstraction leakage surface and your N+1 query hiding failure mode

A 29-person B2B SaaS company introduced a repository pattern in month three of development after a senior engineer joined from a consulting background and proposed it as an improvement over the direct db.query() calls that had accumulated in the first two months. The pitch was correct: the existing code had database calls in controller methods, service classes, and background jobs, with SQL strings scattered across 34 files with no consistent structure. The repository pattern would consolidate database access behind named interfaces — ProjectRepository, UserRepository, ActivityRepository, OrganizationRepository — and each interface method would have a meaningful name (findAllForOrganization, findMembersForProject, recentForProject) that communicated its intent without exposing the underlying SQL. The team agreed. Over three days, the senior engineer extracted the existing database calls into repository classes, and the code was cleaner: the controllers called repository methods, each repository method issued a database query, and the relationship between application logic and database operations was explicit in the code structure.

The organization dashboard page loaded all the data for an organization's home view: the list of projects, the members of each project, and the recent activity for each project. In the repository pattern implementation, the controller called three repository methods in sequence: projectRepository.findAllForOrganization(orgId) to get the projects, then inside the loop over the project list, userRepository.findMembersForProject(project.id) and activityRepository.recentForProject(project.id) for each project. The loop was natural — the controller had the list of projects and needed the members and activity for each one. Each repository call looked correct in isolation: findMembersForProject issued one query, recentForProject issued one query. The controller code was readable and expressed its intent clearly. In the development environment, the seed database had three organizations with 3, 4, and 5 projects respectively. The dashboard loaded in 18 milliseconds with 9 queries (1 for projects, 4 for members across 4 projects, 4 for activity across 4 projects). Nobody observed 9 queries because 18 milliseconds felt fast and no query monitoring was in place during development.

Fourteen months after the repository pattern was introduced, the company had grown its customer base to 87 organizations. Twelve of those organizations were enterprise accounts — they had more than 30 projects, paid the Team tier at $2,400/year each, and together generated 38% of MRR. The enterprise accounts had been onboarded over the previous six months and their project counts ranged from 35 to 67. When an engineer from the largest enterprise account wrote to support saying their organization dashboard was "basically unusable — loads in 2-3 seconds and sometimes times out," the support team initially categorized it as a network issue. A second ticket from a different enterprise account the same week triggered an engineering investigation.

The engineer who investigated added query logging to the development environment and loaded the dashboard page for the largest enterprise account's organization, which had 45 projects in the staging database. The query log showed 91 queries: 1 for the project list, 45 for the member queries (one per project), and 45 for the activity queries (one per project). Response time was 2.3 seconds. The engineer added a 65-project organization and measured 131 queries and 3.4 seconds. The pattern was clear: the dashboard issued 2N + 1 queries where N was the number of projects. Every enterprise account's dashboard was an N+1 problem at scale. The smallest enterprise account had 35 projects (71 queries, 1.8 seconds) and the largest had 67 projects (135 queries, 3.5 seconds). All 12 enterprise accounts were experiencing degraded dashboard performance and none had reported it until it became severe enough to notice — users had adapted to the slow dashboard by refreshing less frequently and using other navigation paths that bypassed it.

Fixing the N+1 required redesigning the repository interface. The three separate methods (findAllForOrganization, findMembersForProject, recentForProject) were combined into a single method (findAllForOrganizationWithMembersAndActivity) that issued three queries: one for all projects in the organization, one for all members across all projects in the organization (using an IN clause on the project IDs), and one for all recent activity across all projects in the organization (similarly batched). The three queries ran in 2, 3, and 4 milliseconds respectively against the 45-project enterprise account — a total of 9 milliseconds for the same data that had previously taken 2.3 seconds and 91 queries. The refactoring required updating 7 call sites that had used the old methods in loops, adding query count assertions to the integration tests for the dashboard endpoint and the three other endpoints that had similar N+1 patterns, and auditing all remaining repository method calls for loop-based usage. The total engineering effort was 11 days. The founding session that introduced the repository pattern documented "repository pattern for clean database access" and no more. It did not specify that each repository method must declare how many queries it generates for a given input, that callers must not invoke repository methods inside loops over collections returned by other repository methods, or that query count is a testable assertion that must be included in the integration test for every endpoint whose implementation might naturally lead a developer to write a loop over a collection and call a repository method inside it.

A 21-person startup adopted TypeORM in month two of development as the database access layer for their Node.js backend. The decision was straightforward: the founding engineers had used TypeORM in previous roles, it had strong TypeScript support, and its entity-based model fit the application's domain objects well. The default configuration was used throughout: entities were decorated with @Entity(), @OneToMany(), @ManyToOne(), and other relationship decorators, and no fetch strategy was specified for any relationship, which meant TypeORM used its default lazy loading behavior — associations were not loaded with the parent entity but instead loaded on first access via a dynamically-generated query at the moment the property was read.

The posts endpoint (GET /api/posts) was written in the third month to return the 20 most recent posts for the authenticated user's feed. The endpoint controller loaded the posts using the post repository, then passed the results to a serializer that produced the JSON response. The serializer needed to include each post's author name (post.author.name) and comment count (post.comments.length) in the response. Both post.author and post.comments were TypeORM lazy-loaded associations — @ManyToOne to the User entity and @OneToMany to the Comment entity respectively. The serializer accessed both associations for each of the 20 posts in the response array. TypeORM's lazy loading intercepted each access and issued a separate database query: 20 queries for the 20 authors (SELECT * FROM users WHERE id = $1 executed once per post with a different user ID each time) and 20 queries for the 20 comment collections (SELECT * FROM comments WHERE post_id = $1 executed once per post). Including the initial posts query, the endpoint issued 41 database queries per request.

The development and QA environments ran with low enough request volume that 41 queries per request was unobservable. The endpoint responded in 45 milliseconds in development (where the database and application ran on the same machine with no network latency between them) and in 120 milliseconds in staging (where the application and database were on separate instances with a 3-millisecond average network round trip). Neither measurement triggered concern: 120 milliseconds was within the team's informal performance standard of "under 500 milliseconds." The endpoint shipped.

Three months after the endpoint shipped, the application had grown to 14,000 registered users and the posts feed was the primary engagement surface. At peak usage (weekday mornings and Sunday evenings), the posts endpoint received 180 requests per minute. At 41 queries per request, this was 7,380 queries per minute directed at the PostgreSQL database. The PostgreSQL instance was a single-node setup with 4 CPU cores and 16 GB of RAM, which was adequate for the query volume, but the database connection pool was configured for 10 connections — a default that had never been revisited after initial setup. At 7,380 queries per minute, the average query time was 8 milliseconds, and 10 connections could process 75,000 queries per minute — well above the demand. The bottleneck was not the total query throughput but the queuing behavior under bursty load. The posts endpoint's 41 queries did not arrive at the connection pool as a uniform stream of 7,380 individual queries per minute. They arrived as 180 bursts of 41 simultaneous queries (each request issuing its 41 queries as rapidly as the await chain resolved). Under a burst of 180 concurrent requests, the connection pool received 7,380 query requests in a sub-second window. With 10 pool connections, 40 of those 41 queries per request had to queue behind other requests' queries. The average queue wait time was 180 milliseconds. Total request time at peak: 120ms (normal) + 180ms (pool queue wait) = 300ms average, 4.2 seconds P99 (the requests at the tail of the queue). The P99 degradation appeared in support tickets as "the feed takes forever to load sometimes." At first the team attributed it to server load. A senior engineer added query logging to the production application and observed the 41-query pattern in the logs. The fix — adding relations: ['author', 'comments'] to the posts repository query, which told TypeORM to eager-load both associations via a single JOIN query instead of issuing lazy-loaded queries on access — reduced the endpoint from 41 queries per request to 2 queries (one JOIN for posts with authors, one batched query for comment counts). P99 latency returned to 180 milliseconds. The fix took 20 minutes to implement once the N+1 was identified. The founding session that adopted TypeORM documented "use TypeORM for the database access layer" and no more. It did not specify the fetch strategy for any entity association, identify which associations would be accessed during serialization (and therefore required eager loading), set a maximum acceptable query count per endpoint, or require that integration tests include a query count assertion for each endpoint that serializes associated entities.

A 34-person SaaS company began writing raw SQL strings in their application code from the first sprint. The initial choice was pragmatic: the founding engineers were SQL-comfortable, an ORM felt like indirection they didn't need, and db.query('SELECT * FROM users WHERE id = $1', [userId]) was faster to write than setting up entity mappings. The practice spread organically — whenever a developer needed data from the database, they wrote a SQL string, tested it in a database client, and embedded it in the application code. No policy governed where SQL strings were written, how schema element names were referenced, or what the process was for updating SQL strings when a schema element changed.

By year three, the codebase had grown to include 287 raw SQL strings embedded in 94 files. The strings ranged from simple primary-key lookups to multi-join aggregation queries. They appeared in controller classes, service classes, repository-like utilities, background job implementations, report generators, and data correction scripts. The majority of the strings were in files that were touched regularly by the engineering team — API endpoint controllers, core service classes, background jobs for common operations. But 4 strings existed in files that were rarely opened: the monthly billing reconciliation job (jobs/billing/monthly-reconciliation.js), the quarterly user retention report (jobs/reports/quarterly-retention.js), the annual audit log export (scripts/audit-export.js), and a data correction utility from a customer support incident two years earlier (scripts/fix-subscription-state.js). These 4 files had not been modified in 8, 11, 14, and 16 months respectively.

In month 34, the engineering team identified a performance problem with the user_events table, which had grown to 420 million rows after three years of event ingestion. Queries against user_events with date filters were performing full-index scans because the index on created_at was too large for the query planner to choose an index range scan effectively at the table's current size. The solution was to partition the user_events table by month: create a parent table user_events with month-based child partitions (user_events_2024_01, user_events_2024_02, and so on), migrate the existing data into the partitions, and let PostgreSQL's partition pruning route queries to the correct child partition based on the created_at date in the WHERE clause. For most queries, the partitioned table behaved identically to the original unpartitioned table under the same name user_events — PostgreSQL's query planner transparently routed queries through the partition hierarchy. The technical migration plan was correct. The risk the team underweighted was that the unpartitioned user_events table was being dropped and replaced by the partitioned parent, and any SQL string that had relied on the original unpartitioned table's physical existence — rather than on the partitioned parent — would fail.

Two engineers were assigned to audit all references to user_events in the application codebase before the migration was deployed. They ran grep -r "user_events" src/ jobs/ scripts/ and found 147 matches across 91 files. They reviewed each match and updated 143 of them to either use the partitioned parent table name (which was the same, user_events, making most of the updates no-ops) or to use the month-specific child partition name for the handful of administrative queries that needed to reference a specific partition directly. The 4 missed references were in the 4 rarely-opened files: the grep search had been run against src/ jobs/ scripts/, but the monthly billing reconciliation job had been moved from jobs/ to a cron/ directory 11 months earlier as part of a restructuring, and its path was no longer covered by the grep pattern. The quarterly retention report, the audit log export, and the data correction utility were in scripts/ and were covered by the grep — but the grep had returned 147 matches and the engineers had reviewed them by scrolling through the list; three of the four files in scripts/ were visually similar in name to other files the engineers had already reviewed (the scripts directory had 22 files), and the matches for those three were scrolled past without updating. The migration was deployed on a Thursday evening. The deployment log showed clean execution. PostgreSQL confirmed the partition scheme was in place. The engineers ran the primary dashboard and reporting endpoints against the migrated database — all returned correct data. The migration was declared successful.

Nine days after the migration, the monthly billing reconciliation job ran as scheduled at 2:00 AM on the first of the following month. The job's SQL included FROM user_events in a subquery that counted events per customer per billing period. The migration had replaced the original unpartitioned user_events table with a partitioned parent of the same name — but the reconciliation job had been moved to the cron/ directory, which had not been in the grep scope. Its SQL still referenced user_events, which now pointed to the partitioned parent. The partitioned parent should have routed queries correctly — and it did for queries that included a created_at date filter in the WHERE clause, which triggered PostgreSQL's partition pruning. The reconciliation job's subquery did not include a date filter on created_at for the outer count query (it filtered on billing_period_id, a foreign key column, which was not part of the partition key). Without partition pruning, the query attempted a full scan of all partitions, and after the migration the statistics for the partitioned parent table were stale — ANALYZE had been run on the partition child tables but not propagated to the parent's statistics in the version of PostgreSQL the company was running. The query planner produced a plan that timed out after 30 minutes (the job's configured query timeout). The job's exception handler caught the timeout, logged it at WARNING level (not ERROR), and marked the reconciliation run as completed with a status of "partial." No alert was configured for the "partial" reconciliation status — it had been introduced months earlier for a different use case (handling mid-month plan changes) and had never been associated with a reconciliation failure. The billing team discovered the problem 11 days later, on the 12th of the month, when a team member reviewing a customer account noticed that the event count for the current billing period showed zero despite the customer being actively engaged. Checking three other accounts revealed the same pattern. The billing team escalated to engineering. The investigation identified the stale statistics and the missed SQL reference within two hours. Running ANALYZE on the partitioned parent table resolved the query plan and the reconciliation job completed successfully when rerun. But the reconciliation data for 29 customers who had been billed using the partial run output required manual correction — cross-referencing the actual event counts in the partitioned tables against the billing records that had been generated from the zero-count run. The manual reconciliation took 6 engineering days. The founding session (or rather, the series of sessions across sprint one through month 34 that established the practice of writing raw SQL strings wherever convenient) documented nothing about where SQL strings were permitted, how schema element names must be referenced to enable exhaustive migration audits, or how the migration safety process must verify that all raw SQL strings referencing a changed schema element have been found and reviewed before the migration is deployed.

Structural properties set by the data access layer decision

Three structural properties are determined when a team establishes how their application accesses the database. None appear explicitly in the session that introduces a repository pattern, the session that selects an ORM, or the series of sessions that permit raw SQL strings to accumulate without policy — they are the operational consequences of design choices made under the assumption that "database access" is an implementation detail rather than an interface with a contract that determines performance behavior, query multiplicity, and migration safety across the entire lifecycle of the application.

Property 1: The repository interface contract and the N+1 query surface. The N+1 query surface of a repository-based application is the set of all call sites where application code invokes a repository method inside a loop over a collection returned by a different repository method. Each element of that surface generates N additional queries (one per collection element) beyond the first query that loaded the collection. The surface is zero when repository interfaces are designed to prohibit the pattern — when each interface method declares the maximum number of queries it issues for a given input, and when the interface contract explicitly prohibits callers from invoking repository methods inside loops over collections returned by other repository methods. The surface grows when interface design implicitly permits the pattern by providing element-level methods (findMembersForProject(projectId)) with no corresponding batch method (findMembersForProjects(projectIds)), leaving the element-level method as the only option for callers who need data for a collection. The growth is invisible in development environments where collections are small, and becomes visible in production when specific customers accumulate collections large enough to push response times above user-tolerance thresholds. The structural response is interface design: replace any element-level method that is naturally called in a loop with a batch method that accepts the full collection and returns all results in a bounded number of queries. The batch method's implementation uses a SQL IN clause, a JOIN on the parent ID, or a lateral join depending on the query structure — one query (or a small fixed number) replaces the N queries of the loop. The enforcement mechanism is a query count assertion in the integration test for each endpoint: the test installs a query counter before the endpoint is called, calls the endpoint with a collection of representative size, and asserts that the query count matches the contract. A contract violation (more queries than specified) fails the test immediately, catching the regression at introduction rather than at the first enterprise customer with a large enough collection to make the regression visible in production. The performance optimization decision record documents the performance gate that requires EXPLAIN ANALYZE review for endpoints above a query-count threshold, and the pre-deploy load test that validates endpoint response time against a production-representative data volume. The database indexing strategy decision record documents the index creation policy for the batch queries that replace N+1 loops — the IN clause on project_id in the members query, for example, requires an index on project_id in the project_members table to avoid a full-table scan for each batch, and that index is created as part of the same change that introduces the batch method.

Property 2: The ORM fetch strategy and the implicit query multiplication surface. An ORM's lazy loading default means that the number of queries generated by a request is not visible in the application code that handles the request. The application code reads post.author.name — one property access, syntactically indistinguishable from accessing a non-lazy property — and the ORM generates a database query. The query generation is implicit: it happens inside the ORM's lazy-loading mechanism at the property access site, not at a db.query() call that the developer wrote. The implicit query multiplication surface is the set of all lazy-loaded property accesses in serializers, view templates, and computed properties that execute per-element during the processing of a collection. Each such access adds N implicit queries to the request, where N is the number of elements in the collection. The surface is zero when every association accessed during serialization is declared as eager at the repository or query level, so that the ORM loads all required associations in the same round trip as the parent entities. The surface grows when developers add serializer fields that access lazy-loaded associations without adding the corresponding prefetch declaration to the repository method or query that loaded the parent entities. The growth is invisible in development (where N is small and the implicit queries complete quickly) and becomes visible in production when N grows large enough and request concurrency grows high enough to saturate the database connection pool — at which point the saturation manifests as P99 latency spikes that appear to be database throughput problems rather than connection pool exhaustion caused by implicit query multiplication. The structural response has two components: a fetch strategy specification for each entity association in the entity model (explicitly declare each association as eager or lazy, with lazy associations explicitly excluded from serialization unless an explicit prefetch call is included), and a query count assertion in the integration test for each endpoint that serializes associated entities. The query count assertion catches implicit query multiplication at introduction: a developer who adds a new serializer field that accesses a lazy-loaded association will see their test fail with "expected 2 queries, got 22" when they run the test suite, prompting them to add the eager load declaration before the code is reviewed or merged. The database connection pool decision record documents the connection pool sizing model — the relationship between concurrent request count, queries per request, average query time, and pool size that determines the request rate at which the pool saturates — which is the model that makes the implicit query multiplication surface visible in terms of operational risk rather than as an abstract code quality concern. The observability strategy decision record documents the query count monitoring in production — the per-endpoint query count metric that surfaces the first sign of implicit query multiplication regression as a metric anomaly (queries per request for GET /api/posts increased from 2 to 22) before it manifests as a latency or error rate anomaly in the user experience, providing earlier detection and a shorter window between introduction and resolution.

Property 3: The raw SQL policy and the schema coupling surface. Raw SQL strings embedded in application code are directly coupled to the physical database schema: the table names, column names, join conditions, partition names, and index names that appear in the SQL string must exactly match the current database schema for the string to execute without error. The schema coupling surface of a codebase is the set of all raw SQL strings that reference schema elements that could change in a future migration — which, in a living application, is every schema element. The surface grows monotonically as raw SQL strings are added: each new string that references a table name adds one more location that must be found and updated when that table is renamed, partitioned, or dropped. The surface is manageable when raw SQL strings are concentrated in designated, easily-enumerable locations (repository classes, query builder files, migration files) that are trivially searched and whose schema references are registered in a machine-readable schema dependency map. The surface becomes unmanageable when raw SQL strings are distributed across the entire codebase — in controllers, services, jobs, scripts, utilities, and one-off fixes — because exhaustive enumeration by grep is unreliable (grep scope misses relocated files; visual review of long grep output lists misses matches in files with similar names; rarely-executed files are not mentally salient during review). The cost of an unreliable enumeration is a missed reference that produces a failure delayed until the specific code path executes: a missed reference in a nightly cron job fails the next night; a missed reference in a monthly billing job fails 30 days after the migration; a missed reference in a quarterly report fails 90 days after the migration; a missed reference in an annual audit export fails 365 days after the migration. In each case, the failure is causally connected to a migration that has already been declared successful, making diagnosis non-obvious, and the affected code path is by definition a low-frequency path (otherwise it would have been executed before the migration and caught in the deployment window). The structural response is a centralization policy (raw SQL permitted only in designated data access objects), a schema reference registration (table and column names referenced via typed constants or registered annotations rather than inline strings), and a migration safety gate (automated enumeration of schema dependencies flagged for human review before the migration is deployed). The database migration strategy decision record documents the migration process including the pre-migration audit step — the machine-readable enumeration of all schema element references that must be reviewed before any DDL migration that changes table names, partition schemes, column names, or schema structure. The CI/CD pipeline decision record documents the integration of the schema coupling linter into the CI pipeline — the static analysis check that fails the build if a raw SQL string is detected in a file outside the designated data access layer, enforcing the centralization policy at introduction rather than during a pre-migration audit sprint.

What the founding session records and what it omits

The founding data access layer session typically records the technology choice (repository pattern, ORM name, query builder, raw SQL) and the rationale for that choice (separation of concerns, developer familiarity, type safety, performance control). It may record the primary entities and their relationships, the database schema version at the time of the decision, and the integration framework chosen. What it does not record is the query-count contract for each data access method — the statement that findAllForOrganizationWithMembersAndActivity issues 3 queries and that callers must never issue additional queries per element of the returned collection. Without the contract, the number of queries issued by any endpoint is an emergent property of the application code rather than a specified design constraint, and N+1 patterns can be introduced by any developer at any time without a visible signal that they are violating a contract.

The founding session also does not record the fetch strategy for ORM entity associations, because the ORM adoption decision is typically made for its type safety, its migration tooling, or its entity mapping capabilities — performance characteristics of specific fetch strategies are not salient at the time the ORM is selected, before any associations have been modeled and before any serializers have been written. The lazy loading default appears safe at selection time (it loads less data, which seems like it would be faster) and its performance implications are only observable when the combination of association access patterns in serializers and the request concurrency in production creates a connection pool saturation event. The adoption decision that would have prevented the saturation — specify eager loading for associations accessed during serialization, test with a query count assertion — requires knowing, at adoption time, which associations will be accessed during serialization, which requires knowing the serializer design, which is not yet designed. The correct approach is not to predict the serializer design at adoption time but to establish the decision rule: for any association that is accessed during serialization, declare it as eager at the query site (or exclude it from lazy loading in the entity configuration), and add a query count assertion to the integration test for the endpoint. This rule can be applied at any point — at adoption, as each endpoint is written, or as the first N+1 regression is encountered — but it produces the most value when applied as each endpoint is written, before the first serializer is shipped to production.

The founding session also does not record the centralization policy for raw SQL strings, because the first raw SQL string written in the application is usually a justified choice (ORM overhead not warranted for a simple lookup, query builder not expressive enough for a complex aggregation) and the policy concern only becomes apparent when the number of raw SQL strings has grown large enough that a migration audit becomes nontrivial. The correct time to establish the centralization policy is when the first raw SQL string is written — establishing which file locations are permitted, how schema element names must be referenced (typed constant or registered annotation rather than inline string), and what the migration process is for verifying that all references to a changed schema element have been updated. Establishing the policy for a single string is trivial. Establishing it for 287 strings requires 6-8 days of refactoring spread across 94 files, during which the application's test coverage for the raw SQL paths must be verified (since the refactoring touches SQL strings, and a refactoring error produces a silent schema mismatch rather than a compilation error). The founding session made a de facto policy decision by writing raw SQL strings in an arbitrary location — the de facto policy is "raw SQL is permitted anywhere," and 287 strings and 94 files represent the natural consequence of that policy over three years of development. The billing reconciliation failure was not caused by the raw SQL strings themselves (the SQL was correct) but by the inability to exhaustively enumerate them: the schema coupling surface was large enough that a two-engineer audit of 147 matches missed 4 matches and produced a migration declaration of "successful" that was incorrect for a subset of code paths.

The accumulated operational cost of these three omissions arrives at different timescales and through different mechanisms. The N+1 omission produces a graduated performance degradation correlated with customer growth — the largest and most valuable enterprise customers experience the worst performance because they have the largest collections, meaning that the customers whose retention is most important receive the worst product experience before the problem is diagnosed. The ORM fetch strategy omission produces a threshold failure under load — the endpoint performs acceptably at moderate concurrency and fails abruptly above the connection pool saturation threshold, making the failure appear to be an infrastructure capacity problem rather than a code design problem. The raw SQL centralization omission produces a delayed, silent, causally-disconnected failure — the migration is successful, monitoring looks clean, the team closes the ticket, and the failure appears weeks or months later in a code path that was not part of the migration validation scope. Each failure mode has a distinct detection challenge: N+1 is detected by query count monitoring after it degrades production performance; ORM lazy loading saturation is detected by connection pool wait time metrics that are only observed when the pool saturates; raw SQL migration misses are detected only by executing the specific code paths that reference the missed schema element. The test data management decision record documents how production-representative test data volume is required to make N+1 query count assertions meaningful — a test database with 3-5 records per collection will not fail a query count assertion if the expected count was set against 3-5 records and the N+1 exists but produces only 9 queries rather than 91. The test strategy decision record documents the test types and coverage requirements for each data access pattern — which tests are responsible for the query count assertion (integration tests against a real database), which tests validate the batch query correctness (integration tests with a representative collection size), and which tests validate migration safety for raw SQL paths (end-to-end tests that execute every code path with raw SQL against the migrated schema before the migration is declared complete). The WhyChose decision extractor finds the founding data access layer sessions in your ChatGPT and Claude export — the "how should we structure database access?" architecture discussion, the "ORM vs. raw SQL?" tradeoff session, the "let's add a repository pattern" refactoring proposal. It extracts the data access choice, the ORM selection, and the raw SQL policy (or lack of one) from the founding sessions and surfaces the query-count contract, fetch strategy specification, and centralization policy that the sessions documented versus the ones they omitted — the decisions that determine whether a 45-project enterprise account loads their dashboard in 9 milliseconds or 2.3 seconds, whether a peak-load surge saturates the connection pool at 120 requests per minute, and whether a table partitioning migration misses 4 references and breaks billing reconciliation for 29 customers.

The five ADR sections for a data access layer decision

Section 1: Repository interface contract and N+1 prevention requirements. Specify the repository interface design rules that prevent N+1 query patterns from being introduced at any call site. The core rule: every repository interface method must declare its query-count contract — the maximum number of database queries the method issues for a given input, expressed as a constant (1 query, 2 queries, 3 queries) or as a function of a declared parameter (1 query regardless of collection size; N queries where N is the number of distinct parent entity types, not the number of collection elements). Methods whose natural usage would lead a caller to invoke them inside a loop over a collection must be replaced by or supplemented with batch methods that accept the full collection and return all results in a bounded number of queries. An element-level method (findMembersForProject(projectId: string)) is permitted only if it is not accessed during the rendering of a collection of projects — if it is, it must be accompanied by a batch method (findMembersForProjects(projectIds: string[])) and a linting rule that flags usages of the element-level method inside collection iteration (via a static analysis rule on the TypeScript/Python/Ruby source, or via a code review checklist item). Specify the query count assertion requirement: every integration test for an endpoint that loads entities from the database must include a query count assertion that verifies the endpoint issues no more than the specified number of queries per request, regardless of the size of the entity collections involved. The assertion is written at the integration test level (against a real database), not at the unit test level, because unit tests with mocked repositories cannot detect N+1 patterns — the mock returns data without issuing queries, making the N+1 invisible. Specify the query count monitoring requirement: each production endpoint must emit a metric for the number of database queries issued per request (sampled at 1-5% to minimize overhead), with an alert threshold set to 2× the specified contract for that endpoint. A regression from 3 queries to 7 queries per request signals either a new N+1 pattern or an unspecified query being issued outside the repository layer, both of which require investigation. The performance optimization decision record documents the pre-deploy performance gate — the load test against production-representative data volumes that includes query count verification in addition to response time verification, so that an N+1 pattern that passes the response time threshold on small test data is still caught before deployment when the load test runs against representative collection sizes.

Section 2: ORM fetch strategy policy and implicit query generation requirements. Specify the fetch strategy policy for every entity association in the entity model. The policy has two valid strategies: eager (the association is loaded in the same query as the parent entity, via a JOIN or a batch query, and the result is always available without triggering an additional database access) and explicit lazy (the association is not loaded with the parent entity and is explicitly excluded from any serializer or computed property that would trigger an implicit database access on property read). There is no third option of implicit lazy (the ORM default) — the implicit lazy strategy is prohibited because it makes the query count of any code path that accesses the association a function of whether the accessing code happens to be inside a collection iteration, which is not statically analyzable without tracing the call path from the association access to the query site. Specify the serializer access rule: for every serializer that produces an API response from an entity, list the associations accessed during serialization. Every listed association must be declared eager at the query site that loads the parent entity, and the query site must use an explicit prefetch list (TypeORM's relations option, Prisma's include, Django's select_related/prefetch_related, Active Record's includes) rather than relying on ORM-level entity configuration for eager loading, because ORM-level eager configuration applies globally and may load associations that are not needed for every use of the entity, increasing data transfer unnecessarily. Specify the query count assertion for every endpoint that serializes associated entities: loading N parent entities must issue at most K queries, where K is the number of distinct association types loaded (one query per association type via batching, not N queries per association type via lazy loading). The test must verify this invariant holds for N = 1, N = 10, and N = 100 — a correctly implemented eager load issues the same number of queries for all three collection sizes; a lazy load issues 1, 10, and 100 queries respectively for the association queries. Specify the explicit lazy guard: for associations declared as explicit lazy, add a guard in the entity class that raises a loud error if the lazy-loaded property is accessed without an explicit prefetch call being recorded for the current request context (via a thread-local or request-context marker set by the prefetch call). The guard converts a silent implicit query into a loud error that is caught in the development environment, preventing the implicit lazy association from accidentally being accessed in a serializer. The observability strategy decision record documents the production query monitoring — the per-endpoint query count distribution (p50, p95, p99) that provides an early warning when a new code path introduces implicit query multiplication, visible as an increase in the p99 query count for the affected endpoint before it manifests as a latency increase. The caching strategy decision record documents the caching layer policy for entity associations that are expensive to load but rarely change — for associations that qualify for caching, the caching layer may be an alternative to eager loading that avoids redundant database queries at the cost of cache invalidation complexity, and the tradeoff between eager loading and caching is a decision that belongs in the data access layer decision record rather than being made independently by each developer who encounters an expensive association.

Section 3: Raw SQL policy, schema coupling surface, and migration safety requirements. Specify the locations in the codebase where raw SQL strings are permitted. The permitted locations are: repository implementation classes (one file per primary entity, in a designated data access layer directory), query builder utility classes (for complex aggregation queries that are reused across multiple callers), and database migration files (for DDL statements and data migration queries). Raw SQL is not permitted in application service classes, controller classes, background job implementations, scheduled task classes, report generator modules, utility scripts, or any file outside the designated data access layer directory. Enforce the centralization policy with a linter rule that fails the CI build if a raw SQL string is detected in a non-permitted file. The linter rule is written as a file-path check on SQL string literals: if the file path does not match the permitted data access layer directory pattern and the file contains a string that matches the SQL statement pattern (starts with SELECT, INSERT, UPDATE, DELETE, WITH, or contains JOIN or WHERE as distinct words), the build fails with the file path and line number of the violation. Specify the schema element reference requirement: every raw SQL string that references a table name or column name must reference it via a typed constant defined in a schema constants file (TABLES.USER_EVENTS = 'user_events', COLUMNS.CREATED_AT = 'created_at') rather than as an inline string literal. The typed constant serves two purposes: it makes the schema element reference machine-readable (a tool can enumerate all uses of TABLES.USER_EVENTS without parsing SQL strings) and it provides a single point of change (renaming the table requires changing the constant value once, and TypeScript/Python/Ruby type checking will fail at any usage of the old constant name if it is removed). Specify the migration safety gate: for every database migration that changes a schema element (renames a table, adds or removes a partition scheme, renames or drops a column, changes a table's schema or namespace), the migration PR must include an automated enumeration of all schema element references (via the typed constant usage map or via code search for the constant name) and a developer sign-off for each reference confirming that it has been reviewed and either updated, confirmed unaffected, or explicitly deferred with a follow-up ticket. The migration is not deployed without the complete sign-off list. The sign-off list is committed to the migration PR as a migration-audit file (migrations/audit/YYYYMMDD-migration-name.md) so that the audit trail is preserved in version control alongside the migration itself. Specify the testing requirement for raw SQL paths: every code path that contains a raw SQL string must have an integration test that executes against a real database, not a mock. The test must execute against the current schema (after the most recent migration) and must be in the CI pipeline that runs on every PR branch, not only in a nightly test suite. A raw SQL path that is tested only nightly will not catch a schema coupling failure introduced by a migration that is deployed between nightly test runs. The database migration strategy decision record documents the full migration process including the pre-migration audit step, the migration validation tests that run against the migrated schema in a staging environment before production deployment, and the rollback plan for migrations that affect schema elements referenced in raw SQL strings. The CI/CD pipeline decision record documents the linter integration — the raw SQL centralization check that runs on every PR, the schema constant reference check that flags inline SQL string literals, and the migration audit file check that fails a migration PR if the audit file is missing or if any referenced schema element is listed as unreviewed.

Section 4: Query observability and performance gate requirements. Specify the observability requirements for database query behavior in production. Every application endpoint must emit three database-related metrics per request: the total number of queries issued, the total time spent waiting for database query results (not counting application processing time), and the connection pool wait time (the time between requesting a connection from the pool and receiving one). These three metrics together distinguish N+1 query problems (high query count, high total query time, low pool wait time in low concurrency), connection pool saturation (moderate query count, moderate total query time, high pool wait time at peak concurrency), and slow individual queries (low query count, high total query time per query, low pool wait time). Without all three, a P99 latency spike is ambiguous: it could be caused by a slow query, by an N+1 pattern that materializes only under large collections, or by pool saturation under high concurrency, and each root cause has a different fix. Specify the performance gate that runs before any deployment: the gate loads each endpoint with a representative traffic pattern (simulating the peak request rate observed in the prior 7 days), measures the three database metrics under that load, and fails the deployment if any endpoint's query count exceeds its specified contract by more than 10%, if any endpoint's P99 latency exceeds its SLO, or if the connection pool wait time exceeds 50ms at the simulated peak rate. The 10% tolerance in the query count check is to accommodate legitimate variance in query count for endpoints with optional includes; a variance greater than 10% signals either an N+1 regression or an unspecified code path accessing the database outside the repository layer. Specify the slow query log review process: every query that takes more than 100ms in production is written to a slow query log and reviewed in the weekly engineering meeting, with an action item assigned for any query that appears more than twice per week. The slow query review is the mechanism by which query plan regressions (caused by schema changes, statistics changes, or data distribution changes) are caught before they become user-visible latency problems. The database indexing strategy decision record documents the index creation policy that is triggered by the slow query review — when a slow query is identified as needing an index, the index is created as part of the same sprint cycle rather than deferred to a future performance sprint, and the creation process includes EXPLAIN ANALYZE verification that the new index is used by the query planner and that the query time meets the performance target. The database connection pool decision record documents the pool sizing model and the scaling policy — the pool size is derived from the formula (peak concurrent requests × queries per request × average query time) / desired max pool wait time, and the pool size is reviewed when any of the input variables changes by more than 20%.

Section 5: Data access layer evolution policy and refactoring requirements. Specify the policy for evolving the data access layer as the application grows. The data access layer has a tendency to accumulate technical debt in three specific forms: N+1 patterns introduced by new features (callers adding repository method calls inside existing loops for convenience), lazy-load associations added to serializers without prefetch declarations (developers adding a new serializer field that accesses an association that happens to be lazy-loaded without realizing it will add N queries), and raw SQL strings added outside the designated data access layer when the layer's API is insufficiently expressive for a specific query. Each of these debt forms has a different prevention mechanism (query count assertion, query count monitoring, linting rule) but the same detection threshold: the debt is introduced one unit at a time and each unit is invisible in isolation, but the accumulation becomes visible only at a scale that requires a significant refactoring effort to reverse. The evolution policy specifies the cadence for data access layer audits: a monthly automated check that enumerates all repository method calls in the codebase (not just in designated data access layer files) and flags any method call inside a loop or inside a collection iteration callback; a monthly automated check that enumerates all serializer files and verifies that every association field access is accompanied by a prefetch declaration in the repository method or query that loads the parent entities; and a monthly automated check that counts raw SQL strings per file and flags any file outside the designated data access layer with a raw SQL string count greater than zero. The monthly check outputs are reviewed by the senior engineer responsible for the data access layer (a rotating role, 3-month terms) who triages the findings, creates tickets for items requiring remediation, and estimates the refactoring effort. Specify the refactoring trigger: if any monthly audit produces more than 10 items requiring remediation, the refactoring is scheduled as a dedicated sprint in the next quarter rather than being added to the backlog where it will be deferred indefinitely. The 10-item threshold is based on the observed correlation between the number of N+1 patterns in a codebase and the number of customer-reported dashboard performance issues: below 5 N+1 patterns, performance complaints are rare; above 10, they are routine. Specify the data access layer onboarding requirement: every engineer who joins the team must complete a data access layer onboarding that covers the query-count contract model, the fetch strategy specification for the team's ORM, and the raw SQL centralization policy. The onboarding is a 2-hour session with hands-on exercises that involve writing a repository method with a query count contract, writing the corresponding integration test with a query count assertion, and updating the schema constants file when a new table is added. Engineers who have not completed the onboarding are required to have their data access layer changes reviewed by an engineer who has. The database migration strategy decision record documents the refactoring safety process for data access layer changes — since refactoring repository methods changes the SQL that is executed, refactoring PRs must include integration tests that verify the output of the refactored method matches the output of the original method for a representative set of inputs, and must pass the performance gate (query count and response time) before deployment. The test strategy decision record documents the test coverage requirements for data access layer code — specifically, that repository methods are integration-tested against a real database (not mocked), that the test database is seeded with a collection size that makes N+1 patterns visible (at least 10 elements, not 1-3 elements that are insufficient to distinguish one-query-per-element from one-query-total), and that the test suite includes both correctness assertions (the right data is returned) and behavioral assertions (the query count matches the contract and does not vary with collection size in a way that indicates N+1).