# Database table references exist in schema

- **Pattern:** `ab-000034` (`ai-slop-hallucinations.data-references.db-models-in-schema`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-20
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000034
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000034)

## Why it matters

A hallucinated table name is one of the clearest markers of AI-generated database code: the model invents a plausible name (`userProfile`, `user_profiles`) that differs from the actual schema (`users`, `profiles`). Prisma throws `Cannot read property 'findMany' of undefined` at runtime — a hard crash. Supabase returns a PostgREST 404 or an empty result set, which can silently suppress data that the application treats as authoritative. In both cases the failure mode is a runtime data-layer outage tied directly to reference-integrity drift (CWE-1188, OWASP A08: Software and Data Integrity Failures). The mismatch is undetectable at build time in Supabase projects unless type generation is wired into CI.

## Severity rationale

Critical because a single unresolved table reference crashes the affected code path at runtime — Prisma hard-throws, Supabase silently returns no data — with no compile-time safety net in most stacks.

## Remediation

Fix each hallucinated table name to exactly match the schema definition. Prisma model names are camelCase client accessors derived from PascalCase model names; Supabase table names are the literal `CREATE TABLE` identifier (typically snake_case).

```ts
// Prisma — model 'User' in schema.prisma → prisma.user (camelCase)
await prisma.userProfile.findMany()  // Bad: 'userProfile' not in schema
await prisma.user.findMany()         // Good

// Supabase — must match CREATE TABLE name in migrations exactly
await supabase.from('user_profiles').select('*')  // Bad: table is 'profiles'
await supabase.from('profiles').select('*')        // Good
```

For Supabase projects, run `supabase gen types typescript --local > src/database.types.ts` and use the generated `Database` type with the client to get compile-time table name checking.

## Detection

- **ID:** `db-models-in-schema`
- **Severity:** `critical`
- **What to look for:** This check validates that every database table/model reference in code matches a table defined in the schema. It covers TWO paths depending on the project's database access pattern:

  **Path A — ORM projects (Prisma, Drizzle, TypeORM, Sequelize, Mongoose, Kysely):** Read the schema file(s) and build `KNOWN_TABLES` from defined model/table names. Walk source files for ORM query patterns: `prisma.X.findMany`, `prisma.X.create`, `prisma.X.update`, `prisma.X.delete`, `prisma.X.upsert`, `prisma.X.findUnique`, `prisma.X.findFirst`, `prisma.X.count`, `prisma.X.aggregate`, `db.X.findMany`, `db.select().from(X)`, `db.insert(X)`, `db.update(X)`, `db.delete(X)`, `repository.find()` (TypeORM), `Model.find()` (Mongoose), `Model.findOne()`. Extract the model name `X` from each call.

  **Path B — Supabase projects (`@supabase/supabase-js` in dependencies):** Build `KNOWN_TABLES` from migration SQL files (see "How to Analyze" step 8 for parsing instructions) or from a generated `database.types.ts`. Walk source files for `.from('tablename')` and `.from("tablename")` calls on the Supabase client. Extract the table name string literal. SKIP Supabase reserved schema tables — any `.from()` call where the argument starts with `auth.`, `storage.`, `realtime.`, `extensions.`, or `vault.` (these are Supabase-managed tables not defined in user migrations).

  For both paths: before evaluating, extract and quote the table/model name from each call site, then verify each name exists in `KNOWN_TABLES`. Count all table references inspected, total resolved, total unresolved.
- **Detector snippet (Node-capable tools only):** If the tool has shell + Node, extract model names from `prisma/*.prisma` files and CREATE TABLE statements in `supabase/**/*.sql`, then scan src for `.from('...')` calls and compare. Output reports refs, missing count, and the offending table names. If exit 0 with "missing=0", report pass. If "missing>0", report fail. Exit >=2 or command not found — fall back to prose reasoning below.

  ```detect-node
  node -e 'const fs=require("fs"),path=require("path");function w(d,o=[]){try{for(const e of fs.readdirSync(d,{withFileTypes:true})){if(e.name==="node_modules"||e.name===".next")continue;const p=path.join(d,e.name);e.isDirectory()?w(p,o):o.push(p)}}catch{}return o}let models=new Set();for(const f of w("prisma")){if(!/\.prisma$/.test(f))continue;const s=fs.readFileSync(f,"utf-8");for(const m of s.matchAll(/^model\s+(\w+)/gm))models.add(m[1].toLowerCase())}for(const f of w("supabase")){if(!/\.sql$/.test(f))continue;const s=fs.readFileSync(f,"utf-8");for(const m of s.matchAll(/create\s+table\s+(?:if\s+not\s+exists\s+)?[\u0022\u0060]?(\w+)/gi))models.add(m[1].toLowerCase())}let refs=0,missing=[];for(const f of w("src")){if(!/\.(ts|tsx|js|jsx)$/.test(f))continue;const s=fs.readFileSync(f,"utf-8");for(const m of s.matchAll(/\.from\([\u0022\u0027]([^\u0022\u0027]+)[\u0022\u0027]\)/g)){refs++;if(!models.has(m[1].toLowerCase()))missing.push(f+":"+m[1])}}console.log("refs="+refs+" missing="+missing.length+" models="+models.size);missing.slice(0,3).forEach(x=>console.log(x))'
  ```

- **Pass criteria:** 100% of database table references resolve to a defined table in the schema. Report: "X table references inspected (source: [Prisma schema / Supabase migrations / ...]), Y resolved, 0 unresolved."
- **Fail criteria:** At least 1 database call references a table that is not defined in the schema.
- **Do NOT pass when:** The table name is dynamically computed (`prisma[modelName]` or `supabase.from(tableName)` where `tableName` is a variable) AND no fallback static reference exists — flag as a "dynamic table access" issue with detail.
- **Skip (N/A) when:** No ORM is detected (none of `prisma`, `drizzle-orm`, `typeorm`, `sequelize`, `mongoose`, `kysely` in dependencies) AND no `@supabase/supabase-js` in dependencies AND no raw SQL client (`pg`, `mysql2`, `better-sqlite3`) in dependencies.
- **Cross-reference:** For deeper schema design analysis, the Database Design & Operations audit (`database-design-operations`) covers index strategy, normalization, and migration safety.
- **Detail on fail:** `"3 unresolved table references: prisma.userProfile.findMany() in src/app/profile/page.tsx (model 'userProfile' not in schema.prisma), supabase.from('user_profiles') in src/lib/db.ts (table 'user_profiles' not in migrations — closest match: 'profiles')"`
- **Remediation:** Hallucinated table references cause runtime errors — Prisma throws `Cannot read property 'findMany' of undefined`, Supabase returns a 404 or empty result with a misleading error. Fix each unresolved reference:

  ```ts
  // Prisma: model name doesn't match schema
  await prisma.userProfile.findMany()  // Bad
  await prisma.user.findMany()         // Good: matches schema.prisma

  // Supabase: table name doesn't match migrations
  await supabase.from('user_profiles').select('*')  // Bad
  await supabase.from('profiles').select('*')        // Good: matches CREATE TABLE
  ```

  Prisma model names are case-sensitive — `User` in the schema becomes `prisma.user` (camelCased). Supabase table names must exactly match the `CREATE TABLE` name in migrations (usually lowercase with underscores).

## External references

- cwe CWE-1188
- owasp A08

Taxons: reference-integrity

HTML version: https://auditbuffet.com/patterns/ab-000034