`migrations/005_leads.sql`

34 lines
-- Inbound consumer leads — the LendingTree-for-lawyers funnel.
-- Submitted via the public /find-a-lawyer form, reviewed/routed by admins.

CREATE TABLE IF NOT EXISTS leads (
  id                 BIGSERIAL PRIMARY KEY,
  full_name          TEXT NOT NULL,
  email              TEXT NOT NULL,
  phone              TEXT,
  practice_area      TEXT NOT NULL,    -- 'personal_injury' | 'family' | 'criminal' | 'immigration' | 'business' | 'estate' | 'other'
  zip                TEXT,
  city               TEXT,
  case_description   TEXT,
  budget             TEXT,             -- 'under_500' | '500_2k' | '2k_10k' | '10k_plus' | 'unsure'
  urgency            TEXT,             -- 'immediate' | 'within_week' | 'within_month' | 'just_researching'
  consent_to_contact BOOLEAN NOT NULL DEFAULT true,
  status             TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'matched', 'contacted', 'won', 'lost', 'spam')),
  matched_firm_ids   BIGINT[],
  admin_notes        TEXT,
  ip                 TEXT,
  user_agent         TEXT,
  source             TEXT,             -- 'public_form' | 'partner' | 'organic' | 'paid'
  created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_leads_status        ON leads(status);
CREATE INDEX IF NOT EXISTS idx_leads_practice_area ON leads(practice_area);
CREATE INDEX IF NOT EXISTS idx_leads_created       ON leads(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_leads_zip           ON leads(zip);

CREATE OR REPLACE TRIGGER trg_leads_updated
  BEFORE UPDATE ON leads
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();