[object Object]

← back to Interiordesignershowroom

deploy: schema-migration step + /shop smoke test (stop code-ahead-of-schema)

181743c97d1e942eceb41e274c25e9f697c45339 · 2026-08-03 10:44:12 -0700 · Steve

Prevents a repeat of the 2026-08-03 regression where shipped code referenced a
prod-missing column (products.suppressed) + tables (affiliate_settings,
suppress_rules) and /shop 500'd while the DB-blind /healthz smoke test stayed
green.

- db/schema.sql: explicit ADD COLUMN IF NOT EXISTS guards (inline, before
  dependent indexes) so the file is a complete idempotent migration on a fresh
  OR drifted DB, not just a fresh-create.
- scripts/migrate.sh: applies schema.sql with ON_ERROR_STOP; loads DATABASE_URL
  from the remote's own .env.
- .deploy.conf: BUILD_CMD runs the migration on the remote after install/before
  reload; HEALTH_URL now hits /shop (exercises the real catalog query, not the
  DB-blind /healthz); INSTALL_CMD bakes in the sharp-safe (scripts-enabled) install.

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

Files touched

Diff

commit 181743c97d1e942eceb41e274c25e9f697c45339
Author: Steve <steve@designerwallcoverings.com>
Date:   Mon Aug 3 10:44:12 2026 -0700

    deploy: schema-migration step + /shop smoke test (stop code-ahead-of-schema)
    
    Prevents a repeat of the 2026-08-03 regression where shipped code referenced a
    prod-missing column (products.suppressed) + tables (affiliate_settings,
    suppress_rules) and /shop 500'd while the DB-blind /healthz smoke test stayed
    green.
    
    - db/schema.sql: explicit ADD COLUMN IF NOT EXISTS guards (inline, before
      dependent indexes) so the file is a complete idempotent migration on a fresh
      OR drifted DB, not just a fresh-create.
    - scripts/migrate.sh: applies schema.sql with ON_ERROR_STOP; loads DATABASE_URL
      from the remote's own .env.
    - .deploy.conf: BUILD_CMD runs the migration on the remote after install/before
      reload; HEALTH_URL now hits /shop (exercises the real catalog query, not the
      DB-blind /healthz); INSTALL_CMD bakes in the sharp-safe (scripts-enabled) install.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 .deploy.conf       | 17 ++++++++++++++++-
 db/schema.sql      | 12 ++++++++++++
 scripts/migrate.sh | 23 +++++++++++++++++++++++
 3 files changed, 51 insertions(+), 1 deletion(-)

diff --git a/.deploy.conf b/.deploy.conf
index a27e74c..6ad99ee 100644
--- a/.deploy.conf
+++ b/.deploy.conf
@@ -2,5 +2,20 @@
 # Go-live to Kamatera is a GATED step — Steve approves the actual deploy.
 PROJECT_NAME=interiordesignershowroom
 DEPLOY_PATH=/root/Projects/interiordesignershowroom
-HEALTH_URL=http://127.0.0.1:9876/healthz
 PORT=9876
+
+# Smoke test hits /shop (not the DB-blind /healthz) so a code-ahead-of-schema
+# deploy fails RED — /shop exercises the real catalog query that 500'd on
+# 2026-08-03 when prod was missing the `suppressed` column.
+HEALTH_URL=http://127.0.0.1:9876/shop
+
+# sharp is a native dep — the deploy default `--ignore-scripts` skips its binary
+# resolution, so keep scripts enabled here (scoped: sharp 0.35 uses prebuilt
+# platform packages, no arbitrary postinstall build).
+INSTALL_CMD="npm ci --omit=dev"
+
+# Migration step: run AFTER install, BEFORE pm2 reload, on the remote. Applies the
+# idempotent db/schema.sql (CREATE ... IF NOT EXISTS + ADD COLUMN IF NOT EXISTS)
+# so schema never lags the shipped code again. ON_ERROR_STOP aborts the deploy on
+# a bad migration rather than reloading onto a broken DB.
+BUILD_CMD="bash scripts/migrate.sh"
diff --git a/db/schema.sql b/db/schema.sql
index dc42f08..1b97397 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -28,6 +28,15 @@ CREATE TABLE IF NOT EXISTS products (
   UNIQUE (network, external_id)
 );
 
+-- ── Idempotent column migrations (products) ──────────────────────────────────
+-- CREATE TABLE IF NOT EXISTS is a no-op on an existing table, so it will NOT add
+-- a column introduced after that table's genesis. Every later-added column MUST
+-- get an explicit ADD COLUMN IF NOT EXISTS guard HERE — before the indexes that
+-- depend on it — or code reading it 500s on any DB provisioned before the column
+-- existed (exactly what broke /shop on prod 2026-08-03: `suppressed` was code-side
+-- only). These guards run first so idx_products_suppressed below always resolves.
+ALTER TABLE products ADD COLUMN IF NOT EXISTS suppressed BOOLEAN NOT NULL DEFAULT FALSE;
+
 CREATE INDEX IF NOT EXISTS idx_products_room  ON products (room);
 CREATE INDEX IF NOT EXISTS idx_products_style ON products (style);
 CREATE INDEX IF NOT EXISTS idx_products_color ON products (color);
@@ -94,3 +103,6 @@ CREATE TABLE IF NOT EXISTS suppress_rules (
   created_at  TIMESTAMPTZ DEFAULT now(),
   UNIQUE (kind, value)
 );
+-- Note: per-column ADD COLUMN IF NOT EXISTS migration guards live inline with each
+-- table above (see the products block), so running this whole file top-to-bottom is
+-- a complete, safe, repeatable migration on a fresh OR a drifted database.
diff --git a/scripts/migrate.sh b/scripts/migrate.sh
new file mode 100755
index 0000000..4f43516
--- /dev/null
+++ b/scripts/migrate.sh
@@ -0,0 +1,23 @@
+#!/usr/bin/env bash
+# Idempotent DB migration: brings the target database up to db/schema.sql.
+#
+# Wired into the deploy as BUILD_CMD (.deploy.conf), so it runs on the remote
+# AFTER rsync and BEFORE pm2 reload — code is never shipped ahead of its schema
+# again (the 2026-08-03 regression: `products.suppressed` + affiliate_settings /
+# suppress_rules existed in code but not on the prod DB, so /shop 500'd).
+#
+# schema.sql is fully idempotent (CREATE TABLE/INDEX IF NOT EXISTS + explicit
+# ADD COLUMN IF NOT EXISTS guards), so this is safe to run repeatedly and on a
+# fresh OR a drifted database. ON_ERROR_STOP=1 makes a bad migration abort the
+# deploy instead of half-applying.
+set -euo pipefail
+cd "$(dirname "$0")/.."
+
+# 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
+: "${DATABASE_URL:?DATABASE_URL not set — need it in .env to migrate}"
+
+echo "── db migrate: applying db/schema.sql ──"
+psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f db/schema.sql
+echo "✓ schema applied"

← ffa8079 chore: v0.3.0 — keyword/id suppression feature (session clos  ·  back to Interiordesignershowroom  ·  auto-save: 2026-08-03T10:53:14 (2 files) — lib/rooms.js serv c8362bb →