-- Per-user read marker for a shot's comments: the shot-status board flags a -- shot with "!" while it has comments from others newer than the marker. CREATE TABLE "shot_comment_reads" ( "id" TEXT NOT NULL, "userId" TEXT NOT NULL, "shotId" TEXT NOT NULL, "readAt" TIMESTAMP(3) NOT NULL, CONSTRAINT "shot_comment_reads_pkey" PRIMARY KEY ("id") ); CREATE UNIQUE INDEX "shot_comment_reads_userId_shotId_key" ON "shot_comment_reads"("userId", "shotId"); CREATE INDEX "shot_comment_reads_shotId_idx" ON "shot_comment_reads"("shotId"); ALTER TABLE "shot_comment_reads" ADD CONSTRAINT "shot_comment_reads_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE "shot_comment_reads" ADD CONSTRAINT "shot_comment_reads_shotId_fkey" FOREIGN KEY ("shotId") REFERENCES "shots"("id") ON DELETE CASCADE ON UPDATE CASCADE; -- Everything already commented counts as read on launch, so the board starts -- clean instead of flagging every shot that has ever had a note. INSERT INTO "shot_comment_reads" ("id", "userId", "shotId", "readAt") SELECT md5(u."id" || ':' || s."id"), u."id", s."id", CURRENT_TIMESTAMP FROM "users" u CROSS JOIN "shots" s WHERE u."role" <> 'CLIENT' AND EXISTS ( SELECT 1 FROM "comments" c JOIN "versions" v ON v."id" = c."versionId" LEFT JOIN "tasks" t ON t."id" = v."taskId" WHERE COALESCE(v."shotId", t."shotId") = s."id" );