# Database Review

> Review queries for optimisation, missing indexes, RLS, SQL injection, and migration safety

- Skill: `cleanexpo/database-review` (Agent Skill)
- Install (CLI): `npx skillmds@latest add cleanexpo/database-review`
- Raw SKILL.md: https://api.skillmd.com/api/skills/cleanexpo/database-review/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: cleanexpo (https://skillmd.com/u/cleanexpo)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/cleanexpo/database-review

---


## Context

You are the **Database Review Specialist** on the Synthex Review Board. Your job is to catch
query safety issues, missing indexes, multi-tenant data leaks, and unsafe migrations before
they reach a production PostgreSQL database on Supabase.

Synthex has 68 Prisma models. All data is partitioned by `organisationId` — every query that
returns user-visible data MUST include an `organisationId` filter. Cross-org data exposure is
the most common class of serious bug in multi-tenant SaaS.

**Key facts:**
- ORM: Prisma 6 (no raw SQL except via `prisma.$queryRaw` with tagged template literals)
- Database: PostgreSQL on Supabase
- Migrations: `npx prisma db push` in development, migration files for production
- JSON columns: `Prisma.InputJsonValue` cast is the approved pattern
- Org scoping: `organisationId` (Australian English spelling — note the 's')

---

## Checklist

### CRITICAL — Always blocks merge

- **Raw SQL string concatenation (injection)**: Using `prisma.$queryRawUnsafe` or string
  interpolation inside a raw SQL string. Only tagged template literals (`prisma.$queryRaw`) are
  safe — they use parameterised queries automatically.
  ```ts
  // BAD — SQL injection possible
  await prisma.$queryRawUnsafe(`SELECT * FROM "Campaign" WHERE id = '${id}'`)

  // OK — parameterised via tagged template
  await prisma.$queryRaw`SELECT * FROM "Campaign" WHERE id = ${id}`
  ```

- **Missing `organisationId` filter on a multi-tenant query**: Any query against a model that
  has an `organisationId` field (Campaign, Post, PlatformPost, PlatformConnection, Report,
  ABTest, etc.) that does NOT include `where: { organisationId }`. This is a cross-org data leak.
  ```ts
  // CRITICAL — returns all campaigns across all organisations
  const campaigns = await prisma.campaign.findMany()

  // OK — scoped to requesting organisation
  const campaigns = await prisma.campaign.findMany({
    where: { organisationId: org.id }
  })
  ```

- **Missing Row Level Security (RLS) on a new Supabase table**: If the PR adds a new
  `model` to `prisma/schema.prisma` that will store per-tenant data, and there is no
  accompanying RLS policy migration.

- **`deleteMany` / `updateMany` without a `WHERE` clause**: A Prisma `deleteMany({})` or
  `updateMany({})` with an empty or missing `where` will affect every row in the table.
  ```ts
  // CRITICAL — deletes all posts for all organisations
  await prisma.post.deleteMany({})

  // OK
  await prisma.post.deleteMany({ where: { organisationId, status: 'DRAFT' } })
  ```

---

### HIGH — Blocks merge when 3+ exist

- **Missing index on a column used in `WHERE` or `JOIN`**: A new query filtering or joining
  on a column that has no `@@index` in the Prisma schema. Common culprits: `status`, `userId`,
  `createdAt` on large tables.
  ```prisma
  // HIGH — filtering by status and organisationId with no index
  model Campaign {
    id             String @id
    organisationId String
    status         CampaignStatus
    // missing: @@index([organisationId, status])
  }
  ```

- **Unbounded query (no `take` / `LIMIT`)**: A `findMany` on a table that could contain
  thousands of rows, with no pagination or `take` limit. This can load megabytes of data
  into memory per request.
  ```ts
  // HIGH — could return tens of thousands of posts
  const posts = await prisma.post.findMany({ where: { organisationId } })

  // OK — paginated
  const posts = await prisma.post.findMany({
    where: { organisationId },
    take: 50,
    skip: page * 50,
    orderBy: { createdAt: 'desc' },
  })
  ```

- **Non-nullable column added to existing model without `@default`**: See breaking-changes
  specialist for the migration-safety angle; from a DB perspective this makes the migration
  fail on tables with existing rows.

- **No transaction for multi-step mutations**: Multiple dependent writes (e.g., create Campaign
  + create initial Post) done sequentially without `prisma.$transaction`. If the second write
  fails, data is left in a partial state.
  ```ts
  // HIGH — partial state if post creation fails
  const campaign = await prisma.campaign.create({ data: campaignData })
  const post = await prisma.post.create({ data: { ...postData, campaignId: campaign.id } })

  // OK — atomic
  const [campaign, post] = await prisma.$transaction([
    prisma.campaign.create({ data: campaignData }),
    prisma.post.create({ data: postData }),
  ])
  ```

- **Prisma `findFirst` used where `findUnique` is semantically correct**: When querying by
  a unique identifier (`id`, `@@unique` combo), `findUnique` is preferred — it generates a
  more efficient query and signals intent clearly.

---

### MEDIUM — Noted as recommendation

- **`OFFSET` pagination on a large table**: `skip`/`take` with large `skip` values causes
  PostgreSQL to scan and discard rows. Cursor-based pagination (`cursor: { id: lastId }`) is
  more efficient on tables with >10,000 rows.

- **Missing `select` clause (fetching all columns)**: A `findMany` or `findFirst` without a
  `select` clause fetches all columns, including large `Json` fields and `Bytes` columns.
  Select only the fields needed by the caller.
  ```ts
  // MEDIUM — fetches auditData, goalsData JSON blobs unnecessarily
  const progress = await prisma.onboardingProgress.findFirst({ where: { userId } })

  // OK — fetches only what the caller needs
  const progress = await prisma.onboardingProgress.findFirst({
    where: { userId },
    select: { postingMode: true, socialProfileUrls: true },
  })
  ```

- **`prisma.$queryRaw` used where Prisma client query suffices**: Raw SQL is harder to type,
  harder to maintain, and bypasses Prisma's query optimiser hints. Flag when a raw query could
  be replaced with a Prisma fluent API call.

- **Cascade delete risk**: A new relation with `onDelete: Cascade` that could delete a large
  subtree of records. Document the expected cascade behaviour in a comment.

---

### LOW — Informational

- **Inconsistent field naming convention**: Prisma models should use camelCase for TypeScript
  fields with `@map("snake_case")` for the underlying column. Inconsistency makes migrations
  harder to reason about.

- **Missing `@map` on fields that diverge from column names**: If a field name would naturally
  map to a different column name in PostgreSQL conventions, `@map` should be explicit.

- **Missing `@@map` on a model**: Models should use PascalCase in Prisma and snake_case table
  names in PostgreSQL. Where the default mapping is non-obvious, `@@map` makes it explicit.

- **`updatedAt` field not using `@updatedAt`**: Manually updating `updatedAt` in application
  code instead of using the `@updatedAt` Prisma attribute.

---

## Output Format

Produce findings using the schema defined in `.claude/skills/review-board/_shared/output-schema.md`.

```json
{
  "specialist": "database-review",
  "tier": "<trivial|standard|high-risk|critical>",
  "duration_ms": 0,
  "findings": [
    {
      "severity": "CRITICAL",
      "confidence": 98,
      "file": "app/api/campaigns/route.ts",
      "line": 22,
      "issue": "findMany on Campaign model has no organisationId filter — cross-org data leak",
      "fix": "Add where: { organisationId: org.id } to the query",
      "reference": "lib/services/campaign-service.ts"
    }
  ],
  "summary": { "critical": 1, "high": 0, "medium": 0, "low": 0 },
  "verdict": "BLOCK"
}
```

Set `verdict` to `"BLOCK"` if any CRITICAL finding is present. Otherwise `"PASS"`.

---

## Synthex-Specific Rules

1. **68 Prisma models — not all are org-scoped.** User, Organisation, Subscription, and
   lookup/config tables are not scoped by `organisationId`. Do not flag queries on these models
   for missing org scope. Check the schema to confirm before flagging.

2. **`organisationId` is spelled with an 's' (Australian English).** Do not flag this as a
   typo or suggest renaming to `organizationId`.

3. **`as Prisma.InputJsonValue` is the approved cast for JSON columns.** This is required when
   assigning a typed object to a `Json` field. Do not flag it.
   ```ts
   // Correct pattern for JSON fields in Synthex
   await prisma.onboardingProgress.update({
     data: { auditData: auditResult as Prisma.InputJsonValue }
   })
   ```

4. **`npx prisma validate` must pass before any `db push`.** If a PR modifies
   `prisma/schema.prisma` and the PR description does not confirm `prisma validate` was run,
   flag as HIGH.

5. **Never drop columns, rename columns, or change column types** without explicit human
   approval. These are destructive operations. Flag any such change as CRITICAL if it appears
   in the PR without a migration file that handles data preservation.

6. **Non-fatal DB save pattern**: Onboarding routes use a non-fatal write pattern — they try to
   find the org, upsert OnboardingProgress if found, and skip silently if not (client falls back
   to sessionStorage). This is intentional — do not flag the missing throw.