`migrations/014_ratings.sql`

46 lines
-- Star ratings + dimension scores (service / price / quality) for firms and attorneys.
-- Sources: user-submitted, Avvo, Yelp, Google, Reddit (future), manual.

CREATE TABLE IF NOT EXISTS ratings (
  id BIGSERIAL PRIMARY KEY,
  professional_id BIGINT REFERENCES professionals(id) ON DELETE CASCADE,
  organization_id BIGINT REFERENCES organizations(id) ON DELETE CASCADE,
  reviewer_user_id BIGINT REFERENCES app_users(id) ON DELETE SET NULL,
  source TEXT NOT NULL CHECK (source IN ('user','avvo','google','yelp','reddit','manual')),
  source_url TEXT,
  external_review_id TEXT,                       -- e.g. Yelp review id, for dedup
  stars NUMERIC(2,1) NOT NULL CHECK (stars BETWEEN 0 AND 5),
  service_score NUMERIC(2,1),
  price_score NUMERIC(2,1),
  quality_score NUMERIC(2,1),
  comment TEXT,
  reviewer_name TEXT,                            -- for external sources where we only have a display name
  posted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ingested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  hidden_at TIMESTAMPTZ,                         -- moderation soft-delete
  hidden_reason TEXT,
  CONSTRAINT rating_target_check
    CHECK (professional_id IS NOT NULL OR organization_id IS NOT NULL)
);
CREATE INDEX IF NOT EXISTS idx_ratings_pro ON ratings (professional_id) WHERE professional_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_ratings_org ON ratings (organization_id) WHERE organization_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_ratings_source ON ratings (source);
CREATE UNIQUE INDEX IF NOT EXISTS uq_ratings_external
  ON ratings (source, external_review_id) WHERE external_review_id IS NOT NULL;

-- Aggregate cache (rebuilt from triggers / cron — keeps firm/attorney pages fast)
CREATE TABLE IF NOT EXISTS rating_aggregates (
  target_kind TEXT NOT NULL CHECK (target_kind IN ('professional','organization')),
  target_id BIGINT NOT NULL,
  total_reviews INT NOT NULL DEFAULT 0,
  user_review_count INT NOT NULL DEFAULT 0,
  avg_stars NUMERIC(3,2),
  avg_service NUMERIC(3,2),
  avg_price NUMERIC(3,2),
  avg_quality NUMERIC(3,2),
  overall_score NUMERIC(4,2),                   -- weighted formula from spec
  by_source JSONB,                              -- {avvo:{count,avg}, yelp:{...}, google:{...}, user:{...}}
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (target_kind, target_id)
);