Feature 03 — Apparel Analytics Reports

Phase: 2 (after Collections and Variant Matrix) Priority: 🔴 Critical — key differentiator vs. competitors Status: ⏳ Pending

Context

No new tables are required. All data already exists:

sale.sale_detail — JSON array of items with productId, quantity, unitPrice
inventory — current stock per (location_id, product_id)
inventory_ledger — all stock movements with reference_type
product — with size_id, color_id, brand_id, category_id FKs
size, color — attribute lookup tables
collection_product (from Feature 07) — product → collection mapping

This is the biggest market differentiator. Most POS systems cannot answer:

"What sizes are left for this style?"
"Which colors underperformed this season?"
"What is our sell-through rate for Summer 2026?"

Relevant existing files:

packages/backend/database/src/migrations/2024-06-02...-onboarding-tables.mjs — sale, inventory, inventory_ledger, product, size, color
apps/backend/src/pricing/application/price-resolution.service.ts — reference for complex Kysely queries

Task Checklist

Database

Create migration for performance indexes: packages/backend/database/src/migrations/2026-03-XX-retail-analytics-indexes.mjs
Add index on sale(business_id, sale_date) if missing
Add index on inventory_ledger(product_id, created_at) if missing
Run pnpm run migration:local:push

Backend

Create module: apps/backend/src/retail-analytics/
Create retail-analytics.module.ts
Create application/retail-analytics.service.ts with all 5 query methods
Create interfaces/retail-analytics.controller.ts with 5 endpoints
Create interfaces/query/retail-analytics.query.ts (shared filter params)
Register RetailAnalyticsModule in apps/backend/src/app.module.ts

PWA Frontend

Create apps/frontend-pwa/src/types/retailAnalytics.ts
Create apps/frontend-pwa/src/services/retailAnalyticsService.ts
Create apps/frontend-pwa/src/components/forms/retail-analytics/RetailAnalyticsPage.tsx
Implement 5 tabbed report views (Size Curve, Color, Sell-Through, Velocity, Replenishment)
Register in apps/frontend-pwa/src/pages/MainPage.tsx

Verification

Migration applies cleanly
Backend builds
GET /retail-analytics/size-curve?businessId=X&dateFrom=Y&dateTo=Z returns data
GET /retail-analytics/sell-through?businessId=X&collectionId=Y returns data
Reports page renders with correct data
Filters (date range, location, collection) work correctly

Database

Migration: Performance Indexes

// packages/backend/database/src/migrations/2026-03-XX-retail-analytics-indexes.mjs
import { Kysely, sql } from "kysely";

export async function up(db) {
  await sql`
    CREATE INDEX IF NOT EXISTS idx_sale_business_date
    ON sale(business_id, sale_date)
  `.execute(db);

  await sql`
    CREATE INDEX IF NOT EXISTS idx_inventory_ledger_product_date
    ON inventory_ledger(product_id, created_at)
  `.execute(db);

  await sql`
    CREATE INDEX IF NOT EXISTS idx_inventory_product_location
    ON inventory(business_id, location_id, product_id)
  `.execute(db);
}

export async function down(db) {
  await sql`DROP INDEX IF EXISTS idx_inventory_product_location`.execute(db);
  await sql`DROP INDEX IF EXISTS idx_inventory_ledger_product_date`.execute(db);
  await sql`DROP INDEX IF EXISTS idx_sale_business_date`.execute(db);
}

Backend Implementation

Module Structure

apps/backend/src/retail-analytics/
├── retail-analytics.module.ts
├── application/
│   └── retail-analytics.service.ts
└── interfaces/
    ├── retail-analytics.controller.ts
    └── query/
        └── retail-analytics.query.ts

Query Params: `retail-analytics.query.ts`

export class RetailAnalyticsQuery {
  @IsUUID() businessId: string;
  @IsOptional() @IsUUID() locationId?: string;
  @IsOptional() @IsUUID() collectionId?: string;
  @IsOptional() @IsUUID() categoryId?: string;
  @IsOptional() @IsUUID() brandId?: string;
  @IsOptional() @IsUUID() productId?: string;
  @IsOptional() @IsDateString() dateFrom?: string;
  @IsOptional() @IsDateString() dateTo?: string;
}

Endpoints: `retail-analytics.controller.ts`

@Controller("retail-analytics")
export class RetailAnalyticsController {
  @Get("size-curve")
  getSizeCurve(@Query() query: RetailAnalyticsQuery): Promise<SizeCurveRow[]>

  @Get("color-performance")
  getColorPerformance(@Query() query: RetailAnalyticsQuery): Promise<ColorPerformanceRow[]>

  @Get("sell-through")
  getSellThrough(@Query() query: RetailAnalyticsQuery): Promise<SellThroughRow[]>

  @Get("velocity")
  getVelocity(@Query() query: RetailAnalyticsQuery): Promise<VelocityRow[]>
  // Returns both slow_movers (velocity < threshold) and fast_movers
}

Service: `retail-analytics.service.ts`

Size Curve Query

async getSizeCurve(params: RetailAnalyticsQuery): Promise<SizeCurveRow[]> {
  // Strategy:
  // 1. Unnest sale_detail JSON items → get productId + quantity per size
  // 2. Join product to size via size_id
  // 3. Join inventory for current stock per size
  // 4. Aggregate: units_sold, current_stock, sell_through_pct

  const result = await sql<SizeCurveRow>`
    WITH sold AS (
      SELECT
        p.size_id,
        s.name AS size_name,
        SUM((item->>'quantity')::numeric) AS units_sold
      FROM sale
      CROSS JOIN LATERAL jsonb_array_elements(sale.sale_detail->'items') AS item
      JOIN product p ON p.id = (item->>'productId')::uuid
      JOIN size s ON s.id = p.size_id
      WHERE sale.business_id = ${params.businessId}
        AND sale.sale_date BETWEEN ${params.dateFrom ?? '2000-01-01'} AND ${params.dateTo ?? 'now()'}
        AND ${params.collectionId ? sql`p.id IN (SELECT product_id FROM collection_product WHERE collection_id = ${params.collectionId})` : sql`TRUE`}
      GROUP BY p.size_id, s.name
    ),
    stocked AS (
      SELECT
        p.size_id,
        SUM(i.quantity) AS current_stock
      FROM inventory i
      JOIN product p ON p.id = i.product_id
      WHERE i.business_id = ${params.businessId}
        AND ${params.locationId ? sql`i.location_id = ${params.locationId}` : sql`TRUE`}
      GROUP BY p.size_id
    )
    SELECT
      COALESCE(sold.size_name, 'Unknown') AS size_name,
      COALESCE(sold.units_sold, 0) AS units_sold,
      COALESCE(stocked.current_stock, 0) AS current_stock,
      CASE
        WHEN (COALESCE(sold.units_sold, 0) + COALESCE(stocked.current_stock, 0)) > 0
        THEN ROUND(sold.units_sold / (sold.units_sold + stocked.current_stock) * 100, 1)
        ELSE 0
      END AS sell_through_pct
    FROM sold
    FULL OUTER JOIN stocked ON sold.size_id = stocked.size_id
    ORDER BY units_sold DESC
  `.execute(this.database);

  return result.rows;
}

Velocity / Slow-Fast Movers Query

async getVelocity(params: RetailAnalyticsQuery): Promise<VelocityRow[]> {
  const result = await sql<VelocityRow>`
    WITH velocity AS (
      SELECT
        p.id AS product_id,
        p.name AS product_name,
        p.sku,
        SUM(CASE WHEN sale.sale_date >= NOW() - INTERVAL '30 days'
          THEN (item->>'quantity')::numeric ELSE 0 END) AS velocity_30d,
        SUM(CASE WHEN sale.sale_date >= NOW() - INTERVAL '90 days'
          THEN (item->>'quantity')::numeric ELSE 0 END) AS velocity_90d
      FROM sale
      CROSS JOIN LATERAL jsonb_array_elements(sale.sale_detail->'items') AS item
      JOIN product p ON p.id = (item->>'productId')::uuid
      WHERE sale.business_id = ${params.businessId}
        AND sale.sale_date >= NOW() - INTERVAL '90 days'
      GROUP BY p.id, p.name, p.sku
    )
    SELECT
      v.*,
      i.quantity AS current_stock,
      i.reorder_point,
      CASE WHEN v.velocity_30d < 1 THEN true ELSE false END AS is_slow_mover,
      CASE WHEN v.velocity_30d > 10 THEN true ELSE false END AS is_fast_mover
    FROM velocity v
    LEFT JOIN inventory i ON i.product_id = v.product_id
      AND ${params.locationId ? sql`i.location_id = ${params.locationId}` : sql`TRUE`}
    ORDER BY v.velocity_30d ASC
  `.execute(this.database);

  return result.rows;
}

Sell-Through Query

async getSellThrough(params: RetailAnalyticsQuery): Promise<SellThroughRow[]> {
  // Initial stock = total received via goods_received_note (from inventory_ledger)
  // Units sold = from sale_detail JSON
  // Group by: collection, category, or product
  const result = await sql<SellThroughRow>`
    WITH received AS (
      SELECT product_id, SUM(quantity) AS initial_stock
      FROM inventory_ledger
      WHERE reference_type IN ('goods_received_note', 'purchase')
        AND created_at BETWEEN ${params.dateFrom ?? '2000-01-01'} AND ${params.dateTo ?? 'now()'}
      GROUP BY product_id
    ),
    sold AS (
      SELECT (item->>'productId')::uuid AS product_id,
             SUM((item->>'quantity')::numeric) AS units_sold
      FROM sale
      CROSS JOIN LATERAL jsonb_array_elements(sale.sale_detail->'items') AS item
      WHERE sale.business_id = ${params.businessId}
        AND sale.sale_date BETWEEN ${params.dateFrom ?? '2000-01-01'} AND ${params.dateTo ?? 'now()'}
      GROUP BY 1
    )
    SELECT
      p.name AS product_name, p.sku,
      COALESCE(r.initial_stock, 0) AS initial_stock,
      COALESCE(s.units_sold, 0) AS units_sold,
      COALESCE(i.quantity, 0) AS remaining_stock,
      CASE WHEN COALESCE(r.initial_stock, 0) > 0
        THEN ROUND(s.units_sold / r.initial_stock * 100, 1) ELSE 0
      END AS sell_through_pct
    FROM product p
    LEFT JOIN received r ON r.product_id = p.id
    LEFT JOIN sold s ON s.product_id = p.id
    LEFT JOIN inventory i ON i.product_id = p.id
    WHERE p.business_id = ${params.businessId}
    ORDER BY sell_through_pct DESC
  `.execute(this.database);

  return result.rows;
}

PWA Frontend

Types: `types/retailAnalytics.ts`

export interface SizeCurveRow {
  sizeName: string;
  unitsSold: number;
  currentStock: number;
  sellThroughPct: number;
}

export interface ColorPerformanceRow {
  colorName: string;
  hexValue?: string;
  unitsSold: number;
  revenue: number;
  currentStock: number;
  sellThroughPct: number;
}

export interface SellThroughRow {
  productName: string;
  sku?: string;
  collectionName?: string;
  categoryName?: string;
  initialStock: number;
  unitsSold: number;
  remainingStock: number;
  sellThroughPct: number;
}

export interface VelocityRow {
  productId: string;
  productName: string;
  sku?: string;
  velocity30d: number;
  velocity90d: number;
  currentStock: number;
  reorderPoint: number;
  isSlowMover: boolean;
  isFastMover: boolean;
}

Page Layout: `RetailAnalyticsPage.tsx`

┌─────────────────────────────────────────────────────────────┐
│  Retail Analytics                                           │
│  [Date From] [Date To]  [Location ▼] [Collection ▼] [Apply]│
├─────────────────────────────────────────────────────────────┤
│ [Size Curve] [Color] [Sell-Through] [Slow/Fast] [Replenish] │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  SIZE CURVE TAB:                                            │
│  ████████████████ XL  (245 sold, 18% remaining)            │
│  ██████████████   L   (210 sold, 22% remaining)            │
│  ████████████     M   (180 sold, 31% remaining)  ← warn    │
│  ████████         S   (142 sold, 45% remaining)  ← warn    │
│                                                             │
│  Size  | Sold | Stock | Sell-Through%                       │
│  XL    |  245 |    52 |  82.5%                             │
│  ...                                                        │
└─────────────────────────────────────────────────────────────┘

Tabs:

Size Curve — horizontal bar chart + table (size, units sold, current stock, sell-through%)
Color Performance — colored dot + bar chart + table (color swatch, units sold, revenue)
Sell-Through — table grouped by collection/category, sortable by sell-through%
Velocity — two sections: Fast Movers (🔥) and Slow Movers (🐢); "Flag for Markdown" action
Replenishment — linked to Feature 08 page (shortcut button)

Context​

Task Checklist​

Database​

Backend​

PWA Frontend​

Verification​

Database​

Migration: Performance Indexes​

Backend Implementation​

Module Structure​

Query Params: retail-analytics.query.ts​

Endpoints: retail-analytics.controller.ts​

Service: retail-analytics.service.ts​

Size Curve Query​

Velocity / Slow-Fast Movers Query​

Sell-Through Query​

PWA Frontend​

Types: types/retailAnalytics.ts​

Page Layout: RetailAnalyticsPage.tsx​

Context

Task Checklist

Database

Backend

PWA Frontend

Verification

Database

Migration: Performance Indexes

Backend Implementation

Module Structure

Query Params: `retail-analytics.query.ts`

Endpoints: `retail-analytics.controller.ts`

Service: `retail-analytics.service.ts`

Size Curve Query

Velocity / Slow-Fast Movers Query

Sell-Through Query

PWA Frontend

Types: `types/retailAnalytics.ts`

Page Layout: `RetailAnalyticsPage.tsx`