← back to 4square Admin
feat: patternbank + vintage-wallpaper archive sources
724d1f82cce532365b7c638a7914c37c3b5e1af1 · 2026-05-14 09:37:16 -0700 · Steve
- new poolPB (patternbank_archive db) + poolVW (vintage_wallpaper_archive db) with graceful safe() catch if db absent on a given machine
- /api/sources expands left rail when archives exist:
Patternbank archive (1,802) + top 20 categories
Vintage Hannah's Treasures (494) + decade buckets
- /api/products handles patternbank:all|cat:<c> and vintage:all|decade:<d> with same q + sort + offset semantics
- /img/:id handles patternbank (local file → remote_url fallback) + vintage (redirect to remote src)
- both kinds rendered readonly with internal-only license badge (PB / decade pill); deep-link button → source_url
- new sort axes: designer/category for patternbank, era ↑↓/vendor for vintage
Files touched
Diff
commit 724d1f82cce532365b7c638a7914c37c3b5e1af1
Author: Steve <steve@designerwallcoverings.com>
Date: Thu May 14 09:37:16 2026 -0700
feat: patternbank + vintage-wallpaper archive sources
- new poolPB (patternbank_archive db) + poolVW (vintage_wallpaper_archive db) with graceful safe() catch if db absent on a given machine
- /api/sources expands left rail when archives exist:
Patternbank archive (1,802) + top 20 categories
Vintage Hannah's Treasures (494) + decade buckets
- /api/products handles patternbank:all|cat:<c> and vintage:all|decade:<d> with same q + sort + offset semantics
- /img/:id handles patternbank (local file → remote_url fallback) + vintage (redirect to remote src)
- both kinds rendered readonly with internal-only license badge (PB / decade pill); deep-link button → source_url
- new sort axes: designer/category for patternbank, era ↑↓/vendor for vintage
---
index.html | 54 +++++++++++++++------
server.js | 156 +++++++++++++++++++++++++++++++++++++++++++++++++++----------
2 files changed, 172 insertions(+), 38 deletions(-)
diff --git a/index.html b/index.html
index 0d89ad2..42ddfa1 100644
--- a/index.html
+++ b/index.html
@@ -230,6 +230,17 @@ const SORT_SHOPIFY = [
['vendor','Vendor'],['status','Status'],
['price-up','Price ↑'],['price-down','Price ↓'],
];
+const SORT_PATTERNBANK = [
+ ['newest','Newest ingested'],['oldest','Oldest ingested'],
+ ['title','Title A→Z'],['title-desc','Title Z→A'],
+ ['designer','Designer'],['category','Category'],
+];
+const SORT_VINTAGE = [
+ ['newest','Newest ingested'],['oldest','Oldest ingested'],
+ ['title','Title A→Z'],['title-desc','Title Z→A'],
+ ['era','Era (oldest first)'],['era-desc','Era (newest first)'],
+ ['vendor','Vendor'],
+];
const toast = (msg, kind='ok') => {
const t = $('#toast'); t.textContent = msg; t.className = 'show ' + kind;
@@ -278,7 +289,10 @@ function switchTo(srcId) {
function populateSortOptions(kind) {
const sel = $('#sort');
sel.innerHTML = '';
- const opts = kind === 'shopify' ? SORT_SHOPIFY : SORT_WALLCO;
+ const opts = kind === 'shopify' ? SORT_SHOPIFY
+ : kind === 'patternbank' ? SORT_PATTERNBANK
+ : kind === 'vintage' ? SORT_VINTAGE
+ : SORT_WALLCO;
for (const [v, l] of opts) {
const o = document.createElement('option');
o.value = v; o.textContent = l;
@@ -371,38 +385,52 @@ function renderMatrix(wrap) {
attachCardHandlers();
}
-const SHOPIFY_STORE_SLUG = 'designer-laboratory-sandbox'; // for admin deep links
-function deepLinks(r, isShopify) {
- if (isShopify) {
+const SHOPIFY_STORE_SLUG = 'designer-laboratory-sandbox';
+function deepLinks(r, kind) {
+ if (kind === 'shopify') {
const links = [];
if (r.shopify_id) links.push(`<a target="_blank" rel="noopener" href="https://admin.shopify.com/store/${SHOPIFY_STORE_SLUG}/products/${r.shopify_id}" title="Open in Shopify Admin">S</a>`);
if (r.handle) links.push(`<a target="_blank" rel="noopener" href="https://designerwallcoverings.com/products/${r.handle}" title="Open on DW storefront">↗</a>`);
return links.join('');
}
- // wallco
+ if (kind === 'patternbank' || kind === 'vintage') {
+ return r.source_url ? `<a target="_blank" rel="noopener" href="${esc(r.source_url)}" title="Open source page">↗</a>` : '';
+ }
return `<a target="_blank" rel="noopener" href="https://wallco.ai/design/${r.id}" title="Open on wallco.ai">↗</a>`;
}
function cardHtml(r, opts={}) {
const ro = CURRENT.readonly ? 'readonly' : '';
const sel = selected.has(r.id) ? 'selected' : '';
- const isShopify = CURRENT.kind === 'shopify';
- const imgSrc = `/img/${r.id}?kind=${CURRENT.kind}`;
- let badge = '';
- if (isShopify) {
+ const kind = CURRENT.kind;
+ const imgSrc = `/img/${encodeURIComponent(r.id)}?kind=${kind}`;
+ let badge = '', dig = '', title = '', sub = '';
+ if (kind === 'shopify') {
const cls = `shopify-${(r.status||'').toLowerCase()}`;
badge = `<span class="badge ${cls}">${esc((r.status||'?').toUpperCase())}</span>`;
+ dig = r.dw_sku || r.sku || r.handle || r.shopify_id;
+ title = r.title;
+ sub = `${esc(r.vendor||'')}${r.price?' · $'+Number(r.price).toFixed(2):''}`;
+ } else if (kind === 'patternbank') {
+ badge = `<span class="badge" style="background:#3a2a4a;color:#c89cd8;left:6px">PB</span>`;
+ dig = r.slug || r.id;
+ title = r.title || '';
+ sub = `${esc(r.designer||'')}${r.category?' · '+esc(r.category):''}`;
+ } else if (kind === 'vintage') {
+ badge = `<span class="badge" style="background:#3a3a16;color:#d8d27a;left:6px">${r.era_decade?esc(r.era_decade)+'s':'VINTAGE'}</span>`;
+ dig = r.handle || `#${r.id}`;
+ title = r.title || '';
+ sub = `${esc(r.vendor||'')}${r.era_year?' · '+esc(r.era_year):''}`;
} else {
badge = `<span class="badge ${r.is_published?'pub':'draft'}">${r.is_published?'PUBLISHED':'DRAFT'}</span>`;
+ dig = r.dig_number || `#${r.id}`;
+ title = opts.matrixLabel || r.category || '';
}
- const dig = isShopify ? (r.dw_sku || r.sku || r.handle || r.shopify_id) : (r.dig_number || `#${r.id}`);
- const title = isShopify ? r.title : (opts.matrixLabel || r.category || '');
- const sub = isShopify ? `${esc(r.vendor||'')}${r.price?' · $'+Number(r.price).toFixed(2):''}` : '';
return `
<div class="card ${sel} ${ro}" data-id="${r.id}">
${badge}
<span class="cb">✓</span>
- <div class="actions" onclick="event.stopPropagation()">${deepLinks(r, isShopify)}</div>
+ <div class="actions" onclick="event.stopPropagation()">${deepLinks(r, kind)}</div>
<img loading="lazy" src="${imgSrc}" alt="">
<div class="meta">
<div class="dig">${esc(dig)}</div>
diff --git a/server.js b/server.js
index 479a51f..3b10317 100644
--- a/server.js
+++ b/server.js
@@ -19,7 +19,11 @@ const SPEC = JSON.parse(fs.readFileSync(path.join(WALLCO, 'data/fliepaper-bugs/
fs.mkdirSync(QUEUE, { recursive: true });
fs.mkdirSync(TRASH, { recursive: true });
-const pool = new Pool({ host: '/tmp', user: 'stevestudio2', database: 'dw_unified' });
+const pool = new Pool({ host: '/tmp', user: 'stevestudio2', database: 'dw_unified' });
+const poolPB = new Pool({ host: '/tmp', user: 'stevestudio2', database: 'patternbank_archive' });
+const poolVW = new Pool({ host: '/tmp', user: 'stevestudio2', database: 'vintage_wallpaper_archive' });
+// Gracefully handle "archive db doesn't exist on this machine" by catching at use-site.
+const safe = (p, sql, params) => p.query(sql, params).catch(e => ({ _err: e.message, rows: [] }));
const app = express();
app.use(express.json());
@@ -27,34 +31,59 @@ app.use(express.json());
// Tree of selectable data sources for the left rail.
app.get('/api/sources', async (_req, res) => {
try {
- const [wallcoCats, wallcoTotal, shopifyVendors, shopifyTotal, shopifyByStatus] = await Promise.all([
+ const [wallcoCats, wallcoTotal, shopifyVendors, shopifyTotal, shopifyByStatus,
+ pbTotal, pbCats, vwTotal, vwDecades] = await Promise.all([
pool.query(`SELECT category, COUNT(*)::int AS n FROM spoon_all_designs WHERE category IS NOT NULL GROUP BY category ORDER BY 2 DESC`),
pool.query(`SELECT COUNT(*)::int AS n FROM spoon_all_designs`),
pool.query(`SELECT vendor, COUNT(*)::int AS n FROM shopify_products WHERE vendor IS NOT NULL GROUP BY vendor ORDER BY 2 DESC LIMIT 30`),
pool.query(`SELECT COUNT(*)::int AS n FROM shopify_products`),
pool.query(`SELECT status, COUNT(*)::int AS n FROM shopify_products WHERE status IS NOT NULL GROUP BY status ORDER BY 2 DESC`),
+ safe(poolPB, `SELECT COUNT(*)::int AS n FROM patterns`),
+ safe(poolPB, `SELECT category, COUNT(*)::int AS n FROM patterns WHERE category IS NOT NULL GROUP BY category ORDER BY 2 DESC LIMIT 20`),
+ safe(poolVW, `SELECT COUNT(*)::int AS n FROM products`),
+ safe(poolVW, `SELECT era_decade, COUNT(*)::int AS n FROM products WHERE era_decade IS NOT NULL GROUP BY era_decade ORDER BY 1`),
]);
- res.json({
- ok: true,
- sources: [
- {
- id: 'wallco', label: 'wallco.ai', kind: 'wallco', total: wallcoTotal.rows[0].n,
- children: [
- { id: 'wallco:all', label: 'all designs', n: wallcoTotal.rows[0].n },
- ...wallcoCats.rows.map(r => ({ id: `wallco:${r.category}`, label: r.category, n: r.n, special: r.category === 'fliepaper-bugs' ? 'matrix' : null }))
- ],
- },
- {
- id: 'shopify', label: 'DW Shopify', kind: 'shopify', total: shopifyTotal.rows[0].n, readonly: true,
- children: [
- { id: 'shopify:all', label: 'all products', n: shopifyTotal.rows[0].n },
- ...shopifyByStatus.rows.map(r => ({ id: `shopify:status:${r.status}`, label: `status: ${r.status}`, n: r.n })),
- { id: '__divider__' },
- ...shopifyVendors.rows.map(r => ({ id: `shopify:vendor:${r.vendor}`, label: r.vendor, n: r.n }))
- ],
- },
- ],
- });
+ const sources = [
+ {
+ id: 'wallco', label: 'wallco.ai', kind: 'wallco', total: wallcoTotal.rows[0].n,
+ children: [
+ { id: 'wallco:all', label: 'all designs', n: wallcoTotal.rows[0].n },
+ ...wallcoCats.rows.map(r => ({ id: `wallco:${r.category}`, label: r.category, n: r.n, special: r.category === 'fliepaper-bugs' ? 'matrix' : null }))
+ ],
+ },
+ {
+ id: 'shopify', label: 'DW Shopify', kind: 'shopify', total: shopifyTotal.rows[0].n, readonly: true,
+ children: [
+ { id: 'shopify:all', label: 'all products', n: shopifyTotal.rows[0].n },
+ ...shopifyByStatus.rows.map(r => ({ id: `shopify:status:${r.status}`, label: `status: ${r.status}`, n: r.n })),
+ { id: '__divider__' },
+ ...shopifyVendors.rows.map(r => ({ id: `shopify:vendor:${r.vendor}`, label: r.vendor, n: r.n }))
+ ],
+ },
+ ];
+ if (pbTotal.rows[0]?.n) {
+ sources.push({
+ id: 'patternbank', label: 'Patternbank archive', kind: 'patternbank',
+ total: pbTotal.rows[0].n, readonly: true, license_note: 'internal-only · owned_by_steve=false',
+ children: [
+ { id: 'patternbank:all', label: 'all patterns', n: pbTotal.rows[0].n },
+ { id: '__divider__' },
+ ...pbCats.rows.map(r => ({ id: `patternbank:cat:${r.category}`, label: r.category, n: r.n })),
+ ],
+ });
+ }
+ if (vwTotal.rows[0]?.n) {
+ sources.push({
+ id: 'vintage', label: 'Vintage (Hannah\'s Treasures)', kind: 'vintage',
+ total: vwTotal.rows[0].n, readonly: true, license_note: 'internal-only · owned_by_steve=false',
+ children: [
+ { id: 'vintage:all', label: 'all products', n: vwTotal.rows[0].n },
+ { id: '__divider__' },
+ ...vwDecades.rows.map(r => ({ id: `vintage:decade:${r.era_decade}`, label: `${r.era_decade}s`, n: r.n })),
+ ],
+ });
+ }
+ res.json({ ok: true, sources });
} catch (e) {
res.status(500).json({ ok: false, error: e.message });
}
@@ -163,6 +192,60 @@ app.get('/api/products', async (req, res) => {
});
}
+ if (source.startsWith('patternbank:')) {
+ const parts = source.split(':');
+ const params = [];
+ let where = '1=1';
+ if (parts[1] === 'cat' && parts[2]) { params.push(parts.slice(2).join(':')); where = `category = $${params.length}`; }
+ if (q) {
+ params.push(`%${q}%`); const i = params.length;
+ where += ` AND (title ILIKE $${i} OR designer_display_name ILIKE $${i} OR slug ILIKE $${i} OR tags::text ILIKE $${i})`;
+ }
+ const orderBy = ({
+ 'newest':'ingested_at DESC','oldest':'ingested_at ASC',
+ 'title':'title ASC NULLS LAST','title-desc':'title DESC NULLS LAST',
+ 'designer':'designer_display_name ASC NULLS LAST',
+ 'category':'category ASC NULLS LAST',
+ })[sort] || 'ingested_at DESC';
+ const countSql = `SELECT COUNT(*)::int AS n FROM patterns WHERE ${where}`;
+ const listSql = `SELECT p.pattern_id AS id, p.slug, p.title, p.designer_display_name AS designer,
+ p.category, p.subcategory, p.tags, p.colors_hex, p.is_seamless,
+ p.source_url, p.ingested_at,
+ (SELECT local_path FROM pattern_images i WHERE i.pattern_id=p.pattern_id ORDER BY position NULLS LAST LIMIT 1) AS local_path,
+ (SELECT remote_url FROM pattern_images i WHERE i.pattern_id=p.pattern_id ORDER BY position NULLS LAST LIMIT 1) AS remote_url
+ FROM patterns p WHERE ${where} ORDER BY ${orderBy} LIMIT ${limit} OFFSET ${offset}`;
+ const [c, r] = await Promise.all([poolPB.query(countSql, params), poolPB.query(listSql, params)]);
+ return res.json({ ok:true, kind:'patternbank', source, sort, limit, offset,
+ total: c.rows[0].n, has_more: (offset + r.rows.length) < c.rows[0].n,
+ rows: r.rows, readonly: true, license_note: 'patternbank — internal only' });
+ }
+
+ if (source.startsWith('vintage:')) {
+ const parts = source.split(':');
+ const params = [];
+ let where = '1=1';
+ if (parts[1] === 'decade' && parts[2]) { params.push(parseInt(parts[2],10)); where = `era_decade = $${params.length}`; }
+ if (q) {
+ params.push(`%${q}%`); const i = params.length;
+ where += ` AND (title ILIKE $${i} OR handle ILIKE $${i} OR vendor ILIKE $${i} OR tags::text ILIKE $${i})`;
+ }
+ const orderBy = ({
+ 'newest':'ingested_at DESC','oldest':'ingested_at ASC',
+ 'title':'title ASC NULLS LAST','title-desc':'title DESC NULLS LAST',
+ 'era':'era_year ASC NULLS LAST','era-desc':'era_year DESC NULLS LAST',
+ 'vendor':'vendor ASC NULLS LAST',
+ })[sort] || 'ingested_at DESC';
+ const countSql = `SELECT COUNT(*)::int AS n FROM products WHERE ${where}`;
+ const listSql = `SELECT p.shopify_product_id AS id, p.handle, p.title, p.vendor, p.product_type,
+ p.tags, p.era_decade, p.era_year, p.pattern_width_inches, p.source_url,
+ (SELECT src FROM images i WHERE i.shopify_product_id=p.shopify_product_id ORDER BY position NULLS LAST LIMIT 1) AS image_url
+ FROM products p WHERE ${where} ORDER BY ${orderBy} LIMIT ${limit} OFFSET ${offset}`;
+ const [c, r] = await Promise.all([poolVW.query(countSql, params), poolVW.query(listSql, params)]);
+ return res.json({ ok:true, kind:'vintage', source, sort, limit, offset,
+ total: c.rows[0].n, has_more: (offset + r.rows.length) < c.rows[0].n,
+ rows: r.rows, readonly: true, license_note: 'Hannah\'s Treasures — internal only' });
+ }
+
res.status(400).json({ ok:false, error: 'unknown source' });
} catch (e) {
res.status(500).json({ ok: false, error: e.message });
@@ -201,16 +284,39 @@ app.get('/api/product/:id', async (req, res) => {
// ── /img/:id?kind=wallco|shopify ──────────────────────────────────────
app.get('/img/:id', async (req, res) => {
- const id = parseInt(req.params.id, 10);
+ const idRaw = req.params.id;
const kind = String(req.query.kind || 'wallco');
- if (!Number.isFinite(id)) return res.status(400).send('bad id');
try {
if (kind === 'shopify') {
+ const id = parseInt(idRaw, 10);
const { rows } = await pool.query(`SELECT image_url FROM shopify_products WHERE id=$1`, [id]);
const u = rows[0]?.image_url;
if (!u) return res.status(404).send('no image');
return res.redirect(302, u);
}
+ if (kind === 'patternbank') {
+ // id is text pattern_id
+ const { rows } = await poolPB.query(
+ `SELECT local_path, remote_url FROM pattern_images WHERE pattern_id=$1 ORDER BY position NULLS LAST LIMIT 1`, [idRaw]);
+ const r = rows[0];
+ if (!r) return res.status(404).send('no image');
+ if (r.local_path && fs.existsSync(r.local_path)) {
+ res.type(path.extname(r.local_path) === '.jpg' ? 'jpg' : 'png');
+ return fs.createReadStream(r.local_path).pipe(res);
+ }
+ if (r.remote_url) return res.redirect(302, r.remote_url);
+ return res.status(404).send('no image');
+ }
+ if (kind === 'vintage') {
+ const id = parseInt(idRaw, 10);
+ const { rows } = await poolVW.query(
+ `SELECT src FROM images WHERE shopify_product_id=$1 ORDER BY position NULLS LAST LIMIT 1`, [id]);
+ const u = rows[0]?.src;
+ if (!u) return res.status(404).send('no image');
+ return res.redirect(302, u);
+ }
+ // default wallco
+ const id = parseInt(idRaw, 10);
const { rows } = await pool.query(`SELECT local_path FROM spoon_all_designs WHERE id=$1`, [id]);
const p = rows[0]?.local_path;
if (!p || !fs.existsSync(p)) return res.status(404).send('img missing');
← c8d2297 feat: full keyboard navigation
·
back to 4square Admin
·
feat: source-context header bar + CSV export of selected row 0598058 →