visual-search /similar: also return storefront handle (LEFT JOIN shopify_products) so PDP cards link to real product pages

06f58395cfacea2dbeaaffd449db896c6c33cdc5 · 2026-07-08 14:55:13 -0700 · Steve

Files touched

M visual-search/search_service.py

Diff

commit 06f58395cfacea2dbeaaffd449db896c6c33cdc5
Author: Steve <steve@designerwallcoverings.com>
Date:   Wed Jul 8 14:55:13 2026 -0700

    visual-search /similar: also return storefront handle (LEFT JOIN shopify_products) so PDP cards link to real product pages
---
 visual-search/search_service.py | 14 ++++++++------
 1 file changed, 8 insertions(+), 6 deletions(-)

diff --git a/visual-search/search_service.py b/visual-search/search_service.py
index 1cd5d32..127f1cc 100644
--- a/visual-search/search_service.py
+++ b/visual-search/search_service.py
@@ -87,18 +87,20 @@ _FAM_ADJ = {
 
 def load():
     conn = psycopg2.connect(DB); cur = conn.cursor()
-    # LEFT JOIN vendor_catalog for the color/style re-rank signals (dominant_color_hex + ai_styles).
-    # Keyed by dw_sku; rows without a match just carry null fam/styles (soft signals, never required).
+    # LEFT JOIN vendor_catalog for the color/style re-rank signals (dominant_color_hex + ai_styles),
+    # and shopify_products for the storefront `handle` so /similar can return a real PDP link.
+    # Keyed by dw_sku; rows without a match just carry null fam/styles/handle (soft signals, never required).
     cur.execute("""select e.vc_id, e.dw_sku, e.mfr_sku, e.vendor_code, e.pattern_name, e.image_url,
-                          e.embedding, v.dominant_color_hex, v.ai_styles
+                          e.embedding, v.dominant_color_hex, v.ai_styles, sp.handle
                    from image_embeddings e
                    left join vendor_catalog v on v.dw_sku = e.dw_sku
+                   left join shopify_products sp on sp.dw_sku = e.dw_sku
                    where e.embedding is not null""")
     vecs, meta, fam, styles, sku_ix = [], [], [], [], {}
-    for vc_id, dw_sku, mfr, vc, pat, url, emb, hexc, ai_styles in cur:
+    for vc_id, dw_sku, mfr, vc, pat, url, emb, hexc, ai_styles, handle in cur:
         i = len(meta)
         vecs.append(np.frombuffer(bytes(emb), dtype="float32"))
-        meta.append({"vc_id": vc_id, "dw_sku": dw_sku, "mfr_sku": mfr, "vendor": vc, "pattern": pat, "image": url})
+        meta.append({"vc_id": vc_id, "dw_sku": dw_sku, "mfr_sku": mfr, "vendor": vc, "pattern": pat, "image": url, "handle": handle})
         fam.append(_family_from_hex(hexc))
         # ai_styles is jsonb array → normalize to a lowercase set for overlap test
         sset = set()
@@ -217,7 +219,7 @@ class H(BaseHTTPRequestHandler):
             for _rank, base, i in scored[:k]:
                 m = meta[i]
                 res.append({"dw_sku": m["dw_sku"], "image": m["image"], "pattern": m["pattern"],
-                            "vendor": m["vendor"], "score": round(base, 4)})
+                            "vendor": m["vendor"], "handle": m.get("handle"), "score": round(base, 4)})
             self._send(200, {"ok": True, "n": STATE["n"], "query": {
                 "dw_sku": dw_sku, "family": qfam, "styles": sorted(qstyles)}, "results": res})
         except Exception as e:

← 3e6d84f Save EVERY scan (Steve): identify-multi now persists the raw · back to Dw Photo Capture · dwphoto: add SSRF-guarded CORS-scoped /apps/similar proxy ro c473b21 →