`db/schema.sql`

109 lines
-- interiordesignershowroom catalog schema
-- One normalized products table fed by 4 network adapters (CJ, Amazon, Rakuten, ShareASale).
-- The (network, external_id) pair is the natural key so re-ingests UPSERT instead of duplicating.

CREATE TABLE IF NOT EXISTS products (
  id             BIGSERIAL PRIMARY KEY,
  network        TEXT NOT NULL,               -- cj | amazon | rakuten | shareasale
  advertiser     TEXT,                        -- merchant/brand the link points at (Wayfair, etc.)
  external_id    TEXT NOT NULL,               -- network product id / ASIN / SKU
  title          TEXT NOT NULL,
  description    TEXT,
  brand          TEXT,
  category       TEXT,                         -- vendor category (raw)
  room           TEXT,                         -- OUR taxonomy: living-room, bedroom, ...
  style          TEXT,                         -- OUR taxonomy: modern, traditional, ...
  color          TEXT,                         -- OUR taxonomy: neutral, blue, green, ...
  price          NUMERIC(12,2),
  sale_price     NUMERIC(12,2),
  currency       TEXT DEFAULT 'USD',
  image_url      TEXT,
  affiliate_url  TEXT NOT NULL,                -- the tracked deep link (the whole point)
  in_stock       BOOLEAN DEFAULT TRUE,
  featured       BOOLEAN DEFAULT FALSE,        -- hand-picked hero pieces
  suppressed     BOOLEAN NOT NULL DEFAULT FALSE, -- admin "hide this product/brand" flag (kept in DB, hidden from every browse surface). Distinct from affiliate_settings (source-level).
  price_checked_at TIMESTAMPTZ,               -- Amazon TOS: never show stale prices
  created_at     TIMESTAMPTZ DEFAULT now(),
  updated_at     TIMESTAMPTZ DEFAULT now(),
  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);
CREATE INDEX IF NOT EXISTS idx_products_created ON products (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_products_suppressed ON products (suppressed) WHERE suppressed = TRUE;

-- Editorial buying guides / shop-the-look articles (the SEO + helpful-content layer).
CREATE TABLE IF NOT EXISTS guides (
  id           BIGSERIAL PRIMARY KEY,
  slug         TEXT UNIQUE NOT NULL,
  title        TEXT NOT NULL,
  dek          TEXT,                            -- subtitle / summary
  hero_image   TEXT,
  body_md      TEXT,                            -- markdown body
  product_ids  BIGINT[] DEFAULT '{}',           -- featured products in this guide
  room         TEXT,
  style        TEXT,
  published    BOOLEAN DEFAULT FALSE,
  created_at   TIMESTAMPTZ DEFAULT now(),
  updated_at   TIMESTAMPTZ DEFAULT now()
);

-- Affiliate click log — powers "what's converting" analytics + Amazon compliance audit trail.
CREATE TABLE IF NOT EXISTS clicks (
  id           BIGSERIAL PRIMARY KEY,
  product_id   BIGINT REFERENCES products(id) ON DELETE SET NULL,
  network      TEXT,
  advertiser   TEXT,
  referer      TEXT,
  ua           TEXT,
  clicked_at   TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_clicks_product ON clicks (product_id);
CREATE INDEX IF NOT EXISTS idx_clicks_time    ON clicks (clicked_at DESC);

-- Affiliate on/off control. An "affiliate" here is a source of tracked links —
-- either a whole NETWORK (advertiser = '') or a specific ADVERTISER/merchant on
-- that network. A product is visible on the storefront only if NEITHER its network
-- nor its (network, advertiser) has been switched OFF here. ABSENCE = ENABLED, so a
-- newly-ingested advertiser is live by default and we only persist explicit OFFs.
CREATE TABLE IF NOT EXISTS affiliate_settings (
  network     TEXT NOT NULL,
  advertiser  TEXT NOT NULL DEFAULT '',      -- '' = the whole network
  enabled     BOOLEAN NOT NULL DEFAULT TRUE,
  updated_at  TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY (network, advertiser)
);
-- Only the disabled rows ever need scanning during a storefront query.
CREATE INDEX IF NOT EXISTS idx_affiliate_settings_off ON affiliate_settings (network, advertiser) WHERE enabled = FALSE;

-- Durable keyword/id suppression rules. Distinct from affiliate_settings (which hides
-- by SOURCE) and from the raw `suppressed` flag (a one-off hide): a rule here is a
-- persistent record of "any product whose title/brand/advertiser contains this keyword
-- (or whose id equals this) should be hidden." Adding a rule immediately flips
-- `suppressed=TRUE` on every current match; "Vacuum" re-applies every rule so products
-- ingested LATER that match an old keyword get suppressed too. Kept as data (not just a
-- column flip) so the admin can see, re-apply, and remove what was entered.
CREATE TABLE IF NOT EXISTS suppress_rules (
  id          BIGSERIAL PRIMARY KEY,
  kind        TEXT NOT NULL DEFAULT 'keyword' CHECK (kind IN ('keyword','id')),
  value       TEXT NOT NULL,                 -- the keyword text, or the product id as text
  note        TEXT,
  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.