Felitendo
2421951be6
The feedback board ranked on a thumbs up buried among seven other emoji, which meant seconding a request cost a tap to open the thread, a tap to find the picker and a tap to pick. Every report now carries an arrow, a score and an arrow on the list itself, so agreeing with one is one tap and never leaves the board. Replies carry the same control. One install gets one vote per report and per reply, up or down, and the score is up minus down. The reactions are gone with the room they took: a board with two ways to agree ranks on neither. The request carries the state to end up with rather than a toggle, so a retry after a dropped connection cannot silently undo the vote it just made, and both front ends move the number before the answer arrives. Operators still read the tally without being able to add to it. Upgrading an existing database drops a table and a column, which the start-up push refuses on purpose; web/README.md carries the statement to apply once by hand.
73 lines
3.0 KiB
SQL
73 lines
3.0 KiB
SQL
-- Voting replaces reacting on the feedback board.
|
|
--
|
|
-- A reaction was eight emoji of which exactly one meant anything: `+1` was what
|
|
-- ranked the board. A vote is one value per install per target, `1` or `-1`, so
|
|
-- "how much does anyone want this?" is a sum rather than a tally of glyphs, and
|
|
-- it fits on a board row next to the title instead of behind a tap.
|
|
--
|
|
-- The two emoji that were already votes carry over; the other six were opinions
|
|
-- with no ranking behind them and are dropped with the table. The counters are
|
|
-- renamed rather than reused because their old values counted rows that are
|
|
-- about to be gone, and `vote_score` is stored because Prisma cannot order by an
|
|
-- expression.
|
|
|
|
CREATE TABLE "feedback_votes" (
|
|
"id" SERIAL NOT NULL,
|
|
"ticket_id" INTEGER NOT NULL,
|
|
"comment_id" INTEGER,
|
|
"install_id" TEXT NOT NULL,
|
|
"value" INTEGER NOT NULL,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "feedback_votes_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE INDEX "feedback_votes_ticket_id_comment_id_idx" ON "feedback_votes"("ticket_id", "comment_id");
|
|
CREATE INDEX "feedback_votes_install_id_idx" ON "feedback_votes"("install_id");
|
|
|
|
ALTER TABLE "feedback_votes" ADD CONSTRAINT "feedback_votes_ticket_id_fkey"
|
|
FOREIGN KEY ("ticket_id") REFERENCES "feedback_tickets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "feedback_votes" ADD CONSTRAINT "feedback_votes_comment_id_fkey"
|
|
FOREIGN KEY ("comment_id") REFERENCES "feedback_comments"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- One row per install per target: an install that managed to file two `+1`s on
|
|
-- one ticket was always counted once, and `DISTINCT ON` keeps that true. An
|
|
-- install that reacted both 👍 and 👎 to the same thing kept both rows; the
|
|
-- newest one is the vote it ends up with.
|
|
INSERT INTO "feedback_votes" ("ticket_id", "comment_id", "install_id", "value", "created_at", "updated_at")
|
|
SELECT DISTINCT ON ("ticket_id", "comment_id", "install_id")
|
|
"ticket_id",
|
|
"comment_id",
|
|
"install_id",
|
|
CASE WHEN "emoji" = '+1' THEN 1 ELSE -1 END,
|
|
"created_at",
|
|
"created_at"
|
|
FROM "feedback_reactions"
|
|
WHERE "emoji" IN ('+1', '-1')
|
|
ORDER BY "ticket_id", "comment_id", "install_id", "created_at" DESC;
|
|
|
|
DROP TABLE "feedback_reactions";
|
|
|
|
ALTER TABLE "feedback_tickets"
|
|
DROP COLUMN "upvotes",
|
|
ADD COLUMN "vote_up" INTEGER NOT NULL DEFAULT 0,
|
|
ADD COLUMN "vote_down" INTEGER NOT NULL DEFAULT 0,
|
|
ADD COLUMN "vote_score" INTEGER NOT NULL DEFAULT 0;
|
|
|
|
UPDATE "feedback_tickets" t
|
|
SET "vote_up" = v."up", "vote_down" = v."down", "vote_score" = v."up" - v."down"
|
|
FROM (
|
|
SELECT
|
|
"ticket_id",
|
|
count(*) FILTER (WHERE "value" > 0) AS "up",
|
|
count(*) FILTER (WHERE "value" < 0) AS "down"
|
|
FROM "feedback_votes"
|
|
WHERE "comment_id" IS NULL
|
|
GROUP BY "ticket_id"
|
|
) v
|
|
WHERE t."id" = v."ticket_id";
|
|
|
|
-- `feedback_tickets_hidden_upvotes_idx` went with the column it was on.
|
|
CREATE INDEX "feedback_tickets_hidden_vote_score_idx" ON "feedback_tickets"("hidden", "vote_score");
|