Chapter 07: Schema Design

51 Tables Across 13 Domains

7.1 Overview

The POS Platform database consists of 51 tables organized into 13 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 (45 tables, all with tenant_id + RLS)       │ │
│  │                                                                        │ │
│  │  Domain 1-3: Catalog        Domain 4-5: Inventory    Domain 6: Sales   │ │
│  │  ┌────────────────────┐    ┌───────────────────┐   ┌────────────────┐ │ │
│  │  │ products      [T]  │    │ locations    [T]  │   │ customers [T]  │ │ │
│  │  │ variants      [T]  │    │ inventory_   [T]  │   │ orders    [T]  │ │ │
│  │  │ brands        [T]  │    │   levels          │   │ order_    [T]  │ │ │
│  │  │ categories    [T]  │    │ inventory_   [T]  │   │   items        │ │ │
│  │  │ collections   [T]  │    │   transactions    │   └────────────────┘ │ │
│  │  │ tags          [T]  │    │ taxes        [T]  │                      │ │
│  │  │ product_      [T]  │    │ location_tax [T]  │   Domain 9: Auth     │ │
│  │  │   collection       │    └───────────────────┘   ┌────────────────┐ │ │
│  │  │ product_tag   [T]  │                            │ roles     [T]  │ │ │
│  │  │ product_      [T]  │    Domain 10: Sync         │ role_     [T]  │ │ │
│  │  │   groups           │    ┌───────────────────┐   │   perms        │ │ │
│  │  │ genders       [T]  │    │ devices      [T]  │   │ tenant_   [T]  │ │ │
│  │  │ origins       [T]  │    │ sync_queue   [T]  │   │   users        │ │ │
│  │  │ fabrics       [T]  │    │ sync_        [T]  │   │ tenant_   [T]  │ │ │
│  │  └────────────────────┘    │   conflicts       │   │   settings     │ │ │
│  │                            │ sync_        [T]  │   └────────────────┘ │ │
│  │  Domain 7: Prefs           │   checkpoints     │                      │ │
│  │  ┌────────────────────┐    └───────────────────┘   Domain 11-12: Ops  │ │
│  │  │ item_view_    [T]  │                            ┌────────────────┐ │ │
│  │  │   settings         │    Domain 13: RFID         │ shifts    [T]  │ │ │
│  │  └────────────────────┘    ┌───────────────────┐   │ cash_     [T]  │ │ │
│  │                            │ rfid_config  [T]  │   │   drawers      │ │ │
│  │   [T] = has tenant_id     │ rfid_printers[T]  │   │ cash_     [T]  │ │ │
│  │         column + RLS       │ rfid_        [T]  │   │   counts       │ │ │
│  │         policy             │   templates       │   │ cash_     [T]  │ │ │
│  │                            │ rfid_print_  [T]  │   │   movements    │ │ │
│  │                            │   jobs            │   │ cash_drops[T]  │ │ │
│  │                            │ rfid_tags    [T]  │   │ cash_     [T]  │ │ │
│  │                            │ rfid_scan_   [T]  │   │   pickups      │ │ │
│  │                            │   sessions        │   │ payment_  [T]  │ │ │
│  │                            │ rfid_scan_   [T]  │   │   terminals    │ │ │
│  │                            │   events          │   │ payment_  [T]  │ │ │
│  │                            └───────────────────┘   │   attempts     │ │ │
│  │                                                    │ payment_  [T]  │ │ │
│  │                                                    │   batches      │ │ │
│  │                                                    │ payment_  [T]  │ │ │
│  │                                                    │   recon        │ │ │
│  │                                                    └────────────────┘ │ │
│  └───────────────────────────────────────────────────────────────────────┘ │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

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 SERIAL PRIMARY KEY,
    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 SERIAL PRIMARY KEY,
    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 (45 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)
• 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 51 tables, see Chapter 08 (Entity Specifications).

Example: products

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    brand_id INT,
    product_group_id INT,
    gender_id INT,
    origin_id INT,
    fabric_id INT,
    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 TIMESTAMPTZ,
    deleted_by UUID REFERENCES shared.users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 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 SERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
    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 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 SERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
    order_number VARCHAR(50) NOT NULL,
    customer_id INT REFERENCES customers(id),
    location_id INT NOT NULL,
    employee_id UUID NOT NULL REFERENCES shared.users(id),
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    tax_total DECIMAL(12,2) NOT NULL DEFAULT 0,
    discount_total DECIMAL(12,2) NOT NULL DEFAULT 0,
    grand_total DECIMAL(12,2) 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()
);

-- 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);
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 SERIAL PRIMARY KEY,
    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(12,2) DEFAULT 0,
    visit_count INT DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 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 SERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) NOT NULL,
    address_line1 VARCHAR(255),
    address_line2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    zip_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()
);

-- 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 SERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
    variant_id INT NOT NULL REFERENCES variants(id),
    location_id INT NOT NULL REFERENCES locations(id),
    quantity_on_hand INT NOT NULL DEFAULT 0,
    quantity_committed INT NOT NULL DEFAULT 0,
    -- quantity_available is NEVER stored — computed at query time:
    -- available = on_hand - committed - reserved
    reorder_point INT,
    reorder_quantity INT,
    last_counted_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT inventory_unique UNIQUE (tenant_id, variant_id, location_id)
);

-- 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 tenant-scoped tables
-- Domain 1: Products & Variants
SELECT apply_rls_policy('products');
SELECT apply_rls_policy('variants');
SELECT apply_rls_policy('product_collection');
SELECT apply_rls_policy('product_tag');
SELECT apply_rls_policy('brands');

-- Domain 2: Categories & Tags
SELECT apply_rls_policy('categories');
SELECT apply_rls_policy('collections');
SELECT apply_rls_policy('tags');

-- Domain 3: Product Attributes
SELECT apply_rls_policy('product_groups');
SELECT apply_rls_policy('genders');
SELECT apply_rls_policy('origins');
SELECT apply_rls_policy('fabrics');

-- Domain 4: Inventory & Locations
SELECT apply_rls_policy('locations');
SELECT apply_rls_policy('inventory_levels');
SELECT apply_rls_policy('inventory_transactions');

-- Domain 5: Tax Configuration
SELECT apply_rls_policy('taxes');
SELECT apply_rls_policy('location_tax');

-- Domain 6: Orders & Customers
SELECT apply_rls_policy('customers');
SELECT apply_rls_policy('orders');
SELECT apply_rls_policy('order_items');

-- Domain 7: User Preferences
SELECT apply_rls_policy('item_view_settings');

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

-- Domain 10: Offline Sync
SELECT apply_rls_policy('devices');
SELECT apply_rls_policy('sync_queue');
SELECT apply_rls_policy('sync_conflicts');
SELECT apply_rls_policy('sync_checkpoints');

-- Domain 11: Cash Drawer Operations
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 12: Payment Processing
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 13: RFID Module
SELECT apply_rls_policy('rfid_config');
SELECT apply_rls_policy('rfid_printers');
SELECT apply_rls_policy('rfid_print_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');

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 INT;
    v_admin_role_id INT;
    v_manager_role_id INT;
    v_staff_role_id INT;
    v_buyer_role_id INT;
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'

C# Provisioning Service

// TenantProvisioningService.cs
public class TenantProvisioningService
{
    private readonly PosDbContext _dbContext;
    private readonly ILogger<TenantProvisioningService> _logger;

    public async Task<Guid> ProvisionTenantAsync(CreateTenantRequest request)
    {
        await using var transaction = await _dbContext.Database.BeginTransactionAsync();

        try
        {
            // Step 1: Create tenant record
            var tenant = new Tenant
            {
                Name = request.Name,
                Slug = request.Slug,
                Status = "provisioning",
                Tier = request.Tier,
                ContactEmail = request.ContactEmail
            };
            _dbContext.Tenants.Add(tenant);
            await _dbContext.SaveChangesAsync();

            // Step 2: Create subscription
            var subscription = new TenantSubscription
            {
                TenantId = tenant.Id,
                PlanId = request.PlanId,
                Status = "active",
                BillingCycle = "monthly",
                PriceCents = 9900,
                CurrentPeriodStart = DateTime.UtcNow,
                CurrentPeriodEnd = DateTime.UtcNow.AddMonths(1)
            };
            _dbContext.TenantSubscriptions.Add(subscription);
            await _dbContext.SaveChangesAsync();

            // Step 3: Seed default data (no schema creation needed!)
            await _dbContext.Database.ExecuteSqlRawAsync(
                "SELECT seed_tenant_data({0})", tenant.Id);

            // Step 4: Activate
            tenant.Status = "active";
            await _dbContext.SaveChangesAsync();

            await transaction.CommitAsync();

            _logger.LogInformation(
                "Tenant provisioned: {Name} ({Id})", tenant.Name, tenant.Id);

            return tenant.Id;
        }
        catch (Exception ex)
        {
            await transaction.RollbackAsync();
            _logger.LogError(ex, "Tenant provisioning failed for {Slug}", request.Slug);
            throw;
        }
    }
}

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 (45, all with tenant_id + RLS)

-- Domain 1: Products (5)
products, variants, product_collection, product_tag, brands

-- Domain 2: Categories (5)
categories, collections, tags, (product_collection, product_tag counted above)

-- Domain 3: Attributes (4)
product_groups, genders, origins, fabrics

-- Domain 4: Inventory (3)
locations, inventory_levels, inventory_transactions

-- Domain 5: Tax (2)
taxes, location_tax

-- Domain 6: Orders (3)
customers, orders, order_items

-- Domain 7: Preferences (1)
item_view_settings

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

-- Domain 10: Sync (4)
devices, sync_queue, sync_conflicts, sync_checkpoints

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

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

-- Domain 13: RFID (7)
rfid_config, rfid_printers, rfid_print_templates, rfid_print_jobs,
rfid_tags, rfid_scan_sessions, rfid_scan_events

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 51 tables organized by domain.

Document Information

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