v0.7 #31: auto-email worker for installer_interest queue

f21468c32f7e46b043c7def43fb7acf2de8c29fa · 2026-05-06 15:46:51 -0700 · SteveStudio2

scripts/notify-interest-queue.js — scans installer_interest WHERE notified_at
IS NULL and the installer is now in 'ready' state (claim_status in self/claimed
AND tier in pro/signature/enterprise AND ≥1 active installer_availability),
sends a one-shot 'they're live' email via Purelymail SMTP, marks notified_at
+ notify_msg_id on the queue row, audits to comms_send_audit.

Compliance:
- Pre-flight assertSendCompliance() — refuses to enter the loop if MAILING_ADDRESS
  / SESSION_SECRET / PUBLIC_URL / suppression tables are misconfigured.
- Per-recipient isSuppressed() scrub. Suppressed rows still get notified_at
  marked (so they don't sit in the queue forever) plus a 'skipped' audit row.
- 1-click unsubscribe token + List-Unsubscribe headers (RFC 2369/8058).
- complianceFooter with MAILING_ADDRESS in every email.

Cron: */15 * * * * on Kamatera root crontab → /var/log/nph-notify-interest.log.

Live test on prod 2026-05-06:
- Seed row → run → sent (msgId @nationalpaperhangers.com) → audit row 'sent' → queue.notified_at populated.
- Second run → 0 candidates (idempotent).

Flags: --dry-run (no DB write, no SMTP, just log), --limit N (cap iteration).

Files touched

A scripts/notify-interest-queue.js

Diff

commit f21468c32f7e46b043c7def43fb7acf2de8c29fa
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date:   Wed May 6 15:46:51 2026 -0700

    v0.7 #31: auto-email worker for installer_interest queue
    
    scripts/notify-interest-queue.js — scans installer_interest WHERE notified_at
    IS NULL and the installer is now in 'ready' state (claim_status in self/claimed
    AND tier in pro/signature/enterprise AND ≥1 active installer_availability),
    sends a one-shot 'they're live' email via Purelymail SMTP, marks notified_at
    + notify_msg_id on the queue row, audits to comms_send_audit.
    
    Compliance:
    - Pre-flight assertSendCompliance() — refuses to enter the loop if MAILING_ADDRESS
      / SESSION_SECRET / PUBLIC_URL / suppression tables are misconfigured.
    - Per-recipient isSuppressed() scrub. Suppressed rows still get notified_at
      marked (so they don't sit in the queue forever) plus a 'skipped' audit row.
    - 1-click unsubscribe token + List-Unsubscribe headers (RFC 2369/8058).
    - complianceFooter with MAILING_ADDRESS in every email.
    
    Cron: */15 * * * * on Kamatera root crontab → /var/log/nph-notify-interest.log.
    
    Live test on prod 2026-05-06:
    - Seed row → run → sent (msgId @nationalpaperhangers.com) → audit row 'sent' → queue.notified_at populated.
    - Second run → 0 candidates (idempotent).
    
    Flags: --dry-run (no DB write, no SMTP, just log), --limit N (cap iteration).
---
 scripts/notify-interest-queue.js | 172 +++++++++++++++++++++++++++++++++++++++
 1 file changed, 172 insertions(+)

diff --git a/scripts/notify-interest-queue.js b/scripts/notify-interest-queue.js
new file mode 100644
index 0000000..d3348d5
--- /dev/null
+++ b/scripts/notify-interest-queue.js
@@ -0,0 +1,172 @@
+#!/usr/bin/env node
+// One-shot worker: scan installer_interest for un-notified rows where the
+// installer is now ready to take bookings (claimed + paid + has availability),
+// send the "they're live" notification, mark notified_at + notify_msg_id.
+//
+// Designed to run on a 15-minute launchd timer. Each row is independently
+// audited via lib/compliance, so a partial failure (one bad email, one
+// suppression hit) doesn't block the rest of the queue.
+//
+// Usage:
+//   node scripts/notify-interest-queue.js              # production run
+//   node scripts/notify-interest-queue.js --dry-run    # log what would send, no DB write, no email
+//   node scripts/notify-interest-queue.js --limit 10   # cap iteration count
+
+const path = require('node:path');
+require('dotenv').config({ path: path.resolve(__dirname, '..', '.env') });
+
+const db = require('../lib/db');
+const { sendEmail } = require('../lib/email');
+const compliance = require('../lib/compliance');
+
+const DRY_RUN = process.argv.includes('--dry-run');
+const LIMIT = (() => {
+  const i = process.argv.indexOf('--limit');
+  return i >= 0 ? parseInt(process.argv[i + 1], 10) || 100 : 100;
+})();
+
+const PUBLIC_URL = (process.env.PUBLIC_URL || 'https://nationalpaperhangers.com').replace(/\/+$/, '');
+const CAMPAIGN = 'installer_now_live';
+
+function buildEmail({ installer, unsubscribeUrl }) {
+  const subject = `${installer.business_name} just opened bookings on National Paper Hangers`;
+  const bookUrl = `${PUBLIC_URL}/installer/${encodeURIComponent(installer.slug)}/book`;
+  const profileUrl = `${PUBLIC_URL}/installer/${encodeURIComponent(installer.slug)}`;
+  const cityState = [installer.city, installer.state].filter(Boolean).join(', ');
+
+  const html = `<div style="font-family:Georgia,serif;max-width:560px;margin:0 auto;padding:32px 24px;color:#0e0e0e">
+  <p style="font-size:13px;letter-spacing:0.08em;text-transform:uppercase;color:#b8860b;margin:0 0 8px">Studio now live</p>
+  <h1 style="font-size:26px;font-weight:400;margin:0 0 16px;line-height:1.2">${installer.business_name} is on National Paper Hangers</h1>
+  <p style="font-size:15px;line-height:1.6;margin:0 0 20px">You asked us to let you know when ${installer.business_name}${cityState ? ' in ' + cityState : ''} opened up for direct booking. They've claimed their listing and configured their calendar — you can book a visit now.</p>
+  <p style="margin:24px 0">
+    <a href="${bookUrl}" style="display:inline-block;background:#0e0e0e;color:#fff;padding:14px 24px;text-decoration:none;font-size:14px;letter-spacing:0.04em;border-radius:2px">Book a visit →</a>
+  </p>
+  <p style="font-size:13px;line-height:1.6;color:#666;margin:0 0 8px">Or browse their work first at <a href="${profileUrl}" style="color:#666">${profileUrl.replace(/^https?:\/\//, '')}</a>.</p>
+${compliance.complianceFooter({ campaign: CAMPAIGN, unsubscribeUrl })}
+</div>`;
+
+  const text = `${installer.business_name} just opened bookings on National Paper Hangers.\n\nYou asked us to notify you. Book a visit: ${bookUrl}\n\nProfile: ${profileUrl}\n\n---\nUnsubscribe: ${unsubscribeUrl}`;
+  return { subject, html, text };
+}
+
+(async () => {
+  // Pre-flight compliance gate. Throws if MAILING_ADDRESS / SESSION_SECRET /
+  // PUBLIC_URL / suppression tables aren't ready. Don't enter the loop on a
+  // misconfigured host.
+  if (!DRY_RUN) {
+    await compliance.assertSendCompliance({ campaign: CAMPAIGN });
+  }
+
+  // The "ready" filter mirrors the calendarEnabled gate in routes/public.js
+  // (claim_status in self/claimed AND tier in pro/signature/enterprise AND
+  //  ≥1 active installer_availability window). Aggregating availability count
+  // keeps it to a single query.
+  const queue = await db.query(`
+    SELECT ii.id           AS interest_id,
+           ii.email,
+           ii.installer_id,
+           i.slug,
+           i.business_name,
+           i.city, i.state
+      FROM installer_interest ii
+      JOIN installers i ON i.id = ii.installer_id
+     WHERE ii.notified_at IS NULL
+       AND i.claim_status IN ('self','claimed')
+       AND i.tier IN ('pro','signature','enterprise')
+       AND EXISTS (
+         SELECT 1 FROM installer_availability a
+          WHERE a.installer_id = i.id AND a.active = true
+       )
+     ORDER BY ii.created_at ASC
+     LIMIT $1
+  `, [LIMIT]);
+
+  console.log(`[notify-queue] ${queue.rowCount} candidate(s)${DRY_RUN ? ' (dry run)' : ''}`);
+  if (queue.rowCount === 0) return;
+
+  let sent = 0, suppressed = 0, errors = 0;
+
+  for (const row of queue.rows) {
+    const recipient = String(row.email).trim().toLowerCase();
+
+    // 1. Suppression check — recipient may have unsubscribed since capture.
+    const supressed = await compliance.isSuppressed({ channel: 'email', identifier: recipient });
+    if (supressed) {
+      suppressed++;
+      console.log(`  · ${recipient} → SUPPRESSED (skipping, marking notified to clear queue)`);
+      if (!DRY_RUN) {
+        await db.query(
+          `UPDATE installer_interest SET notified_at = now(), notify_msg_id = 'suppressed' WHERE id = $1`,
+          [row.interest_id]
+        );
+        await compliance.recordAudit({
+          channel: 'email', campaign: CAMPAIGN, recipient,
+          installerId: row.installer_id,
+          decision: 'skipped', reason: 'suppression',
+          subject: null, messageId: null, payload: { interest_id: row.interest_id }
+        });
+      }
+      continue;
+    }
+
+    // 2. Mint a 1-click unsubscribe token + build the email
+    const unsubToken = DRY_RUN
+      ? 'dry-run-token'
+      : await compliance.mintUnsubscribeToken({
+          channel: 'email', identifier: recipient,
+          campaign: CAMPAIGN, installerId: row.installer_id
+        });
+    const unsubscribeUrl = `${PUBLIC_URL}/unsubscribe?t=${unsubToken}`;
+    const { subject, html, text } = buildEmail({ installer: row, unsubscribeUrl });
+
+    if (DRY_RUN) {
+      console.log(`  · ${recipient} → would send "${subject}" (slug=${row.slug})`);
+      sent++;
+      continue;
+    }
+
+    // 3. Send
+    const result = await sendEmail({
+      to: recipient,
+      subject, html, text,
+      extraHeaders: compliance.listUnsubscribeHeader(unsubscribeUrl)
+    });
+
+    // 4. Record outcome — both audit row and the queue row.
+    if (result && result.ok) {
+      sent++;
+      await db.query(
+        `UPDATE installer_interest SET notified_at = now(), notify_msg_id = $2 WHERE id = $1`,
+        [row.interest_id, result.messageId || 'sent-no-id']
+      );
+      await compliance.recordAudit({
+        channel: 'email', campaign: CAMPAIGN, recipient,
+        installerId: row.installer_id,
+        decision: 'sent', reason: null,
+        subject, messageId: result.messageId || null,
+        payload: { interest_id: row.interest_id }
+      });
+      console.log(`  · ${recipient} → sent (msg ${(result.messageId || '').slice(0, 28)}…)`);
+    } else {
+      errors++;
+      await compliance.recordAudit({
+        channel: 'email', campaign: CAMPAIGN, recipient,
+        installerId: row.installer_id,
+        decision: 'errored', reason: (result && result.error) || 'unknown',
+        subject, messageId: null,
+        payload: { interest_id: row.interest_id, smtp_code: result && result.code }
+      });
+      console.error(`  · ${recipient} → ERROR ${(result && result.code) || ''} ${(result && result.error || '').slice(0, 80)}`);
+      // Don't update notified_at — let the next run retry. Stripe-style backoff
+      // is overkill here; we run on a 15-min cadence so worst-case retry latency is bounded.
+    }
+  }
+
+  console.log(`[notify-queue] done — sent=${sent} suppressed=${suppressed} errors=${errors}`);
+})().catch(err => {
+  console.error('[notify-queue] FATAL:', err.message);
+  process.exit(1);
+}).finally(() => {
+  // pg pool cleanup so the process exits cleanly under launchd.
+  setTimeout(() => process.exit(0), 200);
+});

← f339c34 v0.6: notify-when-live capture form on unclaimed /book · back to NationalPaperHangers · Bump platform fee 10%→25% + reframe as 'lead-coordination fe bec9ac1 →