# @coda/db

Prisma ORM layer for the Coda Aurora MySQL database. Provides the generated
Prisma client, migration tooling, connection factory, and identity crypto
utilities used across the server.

## What it provides

- **Prisma client** — generated types and query builder targeting Aurora MySQL
  via the MariaDB adapter (`@prisma/adapter-mariadb`)
- **`createPrismaClient(config)`** — factory that wires up connection pooling
  with sane defaults (20 connections, 15 s idle timeout)
- **`buildDatabaseUrl(config)`** — assembles a `mysql://` URL from discrete
  config fields
- **Crypto utilities** — `hashIdentity` (HMAC-SHA256), `encryptIdentity` /
  `decryptIdentity` (AES-256-GCM) for the two-layer user identity scheme
- **Shared types** — re-exports all Prisma-generated model types plus
  `PrismaTransactionClient`, `PrismaLike`, and `MessageWithSatellite`

## Setup

```bash
# Generate the Prisma client after schema changes
pnpm db:generate

# Run pending migrations (dev)
pnpm db:migrate

# Deploy migrations (CI / production)
pnpm --filter @coda/db migrate:deploy
```

## Environment

The server reads individual connection fields from its `.env`; there is no
single `DATABASE_URL`. Aurora MySQL connection config shape:

```
DB_HOST=<aurora-cluster-endpoint>
DB_PORT=3306
DB_USER=coda
DB_PASSWORD=<secret>
DB_NAME=coda
```

## Schema overview

| Model               | Table                 | Notes                                                                       |
| ------------------- | --------------------- | --------------------------------------------------------------------------- |
| `User`              | `users`               | Coda users; identity stored as HMAC hash + AES ciphertext, never plaintext  |
| `OAuthConnection`   | `oauth_connections`   | One encrypted token set per provider per user                               |
| `Chat`              | `chats`               | Conversation container; soft-deleted via `deleted_at`                       |
| `Message`           | `messages`            | Tree nodes with leaf-cache (`active_leaf_message_id`) for O(1) path queries |
| `Model`             | `models`              | LLM model registry; seeded at startup                                       |
| `MessageToolCall`   | `message_tool_calls`  | Tool use → result pairs per assistant message                               |
| `MessageThought`    | `message_thoughts`    | Extended thinking / reasoning blocks                                        |
| `MessageSource`     | `message_sources`     | Deep-links to Orchard platform entities                                     |
| `MessageAttachment` | `message_attachments` | File metadata; binary content stored in S3                                  |
| `MessageFeedback`   | `message_feedback`    | Thumbs up/down + optional comment; one per message                          |

### Key relationships

```
User ──< Chat ──< Message ──< MessageToolCall
                           ──< MessageThought
                           ──< MessageSource
                           ──< MessageAttachment
                           ──  MessageFeedback (1:1)
                           >── Model

User ──< OAuthConnection
```