# SneakerPicks Feed Management System — Technical Design

**Version:** 1.0  
**Date:** 2026-02-19  
**Status:** Draft  
**Author:** AI-assisted design for sneakerpicks.nl

---

## 1. Overview

SneakerPicks is a Dutch sneaker price comparison site that aggregates product data from multiple affiliate networks. This document describes the architecture of a unified feed management system that ingests, normalizes, deduplicates, and serves product data from **Webgains**, **TradeTracker**, and **Awin**.

### Current State
- **Webgains** integration is live: 27 feeds, 80K+ products
- **TradeTracker** and **Awin** need to be added
- No unified feed management or admin dashboard exists

### Goals
1. Pluggable provider architecture — add new affiliate networks without changing core logic
2. Configurable field mapping per provider/feed
3. Per-feed sync scheduling with error handling and retry
4. Cross-feed product matching and deduplication
5. Admin dashboard for feed management and monitoring

---

## 2. Architecture

```mermaid
graph TB
    subgraph "Admin Dashboard"
        UI[Feed Management UI]
    end

    subgraph "API Layer"
        API[REST API]
    end

    subgraph "Sync Engine"
        SCHED[Scheduler / Cron]
        PIPE[Sync Pipeline]
        QUEUE[Job Queue]
    end

    subgraph "Provider Adapters"
        WG[Webgains Adapter]
        TT[TradeTracker Adapter]
        AW[Awin Adapter]
    end

    subgraph "Processing"
        MAP[Field Mapper]
        NORM[Normalizer]
        MATCH[Product Matcher]
        DEDUP[Deduplicator]
    end

    subgraph "Storage"
        DB[(PostgreSQL)]
    end

    subgraph "External APIs"
        WG_API[Webgains API]
        TT_API[TradeTracker SOAP API]
        AW_API[Awin API]
    end

    UI --> API
    API --> DB
    API --> QUEUE
    SCHED --> QUEUE
    QUEUE --> PIPE
    PIPE --> WG --> WG_API
    PIPE --> TT --> TT_API
    PIPE --> AW --> AW_API
    WG --> MAP
    TT --> MAP
    AW --> MAP
    MAP --> NORM --> MATCH --> DEDUP --> DB
```

### Component Responsibilities

| Component | Role |
|-----------|------|
| **Scheduler** | Triggers feed syncs based on per-feed cron config |
| **Job Queue** | Manages sync jobs, prevents duplicate runs, handles retries |
| **Provider Adapter** | Fetches raw data from external API, handles auth & pagination |
| **Field Mapper** | Applies configurable field mappings (provider fields → our schema) |
| **Normalizer** | Cleans data: trim whitespace, normalize prices, validate URLs |
| **Product Matcher** | Finds existing products by EAN/GTIN, then fuzzy match |
| **Deduplicator** | Merges offers from multiple feeds into unified products |
| **REST API** | CRUD for feeds, mappings, sync triggers; dashboard data |

---

## 3. Database Schema

### 3.1 `providers` — Affiliate network registry

```sql
CREATE TABLE providers (
    id              SERIAL PRIMARY KEY,
    slug            VARCHAR(50) UNIQUE NOT NULL,  -- 'webgains', 'tradetracker', 'awin'
    name            VARCHAR(100) NOT NULL,
    adapter_type    VARCHAR(50) NOT NULL,          -- class/module identifier
    config          JSONB NOT NULL DEFAULT '{}',   -- API keys, endpoints, etc.
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Seed data
INSERT INTO providers (slug, name, adapter_type, config) VALUES
('webgains', 'Webgains', 'webgains', '{"api_url": "https://platform-api.webgains.com"}'),
('tradetracker', 'TradeTracker', 'tradetracker', '{"wsdl_url": "https://ws.tradetracker.com/soap/affiliate?wsdl", "customer_id": 35058, "locale": "nl_NL"}'),
('awin', 'Awin', 'awin', '{"api_url": "https://api.awin.com"}');
```

### 3.2 `feeds` — Individual product feeds

```sql
CREATE TABLE feeds (
    id              SERIAL PRIMARY KEY,
    provider_id     INTEGER NOT NULL REFERENCES providers(id),
    external_id     VARCHAR(255),                  -- Feed ID in provider's system
    name            VARCHAR(255) NOT NULL,
    merchant_name   VARCHAR(255),
    feed_url        TEXT,                           -- Direct feed URL if applicable
    sync_frequency  VARCHAR(10) NOT NULL DEFAULT '4h',  -- '1h', '4h', '12h', '24h'
    is_enabled      BOOLEAN NOT NULL DEFAULT true,
    priority        INTEGER NOT NULL DEFAULT 0,     -- Higher = preferred for pricing
    config          JSONB NOT NULL DEFAULT '{}',    -- Feed-specific settings
    product_count   INTEGER NOT NULL DEFAULT 0,
    last_sync_at    TIMESTAMPTZ,
    last_sync_status VARCHAR(20),                   -- 'success', 'partial', 'failed'
    last_error      TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_feeds_provider ON feeds(provider_id);
CREATE INDEX idx_feeds_enabled ON feeds(is_enabled) WHERE is_enabled = true;
CREATE INDEX idx_feeds_next_sync ON feeds(sync_frequency, last_sync_at);
```

### 3.3 `field_mappings` — Configurable field mapping per feed/provider

```sql
CREATE TABLE field_mappings (
    id              SERIAL PRIMARY KEY,
    provider_id     INTEGER REFERENCES providers(id),
    feed_id         INTEGER REFERENCES feeds(id),     -- NULL = provider-wide default
    source_field    VARCHAR(255) NOT NULL,             -- Field name in provider data
    target_field    VARCHAR(100) NOT NULL,             -- Our canonical field name
    transform       VARCHAR(50),                       -- 'lowercase', 'strip_html', 'parse_price', 'regex', etc.
    transform_args  JSONB,                             -- Arguments for transform
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    -- Feed-level overrides provider-level; unique per scope+source
    UNIQUE(provider_id, feed_id, source_field)
);

CREATE INDEX idx_field_mappings_provider ON field_mappings(provider_id);
CREATE INDEX idx_field_mappings_feed ON field_mappings(feed_id);
```

**Canonical target fields:**

| Target Field | Type | Description |
|-------------|------|-------------|
| `name` | string | Product name |
| `brand` | string | Brand name |
| `model` | string | Model name |
| `colorway` | string | Color variant |
| `ean` | string | EAN/GTIN barcode |
| `sku` | string | Merchant SKU |
| `price` | decimal | Current price |
| `original_price` | decimal | RRP / was-price |
| `currency` | string | ISO 4217 |
| `product_url` | string | Affiliate deep link |
| `image_url` | string | Product image |
| `description` | text | Product description |
| `category` | string | Product category |
| `size` | string | Available sizes |
| `in_stock` | boolean | Availability |

### 3.4 `sync_logs` — Audit trail for every sync run

```sql
CREATE TABLE sync_logs (
    id              SERIAL PRIMARY KEY,
    feed_id         INTEGER NOT NULL REFERENCES feeds(id),
    started_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    finished_at     TIMESTAMPTZ,
    status          VARCHAR(20) NOT NULL DEFAULT 'running', -- 'running','success','partial','failed'
    products_fetched    INTEGER DEFAULT 0,
    products_new        INTEGER DEFAULT 0,
    products_updated    INTEGER DEFAULT 0,
    products_removed    INTEGER DEFAULT 0,
    products_matched    INTEGER DEFAULT 0,    -- Matched to existing canonical products
    errors_count        INTEGER DEFAULT 0,
    error_details       JSONB,                -- Array of {row, field, message}
    metadata            JSONB                  -- Timing, memory, API calls made
);

CREATE INDEX idx_sync_logs_feed ON sync_logs(feed_id, started_at DESC);
CREATE INDEX idx_sync_logs_status ON sync_logs(status) WHERE status = 'failed';
```

### 3.5 `products` — Canonical product catalog (additions to existing schema)

```sql
-- Assuming products table exists; these are the additions needed:
ALTER TABLE products ADD COLUMN IF NOT EXISTS ean VARCHAR(20);
ALTER TABLE products ADD COLUMN IF NOT EXISTS gtin VARCHAR(20);
ALTER TABLE products ADD COLUMN IF NOT EXISTS brand_normalized VARCHAR(100);
ALTER TABLE products ADD COLUMN IF NOT EXISTS model_normalized VARCHAR(255);
ALTER TABLE products ADD COLUMN IF NOT EXISTS colorway_normalized VARCHAR(100);
ALTER TABLE products ADD COLUMN IF NOT EXISTS match_fingerprint VARCHAR(64); -- hash for fuzzy matching

CREATE INDEX idx_products_ean ON products(ean) WHERE ean IS NOT NULL;
CREATE INDEX idx_products_gtin ON products(gtin) WHERE gtin IS NOT NULL;
CREATE INDEX idx_products_fingerprint ON products(match_fingerprint);
CREATE INDEX idx_products_brand_model ON products(brand_normalized, model_normalized);
```

### 3.6 `product_offers` — Per-feed pricing (price comparison core)

```sql
CREATE TABLE product_offers (
    id              SERIAL PRIMARY KEY,
    product_id      INTEGER NOT NULL REFERENCES products(id),
    feed_id         INTEGER NOT NULL REFERENCES feeds(id),
    external_id     VARCHAR(255),               -- Product ID in provider's feed
    price           DECIMAL(10,2) NOT NULL,
    original_price  DECIMAL(10,2),
    currency        VARCHAR(3) NOT NULL DEFAULT 'EUR',
    product_url     TEXT NOT NULL,               -- Affiliate link
    in_stock        BOOLEAN DEFAULT true,
    last_seen_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    
    UNIQUE(feed_id, external_id)
);

CREATE INDEX idx_offers_product ON product_offers(product_id);
CREATE INDEX idx_offers_feed ON product_offers(feed_id);
CREATE INDEX idx_offers_stale ON product_offers(last_seen_at);
```

---

## 4. Provider Adapter Interface

```typescript
// src/lib/feeds/types.ts

interface FeedProvider {
    readonly slug: string;
    
    /** Authenticate with provider API */
    authenticate(config: ProviderConfig): Promise<void>;
    
    /** List available feeds from this provider */
    listFeeds(): Promise<ExternalFeed[]>;
    
    /** Fetch all products from a specific feed */
    fetchProducts(feedId: string, options?: FetchOptions): AsyncGenerator<RawProduct[]>;
    
    /** Get feed metadata (product count, last update) */
    getFeedInfo(feedId: string): Promise<FeedInfo>;
}

interface ExternalFeed {
    externalId: string;
    name: string;
    merchantName: string;
    productCount: number;
    lastUpdated: Date | null;
    feedUrl?: string;
}

interface RawProduct {
    /** Raw key-value pairs from the provider */
    [key: string]: string | number | boolean | null;
}

interface FetchOptions {
    offset?: number;
    limit?: number;
    modifiedSince?: Date;
}

interface FeedInfo {
    productCount: number;
    lastUpdated: Date | null;
    categories?: string[];
}

interface ProviderConfig {
    [key: string]: any;
}
```

### 4.1 Webgains Adapter

```typescript
// src/lib/feeds/adapters/webgains.ts

class WebgainsAdapter implements FeedProvider {
    readonly slug = 'webgains';
    private apiClient: AxiosInstance;
    private accessToken: string;

    async authenticate(config: ProviderConfig): Promise<void> {
        // OAuth2 client credentials flow
        const response = await axios.post('https://platform-api.webgains.com/token', {
            grant_type: 'client_credentials',
            client_id: config.client_id,
            client_secret: config.client_secret
        });
        this.accessToken = response.data.access_token;
        this.apiClient = axios.create({
            baseURL: 'https://platform-api.webgains.com',
            headers: { Authorization: `Bearer ${this.accessToken}` }
        });
    }

    async listFeeds(): Promise<ExternalFeed[]> {
        const { data } = await this.apiClient.get('/feeds');
        return data.map((f: any) => ({
            externalId: String(f.id),
            name: f.name,
            merchantName: f.merchant_name,
            productCount: f.product_count,
            lastUpdated: f.last_updated ? new Date(f.last_updated) : null,
            feedUrl: f.download_url
        }));
    }

    async *fetchProducts(feedId: string): AsyncGenerator<RawProduct[]> {
        // Webgains feeds are typically CSV/XML downloads
        // Stream and parse in chunks of 1000
        const feedUrl = await this.getFeedDownloadUrl(feedId);
        const stream = await this.streamFeed(feedUrl);
        
        let batch: RawProduct[] = [];
        for await (const row of stream) {
            batch.push(row);
            if (batch.length >= 1000) {
                yield batch;
                batch = [];
            }
        }
        if (batch.length > 0) yield batch;
    }
}
```

### 4.2 TradeTracker Adapter

TradeTracker uses a **SOAP API** at `https://ws.tradetracker.com/soap/affiliate?wsdl`.

```typescript
// src/lib/feeds/adapters/tradetracker.ts

import * as soap from 'soap';

class TradeTrackerAdapter implements FeedProvider {
    readonly slug = 'tradetracker';
    private client: soap.Client;
    private authenticated = false;
    private customerId: number;
    private affiliateSiteId: number;

    async authenticate(config: ProviderConfig): Promise<void> {
        this.customerId = config.customer_id;    // 35058
        this.affiliateSiteId = config.affiliate_site_id;
        
        this.client = await soap.createClientAsync(config.wsdl_url);
        
        // SOAP authenticate call
        await this.client.authenticateAsync({
            customerID: this.customerId,
            passphrase: config.access_key,       // ***REDACTED***
            sandbox: false,
            locale: config.locale || 'nl_NL',
            demo: false
        });
        this.authenticated = true;
    }

    async listFeeds(): Promise<ExternalFeed[]> {
        // getFeeds(affiliateSiteID, options: FeedFilter)
        const [result] = await this.client.getFeedsAsync(
            this.affiliateSiteId,
            { assignmentStatus: 'accepted' }
        );
        
        return result.feeds.map((f: any) => ({
            externalId: String(f.ID),
            name: f.name,
            merchantName: f.campaign?.name || f.name,
            productCount: Number(f.productCount),
            lastUpdated: f.updateDate ? new Date(f.updateDate) : null,
            feedUrl: f.URL || null
        }));
    }

    async *fetchProducts(feedId: string, options?: FetchOptions): AsyncGenerator<RawProduct[]> {
        const PAGE_SIZE = 100;  // TradeTracker max per call
        let offset = 0;
        let hasMore = true;

        while (hasMore) {
            const [result] = await this.client.getFeedProductsAsync(
                this.affiliateSiteId,
                {
                    feedID: parseInt(feedId),
                    limit: PAGE_SIZE,
                    offset: offset
                }
            );

            const products = result.feedProducts || [];
            if (products.length === 0) {
                hasMore = false;
                break;
            }

            // Map SOAP response to flat RawProduct
            const batch: RawProduct[] = products.map((p: any) => ({
                identifier: p.identifier,
                name: p.name,
                productCategoryName: p.productCategoryName,
                description: p.description,
                price: p.price,
                productURL: p.productURL,
                imageURL: p.imageURL,
                // Flatten additional fields
                ...this.flattenAdditional(p.additional)
            }));

            yield batch;
            offset += PAGE_SIZE;
            
            if (products.length < PAGE_SIZE) hasMore = false;
        }
    }

    private flattenAdditional(additional: any[]): Record<string, string> {
        if (!additional) return {};
        const result: Record<string, string> = {};
        for (const item of additional) {
            result[`additional_${item.name}`] = item.value;
        }
        return result;
    }

    async getFeedInfo(feedId: string): Promise<FeedInfo> {
        // Can also get categories
        const [catResult] = await this.client.getFeedCategoriesAsync(
            this.affiliateSiteId,
            { feedID: parseInt(feedId) }
        );
        
        const categories = (catResult.feedCategories || []).map((c: any) => c.name);
        const totalProducts = (catResult.feedCategories || [])
            .reduce((sum: number, c: any) => sum + Number(c.productCount), 0);
        
        return {
            productCount: totalProducts,
            lastUpdated: null,
            categories
        };
    }
}
```

#### TradeTracker SOAP API Reference (Key Methods)

| Method | Parameters | Returns | Description |
|--------|-----------|---------|-------------|
| `authenticate` | customerID, passphrase, sandbox, locale, demo | void | Establish session |
| `getFeeds` | affiliateSiteID, FeedFilter | Feed[] | List available feeds |
| `getFeedProducts` | affiliateSiteID, FeedProductFilter | FeedProduct[] | Get products from feed |
| `getFeedCategories` | affiliateSiteID, FeedFilter | FeedCategory[] | Get feed categories |

**FeedProduct fields from SOAP:**
- `identifier` — Merchant's product ID
- `name` — Product name
- `productCategoryName` — Category
- `description` — Product description
- `price` — Decimal price
- `productURL` — Affiliate tracking URL
- `imageURL` — Product image
- `additional[]` — Key-value pairs (brand, EAN, color, size, etc.)

**FeedProductFilter:**
- `feedID` — Filter by specific feed
- `campaignID` — Filter by campaign
- `feedCategoryName` — Filter by category
- `query` — Search query
- `priceFrom` / `priceTo` — Price range
- `limit` / `offset` — Pagination (max 100)

### 4.3 Awin Adapter

```typescript
// src/lib/feeds/adapters/awin.ts

class AwinAdapter implements FeedProvider {
    readonly slug = 'awin';
    private apiToken: string;
    private publisherId: string;

    async authenticate(config: ProviderConfig): Promise<void> {
        this.apiToken = config.api_token;
        this.publisherId = config.publisher_id;
    }

    async listFeeds(): Promise<ExternalFeed[]> {
        // Awin Product Feed API: GET /publishers/{publisherId}/programmes
        const response = await fetch(
            `https://api.awin.com/publishers/${this.publisherId}/programmedetails`,
            { headers: { Authorization: `Bearer ${this.apiToken}` } }
        );
        const programmes = await response.json();
        
        // Filter for programmes with product feeds
        return programmes
            .filter((p: any) => p.hasProductFeed)
            .map((p: any) => ({
                externalId: String(p.id),
                name: p.name,
                merchantName: p.name,
                productCount: p.productFeedCount || 0,
                lastUpdated: null,
                feedUrl: p.productFeedUrl
            }));
    }

    async *fetchProducts(feedId: string): AsyncGenerator<RawProduct[]> {
        // Awin feeds are typically downloadable CSV/XML via datafeed URL
        // GET /publishers/{publisherId}/product-feeds/{feedId}/products
        const feedUrl = `https://productdata.awin.com/datafeed/list/apikey/${this.apiToken}/language/nl/fid/${feedId}/format/csv/`;
        
        const stream = await this.streamCsvFeed(feedUrl);
        let batch: RawProduct[] = [];
        for await (const row of stream) {
            batch.push(row);
            if (batch.length >= 1000) {
                yield batch;
                batch = [];
            }
        }
        if (batch.length > 0) yield batch;
    }
}
```

---

## 5. Default Field Mappings

### Webgains → Canonical

| Source Field | Target Field | Transform |
|-------------|-------------|-----------|
| `product_name` | `name` | — |
| `brand_name` | `brand` | — |
| `model_number` | `model` | — |
| `colour` | `colorway` | `lowercase` |
| `ean` | `ean` | `strip_whitespace` |
| `price` | `price` | `parse_price` |
| `rrp` | `original_price` | `parse_price` |
| `currency` | `currency` | `uppercase` |
| `deep_link` | `product_url` | — |
| `image_url` | `image_url` | — |
| `description` | `description` | `strip_html` |
| `category_name` | `category` | — |
| `in_stock` | `in_stock` | `parse_boolean` |

### TradeTracker → Canonical

| Source Field | Target Field | Transform |
|-------------|-------------|-----------|
| `name` | `name` | — |
| `additional_brand` | `brand` | — |
| `additional_EAN` | `ean` | `strip_whitespace` |
| `additional_color` | `colorway` | `lowercase` |
| `additional_size` | `size` | — |
| `price` | `price` | `parse_price` |
| `additional_fromPrice` | `original_price` | `parse_price` |
| `productURL` | `product_url` | — |
| `imageURL` | `image_url` | — |
| `description` | `description` | `strip_html` |
| `productCategoryName` | `category` | — |

### Awin → Canonical

| Source Field | Target Field | Transform |
|-------------|-------------|-----------|
| `product_name` | `name` | — |
| `brand_name` | `brand` | — |
| `model_number` | `model` | — |
| `colour` | `colorway` | `lowercase` |
| `ean` | `ean` | `strip_whitespace` |
| `search_price` | `price` | `parse_price` |
| `rrp_price` | `original_price` | `parse_price` |
| `currency` | `currency` | `uppercase` |
| `aw_deep_link` | `product_url` | — |
| `aw_image_url` | `image_url` | — |
| `description` | `description` | `strip_html` |
| `merchant_category` | `category` | — |
| `in_stock` | `in_stock` | `parse_boolean` |
| `aw_product_id` | `sku` | — |

---

## 6. Sync Pipeline Flow

```mermaid
sequenceDiagram
    participant S as Scheduler
    participant Q as Job Queue
    participant P as Pipeline
    participant A as Adapter
    participant M as Mapper
    participant N as Normalizer
    participant D as Matcher/Dedup
    participant DB as Database

    S->>Q: Enqueue feed sync job (feed_id=42)
    Q->>P: Start pipeline
    P->>DB: Create sync_log (status=running)
    P->>A: authenticate()
    P->>A: fetchProducts(feed_id)
    
    loop For each batch (1000 products)
        A-->>P: yield RawProduct[]
        P->>M: applyMappings(batch)
        M-->>P: MappedProduct[]
        P->>N: normalize(batch)
        N-->>P: NormalizedProduct[]
        P->>D: matchAndDedup(batch)
        D->>DB: SELECT by EAN/fingerprint
        D->>DB: UPSERT product_offers
        D->>DB: INSERT new products
        D-->>P: stats (new, updated, matched)
    end

    P->>DB: Mark stale offers (not seen this sync)
    P->>DB: Update sync_log (status=success, stats)
    P->>DB: Update feed (last_sync_at, product_count)
```

### Pipeline Steps in Detail

#### Step 1: Fetch
- Adapter streams products in batches via `AsyncGenerator`
- Handles pagination, rate limiting, retries at the adapter level
- Timeout per feed: configurable (default 30 min)

#### Step 2: Field Mapping
```typescript
function applyMappings(raw: RawProduct, mappings: FieldMapping[]): MappedProduct {
    const result: Partial<MappedProduct> = {};
    
    for (const mapping of mappings) {
        const sourceValue = raw[mapping.source_field];
        if (sourceValue == null) continue;
        
        let value = String(sourceValue);
        
        // Apply transform
        if (mapping.transform) {
            value = applyTransform(value, mapping.transform, mapping.transform_args);
        }
        
        result[mapping.target_field] = value;
    }
    
    return result as MappedProduct;
}
```

#### Step 3: Normalization
- Trim all string fields
- Validate and normalize prices (handle comma decimals: `49,99` → `49.99`)
- Validate URLs (must start with http/https)
- Normalize brand names (brand alias table: `Nike, NIKE, nike` → `Nike`)
- Generate `brand_normalized` and `model_normalized` (lowercase, stripped)
- Validate EAN checksum (EAN-13)

#### Step 4: Product Matching (see §7)

#### Step 5: Upsert
- `product_offers`: UPSERT on `(feed_id, external_id)`
- Update `last_seen_at` for matched offers
- After sync: mark offers with `last_seen_at < sync_start` as out of stock

---

## 7. Product Matching Algorithm

Product matching is the core challenge: the same sneaker appears in multiple feeds with different naming conventions.

### Matching Strategy (Waterfall)

```
┌─────────────────────────┐
│  1. Exact EAN/GTIN Match │  ← Highest confidence
│     Confidence: 100%     │
└───────────┬─────────────┘
            │ no match
┌───────────▼─────────────┐
│  2. Brand + Exact Model  │  ← e.g. "Nike" + "Air Max 90"
│     Confidence: 95%      │
└───────────┬─────────────┘
            │ no match
┌───────────▼─────────────┐
│  3. Brand + Fuzzy Model  │  ← Levenshtein + token overlap
│     + Colorway           │
│     Threshold: 85%       │
└───────────┬─────────────┘
            │ no match
┌───────────▼─────────────┐
│  4. Create New Product   │
└─────────────────────────┘
```

### Matching Implementation

```typescript
interface MatchResult {
    productId: number | null;
    confidence: number;
    matchType: 'ean' | 'exact_model' | 'fuzzy' | 'new';
}

async function matchProduct(product: NormalizedProduct, db: Database): Promise<MatchResult> {
    // 1. EAN match
    if (product.ean) {
        const match = await db.query(
            'SELECT id FROM products WHERE ean = $1 OR gtin = $1', 
            [product.ean]
        );
        if (match.rows.length > 0) {
            return { productId: match.rows[0].id, confidence: 1.0, matchType: 'ean' };
        }
    }

    // 2. Exact brand + model match
    if (product.brand_normalized && product.model_normalized) {
        const match = await db.query(
            `SELECT id FROM products 
             WHERE brand_normalized = $1 AND model_normalized = $2`,
            [product.brand_normalized, product.model_normalized]
        );
        if (match.rows.length === 1) {
            return { productId: match.rows[0].id, confidence: 0.95, matchType: 'exact_model' };
        }
    }

    // 3. Fuzzy match: brand + model + colorway fingerprint
    const fingerprint = generateFingerprint(product);
    const candidates = await db.query(
        `SELECT id, brand_normalized, model_normalized, colorway_normalized, match_fingerprint 
         FROM products 
         WHERE brand_normalized = $1 
         ORDER BY similarity(model_normalized, $2) DESC 
         LIMIT 10`,
        [product.brand_normalized, product.model_normalized]
    );

    for (const candidate of candidates.rows) {
        const score = computeSimilarity(product, candidate);
        if (score >= 0.85) {
            return { productId: candidate.id, confidence: score, matchType: 'fuzzy' };
        }
    }

    // 4. No match → new product
    return { productId: null, confidence: 0, matchType: 'new' };
}

function generateFingerprint(product: NormalizedProduct): string {
    // Deterministic hash of normalized brand + model + colorway
    const input = [
        product.brand_normalized,
        product.model_normalized,
        product.colorway_normalized
    ].filter(Boolean).join('|');
    
    return crypto.createHash('sha256').update(input).digest('hex').slice(0, 16);
}

function computeSimilarity(a: NormalizedProduct, b: any): number {
    const modelScore = tokenOverlap(a.model_normalized, b.model_normalized);
    const colorScore = a.colorway_normalized && b.colorway_normalized
        ? tokenOverlap(a.colorway_normalized, b.colorway_normalized)
        : 0.5; // neutral if missing
    
    // Weighted: model matters more than colorway
    return modelScore * 0.7 + colorScore * 0.3;
}

function tokenOverlap(a: string, b: string): number {
    const tokensA = new Set(a.toLowerCase().split(/[\s\-\/]+/));
    const tokensB = new Set(b.toLowerCase().split(/[\s\-\/]+/));
    const intersection = new Set([...tokensA].filter(t => tokensB.has(t)));
    const union = new Set([...tokensA, ...tokensB]);
    return intersection.size / union.size; // Jaccard similarity
}
```

### Sneaker-Specific Normalization

```typescript
const BRAND_ALIASES: Record<string, string> = {
    'nike': 'Nike', 'NIKE': 'Nike',
    'adidas': 'adidas', 'Adidas': 'adidas', 'ADIDAS': 'adidas',
    'new balance': 'New Balance', 'new-balance': 'New Balance', 'NB': 'New Balance',
    'asics': 'ASICS', 'Asics': 'ASICS',
    'puma': 'PUMA', 'Puma': 'PUMA',
    'converse': 'Converse',
    'reebok': 'Reebok',
    'vans': 'Vans',
};

function extractModelFromName(name: string, brand: string): string {
    // Remove brand from name to get model
    let model = name.replace(new RegExp(brand, 'gi'), '').trim();
    // Remove size info, color codes, "men's", "women's"
    model = model.replace(/\b(maat|size|eu|us|uk)\s*\d+/gi, '');
    model = model.replace(/\b(heren|damen|men'?s?|women'?s?|unisex)\b/gi, '');
    return model.trim();
}
```

---

## 8. Sync Scheduling

### Cron Configuration

Each feed has a `sync_frequency` field. The scheduler checks every minute for feeds due for sync:

```typescript
// Frequency to cron expression mapping
const FREQ_MAP: Record<string, number> = {
    '1h': 60 * 60 * 1000,
    '4h': 4 * 60 * 60 * 1000,
    '12h': 12 * 60 * 60 * 1000,
    '24h': 24 * 60 * 60 * 1000,
};

async function getOverdueFeeds(db: Database): Promise<Feed[]> {
    return db.query(`
        SELECT f.*, p.slug as provider_slug, p.config as provider_config
        FROM feeds f
        JOIN providers p ON f.provider_id = p.id
        WHERE f.is_enabled = true
          AND p.is_active = true
          AND (
            f.last_sync_at IS NULL
            OR (
              CASE f.sync_frequency
                WHEN '1h'  THEN f.last_sync_at < NOW() - INTERVAL '1 hour'
                WHEN '4h'  THEN f.last_sync_at < NOW() - INTERVAL '4 hours'
                WHEN '12h' THEN f.last_sync_at < NOW() - INTERVAL '12 hours'
                WHEN '24h' THEN f.last_sync_at < NOW() - INTERVAL '24 hours'
              END
            )
          )
        ORDER BY f.last_sync_at ASC NULLS FIRST
        LIMIT 5
    `);
}
```

### Concurrency Controls

- Max **3 concurrent syncs** globally (to avoid API rate limits)
- Max **1 sync per provider** at a time
- Stale lock detection: if a sync_log is `running` for >60 min, mark as `failed`

---

## 9. Error Handling & Retry Logic

### Error Categories

| Category | Retry? | Max Retries | Backoff |
|----------|--------|-------------|---------|
| **Auth failure** | Yes | 3 | Exponential (1m, 5m, 15m) |
| **Rate limited (429)** | Yes | 5 | Respect Retry-After header |
| **Network timeout** | Yes | 3 | Exponential (30s, 2m, 10m) |
| **Invalid data (single row)** | Skip row | — | Log and continue |
| **Feed empty / 0 products** | No | — | Alert, don't delete existing |
| **Provider API down (5xx)** | Yes | 3 | Exponential (5m, 15m, 60m) |

### Alerting

- Feed fails **3 consecutive times** → Mark feed as `last_sync_status = 'failed'`
- Any provider has **0 successful syncs in 24h** → Alert via webhook/email
- Product count drops **>50%** vs previous sync → Alert (possible feed issue)

```typescript
async function checkAlerts(feedId: number, syncLog: SyncLog): Promise<void> {
    if (syncLog.status === 'failed') {
        const consecutiveFailures = await db.query(`
            SELECT COUNT(*) FROM (
                SELECT status FROM sync_logs 
                WHERE feed_id = $1 
                ORDER BY started_at DESC LIMIT 3
            ) sub WHERE status = 'failed'
        `, [feedId]);
        
        if (consecutiveFailures.rows[0].count >= 3) {
            await sendAlert({
                level: 'critical',
                message: `Feed ${feedId} has failed 3 consecutive syncs`,
                details: syncLog.error_details
            });
        }
    }
    
    // Check for suspicious product count drop
    if (syncLog.status === 'success' && syncLog.products_fetched > 0) {
        const previousCount = await db.query(
            'SELECT product_count FROM feeds WHERE id = $1', [feedId]
        );
        const prev = previousCount.rows[0]?.product_count || 0;
        if (prev > 100 && syncLog.products_fetched < prev * 0.5) {
            await sendAlert({
                level: 'warning',
                message: `Feed ${feedId}: product count dropped from ${prev} to ${syncLog.products_fetched}`
            });
        }
    }
}
```

---

## 10. API Endpoints

### Feed Management

| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/admin/providers` | List all providers |
| `GET` | `/api/admin/providers/:slug/available-feeds` | List feeds available from provider (calls adapter) |
| `GET` | `/api/admin/feeds` | List registered feeds (with stats) |
| `POST` | `/api/admin/feeds` | Register a new feed |
| `PATCH` | `/api/admin/feeds/:id` | Update feed (enable/disable, frequency) |
| `DELETE` | `/api/admin/feeds/:id` | Remove a feed |
| `POST` | `/api/admin/feeds/:id/sync` | Trigger immediate sync |
| `GET` | `/api/admin/feeds/:id/logs` | Get sync history |
| `GET` | `/api/admin/feeds/:id/logs/:logId` | Get sync log detail (with errors) |

### Field Mappings

| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/admin/providers/:slug/mappings` | Get provider default mappings |
| `PUT` | `/api/admin/providers/:slug/mappings` | Update provider mappings |
| `GET` | `/api/admin/feeds/:id/mappings` | Get feed-specific overrides |
| `PUT` | `/api/admin/feeds/:id/mappings` | Update feed-specific overrides |

### Dashboard Data

| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/admin/dashboard/overview` | Total products, per-provider counts, active feeds |
| `GET` | `/api/admin/dashboard/sync-status` | Last sync per feed, errors, next scheduled |
| `GET` | `/api/admin/dashboard/health` | Provider health, failed feeds, alerts |

### Example Responses

```json
// GET /api/admin/feeds
{
    "feeds": [
        {
            "id": 1,
            "provider": "webgains",
            "name": "Nike NL Official",
            "merchant_name": "Nike",
            "external_id": "12345",
            "sync_frequency": "4h",
            "is_enabled": true,
            "product_count": 3420,
            "last_sync_at": "2026-02-19T14:00:00Z",
            "last_sync_status": "success",
            "last_error": null
        }
    ],
    "total": 27
}

// GET /api/admin/dashboard/overview
{
    "total_products": 82450,
    "total_canonical_products": 15230,
    "total_offers": 82450,
    "by_provider": {
        "webgains": { "feeds": 27, "products": 80120 },
        "tradetracker": { "feeds": 0, "products": 0 },
        "awin": { "feeds": 0, "products": 0 }
    },
    "recent_syncs": {
        "last_24h": 42,
        "successful": 40,
        "failed": 2
    }
}
```

---

## 11. TradeTracker Integration Details

### Authentication Flow

```
1. Create SOAP client from WSDL: https://ws.tradetracker.com/soap/affiliate?wsdl
2. Call authenticate(customerID=35058, passphrase=<access_key>, sandbox=false, locale='nl_NL', demo=false)
3. Session is maintained via SOAP cookies (persist the HTTP session)
```

### Feed Discovery

```
1. First get affiliate site ID: getAffiliateSites()
2. Then list feeds: getFeeds(affiliateSiteID, {assignmentStatus: 'accepted'})
3. Each Feed has: ID, campaign, name, URL, updateDate, updateInterval, productCount
```

### Product Fetching

```
1. getFeedProducts(affiliateSiteID, filter) — paginate with limit/offset (max 100)
2. FeedProduct structure:
   - identifier: string (merchant product ID)
   - name: string
   - productCategoryName: string
   - description: string
   - price: decimal
   - productURL: string (tracking URL)
   - imageURL: string
   - additional: Array<{name, value}> — contains brand, EAN, color, size, etc.
3. Additional fields vary per merchant — map dynamically
```

### Feed URL Alternative

TradeTracker feeds also have a direct download `URL` field on the Feed object. For large feeds, downloading the XML/CSV directly may be faster than paginating through SOAP:

```typescript
// If feed.URL is available, prefer direct download for performance
if (feed.URL) {
    return this.streamDirectFeed(feed.URL);
} else {
    return this.paginateSoapApi(feed.ID);
}
```

### Rate Limits

TradeTracker SOAP API doesn't document explicit rate limits, but based on experience:
- Keep requests < 10/second
- Use direct feed URLs for bulk fetches when available
- Cache `getFeeds()` results (they change infrequently)

---

## 12. Implementation Plan

### Phase 1: Core Infrastructure (Week 1-2)
- [ ] Database migrations (providers, feeds, field_mappings, sync_logs, product_offers)
- [ ] Provider adapter interface + Webgains adapter (port existing)
- [ ] Field mapping engine
- [ ] Basic sync pipeline

### Phase 2: TradeTracker Integration (Week 3)
- [ ] TradeTracker SOAP adapter
- [ ] Test with real feeds (authenticate, list, fetch products)
- [ ] Configure field mappings for TradeTracker feeds

### Phase 3: Product Matching (Week 4)
- [ ] EAN matching
- [ ] Fuzzy brand+model matching
- [ ] Brand alias table
- [ ] Product deduplication across feeds

### Phase 4: Admin API & Dashboard (Week 5)
- [ ] REST API endpoints
- [ ] Feed management UI
- [ ] Sync monitoring dashboard

### Phase 5: Awin + Polish (Week 6)
- [ ] Awin adapter
- [ ] Alerting system
- [ ] Performance optimization (batch inserts, connection pooling)
- [ ] Documentation

---

## 13. Technology Stack

| Component | Technology |
|-----------|-----------|
| Runtime | Node.js (already used by Astro) |
| Database | PostgreSQL (with `pg_trgm` extension for fuzzy matching) |
| SOAP Client | `soap` npm package |
| Job Queue | `pg-boss` (PostgreSQL-backed) or simple cron |
| CSV Parsing | `csv-parse` (streaming) |
| XML Parsing | `fast-xml-parser` |
| HTTP Client | `undici` / `fetch` |
| Admin API | Express or Astro API routes |

### PostgreSQL Extensions Required

```sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;  -- For similarity() function
CREATE EXTENSION IF NOT EXISTS pgcrypto;  -- For gen_random_uuid()
```