🚨 CRITICAL: Database Schema Verification

Last Verified: 2025-07-03 (Neo4j migration update)
Database: Supabase Project gjzcqcxoacxkrchrzaen (Updated for Neo4j migration)
Method: Direct MCP query to production database

⚠️ NEVER ASSUME COLUMN NAMES - ALWAYS VERIFY

This document exists because we had a production bug where the resolver code used incorrect column names, causing SQL errors. The documentation showed different column names than what actually existed in the database.

🔍 Verified Production Schema

`object_instances` Table

-- ✅ VERIFIED COLUMNS (production database)
CREATE TABLE object_instances (
    id UUID PRIMARY KEY,
    neo4j_id TEXT NOT NULL UNIQUE,  -- ⚠️ CORRECT COLUMN NAME
    name TEXT NOT NULL,
    description TEXT,
    owner_organization_id UUID,
    created_by UUID,
    created_at TIMESTAMP WITH TIME ZONE,
    updated_at TIMESTAMP WITH TIME ZONE,
    status TEXT NOT NULL DEFAULT 'active',
    main_image_url TEXT,
    last_scanned_at TIMESTAMP WITH TIME ZONE,
    last_scanned_by UUID
);

`object_images` Table

-- ✅ VERIFIED COLUMNS (production database)
CREATE TABLE object_images (
    id UUID PRIMARY KEY,
    object_instance_id UUID,              -- ⚠️ NOT object_id!
    storage_path TEXT NOT NULL,
    public_url TEXT NOT NULL,             -- ⚠️ NOT image_url!
    is_main BOOLEAN DEFAULT FALSE,
    uploaded_by UUID,
    uploaded_at TIMESTAMP WITH TIME ZONE, -- ⚠️ NOT created_at!
    file_size INTEGER,
    content_type TEXT
);

🐛 The Bug That Happened

Code used: object_id, image_url, created_at
Actual columns: object_instance_id, public_url, uploaded_at

SQL Error:

(psycopg.errors.UndefinedColumn) column "object_id" does not exist

Root Cause: Documentation was outdated and didn’t match production schema.

🛡️ Prevention Rules

1. ALWAYS Verify Before Coding

Before writing any SQL queries or resolvers:

# Use MCP to verify actual column names
mcp_supabase_list_tables --project_id gjzcqcxoacxkrchrzaen --schemas public

2. Update This Document When Schema Changes

Any schema migration MUST update this verification document
Include the verification date and method
Mark old column names as deprecated with warnings

3. Code Review Checklist

Column names match this verification document
No hardcoded column names without verification
SQL queries tested against actual database schema

4. Documentation Synchronization

All schema docs MUST reference this verification document
Frontend docs MUST match backend reality
API documentation MUST reflect actual database structure

📋 Verification Checklist for Developers

When working with database queries:

I have verified column names against this document
I have tested my SQL against the actual database
I understand the difference between docs and reality
I will update this document if I find discrepancies

🔄 How to Re-verify Schema

Use MCP Supabase Tools:

# List all tables and their columns
mcp_supabase_list_tables --project_id gjzcqcxoacxkrchrzaen

Direct Database Query:

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'object_images' 
ORDER BY ordinal_position;

Update This Document with verification date and any changes found.

🚨 Red Flags - Never Trust These Sources Alone

❌ Old documentation files
❌ Comments in code (they go stale)
❌ Schema files in /docs (they might be outdated)
❌ Your memory of column names

✅ Trusted Sources

✅ This verification document (when recently updated)
✅ Direct MCP database queries
✅ Live database information_schema queries
✅ Production error messages (they don’t lie!)

Remember: The database is the source of truth, not the documentation. When in doubt, verify directly against the production schema.

Search Results: