# SneakerPicks Admin Panel & Analytics — Technical Design

> **Version:** 1.0 · **Date:** 2026-02-19 · **Status:** Draft

---

## Table of Contents

1. [Architecture Overview](#1-architecture-overview)
2. [Admin Panel Pages](#2-admin-panel-pages)
3. [Database Schema](#3-database-schema)
4. [API Endpoints](#4-api-endpoints)
5. [Click Tracking](#5-click-tracking)
6. [Revenue Sync Pipeline](#6-revenue-sync-pipeline)
7. [Analytics & Aggregation](#7-analytics--aggregation)
8. [Performance-based Ranking](#8-performance-based-ranking)
9. [Authentication](#9-authentication)
10. [Implementation Plan](#10-implementation-plan)

---

## 1. Architecture Overview

```
┌─────────────────────────────────────────────────────┐
│  Astro 5 App (sneakerpicks.nl)                      │
│                                                     │
│  Public Pages (SSG/SSR)    Admin SPA (/admin/*)     │
│  ┌──────────────────┐      ┌──────────────────────┐ │
│  │ Product pages     │      │ React SPA (CSR)      │ │
│  │ Search / Deals    │      │ - Dashboard          │ │
│  │ Collections       │      │ - Homepage Manager   │ │
│  │                   │      │ - Feed Manager       │ │
│  │ Click tracking ───┼──┐   │ - Product Manager    │ │
│  │ (client-side)     │  │   │ - Collection Manager │ │
│  └──────────────────┘  │   │ - Analytics          │ │
│                        │   └───────┬──────────────┘ │
│                        │           │                │
│  ┌─────────────────────┴───────────┴──────────────┐ │
│  │  API Routes (/api/*)                           │ │
│  │  - /api/track/click                            │ │
│  │  - /api/admin/*  (auth-protected)              │ │
│  └──────────────────────┬─────────────────────────┘ │
└─────────────────────────┼───────────────────────────┘
                          │
                          ▼
              ┌───────────────────────┐
              │  PostgreSQL (Supabase) │
              │  - click_events        │
              │  - conversions         │
              │  - daily_stats         │
              │  - homepage_sections   │
              │  - admin_users         │
              └───────────────────────┘
                          ▲
              ┌───────────┴───────────┐
              │  Cron Jobs (VPS)      │
              │  - Revenue sync       │
              │  - Stats aggregation  │
              │  - Product scoring    │
              └───────────────────────┘
```

**Key decisions:**
- Admin panel is a **React SPA** rendered client-side within an Astro page (`/admin/[...path].astro`) — no SSR needed, keeps it simple
- Click tracking via **lightweight client-side POST** to `/api/track/click`
- Revenue sync via **cron jobs** on the VPS (not triggered by user requests)
- Aggregation tables for fast dashboard queries (don't query raw click_events)

---

## 2. Admin Panel Pages

### 2.1 Login (`/admin/login`)

```
┌──────────────────────────────────────┐
│         🔒 SneakerPicks Admin        │
│                                      │
│   Email:    [________________]       │
│   Password: [________________]       │
│                                      │
│          [ Log In ]                  │
└──────────────────────────────────────┘
```

### 2.2 Dashboard (`/admin`)

```
┌──────────────────────────────────────────────────────────┐
│  ☰ SneakerPicks Admin                          [Logout]  │
├────────┬─────────────────────────────────────────────────┤
│        │                                                 │
│  📊   │  ┌──────┐ ┌──────┐ ┌──────┐ ┌───────┐          │
│  Dash  │  │12,432│ │  14  │ │ 847  │ │€1,240 │          │
│        │  │Prod. │ │Feeds │ │Click │ │Rev/mo │          │
│  🏠   │  └──────┘ └──────┘ └──────┘ └───────┘          │
│  Home  │                                                 │
│        │  Clicks (last 30 days)                          │
│  📡   │  ┌─────────────────────────────────────┐        │
│  Feeds │  │  ▂▃▅▇▆▅▃▂▃▅▆▇▆▅▄▃▂▃▅▆▇▅▃▂▃▅▆▇▅▃ │        │
│        │  └─────────────────────────────────────┘        │
│  📦   │                                                 │
│  Prod. │  Revenue (last 30 days)                         │
│        │  ┌─────────────────────────────────────┐        │
│  🏷️   │  │  ▂▃▅▇▆▅▃▂▃▅▆▇▆▅▄▃▂▃▅▆▇▅▃▂▃▅▆▇▅▃ │        │
│  Coll. │  └─────────────────────────────────────┘        │
│        │                                                 │
│  📈   │  Top Products Today        Top Shops Today      │
│  Stats │  1. Nike Dunk Low  (124)   1. Footlocker (312) │
│        │  2. NB 550         (98)    2. JD Sports  (245) │
│        │  3. Adidas Samba   (87)    3. Zalando    (198) │
└────────┴─────────────────────────────────────────────────┘
```

### 2.3 Homepage Manager (`/admin/homepage`)

```
┌─────────────────────────────────────────────────────┐
│  Homepage Sections                    [+ Add Section]│
│                                                      │
│  ≡ 1. Hero Banner                          [Edit] ✕ │
│    ├─ Image: hero-jan-2026.jpg                       │
│    ├─ Title: "Beste sneaker deals van Nederland"     │
│    └─ CTA: "Bekijk deals" → /deals                   │
│                                                      │
│  ≡ 2. Featured Products                   [Edit] ✕ │
│    ├─ Type: Manual selection                         │
│    └─ Products: [Nike Dunk Low, NB 550, ...]         │
│                                                      │
│  ≡ 3. Collection: Bestsellers             [Edit] ✕ │
│    ├─ Type: Smart collection                         │
│    └─ Collection: bestsellers (auto)                 │
│                                                      │
│  ≡ 4. Trending Brands                     [Edit] ✕ │
│    └─ Brands: [Nike, Adidas, New Balance, ...]       │
│                                                      │
│  [Save Order]  [Preview]                             │
└─────────────────────────────────────────────────────┘
```

Sections are **drag/drop reorderable** (use `@dnd-kit/sortable`).

**Section types:**
- `hero` — Banner image + title + CTA
- `featured_products` — Manual product picks
- `collection` — Link to a smart collection
- `brand_grid` — Brand logos/links
- `category_grid` — Category tiles
- `deals` — Latest price drops
- `custom_html` — Freeform content

### 2.4 Feed Manager (`/admin/feeds`)

```
┌─────────────────────────────────────────────────────────────┐
│  Feeds                                                       │
│                                                              │
│  Name          │ Status  │ Products │ Last Sync    │ Actions │
│  ──────────────┼─────────┼──────────┼──────────────┼─────────│
│  Footlocker NL │ ✅ Active│   1,204  │ 2h ago       │ ⟳ ⏸ ✎  │
│  JD Sports     │ ✅ Active│   2,341  │ 1h ago       │ ⟳ ⏸ ✎  │
│  Zalando NL    │ ⚠️ Error │     892  │ 6h ago (!)   │ ⟳ ⏸ ✎  │
│  Sneakerstad   │ ⏸ Paused│     456  │ 3d ago       │ ⟳ ▶ ✎  │
│  TradeTracker  │ ✅ Active│   3,102  │ 30m ago      │ ⟳ ⏸ ✎  │
│                                                              │
│  ⟳ = Force sync   ⏸/▶ = Pause/Resume   ✎ = Edit config     │
└─────────────────────────────────────────────────────────────┘
```

### 2.5 Product Manager (`/admin/products`)

```
┌─────────────────────────────────────────────────────────────┐
│  Products                                                    │
│  [Search: _______________] [Category: All ▾] [Brand: All ▾] │
│                                                              │
│  ☐ │ Image │ Name              │ Brand │ Price │ Shops │ Act │
│  ──┼───────┼───────────────────┼───────┼───────┼───────┼─────│
│  ☐ │ 🖼    │ Nike Dunk Low     │ Nike  │ €109  │  6    │ ✎ 🔗│
│  ☐ │ 🖼    │ NB 550 White Grey │ NB    │ €149  │  4    │ ✎ 🔗│
│  ☐ │ 🖼    │ Adidas Samba OG   │Adidas │ €100  │  8    │ ✎ 🔗│
│                                                              │
│  With selected: [Merge Duplicates] [Set Category] [Delete]   │
│  Page 1 of 124   [< Prev] [Next >]                          │
└─────────────────────────────────────────────────────────────┘
```

**Edit product modal:** name, slug, brand, category, images, description, tags, SEO fields.
**Merge duplicates:** Select 2+ products → pick canonical → merge shop listings.

### 2.6 Collection Manager (`/admin/collections`)

```
┌──────────────────────────────────────────────────────────┐
│  Collections                              [+ New Collection]│
│                                                           │
│  Name          │ Type   │ Products │ Rules                │
│  ──────────────┼────────┼──────────┼──────────────────────│
│  Bestsellers   │ Smart  │   50     │ score > 70           │
│  Nike Dunks    │ Smart  │  124     │ brand=Nike & model~Dunk│
│  Under €100    │ Smart  │  892     │ price < 100          │
│  Staff Picks   │ Manual │   12     │ —                    │
└──────────────────────────────────────────────────────────┘

Edit Smart Collection:
┌──────────────────────────────────────────────────────────┐
│  Name: [Bestsellers          ]                           │
│  Slug: [bestsellers          ]                           │
│                                                          │
│  Rules (ALL must match):                                 │
│  ┌────────────────────────────────────────────────────┐  │
│  │ [performance_score ▾] [greater than ▾] [70    ]  ✕ │  │
│  │ [price             ▾] [less than    ▾] [300   ]  ✕ │  │
│  │ [+ Add Rule]                                       │  │
│  └────────────────────────────────────────────────────┘  │
│                                                          │
│  Sort by: [performance_score ▾] [Desc ▾]                 │
│  Limit: [50]                                             │
│                                                          │
│  Preview: 50 products match  [View]                      │
│  [Save]  [Cancel]                                        │
└──────────────────────────────────────────────────────────┘
```

**Rule fields:** brand, category, price, performance_score, shop_count, clicks_7d, created_at
**Operators:** equals, not_equals, contains, greater_than, less_than, in_list

### 2.7 Analytics (`/admin/analytics`)

```
┌──────────────────────────────────────────────────────────┐
│  Analytics          [Last 7d ▾] [Last 30d] [Custom]      │
│                                                          │
│  ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐          │
│  │5,847 │ │  23  │ │€342  │ │ 0.39%│ │€14.87│          │
│  │Clicks│ │Conv. │ │Rev.  │ │ CTR  │ │ ROAS │          │
│  └──────┘ └──────┘ └──────┘ └──────┘ └──────┘          │
│                                                          │
│  [Clicks] [Revenue] [Conversions] [CTR]  ← tab selector │
│                                                          │
│  ┌──────────────────────────────────────────────────┐    │
│  │                                                  │    │
│  │        Line/bar chart (Recharts)                 │    │
│  │        X = date, Y = metric                      │    │
│  │                                                  │    │
│  └──────────────────────────────────────────────────┘    │
│                                                          │
│  Top Products              │ Top Shops                   │
│  ─────────────────────────┼─────────────────────────────│
│  1. Nike Dunk Low    342c │ 1. Footlocker   €124 rev    │
│  2. NB 550           234c │ 2. JD Sports    €98 rev     │
│                                                          │
│  Source Breakdown          │ Revenue by Network          │
│  ─────────────────────────┼─────────────────────────────│
│  Homepage: 42%             │ Webgains:     €210 (61%)   │
│  Search:   31%             │ TradeTracker: €132 (39%)   │
│  Deals:    18%             │                             │
│  Google:    9%             │                             │
└──────────────────────────────────────────────────────────┘
```

Charts: **Recharts** (lightweight, React-native).

---

## 3. Database Schema

### 3.1 Admin Users

```sql
CREATE TABLE admin_users (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email         TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,  -- bcrypt
  name          TEXT,
  created_at    TIMESTAMPTZ DEFAULT now()
);
```

### 3.2 Click Events

```sql
CREATE TABLE click_events (
  id          BIGSERIAL PRIMARY KEY,
  click_ref   TEXT UNIQUE NOT NULL,       -- unique ref for affiliate matching
  product_id  UUID NOT NULL REFERENCES products(id),
  shop_id     UUID NOT NULL REFERENCES shops(id),
  offer_id    UUID REFERENCES offers(id),
  session_id  TEXT,                        -- anonymous session cookie
  source      TEXT NOT NULL,               -- 'homepage','search','deals','collection','pdp'
  source_detail TEXT,                      -- collection slug, search query, etc.
  position    SMALLINT,                    -- position on page (for CTR calc)
  user_agent  TEXT,
  ip_hash     TEXT,                        -- hashed IP (privacy)
  referrer    TEXT,                        -- external referrer (google, direct, etc)
  utm_source  TEXT,
  utm_medium  TEXT,
  utm_campaign TEXT,
  created_at  TIMESTAMPTZ DEFAULT now()
);

-- Indexes for fast querying
CREATE INDEX idx_click_events_created ON click_events (created_at);
CREATE INDEX idx_click_events_product ON click_events (product_id, created_at);
CREATE INDEX idx_click_events_shop    ON click_events (shop_id, created_at);
CREATE INDEX idx_click_events_ref     ON click_events (click_ref);
CREATE INDEX idx_click_events_source  ON click_events (source, created_at);
```

**click_ref format:** `sp_{timestamp_hex}_{random_6char}` — e.g. `sp_65b2f1a0_x8k2m1`

### 3.3 Conversions

```sql
CREATE TABLE conversions (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  click_ref       TEXT REFERENCES click_events(click_ref),
  network         TEXT NOT NULL,              -- 'webgains' | 'tradetracker'
  network_id      TEXT NOT NULL,              -- transaction ID from network
  status          TEXT DEFAULT 'pending',     -- 'pending','approved','rejected'
  commission      DECIMAL(10,2) NOT NULL,     -- our commission in EUR
  sale_amount     DECIMAL(10,2),              -- total order value
  currency        TEXT DEFAULT 'EUR',
  product_id      UUID REFERENCES products(id),
  shop_id         UUID REFERENCES shops(id),
  click_time      TIMESTAMPTZ,
  conversion_time TIMESTAMPTZ NOT NULL,
  network_data    JSONB,                      -- raw API response
  matched         BOOLEAN DEFAULT false,      -- successfully matched to click?
  created_at      TIMESTAMPTZ DEFAULT now(),
  updated_at      TIMESTAMPTZ DEFAULT now(),
  
  UNIQUE(network, network_id)
);

CREATE INDEX idx_conversions_click_ref ON conversions (click_ref);
CREATE INDEX idx_conversions_time      ON conversions (conversion_time);
CREATE INDEX idx_conversions_status    ON conversions (status);
```

### 3.4 Daily Stats (Aggregation)

```sql
CREATE TABLE daily_stats (
  id          BIGSERIAL PRIMARY KEY,
  date        DATE NOT NULL,
  dimension   TEXT NOT NULL,      -- 'total','product','shop','source','campaign'
  dimension_id TEXT,              -- product_id, shop_id, source name, campaign id
  clicks      INTEGER DEFAULT 0,
  impressions INTEGER DEFAULT 0,  -- if we track impressions later
  conversions INTEGER DEFAULT 0,
  revenue     DECIMAL(10,2) DEFAULT 0,
  ad_spend    DECIMAL(10,2) DEFAULT 0,  -- from Google Ads
  
  UNIQUE(date, dimension, dimension_id)
);

CREATE INDEX idx_daily_stats_date ON daily_stats (date, dimension);
```

### 3.5 Homepage Sections

```sql
CREATE TABLE homepage_sections (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type        TEXT NOT NULL,          -- 'hero','featured_products','collection','brand_grid','custom_html'
  title       TEXT,
  config      JSONB NOT NULL,         -- type-specific config
  sort_order  SMALLINT NOT NULL,
  enabled     BOOLEAN DEFAULT true,
  created_at  TIMESTAMPTZ DEFAULT now(),
  updated_at  TIMESTAMPTZ DEFAULT now()
);

-- config examples:
-- hero:              { "image": "url", "title": "...", "subtitle": "...", "cta_text": "...", "cta_url": "/deals" }
-- featured_products: { "product_ids": ["uuid1", "uuid2", ...], "layout": "grid" }
-- collection:        { "collection_id": "uuid", "limit": 8, "layout": "carousel" }
-- brand_grid:        { "brand_ids": ["uuid1", ...] }
-- custom_html:       { "html": "<div>...</div>" }
```

### 3.6 Smart Collections

```sql
CREATE TABLE collections (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  slug        TEXT UNIQUE NOT NULL,
  type        TEXT NOT NULL,             -- 'smart' | 'manual'
  rules       JSONB,                     -- for smart: array of rule objects
  sort_field  TEXT DEFAULT 'performance_score',
  sort_dir    TEXT DEFAULT 'desc',
  item_limit  INTEGER DEFAULT 50,
  description TEXT,
  seo_title   TEXT,
  seo_desc    TEXT,
  enabled     BOOLEAN DEFAULT true,
  created_at  TIMESTAMPTZ DEFAULT now(),
  updated_at  TIMESTAMPTZ DEFAULT now()
);

-- rules example:
-- [
--   { "field": "brand", "op": "equals", "value": "Nike" },
--   { "field": "price", "op": "less_than", "value": 150 },
--   { "field": "performance_score", "op": "greater_than", "value": 50 }
-- ]

CREATE TABLE collection_products (
  collection_id UUID REFERENCES collections(id) ON DELETE CASCADE,
  product_id    UUID REFERENCES products(id) ON DELETE CASCADE,
  sort_order    SMALLINT,
  PRIMARY KEY (collection_id, product_id)
);
```

### 3.7 Product Score (extension of existing products table)

```sql
ALTER TABLE products ADD COLUMN IF NOT EXISTS performance_score DECIMAL(5,2) DEFAULT 0;
ALTER TABLE products ADD COLUMN IF NOT EXISTS clicks_7d INTEGER DEFAULT 0;
ALTER TABLE products ADD COLUMN IF NOT EXISTS clicks_30d INTEGER DEFAULT 0;
ALTER TABLE products ADD COLUMN IF NOT EXISTS conversions_30d INTEGER DEFAULT 0;
ALTER TABLE products ADD COLUMN IF NOT EXISTS revenue_30d DECIMAL(10,2) DEFAULT 0;
```

---

## 4. API Endpoints

### 4.1 Public (no auth)

| Method | Path | Description |
|--------|------|-------------|
| `POST` | `/api/track/click` | Record outbound click, return redirect URL |

### 4.2 Admin (auth required)

**Dashboard:**
| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/admin/dashboard` | Summary stats |

**Homepage:**
| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/admin/homepage/sections` | List all sections |
| `PUT` | `/api/admin/homepage/sections` | Reorder sections (array of ids) |
| `POST` | `/api/admin/homepage/sections` | Create section |
| `PUT` | `/api/admin/homepage/sections/:id` | Update section |
| `DELETE` | `/api/admin/homepage/sections/:id` | Delete section |

**Feeds:**
| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/admin/feeds` | List feeds + status |
| `PUT` | `/api/admin/feeds/:id` | Update feed config |
| `POST` | `/api/admin/feeds/:id/sync` | Force sync |
| `POST` | `/api/admin/feeds/:id/toggle` | Enable/disable |

**Products:**
| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/admin/products` | List (paginated, filterable) |
| `GET` | `/api/admin/products/:id` | Get product detail |
| `PUT` | `/api/admin/products/:id` | Update product |
| `POST` | `/api/admin/products/merge` | Merge duplicates |
| `DELETE` | `/api/admin/products/:id` | Delete product |

**Collections:**
| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/admin/collections` | List all |
| `POST` | `/api/admin/collections` | Create |
| `PUT` | `/api/admin/collections/:id` | Update |
| `DELETE` | `/api/admin/collections/:id` | Delete |
| `GET` | `/api/admin/collections/:id/preview` | Preview matching products |

**Analytics:**
| Method | Path | Description |
|--------|------|-------------|
| `GET` | `/api/admin/analytics/overview` | KPI cards |
| `GET` | `/api/admin/analytics/timeseries` | Chart data (`?metric=clicks&period=30d`) |
| `GET` | `/api/admin/analytics/top-products` | Top products by clicks/revenue |
| `GET` | `/api/admin/analytics/top-shops` | Top shops |
| `GET` | `/api/admin/analytics/sources` | Traffic source breakdown |
| `GET` | `/api/admin/analytics/revenue` | Revenue by network/shop |

**Auth:**
| Method | Path | Description |
|--------|------|-------------|
| `POST` | `/api/admin/auth/login` | Login → JWT |
| `POST` | `/api/admin/auth/logout` | Invalidate token |
| `GET` | `/api/admin/auth/me` | Current user |

---

## 5. Click Tracking

### 5.1 Flow

```
User clicks "Bekijk deal" button
        │
        ▼
Client JS sends POST /api/track/click
  { product_id, shop_id, offer_id, source, position }
        │
        ▼
API endpoint:
  1. Generate click_ref (sp_{hex_ts}_{rand6})
  2. Insert into click_events
  3. Build affiliate URL with click_ref as subid
  4. Return { redirect_url }
        │
        ▼
Client JS does window.open(redirect_url, '_blank')
```

### 5.2 Client Implementation

```tsx
// components/OutboundLink.tsx
async function handleClick(e: React.MouseEvent, offer: Offer) {
  e.preventDefault();
  
  const res = await fetch('/api/track/click', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
      product_id: offer.product_id,
      shop_id: offer.shop_id,
      offer_id: offer.id,
      source: getPageSource(),        // 'homepage' | 'search' | 'deals' | 'pdp' | 'collection'
      source_detail: getSourceDetail(), // search query, collection slug, etc
      position: offer.position,
    }),
  });
  
  const { redirect_url } = await res.json();
  window.open(redirect_url, '_blank');
}

function getPageSource(): string {
  const path = window.location.pathname;
  if (path === '/') return 'homepage';
  if (path.startsWith('/zoeken')) return 'search';
  if (path.startsWith('/deals')) return 'deals';
  if (path.startsWith('/sneaker/')) return 'pdp';
  if (path.startsWith('/collectie/')) return 'collection';
  return 'other';
}
```

### 5.3 API Endpoint

```ts
// src/pages/api/track/click.ts
export const POST: APIRoute = async ({ request }) => {
  const body = await request.json();
  const clickRef = generateClickRef();
  
  // Insert click
  await db.insert(clickEvents).values({
    click_ref: clickRef,
    product_id: body.product_id,
    shop_id: body.shop_id,
    offer_id: body.offer_id,
    session_id: getSessionId(request),
    source: body.source,
    source_detail: body.source_detail,
    position: body.position,
    user_agent: request.headers.get('user-agent'),
    ip_hash: hashIP(getClientIP(request)),
    referrer: request.headers.get('referer'),
    utm_source: new URL(request.headers.get('referer') || '').searchParams.get('utm_source'),
    utm_medium: /* ... */,
    utm_campaign: /* ... */,
  });
  
  // Build affiliate URL
  const offer = await db.select().from(offers).where(eq(offers.id, body.offer_id)).limit(1);
  const redirectUrl = buildAffiliateUrl(offer, clickRef);
  
  return new Response(JSON.stringify({ redirect_url: redirectUrl }), {
    headers: { 'Content-Type': 'application/json' },
  });
};

function buildAffiliateUrl(offer: Offer, clickRef: string): string {
  // Webgains: append &clickRef=sp_xxx
  // TradeTracker: append &r=sp_xxx (subid parameter)
  const url = new URL(offer.affiliate_url);
  
  if (offer.network === 'webgains') {
    url.searchParams.set('clickRef', clickRef);
  } else if (offer.network === 'tradetracker') {
    url.searchParams.set('r', clickRef);
  }
  
  return url.toString();
}

function generateClickRef(): string {
  const ts = Math.floor(Date.now() / 1000).toString(16);
  const rand = Math.random().toString(36).substring(2, 8);
  return `sp_${ts}_${rand}`;
}
```

### 5.4 Session Tracking

Use a first-party cookie `sp_sid` (set on first visit, 30-day expiry). No personal data, just a random UUID for grouping clicks per visitor.

```ts
function getSessionId(request: Request): string {
  const cookies = parseCookies(request.headers.get('cookie'));
  return cookies.sp_sid || crypto.randomUUID();
  // Set cookie in response if new
}
```

---

## 6. Revenue Sync Pipeline

### 6.1 Overview

```
┌─────────────┐     ┌─────────────┐
│  Webgains   │     │ TradeTracker │
│  API        │     │ API          │
└──────┬──────┘     └──────┬──────┘
       │                   │
       ▼                   ▼
┌──────────────────────────────────┐
│  Revenue Sync Cron (every 2h)   │
│                                  │
│  1. Fetch new conversions        │
│  2. Match click_ref → click_event│
│  3. Upsert into conversions      │
│  4. Update daily_stats           │
│  5. Recalculate product scores   │
└──────────────────────────────────┘
```

Runs on VPS via cron: `0 */2 * * * node /path/to/sync-revenue.mjs`

### 6.2 Webgains Sync

```ts
// scripts/sync-webgains.ts
async function syncWebgains() {
  const API_URL = 'https://api.webgains.com/2.0';
  const token = await getWebgainsToken(); // OAuth2 client_credentials
  
  // Fetch transactions from last 48h (overlap to catch updates)
  const since = new Date(Date.now() - 48 * 3600 * 1000).toISOString();
  
  const res = await fetch(`${API_URL}/reporting/transactions?dateFrom=${since}&dateType=transaction`, {
    headers: { Authorization: `Bearer ${token}` },
  });
  
  const transactions = await res.json();
  
  for (const tx of transactions) {
    await db.insert(conversions).values({
      click_ref: tx.clickRef || null,           // our sp_xxx ref
      network: 'webgains',
      network_id: tx.transactionId,
      status: mapStatus(tx.status),             // 'pending' | 'approved' | 'rejected'
      commission: tx.commission,
      sale_amount: tx.saleValue,
      conversion_time: tx.transactionDate,
      network_data: tx,
      matched: !!tx.clickRef,
    }).onConflictDoUpdate({
      target: [conversions.network, conversions.network_id],
      set: {
        status: mapStatus(tx.status),
        commission: tx.commission,
        updated_at: new Date(),
      },
    });
    
    // Match to product/shop via click_ref
    if (tx.clickRef) {
      const click = await db.select()
        .from(clickEvents)
        .where(eq(clickEvents.click_ref, tx.clickRef))
        .limit(1);
      
      if (click.length) {
        await db.update(conversions)
          .set({
            product_id: click[0].product_id,
            shop_id: click[0].shop_id,
            click_time: click[0].created_at,
            matched: true,
          })
          .where(and(
            eq(conversions.network, 'webgains'),
            eq(conversions.network_id, tx.transactionId),
          ));
      }
    }
  }
}
```

### 6.3 TradeTracker Sync

```ts
// scripts/sync-tradetracker.ts
async function syncTradeTracker() {
  // TradeTracker uses SOAP or REST API
  // Authenticate with customer ID + passphrase
  const client = new TradeTrackerClient({
    customerId: process.env.TT_CUSTOMER_ID,
    passphrase: process.env.TT_PASSPHRASE,
  });
  
  const since = new Date(Date.now() - 48 * 3600 * 1000);
  const transactions = await client.getConversionTransactions({
    registrationDateFrom: since,
  });
  
  for (const tx of transactions) {
    // tx.reference contains our click_ref (passed via `r` param)
    const clickRef = tx.reference || null;
    
    await db.insert(conversions).values({
      click_ref: clickRef,
      network: 'tradetracker',
      network_id: tx.transactionId,
      status: mapTTStatus(tx.transactionStatus),
      commission: tx.commission,
      sale_amount: tx.orderAmount,
      conversion_time: tx.registrationDate,
      network_data: tx,
      matched: !!clickRef,
    }).onConflictDoUpdate({
      target: [conversions.network, conversions.network_id],
      set: {
        status: mapTTStatus(tx.transactionStatus),
        commission: tx.commission,
        updated_at: new Date(),
      },
    });
    
    // Same click_ref matching as Webgains
    if (clickRef) { /* ... same pattern ... */ }
  }
}
```

### 6.4 Combined Runner

```ts
// scripts/sync-revenue.ts
async function main() {
  console.log(`[${new Date().toISOString()}] Starting revenue sync...`);
  
  await Promise.allSettled([
    syncWebgains(),
    syncTradeTracker(),
  ]);
  
  // After sync: update daily_stats
  await aggregateDailyStats();
  
  // Recalculate product scores
  await updateProductScores();
  
  console.log('Revenue sync complete.');
}
```

---

## 7. Analytics & Aggregation

### 7.1 Daily Stats Aggregation

Runs after each revenue sync and also as a separate daily cron at midnight.

```sql
-- Aggregate clicks per product per day
INSERT INTO daily_stats (date, dimension, dimension_id, clicks)
SELECT
  DATE(created_at) as date,
  'product' as dimension,
  product_id::text as dimension_id,
  COUNT(*) as clicks
FROM click_events
WHERE created_at >= CURRENT_DATE - INTERVAL '2 days'
GROUP BY DATE(created_at), product_id
ON CONFLICT (date, dimension, dimension_id)
DO UPDATE SET clicks = EXCLUDED.clicks;

-- Aggregate clicks per shop per day
INSERT INTO daily_stats (date, dimension, dimension_id, clicks)
SELECT
  DATE(created_at), 'shop', shop_id::text, COUNT(*)
FROM click_events
WHERE created_at >= CURRENT_DATE - INTERVAL '2 days'
GROUP BY DATE(created_at), shop_id
ON CONFLICT (date, dimension, dimension_id)
DO UPDATE SET clicks = EXCLUDED.clicks;

-- Aggregate clicks per source per day
INSERT INTO daily_stats (date, dimension, dimension_id, clicks)
SELECT
  DATE(created_at), 'source', source, COUNT(*)
FROM click_events
WHERE created_at >= CURRENT_DATE - INTERVAL '2 days'
GROUP BY DATE(created_at), source
ON CONFLICT (date, dimension, dimension_id)
DO UPDATE SET clicks = EXCLUDED.clicks;

-- Aggregate revenue per product per day
INSERT INTO daily_stats (date, dimension, dimension_id, conversions, revenue)
SELECT
  DATE(conversion_time), 'product', product_id::text,
  COUNT(*), SUM(commission)
FROM conversions
WHERE conversion_time >= CURRENT_DATE - INTERVAL '2 days'
  AND status != 'rejected'
  AND matched = true
GROUP BY DATE(conversion_time), product_id
ON CONFLICT (date, dimension, dimension_id)
DO UPDATE SET
  conversions = EXCLUDED.conversions,
  revenue = EXCLUDED.revenue;

-- Total stats per day
INSERT INTO daily_stats (date, dimension, dimension_id, clicks, conversions, revenue)
SELECT
  d.date, 'total', 'all',
  COALESCE(c.clicks, 0),
  COALESCE(r.conversions, 0),
  COALESCE(r.revenue, 0)
FROM (SELECT generate_series(CURRENT_DATE - 2, CURRENT_DATE, '1 day')::date as date) d
LEFT JOIN (
  SELECT DATE(created_at) as date, COUNT(*) as clicks
  FROM click_events WHERE created_at >= CURRENT_DATE - 2
  GROUP BY DATE(created_at)
) c ON c.date = d.date
LEFT JOIN (
  SELECT DATE(conversion_time) as date, COUNT(*) as conversions, SUM(commission) as revenue
  FROM conversions WHERE conversion_time >= CURRENT_DATE - 2 AND status != 'rejected'
  GROUP BY DATE(conversion_time)
) r ON r.date = d.date
ON CONFLICT (date, dimension, dimension_id)
DO UPDATE SET clicks = EXCLUDED.clicks, conversions = EXCLUDED.conversions, revenue = EXCLUDED.revenue;
```

### 7.2 Dashboard Query

```sql
-- KPI cards
SELECT
  (SELECT COUNT(*) FROM products WHERE active = true) as total_products,
  (SELECT COUNT(*) FROM feeds WHERE enabled = true) as active_feeds,
  (SELECT COALESCE(SUM(clicks), 0) FROM daily_stats
   WHERE date = CURRENT_DATE AND dimension = 'total') as clicks_today,
  (SELECT COALESCE(SUM(revenue), 0) FROM daily_stats
   WHERE date >= DATE_TRUNC('month', CURRENT_DATE) AND dimension = 'total') as revenue_month;
```

### 7.3 Timeseries Query

```sql
-- Clicks per day for last N days
SELECT date, clicks, conversions, revenue
FROM daily_stats
WHERE dimension = 'total'
  AND dimension_id = 'all'
  AND date >= CURRENT_DATE - :days
ORDER BY date;
```

### 7.4 Top Products Query

```sql
SELECT
  p.id, p.name, p.brand, p.slug,
  SUM(ds.clicks) as total_clicks,
  SUM(ds.conversions) as total_conversions,
  SUM(ds.revenue) as total_revenue,
  CASE WHEN SUM(ds.clicks) > 0
    THEN ROUND(SUM(ds.conversions)::numeric / SUM(ds.clicks) * 100, 2)
    ELSE 0 END as conversion_rate
FROM daily_stats ds
JOIN products p ON p.id::text = ds.dimension_id
WHERE ds.dimension = 'product'
  AND ds.date >= CURRENT_DATE - :days
GROUP BY p.id
ORDER BY total_clicks DESC
LIMIT 20;
```

---

## 8. Performance-based Ranking

### 8.1 Scoring Formula

```
performance_score = (
    (normalized_clicks_7d    * 0.30) +
    (normalized_clicks_30d   * 0.15) +
    (normalized_conversions  * 0.30) +
    (normalized_revenue      * 0.15) +
    (normalized_ctr          * 0.10)
) * 100
```

Each metric is **min-max normalized** to 0-1 across all products with >0 clicks.

### 8.2 Score Update Query

```sql
-- Update rolling click counts
UPDATE products p SET
  clicks_7d = COALESCE(s7.clicks, 0),
  clicks_30d = COALESCE(s30.clicks, 0),
  conversions_30d = COALESCE(c30.convs, 0),
  revenue_30d = COALESCE(c30.rev, 0)
FROM products p2
LEFT JOIN (
  SELECT dimension_id, SUM(clicks) as clicks
  FROM daily_stats WHERE dimension = 'product' AND date >= CURRENT_DATE - 7
  GROUP BY dimension_id
) s7 ON s7.dimension_id = p2.id::text
LEFT JOIN (
  SELECT dimension_id, SUM(clicks) as clicks
  FROM daily_stats WHERE dimension = 'product' AND date >= CURRENT_DATE - 30
  GROUP BY dimension_id
) s30 ON s30.dimension_id = p2.id::text
LEFT JOIN (
  SELECT dimension_id, SUM(conversions) as convs, SUM(revenue) as rev
  FROM daily_stats WHERE dimension = 'product' AND date >= CURRENT_DATE - 30
  GROUP BY dimension_id
) c30 ON c30.dimension_id = p2.id::text
WHERE p.id = p2.id;

-- Then calculate normalized score in application code
-- (easier to do normalization in JS than SQL)
```

```ts
async function updateProductScores() {
  const products = await db.select({
    id: productsTable.id,
    clicks_7d: productsTable.clicks_7d,
    clicks_30d: productsTable.clicks_30d,
    conversions_30d: productsTable.conversions_30d,
    revenue_30d: productsTable.revenue_30d,
  }).from(productsTable).where(gt(productsTable.clicks_30d, 0));
  
  if (!products.length) return;
  
  const max = {
    c7:   Math.max(...products.map(p => p.clicks_7d)),
    c30:  Math.max(...products.map(p => p.clicks_30d)),
    conv: Math.max(...products.map(p => p.conversions_30d)),
    rev:  Math.max(...products.map(p => p.revenue_30d)),
  };
  
  for (const p of products) {
    const norm = (v: number, m: number) => m > 0 ? v / m : 0;
    const ctr = p.clicks_30d > 0 ? p.conversions_30d / p.clicks_30d : 0;
    const maxCtr = Math.max(...products.map(x => x.clicks_30d > 0 ? x.conversions_30d / x.clicks_30d : 0));
    
    const score = (
      norm(p.clicks_7d, max.c7) * 0.30 +
      norm(p.clicks_30d, max.c30) * 0.15 +
      norm(p.conversions_30d, max.conv) * 0.30 +
      norm(p.revenue_30d, max.rev) * 0.15 +
      norm(ctr, maxCtr) * 0.10
    ) * 100;
    
    await db.update(productsTable)
      .set({ performance_score: Math.round(score * 100) / 100 })
      .where(eq(productsTable.id, p.id));
  }
}
```

### 8.3 Bestsellers Collection

Auto-populated smart collection:
```json
{
  "name": "Bestsellers",
  "slug": "bestsellers",
  "type": "smart",
  "rules": [
    { "field": "performance_score", "op": "greater_than", "value": 50 },
    { "field": "active", "op": "equals", "value": true }
  ],
  "sort_field": "performance_score",
  "sort_dir": "desc",
  "item_limit": 50
}
```

### 8.4 Google Ads Integration

Export performance data for Google Ads optimization:

```ts
// /api/admin/analytics/ads-export
// Returns CSV with product performance for Google Ads scripts
// Columns: product_id, clicks, conversions, revenue, roas, score
// Google Ads script can use this to adjust bids per product
```

**ROAS Calculation:**

```
ROAS = Revenue (commission) / Ad Spend

Per campaign:
  1. Match utm_campaign from click_events to Google Ads campaign
  2. Sum revenue from matched conversions
  3. Pull ad spend from Google Ads API (or manual import)
  4. ROAS = total_revenue / total_spend
```

```sql
-- ROAS per campaign
SELECT
  ce.utm_campaign,
  COUNT(DISTINCT ce.id) as clicks,
  COUNT(DISTINCT c.id) as conversions,
  COALESCE(SUM(c.commission), 0) as revenue,
  ds.ad_spend,
  CASE WHEN ds.ad_spend > 0
    THEN ROUND(SUM(c.commission) / ds.ad_spend, 2)
    ELSE NULL END as roas
FROM click_events ce
LEFT JOIN conversions c ON c.click_ref = ce.click_ref AND c.status != 'rejected'
LEFT JOIN (
  SELECT dimension_id, SUM(ad_spend) as ad_spend
  FROM daily_stats
  WHERE dimension = 'campaign' AND date >= CURRENT_DATE - :days
  GROUP BY dimension_id
) ds ON ds.dimension_id = ce.utm_campaign
WHERE ce.utm_campaign IS NOT NULL
  AND ce.created_at >= CURRENT_DATE - :days
GROUP BY ce.utm_campaign, ds.ad_spend
ORDER BY revenue DESC;
```

---

## 9. Authentication

### Phase 1: Simple JWT (MVP)

```ts
// Hardcoded admin user (replace with Supabase Auth later)
const ADMIN_EMAIL = process.env.ADMIN_EMAIL;
const ADMIN_PASSWORD_HASH = process.env.ADMIN_PASSWORD_HASH; // bcrypt hash

// POST /api/admin/auth/login
export const POST: APIRoute = async ({ request }) => {
  const { email, password } = await request.json();
  
  if (email !== ADMIN_EMAIL || !await bcrypt.compare(password, ADMIN_PASSWORD_HASH)) {
    return new Response(JSON.stringify({ error: 'Invalid credentials' }), { status: 401 });
  }
  
  const token = jwt.sign({ email, role: 'admin' }, process.env.JWT_SECRET, { expiresIn: '7d' });
  
  return new Response(JSON.stringify({ token }), {
    headers: {
      'Set-Cookie': `admin_token=${token}; HttpOnly; Secure; SameSite=Strict; Path=/; Max-Age=${7*86400}`,
    },
  });
};
```

**Auth middleware** for all `/api/admin/*` routes (except `/api/admin/auth/login`):

```ts
// src/middleware.ts
export const onRequest = defineMiddleware(async ({ request, url }, next) => {
  if (url.pathname.startsWith('/api/admin/') && !url.pathname.includes('/auth/login')) {
    const token = request.headers.get('Authorization')?.replace('Bearer ', '')
      || parseCookies(request.headers.get('cookie')).admin_token;
    
    if (!token) return new Response('Unauthorized', { status: 401 });
    
    try {
      jwt.verify(token, process.env.JWT_SECRET);
    } catch {
      return new Response('Unauthorized', { status: 401 });
    }
  }
  
  return next();
});
```

### Phase 2: Supabase Auth (Later)

Replace JWT with Supabase Auth session management. Add `admin_users` table with RLS policies.

---

## 10. Implementation Plan

### Phase 1: Foundation (Week 1-2)
1. ✅ Database migrations (click_events, conversions, daily_stats, homepage_sections, collections)
2. ✅ Click tracking endpoint (`/api/track/click`)
3. ✅ OutboundLink component integration
4. ✅ Admin auth (JWT + middleware)
5. ✅ Admin shell (sidebar, routing, layout)
6. ✅ Dashboard with live stats

### Phase 2: Content Management (Week 3)
7. Homepage Manager (sections CRUD + drag/drop reorder)
8. Feed Manager (list, status, force sync, toggle)
9. Product Manager (search, edit, merge)
10. Collection Manager (smart rules + manual)

### Phase 3: Revenue & Analytics (Week 4)
11. Webgains sync script
12. TradeTracker sync script
13. Daily stats aggregation cron
14. Analytics page (charts, top products, sources)
15. Product scoring + bestsellers collection

### Phase 4: Optimization (Week 5+)
16. ROAS dashboard per campaign
17. Google Ads export/integration
18. Supabase Auth migration
19. Performance optimization (caching, pagination)

---

## Tech Dependencies

| Package | Purpose |
|---------|---------|
| `recharts` | Charts in analytics dashboard |
| `@dnd-kit/core` + `@dnd-kit/sortable` | Drag/drop for homepage sections |
| `jsonwebtoken` | JWT auth (Phase 1) |
| `bcryptjs` | Password hashing |
| `date-fns` | Date formatting/manipulation |
| `@tanstack/react-query` | Data fetching + caching in admin |
| `react-router-dom` | Client-side routing within admin SPA |
| `react-hot-toast` | Toast notifications |

---

## File Structure

```
src/
├── pages/
│   ├── admin/
│   │   └── [...path].astro        # Admin SPA shell
│   └── api/
│       ├── track/
│       │   └── click.ts           # Click tracking
│       └── admin/
│           ├── auth/
│           │   ├── login.ts
│           │   └── me.ts
│           ├── dashboard.ts
│           ├── homepage/
│           │   └── sections.ts
│           ├── feeds/
│           │   └── [id].ts
│           ├── products/
│           │   ├── index.ts
│           │   └── [id].ts
│           ├── collections/
│           │   ├── index.ts
│           │   └── [id].ts
│           └── analytics/
│               ├── overview.ts
│               ├── timeseries.ts
│               ├── top-products.ts
│               └── revenue.ts
├── components/
│   ├── admin/                      # Admin React components
│   │   ├── AdminApp.tsx            # Root SPA component
│   │   ├── AdminLayout.tsx         # Sidebar + header
│   │   ├── Dashboard.tsx
│   │   ├── HomepageManager.tsx
│   │   ├── FeedManager.tsx
│   │   ├── ProductManager.tsx
│   │   ├── CollectionManager.tsx
│   │   ├── Analytics.tsx
│   │   └── ui/                     # Shared admin UI components
│   │       ├── DataTable.tsx
│   │       ├── RuleBuilder.tsx
│   │       ├── StatCard.tsx
│   │       └── Chart.tsx
│   └── OutboundLink.tsx            # Click tracking wrapper
├── lib/
│   ├── auth.ts                     # JWT helpers
│   ├── click-tracking.ts           # Click ref generation
│   └── db/
│       └── schema.ts               # Drizzle schema additions
└── scripts/
    ├── sync-revenue.ts             # Combined revenue sync
    ├── sync-webgains.ts
    ├── sync-tradetracker.ts
    ├── aggregate-stats.ts          # Daily stats aggregation
    └── update-scores.ts            # Product score calculation
```