fix: Static file paths and crawl_enabled API filters

- Fix static file paths for local development (./public/* instead of /app/public/*)
- Add crawl_enabled and dutchie_verified filters to /api/stores and /api/dispensaries
- Default API to return only enabled stores (crawl_enabled=true)
- Add ?crawl_enabled=false to show disabled, ?crawl_enabled=all to show all

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

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

backend/src/db/migrate.ts

@@ -372,6 +372,51 @@ async function runMigrations() {
       ON CONFLICT (key) DO NOTHING;
     `);
 
+    // SEO Pages table
+    await client.query(`
+      CREATE TABLE IF NOT EXISTS seo_pages (
+        id SERIAL PRIMARY KEY,
+        type VARCHAR(50) NOT NULL,
+        slug VARCHAR(255) NOT NULL UNIQUE,
+        page_key VARCHAR(255) NOT NULL,
+        primary_keyword VARCHAR(255),
+        status VARCHAR(50) DEFAULT 'pending_generation',
+        data_source VARCHAR(100),
+        meta_title VARCHAR(255),
+        meta_description TEXT,
+        last_generated_at TIMESTAMPTZ,
+        last_reviewed_at TIMESTAMPTZ,
+        created_at TIMESTAMPTZ DEFAULT NOW(),
+        updated_at TIMESTAMPTZ DEFAULT NOW()
+      );
+      CREATE INDEX IF NOT EXISTS idx_seo_pages_type ON seo_pages(type);
+      CREATE INDEX IF NOT EXISTS idx_seo_pages_status ON seo_pages(status);
+      CREATE INDEX IF NOT EXISTS idx_seo_pages_slug ON seo_pages(slug);
+    `);
+
+    // SEO Page Contents table
+    await client.query(`
+      CREATE TABLE IF NOT EXISTS seo_page_contents (
+        id SERIAL PRIMARY KEY,
+        page_id INTEGER NOT NULL REFERENCES seo_pages(id) ON DELETE CASCADE,
+        version INTEGER DEFAULT 1,
+        blocks JSONB NOT NULL DEFAULT '[]',
+        meta JSONB NOT NULL DEFAULT '{}',
+        meta_title VARCHAR(255),
+        meta_description TEXT,
+        h1 VARCHAR(255),
+        canonical_url TEXT,
+        og_title VARCHAR(255),
+        og_description TEXT,
+        og_image_url TEXT,
+        generated_by VARCHAR(50) DEFAULT 'claude',
+        created_at TIMESTAMPTZ DEFAULT NOW(),
+        updated_at TIMESTAMPTZ DEFAULT NOW(),
+        UNIQUE(page_id, version)
+      );
+      CREATE INDEX IF NOT EXISTS idx_seo_page_contents_page ON seo_page_contents(page_id);
+    `);
+
     await client.query('COMMIT');
     console.log('✅ Migrations completed successfully');
   } catch (error) {

backend/src/index.ts

@@ -17,11 +17,13 @@ app.use(cors());
 app.use(express.json());
 
 // Serve static images when MinIO is not configured
-const LOCAL_IMAGES_PATH = process.env.LOCAL_IMAGES_PATH || '/app/public/images';
+// Uses ./public/images relative to working directory (works for both Docker and local dev)
+const LOCAL_IMAGES_PATH = process.env.LOCAL_IMAGES_PATH || './public/images';
 app.use('/images', express.static(LOCAL_IMAGES_PATH));
 
 // Serve static downloads (plugin files, etc.)
-const LOCAL_DOWNLOADS_PATH = process.env.LOCAL_DOWNLOADS_PATH || '/app/public/downloads';
+// Uses ./public/downloads relative to working directory (works for both Docker and local dev)
+const LOCAL_DOWNLOADS_PATH = process.env.LOCAL_DOWNLOADS_PATH || './public/downloads';
 app.use('/downloads', express.static(LOCAL_DOWNLOADS_PATH));
 
 // Simple health check for load balancers/K8s probes

backend/src/routes/dispensaries.ts

@@ -11,29 +11,46 @@ const VALID_MENU_TYPES = ['dutchie', 'treez', 'jane', 'weedmaps', 'leafly', 'mea
 // Get all dispensaries
 router.get('/', async (req, res) => {
   try {
-    const { menu_type, city, state } = req.query;
+    const { menu_type, city, state, crawl_enabled, dutchie_verified } = req.query;
 
     let query = `
       SELECT
         id,
         name,
-        company_name,
         slug,
-        address,
+        address1,
+        address2,
         city,
         state,
-        zip,
+        zipcode,
         phone,
         website,
+        email,
         dba_name,
         latitude,
         longitude,
+        timezone,
         menu_url,
         menu_type,
         platform,
         platform_dispensary_id,
+        c_name,
+        chain_slug,
+        enterprise_id,
+        description,
+        logo_image,
+        banner_image,
+        offer_pickup,
+        offer_delivery,
+        offer_curbside_pickup,
+        is_medical,
+        is_recreational,
+        status,
+        country,
         product_count,
         last_crawl_at,
+        crawl_enabled,
+        dutchie_verified,
         created_at,
         updated_at
       FROM dispensaries
@@ -48,10 +65,10 @@ router.get('/', async (req, res) => {
       params.push(menu_type);
     }
 
-    // Filter by city if provided
+    // Filter by city if provided (supports partial match)
     if (city) {
       conditions.push(`city ILIKE $${params.length + 1}`);
-      params.push(city);
+      params.push(`%${city}%`);
     }
 
     // Filter by state if provided
@@ -60,6 +77,27 @@ router.get('/', async (req, res) => {
       params.push(state);
     }
 
+    // Filter by crawl_enabled - defaults to showing only enabled
+    if (crawl_enabled === 'false' || crawl_enabled === '0') {
+      // Explicitly show disabled only
+      conditions.push(`(crawl_enabled = false OR crawl_enabled IS NULL)`);
+    } else if (crawl_enabled === 'all') {
+      // Show all (no filter)
+    } else {
+      // Default: show only enabled
+      conditions.push(`crawl_enabled = true`);
+    }
+
+    // Filter by dutchie_verified if provided
+    if (dutchie_verified !== undefined) {
+      const verified = dutchie_verified === 'true' || dutchie_verified === '1';
+      if (verified) {
+        conditions.push(`dutchie_verified = true`);
+      } else {
+        conditions.push(`(dutchie_verified = false OR dutchie_verified IS NULL)`);
+      }
+    }
+
     if (conditions.length > 0) {
       query += ` WHERE ${conditions.join(' AND ')}`;
     }
@@ -68,7 +106,7 @@ router.get('/', async (req, res) => {
 
     const result = await pool.query(query, params);
 
-    res.json({ dispensaries: result.rows });
+    res.json({ dispensaries: result.rows, total: result.rowCount });
   } catch (error) {
     console.error('Error fetching dispensaries:', error);
     res.status(500).json({ error: 'Failed to fetch dispensaries' });
@@ -91,6 +129,46 @@ router.get('/stats/menu-types', async (req, res) => {
   }
 });
 
+// Get crawl status stats
+router.get('/stats/crawl-status', async (req, res) => {
+  try {
+    const { state, city } = req.query;
+
+    let query = `
+      SELECT
+        COUNT(*) FILTER (WHERE crawl_enabled = true) as enabled_count,
+        COUNT(*) FILTER (WHERE crawl_enabled = false OR crawl_enabled IS NULL) as disabled_count,
+        COUNT(*) FILTER (WHERE dutchie_verified = true) as verified_count,
+        COUNT(*) FILTER (WHERE dutchie_verified = false OR dutchie_verified IS NULL) as unverified_count,
+        COUNT(*) as total_count
+      FROM dispensaries
+    `;
+
+    const params: any[] = [];
+    const conditions: string[] = [];
+
+    if (state) {
+      conditions.push(`state = $${params.length + 1}`);
+      params.push(state);
+    }
+
+    if (city) {
+      conditions.push(`city ILIKE $${params.length + 1}`);
+      params.push(`%${city}%`);
+    }
+
+    if (conditions.length > 0) {
+      query += ` WHERE ${conditions.join(' AND ')}`;
+    }
+
+    const result = await pool.query(query, params);
+    res.json(result.rows[0]);
+  } catch (error) {
+    console.error('Error fetching crawl status stats:', error);
+    res.status(500).json({ error: 'Failed to fetch crawl status stats' });
+  }
+});
+
 // Get single dispensary by slug or ID
 router.get('/:slugOrId', async (req, res) => {
   try {
@@ -101,21 +179,36 @@ router.get('/:slugOrId', async (req, res) => {
       SELECT
         id,
         name,
-        company_name,
         slug,
-        address,
+        address1,
+        address2,
         city,
         state,
-        zip,
+        zipcode,
         phone,
         website,
+        email,
         dba_name,
         latitude,
         longitude,
+        timezone,
         menu_url,
         menu_type,
         platform,
         platform_dispensary_id,
+        c_name,
+        chain_slug,
+        enterprise_id,
+        description,
+        logo_image,
+        banner_image,
+        offer_pickup,
+        offer_delivery,
+        offer_curbside_pickup,
+        is_medical,
+        is_recreational,
+        status,
+        country,
         product_count,
         last_crawl_at,
         raw_metadata,
@@ -143,19 +236,34 @@ router.put('/:id', async (req, res) => {
     const {
       name,
       dba_name,
-      company_name,
       website,
       phone,
-      address,
+      email,
+      address1,
+      address2,
       city,
       state,
-      zip,
+      zipcode,
       latitude,
       longitude,
+      timezone,
       menu_url,
       menu_type,
       platform,
       platform_dispensary_id,
+      c_name,
+      chain_slug,
+      enterprise_id,
+      description,
+      logo_image,
+      banner_image,
+      offer_pickup,
+      offer_delivery,
+      offer_curbside_pickup,
+      is_medical,
+      is_recreational,
+      status,
+      country,
       slug,
     } = req.body;
 
@@ -171,39 +279,69 @@ router.put('/:id', async (req, res) => {
       SET
         name = COALESCE($1, name),
         dba_name = COALESCE($2, dba_name),
-        company_name = COALESCE($3, company_name),
-        website = COALESCE($4, website),
-        phone = COALESCE($5, phone),
-        address = COALESCE($6, address),
-        city = COALESCE($7, city),
-        state = COALESCE($8, state),
-        zip = COALESCE($9, zip),
-        latitude = COALESCE($10, latitude),
-        longitude = COALESCE($11, longitude),
-        menu_url = COALESCE($12, menu_url),
-        menu_type = COALESCE($13, menu_type),
-        platform = COALESCE($14, platform),
-        platform_dispensary_id = COALESCE($15, platform_dispensary_id),
-        slug = COALESCE($16, slug),
+        website = COALESCE($3, website),
+        phone = COALESCE($4, phone),
+        email = COALESCE($5, email),
+        address1 = COALESCE($6, address1),
+        address2 = COALESCE($7, address2),
+        city = COALESCE($8, city),
+        state = COALESCE($9, state),
+        zipcode = COALESCE($10, zipcode),
+        latitude = COALESCE($11, latitude),
+        longitude = COALESCE($12, longitude),
+        timezone = COALESCE($13, timezone),
+        menu_url = COALESCE($14, menu_url),
+        menu_type = COALESCE($15, menu_type),
+        platform = COALESCE($16, platform),
+        platform_dispensary_id = COALESCE($17, platform_dispensary_id),
+        c_name = COALESCE($18, c_name),
+        chain_slug = COALESCE($19, chain_slug),
+        enterprise_id = COALESCE($20, enterprise_id),
+        description = COALESCE($21, description),
+        logo_image = COALESCE($22, logo_image),
+        banner_image = COALESCE($23, banner_image),
+        offer_pickup = COALESCE($24, offer_pickup),
+        offer_delivery = COALESCE($25, offer_delivery),
+        offer_curbside_pickup = COALESCE($26, offer_curbside_pickup),
+        is_medical = COALESCE($27, is_medical),
+        is_recreational = COALESCE($28, is_recreational),
+        status = COALESCE($29, status),
+        country = COALESCE($30, country),
+        slug = COALESCE($31, slug),
         updated_at = CURRENT_TIMESTAMP
-      WHERE id = $17
+      WHERE id = $32
       RETURNING *
     `, [
       name,
       dba_name,
-      company_name,
       website,
       phone,
-      address,
+      email,
+      address1,
+      address2,
       city,
       state,
-      zip,
+      zipcode,
       latitude,
       longitude,
+      timezone,
       menu_url,
       menu_type,
       platform,
       platform_dispensary_id,
+      c_name,
+      chain_slug,
+      enterprise_id,
+      description,
+      logo_image,
+      banner_image,
+      offer_pickup,
+      offer_delivery,
+      offer_curbside_pickup,
+      is_medical,
+      is_recreational,
+      status,
+      country,
       slug,
       id
     ]);

backend/src/routes/stores.ts

@@ -70,7 +70,7 @@ function detectProvider(menuUrl: string | null): string {
 // Get all stores (from dispensaries table)
 router.get('/', async (req, res) => {
   try {
-    const { city, state, menu_type } = req.query;
+    const { city, state, menu_type, crawl_enabled, dutchie_verified } = req.query;
 
     let query = `
       SELECT
@@ -79,18 +79,36 @@ router.get('/', async (req, res) => {
         slug,
         city,
         state,
-        address,
-        zip,
+        address1,
+        address2,
+        zipcode,
         phone,
         website,
+        email,
         latitude,
         longitude,
+        timezone,
         menu_url,
         menu_type,
         platform,
         platform_dispensary_id,
+        c_name,
+        chain_slug,
+        enterprise_id,
+        description,
+        logo_image,
+        banner_image,
+        offer_pickup,
+        offer_delivery,
+        offer_curbside_pickup,
+        is_medical,
+        is_recreational,
+        status,
+        country,
         product_count,
         last_crawl_at,
+        crawl_enabled,
+        dutchie_verified,
         created_at,
         updated_at
       FROM dispensaries
@@ -99,21 +117,45 @@ router.get('/', async (req, res) => {
     const params: any[] = [];
     const conditions: string[] = [];
 
+    // Filter by city (partial match)
     if (city) {
       conditions.push(`city ILIKE $${params.length + 1}`);
-      params.push(city);
+      params.push(`%${city}%`);
     }
 
+    // Filter by state
     if (state) {
       conditions.push(`state = $${params.length + 1}`);
       params.push(state);
     }
 
+    // Filter by menu_type
     if (menu_type) {
       conditions.push(`menu_type = $${params.length + 1}`);
       params.push(menu_type);
     }
 
+    // Filter by crawl_enabled - defaults to showing only enabled
+    if (crawl_enabled === 'false' || crawl_enabled === '0') {
+      // Explicitly show disabled only
+      conditions.push(`(crawl_enabled = false OR crawl_enabled IS NULL)`);
+    } else if (crawl_enabled === 'all') {
+      // Show all (no filter)
+    } else {
+      // Default: show only enabled
+      conditions.push(`crawl_enabled = true`);
+    }
+
+    // Filter by dutchie_verified
+    if (dutchie_verified !== undefined) {
+      const verified = dutchie_verified === 'true' || dutchie_verified === '1';
+      if (verified) {
+        conditions.push(`dutchie_verified = true`);
+      } else {
+        conditions.push(`(dutchie_verified = false OR dutchie_verified IS NULL)`);
+      }
+    }
+
     if (conditions.length > 0) {
       query += ` WHERE ${conditions.join(' AND ')}`;
     }
@@ -129,7 +171,7 @@ router.get('/', async (req, res) => {
       ...calculateFreshness(row.last_crawl_at)
     }));
 
-    res.json({ stores });
+    res.json({ stores, total: result.rowCount });
   } catch (error) {
     console.error('Error fetching stores:', error);
     res.status(500).json({ error: 'Failed to fetch stores' });
@@ -148,18 +190,33 @@ router.get('/:id', async (req, res) => {
         slug,
         city,
         state,
-        address,
-        zip,
+        address1,
+        address2,
+        zipcode,
         phone,
         website,
+        email,
         dba_name,
-        company_name,
         latitude,
         longitude,
+        timezone,
         menu_url,
         menu_type,
         platform,
         platform_dispensary_id,
+        c_name,
+        chain_slug,
+        enterprise_id,
+        description,
+        logo_image,
+        banner_image,
+        offer_pickup,
+        offer_delivery,
+        offer_curbside_pickup,
+        is_medical,
+        is_recreational,
+        status,
+        country,
         product_count,
         last_crawl_at,
         raw_metadata,
@@ -203,16 +260,32 @@ router.post('/', requireRole('superadmin', 'admin'), async (req, res) => {
       slug,
       city,
       state,
-      address,
-      zip,
+      address1,
+      address2,
+      zipcode,
       phone,
       website,
+      email,
       menu_url,
       menu_type,
       platform,
       platform_dispensary_id,
+      c_name,
+      chain_slug,
+      enterprise_id,
       latitude,
-      longitude
+      longitude,
+      timezone,
+      description,
+      logo_image,
+      banner_image,
+      offer_pickup,
+      offer_delivery,
+      offer_curbside_pickup,
+      is_medical,
+      is_recreational,
+      status,
+      country
     } = req.body;
 
     if (!name || !slug || !city || !state) {
@@ -221,16 +294,19 @@ router.post('/', requireRole('superadmin', 'admin'), async (req, res) => {
 
     const result = await pool.query(`
       INSERT INTO dispensaries (
-        name, slug, city, state, address, zip, phone, website,
-        menu_url, menu_type, platform, platform_dispensary_id,
-        latitude, longitude, created_at, updated_at
+        name, slug, city, state, address1, address2, zipcode, phone, website, email,
+        menu_url, menu_type, platform, platform_dispensary_id, c_name, chain_slug, enterprise_id,
+        latitude, longitude, timezone, description, logo_image, banner_image,
+        offer_pickup, offer_delivery, offer_curbside_pickup, is_medical, is_recreational, status, country,
+        created_at, updated_at
       )
-      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
+      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
       RETURNING *
     `, [
-      name, slug, city, state, address, zip, phone, website,
-      menu_url, menu_type, platform || 'dutchie', platform_dispensary_id,
-      latitude, longitude
+      name, slug, city, state, address1, address2, zipcode, phone, website, email,
+      menu_url, menu_type, platform || 'dutchie', platform_dispensary_id, c_name, chain_slug, enterprise_id,
+      latitude, longitude, timezone, description, logo_image, banner_image,
+      offer_pickup, offer_delivery, offer_curbside_pickup, is_medical, is_recreational, status, country || 'United States'
     ]);
 
     res.status(201).json(result.rows[0]);
@@ -253,16 +329,32 @@ router.put('/:id', requireRole('superadmin', 'admin'), async (req, res) => {
       slug,
       city,
       state,
-      address,
-      zip,
+      address1,
+      address2,
+      zipcode,
       phone,
       website,
+      email,
       menu_url,
       menu_type,
       platform,
       platform_dispensary_id,
+      c_name,
+      chain_slug,
+      enterprise_id,
       latitude,
-      longitude
+      longitude,
+      timezone,
+      description,
+      logo_image,
+      banner_image,
+      offer_pickup,
+      offer_delivery,
+      offer_curbside_pickup,
+      is_medical,
+      is_recreational,
+      status,
+      country
     } = req.body;
 
     const result = await pool.query(`
@@ -272,23 +364,40 @@ router.put('/:id', requireRole('superadmin', 'admin'), async (req, res) => {
         slug = COALESCE($2, slug),
         city = COALESCE($3, city),
         state = COALESCE($4, state),
-        address = COALESCE($5, address),
-        zip = COALESCE($6, zip),
-        phone = COALESCE($7, phone),
-        website = COALESCE($8, website),
-        menu_url = COALESCE($9, menu_url),
-        menu_type = COALESCE($10, menu_type),
-        platform = COALESCE($11, platform),
-        platform_dispensary_id = COALESCE($12, platform_dispensary_id),
-        latitude = COALESCE($13, latitude),
-        longitude = COALESCE($14, longitude),
+        address1 = COALESCE($5, address1),
+        address2 = COALESCE($6, address2),
+        zipcode = COALESCE($7, zipcode),
+        phone = COALESCE($8, phone),
+        website = COALESCE($9, website),
+        email = COALESCE($10, email),
+        menu_url = COALESCE($11, menu_url),
+        menu_type = COALESCE($12, menu_type),
+        platform = COALESCE($13, platform),
+        platform_dispensary_id = COALESCE($14, platform_dispensary_id),
+        c_name = COALESCE($15, c_name),
+        chain_slug = COALESCE($16, chain_slug),
+        enterprise_id = COALESCE($17, enterprise_id),
+        latitude = COALESCE($18, latitude),
+        longitude = COALESCE($19, longitude),
+        timezone = COALESCE($20, timezone),
+        description = COALESCE($21, description),
+        logo_image = COALESCE($22, logo_image),
+        banner_image = COALESCE($23, banner_image),
+        offer_pickup = COALESCE($24, offer_pickup),
+        offer_delivery = COALESCE($25, offer_delivery),
+        offer_curbside_pickup = COALESCE($26, offer_curbside_pickup),
+        is_medical = COALESCE($27, is_medical),
+        is_recreational = COALESCE($28, is_recreational),
+        status = COALESCE($29, status),
+        country = COALESCE($30, country),
         updated_at = CURRENT_TIMESTAMP
-      WHERE id = $15
+      WHERE id = $31
       RETURNING *
     `, [
-      name, slug, city, state, address, zip, phone, website,
-      menu_url, menu_type, platform, platform_dispensary_id,
-      latitude, longitude, id
+      name, slug, city, state, address1, address2, zipcode, phone, website, email,
+      menu_url, menu_type, platform, platform_dispensary_id, c_name, chain_slug, enterprise_id,
+      latitude, longitude, timezone, description, logo_image, banner_image,
+      offer_pickup, offer_delivery, offer_curbside_pickup, is_medical, is_recreational, status, country, id
     ]);
 
     if (result.rows.length === 0) {

cannaiq/src/pages/Home.tsx

@@ -18,6 +18,9 @@ import {
   ShoppingBag,
   LineChart
 } from 'lucide-react';
+
+const API_URL = import.meta.env.VITE_API_URL || '';
+const PLUGIN_DOWNLOAD_URL = `${API_URL}/downloads/cannaiq-menus-1.5.3.zip`;
 import { api } from '../lib/api';
 
 interface VersionInfo {
@@ -440,8 +443,9 @@ export function Home() {
                   Go to /admin
                 </Link>
                 <a
-                  href="/downloads/cannaiq-menus-1.5.3.zip"
+                  href={PLUGIN_DOWNLOAD_URL}
                   className="flex items-center justify-center gap-2 border-2 border-emerald-600 text-emerald-700 font-semibold py-3 px-6 rounded-lg hover:bg-emerald-50 transition-colors"
+                  download
                 >
                   <Code className="w-5 h-5" />
                   Download WordPress Plugin
@@ -497,7 +501,7 @@ export function Home() {
             <div className="flex items-center gap-6 text-gray-400 text-sm">
               <Link to="/login" className="hover:text-white transition-colors">Sign in</Link>
               <a href="mailto:hello@cannaiq.co" className="hover:text-white transition-colors">Contact</a>
-              <a href="/downloads/cannaiq-menus-1.5.3.zip" className="hover:text-white transition-colors">WordPress Plugin</a>
+              <a href={PLUGIN_DOWNLOAD_URL} className="hover:text-white transition-colors" download>WordPress Plugin</a>
             </div>
           </div>
 

k8s/ingress.yaml

@@ -102,6 +102,13 @@ spec:
                 name: scraper
                 port:
                   number: 80
+          - path: /downloads
+            pathType: Prefix
+            backend:
+              service:
+                name: scraper
+                port:
+                  number: 80
           - path: /
             pathType: Prefix
             backend:
@@ -119,6 +126,13 @@ spec:
                 name: scraper
                 port:
                   number: 80
+          - path: /downloads
+            pathType: Prefix
+            backend:
+              service:
+                name: scraper
+                port:
+                  number: 80
           - path: /
             pathType: Prefix
             backend: