`db/migrations/016_installer_interest.sql`

30 lines
-- Capture queue for "notify me when this studio is on NPH" submissions
-- collected from the /book page when an installer is unclaimed. When the
-- studio claims their listing + configures availability, every email in
-- this table for that installer_id gets a one-shot notification email.
--
-- Why a real table: a queue beats firing emails inline because (a) the
-- claimer's first action might be over hours/days/weeks after the request,
-- and (b) compliance scrub (suppression list) needs to run at send-time,
-- not capture-time, since users can opt out in between.

CREATE TABLE IF NOT EXISTS installer_interest (
  id            BIGSERIAL PRIMARY KEY,
  installer_id  BIGINT NOT NULL REFERENCES installers(id) ON DELETE CASCADE,
  email         TEXT NOT NULL,  -- always stored lower-cased; route handler normalizes
  source        TEXT NOT NULL DEFAULT 'book_page',
  zip           TEXT,
  notes         TEXT,
  ip_hash       TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  notified_at   TIMESTAMPTZ,
  notify_msg_id TEXT,
  UNIQUE (installer_id, email)
);

CREATE INDEX IF NOT EXISTS installer_interest_installer_id_idx
  ON installer_interest (installer_id) WHERE notified_at IS NULL;

CREATE INDEX IF NOT EXISTS installer_interest_email_idx
  ON installer_interest (email);