# SneakerPicks — Categorization & Smart Collections

> Technical Design Document · v1.0 · 2026-02-19

---

## 1. Category Taxonomy

### 1.1 Taxonomy Tree

```
Root
├── BY ACTIVITY
│   ├── Lifestyle
│   │   ├── Casual
│   │   ├── Fashion / Streetwear
│   │   └── Slip-on / Mule
│   ├── Running
│   │   ├── Road Running
│   │   ├── Trail Running
│   │   └── Racing / Competition
│   ├── Basketball
│   │   ├── Performance
│   │   └── Lifestyle / Off-court
│   ├── Skateboarding
│   │   ├── Low-top
│   │   └── High-top
│   ├── Training & Gym
│   │   ├── Cross-training
│   │   └── Weightlifting
│   ├── Football / Soccer
│   │   ├── Indoor
│   │   └── Turf / Street
│   ├── Tennis / Padel
│   └── Walking / Outdoor
│       ├── Hiking
│       └── Sandals
│
├── BY STYLE
│   ├── Retro / Heritage
│   │   ├── 70s & 80s Classics
│   │   ├── 90s Retro
│   │   └── 2000s Retro
│   ├── Limited / Collab
│   │   ├── Designer Collabs
│   │   ├── Artist Collabs
│   │   └── Limited Editions
│   ├── High-top
│   ├── Low-top
│   ├── Mid-top
│   └── Platform / Chunky
│
├── BY BRAND → MODEL FAMILY
│   ├── Nike
│   │   ├── Air Max (1, 90, 95, 97, Plus, etc.)
│   │   ├── Air Force 1
│   │   ├── Dunk (Low, High)
│   │   ├── Air Jordan (1-14, misc)
│   │   ├── Blazer
│   │   ├── Cortez
│   │   ├── Vomero / Pegasus (running)
│   │   └── Other Nike
│   ├── Adidas
│   │   ├── Superstar
│   │   ├── Stan Smith
│   │   ├── Forum
│   │   ├── Gazelle / Samba / Spezial
│   │   ├── Ultraboost
│   │   ├── NMD
│   │   ├── Yeezy (if applicable)
│   │   └── Other Adidas
│   ├── New Balance
│   │   ├── 550
│   │   ├── 574
│   │   ├── 990 Series (990, 991, 992, 993)
│   │   ├── 2002R
│   │   ├── 1906R
│   │   └── Other NB
│   ├── Puma
│   │   ├── Suede
│   │   ├── RS-X
│   │   └── Other Puma
│   ├── Converse
│   │   ├── Chuck Taylor / All Star
│   │   └── One Star
│   ├── Vans
│   │   ├── Old Skool
│   │   ├── Sk8-Hi
│   │   └── Authentic / Era
│   ├── Asics
│   │   ├── Gel-Lyte III / V
│   │   ├── Gel-Kayano
│   │   └── Gel-1130 / Gel-NYC
│   ├── Reebok
│   │   ├── Classic Leather
│   │   └── Club C
│   ├── On Running
│   │   ├── Cloud 5
│   │   ├── Cloudmonster
│   │   └── Cloudnova
│   ├── Salomon
│   │   ├── XT-6
│   │   ├── Speedcross
│   │   └── ACS Pro
│   └── Other Brands
│
├── BY GENDER
│   ├── Men
│   ├── Women
│   ├── Unisex
│   └── Kids
│       ├── Boys
│       ├── Girls
│       ├── Toddler
│       └── Infant
│
└── TAGS (multi-assign, non-hierarchical)
    ├── Retro
    ├── Sustainable / Eco
    ├── Waterproof
    ├── Gore-Tex
    ├── Collab
    ├── Limited Edition
    ├── Vegan
    ├── Wide Fit
    └── (extensible by admin)
```

### 1.2 Design Principles

- A product has **one primary activity category**, **one brand→model family**, **one gender**.
- A product can have **multiple style categories** and **multiple tags**.
- Categories are *not* mutually exclusive across dimensions (a Nike Dunk is both `Skateboarding` and `Retro/Heritage`).
- Tags are flat, freeform, extensible. Categories are structured.

---

## 2. Database Schema

### 2.1 Core Tables

```sql
-- ============================================================
-- CATEGORIES
-- ============================================================

CREATE TABLE categories (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug          TEXT UNIQUE NOT NULL,          -- 'running/trail'
  name_en       TEXT NOT NULL,
  name_nl       TEXT NOT NULL,
  dimension     TEXT NOT NULL,                 -- 'activity' | 'style' | 'brand_family' | 'gender'
  parent_id     UUID REFERENCES categories(id),
  depth         INT NOT NULL DEFAULT 0,
  sort_order    INT NOT NULL DEFAULT 0,
  icon_url      TEXT,
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_categories_parent ON categories(parent_id);
CREATE INDEX idx_categories_dimension ON categories(dimension);

-- ============================================================
-- PRODUCT ↔ CATEGORY (many-to-many)
-- ============================================================

CREATE TABLE product_categories (
  product_id    UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  category_id   UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  is_primary    BOOLEAN NOT NULL DEFAULT FALSE,  -- primary per dimension
  confidence    REAL NOT NULL DEFAULT 1.0,        -- 1.0 = rule/manual, <1.0 = ML
  source        TEXT NOT NULL DEFAULT 'rule',     -- 'rule' | 'ml' | 'manual'
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (product_id, category_id)
);

CREATE INDEX idx_pc_product ON product_categories(product_id);
CREATE INDEX idx_pc_category ON product_categories(category_id);

-- ============================================================
-- TAGS (flat, multi-assign)
-- ============================================================

CREATE TABLE tags (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug          TEXT UNIQUE NOT NULL,
  name_en       TEXT NOT NULL,
  name_nl       TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

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,
  source        TEXT NOT NULL DEFAULT 'rule',
  PRIMARY KEY (product_id, tag_id)
);

-- ============================================================
-- COLLECTIONS
-- ============================================================

CREATE TABLE collections (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug          TEXT UNIQUE NOT NULL,
  name_en       TEXT NOT NULL,
  name_nl       TEXT NOT NULL,
  description   TEXT,
  type          TEXT NOT NULL,                 -- 'smart' | 'manual' | 'hybrid'
  rules_json    JSONB,                         -- for smart collections
  sort_by       TEXT NOT NULL DEFAULT 'relevance', -- 'relevance'|'price_asc'|'price_desc'|'newest'|'discount'|'popularity'
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  is_featured   BOOLEAN NOT NULL DEFAULT FALSE,  -- show on homepage
  homepage_sort INT,                           -- ordering on homepage
  image_url     TEXT,
  max_products  INT,                           -- NULL = unlimited
  cache_ttl_sec INT NOT NULL DEFAULT 300,      -- how long to cache membership
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Pre-computed membership for fast reads
CREATE TABLE collection_products (
  collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
  product_id    UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  sort_score    REAL NOT NULL DEFAULT 0,       -- pre-computed sort value
  added_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (collection_id, product_id)
);

CREATE INDEX idx_cp_collection_sort ON collection_products(collection_id, sort_score DESC);

-- Manual additions/exclusions for hybrid collections
CREATE TABLE collection_overrides (
  collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
  product_id    UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  action        TEXT NOT NULL,                 -- 'include' | 'exclude' | 'pin'
  pin_position  INT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (collection_id, product_id)
);

-- ============================================================
-- HOMEPAGE SECTIONS
-- ============================================================

CREATE TABLE homepage_sections (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type          TEXT NOT NULL,                 -- 'collection' | 'editors_picks' | 'banner' | 'brands'
  collection_id UUID REFERENCES collections(id),
  title_en      TEXT,
  title_nl      TEXT,
  sort_order    INT NOT NULL DEFAULT 0,
  max_items     INT NOT NULL DEFAULT 12,
  layout        TEXT NOT NULL DEFAULT 'carousel', -- 'carousel' | 'grid' | 'list'
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  config_json   JSONB,                         -- extra layout/styling config
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Editor's picks / featured products (manual)
CREATE TABLE featured_products (
  section_id    UUID NOT NULL REFERENCES homepage_sections(id) ON DELETE CASCADE,
  product_id    UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  sort_order    INT NOT NULL DEFAULT 0,
  PRIMARY KEY (section_id, product_id)
);

-- ============================================================
-- CATEGORIZATION RULES
-- ============================================================

CREATE TABLE categorization_rules (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          TEXT NOT NULL,
  priority      INT NOT NULL DEFAULT 100,      -- lower = higher priority
  conditions    JSONB NOT NULL,                -- rule conditions
  actions       JSONB NOT NULL,                -- what to assign
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  hit_count     BIGINT NOT NULL DEFAULT 0,     -- monitoring
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_rules_priority ON categorization_rules(priority) WHERE is_active;
```

### 2.2 Required Columns on `products` Table

```sql
-- Ensure these exist on the products table
ALTER TABLE products ADD COLUMN IF NOT EXISTS brand TEXT;
ALTER TABLE products ADD COLUMN IF NOT EXISTS model_name TEXT;         -- extracted
ALTER TABLE products ADD COLUMN IF NOT EXISTS gender TEXT;             -- detected
ALTER TABLE products ADD COLUMN IF NOT EXISTS original_price NUMERIC;
ALTER TABLE products ADD COLUMN IF NOT EXISTS clicks_48h INT DEFAULT 0;
ALTER TABLE products ADD COLUMN IF NOT EXISTS clicks_7d INT DEFAULT 0;
ALTER TABLE products ADD COLUMN IF NOT EXISTS conversions_30d INT DEFAULT 0;
ALTER TABLE products ADD COLUMN IF NOT EXISTS first_seen_at TIMESTAMPTZ;
ALTER TABLE products ADD COLUMN IF NOT EXISTS categorized_at TIMESTAMPTZ;
ALTER TABLE products ADD COLUMN IF NOT EXISTS categorization_version INT DEFAULT 0;
```

---

## 3. Auto-Categorization Algorithm

### 3.1 Pipeline Overview

```
Feed Product Ingested
       │
       ▼
┌──────────────────┐
│ 1. Normalize      │  lowercase, strip special chars, expand abbreviations
│    product name   │  "Wmns Air Max 90" → "women air max 90"
└────────┬─────────┘
         ▼
┌──────────────────┐
│ 2. Brand          │  from feed field OR keyword extraction
│    Detection      │  "nike", "adidas", "new balance", etc.
└────────┬─────────┘
         ▼
┌──────────────────┐
│ 3. Gender         │  keywords: wmns/women/dames → Women
│    Detection      │  men/heren → Men, gs/kids/junior → Kids
└────────┬─────────┘  feed gender field as fallback
         ▼
┌──────────────────┐
│ 4. Rule-based     │  iterate categorization_rules by priority
│    Matching       │  first match per dimension wins
└────────┬─────────┘
         ▼
┌──────────────────┐
│ 5. Fuzzy/ML       │  if no match for a dimension:
│    Fallback       │  TF-IDF similarity to known products
└────────┬─────────┘
         ▼
┌──────────────────┐
│ 6. Write Results  │  insert into product_categories + product_tags
│                   │  with confidence score + source
└──────────────────┘
```

### 3.2 Categorization Rule Format

Rules are stored as JSON and evaluated in priority order.

```jsonc
// Example: categorization_rules.conditions + actions
{
  "conditions": {
    "operator": "AND",
    "rules": [
      { "field": "brand", "op": "eq", "value": "nike" },
      { "field": "name_normalized", "op": "contains_any", "value": ["air max 90", "am90"] }
    ]
  },
  "actions": {
    "assign_categories": [
      { "slug": "nike/air-max", "dimension": "brand_family" }
    ],
    "assign_tags": ["retro"],
    "set_activity": "lifestyle"
  }
}
```

**Supported condition operators:**

| op | description |
|---|---|
| `eq` | exact match |
| `neq` | not equal |
| `contains` | substring match |
| `contains_any` | any of the substrings |
| `contains_all` | all substrings present |
| `regex` | regex pattern |
| `in` | value in list |
| `gt`, `lt`, `gte`, `lte` | numeric comparison |
| `is_null`, `is_not_null` | null checks |

**Supported fields:** `name_normalized`, `brand`, `feed_category`, `shop`, `price`, `original_price`, `description`, `color`, `sku`.

### 3.3 Example Rules

```jsonc
// Rule: Detect "Air Max" family
{
  "name": "Nike Air Max family",
  "priority": 10,
  "conditions": {
    "operator": "AND",
    "rules": [
      { "field": "brand", "op": "eq", "value": "nike" },
      { "field": "name_normalized", "op": "regex", "value": "air\\s*max" }
    ]
  },
  "actions": {
    "assign_categories": [{ "slug": "nike/air-max", "dimension": "brand_family" }],
    "set_activity": "lifestyle",
    "assign_tags": ["retro"]
  }
}

// Rule: Running shoes (multi-language)
{
  "name": "Running category detection",
  "priority": 50,
  "conditions": {
    "operator": "OR",
    "rules": [
      { "field": "name_normalized", "op": "contains_any", "value": ["running", "hardloop", "runner"] },
      { "field": "feed_category", "op": "contains_any", "value": ["running", "hardlopen", "run"] }
    ]
  },
  "actions": {
    "set_activity": "running"
  }
}

// Rule: Gender detection from name
{
  "name": "Women's product detection",
  "priority": 5,
  "conditions": {
    "operator": "OR",
    "rules": [
      { "field": "name_normalized", "op": "contains_any", "value": ["wmns", "women", "dames", "w's"] }
    ]
  },
  "actions": {
    "set_gender": "women"
  }
}
```

### 3.4 Fuzzy / ML Fallback

For products not matched by rules:

1. **TF-IDF similarity**: Compare normalized product name against names of already-categorized products. Assign same categories if cosine similarity > 0.85.
2. **Feed category mapping**: Maintain a `feed_category_map` table that maps known feed categories (e.g. Zalando's "Hardloopschoenen") to our taxonomy. Build this iteratively.
3. **Uncategorized queue**: Products with confidence < 0.7 go into an admin review queue.

```sql
CREATE TABLE feed_category_map (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  shop            TEXT NOT NULL,
  feed_category   TEXT NOT NULL,
  our_category_id UUID REFERENCES categories(id),
  confidence      REAL NOT NULL DEFAULT 1.0,
  UNIQUE(shop, feed_category)
);
```

### 3.5 Admin Override

Manual categorization always wins. When an admin recategorizes a product:

- `source` is set to `'manual'`
- `confidence` is set to `1.0`
- The product is excluded from future auto-categorization (unless admin resets it)

---

## 4. Smart Collection Rule Engine

### 4.1 Collection `rules_json` Format

```jsonc
{
  "operator": "AND",        // top-level AND/OR
  "rules": [
    {
      "type": "field",       // field comparison
      "field": "discount_pct",
      "op": "gte",
      "value": 20
    },
    {
      "type": "category",    // must be in category
      "slug": "lifestyle"
    },
    {
      "type": "tag",
      "slug": "retro"
    },
    {
      "type": "time",        // time-based
      "field": "first_seen_at",
      "op": "within_days",
      "value": 7
    },
    {
      "type": "nested",      // sub-group
      "operator": "OR",
      "rules": [...]
    }
  ]
}
```

### 4.2 Built-in Collection Definitions

```yaml
sales:
  name_nl: "Sale"
  rules:
    operator: AND
    rules:
      - { type: field, field: discount_pct, op: gte, value: 15 }
      - { type: field, field: price, op: gt, value: 0 }
  sort_by: discount_desc
  cache_ttl: 300

bestsellers:
  name_nl: "Bestsellers"
  rules:
    operator: AND
    rules:
      - { type: field, field: conversions_30d, op: gte, value: 1 }
  sort_by: conversions_desc
  max_products: 50
  cache_ttl: 3600

trending:
  name_nl: "Trending"
  rules:
    operator: AND
    rules:
      - { type: field, field: clicks_48h, op: gte, value: 5 }
  sort_by: clicks_48h_desc
  max_products: 40
  cache_ttl: 300

price_drops:
  name_nl: "Prijsdalingen"
  rules:
    operator: AND
    rules:
      - { type: price_history, op: dropped, period_days: 7, min_drop_pct: 10 }
  sort_by: drop_pct_desc
  cache_ttl: 600

new_arrivals:
  name_nl: "Nieuw Binnen"
  rules:
    operator: AND
    rules:
      - { type: time, field: first_seen_at, op: within_days, value: 7 }
  sort_by: newest
  cache_ttl: 600

under_100:
  name_nl: "Onder €100"
  rules:
    operator: AND
    rules:
      - { type: field, field: price, op: lt, value: 100 }
      - { type: field, field: price, op: gt, value: 0 }
  sort_by: price_asc
  cache_ttl: 600
```

### 4.3 Collection Refresh Strategy

Collections are **materialized** into `collection_products` for fast reads.

```
┌─────────────────────────────────────────────────┐
│              Refresh Strategies                  │
├─────────────────────────────────────────────────┤
│ FULL REBUILD  │ Run after feed import (daily)    │
│               │ Re-evaluate ALL smart collections│
│               │ against all products             │
├───────────────┤──────────────────────────────────┤
│ INCREMENTAL   │ On product update/insert:        │
│               │ evaluate that product against     │
│               │ all active collections            │
├───────────────┤──────────────────────────────────┤
│ TTL-BASED     │ API checks cache_ttl_sec.        │
│               │ If stale, trigger async refresh.  │
│               │ Serve stale data while refreshing.│
└─────────────────────────────────────────────────┘
```

**Implementation:** A background worker (cron or pg_cron) runs:

```sql
-- Pseudo-SQL for rebuilding a collection
DELETE FROM collection_products WHERE collection_id = $1;

INSERT INTO collection_products (collection_id, product_id, sort_score)
SELECT $1, p.id, <sort_expression>
FROM products p
LEFT JOIN product_categories pc ON pc.product_id = p.id
LEFT JOIN product_tags pt ON pt.product_id = p.id
WHERE <dynamic_where_from_rules>
ORDER BY <sort_expression>
LIMIT <max_products>;
```

The rule engine translates `rules_json` into a SQL WHERE clause at runtime.

### 4.4 Rule-to-SQL Translation

```typescript
function rulesToSQL(rules: RuleGroup): { where: string; params: any[] } {
  const clauses = rules.rules.map(rule => {
    switch (rule.type) {
      case 'field':
        return fieldToSQL(rule);        // "p.price < $1"
      case 'category':
        return `EXISTS (SELECT 1 FROM product_categories pc2 
                WHERE pc2.product_id = p.id 
                AND pc2.category_id = (SELECT id FROM categories WHERE slug = $N))`;
      case 'tag':
        return `EXISTS (SELECT 1 FROM product_tags pt2 
                JOIN tags t ON t.id = pt2.tag_id 
                WHERE pt2.product_id = p.id AND t.slug = $N)`;
      case 'time':
        return timeToSQL(rule);         // "p.first_seen_at > now() - interval '7 days'"
      case 'price_history':
        return priceHistoryToSQL(rule); // subquery on price_history table
      case 'nested':
        return `(${rulesToSQL(rule).where})`;
    }
  });
  
  return { where: clauses.join(` ${rules.operator} `), params };
}
```

---

## 5. API Endpoints

### 5.1 Category Management (Admin)

```
GET    /api/admin/categories                    # list tree
POST   /api/admin/categories                    # create category
PUT    /api/admin/categories/:id                # update
DELETE /api/admin/categories/:id                # soft-delete
POST   /api/admin/categories/reorder            # batch sort_order update
```

### 5.2 Product Categorization (Admin)

```
GET    /api/admin/products/:id/categories       # view assignments
PUT    /api/admin/products/:id/categories       # manual override (replaces all)
POST   /api/admin/products/:id/tags             # add tag
DELETE /api/admin/products/:id/tags/:tagId      # remove tag
GET    /api/admin/products/uncategorized        # review queue (confidence < 0.7)
POST   /api/admin/products/bulk-categorize      # batch assign
```

### 5.3 Categorization Rules (Admin)

```
GET    /api/admin/rules                         # list all rules
POST   /api/admin/rules                         # create rule
PUT    /api/admin/rules/:id                     # update rule
DELETE /api/admin/rules/:id                     # delete rule
POST   /api/admin/rules/:id/test               # dry-run: show which products match
POST   /api/admin/rules/rerun                   # re-run all rules against all products
```

### 5.4 Collections (Admin)

```
GET    /api/admin/collections                   # list all
POST   /api/admin/collections                   # create
PUT    /api/admin/collections/:id               # update rules/metadata
DELETE /api/admin/collections/:id               # delete
POST   /api/admin/collections/:id/refresh       # force refresh
GET    /api/admin/collections/:id/preview       # preview products (dry-run rules)
POST   /api/admin/collections/:id/override      # pin/exclude product
```

### 5.5 Homepage Sections (Admin)

```
GET    /api/admin/homepage                      # list sections
POST   /api/admin/homepage                      # add section
PUT    /api/admin/homepage/:id                  # update
DELETE /api/admin/homepage/:id                  # remove
POST   /api/admin/homepage/reorder              # batch sort update
PUT    /api/admin/homepage/:id/featured         # set featured products (editors_picks)
```

### 5.6 Public API

```
GET    /api/categories                          # full tree for navigation
GET    /api/categories/:slug/products           # products in category (paginated)
GET    /api/collections/:slug                   # collection metadata + products
GET    /api/homepage                            # all active sections with products
GET    /api/tags                                # all tags
GET    /api/tags/:slug/products                 # products by tag
```

---

## 6. Homepage Curation

### 6.1 Section Types

| Type | Source | Description |
|---|---|---|
| `collection` | Smart/manual collection | "Trending", "Sale", "New Arrivals" |
| `editors_picks` | Manual product selection | Curated by admin |
| `banner` | config_json | Hero banner / promo |
| `brands` | Static | Brand logo carousel |
| `categories` | Categories table | Category tiles for navigation |

### 6.2 Default Homepage Layout

```
1. [banner]          Hero — seasonal campaign
2. [collection]      Trending (carousel)
3. [collection]      New Arrivals (carousel)
4. [editors_picks]   Editor's Picks (grid, 6 items)
5. [brands]          Shop by Brand
6. [collection]      Sale (carousel)
7. [collection]      Price Drops (carousel)
8. [categories]      Shop by Category (tiles)
9. [collection]      Under €100 (carousel)
```

Admin can reorder, add, remove, and toggle sections via the API.

---

## 7. Categorization Worker

### 7.1 On Feed Import

```typescript
async function onFeedImported(products: Product[]) {
  const rules = await db.categorizationRules.findActive({ orderBy: 'priority' });
  
  for (const product of products) {
    // Skip manually categorized
    if (product.categorization_source === 'manual') continue;
    
    const normalized = normalize(product.name);
    const assignments: Assignment[] = [];
    
    // Phase 1: brand detection
    const brand = detectBrand(product);
    
    // Phase 2: gender detection
    const gender = detectGender(normalized, product.feed_gender);
    
    // Phase 3: rule matching
    for (const rule of rules) {
      if (evaluateConditions(rule.conditions, { ...product, name_normalized: normalized })) {
        assignments.push(...rule.actions);
        rule.hit_count++;
      }
    }
    
    // Phase 4: fuzzy fallback for unmatched dimensions
    const missingDimensions = findMissingDimensions(assignments);
    if (missingDimensions.length > 0) {
      const fuzzyResults = await fuzzyMatch(normalized, missingDimensions);
      assignments.push(...fuzzyResults);
    }
    
    // Phase 5: write
    await writeAssignments(product.id, assignments);
    
    // Phase 6: update collections (incremental)
    await refreshCollectionsForProduct(product.id);
  }
}
```

### 7.2 Normalization

```typescript
function normalize(name: string): string {
  return name
    .toLowerCase()
    .replace(/[''`]/g, '')
    .replace(/[-_\/\\]/g, ' ')
    .replace(/\s+/g, ' ')
    .replace(/wmns/g, 'women')
    .replace(/gs/g, 'grade school')
    .replace(/td/g, 'toddler')
    .replace(/ps/g, 'preschool')
    .trim();
}
```

---

## 8. Performance Considerations

1. **Materialized collections**: Collection membership is pre-computed. Public reads are simple JOINs against `collection_products`.
2. **Indices**: All foreign keys indexed. `collection_products` has a composite sort index.
3. **Batch processing**: Feed imports categorize in batches of 500. Collection refreshes run as a single SQL per collection.
4. **Cache TTL**: Each collection has its own TTL. High-churn collections (trending) get shorter TTLs.
5. **Async refresh**: Collection refresh is non-blocking. Stale data is served while refreshing.

---

## 9. Migration Path

### Phase 1: Schema + Basic Rules
- Deploy schema
- Create top-level categories
- Write 20-30 rules covering major brands and model families
- Run initial categorization pass

### Phase 2: Collections
- Create built-in collections
- Implement refresh worker
- Wire up homepage sections

### Phase 3: Admin UI
- Category tree editor
- Rule builder (visual)
- Collection manager with preview
- Homepage section drag-and-drop

### Phase 4: ML Fallback
- Train TF-IDF model on categorized products
- Implement uncategorized review queue
- Build feed_category_map from admin actions

---

## 10. Open Questions

1. **Sizing taxonomy**: Should we categorize by EU sizes available? (enables "Available in EU 46" filters)
2. **Color extraction**: Extract colorway from product name for filtering?
3. **Seasonal collections**: Auto-expire collections (e.g., "Back to School 2026")?
4. **A/B testing homepage**: Different section orderings per user segment?