`RUNBOOK-kamatera-deploy.md`

68 lines
# Kamatera deploy runbook — re-flyers-viewer (TK-10708)

Target: 45.61.58.125 (root). Nothing here runs without Steve's explicit OK per phase.
The viewer shells out to `psql usre`, so the WHOLE deploy is moot unless Kamatera has a
`usre` DB with `recent_commercial_deals` populated — **Phase 0 verifies that first.**

## Phase 0 — READ-ONLY recon (changes NOTHING; run this first)
```sh
ssh root@45.61.58.125 '
  hostname
  psql -l | grep -i usre || echo "NO usre DB on Kamatera"
  psql usre -tAc "SELECT count(*) FROM recent_commercial_deals" 2>&1 | head -1
  psql usre -tAc "SELECT to_regclass('"'"'public.deal_assets_v'"'"')" 2>&1
  # free port + existing service names
  for p in 9860 9861 9862 9863 9864; do ss -ltn | grep -q ":$p " && echo "$p BUSY" || { echo "$p FREE"; break; }; done
  ls /etc/nginx/sites-enabled/ 2>/dev/null | head
  pm2 ls | grep -i re-flyers || echo "no existing re-flyers pm2"
'
```
DECISION GATE: if "NO usre DB" or `recent_commercial_deals` count is 0 → STOP and rethink
(the viewer would be empty on Kamatera; we'd have to sync the deals data too — a bigger job).

## Phase 1 — promote schema + 15 rows into Kamatera usre (WRITE, reversible)
```sh
# ship schema + a data-only dump of the two tables
scp db/001_external_marketing_asset.sql db/002_deal_assets_view.sql root@45.61.58.125:/tmp/
pg_dump usre --data-only -t external_marketing_asset -t asset_subject_link \
  | ssh root@45.61.58.125 'cat > /tmp/reflyers-data.sql'
ssh root@45.61.58.125 '
  psql usre -f /tmp/001_external_marketing_asset.sql
  psql usre -f /tmp/002_deal_assets_view.sql
  psql usre -f /tmp/reflyers-data.sql
  psql usre -tAc "SELECT count(*) FROM deal_assets_v"   # expect 14
'
```
Rollback: `ssh root@45.61.58.125 'psql usre -c "DROP VIEW deal_assets_v; DROP TABLE asset_subject_link; DROP TABLE external_marketing_asset;"'`

## Phase 2 — deploy the app (WRITE, reversible)
```sh
rsync -az --delete --exclude node_modules --exclude .git --exclude .env --exclude out \
  ~/Projects/re-flyer-aggregator/ root@45.61.58.125:/root/public-projects/re-flyer-aggregator/
ssh root@45.61.58.125 '
  cd /root/public-projects/re-flyer-aggregator
  PORT=<FREE_PORT_FROM_PHASE0> pm2 start server.js --name re-flyers-viewer --update-env
  pm2 save
  sleep 1; curl -s -o /dev/null -w "%{http_code}\n" -u admin:DW2024! http://127.0.0.1:<FREE_PORT>/health
'
```
NOTE / UNKNOWN: the server runs `psql usre` assuming local trust auth on Kamatera. If Kamatera
PG needs a user/password, add PG env to server.js call OR a .env — Phase 0 output tells us.
Rollback: `ssh root@45.61.58.125 'pm2 delete re-flyers-viewer && pm2 save'`

## Phase 3 — public exposure (WRITE) — OPTIONAL, DNS-gated
- DW zone already has wildcard `*.designerwallcoverings.com -> 45.61.58.125` (per memory), so a
  single-label subdomain like `dealassets.designerwallcoverings.com` needs **NO new DNS record** —
  only an nginx vhost proxying to 127.0.0.1:<port>. SSL via CF proxy or certbot.
- Access is already gated by basic-auth (admin/DW2024!). If you'd rather keep it internal-only,
  SKIP Phase 3 entirely and reach it via the server IP:port over SSH tunnel.
- nginx vhost = a customer-facing-adjacent change → I will draft the exact vhost + show it before applying.

## Summary of what each phase touches
| Phase | Writes | Reversible |
|---|---|---|
| 0 | nothing (read-only) | n/a |
| 1 | 2 tables + 1 view + 15 rows in Kamatera usre | DROP (one line) |
| 2 | rsync dir + 1 pm2 process | pm2 delete + rm dir |
| 3 | nginx vhost (+ maybe cert) | rm vhost + reload |