`db/migrations/002_enrichment.sql`

76 lines
-- Phase v: enrichment for "who to contact and how" analysis.
-- Two new tables: business_enrichment (1:1 with business) and business_contacts
-- (1:many — each named human associated with the business).

BEGIN;

-- ─── business_enrichment ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS business_enrichment (
  business_id        BIGINT PRIMARY KEY REFERENCES businesses(id) ON DELETE CASCADE,
  enriched_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  -- Ownership / structure
  ownership_class    TEXT,                  -- 'corporate' | 'franchise' | 'independent' | 'nonprofit' | 'unknown'
  parent_company     TEXT,                  -- e.g., "CVS Health Corporation"
  parent_brand       TEXT,                  -- e.g., "CVS"
  corporate_domain   TEXT,                  -- e.g., "cvshealth.com" (often different from cvs.com)
  is_franchise       BOOLEAN DEFAULT FALSE,
  is_chain           BOOLEAN DEFAULT FALSE,
  -- Best contact route inferred from the data we have
  best_contact_path  TEXT,                  -- 'local-manager' | 'corporate-pr' | 'owner-direct' | 'franchisee' | 'unknown'
  best_contact_note  TEXT,
  -- Niche metadata
  cuisine            TEXT,                  -- restaurants only
  price_band         TEXT,                  -- '$' | '$$' | '$$$' | '$$$$'
  hours_json         JSONB,                 -- weekly hours if extracted
  -- Social
  linkedin_company   TEXT,                  -- LinkedIn company page URL
  facebook_url       TEXT,
  instagram_handle   TEXT,
  twitter_handle     TEXT,
  -- Reviews
  rating_google      NUMERIC(3,2),
  rating_yelp        NUMERIC(3,2),
  review_count_total INTEGER,
  -- Provenance + quality
  enrichment_sources TEXT[],                -- which agents touched this row
  confidence         NUMERIC(3,2),          -- 0..1 overall confidence
  raw_signals        JSONB                  -- full detail blob for debugging
);
CREATE INDEX IF NOT EXISTS idx_enrichment_class ON business_enrichment (ownership_class);
CREATE INDEX IF NOT EXISTS idx_enrichment_chain ON business_enrichment (is_chain) WHERE is_chain;

-- ─── business_contacts ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS business_contacts (
  id                 BIGSERIAL PRIMARY KEY,
  business_id        BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
  -- Role within the business
  role               TEXT NOT NULL,         -- 'owner' | 'co-owner' | 'gm' | 'chef' | 'manager' | 'franchisee'
                                            --  | 'marketing' | 'corporate_local_rep' | 'pr' | 'media-relations'
  person_name        TEXT,
  person_title       TEXT,                  -- raw title pulled from source ("Owner", "Executive Chef", "GM")
  person_linkedin    TEXT,
  person_email       TEXT,                  -- only when sourced cleanly (Hunter, About page)
  person_phone       TEXT,
  -- Provenance
  source             TEXT NOT NULL,         -- 'linkedin' | 'website-about' | 'gmb' | 'yelp' | 'manual' | 'other'
  source_url         TEXT,
  source_snippet     TEXT,                  -- the bit of text we pulled the contact from
  confidence         NUMERIC(3,2),          -- 0..1
  first_seen_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_verified_at   TIMESTAMPTZ,
  notes              TEXT,
  -- Synthesised dedupe key (functional indexes can't be UNIQUE inline in PG;
  -- compute it here and use a unique index below).
  dedupe_key         TEXT GENERATED ALWAYS AS (COALESCE(person_linkedin, person_name, '')) STORED
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_contacts_business_source_dedupe
  ON business_contacts (business_id, source, dedupe_key);
CREATE INDEX IF NOT EXISTS idx_contacts_business ON business_contacts (business_id);
CREATE INDEX IF NOT EXISTS idx_contacts_role ON business_contacts (role);

-- ─── Migrations ledger ───────────────────────────────────────────────
INSERT INTO schema_migrations (filename) VALUES ('002_enrichment.sql')
  ON CONFLICT (filename) DO NOTHING;

COMMIT;