2. Requirements and Invariants

RFC-DBOPS-0002                                                    Section 2
Category: Standards Track                        Requirements and Invariants

This section defines the constraints and invariants that shape all subsequent architectural decisions. No design presented in later sections MAY violate the principles established here.

2.1 Design Goals

2.1.1 Query-Layer Overhead Reduction

Backend services MUST use a query builder that minimizes runtime overhead between the application query API and the SQL sent to PostgreSQL.

The current Prisma Client path introduces measurable latency through its internal engine binary and query-generation pipeline. Replacing this layer with a lightweight query builder that compiles to SQL without an intermediary process reduces per-request overhead for read and write operations.

2.1.2 Explicit Query Control

The query builder MUST expose SQL composition at a level where developers control join strategy, subquery structure, and schema qualification directly.

Explicit query control enables performance optimization for specific access patterns and allows developers to reason about generated SQL without depending on an opaque query planner.

2.1.3 Schema-Scoped Multi-Tenant Isolation

All tenant-scoped queries MUST use per-query schema qualification rather than session-level search_path mutation.

Per-query schema scoping eliminates the category of cross-tenant data leakage risk that arises when pooled connections retain a stale search_path from a previous request. The query builder MUST support a mechanism equivalent to Kysely's withSchema() that qualifies all table references in the generated SQL with the tenant schema.

2.1.4 Compile-Time Type Safety

The query builder MUST provide compile-time TypeScript type checking for query parameters, column references, and result shapes.

Type safety prevents a class of runtime errors where column names, parameter types, or result shapes diverge from the database schema. The type definitions MUST be derived from or validated against the authoritative Prisma schema to prevent drift.

2.1.5 Shared Connection Pool

The query builder MUST operate over a shared connection pool rather than maintaining per-tenant client instances.

A single pg.Pool shared across all tenants, with per-query schema scoping, replaces the current model of one TenantPrismaClient per tenant. This reduces connection count growth from linear-in-tenants to bounded-by-pool-size.

2.1.6 Migration Tooling Continuity

Prisma Migrate and Atlas MUST remain the schema migration tools throughout and after the query-layer migration.

The query-layer migration concerns runtime query generation only. Schema migration tooling is a separate concern with its own lifecycle and is not affected by this architectural change.

2.2 Non-Goals

2.2.1 Schema Migration Replacement

This RFC MUST NOT alter the schema migration tooling. Prisma Migrate and Atlas continue to manage schema evolution for metadata and tenant databases.

2.2.2 Database Engine Changes

This RFC MUST NOT introduce changes to the PostgreSQL engine version, configuration, or deployment topology. The query-layer migration operates above the database engine boundary.

2.2.3 ATS-Main or MongoDB Clients

This RFC MUST NOT alter AtsPrismaClient or any MongoDB Prisma client (AtsPolicyPrismaClient, TenantPolicyPrismaClient, CommsMailPrismaClient). These clients serve different databases and access patterns that are outside the scope of this decision.

2.2.4 PgBouncer Configuration

This RFC MUST NOT redesign the connection pooling topology. The pg driver connection pool replaces Prisma's internal pool for Kysely queries, but PgBouncer and overall pooling architecture remain unchanged.

2.2.5 API Contract Changes

This RFC MUST NOT alter the external API contracts of any endpoint. Response shapes, status codes, pagination behavior, and error semantics MUST remain identical before and after query-layer migration.

2.3 Invariants

Invariant 1 — Type Safety

The Kysely query builder MUST provide compile-time TypeScript type safety for all query parameters, column references, and result types that is equivalent to or exceeds the type safety provided by Prisma Client's generated types.

No query MUST use any casts for table names, column references, or result shapes. Type definitions MUST be derived from the Prisma schema or from database introspection to prevent schema drift.

This invariant prevents the introduction of runtime data-access failures that Prisma's generated types currently guard against. Violation would degrade developer confidence and increase production error rates.

Invariant 2 — Multi-Tenant Isolation

All tenant-scoped queries MUST use Kysely's withSchema() method to qualify table references with the tenant schema identifier. No tenant query MAY rely on session-level search_path mutation or implicit schema resolution.

This invariant eliminates the category of cross-tenant data leakage risk present when pooled connections carry stale schema context. Violation would create a data isolation failure affecting tenant confidentiality.

Invariant 3 — Feature Parity

No endpoint MUST be migrated from Prisma to Kysely until the Kysely implementation demonstrates functional parity with the existing Prisma implementation for all supported query parameters, relation hydration, pagination, filtering, sorting, and response shapes.

This invariant prevents partial migrations that degrade endpoint behavior. Violation would introduce regressions visible to API consumers.

Invariant 4 — Zero Data Regression

The Kysely implementation for any migrated endpoint MUST return data that is logically equivalent to the Prisma implementation for the same input parameters. No migration MUST alter query semantics, data correctness, or result ordering unless the ordering change is explicitly documented and approved.

This invariant protects data integrity and prevents silent behavioral changes. Violation would produce incorrect data visible to end users.

Invariant 5 — Rollback Capability

Each migrated endpoint MUST retain the ability to revert to the Prisma implementation path until the Kysely implementation is validated in production. The Prisma implementation code MUST NOT be removed until the corresponding Kysely path is confirmed stable for a defined validation period.

This invariant guarantees recoverability when post-migration validation uncovers regressions. Violation would eliminate the safety net for production incidents.

Invariant 6 — Connection Pool Stability

The introduction of the pg driver connection pool for Kysely queries MUST NOT degrade connection pool behavior, increase connection churn, or conflict with existing PgBouncer pooling. During the coexistence period, the Kysely pg.Pool and the remaining Prisma client pools MUST coexist without resource contention that causes connection exhaustion.

This invariant preserves connectivity stability for all services during and after migration. Violation would introduce connection exhaustion or latency spikes affecting all database consumers.

2.4 Success Criteria