1. Introduction and Motivation

RFC-DBOPS-0002                                                    Section 1
Category: Standards Track                                     Introduction

1.1 Background

The platform backend services rely on PostgreSQL as the primary relational data store. Runtime query generation is handled by Prisma Client, which provides an ORM-style abstraction over SQL. Prisma Client generates SQL at runtime from a declarative query API, manages connection pooling through its internal engine binary, and supports nested relation includes that hydrate complex object graphs from a single API call.

The backend-main service is a NestJS application containing 20 tenant-scoped modules (company, candidates, positions, submissions, contacts, interviews, and others). All modules inject PrismaService from the shared @pnats/schema-manager package and follow the same query pattern: obtain a tenant-scoped Prisma client via prismaService.getClient(tenantId), then invoke Prisma's query API. This pattern appears across 134+ call sites in the codebase.

The @pnats/schema-manager package generates five separate Prisma clients for three PostgreSQL databases and two MongoDB databases. This RFC concerns only the tenant SQL client (TenantPrismaClient), which serves the bulk of application queries.

1.2 Current State

The backend query layer has the following characteristics:

1.2.1 Multi-Tenant Schema Access

The current architecture uses PostgreSQL's schema-per-tenant pattern. Each tenant receives a schema named after its UUIDv7 identifier. All TenantPrismaClient instances share the same base connection URL (TENANT_DB_QUERY_URL with ?schema=public). For raw SQL operations such as candidate search, the code explicitly executes SET search_path TO public, "${tenantId}" before running queries. For standard Prisma ORM calls, schema resolution depends on the Prisma client configuration.

In a pooled-connection environment, session-wide search_path mutation creates a risk of cross-tenant data leakage if connections are returned to the pool with a stale schema context. This risk requires careful coordination between the ORM layer and connection pool lifecycle.

1.2.2 Client-per-Tenant Scaling

The PrismaManager caches one TenantPrismaClient instance per tenant in a static dictionary. Each instance carries its own Prisma Engine process and internal connection pool. As the number of active tenants grows, this creates a linear increase in engine processes and pooled connections, which introduces memory and connection-count pressure on the database server.

1.3 Benchmark Evidence

A controlled benchmark was conducted on the backend-main service comparing the existing Prisma path against a Kysely-based replica for the company list retrieval endpoint.

1.3.1 Benchmark Scope

The Kysely replica hydrates the same relation graph as the Prisma path: contacts, positions (with submissions), leads, agreement, status relation, opportunities, and submission status/candidate joins for nested submission payloads.

1.3.2 Benchmark Results

1.3.3 Benchmark Constraints

The benchmark represents a controlled comparison with intentional limitations:

Despite these constraints, the benchmark provides meaningful evidence that query-layer overhead is non-trivial in the current Prisma path for read-heavy endpoints with relation hydration.

1.4 Motivation

The current Prisma Client query layer creates three architectural pressures that motivate this migration:

Query-layer overhead. The benchmark evidence demonstrates that Prisma's runtime query generation and internal engine processing add measurable latency to read-heavy endpoints. For endpoints that serve high-frequency list views with nested relations, this overhead compounds across concurrent requests. The Prisma Engine binary interposes between the application and the database, adding serialization, query planning, and result-mapping overhead that a lightweight query builder avoids.

Limited query control. Prisma Client abstracts SQL generation behind a declarative API. Developers cannot control join strategies, subquery placement, or schema qualification without escaping to raw SQL via $queryRaw or $executeRawUnsafe. This opacity limits the ability to optimize queries for specific access patterns and makes performance debugging dependent on Prisma's internal query planner rather than direct SQL analysis.

Multi-tenant schema safety. Prisma Client does not support per-query schema scoping. The current approach relies on session-level search_path mutation for raw queries, which introduces cross-tenant risk in pooled environments. Kysely's withSchema() method provides per-query schema qualification that eliminates this category of risk by making schema scope explicit and immutable at the query level.

Client scaling pressure. The one-client-per-tenant model creates linear growth in Prisma Engine processes and pooled connections. A shared pg.Pool with per-query schema scoping via withSchema() replaces N client instances with a single connection pool, reducing memory and connection overhead as tenant count grows.

1.5 Scope Statement

This RFC defines the architectural decision, invariants, and migration strategy for replacing TenantPrismaClient with Kysely as the runtime query builder across backend-main tenant-scoped modules. It does not prescribe implementation timelines, team assignments, or step-by-step migration procedures.

Prisma Migrate and Atlas remain the schema migration tools. AtsPrismaClient and all MongoDB Prisma clients are unaffected by this RFC. The ATS-main database query path is out of scope.