[object Object]

← back to NationalPaperHangers

yolo tick 4: paper-thread contribution count + helpful votes — backlog #3 SHIPPED

aa09d6eafd4f35c3fae78720854b92be7a366a70 · 2026-05-10 18:57:03 -0700 · SteveStudio2

Final polish for UX backlog #3. Two pieces:

1. Helpful votes on individual comments (migration 019):
   - New table paper_comment_votes (comment_id, ip_hash, created_at)
     with PK (comment_id, ip_hash) — one vote per IP-hash per comment.
   - POST /papers/:slug/comments/:id/helpful — no auth (designer
     participation is the value). Rate-limited via geoLimiter
     (60/min/IP). IP hashed sha256-prefix-16 same as installer_interest.
   - ON CONFLICT DO NOTHING + RETURNING — only first vote bumps the
     denormalized helpful_count. Repeat votes silently no-op.
   - Comments sorted by helpful_count DESC then created_at ASC, so
     useful notes rise.
   - Smoke tested: 1st vote 302 + count 3→4, 2nd vote same IP-hash
     302 + count still 4, paper_comment_votes has exactly 1 row.

2. Installer-profile selection signal:
   - routes/public.js /installer/:slug now computes paperContributions
     {count, helpful, recent[3]} via two cheap queries on the indexed
     paper_comments table.
   - views/partials/paper-contributions.ejs renders nothing when
     count=0, otherwise: "N contributions · X found helpful" + list
     of 3 recent threads + link to /papers.
   - Included by all 7 templates: installer.ejs (in sidebar) + the 6
     installer-tpl-*.ejs variants (before coi-request include).
   - Designers see at a glance which studios contribute craft
     knowledge — selection signal no generic directory has.

UX_CREATIVE_BACKLOG.md: item #3 marked SHIPPED.

Entire 7-item UX backlog now ships:
  #1 In-the-seams portfolio  ✓
  #2 Brand-trained badges    ✓
  #3 Paper threads           ✓ (this tick)
  #4 Equipment fleet card    ✓
  #5 Live COI request        ✓
  #6 Structured booking      ✓
  #7 Acceptance-rate badge   ✓

Loop stops here per the tick-4 prompt condition.

Files touched

Diff

commit aa09d6eafd4f35c3fae78720854b92be7a366a70
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date:   Sun May 10 18:57:03 2026 -0700

    yolo tick 4: paper-thread contribution count + helpful votes — backlog #3 SHIPPED
    
    Final polish for UX backlog #3. Two pieces:
    
    1. Helpful votes on individual comments (migration 019):
       - New table paper_comment_votes (comment_id, ip_hash, created_at)
         with PK (comment_id, ip_hash) — one vote per IP-hash per comment.
       - POST /papers/:slug/comments/:id/helpful — no auth (designer
         participation is the value). Rate-limited via geoLimiter
         (60/min/IP). IP hashed sha256-prefix-16 same as installer_interest.
       - ON CONFLICT DO NOTHING + RETURNING — only first vote bumps the
         denormalized helpful_count. Repeat votes silently no-op.
       - Comments sorted by helpful_count DESC then created_at ASC, so
         useful notes rise.
       - Smoke tested: 1st vote 302 + count 3→4, 2nd vote same IP-hash
         302 + count still 4, paper_comment_votes has exactly 1 row.
    
    2. Installer-profile selection signal:
       - routes/public.js /installer/:slug now computes paperContributions
         {count, helpful, recent[3]} via two cheap queries on the indexed
         paper_comments table.
       - views/partials/paper-contributions.ejs renders nothing when
         count=0, otherwise: "N contributions · X found helpful" + list
         of 3 recent threads + link to /papers.
       - Included by all 7 templates: installer.ejs (in sidebar) + the 6
         installer-tpl-*.ejs variants (before coi-request include).
       - Designers see at a glance which studios contribute craft
         knowledge — selection signal no generic directory has.
    
    UX_CREATIVE_BACKLOG.md: item #3 marked SHIPPED.
    
    Entire 7-item UX backlog now ships:
      #1 In-the-seams portfolio  ✓
      #2 Brand-trained badges    ✓
      #3 Paper threads           ✓ (this tick)
      #4 Equipment fleet card    ✓
      #5 Live COI request        ✓
      #6 Structured booking      ✓
      #7 Acceptance-rate badge   ✓
    
    Loop stops here per the tick-4 prompt condition.
---
 UX_CREATIVE_BACKLOG.md                    |  2 +-
 db/migrations/019_paper_comment_votes.sql | 28 ++++++++++++
 routes/public.js                          | 73 ++++++++++++++++++++++++++++++-
 server.js                                 |  5 +++
 views/partials/paper-contributions.ejs    | 23 ++++++++++
 views/public/installer-tpl-bilingue.ejs   |  1 +
 views/public/installer-tpl-concierge.ejs  |  1 +
 views/public/installer-tpl-editorial.ejs  |  1 +
 views/public/installer-tpl-heritage.ejs   |  1 +
 views/public/installer-tpl-studio.ejs     |  1 +
 views/public/installer-tpl-trade-pro.ejs  |  1 +
 views/public/installer.ejs                |  1 +
 views/public/papers-detail.ejs            | 23 +++++++---
 13 files changed, 152 insertions(+), 9 deletions(-)

diff --git a/UX_CREATIVE_BACKLOG.md b/UX_CREATIVE_BACKLOG.md
index d9a0adc..4080cca 100644
--- a/UX_CREATIVE_BACKLOG.md
+++ b/UX_CREATIVE_BACKLOG.md
@@ -46,7 +46,7 @@ luxury-trade move.
 
 ---
 
-## 3. "This Paper" peer-installer commentary
+## 3. "This Paper" peer-installer commentary *(SHIPPED 2026-05-10 — schema + 5 seed threads + public read-only list/detail + verified-installer comment submission + ops moderation + installer-profile contribution count + helpful votes with IP-hash idempotency)*
 
 **Problem:** Wallcovering knowledge lives in the heads of 3,000 working
 installers globally. Most papers (de Gournay Earlham, Fromental Bois,
diff --git a/db/migrations/019_paper_comment_votes.sql b/db/migrations/019_paper_comment_votes.sql
new file mode 100644
index 0000000..3ef8ff2
--- /dev/null
+++ b/db/migrations/019_paper_comment_votes.sql
@@ -0,0 +1,28 @@
+-- 019 · Helpful-votes on paper-thread comments (UX backlog #3, tick 4)
+--
+-- Designers and other installers can mark a comment "helpful" — that signal
+-- drives the in-page sort (helpful_count DESC) AND the installer profile
+-- contribution count. To prevent ballot-stuffing without forcing log-in
+-- (we want designer participation, not just installer self-promotion),
+-- track votes by SHA-256(ip + SESSION_SECRET) — same hashing pattern as
+-- installer_interest.ip_hash.
+--
+-- Reversible: DROP TABLE paper_comment_votes;
+
+BEGIN;
+
+CREATE TABLE IF NOT EXISTS paper_comment_votes (
+  comment_id  INTEGER NOT NULL REFERENCES paper_comments(id) ON DELETE CASCADE,
+  ip_hash     TEXT NOT NULL,
+                -- 16-char prefix of sha256(ip + SESSION_SECRET)
+                -- low-cardinality but high enough that 1k spoofed votes
+                -- per comment costs measurable effort.
+  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
+  PRIMARY KEY (comment_id, ip_hash)
+                -- PK enforces one vote per IP per comment. Subsequent
+                -- inserts ON CONFLICT DO NOTHING — fully idempotent.
+);
+
+CREATE INDEX IF NOT EXISTS idx_paper_comment_votes_comment ON paper_comment_votes(comment_id, created_at DESC);
+
+COMMIT;
diff --git a/routes/public.js b/routes/public.js
index 1955e6e..cc8eb44 100644
--- a/routes/public.js
+++ b/routes/public.js
@@ -308,6 +308,49 @@ router.post('/papers/:slug/comments', express.urlencoded({ extended: false }), r
   } catch (err) { next(err); }
 });
 
+// POST /papers/:slug/comments/:id/helpful — designer-and-anyone vote.
+// No auth required (designer participation is the value). One vote per
+// IP-hash per comment via PK on paper_comment_votes. Idempotent.
+router.post('/papers/:slug/comments/:id/helpful', express.urlencoded({ extended: false }), async (req, res, next) => {
+  try {
+    const commentId = parseInt(req.params.id, 10);
+    if (!Number.isFinite(commentId)) return res.status(400).send('bad request');
+
+    // Verify the comment exists + belongs to this thread (defense in depth —
+    // a malformed URL with a mismatched slug shouldn't allow voting on
+    // unrelated comments).
+    const c = await db.one(
+      `SELECT c.id FROM paper_comments c
+         JOIN paper_threads t ON t.id = c.thread_id
+        WHERE c.id = $1 AND t.slug = $2 AND c.flagged = false`,
+      [commentId, req.params.slug]
+    );
+    if (!c) return res.status(404).render('public/404', { title: 'Not Found' });
+
+    const ipHash = require('crypto')
+      .createHash('sha256')
+      .update((req.headers['x-forwarded-for'] || req.ip || '') + (process.env.SESSION_SECRET || ''))
+      .digest('hex')
+      .slice(0, 16);
+
+    // Try to record the vote. ON CONFLICT DO NOTHING + RETURNING distinguishes
+    // first-vote from repeat-vote. Only first-vote bumps the denormalized
+    // counter — repeat-votes silently no-op.
+    const inserted = await db.many(
+      `INSERT INTO paper_comment_votes (comment_id, ip_hash)
+         VALUES ($1, $2)
+         ON CONFLICT (comment_id, ip_hash) DO NOTHING
+         RETURNING comment_id`,
+      [commentId, ipHash]
+    );
+    if (inserted.length > 0) {
+      await db.query(`UPDATE paper_comments SET helpful_count = helpful_count + 1 WHERE id = $1`, [commentId]);
+    }
+
+    res.redirect(`/papers/${req.params.slug}#comments`);
+  } catch (err) { next(err); }
+});
+
 router.get('/installer/:slug', async (req, res, next) => {
   try {
     const installer = await db.one(
@@ -364,6 +407,33 @@ router.get('/installer/:slug', async (req, res, next) => {
         };
       }
     }
+
+    // Paper-thread contribution signal (UX backlog #3, tick 4). Designers see
+    // which studios contribute craft knowledge → selection signal that no
+    // generic directory has. Limit to non-flagged comments + 3 recent threads
+    // for the inline list.
+    const contribSummary = await db.one(
+      `SELECT COUNT(*)::int AS n,
+              COALESCE(SUM(helpful_count), 0)::int AS helpful_total
+         FROM paper_comments
+        WHERE installer_id = $1 AND flagged = false`,
+      [installer.id]
+    );
+    const contribRecent = await db.many(
+      `SELECT DISTINCT ON (t.id) t.slug, t.brand, t.paper_name, c.created_at
+         FROM paper_comments c
+         JOIN paper_threads t ON t.id = c.thread_id
+        WHERE c.installer_id = $1 AND c.flagged = false
+        ORDER BY t.id, c.created_at DESC
+        LIMIT 3`,
+      [installer.id]
+    );
+    const paperContributions = {
+      count: contribSummary.n,
+      helpful: contribSummary.helpful_total,
+      recent: contribRecent
+    };
+
     const cityState = [installer.city, installer.state].filter(Boolean).join(', ');
     const matSummary = (installer.materials || []).slice(0, 3).map(m => m.replace(/_/g, ' ')).join(', ');
     // Pick the template the studio chose (defaults to 'editorial' via SQL default).
@@ -393,7 +463,8 @@ router.get('/installer/:slug', async (req, res, next) => {
       metaDescription: `${installer.business_name}${cityState ? ' is a verified wallcovering installer in ' + cityState : ' — wallcovering installer'}.${matSummary ? ' Specializing in ' + matSummary + '.' : ''} Book a consultation on National Paper Hangers.`,
       canonicalPath: `/installer/${installer.slug}`,
       ogImage: _ogImage,
-      installer, portfolio, reviews, acceptance, credentials, featuredVideos
+      installer, portfolio, reviews, acceptance, credentials, featuredVideos,
+      paperContributions
     });
   } catch (err) { next(err); }
 });
diff --git a/server.js b/server.js
index 4c4c2c3..1f54071 100644
--- a/server.js
+++ b/server.js
@@ -182,6 +182,11 @@ app.use('/api/installers.geo', geoLimiter);
 app.use('/installer/:slug/notify-when-live', claimLimiter);
 app.use('/installer/:slug/coi-request', claimLimiter);
 app.use('/papers/:slug/comments', claimLimiter);
+app.use('/papers/:slug/comments/:id/helpful', geoLimiter);
+                                             // 60 votes/min/IP — high enough
+                                             // to not block real browsing,
+                                             // low enough to make scripted
+                                             // ballot stuffing visible.
 
 app.use('/', publicRoutes);
 app.use('/', authRoutes);
diff --git a/views/partials/paper-contributions.ejs b/views/partials/paper-contributions.ejs
new file mode 100644
index 0000000..c8cd41b
--- /dev/null
+++ b/views/partials/paper-contributions.ejs
@@ -0,0 +1,23 @@
+<%# Shared "X contributions to paper threads" signal — UX backlog #3, tick 4.
+    Renders nothing for studios with 0 contributions. Drop into any
+    installer template at a sidebar/stats position.
+    Expects: paperContributions in scope. %>
+<% if (typeof paperContributions !== 'undefined' && paperContributions && paperContributions.count > 0) { %>
+  <h3 style="margin-top:24px">Paper threads</h3>
+  <p style="margin:0 0 6px;font-size:14px">
+    <strong><%= paperContributions.count %></strong> <%= paperContributions.count === 1 ? 'contribution' : 'contributions' %>
+    <% if (paperContributions.helpful > 0) { %>
+      <span class="muted"> · <%= paperContributions.helpful %> found helpful</span>
+    <% } %>
+  </p>
+  <% if (paperContributions.recent && paperContributions.recent.length) { %>
+    <ul style="list-style:none;padding:0;margin:6px 0 0;font-size:13px">
+      <% paperContributions.recent.forEach(function(t){ %>
+        <li style="padding:4px 0">
+          <a href="/papers/<%= t.slug %>" style="color:inherit"><%= t.brand %> · <%= t.paper_name %></a>
+        </li>
+      <% }); %>
+    </ul>
+  <% } %>
+  <p class="muted" style="font-size:12px;margin:8px 0 0"><a href="/papers" style="color:inherit">See all paper threads →</a></p>
+<% } %>
diff --git a/views/public/installer-tpl-bilingue.ejs b/views/public/installer-tpl-bilingue.ejs
index b6479f8..a83f760 100644
--- a/views/public/installer-tpl-bilingue.ejs
+++ b/views/public/installer-tpl-bilingue.ejs
@@ -111,5 +111,6 @@
     }
   })();
 </script>
+<%- include('../partials/paper-contributions') %>
 <%- include('../partials/coi-request') %>
 <%- include('../partials/footer') %>
diff --git a/views/public/installer-tpl-concierge.ejs b/views/public/installer-tpl-concierge.ejs
index 2684751..cf93798 100644
--- a/views/public/installer-tpl-concierge.ejs
+++ b/views/public/installer-tpl-concierge.ejs
@@ -63,5 +63,6 @@
   <%- include('../partials/social-videos') %>
 
 </article>
+<%- include('../partials/paper-contributions') %>
 <%- include('../partials/coi-request') %>
 <%- include('../partials/footer') %>
diff --git a/views/public/installer-tpl-editorial.ejs b/views/public/installer-tpl-editorial.ejs
index aca8c77..0907d67 100644
--- a/views/public/installer-tpl-editorial.ejs
+++ b/views/public/installer-tpl-editorial.ejs
@@ -85,5 +85,6 @@
     </section>
   <% } %>
 </article>
+<%- include('../partials/paper-contributions') %>
 <%- include('../partials/coi-request') %>
 <%- include('../partials/footer') %>
diff --git a/views/public/installer-tpl-heritage.ejs b/views/public/installer-tpl-heritage.ejs
index 2e7fccb..442c581 100644
--- a/views/public/installer-tpl-heritage.ejs
+++ b/views/public/installer-tpl-heritage.ejs
@@ -91,5 +91,6 @@
   <%- include('../partials/social-videos') %>
 
 </article>
+<%- include('../partials/paper-contributions') %>
 <%- include('../partials/coi-request') %>
 <%- include('../partials/footer') %>
diff --git a/views/public/installer-tpl-studio.ejs b/views/public/installer-tpl-studio.ejs
index 90acf5f..7f72ea3 100644
--- a/views/public/installer-tpl-studio.ejs
+++ b/views/public/installer-tpl-studio.ejs
@@ -93,5 +93,6 @@
   <%- include('../partials/social-videos') %>
 
 </article>
+<%- include('../partials/paper-contributions') %>
 <%- include('../partials/coi-request') %>
 <%- include('../partials/footer') %>
diff --git a/views/public/installer-tpl-trade-pro.ejs b/views/public/installer-tpl-trade-pro.ejs
index c991190..4410f34 100644
--- a/views/public/installer-tpl-trade-pro.ejs
+++ b/views/public/installer-tpl-trade-pro.ejs
@@ -95,5 +95,6 @@
   <%- include('../partials/social-videos') %>
 
 </article>
+<%- include('../partials/paper-contributions') %>
 <%- include('../partials/coi-request') %>
 <%- include('../partials/footer') %>
diff --git a/views/public/installer.ejs b/views/public/installer.ejs
index 4673e4c..e1b9680 100644
--- a/views/public/installer.ejs
+++ b/views/public/installer.ejs
@@ -171,6 +171,7 @@
         <p class="muted" style="font-size:12px;margin:6px 0 0">A studio booked solid enough to be selective.</p>
       <% } %>
 
+      <%- include('../partials/paper-contributions') %>
     </aside>
   </div>
 
diff --git a/views/public/papers-detail.ejs b/views/public/papers-detail.ejs
index 369ec2a..c270b9f 100644
--- a/views/public/papers-detail.ejs
+++ b/views/public/papers-detail.ejs
@@ -42,20 +42,29 @@
       <p class="muted" style="margin:0;font-size:13px">Working installers — <a href="/login">log in</a> to contribute.</p>
     </div>
   <% } else { %>
-    <ul style="list-style:none;padding:0;margin:0;display:grid;gap:18px">
+    <ul id="comments" style="list-style:none;padding:0;margin:0;display:grid;gap:18px">
       <% comments.forEach(function(c){ %>
-        <li style="padding:18px 20px;border:1px solid var(--border,#ddd);border-radius:8px">
+        <li id="comment-<%= c.id %>" style="padding:18px 20px;border:1px solid var(--border,#ddd);border-radius:8px">
           <p style="margin:0 0 12px;font-size:15px;line-height:1.6;white-space:pre-wrap"><%= c.body %></p>
-          <footer style="display:flex;justify-content:space-between;align-items:center;gap:12px;font-size:13px">
+          <footer style="display:flex;justify-content:space-between;align-items:center;gap:12px;font-size:13px;flex-wrap:wrap">
             <a href="/installer/<%= c.installer_slug %>" style="color:inherit;text-decoration:none">
               <strong><%= c.installer_business_name %></strong>
               <% if (c.installer_verified) { %><span style="color:#888"> · verified</span><% } %>
               <% if (c.installer_city) { %><span class="muted"> · <%= c.installer_city %><% if (c.installer_state) { %>, <%= c.installer_state %><% } %></span><% } %>
             </a>
-            <span class="muted" style="font-size:12px">
-              <%= new Date(c.created_at).toLocaleDateString('en-US', {year:'numeric', month:'short', day:'numeric'}) %>
-              <% if (c.helpful_count > 0) { %> · <%= c.helpful_count %> found helpful<% } %>
-            </span>
+            <div style="display:flex;gap:10px;align-items:center">
+              <span class="muted" style="font-size:12px">
+                <%= new Date(c.created_at).toLocaleDateString('en-US', {year:'numeric', month:'short', day:'numeric'}) %>
+              </span>
+              <form method="post" action="/papers/<%= thread.slug %>/comments/<%= c.id %>/helpful#comment-<%= c.id %>" style="margin:0;display:inline">
+                <input type="hidden" name="_csrf" value="<%= csrfToken %>">
+                <button type="submit"
+                        style="background:transparent;border:1px solid var(--border,#ddd);border-radius:14px;padding:3px 10px;cursor:pointer;font-size:12px;color:inherit"
+                        title="Mark this note helpful — designers, installers, anyone">
+                  ↑ helpful<% if (c.helpful_count > 0) { %> · <%= c.helpful_count %><% } %>
+                </button>
+              </form>
+            </div>
           </footer>
         </li>
       <% }); %>

← 4a79e6f yolo tick 3: paper-thread comment submission + ops moderatio  ·  back to NationalPaperHangers  ·  yolo tick 5: expand /papers seed library 5→17 + helpful-vote 5918434 →