Database table references exist in schema

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.

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).

// 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.

• 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.

  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:

  // 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).