Shared database layer for AgentBadge — Prisma ORM 8 contract-based client, repository abstraction, and in-memory fallback. Consumers call createDatabase() — never touch Prisma directly.

Quickstart (clone → seeded DB in <5 min)

cd packages/database
bun install
bun run db:up                    # Postgres 17 in Docker, host port 5335
cp .env.example .env             # DATABASE_URL + DIRECT_URL → localhost:5335
bun run contract:emit            # regenerate contract.json/.d.ts (committed)
bun run db:init                  # bootstrap schema + sign marker
bun run db:seed                  # 3 sample events (idempotent)
bunx vitest run                  # unit tests (PG-backed when DATABASE_URL set)

Stop / inspect / wipe:

bun run db:down                  # stop container (named volume keeps data)
bun run db:logs                  # follow Postgres logs
docker compose down -v           # wipe data completely

Consumer guide

import { createDatabase } from "@agentbadge/database";

const database = createDatabase({
  enabled: config.database?.enabled ?? false, // DATABASE_ENABLED gate
  url: config.database?.url,                  // DATABASE_URL (pooled)
});

// database.db      — Prisma client facade, null when disabled
// database.events  — Store (EventRepository on PG, InMemoryStore otherwise)
// database.scanResults — ScanResultStore (ScanResultRepository │ InMemoryScanResultStore)
// database.chatSubscriptions — ChatSubscriptionStore (ChatSubscriptionRepository │ InMemoryChatSubscriptionStore)
// database.health()— SELECT 1 probe → boolean
// database.close() — call ONCE at shutdown, never per-request

enabled:false or missing url → in-memory store, db=null, health()→false, never throws. Server wiring lives in hackathon/server/src/server/lib/database.ts (lazy getDatabase() singleton); env section in src/config/env/database.ts.

Env topology: the package never reads env at runtime — the caller passes url. Server runtime uses hackathon/server/.env locally and Fly secrets in prod (DATABASE_ENABLED + DATABASE_URL only). This package's .env is for CLI/migrations/tests inside the package dir only and is never published (files: ["dist"]). DIRECT_URL (unpooled, migrations) stays local in .env.deployer — see RUNBOOK.md.

`Event` as a generic persisted-event table

Event is intentionally generic — new persisted event streams do NOT need their own model. Convention (EPIC-145):

type — the stream kind, e.g. "audit" (keeperhub audit events)
source — the producing subsystem, e.g. "keeperhub"
payload — the event JSON
Store.list({ type }) filters by stream kind (added in SLICE-145-4)

Dedicated models exist only where the shape is truly relational: ScanResult (latest-scan-per-domain lookups) and ChatSubscription (telegram chat ↔ username bindings, unique constraints + upsert).

Adding a new entity

Add the model to src/prisma/contract.prisma
bun run contract:emit — regenerate committed artifacts
bun run db:plan --name add_<entity> — writes migrations/app/<ts>_add_<entity>/
bun run db:migrate — apply (single tx + advisory lock on PG)
bun run db:verify — marker + schema match contract
Create src/repositories/<entity>-repository.ts copying EventRepository (extends Repository<T,TCreate,TUpdate>, implements the entity's Store interface)
Extend the Store contract + InMemoryStore if the entity needs an in-memory fallback; wire into createDatabase()
Tests: add the backend to the parity suite in tests/store.test.ts

Scripts

Script	Purpose
`bun run build`	`tsc` → `dist/`
`bun run typecheck`	`tsc --noEmit` over src + scripts + tests
`bun test` / `bunx vitest run`	unit tests (PG suites skip without `DATABASE_URL`)
`bun run db:up` / `db:down` / `db:logs`	dev Postgres lifecycle
`bun run contract:emit`	regenerate `contract.json` + `contract.d.ts`
`bun run db:init`	bootstrap DB schema + sign marker (fresh DB)
`bun run db:update`	dev-loop schema sync (destructive needs `--confirm <db>`)
`bun run db:plan --name <slug>`	write a reviewable migration package
`bun run db:migrate`	replay committed migrations (`--advance-ref db`)
`bun run db:verify`	check DB marker + live schema match contract
`bun run db:seed`	upsert 3 sample events (idempotent)

Contract workflow (Prisma ORM 8)

No prisma generate, no generated/ client folder. The contract is the source of truth; contract.json + contract.d.ts are committed to git (D16). Migrations are on-disk packages under migrations/app/ — replayed by db migrate, never invented at apply time. See RUNBOOK.md for prod.

Pinned RC versions (API breaks between RCs — do not upgrade casually): prisma@8.0.0-rc.15, @prisma/orm-postgres@8.0.0-rc.11, @prisma/cli-engine@0.4.0.

Gotchas

No db.close() in handlers — the pool is shared; close once at shutdown (server does this in the SIGTERM/SIGINT handler).
db is not exported from the package index — src/prisma/db.ts reads process.env.DATABASE_URL at module load; only scripts/seed.ts imports it directly. Consumers: createDatabase().
CONTRACT.MARKER_READ_FAILED / CONFIG.DB_CONNECTION_REQUIRED — DATABASE_URL/DIRECT_URL not set in the package's .env (CLI context).
Destructive ops — db update refuses without --confirm <database> (--yes does NOT grant consent). db migrate has no flag — review destructive ops in the plan output before applying.
Renames — no contract-level rename hint; planner sees drop+add. Hand-edit migration.ts after db:plan, or keep-then-drop in two migrations.
upsert conflictOn — pass the unique field as an object: conflictOn: { id }, not "id".
Health probe needs a column alias — SELECT 1 AS health (bare SELECT 1 yields ?column?, which fails the row spec).

Consuming the package

# local dev (monorepo) — already wired in hackathon/server:
#   "@agentbadge/database": "file:../../packages/database"
# after npm publish:
bun add @agentbadge/database

import { createDatabase } from "@agentbadge/database";

const database = createDatabase({
  enabled: process.env.DATABASE_ENABLED === "true",
  url: process.env.DATABASE_URL, // pooled postgres:// URL
});

await database.events.create({ type: "scan.completed", payload: { s: 92 } });
const up = await database.health(); // SELECT 1 probe
await database.close();             // once, at shutdown — never per-request

The package ships dist/ only (files: ["dist"]) — contract artifacts are compiled in, no prisma generate needed at install. It never reads env at runtime; the caller passes url explicitly.

Publishing to npm

# from repo root — "database" is in the PACKAGES list:
./scripts/publish-packages.sh patch --dry-run   # verify tarball contents
./scripts/publish-packages.sh patch             # real publish (needs npm login + OTP)

# then switch the consumer from file: to the published version:
#   hackathon/server/package.json:
#   "@agentbadge/database": "file:../../packages/database" → "^0.1.0"
cd hackathon/server && bun install
bunx vitest run tests/e2e/database.test.ts --config vitest.e2e.config.ts

Version bumps follow semver: patch = fixes, minor = new entity/store, major = CacheProvider/Store/Database interface breaks.

How it fits the system

hackathon/server (Hono)                Fly.io machine
  └─ createDatabase({enabled, url}) ──► Prisma Postgres
       ├─ events: Store (EventRepository │ InMemoryStore)
       ├─ scanResults: ScanResultStore (ScanResultRepository │ InMemoryScanResultStore)
       ├─ chatSubscriptions: ChatSubscriptionStore (ChatSubscriptionRepository │ InMemoryChatSubscriptionStore)
       ├─ health() → SELECT 1           (pooled DATABASE_URL)
       └─ close() on SIGTERM/SIGINT

packages/database (this repo)          local dev
  └─ docker compose: postgres:5335 + valkey:6336
  └─ migrations: contract.prisma → emit → plan → migrate
     (DIRECT_URL, local shell only — never on Fly)

Layout

src/ — package source (published via dist/)
src/prisma/ — contract artifacts (contract.json, contract.d.ts, db.ts dev client) — committed (D16)
src/repositories/ — EventRepository (canonical template), ScanResultRepository, ChatSubscriptionRepository
migrations/ — committed migration packages + snapshot store + db ref
tests/ — vitest suites (PG legs skip without DATABASE_URL)
scripts/seed.ts — idempotent seed
docker-compose.yml — local dev Postgres only; prod = managed Prisma Postgres (see RUNBOOK.md)