Appendix I: Product Organization Model
Overview
This appendix documents the product organization system for the POS platform, explaining how products are categorized, grouped, and organized for both operational reporting and marketing merchandising. The design bridges two fundamentally different approaches:
- QuickBooks POS Departments - Hierarchical, single-assignment, operations-focused
- Shopify Collections - Flexible, multi-assignment, marketing-focused
The POS platform implements a hybrid model that supports both paradigms, enabling seamless sync with external systems while providing powerful organization capabilities.
Table of Contents
- Conceptual Foundation
- The Hybrid Model
- Department-Category-Subcategory (DCS) Hierarchy
- Collections and Tags
- Database Schema
- UI Placement Guide
- API Endpoints
- Sync Mapping
- Configuration Guide
- Best Practices
Conceptual Foundation
Why Two Systems Exist
Retail software evolved along two separate paths, each optimizing for different use cases:
┌─────────────────────────────────────────────────────────────────────────────┐
│ EVOLUTION OF PRODUCT ORGANIZATION │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ BRICK-AND-MORTAR PATH E-COMMERCE PATH │
│ ───────────────────── ─────────────── │
│ │
│ 1980s: Cash registers 1990s: Online catalogs │
│ Need: Track what sold Need: Help customers browse │
│ ↓ ↓ │
│ 1990s: POS systems 2000s: Shopping carts │
│ Need: Inventory counts Need: Search & filter │
│ ↓ ↓ │
│ 2000s: Multi-store retail 2010s: Omnichannel │
│ Need: Reporting by category Need: Promotions & SEO │
│ ↓ ↓ │
│ ┌─────────────────────┐ ┌─────────────────────┐ │
│ │ DEPARTMENTS │ │ COLLECTIONS │ │
│ │ (Single-assign) │ │ (Multi-assign) │ │
│ │ (Hierarchical) │ │ (Flat/Flexible) │ │
│ │ (Operations) │ │ (Marketing) │ │
│ └─────────────────────┘ └─────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
QuickBooks POS Departments
Purpose: Operational control and financial reporting
Characteristics:
- Hierarchical structure: Department → Category → Subcategory
- Each item belongs to exactly one department
- Rarely changed (designed for consistent year-over-year reporting)
- Used for: Sales reports, inventory counts, tax assignment, purchase orders
Example Structure:
Men's (Department)
├── Dress (Category)
│ ├── Leather (Subcategory)
│ └── Formal (Subcategory)
├── Casual (Category)
│ ├── T-Shirts (Subcategory)
│ └── Polos (Subcategory)
└── Athletic (Category)
├── Running (Subcategory)
└── Training (Subcategory)
QuickBooks Reports Using Departments:
- Sales by Department (daily, weekly, monthly)
- Inventory Valuation by Department
- Best/Worst Sellers by Department
- Department Comparison Across Stores
Shopify Collections
Purpose: Customer-facing merchandising and marketing
Characteristics:
- Flat structure (no parent-child hierarchy)
- Products can belong to multiple collections
- Frequently changed (seasonal, promotional)
- Used for: Storefront navigation, promotional campaigns, SEO
Two Types:
| Type | How It Works | Best For |
|---|---|---|
| Manual Collections | Merchant hand-picks products | Curated promotions, gift guides |
| Smart Collections | Auto-populated via rules | Sale items, new arrivals, low stock |
Smart Collection Rule Examples:
Collection: "Sale Items"
Rule: Compare-at Price is not empty
Collection: "Nike Products"
Rule: Vendor equals "Nike"
Collection: "Under $50"
Rule: Price is less than 50
Collection: "Summer T-Shirts"
Rules (ALL must match):
- Product Type equals "T-Shirt"
- Tag equals "summer"
A Single Product Might Belong To:
- “Men’s Clothing” (manual)
- “T-Shirts” (smart: type = t-shirt)
- “Nike” (smart: vendor = Nike)
- “Sale” (smart: compare_at_price > 0)
- “Summer 2025” (manual)
- “New Arrivals” (smart: created_at > 30 days ago)
The Hybrid Model
The POS platform implements both systems to serve different needs:
┌─────────────────────────────────────────────────────────────────────────────┐
│ HYBRID ORGANIZATION MODEL │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ OPERATIONAL CLASSIFICATION │ │
│ │ (Single-Assignment, Required) │ │
│ │ ─────────────────────────────────────────── │ │
│ │ │ │
│ │ Purpose: Reports, inventory counts, tax rules, QB POS sync │ │
│ │ │ │
│ │ Structure: │ │
│ │ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ │
│ │ │ DEPARTMENT │ → │ CATEGORY │ → │ SUBCATEGORY │ │ │
│ │ │ (Level 1) │ │ (Level 2) │ │ (Level 3) │ │ │
│ │ │ Required │ │ Required │ │ Optional │ │ │
│ │ └──────────────┘ └──────────────┘ └──────────────┘ │ │
│ │ │ │
│ │ Example: Men's → Casual → T-Shirts │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ MARKETING CLASSIFICATION │ │
│ │ (Multi-Assignment, Optional) │ │
│ │ ─────────────────────────────────────────── │ │
│ │ │ │
│ │ Purpose: Storefront navigation, promotions, Shopify sync │ │
│ │ │ │
│ │ Components: │ │
│ │ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ │
│ │ │ PRODUCT TYPE │ │ TAGS │ │ COLLECTIONS │ │ │
│ │ │ (1 value) │ │ (multiple) │ │ (multiple) │ │ │
│ │ │ Shopify PT │ │ searchable │ │ groupings │ │ │
│ │ └──────────────┘ └──────────────┘ └──────────────┘ │ │
│ │ │ │
│ │ Example: Type="T-Shirt", Tags=["summer","bestseller"], │ │
│ │ Collections=["Sale", "New Arrivals", "Nike"] │ │
│ │ │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
When to Use Each
| Use Case | Use Operational (DCS) | Use Marketing (Collections/Tags) |
|---|---|---|
| Sales reports by category | ✅ | |
| Inventory counts | ✅ | |
| Tax rules by category | ✅ | |
| QB POS sync | ✅ | |
| Storefront navigation | ✅ | |
| Promotional campaigns | ✅ | |
| “Sale” or “New Arrivals” | ✅ | |
| Shopify sync | Partially | ✅ |
| Search filtering | ✅ | ✅ |
| POS browse screen | ✅ (primary tabs) | ✅ (special tabs) |
DCS Hierarchy
Structure Definition
The Department-Category-Subcategory (DCS) hierarchy is a tree structure where:
Level 1: DEPARTMENT (Required)
│ Most general classification
│ Examples: Men's, Women's, Kids, Accessories, Home
│
├── Level 2: CATEGORY (Required)
│ Mid-level classification within department
│ Examples: Dress, Casual, Athletic, Outerwear
│
└── └── Level 3: SUBCATEGORY (Optional)
Most specific classification
Examples: T-Shirts, Polos, Tank Tops, Henleys
Hierarchy Rules
| Rule | Description | Rationale |
|---|---|---|
| Single Assignment | Product belongs to exactly one path | Prevents double-counting in reports |
| Cascading Selection | Category dropdown filtered by department | Ensures valid combinations |
| Department Required | Every product must have a department | Foundation for all reports |
| Category Required | Every product must have a category | Meaningful grouping level |
| Subcategory Optional | Provides additional detail when needed | Flexibility without complexity |
| Unique Department Names | Departments must be unique across tenant | Prevents confusion |
| Category Names Per Department | Same category name can exist in different departments | “Dress” in Men’s vs Women’s |
Example Complete Hierarchy
┌─────────────────────────────────────────────────────────────────────────────┐
│ EXAMPLE: CLOTHING RETAILER │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Men's Women's Kids │
│ ├── Dress ├── Dress ├── Boys │
│ │ ├── Suits │ ├── Dresses │ ├── Tops │
│ │ ├── Dress Shirts │ ├── Blouses │ ├── Bottoms │
│ │ ├── Dress Pants │ └── Skirts │ └── Sets │
│ │ └── Ties ├── Casual ├── Girls │
│ ├── Casual │ ├── T-Shirts │ ├── Tops │
│ │ ├── T-Shirts │ ├── Jeans │ ├── Bottoms │
│ │ ├── Polos │ └── Shorts │ └── Dresses │
│ │ ├── Jeans ├── Athletic └── Infant │
│ │ └── Shorts │ ├── Activewear ├── Onesies │
│ ├── Athletic │ ├── Sports Bras └── Sets │
│ │ ├── Performance Tops │ └── Leggings │
│ │ ├── Athletic Shorts └── Outerwear Accessories │
│ │ └── Track Pants ├── Jackets ├── Hats │
│ └── Outerwear └── Coats ├── Bags │
│ ├── Jackets ├── Belts │
│ └── Coats └── Jewelry │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Collections and Tags
Tags
Definition: Free-form labels attached to products for searching and filtering.
Characteristics:
- Multiple tags per product
- No hierarchy
- Used for search autocomplete
- Synced with Shopify tags
Common Tag Patterns:
| Pattern | Examples | Purpose |
|---|---|---|
| Season | spring, summer, fall, winter | Seasonal filtering |
| Style | casual, formal, sporty, vintage | Style preferences |
| Material | cotton, leather, denim, silk | Material search |
| Feature | bestseller, staff-pick, new | Highlights |
| Promotion | clearance, bogo, flash-sale | Promotional grouping |
| Attribute | organic, sustainable, made-in-usa | Attribute filtering |
Tag Best Practices:
- Use lowercase, hyphenated format:
father-day-2025 - Be consistent: decide on
t-shirtvstshirtvst_shirt - Don’t duplicate category info: if in “T-Shirts” subcategory, don’t tag “tshirt”
- Limit to 10-15 tags per product maximum
Collections
Definition: Named groupings of products for merchandising purposes.
Types:
| Type | How Populated | When to Use |
|---|---|---|
| Manual | Admin hand-picks products | Curated selections, gift guides, limited-time bundles |
| Smart | Auto-populated by rules | “Sale Items”, “New Arrivals”, “Low Stock” |
Smart Collection Rules
Smart collections use conditions to automatically include/exclude products:
Available Conditions:
| Field | Operators | Example |
|---|---|---|
| Product Title | equals, contains, starts with, ends with | Title contains “Premium” |
| Product Type | equals, not equals | Type equals “T-Shirt” |
| Vendor | equals, not equals | Vendor equals “Nike” |
| Tag | equals, contains | Tag equals “summer” |
| Price | equals, greater than, less than | Price less than 50 |
| Compare-at Price | is empty, is not empty, greater than | Compare-at is not empty |
| Inventory | equals, greater than, less than | Inventory less than 10 |
| Created Date | greater than | Created after 2025-01-01 |
Condition Logic:
- ALL (AND): Product must match every condition
- ANY (OR): Product must match at least one condition
Example Smart Collections:
Collection: "Sale Items"
Logic: ANY
Conditions:
- Compare-at Price is not empty
Collection: "New Arrivals"
Logic: ALL
Conditions:
- Created Date greater than [30 days ago]
Collection: "Nike Under $50"
Logic: ALL
Conditions:
- Vendor equals "Nike"
- Price less than 50
Collection: "Low Stock Alert"
Logic: ALL
Conditions:
- Inventory less than 5
- Tag not equals "discontinued"
Database Schema
Entity Relationship Diagram
┌─────────────────────────────────────────────────────────────────────────────┐
│ PRODUCT ORGANIZATION ERD │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────────┐ ┌──────────────────┐ │
│ │ product_categories│ │ products │ │
│ │ (DCS Hierarchy) │ │ │ │
│ ├──────────────────┤ ┌───►├──────────────────┤ │
│ │ id PK │ │ │ id PK │ │
│ │ tenant_id FK │ │ │ tenant_id FK │ │
│ │ parent_id FK ─┼────┘ │ department_id FK │◄───┐ │
│ │ name │ │ category_id FK │◄───┼── References │
│ │ level │ │ subcategory_id FK│◄───┘ product_categories │
│ │ sort_order │ │ product_type │ │
│ │ is_active │ │ vendor │ │
│ └──────────────────┘ │ tags (array) │ │
│ │ │ ... │ │
│ │ parent_id └────────┬─────────┘ │
│ │ self-reference │ │
│ └──────────────────────────────┤ │
│ │ │
│ ┌────────────┼────────────┐ │
│ │ │ │ │
│ ▼ │ ▼ │
│ ┌──────────────────┐ │ ┌──────────────────┐ │
│ │ collections │ │ │ product_tags │ │
│ ├──────────────────┤ │ │ (if normalized) │ │
│ │ id PK │ │ ├──────────────────┤ │
│ │ tenant_id FK │ │ │ product_id FK │ │
│ │ name │ │ │ tag FK │ │
│ │ handle │ │ └──────────────────┘ │
│ │ type (manual/ │ │ │
│ │ smart) │ │ ┌──────────────────┐ │
│ │ rules_json │ │ │ tags │ │
│ │ shopify_id │ │ │ (if normalized) │ │
│ │ sort_order │ │ ├──────────────────┤ │
│ │ is_active │ │ │ id PK │ │
│ └────────┬─────────┘ │ │ tenant_id FK │ │
│ │ │ │ name │ │
│ │ │ │ usage_count │ │
│ ▼ │ └──────────────────┘ │
│ ┌──────────────────┐ │ │
│ │product_collections│ │ │
│ │ (Join Table) │◄────────────────┘ │
│ ├──────────────────┤ │
│ │ product_id FK │ │
│ │ collection_id FK │ │
│ │ position │ │
│ └──────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Table Definitions
product_categories (DCS Hierarchy)
CREATE TABLE product_categories (
-- Identity
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
-- Hierarchy
parent_id UUID REFERENCES product_categories(id) ON DELETE CASCADE,
level SMALLINT NOT NULL CHECK (level BETWEEN 1 AND 3),
-- 1 = Department, 2 = Category, 3 = Subcategory
-- Data
name VARCHAR(100) NOT NULL,
description TEXT,
code VARCHAR(20), -- Short code for reports: "MEN", "WMN"
-- Display
sort_order INTEGER DEFAULT 0,
icon VARCHAR(50), -- Icon identifier for UI
color VARCHAR(7), -- Hex color for UI: "#3B82F6"
-- Status
is_active BOOLEAN DEFAULT true,
-- Sync
quickbooks_id VARCHAR(50), -- QB POS Department/Category ID
-- Audit
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
-- Constraints
CONSTRAINT unique_category_name_per_parent
UNIQUE (tenant_id, parent_id, name),
CONSTRAINT department_has_no_parent
CHECK (level != 1 OR parent_id IS NULL),
CONSTRAINT category_has_department_parent
CHECK (level != 2 OR parent_id IS NOT NULL),
CONSTRAINT subcategory_has_category_parent
CHECK (level != 3 OR parent_id IS NOT NULL)
);
-- Indexes
CREATE INDEX idx_product_categories_tenant ON product_categories(tenant_id);
CREATE INDEX idx_product_categories_parent ON product_categories(parent_id);
CREATE INDEX idx_product_categories_level ON product_categories(tenant_id, level);
products (Extended with DCS)
-- Add columns to existing products table
ALTER TABLE products ADD COLUMN IF NOT EXISTS
department_id UUID REFERENCES product_categories(id);
ALTER TABLE products ADD COLUMN IF NOT EXISTS
category_id UUID REFERENCES product_categories(id);
ALTER TABLE products ADD COLUMN IF NOT EXISTS
subcategory_id UUID REFERENCES product_categories(id);
ALTER TABLE products ADD COLUMN IF NOT EXISTS
product_type VARCHAR(100); -- Shopify Product Type
ALTER TABLE products ADD COLUMN IF NOT EXISTS
vendor VARCHAR(255); -- Brand/Manufacturer
ALTER TABLE products ADD COLUMN IF NOT EXISTS
tags TEXT[]; -- Array of tag strings
-- Indexes for filtering
CREATE INDEX idx_products_department ON products(tenant_id, department_id);
CREATE INDEX idx_products_category ON products(tenant_id, category_id);
CREATE INDEX idx_products_vendor ON products(tenant_id, vendor);
CREATE INDEX idx_products_tags ON products USING GIN(tags);
collections
CREATE TABLE collections (
-- Identity
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
-- Data
name VARCHAR(255) NOT NULL,
handle VARCHAR(255) NOT NULL, -- URL-friendly slug
description TEXT,
-- Type
type VARCHAR(10) NOT NULL CHECK (type IN ('manual', 'smart')),
-- Smart Collection Rules (JSON)
rules_json JSONB,
/*
Example rules_json:
{
"logic": "all", -- or "any"
"conditions": [
{"field": "vendor", "operator": "equals", "value": "Nike"},
{"field": "price", "operator": "less_than", "value": 50}
]
}
*/
-- Display
sort_order INTEGER DEFAULT 0,
image_url VARCHAR(500),
-- Status
is_active BOOLEAN DEFAULT true,
is_visible BOOLEAN DEFAULT true, -- Show in storefront navigation
-- Sync
shopify_id BIGINT, -- Shopify collection ID
shopify_handle VARCHAR(255),
-- Audit
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
-- Constraints
CONSTRAINT unique_collection_handle UNIQUE (tenant_id, handle)
);
-- Indexes
CREATE INDEX idx_collections_tenant ON collections(tenant_id);
CREATE INDEX idx_collections_shopify ON collections(shopify_id) WHERE shopify_id IS NOT NULL;
CREATE INDEX idx_collections_type ON collections(tenant_id, type);
product_collections (Join Table)
CREATE TABLE product_collections (
product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
position INTEGER DEFAULT 0, -- Sort order within collection
added_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
PRIMARY KEY (product_id, collection_id)
);
-- Indexes
CREATE INDEX idx_product_collections_collection ON product_collections(collection_id);
CREATE INDEX idx_product_collections_product ON product_collections(product_id);
tags (Optional - Normalized)
-- Option A: Denormalized (tags as TEXT[] on products) - simpler, faster writes
-- Option B: Normalized (separate table) - better for tag management UI
CREATE TABLE tags (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES shared.tenants(id),
name VARCHAR(100) NOT NULL,
usage_count INTEGER DEFAULT 0, -- Number of products using this tag
CONSTRAINT unique_tag_name UNIQUE (tenant_id, name)
);
CREATE TABLE product_tags (
product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (product_id, tag_id)
);
Sample Data
-- Departments
INSERT INTO product_categories (id, tenant_id, parent_id, level, name, code, sort_order) VALUES
('dep-mens', 'tenant-1', NULL, 1, 'Men''s', 'MEN', 1),
('dep-wmns', 'tenant-1', NULL, 1, 'Women''s', 'WMN', 2),
('dep-kids', 'tenant-1', NULL, 1, 'Kids', 'KID', 3),
('dep-accs', 'tenant-1', NULL, 1, 'Accessories', 'ACC', 4);
-- Categories under Men's
INSERT INTO product_categories (id, tenant_id, parent_id, level, name, sort_order) VALUES
('cat-men-dress', 'tenant-1', 'dep-mens', 2, 'Dress', 1),
('cat-men-casual', 'tenant-1', 'dep-mens', 2, 'Casual', 2),
('cat-men-athletic', 'tenant-1', 'dep-mens', 2, 'Athletic', 3);
-- Subcategories under Men's Casual
INSERT INTO product_categories (id, tenant_id, parent_id, level, name, sort_order) VALUES
('sub-men-cas-tshirts', 'tenant-1', 'cat-men-casual', 3, 'T-Shirts', 1),
('sub-men-cas-polos', 'tenant-1', 'cat-men-casual', 3, 'Polos', 2),
('sub-men-cas-jeans', 'tenant-1', 'cat-men-casual', 3, 'Jeans', 3);
-- Collections
INSERT INTO collections (id, tenant_id, name, handle, type, rules_json) VALUES
('col-sale', 'tenant-1', 'Sale', 'sale', 'smart',
'{"logic": "any", "conditions": [{"field": "compare_at_price", "operator": "is_not_empty"}]}'),
('col-new', 'tenant-1', 'New Arrivals', 'new-arrivals', 'smart',
'{"logic": "all", "conditions": [{"field": "created_at", "operator": "greater_than", "value": "30_days_ago"}]}'),
('col-summer', 'tenant-1', 'Summer 2025', 'summer-2025', 'manual', NULL);
UI Placement Guide
1. Admin Portal: Category Configuration
Location: Settings → Product Categories
Purpose: Define and manage the DCS hierarchy for the tenant.
┌─────────────────────────────────────────────────────────────────────────────┐
│ ⚙️ SETTINGS │
├──────┬──────────┬──────┬──────────┬────────────────────┬──────────┬────────┤
│ General │ Locations │ Taxes │ Payment │ Product Categories │ Integrations │ ... │
│ │ │ │ │ ══════════════════ │ │ │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ DEPARTMENT / CATEGORY / SUBCATEGORY HIERARCHY │
│ ══════════════════════════════════════════════ │
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ 🏢 Men's [Edit] [⋮ More] │ │
│ │ │ Code: MEN • Products: 1,245 • Active ✓ │ │
│ │ │ │ │
│ │ ├── 👔 Dress [Edit] [+ Subcategory] │ │
│ │ │ │ Products: 312 │ │
│ │ │ ├── Suits (45) │ │
│ │ │ ├── Dress Shirts (156) │ │
│ │ │ ├── Dress Pants (89) │ │
│ │ │ └── Ties (22) │ │
│ │ │ │ │
│ │ ├── 👕 Casual [Edit] [+ Subcategory] │ │
│ │ │ │ Products: 578 │ │
│ │ │ ├── T-Shirts (234) │ │
│ │ │ ├── Polos (145) │ │
│ │ │ ├── Jeans (123) │ │
│ │ │ └── Shorts (76) │ │
│ │ │ │ │
│ │ ├── 🏃 Athletic [Edit] [+ Subcategory] │ │
│ │ │ Products: 355 │ │
│ │ │ │ │
│ │ └── [+ Add Category] │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ 🏢 Women's [Edit] [⋮ More] │ │
│ │ Code: WMN • Products: 1,567 • Active ✓ │ │
│ │ [Expand ▼] │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ [+ Add Department] │
│ │
│ ─────────────────────────────────────────────────────────────────────── │
│ │
│ SYNC SETTINGS │
│ │
│ QuickBooks POS: │
│ [☑] Sync Department/Category/Subcategory with QB POS │
│ [☑] Auto-create missing categories on sync │
│ │
│ Shopify: │
│ [☑] Department → Shopify Product Type │
│ [☑] Category → Shopify Tag (prefix: cat:) │
│ [☐] Subcategory → Shopify Tag (prefix: subcat:) │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Edit Category Modal:
┌─────────────────────────────────────────────────────────────┐
│ EDIT CATEGORY [X] │
├─────────────────────────────────────────────────────────────┤
│ │
│ Parent: Men's (Department) │
│ │
│ Name: [Casual_________________________] │
│ │
│ Code: [CAS___] (optional, for reports) │
│ │
│ Description: [Everyday casual wear____________] │
│ │
│ Icon: [👕 ▼] (select icon) │
│ │
│ Color: [████] #3B82F6 (optional, for charts) │
│ │
│ [☑] Active │
│ │
│ QuickBooks ID: QBCAT-12345 (auto-linked) │
│ │
│ [Cancel] [Save Changes] │
└─────────────────────────────────────────────────────────────┘
2. Admin Portal: Collection Management
Location: Marketing → Collections
┌─────────────────────────────────────────────────────────────────────────────┐
│ 📦 MARKETING → COLLECTIONS [+ New Collection] │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ [All] [Smart] [Manual] [Synced from Shopify] 🔍 Search collections... │
│ ═══ │
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ Collection │ Type │ Products │ Visible │ Shopify │ Actions │ │
│ ├─────────────────────────────────────────────────────────────────────┤ │
│ │ 🏷️ Sale │ Smart │ 156 │ ✓ │ Synced │ [⋮] │ │
│ │ ⭐ New Arrivals │ Smart │ 89 │ ✓ │ Synced │ [⋮] │ │
│ │ ☀️ Summer 2025 │ Manual │ 234 │ ✓ │ Synced │ [⋮] │ │
│ │ 🏆 Best Sellers │ Smart │ 50 │ ✓ │ Synced │ [⋮] │ │
│ │ 🎁 Gift Ideas │ Manual │ 78 │ ✓ │ Synced │ [⋮] │ │
│ │ ⚠️ Low Stock │ Smart │ 23 │ ✗ │ Local │ [⋮] │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ Total: 6 collections • Last Shopify sync: 5 minutes ago [↻ Sync Now] │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
Create/Edit Smart Collection:
┌─────────────────────────────────────────────────────────────────────────────┐
│ CREATE SMART COLLECTION [X] │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ BASIC INFO │
│ ────────── │
│ Name: [Nike Under $50___________________] │
│ Handle: [nike-under-50____________________] (URL slug) │
│ Description: [Shop Nike products under $50_____] │
│ │
│ ═══════════════════════════════════════════════════════════════════════ │
│ │
│ CONDITIONS │
│ ────────── │
│ Products must match: (•) ALL conditions ( ) ANY condition │
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ [Vendor______▼] [equals________▼] [Nike_______________] [🗑️] │ │
│ │ [Price_______▼] [less than____▼] [50__________________] [🗑️] │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ [+ Add condition] │
│ │
│ ═══════════════════════════════════════════════════════════════════════ │
│ │
│ PREVIEW Matching: 34 products │
│ ────────── │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │ [IMG] │ │ [IMG] │ │ [IMG] │ │ [IMG] │ ... │
│ │ Nike Tee│ │Nike Polo│ │ Air Max │ │ Shorts │ │
│ │ $39.99 │ │ $45.00 │ │ $49.99 │ │ $29.99 │ │
│ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │
│ │
│ ═══════════════════════════════════════════════════════════════════════ │
│ │
│ SETTINGS │
│ ──────── │
│ [☑] Visible on storefront │
│ [☑] Sync to Shopify │
│ │
│ [Cancel] [Create Collection] │
└─────────────────────────────────────────────────────────────────────────────┘
3. Product Creation Flow: Step 1 (Basic Info + Classification)
Location: Catalog → Products → Create
┌─────────────────────────────────────────────────────────────────────────────┐
│ CREATE PRODUCT Step 1/6 │
│ ───────────── │
│ [1 Basic Info] → [2 Media] → [3 Options] → [4 Variants] → [5 Inv] → [6 ✓] │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ BASIC INFORMATION │
│ ═════════════════ │
│ │
│ Product Name * [Galaxy Plain V-Neck T-Shirt__________________] │
│ │
│ Description [Premium cotton blend casual t-shirt with a │
│ comfortable fit for everyday wear.___________] │
│ │
│ Vendor / Brand [Nike_______________________________________] ▼ │
│ └─ Autocomplete from existing vendors │
│ │
│ ═══════════════════════════════════════════════════════════════════════ │
│ │
│ OPERATIONAL CLASSIFICATION (Required) │
│ ═══════════════════════════════════════ │
│ Used for: Inventory reports, sales analysis, QuickBooks POS sync │
│ │
│ Department * [Men's____________________________________] ▼ │
│ Options: Men's, Women's, Kids, Accessories │
│ │
│ Category * [Casual___________________________________] ▼ │
│ Options: (filtered by department) Dress, Casual, │
│ Athletic, Outerwear │
│ │
│ Subcategory [T-Shirts_________________________________] ▼ │
│ Options: (filtered by category) T-Shirts, Polos, │
│ Jeans, Shorts │
│ │
│ ═══════════════════════════════════════════════════════════════════════ │
│ │
│ MARKETING CLASSIFICATION (Optional) │
│ ═══════════════════════════════════ │
│ Used for: Shopify sync, storefront navigation, promotions │
│ │
│ Product Type [T-Shirt__________________________________] │
│ └─ Auto-filled from Subcategory, can override │
│ │
│ Tags [summer] [casual] [new-arrival] [+ Add Tag] │
│ └─ Autocomplete from existing tags │
│ │
│ Collections [☑ New Arrivals] [☐ Sale] [☑ Summer 2025] │
│ [☐ Best Sellers] [Manage Collections...] │
│ └─ Manual collections only; smart auto-apply │
│ │
│ [Cancel] [Next: Media →] │
└─────────────────────────────────────────────────────────────────────────────┘
Cascading Dropdown Behavior:
User selects Department: "Men's"
↓
Category dropdown enables, shows: ["Dress", "Casual", "Athletic", "Outerwear"]
↓
User selects Category: "Casual"
↓
Subcategory dropdown enables, shows: ["T-Shirts", "Polos", "Jeans", "Shorts"]
↓
User selects Subcategory: "T-Shirts"
↓
Product Type auto-fills: "T-Shirt" (can be overridden)
4. POS Client: Browse Products
Location: POS → Product Grid
┌─────────────────────────────────────────────────────────────────────────────┐
│ POS - BROWSE PRODUCTS [Register 1] │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ 🔍 [Search by name, SKU, or barcode_________________________] [Scan 📷] │
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ PRIMARY TABS (Departments) │ │
│ │ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ │ │
│ │ │ All │ │Men's │ │Women's│ │ Kids │ │ Acc │ │ Sale │ │ New │ │ │
│ │ └──────┘ └══════┘ └──────┘ └──────┘ └──────┘ └──────┘ └──────┘ │ │
│ │ ════ ↑ ↑ │ │
│ │ (selected) (Collection tabs) │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────────────────────┐ │
│ │ SECONDARY TABS (Categories - filtered by selected Department) │ │
│ │ ┌──────┐ ┌──────┐ ┌────────┐ ┌──────────┐ │ │
│ │ │ All │ │Dress │ │ Casual │ │ Athletic │ │ │
│ │ └──────┘ └──────┘ └════════┘ └──────────┘ │ │
│ │ ════ │ │
│ │ (selected) │ │
│ └─────────────────────────────────────────────────────────────────────┘ │
│ │
│ SHOWING: Men's → Casual 234 products [Clear ✕] │
│ ─────────────────────────────────────────────────────────────────────── │
│ │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │ [IMG] │ │ [IMG] │ │ [IMG] │ │ [IMG] │ │ [IMG] │ │
│ │ V-Neck │ │ Crew │ │ Polo │ │ Henley │ │ Tank │ │
│ │ Tee │ │ Tee │ │ Shirt │ │ Shirt │ │ Top │ │
│ │ $24.99 │ │ $19.99 │ │ $34.99 │ │ $29.99 │ │ $14.99 │ │
│ │ ● In Stock │ ● In Stock │ ⚠️ Low │ ● In Stock │ ● In Stock │ │
│ └─────────┘ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │
│ │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │ [IMG] │ │ [IMG] │ │ [IMG] │ │ [IMG] │ │ [IMG] │ │
│ │ Jeans │ │ Shorts │ │ Khakis │ │Joggers │ │Chinos │ │
│ │ $49.99 │ │ $29.99 │ │ $44.99 │ │ $39.99 │ │ $54.99 │ │
│ └─────────┘ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │
│ │
│ [Load More...] │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
POS Browse Behavior:
| Tab Type | Source | Behavior |
|---|---|---|
| “All” | System | Shows all products |
| Department tabs (Men’s, Women’s) | DCS | Filters by department_id |
| Category sub-tabs (Dress, Casual) | DCS | Filters by category_id (within selected department) |
| Collection tabs (Sale, New) | Collections | Filters by collection membership |
API Endpoints
Category Management
# List all categories (hierarchical)
GET /api/v1/catalog/categories
Response:
- id: "dep-mens"
name: "Men's"
level: 1
children:
- id: "cat-men-casual"
name: "Casual"
level: 2
children:
- id: "sub-men-cas-tshirts"
name: "T-Shirts"
level: 3
# Get departments only
GET /api/v1/catalog/categories?level=1
# Get categories for a department
GET /api/v1/catalog/categories?parent_id={department_id}
# Create category
POST /api/v1/catalog/categories
Body:
name: "Athletic"
parent_id: "dep-mens" # null for department
level: 2
code: "ATH"
is_active: true
# Update category
PUT /api/v1/catalog/categories/{id}
Body:
name: "Athletics"
code: "ATHL"
# Delete category (fails if products assigned)
DELETE /api/v1/catalog/categories/{id}
# Reorder categories
PATCH /api/v1/catalog/categories/reorder
Body:
- id: "cat-1", sort_order: 1
- id: "cat-2", sort_order: 2
Collection Management
# List collections
GET /api/v1/marketing/collections
GET /api/v1/marketing/collections?type=smart
GET /api/v1/marketing/collections?visible=true
# Get collection with products
GET /api/v1/marketing/collections/{id}
GET /api/v1/marketing/collections/{id}/products
# Create manual collection
POST /api/v1/marketing/collections
Body:
name: "Summer 2025"
handle: "summer-2025"
type: "manual"
is_visible: true
product_ids: ["prod-1", "prod-2", "prod-3"]
# Create smart collection
POST /api/v1/marketing/collections
Body:
name: "Nike Under $50"
handle: "nike-under-50"
type: "smart"
is_visible: true
rules:
logic: "all"
conditions:
- field: "vendor"
operator: "equals"
value: "Nike"
- field: "price"
operator: "less_than"
value: 50
# Add products to manual collection
POST /api/v1/marketing/collections/{id}/products
Body:
product_ids: ["prod-4", "prod-5"]
# Remove products from manual collection
DELETE /api/v1/marketing/collections/{id}/products
Body:
product_ids: ["prod-4"]
# Evaluate smart collection (preview)
POST /api/v1/marketing/collections/evaluate
Body:
rules:
logic: "all"
conditions: [...]
Response:
matching_count: 34
preview_products: [...]
Product Classification
# Create product with classification
POST /api/v1/catalog/products
Body:
name: "Galaxy V-Neck T-Shirt"
vendor: "Nike"
# Operational
department_id: "dep-mens"
category_id: "cat-men-casual"
subcategory_id: "sub-men-cas-tshirts"
# Marketing
product_type: "T-Shirt"
tags: ["summer", "casual", "new-arrival"]
collection_ids: ["col-summer"] # Manual collections only
# Update product classification
PATCH /api/v1/catalog/products/{id}/classification
Body:
department_id: "dep-mens"
category_id: "cat-men-athletic"
subcategory_id: null
tags: ["athletic", "performance"]
# Bulk update classification
PATCH /api/v1/catalog/products/bulk/classification
Body:
product_ids: ["prod-1", "prod-2", "prod-3"]
department_id: "dep-mens"
add_tags: ["clearance"]
remove_tags: ["new-arrival"]
Sync Mapping
QuickBooks POS ↔ POS Platform
| QB POS Field | POS Platform Field | Sync Direction | Notes |
|---|---|---|---|
| Department | department_id (via lookup) | Bi-directional | Map by name or code |
| Category | category_id (via lookup) | Bi-directional | Within department context |
| Subcategory | subcategory_id (via lookup) | Bi-directional | Within category context |
| - | tags | Not synced | Tags are marketing-only |
| - | collections | Not synced | Collections are marketing-only |
Sync Logic:
QB POS → POS Platform:
1. Receive item from QB with Department="Men's", Category="Casual", Subcategory="T-Shirts"
2. Look up department by name: SELECT id FROM product_categories WHERE name='Men''s' AND level=1
3. Look up category by name under department: SELECT id FROM product_categories WHERE name='Casual' AND parent_id=?
4. Look up subcategory by name under category: SELECT id FROM product_categories WHERE name='T-Shirts' AND parent_id=?
5. If not found, optionally auto-create with is_active=false (pending review)
POS Platform → QB POS:
1. Get product with department_id, category_id, subcategory_id
2. Look up names: SELECT name FROM product_categories WHERE id IN (?, ?, ?)
3. Send to QB with Department="Men's", Category="Casual", Subcategory="T-Shirts"
Shopify ↔ POS Platform
| Shopify Field | POS Platform Field | Sync Direction | Notes |
|---|---|---|---|
| product_type | product_type | Bi-directional | Direct mapping |
| vendor | vendor | Bi-directional | Direct mapping |
| tags | tags (comma-separated) | Bi-directional | Split/join on comma |
| - | department_id | Shopify → POS (as tag) | Optionally sync as dept:Men's tag |
| - | category_id | Shopify → POS (as tag) | Optionally sync as cat:Casual tag |
| collection.id | collection.shopify_id | Shopify → POS | Cache collection membership |
| collection.handle | collection.shopify_handle | Shopify → POS | For URL generation |
Sync Options (Configurable):
| Setting | Effect |
|---|---|
| Sync Department → Shopify Product Type | ON: Department name becomes product_type |
| Sync Category → Shopify Tag | ON: Category name becomes tag cat:Casual |
| Sync Subcategory → Shopify Tag | ON: Subcategory name becomes tag subcat:T-Shirts |
| Import Collections from Shopify | ON: Create local collection records from Shopify |
Configuration Guide
Initial Setup Checklist
## Category Setup Checklist
### 1. Define Departments (Level 1)
- [ ] List all top-level departments for your business
- [ ] Assign short codes (3-4 chars) for reports
- [ ] Set sort order for navigation
- [ ] Example: Men's (MEN), Women's (WMN), Kids (KID), Accessories (ACC)
### 2. Define Categories (Level 2)
- [ ] For each department, list mid-level categories
- [ ] Categories can repeat across departments (e.g., "Dress" in Men's and Women's)
- [ ] Example for Men's: Dress, Casual, Athletic, Outerwear
### 3. Define Subcategories (Level 3) - Optional
- [ ] For high-volume categories, add subcategories
- [ ] Keep to 5-10 max per category
- [ ] Example for Men's → Casual: T-Shirts, Polos, Jeans, Shorts
### 4. Configure Sync Settings
- [ ] Enable QB POS sync if using QuickBooks
- [ ] Enable Shopify sync if connected
- [ ] Decide on tag prefix strategy (cat:, subcat:)
### 5. Set Up Collections
- [ ] Create "Sale" smart collection (compare_at_price not empty)
- [ ] Create "New Arrivals" smart collection (created_at > 30 days ago)
- [ ] Create any seasonal/promotional manual collections
Migration from Existing Data
If migrating from Shopify only:
- Import Product Types as Departments or Categories
- Use existing tags for Subcategory hints
- Manually assign DCS hierarchy to products
If migrating from QuickBooks POS only:
- Import Department/Category/Subcategory directly
- Set product_type = Subcategory (or Category if no subcategory)
- Collections start empty (create manually)
If migrating from both (Shopify + QB POS):
- Use QB POS as source of truth for DCS (operational)
- Use Shopify as source of truth for collections/tags (marketing)
- Map Product Type ↔ Subcategory (or define mapping rules)
Best Practices
DCS Hierarchy Design
| Practice | Reason |
|---|---|
| Limit to 8-12 departments max | Too many clutters navigation |
| Use consistent naming conventions | “Men’s” vs “Mens” causes sync issues |
| Categories should be mutually exclusive | A shirt can’t be in both “Dress” and “Casual” |
| Plan for growth, not perfection | You can add subcategories later |
| Don’t rename departments after data exists | Breaks historical reports |
Collection Management
| Practice | Reason |
|---|---|
| Use smart collections for dynamic content | “Sale”, “Low Stock” update automatically |
| Use manual collections for curated content | “Gift Guide”, “Staff Picks” need curation |
| Limit visible collections to 6-8 | Navigation overload |
| Review smart collection rules quarterly | Ensure rules still make sense |
Tagging Strategy
| Practice | Reason |
|---|---|
| Use lowercase, hyphenated format | Consistency: father-day-2025 not Father's Day 2025 |
| Create a tag taxonomy document | Prevents duplicates: t-shirt vs tshirt |
| Don’t duplicate category info in tags | If in “T-Shirts” subcategory, don’t tag “tshirt” |
| Limit to 10-15 tags per product | More than that is noise |
| Prefix special tags | promo:bogo, season:summer, feature:bestseller |
Implementation Roadmap
Phase 1: Foundation (Week 3-4 of overall roadmap)
- Create
product_categoriestable - Build category management UI (Admin → Settings)
- Implement cascading dropdown in product creation
- Add department/category/subcategory to product model
Phase 2: Collections (Week 4-5)
- Create
collectionsandproduct_collectionstables - Build collection management UI (Admin → Marketing)
- Implement smart collection rule evaluation
- Add collection checkboxes to product creation
Phase 3: POS Integration (Week 5-6)
- Add department tabs to POS browse screen
- Add category sub-tabs
- Add collection quick-filter buttons
- Optimize queries for fast filtering
Phase 4: Sync (Week 6+)
- QB POS Department sync (bi-directional)
- Shopify Collection import
- Shopify tag sync
- Conflict resolution for mismatched categories
Appendix I Complete - Product Organization Model