Chapter 12: Schema Design

51 Tables Across 13 Domains

12.1 Overview

The POS Platform database consists of 51 tables organized into 13 functional domains. This chapter provides the complete schema design with the shared schema definition and tenant schema template.

Domain Summary

Domain	Schema	Tables	Purpose
1. Products & Variants	tenant	5	Product catalog with SKU/variant model
2. Categories & Tags	tenant	5	Flexible product organization
3. Product Attributes	tenant	4	Brand, gender, origin, fabric attributes
4. Inventory & Locations	tenant	3	Multi-location inventory tracking
5. Tax Configuration	tenant	2	Location-specific tax rates
6. Orders & Customers	tenant	3	Transactions and customer profiles
7. User Preferences	tenant	1	Per-user view settings
8. Tenant Management	shared	3	Platform tenant registry
9. Authentication & Authorization	shared + tenant	7	Users, sessions, roles
10. Offline Sync Infrastructure	tenant	4	Device sync and conflicts
11. Cash Drawer Operations	tenant	6	Shift and cash management
12. Payment Processing	tenant	4	Terminals and settlements
13. RFID Module (Optional)	tenant	7	Tag printing and scanning
TOTAL		51

12.2 Schema Architecture

Visual Diagram

┌─────────────────────────────────────────────────────────────────────────────┐
│                              pos_platform                                    │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                         shared SCHEMA (6 tables)                     │   │
│  │                                                                      │   │
│  │  ┌─────────────┐ ┌───────────────────┐ ┌─────────────────────────┐  │   │
│  │  │   tenants   │ │tenant_subscriptions│ │    tenant_modules      │  │   │
│  │  │  (registry) │ │   (billing)        │ │ (feature add-ons)      │  │   │
│  │  └─────────────┘ └───────────────────┘ └─────────────────────────┘  │   │
│  │  ┌─────────────────┐ ┌─────────────────────┐ ┌───────────────────┐  │   │
│  │  │     users       │ │    user_sessions    │ │  password_resets  │  │   │
│  │  │ (platform auth) │ │ (session tracking)  │ │   (recovery)      │  │   │
│  │  └─────────────────┘ └─────────────────────┘ └───────────────────┘  │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                                     │                                       │
│                    ┌────────────────┼────────────────┐                     │
│                    │                │                │                      │
│                    ▼                ▼                ▼                      │
│  ┌───────────────────────────────────────────────────────────────────────┐ │
│  │              tenant_XXXX SCHEMAS (45 tables each)                      │ │
│  │                                                                        │ │
│  │  Domain 1-3: Catalog        Domain 4-5: Inventory    Domain 6: Sales   │ │
│  │  ┌────────────────────┐    ┌───────────────────┐   ┌────────────────┐ │ │
│  │  │ products           │    │ locations         │   │ customers      │ │ │
│  │  │ variants           │    │ inventory_levels  │   │ orders         │ │ │
│  │  │ brands             │    │ inventory_trans   │   │ order_items    │ │ │
│  │  │ categories         │    │ taxes             │   └────────────────┘ │ │
│  │  │ collections        │    │ location_tax      │                      │ │
│  │  │ tags               │    └───────────────────┘   Domain 9: Auth     │ │
│  │  │ product_collection │                            ┌────────────────┐ │ │
│  │  │ product_tag        │    Domain 10: Sync         │ roles          │ │ │
│  │  │ product_groups     │    ┌───────────────────┐   │ role_perms     │ │ │
│  │  │ genders            │    │ devices           │   │ tenant_users   │ │ │
│  │  │ origins            │    │ sync_queue        │   │ tenant_settings│ │ │
│  │  │ fabrics            │    │ sync_conflicts    │   └────────────────┘ │ │
│  │  └────────────────────┘    │ sync_checkpoints  │                      │ │
│  │                            └───────────────────┘   Domain 11-12: Ops  │ │
│  │  Domain 7: Prefs                                   ┌────────────────┐ │ │
│  │  ┌────────────────────┐    Domain 13: RFID         │ shifts         │ │ │
│  │  │ item_view_settings │    ┌───────────────────┐   │ cash_drawers   │ │ │
│  │  └────────────────────┘    │ rfid_config       │   │ cash_counts    │ │ │
│  │                            │ rfid_printers     │   │ cash_movements │ │ │
│  │                            │ rfid_templates    │   │ cash_drops     │ │ │
│  │                            │ rfid_print_jobs   │   │ cash_pickups   │ │ │
│  │                            │ rfid_tags         │   │ payment_terms  │ │ │
│  │                            │ rfid_scan_sessions│   │ payment_attemps│ │ │
│  │                            │ rfid_scan_events  │   │ payment_batches│ │ │
│  │                            └───────────────────┘   │ payment_recon  │ │ │
│  │                                                    └────────────────┘ │ │
│  └───────────────────────────────────────────────────────────────────────┘ │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

12.3 Shared Schema: Complete CREATE TABLE Statements

The shared schema contains platform-wide tables for tenant management and authentication.

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;

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,
    schema_name VARCHAR(63) 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 TIMESTAMP,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    -- Constraints
    CONSTRAINT tenants_slug_unique UNIQUE (slug),
    CONSTRAINT tenants_schema_unique UNIQUE (schema_name),
    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.schema_name IS 'PostgreSQL schema name (tenant_XXXX format)';
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 TIMESTAMP NOT NULL,
    current_period_end TIMESTAMP NOT NULL,
    cancelled_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP 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 TIMESTAMP NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMP,
    monthly_fee_cents INT,
    trial_days_remaining INT,
    configuration JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP 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 TIMESTAMP,
    last_login_at TIMESTAMP,
    failed_login_count INT DEFAULT 0,
    locked_until TIMESTAMP,
    mfa_enabled BOOLEAN DEFAULT FALSE,
    mfa_secret VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP 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 TIMESTAMP DEFAULT NOW(),
    expires_at TIMESTAMP NOT NULL,
    last_activity_at TIMESTAMP 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 TIMESTAMP DEFAULT NOW(),
    expires_at TIMESTAMP NOT NULL,
    used_at TIMESTAMP,
    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)';

12.4 Tenant Schema Template

When a new tenant is provisioned, the following tables are created in their dedicated schema.

Schema Creation Function

-- Master function to create all tenant tables
CREATE OR REPLACE FUNCTION shared.create_tenant_tables(p_schema_name VARCHAR(63))
RETURNS VOID AS $$
BEGIN
    -- Create all tables in order (respecting foreign key dependencies)

    -- Domain 3: Product Attributes (no dependencies)
    PERFORM shared.create_tenant_attributes_tables(p_schema_name);

    -- Domain 2: Categories & Tags (no dependencies)
    PERFORM shared.create_tenant_category_tables(p_schema_name);

    -- Domain 4: Locations (no dependencies)
    PERFORM shared.create_tenant_location_tables(p_schema_name);

    -- Domain 5: Tax Configuration (depends on locations)
    PERFORM shared.create_tenant_tax_tables(p_schema_name);

    -- Domain 9: Roles & Permissions (minimal dependencies)
    PERFORM shared.create_tenant_auth_tables(p_schema_name);

    -- Domain 1: Products & Variants (depends on attributes)
    PERFORM shared.create_tenant_product_tables(p_schema_name);

    -- Domain 4 continued: Inventory (depends on variants, locations)
    PERFORM shared.create_tenant_inventory_tables(p_schema_name);

    -- Domain 6: Orders & Customers (depends on variants, locations)
    PERFORM shared.create_tenant_order_tables(p_schema_name);

    -- Domain 7: User Preferences
    PERFORM shared.create_tenant_preference_tables(p_schema_name);

    -- Domain 10: Offline Sync (depends on devices, locations)
    PERFORM shared.create_tenant_sync_tables(p_schema_name);

    -- Domain 11: Cash Drawer Operations (depends on locations)
    PERFORM shared.create_tenant_cash_tables(p_schema_name);

    -- Domain 12: Payment Processing (depends on locations, orders)
    PERFORM shared.create_tenant_payment_tables(p_schema_name);

    -- Domain 13: RFID Module (optional, depends on variants)
    PERFORM shared.create_tenant_rfid_tables(p_schema_name);

    RAISE NOTICE 'All tables created for schema: %', p_schema_name;
END;
$$ LANGUAGE plpgsql;

Example: Create Product Tables Function

-- Create Domain 1: Products & Variants tables
CREATE OR REPLACE FUNCTION shared.create_tenant_product_tables(p_schema_name VARCHAR(63))
RETURNS VOID AS $$
BEGIN
    -- products table
    EXECUTE format($DDL$
        CREATE TABLE %I.products (
            id SERIAL PRIMARY KEY,
            sku VARCHAR(50) NOT NULL,
            name VARCHAR(255) NOT NULL,
            description TEXT,
            brand_id INT REFERENCES %I.brands(id),
            product_group_id INT REFERENCES %I.product_groups(id),
            gender_id INT REFERENCES %I.genders(id),
            origin_id INT REFERENCES %I.origins(id),
            fabric_id INT REFERENCES %I.fabrics(id),
            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),
            created_at TIMESTAMP DEFAULT NOW(),
            updated_at TIMESTAMP DEFAULT NOW()
        )
    $DDL$, p_schema_name, p_schema_name, p_schema_name,
           p_schema_name, p_schema_name, p_schema_name);

    -- Products indexes
    EXECUTE format($DDL$
        CREATE UNIQUE INDEX idx_%I_products_sku ON %I.products(sku) WHERE deleted_at IS NULL
    $DDL$, p_schema_name, p_schema_name);

    EXECUTE format($DDL$
        CREATE INDEX idx_%I_products_brand ON %I.products(brand_id)
    $DDL$, p_schema_name, p_schema_name);

    EXECUTE format($DDL$
        CREATE INDEX idx_%I_products_active ON %I.products(is_active)
            WHERE is_active = TRUE AND deleted_at IS NULL
    $DDL$, p_schema_name, p_schema_name);

    -- variants table
    EXECUTE format($DDL$
        CREATE TABLE %I.variants (
            id SERIAL PRIMARY KEY,
            product_id INT NOT NULL REFERENCES %I.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),
            created_at TIMESTAMP DEFAULT NOW(),
            updated_at TIMESTAMP DEFAULT NOW()
        )
    $DDL$, p_schema_name, p_schema_name);

    -- Variants indexes
    EXECUTE format($DDL$
        CREATE UNIQUE INDEX idx_%I_variants_sku ON %I.variants(sku) WHERE deleted_at IS NULL
    $DDL$, p_schema_name, p_schema_name);

    EXECUTE format($DDL$
        CREATE UNIQUE INDEX idx_%I_variants_barcode ON %I.variants(barcode)
            WHERE barcode IS NOT NULL AND deleted_at IS NULL
    $DDL$, p_schema_name, p_schema_name);

    EXECUTE format($DDL$
        CREATE INDEX idx_%I_variants_product ON %I.variants(product_id)
    $DDL$, p_schema_name, p_schema_name);

    -- Junction tables for product collections and tags
    EXECUTE format($DDL$
        CREATE TABLE %I.product_collection (
            id SERIAL PRIMARY KEY,
            product_id INT NOT NULL REFERENCES %I.products(id) ON DELETE CASCADE,
            collection_id INT NOT NULL REFERENCES %I.collections(id) ON DELETE CASCADE,
            display_order INT DEFAULT 0,
            UNIQUE (product_id, collection_id)
        )
    $DDL$, p_schema_name, p_schema_name, p_schema_name);

    EXECUTE format($DDL$
        CREATE TABLE %I.product_tag (
            id SERIAL PRIMARY KEY,
            product_id INT NOT NULL REFERENCES %I.products(id) ON DELETE CASCADE,
            tag_id INT NOT NULL REFERENCES %I.tags(id) ON DELETE CASCADE,
            UNIQUE (product_id, tag_id)
        )
    $DDL$, p_schema_name, p_schema_name, p_schema_name);

    RAISE NOTICE 'Product tables created for schema: %', p_schema_name;
END;
$$ LANGUAGE plpgsql;

12.5 Seed Data Function

-- Seed default data for a new tenant
CREATE OR REPLACE FUNCTION shared.seed_tenant_data(
    p_schema_name VARCHAR(63),
    p_tenant_id UUID
)
RETURNS VOID AS $$
DECLARE
    v_owner_role_id INT;
    v_admin_role_id INT;
    v_manager_role_id INT;
    v_cashier_role_id INT;
    v_viewer_role_id INT;
BEGIN
    -- Seed default roles
    EXECUTE format($SQL$
        INSERT INTO %I.roles (name, display_name, description, is_system)
        VALUES
            ('owner', 'Owner', 'Full access to all features and settings', TRUE),
            ('admin', 'Administrator', 'Administrative access excluding billing', TRUE),
            ('manager', 'Manager', 'Store management and reporting access', TRUE),
            ('cashier', 'Cashier', 'Sales and basic customer operations', TRUE),
            ('viewer', 'Viewer', 'Read-only access to reports', TRUE)
        RETURNING id
    $SQL$, p_schema_name);

    -- Get role IDs for permission assignment
    EXECUTE format('SELECT id FROM %I.roles WHERE name = ''owner''', p_schema_name)
        INTO v_owner_role_id;
    EXECUTE format('SELECT id FROM %I.roles WHERE name = ''admin''', p_schema_name)
        INTO v_admin_role_id;
    EXECUTE format('SELECT id FROM %I.roles WHERE name = ''manager''', p_schema_name)
        INTO v_manager_role_id;
    EXECUTE format('SELECT id FROM %I.roles WHERE name = ''cashier''', p_schema_name)
        INTO v_cashier_role_id;

    -- Seed role permissions (Owner gets all)
    EXECUTE format($SQL$
        INSERT INTO %I.role_permissions (role_id, permission, granted) VALUES
        -- Owner permissions (all)
        (%s, 'products.*', TRUE),
        (%s, 'inventory.*', TRUE),
        (%s, 'orders.*', TRUE),
        (%s, 'customers.*', TRUE),
        (%s, 'reports.*', TRUE),
        (%s, 'settings.*', TRUE),
        (%s, 'users.*', TRUE),
        (%s, 'billing.*', TRUE),
        -- Manager permissions
        (%s, 'products.view', TRUE),
        (%s, 'products.edit', TRUE),
        (%s, 'inventory.*', TRUE),
        (%s, 'orders.*', TRUE),
        (%s, 'customers.*', TRUE),
        (%s, 'reports.view', TRUE),
        (%s, 'shifts.*', TRUE),
        -- Cashier permissions
        (%s, 'products.view', TRUE),
        (%s, 'orders.create', TRUE),
        (%s, 'orders.view', TRUE),
        (%s, 'customers.view', TRUE),
        (%s, 'customers.create', TRUE),
        (%s, 'shifts.open', TRUE),
        (%s, 'shifts.close', TRUE)
    $SQL$, p_schema_name,
        v_owner_role_id, v_owner_role_id, v_owner_role_id, v_owner_role_id,
        v_owner_role_id, v_owner_role_id, v_owner_role_id, v_owner_role_id,
        v_manager_role_id, v_manager_role_id, v_manager_role_id, v_manager_role_id,
        v_manager_role_id, v_manager_role_id, v_manager_role_id,
        v_cashier_role_id, v_cashier_role_id, v_cashier_role_id,
        v_cashier_role_id, v_cashier_role_id, v_cashier_role_id, v_cashier_role_id);

    -- Seed default genders
    EXECUTE format($SQL$
        INSERT INTO %I.genders (name) VALUES
            ('Men'), ('Women'), ('Unisex'), ('Kids'), ('Boys'), ('Girls')
    $SQL$, p_schema_name);

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

    RAISE NOTICE 'Seed data inserted for schema: %', p_schema_name;
END;
$$ LANGUAGE plpgsql;

12.6 Schema Provisioning SQL Script

Complete script to provision a new tenant:

-- ============================================================
-- TENANT PROVISIONING SCRIPT
-- Run this to create a new tenant
-- ============================================================

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

-- Begin transaction
BEGIN;

-- Step 1: Create tenant record
INSERT INTO shared.tenants (
    name, slug, schema_name, status, tier, contact_email
) VALUES (
    :'tenant_name',
    :'tenant_slug',
    'tenant_' || :'schema_id',
    '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: Create tenant schema
SELECT shared.provision_tenant_schema(:'tenant_id'::uuid, 'tenant_' || :'schema_id');

-- Step 4: Verify creation
SELECT
    t.name,
    t.slug,
    t.schema_name,
    t.status,
    (SELECT COUNT(*) FROM information_schema.tables
     WHERE table_schema = t.schema_name) AS table_count
FROM shared.tenants t
WHERE t.id = :'tenant_id'::uuid;

COMMIT;

-- Success message
\echo 'Tenant provisioned successfully!'
\echo 'Schema: tenant_' || :'schema_id'

12.7 Table Count by Domain

Domain	Tables	Shared	Tenant
1. Products & Variants	5	0	5
2. Categories & Tags	5	0	5
3. Product Attributes	4	0	4
4. Inventory & Locations	3	0	3
5. Tax Configuration	2	0	2
6. Orders & Customers	3	0	3
7. User Preferences	1	0	1
8. Tenant Management	3	3	0
9. Auth & Authorization	7	3	4
10. Offline Sync	4	0	4
11. Cash Drawer	6	0	6
12. Payment Processing	4	0	4
13. RFID Module	7	0	7
TOTAL	51	6	45

12.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

Tenant Schema Tables (45)

-- 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

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

Chapter 12 | Schema Design | POS Platform Blueprint v1.0.0

Keyboard shortcuts

The POS Platform Blueprint