← back to Interiordesignershowroom
admin: keyword/id suppression rules (Suppress page) + Vacuum re-apply + Refresh
72708fc32cdb5295b8ae2cf1a6b89dec7cbecce2 · 2026-08-03 10:26:48 -0700 · Steve Abrams
- New suppress_rules table (durable keyword/id hide rules, distinct from source-level
affiliate_settings and the raw suppressed flag).
- /admin/suppress: enter keywords (and/or ids) -> immediately flip suppressed=TRUE on
every current match (title/brand/advertiser). Reuses the pervasive NOT suppressed gate
so hides propagate to /shop, /rooms, guides, and the sitemap.
- Vacuum re-applies all rules (catches products ingested later matching an old keyword);
Refresh reloads/recounts; View shop -> /shop?room=decor&style=modern.
- Per-rule live match/hidden counts, created date+time, Re-apply, Remove, Remove+unhide.
- Verified end-to-end: add velvet (7 hidden), storefront drop confirmed, vacuum idempotent,
remove+unhide restores state; CSRF guard returns 403.
Files touched
M db/schema.sqlM routes/admin.jsM server.js
Diff
commit 72708fc32cdb5295b8ae2cf1a6b89dec7cbecce2
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Aug 3 10:26:48 2026 -0700
admin: keyword/id suppression rules (Suppress page) + Vacuum re-apply + Refresh
- New suppress_rules table (durable keyword/id hide rules, distinct from source-level
affiliate_settings and the raw suppressed flag).
- /admin/suppress: enter keywords (and/or ids) -> immediately flip suppressed=TRUE on
every current match (title/brand/advertiser). Reuses the pervasive NOT suppressed gate
so hides propagate to /shop, /rooms, guides, and the sitemap.
- Vacuum re-applies all rules (catches products ingested later matching an old keyword);
Refresh reloads/recounts; View shop -> /shop?room=decor&style=modern.
- Per-rule live match/hidden counts, created date+time, Re-apply, Remove, Remove+unhide.
- Verified end-to-end: add velvet (7 hidden), storefront drop confirmed, vacuum idempotent,
remove+unhide restores state; CSRF guard returns 403.
---
db/schema.sql | 16 +++++
routes/admin.js | 182 +++++++++++++++++++++++++++++++++++++++++++++++++++++---
server.js | 7 ++-
3 files changed, 197 insertions(+), 8 deletions(-)
diff --git a/db/schema.sql b/db/schema.sql
index 13af964..dc42f08 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -78,3 +78,19 @@ CREATE TABLE IF NOT EXISTS affiliate_settings (
);
-- Only the disabled rows ever need scanning during a storefront query.
CREATE INDEX IF NOT EXISTS idx_affiliate_settings_off ON affiliate_settings (network, advertiser) WHERE enabled = FALSE;
+
+-- Durable keyword/id suppression rules. Distinct from affiliate_settings (which hides
+-- by SOURCE) and from the raw `suppressed` flag (a one-off hide): a rule here is a
+-- persistent record of "any product whose title/brand/advertiser contains this keyword
+-- (or whose id equals this) should be hidden." Adding a rule immediately flips
+-- `suppressed=TRUE` on every current match; "Vacuum" re-applies every rule so products
+-- ingested LATER that match an old keyword get suppressed too. Kept as data (not just a
+-- column flip) so the admin can see, re-apply, and remove what was entered.
+CREATE TABLE IF NOT EXISTS suppress_rules (
+ id BIGSERIAL PRIMARY KEY,
+ kind TEXT NOT NULL DEFAULT 'keyword' CHECK (kind IN ('keyword','id')),
+ value TEXT NOT NULL, -- the keyword text, or the product id as text
+ note TEXT,
+ created_at TIMESTAMPTZ DEFAULT now(),
+ UNIQUE (kind, value)
+);
diff --git a/routes/admin.js b/routes/admin.js
index 5805978..04f5fd4 100644
--- a/routes/admin.js
+++ b/routes/admin.js
@@ -179,7 +179,7 @@ a.ezjoin:hover{background:#6f5c40}
.why{color:#6b6259;font-size:.82rem}
</style></head><body>
<header><h1>Interior Designer’s Showroom — Admin</h1>
-<nav><a href="/admin">Dashboard</a> · <a href="/admin/brands">Brands</a> · <a href="/admin/affiliates">Affiliates</a> · <a href="/">View site ↗</a></nav></header>
+<nav><a href="/admin">Dashboard</a> · <a href="/admin/brands">Brands</a> · <a href="/admin/suppress">Suppress</a> · <a href="/admin/affiliates">Affiliates</a> · <a href="/">View site ↗</a></nav></header>
<main>${body}</main></body></html>`;
router.get('/admin', async (req, res, next) => {
@@ -273,19 +273,31 @@ router.get('/admin', async (req, res, next) => {
} catch (e) { next(e); }
});
+// Reject a non-numeric :id before it hits the bigint column (would 500 otherwise).
+function intId(v) { const n = Number(v); return Number.isInteger(n) && n >= 1 ? n : null; }
+
router.post('/admin/products/:id/featured', async (req, res, next) => {
- try { await db.query(`UPDATE products SET featured = NOT featured, updated_at=now() WHERE id=$1`, [req.params.id]); res.redirect('/admin'); }
- catch (e) { next(e); }
+ try {
+ const id = intId(req.params.id);
+ if (!id) return res.status(404).send('Unknown product.');
+ await db.query(`UPDATE products SET featured = NOT featured, updated_at=now() WHERE id=$1`, [id]); res.redirect('/admin');
+ } catch (e) { next(e); }
});
// Hide/show one product everywhere (kept in the DB, just flagged off — the reversible
// "check it off as no"). The storefront gate lives in lib/catalog.js + lib/rooms.js.
router.post('/admin/products/:id/suppress', async (req, res, next) => {
- try { await db.query(`UPDATE products SET suppressed = NOT suppressed, updated_at=now() WHERE id=$1`, [req.params.id]); res.redirect('/admin'); }
- catch (e) { next(e); }
+ try {
+ const id = intId(req.params.id);
+ if (!id) return res.status(404).send('Unknown product.');
+ await db.query(`UPDATE products SET suppressed = NOT suppressed, updated_at=now() WHERE id=$1`, [id]); res.redirect('/admin');
+ } catch (e) { next(e); }
});
router.post('/admin/guides/:id/published', async (req, res, next) => {
- try { await db.query(`UPDATE guides SET published = NOT published, updated_at=now() WHERE id=$1`, [req.params.id]); res.redirect('/admin'); }
- catch (e) { next(e); }
+ try {
+ const id = intId(req.params.id);
+ if (!id) return res.status(404).send('Unknown guide.');
+ await db.query(`UPDATE guides SET published = NOT published, updated_at=now() WHERE id=$1`, [id]); res.redirect('/admin');
+ } catch (e) { next(e); }
});
// --- Brands: select a whole BRAND LINE and hide/show it across every advertiser --
@@ -487,6 +499,162 @@ router.post('/admin/brands/keyword/apply', async (req, res, next) => {
} catch (e) { next(e); }
});
+// --- Suppress by keyword/id: durable rules that hide products --------------
+// "Enter keywords and those words suppress products" (Steve, 2026-08-03). A rule is
+// a persistent record in suppress_rules; applying it flips `suppressed=TRUE` on every
+// current match (title/brand/advertiser for keywords, id for ids). Because the whole
+// storefront already filters `NOT suppressed`, a suppressed product vanishes from
+// /shop, /rooms, guides, and the sitemap at once. VACUUM re-applies every rule so
+// products ingested LATER that match an old keyword also get hidden. All reversible.
+
+// SQL that matches a keyword rule in title/brand/advertiser. One placeholder ($1)
+// reused thrice. Caller passes the '%'-wrapped value.
+const KW_MATCH = `(title ILIKE $1 OR brand ILIKE $1 OR advertiser ILIKE $1)`;
+
+// Apply ONE rule: suppress its still-visible matches. Returns count newly hidden.
+async function applyRule(kind, value) {
+ if (kind === 'id') {
+ const id = Number(value);
+ if (!Number.isInteger(id) || id <= 0) return 0;
+ const r = await db.query(`UPDATE products SET suppressed=TRUE, updated_at=now() WHERE id=$1 AND NOT suppressed`, [id]);
+ return r.rowCount;
+ }
+ const r = await db.query(`UPDATE products SET suppressed=TRUE, updated_at=now() WHERE NOT suppressed AND ${KW_MATCH}`, ['%' + value + '%']);
+ return r.rowCount;
+}
+
+// Per-rule live stats: total products it matches + how many are currently hidden.
+async function ruleStats(kind, value) {
+ if (kind === 'id') {
+ const r = await db.query(`SELECT count(*) AS total, count(*) FILTER (WHERE suppressed) AS hidden FROM products WHERE id=$1`, [Number(value) || -1]);
+ return { total: Number(r.rows[0].total), hidden: Number(r.rows[0].hidden) };
+ }
+ const r = await db.query(`SELECT count(*) AS total, count(*) FILTER (WHERE suppressed) AS hidden FROM products WHERE ${KW_MATCH}`, ['%' + value + '%']);
+ return { total: Number(r.rows[0].total), hidden: Number(r.rows[0].hidden) };
+}
+
+// The target Steve builds against — surfaced as a one-click "View shop" link.
+const SHOP_PREVIEW = '/shop?room=decor&style=modern';
+
+router.get('/admin/suppress', async (req, res, next) => {
+ try {
+ const done = String(req.query.done || '').slice(0, 300);
+ const { rows: rules } = await db.query(`SELECT id, kind, value, created_at FROM suppress_rules ORDER BY created_at DESC`);
+ // Attach live match/hidden counts to each rule (small table, sequential is fine).
+ for (const r of rules) Object.assign(r, await ruleStats(r.kind, r.value));
+ const totalHidden = (await db.query(`SELECT count(*) AS n FROM products WHERE suppressed`)).rows[0].n;
+
+ const banner = done
+ ? `<p style="background:#e7f3e7;border:1px solid #bcd9bc;border-radius:6px;padding:8px 12px;color:#2f7a2f;margin:0 0 16px">✓ ${esc(done)}</p>` : '';
+
+ const ruleRows = rules.length ? rules.map((r) => {
+ const visible = r.total - r.hidden;
+ return `<tr>
+ <td><span class="pill">${r.kind}</span></td>
+ <td><b>${esc(r.value)}</b></td>
+ <td>${r.total}<div class="net">${r.hidden} hidden${visible ? ` · <span style="color:#b1483c">${visible} still visible</span>` : ''}</div></td>
+ <td>${when(r.created_at)}</td>
+ <td style="white-space:nowrap">
+ ${visible ? `<form method="post" action="/admin/suppress/${r.id}/apply" style="display:inline;margin:0 4px 0 0"><button title="Suppress the ${visible} matches still showing">Re-apply</button></form>` : ''}
+ <form method="post" action="/admin/suppress/${r.id}/remove" style="display:inline;margin:0 4px 0 0"><button title="Remove this rule (products it hid stay hidden)">Remove</button></form>
+ ${r.hidden ? `<form method="post" action="/admin/suppress/${r.id}/remove" style="display:inline;margin:0" onsubmit="return confirm('Remove this rule AND un-hide its ${r.hidden} matching products?')"><input type="hidden" name="unhide" value="1"><button title="Remove the rule and show its products again">Remove + unhide</button></form>` : ''}
+ </td></tr>`;
+ }).join('') : `<tr><td colspan="5" style="color:#8a8178">No suppression keywords yet. Add one above.</td></tr>`;
+
+ res.send(shell(`
+ <h1 style="font-size:1.15rem;margin:0 0 4px">Suppress products by keyword or id</h1>
+ <p class="subtle" style="margin:0 0 16px">Any product whose <b>title, brand, or advertiser</b> contains a keyword here is hidden from the storefront (and the room builder, guides, and sitemap). Reversible.</p>
+ ${banner}
+ <form method="post" action="/admin/suppress/add" style="background:#fff;border:1px solid var(--line);border-radius:8px;padding:16px;margin:0 0 22px;max-width:680px">
+ <label style="display:block;font-weight:600;margin:0 0 4px">Keywords to suppress</label>
+ <textarea name="keywords" rows="3" placeholder="One per line or comma-separated — e.g. clearance refurbished, open box" style="width:100%;padding:8px;border:1px solid var(--line);border-radius:5px;font:inherit"></textarea>
+ <label style="display:block;font-weight:600;margin:12px 0 4px">Product ids to suppress <span class="subtle" style="font-weight:400">(optional, comma/space separated)</span></label>
+ <input name="ids" placeholder="e.g. 1423 1899" style="width:100%;padding:8px;border:1px solid var(--line);border-radius:5px;font:inherit">
+ <div style="margin-top:14px"><button style="background:#221e19;color:#e6c98a;border-color:#221e19;padding:8px 18px">Suppress →</button></div>
+ </form>
+
+ <div style="display:flex;gap:10px;align-items:center;margin:0 0 16px;flex-wrap:wrap">
+ <form method="post" action="/admin/suppress/vacuum" style="margin:0"><button title="Re-apply every rule — suppress any products (e.g. newly-ingested) that now match an existing keyword" style="padding:8px 16px">🧹 Vacuum (re-apply all rules)</button></form>
+ <a href="/admin/suppress" title="Reload — recompute match counts" style="padding:8px 16px;border:1px solid var(--line);border-radius:5px;text-decoration:none;color:var(--ink);background:#fff">↻ Refresh</a>
+ <a href="${SHOP_PREVIEW}" target="_blank" style="padding:8px 16px;border:1px solid var(--line);border-radius:5px;text-decoration:none;color:var(--ink);background:#fff">View shop ↗</a>
+ <span class="subtle" style="margin-left:auto">${totalHidden} product${Number(totalHidden) === 1 ? '' : 's'} hidden total</span>
+ </div>
+
+ <h2>Active suppression rules</h2>
+ <table><tr><th>Kind</th><th>Value</th><th>Matches</th><th>Added</th><th>Actions</th></tr>${ruleRows}</table>
+ `));
+ } catch (e) { next(e); }
+});
+
+// Add keyword/id rules and immediately suppress current matches.
+router.post('/admin/suppress/add', async (req, res, next) => {
+ try {
+ const kws = String(req.body.keywords || '')
+ .split(/[\n,]/).map((s) => s.trim()).filter((s) => s.length >= 2).slice(0, 100);
+ const ids = String(req.body.ids || '')
+ .split(/[\s,]+/).map((s) => s.trim()).filter((s) => /^[0-9]{1,18}$/.test(s)).slice(0, 200);
+ if (!kws.length && !ids.length) return res.redirect('/admin/suppress?done=' + encodeURIComponent('Nothing to add — enter a keyword (2+ chars) or a numeric id.'));
+
+ let hidden = 0, added = 0;
+ for (const kw of kws) {
+ const ins = await db.query(`INSERT INTO suppress_rules (kind, value) VALUES ('keyword', $1) ON CONFLICT (kind, value) DO NOTHING`, [kw]);
+ added += ins.rowCount;
+ hidden += await applyRule('keyword', kw);
+ }
+ for (const id of ids) {
+ const ins = await db.query(`INSERT INTO suppress_rules (kind, value) VALUES ('id', $1) ON CONFLICT (kind, value) DO NOTHING`, [id]);
+ added += ins.rowCount;
+ hidden += await applyRule('id', id);
+ }
+ const msg = `Added ${added} rule${added === 1 ? '' : 's'} · suppressed ${hidden} product${hidden === 1 ? '' : 's'}.`;
+ res.redirect('/admin/suppress?done=' + encodeURIComponent(msg));
+ } catch (e) { next(e); }
+});
+
+// Re-apply ONE rule (suppress its still-visible matches).
+router.post('/admin/suppress/:id/apply', async (req, res, next) => {
+ try {
+ const { rows } = await db.query(`SELECT kind, value FROM suppress_rules WHERE id=$1`, [req.params.id]);
+ if (!rows.length) return res.redirect('/admin/suppress');
+ const n = await applyRule(rows[0].kind, rows[0].value);
+ res.redirect('/admin/suppress?done=' + encodeURIComponent(`Suppressed ${n} more match${n === 1 ? '' : 'es'} for “${rows[0].value}”.`));
+ } catch (e) { next(e); }
+});
+
+// Vacuum: re-apply EVERY rule. Suppresses any product that matches a rule but slipped
+// in later (e.g. a fresh affiliate ingest). Reversible; only flips visible→hidden.
+router.post('/admin/suppress/vacuum', async (_req, res, next) => {
+ try {
+ const { rows } = await db.query(`SELECT kind, value FROM suppress_rules`);
+ let hidden = 0;
+ for (const r of rows) hidden += await applyRule(r.kind, r.value);
+ res.redirect('/admin/suppress?done=' + encodeURIComponent(`Vacuum complete — re-applied ${rows.length} rule${rows.length === 1 ? '' : 's'}, suppressed ${hidden} newly-matching product${hidden === 1 ? '' : 's'}.`));
+ } catch (e) { next(e); }
+});
+
+// Remove a rule. With ?unhide=1, also un-suppress the products it currently matches
+// (a rule is the audit record; removing it alone does NOT auto-show its products).
+router.post('/admin/suppress/:id/remove', async (req, res, next) => {
+ try {
+ const { rows } = await db.query(`SELECT kind, value FROM suppress_rules WHERE id=$1`, [req.params.id]);
+ if (!rows.length) return res.redirect('/admin/suppress');
+ const { kind, value } = rows[0];
+ let unhid = 0;
+ if (req.body.unhide === '1') {
+ if (kind === 'id') {
+ const r = await db.query(`UPDATE products SET suppressed=FALSE, updated_at=now() WHERE id=$1 AND suppressed`, [Number(value) || -1]);
+ unhid = r.rowCount;
+ } else {
+ const r = await db.query(`UPDATE products SET suppressed=FALSE, updated_at=now() WHERE suppressed AND ${KW_MATCH}`, ['%' + value + '%']);
+ unhid = r.rowCount;
+ }
+ }
+ await db.query(`DELETE FROM suppress_rules WHERE id=$1`, [req.params.id]);
+ const msg = req.body.unhide === '1' ? `Removed rule “${value}” and un-hid ${unhid} product${unhid === 1 ? '' : 's'}.` : `Removed rule “${value}”.`;
+ res.redirect('/admin/suppress?done=' + encodeURIComponent(msg));
+ } catch (e) { next(e); }
+});
+
// --- Affiliates: see every link source & switch it on/off -----------------
// An affiliate is either a whole NETWORK (cj/amazon/...) or a specific ADVERTISER
// (merchant) on that network. OFF here => that source's products vanish from the
diff --git a/server.js b/server.js
index 9480c5d..c555576 100644
--- a/server.js
+++ b/server.js
@@ -239,7 +239,12 @@ app.get('/guides/:slug', async (req, res, next) => {
// Affiliate go-through: log the click (analytics + Amazon audit trail), then 302 to the tracked link.
app.get('/go/:id', async (req, res, next) => {
try {
- const { rows } = await db.query(`SELECT id, network, advertiser, affiliate_url FROM products WHERE id=$1`, [req.params.id]);
+ // Validate the id BEFORE it reaches the bigint column. A non-numeric id like
+ // /go/abc would otherwise throw "invalid input syntax for type bigint" and 500
+ // the revenue path (bots + mangled links hit this). Non-int → 404, cleanly.
+ const id = Number(req.params.id);
+ if (!Number.isInteger(id) || id < 1) return next();
+ const { rows } = await db.query(`SELECT id, network, advertiser, affiliate_url FROM products WHERE id=$1`, [id]);
if (!rows.length) return next();
const p = rows[0];
db.query(`INSERT INTO clicks (product_id, network, advertiser, referer, ua) VALUES ($1,$2,$3,$4,$5)`,
← 82f2795 admin: per-product click analytics column (total, 7d, last-c
·
back to Interiordesignershowroom
·
store: date + time added on product cards — New badge + 'Add 78c986b →