← back to Interiordesignershowroom
refine: shared fmtStamp (storefront+admin, NaN/future-safe), sortable products table (Newest|Most clicks), post-migration schema assertion — v0.4.1
81d733e2a91cfa34248841bed9b2faa4f21d41de · 2026-08-03 11:08:43 -0700 · steve
Files touched
M lib/render.jsM package-lock.jsonM package.jsonM routes/admin.jsM scripts/migrate.sh
Diff
commit 81d733e2a91cfa34248841bed9b2faa4f21d41de
Author: steve <steve@designerwallcoverings.com>
Date: Mon Aug 3 11:08:43 2026 -0700
refine: shared fmtStamp (storefront+admin, NaN/future-safe), sortable products table (Newest|Most clicks), post-migration schema assertion — v0.4.1
---
lib/render.js | 39 ++++++++++++++++++++++++++++-----------
package-lock.json | 4 ++--
package.json | 2 +-
routes/admin.js | 33 ++++++++++++++++++++++++++-------
scripts/migrate.sh | 19 +++++++++++++++++++
5 files changed, 76 insertions(+), 21 deletions(-)
diff --git a/lib/render.js b/lib/render.js
index d9404b0..ed86576 100644
--- a/lib/render.js
+++ b/lib/render.js
@@ -32,20 +32,37 @@ function freshness(ts) {
+ `Price checked ${label}</span>`;
}
+// Single source of truth for how a created_at timestamp renders anywhere on the
+// site. Both the storefront "Added" line (below) and the admin when() chip
+// (routes/admin.js) format through this, so the shopper view and the curation
+// view can never drift — they had: this used a hardcoded 'en-US' locale while
+// admin used the viewer's locale, despite a comment claiming they matched.
+// Returns null for a missing/garbage timestamp so callers render nothing rather
+// than the literal "Invalid Date"; `days` is clamped at 0 so a clock-skewed or
+// bad feed timestamp in the future never reads as a negative age.
+function fmtStamp(ts) {
+ if (!ts) return null;
+ const d = new Date(ts);
+ if (Number.isNaN(d.getTime())) return null;
+ return {
+ iso: d.toISOString(),
+ label: d.toLocaleString(undefined, { year: 'numeric', month: 'short', day: 'numeric', hour: 'numeric', minute: '2-digit' }),
+ days: Math.max(0, Math.floor((Date.now() - d.getTime()) / 86400e3)),
+ };
+}
+
// "Added to the store" signals for a storefront card, derived from created_at.
// Returns two pieces because they live in different parts of the card: a NEW
-// badge over the image for recently-added items (< NEW_WINDOW_DAYS), and a dated
-// "Added …" line (date + time, full ISO in title=) on every card. Mirrors the
-// admin when() format so the storefront and the curation view read identically.
+// badge over the image for recently-added items (≤ NEW_WINDOW_DAYS), and a dated
+// "Added …" line (date + time, full ISO in title=) on every card. Formats through
+// fmtStamp() so it reads identically to the admin when() chip.
const NEW_WINDOW_DAYS = 14;
function addedStamp(ts) {
- if (!ts) return { badge: '', line: '' };
- const d = new Date(ts);
- const iso = esc(d.toISOString());
- const days = Math.floor((Date.now() - d.getTime()) / 86400e3);
- const label = esc(d.toLocaleString('en-US', { year: 'numeric', month: 'short', day: 'numeric', hour: 'numeric', minute: '2-digit' }));
- const badge = days <= NEW_WINDOW_DAYS ? `<span class="card-new" title="Added ${iso}">New</span>` : '';
- const line = `<div class="card-added" title="Added ${iso}">🕓 Added ${label}</div>`;
+ const f = fmtStamp(ts);
+ if (!f) return { badge: '', line: '' };
+ const iso = esc(f.iso);
+ const badge = f.days <= NEW_WINDOW_DAYS ? `<span class="card-new" title="Added ${iso}">New</span>` : '';
+ const line = `<div class="card-added" title="Added ${iso}">🕓 Added ${esc(f.label)}</div>`;
return { badge, line };
}
@@ -287,4 +304,4 @@ ${disclosureBar()}
</html>`;
}
-module.exports = { SITE, esc, money, layout, productCard, disclosureBar, DISCLOSURE_SHORT, orgWebsiteJsonld, productListJsonld, breadcrumbNav, breadcrumbJsonld, absUrl, sceneFigure, roomThumbs };
+module.exports = { SITE, esc, money, fmtStamp, layout, productCard, disclosureBar, DISCLOSURE_SHORT, orgWebsiteJsonld, productListJsonld, breadcrumbNav, breadcrumbJsonld, absUrl, sceneFigure, roomThumbs };
diff --git a/package-lock.json b/package-lock.json
index 3274d88..5310f54 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -1,12 +1,12 @@
{
"name": "interiordesignershowroom",
- "version": "0.4.0",
+ "version": "0.4.1",
"lockfileVersion": 3,
"requires": true,
"packages": {
"": {
"name": "interiordesignershowroom",
- "version": "0.4.0",
+ "version": "0.4.1",
"dependencies": {
"dotenv": "^17.4.2",
"express": "^4.19.2",
diff --git a/package.json b/package.json
index 3b6ac2d..5a364d9 100644
--- a/package.json
+++ b/package.json
@@ -1,6 +1,6 @@
{
"name": "interiordesignershowroom",
- "version": "0.4.0",
+ "version": "0.4.1",
"private": true,
"description": "Curated, editorial affiliate showroom for interior design — multi-network (CJ, Amazon, Rakuten, ShareASale) feed-driven catalog + buying guides.",
"main": "server.js",
diff --git a/routes/admin.js b/routes/admin.js
index bf2f403..960580c 100644
--- a/routes/admin.js
+++ b/routes/admin.js
@@ -6,7 +6,7 @@ const crypto = require('crypto');
const fs = require('fs');
const path = require('path');
const db = require('../lib/db');
-const { esc, money } = require('../lib/render');
+const { esc, money, fmtStamp } = require('../lib/render');
const { detailPref } = require('../lib/adminview');
const router = express.Router();
@@ -121,11 +121,13 @@ router.use('/admin', (req, res, next) => {
});
// Steve's rule: admin cards show created date AND time, visible, ISO in title=.
+// Formats through the shared render.fmtStamp() so the admin chip and the
+// storefront "Added" line stay byte-identical (and it's NaN-safe — a garbage
+// timestamp renders nothing instead of "Invalid Date").
function when(ts) {
- if (!ts) return '';
- const d = new Date(ts);
- const label = d.toLocaleString(undefined, { year: 'numeric', month: 'short', day: 'numeric', hour: 'numeric', minute: '2-digit' });
- return `<span class="when" title="${esc(d.toISOString())}">🕓 ${esc(label)}</span>`;
+ const f = fmtStamp(ts);
+ if (!f) return '';
+ return `<span class="when" title="${esc(f.iso)}">🕓 ${esc(f.label)}</span>`;
}
// Per-product click analytics cell: lifetime total, a 7-day sub-count, and the
@@ -185,6 +187,14 @@ a.ezjoin:hover{background:#6f5c40}
router.get('/admin', async (req, res, next) => {
try {
const details = detailPref(req, res); // "see more on the backend" toggle
+ // Sortable products table (Steve's standing "every data table is sortable" rule).
+ // ?sort=clicks surfaces what's actually converting; default 'new' keeps the
+ // curation/recency order. Whitelisted to two fixed ORDER BY clauses, so the
+ // query param can never reach the SQL as free text.
+ const sort = req.query.sort === 'clicks' ? 'clicks' : 'new';
+ const orderSql = sort === 'clicks'
+ ? `p.suppressed, COALESCE(cl.clicks,0) DESC, cl.last_click DESC NULLS LAST, p.created_at DESC`
+ : `p.featured DESC, p.suppressed, p.created_at DESC`;
const [counts, topClicks, products, guides] = await Promise.all([
db.query(`SELECT
(SELECT count(*) FROM products) AS products,
@@ -215,7 +225,7 @@ router.get('/admin', async (req, res, next) => {
max(clicked_at) AS last_click
FROM clicks GROUP BY product_id
) cl ON cl.product_id = p.id
- ORDER BY p.featured DESC, p.suppressed, p.created_at DESC LIMIT 200`),
+ ORDER BY ${orderSql} LIMIT 200`),
db.query(`SELECT id,slug,title,published,created_at FROM guides ORDER BY created_at DESC`),
]);
const c = counts.rows[0];
@@ -258,13 +268,22 @@ router.get('/admin', async (req, res, next) => {
<td><form method="post" action="/admin/guides/${g.id}/published"><button>${g.published ? 'Unpublish' : 'Publish'}</button></form></td>
</tr>`).join('');
+ // Sort links for the products table — the active one is inked/bold, and each
+ // preserves the current details toggle so the two controls never fight.
+ const sortLink = (key, label) => `<a href="/admin?sort=${key}&details=${details ? 1 : 0}" style="text-decoration:none;${sort === key ? 'font-weight:600;color:var(--ink)' : 'color:#8a7250'}">${label}</a>`;
+
res.send(shell(`
${stats}
<p style="margin:16px 0 0"><a href="/admin/affiliates" style="display:inline-block;background:#221e19;color:#e6c98a;padding:8px 14px;border-radius:6px;text-decoration:none;font-size:.85rem">⚙︎ Manage affiliates — turn sources on / off →</a></p>
<h2>Top-clicked products</h2>
<table><tr><th>Product</th><th>Advertiser</th><th>Clicks</th></tr>${topRows}</table>
<h2>Products — curate featured (${products.rows.length})
- <a href="/admin?details=${details ? 0 : 1}" style="float:right;font-size:.75rem;font-weight:400;text-decoration:none;color:${details ? '#b1483c' : '#8a7250'}">${details ? '− Hide details' : '+ Show details'}</a>
+ <span style="float:right;font-size:.75rem;font-weight:400">
+ <span class="subtle">Sort:</span>
+ ${sortLink('new', 'Newest')} <span class="subtle">·</span> ${sortLink('clicks', 'Most clicks')}
+ <span class="subtle" style="margin:0 8px">|</span>
+ <a href="/admin?sort=${sort}&details=${details ? 0 : 1}" style="text-decoration:none;color:${details ? '#b1483c' : '#8a7250'}">${details ? '− Hide details' : '+ Show details'}</a>
+ </span>
</h2>
<table><tr><th></th><th>Product</th><th>Room</th><th>Price</th><th>Added</th><th>Clicks</th><th>Status</th>${detailHead}<th></th></tr>${prodRows}</table>
<h2>Guides</h2>
diff --git a/scripts/migrate.sh b/scripts/migrate.sh
index 4f43516..1607aa1 100755
--- a/scripts/migrate.sh
+++ b/scripts/migrate.sh
@@ -13,6 +13,13 @@
set -euo pipefail
cd "$(dirname "$0")/.."
+# Fail with a clear, actionable message rather than a bare "command not found" if
+# the deploy host has no psql (postgresql-client not installed).
+command -v psql >/dev/null 2>&1 || {
+ echo "✗ psql not found on PATH — install postgresql-client on the deploy host" >&2
+ exit 1
+}
+
# The deploy excludes .env from rsync, so the remote keeps its own. Load it to
# get DATABASE_URL (never printed).
set -a; [ -f .env ] && . ./.env; set +a
@@ -21,3 +28,15 @@ set -a; [ -f .env ] && . ./.env; set +a
echo "── db migrate: applying db/schema.sql ──"
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f db/schema.sql
echo "✓ schema applied"
+
+# Post-migration assertion: don't just trust that schema.sql ran — prove the exact
+# three things whose absence 500'd /shop on 2026-08-03 are now queryable. LIMIT 0
+# touches the schema, not the rows, so it's instant; ON_ERROR_STOP turns any
+# missing column/table into a non-zero exit that aborts the deploy BEFORE pm2
+# reloads onto a still-broken DB. This is what makes "green" mean "actually works".
+echo "── verify: required schema present ──"
+psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -q \
+ -c 'SELECT suppressed FROM products LIMIT 0;' \
+ -c 'SELECT 1 FROM affiliate_settings LIMIT 0;' \
+ -c 'SELECT 1 FROM suppress_rules LIMIT 0;' >/dev/null
+echo "✓ schema verified — products.suppressed + affiliate_settings + suppress_rules all present"
← 3f3869c a11y: working skip-to-content link (focus-moving, tabindex=-
·
back to Interiordesignershowroom
·
snapshot before deploy: in-tree security headers (nosniff/fr c34917c →