Chapter 13: Entity Specifications

Complete SQL for All 51 Tables

13.1 Overview

This chapter provides complete CREATE TABLE statements for all 51 tables in the POS Platform database. Each table includes:

• Column definitions with data types
• Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK)
• Default values
• Comments explaining purpose

Usage: Copy-paste these statements to create the database schema.

Domain 1-2: Catalog (Products, Categories, Tags)

brands

-- Brand/manufacturer reference data
CREATE TABLE brands (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    logo_url VARCHAR(500),
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT brands_name_unique UNIQUE (name)
);

CREATE INDEX idx_brands_active ON brands(is_active) WHERE is_active = TRUE;

COMMENT ON TABLE brands IS 'Brand/manufacturer reference data for product categorization';

product_groups

-- High-level product type categorization
CREATE TABLE product_groups (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT product_groups_name_unique UNIQUE (name)
);

COMMENT ON TABLE product_groups IS 'High-level product types (Tops, Bottoms, Accessories, etc.)';

genders

-- Target demographic for products
CREATE TABLE genders (
    id SERIAL PRIMARY KEY,
    name VARCHAR(20) NOT NULL,

    CONSTRAINT genders_name_unique UNIQUE (name)
);

COMMENT ON TABLE genders IS 'Target demographic (Men, Women, Unisex, Kids)';

origins

-- Country of origin for compliance tracking
CREATE TABLE origins (
    id SERIAL PRIMARY KEY,
    country VARCHAR(100) NOT NULL,
    code VARCHAR(3),

    CONSTRAINT origins_country_unique UNIQUE (country),
    CONSTRAINT origins_code_unique UNIQUE (code)
);

COMMENT ON TABLE origins IS 'Country of origin for compliance and import tracking';
COMMENT ON COLUMN origins.code IS 'ISO 3166-1 alpha-3 country code';

fabrics

-- Material composition and care instructions
CREATE TABLE fabrics (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    care_instructions TEXT,

    CONSTRAINT fabrics_name_unique UNIQUE (name)
);

COMMENT ON TABLE fabrics IS 'Fabric/material composition (100% Cotton, Polyester Blend, etc.)';

products

-- Master product record containing shared attributes
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    brand_id INT REFERENCES brands(id) ON DELETE SET NULL,
    product_group_id INT REFERENCES product_groups(id) ON DELETE SET NULL,
    gender_id INT REFERENCES genders(id) ON DELETE SET NULL,
    origin_id INT REFERENCES origins(id) ON DELETE SET NULL,
    fabric_id INT REFERENCES fabrics(id) ON DELETE SET NULL,
    base_price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    has_variants BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP,
    deleted_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT products_price_positive CHECK (base_price >= 0),
    CONSTRAINT products_cost_positive CHECK (cost_price >= 0)
);

-- Indexes
CREATE UNIQUE INDEX idx_products_sku ON products(sku) WHERE deleted_at IS NULL;
CREATE INDEX idx_products_brand ON products(brand_id);
CREATE INDEX idx_products_group ON products(product_group_id);
CREATE INDEX idx_products_active ON products(is_active) WHERE is_active = TRUE AND deleted_at IS NULL;
CREATE INDEX idx_products_deleted ON products(deleted_at) WHERE deleted_at IS NOT NULL;
CREATE INDEX idx_products_name_search ON products USING gin(to_tsvector('english', name));

COMMENT ON TABLE products IS 'Master product catalog with shared attributes';
COMMENT ON COLUMN products.has_variants IS 'TRUE if product has size/color variants; inventory tracked at variant level';
COMMENT ON COLUMN products.deleted_at IS 'Soft delete timestamp (NULL = active)';

variants

-- Product variations (size, color) with own SKUs and inventory
CREATE TABLE variants (
    id SERIAL PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    sku VARCHAR(50) NOT NULL,
    size VARCHAR(20),
    color VARCHAR(50),
    price_adjustment DECIMAL(10,2) DEFAULT 0.00,
    weight DECIMAL(10,3),
    barcode VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    deleted_at TIMESTAMP,
    deleted_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE UNIQUE INDEX idx_variants_sku ON variants(sku) WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX idx_variants_barcode ON variants(barcode)
    WHERE barcode IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_variants_product ON variants(product_id);
CREATE INDEX idx_variants_size ON variants(size) WHERE size IS NOT NULL;
CREATE INDEX idx_variants_color ON variants(color) WHERE color IS NOT NULL;
CREATE INDEX idx_variants_deleted ON variants(deleted_at) WHERE deleted_at IS NOT NULL;

COMMENT ON TABLE variants IS 'Product variants with size/color combinations and unique SKUs';
COMMENT ON COLUMN variants.price_adjustment IS 'Price modifier from base (can be negative for discounts)';
COMMENT ON COLUMN variants.barcode IS 'UPC/EAN barcode for POS scanning';

categories

-- Hierarchical product categories
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES categories(id) ON DELETE SET NULL,
    description TEXT,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT categories_name_unique UNIQUE (name)
);

-- Indexes
CREATE INDEX idx_categories_parent ON categories(parent_id);
CREATE INDEX idx_categories_display ON categories(display_order);
CREATE INDEX idx_categories_active ON categories(is_active) WHERE is_active = TRUE;

COMMENT ON TABLE categories IS 'Hierarchical product categories (Clothing > Mens > Shirts)';
COMMENT ON COLUMN categories.parent_id IS 'Self-reference for hierarchy; NULL = root category';

collections

-- Marketing/seasonal product groupings
CREATE TABLE collections (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    image_url VARCHAR(500),
    is_active BOOLEAN DEFAULT TRUE,
    start_date TIMESTAMP,
    end_date TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT collections_name_unique UNIQUE (name),
    CONSTRAINT collections_date_order CHECK (end_date IS NULL OR start_date IS NULL OR end_date > start_date)
);

-- Indexes
CREATE INDEX idx_collections_active ON collections(is_active, start_date, end_date);
CREATE INDEX idx_collections_current ON collections(start_date, end_date)
    WHERE is_active = TRUE;

COMMENT ON TABLE collections IS 'Marketing collections (Summer 2025, Clearance, New Arrivals)';

tags

-- Flexible product tagging
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    color VARCHAR(7),

    CONSTRAINT tags_name_unique UNIQUE (name),
    CONSTRAINT tags_color_hex CHECK (color IS NULL OR color ~ '^#[0-9A-Fa-f]{6}$')
);

COMMENT ON TABLE tags IS 'Freeform product tags for quick filtering';
COMMENT ON COLUMN tags.color IS 'Hex color code for UI display (#FF5733)';

product_collection

-- Junction table: products to collections (many-to-many)
CREATE TABLE product_collection (
    id SERIAL PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    collection_id INT NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
    display_order INT DEFAULT 0,

    CONSTRAINT product_collection_unique UNIQUE (product_id, collection_id)
);

CREATE INDEX idx_product_collection_product ON product_collection(product_id);
CREATE INDEX idx_product_collection_collection ON product_collection(collection_id);

COMMENT ON TABLE product_collection IS 'Links products to marketing collections';

product_tag

-- Junction table: products to tags (many-to-many)
CREATE TABLE product_tag (
    id SERIAL PRIMARY KEY,
    product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    tag_id INT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,

    CONSTRAINT product_tag_unique UNIQUE (product_id, tag_id)
);

CREATE INDEX idx_product_tag_product ON product_tag(product_id);
CREATE INDEX idx_product_tag_tag ON product_tag(tag_id);

COMMENT ON TABLE product_tag IS 'Links products to tags for flexible categorization';

Domain 3: Inventory

locations

-- Physical stores, warehouses, and fulfillment centers
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    code VARCHAR(10) NOT NULL,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL,
    address VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    phone VARCHAR(20),
    timezone VARCHAR(50) NOT NULL DEFAULT 'America/New_York',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT locations_code_unique UNIQUE (code),
    CONSTRAINT locations_type_check CHECK (type IN ('store', 'warehouse', 'online', 'popup'))
);

CREATE INDEX idx_locations_type ON locations(type);
CREATE INDEX idx_locations_active ON locations(is_active) WHERE is_active = TRUE;

COMMENT ON TABLE locations IS 'Physical and virtual locations for inventory tracking';
COMMENT ON COLUMN locations.code IS 'Short code (GM, HM, LM, NM, HQ)';
COMMENT ON COLUMN locations.type IS 'Location type: store, warehouse, online, popup';

inventory_levels

-- Current stock quantity per variant per location
CREATE TABLE inventory_levels (
    id SERIAL PRIMARY KEY,
    variant_id INT NOT NULL REFERENCES variants(id) ON DELETE CASCADE,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
    quantity_on_hand INT DEFAULT 0,
    quantity_reserved INT DEFAULT 0,
    quantity_available INT GENERATED ALWAYS AS (quantity_on_hand - quantity_reserved) STORED,
    reorder_point INT DEFAULT 0,
    reorder_quantity INT DEFAULT 0,
    last_counted TIMESTAMP,
    deleted_at TIMESTAMP,
    deleted_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT inventory_levels_on_hand_check CHECK (quantity_on_hand >= 0 OR
        (SELECT value::boolean FROM tenant_settings WHERE key = 'allow_negative_inventory')),
    CONSTRAINT inventory_levels_reserved_check CHECK (quantity_reserved >= 0)
);

-- Indexes
CREATE UNIQUE INDEX idx_inventory_levels_lookup ON inventory_levels(variant_id, location_id)
    WHERE deleted_at IS NULL;
CREATE INDEX idx_inventory_levels_location ON inventory_levels(location_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_inventory_levels_low_stock ON inventory_levels(location_id, quantity_on_hand)
    WHERE quantity_on_hand <= reorder_point AND deleted_at IS NULL;
CREATE INDEX idx_inventory_levels_variant ON inventory_levels(variant_id);

COMMENT ON TABLE inventory_levels IS 'Current inventory quantities per variant per location';
COMMENT ON COLUMN inventory_levels.quantity_available IS 'Computed: on_hand - reserved';
COMMENT ON COLUMN inventory_levels.reorder_point IS 'Low stock alert threshold';

inventory_transactions

-- Audit log for all inventory movements (append-only)
CREATE TABLE inventory_transactions (
    id BIGSERIAL PRIMARY KEY,
    variant_id INT NOT NULL REFERENCES variants(id) ON DELETE RESTRICT,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
    transaction_type VARCHAR(20) NOT NULL,
    quantity_change INT NOT NULL,
    quantity_before INT NOT NULL,
    quantity_after INT NOT NULL,
    reference_type VARCHAR(50),
    reference_id INT,
    notes TEXT,
    user_id UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT inventory_trans_type_check CHECK (transaction_type IN (
        'sale', 'return', 'purchase', 'transfer_in', 'transfer_out',
        'adjustment', 'count', 'damage', 'theft', 'found'
    )),
    CONSTRAINT inventory_trans_math CHECK (quantity_after = quantity_before + quantity_change)
);

-- Indexes (BRIN for time-series, B-tree for lookups)
CREATE INDEX idx_inventory_trans_date ON inventory_transactions USING BRIN (created_at);
CREATE INDEX idx_inventory_trans_variant ON inventory_transactions(variant_id, created_at DESC);
CREATE INDEX idx_inventory_trans_location ON inventory_transactions(location_id, created_at DESC);
CREATE INDEX idx_inventory_trans_reference ON inventory_transactions(reference_type, reference_id)
    WHERE reference_type IS NOT NULL;
CREATE INDEX idx_inventory_trans_type ON inventory_transactions(transaction_type, created_at DESC);

COMMENT ON TABLE inventory_transactions IS 'Immutable audit log of all inventory changes';
COMMENT ON COLUMN inventory_transactions.transaction_type IS 'Type of movement: sale, return, purchase, transfer, adjustment';
COMMENT ON COLUMN inventory_transactions.reference_type IS 'Source document type (order, transfer, adjustment)';

Domain 4: Sales

customers

-- Customer profiles with loyalty tracking
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    loyalty_number VARCHAR(20),
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(20),
    address TEXT,
    loyalty_points INT DEFAULT 0,
    total_spent DECIMAL(12,2) DEFAULT 0,
    visit_count INT DEFAULT 0,
    first_visit TIMESTAMP,
    last_visit TIMESTAMP,
    deleted_at TIMESTAMP,
    deleted_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    anonymized_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT customers_points_positive CHECK (loyalty_points >= 0),
    CONSTRAINT customers_spent_positive CHECK (total_spent >= 0)
);

-- Indexes
CREATE UNIQUE INDEX idx_customers_loyalty ON customers(loyalty_number)
    WHERE loyalty_number IS NOT NULL AND deleted_at IS NULL;
CREATE UNIQUE INDEX idx_customers_email ON customers(email)
    WHERE email IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_customers_name ON customers(last_name, first_name) WHERE deleted_at IS NULL;
CREATE INDEX idx_customers_phone ON customers(phone) WHERE phone IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_customers_last_visit ON customers(last_visit DESC);

COMMENT ON TABLE customers IS 'Customer profiles with loyalty program tracking';
COMMENT ON COLUMN customers.anonymized_at IS 'GDPR: timestamp when PII was scrubbed';

orders

-- Transaction header with payment and status
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number VARCHAR(20) NOT NULL,
    customer_id INT REFERENCES customers(id) ON DELETE SET NULL,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
    user_id UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    shift_id INT REFERENCES shifts(id) ON DELETE SET NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    subtotal DECIMAL(12,2) NOT NULL,
    tax_amount DECIMAL(12,2) NOT NULL,
    discount_amount DECIMAL(12,2) DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL,
    payment_method VARCHAR(20) NOT NULL,
    payment_reference VARCHAR(100),
    notes TEXT,
    deleted_at TIMESTAMP,
    deleted_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    void_reason VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW(),
    completed_at TIMESTAMP,
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT orders_number_unique UNIQUE (order_number),
    CONSTRAINT orders_status_check CHECK (status IN ('pending', 'completed', 'refunded', 'voided', 'on_hold')),
    CONSTRAINT orders_payment_check CHECK (payment_method IN (
        'cash', 'credit', 'debit', 'mobile', 'gift_card', 'store_credit', 'split', 'check'
    )),
    CONSTRAINT orders_amounts_positive CHECK (
        subtotal >= 0 AND tax_amount >= 0 AND discount_amount >= 0 AND total_amount >= 0
    ),
    CONSTRAINT orders_total_math CHECK (
        total_amount = subtotal + tax_amount - discount_amount
    )
);

-- Indexes
CREATE INDEX idx_orders_date ON orders(created_at DESC);
CREATE INDEX idx_orders_location ON orders(location_id, created_at DESC);
CREATE INDEX idx_orders_customer ON orders(customer_id) WHERE customer_id IS NOT NULL;
CREATE INDEX idx_orders_shift ON orders(shift_id) WHERE shift_id IS NOT NULL;
CREATE INDEX idx_orders_status ON orders(status, created_at DESC);
CREATE INDEX idx_orders_number ON orders(order_number);

COMMENT ON TABLE orders IS 'Sales transaction headers with payment info';
COMMENT ON COLUMN orders.order_number IS 'Format: LOC-YYYYMMDD-SEQUENCE';
COMMENT ON COLUMN orders.void_reason IS 'Required explanation when status = voided';

order_items

-- Line items with snapshots of product data at time of sale
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    variant_id INT NOT NULL REFERENCES variants(id) ON DELETE RESTRICT,
    sku VARCHAR(50) NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    tax_amount DECIMAL(10,2) NOT NULL,
    line_total DECIMAL(10,2) NOT NULL,
    is_returned BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT order_items_quantity_positive CHECK (quantity > 0),
    CONSTRAINT order_items_amounts_positive CHECK (
        unit_price >= 0 AND discount_amount >= 0 AND tax_amount >= 0
    ),
    CONSTRAINT order_items_total_math CHECK (
        line_total = (unit_price * quantity) - discount_amount + tax_amount
    )
);

-- Indexes
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_variant ON order_items(variant_id);
CREATE INDEX idx_order_items_sku ON order_items(sku);
CREATE INDEX idx_order_items_returned ON order_items(order_id) WHERE is_returned = TRUE;

COMMENT ON TABLE order_items IS 'Order line items with point-in-time price snapshots';
COMMENT ON COLUMN order_items.sku IS 'SKU snapshot at time of sale (product may change)';
COMMENT ON COLUMN order_items.product_name IS 'Name snapshot at time of sale';

Domain 5: Customer Loyalty & Gift Cards

loyalty_accounts

-- Customer loyalty program accounts
CREATE TABLE loyalty_accounts (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    tier VARCHAR(20) NOT NULL DEFAULT 'bronze',
    points_balance INT DEFAULT 0,
    lifetime_points INT DEFAULT 0,
    tier_start_date DATE,
    tier_expiry_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT loyalty_accounts_customer_unique UNIQUE (customer_id),
    CONSTRAINT loyalty_tier_check CHECK (tier IN ('bronze', 'silver', 'gold', 'platinum')),
    CONSTRAINT loyalty_points_positive CHECK (points_balance >= 0 AND lifetime_points >= 0)
);

CREATE INDEX idx_loyalty_tier ON loyalty_accounts(tier) WHERE is_active = TRUE;

COMMENT ON TABLE loyalty_accounts IS 'Customer loyalty program tier and points tracking';

loyalty_transactions

-- Loyalty points earn/redeem history
CREATE TABLE loyalty_transactions (
    id BIGSERIAL PRIMARY KEY,
    loyalty_account_id INT NOT NULL REFERENCES loyalty_accounts(id) ON DELETE CASCADE,
    order_id INT REFERENCES orders(id) ON DELETE SET NULL,
    transaction_type VARCHAR(20) NOT NULL,
    points INT NOT NULL,
    points_balance_after INT NOT NULL,
    description VARCHAR(255),
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT loyalty_trans_type_check CHECK (transaction_type IN (
        'earn', 'redeem', 'expire', 'adjust', 'bonus', 'transfer'
    ))
);

CREATE INDEX idx_loyalty_trans_account ON loyalty_transactions(loyalty_account_id, created_at DESC);
CREATE INDEX idx_loyalty_trans_order ON loyalty_transactions(order_id) WHERE order_id IS NOT NULL;
CREATE INDEX idx_loyalty_trans_expiry ON loyalty_transactions(expires_at)
    WHERE expires_at IS NOT NULL AND transaction_type = 'earn';

COMMENT ON TABLE loyalty_transactions IS 'Audit trail of loyalty point changes';

gift_cards

-- Gift card issuance and balance tracking
CREATE TABLE gift_cards (
    id SERIAL PRIMARY KEY,
    card_number VARCHAR(20) NOT NULL,
    pin_hash VARCHAR(255),
    initial_balance DECIMAL(10,2) NOT NULL,
    current_balance DECIMAL(10,2) NOT NULL,
    currency_code CHAR(3) DEFAULT 'USD',
    issued_at TIMESTAMP DEFAULT NOW(),
    expires_at TIMESTAMP,
    issued_by UUID REFERENCES shared.users(id),
    issued_location_id INT REFERENCES locations(id),
    purchased_order_id INT REFERENCES orders(id),
    is_active BOOLEAN DEFAULT TRUE,
    deactivated_at TIMESTAMP,
    deactivated_reason VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT gift_cards_number_unique UNIQUE (card_number),
    CONSTRAINT gift_cards_balance_positive CHECK (initial_balance > 0 AND current_balance >= 0),
    CONSTRAINT gift_cards_balance_max CHECK (current_balance <= initial_balance)
);

CREATE INDEX idx_gift_cards_number ON gift_cards(card_number);
CREATE INDEX idx_gift_cards_active ON gift_cards(is_active, expires_at);

COMMENT ON TABLE gift_cards IS 'Gift card issuance and balance management';
COMMENT ON COLUMN gift_cards.pin_hash IS 'Optional PIN for additional security (hashed)';

gift_card_transactions

-- Gift card usage history
CREATE TABLE gift_card_transactions (
    id BIGSERIAL PRIMARY KEY,
    gift_card_id INT NOT NULL REFERENCES gift_cards(id) ON DELETE CASCADE,
    order_id INT REFERENCES orders(id) ON DELETE SET NULL,
    transaction_type VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    balance_after DECIMAL(10,2) NOT NULL,
    location_id INT REFERENCES locations(id),
    user_id UUID REFERENCES shared.users(id),
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT gift_card_trans_type CHECK (transaction_type IN (
        'issue', 'redeem', 'reload', 'refund', 'adjust', 'expire'
    )),
    CONSTRAINT gift_card_trans_amount CHECK (amount > 0)
);

CREATE INDEX idx_gift_card_trans_card ON gift_card_transactions(gift_card_id, created_at DESC);
CREATE INDEX idx_gift_card_trans_order ON gift_card_transactions(order_id) WHERE order_id IS NOT NULL;

COMMENT ON TABLE gift_card_transactions IS 'Audit trail of gift card balance changes';

Domain 6-7: Returns & Reporting

returns

-- Return/exchange header
CREATE TABLE returns (
    id SERIAL PRIMARY KEY,
    return_number VARCHAR(20) NOT NULL,
    original_order_id INT NOT NULL REFERENCES orders(id) ON DELETE RESTRICT,
    customer_id INT REFERENCES customers(id) ON DELETE SET NULL,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
    user_id UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    return_type VARCHAR(20) NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL,
    tax_amount DECIMAL(12,2) NOT NULL,
    refund_amount DECIMAL(12,2) NOT NULL,
    refund_method VARCHAR(20) NOT NULL,
    reason VARCHAR(255),
    notes TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    completed_at TIMESTAMP,

    CONSTRAINT returns_number_unique UNIQUE (return_number),
    CONSTRAINT returns_status_check CHECK (status IN ('pending', 'approved', 'completed', 'rejected')),
    CONSTRAINT returns_type_check CHECK (return_type IN ('refund', 'exchange', 'store_credit')),
    CONSTRAINT returns_method_check CHECK (refund_method IN (
        'original_payment', 'cash', 'store_credit', 'gift_card'
    ))
);

CREATE INDEX idx_returns_order ON returns(original_order_id);
CREATE INDEX idx_returns_date ON returns(created_at DESC);
CREATE INDEX idx_returns_status ON returns(status);

COMMENT ON TABLE returns IS 'Return and exchange transaction headers';

return_items

-- Individual items being returned
CREATE TABLE return_items (
    id SERIAL PRIMARY KEY,
    return_id INT NOT NULL REFERENCES returns(id) ON DELETE CASCADE,
    order_item_id INT NOT NULL REFERENCES order_items(id) ON DELETE RESTRICT,
    variant_id INT NOT NULL REFERENCES variants(id) ON DELETE RESTRICT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    refund_amount DECIMAL(10,2) NOT NULL,
    reason VARCHAR(50),
    condition VARCHAR(20) DEFAULT 'sellable',
    restocked BOOLEAN DEFAULT FALSE,
    restocked_location_id INT REFERENCES locations(id),
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT return_items_quantity_positive CHECK (quantity > 0),
    CONSTRAINT return_items_condition_check CHECK (condition IN (
        'sellable', 'damaged', 'defective', 'other'
    ))
);

CREATE INDEX idx_return_items_return ON return_items(return_id);
CREATE INDEX idx_return_items_variant ON return_items(variant_id);

COMMENT ON TABLE return_items IS 'Individual items in a return transaction';

reports (User Preferences)

-- Saved report configurations
CREATE TABLE reports (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    report_type VARCHAR(50) NOT NULL,
    description TEXT,
    query_config JSONB NOT NULL,
    schedule_config JSONB,
    is_system BOOLEAN DEFAULT FALSE,
    is_public BOOLEAN DEFAULT FALSE,
    created_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_reports_type ON reports(report_type);
CREATE INDEX idx_reports_public ON reports(is_public) WHERE is_public = TRUE;

COMMENT ON TABLE reports IS 'Saved report configurations and schedules';

item_view_settings (user_preferences)

-- Personalized view preferences for inventory screens
CREATE TABLE item_view_settings (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES shared.users(id) ON DELETE CASCADE,
    view_type VARCHAR(10) DEFAULT 'list',
    visible_columns JSONB,
    sort_preferences JSONB,
    filter_defaults JSONB,
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT item_view_settings_user_unique UNIQUE (user_id),
    CONSTRAINT item_view_settings_type_check CHECK (view_type IN ('list', 'grid', 'compact'))
);

COMMENT ON TABLE item_view_settings IS 'User-specific inventory view preferences';

Domain 8: Multi-tenant (Shared Schema)

See Chapter 12 for complete shared schema tables: tenants, tenant_subscriptions, tenant_modules

Domain 9: Authentication & Authorization

roles (Tenant Schema)

-- Role definitions per tenant
CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    is_system BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT roles_name_unique UNIQUE (name)
);

COMMENT ON TABLE roles IS 'Role definitions customizable per tenant';
COMMENT ON COLUMN roles.is_system IS 'System roles (Owner, Admin, etc.) cannot be deleted';

role_permissions

-- Permission matrix linking roles to permissions
CREATE TABLE role_permissions (
    id SERIAL PRIMARY KEY,
    role_id INT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    permission VARCHAR(100) NOT NULL,
    granted BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT role_permissions_unique UNIQUE (role_id, permission)
);

CREATE INDEX idx_role_permissions_role ON role_permissions(role_id);
CREATE INDEX idx_role_permissions_permission ON role_permissions(permission);

COMMENT ON TABLE role_permissions IS 'Fine-grained permission assignments per role';
COMMENT ON COLUMN role_permissions.permission IS 'Permission ID (products.view, orders.create, etc.)';

tenant_users

-- User-tenant-role mapping
CREATE TABLE tenant_users (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES shared.users(id) ON DELETE CASCADE,
    role_id INT NOT NULL REFERENCES roles(id) ON DELETE RESTRICT,
    employee_id VARCHAR(20),
    pin_hash VARCHAR(255),
    hourly_rate DECIMAL(8,2),
    commission_rate DECIMAL(5,4),
    default_location_id INT REFERENCES locations(id) ON DELETE SET NULL,
    is_active BOOLEAN DEFAULT TRUE,
    hired_at DATE,
    terminated_at DATE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT tenant_users_user_unique UNIQUE (user_id),
    CONSTRAINT tenant_users_employee_unique UNIQUE (employee_id)
);

CREATE INDEX idx_tenant_users_role ON tenant_users(role_id);
CREATE INDEX idx_tenant_users_location ON tenant_users(default_location_id);
CREATE INDEX idx_tenant_users_active ON tenant_users(is_active) WHERE is_active = TRUE;

COMMENT ON TABLE tenant_users IS 'Links platform users to tenant with role assignment';
COMMENT ON COLUMN tenant_users.employee_id IS 'Short ID for quick POS login';
COMMENT ON COLUMN tenant_users.pin_hash IS 'Quick login PIN (not primary auth)';

tenant_settings

-- Per-tenant configuration settings
CREATE TABLE tenant_settings (
    id SERIAL PRIMARY KEY,
    category VARCHAR(50) NOT NULL,
    key VARCHAR(100) NOT NULL,
    value TEXT NOT NULL,
    value_type VARCHAR(20) NOT NULL,
    description TEXT,
    is_secret BOOLEAN DEFAULT FALSE,
    updated_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT tenant_settings_key_unique UNIQUE (category, key),
    CONSTRAINT tenant_settings_type_check CHECK (value_type IN ('string', 'number', 'boolean', 'json'))
);

CREATE INDEX idx_tenant_settings_category ON tenant_settings(category);

COMMENT ON TABLE tenant_settings IS 'Key-value configuration settings per tenant';
COMMENT ON COLUMN tenant_settings.is_secret IS 'Mask value in UI (API keys, passwords)';

Domain 10: Offline Sync Infrastructure

devices

-- POS terminals and device registration
CREATE TABLE devices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    device_type VARCHAR(30) NOT NULL,
    hardware_id VARCHAR(255) NOT NULL,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
    cash_drawer_id INT REFERENCES cash_drawers(id) ON DELETE SET NULL,
    payment_terminal_id INT REFERENCES payment_terminals(id) ON DELETE SET NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    last_sync_at TIMESTAMP,
    last_seen_at TIMESTAMP,
    app_version VARCHAR(20),
    os_version VARCHAR(50),
    ip_address INET,
    push_token VARCHAR(500),
    settings JSONB,
    registered_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT devices_hardware_unique UNIQUE (hardware_id),
    CONSTRAINT devices_type_check CHECK (device_type IN ('pos_terminal', 'tablet', 'mobile', 'kiosk')),
    CONSTRAINT devices_status_check CHECK (status IN ('pending', 'active', 'disabled', 'lost'))
);

CREATE INDEX idx_devices_location ON devices(location_id);
CREATE INDEX idx_devices_status ON devices(status);
CREATE INDEX idx_devices_last_seen ON devices(last_seen_at);

COMMENT ON TABLE devices IS 'Registered POS devices and tablets';
COMMENT ON COLUMN devices.hardware_id IS 'Unique hardware fingerprint to prevent cloning';

sync_queue

-- Pending sync operations from offline devices
CREATE TABLE sync_queue (
    id BIGSERIAL PRIMARY KEY,
    device_id UUID NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    idempotency_key UUID NOT NULL DEFAULT gen_random_uuid(),
    operation_type VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id VARCHAR(100) NOT NULL,
    payload JSONB NOT NULL,
    checksum VARCHAR(64) NOT NULL,
    sequence_number BIGINT NOT NULL,
    causality_version BIGINT NOT NULL DEFAULT 0,
    priority INT DEFAULT 5,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    attempts INT DEFAULT 0,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    processed_at TIMESTAMP,

    CONSTRAINT sync_queue_idempotency_unique UNIQUE (idempotency_key),
    CONSTRAINT sync_queue_status_check CHECK (status IN (
        'pending', 'processing', 'completed', 'failed', 'conflict'
    )),
    CONSTRAINT sync_queue_priority_check CHECK (priority BETWEEN 1 AND 10)
);

CREATE INDEX idx_sync_queue_device ON sync_queue(device_id, sequence_number);
CREATE INDEX idx_sync_queue_status ON sync_queue(status, priority, created_at);
CREATE INDEX idx_sync_queue_entity ON sync_queue(entity_type, entity_id);
CREATE INDEX idx_sync_queue_pending ON sync_queue(device_id, processed_at) WHERE processed_at IS NULL;

COMMENT ON TABLE sync_queue IS 'Pending sync operations from offline devices';
COMMENT ON COLUMN sync_queue.idempotency_key IS 'Prevents duplicate processing on replay';
COMMENT ON COLUMN sync_queue.causality_version IS 'Lamport timestamp for event ordering';

sync_conflicts

-- Conflict types enum
CREATE TYPE conflict_type_enum AS ENUM (
    'update_update',
    'update_delete',
    'delete_update',
    'version_mismatch',
    'schema_change'
);

CREATE TYPE resolution_strategy_enum AS ENUM (
    'keep_local',
    'keep_server',
    'merge',
    'ignore',
    'auto_local',
    'auto_server'
);

-- Conflict tracking requiring resolution
CREATE TABLE sync_conflicts (
    id SERIAL PRIMARY KEY,
    sync_queue_id BIGINT NOT NULL REFERENCES sync_queue(id) ON DELETE CASCADE,
    entity_type VARCHAR(50) NOT NULL,
    entity_id VARCHAR(100) NOT NULL,
    local_data JSONB NOT NULL,
    server_data JSONB NOT NULL,
    conflict_type conflict_type_enum NOT NULL,
    resolution resolution_strategy_enum,
    resolution_data JSONB,
    resolution_notes TEXT,
    resolved_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    resolved_at TIMESTAMP,
    auto_resolved BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_sync_conflicts_entity ON sync_conflicts(entity_type, entity_id);
CREATE INDEX idx_sync_conflicts_unresolved ON sync_conflicts(created_at) WHERE resolved_at IS NULL;
CREATE INDEX idx_sync_conflicts_type ON sync_conflicts(conflict_type);

COMMENT ON TABLE sync_conflicts IS 'Sync conflicts requiring manual or automatic resolution';
COMMENT ON COLUMN sync_conflicts.auto_resolved IS 'TRUE if resolved by policy without human intervention';

sync_checkpoints

-- Sync progress tracking per device
CREATE TABLE sync_checkpoints (
    id SERIAL PRIMARY KEY,
    device_id UUID NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    entity_type VARCHAR(50) NOT NULL,
    direction VARCHAR(10) NOT NULL,
    last_sync_at TIMESTAMP NOT NULL,
    last_sequence BIGINT NOT NULL,
    last_server_timestamp TIMESTAMP,
    records_synced INT DEFAULT 0,
    error_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT sync_checkpoints_unique UNIQUE (device_id, entity_type, direction),
    CONSTRAINT sync_checkpoints_direction_check CHECK (direction IN ('push', 'pull'))
);

CREATE INDEX idx_sync_checkpoints_device ON sync_checkpoints(device_id);

COMMENT ON TABLE sync_checkpoints IS 'Tracks sync progress for incremental synchronization';

Domain 11: Cash Drawer Operations

shifts

-- Shift lifecycle management
CREATE TABLE shifts (
    id SERIAL PRIMARY KEY,
    shift_number VARCHAR(20) NOT NULL,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
    cash_drawer_id INT NOT NULL REFERENCES cash_drawers(id) ON DELETE RESTRICT,
    device_id UUID REFERENCES devices(id) ON DELETE SET NULL,
    opened_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
    closed_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    opened_at TIMESTAMP NOT NULL DEFAULT NOW(),
    closed_at TIMESTAMP,
    opening_cash DECIMAL(12,2) NOT NULL,
    expected_cash DECIMAL(12,2),
    actual_cash DECIMAL(12,2),
    cash_variance DECIMAL(12,2),
    total_sales DECIMAL(12,2) DEFAULT 0,
    total_refunds DECIMAL(12,2) DEFAULT 0,
    total_voids DECIMAL(12,2) DEFAULT 0,
    transaction_count INT DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT shifts_number_unique UNIQUE (shift_number),
    CONSTRAINT shifts_status_check CHECK (status IN ('open', 'closing', 'closed', 'reconciled')),
    CONSTRAINT shifts_opening_positive CHECK (opening_cash >= 0)
);

CREATE INDEX idx_shifts_location ON shifts(location_id, opened_at DESC);
CREATE INDEX idx_shifts_drawer_open ON shifts(cash_drawer_id, status) WHERE status = 'open';
CREATE INDEX idx_shifts_date ON shifts(opened_at DESC);

COMMENT ON TABLE shifts IS 'Employee shift lifecycle with cash accountability';
COMMENT ON COLUMN shifts.shift_number IS 'Format: LOC-YYYYMMDD-SEQUENCE';

cash_drawers

-- Physical cash drawer registration
CREATE TABLE cash_drawers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    drawer_number VARCHAR(20) NOT NULL,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
    current_shift_id INT,  -- FK added after shifts table exists
    status VARCHAR(20) NOT NULL DEFAULT 'available',
    is_active BOOLEAN DEFAULT TRUE,
    last_counted_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT cash_drawers_number_unique UNIQUE (drawer_number),
    CONSTRAINT cash_drawers_status_check CHECK (status IN ('available', 'in_use', 'maintenance'))
);

-- Add FK after shifts table exists
ALTER TABLE cash_drawers ADD CONSTRAINT cash_drawers_shift_fk
    FOREIGN KEY (current_shift_id) REFERENCES shifts(id) ON DELETE SET NULL;

CREATE INDEX idx_cash_drawers_location ON cash_drawers(location_id);
CREATE INDEX idx_cash_drawers_status ON cash_drawers(status, location_id);

COMMENT ON TABLE cash_drawers IS 'Physical cash drawer registration and status';

cash_counts

-- Detailed cash denomination counts
CREATE TABLE cash_counts (
    id SERIAL PRIMARY KEY,
    shift_id INT NOT NULL REFERENCES shifts(id) ON DELETE CASCADE,
    count_type VARCHAR(20) NOT NULL,
    counted_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
    count_timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
    -- Coins
    pennies INT DEFAULT 0,
    nickels INT DEFAULT 0,
    dimes INT DEFAULT 0,
    quarters INT DEFAULT 0,
    half_dollars INT DEFAULT 0,
    dollar_coins INT DEFAULT 0,
    -- Bills
    ones INT DEFAULT 0,
    twos INT DEFAULT 0,
    fives INT DEFAULT 0,
    tens INT DEFAULT 0,
    twenties INT DEFAULT 0,
    fifties INT DEFAULT 0,
    hundreds INT DEFAULT 0,
    -- Other
    rolled_coins DECIMAL(10,2) DEFAULT 0,
    other_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL,
    notes TEXT,

    CONSTRAINT cash_counts_type_check CHECK (count_type IN (
        'opening', 'closing', 'drop', 'pickup', 'audit', 'mid_shift'
    )),
    CONSTRAINT cash_counts_total_positive CHECK (total_amount >= 0)
);

CREATE INDEX idx_cash_counts_shift ON cash_counts(shift_id, count_type);
CREATE INDEX idx_cash_counts_timestamp ON cash_counts(count_timestamp DESC);

COMMENT ON TABLE cash_counts IS 'Denomination-level cash counts for accountability';

cash_movements

-- Cash audit trail for all drawer operations
CREATE TABLE cash_movements (
    id SERIAL PRIMARY KEY,
    shift_id INT NOT NULL REFERENCES shifts(id) ON DELETE CASCADE,
    cash_drawer_id INT NOT NULL REFERENCES cash_drawers(id) ON DELETE RESTRICT,
    movement_type VARCHAR(30) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    reference_type VARCHAR(50),
    reference_id INT,
    performed_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
    approved_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    reason VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT cash_movements_type_check CHECK (movement_type IN (
        'sale_cash', 'refund_cash', 'paid_in', 'paid_out',
        'cash_drop', 'cash_pickup', 'opening_float', 'closing_count', 'no_sale'
    ))
);

CREATE INDEX idx_cash_movements_shift ON cash_movements(shift_id);
CREATE INDEX idx_cash_movements_drawer ON cash_movements(cash_drawer_id, created_at DESC);
CREATE INDEX idx_cash_movements_type ON cash_movements(movement_type);
CREATE INDEX idx_cash_movements_reference ON cash_movements(reference_type, reference_id)
    WHERE reference_type IS NOT NULL;

COMMENT ON TABLE cash_movements IS 'Immutable audit trail of all cash drawer operations';

cash_drops

-- Cash drops from drawer to safe
CREATE TABLE cash_drops (
    id SERIAL PRIMARY KEY,
    shift_id INT NOT NULL REFERENCES shifts(id) ON DELETE CASCADE,
    cash_drawer_id INT NOT NULL REFERENCES cash_drawers(id) ON DELETE RESTRICT,
    drop_number VARCHAR(20) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    dropped_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
    witnessed_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    safe_bag_number VARCHAR(50),
    counted_amount DECIMAL(12,2),
    variance DECIMAL(12,2),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    notes TEXT,
    dropped_at TIMESTAMP NOT NULL DEFAULT NOW(),
    verified_at TIMESTAMP,

    CONSTRAINT cash_drops_number_unique UNIQUE (drop_number),
    CONSTRAINT cash_drops_amount_positive CHECK (amount > 0),
    CONSTRAINT cash_drops_status_check CHECK (status IN ('pending', 'verified', 'variance'))
);

CREATE INDEX idx_cash_drops_shift ON cash_drops(shift_id);
CREATE INDEX idx_cash_drops_status ON cash_drops(status) WHERE status = 'pending';

COMMENT ON TABLE cash_drops IS 'Cash drops from drawer to safe with verification tracking';

cash_pickups

-- Armored car pickup tracking
CREATE TABLE cash_pickups (
    id SERIAL PRIMARY KEY,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
    pickup_date DATE NOT NULL,
    carrier VARCHAR(100) NOT NULL,
    driver_name VARCHAR(100),
    driver_id VARCHAR(50),
    pickup_number VARCHAR(50),
    expected_amount DECIMAL(12,2) NOT NULL,
    bag_count INT NOT NULL,
    bag_numbers TEXT[],
    received_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
    verified_amount DECIMAL(12,2),
    variance DECIMAL(12,2),
    status VARCHAR(20) NOT NULL DEFAULT 'picked_up',
    notes TEXT,
    picked_up_at TIMESTAMP NOT NULL DEFAULT NOW(),
    deposited_at TIMESTAMP,

    CONSTRAINT cash_pickups_number_unique UNIQUE (pickup_number),
    CONSTRAINT cash_pickups_status_check CHECK (status IN (
        'picked_up', 'in_transit', 'deposited', 'variance'
    ))
);

CREATE INDEX idx_cash_pickups_location ON cash_pickups(location_id, pickup_date DESC);
CREATE INDEX idx_cash_pickups_status ON cash_pickups(status);

COMMENT ON TABLE cash_pickups IS 'Armored car pickup and bank deposit tracking';

Domain 12: Payment Processing

payment_terminals

-- Payment device registration
CREATE TABLE payment_terminals (
    id SERIAL PRIMARY KEY,
    terminal_id VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
    device_id UUID REFERENCES devices(id) ON DELETE SET NULL,
    processor VARCHAR(50) NOT NULL,
    terminal_type VARCHAR(30) NOT NULL,
    model VARCHAR(100),
    serial_number VARCHAR(100),
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    supports_contactless BOOLEAN DEFAULT TRUE,
    supports_emv BOOLEAN DEFAULT TRUE,
    supports_swipe BOOLEAN DEFAULT TRUE,
    last_transaction_at TIMESTAMP,
    last_batch_at TIMESTAMP,
    configuration JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT payment_terminals_id_unique UNIQUE (terminal_id),
    CONSTRAINT payment_terminals_type_check CHECK (terminal_type IN (
        'integrated', 'standalone', 'virtual', 'mobile'
    )),
    CONSTRAINT payment_terminals_status_check CHECK (status IN (
        'active', 'offline', 'maintenance', 'disabled'
    ))
);

CREATE INDEX idx_payment_terminals_location ON payment_terminals(location_id);
CREATE INDEX idx_payment_terminals_status ON payment_terminals(status);
CREATE INDEX idx_payment_terminals_device ON payment_terminals(device_id) WHERE device_id IS NOT NULL;

COMMENT ON TABLE payment_terminals IS 'Payment device registration and configuration';

payment_attempts

-- Payment processing attempt tracking
CREATE TABLE payment_attempts (
    id BIGSERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(id) ON DELETE RESTRICT,
    terminal_id INT REFERENCES payment_terminals(id) ON DELETE SET NULL,
    payment_method VARCHAR(30) NOT NULL,
    card_type VARCHAR(20),
    card_last_four CHAR(4),
    card_entry_mode VARCHAR(20),
    amount DECIMAL(12,2) NOT NULL,
    tip_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL,
    currency_code CHAR(3) NOT NULL DEFAULT 'USD',
    status VARCHAR(20) NOT NULL,
    processor_response_code VARCHAR(10),
    processor_response_text VARCHAR(255),
    authorization_code VARCHAR(20),
    processor_transaction_id VARCHAR(100),
    avs_result VARCHAR(10),
    cvv_result VARCHAR(10),
    emv_application_id VARCHAR(32),
    emv_cryptogram VARCHAR(64),
    risk_score INT,
    created_at TIMESTAMP DEFAULT NOW(),
    processed_at TIMESTAMP,

    CONSTRAINT payment_attempts_method_check CHECK (payment_method IN (
        'card', 'cash', 'gift_card', 'store_credit', 'check', 'mobile_pay'
    )),
    CONSTRAINT payment_attempts_status_check CHECK (status IN (
        'pending', 'approved', 'declined', 'error', 'voided', 'refunded'
    )),
    CONSTRAINT payment_attempts_entry_check CHECK (card_entry_mode IS NULL OR card_entry_mode IN (
        'chip', 'swipe', 'contactless', 'manual', 'ecommerce', 'fallback'
    ))
);

CREATE INDEX idx_payment_attempts_order ON payment_attempts(order_id);
CREATE INDEX idx_payment_attempts_status ON payment_attempts(status, created_at DESC);
CREATE INDEX idx_payment_attempts_processor ON payment_attempts(processor_transaction_id)
    WHERE processor_transaction_id IS NOT NULL;
CREATE INDEX idx_payment_attempts_date ON payment_attempts(created_at DESC);

COMMENT ON TABLE payment_attempts IS 'Payment processing attempts with full audit trail';
COMMENT ON COLUMN payment_attempts.emv_cryptogram IS 'EMV TC/ARQC for chip transactions';

payment_batches

-- End-of-day settlement batch tracking
CREATE TABLE payment_batches (
    id SERIAL PRIMARY KEY,
    batch_number VARCHAR(50) NOT NULL,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
    terminal_id INT REFERENCES payment_terminals(id) ON DELETE SET NULL,
    processor VARCHAR(50) NOT NULL,
    batch_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    transaction_count INT NOT NULL DEFAULT 0,
    gross_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    refund_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    net_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    fee_amount DECIMAL(10,2),
    deposit_amount DECIMAL(12,2),
    opened_at TIMESTAMP NOT NULL DEFAULT NOW(),
    submitted_at TIMESTAMP,
    settled_at TIMESTAMP,
    deposit_reference VARCHAR(100),
    notes TEXT,

    CONSTRAINT payment_batches_number_unique UNIQUE (batch_number),
    CONSTRAINT payment_batches_status_check CHECK (status IN (
        'open', 'pending', 'settled', 'rejected'
    )),
    CONSTRAINT payment_batches_net_math CHECK (net_amount = gross_amount - refund_amount)
);

CREATE INDEX idx_payment_batches_location ON payment_batches(location_id, batch_date DESC);
CREATE INDEX idx_payment_batches_status ON payment_batches(status) WHERE status IN ('open', 'pending');
CREATE INDEX idx_payment_batches_date ON payment_batches(batch_date DESC);

COMMENT ON TABLE payment_batches IS 'End-of-day settlement batch tracking';

payment_reconciliation

-- Variance tracking between POS and processor/bank
CREATE TABLE payment_reconciliation (
    id SERIAL PRIMARY KEY,
    batch_id INT NOT NULL REFERENCES payment_batches(id) ON DELETE CASCADE,
    reconciliation_date DATE NOT NULL,
    pos_transaction_count INT NOT NULL,
    processor_transaction_count INT NOT NULL,
    transaction_count_variance INT NOT NULL,
    pos_gross_amount DECIMAL(12,2) NOT NULL,
    processor_gross_amount DECIMAL(12,2) NOT NULL,
    gross_variance DECIMAL(12,2) NOT NULL,
    pos_net_amount DECIMAL(12,2) NOT NULL,
    bank_deposit_amount DECIMAL(12,2),
    deposit_variance DECIMAL(12,2),
    fee_variance DECIMAL(10,2),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    variance_reason TEXT,
    resolved_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    resolved_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT payment_recon_status_check CHECK (status IN (
        'pending', 'matched', 'variance', 'resolved'
    ))
);

CREATE INDEX idx_payment_recon_batch ON payment_reconciliation(batch_id);
CREATE INDEX idx_payment_recon_date ON payment_reconciliation(reconciliation_date DESC);
CREATE INDEX idx_payment_recon_status ON payment_reconciliation(status)
    WHERE status IN ('pending', 'variance');

COMMENT ON TABLE payment_reconciliation IS 'Payment reconciliation with variance tracking';

Domain 13: RFID Module (Optional)

taxes

-- Tax rate definitions
CREATE TABLE taxes (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) NOT NULL,
    rate DECIMAL(5,4) NOT NULL,
    is_compound BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT taxes_name_unique UNIQUE (name),
    CONSTRAINT taxes_code_unique UNIQUE (code),
    CONSTRAINT taxes_rate_check CHECK (rate >= 0 AND rate <= 1)
);

COMMENT ON TABLE taxes IS 'Tax rate definitions (rate as decimal: 0.0825 = 8.25%)';

location_tax

-- Junction: taxes to locations with date ranges
CREATE TABLE location_tax (
    id SERIAL PRIMARY KEY,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
    tax_id INT NOT NULL REFERENCES taxes(id) ON DELETE CASCADE,
    effective_from TIMESTAMP NOT NULL,
    effective_to TIMESTAMP,

    CONSTRAINT location_tax_dates CHECK (effective_to IS NULL OR effective_to > effective_from)
);

CREATE INDEX idx_location_tax_effective ON location_tax(location_id, effective_from, effective_to);

COMMENT ON TABLE location_tax IS 'Assigns tax rates to locations with effective date ranges';

rfid_config

-- Tenant RFID configuration
CREATE TABLE rfid_config (
    id SERIAL PRIMARY KEY,
    epc_company_prefix VARCHAR(24) NOT NULL,
    epc_indicator CHAR(1) NOT NULL DEFAULT '0',
    epc_filter CHAR(1) NOT NULL DEFAULT '3',
    epc_partition INT NOT NULL DEFAULT 5,
    last_serial_number BIGINT NOT NULL DEFAULT 0,
    default_template_id UUID,
    default_printer_id UUID,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

COMMENT ON TABLE rfid_config IS 'Tenant RFID configuration for EPC encoding';
COMMENT ON COLUMN rfid_config.last_serial_number IS 'Auto-incrementing serial (never decrements)';

rfid_printers

-- Registered RFID printers
CREATE TABLE rfid_printers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
    printer_type VARCHAR(50) NOT NULL,
    connection_type VARCHAR(20) NOT NULL,
    ip_address INET,
    port INT DEFAULT 9100,
    mac_address VARCHAR(17),
    serial_number VARCHAR(100),
    firmware_version VARCHAR(50),
    dpi INT DEFAULT 203,
    label_width_mm INT NOT NULL,
    label_height_mm INT NOT NULL,
    rfid_position VARCHAR(20) DEFAULT 'center',
    status VARCHAR(20) NOT NULL DEFAULT 'offline',
    last_seen_at TIMESTAMP,
    error_message TEXT,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT rfid_printers_type_check CHECK (printer_type IN (
        'zebra_zd621r', 'zebra_zd500r', 'sato_cl4nx', 'tsc_mx240p'
    )),
    CONSTRAINT rfid_printers_conn_check CHECK (connection_type IN ('network', 'usb', 'bluetooth'))
);

CREATE INDEX idx_rfid_printers_location ON rfid_printers(location_id);
CREATE INDEX idx_rfid_printers_status ON rfid_printers(status);
CREATE UNIQUE INDEX idx_rfid_printers_default ON rfid_printers(location_id) WHERE is_default = TRUE;

COMMENT ON TABLE rfid_printers IS 'RFID-enabled printers registered per location';

rfid_print_jobs

-- Print job queue
CREATE TABLE rfid_print_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_number VARCHAR(20) NOT NULL,
    printer_id UUID NOT NULL REFERENCES rfid_printers(id) ON DELETE RESTRICT,
    template_id UUID NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'queued',
    priority INT DEFAULT 5,
    total_tags INT NOT NULL,
    printed_tags INT DEFAULT 0,
    failed_tags INT DEFAULT 0,
    error_message TEXT,
    job_data JSONB NOT NULL,
    created_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT rfid_print_jobs_number_unique UNIQUE (job_number),
    CONSTRAINT rfid_print_jobs_status_check CHECK (status IN (
        'queued', 'printing', 'completed', 'failed', 'cancelled'
    )),
    CONSTRAINT rfid_print_jobs_priority_check CHECK (priority BETWEEN 1 AND 10)
);

CREATE INDEX idx_rfid_print_jobs_status ON rfid_print_jobs(status, priority, created_at)
    WHERE status IN ('queued', 'printing');
CREATE INDEX idx_rfid_print_jobs_printer ON rfid_print_jobs(printer_id, created_at DESC);

COMMENT ON TABLE rfid_print_jobs IS 'RFID tag print job queue with progress tracking';

rfid_tags

-- Individual RFID tags linked to variants
CREATE TABLE rfid_tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    epc VARCHAR(24) NOT NULL,
    variant_id INT NOT NULL REFERENCES variants(id) ON DELETE RESTRICT,
    serial_number BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    print_job_id UUID REFERENCES rfid_print_jobs(id) ON DELETE SET NULL,
    printed_at TIMESTAMP,
    printed_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    current_location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
    last_scanned_at TIMESTAMP,
    last_scanned_session_id UUID,
    sold_at TIMESTAMP,
    sold_order_id INT REFERENCES orders(id) ON DELETE SET NULL,
    transferred_at TIMESTAMP,
    notes TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT rfid_tags_epc_unique UNIQUE (epc),
    CONSTRAINT rfid_tags_status_check CHECK (status IN (
        'active', 'sold', 'transferred', 'lost', 'void', 'returned'
    ))
);

CREATE INDEX idx_rfid_tags_variant ON rfid_tags(variant_id);
CREATE INDEX idx_rfid_tags_location ON rfid_tags(current_location_id, status);
CREATE INDEX idx_rfid_tags_serial ON rfid_tags(serial_number);
CREATE INDEX idx_rfid_tags_status ON rfid_tags(status) WHERE status = 'active';
CREATE INDEX idx_rfid_tags_scan ON rfid_tags(last_scanned_at DESC) WHERE last_scanned_at IS NOT NULL;

COMMENT ON TABLE rfid_tags IS 'Individual RFID tags with lifecycle tracking';
COMMENT ON COLUMN rfid_tags.epc IS 'SGTIN-96 Electronic Product Code (hex)';

rfid_scan_sessions

-- Inventory scan sessions
CREATE TABLE rfid_scan_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_number VARCHAR(20) NOT NULL,
    location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
    session_type VARCHAR(30) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'in_progress',
    device_id UUID REFERENCES devices(id) ON DELETE SET NULL,
    started_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
    completed_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
    started_at TIMESTAMP NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMP,
    total_reads INT DEFAULT 0,
    unique_tags INT DEFAULT 0,
    matched_tags INT DEFAULT 0,
    unmatched_tags INT DEFAULT 0,
    expected_count INT,
    variance INT,
    variance_value DECIMAL(12,2),
    notes TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    CONSTRAINT rfid_sessions_number_unique UNIQUE (session_number),
    CONSTRAINT rfid_sessions_type_check CHECK (session_type IN (
        'full_inventory', 'cycle_count', 'spot_check', 'find_item', 'receiving'
    )),
    CONSTRAINT rfid_sessions_status_check CHECK (status IN (
        'in_progress', 'completed', 'cancelled', 'uploaded'
    ))
);

CREATE INDEX idx_rfid_sessions_location ON rfid_scan_sessions(location_id, started_at DESC);
CREATE INDEX idx_rfid_sessions_status ON rfid_scan_sessions(status) WHERE status = 'in_progress';

COMMENT ON TABLE rfid_scan_sessions IS 'RFID inventory scan sessions with variance calculation';

rfid_scan_events

-- Individual tag reads during scan sessions
CREATE TABLE rfid_scan_events (
    id BIGSERIAL PRIMARY KEY,
    session_id UUID NOT NULL REFERENCES rfid_scan_sessions(id) ON DELETE CASCADE,
    epc VARCHAR(24) NOT NULL,
    rfid_tag_id UUID REFERENCES rfid_tags(id) ON DELETE SET NULL,
    rssi SMALLINT,
    read_count INT DEFAULT 1,
    antenna SMALLINT,
    first_seen_at TIMESTAMP NOT NULL,
    last_seen_at TIMESTAMP NOT NULL
);

-- Indexes (BRIN for high-volume time-series)
CREATE INDEX idx_rfid_events_session ON rfid_scan_events(session_id);
CREATE INDEX idx_rfid_events_epc ON rfid_scan_events(epc);
CREATE INDEX idx_rfid_events_tag ON rfid_scan_events(rfid_tag_id) WHERE rfid_tag_id IS NOT NULL;
CREATE INDEX idx_rfid_events_unknown ON rfid_scan_events(session_id) WHERE rfid_tag_id IS NULL;

COMMENT ON TABLE rfid_scan_events IS 'Individual RFID tag reads during scan sessions';
COMMENT ON COLUMN rfid_scan_events.rssi IS 'Signal strength (-127 to 0 dBm)';

13.2 Table Count Summary

Next Chapter: Chapter 14: Indexes & Performance - Index strategy and query optimization.

Chapter 13 | Entity Specifications | POS Platform Blueprint v1.0.0