Felitendo
71d670bd1c
Anonymous used to be one word for everybody, so three unnamed people in a thread were indistinguishable and none of them could be replied to. Every author now has a handle from the moment their row exists, and the board shows it: Anonymous_12 is a real identity, stable, distinct, and still saying nothing about who is behind it. That makes @mentions possible, and they work for a handle as well as for a name. Mentions are resolved when the post is written rather than searched for afterwards, so a rename cannot rewrite who was pinged and @ann never fires on @anna. Being named puts the thread on your list and notifies you, the same way an answer does. Both consoles draw them, and the team can @ somebody too. Picking a name is a registration now. A name belongs to one install, it is held by a four digit PIN, and the same PIN is what lets its owner take it with them onto a new phone, since the install id does not survive a reinstall. Wrong PINs are counted against the name rather than the caller and lock it for a while, doubling from five minutes. Names are two to thirty two letters, numbers or underscores, which is what gives a mention an unambiguous end. Also: - the composer no longer asks for a name. It says who the report will be posted as and offers the one place that changes it, and no name is accepted alongside a post any more. - a team reply is badged Admin instead of Team. The byline beside it already reads FTStore Team, so the old badge repeated the line under it. - names that existed before registration did keep working: their key fills itself in the first time their owner opens the app.
64 lines
3.1 KiB
SQL
64 lines
3.1 KiB
SQL
-- Giving every feedback author a name that resolves.
|
|
--
|
|
-- Three things at once, because they are one idea. An author who never picked a
|
|
-- name still gets `handle_number`, so the board can call them `Anonymous_12`
|
|
-- instead of merging every silent install into one "Anonymous". A name is now
|
|
-- registered rather than typed: `name_key` is unique, so it belongs to one
|
|
-- install, and `pin_hash` plus the two throttle columns are what let the person
|
|
-- behind it claim it again on a new device without letting anyone else.
|
|
--
|
|
-- `feedback_mentions` is the resolved form of an `@handle`: written at post
|
|
-- time, so a rename cannot rewrite who was pinged, and cascaded away with the
|
|
-- ticket or the reply it was written in.
|
|
|
|
ALTER TABLE "feedback_authors"
|
|
ADD COLUMN "handle_number" SERIAL,
|
|
ADD COLUMN "name_key" TEXT,
|
|
ADD COLUMN "pin_hash" TEXT,
|
|
ADD COLUMN "pin_failures" INTEGER NOT NULL DEFAULT 0,
|
|
ADD COLUMN "pin_locked_until" TIMESTAMP(3);
|
|
|
|
-- Names that existed before registration did. They keep the name they are
|
|
-- posting under and hold it against anyone else claiming it; the first time
|
|
-- their owner opens the name screen they are asked to set a PIN.
|
|
UPDATE "feedback_authors"
|
|
SET "name_key" = trim(regexp_replace(lower("display_name"), '[^a-z0-9]+', ' ', 'g'))
|
|
WHERE "display_name" IS NOT NULL
|
|
AND trim(regexp_replace(lower("display_name"), '[^a-z0-9]+', ' ', 'g')) <> '';
|
|
|
|
-- Two installs that picked the same name before it was unique: the older row
|
|
-- keeps it, the rest go back to their anonymous handle rather than the
|
|
-- migration failing on the index below.
|
|
UPDATE "feedback_authors" a
|
|
SET "display_name" = NULL, "name_key" = NULL
|
|
WHERE a."name_key" IS NOT NULL
|
|
AND EXISTS (
|
|
SELECT 1 FROM "feedback_authors" b
|
|
WHERE b."name_key" = a."name_key"
|
|
AND (b."created_at" < a."created_at"
|
|
OR (b."created_at" = a."created_at" AND b."install_id" < a."install_id"))
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "feedback_authors_handle_number_key" ON "feedback_authors"("handle_number");
|
|
CREATE UNIQUE INDEX "feedback_authors_name_key_key" ON "feedback_authors"("name_key");
|
|
|
|
CREATE TABLE "feedback_mentions" (
|
|
"id" SERIAL NOT NULL,
|
|
"ticket_id" INTEGER NOT NULL,
|
|
"comment_id" INTEGER,
|
|
"install_id" TEXT NOT NULL,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "feedback_mentions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE INDEX "feedback_mentions_install_id_ticket_id_idx" ON "feedback_mentions"("install_id", "ticket_id");
|
|
CREATE INDEX "feedback_mentions_ticket_id_comment_id_idx" ON "feedback_mentions"("ticket_id", "comment_id");
|
|
|
|
ALTER TABLE "feedback_mentions" ADD CONSTRAINT "feedback_mentions_ticket_id_fkey"
|
|
FOREIGN KEY ("ticket_id") REFERENCES "feedback_tickets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "feedback_mentions" ADD CONSTRAINT "feedback_mentions_comment_id_fkey"
|
|
FOREIGN KEY ("comment_id") REFERENCES "feedback_comments"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "feedback_mentions" ADD CONSTRAINT "feedback_mentions_install_id_fkey"
|
|
FOREIGN KEY ("install_id") REFERENCES "feedback_authors"("install_id") ON DELETE CASCADE ON UPDATE CASCADE;
|