Supabase Core Schema (PostgreSQL) - Wallet-First Architecture

Last Verified: 2025-07-05 11:30 CEST via MCP list-tables on production project gjzcqcxoacxkrchrzaen. Last Updated: Sön 13 Jul 2025 10:10:43 CEST - MAJOR UPDATE: Wallet-first database design with multi-wallet support

Always verify column names before writing SQL: see SCHEMA-VERIFICATION.md for the authoritative live schema snapshot.

Overview

This document captures the relational schema that lives in the Supabase Postgres database. Neo4j handles graph relationships; Postgres is used for metadata, security, and reference tables (predicate catalogues, etc.).

NEW: Wallet-First Architecture - The HD wallet system now uses wallet versioning as the top-level hierarchy, enabling clean key rotation, incident response, and multi-environment support.

The tables were enumerated directly from production using the Supabase Management API to avoid drift.

Table	Purpose
`profiles`	User profile metadata (1-to-1 with `auth.users`)
`organizations`	Root entity for multi-tenant isolation
`organization_members`	Junction table mapping users to organizations & roles
`object_instances`	Core business objects (metadata + `neo4j_id` pointer)
`object_images`	Storage metadata for object images
`functional_predicates`	Catalogue of functional relationship types
`spatial_predicates`	Catalogue of spatial relationship types
`wallet_versions`	NEW: Central registry of wallet versions for multi-wallet management
`path_registry`	UPDATED: Wallet-aware HD path allocation tracking for collision prevention
`manufacturer_registry`	UPDATED: Wallet-specific manufacturer registration for HD wallet path prefixes
`manufacturer_lineage`	NEW: Track manufacturer identity across wallet assignments

1. `profiles`

create table profiles (
  id uuid primary key references auth.users(id),
  full_name   text,
  avatar_url  text,
  updated_at  timestamptz default now()
);

Row-Level-Security (RLS) is enabled and forced via the auth.uid() match policy.

2. `organizations`

create table organizations (
  id           uuid primary key default gen_random_uuid(),
  name         text not null,
  created_at   timestamptz default now(),
  created_by   uuid references auth.users(id),
  type         text not null check (type in ('Individual','Family','Company','Institution')) default 'Individual',
  description  text
);

Ownership checks are enforced via organization-scoped RLS policies.

3. `organization_members`

create table organization_members (
  organization_id uuid references organizations(id),
  user_id         uuid references profiles(id),
  role            text check (role in ('owner','admin','member')),
  joined_at       timestamptz default now(),
  primary key (organization_id, user_id)
);

4. `object_instances`

Core metadata for every physical object.

create table object_instances (
  id                   uuid primary key default gen_random_uuid(),
  neo4j_id             text not null unique,   -- pointer to Neo4j node
  name                 text not null,
  description          text,
  owner_organization_id uuid references organizations(id),
  created_by           uuid references auth.users(id),
  created_at           timestamptz default now(),
  updated_at           timestamptz default now(),
  status               text not null default 'active',
  main_image_url       text,
  last_scanned_at      timestamptz,
  last_scanned_by      uuid references auth.users(id)
);

RLS policies assert that users can only see objects belonging to their organization.

5. `object_images`

create table object_images (
  id                uuid primary key default gen_random_uuid(),
  object_instance_id uuid references object_instances(id),
  storage_path      text not null,
  public_url        text not null,
  is_main           boolean default false,
  uploaded_by       uuid references auth.users(id),
  uploaded_at       timestamptz default now(),
  file_size         integer,
  content_type      text
);

6. `functional_predicates`

Reference catalogue of functional relationship types. Used by GraphQL resolver for validation and UI dropdowns.

create table functional_predicates (
  key           text primary key,
  category      text not null,
  description   text,
  inverse_key   text references functional_predicates(key),
  is_symmetric  boolean default false,
  icon_name     text,
  deprecated    boolean default false,
  created_at    timestamptz default now()
);

RLS: read-only for all authenticated users.

7. `spatial_predicates`

Reference catalogue for spatial relationship types. Includes creates_parent_relationship flag used by movement logic.

create table spatial_predicates (
  key                       text primary key,
  axis                      text,
  description               text,
  inverse_key               text references spatial_predicates(key),
  is_symmetric              boolean default false,
  creates_parent_relationship boolean default false,
  icon_name                 text,
  deprecated                boolean default false,
  created_at                timestamptz default now()
);

Next Steps

Keep this doc in sync with: • SCHEMA-VERIFICATION.md (verified columns) • supabase_schema.sql (DDL dump)
Any schema migration must update this file and the verification doc.

8. `wallet_versions` (New Core Table)

Purpose: Central registry of all wallet versions for multi-wallet management and operational flexibility.

CREATE TABLE wallet_versions (
    version_id INTEGER PRIMARY KEY,
    version_name VARCHAR(10) NOT NULL UNIQUE,    -- "v1", "v2", "v3", etc.
    master_key_id VARCHAR(100) NOT NULL,         -- HSM key reference
    description TEXT,                             -- "Production wallet", "Development v3"
    environment VARCHAR(20) DEFAULT 'production', -- "production", "testing", "development"
    security_level VARCHAR(20) DEFAULT 'standard', -- "standard", "high", "experimental"
    status VARCHAR(20) DEFAULT 'active',          -- "active", "inactive", "deprecated", "retired"
    created_at TIMESTAMP DEFAULT NOW(),
    deactivated_at TIMESTAMP,                     -- When wallet was deactivated
    retired_at TIMESTAMP,                         -- When wallet was fully retired
    is_default BOOLEAN DEFAULT FALSE,             -- Only one default wallet allowed
    
    -- Constraints
    CONSTRAINT unique_version_name UNIQUE (version_name),
    CONSTRAINT unique_default_wallet 
        EXCLUDE (is_default WITH =) WHERE (is_default = true)
);

9. `path_registry` (Enhanced for Wallet Support)

Purpose: Manages allocation and tracking of HD wallet derivation paths within each wallet version.

CREATE TABLE path_registry (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wallet_version INTEGER NOT NULL REFERENCES wallet_versions(version_id),
    path VARCHAR(50) NOT NULL,                    -- e.g., "2.2.2.2.2", "2.3.2.7P.9j" (class pointer separate)
    hd_derivation VARCHAR(100) NOT NULL,          -- e.g., "m/44'/501'/1'/1'/1'/1'/1'/1'"
    allocation_type VARCHAR(20) NOT NULL,         -- "generic", "test", "manufacturer", "development"
    manufacturer_name VARCHAR(100),               -- For manufacturer batch allocations
    batch_name VARCHAR(100),                      -- Human-readable batch identifier
    quantity INTEGER,                             -- Number of identifiers in this allocation
    purpose TEXT,                                 -- Description/notes
    status VARCHAR(20) DEFAULT 'active',          -- "active", "exhausted", "transferred", "retired"
    allocated_by UUID NOT NULL,
    allocated_at TIMESTAMP DEFAULT NOW(),
    completed_at TIMESTAMP,                       -- When batch generation completed
    transferred_to_wallet INTEGER,               -- Target wallet for transfer
    transferred_at TIMESTAMP,                    -- When transfer completed
    
    -- Constraints
    CONSTRAINT unique_path_per_wallet 
        UNIQUE (wallet_version, path),
    
    -- Foreign Keys
    CONSTRAINT fk_allocated_by 
        FOREIGN KEY (allocated_by) REFERENCES auth.users(id),
    CONSTRAINT fk_transferred_to_wallet 
        FOREIGN KEY (transferred_to_wallet) REFERENCES wallet_versions(version_id)
);

Design Rationale:

Wallet-Aware Collision Prevention: UNIQUE constraint on (wallet_version, path) + custom trigger prevents overlaps within wallets
Cross-Wallet Support: Same logical path can exist across different wallet versions
Transfer Support: Track path transfers during wallet transitions with audit trails
Complete Hierarchy: Supports full manufacturer.category.class.batch.instance structure
Scalability: Each path supports up to 2 billion identifiers per wallet

Key Indexes:

CREATE INDEX idx_path_wallet_path ON path_registry(wallet_version, path);
CREATE INDEX idx_path_allocation_type ON path_registry(allocation_type);
CREATE INDEX idx_path_manufacturer ON path_registry(manufacturer_name);
CREATE INDEX idx_path_status ON path_registry(status);
CREATE INDEX idx_path_transfer ON path_registry(transferred_to_wallet, transferred_at) WHERE transferred_to_wallet IS NOT NULL;

Multi-Wallet Collision Prevention Trigger:

-- Prevents path overlap within the same wallet (e.g., can't allocate "2.2.2" if "2.2.2.2.2" exists in same wallet)
CREATE OR REPLACE FUNCTION prevent_wallet_path_overlap() 
RETURNS trigger AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM path_registry 
        WHERE wallet_version = NEW.wallet_version
          AND (
            -- New path is parent of existing
            NEW.path LIKE path || '.%' OR 
            -- New path is child of existing
            path LIKE NEW.path || '.%'
          )
          AND status IN ('active', 'transferring')
          AND id != COALESCE(NEW.id, gen_random_uuid())
    ) THEN
        RAISE EXCEPTION 'Path conflicts with existing allocation in wallet v%: %', 
                       NEW.wallet_version, NEW.path;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER wallet_path_overlap_check 
    BEFORE INSERT OR UPDATE ON path_registry
    FOR EACH ROW EXECUTE FUNCTION prevent_wallet_path_overlap();

10. `manufacturer_registry` (Enhanced for Wallet Support)

Purpose: Controls manufacturer namespace allocation within each wallet version.

CREATE TABLE manufacturer_registry (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wallet_version INTEGER NOT NULL REFERENCES wallet_versions(version_id),
    manufacturer_index INTEGER NOT NULL,          -- Index within this wallet (1=Plings, 2+=Others)
    manufacturer_name VARCHAR(100) NOT NULL,      -- "Plings", "IKEA", "Coca-Cola"
    organization_id UUID,                         -- Link to organization
    public_key TEXT,                              -- Ed25519 public key if delegated
    key_delegation_level VARCHAR(20) DEFAULT 'none', -- "none", "verification", "full"
    status VARCHAR(20) DEFAULT 'active',          -- "active", "suspended", "transferring", "retired"
    registered_by UUID NOT NULL,
    registered_at TIMESTAMP DEFAULT NOW(),
    transferred_from_wallet INTEGER,             -- Previous wallet version (for transfer tracking)
    transfer_completed_at TIMESTAMP,
    
    -- Constraints
    CONSTRAINT unique_manufacturer_per_wallet 
        UNIQUE (wallet_version, manufacturer_index),
    CONSTRAINT unique_name_per_wallet 
        UNIQUE (wallet_version, manufacturer_name),
    CONSTRAINT check_plings_index_one
        CHECK (manufacturer_name != 'Plings' OR manufacturer_index = 1),
    
    -- Foreign Keys
    CONSTRAINT fk_manufacturer_org 
        FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_manufacturer_registered_by 
        FOREIGN KEY (registered_by) REFERENCES auth.users(id),
    CONSTRAINT fk_transferred_from_wallet 
        FOREIGN KEY (transferred_from_wallet) REFERENCES wallet_versions(version_id)
);

11. `manufacturer_lineage` (Wallet Assignment Tracking)

Purpose: Track manufacturer identity across wallet assignments for audit and continuity.

CREATE TABLE manufacturer_lineage (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    canonical_name VARCHAR(100) NOT NULL,         -- "IKEA", "Coca-Cola" (consistent across wallets)
    current_wallet_version INTEGER NOT NULL,
    current_manufacturer_index INTEGER NOT NULL,
    predecessor_wallet INTEGER,                    -- Previous wallet version
    predecessor_index INTEGER,                     -- Previous manufacturer index
    assignment_reason VARCHAR(50),                 -- "initial_setup", "security_upgrade", "environment_separation"
    assignment_date TIMESTAMP DEFAULT NOW(),
    status VARCHAR(20) DEFAULT 'active',          -- "active", "in_progress", "completed"
    completion_percentage INTEGER DEFAULT 0,      -- 0-100% for tracking progress
    
    -- Foreign Keys
    CONSTRAINT fk_lineage_current_manufacturer
        FOREIGN KEY (current_wallet_version, current_manufacturer_index) 
        REFERENCES manufacturer_registry(wallet_version, manufacturer_index),
    CONSTRAINT fk_lineage_predecessor_wallet 
        FOREIGN KEY (predecessor_wallet) REFERENCES wallet_versions(version_id),
    
    -- Constraints
    CONSTRAINT unique_canonical_manufacturer UNIQUE (canonical_name)
);

Design Rationale:

Wallet-Specific Indexing: manufacturer_index unique within each wallet version
Cross-Wallet Continuity: Same manufacturer can have different indices across wallets
Transfer Support: Complete tracking of wallet transitions with audit trails
Progressive Security: Key delegation levels from none → verification → full
Organization Integration: Seamless integration with existing multi-tenant structure

Key Indexes:

CREATE INDEX idx_manufacturer_wallet_name ON manufacturer_registry(wallet_version, manufacturer_name);
CREATE INDEX idx_manufacturer_organization ON manufacturer_registry(organization_id);
CREATE INDEX idx_manufacturer_status ON manufacturer_registry(status);

-- Lineage indexes
CREATE INDEX idx_lineage_canonical ON manufacturer_lineage(canonical_name);
CREATE INDEX idx_lineage_current_wallet ON manufacturer_lineage(current_wallet_version);
CREATE INDEX idx_lineage_status ON manufacturer_lineage(status);

Initial Data:

-- Create default wallet version v1
INSERT INTO wallet_versions (
    version_id, version_name, master_key_id, description, environment, is_default
) VALUES (
    1, 'v1', 'plings_master_key_v1', 'Initial production wallet', 'production', true
);

-- Register Plings as manufacturer #1 in wallet v1
INSERT INTO manufacturer_registry (
    wallet_version, manufacturer_index, manufacturer_name, status, registered_by
) VALUES (
    1, 1, 'Plings', 'active', '00000000-0000-0000-0000-000000000000'
);

-- Create lineage tracking for Plings
INSERT INTO manufacturer_lineage (
    canonical_name, current_wallet_version, current_manufacturer_index, assignment_reason, status, completion_percentage
) VALUES (
    'Plings', 1, 1, 'initial_registration', 'completed', 100
);

-- Pre-allocate base paths for Plings in wallet v1 with complete hierarchy
INSERT INTO path_registry (wallet_version, path, hd_derivation, allocation_type, manufacturer_name, purpose, allocated_by)
VALUES 
    (1, '1.1.C1', 'm/44''/501''/1''/1''/1''/1''', 'generic', 'Plings', 'Generic Sticker Tags Category', '00000000-0000-0000-0000-000000000000'),
    (1, '1.2.C1', 'm/44''/501''/1''/1''/2''/1''', 'generic', 'Plings', 'Generic Etched Tags Category', '00000000-0000-0000-0000-000000000000'),
    (1, '1.3.C1', 'm/44''/501''/1''/1''/3''/1''', 'test', 'Plings', 'Test Batches Category', '00000000-0000-0000-0000-000000000000'),
    (1, '1.4.C1', 'm/44''/501''/1''/1''/4''/1''', 'manufacturer', 'Plings', 'Manufacturer Batch Orders', '00000000-0000-0000-0000-000000000000');

Wallet-First HD Wallet Database Design Decisions

Why PostgreSQL for Multi-Wallet Path Registry?

ACID Compliance: Path allocation must be atomic across wallet versions to prevent duplicates
Relational Integrity: Foreign keys ensure valid wallet, user, and organization references
Trigger Support: Complex multi-wallet collision prevention logic via database triggers
Integration: Seamless with existing Supabase infrastructure and RLS policies
Transfer Support: Complete audit trails for wallet transitions during security scenarios

Why Wallet-First Architecture?

Operational Resilience: Isolated wallet failures don’t affect other wallets
Clean Transfers: Complete audit trail for wallet transitions during security operations
Environment Separation: Development, testing, and production wallet isolation
Progressive Security: Support for evolving key management strategies (none → verification → full delegation)
Scalability: Linear scaling with new wallet versions as ecosystem grows

Why These Specific Tables?

wallet_versions:

Central registry of all wallet versions for multi-wallet management
Enables clean key rotation without disrupting manufacturer hierarchies
Supports multiple environments (production, testing, development)
Provides operational capabilities with automated security handling

path_registry (Enhanced):

Wallet-aware path allocation with cross-wallet support
Same logical path can exist across different wallet versions
Transfer tracking during wallet transitions
Complete hierarchy: manufacturer.category.class.batch.instance

manufacturer_registry (Enhanced):

Wallet-specific manufacturer registration enabling clean transfers
Progressive security model with key delegation levels
Cross-wallet manufacturer continuity via lineage tracking

manufacturer_lineage (New):

Track manufacturer identity across wallet assignments
Complete audit trail for regulatory compliance
Assignment progress tracking for large-scale transitions

Key Wallet-First Design Patterns

Multi-Wallet Hierarchical Path Collision Prevention:
- Can’t allocate “2.2.2” if “2.2.2.2.2” exists within same wallet
- Same path can exist across different wallet versions (cross-wallet support)
- Enforced at database level with wallet-aware triggers
Cross-Wallet Migration Support:
- Complete tracking of manufacturer migrations across wallets
- Audit trails for all wallet transitions with timestamps and reasons
- Progress tracking for large-scale migrations (0-100% completion)
Multi-Environment Wallet Isolation:
- Production, testing, and development wallets completely isolated
- Default wallet management for new identifier generation
- Environment-specific security levels and access controls
Progressive Security Evolution:
- Key delegation levels: none → verification → full
- Future-proof architecture for distributed key management
- HSM integration support for master key storage
Incident Response Capabilities:
- Rapid wallet compromise detection and isolation
- Automated manufacturer migration to new wallet versions
- Complete forensic audit trails for security investigations

Multi-Wallet Examples

Cross-Wallet Path Support:

-- Same path across different wallets (ALLOWED)
INSERT INTO path_registry (wallet_version, path, ...) VALUES (1, '2.2.2.2.2', ...);  -- Wallet v1
INSERT INTO path_registry (wallet_version, path, ...) VALUES (2, '2.2.2.2.2', ...);  -- Wallet v2 (OK)

-- Path conflict within wallet (PREVENTED)
INSERT INTO path_registry (wallet_version, path, ...) VALUES (1, '1.1.C1', ...);        -- Success
INSERT INTO path_registry (wallet_version, path, ...) VALUES (1, '1.1.C1.1.1', ...);  -- FAILS: Parent exists

Manufacturer Migration Example:

-- IKEA migrates from wallet v1 to v2 during key compromise
-- Old: wallet v1, manufacturer index 2
-- New: wallet v2, manufacturer index 2 (same logical position)
UPDATE manufacturer_lineage 
SET current_wallet_version = 2, migration_reason = 'key_compromise'
WHERE canonical_name = 'IKEA';

For comprehensive understanding of the wallet-first architecture, see:

Wallet Management Architecture - Complete wallet-first system overview
HD Wallet Database Schema - Detailed database design and relationships
Path Registry Specification - Multi-wallet path allocation procedures
Migration File - Database migration for wallet-first tables

Last updated: Sön 13 Jul 2025 10:10:43 CEST - MAJOR UPDATE: Wallet-first database design with multi-wallet support, complete hierarchy structure, and cross-wallet migration capabilities

Search Results:

Supabase Core Schema (PostgreSQL) - Wallet-First Architecture

Overview

1. profiles

2. organizations

3. organization_members

4. object_instances

5. object_images

6. functional_predicates

7. spatial_predicates