@derwinjs/db

Prisma schema + migrations for Derwin's own Postgres. 14 models, project-namespaced. Per ADR-0005.

This package owns the platform's persistence layer. Consumer DBs (Lifeline's Postgres, Side Piece's Postgres) are NOT shared with this — Derwin reads from them via Ticket / Document adapters; it stores its own state here.

Quick start

# 1. Set up Postgres locally (one option: Docker)
docker run -d --name derwin-pg \
  -e POSTGRES_USER=derwin \
  -e POSTGRES_PASSWORD=derwin \
  -e POSTGRES_DB=derwin \
  -p 5432:5432 \
  postgres:16

# 2. Configure connection
cp packages/db/.env.example packages/db/.env
# Edit DATABASE_URL if needed

# 3. Generate Prisma client + apply migrations
pnpm --filter @derwinjs/db prisma:generate
pnpm --filter @derwinjs/db prisma:migrate

# 4. Seed dev fixture (Lifeline project + Profile + default Policy)
pnpm --filter @derwinjs/db db:seed

After seeding, you can connect with psql and inspect:

docker exec -it derwin-pg psql -U derwin -d derwin -c "SELECT slug, type, mode FROM projects;"

Expected: one row, lifeline | ERP | AUTHOR.

Production: Supabase

Per ADR-0005, the production deployment uses Supabase (parity with Lifeline). Connection string format:

postgresql://postgres:[PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres

The pgvector extension gets enabled in QAP-036 (Sprint 3 — DB deployment). The RAGCorpus.embedding column is Bytes? for v1; QAP-076 (Sprint 7) switches it to vector(1536) once pgvector is available.

Schema conventions

• Project-namespaced. Every project-scoped table has a projectId FK to Project. Cross-project leakage is the leading regression risk; queries MUST filter on projectId.
• Cascading deletes. Deleting a Project cleanly removes all project-scoped data (cascades through 12 of 14 models).
• Append-only logs. ProfileEvolutionLog, ProjectModeLog, SpendLedger are append-only — never updated, never deleted (compliance requirement per product brief §11.3).
• Audit retention. AuditArtifact.retentionUntil is computed at write time from project compliance mode; a separate retention cron prunes expired artifacts (lands in Sprint 7).
• Indices on every read pattern. Per technical spec §3 — (projectId, status), (projectId, finalBucket), (projectId, createdAt DESC), etc.

Models (14)

| # | Model | Purpose | Layer | | --- | --------------------- | --------------------------------------------------------- | ------------- | | 1 | Project | Multi-tenant root | A (Profile) | | 2 | ProjectProfile | Domain ontology, risk weights, critical flows, glossary | A | | 3 | IngestedDoc | Source docs that fed the Profile | A | | 4 | ProfileEvolutionLog | Append-only audit of every Profile change | A | | 5 | QARun | One discovery pass | B/C | | 6 | RawSignal | What a Discovery route emits before triage | B/C | | 7 | QATicket | The structured bug ticket | D | | 8 | QAFixAttempt | One Claude dispatch + outcome | E | | 9 | ClassificationTrust | Per-classification rolling 30-day trust score | F | | 10 | RAGCorpus | Successful past fixes for in-context examples | F | | 11 | AuditArtifact | Persistent evidence (screenshots, videos, prompts, diffs) | cross-cutting | | 12 | Policy | Risk-tier rules, classification overrides, thresholds | safety | | 13 | ProjectModeLog | Append-only mode-change audit | safety | | 14 | SpendLedger | Per-event $ spend (Anthropic, storage, etc.) | cost |

Detail: see prisma/schema.prisma and technical spec §3.

Status

Scaffolded in QAP-005 (Sprint 1). Schema is complete; the Prisma client gets imported by @derwinjs/core and @derwinjs/api starting in Sprint 2.