Rcr Backend

---
name: rcr-backend
description: Database, API, and integration conventions for Red Cliff Record. Use when working with Drizzle ORM, tRPC routers, database migrations, integration syncs, or media alt-text workflows in this project. Triggers on database queries, API routes, Drizzle v2, tRPC, Zod validation, integration work, or `rcr media` commands.
---

# RCR Backend

Supplements global `typescript-guidelines` skill.

## Drizzle ORM

- **Always use Drizzle v2 query syntax** for reads: `db.query.<table>.findMany/findFirst` (not `db.select().from()`)
  - Provides object-style `where`, built-in `with` for relations, and `columns` selection
- Avoid raw SQL — use query builder APIs instead of `sql` template literals
- Mutations: `db.insert()`, `db.update()`, `db.delete()`
- Always upsert with `.onConflictDoUpdate()` on insertions
- External-source PKs: `integer('id').primaryKey()` (not `serial`)
- Never edit Drizzle meta files (`snapshot.json`, `_journal.json`) — they're generated by `drizzle-kit`

## tRPC

- Routers live in `src/server/api/routers/`
- Zod v4 for input validation
- Client hooks from `src/app/trpc.ts`
- Invalidate queries after mutations

## Integrations

Canonical guide: `INTEGRATIONS.md`.

- Syncs are Effect v4 pipelines registered in `src/server/integrations/runtime/registry.ts`; the shared runtime (`src/server/integrations/runtime/`) provides run-row tracking, typed errors, rate-limited HTTP clients, debug capture, and the `ManagedRuntime` boundary the CLI calls through
- File convention: `types.ts` (Zod schemas at the API boundary), `sync.ts` (Effect pipeline), `map.ts` (staging-to-record promotion, plain async invoked via `legacyOperation`)
- Item failures collect into the run summary via `forEachCollect` — one bad item never fails a run; only systemic errors (auth, config, connectivity) do
- Rate limits come from `makeApiClient` (fixed-window limiter, auto-429 with Retry-After, bounded transient retries); upsert for idempotency
- `effect` and `@effect/platform-bun` are exact-pinned to the same v4 version — bump both together and deliberately. Import HTTP and persistence modules from `effect/http` and `effect/persistence`; APIs tagged `@stability unstable` may still break in minor releases, and `@stability experimental` APIs may break in patch releases

## Database Management

Canonical operations: `README.md` and `src/server/db/db-manager.sh`. Migration workflow, including the Zero publication steps: `AGENTS.md` (Database Migrations).

- `bun run db:generate` to generate migration files
- `bun run db:studio` for database inspection
- Apply and verify every migration against dev before handoff. Never migrate prod: merging to main deploys migrations
- Never run destructive operations without explicit user permission
- Backups are named by environment (`prod-{timestamp}.dump`, `dev-{timestamp}.dump`), not database name

## Agent Workflow: Media Alt Text

The `rcr media` commands support CLI-based alt text updates. Workflow for looped agents:

1. **List** images needing alt text:
   ```bash
   rcr media list --type=image --alt-text=false --limit=100 --order=recordCreatedAt
   ```
2. **Get** item with parent record context:
   ```bash
   rcr media get <id> --with-record
   ```
   Returns media item plus `record: { id, title, type, mediaCaption, url }`.
3. **Update**:
   ```bash
   rcr media update <id> '{"altText": "Description of the image"}'
   ```

**Parallel processing**: pre-assign media IDs per agent to avoid overlap. Parent fetches a batch, splits into chunks, each subagent processes its assigned IDs.

Schema: `media` table has `altText` (nullable), `url`, `type`, `width`, `height`, `recordId` (FK). ~4,090 images total.