Data Types

Custom data types, enums, and domains used throughout the Smart Shelf database schema.

Data Types

The Smart Shelf database utilizes PostgreSQL's rich type system, including custom enums, domains, and composite types to ensure data integrity and improve code readability.

Overview

Custom data types provide:

Data Integrity: Constraints at the database level
Code Clarity: Self-documenting field meanings
Consistency: Standardized values across tables
Performance: Optimized storage and indexing
Maintainability: Centralized type definitions

Enumeration Types

User and Access Types

-- User roles with hierarchical permissions
CREATE TYPE user_role AS ENUM (
    'admin',        -- Full system access
    'manager',      -- Department/warehouse management
    'employee',     -- Operational access
    'viewer'        -- Read-only access
);

-- User account status
CREATE TYPE user_status AS ENUM (
    'active',
    'inactive',
    'suspended',
    'pending_verification'
);

-- User department classifications
CREATE TYPE user_department AS ENUM (
    'administration',
    'sales',
    'purchasing',
    'inventory',
    'warehouse',
    'accounting',
    'customer_service',
    'it'
);

Product and Inventory Types

-- Product lifecycle status
CREATE TYPE product_status AS ENUM (
    'draft',           -- Being created/edited
    'active',          -- Available for sale
    'discontinued',    -- No longer sold but may have inventory
    'obsolete',        -- Completely phased out
    'seasonal'         -- Seasonal availability
);

-- Product condition types
CREATE TYPE product_condition AS ENUM (
    'new',
    'refurbished',
    'used',
    'damaged',
    'returned'
);

-- Inventory movement types for tracking
CREATE TYPE inventory_movement_type AS ENUM (
    'purchase',        -- Incoming from supplier
    'sale',           -- Outgoing to customer
    'adjustment_in',   -- Positive adjustment
    'adjustment_out',  -- Negative adjustment
    'transfer_in',     -- Transfer from another location
    'transfer_out',    -- Transfer to another location
    'return_in',       -- Customer return
    'return_out',      -- Return to supplier
    'damaged',         -- Damaged goods write-off
    'expired',         -- Expired goods write-off
    'cycle_count'      -- Cycle count adjustment
);

-- Unit of measure types
CREATE TYPE unit_of_measure AS ENUM (
    'each',     -- Individual items
    'case',     -- Case quantities
    'pallet',   -- Pallet quantities
    'kg',       -- Kilograms
    'lb',       -- Pounds
    'liter',    -- Liters
    'gallon',   -- Gallons
    'meter',    -- Meters
    'foot'      -- Feet
);

Order Management Types

-- Sales order status workflow
CREATE TYPE sales_order_status AS ENUM (
    'draft',           -- Being created
    'pending',         -- Awaiting processing
    'processing',      -- Being fulfilled
    'backordered',     -- Partially fulfilled, waiting for inventory
    'shipped',         -- Shipped to customer
    'delivered',       -- Confirmed delivery
    'cancelled',       -- Cancelled by customer or system
    'returned'         -- Returned by customer
);

-- Purchase order status workflow
CREATE TYPE purchase_order_status AS ENUM (
    'draft',           -- Being created
    'ordered',         -- Sent to supplier
    'acknowledged',    -- Acknowledged by supplier
    'partially_received', -- Some items received
    'received',        -- All items received
    'invoiced',        -- Invoice received from supplier
    'paid',           -- Payment sent to supplier
    'cancelled',       -- Order cancelled
    'closed'          -- Order completed and closed
);

-- Payment methods
CREATE TYPE payment_method AS ENUM (
    'cash',
    'credit_card',
    'debit_card',
    'bank_transfer',
    'check',
    'store_credit',
    'net_terms'
);

-- Payment status
CREATE TYPE payment_status AS ENUM (
    'pending',
    'processing',
    'completed',
    'failed',
    'cancelled',
    'refunded'
);

Warehouse and Location Types

-- Warehouse zone types for organization
CREATE TYPE warehouse_zone_type AS ENUM (
    'receiving',       -- Incoming goods area
    'storage',         -- Main storage area
    'picking',         -- Order picking area
    'packing',         -- Order packing area
    'staging',         -- Staging for shipment
    'shipping',        -- Outbound shipping area
    'returns',         -- Returned goods area
    'quarantine'       -- Quality control area
);

-- Location types within zones
CREATE TYPE location_type AS ENUM (
    'bin',            -- Individual storage bin
    'shelf',          -- Shelf location
    'pallet',         -- Pallet location
    'floor',          -- Floor storage
    'rack',           -- Rack storage
    'dock'            -- Dock door
);

-- Storage environment requirements
CREATE TYPE storage_environment AS ENUM (
    'ambient',         -- Normal room temperature
    'refrigerated',    -- Cold storage (2-8°C)
    'frozen',          -- Frozen storage (-18°C or below)
    'controlled',      -- Climate controlled
    'hazmat'           -- Hazardous materials storage
);

Communication and Notification Types

-- Notification types
CREATE TYPE notification_type AS ENUM (
    'info',
    'warning',
    'error',
    'success',
    'low_stock',
    'order_status',
    'system_alert',
    'user_action'
);

-- Notification priority levels
CREATE TYPE notification_priority AS ENUM (
    'low',
    'normal',
    'high',
    'critical'
);

-- Contact method preferences
CREATE TYPE contact_method AS ENUM (
    'email',
    'sms',
    'phone',
    'push_notification',
    'in_app'
);

Domain Types

Domains add constraints to base types for data validation:

Identifier Domains

-- SKU format validation
CREATE DOMAIN sku_code AS VARCHAR(50)
    CHECK (VALUE ~ '^[A-Z0-9\-]{3,50}$');

-- Email validation
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Phone number validation (flexible international format)
CREATE DOMAIN phone_number AS VARCHAR(20)
    CHECK (VALUE ~ '^\+?[1-9]\d{1,14}$');

-- Postal code validation (flexible for international use)
CREATE DOMAIN postal_code AS VARCHAR(20)
    CHECK (LENGTH(TRIM(VALUE)) >= 3);

Financial Domains

-- Monetary amounts (non-negative)
CREATE DOMAIN money_amount AS DECIMAL(15,2)
    CHECK (VALUE >= 0);

-- Price amounts (positive)
CREATE DOMAIN price_amount AS DECIMAL(10,2)
    CHECK (VALUE > 0);

-- Percentage values
CREATE DOMAIN percentage_value AS DECIMAL(5,2)
    CHECK (VALUE >= 0 AND VALUE <= 100);

-- Tax rate (0-100%)
CREATE DOMAIN tax_rate AS DECIMAL(5,4)
    CHECK (VALUE >= 0 AND VALUE <= 1);

-- Discount rate (0-100%)
CREATE DOMAIN discount_rate AS DECIMAL(5,2)
    CHECK (VALUE >= 0 AND VALUE <= 100);

Measurement Domains

-- Weight in kilograms (non-negative)
CREATE DOMAIN weight_kg AS DECIMAL(10,3)
    CHECK (VALUE >= 0);

-- Dimensions in centimeters (positive)
CREATE DOMAIN dimension_cm AS DECIMAL(8,2)
    CHECK (VALUE > 0);

-- Volume in cubic meters (positive)
CREATE DOMAIN volume_m3 AS DECIMAL(10,4)
    CHECK (VALUE > 0);

-- Quantity (non-negative integer)
CREATE DOMAIN quantity_value AS INTEGER
    CHECK (VALUE >= 0);

Text Domains

-- Short descriptive text
CREATE DOMAIN short_description AS VARCHAR(255)
    CHECK (LENGTH(TRIM(VALUE)) > 0);

-- Medium text content
CREATE DOMAIN medium_text AS VARCHAR(1000)
    CHECK (LENGTH(TRIM(VALUE)) > 0);

-- Long text content
CREATE DOMAIN long_text AS TEXT
    CHECK (LENGTH(TRIM(VALUE)) > 0);

-- Name fields (no special characters)
CREATE DOMAIN name_field AS VARCHAR(100)
    CHECK (VALUE ~ '^[A-Za-z0-9\s\-\.'']+$' AND LENGTH(TRIM(VALUE)) > 0);

Composite Types

Complex data structures for related information:

Address Type

CREATE TYPE address_type AS (
    street_line1    VARCHAR(100),
    street_line2    VARCHAR(100),
    city           VARCHAR(50),
    state_province VARCHAR(50),
    postal_code    postal_code,
    country        VARCHAR(50),
    is_primary     BOOLEAN
);

-- Usage example in table
CREATE TABLE customers (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name           name_field NOT NULL,
    email          email_address,
    phone          phone_number,
    billing_address address_type,
    shipping_address address_type,
    created_at     TIMESTAMPTZ DEFAULT NOW()
);

Audit Information Type

CREATE TYPE audit_info AS (
    created_by      UUID,
    created_at      TIMESTAMPTZ,
    updated_by      UUID,
    updated_at      TIMESTAMPTZ,
    version         INTEGER
);

-- Function to update audit info
CREATE OR REPLACE FUNCTION update_audit_info(
    old_audit audit_info,
    user_id UUID
) 
RETURNS audit_info AS $$
BEGIN
    RETURN ROW(
        COALESCE(old_audit.created_by, user_id),
        COALESCE(old_audit.created_at, NOW()),
        user_id,
        NOW(),
        COALESCE(old_audit.version, 0) + 1
    )::audit_info;
END;
$$ LANGUAGE plpgsql;

Dimension Type

CREATE TYPE dimensions_type AS (
    length_cm       dimension_cm,
    width_cm        dimension_cm,
    height_cm       dimension_cm,
    weight_kg       weight_kg
);

-- Function to calculate volume
CREATE OR REPLACE FUNCTION calculate_volume(dims dimensions_type)
RETURNS volume_m3 AS $$
BEGIN
    RETURN ((dims.length_cm * dims.width_cm * dims.height_cm) / 1000000.0)::volume_m3;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Array Types

For storing multiple values of the same type:

Tag Arrays

-- Product tags for categorization
CREATE DOMAIN tag_array AS TEXT[]
    CHECK (array_length(VALUE, 1) <= 20); -- Maximum 20 tags

-- Example usage
ALTER TABLE products ADD COLUMN tags tag_array;

-- Search function for tags
CREATE OR REPLACE FUNCTION has_tag(product_tags tag_array, search_tag TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN search_tag = ANY(product_tags);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Contact Arrays

-- Multiple contact methods
CREATE TYPE contact_info AS (
    method      contact_method,
    value       TEXT,
    is_primary  BOOLEAN
);

CREATE DOMAIN contact_array AS contact_info[]
    CHECK (array_length(VALUE, 1) <= 10); -- Maximum 10 contact methods

JSON/JSONB Types

For flexible, semi-structured data:

Configuration Types

-- Product specifications as JSONB
CREATE DOMAIN product_specifications AS JSONB
    CHECK (jsonb_typeof(VALUE) = 'object');

-- System settings
CREATE DOMAIN system_settings AS JSONB
    CHECK (jsonb_typeof(VALUE) = 'object');

-- User preferences
CREATE DOMAIN user_preferences AS JSONB
    CHECK (jsonb_typeof(VALUE) = 'object');

Metadata Types

-- Flexible metadata storage
CREATE DOMAIN metadata_json AS JSONB
    CHECK (jsonb_typeof(VALUE) = 'object');

-- Example usage in products table
ALTER TABLE products ADD COLUMN metadata metadata_json;

-- Helper function to get metadata value
CREATE OR REPLACE FUNCTION get_metadata_value(
    metadata_obj metadata_json,
    key_path TEXT
)
RETURNS TEXT AS $$
BEGIN
    RETURN metadata_obj #>> string_to_array(key_path, '.');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Type Conversion Functions

Utility functions for type conversions:

-- Convert enum to human-readable text
CREATE OR REPLACE FUNCTION format_order_status(status sales_order_status)
RETURNS TEXT AS $$
BEGIN
    RETURN CASE status
        WHEN 'draft' THEN 'Draft'
        WHEN 'pending' THEN 'Pending'
        WHEN 'processing' THEN 'Processing'
        WHEN 'backordered' THEN 'Backordered'
        WHEN 'shipped' THEN 'Shipped'
        WHEN 'delivered' THEN 'Delivered'
        WHEN 'cancelled' THEN 'Cancelled'
        WHEN 'returned' THEN 'Returned'
        ELSE 'Unknown'
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Format money amounts for display
CREATE OR REPLACE FUNCTION format_money(amount money_amount, currency TEXT DEFAULT 'USD')
RETURNS TEXT AS $$
BEGIN
    RETURN FORMAT('%s %.2f', currency, amount);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Type Validation Functions

Functions to validate complex data types:

-- Validate SKU format
CREATE OR REPLACE FUNCTION validate_sku(sku TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN sku ~ '^[A-Z0-9\-]{3,50}$';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Validate email format
CREATE OR REPLACE FUNCTION validate_email(email TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Migration Considerations

When adding or modifying types:

-- Safe enum modification (adding new values)
ALTER TYPE product_status ADD VALUE 'prototype' AFTER 'draft';

-- Type conversion for existing data
UPDATE products 
SET status = 'active'::product_status 
WHERE status IS NULL;

Best Practices

Naming: Use descriptive names for custom types
Validation: Add appropriate constraints to domains
Documentation: Comment type purposes and valid values
Consistency: Use types consistently across related tables
Evolution: Plan for type evolution and migration
Performance: Consider index performance with custom types
Application Integration: Ensure ORM/application support

Core Tables - Tables using these data types
Functions & Triggers - Functions using custom types
Design Principles - Type design philosophy
Migration Scripts - Type evolution scripts

Views

Migration Scripts

Data Types

Data Types

Overview

Enumeration Types

User and Access Types

Product and Inventory Types

Order Management Types

Warehouse and Location Types

Communication and Notification Types

Domain Types

Identifier Domains

Financial Domains

Measurement Domains

Text Domains

Composite Types

Address Type

Audit Information Type

Dimension Type

Array Types

Tag Arrays

Contact Arrays

JSON/JSONB Types

Configuration Types

Metadata Types

Type Conversion Functions

Type Validation Functions

Migration Considerations

Best Practices

Related Documentation