Merge branch 'master' into fix/analytics-v2-queries

- Add authMiddleware to analytics-v2.ts to require authentication
- Add permanent rule #6 to CLAUDE.md: "ALL API ROUTES REQUIRE AUTHENTICATION"
- Add forbidden action #19: "Creating API routes without authMiddleware"
- Document authentication flow and trusted origins

🤖 Generated with [Claude Code](https://claude.com/claude-code)

Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>

CLAUDE.md

@@ -119,7 +119,42 @@ npx tsx src/db/migrate.ts
 - Importing it at runtime causes startup crashes if env vars aren't perfect
 - `pool.ts` uses lazy initialization - only validates when first query is made
 
-### 6. LOCAL DEVELOPMENT BY DEFAULT
+### 6. ALL API ROUTES REQUIRE AUTHENTICATION — NO EXCEPTIONS
+
+**Every API router MUST apply `authMiddleware` at the router level.**
+
+```typescript
+import { authMiddleware } from '../auth/middleware';
+
+const router = Router();
+router.use(authMiddleware);  // REQUIRED - first line after router creation
+```
+
+**Authentication flow (see `src/auth/middleware.ts`):**
+1. Check Bearer token (JWT or API token) → grant access if valid
+2. Check trusted origins (cannaiq.co, findadispo.com, localhost, etc.) → grant access
+3. Check trusted IPs (127.0.0.1, ::1, internal pod IPs) → grant access
+4. **Return 401 Unauthorized** if none of the above
+
+**NEVER create API routes without auth middleware:**
+- No "public" endpoints that bypass authentication
+- No "read-only" exceptions
+- No "analytics-only" exceptions
+- If an endpoint exists under `/api/*`, it MUST be protected
+
+**When creating new route files:**
+1. Import `authMiddleware` from `../auth/middleware`
+2. Add `router.use(authMiddleware)` immediately after creating the router
+3. Document security requirements in file header comments
+
+**Trusted origins (defined in middleware):**
+- `https://cannaiq.co`
+- `https://findadispo.com`
+- `https://findagram.co`
+- `*.cannabrands.app` domains
+- `localhost:*` for development
+
+### 7. LOCAL DEVELOPMENT BY DEFAULT
 
 **Quick Start:**
 ```bash
@@ -452,6 +487,7 @@ const result = await pool.query(`
 16. **Running `lsof -ti:PORT | xargs kill`** or similar process-killing commands
 17. **Using hardcoded database names** in code or comments
 18. **Creating or connecting to a second database**
+19. **Creating API routes without authMiddleware** (all `/api/*` routes MUST be protected)
 
 ---
 

backend/src/routes/analytics-v2.ts

@@ -7,10 +7,17 @@
  * Routes are prefixed with /api/analytics/v2
  *
  * Phase 3: Analytics Engine + Rec/Med by State
+ *
+ * SECURITY: All routes require authentication via authMiddleware.
+ * Access is granted to:
+ * - Trusted origins (cannaiq.co, findadispo.com, etc.)
+ * - Trusted IPs (localhost, internal pods)
+ * - Valid JWT or API tokens
  */
 
 import { Router, Request, Response } from 'express';
 import { Pool } from 'pg';
+import { authMiddleware } from '../auth/middleware';
 import { PriceAnalyticsService } from '../services/analytics/PriceAnalyticsService';
 import { BrandPenetrationService } from '../services/analytics/BrandPenetrationService';
 import { CategoryAnalyticsService } from '../services/analytics/CategoryAnalyticsService';
@@ -36,6 +43,10 @@ function parseLegalType(legalType?: string): LegalType {
 export function createAnalyticsV2Router(pool: Pool): Router {
   const router = Router();
 
+  // SECURITY: Apply auth middleware to ALL routes
+  // This gate ensures only authenticated requests can access analytics data
+  router.use(authMiddleware);
+
   // Initialize services
   const priceService = new PriceAnalyticsService(pool);
   const brandService = new BrandPenetrationService(pool);

backend/src/services/analytics/CategoryAnalyticsService.ts

@@ -43,14 +43,14 @@ export class CategoryAnalyticsService {
     // Get current category metrics
     const currentResult = await this.pool.query(`
       SELECT
-        sp.category,
+        sp.category_raw,
         COUNT(*) AS sku_count,
         COUNT(DISTINCT sp.dispensary_id) AS dispensary_count,
         AVG(sp.price_rec) AS avg_price
       FROM store_products sp
-      WHERE sp.category = $1
+      WHERE sp.category_raw = $1
         AND sp.is_in_stock = TRUE
-      GROUP BY sp.category
+      GROUP BY sp.category_raw
     `, [category]);
 
     if (currentResult.rows.length === 0) {
@@ -70,7 +70,7 @@ export class CategoryAnalyticsService {
         COUNT(DISTINCT sps.dispensary_id) AS dispensary_count,
         AVG(sps.price_rec) AS avg_price
       FROM store_product_snapshots sps
-      WHERE sps.category = $1
+      WHERE sps.category_raw = $1
         AND sps.captured_at >= $2
         AND sps.captured_at <= $3
         AND sps.is_in_stock = TRUE
@@ -111,8 +111,9 @@ export class CategoryAnalyticsService {
         COUNT(DISTINCT sp.dispensary_id) AS dispensary_count,
         AVG(sp.price_rec) AS avg_price
       FROM store_products sp
-      JOIN states s ON s.id = sp.state_id
+      JOIN dispensaries d ON d.id = sp.dispensary_id
-      WHERE sp.category = $1
+      JOIN states s ON s.id = d.state_id
+      WHERE sp.category_raw = $1
         AND sp.is_in_stock = TRUE
       GROUP BY s.code, s.name, s.recreational_legal
       ORDER BY sku_count DESC
@@ -154,24 +155,25 @@ export class CategoryAnalyticsService {
 
     const result = await this.pool.query(`
       SELECT
-        sp.category,
+        sp.category_raw,
         COUNT(*) AS sku_count,
         COUNT(DISTINCT sp.dispensary_id) AS dispensary_count,
-        COUNT(DISTINCT sp.brand_name) AS brand_count,
+        COUNT(DISTINCT sp.brand_name_raw) AS brand_count,
         AVG(sp.price_rec) AS avg_price,
         COUNT(DISTINCT s.code) AS state_count
       FROM store_products sp
-      LEFT JOIN states s ON s.id = sp.state_id
+      LEFT JOIN dispensaries d ON d.id = sp.dispensary_id
-      WHERE sp.category IS NOT NULL
+      JOIN states s ON s.id = d.state_id
+      WHERE sp.category_raw IS NOT NULL
         AND sp.is_in_stock = TRUE
         ${stateFilter}
-      GROUP BY sp.category
+      GROUP BY sp.category_raw
       ORDER BY sku_count DESC
       LIMIT $1
     `, params);
 
     return result.rows.map((row: any) => ({
-      category: row.category,
+      category: row.category_raw,
       sku_count: parseInt(row.sku_count),
       dispensary_count: parseInt(row.dispensary_count),
       brand_count: parseInt(row.brand_count),
@@ -188,14 +190,14 @@ export class CategoryAnalyticsService {
     let categoryFilter = '';
 
     if (category) {
-      categoryFilter = 'WHERE sp.category = $1';
+      categoryFilter = 'WHERE sp.category_raw = $1';
       params.push(category);
     }
 
     const result = await this.pool.query(`
       WITH category_stats AS (
         SELECT
-          sp.category,
+          sp.category_raw,
           CASE WHEN s.recreational_legal = TRUE THEN 'recreational' ELSE 'medical_only' END AS legal_type,
           COUNT(DISTINCT s.code) AS state_count,
           COUNT(DISTINCT sp.dispensary_id) AS dispensary_count,
@@ -203,13 +205,14 @@ export class CategoryAnalyticsService {
           AVG(sp.price_rec) AS avg_price,
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sp.price_rec) AS median_price
         FROM store_products sp
-        JOIN states s ON s.id = sp.state_id
+        JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
         ${categoryFilter}
-        ${category ? 'AND' : 'WHERE'} sp.category IS NOT NULL
+        ${category ? 'AND' : 'WHERE'} sp.category_raw IS NOT NULL
           AND sp.is_in_stock = TRUE
           AND sp.price_rec IS NOT NULL
           AND (s.recreational_legal = TRUE OR s.medical_legal = TRUE)
-        GROUP BY sp.category, CASE WHEN s.recreational_legal = TRUE THEN 'recreational' ELSE 'medical_only' END
+        GROUP BY sp.category_raw, CASE WHEN s.recreational_legal = TRUE THEN 'recreational' ELSE 'medical_only' END
       ),
       rec_stats AS (
         SELECT * FROM category_stats WHERE legal_type = 'recreational'
@@ -218,7 +221,7 @@ export class CategoryAnalyticsService {
         SELECT * FROM category_stats WHERE legal_type = 'medical_only'
       )
       SELECT
-        COALESCE(r.category, m.category) AS category,
+        COALESCE(r.category_raw, m.category_raw) AS category,
         r.state_count AS rec_state_count,
         r.dispensary_count AS rec_dispensary_count,
         r.sku_count AS rec_sku_count,
@@ -235,7 +238,7 @@ export class CategoryAnalyticsService {
           ELSE NULL
         END AS price_diff_percent
       FROM rec_stats r
-      FULL OUTER JOIN med_stats m ON r.category = m.category
+      FULL OUTER JOIN med_stats m ON r.category_raw = m.category_raw
       ORDER BY COALESCE(r.sku_count, 0) + COALESCE(m.sku_count, 0) DESC
     `, params);
 
@@ -282,7 +285,7 @@ export class CategoryAnalyticsService {
           COUNT(*) AS sku_count,
           COUNT(DISTINCT sps.dispensary_id) AS dispensary_count
         FROM store_product_snapshots sps
-        WHERE sps.category = $1
+        WHERE sps.category_raw = $1
           AND sps.captured_at >= $2
           AND sps.captured_at <= $3
           AND sps.is_in_stock = TRUE
@@ -335,31 +338,33 @@ export class CategoryAnalyticsService {
       WITH category_total AS (
         SELECT COUNT(*) AS total
         FROM store_products sp
-        LEFT JOIN states s ON s.id = sp.state_id
+        LEFT JOIN dispensaries d ON d.id = sp.dispensary_id
-        WHERE sp.category = $1
+      JOIN states s ON s.id = d.state_id
+        WHERE sp.category_raw = $1
           AND sp.is_in_stock = TRUE
-          AND sp.brand_name IS NOT NULL
+          AND sp.brand_name_raw IS NOT NULL
           ${stateFilter}
       )
       SELECT
-        sp.brand_name,
+        sp.brand_name_raw,
         COUNT(*) AS sku_count,
         COUNT(DISTINCT sp.dispensary_id) AS dispensary_count,
         AVG(sp.price_rec) AS avg_price,
         ROUND(COUNT(*)::NUMERIC * 100 / NULLIF((SELECT total FROM category_total), 0), 2) AS category_share_percent
       FROM store_products sp
-      LEFT JOIN states s ON s.id = sp.state_id
+      LEFT JOIN dispensaries d ON d.id = sp.dispensary_id
-      WHERE sp.category = $1
+      JOIN states s ON s.id = d.state_id
+      WHERE sp.category_raw = $1
         AND sp.is_in_stock = TRUE
-        AND sp.brand_name IS NOT NULL
+        AND sp.brand_name_raw IS NOT NULL
         ${stateFilter}
-      GROUP BY sp.brand_name
+      GROUP BY sp.brand_name_raw
       ORDER BY sku_count DESC
       LIMIT $2
     `, params);
 
     return result.rows.map((row: any) => ({
-      brand_name: row.brand_name,
+      brand_name: row.brand_name_raw,
       sku_count: parseInt(row.sku_count),
       dispensary_count: parseInt(row.dispensary_count),
       avg_price: row.avg_price ? parseFloat(row.avg_price) : null,
@@ -421,7 +426,7 @@ export class CategoryAnalyticsService {
     `, [start, end, limit]);
 
     return result.rows.map((row: any) => ({
-      category: row.category,
+      category: row.category_raw,
       start_sku_count: parseInt(row.start_sku_count),
       end_sku_count: parseInt(row.end_sku_count),
       growth: parseInt(row.growth),

backend/src/services/analytics/PriceAnalyticsService.ts

@@ -43,9 +43,9 @@ export class PriceAnalyticsService {
     const productResult = await this.pool.query(`
       SELECT
         sp.id,
-        sp.name,
+        sp.name_raw,
-        sp.brand_name,
+        sp.brand_name_raw,
-        sp.category,
+        sp.category_raw,
         sp.dispensary_id,
         sp.price_rec,
         sp.price_med,
@@ -53,7 +53,7 @@ export class PriceAnalyticsService {
         s.code AS state_code
       FROM store_products sp
       JOIN dispensaries d ON d.id = sp.dispensary_id
-      LEFT JOIN states s ON s.id = sp.state_id
+      JOIN states s ON s.id = d.state_id
       WHERE sp.id = $1
     `, [storeProductId]);
 
@@ -133,7 +133,7 @@ export class PriceAnalyticsService {
 
     const result = await this.pool.query(`
       SELECT
-        sp.category,
+        sp.category_raw,
         s.code AS state_code,
         s.name AS state_name,
         CASE
@@ -148,18 +148,18 @@ export class PriceAnalyticsService {
         COUNT(DISTINCT sp.dispensary_id) AS dispensary_count
       FROM store_products sp
       JOIN dispensaries d ON d.id = sp.dispensary_id
-      JOIN states s ON s.id = sp.state_id
+      JOIN states s ON s.id = d.state_id
-      WHERE sp.category = $1
+      WHERE sp.category_raw = $1
         AND sp.price_rec IS NOT NULL
         AND sp.is_in_stock = TRUE
         AND (s.recreational_legal = TRUE OR s.medical_legal = TRUE)
         ${stateFilter}
-      GROUP BY sp.category, s.code, s.name, s.recreational_legal
+      GROUP BY sp.category_raw, s.code, s.name, s.recreational_legal
       ORDER BY state_code
     `, params);
 
     return result.rows.map((row: any) => ({
-      category: row.category,
+      category: row.category_raw,
       state_code: row.state_code,
       state_name: row.state_name,
       legal_type: row.legal_type,
@@ -189,7 +189,7 @@ export class PriceAnalyticsService {
 
     const result = await this.pool.query(`
       SELECT
-        sp.brand_name AS category,
+        sp.brand_name_raw AS category,
         s.code AS state_code,
         s.name AS state_name,
         CASE
@@ -204,18 +204,18 @@ export class PriceAnalyticsService {
         COUNT(DISTINCT sp.dispensary_id) AS dispensary_count
       FROM store_products sp
       JOIN dispensaries d ON d.id = sp.dispensary_id
-      JOIN states s ON s.id = sp.state_id
+      JOIN states s ON s.id = d.state_id
-      WHERE sp.brand_name = $1
+      WHERE sp.brand_name_raw = $1
         AND sp.price_rec IS NOT NULL
         AND sp.is_in_stock = TRUE
         AND (s.recreational_legal = TRUE OR s.medical_legal = TRUE)
         ${stateFilter}
-      GROUP BY sp.brand_name, s.code, s.name, s.recreational_legal
+      GROUP BY sp.brand_name_raw, s.code, s.name, s.recreational_legal
       ORDER BY state_code
     `, params);
 
     return result.rows.map((row: any) => ({
-      category: row.category,
+      category: row.category_raw,
       state_code: row.state_code,
       state_name: row.state_name,
       legal_type: row.legal_type,
@@ -254,7 +254,7 @@ export class PriceAnalyticsService {
     }
 
     if (category) {
-      filters += ` AND sp.category = $${paramIdx}`;
+      filters += ` AND sp.category_raw = $${paramIdx}`;
       params.push(category);
       paramIdx++;
     }
@@ -288,15 +288,16 @@ export class PriceAnalyticsService {
       )
       SELECT
         v.store_product_id,
-        sp.name AS product_name,
+        sp.name_raw AS product_name,
-        sp.brand_name,
+        sp.brand_name_raw,
         v.change_count,
         v.avg_change_pct,
         v.max_change_pct,
         v.last_change_at
       FROM volatility v
       JOIN store_products sp ON sp.id = v.store_product_id
-      LEFT JOIN states s ON s.id = sp.state_id
+      LEFT JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
       WHERE 1=1 ${filters}
       ORDER BY v.change_count DESC, v.avg_change_pct DESC
       LIMIT $3
@@ -305,7 +306,7 @@ export class PriceAnalyticsService {
     return result.rows.map((row: any) => ({
       store_product_id: row.store_product_id,
       product_name: row.product_name,
-      brand_name: row.brand_name,
+      brand_name: row.brand_name_raw,
       change_count: parseInt(row.change_count),
       avg_change_percent: row.avg_change_pct ? parseFloat(row.avg_change_pct) : 0,
       max_change_percent: row.max_change_pct ? parseFloat(row.max_change_pct) : 0,
@@ -327,13 +328,13 @@ export class PriceAnalyticsService {
     let categoryFilter = '';
 
     if (category) {
-      categoryFilter = 'WHERE sp.category = $1';
+      categoryFilter = 'WHERE sp.category_raw = $1';
       params.push(category);
     }
 
     const result = await this.pool.query(`
       SELECT
-        sp.category,
+        sp.category_raw,
         AVG(sp.price_rec) FILTER (WHERE s.recreational_legal = TRUE) AS rec_avg,
         PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sp.price_rec)
           FILTER (WHERE s.recreational_legal = TRUE) AS rec_median,
@@ -343,17 +344,18 @@ export class PriceAnalyticsService {
         PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sp.price_rec)
           FILTER (WHERE s.medical_legal = TRUE AND (s.recreational_legal = FALSE OR s.recreational_legal IS NULL)) AS med_median
       FROM store_products sp
-      JOIN states s ON s.id = sp.state_id
+      JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
       ${categoryFilter}
       ${category ? 'AND' : 'WHERE'} sp.price_rec IS NOT NULL
         AND sp.is_in_stock = TRUE
-        AND sp.category IS NOT NULL
+        AND sp.category_raw IS NOT NULL
-      GROUP BY sp.category
+      GROUP BY sp.category_raw
-      ORDER BY sp.category
+      ORDER BY sp.category_raw
     `, params);
 
     return result.rows.map((row: any) => ({
-      category: row.category,
+      category: row.category_raw,
       rec_avg: row.rec_avg ? parseFloat(row.rec_avg) : null,
       rec_median: row.rec_median ? parseFloat(row.rec_median) : null,
       med_avg: row.med_avg ? parseFloat(row.med_avg) : null,

backend/src/services/analytics/StateAnalyticsService.ts

@@ -108,14 +108,14 @@ export class StateAnalyticsService {
       SELECT
         COUNT(DISTINCT d.id) AS dispensary_count,
         COUNT(DISTINCT sp.id) AS product_count,
-        COUNT(DISTINCT sp.brand_name) FILTER (WHERE sp.brand_name IS NOT NULL) AS brand_count,
+        COUNT(DISTINCT sp.brand_name_raw) FILTER (WHERE sp.brand_name_raw IS NOT NULL) AS brand_count,
-        COUNT(DISTINCT sp.category) FILTER (WHERE sp.category IS NOT NULL) AS category_count,
+        COUNT(DISTINCT sp.category_raw) FILTER (WHERE sp.category_raw IS NOT NULL) AS category_count,
         COUNT(sps.id) AS snapshot_count,
         MAX(sps.captured_at) AS last_crawl_at
       FROM states s
       LEFT JOIN dispensaries d ON d.state_id = s.id
-      LEFT JOIN store_products sp ON sp.state_id = s.id AND sp.is_in_stock = TRUE
+      LEFT JOIN store_products sp ON sp.dispensary_id = d.id AND sp.is_in_stock = TRUE
-      LEFT JOIN store_product_snapshots sps ON sps.state_id = s.id
+      LEFT JOIN store_product_snapshots sps ON sps.dispensary_id = d.id
       WHERE s.code = $1
     `, [stateCode]);
 
@@ -129,7 +129,8 @@ export class StateAnalyticsService {
         MIN(price_rec) AS min_price,
         MAX(price_rec) AS max_price
       FROM store_products sp
-      JOIN states s ON s.id = sp.state_id
+      JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
       WHERE s.code = $1
         AND sp.price_rec IS NOT NULL
         AND sp.is_in_stock = TRUE
@@ -140,14 +141,15 @@ export class StateAnalyticsService {
     // Get top categories
     const topCategoriesResult = await this.pool.query(`
       SELECT
-        sp.category,
+        sp.category_raw,
         COUNT(*) AS count
       FROM store_products sp
-      JOIN states s ON s.id = sp.state_id
+      JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
       WHERE s.code = $1
-        AND sp.category IS NOT NULL
+        AND sp.category_raw IS NOT NULL
         AND sp.is_in_stock = TRUE
-      GROUP BY sp.category
+      GROUP BY sp.category_raw
       ORDER BY count DESC
       LIMIT 10
     `, [stateCode]);
@@ -155,14 +157,15 @@ export class StateAnalyticsService {
     // Get top brands
     const topBrandsResult = await this.pool.query(`
       SELECT
-        sp.brand_name AS brand,
+        sp.brand_name_raw AS brand,
         COUNT(*) AS count
       FROM store_products sp
-      JOIN states s ON s.id = sp.state_id
+      JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
       WHERE s.code = $1
-        AND sp.brand_name IS NOT NULL
+        AND sp.brand_name_raw IS NOT NULL
         AND sp.is_in_stock = TRUE
-      GROUP BY sp.brand_name
+      GROUP BY sp.brand_name_raw
       ORDER BY count DESC
       LIMIT 10
     `, [stateCode]);
@@ -191,7 +194,7 @@ export class StateAnalyticsService {
         max_price: pricing.max_price ? parseFloat(pricing.max_price) : null,
       },
       top_categories: topCategoriesResult.rows.map((row: any) => ({
-        category: row.category,
+        category: row.category_raw,
         count: parseInt(row.count),
       })),
       top_brands: topBrandsResult.rows.map((row: any) => ({
@@ -215,8 +218,8 @@ export class StateAnalyticsService {
         COUNT(sps.id) AS snapshot_count
       FROM states s
       LEFT JOIN dispensaries d ON d.state_id = s.id
-      LEFT JOIN store_products sp ON sp.state_id = s.id AND sp.is_in_stock = TRUE
+      LEFT JOIN store_products sp ON sp.dispensary_id = d.id AND sp.is_in_stock = TRUE
-      LEFT JOIN store_product_snapshots sps ON sps.state_id = s.id
+      LEFT JOIN store_product_snapshots sps ON sps.dispensary_id = d.id
       WHERE s.recreational_legal = TRUE
       GROUP BY s.code, s.name
       ORDER BY dispensary_count DESC
@@ -232,8 +235,8 @@ export class StateAnalyticsService {
         COUNT(sps.id) AS snapshot_count
       FROM states s
       LEFT JOIN dispensaries d ON d.state_id = s.id
-      LEFT JOIN store_products sp ON sp.state_id = s.id AND sp.is_in_stock = TRUE
+      LEFT JOIN store_products sp ON sp.dispensary_id = d.id AND sp.is_in_stock = TRUE
-      LEFT JOIN store_product_snapshots sps ON sps.state_id = s.id
+      LEFT JOIN store_product_snapshots sps ON sps.dispensary_id = d.id
       WHERE s.medical_legal = TRUE
         AND (s.recreational_legal = FALSE OR s.recreational_legal IS NULL)
       GROUP BY s.code, s.name
@@ -295,46 +298,48 @@ export class StateAnalyticsService {
     let groupBy = 'NULL';
 
     if (category) {
-      categoryFilter = 'AND sp.category = $1';
+      categoryFilter = 'AND sp.category_raw = $1';
       params.push(category);
-      groupBy = 'sp.category';
+      groupBy = 'sp.category_raw';
     } else {
-      groupBy = 'sp.category';
+      groupBy = 'sp.category_raw';
     }
 
     const result = await this.pool.query(`
       WITH rec_prices AS (
         SELECT
-          ${category ? 'sp.category' : 'sp.category'},
+          ${category ? 'sp.category_raw' : 'sp.category_raw'},
           COUNT(DISTINCT s.code) AS state_count,
           COUNT(*) AS product_count,
           AVG(sp.price_rec) AS avg_price,
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sp.price_rec) AS median_price
         FROM store_products sp
-        JOIN states s ON s.id = sp.state_id
+        JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
         WHERE s.recreational_legal = TRUE
           AND sp.price_rec IS NOT NULL
           AND sp.is_in_stock = TRUE
-          AND sp.category IS NOT NULL
+          AND sp.category_raw IS NOT NULL
           ${categoryFilter}
-        GROUP BY sp.category
+        GROUP BY sp.category_raw
       ),
       med_prices AS (
         SELECT
-          ${category ? 'sp.category' : 'sp.category'},
+          ${category ? 'sp.category_raw' : 'sp.category_raw'},
           COUNT(DISTINCT s.code) AS state_count,
           COUNT(*) AS product_count,
           AVG(sp.price_rec) AS avg_price,
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sp.price_rec) AS median_price
         FROM store_products sp
-        JOIN states s ON s.id = sp.state_id
+        JOIN dispensaries d ON d.id = sp.dispensary_id
+      JOIN states s ON s.id = d.state_id
         WHERE s.medical_legal = TRUE
           AND (s.recreational_legal = FALSE OR s.recreational_legal IS NULL)
           AND sp.price_rec IS NOT NULL
           AND sp.is_in_stock = TRUE
-          AND sp.category IS NOT NULL
+          AND sp.category_raw IS NOT NULL
           ${categoryFilter}
-        GROUP BY sp.category
+        GROUP BY sp.category_raw
       )
       SELECT
         COALESCE(r.category, m.category) AS category,
@@ -357,7 +362,7 @@ export class StateAnalyticsService {
     `, params);
 
     return result.rows.map((row: any) => ({
-      category: row.category,
+      category: row.category_raw,
       recreational: {
         state_count: parseInt(row.rec_state_count) || 0,
         product_count: parseInt(row.rec_product_count) || 0,
@@ -395,12 +400,12 @@ export class StateAnalyticsService {
         COALESCE(s.medical_legal, FALSE) AS medical_legal,
         COUNT(DISTINCT d.id) AS dispensary_count,
         COUNT(DISTINCT sp.id) AS product_count,
-        COUNT(DISTINCT sp.brand_name) FILTER (WHERE sp.brand_name IS NOT NULL) AS brand_count,
+        COUNT(DISTINCT sp.brand_name_raw) FILTER (WHERE sp.brand_name_raw IS NOT NULL) AS brand_count,
         MAX(sps.captured_at) AS last_crawl_at
       FROM states s
       LEFT JOIN dispensaries d ON d.state_id = s.id
-      LEFT JOIN store_products sp ON sp.state_id = s.id AND sp.is_in_stock = TRUE
+      LEFT JOIN store_products sp ON sp.dispensary_id = d.id AND sp.is_in_stock = TRUE
-      LEFT JOIN store_product_snapshots sps ON sps.state_id = s.id
+      LEFT JOIN store_product_snapshots sps ON sps.dispensary_id = d.id
       GROUP BY s.code, s.name, s.recreational_legal, s.medical_legal
       ORDER BY dispensary_count DESC, s.name
     `);
@@ -451,8 +456,8 @@ export class StateAnalyticsService {
         END AS gap_reason
       FROM states s
       LEFT JOIN dispensaries d ON d.state_id = s.id
-      LEFT JOIN store_products sp ON sp.state_id = s.id AND sp.is_in_stock = TRUE
+      LEFT JOIN store_products sp ON sp.dispensary_id = d.id AND sp.is_in_stock = TRUE
-      LEFT JOIN store_product_snapshots sps ON sps.state_id = s.id
+      LEFT JOIN store_product_snapshots sps ON sps.dispensary_id = d.id
       WHERE s.recreational_legal = TRUE OR s.medical_legal = TRUE
       GROUP BY s.code, s.name, s.recreational_legal, s.medical_legal
       HAVING COUNT(DISTINCT d.id) = 0
@@ -499,7 +504,8 @@ export class StateAnalyticsService {
           PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sp.price_rec) AS median_price,
           COUNT(*) AS product_count
         FROM states s
-        JOIN store_products sp ON sp.state_id = s.id
+        JOIN dispensaries d ON d.state_id = s.id
+        JOIN store_products sp ON sp.dispensary_id = d.id
         WHERE sp.price_rec IS NOT NULL
           AND sp.is_in_stock = TRUE
           AND (s.recreational_legal = TRUE OR s.medical_legal = TRUE)

backend/src/services/analytics/StoreAnalyticsService.ts

@@ -89,22 +89,22 @@ export class StoreAnalyticsService {
     // Get brands added/dropped
     const brandsResult = await this.pool.query(`
       WITH start_brands AS (
-        SELECT DISTINCT brand_name
+        SELECT DISTINCT brand_name_raw
         FROM store_product_snapshots
         WHERE dispensary_id = $1
-          AND captured_at >= $2 AND captured_at < $2 + INTERVAL '1 day'
+          AND captured_at >= $2::timestamp AND captured_at < $2::timestamp + INTERVAL '1 day'
-          AND brand_name IS NOT NULL
+          AND brand_name_raw IS NOT NULL
       ),
       end_brands AS (
-        SELECT DISTINCT brand_name
+        SELECT DISTINCT brand_name_raw
         FROM store_product_snapshots
         WHERE dispensary_id = $1
-          AND captured_at >= $3 - INTERVAL '1 day' AND captured_at <= $3
+          AND captured_at >= $3::timestamp - INTERVAL '1 day' AND captured_at <= $3::timestamp
-          AND brand_name IS NOT NULL
+          AND brand_name_raw IS NOT NULL
       )
       SELECT
-        ARRAY(SELECT brand_name FROM end_brands EXCEPT SELECT brand_name FROM start_brands) AS added,
+        ARRAY(SELECT brand_name_raw FROM end_brands EXCEPT SELECT brand_name_raw FROM start_brands) AS added,
-        ARRAY(SELECT brand_name FROM start_brands EXCEPT SELECT brand_name FROM end_brands) AS dropped
+        ARRAY(SELECT brand_name_raw FROM start_brands EXCEPT SELECT brand_name_raw FROM end_brands) AS dropped
     `, [dispensaryId, start, end]);
 
     const brands = brandsResult.rows[0] || { added: [], dropped: [] };
@@ -184,9 +184,9 @@ export class StoreAnalyticsService {
         -- Products added
         SELECT
           sp.id AS store_product_id,
-          sp.name AS product_name,
+          sp.name_raw AS product_name,
-          sp.brand_name,
+          sp.brand_name_raw,
-          sp.category,
+          sp.category_raw,
           'added' AS event_type,
           sp.first_seen_at AS event_date,
           NULL::TEXT AS old_value,
@@ -201,9 +201,9 @@ export class StoreAnalyticsService {
         -- Stock in/out from snapshots
         SELECT
           sps.store_product_id,
-          sp.name AS product_name,
+          sp.name_raw AS product_name,
-          sp.brand_name,
+          sp.brand_name_raw,
-          sp.category,
+          sp.category_raw,
           CASE
             WHEN sps.is_in_stock = TRUE AND LAG(sps.is_in_stock) OVER w = FALSE THEN 'stock_in'
             WHEN sps.is_in_stock = FALSE AND LAG(sps.is_in_stock) OVER w = TRUE THEN 'stock_out'
@@ -224,9 +224,9 @@ export class StoreAnalyticsService {
         -- Price changes from snapshots
         SELECT
           sps.store_product_id,
-          sp.name AS product_name,
+          sp.name_raw AS product_name,
-          sp.brand_name,
+          sp.brand_name_raw,
-          sp.category,
+          sp.category_raw,
           'price_change' AS event_type,
           sps.captured_at AS event_date,
           LAG(sps.price_rec::TEXT) OVER w AS old_value,
@@ -250,8 +250,8 @@ export class StoreAnalyticsService {
     return result.rows.map((row: any) => ({
       store_product_id: row.store_product_id,
       product_name: row.product_name,
-      brand_name: row.brand_name,
+      brand_name: row.brand_name_raw,
-      category: row.category,
+      category: row.category_raw,
       event_type: row.event_type,
       event_date: row.event_date ? row.event_date.toISOString() : null,
       old_value: row.old_value,
@@ -364,8 +364,8 @@ export class StoreAnalyticsService {
       changes: result.rows.map((row: any) => ({
         store_product_id: row.store_product_id,
         product_name: row.product_name,
-        brand_name: row.brand_name,
+        brand_name: row.brand_name_raw,
-        category: row.category,
+        category: row.category_raw,
         old_quantity: row.old_quantity,
         new_quantity: row.new_quantity,
         quantity_delta: row.qty_delta,
@@ -415,14 +415,14 @@ export class StoreAnalyticsService {
     // Get top brands
     const brandsResult = await this.pool.query(`
       SELECT
-        brand_name AS brand,
+        brand_name_raw AS brand,
         COUNT(*) AS count,
         ROUND(COUNT(*)::NUMERIC * 100 / NULLIF($2, 0), 2) AS percent
       FROM store_products
       WHERE dispensary_id = $1
-        AND brand_name IS NOT NULL
+        AND brand_name_raw IS NOT NULL
         AND is_in_stock = TRUE
-      GROUP BY brand_name
+      GROUP BY brand_name_raw
       ORDER BY count DESC
       LIMIT 20
     `, [dispensaryId, totalProducts]);
@@ -432,7 +432,7 @@ export class StoreAnalyticsService {
       in_stock_count: parseInt(totals.in_stock) || 0,
       out_of_stock_count: parseInt(totals.out_of_stock) || 0,
       categories: categoriesResult.rows.map((row: any) => ({
-        category: row.category,
+        category: row.category_raw,
         count: parseInt(row.count),
         percent: parseFloat(row.percent) || 0,
       })),
@@ -574,23 +574,24 @@ export class StoreAnalyticsService {
       ),
       market_prices AS (
         SELECT
-          sp.category,
+          sp.category_raw,
           AVG(sp.price_rec) AS market_avg
         FROM store_products sp
-        WHERE sp.state_id = $2
+        JOIN dispensaries d ON d.id = sp.dispensary_id
+        WHERE d.state_id = $2
           AND sp.price_rec IS NOT NULL
           AND sp.is_in_stock = TRUE
-          AND sp.category IS NOT NULL
+          AND sp.category_raw IS NOT NULL
-        GROUP BY sp.category
+        GROUP BY sp.category_raw
       )
       SELECT
-        sp.category,
+        sp.category_raw,
         sp.store_avg AS store_avg_price,
         mp.market_avg AS market_avg_price,
         ROUND(((sp.store_avg - mp.market_avg) / NULLIF(mp.market_avg, 0) * 100)::NUMERIC, 2) AS price_vs_market_percent,
         sp.product_count
       FROM store_prices sp
-      LEFT JOIN market_prices mp ON mp.category = sp.category
+      LEFT JOIN market_prices mp ON mp.category = sp.category_raw
       ORDER BY sp.product_count DESC
     `, [dispensaryId, dispensary.state_id]);
 
@@ -602,9 +603,10 @@ export class StoreAnalyticsService {
         WHERE dispensary_id = $1 AND price_rec IS NOT NULL AND is_in_stock = TRUE
       ),
       market_avg AS (
-        SELECT AVG(price_rec) AS avg
+        SELECT AVG(sp.price_rec) AS avg
-        FROM store_products
+        FROM store_products sp
-        WHERE state_id = $2 AND price_rec IS NOT NULL AND is_in_stock = TRUE
+        JOIN dispensaries d ON d.id = sp.dispensary_id
+        WHERE d.state_id = $2 AND sp.price_rec IS NOT NULL AND sp.is_in_stock = TRUE
       )
       SELECT
         ROUND(((sa.avg - ma.avg) / NULLIF(ma.avg, 0) * 100)::NUMERIC, 2) AS price_vs_market
@@ -615,7 +617,7 @@ export class StoreAnalyticsService {
       dispensary_id: dispensaryId,
       dispensary_name: dispensary.name,
       categories: result.rows.map((row: any) => ({
-        category: row.category,
+        category: row.category_raw,
         store_avg_price: parseFloat(row.store_avg_price),
         market_avg_price: row.market_avg_price ? parseFloat(row.market_avg_price) : 0,
         price_vs_market_percent: row.price_vs_market_percent ? parseFloat(row.price_vs_market_percent) : 0,