# @coda/db Package Extraction -- TRD

## Status

Shipped

## Overview

Extracted shared database infrastructure -- Prisma schema, migrations, client factory, types, and crypto utilities -- from `server/src/db/coda/` into a standalone workspace package (`@coda/db`). This decouples schema management and connection logic from the server, enabling future services to share the same database without duplicating infrastructure. Server-specific access patterns (services, orchestration, persistence) remain in the server.

## Goals

1. **Eliminate schema duplication risk.** Any future consumer of the Coda database must use the same Prisma schema and migration history. A shared package enforces a single source of truth.
2. **Share the crypto contract.** The `hashIdentity` / `encryptIdentity` / `decryptIdentity` functions are inseparable from the User model's storage contract. Every service that performs user lookup needs them, so they belong alongside the schema.
3. **Follow existing monorepo conventions.** Match the terse naming (`@coda/db` alongside `api`, `client`, `server`), the `tsdown` build tooling (ESM + CJS + declarations), and the workspace dependency pattern already established by `@coda/core-api`.
4. **Keep the server lean.** Only shared, schema-level concerns move. Service classes, orchestration (loader, persister), and server-specific access patterns stay in `server/`, avoiding cross-consumer coupling.
5. **Decouple seeding from server startup.** Seed scripts become a schema concern run via `prisma db seed`, not inline during server boot.

## Architecture

### Package structure

```
db/
  package.json              # @coda/db
  tsconfig.json
  tsdown.config.ts
  prisma/
    schema.prisma           # moved from server/prisma/
    migrations/             # moved from server/prisma/
    seed/
      index.ts              # orchestrator -- calls each seeder
      models.ts             # rewritten from server/src/db/coda/seed-models.ts
  src/
    index.ts                # barrel: re-exports types, client, crypto
    client.ts               # PrismaClient factory (createPrismaClient, buildDatabaseUrl)
    types.ts                # shared DB types (CodaDbConfig, PrismaLike, MessageWithSatellite, etc.)
    crypto.ts               # identity hashing (HMAC-SHA256) and encryption (AES-256-GCM)
    generated/prisma/       # prisma generate output (gitignored)
```

```
server/src/db/coda/         # what remains
  index.ts                  # updated barrel -- re-exports server-specific modules only
  coda-services.ts          # service registry, imports types from @coda/db
  loader.ts                 # conversation loading, imports types from @coda/db
  stream-persister.ts       # stream persistence, imports types from @coda/db
  conversation-repository.ts
  services/                 # 6 service classes -- same logic, import paths updated
    chat.service.ts
    user.service.ts
    message-tree.service.ts
    satellite.service.ts
    feedback.service.ts
    model.service.ts
```

### Dependency graph

```
@coda/server-app  -->  @coda/db  <--  (future services)
                  -->  @coda/core-api
@coda/client-app  -->  @coda/core-api
```

`@coda/db` has no workspace dependencies. Its runtime dependencies are `@prisma/client` and `@prisma/adapter-mariadb`. It uses only Node built-ins (`node:crypto`) beyond those.

### Build order

Root `pnpm build` executes: `db:generate` (Prisma client generation) then `@coda/server-app build` (which transitively resolves `@coda/db` and `@coda/core-api` via source-first exports).

## Detailed Design

### What moved

| Source (server)                     | Destination (db)                       |
| ----------------------------------- | -------------------------------------- |
| `server/prisma/schema.prisma`       | `db/prisma/schema.prisma`              |
| `server/prisma/migrations/`         | `db/prisma/migrations/`                |
| `server/src/db/coda/client.ts`      | `db/src/client.ts`                     |
| `server/src/db/coda/types.ts`       | `db/src/types.ts`                      |
| `server/src/db/coda/crypto.ts`      | `db/src/crypto.ts`                     |
| `server/src/db/coda/seed-models.ts` | `db/prisma/seed/models.ts` (rewritten) |

### What stayed in server

- `coda-services.ts` -- service registry / factory
- `loader.ts` -- conversation loading orchestration
- `stream-persister.ts` -- stream persistence orchestration
- `conversation-repository.ts` -- conversation data access
- `services/` -- all 6 service classes (chat, user, message-tree, satellite, feedback, model)
- `server/src/db/snowflake/` -- read-only Snowflake connector (no shared schema, no other consumers)

### Import rewrites

After extraction, `@prisma/client` was removed from `server/package.json`. Under pnpm strict mode, transitive dependencies are not resolvable, so every direct `@prisma/client` import in server code was rewritten to `@coda/db`.

Key rewrites:

| Server file             | Before                                          | After                                       |
| ----------------------- | ----------------------------------------------- | ------------------------------------------- |
| `server.ts`             | `"./db/coda/client"`, `"./db/coda/seed-models"` | `"@coda/db"` (seed import removed entirely) |
| `config/load-config.ts` | `"../db/coda/types"`                            | `"@coda/db"`                                |
| `routes/chat-routes.ts` | `"../db/coda/crypto"`, `"../db/coda/types"`     | `"@coda/db"`                                |
| All 6 service files     | `"@prisma/client"` + `"../types"`               | `"@coda/db"`                                |
| `coda-services.ts`      | `"@prisma/client"` + `"./types"`                | `"@coda/db"`                                |
| `loader.ts`             | `"./types"` + `"@prisma/client"`                | `"@coda/db"`                                |
| `persister.ts`          | `"@prisma/client"`                              | `"@coda/db"`                                |

The server's `db/coda/index.ts` barrel was simplified to export only server-specific modules (no longer re-exports `@coda/db`). The server's `db/index.ts` was reduced to Snowflake-only exports.

### Prisma client resolution

The Prisma schema output path resolves relative to the schema file. With the schema at `db/prisma/schema.prisma`, the generated client lands in `db/src/generated/prisma/`. The Prisma config (`db/prisma.config.ts`) controls the output location.

`prisma generate` must always run from the `db/` package context (`pnpm --filter @coda/db db:generate` or `pnpm db:generate`). Running from root or server generates the client in the wrong location.

Resolution chain in production: `server (compiled JS) --> @coda/db (source, Node 24 type-stripping) --> generated Prisma client`.

### Barrel exports

`db/src/index.ts` re-exports all public symbols:

```
export * from "./types";    # CodaDbConfig, PrismaLike, MessageWithSatellite, etc.
export * from "./crypto";   # hashIdentity, encryptIdentity, decryptIdentity
export * from "./client";   # createPrismaClient, buildDatabaseUrl
```

### Server startup change

The inline `seedModels()` call was removed from `server.ts`. The server now only calls `createPrismaClient()` from `@coda/db` and `createCodaServices()` from the local coda module. Seeding runs separately via `prisma db seed` during migrations, CI, or local setup.

### Docker changes

- **`prod-deps`**: Added `COPY db/package.json ./db/` for workspace resolution.
- **`dev-deps`**: Added db source, configs, and Prisma schema. Runs `pnpm --filter @coda/db db:generate` after install to produce the generated Prisma client.
- **`deploy-server`**: Copies `db/package.json`, `db/src/` (source-first, Node 24 type-stripping), and `db/node_modules/` from prod-deps. Library packages use source-first exports -- no build step needed.
- **`lint-and-test`**: Copies db jest configs for test execution.
- **`migrate`**: Runs `pnpm --filter @coda/db migrate:deploy && pnpm db:seed`.

### Workspace and root config changes

- `pnpm-workspace.yaml`: Added `db` to the packages list.
- `server/package.json`: Added `"@coda/db": "workspace:*"` to dependencies. Removed `@prisma/client`, `@prisma/adapter-mariadb` from dependencies. Removed `prisma` from devDependencies.
- Root `package.json`: Added `db:generate`, `db:migrate`, `db:seed`, `lint:db`, `typecheck:db` scripts. Updated `build` to run `db:generate` before server build. Updated `clean:all` to include `db/node_modules`.

## Alternatives Explored

### Keep everything in server

Rejected. Any future service consuming the Coda database would need to duplicate the Prisma schema, migrations, client factory, and crypto utilities. This creates schema drift risk and violates DRY.

### Move crypto to a separate `@coda/crypto` package

Rejected. The hashing and encryption functions exist solely to serve the User model's storage contract (`hashIdentity` for lookup, `encryptIdentity`/`decryptIdentity` for PII). They have no use outside database operations. Splitting them into a separate package would create an artificial boundary.

### Move service classes into `@coda/db`

Rejected. Service classes encode server-specific access patterns (transaction scoping, query composition, business rules). Different consumers may need different access patterns for the same tables. Keeping services in the server avoids cross-consumer coupling.

### Move Snowflake connector to `@coda/db`

Rejected. The Snowflake connector is a read-only, server-specific integration with no shared schema. No other consumers are planned. It has no relationship to the Prisma-managed Coda database.

### Use `tsc` instead of `tsdown` for builds

Rejected. `tsdown` matches the pattern established by `@coda/core-api` and produces both ESM and CJS outputs with declarations. Consistency across workspace packages reduces cognitive overhead.

## Cost Analysis

### Engineering effort

The extraction was completed in a single implementation cycle across 7 chunks: scaffold, move schema/migrations, move source files, create seed scripts, update server dependencies, rewrite imports, and update root config / Dockerfile. The bulk of the work was mechanical import path rewrites (approximately 15 server files).

### Infrastructure cost

Zero. No new infrastructure, services, or deployments. The database, schema, and runtime behavior are identical before and after extraction.

### Benefit

Reduced duplication cost for any future database consumer. Without this extraction, each new service would need to: copy the Prisma schema, maintain its own migration history, duplicate the crypto module, and keep all of these in sync with the server. The `@coda/db` package eliminates this class of work entirely.

## Performance Analysis

### Build time

Negligible impact. The `db:generate` step (Prisma client generation) already ran as part of the server build. Moving it to a separate package does not change the work performed. The `tsdown` compilation of 3 small source files (client, types, crypto) adds under 1 second. In production Docker builds with source-first exports, the tsdown build step is not needed at all -- Node 24 type-stripping handles `.ts` imports directly.

### Runtime

Zero impact. The same Prisma client, the same connection logic, and the same crypto functions execute at runtime. The only difference is the module resolution path, which has no measurable cost.

### Prisma generate overhead

`prisma generate` produces the client once during build. It does not run at server startup. The generation time is unchanged; only the output directory moved from `server/node_modules/.prisma/client` to `db/src/generated/prisma/`.

## Scaling Characteristics

### Multi-service consumption

The primary scaling benefit. Any new service that needs Coda database access adds `"@coda/db": "workspace:*"` to its dependencies and imports `createPrismaClient`, types, and crypto from the package. Schema and migrations are managed in one place.

### Schema management

Centralized in `db/prisma/`. All consumers share the same migration history. `prisma migrate dev` and `prisma migrate deploy` run from the db package context. There is no risk of schema drift between services.

### Seed extensibility

The `db/prisma/seed/` directory uses an orchestrator pattern (`index.ts` calls individual seeders). Adding new reference data (e.g., permissions, feature flags) means adding a new file to the seed directory and calling it from the orchestrator.

## Breakdown Points and Mitigations

### Prisma client generated in wrong location

**Risk:** Running `prisma generate` from root or server instead of the db package context produces the client in the wrong `node_modules/.prisma/client` directory. The server fails to start with missing Prisma client errors.

**Mitigation:** Root `package.json` provides `db:generate` script that filters to `@coda/db`. The `build` script calls `db:generate` first. Dockerfile runs `pnpm --filter @coda/db db:generate` explicitly. Documentation in the design spec and code comments calls this out.

### Import path breaks under pnpm strict mode

**Risk:** After removing `@prisma/client` from `server/package.json`, any remaining direct `@prisma/client` import in server code fails at build time. pnpm strict mode does not resolve transitive dependencies.

**Mitigation:** All `@prisma/client` imports were systematically rewritten to `@coda/db` during extraction. The barrel export in `db/src/index.ts` re-exports all necessary Prisma types. TypeScript's `--noEmit` typecheck catches any missed imports before runtime.

### Circular dependencies

**Risk:** If `@coda/db` were to import from `@coda/server-app` or `@coda/core-api`, a circular dependency would form.

**Mitigation:** `@coda/db` has zero workspace dependencies by design. It depends only on `@prisma/client`, `@prisma/adapter-mariadb`, and Node built-ins. The dependency graph is strictly: `server --> db` (one direction).

### Docker layer caching invalidation

**Risk:** Changes to any file in `db/` invalidate Docker build cache for downstream layers.

**Mitigation:** The Dockerfile separates `db/package.json` (copied in `prod-deps`, rarely changes) from `db/src/` and `db/prisma/` (copied in `dev-deps`). Dependency-only changes to `package.json` do not invalidate the source layers and vice versa.

## Decision Log

| #   | Decision                   | Choice                              | Rationale                                                                                                                                                 |
| --- | -------------------------- | ----------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------- |
| 1   | Package name               | `@coda/db`                          | Matches the terse naming convention established by `api`, `client`, `server`.                                                                             |
| 2   | Snowflake connector        | Stays in `server/src/db/snowflake/` | Read-only connector with no shared schema. No other consumers planned.                                                                                    |
| 3   | `crypto.ts` placement      | Moves to `@coda/db`                 | Hashing/encryption is inseparable from the User model's storage contract. Other services need `hashIdentity` for user lookup.                             |
| 4   | `seed-models.ts` placement | Moves to `db/prisma/seed/`          | Seeding reference data is a schema concern. The directory structure accommodates future seed files.                                                       |
| 5   | Service classes            | Stay in server                      | Access patterns may be unique per consumer. Moving them would create cross-consumer coupling.                                                             |
| 6   | Build tooling              | `tsdown` (ESM + CJS, declarations)  | Matches the `@coda/core-api` build pattern for workspace consistency.                                                                                     |
| 7   | Prisma deps in server      | Removed from `server/package.json`  | `@coda/db` owns the Prisma dependency. pnpm strict mode enforces this boundary -- server code must import from `@coda/db`, not `@prisma/client` directly. |

## Dependencies

### Runtime

| Dependency                | Version | Purpose                                        |
| ------------------------- | ------- | ---------------------------------------------- |
| `@prisma/client`          | ^7.5.0  | Generated database client and type definitions |
| `@prisma/adapter-mariadb` | ^7.5.0  | Aurora MySQL driver adapter for Prisma         |

### Development

| Dependency         | Version           | Purpose                                            |
| ------------------ | ----------------- | -------------------------------------------------- |
| `prisma`           | ^7.5.0            | CLI for schema management, migrations, seeding     |
| `tsdown`           | ^0.21.4           | Build tooling (ESM + CJS + declarations)           |
| `tsx`              | ^4.19.0           | Runs seed scripts (`prisma db seed` uses tsx)      |
| `typescript`       | ~5.9.3            | Type checking                                      |
| `jest` / `ts-jest` | ^29.7.0 / ^29.3.0 | Unit testing                                       |
| `eslint`           | ^10.0.3           | Linting                                            |
| `dotenv`           | ^17.3.1           | Environment variable loading for seed/test scripts |

### Workspace consumers

- `@coda/server-app` depends on `@coda/db` via `"workspace:*"`.
- No other workspace packages depend on `@coda/db` currently.

## Testing Strategy

### Unit tests in `@coda/db`

Three test files shipped with the package:

- **`db/src/__tests__/crypto.test.ts`** -- Tests `hashIdentity` (determinism, uniqueness, output format), `encryptIdentity`/`decryptIdentity` (round-trip for simple, empty, long, unicode, and special-character strings; non-deterministic ciphertext; tamper detection for auth tag and ciphertext; wrong-key rejection; short/non-hex/empty input rejection). 17 test cases.
- **`db/src/__tests__/client.test.ts`** -- Tests `buildDatabaseUrl` (URL construction, special character encoding, credential positioning, driver variants) and `createPrismaClient` (adapter config passthrough, log level by environment, connection limit). 11 test cases.
- **`db/src/__tests__/index.test.ts`** -- Barrel export verification. Confirms all public symbols (`hashIdentity`, `encryptIdentity`, `decryptIdentity`, `buildDatabaseUrl`, `createPrismaClient`) are exported from the package entry point.

### Existing server tests

All existing server-side tests for services, loader, and persister continued to pass after import path rewrites. These tests validate the same logic -- only the module resolution paths changed. Test files mock `@coda/db` or `@prisma/client` as appropriate.

### Verification checklist (all passed)

- `pnpm build` completes (db:generate then server build)
- `pnpm typecheck` passes with zero errors across all packages
- `pnpm test:unit` passes across all packages
- `pnpm lint` passes across all packages
- Prisma generate produces client in correct location (`db/src/generated/prisma/`)
- Server starts and connects to Coda DB

## Rollout Plan

This was a refactoring-only change with no behavioral differences. The rollout was straightforward:

1. **Implementation.** All 7 chunks completed in sequence: scaffold, move schema, move source, create seeds, update deps, rewrite imports, update Docker/root configs.
2. **Local verification.** Full build, typecheck, lint, and test suite run locally.
3. **CI verification.** Docker-based lint-and-test pipeline validated the extraction in an isolated environment.
4. **Deploy.** Standard deployment pipeline. The server binary, database, and runtime behavior are identical. No migration, feature flag, or rollback mechanism was needed because no external contract changed.

## Open Questions

None. All design decisions were resolved during implementation and are recorded in the Decision Log above.