Chapter 07: Schema Design

69 Tables Across 16 Domains

7.1 Overview

The POS Platform database consists of 69 tables organized into 16 functional domains. This chapter provides the complete schema design using a single-database, single-schema architecture with Row-Level Security (RLS) for tenant isolation.

All tenant-scoped tables include a tenant_id UUID NOT NULL column. PostgreSQL RLS policies automatically filter rows per tenant, ensuring data isolation at the database level.

Domain Summary

7.2 Schema Architecture

Visual Diagram

┌─────────────────────────────────────────────────────────────────────────────┐
│                              pos_platform                                    │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                      shared SCHEMA (6 tables)                        │   │
│  │                  Platform-wide, NO tenant_id, NO RLS                 │   │
│  │                                                                      │   │
│  │  ┌─────────────┐ ┌───────────────────┐ ┌─────────────────────────┐  │   │
│  │  │   tenants   │ │tenant_subscriptions│ │    tenant_modules      │  │   │
│  │  │  (registry) │ │   (billing)        │ │ (feature add-ons)      │  │   │
│  │  └─────────────┘ └───────────────────┘ └─────────────────────────┘  │   │
│  │  ┌─────────────────┐ ┌─────────────────────┐ ┌───────────────────┐  │   │
│  │  │     users       │ │    user_sessions    │ │  password_resets  │  │   │
│  │  │ (platform auth) │ │ (session tracking)  │ │   (recovery)      │  │   │
│  │  └─────────────────┘ └─────────────────────┘ └───────────────────┘  │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                                     │                                       │
│                    All tenant-scoped tables reference                       │
│                    shared.tenants(id) via tenant_id FK                      │
│                                     │                                       │
│                                     ▼                                       │
│  ┌───────────────────────────────────────────────────────────────────────┐ │
│  │             public SCHEMA (63 tables, all with tenant_id + RLS)       │ │
│  │                                                                        │ │
│  │  Domain 1-2: Catalog (13)   Domain 3: Inventory (7)                   │ │
│  │  ┌────────────────────┐    ┌─────────────────────────┐                │ │
│  │  │ products      [T]  │    │ locations          [T]  │                │ │
│  │  │ variants      [T]  │    │ inventory_levels   [T]  │                │ │
│  │  │ brands        [T]  │    │ inventory_trans    [T]  │                │ │
│  │  │ categories    [T]  │    │ purchase_orders    [T]  │                │ │
│  │  │ collections   [T]  │    │ purchase_order_    [T]  │                │ │
│  │  │ tags          [T]  │    │   items                 │                │ │
│  │  │ product_coll  [T]  │    │ transfer_orders    [T]  │                │ │
│  │  │ product_tag   [T]  │    │ transfer_order_    [T]  │                │ │
│  │  │ product_groups[T]  │    │   items                 │                │ │
│  │  │ genders       [T]  │    └─────────────────────────┘                │ │
│  │  │ origins       [T]  │                                               │ │
│  │  │ fabrics       [T]  │    Domain 4: Sales (3)                        │ │
│  │  │ pricing_rules [T]  │    ┌─────────────────────────┐                │ │
│  │  └────────────────────┘    │ customers          [T]  │                │ │
│  │                            │ orders             [T]  │                │ │
│  │  Domain 10: Auth (4)       │ order_items        [T]  │                │ │
│  │  ┌────────────────────┐    └─────────────────────────┘                │ │
│  │  │ roles         [T]  │                                               │ │
│  │  │ role_perms    [T]  │    Domain 5: Loyalty (5)                      │ │
│  │  │ tenant_users  [T]  │    ┌─────────────────────────┐                │ │
│  │  │ tenant_settings[T] │    │ loyalty_accounts   [T]  │                │ │
│  │  └────────────────────┘    │ loyalty_trans      [T]  │                │ │
│  │                            │ gift_cards         [T]  │                │ │
│  │  Domain 8: Prefs (1)       │ gift_card_trans    [T]  │                │ │
│  │  ┌────────────────────┐    │ store_credits      [T]  │                │ │
│  │  │ item_view_    [T]  │    └─────────────────────────┘                │ │
│  │  │   settings         │                                               │ │
│  │  └────────────────────┘    Domain 6-7: Returns (3)                    │ │
│  │                            ┌─────────────────────────┐                │ │
│  │  Domain 12: Events (2)     │ returns            [T]  │                │ │
│  │  ┌────────────────────┐    │ return_items       [T]  │                │ │
│  │  │ event_outbox  [T]  │    │ reports            [T]  │                │ │
│  │  │ state_trans   [T]  │    └─────────────────────────┘                │ │
│  │  └────────────────────┘                                               │ │
│  │                            Domain 11: Sync (3)                        │ │
│  │  Domain 13: Cash (6)       ┌─────────────────────────┐                │ │
│  │  ┌────────────────────┐    │ devices            [T]  │                │ │
│  │  │ shifts        [T]  │    │ sync_queue         [T]  │                │ │
│  │  │ cash_drawers  [T]  │    │ sync_checkpoints   [T]  │                │ │
│  │  │ cash_counts   [T]  │    └─────────────────────────┘                │ │
│  │  │ cash_movements[T]  │                                               │ │
│  │  │ cash_drops    [T]  │    Domain 14: Payment (4)                     │ │
│  │  │ cash_pickups  [T]  │    ┌─────────────────────────┐                │ │
│  │  └────────────────────┘    │ payment_terminals  [T]  │                │ │
│  │                            │ payment_attempts   [T]  │                │ │
│  │  Domain 16: RFID (9)       │ payment_batches    [T]  │                │ │
│  │  ┌────────────────────┐    │ payment_recon      [T]  │                │ │
│  │  │ rfid_config   [T]  │    └─────────────────────────┘                │ │
│  │  │ rfid_printers [T]  │                                               │ │
│  │  │ rfid_templates[T]  │    Domain 15: Tax (3)                         │ │
│  │  │ rfid_print_   [T]  │    ┌─────────────────────────┐                │ │
│  │  │   jobs             │    │ tax_jurisdictions  [T]  │                │ │
│  │  │ rfid_tags     [T]  │    │ tax_rates          [T]  │                │ │
│  │  │ rfid_sessions [T]  │    │ location_tax_juris [T]  │                │ │
│  │  │ rfid_events   [T]  │    └─────────────────────────┘                │ │
│  │  │ rfid_mappings [T]  │                                               │ │
│  │  │ session_ops   [T]  │    [T] = has tenant_id column + RLS policy   │ │
│  │  └────────────────────┘                                               │ │
│  └───────────────────────────────────────────────────────────────────────┘ │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

7.3 Complete CREATE TABLE Statements

All tables reside in a single database. The shared schema holds platform-wide tables (no tenant_id). The public schema holds all tenant-scoped tables with tenant_id UUID NOT NULL and RLS policies.

Enable Required Extensions

-- Run once on database creation
\c pos_platform

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Create shared schema
CREATE SCHEMA IF NOT EXISTS shared;

Shared Schema Tables (6 tables — no tenant_id)

Table: tenants

-- Tenant registry for multi-tenant SaaS architecture
CREATE TABLE shared.tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    tier VARCHAR(20) NOT NULL DEFAULT 'standard',
    contact_email VARCHAR(255) NOT NULL,
    contact_phone VARCHAR(20),
    billing_email VARCHAR(255),
    timezone VARCHAR(50) NOT NULL DEFAULT 'UTC',
    currency_code CHAR(3) NOT NULL DEFAULT 'USD',
    locale VARCHAR(10) NOT NULL DEFAULT 'en-US',
    trial_ends_at TIMESTAMPTZ,
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    -- Constraints
    CONSTRAINT tenants_slug_unique UNIQUE (slug),
    CONSTRAINT tenants_status_check CHECK (status IN ('provisioning', 'active', 'suspended', 'cancelled', 'trial')),
    CONSTRAINT tenants_tier_check CHECK (tier IN ('free', 'starter', 'standard', 'enterprise'))
);

-- Indexes
CREATE INDEX idx_tenants_status ON shared.tenants(status);
CREATE INDEX idx_tenants_tier ON shared.tenants(tier);
CREATE INDEX idx_tenants_trial ON shared.tenants(trial_ends_at) WHERE trial_ends_at IS NOT NULL;

COMMENT ON TABLE shared.tenants IS 'Tenant/organization registry for multi-tenant SaaS architecture';
COMMENT ON COLUMN shared.tenants.slug IS 'URL-safe identifier used for subdomain routing';
COMMENT ON COLUMN shared.tenants.tier IS 'Subscription tier determining feature access';

Table: tenant_subscriptions

-- Billing and subscription plan tracking
CREATE TABLE shared.tenant_subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id) ON DELETE CASCADE,
    plan_id VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    billing_cycle VARCHAR(20) NOT NULL,
    price_cents INT NOT NULL,
    currency_code CHAR(3) NOT NULL DEFAULT 'USD',
    location_limit INT NOT NULL DEFAULT 5,
    user_limit INT NOT NULL DEFAULT 10,
    device_limit INT NOT NULL DEFAULT 20,
    external_subscription_id VARCHAR(255),
    current_period_start TIMESTAMPTZ NOT NULL,
    current_period_end TIMESTAMPTZ NOT NULL,
    cancelled_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    -- Constraints
    CONSTRAINT subscriptions_status_check CHECK (status IN ('active', 'past_due', 'cancelled', 'paused')),
    CONSTRAINT subscriptions_cycle_check CHECK (billing_cycle IN ('monthly', 'annual'))
);

-- Indexes
CREATE INDEX idx_tenant_subscriptions_tenant ON shared.tenant_subscriptions(tenant_id);
CREATE INDEX idx_tenant_subscriptions_status ON shared.tenant_subscriptions(status);
CREATE INDEX idx_tenant_subscriptions_period ON shared.tenant_subscriptions(current_period_end);
CREATE INDEX idx_tenant_subscriptions_external ON shared.tenant_subscriptions(external_subscription_id)
    WHERE external_subscription_id IS NOT NULL;

COMMENT ON TABLE shared.tenant_subscriptions IS 'Billing and subscription plan tracking for each tenant';
COMMENT ON COLUMN shared.tenant_subscriptions.external_subscription_id IS 'Stripe/PayPal subscription ID for payment integration';

Table: tenant_modules

-- Optional module subscriptions (RFID, promotions, gift cards, etc.)
CREATE TABLE shared.tenant_modules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id) ON DELETE CASCADE,
    module_code VARCHAR(50) NOT NULL,
    is_enabled BOOLEAN DEFAULT TRUE,
    activated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ,
    monthly_fee_cents INT,
    trial_days_remaining INT,
    configuration JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    -- Constraints
    CONSTRAINT tenant_modules_unique UNIQUE (tenant_id, module_code),
    CONSTRAINT tenant_modules_code_check CHECK (module_code IN (
        'rfid', 'promotions', 'gift_cards', 'scheduling',
        'loyalty_advanced', 'analytics', 'ecommerce', 'b2b'
    ))
);

-- Indexes
CREATE INDEX idx_tenant_modules_code ON shared.tenant_modules(module_code) WHERE is_enabled = TRUE;
CREATE INDEX idx_tenant_modules_expiring ON shared.tenant_modules(expires_at)
    WHERE expires_at IS NOT NULL;

COMMENT ON TABLE shared.tenant_modules IS 'Optional add-on modules subscribed by each tenant';
COMMENT ON COLUMN shared.tenant_modules.module_code IS 'Identifier for the module (rfid, promotions, etc.)';
COMMENT ON COLUMN shared.tenant_modules.configuration IS 'Module-specific settings in JSON format';

Table: users

-- Platform-wide user accounts (can belong to multiple tenants)
CREATE TABLE shared.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    avatar_url VARCHAR(500),
    is_platform_admin BOOLEAN DEFAULT FALSE,
    email_verified BOOLEAN DEFAULT FALSE,
    email_verified_at TIMESTAMPTZ,
    last_login_at TIMESTAMPTZ,
    failed_login_count INT DEFAULT 0,
    locked_until TIMESTAMPTZ,
    mfa_enabled BOOLEAN DEFAULT FALSE,
    mfa_secret VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    -- Constraints
    CONSTRAINT users_email_unique UNIQUE (email),
    CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

-- Indexes
CREATE INDEX idx_users_name ON shared.users(last_name, first_name);
CREATE INDEX idx_users_locked ON shared.users(locked_until) WHERE locked_until IS NOT NULL;
CREATE INDEX idx_users_login ON shared.users(last_login_at);

COMMENT ON TABLE shared.users IS 'Platform-wide user accounts supporting multi-tenant membership';
COMMENT ON COLUMN shared.users.password_hash IS 'Argon2id password hash (memory-hard algorithm)';
COMMENT ON COLUMN shared.users.mfa_secret IS 'Encrypted TOTP secret for 2FA';

Table: user_sessions

-- Active user sessions across all tenants
CREATE TABLE shared.user_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES shared.users(id) ON DELETE CASCADE,
    tenant_id UUID REFERENCES shared.tenants(id) ON DELETE CASCADE,
    session_token VARCHAR(255) NOT NULL,
    refresh_token VARCHAR(255),
    device_id UUID,
    ip_address INET NOT NULL,
    user_agent VARCHAR(500),
    device_type VARCHAR(20) NOT NULL DEFAULT 'web',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL,
    last_activity_at TIMESTAMPTZ DEFAULT NOW(),

    -- Constraints
    CONSTRAINT sessions_token_unique UNIQUE (session_token),
    CONSTRAINT sessions_refresh_unique UNIQUE (refresh_token),
    CONSTRAINT sessions_device_type_check CHECK (device_type IN ('web', 'mobile', 'pos_terminal', 'api'))
);

-- Indexes
CREATE INDEX idx_sessions_user ON shared.user_sessions(user_id);
CREATE INDEX idx_sessions_tenant ON shared.user_sessions(tenant_id) WHERE tenant_id IS NOT NULL;
CREATE INDEX idx_sessions_expiry ON shared.user_sessions(expires_at) WHERE is_active = TRUE;
CREATE INDEX idx_sessions_device ON shared.user_sessions(device_id) WHERE device_id IS NOT NULL;
CREATE INDEX idx_sessions_activity ON shared.user_sessions(last_activity_at);

COMMENT ON TABLE shared.user_sessions IS 'Active session tracking with multi-tenant context';
COMMENT ON COLUMN shared.user_sessions.tenant_id IS 'Current tenant context (NULL for platform-level sessions)';

Table: password_resets

-- Password reset token management
CREATE TABLE shared.password_resets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES shared.users(id) ON DELETE CASCADE,
    token_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL,
    used_at TIMESTAMPTZ,
    ip_address INET NOT NULL,

    -- Constraints
    CONSTRAINT password_resets_token_unique UNIQUE (token_hash)
);

-- Indexes
CREATE INDEX idx_password_resets_user ON shared.password_resets(user_id);
CREATE INDEX idx_password_resets_expiry ON shared.password_resets(expires_at) WHERE used_at IS NULL;

COMMENT ON TABLE shared.password_resets IS 'Password reset token management with expiration';
COMMENT ON COLUMN shared.password_resets.token_hash IS 'SHA-256 hash of reset token (token sent to user)';

Public Schema Tables (58 tables — all with tenant_id + RLS)

The public schema contains all tenant-scoped tables. Every table includes:

• tenant_id UUID NOT NULL — references shared.tenants(id)
• id UUID PRIMARY KEY DEFAULT gen_random_uuid() — UUID primary keys
• A composite index with tenant_id as the leading column
• RLS policies applied (see Section 7.4)

Below are representative examples. For complete CREATE TABLE statements for all 64 tables, see Chapter 08 (Entity Specifications).

Example: products

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    brand_id UUID REFERENCES brands(id) ON DELETE SET NULL,
    product_group_id UUID REFERENCES product_groups(id) ON DELETE SET NULL,
    gender_id UUID REFERENCES genders(id) ON DELETE SET NULL,
    origin_id UUID REFERENCES origins(id) ON DELETE SET NULL,
    fabric_id UUID REFERENCES fabrics(id) ON DELETE SET NULL,
    base_price DECIMAL(19,4) NOT NULL,
    cost_price DECIMAL(19,4) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    has_variants BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES shared.users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

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

-- Tenant isolation index (CRITICAL for RLS performance)
CREATE INDEX idx_products_tenant ON products(tenant_id);
CREATE UNIQUE INDEX idx_products_tenant_sku ON products(tenant_id, sku) WHERE deleted_at IS NULL;
CREATE INDEX idx_products_tenant_brand ON products(tenant_id, brand_id);
CREATE INDEX idx_products_tenant_active ON products(tenant_id, is_active)
    WHERE is_active = TRUE AND deleted_at IS NULL;

COMMENT ON TABLE products IS 'Product catalog — tenant-scoped with RLS';

Example: variants

CREATE TABLE variants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    sku VARCHAR(50) NOT NULL,
    size VARCHAR(20),
    color VARCHAR(50),
    price_adjustment DECIMAL(19,4) DEFAULT 0.00,
    weight DECIMAL(10,3),
    barcode VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID REFERENCES shared.users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tenant isolation indexes
CREATE INDEX idx_variants_tenant ON variants(tenant_id);
CREATE UNIQUE INDEX idx_variants_tenant_sku ON variants(tenant_id, sku) WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX idx_variants_tenant_barcode ON variants(tenant_id, barcode)
    WHERE barcode IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_variants_tenant_product ON variants(tenant_id, product_id);

Example: orders

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
    order_number VARCHAR(50) NOT NULL,
    customer_id UUID REFERENCES customers(id),
    location_id UUID NOT NULL REFERENCES locations(id),
    employee_id UUID NOT NULL REFERENCES shared.users(id),
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    subtotal DECIMAL(19,4) NOT NULL DEFAULT 0,
    tax_total DECIMAL(19,4) NOT NULL DEFAULT 0,
    discount_total DECIMAL(19,4) NOT NULL DEFAULT 0,
    grand_total DECIMAL(19,4) NOT NULL DEFAULT 0,
    payment_status VARCHAR(20) NOT NULL DEFAULT 'unpaid',
    notes TEXT,
    voided_at TIMESTAMPTZ,
    voided_by UUID REFERENCES shared.users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT orders_status_check CHECK (status IN ('pending', 'completed', 'refunded', 'voided', 'on_hold')),
    CONSTRAINT orders_payment_status_check CHECK (payment_status IN ('unpaid', 'partial', 'paid', 'refunded')),
    CONSTRAINT orders_amounts_positive CHECK (subtotal >= 0 AND tax_total >= 0 AND discount_total >= 0 AND grand_total >= 0)
);

-- Tenant isolation indexes
CREATE INDEX idx_orders_tenant ON orders(tenant_id);
CREATE UNIQUE INDEX idx_orders_tenant_number ON orders(tenant_id, order_number);
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at DESC);
CREATE INDEX idx_orders_tenant_customer ON orders(tenant_id, customer_id);
CREATE INDEX idx_orders_tenant_location ON orders(tenant_id, location_id);
CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status);

Example: customers

CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(20),
    loyalty_points INT DEFAULT 0,
    total_spent DECIMAL(19,4) DEFAULT 0,
    visit_count INT DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

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

-- Tenant isolation indexes
CREATE INDEX idx_customers_tenant ON customers(tenant_id);
CREATE UNIQUE INDEX idx_customers_tenant_email ON customers(tenant_id, email)
    WHERE email IS NOT NULL;
CREATE INDEX idx_customers_tenant_name ON customers(tenant_id, last_name, first_name);
CREATE INDEX idx_customers_tenant_phone ON customers(tenant_id, phone)
    WHERE phone IS NOT NULL;

Example: locations

CREATE TABLE locations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) NOT NULL,
    type VARCHAR(20) NOT NULL,
    address VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    country CHAR(2) DEFAULT 'US',
    phone VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    timezone VARCHAR(50) DEFAULT 'UTC',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

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

-- Tenant isolation indexes
CREATE INDEX idx_locations_tenant ON locations(tenant_id);
CREATE UNIQUE INDEX idx_locations_tenant_code ON locations(tenant_id, code);

Example: inventory_levels

CREATE TABLE inventory_levels (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
    variant_id UUID NOT NULL REFERENCES variants(id),
    location_id UUID NOT NULL REFERENCES locations(id),
    quantity_on_hand INT NOT NULL DEFAULT 0,
    quantity_committed INT NOT NULL DEFAULT 0,
    quantity_reserved INT NOT NULL DEFAULT 0,
    -- quantity_available is NEVER stored — computed at query time:
    -- available = on_hand - committed - reserved
    reorder_point INT DEFAULT 0,
    reorder_quantity INT DEFAULT 0,
    average_cost DECIMAL(19,4) DEFAULT 0.00,
    last_counted_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT inventory_unique UNIQUE (tenant_id, variant_id, location_id),
    CONSTRAINT inventory_levels_reserved_check CHECK (quantity_reserved >= 0),
    CONSTRAINT inventory_levels_committed_check CHECK (quantity_committed >= 0)
);

-- Tenant isolation indexes
CREATE INDEX idx_inventory_tenant ON inventory_levels(tenant_id);
CREATE INDEX idx_inventory_tenant_location ON inventory_levels(tenant_id, location_id);
CREATE INDEX idx_inventory_tenant_variant ON inventory_levels(tenant_id, variant_id);

7.4 RLS Policy Definitions

Every tenant-scoped table in the public schema must have RLS enabled with isolation policies. The application sets app.current_tenant via middleware before any query executes.

Master RLS Setup Script

-- ============================================================
-- RLS POLICY SETUP
-- Apply to all tenant-scoped tables in public schema
-- ============================================================

-- Helper function to apply RLS to a table
CREATE OR REPLACE FUNCTION apply_rls_policy(p_table_name TEXT)
RETURNS VOID AS $$
BEGIN
    -- Enable RLS
    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', p_table_name);

    -- Force RLS even for table owners (defense-in-depth)
    EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', p_table_name);

    -- SELECT, UPDATE, DELETE policy
    EXECUTE format(
        'CREATE POLICY tenant_isolation ON %I
            USING (tenant_id = current_setting(''app.current_tenant'')::uuid)',
        p_table_name
    );

    -- INSERT policy (prevent inserting rows for wrong tenant)
    EXECUTE format(
        'CREATE POLICY tenant_insert ON %I
            FOR INSERT
            WITH CHECK (tenant_id = current_setting(''app.current_tenant'')::uuid)',
        p_table_name
    );

    RAISE NOTICE 'RLS policies applied to: %', p_table_name;
END;
$$ LANGUAGE plpgsql;

-- Apply RLS to all 63 tenant-scoped tables in public schema
-- Domain 1-2: Catalog (13 tables)
SELECT apply_rls_policy('products');
SELECT apply_rls_policy('variants');
SELECT apply_rls_policy('brands');
SELECT apply_rls_policy('product_groups');
SELECT apply_rls_policy('genders');
SELECT apply_rls_policy('origins');
SELECT apply_rls_policy('fabrics');
SELECT apply_rls_policy('categories');
SELECT apply_rls_policy('collections');
SELECT apply_rls_policy('tags');
SELECT apply_rls_policy('product_collection');
SELECT apply_rls_policy('product_tag');
SELECT apply_rls_policy('pricing_rules');

-- Domain 3: Inventory & Locations (7 tables)
SELECT apply_rls_policy('locations');
SELECT apply_rls_policy('inventory_levels');
SELECT apply_rls_policy('inventory_transactions');
SELECT apply_rls_policy('purchase_orders');
SELECT apply_rls_policy('purchase_order_items');
SELECT apply_rls_policy('transfer_orders');
SELECT apply_rls_policy('transfer_order_items');

-- Domain 4: Sales (3 tables)
SELECT apply_rls_policy('customers');
SELECT apply_rls_policy('orders');
SELECT apply_rls_policy('order_items');

-- Domain 5: Customer Loyalty & Gift Cards (5 tables)
SELECT apply_rls_policy('loyalty_accounts');
SELECT apply_rls_policy('loyalty_transactions');
SELECT apply_rls_policy('gift_cards');
SELECT apply_rls_policy('gift_card_transactions');
SELECT apply_rls_policy('store_credits');

-- Domain 6-7: Returns & Reporting (3 tables)
SELECT apply_rls_policy('returns');
SELECT apply_rls_policy('return_items');
SELECT apply_rls_policy('reports');

-- Domain 8: User Preferences (1 table)
SELECT apply_rls_policy('item_view_settings');

-- Domain 10: Auth (4 tenant-specific tables)
SELECT apply_rls_policy('roles');
SELECT apply_rls_policy('role_permissions');
SELECT apply_rls_policy('tenant_users');
SELECT apply_rls_policy('tenant_settings');

-- Domain 11: Offline Sync (3 tables)
SELECT apply_rls_policy('devices');
SELECT apply_rls_policy('sync_queue');
SELECT apply_rls_policy('sync_checkpoints');

-- Domain 12: Event Infrastructure (2 tables)
SELECT apply_rls_policy('event_outbox');
SELECT apply_rls_policy('state_transitions');

-- Domain 13: Cash Drawer Operations (6 tables)
SELECT apply_rls_policy('shifts');
SELECT apply_rls_policy('cash_drawers');
SELECT apply_rls_policy('cash_counts');
SELECT apply_rls_policy('cash_movements');
SELECT apply_rls_policy('cash_drops');
SELECT apply_rls_policy('cash_pickups');

-- Domain 14: Payment Processing (4 tables)
SELECT apply_rls_policy('payment_terminals');
SELECT apply_rls_policy('payment_attempts');
SELECT apply_rls_policy('payment_batches');
SELECT apply_rls_policy('payment_reconciliation');

-- Domain 15: Tax Configuration (3 tables)
SELECT apply_rls_policy('tax_jurisdictions');
SELECT apply_rls_policy('tax_rates');
SELECT apply_rls_policy('location_tax_jurisdictions');

-- Domain 16: RFID Module (9 tables)
SELECT apply_rls_policy('rfid_config');
SELECT apply_rls_policy('rfid_printers');
SELECT apply_rls_policy('rfid_tag_templates');
SELECT apply_rls_policy('rfid_print_jobs');
SELECT apply_rls_policy('rfid_tags');
SELECT apply_rls_policy('rfid_scan_sessions');
SELECT apply_rls_policy('rfid_scan_events');
SELECT apply_rls_policy('rfid_tag_mappings');
SELECT apply_rls_policy('session_operators');

Verification Query

-- Verify RLS is enabled on all tenant-scoped tables
SELECT
    schemaname,
    tablename,
    rowsecurity AS rls_enabled
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

-- Verify policies exist
SELECT
    schemaname,
    tablename,
    policyname,
    cmd AS applies_to,
    qual AS using_expression,
    with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

RLS Bypass for Admin Operations

-- The pos_admin role bypasses RLS for cross-tenant operations
-- ONLY use for: reporting, data migration, tenant cleanup

-- Example: Cross-tenant product count (admin only)
SET ROLE pos_admin;
SELECT tenant_id, COUNT(*) AS product_count
FROM products
GROUP BY tenant_id;

-- Reset to application role
RESET ROLE;

7.5 Seed Data

When a new tenant is provisioned, default data is inserted with the tenant’s tenant_id:

-- Seed default data for a new tenant
CREATE OR REPLACE FUNCTION seed_tenant_data(p_tenant_id UUID)
RETURNS VOID AS $$
DECLARE
    v_owner_role_id UUID;
    v_admin_role_id UUID;
    v_manager_role_id UUID;
    v_staff_role_id UUID;
    v_buyer_role_id UUID;
BEGIN
    -- Seed default roles
    INSERT INTO roles (tenant_id, name, display_name, description, is_system)
    VALUES
        (p_tenant_id, 'owner', 'Owner', 'Full access to all features and settings', TRUE),
        (p_tenant_id, 'admin', 'Administrator', 'Administrative access excluding billing', TRUE),
        (p_tenant_id, 'manager', 'Manager', 'Store management and reporting access', TRUE),
        (p_tenant_id, 'staff', 'Staff', 'Sales and basic customer operations', TRUE),
        (p_tenant_id, 'buyer', 'Buyer', 'Purchasing and vendor management access', TRUE);

    -- Get role IDs for permission assignment
    SELECT id INTO v_owner_role_id FROM roles WHERE tenant_id = p_tenant_id AND name = 'owner';
    SELECT id INTO v_admin_role_id FROM roles WHERE tenant_id = p_tenant_id AND name = 'admin';
    SELECT id INTO v_manager_role_id FROM roles WHERE tenant_id = p_tenant_id AND name = 'manager';
    SELECT id INTO v_staff_role_id FROM roles WHERE tenant_id = p_tenant_id AND name = 'staff';
    SELECT id INTO v_buyer_role_id FROM roles WHERE tenant_id = p_tenant_id AND name = 'buyer';

    -- Seed role permissions (Owner gets all)
    INSERT INTO role_permissions (tenant_id, role_id, permission, granted) VALUES
    -- Owner permissions (all)
    (p_tenant_id, v_owner_role_id, 'products.*', TRUE),
    (p_tenant_id, v_owner_role_id, 'inventory.*', TRUE),
    (p_tenant_id, v_owner_role_id, 'orders.*', TRUE),
    (p_tenant_id, v_owner_role_id, 'customers.*', TRUE),
    (p_tenant_id, v_owner_role_id, 'reports.*', TRUE),
    (p_tenant_id, v_owner_role_id, 'settings.*', TRUE),
    (p_tenant_id, v_owner_role_id, 'users.*', TRUE),
    (p_tenant_id, v_owner_role_id, 'billing.*', TRUE),
    -- Manager permissions
    (p_tenant_id, v_manager_role_id, 'products.view', TRUE),
    (p_tenant_id, v_manager_role_id, 'products.edit', TRUE),
    (p_tenant_id, v_manager_role_id, 'inventory.*', TRUE),
    (p_tenant_id, v_manager_role_id, 'orders.*', TRUE),
    (p_tenant_id, v_manager_role_id, 'customers.*', TRUE),
    (p_tenant_id, v_manager_role_id, 'reports.view', TRUE),
    (p_tenant_id, v_manager_role_id, 'shifts.*', TRUE),
    -- Staff permissions
    (p_tenant_id, v_staff_role_id, 'products.view', TRUE),
    (p_tenant_id, v_staff_role_id, 'orders.create', TRUE),
    (p_tenant_id, v_staff_role_id, 'orders.view', TRUE),
    (p_tenant_id, v_staff_role_id, 'customers.view', TRUE),
    (p_tenant_id, v_staff_role_id, 'customers.create', TRUE),
    (p_tenant_id, v_staff_role_id, 'shifts.open', TRUE),
    (p_tenant_id, v_staff_role_id, 'shifts.close', TRUE),
    -- Buyer permissions
    (p_tenant_id, v_buyer_role_id, 'products.view', TRUE),
    (p_tenant_id, v_buyer_role_id, 'products.edit', TRUE),
    (p_tenant_id, v_buyer_role_id, 'inventory.view', TRUE),
    (p_tenant_id, v_buyer_role_id, 'inventory.receive', TRUE),
    (p_tenant_id, v_buyer_role_id, 'inventory.transfer', TRUE),
    (p_tenant_id, v_buyer_role_id, 'reports.view', TRUE);

    -- Seed default genders
    INSERT INTO genders (tenant_id, name) VALUES
        (p_tenant_id, 'Men'), (p_tenant_id, 'Women'), (p_tenant_id, 'Unisex'),
        (p_tenant_id, 'Kids'), (p_tenant_id, 'Boys'), (p_tenant_id, 'Girls');

    -- Seed default tenant settings
    INSERT INTO tenant_settings (tenant_id, category, key, value, value_type, description) VALUES
        (p_tenant_id, 'general', 'business_name', '"New Business"', 'string', 'Business display name'),
        (p_tenant_id, 'general', 'timezone', '"UTC"', 'string', 'Default timezone'),
        (p_tenant_id, 'pos', 'require_customer', 'false', 'boolean', 'Require customer for sales'),
        (p_tenant_id, 'pos', 'allow_negative_inventory', 'false', 'boolean', 'Allow selling without stock'),
        (p_tenant_id, 'pos', 'receipt_footer', '"Thank you for your business!"', 'string', 'Receipt footer message'),
        (p_tenant_id, 'inventory', 'low_stock_threshold', '5', 'number', 'Low stock alert threshold'),
        (p_tenant_id, 'cash', 'require_drawer_count', 'true', 'boolean', 'Require cash count at shift open/close'),
        (p_tenant_id, 'loyalty', 'points_per_dollar', '1', 'number', 'Loyalty points earned per dollar spent');

    RAISE NOTICE 'Seed data inserted for tenant: %', p_tenant_id;
END;
$$ LANGUAGE plpgsql;

7.6 Tenant Provisioning

With RLS architecture, provisioning a new tenant is significantly simpler than schema-per-tenant. No schema creation is needed — just insert a tenant record and seed default data.

Provisioning Script

-- ============================================================
-- TENANT PROVISIONING SCRIPT (RLS)
-- Much simpler than schema-per-tenant: no CREATE SCHEMA needed
-- ============================================================

-- Variables (replace with actual values)
\set tenant_name 'Acme Retail'
\set tenant_slug 'acme-retail'
\set contact_email 'admin@acmeretail.com'

-- Begin transaction
BEGIN;

-- Step 1: Create tenant record in shared schema
INSERT INTO shared.tenants (
    name, slug, status, tier, contact_email
) VALUES (
    :'tenant_name',
    :'tenant_slug',
    'provisioning',
    'standard',
    :'contact_email'
) RETURNING id AS tenant_id \gset

-- Step 2: Create subscription record
INSERT INTO shared.tenant_subscriptions (
    tenant_id,
    plan_id,
    status,
    billing_cycle,
    price_cents,
    location_limit,
    user_limit,
    device_limit,
    current_period_start,
    current_period_end
) VALUES (
    :'tenant_id',
    'standard_monthly',
    'active',
    'monthly',
    9900,  -- $99.00
    5,
    10,
    20,
    CURRENT_DATE,
    CURRENT_DATE + INTERVAL '1 month'
);

-- Step 3: Seed default data (roles, permissions, settings)
-- The seed function uses RLS-compatible tenant_id on every row
SELECT seed_tenant_data(:'tenant_id'::uuid);

-- Step 4: Activate tenant
UPDATE shared.tenants
SET status = 'active'
WHERE id = :'tenant_id'::uuid;

-- Step 5: Verify creation
SELECT
    t.name,
    t.slug,
    t.status,
    (SELECT COUNT(*) FROM roles WHERE tenant_id = t.id) AS roles_created,
    (SELECT COUNT(*) FROM tenant_settings WHERE tenant_id = t.id) AS settings_created
FROM shared.tenants t
WHERE t.id = :'tenant_id'::uuid;

COMMIT;

-- Success message
\echo 'Tenant provisioned successfully!'
\echo 'Tenant ID: ' :'tenant_id'

TypeScript Provisioning Service

// services/tenantProvisioning.ts
import { PrismaClient } from '@prisma/client';
import { randomUUID } from 'crypto';

interface ProvisionTenantInput {
  name: string;
  subdomain: string;
  plan: 'starter' | 'professional' | 'enterprise';
  ownerEmail: string;
  ownerName: string;
}

export class TenantProvisioningService {
  constructor(private readonly prisma: PrismaClient) {}

  async provisionTenant(input: ProvisionTenantInput): Promise<string> {
    const tenantId = randomUUID();

    // Use transaction for atomic provisioning
    await this.prisma.$transaction(async (tx) => {
      // 1. Create tenant record
      await tx.tenant.create({
        data: {
          id: tenantId,
          name: input.name,
          subdomain: input.subdomain,
          plan: input.plan,
          isActive: true,
        }
      });

      // 2. Set RLS context for seeding tenant-specific data
      await tx.$executeRawUnsafe(
        `SET app.current_tenant = '${tenantId}'`
      );

      // 3. Create owner user
      const { hash } = await import('argon2');
      const tempPassword = generateTempPassword();
      await tx.user.create({
        data: {
          id: randomUUID(),
          tenantId,
          email: input.ownerEmail,
          displayName: input.ownerName,
          passwordHash: await hash(tempPassword),
          role: 'OWNER',
          isActive: true,
        }
      });

      // 4. Seed default data (roles, permissions, tax jurisdictions)
      await this.seedDefaults(tx, tenantId);

      // 5. Create RFID EPC sequence for tenant
      await tx.$executeRawUnsafe(
        `CREATE SEQUENCE IF NOT EXISTS epc_serial_${tenantId.replace(/-/g, '_')} START 1`
      );
    });

    return tenantId;
  }

  private async seedDefaults(
    tx: PrismaClient,
    tenantId: string
  ): Promise<void> {
    // Seed default roles, permissions, settings
    // See Chapter 08 for entity specifications
  }
}

RLS vs Schema-Per-Tenant Provisioning Comparison

7.7 Table Count by Domain

7.8 Quick Reference: Table List

Shared Schema Tables (6)

shared.tenants
shared.tenant_subscriptions
shared.tenant_modules
shared.users
shared.user_sessions
shared.password_resets

Public Schema Tables (63, all with tenant_id + RLS)

-- Domain 1-2: Catalog (13)
products, variants, brands, product_groups, genders, origins, fabrics,
categories, collections, tags, product_collection, product_tag, pricing_rules

-- Domain 3: Inventory (7)
locations, inventory_levels, inventory_transactions,
purchase_orders, purchase_order_items, transfer_orders, transfer_order_items

-- Domain 4: Sales (3)
customers, orders, order_items

-- Domain 5: Customer Loyalty & Gift Cards (5)
loyalty_accounts, loyalty_transactions, gift_cards, gift_card_transactions,
store_credits

-- Domain 6-7: Returns & Reporting (3)
returns, return_items, reports

-- Domain 8: Preferences (1)
item_view_settings

-- Domain 10: Auth (4 tenant-specific)
roles, role_permissions, tenant_users, tenant_settings

-- Domain 11: Sync (3)
devices, sync_queue, sync_checkpoints

-- Domain 12: Event Infrastructure (2)
event_outbox, state_transitions

-- Domain 13: Cash (6)
shifts, cash_drawers, cash_counts, cash_movements, cash_drops, cash_pickups

-- Domain 14: Payment (4)
payment_terminals, payment_attempts, payment_batches, payment_reconciliation

-- Domain 15: Tax (3)
tax_jurisdictions, tax_rates, location_tax_jurisdictions

-- Domain 16: RFID (9)
rfid_config, rfid_printers, rfid_tag_templates, rfid_print_jobs,
rfid_tags, rfid_scan_sessions, rfid_scan_events, rfid_tag_mappings,
session_operators

Index Naming Convention

All tenant-scoped tables follow this composite index pattern:

-- Primary tenant isolation index
CREATE INDEX idx_<table>_tenant ON <table>(tenant_id);

-- Composite indexes for common queries (tenant_id always first)
CREATE INDEX idx_<table>_tenant_<column> ON <table>(tenant_id, <column>);

-- Unique constraints include tenant_id for proper scoping
CREATE UNIQUE INDEX idx_<table>_tenant_<column> ON <table>(tenant_id, <column>);

Next Chapter: Chapter 08: Entity Specifications - Complete CREATE TABLE statements for all 69 tables organized by domain.

Document Information

This chapter is part of the POS Blueprint Book. All content is self-contained.