reflyers: deploy deal-assets viewer (server.js) + auto-refresh usre snapshot 4x/day (item 2)

6e761c745c4bd45fc0ee717862bb703c94ee3f4a · 2026-08-20 09:35:55 -0700 · Steve Abrams

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

M scripts/dtt-cron.sh
A scripts/sync-deals-to-kamatera.sh

Diff

commit 6e761c745c4bd45fc0ee717862bb703c94ee3f4a
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Aug 20 09:35:55 2026 -0700

    reflyers: deploy deal-assets viewer (server.js) + auto-refresh usre snapshot 4x/day (item 2)
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 scripts/dtt-cron.sh               |  2 ++
 scripts/sync-deals-to-kamatera.sh | 24 ++++++++++++++++++++++++
 2 files changed, 26 insertions(+)

diff --git a/scripts/dtt-cron.sh b/scripts/dtt-cron.sh
index 3696cea..f340222 100755
--- a/scripts/dtt-cron.sh
+++ b/scripts/dtt-cron.sh
@@ -16,4 +16,6 @@ node scripts/build-flyers-site.mjs >> "$LOG" 2>&1
 bash scripts/email-digest.sh >> "$LOG" 2>&1
 # 6) Push the fresh viewer data to the LIVE Kamatera re-flyers-viewer (/search + /)
 bash scripts/sync-viewer-data-to-kamatera.sh >> "$LOG" 2>&1
+# 7) Refresh the deal-assets viewer's DB snapshot on Kamatera (re-flyers-deals / dealassets)
+bash scripts/sync-deals-to-kamatera.sh >> "$LOG" 2>&1
 echo "--- exit $? ---" >> "$LOG"
diff --git a/scripts/sync-deals-to-kamatera.sh b/scripts/sync-deals-to-kamatera.sh
new file mode 100755
index 0000000..2045416
--- /dev/null
+++ b/scripts/sync-deals-to-kamatera.sh
@@ -0,0 +1,24 @@
+#!/bin/bash
+# Refresh the deal-assets viewer's DB snapshot on Kamatera usre from Mac2's live data.
+# recent_commercial_deals is a VIEW over 3.5GB of base tables that DON'T exist on Kamatera,
+# so we materialize it (3.2k rows) here and atomically swap it in on prod. The two curated
+# asset tables (15 rows each) are reloaded too. Called by dtt-cron.sh (4x/day). $0 (local pg).
+set -euo pipefail
+export PATH="/opt/homebrew/bin:/opt/homebrew/opt/postgresql@14/bin:/usr/bin:/bin:/usr/sbin:/sbin"
+K=root@45.61.58.125
+# 1) materialize a fresh snapshot on Mac2 + dump it (+ the curated asset rows)
+psql usre -q -c "DROP TABLE IF EXISTS rcd_snap; CREATE TABLE rcd_snap AS TABLE recent_commercial_deals;"
+pg_dump usre --no-owner --no-privileges -t rcd_snap > /tmp/reflyers-rcd.sql
+pg_dump usre --no-owner --no-privileges --data-only -t external_marketing_asset -t asset_subject_link > /tmp/reflyers-assets.sql
+psql usre -q -c "DROP TABLE IF EXISTS rcd_snap;"   # clean up the local temp table
+# 2) ship + apply on Kamatera with an atomic swap (near-zero downtime)
+scp -q /tmp/reflyers-rcd.sql /tmp/reflyers-assets.sql "$K:/tmp/"
+ssh "$K" '
+  set -e
+  psql usre -q -c "DROP TABLE IF EXISTS rcd_snap;"
+  psql usre -q -f /tmp/reflyers-rcd.sql
+  psql usre -q -c "BEGIN; DROP TABLE IF EXISTS recent_commercial_deals; ALTER TABLE rcd_snap RENAME TO recent_commercial_deals; COMMIT;"
+  psql usre -q -c "TRUNCATE external_marketing_asset, asset_subject_link RESTART IDENTITY;"
+  psql usre -q -f /tmp/reflyers-assets.sql
+'
+echo "refreshed deals snapshot on Kamatera $(date '+%Y-%m-%d %H:%M:%S')"

← 3bbdac8 reflyers: auto-sync fresh viewer data to Kamatera prod after · back to Re Flyer Aggregator · auto-data-snapshot: 2026-08-20T09:38:44 (3 data files) — out 2b4363f →