← back to Animals

migrations/002_emails.sql

56 lines

-- Email enrichment — emails are the most valuable contact data on the platform.
-- Without an email we can't run leads outreach (Rail B) OR the upgrade pitch
-- (Rail C). Treat email discovery as a first-class job, not a side-effect.
--
-- Two granularities:
--   1. business_emails — one row per (business, email, source_page_url).
--      Lets us track multiple emails per business + remember WHERE we found
--      them (for compliance + re-verify later when they go stale).
--   2. veterinarian_emails — same but for individual vets.
-- Plus a `email_kind` heuristic to distinguish role-based (info@, contact@)
-- from personal (drsmith@…) so the lead router can pick the right one.

BEGIN;

CREATE TABLE IF NOT EXISTS business_emails (
  id              BIGSERIAL PRIMARY KEY,
  business_id     BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
  email           TEXT NOT NULL,
  email_kind      TEXT CHECK (email_kind IN ('role','personal','staff','intake','admin','unknown')),
  context_label   TEXT,            -- nearby text on page: "Office Manager", "Dr. Smith"
  source_url      TEXT NOT NULL,   -- page where we found it
  source_method   TEXT NOT NULL,   -- 'mailto' | 'plaintext' | 'jsonld' | 'manual'
  found_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  verified_at     TIMESTAMPTZ,
  verification_status TEXT NOT NULL DEFAULT 'unverified'
                      CHECK (verification_status IN ('unverified','syntactically_valid','mx_valid','bounced','opted_out','rejected'))
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_biz_emails_uniq ON business_emails(business_id, LOWER(email), source_url);
CREATE INDEX IF NOT EXISTS idx_biz_emails_biz   ON business_emails(business_id);
CREATE INDEX IF NOT EXISTS idx_biz_emails_email ON business_emails(LOWER(email));
CREATE INDEX IF NOT EXISTS idx_biz_emails_kind  ON business_emails(email_kind);

CREATE TABLE IF NOT EXISTS veterinarian_emails (
  id              BIGSERIAL PRIMARY KEY,
  veterinarian_id BIGINT NOT NULL REFERENCES veterinarians(id) ON DELETE CASCADE,
  business_id     BIGINT REFERENCES businesses(id) ON DELETE SET NULL,
  email           TEXT NOT NULL,
  email_kind      TEXT CHECK (email_kind IN ('role','personal','staff','intake','admin','unknown')),
  source_url      TEXT NOT NULL,
  source_method   TEXT NOT NULL,
  found_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  verified_at     TIMESTAMPTZ,
  verification_status TEXT NOT NULL DEFAULT 'unverified'
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_vet_emails_uniq ON veterinarian_emails(veterinarian_id, LOWER(email));
CREATE INDEX IF NOT EXISTS idx_vet_emails_vet   ON veterinarian_emails(veterinarian_id);
CREATE INDEX IF NOT EXISTS idx_vet_emails_email ON veterinarian_emails(LOWER(email));

-- Add a "last attempted to find emails" stamp so the enricher can skip
-- recently-checked sites and not hammer the same hosts.
ALTER TABLE businesses
  ADD COLUMN IF NOT EXISTS email_search_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS email_search_pages_tried INTEGER NOT NULL DEFAULT 0;

COMMIT;