🚨 CRITICAL: Database Schema Verification

🚨 CRITICAL: Database Schema Verification

Last Verified: Fri 12 Jun 2026 14:48:38 CEST (RLS enabled on organizations & organization_members — see Row-Level Security note below)
Updated: Mon 15 Jun 2026 17:21:39 CEST - Added last_scanned_latitude and last_scanned_longitude columns to object_instances (migration add_scan_geolocation_columns, applied Jun 2026)
Updated: Sat 04 Jul 2026 09:26 CEST - Added default_organization_id uuid NULL to profiles (FK organizations(id) ON DELETE SET NULL; migration add_profiles_default_organization_id, applied Jul 2026) — per-user default organisation (ADR-0066). RLS unchanged (self INSERT/UPDATE); the API writes it via its BYPASSRLS connection, membership-gated in the setDefaultOrganization resolver.
Updated: Mon 06 Jul 2026 22:30 CEST - Added platsankare columns to object_instances: anchor_lat/anchor_lng DOUBLE PRECISION, anchor_provenance TEXT CHECK (manual|auto), anchor_set_at TIMESTAMPTZ (migration 2026-07-06-add-anchor-columns, applied Jul 2026) — kartnavigering slice 1 (Plings-API #103; ADR-0073). Derived positions are never stored — the anchor is the only new persisted position concept.
Database: Supabase Project gjzcqcxoacxkrchrzaen
Method: Direct MCP query to production database (information_schema.columns)

⚠️ 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, 2026-06-11)
-- ⚠️ neo4j_id was REMOVED by the unified-ID migration (July 2025) — do not reference it
CREATE TABLE object_instances (
    id UUID PRIMARY KEY,             -- same UUID in PostgreSQL and Neo4j (unified ID)
    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,
    last_scanned_at TIMESTAMP WITH TIME ZONE,
    last_scanned_by UUID,                    -- FK auth.users
    last_scanned_latitude DOUBLE PRECISION,  -- nullable; added by migration add_scan_geolocation_columns
    last_scanned_longitude DOUBLE PRECISION, -- nullable; added by migration add_scan_geolocation_columns
    anchor_lat DOUBLE PRECISION,             -- nullable; platsankare ("hit hör den"), migration 2026-07-06-add-anchor-columns
    anchor_lng DOUBLE PRECISION,             -- nullable; platsankare
    anchor_provenance TEXT CHECK (anchor_provenance IN ('manual','auto')),  -- nullable
    anchor_set_at TIMESTAMP WITH TIME ZONE,  -- nullable; signalens färskhet
    short_code TEXT,
    location TEXT,
    type TEXT,
    deleted_at TIMESTAMP WITH TIME ZONE  -- soft delete support
);

HD Wallet Registry Tables (added Thu 11 Jun 2026)

Created by migrations/create_hd_wallet_path_registry.sql (adapted at application time: user-FK constraints omitted — public.users does not exist; allocated_by/registered_by are plain UUIDs. RLS enabled deny-all on all four tables; the API’s direct Postgres connection is unaffected).

-- ✅ VERIFIED (production database, 2026-06-11)
wallet_versions       (version_id INTEGER PK, version_name VARCHAR UNIQUE, master_key_id VARCHAR,
                       description TEXT, environment VARCHAR, security_level VARCHAR, status VARCHAR,
                       created_at TIMESTAMP, compromised_at TIMESTAMP, retired_at TIMESTAMP,
                       is_default BOOLEAN)  -- partial unique index enforces single default

path_registry         (id UUID PK, wallet_version INTEGER FK→wallet_versions, path VARCHAR,
                       hd_derivation VARCHAR, allocation_type VARCHAR, manufacturer_name VARCHAR,
                       batch_name VARCHAR, quantity INTEGER, purpose TEXT, status VARCHAR,
                       allocated_by UUID, allocated_at TIMESTAMP, completed_at TIMESTAMP,
                       migrated_to_wallet INTEGER, migrated_at TIMESTAMP)
                      -- UNIQUE (wallet_version, path); trigger prevents parent/child path overlap

manufacturer_registry (id UUID PK, wallet_version INTEGER FK, manufacturer_index INTEGER,
                       manufacturer_name VARCHAR, organization_id UUID FK→organizations,
                       public_key TEXT, key_delegation_level VARCHAR, status VARCHAR,
                       registered_by UUID, registered_at TIMESTAMP,
                       migrated_from_wallet INTEGER, migration_completed_at TIMESTAMP)
                      -- UNIQUE (wallet_version, manufacturer_index) and (wallet_version, manufacturer_name)

manufacturer_lineage  (id UUID PK, canonical_name VARCHAR UNIQUE, current_wallet_version INTEGER,
                       current_manufacturer_index INTEGER, predecessor_wallet INTEGER,
                       predecessor_index INTEGER, migration_reason VARCHAR, migration_date TIMESTAMP,
                       migration_status VARCHAR, migration_completion_percentage INTEGER)

Seed state: wallet v1 (default) · manufacturer Plings (index 1) · 4 category path allocations (1.1.C1 generic stickers, 1.2.C1 generic etched, 1.3.C1 test batches, 1.4.C1 manufacturer orders).

object_images Table

-- ✅ VERIFIED COLUMNS (production database)
CREATE TABLE object_images (
    id UUID PRIMARY KEY,
    object_instance_id UUID,              -- ⚠️ NOT object_id! — nullable since the polymorphic change
    object_class_ref TEXT,                -- polymorphic owner: a class image (ADR-0057); XOR with object_instance_id
    storage_path TEXT NOT NULL,
    public_url TEXT NOT NULL,             -- ⚠️ NOT image_url! — the full-frame ORIGINAL / identity
    is_main BOOLEAN DEFAULT FALSE,        -- cover flag; in sync with position 0
    position INTEGER,                     -- ordered photos: 0 = cover (add_object_images_position)
    uploaded_by UUID,
    uploaded_at TIMESTAMP WITH TIME ZONE, -- ⚠️ NOT created_at!
    file_size INTEGER,
    content_type TEXT,
    -- Background-removal cutout (derived display variant — add_object_images_processed)
    processed_url TEXT,                   -- Photoroom cutout (white bg + soft shadow); NULL until processed
    processed_storage_path TEXT,          -- cutout storage path (for delete/cascade)
    processing_status TEXT DEFAULT 'none',-- none | pending | done | failed
    processing_meta JSONB,                -- provider/presets/cost/bytes
    -- Non-destructive square crop (derived display variant — add_object_images_crop_nondestructive)
    crop_rect JSONB,                      -- normalized {x,y,w,h} (0..1 of the original); NULL on legacy 4:3 rows
    cropped_url TEXT,                     -- square cropped variant; NULL → display public_url directly
    cropped_storage_path TEXT             -- cropped-variant storage path (for delete/cascade)
);

Display/cover precedence: COALESCE(processed_url, cropped_url, public_url) = cutout > cropped > original (ADR-0008). imageUrls always returns the originals (identity). Background removal (processObjectImage) runs on the cropped frame when present (ADR-0012).

A class image is a row with object_class_ref set and object_instance_id NULL (exactly one of the two is non-null — ADR-0057). setObjectClassImage writes/replaces the single is_main=true row for a class (store-only today).

Neo4j :ObjectClass node (not Postgres)

Class identity + relationships are canonical in Neo4j (ADR-0057), not Postgres. The node is keyed by its external class_ref (the value a tag carries as cp), not the Neo4j internal id.

(:ObjectClass {
    class_ref,        // stable external id (e.g. "NmSGC72tAEr"); the lookup key
    name,
    description,
    class_kind,       // "community" | "manufacturer"
    visibility,       // e.g. "public"
    owner_org_id,     // owning organization UUID (ADR-0060); logical ref to a Postgres org, NOT a DB FK
    version,          // 1 at creation (not yet incremented on update)
    created_at
})

owner_org_id (added 2026-06-28, ADR-0060) is the class ownership boundary: myObjectClasses lists classes whose owner_org_id ∈ the caller’s orgs, and class edits are gated on membership of that org (validated against Postgres organization_members). A class with no owner_org_id (legacy, pre-backfill) is editable by no one.

🐛 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

1. Use MCP Supabase Tools:

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

2. Direct Database Query:

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

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

🔒 Row-Level Security Status (verified Fri 12 Jun 2026)

Table	RLS	Notes
organizations	✅ enabled	SELECT via is_member(id); INSERT when auth.uid() = created_by
organization_members	✅ enabled	SELECT via is_member(organization_id); INSERT for org creators/admins
object_instances, object_images, object_deletion_audit	✅ enabled	Member-scoped via organization_members subqueries
profiles	✅ enabled	Public SELECT; self INSERT/UPDATE
functional_predicates, spatial_predicates	✅ enabled	Public read-only
migration_tracking, migration_backup	✅ enabled	%@plings.io admins only
wallet_versions, path_registry, manufacturer_registry, manufacturer_lineage	✅ enabled	Deny-all (RLS on, no policies) by design — only the API’s BYPASSRLS connection reads them

organizations and organization_members had their policies defined but RLS was disabled, leaving them fully exposed to the public anon key. RLS was enabled on Fri 12 Jun 2026 (migrations/20260612124838-enable_rls_organizations_and_members.sql), activating the existing policies. is_member(uuid) is STABLE SECURITY DEFINER with a locked search_path, so the self-referential organization_members SELECT policy does not recurse. Verified post-change: the anon key now reads 0 rows from organizations, while the app (via the GraphQL API’s BYPASSRLS connection) still loads orgs and objects.

---

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