Database

The project uses PostgreSQL (Neon in the cloud, postgres:16-alpine locally via Docker Compose). Schema ownership is strictly split: apps/server manages the schema via Prisma; apps/ai-service connects via asyncpg as a read/write client but never touches migrations.

Schema

Defined in apps/server/prisma/schema.prisma:

Model	Key fields
Thread	id (UUID PK), createdAt
Message	id, threadId (FK → Thread, cascade delete), role, content, name, toolCallId, toolCalls (JSONB), createdAt

Message has a composite index on (threadId, createdAt) for efficient history queries.

Valid role values: "human", "ai", "tool", "system".

Environments

Environment	Database
Local (Docker Compose)	postgres:16-alpine container
Development / staging	Neon dev branch
Production	Neon main branch

Each environment requires its own DATABASE_URL. Set it before running any migration command.

Migration workflow

Local (Docker Compose only)

Generate and apply a new migration:

pnpm --filter server db:migrate

Remote (Neon dev or prod)

Apply pending migrations without generating new ones:

DATABASE_URL=<neon-url> pnpm --filter server db:deploy

Never run db:migrate against a remote Neon branch. It can prompt to reset the database and wipe all data. Always use db:deploy for remote environments.

Prisma client (server)

Import the shared singleton from src/lib/prisma.ts:

import { prisma } from "../lib/prisma.js";

const thread = await prisma.thread.create({ data: {} });

asyncpg pool (AI service)

The Python service connects via the pool managed in app/database.py. The pool is created at startup and closed at shutdown — you never instantiate it directly.

from app.database import get_pool

pool = get_pool()
async with pool.acquire() as conn:
    rows = await conn.fetch("SELECT * FROM \"Message\" WHERE \"threadId\" = $1", thread_id)

The database.py module strips Prisma-style ?schema=public query params and normalises the scheme to postgres:// before passing the DSN to asyncpg.

Environment variables

apps/server/.env

DATABASE_URL=postgresql://peec:peec@localhost:5432/peec?schema=public

apps/ai-service/.env

DATABASE_URL=postgresql://peec:peec@localhost:5432/peec

The AI service omits ?schema=public — asyncpg does not support that query parameter.