`db/migrations/008_pitches.sql`

42 lines
-- 008_pitches.sql · DW pitch pipeline
-- Each row = one outreach target. Lifecycle status walks left-to-right.

CREATE TABLE IF NOT EXISTS pitches (
  id                  BIGSERIAL PRIMARY KEY,
  business_id         BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
  pitch_type          TEXT NOT NULL,           -- 'trade-partner' | 'trade-client' | 'cross-sell' | 'showroom-partner' | 'retail-counter' | 'skip-misfile' | 'unknown'
  priority            SMALLINT NOT NULL DEFAULT 5,  -- 1=DW peer, 2=interior firm, 3=design, 4=home-furnish, 5=furniture, 9=skip
  status              TEXT NOT NULL DEFAULT 'draft',-- 'draft'|'researched'|'scrubbed'|'approved'|'sent'|'replied'|'won'|'lost'|'skip'
  subject             TEXT,
  body                TEXT,
  observation         TEXT,                    -- "what we observed" notes filled in by hand
  why_dw_fits         TEXT,
  research_links      JSONB DEFAULT '{}'::jsonb, -- {google, gmaps, yelp, linkedin, ig}
  pitch_md_path       TEXT,                    -- relative path to data/pitches/<id>-<slug>.md
  -- Lifecycle timestamps
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  scrubbed_at         TIMESTAMPTZ,
  approved_at         TIMESTAMPTZ,
  sent_at             TIMESTAMPTZ,
  replied_at          TIMESTAMPTZ,
  closed_at           TIMESTAMPTZ,
  -- Outcome
  outcome             TEXT,                    -- 'sale'|'meeting'|'unsubscribe'|'ignored'|'wrong_target'|null
  notes               TEXT,
  UNIQUE (business_id)
);

CREATE INDEX IF NOT EXISTS idx_pitches_status   ON pitches (status);
CREATE INDEX IF NOT EXISTS idx_pitches_priority ON pitches (priority);
CREATE INDEX IF NOT EXISTS idx_pitches_type     ON pitches (pitch_type);
CREATE INDEX IF NOT EXISTS idx_pitches_outcome  ON pitches (outcome) WHERE outcome IS NOT NULL;

-- update_updated_at trigger
CREATE OR REPLACE FUNCTION pitches_touch_updated_at() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
$$;
DROP TRIGGER IF EXISTS pitches_updated_at ON pitches;
CREATE TRIGGER pitches_updated_at BEFORE UPDATE ON pitches
  FOR EACH ROW EXECUTE FUNCTION pitches_touch_updated_at();