4. System Components
4.1 Kysely Query Builder
| Aspect | Description |
|---|---|
| Responsibility | Compiles TypeScript query expressions into parameterized SQL for tenant database operations |
| Input | Typed query API calls from tenant module services, scoped to a specific tenant schema via withSchema() |
| Output | Parameterized SQL statements dispatched to the pg.Pool for execution |
| Type Safety | Provides compile-time checking of table names, column references, join conditions, and result shapes against a generated type map |
| Interfaces | Query API consumed by all tenant module services; executes through PostgresDialect backed by pg.Pool |
| Failure Modes | Type errors surface at compile time; runtime SQL errors propagate as pg driver exceptions with full query context |
4.1.1 Schema Scoping
Kysely's withSchema() method qualifies all table references in the
generated SQL with the tenant schema identifier. This produces SQL of
the form SELECT ... FROM "tenant_uuid"."table_name" rather than
relying on search_path resolution.
A single Kysely instance is created at application startup. Per-request
tenant scoping is achieved by calling withSchema(tenantId) on each
query, which returns a schema-qualified query builder without mutating
the base instance. This approach is stateless with respect to tenant
context and safe for concurrent use across multiple tenants.
4.1.2 Relation Hydration
Prisma Client's include directive automatically generates joins and
subqueries to hydrate nested relations. Kysely does not provide an
equivalent ORM-level include mechanism. Relation hydration MUST be
implemented explicitly using joins, subqueries, or sequential queries
composed through Kysely's query API.
The existing company module benchmark implementation demonstrates this pattern by hydrating contacts, positions with submissions, leads, agreement, status relations, opportunities, and submission status/candidate joins through explicit SQL composition.
4.2 PostgreSQL Driver and Connection Pool
| Aspect | Description |
|---|---|
| Responsibility | Manages a bounded pool of PostgreSQL connections shared across all tenant queries |
| Driver | pg (node-postgres) provides the PostgreSQL wire protocol implementation |
| Pool Model | Single pg.Pool instance with configurable minimum, maximum, and idle timeout settings |
| Interfaces | Consumed by Kysely's PostgresDialect; connects to PgBouncer transaction pooler |
| Failure Modes | Pool exhaustion queues or rejects queries; connection errors trigger pool-level reconnection |
4.2.1 Connection Pool Consolidation
The current Prisma architecture maintains one TenantPrismaClient per
active tenant, each with its own internal connection pool. This creates
a linear relationship between tenant count and total connection count.
The target architecture replaces this with a single pg.Pool shared
across all tenants. Per-query schema scoping via withSchema() removes
the need for tenant-specific connection state. Total connection count
becomes bounded by the pool's max setting regardless of tenant count.
4.2.2 Coexistence During Migration
During the migration period, both connection models operate
simultaneously. The pg.Pool serves Kysely queries while remaining
TenantPrismaClient instances serve endpoints not yet migrated. Total
database connection count is the sum of both pools. The migration
strategy (see Section 5) accounts for
this coexistence and defines criteria for retiring Prisma client
instances as endpoints migrate.
4.3 Schema-Scoped Dialect
| Aspect | Description |
|---|---|
| Responsibility | Configures Kysely with the PostgresDialect backed by the shared pg.Pool |
| Configuration | Connection string sourced from TENANT_DB_QUERY_URL via @pnats/schema-manager database configuration |
| Interfaces | Provides the Kysely instance to tenant module services via NestJS dependency injection |
| Failure Modes | Dialect misconfiguration prevents query execution; connection string errors surface at application startup |
4.3.1 Type Database Interface
Kysely requires a TypeScript interface that maps table names to their column types. This interface MUST be generated or derived from the authoritative Prisma schema to prevent schema drift between the migration tool and the query builder.
Options for type generation include:
| Approach | Description |
|---|---|
| Prisma-to-Kysely code generator | Derives Kysely types from the Prisma schema at build time |
| Database introspection | Generates types from a live database using kysely-codegen or similar tooling |
| Manual type definition | Hand-authored types maintained alongside the Prisma schema |
The chosen approach MUST satisfy Invariant 1 (Type Safety) and MUST be integrated into the build pipeline to catch drift before deployment.
4.4 Relation Hydration Layer
| Aspect | Description |
|---|---|
| Responsibility | Composes multi-table queries to reproduce the nested object graphs that Prisma's include directive generates |
| Pattern | Explicit joins, lateral subqueries, or sequential queries assembled through Kysely's typed query API |
| Interfaces | Consumed by each tenant module service; produces response shapes identical to the current Prisma-based implementations |
| Failure Modes | Incorrect join logic produces missing or duplicated relations; missing subqueries omit nested data |
4.4.1 Hydration Strategy
Each endpoint's relation graph is different. The hydration layer is not a generic abstraction but a per-endpoint composition of Kysely queries that produces the same response shape as the Prisma implementation. This explicit approach provides full control over join strategy and query structure at the cost of requiring per-endpoint query authoring.
4.4.2 Validation Against Prisma Output
Feature parity (Invariant 3) and data equivalence (Invariant 4) require that each hydration implementation is validated against the existing Prisma output for the same endpoint and input parameters. This validation is defined in Section 5.5.
4.5 Prisma Migrate and Atlas (Retained)
| Aspect | Description |
|---|---|
| Responsibility | Manages schema evolution for metadata and tenant databases |
| Prisma Migrate | Applies migrations from the multi-file Prisma schema; generates TypeScript client types |
| Atlas | Provides declarative schema management and migration generation as an alternative migration workflow |
| Interfaces | Connects to PostgreSQL via PgBouncer session pooler; migration files stored in @pnats/schema-manager |
| Failure Modes | Migration failures block schema evolution; shadow database workflows require extension compatibility |
4.5.1 Continued Role
Prisma Migrate and Atlas are unaffected by this RFC. Schema migrations continue to use the session pooler. The Prisma schema files remain the authoritative source for table structures and are used to generate or validate Kysely type definitions.
4.6 Prisma Client (Sunset Path)
| Aspect | Description |
|---|---|
| Responsibility | Provides runtime query generation for tenant SQL operations during the migration period |
| Sunset | Prisma Client is retained for endpoints not yet migrated to Kysely and for rollback capability |
| Client Model | One TenantPrismaClient per active tenant, cached in PrismaManager.tenantClients |
| Interfaces | PrismaService.getClient(tenantId) continues to serve non-migrated endpoints |
| Failure Modes | Per-tenant client proliferation increases memory and connection pressure; Prisma Engine binary adds per-query latency overhead |
4.6.1 Removal Criteria
TenantPrismaClient and its supporting infrastructure in
PrismaManager MAY be removed when all of the following criteria
are met:
| Criterion | Description |
|---|---|
| All endpoints migrated | Every tenant module service endpoint operates on the Kysely path |
| Validation period complete | Each migrated endpoint has passed the defined validation period without rollback |
| No raw SQL dependencies | No service uses $queryRaw or $executeRawUnsafe through TenantPrismaClient |
| Connection pool consolidated | The pg.Pool serves all tenant queries with no remaining Prisma Engine connections |