Database column references exist in schema

A hallucinated column name is subtler than a hallucinated table name and more dangerous in Supabase projects: Prisma's TypeScript client will flag unknown field names at compile time if types are regenerated, but Supabase's PostgREST API silently ignores an unrecognized column in .select() — returning a result set with the column omitted and no error. An unrecognized column in .eq('avatarUrl', ...) makes the filter a no-op, so the query returns all rows instead of the intended subset. This is an OWASP A08 data-integrity failure that can expose records to the wrong user if the broken filter was access-gating data.

Match every column reference exactly to the column name in the schema. SQL column names are case-sensitive in comparisons and typically snake_case; AI-generated code frequently uses camelCase by habit.

// Prisma
await prisma.user.findMany({ where: { emailAddress: 'a@b.com' } })  // Bad
await prisma.user.findMany({ where: { email: 'a@b.com' } })          // Good

// Supabase — wrong casing makes the filter a silent no-op
await supabase.from('profiles').select('avatarUrl')   // Bad
await supabase.from('profiles').select('avatar_url')  // Good

await supabase.from('profiles').eq('displayName', 'Alice')   // Bad: filter ignored
await supabase.from('profiles').eq('display_name', 'Alice')  // Good

For Supabase projects, generate TypeScript types with supabase gen types typescript and pass them to the client constructor — column name typos then fail at compile time rather than silently at runtime.

• ID: db-columns-in-schema

• Severity: high

• What to look for: Build a map of TABLE → set of column/field names from the schema (same source as the previous check). Then walk source files for column references:

  Path A — ORM projects: For Prisma: select: { X: true }, where: { X: ... }, data: { X: ... }, orderBy: { X: ... }, include: { X: ... }. For Drizzle: .select({ X: table.column }), eq(table.column, value), .from(table).where(...). For Kysely: .selectFrom('X').select('Y'). SKIP relation names that resolve to other models (Prisma include: { posts: true } where posts is a relation).

  Path B — Supabase projects: Extract column references from these patterns: .select('col1, col2') — split on comma, trim whitespace, SKIP * (select all), SKIP relation syntax where the name is followed by (...) (e.g., author(*) or posts(id, title) — these are PostgREST relation fetches, not column names). .eq('col', val), .neq('col', val), .gt('col', val), .gte('col', val), .lt('col', val), .lte('col', val), .in('col', [...]), .contains('col', val), .order('col') — the first string argument is the column name. .insert({col: val}), .update({col: val}), .upsert({col: val}) — object keys are column names. SKIP dynamically constructed query objects where column names come from spread operators (...userData) or computed keys.

  For both paths: SKIP JSON/JSONB column path expressions — any column reference containing -> or ->> (e.g., .eq('metadata->plan', 'pro') or Prisma metadata: { path: ['plan'], equals: 'pro' }). Only validate the base column name before the first ->. SKIP Supabase reserved schemas. Count all column references inspected, total resolved, total unresolved.

• Pass criteria: 100% of column references resolve to a defined column in the corresponding table. Report: "X column references inspected, Y resolved, 0 unresolved."

• Fail criteria: At least 1 column reference does not exist in the table definition.

• Skip (N/A) when: No ORM detected AND no @supabase/supabase-js in dependencies, OR no schema/migration files present, OR the previous check (db-models-in-schema) reported 0 table references.

• Detail on fail: "4 unresolved column references: prisma.user.findMany({ where: { emailAddress: ... } }) — User model has 'email', not 'emailAddress'. supabase.from('profiles').select('display_name, avatarUrl') — 'profiles' table has 'avatar_url', not 'avatarUrl'."

• Remediation: Column reference hallucinations are caught by Prisma's TypeScript types at build time IF you use the generated client. Supabase has NO compile-time column checking — typos silently return empty results or ignored filters. Fix each reference to match the schema:

  // Prisma: column doesn't match schema
  await prisma.user.findMany({ where: { emailAddress: 'a@b.com' } })  // Bad
  await prisma.user.findMany({ where: { email: 'a@b.com' } })          // Good
  
  // Supabase: column doesn't match migration
  await supabase.from('profiles').select('avatarUrl')   // Bad (camelCase)
  await supabase.from('profiles').select('avatar_url')   // Good (snake_case from SQL)
  

  For Supabase projects, consider generating TypeScript types with supabase gen types typescript to get compile-time column checking.