Chapter 08: Entity Specifications
Complete SQL for All 54 Tables
8.1 Overview
This chapter provides complete CREATE TABLE statements for all 51 tables in the POS Platform database. Each table includes:
- Column definitions with data types
- Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK)
- Default values
- Comments explaining purpose
Usage: Copy-paste these statements to create the database schema.
Note: This chapter combines complete SQL CREATE TABLE statements with Domain Model entity field references (see Ch 04: Architecture Styles, Section L.9C). Domain Model sections provide business context, validation rules, and field descriptions. SQL sections provide implementation-ready schema.
Domain 1-2: Catalog (Products, Categories, Tags)
Domain Model: Product
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| PRODUCT |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| sku | String(50) | Unique stock keeping unit |
| barcode | String(50) | UPC/EAN barcode (nullable) |
| name | String(255) | Display name |
| description | Text | Full description |
| category_id | UUID | FK to Category |
| brand | String(100) | Brand name |
| vendor | String(100) | Supplier/vendor name |
| cost | Decimal | Wholesale cost |
| price | Decimal | Retail price |
| compare_at_price| Decimal | Original price (for discounts) |
| tax_code | String(20) | Tax category code |
| is_taxable | Boolean | Subject to sales tax |
| track_inventory | Boolean | Enable inventory tracking |
| is_active | Boolean | Available for sale |
| shopify_id | String(50) | Shopify product ID (if synced) |
| image_url | String(500) | Primary product image |
| weight | Decimal | Weight in default unit |
| weight_unit | String(10) | lb, kg, oz, g |
| tags | String[] | Searchable tags |
| metadata | JSONB | Custom attributes |
| created_at | Timestamp | Creation timestamp |
| updated_at | Timestamp | Last update timestamp |
+------------------------------------------------------------------+
Domain Model: ProductVariant
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| PRODUCT_VARIANT |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| product_id | UUID | FK to Product (required) |
| sku | String(50) | Unique variant SKU |
| barcode | String(50) | Variant barcode |
| name | String(255) | Variant name (e.g., "Large/Blue") |
| option1_name | String(50) | First option name (e.g., "Size") |
| option1_value | String(100) | First option value (e.g., "Large")|
| option2_name | String(50) | Second option name |
| option2_value | String(100) | Second option value |
| option3_name | String(50) | Third option name |
| option3_value | String(100) | Third option value |
| cost | Decimal | Variant cost (overrides product) |
| price | Decimal | Variant price (overrides product) |
| weight | Decimal | Variant weight |
| image_url | String(500) | Variant-specific image |
| shopify_variant_id | String(50) | Shopify variant ID |
| is_active | Boolean | Available for sale |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
Domain Model: Category
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| CATEGORY |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| name | String(255) | Category name |
| slug | String(255) | URL-friendly identifier |
| parent_id | UUID | FK to parent Category (nullable) |
| description | Text | Category description |
| image_url | String(500) | Category image |
| sort_order | Integer | Display order |
| is_active | Boolean | Show in UI |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
Domain Model: PricingRule
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| PRICING_RULE |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| name | String(255) | Rule name |
| type | String(50) | percentage, fixed, buy_x_get_y |
| value | Decimal | Discount value or percentage |
| product_id | UUID | Apply to specific product |
| category_id | UUID | Apply to category |
| customer_group | String(50) | Apply to customer group |
| min_quantity | Integer | Minimum quantity required |
| start_date | Timestamp | Rule start date |
| end_date | Timestamp | Rule end date |
| priority | Integer | Rule priority (higher wins) |
| is_active | Boolean | Rule is enabled |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
brands
-- Brand/manufacturer reference data
CREATE TABLE brands (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(100) NOT NULL,
logo_url VARCHAR(500),
description TEXT,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT brands_tenant_name_unique UNIQUE (tenant_id, name)
);
CREATE INDEX idx_brands_tenant ON brands(tenant_id);
CREATE INDEX idx_brands_active ON brands(tenant_id, is_active) WHERE is_active = TRUE;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE brands IS 'Brand/manufacturer reference data for product categorization';
product_groups
-- High-level product type categorization
CREATE TABLE product_groups (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(50) NOT NULL,
description TEXT,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT product_groups_tenant_name_unique UNIQUE (tenant_id, name)
);
CREATE INDEX idx_product_groups_tenant ON product_groups(tenant_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE product_groups IS 'High-level product types (Tops, Bottoms, Accessories, etc.)';
genders
-- Target demographic for products
CREATE TABLE genders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(20) NOT NULL,
CONSTRAINT genders_tenant_name_unique UNIQUE (tenant_id, name)
);
CREATE INDEX idx_genders_tenant ON genders(tenant_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE genders IS 'Target demographic (Men, Women, Unisex, Kids)';
origins
-- Country of origin for compliance tracking
CREATE TABLE origins (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
country VARCHAR(100) NOT NULL,
code VARCHAR(3),
CONSTRAINT origins_tenant_country_unique UNIQUE (tenant_id, country),
CONSTRAINT origins_tenant_code_unique UNIQUE (tenant_id, code)
);
CREATE INDEX idx_origins_tenant ON origins(tenant_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE origins IS 'Country of origin for compliance and import tracking';
COMMENT ON COLUMN origins.code IS 'ISO 3166-1 alpha-3 country code';
fabrics
-- Material composition and care instructions
CREATE TABLE fabrics (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(100) NOT NULL,
care_instructions TEXT,
CONSTRAINT fabrics_tenant_name_unique UNIQUE (tenant_id, name)
);
CREATE INDEX idx_fabrics_tenant ON fabrics(tenant_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE fabrics IS 'Fabric/material composition (100% Cotton, Polyester Blend, etc.)';
products
-- Master product record containing shared attributes
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(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) ON DELETE SET NULL,
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)
);
-- Indexes
CREATE UNIQUE INDEX idx_products_tenant_sku ON products(tenant_id, sku) WHERE deleted_at IS NULL;
CREATE INDEX idx_products_tenant ON products(tenant_id);
CREATE INDEX idx_products_brand ON products(tenant_id, brand_id);
CREATE INDEX idx_products_group ON products(tenant_id, product_group_id);
CREATE INDEX idx_products_active ON products(tenant_id, is_active) WHERE is_active = TRUE AND deleted_at IS NULL;
CREATE INDEX idx_products_deleted ON products(deleted_at) WHERE deleted_at IS NOT NULL;
CREATE INDEX idx_products_name_search ON products USING gin(to_tsvector('english', name));
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE products IS 'Master product catalog with shared attributes';
COMMENT ON COLUMN products.has_variants IS 'TRUE if product has size/color variants; inventory tracked at variant level';
COMMENT ON COLUMN products.deleted_at IS 'Soft delete timestamp (NULL = active)';
variants
-- Product variations (size, color) with own SKUs and inventory
CREATE TABLE variants (
id 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(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) ON DELETE SET NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Indexes (tenant_id included in unique constraints for RLS compatibility)
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 ON variants(tenant_id);
CREATE INDEX idx_variants_product ON variants(tenant_id, product_id);
CREATE INDEX idx_variants_size ON variants(size) WHERE size IS NOT NULL;
CREATE INDEX idx_variants_color ON variants(color) WHERE color IS NOT NULL;
CREATE INDEX idx_variants_deleted ON variants(deleted_at) WHERE deleted_at IS NOT NULL;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE variants IS 'Product variants with size/color combinations and unique SKUs';
COMMENT ON COLUMN variants.price_adjustment IS 'Price modifier from base (can be negative for discounts)';
COMMENT ON COLUMN variants.barcode IS 'UPC/EAN barcode for POS scanning';
categories
-- Hierarchical product categories
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(100) NOT NULL,
parent_id INT REFERENCES categories(id) ON DELETE SET NULL,
description TEXT,
display_order INT DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT categories_tenant_name_unique UNIQUE (tenant_id, name)
);
-- Indexes (tenant_id as leading column for RLS performance)
CREATE INDEX idx_categories_tenant ON categories(tenant_id);
CREATE INDEX idx_categories_tenant_parent ON categories(tenant_id, parent_id);
CREATE INDEX idx_categories_tenant_display ON categories(tenant_id, display_order);
CREATE INDEX idx_categories_tenant_active ON categories(tenant_id, is_active) WHERE is_active = TRUE;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE categories IS 'Hierarchical product categories (Clothing > Mens > Shirts)';
COMMENT ON COLUMN categories.parent_id IS 'Self-reference for hierarchy; NULL = root category';
collections
-- Marketing/seasonal product groupings
CREATE TABLE collections (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(100) NOT NULL,
description TEXT,
image_url VARCHAR(500),
is_active BOOLEAN DEFAULT TRUE,
start_date TIMESTAMPTZ,
end_date TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT collections_tenant_name_unique UNIQUE (tenant_id, name),
CONSTRAINT collections_date_order CHECK (end_date IS NULL OR start_date IS NULL OR end_date > start_date)
);
-- Indexes (tenant_id as leading column for RLS performance)
CREATE INDEX idx_collections_tenant ON collections(tenant_id);
CREATE INDEX idx_collections_tenant_active ON collections(tenant_id, is_active, start_date, end_date);
CREATE INDEX idx_collections_tenant_current ON collections(tenant_id, start_date, end_date)
WHERE is_active = TRUE;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE collections IS 'Marketing collections (Summer 2025, Clearance, New Arrivals)';
tags
-- Flexible product tagging
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(50) NOT NULL,
color VARCHAR(7),
CONSTRAINT tags_tenant_name_unique UNIQUE (tenant_id, name),
CONSTRAINT tags_color_hex CHECK (color IS NULL OR color ~ '^#[0-9A-Fa-f]{6}$')
);
-- Indexes (tenant_id as leading column for RLS performance)
CREATE INDEX idx_tags_tenant ON tags(tenant_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE tags IS 'Freeform product tags for quick filtering';
COMMENT ON COLUMN tags.color IS 'Hex color code for UI display (#FF5733)';
product_collection
-- Junction table: products to collections (many-to-many)
CREATE TABLE product_collection (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
collection_id INT NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
display_order INT DEFAULT 0,
CONSTRAINT product_collection_unique UNIQUE (tenant_id, product_id, collection_id)
);
CREATE INDEX idx_product_collection_tenant ON product_collection(tenant_id);
CREATE INDEX idx_product_collection_tenant_product ON product_collection(tenant_id, product_id);
CREATE INDEX idx_product_collection_tenant_collection ON product_collection(tenant_id, collection_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE product_collection IS 'Links products to marketing collections';
product_tag
-- Junction table: products to tags (many-to-many)
CREATE TABLE product_tag (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
tag_id INT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
CONSTRAINT product_tag_unique UNIQUE (tenant_id, product_id, tag_id)
);
CREATE INDEX idx_product_tag_tenant ON product_tag(tenant_id);
CREATE INDEX idx_product_tag_tenant_product ON product_tag(tenant_id, product_id);
CREATE INDEX idx_product_tag_tenant_tag ON product_tag(tenant_id, tag_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE product_tag IS 'Links products to tags for flexible categorization';
Domain 3: Inventory
Domain Model: InventoryItem
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| INVENTORY_ITEM |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| product_id | UUID | FK to Product |
| variant_id | UUID | FK to ProductVariant |
| location_id | UUID | FK to Location (required) |
| quantity_on_hand| Integer | Current stock quantity |
| quantity_committed | Integer | Reserved for pending orders |
| quantity_available | Integer | Calculated: on_hand - committed |
| quantity_incoming | Integer | Expected from purchase orders |
| reorder_point | Integer | Alert when below this level |
| reorder_quantity| Integer | Default reorder amount |
| bin_location | String(50) | Physical bin/shelf location |
| last_counted_at | Timestamp | Last physical count |
| last_received_at| Timestamp | Last inventory receipt |
| last_sold_at | Timestamp | Last sale of this item |
| created_at | Timestamp | Creation timestamp |
| updated_at | Timestamp | Last update |
+------------------------------------------------------------------+
Domain Model: InventoryAdjustment
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| INVENTORY_ADJUSTMENT |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| adjustment_number | String(50)| Human-readable ID |
| location_id | UUID | FK to Location |
| employee_id | UUID | FK to Employee (who adjusted) |
| reason | String(50) | count, damage, theft, return, etc.|
| notes | Text | Adjustment notes |
| status | String(20) | draft, pending, completed |
| created_at | Timestamp | Adjustment timestamp |
| completed_at | Timestamp | When finalized |
+------------------------------------------------------------------+
Domain Model: InventoryTransfer
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| INVENTORY_TRANSFER |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| transfer_number | String(50) | Human-readable ID |
| from_location_id| UUID | FK to source Location |
| to_location_id | UUID | FK to destination Location |
| employee_id | UUID | FK to Employee (initiator) |
| status | String(20) | draft, pending, in_transit, received |
| notes | Text | Transfer notes |
| shipped_at | Timestamp | When shipped |
| received_at | Timestamp | When received |
| received_by | UUID | FK to Employee (receiver) |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
locations
-- Physical stores, warehouses, and fulfillment centers
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
code VARCHAR(10) NOT NULL,
name VARCHAR(100) NOT NULL,
type VARCHAR(20) NOT NULL,
address VARCHAR(255),
city VARCHAR(100),
state VARCHAR(50),
postal_code VARCHAR(20),
phone VARCHAR(20),
timezone VARCHAR(50) NOT NULL DEFAULT 'UTC', -- Tenant timezone for display; all timestamps stored as TIMESTAMPTZ (UTC)
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT locations_tenant_code_unique UNIQUE (tenant_id, code),
CONSTRAINT locations_type_check CHECK (type IN ('store', 'warehouse', 'online', 'popup'))
);
-- Indexes (tenant_id as leading column for RLS performance)
CREATE INDEX idx_locations_tenant ON locations(tenant_id);
CREATE INDEX idx_locations_tenant_type ON locations(tenant_id, type);
CREATE INDEX idx_locations_tenant_active ON locations(tenant_id, is_active) WHERE is_active = TRUE;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE locations IS 'Physical and virtual locations for inventory tracking';
COMMENT ON COLUMN locations.code IS 'Short code (GM, HM, LM, NM, HQ)';
COMMENT ON COLUMN locations.type IS 'Location type: store, warehouse, online, popup';
inventory_levels
-- Current stock quantity per variant per location
-- available = on_hand - committed - reserved (computed, NEVER stored — see Architecture decision)
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) ON DELETE CASCADE,
location_id INT NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
quantity_on_hand INT DEFAULT 0,
quantity_committed INT DEFAULT 0,
quantity_reserved INT DEFAULT 0,
-- available = on_hand - committed - reserved (computed at query time, NEVER stored)
reorder_point INT DEFAULT 0,
reorder_quantity INT DEFAULT 0,
average_cost DECIMAL(10,2) DEFAULT 0.00,
last_counted_at TIMESTAMPTZ,
deleted_at TIMESTAMPTZ,
deleted_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT inventory_levels_tenant_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)
);
-- Indexes (tenant_id as leading column for RLS performance)
CREATE INDEX idx_inventory_levels_tenant ON inventory_levels(tenant_id);
CREATE INDEX idx_inventory_levels_tenant_lookup ON inventory_levels(tenant_id, variant_id, location_id)
WHERE deleted_at IS NULL;
CREATE INDEX idx_inventory_levels_tenant_location ON inventory_levels(tenant_id, location_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_inventory_levels_tenant_low_stock ON inventory_levels(tenant_id, location_id, quantity_on_hand)
WHERE quantity_on_hand <= reorder_point AND deleted_at IS NULL;
CREATE INDEX idx_inventory_levels_tenant_variant ON inventory_levels(tenant_id, variant_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE inventory_levels IS 'Current inventory quantities per variant per location';
COMMENT ON COLUMN inventory_levels.average_cost IS 'Weighted average cost: ((existing_qty * avg_cost) + (new_qty * new_cost)) / (existing_qty + new_qty)';
COMMENT ON COLUMN inventory_levels.reorder_point IS 'Low stock alert threshold';
inventory_transactions
-- Audit log for all inventory movements (append-only)
CREATE TABLE inventory_transactions (
id BIGSERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
variant_id INT NOT NULL REFERENCES variants(id) ON DELETE RESTRICT,
location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
transaction_type VARCHAR(20) NOT NULL,
quantity_change INT NOT NULL,
quantity_before INT NOT NULL,
quantity_after INT NOT NULL,
reference_type VARCHAR(50),
reference_id INT,
notes TEXT,
user_id UUID REFERENCES shared.users(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT inventory_trans_type_check CHECK (transaction_type IN (
'sale', 'return', 'purchase', 'transfer_in', 'transfer_out',
'adjustment', 'count', 'damage', 'theft', 'found'
)),
CONSTRAINT inventory_trans_math CHECK (quantity_after = quantity_before + quantity_change)
);
-- Indexes (BRIN for time-series, B-tree with tenant_id for lookups)
CREATE INDEX idx_inventory_trans_date ON inventory_transactions USING BRIN (created_at);
CREATE INDEX idx_inventory_trans_tenant ON inventory_transactions(tenant_id);
CREATE INDEX idx_inventory_trans_tenant_variant ON inventory_transactions(tenant_id, variant_id, created_at DESC);
CREATE INDEX idx_inventory_trans_tenant_location ON inventory_transactions(tenant_id, location_id, created_at DESC);
CREATE INDEX idx_inventory_trans_tenant_reference ON inventory_transactions(tenant_id, reference_type, reference_id)
WHERE reference_type IS NOT NULL;
CREATE INDEX idx_inventory_trans_tenant_type ON inventory_transactions(tenant_id, transaction_type, created_at DESC);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE inventory_transactions IS 'Immutable audit log of all inventory changes';
COMMENT ON COLUMN inventory_transactions.transaction_type IS 'Type of movement: sale, return, purchase, transfer, adjustment';
COMMENT ON COLUMN inventory_transactions.reference_type IS 'Source document type (order, transfer, adjustment)';
Domain 4: Sales
Domain Model: Sale
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| SALE |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| sale_number | String(50) | Human-readable sale ID |
| location_id | UUID | FK to Location (required) |
| register_id | String(20) | Register identifier |
| employee_id | UUID | FK to Employee (cashier) |
| customer_id | UUID | FK to Customer (nullable) |
| status | String(20) | draft, completed, voided, refunded|
| subtotal | Decimal | Sum of line items before tax |
| discount_total | Decimal | Total discounts applied |
| tax_total | Decimal | Total tax amount |
| total | Decimal | Final total (subtotal-discount+tax)|
| payment_status | String(20) | pending, partial, paid, refunded |
| source | String(20) | pos, online, mobile |
| notes | Text | Sale notes |
| voided_at | Timestamp | When sale was voided |
| voided_by | UUID | Employee who voided |
| void_reason | Text | Reason for void |
| created_at | Timestamp | Sale timestamp |
| updated_at | Timestamp | Last update |
+------------------------------------------------------------------+
Domain Model: SaleLineItem
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| SALE_LINE_ITEM |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| sale_id | UUID | FK to Sale (required) |
| product_id | UUID | FK to Product |
| variant_id | UUID | FK to ProductVariant |
| sku | String(50) | SKU at time of sale |
| name | String(255) | Product name at time of sale |
| quantity | Integer | Quantity sold |
| unit_price | Decimal | Price per unit |
| unit_cost | Decimal | Cost per unit (for profit calc) |
| discount_amount | Decimal | Discount on this line |
| discount_reason | String(100) | Reason for discount |
| tax_amount | Decimal | Tax on this line |
| total | Decimal | Line total |
| is_refunded | Boolean | Line was refunded |
| refunded_at | Timestamp | When refunded |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
Domain Model: Payment
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| PAYMENT |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| sale_id | UUID | FK to Sale (required) |
| payment_method | String(50) | cash, credit, debit, gift, store_credit |
| amount | Decimal | Payment amount |
| tendered | Decimal | Amount tendered (for cash) |
| change_given | Decimal | Change returned |
| reference | String(100) | Card last 4, check #, etc. |
| card_type | String(20) | visa, mastercard, amex, discover |
| auth_code | String(50) | Authorization code |
| status | String(20) | pending, completed, failed, refunded |
| gateway_response| JSONB | Full payment gateway response |
| created_at | Timestamp | Payment timestamp |
+------------------------------------------------------------------+
Domain Model: Refund
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| REFUND |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| sale_id | UUID | FK to original Sale |
| refund_number | String(50) | Human-readable refund ID |
| employee_id | UUID | FK to Employee (who processed) |
| reason | String(100) | Refund reason |
| subtotal | Decimal | Refund subtotal |
| tax_refunded | Decimal | Tax refunded |
| total | Decimal | Total refund amount |
| refund_method | String(50) | original, cash, store_credit |
| notes | Text | Additional notes |
| created_at | Timestamp | Refund timestamp |
+------------------------------------------------------------------+
Domain Model: RefundLineItem
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| REFUND_LINE_ITEM |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| refund_id | UUID | FK to Refund |
| sale_line_item_id | UUID | FK to original SaleLineItem |
| quantity | Integer | Quantity refunded |
| amount | Decimal | Refund amount for this line |
| restock | Boolean | Add back to inventory |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
customers
-- Customer profiles with loyalty tracking
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
loyalty_number VARCHAR(20),
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255),
phone VARCHAR(20),
address TEXT,
loyalty_points INT DEFAULT 0,
total_spent DECIMAL(12,2) DEFAULT 0,
visit_count INT DEFAULT 0,
first_visit TIMESTAMPTZ,
last_visit TIMESTAMPTZ,
deleted_at TIMESTAMPTZ,
deleted_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
anonymized_at TIMESTAMPTZ,
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)
);
-- Indexes (tenant_id as leading column for RLS performance)
CREATE INDEX idx_customers_tenant ON customers(tenant_id);
CREATE UNIQUE INDEX idx_customers_tenant_loyalty ON customers(tenant_id, loyalty_number)
WHERE loyalty_number IS NOT NULL AND deleted_at IS NULL;
CREATE UNIQUE INDEX idx_customers_tenant_email ON customers(tenant_id, email)
WHERE email IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_customers_tenant_name ON customers(tenant_id, last_name, first_name) WHERE deleted_at IS NULL;
CREATE INDEX idx_customers_tenant_phone ON customers(tenant_id, phone) WHERE phone IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX idx_customers_tenant_last_visit ON customers(tenant_id, last_visit DESC);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE customers IS 'Customer profiles with loyalty program tracking';
COMMENT ON COLUMN customers.anonymized_at IS 'GDPR: timestamp when PII was scrubbed';
orders
-- Transaction header with payment and status
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
order_number VARCHAR(20) NOT NULL,
customer_id INT REFERENCES customers(id) ON DELETE SET NULL,
location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
user_id UUID REFERENCES shared.users(id) ON DELETE SET NULL,
shift_id INT REFERENCES shifts(id) ON DELETE SET NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
subtotal DECIMAL(12,2) NOT NULL,
tax_amount DECIMAL(12,2) NOT NULL,
discount_amount DECIMAL(12,2) DEFAULT 0,
total_amount DECIMAL(12,2) NOT NULL,
payment_method VARCHAR(20) NOT NULL,
payment_reference VARCHAR(100),
notes TEXT,
deleted_at TIMESTAMPTZ,
deleted_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
void_reason VARCHAR(255),
created_at TIMESTAMPTZ DEFAULT NOW(),
completed_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT orders_tenant_number_unique UNIQUE (tenant_id, order_number),
CONSTRAINT orders_status_check CHECK (status IN ('pending', 'completed', 'refunded', 'voided', 'on_hold')),
CONSTRAINT orders_payment_check CHECK (payment_method IN (
'cash', 'credit', 'debit', 'mobile', 'gift_card', 'store_credit', 'split', 'check'
)),
CONSTRAINT orders_amounts_positive CHECK (
subtotal >= 0 AND tax_amount >= 0 AND discount_amount >= 0 AND total_amount >= 0
),
CONSTRAINT orders_total_math CHECK (
total_amount = subtotal + tax_amount - discount_amount
)
);
-- Indexes (tenant_id as leading column for RLS performance)
CREATE INDEX idx_orders_tenant ON orders(tenant_id);
CREATE INDEX idx_orders_tenant_date ON orders(tenant_id, created_at DESC);
CREATE INDEX idx_orders_tenant_location ON orders(tenant_id, location_id, created_at DESC);
CREATE INDEX idx_orders_tenant_customer ON orders(tenant_id, customer_id) WHERE customer_id IS NOT NULL;
CREATE INDEX idx_orders_tenant_shift ON orders(tenant_id, shift_id) WHERE shift_id IS NOT NULL;
CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status, created_at DESC);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE orders IS 'Sales transaction headers with payment info';
COMMENT ON COLUMN orders.order_number IS 'Format: LOC-YYYYMMDD-SEQUENCE';
COMMENT ON COLUMN orders.void_reason IS 'Required explanation when status = voided';
order_items
-- Line items with snapshots of product data at time of sale
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
variant_id INT NOT NULL REFERENCES variants(id) ON DELETE RESTRICT,
sku VARCHAR(50) NOT NULL,
product_name VARCHAR(255) NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
discount_amount DECIMAL(10,2) DEFAULT 0,
tax_amount DECIMAL(10,2) NOT NULL,
line_total DECIMAL(10,2) NOT NULL,
is_returned BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT order_items_quantity_positive CHECK (quantity > 0),
CONSTRAINT order_items_amounts_positive CHECK (
unit_price >= 0 AND discount_amount >= 0 AND tax_amount >= 0
),
CONSTRAINT order_items_total_math CHECK (
line_total = (unit_price * quantity) - discount_amount + tax_amount
)
);
-- Indexes (tenant_id as leading column for RLS performance)
CREATE INDEX idx_order_items_tenant ON order_items(tenant_id);
CREATE INDEX idx_order_items_tenant_order ON order_items(tenant_id, order_id);
CREATE INDEX idx_order_items_tenant_variant ON order_items(tenant_id, variant_id);
CREATE INDEX idx_order_items_tenant_sku ON order_items(tenant_id, sku);
CREATE INDEX idx_order_items_tenant_returned ON order_items(tenant_id, order_id) WHERE is_returned = TRUE;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE order_items IS 'Order line items with point-in-time price snapshots';
COMMENT ON COLUMN order_items.sku IS 'SKU snapshot at time of sale (product may change)';
COMMENT ON COLUMN order_items.product_name IS 'Name snapshot at time of sale';
Domain 5: Customer Loyalty & Gift Cards
Domain Model: Customer
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| CUSTOMER |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| customer_number | String(20) | Human-readable customer ID |
| first_name | String(100) | First name |
| last_name | String(100) | Last name |
| email | String(255) | Email address (unique) |
| phone | String(20) | Phone number |
| company | String(255) | Company name |
| date_of_birth | Date | Birthday (for loyalty) |
| tax_exempt | Boolean | Tax exempt status |
| tax_exempt_id | String(50) | Tax exemption certificate |
| notes | Text | Customer notes |
| loyalty_points | Integer | Current loyalty points |
| loyalty_tier | String(20) | bronze, silver, gold, platinum |
| total_spent | Decimal | Lifetime spending |
| visit_count | Integer | Total visits |
| average_order | Decimal | Average order value |
| last_visit_at | Timestamp | Last visit timestamp |
| tags | String[] | Customer tags |
| marketing_consent | Boolean | Opted in for marketing |
| shopify_id | String(50) | Shopify customer ID |
| created_at | Timestamp | Creation timestamp |
| updated_at | Timestamp | Last update |
+------------------------------------------------------------------+
Domain Model: CustomerAddress
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| CUSTOMER_ADDRESS |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| customer_id | UUID | FK to Customer |
| address_type | String(20) | billing, shipping |
| is_default | Boolean | Default address for type |
| first_name | String(100) | Recipient first name |
| last_name | String(100) | Recipient last name |
| company | String(255) | Company name |
| address_line1 | String(255) | Street address line 1 |
| address_line2 | String(255) | Street address line 2 |
| city | String(100) | City |
| state | String(50) | State/Province |
| postal_code | String(20) | ZIP/Postal code |
| country | String(2) | Country code (ISO 3166-1) |
| phone | String(20) | Contact phone |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
Domain Model: StoreCredit
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| STORE_CREDIT |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| customer_id | UUID | FK to Customer |
| code | String(50) | Unique credit code |
| original_amount | Decimal | Initial credit amount |
| current_balance | Decimal | Remaining balance |
| reason | String(100) | Reason for credit |
| issued_by | UUID | FK to Employee |
| expires_at | Timestamp | Expiration date (nullable) |
| is_active | Boolean | Credit is usable |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
Domain Model: LoyaltyTransaction
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| LOYALTY_TRANSACTION |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| customer_id | UUID | FK to Customer |
| sale_id | UUID | FK to Sale (if earned from sale) |
| type | String(20) | earn, redeem, adjustment, expire |
| points | Integer | Points (positive or negative) |
| balance_after | Integer | Balance after transaction |
| description | String(255) | Transaction description |
| created_by | UUID | FK to Employee |
| created_at | Timestamp | Transaction timestamp |
+------------------------------------------------------------------+
loyalty_accounts
-- Customer loyalty program accounts
CREATE TABLE loyalty_accounts (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
customer_id INT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
tier VARCHAR(20) NOT NULL DEFAULT 'bronze',
points_balance INT DEFAULT 0,
lifetime_points INT DEFAULT 0,
tier_start_date DATE,
tier_expiry_date DATE,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT loyalty_accounts_tenant_customer_unique UNIQUE (tenant_id, customer_id),
CONSTRAINT loyalty_tier_check CHECK (tier IN ('bronze', 'silver', 'gold', 'platinum')),
CONSTRAINT loyalty_points_positive CHECK (points_balance >= 0 AND lifetime_points >= 0)
);
CREATE INDEX idx_loyalty_tenant ON loyalty_accounts(tenant_id);
CREATE INDEX idx_loyalty_tenant_tier ON loyalty_accounts(tenant_id, tier) WHERE is_active = TRUE;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE loyalty_accounts IS 'Customer loyalty program tier and points tracking';
loyalty_transactions
-- Loyalty points earn/redeem history
CREATE TABLE loyalty_transactions (
id BIGSERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
loyalty_account_id INT NOT NULL REFERENCES loyalty_accounts(id) ON DELETE CASCADE,
order_id INT REFERENCES orders(id) ON DELETE SET NULL,
transaction_type VARCHAR(20) NOT NULL,
points INT NOT NULL,
points_balance_after INT NOT NULL,
description VARCHAR(255),
expires_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT loyalty_trans_type_check CHECK (transaction_type IN (
'earn', 'redeem', 'expire', 'adjust', 'bonus', 'transfer'
))
);
CREATE INDEX idx_loyalty_trans_tenant ON loyalty_transactions(tenant_id);
CREATE INDEX idx_loyalty_trans_tenant_account ON loyalty_transactions(tenant_id, loyalty_account_id, created_at DESC);
CREATE INDEX idx_loyalty_trans_tenant_order ON loyalty_transactions(tenant_id, order_id) WHERE order_id IS NOT NULL;
CREATE INDEX idx_loyalty_trans_expiry ON loyalty_transactions(expires_at)
WHERE expires_at IS NOT NULL AND transaction_type = 'earn';
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE loyalty_transactions IS 'Audit trail of loyalty point changes';
gift_cards
-- Gift card issuance and balance tracking
CREATE TABLE gift_cards (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
card_number VARCHAR(20) NOT NULL,
pin_hash VARCHAR(255),
initial_balance DECIMAL(10,2) NOT NULL,
current_balance DECIMAL(10,2) NOT NULL,
currency_code CHAR(3) DEFAULT 'USD',
issued_at TIMESTAMPTZ DEFAULT NOW(),
expires_at TIMESTAMPTZ,
issued_by UUID REFERENCES shared.users(id),
issued_location_id INT REFERENCES locations(id),
purchased_order_id INT REFERENCES orders(id),
is_active BOOLEAN DEFAULT TRUE,
deactivated_at TIMESTAMPTZ,
deactivated_reason VARCHAR(255),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT gift_cards_tenant_number_unique UNIQUE (tenant_id, card_number),
CONSTRAINT gift_cards_balance_positive CHECK (initial_balance > 0 AND current_balance >= 0),
CONSTRAINT gift_cards_balance_max CHECK (current_balance <= initial_balance)
);
CREATE INDEX idx_gift_cards_tenant ON gift_cards(tenant_id);
CREATE INDEX idx_gift_cards_tenant_number ON gift_cards(tenant_id, card_number);
CREATE INDEX idx_gift_cards_tenant_active ON gift_cards(tenant_id, is_active, expires_at);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE gift_cards IS 'Gift card issuance and balance management';
COMMENT ON COLUMN gift_cards.pin_hash IS 'Optional PIN for additional security (hashed)';
gift_card_transactions
-- Gift card usage history
CREATE TABLE gift_card_transactions (
id BIGSERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
gift_card_id INT NOT NULL REFERENCES gift_cards(id) ON DELETE CASCADE,
order_id INT REFERENCES orders(id) ON DELETE SET NULL,
transaction_type VARCHAR(20) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
balance_after DECIMAL(10,2) NOT NULL,
location_id INT REFERENCES locations(id),
user_id UUID REFERENCES shared.users(id),
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT gift_card_trans_type CHECK (transaction_type IN (
'issue', 'redeem', 'reload', 'refund', 'adjust', 'expire'
)),
CONSTRAINT gift_card_trans_amount CHECK (amount > 0)
);
CREATE INDEX idx_gift_card_trans_tenant ON gift_card_transactions(tenant_id);
CREATE INDEX idx_gift_card_trans_tenant_card ON gift_card_transactions(tenant_id, gift_card_id, created_at DESC);
CREATE INDEX idx_gift_card_trans_tenant_order ON gift_card_transactions(tenant_id, order_id) WHERE order_id IS NOT NULL;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE gift_card_transactions IS 'Audit trail of gift card balance changes';
Domain 6-7: Returns & Reporting
returns
-- Return/exchange header
CREATE TABLE returns (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
return_number VARCHAR(20) NOT NULL,
original_order_id INT NOT NULL REFERENCES orders(id) ON DELETE RESTRICT,
customer_id INT REFERENCES customers(id) ON DELETE SET NULL,
location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
user_id UUID REFERENCES shared.users(id) ON DELETE SET NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
return_type VARCHAR(20) NOT NULL,
subtotal DECIMAL(12,2) NOT NULL,
tax_amount DECIMAL(12,2) NOT NULL,
refund_amount DECIMAL(12,2) NOT NULL,
refund_method VARCHAR(20) NOT NULL,
reason VARCHAR(255),
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
completed_at TIMESTAMPTZ,
CONSTRAINT returns_tenant_number_unique UNIQUE (tenant_id, return_number),
CONSTRAINT returns_status_check CHECK (status IN ('pending', 'approved', 'completed', 'rejected')),
CONSTRAINT returns_type_check CHECK (return_type IN ('refund', 'exchange', 'store_credit')),
CONSTRAINT returns_method_check CHECK (refund_method IN (
'original_payment', 'cash', 'store_credit', 'gift_card'
))
);
CREATE INDEX idx_returns_tenant ON returns(tenant_id);
CREATE INDEX idx_returns_tenant_order ON returns(tenant_id, original_order_id);
CREATE INDEX idx_returns_tenant_date ON returns(tenant_id, created_at DESC);
CREATE INDEX idx_returns_tenant_status ON returns(tenant_id, status);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE returns IS 'Return and exchange transaction headers';
return_items
-- Individual items being returned
CREATE TABLE return_items (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
return_id INT NOT NULL REFERENCES returns(id) ON DELETE CASCADE,
order_item_id INT NOT NULL REFERENCES order_items(id) ON DELETE RESTRICT,
variant_id INT NOT NULL REFERENCES variants(id) ON DELETE RESTRICT,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
refund_amount DECIMAL(10,2) NOT NULL,
reason VARCHAR(50),
condition VARCHAR(20) DEFAULT 'sellable',
restocked BOOLEAN DEFAULT FALSE,
restocked_location_id INT REFERENCES locations(id),
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT return_items_quantity_positive CHECK (quantity > 0),
CONSTRAINT return_items_condition_check CHECK (condition IN (
'sellable', 'damaged', 'defective', 'other'
))
);
CREATE INDEX idx_return_items_tenant ON return_items(tenant_id);
CREATE INDEX idx_return_items_tenant_return ON return_items(tenant_id, return_id);
CREATE INDEX idx_return_items_tenant_variant ON return_items(tenant_id, variant_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE return_items IS 'Individual items in a return transaction';
reports (User Preferences)
-- Saved report configurations
CREATE TABLE reports (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(100) NOT NULL,
report_type VARCHAR(50) NOT NULL,
description TEXT,
query_config JSONB NOT NULL,
schedule_config JSONB,
is_system BOOLEAN DEFAULT FALSE,
is_public BOOLEAN DEFAULT FALSE,
created_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_reports_tenant ON reports(tenant_id);
CREATE INDEX idx_reports_tenant_type ON reports(tenant_id, report_type);
CREATE INDEX idx_reports_tenant_public ON reports(tenant_id, is_public) WHERE is_public = TRUE;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE reports IS 'Saved report configurations and schedules';
item_view_settings (user_preferences)
-- Personalized view preferences for inventory screens
CREATE TABLE item_view_settings (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
user_id UUID NOT NULL REFERENCES shared.users(id) ON DELETE CASCADE,
view_type VARCHAR(10) DEFAULT 'list',
visible_columns JSONB,
sort_preferences JSONB,
filter_defaults JSONB,
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT item_view_settings_tenant_user_unique UNIQUE (tenant_id, user_id),
CONSTRAINT item_view_settings_type_check CHECK (view_type IN ('list', 'grid', 'compact'))
);
CREATE INDEX idx_item_view_settings_tenant ON item_view_settings(tenant_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE item_view_settings IS 'User-specific inventory view preferences';
Domain 8: Multi-tenant (Shared Schema)
See Chapter 07 (Schema Design) for complete shared schema tables: tenants, tenant_subscriptions, tenant_modules
Domain 9: Authentication & Authorization
Domain Model: Employee
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| EMPLOYEE |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| employee_number | String(20) | Human-readable employee ID |
| first_name | String(100) | First name |
| last_name | String(100) | Last name |
| email | String(255) | Email address (unique) |
| phone | String(20) | Phone number |
| pin_hash | String(255) | Hashed PIN for clock-in |
| role_id | UUID | FK to Role |
| home_location_id| UUID | FK to primary Location |
| hire_date | Date | Date of hire |
| termination_date| Date | Date of termination |
| hourly_rate | Decimal | Hourly pay rate |
| commission_rate | Decimal | Commission percentage |
| is_active | Boolean | Employee is active |
| last_login_at | Timestamp | Last login timestamp |
| created_at | Timestamp | Creation timestamp |
| updated_at | Timestamp | Last update |
+------------------------------------------------------------------+
Domain Model: Role
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| ROLE |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| name | String(100) | Role name |
| code | String(50) | Role code (admin, manager, etc.) |
| description | Text | Role description |
| is_system | Boolean | System role (cannot delete) |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
Domain Model: Permission
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| PERMISSION |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| code | String(100) | Permission code |
| name | String(255) | Permission name |
| category | String(50) | Grouping category |
| description | Text | What this permission allows |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
Domain Model: RolePermission
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| ROLE_PERMISSION |
+------------------------------------------------------------------+
| role_id | UUID | FK to Role |
| permission_id | UUID | FK to Permission |
| created_at | Timestamp | When assigned |
| PRIMARY KEY (role_id, permission_id) |
+------------------------------------------------------------------+
Domain Model: Shift
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| SHIFT |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| employee_id | UUID | FK to Employee |
| location_id | UUID | FK to Location |
| clock_in | Timestamp | Clock in time |
| clock_out | Timestamp | Clock out time |
| break_minutes | Integer | Total break time |
| notes | Text | Shift notes |
| status | String(20) | active, completed, edited |
| edited_by | UUID | FK to Employee (if edited) |
| edit_reason | Text | Reason for edit |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
roles (Tenant Schema)
-- Role definitions per tenant
CREATE TABLE roles (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(50) NOT NULL,
display_name VARCHAR(100) NOT NULL,
description TEXT,
is_system BOOLEAN DEFAULT FALSE,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT roles_tenant_name_unique UNIQUE (tenant_id, name)
);
CREATE INDEX idx_roles_tenant ON roles(tenant_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE roles IS 'Role definitions customizable per tenant';
COMMENT ON COLUMN roles.is_system IS 'System roles (Owner, Admin, etc.) cannot be deleted';
role_permissions
-- Permission matrix linking roles to permissions
CREATE TABLE role_permissions (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
role_id INT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
permission VARCHAR(100) NOT NULL,
granted BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT role_permissions_tenant_unique UNIQUE (tenant_id, role_id, permission)
);
CREATE INDEX idx_role_permissions_tenant ON role_permissions(tenant_id);
CREATE INDEX idx_role_permissions_tenant_role ON role_permissions(tenant_id, role_id);
CREATE INDEX idx_role_permissions_tenant_perm ON role_permissions(tenant_id, permission);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE role_permissions IS 'Fine-grained permission assignments per role';
COMMENT ON COLUMN role_permissions.permission IS 'Permission ID (products.view, orders.create, etc.)';
tenant_users
-- User-tenant-role mapping
CREATE TABLE tenant_users (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
user_id UUID NOT NULL REFERENCES shared.users(id) ON DELETE CASCADE,
role_id INT NOT NULL REFERENCES roles(id) ON DELETE RESTRICT,
employee_id VARCHAR(20),
pin_hash VARCHAR(255),
hourly_rate DECIMAL(8,2),
commission_rate DECIMAL(5,4),
default_location_id INT REFERENCES locations(id) ON DELETE SET NULL,
is_active BOOLEAN DEFAULT TRUE,
hired_at DATE,
terminated_at DATE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT tenant_users_tenant_user_unique UNIQUE (tenant_id, user_id),
CONSTRAINT tenant_users_tenant_employee_unique UNIQUE (tenant_id, employee_id)
);
CREATE INDEX idx_tenant_users_tenant ON tenant_users(tenant_id);
CREATE INDEX idx_tenant_users_tenant_role ON tenant_users(tenant_id, role_id);
CREATE INDEX idx_tenant_users_tenant_location ON tenant_users(tenant_id, default_location_id);
CREATE INDEX idx_tenant_users_tenant_active ON tenant_users(tenant_id, is_active) WHERE is_active = TRUE;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE tenant_users IS 'Links platform users to tenant with role assignment';
COMMENT ON COLUMN tenant_users.employee_id IS 'Short ID for quick POS login';
COMMENT ON COLUMN tenant_users.pin_hash IS 'Quick login PIN (not primary auth)';
tenant_settings
-- Per-tenant configuration settings
CREATE TABLE tenant_settings (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
category VARCHAR(50) NOT NULL,
key VARCHAR(100) NOT NULL,
value TEXT NOT NULL,
value_type VARCHAR(20) NOT NULL,
description TEXT,
is_secret BOOLEAN DEFAULT FALSE,
updated_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT tenant_settings_tenant_key_unique UNIQUE (tenant_id, category, key),
CONSTRAINT tenant_settings_type_check CHECK (value_type IN ('string', 'number', 'boolean', 'json'))
);
CREATE INDEX idx_tenant_settings_tenant ON tenant_settings(tenant_id);
CREATE INDEX idx_tenant_settings_tenant_category ON tenant_settings(tenant_id, category);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE tenant_settings IS 'Key-value configuration settings per tenant';
COMMENT ON COLUMN tenant_settings.is_secret IS 'Mask value in UI (API keys, passwords)';
Domain 10: Offline Sync Infrastructure
devices
-- POS terminals and device registration
CREATE TABLE devices (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(100) NOT NULL,
device_type VARCHAR(30) NOT NULL,
hardware_id VARCHAR(255) NOT NULL,
location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
cash_drawer_id INT REFERENCES cash_drawers(id) ON DELETE SET NULL,
payment_terminal_id INT REFERENCES payment_terminals(id) ON DELETE SET NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
last_sync_at TIMESTAMPTZ,
last_seen_at TIMESTAMPTZ,
app_version VARCHAR(20),
os_version VARCHAR(50),
ip_address INET,
push_token VARCHAR(500),
settings JSONB,
registered_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT devices_tenant_hardware_unique UNIQUE (tenant_id, hardware_id),
CONSTRAINT devices_type_check CHECK (device_type IN ('pos_terminal', 'tablet', 'mobile', 'kiosk')),
CONSTRAINT devices_status_check CHECK (status IN ('pending', 'active', 'disabled', 'lost'))
);
CREATE INDEX idx_devices_tenant ON devices(tenant_id);
CREATE INDEX idx_devices_tenant_location ON devices(tenant_id, location_id);
CREATE INDEX idx_devices_tenant_status ON devices(tenant_id, status);
CREATE INDEX idx_devices_tenant_last_seen ON devices(tenant_id, last_seen_at);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE devices IS 'Registered POS devices and tablets';
COMMENT ON COLUMN devices.hardware_id IS 'Unique hardware fingerprint to prevent cloning';
sync_queue
-- Pending sync operations from offline devices
CREATE TABLE sync_queue (
id BIGSERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
device_id UUID NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
idempotency_key UUID NOT NULL DEFAULT gen_random_uuid(),
operation_type VARCHAR(50) NOT NULL,
entity_type VARCHAR(50) NOT NULL,
entity_id VARCHAR(100) NOT NULL,
payload JSONB NOT NULL,
checksum VARCHAR(64) NOT NULL,
sequence_number BIGINT NOT NULL,
causality_version BIGINT NOT NULL DEFAULT 0,
priority INT DEFAULT 5,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
attempts INT DEFAULT 0,
error_message TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
processed_at TIMESTAMPTZ,
CONSTRAINT sync_queue_idempotency_unique UNIQUE (idempotency_key),
CONSTRAINT sync_queue_status_check CHECK (status IN (
'pending', 'processing', 'completed', 'failed', 'conflict'
)),
CONSTRAINT sync_queue_priority_check CHECK (priority BETWEEN 1 AND 10)
);
CREATE INDEX idx_sync_queue_tenant ON sync_queue(tenant_id);
CREATE INDEX idx_sync_queue_tenant_device ON sync_queue(tenant_id, device_id, sequence_number);
CREATE INDEX idx_sync_queue_tenant_status ON sync_queue(tenant_id, status, priority, created_at);
CREATE INDEX idx_sync_queue_tenant_entity ON sync_queue(tenant_id, entity_type, entity_id);
CREATE INDEX idx_sync_queue_tenant_pending ON sync_queue(tenant_id, device_id, processed_at) WHERE processed_at IS NULL;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE sync_queue IS 'Pending sync operations from offline devices';
COMMENT ON COLUMN sync_queue.idempotency_key IS 'Prevents duplicate processing on replay';
COMMENT ON COLUMN sync_queue.causality_version IS 'Lamport timestamp for event ordering';
sync_conflicts
-- Conflict types enum
CREATE TYPE conflict_type_enum AS ENUM (
'update_update',
'update_delete',
'delete_update',
'version_mismatch',
'schema_change'
);
CREATE TYPE resolution_strategy_enum AS ENUM (
'keep_local',
'keep_server',
'merge',
'ignore',
'auto_local',
'auto_server'
);
-- Conflict tracking requiring resolution
CREATE TABLE sync_conflicts (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
sync_queue_id BIGINT NOT NULL REFERENCES sync_queue(id) ON DELETE CASCADE,
entity_type VARCHAR(50) NOT NULL,
entity_id VARCHAR(100) NOT NULL,
local_data JSONB NOT NULL,
server_data JSONB NOT NULL,
conflict_type conflict_type_enum NOT NULL,
resolution resolution_strategy_enum,
resolution_data JSONB,
resolution_notes TEXT,
resolved_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
resolved_at TIMESTAMPTZ,
auto_resolved BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_sync_conflicts_tenant ON sync_conflicts(tenant_id);
CREATE INDEX idx_sync_conflicts_tenant_entity ON sync_conflicts(tenant_id, entity_type, entity_id);
CREATE INDEX idx_sync_conflicts_tenant_unresolved ON sync_conflicts(tenant_id, created_at) WHERE resolved_at IS NULL;
CREATE INDEX idx_sync_conflicts_tenant_type ON sync_conflicts(tenant_id, conflict_type);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE sync_conflicts IS 'Sync conflicts requiring manual or automatic resolution';
COMMENT ON COLUMN sync_conflicts.auto_resolved IS 'TRUE if resolved by policy without human intervention';
sync_checkpoints
-- Sync progress tracking per device
CREATE TABLE sync_checkpoints (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
device_id UUID NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
entity_type VARCHAR(50) NOT NULL,
direction VARCHAR(10) NOT NULL,
last_sync_at TIMESTAMPTZ NOT NULL,
last_sequence BIGINT NOT NULL,
last_server_timestamp TIMESTAMPTZ,
records_synced INT DEFAULT 0,
error_count INT DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT sync_checkpoints_unique UNIQUE (device_id, entity_type, direction),
CONSTRAINT sync_checkpoints_direction_check CHECK (direction IN ('push', 'pull'))
);
CREATE INDEX idx_sync_checkpoints_tenant ON sync_checkpoints(tenant_id);
CREATE INDEX idx_sync_checkpoints_tenant_device ON sync_checkpoints(tenant_id, device_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE sync_checkpoints IS 'Tracks sync progress for incremental synchronization';
Domain 10B: Event Infrastructure
event_outbox
-- Transactional Outbox pattern: domain events published reliably via polling
-- Events are inserted in the same transaction as the business operation,
-- then a background worker publishes them to LISTEN/NOTIFY (v1) or Kafka (v2).
CREATE TABLE event_outbox (
id BIGSERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
aggregate_type VARCHAR(100) NOT NULL,
aggregate_id UUID NOT NULL,
event_type VARCHAR(100) NOT NULL,
event_data JSONB NOT NULL,
metadata JSONB DEFAULT '{}',
idempotency_key UUID NOT NULL DEFAULT gen_random_uuid(),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
retry_count INT DEFAULT 0,
max_retries INT DEFAULT 5,
error_message TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
published_at TIMESTAMPTZ,
failed_at TIMESTAMPTZ,
CONSTRAINT event_outbox_status_check CHECK (status IN (
'pending', 'published', 'failed', 'dead_letter'
)),
CONSTRAINT event_outbox_idempotency_unique UNIQUE (idempotency_key)
);
-- BRIN index on created_at for time-range scans (append-only table)
CREATE INDEX idx_event_outbox_pending ON event_outbox(status, created_at)
WHERE status = 'pending';
CREATE INDEX idx_event_outbox_tenant ON event_outbox(tenant_id);
CREATE INDEX idx_event_outbox_aggregate ON event_outbox(tenant_id, aggregate_type, aggregate_id);
CREATE INDEX idx_event_outbox_created ON event_outbox USING BRIN (created_at);
CREATE INDEX idx_event_outbox_failed ON event_outbox(status, retry_count)
WHERE status = 'failed' AND retry_count < max_retries;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE event_outbox IS 'Transactional Outbox: domain events queued for reliable async publishing';
COMMENT ON COLUMN event_outbox.idempotency_key IS 'SHA-256 or UUID for 24h deduplication window';
COMMENT ON COLUMN event_outbox.aggregate_type IS 'e.g., Order, Product, InventoryLevel, RfidScanSession';
state_transitions
-- DB-driven state machine transitions for all 16 aggregate state machines
-- Used by the state machine engine instead of hardcoded if/else logic.
-- Reference: Ch 04 Section L.4A.4 (Domain Events Catalog) for all state machines.
CREATE TABLE state_transitions (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
aggregate_type VARCHAR(100) NOT NULL,
from_state VARCHAR(50) NOT NULL,
to_state VARCHAR(50) NOT NULL,
trigger_event VARCHAR(100) NOT NULL,
guard_condition TEXT,
side_effects TEXT[],
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT state_transitions_unique UNIQUE (tenant_id, aggregate_type, from_state, trigger_event),
CONSTRAINT state_transitions_no_self CHECK (from_state != to_state)
);
CREATE INDEX idx_state_transitions_tenant ON state_transitions(tenant_id);
CREATE INDEX idx_state_transitions_lookup ON state_transitions(tenant_id, aggregate_type, from_state)
WHERE is_active = TRUE;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE state_transitions IS 'DB-driven state machine definitions for 16 aggregate types';
COMMENT ON COLUMN state_transitions.guard_condition IS 'Optional C# expression evaluated before transition';
COMMENT ON COLUMN state_transitions.side_effects IS 'Array of domain event types emitted on transition';
-- Example seed data (Order state machine):
-- INSERT INTO state_transitions (tenant_id, aggregate_type, from_state, to_state, trigger_event, side_effects)
-- VALUES
-- ('{tid}', 'Order', 'draft', 'open', 'OrderConfirmed', ARRAY['OrderConfirmedEvent']),
-- ('{tid}', 'Order', 'open', 'completed', 'OrderCompleted', ARRAY['OrderCompletedEvent', 'InventoryDeductedEvent']),
-- ('{tid}', 'Order', 'open', 'voided', 'OrderVoided', ARRAY['OrderVoidedEvent', 'InventoryRestoredEvent']),
-- ('{tid}', 'Order', 'completed', 'returning', 'ReturnInitiated', ARRAY['ReturnInitiatedEvent']);
Domain 11: Cash Drawer Operations
Domain Model: Location
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| LOCATION |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| code | String(10) | Short code (HQ, GM, LM) |
| name | String(255) | Full location name |
| type | String(20) | store, warehouse, popup |
| address_line1 | String(255) | Street address |
| address_line2 | String(255) | Suite/unit |
| city | String(100) | City |
| state | String(50) | State/Province |
| postal_code | String(20) | ZIP/Postal code |
| country | String(2) | Country code |
| phone | String(20) | Phone number |
| email | String(255) | Email address |
| timezone | String(50) | IANA timezone |
| currency | String(3) | Currency code |
| shopify_location_id | String(50) | Shopify location ID |
| is_active | Boolean | Location is operational |
| can_fulfill | Boolean | Can fulfill online orders |
| is_visible_online | Boolean | Show inventory online |
| fulfillment_priority | Integer| Order for fulfillment routing |
| opening_hours | JSONB | Weekly schedule |
| created_at | Timestamp | Creation timestamp |
| updated_at | Timestamp | Last update |
+------------------------------------------------------------------+
Domain Model: Register
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| REGISTER |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| location_id | UUID | FK to Location |
| register_number | String(20) | Register identifier |
| name | String(100) | Display name |
| receipt_footer | Text | Custom receipt message |
| is_active | Boolean | Register is operational |
| last_opened_at | Timestamp | Last opened |
| last_closed_at | Timestamp | Last closed |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
Domain Model: CashDrawer
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| CASH_DRAWER |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| register_id | UUID | FK to Register |
| employee_id | UUID | FK to Employee (opened by) |
| opened_at | Timestamp | When opened |
| closed_at | Timestamp | When closed |
| opening_balance | Decimal | Starting cash amount |
| closing_balance | Decimal | Ending cash amount |
| expected_balance| Decimal | Expected based on transactions |
| variance | Decimal | Difference (closing - expected) |
| notes | Text | Drawer notes |
| status | String(20) | open, closed, reconciled |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
Domain Model: TaxRate
Business context reference — see Ch 04: Architecture Styles, Section L.9C
+------------------------------------------------------------------+
| TAX_RATE |
+------------------------------------------------------------------+
| id | UUID | Primary key |
| location_id | UUID | FK to Location (nullable=all) |
| name | String(100) | Tax name |
| rate | Decimal | Tax rate percentage |
| tax_code | String(20) | Tax category code |
| is_compound | Boolean | Calculated on tax-inclusive total |
| priority | Integer | Order of application |
| is_active | Boolean | Tax is active |
| created_at | Timestamp | Creation timestamp |
+------------------------------------------------------------------+
shifts
-- Shift lifecycle management
CREATE TABLE shifts (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
shift_number VARCHAR(20) NOT NULL,
location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
cash_drawer_id INT NOT NULL REFERENCES cash_drawers(id) ON DELETE RESTRICT,
device_id UUID REFERENCES devices(id) ON DELETE SET NULL,
opened_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
closed_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
status VARCHAR(20) NOT NULL DEFAULT 'open',
opened_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
closed_at TIMESTAMPTZ,
opening_cash DECIMAL(12,2) NOT NULL,
expected_cash DECIMAL(12,2),
actual_cash DECIMAL(12,2),
cash_variance DECIMAL(12,2),
total_sales DECIMAL(12,2) DEFAULT 0,
total_refunds DECIMAL(12,2) DEFAULT 0,
total_voids DECIMAL(12,2) DEFAULT 0,
transaction_count INT DEFAULT 0,
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT shifts_tenant_number_unique UNIQUE (tenant_id, shift_number),
CONSTRAINT shifts_status_check CHECK (status IN ('open', 'closing', 'closed', 'reconciled')),
CONSTRAINT shifts_opening_positive CHECK (opening_cash >= 0)
);
CREATE INDEX idx_shifts_tenant ON shifts(tenant_id);
CREATE INDEX idx_shifts_tenant_location ON shifts(tenant_id, location_id, opened_at DESC);
CREATE INDEX idx_shifts_tenant_drawer_open ON shifts(tenant_id, cash_drawer_id, status) WHERE status = 'open';
CREATE INDEX idx_shifts_tenant_date ON shifts(tenant_id, opened_at DESC);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE shifts IS 'Employee shift lifecycle with cash accountability';
COMMENT ON COLUMN shifts.shift_number IS 'Format: LOC-YYYYMMDD-SEQUENCE';
cash_drawers
-- Physical cash drawer registration
CREATE TABLE cash_drawers (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(50) NOT NULL,
drawer_number VARCHAR(20) NOT NULL,
location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
current_shift_id INT, -- FK added after shifts table exists
status VARCHAR(20) NOT NULL DEFAULT 'available',
is_active BOOLEAN DEFAULT TRUE,
last_counted_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT cash_drawers_tenant_number_unique UNIQUE (tenant_id, drawer_number),
CONSTRAINT cash_drawers_status_check CHECK (status IN ('available', 'in_use', 'maintenance'))
);
-- Add FK after shifts table exists
ALTER TABLE cash_drawers ADD CONSTRAINT cash_drawers_shift_fk
FOREIGN KEY (current_shift_id) REFERENCES shifts(id) ON DELETE SET NULL;
CREATE INDEX idx_cash_drawers_tenant ON cash_drawers(tenant_id);
CREATE INDEX idx_cash_drawers_tenant_location ON cash_drawers(tenant_id, location_id);
CREATE INDEX idx_cash_drawers_tenant_status ON cash_drawers(tenant_id, status, location_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE cash_drawers IS 'Physical cash drawer registration and status';
cash_counts
-- Detailed cash denomination counts
CREATE TABLE cash_counts (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
shift_id INT NOT NULL REFERENCES shifts(id) ON DELETE CASCADE,
count_type VARCHAR(20) NOT NULL,
counted_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
count_timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-- Coins
pennies INT DEFAULT 0,
nickels INT DEFAULT 0,
dimes INT DEFAULT 0,
quarters INT DEFAULT 0,
half_dollars INT DEFAULT 0,
dollar_coins INT DEFAULT 0,
-- Bills
ones INT DEFAULT 0,
twos INT DEFAULT 0,
fives INT DEFAULT 0,
tens INT DEFAULT 0,
twenties INT DEFAULT 0,
fifties INT DEFAULT 0,
hundreds INT DEFAULT 0,
-- Other
rolled_coins DECIMAL(10,2) DEFAULT 0,
other_amount DECIMAL(10,2) DEFAULT 0,
total_amount DECIMAL(12,2) NOT NULL,
notes TEXT,
CONSTRAINT cash_counts_type_check CHECK (count_type IN (
'opening', 'closing', 'drop', 'pickup', 'audit', 'mid_shift'
)),
CONSTRAINT cash_counts_total_positive CHECK (total_amount >= 0)
);
CREATE INDEX idx_cash_counts_tenant ON cash_counts(tenant_id);
CREATE INDEX idx_cash_counts_tenant_shift ON cash_counts(tenant_id, shift_id, count_type);
CREATE INDEX idx_cash_counts_tenant_timestamp ON cash_counts(tenant_id, count_timestamp DESC);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE cash_counts IS 'Denomination-level cash counts for accountability';
cash_movements
-- Cash audit trail for all drawer operations
CREATE TABLE cash_movements (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
shift_id INT NOT NULL REFERENCES shifts(id) ON DELETE CASCADE,
cash_drawer_id INT NOT NULL REFERENCES cash_drawers(id) ON DELETE RESTRICT,
movement_type VARCHAR(30) NOT NULL,
amount DECIMAL(12,2) NOT NULL,
reference_type VARCHAR(50),
reference_id INT,
performed_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
approved_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
reason VARCHAR(255),
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT cash_movements_type_check CHECK (movement_type IN (
'sale_cash', 'refund_cash', 'paid_in', 'paid_out',
'cash_drop', 'cash_pickup', 'opening_float', 'closing_count', 'no_sale'
))
);
CREATE INDEX idx_cash_movements_tenant ON cash_movements(tenant_id);
CREATE INDEX idx_cash_movements_tenant_shift ON cash_movements(tenant_id, shift_id);
CREATE INDEX idx_cash_movements_tenant_drawer ON cash_movements(tenant_id, cash_drawer_id, created_at DESC);
CREATE INDEX idx_cash_movements_tenant_type ON cash_movements(tenant_id, movement_type);
CREATE INDEX idx_cash_movements_tenant_reference ON cash_movements(tenant_id, reference_type, reference_id)
WHERE reference_type IS NOT NULL;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE cash_movements IS 'Immutable audit trail of all cash drawer operations';
cash_drops
-- Cash drops from drawer to safe
CREATE TABLE cash_drops (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
shift_id INT NOT NULL REFERENCES shifts(id) ON DELETE CASCADE,
cash_drawer_id INT NOT NULL REFERENCES cash_drawers(id) ON DELETE RESTRICT,
drop_number VARCHAR(20) NOT NULL,
amount DECIMAL(12,2) NOT NULL,
dropped_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
witnessed_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
safe_bag_number VARCHAR(50),
counted_amount DECIMAL(12,2),
variance DECIMAL(12,2),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
notes TEXT,
dropped_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
verified_at TIMESTAMPTZ,
CONSTRAINT cash_drops_tenant_number_unique UNIQUE (tenant_id, drop_number),
CONSTRAINT cash_drops_amount_positive CHECK (amount > 0),
CONSTRAINT cash_drops_status_check CHECK (status IN ('pending', 'verified', 'variance'))
);
CREATE INDEX idx_cash_drops_tenant ON cash_drops(tenant_id);
CREATE INDEX idx_cash_drops_tenant_shift ON cash_drops(tenant_id, shift_id);
CREATE INDEX idx_cash_drops_tenant_status ON cash_drops(tenant_id, status) WHERE status = 'pending';
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE cash_drops IS 'Cash drops from drawer to safe with verification tracking';
cash_pickups
-- Armored car pickup tracking
CREATE TABLE cash_pickups (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
pickup_date DATE NOT NULL,
carrier VARCHAR(100) NOT NULL,
driver_name VARCHAR(100),
driver_id VARCHAR(50),
pickup_number VARCHAR(50),
expected_amount DECIMAL(12,2) NOT NULL,
bag_count INT NOT NULL,
bag_numbers TEXT[],
received_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
verified_amount DECIMAL(12,2),
variance DECIMAL(12,2),
status VARCHAR(20) NOT NULL DEFAULT 'picked_up',
notes TEXT,
picked_up_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deposited_at TIMESTAMPTZ,
CONSTRAINT cash_pickups_tenant_number_unique UNIQUE (tenant_id, pickup_number),
CONSTRAINT cash_pickups_status_check CHECK (status IN (
'picked_up', 'in_transit', 'deposited', 'variance'
))
);
CREATE INDEX idx_cash_pickups_tenant ON cash_pickups(tenant_id);
CREATE INDEX idx_cash_pickups_tenant_location ON cash_pickups(tenant_id, location_id, pickup_date DESC);
CREATE INDEX idx_cash_pickups_tenant_status ON cash_pickups(tenant_id, status);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE cash_pickups IS 'Armored car pickup and bank deposit tracking';
Domain 12: Payment Processing
payment_terminals
-- Payment device registration
CREATE TABLE payment_terminals (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
terminal_id VARCHAR(50) NOT NULL,
name VARCHAR(100) NOT NULL,
location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
device_id UUID REFERENCES devices(id) ON DELETE SET NULL,
processor VARCHAR(50) NOT NULL,
terminal_type VARCHAR(30) NOT NULL,
model VARCHAR(100),
serial_number VARCHAR(100),
status VARCHAR(20) NOT NULL DEFAULT 'active',
supports_contactless BOOLEAN DEFAULT TRUE,
supports_emv BOOLEAN DEFAULT TRUE,
supports_swipe BOOLEAN DEFAULT TRUE,
last_transaction_at TIMESTAMPTZ,
last_batch_at TIMESTAMPTZ,
configuration JSONB,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT payment_terminals_tenant_id_unique UNIQUE (tenant_id, terminal_id),
CONSTRAINT payment_terminals_type_check CHECK (terminal_type IN (
'integrated', 'standalone', 'virtual', 'mobile'
)),
CONSTRAINT payment_terminals_status_check CHECK (status IN (
'active', 'offline', 'maintenance', 'disabled'
))
);
CREATE INDEX idx_payment_terminals_tenant ON payment_terminals(tenant_id);
CREATE INDEX idx_payment_terminals_tenant_location ON payment_terminals(tenant_id, location_id);
CREATE INDEX idx_payment_terminals_tenant_status ON payment_terminals(tenant_id, status);
CREATE INDEX idx_payment_terminals_device ON payment_terminals(device_id) WHERE device_id IS NOT NULL;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE payment_terminals IS 'Payment device registration and configuration';
payment_attempts
-- Payment processing attempt tracking
CREATE TABLE payment_attempts (
id BIGSERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
order_id INT NOT NULL REFERENCES orders(id) ON DELETE RESTRICT,
terminal_id INT REFERENCES payment_terminals(id) ON DELETE SET NULL,
payment_method VARCHAR(30) NOT NULL,
card_type VARCHAR(20),
card_last_four CHAR(4),
card_entry_mode VARCHAR(20),
amount DECIMAL(12,2) NOT NULL,
tip_amount DECIMAL(10,2) DEFAULT 0,
total_amount DECIMAL(12,2) NOT NULL,
currency_code CHAR(3) NOT NULL DEFAULT 'USD',
status VARCHAR(20) NOT NULL,
processor_response_code VARCHAR(10),
processor_response_text VARCHAR(255),
authorization_code VARCHAR(20),
processor_transaction_id VARCHAR(100),
avs_result VARCHAR(10),
cvv_result VARCHAR(10),
emv_application_id VARCHAR(32),
emv_cryptogram VARCHAR(64),
risk_score INT,
created_at TIMESTAMPTZ DEFAULT NOW(),
processed_at TIMESTAMPTZ,
CONSTRAINT payment_attempts_method_check CHECK (payment_method IN (
'card', 'cash', 'gift_card', 'store_credit', 'check', 'mobile_pay'
)),
CONSTRAINT payment_attempts_status_check CHECK (status IN (
'pending', 'approved', 'declined', 'error', 'voided', 'refunded'
)),
CONSTRAINT payment_attempts_entry_check CHECK (card_entry_mode IS NULL OR card_entry_mode IN (
'chip', 'swipe', 'contactless', 'manual', 'ecommerce', 'fallback'
))
);
CREATE INDEX idx_payment_attempts_tenant ON payment_attempts(tenant_id);
CREATE INDEX idx_payment_attempts_tenant_order ON payment_attempts(tenant_id, order_id);
CREATE INDEX idx_payment_attempts_tenant_status ON payment_attempts(tenant_id, status, created_at DESC);
CREATE INDEX idx_payment_attempts_processor ON payment_attempts(processor_transaction_id)
WHERE processor_transaction_id IS NOT NULL;
CREATE INDEX idx_payment_attempts_tenant_date ON payment_attempts(tenant_id, created_at DESC);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE payment_attempts IS 'Payment processing attempts with full audit trail';
COMMENT ON COLUMN payment_attempts.emv_cryptogram IS 'EMV TC/ARQC for chip transactions';
payment_batches
-- End-of-day settlement batch tracking
CREATE TABLE payment_batches (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
batch_number VARCHAR(50) NOT NULL,
location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
terminal_id INT REFERENCES payment_terminals(id) ON DELETE SET NULL,
processor VARCHAR(50) NOT NULL,
batch_date DATE NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'open',
transaction_count INT NOT NULL DEFAULT 0,
gross_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
refund_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
net_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
fee_amount DECIMAL(10,2),
deposit_amount DECIMAL(12,2),
opened_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
submitted_at TIMESTAMPTZ,
settled_at TIMESTAMPTZ,
deposit_reference VARCHAR(100),
notes TEXT,
CONSTRAINT payment_batches_tenant_number_unique UNIQUE (tenant_id, batch_number),
CONSTRAINT payment_batches_status_check CHECK (status IN (
'open', 'pending', 'settled', 'rejected'
)),
CONSTRAINT payment_batches_net_math CHECK (net_amount = gross_amount - refund_amount)
);
CREATE INDEX idx_payment_batches_tenant ON payment_batches(tenant_id);
CREATE INDEX idx_payment_batches_tenant_location ON payment_batches(tenant_id, location_id, batch_date DESC);
CREATE INDEX idx_payment_batches_tenant_status ON payment_batches(tenant_id, status) WHERE status IN ('open', 'pending');
CREATE INDEX idx_payment_batches_tenant_date ON payment_batches(tenant_id, batch_date DESC);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE payment_batches IS 'End-of-day settlement batch tracking';
payment_reconciliation
-- Variance tracking between POS and processor/bank
CREATE TABLE payment_reconciliation (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
batch_id INT NOT NULL REFERENCES payment_batches(id) ON DELETE CASCADE,
reconciliation_date DATE NOT NULL,
pos_transaction_count INT NOT NULL,
processor_transaction_count INT NOT NULL,
transaction_count_variance INT NOT NULL,
pos_gross_amount DECIMAL(12,2) NOT NULL,
processor_gross_amount DECIMAL(12,2) NOT NULL,
gross_variance DECIMAL(12,2) NOT NULL,
pos_net_amount DECIMAL(12,2) NOT NULL,
bank_deposit_amount DECIMAL(12,2),
deposit_variance DECIMAL(12,2),
fee_variance DECIMAL(10,2),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
variance_reason TEXT,
resolved_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
resolved_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT payment_recon_status_check CHECK (status IN (
'pending', 'matched', 'variance', 'resolved'
))
);
CREATE INDEX idx_payment_recon_tenant ON payment_reconciliation(tenant_id);
CREATE INDEX idx_payment_recon_tenant_batch ON payment_reconciliation(tenant_id, batch_id);
CREATE INDEX idx_payment_recon_tenant_date ON payment_reconciliation(tenant_id, reconciliation_date DESC);
CREATE INDEX idx_payment_recon_tenant_status ON payment_reconciliation(tenant_id, status)
WHERE status IN ('pending', 'variance');
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE payment_reconciliation IS 'Payment reconciliation with variance tracking';
Domain 13: Tax
tax_jurisdictions
-- Compound tax jurisdictions (State/County/City per ADR mandate)
CREATE TABLE tax_jurisdictions (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(100) NOT NULL,
code VARCHAR(20) NOT NULL,
jurisdiction_level VARCHAR(20) NOT NULL,
parent_jurisdiction_id INT REFERENCES tax_jurisdictions(id) ON DELETE SET NULL,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT tax_jurisdictions_tenant_code_unique UNIQUE (tenant_id, code),
CONSTRAINT tax_jurisdictions_level_check CHECK (jurisdiction_level IN (
'state', 'county', 'city', 'district'
))
);
CREATE INDEX idx_tax_jurisdictions_tenant ON tax_jurisdictions(tenant_id);
CREATE INDEX idx_tax_jurisdictions_tenant_level ON tax_jurisdictions(tenant_id, jurisdiction_level);
CREATE INDEX idx_tax_jurisdictions_parent ON tax_jurisdictions(parent_jurisdiction_id)
WHERE parent_jurisdiction_id IS NOT NULL;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE tax_jurisdictions IS 'Compound tax jurisdictions: State > County > City hierarchy';
tax_rates
-- Tax rates per jurisdiction with effective date ranges
CREATE TABLE tax_rates (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
jurisdiction_id INT NOT NULL REFERENCES tax_jurisdictions(id) ON DELETE CASCADE,
name VARCHAR(100) NOT NULL,
rate DECIMAL(5,4) NOT NULL,
is_compound BOOLEAN DEFAULT FALSE,
applies_to VARCHAR(20) NOT NULL DEFAULT 'all',
effective_from DATE NOT NULL,
effective_to DATE,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT tax_rates_rate_check CHECK (rate >= 0 AND rate <= 1),
CONSTRAINT tax_rates_dates CHECK (effective_to IS NULL OR effective_to > effective_from),
CONSTRAINT tax_rates_applies_check CHECK (applies_to IN ('all', 'goods', 'services', 'clothing'))
);
-- Compound tax example: State 6.25% + County 1.00% + City 1.00% = 8.25%
-- Each level is a separate row linked to its jurisdiction
CREATE INDEX idx_tax_rates_tenant ON tax_rates(tenant_id);
CREATE INDEX idx_tax_rates_tenant_jurisdiction ON tax_rates(tenant_id, jurisdiction_id);
CREATE INDEX idx_tax_rates_effective ON tax_rates(tenant_id, effective_from, effective_to)
WHERE is_active = TRUE;
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE tax_rates IS 'Tax rates per jurisdiction with effective date ranges (rate as decimal: 0.0825 = 8.25%)';
location_tax_jurisdictions
-- Assigns tax jurisdictions to locations (which taxes apply where)
CREATE TABLE location_tax_jurisdictions (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
location_id INT NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
jurisdiction_id INT NOT NULL REFERENCES tax_jurisdictions(id) ON DELETE CASCADE,
effective_from DATE NOT NULL,
effective_to DATE,
CONSTRAINT location_tax_juris_unique UNIQUE (tenant_id, location_id, jurisdiction_id),
CONSTRAINT location_tax_juris_dates CHECK (effective_to IS NULL OR effective_to > effective_from)
);
CREATE INDEX idx_location_tax_juris_tenant ON location_tax_jurisdictions(tenant_id);
CREATE INDEX idx_location_tax_juris_location ON location_tax_jurisdictions(tenant_id, location_id);
-- RLS: tenant_id = current_setting('app.current_tenant')::uuid
COMMENT ON TABLE location_tax_jurisdictions IS 'Assigns compound tax jurisdictions to store locations';
Domain 13B: RFID Module (Optional)
rfid_config
-- Tenant RFID configuration (counting subsystem)
CREATE TABLE rfid_config (
id SERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
epc_company_prefix VARCHAR(24) NOT NULL,
epc_indicator CHAR(1) NOT NULL DEFAULT '0',
epc_filter CHAR(1) NOT NULL DEFAULT '3',
epc_partition INT NOT NULL DEFAULT 5,
min_rssi_threshold SMALLINT NOT NULL DEFAULT -70,
auto_save_interval_seconds INT NOT NULL DEFAULT 30,
chunk_upload_size INT NOT NULL DEFAULT 5000,
default_template_id UUID,
default_printer_id UUID,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT rfid_config_tenant_unique UNIQUE (tenant_id)
);
-- RLS: tenant_id = current_setting('app.tenant_id')::uuid
-- Serial numbers use PostgreSQL SEQUENCE per tenant:
-- CREATE SEQUENCE rfid_epc_serial_{tenant_short_id} START 1 INCREMENT 1 NO CYCLE;
-- Application calls nextval('rfid_epc_serial_{tenant_short_id}') during tag encoding.
COMMENT ON TABLE rfid_config IS 'Tenant RFID configuration for EPC encoding and scanning';
COMMENT ON COLUMN rfid_config.min_rssi_threshold IS 'Minimum RSSI (dBm) to accept tag reads; default -70';
rfid_printers
-- Registered RFID printers
CREATE TABLE rfid_printers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(100) NOT NULL,
location_id INT NOT NULL REFERENCES locations(id) ON DELETE CASCADE,
printer_type VARCHAR(50) NOT NULL,
connection_type VARCHAR(20) NOT NULL,
ip_address INET,
port INT DEFAULT 9100,
mac_address VARCHAR(17),
serial_number VARCHAR(100),
firmware_version VARCHAR(50),
dpi INT DEFAULT 203,
label_width_mm INT NOT NULL,
label_height_mm INT NOT NULL,
rfid_position VARCHAR(20) DEFAULT 'center',
status VARCHAR(20) NOT NULL DEFAULT 'offline',
last_seen_at TIMESTAMPTZ,
error_message TEXT,
is_default BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT rfid_printers_type_check CHECK (printer_type IN (
'zebra_zd621r', 'zebra_zd500r', 'sato_cl4nx', 'tsc_mx240p'
)),
CONSTRAINT rfid_printers_conn_check CHECK (connection_type IN ('network', 'usb', 'bluetooth'))
);
CREATE INDEX idx_rfid_printers_location ON rfid_printers(location_id);
CREATE INDEX idx_rfid_printers_status ON rfid_printers(status);
CREATE UNIQUE INDEX idx_rfid_printers_default ON rfid_printers(location_id) WHERE is_default = TRUE;
-- RLS: tenant_id = current_setting('app.tenant_id')::uuid
COMMENT ON TABLE rfid_printers IS 'RFID-enabled printers registered per location';
rfid_print_jobs
-- Print job queue
CREATE TABLE rfid_print_jobs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
job_number VARCHAR(20) NOT NULL,
printer_id UUID NOT NULL REFERENCES rfid_printers(id) ON DELETE RESTRICT,
template_id UUID NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'queued',
priority INT DEFAULT 5,
total_tags INT NOT NULL,
printed_tags INT DEFAULT 0,
failed_tags INT DEFAULT 0,
error_message TEXT,
job_data JSONB NOT NULL,
created_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
started_at TIMESTAMPTZ,
completed_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT rfid_print_jobs_number_unique UNIQUE (job_number),
CONSTRAINT rfid_print_jobs_status_check CHECK (status IN (
'queued', 'printing', 'completed', 'failed', 'cancelled'
)),
CONSTRAINT rfid_print_jobs_priority_check CHECK (priority BETWEEN 1 AND 10)
);
CREATE INDEX idx_rfid_print_jobs_status ON rfid_print_jobs(status, priority, created_at)
WHERE status IN ('queued', 'printing');
CREATE INDEX idx_rfid_print_jobs_printer ON rfid_print_jobs(printer_id, created_at DESC);
-- RLS: tenant_id = current_setting('app.tenant_id')::uuid
COMMENT ON TABLE rfid_print_jobs IS 'RFID tag print job queue with progress tracking';
rfid_tags
-- Individual RFID tags linked to variants (counting subsystem — no lifecycle fields)
CREATE TABLE rfid_tags (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
epc VARCHAR(24) NOT NULL,
variant_id INT NOT NULL REFERENCES variants(id) ON DELETE RESTRICT,
serial_number BIGINT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'active',
print_job_id UUID REFERENCES rfid_print_jobs(id) ON DELETE SET NULL,
printed_at TIMESTAMPTZ,
printed_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
current_location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
last_scanned_at TIMESTAMPTZ,
last_scanned_session_id UUID,
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
-- Scope: counting only — no sold_at, sold_order_id, transferred_at fields
-- Sales and transfers tracked by core inventory system via barcode, not RFID
CONSTRAINT rfid_tags_epc_unique UNIQUE (tenant_id, epc),
CONSTRAINT rfid_tags_epc_format CHECK (epc ~ '^[0-9A-F]{24}$'),
CONSTRAINT rfid_tags_status_check CHECK (status IN ('active', 'void', 'lost'))
);
CREATE INDEX idx_rfid_tags_variant ON rfid_tags(tenant_id, variant_id, status) WHERE status = 'active';
CREATE INDEX idx_rfid_tags_location ON rfid_tags(tenant_id, current_location_id, status) WHERE status = 'active';
CREATE INDEX idx_rfid_tags_serial ON rfid_tags(tenant_id, serial_number);
CREATE INDEX idx_rfid_tags_scan ON rfid_tags(last_scanned_at DESC) WHERE last_scanned_at IS NOT NULL;
-- RLS: tenant_id = current_setting('app.tenant_id')::uuid
COMMENT ON TABLE rfid_tags IS 'Individual RFID tags for inventory counting (EPC-level tracking)';
COMMENT ON COLUMN rfid_tags.epc IS 'SGTIN-96 Electronic Product Code (24 hex chars)';
rfid_scan_sessions
-- Inventory scan sessions (counting subsystem — no 'receiving' type)
CREATE TABLE rfid_scan_sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
session_number VARCHAR(20) NOT NULL,
location_id INT NOT NULL REFERENCES locations(id) ON DELETE RESTRICT,
session_type VARCHAR(30) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'in_progress',
device_id UUID REFERENCES devices(id) ON DELETE SET NULL,
started_by UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
completed_by UUID REFERENCES shared.users(id) ON DELETE SET NULL,
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
completed_at TIMESTAMPTZ,
total_reads INT DEFAULT 0,
unique_tags INT DEFAULT 0,
matched_tags INT DEFAULT 0,
unmatched_tags INT DEFAULT 0,
expected_count INT,
variance INT,
variance_value DECIMAL(12,2),
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT rfid_sessions_number_unique UNIQUE (tenant_id, session_number),
CONSTRAINT rfid_sessions_type_check CHECK (session_type IN (
'full_inventory', 'cycle_count', 'spot_check', 'find_item'
)),
-- NOTE: 'receiving' removed — RFID is counting only (see BRD Section 5.16)
CONSTRAINT rfid_sessions_status_check CHECK (status IN (
'in_progress', 'completed', 'cancelled', 'uploaded'
))
);
CREATE INDEX idx_rfid_sessions_location ON rfid_scan_sessions(tenant_id, location_id, started_at DESC);
CREATE INDEX idx_rfid_sessions_status ON rfid_scan_sessions(status) WHERE status = 'in_progress';
-- RLS: tenant_id = current_setting('app.tenant_id')::uuid
COMMENT ON TABLE rfid_scan_sessions IS 'RFID inventory count sessions with variance calculation';
rfid_scan_events
-- Aggregated tag reads during scan sessions (one row per unique EPC per session)
CREATE TABLE rfid_scan_events (
id BIGSERIAL PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
session_id UUID NOT NULL REFERENCES rfid_scan_sessions(id) ON DELETE CASCADE,
epc VARCHAR(24) NOT NULL,
rfid_tag_id UUID REFERENCES rfid_tags(id) ON DELETE SET NULL,
rssi SMALLINT,
read_count INT DEFAULT 1,
antenna SMALLINT,
first_seen_at TIMESTAMPTZ NOT NULL,
last_seen_at TIMESTAMPTZ NOT NULL,
-- Idempotency: same EPC can only appear once per session
-- On duplicate upload (retry), use UPSERT:
-- ON CONFLICT (session_id, epc) DO UPDATE SET
-- rssi = GREATEST(excluded.rssi, rfid_scan_events.rssi),
-- read_count = rfid_scan_events.read_count + excluded.read_count,
-- last_seen_at = GREATEST(excluded.last_seen_at, rfid_scan_events.last_seen_at)
CONSTRAINT rfid_events_idempotent UNIQUE (session_id, epc)
);
-- Indexes
CREATE INDEX idx_rfid_events_session ON rfid_scan_events(session_id);
CREATE INDEX idx_rfid_events_epc ON rfid_scan_events(epc);
CREATE INDEX idx_rfid_events_tag ON rfid_scan_events(rfid_tag_id) WHERE rfid_tag_id IS NOT NULL;
CREATE INDEX idx_rfid_events_unknown ON rfid_scan_events(session_id) WHERE rfid_tag_id IS NULL;
CREATE INDEX idx_rfid_events_time ON rfid_scan_events USING BRIN (first_seen_at);
-- RLS: tenant_id = current_setting('app.tenant_id')::uuid
COMMENT ON TABLE rfid_scan_events IS 'Aggregated RFID tag reads per session (pre-deduplicated by EPC)';
COMMENT ON COLUMN rfid_scan_events.rssi IS 'Best signal strength (-127 to 0 dBm)';
rfid_tag_templates
-- ZPL label templates for RFID tag printing
CREATE TABLE rfid_tag_templates (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(100) NOT NULL,
template_type VARCHAR(20) NOT NULL,
zpl_content TEXT NOT NULL,
variables JSONB NOT NULL DEFAULT '[]',
is_default BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT rfid_templates_type_check CHECK (template_type IN (
'hang_tag', 'price_tag', 'label'
))
);
CREATE INDEX idx_rfid_templates_tenant ON rfid_tag_templates(tenant_id);
CREATE UNIQUE INDEX idx_rfid_templates_default ON rfid_tag_templates(tenant_id, template_type)
WHERE is_default = TRUE;
-- RLS: tenant_id = current_setting('app.tenant_id')::uuid
COMMENT ON TABLE rfid_tag_templates IS 'ZPL label templates for RFID tag printing';
rfid_tag_mappings
-- EPC prefix to product variant mappings for offline decoding
CREATE TABLE rfid_tag_mappings (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
epc_prefix VARCHAR(20) NOT NULL,
variant_id INT NOT NULL REFERENCES variants(id) ON DELETE CASCADE,
sku VARCHAR(50) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT rfid_mappings_prefix_unique UNIQUE (tenant_id, epc_prefix)
);
CREATE INDEX idx_rfid_mappings_sku ON rfid_tag_mappings(tenant_id, sku);
CREATE INDEX idx_rfid_mappings_variant ON rfid_tag_mappings(variant_id);
-- RLS: tenant_id = current_setting('app.tenant_id')::uuid
COMMENT ON TABLE rfid_tag_mappings IS 'Maps EPC prefix ranges to product variants for offline decoding on Raptag mobile app';
session_operators
-- Multiple operators per RFID scan session with section assignments
CREATE TABLE session_operators (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
session_id UUID NOT NULL REFERENCES rfid_scan_sessions(id) ON DELETE CASCADE,
operator_id UUID NOT NULL REFERENCES shared.users(id) ON DELETE RESTRICT,
assigned_section TEXT,
device_id UUID REFERENCES devices(id) ON DELETE SET NULL,
joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
left_at TIMESTAMPTZ,
CONSTRAINT session_operators_unique UNIQUE (session_id, operator_id)
);
CREATE INDEX idx_session_operators_session ON session_operators(session_id);
CREATE INDEX idx_session_operators_user ON session_operators(operator_id);
-- RLS: tenant_id = current_setting('app.tenant_id')::uuid
COMMENT ON TABLE session_operators IS 'Multiple operators per RFID scan session with section assignments (max 10 per session)';
8.2 Table Count Summary
| Domain | Tables | Schema Location |
|---|---|---|
| 1-2. Catalog | 12 | tenant |
| 3. Inventory | 3 | tenant |
| 4. Sales | 3 | tenant |
| 5. Customer Loyalty | 4 | tenant |
| 6-7. Returns & Reporting | 3 | tenant |
| 8. Multi-tenant | 3 | shared |
| 9. Auth & Authorization | 4 | tenant |
| 10. Offline Sync | 4 | tenant |
| 10B. Event Infrastructure | 2 | tenant |
| 11. Cash Drawer | 6 | tenant |
| 12. Payment Processing | 4 | tenant |
| 13. Tax | 3 | tenant |
| 13. RFID (Optional) | 9 | tenant |
| TOTAL | 54 |
Next Chapter: Chapter 09: Indexes & Performance - Index strategy and query optimization.
Document Information
| Attribute | Value |
|---|---|
| Version | 5.0.0 |
| Created | 2025-12-29 |
| Updated | 2026-02-25 |
| Author | Claude Code |
| Status | Active |
| Part | III - Database |
| Chapter | 08 of 32 |
This chapter is part of the POS Blueprint Book. All content is self-contained.