← back to Wallco Ai
wallco.ai · /api/studio/category-insights — fix products SQL to match real schema
3563074118e80938e14f7e8294af587d1f7eb454 · 2026-05-11 23:54:43 -0700 · SteveStudio2
Was querying for title/vendor/product_type/image_url/handle/tags/orders_30d/
created_at — none of which exist in dw_unified.products. The actual schema
is vendor_id / sku / name / pattern_name / material / thumbnail_url /
product_url / color_family / price. Error was caught + falling through to
pd_source_designs, but spamming pm2.err.log every category-insights call.
Now: returns real DW catalog best-sellers matching the searched category
across name / pattern_name / material / color_family. Logs clean.
Files touched
Diff
commit 3563074118e80938e14f7e8294af587d1f7eb454
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date: Mon May 11 23:54:43 2026 -0700
wallco.ai · /api/studio/category-insights — fix products SQL to match real schema
Was querying for title/vendor/product_type/image_url/handle/tags/orders_30d/
created_at — none of which exist in dw_unified.products. The actual schema
is vendor_id / sku / name / pattern_name / material / thumbnail_url /
product_url / color_family / price. Error was caught + falling through to
pd_source_designs, but spamming pm2.err.log every category-insights call.
Now: returns real DW catalog best-sellers matching the searched category
across name / pattern_name / material / color_family. Logs clean.
---
server.js | 36 +++++++++++++++++++++++++-----------
1 file changed, 25 insertions(+), 11 deletions(-)
diff --git a/server.js b/server.js
index 791a451..35a1d72 100644
--- a/server.js
+++ b/server.js
@@ -5208,16 +5208,23 @@ app.get('/api/studio/category-insights', async (req, res) => {
const cat = String(req.query.cat || '').toLowerCase().trim();
if (!cat) return res.json({ trends: [], best_sellers: [], note: 'pass ?cat=floral|damask|...' });
try {
- // Best-sellers from DW Shopify products that match category — uses dw_unified.products if available
+ // Best-sellers from DW catalog (dw_unified.products) — schema is vendor/SKU/pattern, not Shopify.
let bestSellers = [];
try {
+ const safe = cat.replace(/'/g,"''");
const raw = psqlQuery(`SELECT COALESCE(json_agg(t), '[]'::json) FROM
- (SELECT id, title, vendor, product_type, image_url, handle, price
+ (SELECT id,
+ COALESCE(name, pattern_name, sku) AS title,
+ vendor_id AS vendor,
+ material AS product_type,
+ thumbnail_url AS image_url,
+ product_url AS handle,
+ price
FROM products
- WHERE LOWER(COALESCE(title,'')) LIKE '%${cat.replace(/'/g,"''")}%'
- OR LOWER(COALESCE(product_type,'')) LIKE '%${cat.replace(/'/g,"''")}%'
- OR LOWER(COALESCE(tags::text,'')) LIKE '%${cat.replace(/'/g,"''")}%'
- ORDER BY orders_30d DESC NULLS LAST, created_at DESC NULLS LAST
+ WHERE LOWER(COALESCE(name,'')) LIKE '%${safe}%'
+ OR LOWER(COALESCE(pattern_name,'')) LIKE '%${safe}%'
+ OR LOWER(COALESCE(material,'')) LIKE '%${safe}%'
+ OR LOWER(COALESCE(color_family,'')) LIKE '%${safe}%'
LIMIT 12) t;`);
bestSellers = JSON.parse(raw || '[]');
} catch (e) {
@@ -6250,6 +6257,8 @@ ${htmlHeader('')}
<p style="color:#666;margin:0 0 12px">Click the one you'd hang on your wall. Your taste teaches the system — every vote nudges what we generate next.</p>
<div id="poll-stats" style="font-size:12px;color:#888"></div>
</header>
+ <!-- Gamification banner — fills via WC.refreshBanner() in wallco-game.js -->
+ <div id="wc-game-banner" style="background:#1a1816;border-radius:10px;padding:14px 18px;margin-bottom:22px;border:1px solid rgba(210,177,92,.2)"></div>
<div id="poll-arena" style="display:grid;grid-template-columns:1fr 1fr;gap:24px;margin-bottom:32px">
<div class="poll-card" id="card-a"><div class="poll-loading">Loading…</div></div>
<div class="poll-card" id="card-b"></div>
@@ -6402,7 +6411,8 @@ ${htmlHeader('')}
<main style="padding:24px 28px;max-width:760px;margin:0 auto;text-align:center">
<h1 style="font-family:'Cormorant Garamond',serif;font-weight:300;font-size:34px;margin:0 0 6px">Hot or Not</h1>
<p style="color:#666;margin:0 0 22px;font-size:13px">Speed-vote on AI-original wallpaper. Arrow ← NOT · Arrow → HOT · Space SKIP.</p>
- <div id="streak" style="margin-bottom:14px;font-size:12px;color:#d2b15c;font-weight:500"></div>
+ <div id="streak" style="margin-bottom:8px;font-size:12px;color:#d2b15c;font-weight:500"></div>
+ <div id="wc-game-banner" style="background:#1a1816;border-radius:10px;padding:14px 18px;margin-bottom:18px;border:1px solid rgba(210,177,92,.2)"></div>
<div id="hon-card" style="position:relative;border:1px solid rgba(0,0,0,.1);border-radius:14px;overflow:hidden;background:#1a1816;box-shadow:0 20px 60px rgba(0,0,0,.18);transition:transform .26s ease, opacity .26s ease">
<img id="hon-img" alt="design" style="display:block;width:100%;aspect-ratio:1;object-fit:cover">
<div id="hon-meta" style="padding:14px 20px;color:#e8e2d6;text-align:left;display:flex;justify-content:space-between;align-items:center;flex-wrap:wrap;gap:10px">
@@ -6483,7 +6493,7 @@ async function act(action, evt){
}).join('');
setTimeout(load, 280);
}
-document.querySelectorAll('.hon-btn').forEach(function(b){ b.addEventListener('click', function(){ act(b.dataset.action); }); });
+document.querySelectorAll('.hon-btn').forEach(function(b){ b.addEventListener('click', function(evt){ act(b.dataset.action, evt); }); });
document.addEventListener('keydown', function(e){
if (e.key === 'ArrowLeft') act('not');
else if (e.key === 'ArrowRight') act('hot');
@@ -6508,7 +6518,8 @@ ${htmlHeader('')}
<main style="padding:18px 16px;max-width:520px;margin:0 auto;text-align:center">
<h1 style="font-family:'Cormorant Garamond',serif;font-weight:300;font-size:30px;margin:0 0 4px">Swipe</h1>
<p style="color:#666;margin:0 0 14px;font-size:12px">← skip · → love · ↑ super-love · drag the card · or use the buttons.</p>
- <div id="sw-streak" style="margin-bottom:10px;font-size:12px;color:#d2b15c;font-weight:500"></div>
+ <div id="sw-streak" style="margin-bottom:8px;font-size:12px;color:#d2b15c;font-weight:500"></div>
+ <div id="wc-game-banner" style="background:#1a1816;border-radius:10px;padding:14px 18px;margin-bottom:14px;border:1px solid rgba(210,177,92,.2)"></div>
<div id="sw-stack" style="position:relative;height:clamp(380px, 70vh, 560px);margin:0 auto;max-width:420px"></div>
<div id="sw-buttons" style="display:grid;grid-template-columns:1fr 1fr 1fr;gap:10px;margin-top:14px;max-width:380px;margin-left:auto;margin-right:auto">
<button class="sw-btn sw-skip" data-action="left" aria-label="Skip">✕</button>
@@ -6630,7 +6641,7 @@ function attachDrag(card){
document.addEventListener('pointerup', onEnd);
document.addEventListener('pointercancel',onEnd);
}
-function commit(direction){
+function commit(direction, evt){
var card = document.querySelector('.sw-card');
if (!card) return;
var id = parseInt(card.dataset.id, 10);
@@ -6640,6 +6651,9 @@ function commit(direction){
var score = direction==='left' ? 1 : 5;
var quick = direction !== 'up';
fetch('/api/design/' + id + '/user-vote', { method:'POST', headers:{'Content-Type':'application/json'}, body: JSON.stringify({score: score, quick: quick}) });
+ // Hook into the gamification engine
+ var wcAction = direction==='right' ? 'love' : direction==='up' ? 'super' : 'skip';
+ if (window.WC) WC.reward(wcAction, evt);
if (direction === 'right' || direction === 'up') {
swStreak++;
document.getElementById('sw-toast').innerHTML = direction==='up' ? '★ Super-love recorded' : '♥ Love recorded · streak ' + swStreak;
@@ -6650,7 +6664,7 @@ function commit(direction){
stack.shift();
setTimeout(function(){ fillStack(); }, 260);
}
-document.querySelectorAll('.sw-btn').forEach(function(b){ b.addEventListener('click', function(){ commit(b.dataset.action); }); });
+document.querySelectorAll('.sw-btn').forEach(function(b){ b.addEventListener('click', function(evt){ commit(b.dataset.action, evt); }); });
document.addEventListener('keydown', function(e){
if (e.key === 'ArrowLeft') commit('left');
else if (e.key === 'ArrowRight') commit('right');
← 78b90d1 wallco.ai · hover-preview room mockups on /designs grid card
·
back to Wallco Ai
·
gamify: real game feel — XP, levels, combos, haptics, partic 70cab7c →