# Coda Persistent Database

Persistent storage for Coda conversations using Prisma ORM with Aurora MySQL.

## Setup

### 1. Install dependencies

```bash
pnpm install
```

Installs the Prisma CLI and generated client.

### 2. Generate the typed client

```bash
npx prisma generate
```

Re-run this whenever `prisma/schema.prisma` changes.

### 3. Apply migrations

```bash
npx prisma migrate deploy
```

Applies all pending migrations to the target database.

### 4. Configure environment

Copy `.env.shadow` → `.env` and fill in:

```env
CODA_DB_HOST=<aurora-cluster-endpoint>
CODA_DB_PORT=3306
CODA_DB_USER=<db-user>
CODA_DB_PASS=<db-password>
CODA_DB_DATABASE=coda

CODA_DB_IDENTITY_HMAC_SECRET=<64-hex-char secret>
CODA_DB_IDENTITY_AES_KEY=<64-hex-char key>
```

Generate the identity crypto secrets:

```bash
# HMAC secret (for identity hashing)
node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"

# AES key (for identity encryption — must be 64 hex chars / 32 bytes)
node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"
```

The persistence layer is optional — if the `CODA_DB_*` variables are absent the server continues with Redis-only mode.

## Schema changes

Edit `prisma/schema.prisma`, then generate and record a migration:

```bash
npx prisma migrate dev --name <description>
```

Commit the generated migration file alongside the schema change.

## Architecture

```
Client → SSE stream → Redis (sync) → DB (async, retry x3, backoff)
Client → Redis hit? → yes: return | no: DB → populate Redis → return
```

- **DB** is the source of truth for conversation persistence.
- **Redis** remains the hot cache for the streaming path.
- Writes are fire-and-forget with retry — they never block the streaming hot path.
- On a Redis cache miss, conversations are loaded from DB and back-filled into Redis.

### Services

| Service              | Responsibility                                               |
| -------------------- | ------------------------------------------------------------ |
| `ChatService`        | Conversation CRUD and metadata (title, starred, soft delete) |
| `UserService`        | Identity lookup and upsert                                   |
| `MessageTreeService` | Message tree reads and writes (adjacency list)               |
| `SatelliteService`   | Tool calls, thoughts, sources, attachments                   |
| `FeedbackService`    | Thumbs up/down and comment storage                           |
| `ModelService`       | Model reference by external ID + provider                    |

### Orchestrators

- **persister** — wraps a full conversation write in a single atomic transaction with retry and backoff.
- **loader** — assembles a conversation by running satellite queries in parallel, then merging results.

### Crypto

Identity protection is unchanged from the original design: HMAC hash used for lookup, AES-256 encrypted value stored for investigation. Keys are injected via environment variables.

## Message tree

Each message stores `parent_message_id`, `active_child_message_id`, and `active_leaf_message_id` (leaf cache). The leaf cache enables flat queries with no recursive CTEs — O(1) leaf lookup at the cost of O(depth) write amplification on the async write path.

## Testing

```bash
cd server && npx jest tests/db/coda/ --no-coverage
```