HD Wallet Database Schema - Wallet-First Architecture

Last Updated: Sön 13 Jul 2025 09:45:00 CEST - MAJOR UPDATE: Wallet-first database design with multi-wallet support

This document details the database design for the Plings HD Wallet identifier system using the wallet-first architecture, explaining the schema, relationships, and reasoning behind design decisions.

Overview

The Wallet-First HD Wallet system requires persistent storage for:

  • Wallet Versions: Track multiple wallet versions for security and environment separation
  • Path Allocations: Track which paths are in use within each wallet to prevent collisions
  • Manufacturer Registry: Control who can generate identifiers within each wallet version
  • Transfer Tracking: Complete audit trail for wallet transfers and manufacturer lineage
  • Integration Points: Links to existing organizations and users

Entity Relationship Diagram

erDiagram WALLET_VERSIONS ||--o{ MANUFACTURER_REGISTRY : contains WALLET_VERSIONS ||--o{ PATH_REGISTRY : owns_paths WALLET_VERSIONS ||--o{ MANUFACTURER_PATH_SCHEMAS : defines MANUFACTURER_REGISTRY ||--o{ MANUFACTURER_PATH_SCHEMAS : owns_schema MANUFACTURER_PATH_SCHEMAS ||--o{ PATH_LEVEL_VALUES : contains_values MANUFACTURER_LINEAGE ||--|| MANUFACTURER_REGISTRY : tracks_current MANUFACTURER_LINEAGE ||--|| WALLET_VERSIONS : predecessor_wallet USERS ||--o{ PATH_REGISTRY : allocates USERS ||--o{ MANUFACTURER_REGISTRY : registers ORGANIZATIONS ||--o{ MANUFACTURER_REGISTRY : represents WALLET_VERSIONS { integer version_id PK varchar version_name UK "v1,v2,v3" varchar master_key_id "HSM key reference" text description "Production wallet" varchar environment "production|testing|development" varchar security_level "standard|high|experimental" varchar status "active|inactive|deprecated|retired" timestamp created_at timestamp deactivated_at timestamp retired_at boolean is_default "Only one default wallet" } PATH_REGISTRY { uuid id PK integer wallet_version FK varchar base_path "2.3.4.5.10" varchar hd_derivation_prefix "m/44'/501'/1'/2'/3'/4'/5'/10'" varchar allocation_type "production|test|development|reserved" integer quantity "1000" integer allocated_range_start "1" integer allocated_range_end "1000" text purpose varchar status "active|exhausted|transferred|retired" uuid allocated_by FK timestamp allocated_at timestamp completed_at integer transferred_to_wallet FK timestamp transferred_at } MANUFACTURER_REGISTRY { uuid id PK integer wallet_version FK integer manufacturer_index "Index within wallet" varchar manufacturer_name "Plings|IKEA|Coca-Cola" uuid organization_id FK text public_key "Ed25519 public key" varchar key_delegation_level "none|verification|full" varchar status "active|suspended|transferring|retired" uuid registered_by FK timestamp registered_at integer transferred_from_wallet FK timestamp transfer_completed_at } MANUFACTURER_LINEAGE { uuid id PK varchar canonical_name UK "IKEA,Coca-Cola" integer current_wallet_version FK integer current_manufacturer_index integer predecessor_wallet FK integer predecessor_index varchar assignment_reason "initial_setup|security_upgrade" timestamp assignment_date varchar status "active|in_progress|completed" integer completion_percentage } MANUFACTURER_PATH_SCHEMAS { uuid id PK integer wallet_version FK integer manufacturer_index FK integer path_level "3,4,5..." varchar level_name "region|category|product_line" text level_description varchar gs1_mapping "GS1 AI reference" boolean is_required integer sort_order timestamp created_at timestamp updated_at } PATH_LEVEL_VALUES { uuid id PK integer wallet_version FK integer manufacturer_index FK integer path_level FK integer level_value "Actual path number" varchar value_name "Europe|Furniture|BILLY" text value_description varchar gs1_code "GTIN|GLN codes" varchar sku_reference "Internal SKU" boolean is_active timestamp created_at timestamp updated_at }

Table Details

wallet_versions (New Core Table)

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

Key Design Decisions:

  1. Version ID as Integer:
    • Sequential numbering (1, 2, 3…) for simplicity
    • Maps directly to HD derivation paths
    • Immutable once assigned
  2. Environment Separation:
    • Production, testing, and development wallets
    • Enables clean isolation for different use cases
    • Security level controls access patterns
  3. Default Wallet Management:
    • EXCLUDE constraint ensures only one default wallet
    • Simplifies URL generation and verification
    • Automatic failover capability

Schema Definition:

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),
    
    -- Indexes
    INDEX idx_wallet_status (status),
    INDEX idx_wallet_environment (environment),
    INDEX idx_wallet_default (is_default) WHERE (is_default = true)
);

Common Queries:

-- Get default wallet
SELECT * FROM wallet_versions WHERE is_default = true;

-- List active wallets by environment
SELECT * FROM wallet_versions 
WHERE status = 'active' AND environment = 'production'
ORDER BY version_id;

-- Get wallet for specific version
SELECT * FROM wallet_versions WHERE version_name = 'v2';

path_registry (Flexible Allocation Tracking)

Purpose: Central ledger of allocated path ranges within each wallet version - focused purely on collision prevention and allocation tracking.

Key Design Decisions:

  1. Allocation-Focused:
    • Tracks path range reservations, not individual objects
    • Prevents path collisions within wallet versions
    • Records batch allocation metadata
  2. Flexible Path Structure:
    • No hardcoded semantic assumptions
    • Manufacturers define their own path hierarchies
    • Base path + quantity model for ranges
  3. Transfer Support:
    • transferred_to_wallet tracks wallet transitions
    • Transfer timestamps for audit trails
    • Status field supports transfer states

Schema Definition:

CREATE TABLE path_registry (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wallet_version INTEGER NOT NULL REFERENCES wallet_versions(version_id),
    base_path VARCHAR(50) NOT NULL,               -- e.g., "2.3.4.5.10" (without instances, no class pointers)
    hd_derivation_prefix VARCHAR(100) NOT NULL,   -- e.g., "m/44'/501'/1'/2'/3'/4'/5'/10'" (base HD path)
    allocation_type VARCHAR(20) NOT NULL,         -- "production", "test", "development", "reserved"
    quantity INTEGER NOT NULL,                    -- Number of instances allocated (1-N)
    allocated_range_start INTEGER DEFAULT 1,     -- Starting instance number
    allocated_range_end INTEGER,                  -- Ending instance number (computed: start + quantity - 1)
    purpose TEXT,                                 -- Free-form allocation description
    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_base_path_per_wallet 
        UNIQUE (wallet_version, base_path),
    CONSTRAINT valid_range_end 
        CHECK (allocated_range_end = allocated_range_start + quantity - 1),
    
    -- Foreign Keys
    FOREIGN KEY (allocated_by) REFERENCES users(id),
    FOREIGN KEY (transferred_to_wallet) REFERENCES wallet_versions(version_id),
    
    -- Indexes
    INDEX idx_path_wallet_base (wallet_version, base_path),
    INDEX idx_path_allocation_type (allocation_type),
    INDEX idx_path_status (status),
    INDEX idx_path_range (allocated_range_start, allocated_range_end)
);

Common Queries:

-- Check if base path is available in wallet
SELECT NOT EXISTS (
    SELECT 1 FROM path_registry 
    WHERE wallet_version = 1 
      AND base_path = '2.3.4.5.10'
      AND status = 'active'
) as is_available;

-- Get all allocations for a wallet
SELECT 
    base_path,
    quantity,
    allocated_range_start,
    allocated_range_end,
    purpose,
    allocated_at
FROM path_registry 
WHERE wallet_version = 1 
  AND status = 'active'
ORDER BY allocated_at DESC;

-- Find path range conflicts (before allocation)
SELECT base_path, quantity 
FROM path_registry 
WHERE wallet_version = 1 
  AND base_path LIKE '2.3.4.5.%'  -- Same manufacturer/category hierarchy
  AND status = 'active';

-- Get allocation statistics
SELECT 
    allocation_type,
    COUNT(*) as allocations,
    SUM(quantity) as total_instances
FROM path_registry 
WHERE wallet_version = 1 
  AND status = 'active'
GROUP BY allocation_type;

manufacturer_registry (Enhanced for Wallet Support)

Purpose: Controls manufacturer namespace allocation within each wallet version.

Key Design Decisions:

  1. Wallet-Specific Indexing:
    • manufacturer_index unique within each wallet
    • Same manufacturer can have different indices across wallets
    • Enables clean transfer during security operations
  2. Transfer Tracking:
    • transferred_from_wallet links to previous wallet version
    • transfer_completed_at tracks transition timing
    • Supports gradual transfer processes
  3. Key Delegation Levels:
    • Progressive security model support
    • From none → verification → full delegation
    • Ed25519 public keys for Solana compatibility

Schema Definition:

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
    FOREIGN KEY (organization_id) REFERENCES organizations(id),
    FOREIGN KEY (registered_by) REFERENCES users(id),
    FOREIGN KEY (transferred_from_wallet) REFERENCES wallet_versions(version_id),
    
    -- Indexes
    INDEX idx_manufacturer_wallet_name (wallet_version, manufacturer_name),
    INDEX idx_manufacturer_organization (organization_id),
    INDEX idx_manufacturer_status (status)
);

Common Queries:

-- Get manufacturer by index in specific wallet
SELECT * FROM manufacturer_registry 
WHERE wallet_version = 1 AND manufacturer_index = 2;

-- List all manufacturers in wallet with transfer info
SELECT 
    manufacturer_name,
    manufacturer_index,
    status,
    transferred_from_wallet,
    transfer_completed_at
FROM manufacturer_registry 
WHERE wallet_version = 2
ORDER BY manufacturer_index;

-- Find current wallet for manufacturer
SELECT * FROM manufacturer_registry 
WHERE manufacturer_name = 'IKEA' 
  AND status = 'active'
ORDER BY wallet_version DESC 
LIMIT 1;

manufacturer_path_schemas (Flexible Path Definitions)

Purpose: Define the semantic structure of path hierarchies for each manufacturer, enabling flexible path organization while maintaining system consistency.

Key Design Decisions:

  1. Manufacturer Autonomy:
    • Each manufacturer defines their own path hierarchy
    • Support for GS1 standard mapping
    • Flexible depth and naming conventions
  2. Schema Versioning:
    • Wallet-specific schemas enable evolution
    • Level-based organization (3, 4, 5… after wallet/manufacturer)
    • Descriptive metadata for business understanding

Schema Definition:

CREATE TABLE manufacturer_path_schemas (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wallet_version INTEGER NOT NULL REFERENCES wallet_versions(version_id),
    manufacturer_index INTEGER NOT NULL,
    path_level INTEGER NOT NULL,              -- 3, 4, 5, etc. (after wallet=1, manufacturer=2)
    level_name VARCHAR(100) NOT NULL,         -- "region", "category", "product_line", "variant"
    level_description TEXT,                   -- "Geographic manufacturing region"
    gs1_mapping VARCHAR(100),                 -- Link to GS1 Application Identifier if applicable
    is_required BOOLEAN DEFAULT true,         -- Whether this level must be present in paths
    sort_order INTEGER,                       -- Display order for UI/documentation
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT unique_manufacturer_level 
        UNIQUE (wallet_version, manufacturer_index, path_level),
    CONSTRAINT valid_path_level 
        CHECK (path_level >= 3),  -- Levels 1-2 reserved for wallet/manufacturer
    
    -- Foreign Keys
    FOREIGN KEY (wallet_version, manufacturer_index) 
        REFERENCES manufacturer_registry(wallet_version, manufacturer_index),
    
    -- Indexes
    INDEX idx_schema_manufacturer (wallet_version, manufacturer_index),
    INDEX idx_schema_level (path_level),
    INDEX idx_schema_gs1 (gs1_mapping) WHERE gs1_mapping IS NOT NULL
);

path_level_values (Semantic Path Component Definitions)

Purpose: Store actual semantic definitions for path components, enabling human-readable interpretation of numeric path values.

Key Design Decisions:

  1. Business-Readable Paths:
    • Map numeric values to meaningful names
    • Support for business descriptions and GS1 codes
    • Enables POS integration and customer service
  2. Hierarchical Organization:
    • Values organized by manufacturer and path level
    • Support for nested categories and classifications
    • Extensible for future business needs

Schema Definition:

CREATE TABLE path_level_values (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wallet_version INTEGER NOT NULL REFERENCES wallet_versions(version_id),
    manufacturer_index INTEGER NOT NULL,
    path_level INTEGER NOT NULL,
    level_value INTEGER NOT NULL,             -- The actual number used in paths (decimal)
    value_name VARCHAR(100) NOT NULL,         -- "Europe", "Furniture", "BILLY", "White-80x28x202"
    value_description TEXT,                   -- "European manufacturing and distribution region"
    gs1_code VARCHAR(50),                     -- Actual GS1 code if applicable (GTIN, GLN, etc.)
    sku_reference VARCHAR(100),               -- Internal SKU or product code
    is_active BOOLEAN DEFAULT true,           -- Whether this value is currently in use
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT unique_manufacturer_level_value 
        UNIQUE (wallet_version, manufacturer_index, path_level, level_value),
    CONSTRAINT valid_path_level_ref 
        CHECK (path_level >= 3),
    
    -- Foreign Keys
    FOREIGN KEY (wallet_version, manufacturer_index, path_level) 
        REFERENCES manufacturer_path_schemas(wallet_version, manufacturer_index, path_level),
    
    -- Indexes
    INDEX idx_values_manufacturer_level (wallet_version, manufacturer_index, path_level),
    INDEX idx_values_name (value_name),
    INDEX idx_values_gs1 (gs1_code) WHERE gs1_code IS NOT NULL,
    INDEX idx_values_sku (sku_reference) WHERE sku_reference IS NOT NULL,
    INDEX idx_values_active (is_active) WHERE is_active = true
);

Common Schema Queries:

-- Get complete path schema for a manufacturer
SELECT 
    ps.path_level,
    ps.level_name,
    ps.level_description,
    ps.gs1_mapping,
    ps.is_required
FROM manufacturer_path_schemas ps
WHERE ps.wallet_version = 1 
  AND ps.manufacturer_index = 2  -- IKEA
ORDER BY ps.path_level;

-- Decode a complete path to human-readable format
WITH path_components AS (
    SELECT 
        2 as wallet_version,
        3 as manufacturer_index,  -- IKEA (base58: 2)
        4 as category_value,      -- Furniture (base58: 3) 
        5 as subcategory_value,   -- Bookcases (base58: 4)
        127 as class_value        -- BILLY White 80x28x202 (base58: -2J)
)
SELECT 
    3 as level, 'category' as level_name, pv1.value_name, pv1.value_description
FROM path_components pc
JOIN path_level_values pv1 ON pc.wallet_version = pv1.wallet_version 
    AND pc.manufacturer_index = pv1.manufacturer_index 
    AND pv1.path_level = 3 AND pv1.level_value = pc.category_value

UNION ALL

SELECT 
    4 as level, 'subcategory' as level_name, pv2.value_name, pv2.value_description  
FROM path_components pc
JOIN path_level_values pv2 ON pc.wallet_version = pv2.wallet_version
    AND pc.manufacturer_index = pv2.manufacturer_index
    AND pv2.path_level = 4 AND pv2.level_value = pc.subcategory_value

UNION ALL

SELECT 
    999 as level, 'class' as level_name, pv3.value_name, pv3.value_description
FROM path_components pc  
JOIN path_level_values pv3 ON pc.wallet_version = pv3.wallet_version
    AND pc.manufacturer_index = pv3.manufacturer_index
    AND pv3.path_level = 999 AND pv3.level_value = pc.class_value  -- Special level for class pointers

ORDER BY level;

-- Find all products in a category
SELECT 
    plv.level_value,
    plv.value_name,
    plv.value_description,
    plv.sku_reference,
    plv.gs1_code
FROM path_level_values plv
WHERE plv.wallet_version = 1
  AND plv.manufacturer_index = 2  -- IKEA
  AND plv.path_level = 4  -- Subcategory level
  AND plv.is_active = true
ORDER BY plv.value_name;

manufacturer_lineage (Wallet Assignment Tracking)

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

Key Design Decisions:

  1. Canonical Identity:
    • canonical_name preserves identity across wallets
    • Links current and previous wallet positions
    • Enables assignment history tracking
  2. Assignment Progress:
    • completion_percentage for large transfers
    • status tracks process stages
    • Detailed reason codes for audit

Schema Definition:

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
    FOREIGN KEY (current_wallet_version, current_manufacturer_index) 
        REFERENCES manufacturer_registry(wallet_version, manufacturer_index),
    FOREIGN KEY (predecessor_wallet) REFERENCES wallet_versions(version_id),
    
    -- Constraints
    CONSTRAINT unique_canonical_manufacturer UNIQUE (canonical_name),
    
    -- Indexes
    INDEX idx_lineage_canonical (canonical_name),
    INDEX idx_lineage_current_wallet (current_wallet_version),
    INDEX idx_lineage_status (status)
);

Multi-Wallet Collision Prevention Strategy

Enhanced Problem Definition

In a multi-wallet hierarchical path system, we must prevent:

  1. Path conflicts within the same wallet version
  2. Transfer conflicts during wallet transitions
  3. Manufacturer index conflicts within wallets

Enhanced Solution

CREATE OR REPLACE FUNCTION prevent_wallet_path_overlap() 
RETURNS trigger AS $$
BEGIN
    -- Check if any parent or child path already exists in this wallet
    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', 'migrating')
          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();

Wallet Migration Examples

-- Scenario 1: Same path across wallets (OK)
INSERT INTO path_registry (wallet_version, path, ...) VALUES (1, '2.2.2.2.2', ...);  -- Success
INSERT INTO path_registry (wallet_version, path, ...) VALUES (2, '2.2.2.2.2', ...);  -- Success (different wallet)

-- Scenario 2: Path conflict within wallet (FAILS)
INSERT INTO path_registry (wallet_version, path, ...) VALUES (1, '2.2.2.2', ...);        -- Success
INSERT INTO path_registry (wallet_version, path, ...) VALUES (1, '2.2.2.2.2', ...);  -- FAILS: Parent exists in wallet

-- Scenario 3: Cross-wallet migration
UPDATE path_registry 
SET status = 'migrated', migrated_to_wallet = 2, migrated_at = NOW()
WHERE wallet_version = 1 AND path = '2.2.2.2.2';  -- Success

Integration with Existing Schema

Multi-Wallet Organization Support

-- Paths can exist across multiple wallets for same organization
SELECT 
    p.wallet_version,
    w.version_name,
    p.path,
    p.status,
    o.name as org_name 
FROM path_registry p
JOIN wallet_versions w ON p.wallet_version = w.version_id
JOIN organizations o ON p.organization_id = o.id
WHERE o.id = 'org-uuid'
ORDER BY p.wallet_version, p.path;

Wallet-Aware Permissions

-- Check if user can allocate paths for organization in specific wallet
SELECT 
    w.version_name,
    w.status as wallet_status,
    om.role 
FROM wallet_versions w
CROSS JOIN organization_members om
WHERE w.version_id = 1  -- Target wallet
  AND om.user_id = 'user-uuid' 
  AND om.organization_id = 'org-uuid'
  AND om.role IN ('owner', 'admin')
  AND w.status = 'active';

Performance Considerations

Multi-Wallet Indexes

-- Wallet-specific path lookup (most common)
CREATE INDEX idx_path_registry_wallet_path ON path_registry(wallet_version, path);

-- Active paths across all wallets
CREATE INDEX idx_path_registry_active ON path_registry(status, wallet_version) 
WHERE status IN ('active', 'migrating');

-- Migration tracking
CREATE INDEX idx_path_registry_migration ON path_registry(migrated_to_wallet, migrated_at)
WHERE migrated_to_wallet IS NOT NULL;

-- Manufacturer lookup across wallets
CREATE INDEX idx_manufacturer_registry_name ON manufacturer_registry(manufacturer_name, wallet_version);

Wallet-Aware Query Optimization

-- Materialized view for wallet statistics
CREATE MATERIALIZED VIEW wallet_usage_stats AS
SELECT 
    w.version_id,
    w.version_name,
    w.environment,
    COUNT(p.id) as total_paths,
    SUM(p.quantity) as total_identifiers,
    COUNT(DISTINCT m.id) as active_manufacturers,
    MAX(p.allocated_at) as last_allocation
FROM wallet_versions w
LEFT JOIN path_registry p ON w.version_id = p.wallet_version AND p.status = 'active'
LEFT JOIN manufacturer_registry m ON w.version_id = m.wallet_version AND m.status = 'active'
GROUP BY w.version_id, w.version_name, w.environment
ORDER BY w.version_id;

Migration and Incident Response

Key Compromise Response

-- Mark wallet as compromised
UPDATE wallet_versions 
SET status = 'compromised', compromised_at = NOW()
WHERE version_id = 1;

-- Create new wallet version
INSERT INTO wallet_versions (version_id, version_name, master_key_id, description, is_default)
VALUES (2, 'v2', 'plings_master_key_v2', 'Post-compromise wallet', true);

-- Update previous default
UPDATE wallet_versions SET is_default = false WHERE version_id = 1;

-- Migrate Plings to new wallet
INSERT INTO manufacturer_registry (
    wallet_version, manufacturer_index, manufacturer_name, status, 
    registered_by, migrated_from_wallet
)
VALUES (2, 1, 'Plings', 'active', '00000000-0000-0000-0000-000000000000', 1);

-- Update lineage tracking
INSERT INTO manufacturer_lineage (
    canonical_name, current_wallet_version, current_manufacturer_index,
    predecessor_wallet, predecessor_index, migration_reason
)
VALUES ('Plings', 2, 1, 1, 1, 'key_compromise');

Migration Audit Queries

-- Complete migration history for manufacturer
SELECT 
    ml.canonical_name,
    ml.predecessor_wallet,
    ml.current_wallet_version,
    ml.migration_reason,
    ml.migration_date,
    w1.version_name as from_wallet,
    w2.version_name as to_wallet
FROM manufacturer_lineage ml
LEFT JOIN wallet_versions w1 ON ml.predecessor_wallet = w1.version_id
JOIN wallet_versions w2 ON ml.current_wallet_version = w2.version_id
WHERE ml.canonical_name = 'IKEA'
ORDER BY ml.migration_date;

-- Track path migrations during wallet transition
SELECT 
    p1.path,
    p1.wallet_version as from_wallet,
    p1.migrated_to_wallet as to_wallet,
    p1.migrated_at,
    p2.status as target_status
FROM path_registry p1
LEFT JOIN path_registry p2 ON p1.migrated_to_wallet = p2.wallet_version 
                           AND p1.path = p2.path
WHERE p1.status = 'migrated'
ORDER BY p1.migrated_at DESC;

Security Considerations

Wallet-Level Access Control

  1. Wallet Creation: Super-admin only with multi-approval workflow
  2. Manufacturer Migration: Admin or incident response team with audit trail
  3. Default Wallet Changes: Super-admin with business impact review
  4. Cross-Wallet Operations: Special permissions with enhanced logging

Enhanced Audit Trail

-- Complete audit trail across wallets
SELECT 
    'path_allocation' as operation,
    pr.wallet_version,
    wv.version_name,
    pr.path,
    pr.allocated_at as timestamp,
    u.email as operator,
    pr.purpose as details
FROM path_registry pr
JOIN wallet_versions wv ON pr.wallet_version = wv.version_id
JOIN auth.users u ON pr.allocated_by = u.id
WHERE pr.manufacturer_name = 'IKEA'

UNION ALL

SELECT 
    'manufacturer_registration' as operation,
    mr.wallet_version,
    wv.version_name,
    mr.manufacturer_name as path,
    mr.registered_at as timestamp,
    u.email as operator,
    CONCAT('Index: ', mr.manufacturer_index) as details
FROM manufacturer_registry mr
JOIN wallet_versions wv ON mr.wallet_version = wv.version_id
JOIN auth.users u ON mr.registered_by = u.id
WHERE mr.manufacturer_name = 'IKEA'

ORDER BY timestamp DESC;

Future Enhancements

Planned Multi-Wallet Features

  1. Automated Migration: Trigger-based manufacturer migration during key compromise
  2. Cross-Wallet Analytics: Usage patterns across wallet versions
  3. Wallet Templates: Pre-configured wallet setups for different environments
  4. HSM Integration: Direct wallet key management with hardware security modules
  5. Distributed Verification: Multi-wallet signature verification

Schema Evolution for Advanced Features

-- Future: Add wallet performance metrics
ALTER TABLE wallet_versions
ADD COLUMN daily_allocation_limit INTEGER DEFAULT 1000000,
ADD COLUMN current_daily_allocations INTEGER DEFAULT 0,
ADD COLUMN last_reset_date DATE DEFAULT CURRENT_DATE;

-- Future: Add cross-wallet verification
CREATE TABLE cross_wallet_verifications (
    id UUID PRIMARY KEY,
    source_wallet INTEGER REFERENCES wallet_versions(version_id),
    target_wallet INTEGER REFERENCES wallet_versions(version_id),
    path VARCHAR(50),
    verification_result BOOLEAN,
    verified_at TIMESTAMP DEFAULT NOW()
);

-- Future: Add wallet health monitoring
CREATE TABLE wallet_health_metrics (
    wallet_version INTEGER REFERENCES wallet_versions(version_id),
    metric_date DATE,
    allocations_count INTEGER,
    verifications_count INTEGER,
    error_count INTEGER,
    average_response_time_ms INTEGER,
    PRIMARY KEY (wallet_version, metric_date)
);

This wallet-first database schema provides the foundation for secure, scalable, and operationally robust multi-wallet management while maintaining compatibility with existing Plings infrastructure.

Key Benefits:

  • Incident Resilience: Isolated wallet failures don’t affect other wallets
  • Clean Migrations: Complete audit trail for wallet transitions
  • Environment Separation: Development, testing, and production wallet isolation
  • Progressive Security: Support for evolving key management strategies

For implementation details, see Wallet Management Architecture. For path allocation procedures, see Path Registry Specification.