`db/migrations/019_paper_comment_votes.sql`

29 lines
-- 019 · Helpful-votes on paper-thread comments (UX backlog #3, tick 4)
--
-- Designers and other installers can mark a comment "helpful" — that signal
-- drives the in-page sort (helpful_count DESC) AND the installer profile
-- contribution count. To prevent ballot-stuffing without forcing log-in
-- (we want designer participation, not just installer self-promotion),
-- track votes by SHA-256(ip + SESSION_SECRET) — same hashing pattern as
-- installer_interest.ip_hash.
--
-- Reversible: DROP TABLE paper_comment_votes;

BEGIN;

CREATE TABLE IF NOT EXISTS paper_comment_votes (
  comment_id  INTEGER NOT NULL REFERENCES paper_comments(id) ON DELETE CASCADE,
  ip_hash     TEXT NOT NULL,
                -- 16-char prefix of sha256(ip + SESSION_SECRET)
                -- low-cardinality but high enough that 1k spoofed votes
                -- per comment costs measurable effort.
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (comment_id, ip_hash)
                -- PK enforces one vote per IP per comment. Subsequent
                -- inserts ON CONFLICT DO NOTHING — fully idempotent.
);

CREATE INDEX IF NOT EXISTS idx_paper_comment_votes_comment ON paper_comment_votes(comment_id, created_at DESC);

COMMIT;