`migrations/016_replies_and_views.sql`

26 lines
-- Lawyer can post a single reply per rating (their answer/correction).
CREATE TABLE IF NOT EXISTS rating_replies (
  id BIGSERIAL PRIMARY KEY,
  rating_id BIGINT NOT NULL REFERENCES ratings(id) ON DELETE CASCADE,
  responder_user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
  body TEXT NOT NULL,
  posted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  edited_at TIMESTAMPTZ,
  hidden_at TIMESTAMPTZ,
  UNIQUE (rating_id, responder_user_id)   -- one reply per (rating, user)
);
CREATE INDEX IF NOT EXISTS idx_rating_replies_rating ON rating_replies (rating_id);

-- Pitch-page open tracking — every visit to /p/:firm_id
CREATE TABLE IF NOT EXISTS pitch_views (
  id BIGSERIAL PRIMARY KEY,
  organization_id BIGINT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  visitor_ip TEXT,
  visitor_ua TEXT,
  referrer TEXT,
  utm_source TEXT,                          -- ?utm_source= for tracking which campaign drove it
  viewed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_pitch_views_org ON pitch_views (organization_id, viewed_at DESC);
CREATE INDEX IF NOT EXISTS idx_pitch_views_recent ON pitch_views (viewed_at DESC);