convert/docs/superpowers/specs/2026-07-30-prisma-orm-migrations-design.md

Prisma ORM and DB migrations

Date: 2026-07-30

Goal

Replace the hand-rolled mariadb connection pool and raw SQL repository (src/db.js, src/jobs/jobRepository.js) with Prisma Client for data access, and Prisma Migrate for schema versioning. Today db/schema.sql is the sole source of truth and is applied by hand against local and o2switch production databases (see CLAUDE.md); there is no migration runner. This introduces one.

Scope

• src/db.js, src/jobs/jobRepository.js: full rewrite onto Prisma Client.
• src/app.js, src/server.js, src/worker.js, src/cleanup.js: renamed variable/import (pool → prisma), no logic changes — function signatures taking the client as first argument are preserved.
• New: prisma/schema.prisma, prisma/migrations/, scripts/printDatabaseUrl.js.
• Removed: db/schema.sql, the mariadb dependency.
• Test files that currently talk to the raw pool: test/db.test.js, test/jobs/jobRepository.test.js, test/cleanup.test.js, test/worker.test.js, test/api/jobs.test.js, test/api/jobStatus.test.js, test/api/endToEnd.test.js.
• Out of scope: any change to the conversion_jobs columns themselves (this is a data-access-layer migration, not a schema change), frontend, converters.

Schema

prisma/schema.prisma reproduces db/schema.sql column-for-column, so the initial migration is a no-op against existing databases:

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

enum JobStatus {
  pending
  processing
  done
  failed
}

model ConversionJob {
  id                        Int       @id @default(autoincrement()) @db.UnsignedInt
  uuid                      String    @unique @db.Char(36)
  status                    JobStatus @default(pending)
  family                    String    @db.VarChar(32)
  sourceFormat              String    @map("source_format") @db.VarChar(16)
  targetFormat              String    @map("target_format") @db.VarChar(16)
  originalFilename          String    @map("original_filename") @db.VarChar(255)
  inputPath                 String    @map("input_path") @db.VarChar(255)
  outputPath                String?   @map("output_path") @db.VarChar(255)
  inputMimeType             String    @map("input_mime_type") @db.VarChar(128)
  outputMimeType            String?   @map("output_mime_type") @db.VarChar(128)
  inputSizeBytes            Int       @map("input_size_bytes") @db.UnsignedInt
  outputSizeBytes           Int?      @map("output_size_bytes") @db.UnsignedInt
  quality                   Int?      @db.UnsignedSmallInt
  conversionDurationSeconds Decimal?  @map("conversion_duration_seconds") @db.Decimal(10, 3)
  errorMessage              String?   @map("error_message") @db.VarChar(255)
  errorLog                  String?   @map("error_log") @db.Text
  createdAt                 DateTime  @default(now()) @map("created_at")
  updatedAt                 DateTime  @default(now()) @updatedAt @map("updated_at")
  expiresAt                 DateTime  @map("expires_at")
  cleanedAt                 DateTime? @map("cleaned_at")

  @@index([status])
  @@index([expiresAt])
  @@map("conversion_jobs")
}

Prisma's mysql provider is protocol-compatible with MariaDB for all types used here (ENUM, DECIMAL, CHAR, DATETIME, unsigned integers).

Migration baseline (no data loss)

Local and production databases already have conversion_jobs created by hand via db/schema.sql. To adopt Prisma Migrate without dropping or recreating that table:

1. Generate the initial migration without applying it: npx prisma migrate dev --name init --create-only (with DATABASE_URL set — see "Running Prisma CLI locally" below). This produces prisma/migrations/<timestamp>_init/migration.sql, hand-verified to match db/schema.sql.
2. On every environment that already has the table (local dev DB, o2switch production): npx prisma migrate resolve --applied <timestamp>_init instead of running the migration. This records the migration as applied in Prisma's _prisma_migrations tracking table without touching conversion_jobs or its data.
3. Delete db/schema.sql — prisma/schema.prisma and prisma/migrations/ become the source of truth going forward.

Future schema changes: edit schema.prisma, run npx prisma migrate dev --name <description> locally (generates SQL, applies it to the local DB), commit the generated migration folder, and run npx prisma migrate deploy in production (applies pending migrations, no interactive prompts).

Running Prisma CLI locally

Prisma CLI commands (migrate dev, migrate deploy, generate) need DATABASE_URL in their own process environment, independent of the app. Per CLAUDE.md, .env holds production credentials and must never be loaded for local work, and there is no dotenv wiring that reads .env.local automatically — the existing convention is to pass local DB vars inline. This design keeps that convention: DATABASE_URL is never written into .env or .env.local; it is always computed on demand.

src/db.js exports buildDatabaseUrl(config):

export function buildDatabaseUrl(config) {
  const { host, user, password, database } = config.db;
  return `mysql://${encodeURIComponent(user)}:${encodeURIComponent(password)}@${host}:3306/${database}?connection_limit=10`;
}

?connection_limit=10 preserves the current pool's connectionLimit: 10 behavior under Prisma's own internal connection pool.

scripts/printDatabaseUrl.js reuses it so the URL is computed in exactly one place:

import { loadConfig } from '../src/config.js';
import { buildDatabaseUrl } from '../src/db.js';

console.log(buildDatabaseUrl(loadConfig()));

Local usage, matching the inline-env-var pattern already documented in CLAUDE.md:

DB_HOST=127.0.0.1 DB_USER=convert_user DB_PASSWORD=change_me DB_NAME=file_converter STORAGE_DIR=./storage \
  DATABASE_URL=$(node scripts/printDatabaseUrl.js) npx prisma migrate dev

Production deploy: with the real DB_* variables loaded from .env, run the same DATABASE_URL=$(node scripts/printDatabaseUrl.js) npx prisma migrate deploy before starting the app.

Data access layer

src/db.js: getPool/closePool become getPrismaClient/closePrismaClient. getPrismaClient(config) sets process.env.DATABASE_URL = buildDatabaseUrl(config) before constructing the singleton PrismaClient; closePrismaClient() calls $disconnect().

src/jobs/jobRepository.js is rewritten onto Prisma's model API. Function names and signatures ((prisma, ...) as first argument) are unchanged, so call sites in src/app.js and src/worker.js only need the pool → prisma rename, not a logic change. The manual toCamelJob mapping is deleted: Prisma returns objects already shaped with the model's camelCase field names, with @map handling the snake_case column translation internally.

getJobByUuid, findPendingJobs, and findExpiredJobs must not expose errorLog (only getJobErrorLog does, matching today's explicit SELECT column lists). This is preserved with a shared select object:

const jobSelect = {
  id: true, uuid: true, status: true, family: true, sourceFormat: true, targetFormat: true,
  originalFilename: true, inputPath: true, outputPath: true, inputMimeType: true, outputMimeType: true,
  inputSizeBytes: true, outputSizeBytes: true, quality: true, conversionDurationSeconds: true,
  errorMessage: true, createdAt: true, updatedAt: true, expiresAt: true, cleanedAt: true,
};

Function mapping:

Function	Prisma call
createJob(prisma, job)	prisma.conversionJob.create({ data: { uuid, family, sourceFormat, targetFormat, originalFilename, inputPath, inputMimeType, inputSizeBytes, expiresAt, quality: job.quality ?? null } })
getJobByUuid(prisma, uuid)	prisma.conversionJob.findUnique({ where: { uuid }, select: jobSelect })
getJobErrorLog(prisma, id)	prisma.conversionJob.findUnique({ where: { id }, select: { errorLog: true } }) → ?.errorLog ?? null
markProcessing(prisma, id)	prisma.conversionJob.update({ where: { id }, data: { status: 'processing' } })
markDone(prisma, id, {...})	update with status: 'done', outputPath, outputMimeType, outputSizeBytes, conversionDurationSeconds
markFailed(prisma, id, {...})	update with status: 'failed', errorMessage, errorLog
findPendingJobs(prisma, limit)	prisma.conversionJob.findMany({ where: { status: 'pending' }, orderBy: { createdAt: 'asc' }, take: limit, select: jobSelect })
findExpiredJobs(prisma)	prisma.conversionJob.findMany({ where: { expiresAt: { lt: new Date() }, cleanedAt: null }, select: jobSelect })
markCleaned(prisma, id)	prisma.conversionJob.update({ where: { id }, data: { cleanedAt: new Date() } })

Behavior change to watch for

findExpiredJobs and markCleaned currently compare/write using MariaDB's NOW(), computed on the database server. Under Prisma they use new Date(), computed on the application server. This is the one real behavior change introduced by this migration. CLAUDE.md already documents two pre-existing test failures tied to a clock/timezone mismatch around expiresAt comparisons (test/cleanup.test.js, test/jobs/jobRepository.test.js), not yet root-caused. After implementation, verify no new failures appear beyond those two — this change is not expected to fix or worsen them, but it changes where the clock read happens, so it must be checked rather than assumed neutral.

Package changes

• package.json dependencies: remove mariadb, add @prisma/client.
• package.json devDependencies: add prisma.
• package.json scripts: add "postinstall": "prisma generate" so the generated client exists after npm install on fresh clones, CI, and deploy.
• db/schema.sql deleted.

Test updates

All test files importing getPool/closePool switch to getPrismaClient/closePrismaClient, and pool.query('DELETE FROM conversion_jobs') in beforeEach blocks becomes prisma.conversionJob.deleteMany().

Two special cases:

• test/db.test.js: the raw-connectivity check pool.query('SELECT 1 AS value') becomes prisma.$queryRaw`SELECT 1 AS value`. The singleton-instance assertion (getPool called twice returns the same object) carries over unchanged to getPrismaClient.
• test/api/jobStatus.test.js ("returns a generic 500 without leaking internal error details"): currently monkey-patches pool.query to reject, since that endpoint's code path (getJobByUuid) went through a single .query method. Under Prisma there is no single method to patch — the test instead monkey-patches prisma.conversionJob.findUnique (the specific model method getJobByUuid calls), saving and restoring the original function around the assertion, same pattern as today.

Verification

• Run the test suite with the local env vars documented in CLAUDE.md and confirm the only failures are the two pre-existing, already-documented ones (or fewer) — no new regressions.
• Run npm run build to confirm the frontend build step is unaffected (it has no dependency on the DB layer).
• Manually run the baseline steps (migrate dev --create-only + migrate resolve --applied) against the local dev DB and confirm conversion_jobs and its existing rows are untouched.