End to end encrypted attachment upload and streaming

prisma/migrations/20260115105100_message_extras/migration.sql

@@ -0,0 +1,61 @@
+/*
+  Warnings:
+
+  - You are about to drop the column `edited` on the `Message` table. All the data in the column will be lost.
+
+*/
+-- AlterTable
+ALTER TABLE "Community" ADD COLUMN     "avatar" TEXT;
+
+-- AlterTable
+ALTER TABLE "Message" DROP COLUMN "edited",
+ADD COLUMN     "replyToId" TEXT;
+
+-- AlterTable
+ALTER TABLE "User" ADD COLUMN     "avatar" TEXT;
+
+-- CreateTable
+CREATE TABLE "Reaction" (
+    "id" TEXT NOT NULL,
+    "userId" TEXT NOT NULL,
+    "content" TEXT NOT NULL,
+    "messageId" TEXT NOT NULL,
+
+    CONSTRAINT "Reaction_pkey" PRIMARY KEY ("id")
+);
+
+-- CreateTable
+CREATE TABLE "Attachment" (
+    "id" TEXT NOT NULL,
+    "mimeType" TEXT NOT NULL,
+    "messageId" TEXT NOT NULL,
+
+    CONSTRAINT "Attachment_pkey" PRIMARY KEY ("id")
+);
+
+-- CreateTable
+CREATE TABLE "Chunk" (
+    "id" TEXT NOT NULL,
+    "iv" TEXT NOT NULL,
+    "attachmentId" TEXT,
+
+    CONSTRAINT "Chunk_pkey" PRIMARY KEY ("id")
+);
+
+-- CreateIndex
+CREATE UNIQUE INDEX "Reaction_id_key" ON "Reaction"("id");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "Attachment_id_key" ON "Attachment"("id");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "Chunk_id_key" ON "Chunk"("id");
+
+-- AddForeignKey
+ALTER TABLE "Reaction" ADD CONSTRAINT "Reaction_messageId_fkey" FOREIGN KEY ("messageId") REFERENCES "Message"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
+
+-- AddForeignKey
+ALTER TABLE "Attachment" ADD CONSTRAINT "Attachment_messageId_fkey" FOREIGN KEY ("messageId") REFERENCES "Message"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
+
+-- AddForeignKey
+ALTER TABLE "Chunk" ADD CONSTRAINT "Chunk_attachmentId_fkey" FOREIGN KEY ("attachmentId") REFERENCES "Attachment"("id") ON DELETE SET NULL ON UPDATE CASCADE;

prisma/migrations/20260115105707_message_extras_2/migration.sql

@@ -0,0 +1,25 @@
+/*
+  Warnings:
+
+  - You are about to drop the column `userId` on the `Reaction` table. All the data in the column will be lost.
+
+*/
+-- AlterTable
+ALTER TABLE "Reaction" DROP COLUMN "userId";
+
+-- CreateTable
+CREATE TABLE "_ReactionToUser" (
+    "A" TEXT NOT NULL,
+    "B" TEXT NOT NULL,
+
+    CONSTRAINT "_ReactionToUser_AB_pkey" PRIMARY KEY ("A","B")
+);
+
+-- CreateIndex
+CREATE INDEX "_ReactionToUser_B_index" ON "_ReactionToUser"("B");
+
+-- AddForeignKey
+ALTER TABLE "_ReactionToUser" ADD CONSTRAINT "_ReactionToUser_A_fkey" FOREIGN KEY ("A") REFERENCES "Reaction"("id") ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- AddForeignKey
+ALTER TABLE "_ReactionToUser" ADD CONSTRAINT "_ReactionToUser_B_fkey" FOREIGN KEY ("B") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

prisma/migrations/20260115105814_messages_edited/migration.sql

@@ -0,0 +1,2 @@
+-- AlterTable
+ALTER TABLE "Message" ADD COLUMN     "edited" BOOLEAN NOT NULL DEFAULT false;

prisma/migrations/20260115114337_message_extras_2/migration.sql

@@ -0,0 +1,8 @@
+-- DropForeignKey
+ALTER TABLE "Attachment" DROP CONSTRAINT "Attachment_messageId_fkey";
+
+-- AlterTable
+ALTER TABLE "Attachment" ALTER COLUMN "messageId" DROP NOT NULL;
+
+-- AlterTable
+ALTER TABLE "Message" ADD COLUMN     "attachments" TEXT[];

prisma/migrations/20260115114509_message_extra_3/migration.sql

@@ -0,0 +1,5 @@
+-- AlterTable
+ALTER TABLE "Attachment" ADD COLUMN     "communityId" TEXT;
+
+-- AddForeignKey
+ALTER TABLE "Attachment" ADD CONSTRAINT "Attachment_communityId_fkey" FOREIGN KEY ("communityId") REFERENCES "Community"("id") ON DELETE SET NULL ON UPDATE CASCADE;

prisma/migrations/20260115115729_message_extras_4/migration.sql

@@ -0,0 +1,17 @@
+-- DropForeignKey
+ALTER TABLE "Attachment" DROP CONSTRAINT "Attachment_communityId_fkey";
+
+-- DropForeignKey
+ALTER TABLE "Chunk" DROP CONSTRAINT "Chunk_attachmentId_fkey";
+
+-- DropForeignKey
+ALTER TABLE "Reaction" DROP CONSTRAINT "Reaction_messageId_fkey";
+
+-- AddForeignKey
+ALTER TABLE "Reaction" ADD CONSTRAINT "Reaction_messageId_fkey" FOREIGN KEY ("messageId") REFERENCES "Message"("id") ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- AddForeignKey
+ALTER TABLE "Attachment" ADD CONSTRAINT "Attachment_communityId_fkey" FOREIGN KEY ("communityId") REFERENCES "Community"("id") ON DELETE CASCADE ON UPDATE CASCADE;
+
+-- AddForeignKey
+ALTER TABLE "Chunk" ADD CONSTRAINT "Chunk_attachmentId_fkey" FOREIGN KEY ("attachmentId") REFERENCES "Attachment"("id") ON DELETE CASCADE ON UPDATE CASCADE;

prisma/migrations/20260115122552_message_extras_5/migration.sql

@@ -0,0 +1,24 @@
+/*
+  Warnings:
+
+  - The primary key for the `Reaction` table will be changed. If it partially fails, the table could be left without primary key constraint.
+  - You are about to drop the column `id` on the `Reaction` table. All the data in the column will be lost.
+  - A unique constraint covering the columns `[content]` on the table `Reaction` will be added. If there are existing duplicate values, this will fail.
+
+*/
+-- DropForeignKey
+ALTER TABLE "_ReactionToUser" DROP CONSTRAINT "_ReactionToUser_A_fkey";
+
+-- DropIndex
+DROP INDEX "Reaction_id_key";
+
+-- AlterTable
+ALTER TABLE "Reaction" DROP CONSTRAINT "Reaction_pkey",
+DROP COLUMN "id",
+ADD CONSTRAINT "Reaction_pkey" PRIMARY KEY ("content");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "Reaction_content_key" ON "Reaction"("content");
+
+-- AddForeignKey
+ALTER TABLE "_ReactionToUser" ADD CONSTRAINT "_ReactionToUser_A_fkey" FOREIGN KEY ("A") REFERENCES "Reaction"("content") ON DELETE CASCADE ON UPDATE CASCADE;

prisma/migrations/20260115122709_message_extra_5_1/migration.sql

@@ -0,0 +1,24 @@
+/*
+  Warnings:
+
+  - The primary key for the `Reaction` table will be changed. If it partially fails, the table could be left without primary key constraint.
+  - A unique constraint covering the columns `[id]` on the table `Reaction` will be added. If there are existing duplicate values, this will fail.
+  - The required column `id` was added to the `Reaction` table with a prisma-level default value. This is not possible if the table is not empty. Please add this column as optional, then populate it before making it required.
+
+*/
+-- DropForeignKey
+ALTER TABLE "_ReactionToUser" DROP CONSTRAINT "_ReactionToUser_A_fkey";
+
+-- DropIndex
+DROP INDEX "Reaction_content_key";
+
+-- AlterTable
+ALTER TABLE "Reaction" DROP CONSTRAINT "Reaction_pkey",
+ADD COLUMN     "id" TEXT NOT NULL,
+ADD CONSTRAINT "Reaction_pkey" PRIMARY KEY ("id");
+
+-- CreateIndex
+CREATE UNIQUE INDEX "Reaction_id_key" ON "Reaction"("id");
+
+-- AddForeignKey
+ALTER TABLE "_ReactionToUser" ADD CONSTRAINT "_ReactionToUser_A_fkey" FOREIGN KEY ("A") REFERENCES "Reaction"("id") ON DELETE CASCADE ON UPDATE CASCADE;

prisma/migrations/20260115123550_attachment_name/migration.sql

@@ -0,0 +1,8 @@
+/*
+  Warnings:
+
+  - Added the required column `fileName` to the `Attachment` table without a default value. This is not possible if the table is not empty.
+
+*/
+-- AlterTable
+ALTER TABLE "Attachment" ADD COLUMN     "fileName" TEXT NOT NULL;

prisma/migrations/20260115123636_attachment_date/migration.sql

@@ -0,0 +1,2 @@
+-- AlterTable
+ALTER TABLE "Attachment" ADD COLUMN     "creationDate" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;

prisma/migrations/20260115123915_messages_attachments/migration.sql

@@ -0,0 +1,11 @@
+/*
+  Warnings:
+
+  - You are about to drop the column `attachments` on the `Message` table. All the data in the column will be lost.
+
+*/
+-- AlterTable
+ALTER TABLE "Message" DROP COLUMN "attachments";
+
+-- AddForeignKey
+ALTER TABLE "Attachment" ADD CONSTRAINT "Attachment_messageId_fkey" FOREIGN KEY ("messageId") REFERENCES "Message"("id") ON DELETE CASCADE ON UPDATE CASCADE;

prisma/migrations/20260115124028_message_attachment_2/migration.sql

@@ -0,0 +1,12 @@
+/*
+  Warnings:
+
+  - Made the column `communityId` on table `Attachment` required. This step will fail if there are existing NULL values in that column.
+  - Made the column `attachmentId` on table `Chunk` required. This step will fail if there are existing NULL values in that column.
+
+*/
+-- AlterTable
+ALTER TABLE "Attachment" ALTER COLUMN "communityId" SET NOT NULL;
+
+-- AlterTable
+ALTER TABLE "Chunk" ALTER COLUMN "attachmentId" SET NOT NULL;

prisma/migrations/20260115135751_nickname/migration.sql

@@ -0,0 +1,2 @@
+-- AlterTable
+ALTER TABLE "User" ADD COLUMN     "nickname" TEXT;

prisma/migrations/20260115140047_filename/migration.sql

@@ -0,0 +1,10 @@
+/*
+  Warnings:
+
+  - You are about to drop the column `fileName` on the `Attachment` table. All the data in the column will be lost.
+  - Added the required column `filename` to the `Attachment` table without a default value. This is not possible if the table is not empty.
+
+*/
+-- AlterTable
+ALTER TABLE "Attachment" DROP COLUMN "fileName",
+ADD COLUMN     "filename" TEXT NOT NULL;

prisma/migrations/20260116133849_attachment_size/migration.sql

@@ -0,0 +1,8 @@
+/*
+  Warnings:
+
+  - Added the required column `size` to the `Attachment` table without a default value. This is not possible if the table is not empty.
+
+*/
+-- AlterTable
+ALTER TABLE "Attachment" ADD COLUMN     "size" BIGINT NOT NULL;

prisma/migrations/20260116134610_chunks/migration.sql

@@ -0,0 +1,12 @@
+/*
+  Warnings:
+
+  - Added the required column `finishedUploading` to the `Attachment` table without a default value. This is not possible if the table is not empty.
+  - Added the required column `index` to the `Chunk` table without a default value. This is not possible if the table is not empty.
+
+*/
+-- AlterTable
+ALTER TABLE "Attachment" ADD COLUMN     "finishedUploading" BOOLEAN NOT NULL;
+
+-- AlterTable
+ALTER TABLE "Chunk" ADD COLUMN     "index" INTEGER NOT NULL;

prisma/migrations/20260116141809_chunk_iv_binary/migration.sql

@@ -0,0 +1,9 @@
+/*
+  Warnings:
+
+  - Changed the type of `iv` on the `Chunk` table. No cast exists, the column would be dropped and recreated, which cannot be done if there is data, since the column is required.
+
+*/
+-- AlterTable
+ALTER TABLE "Chunk" DROP COLUMN "iv",
+ADD COLUMN     "iv" BYTEA NOT NULL;

prisma/migrations/20260116142142_mimetype/migration.sql

@@ -0,0 +1,10 @@
+/*
+  Warnings:
+
+  - You are about to drop the column `mimeType` on the `Attachment` table. All the data in the column will be lost.
+  - Added the required column `mimetype` to the `Attachment` table without a default value. This is not possible if the table is not empty.
+
+*/
+-- AlterTable
+ALTER TABLE "Attachment" DROP COLUMN "mimeType",
+ADD COLUMN     "mimetype" TEXT NOT NULL;

prisma/migrations/20260116142745_chunks_community/migration.sql

@@ -0,0 +1,11 @@
+/*
+  Warnings:
+
+  - Added the required column `communityId` to the `Chunk` table without a default value. This is not possible if the table is not empty.
+
+*/
+-- AlterTable
+ALTER TABLE "Chunk" ADD COLUMN     "communityId" TEXT NOT NULL;
+
+-- AddForeignKey
+ALTER TABLE "Chunk" ADD CONSTRAINT "Chunk_communityId_fkey" FOREIGN KEY ("communityId") REFERENCES "Community"("id") ON DELETE CASCADE ON UPDATE CASCADE;

prisma/migrations/20260116143226_attachment_msg/migration.sql

@@ -0,0 +1,8 @@
+/*
+  Warnings:
+
+  - Made the column `messageId` on table `Attachment` required. This step will fail if there are existing NULL values in that column.
+
+*/
+-- AlterTable
+ALTER TABLE "Attachment" ALTER COLUMN "messageId" SET NOT NULL;

prisma/migrations/20260116143544_optional_msg/migration.sql

@@ -0,0 +1,2 @@
+-- AlterTable
+ALTER TABLE "Attachment" ALTER COLUMN "messageId" DROP NOT NULL;

prisma/schema.prisma

@@ -9,16 +9,19 @@ datasource db {
 }
 
 model Community {
-    id           String    @id @unique @default(uuid())
-    name         String    @unique
+    id           String       @id @unique @default(uuid())
+    name         String       @unique
     description  String?
-    creationDate DateTime  @default(now())
-    User         User      @relation(name: "OwnerCommunityToUser", fields: [ownerId], references: [id])
+    avatar       String?
+    creationDate DateTime     @default(now())
+    User         User         @relation(name: "OwnerCommunityToUser", fields: [ownerId], references: [id])
     ownerId      String
-    members      User[]    @relation(name: "MembersCommunitiesToUsers")
+    members      User[]       @relation(name: "MembersCommunitiesToUsers")
     channels     Channel[]
     roles        Role[]
     invites      Invite[]
+    attachments  Attachment[]
+    chunks       Chunk[]
 }
 
 model Channel {
@@ -45,9 +48,11 @@ model Role {
 model User {
     id               String      @id @unique @default(uuid())
     username         String      @unique
+    nickname         String?
     email            String?     @unique
     passwordHash     String?
     description      String?
+    avatar           String?
     admin            Boolean     @default(false)
     registerDate     DateTime    @default(now())
     lastLogin        DateTime?
@@ -57,6 +62,7 @@ model User {
     communities      Community[] @relation(name: "MembersCommunitiesToUsers")
     roles            Role[]      @relation(name: "UsersRolesToUsers")
     messages         Message[]
+    reactions        Reaction[]
 }
 
 model Session {
@@ -84,14 +90,49 @@ model Invite {
 }
 
 model Message {
-    id           String   @id @unique @default(uuid())
+    id           String       @id @unique @default(uuid())
     text         String
     iv           String?
-    editHistory  String[] @default([])
-    edited       Boolean  @default(false)
-    owner        User     @relation(fields: [ownerId], references: [id], onDelete: Cascade)
+    replyToId    String?
+    edited       Boolean      @default(false)
+    editHistory  String[]     @default([])
+    reactions    Reaction[]
+    attachments  Attachment[]
+    owner        User         @relation(fields: [ownerId], references: [id], onDelete: Cascade)
     ownerId      String
-    channel      Channel  @relation(fields: [channelId], references: [id], onDelete: Cascade)
+    channel      Channel      @relation(fields: [channelId], references: [id], onDelete: Cascade)
     channelId    String
-    creationDate DateTime @default(now())
+    creationDate DateTime     @default(now())
+}
+
+model Reaction {
+    id        String  @id @unique @default(uuid())
+    users     User[]
+    content   String
+    message   Message @relation(fields: [messageId], references: [id], onDelete: Cascade)
+    messageId String
+}
+
+model Attachment {
+    id                String    @id @unique @default(uuid())
+    filename          String
+    mimetype          String
+    size              BigInt
+    chunks            Chunk[]
+    finishedUploading Boolean
+    message           Message?  @relation(fields: [messageId], references: [id], onDelete: Cascade)
+    messageId         String?
+    community         Community @relation(fields: [communityId], references: [id], onDelete: Cascade)
+    communityId       String
+    creationDate      DateTime  @default(now())
+}
+
+model Chunk {
+    id           String     @id @unique @default(uuid())
+    iv           Bytes
+    index        Int
+    attachment   Attachment @relation(fields: [attachmentId], references: [id], onDelete: Cascade)
+    attachmentId String
+    community    Community  @relation(fields: [communityId], references: [id], onDelete: Cascade)
+    communityId  String
 }