From 50f6de2f6c8b05193d0ca3f3881bd662b4babe60 Mon Sep 17 00:00:00 2001
From: Aleksandr Nogikh <nogikh@google.com>
Date: Mon, 12 May 2025 14:34:19 +0200
Subject: syz-cluster: add reply tracking functionality

Add API to record replies under the reports that allows to determine the
original report only by having the MessageID.
---
 syz-cluster/pkg/db/migrations/1_initialize.up.sql | 13 +++++++++++--
 1 file changed, 11 insertions(+), 2 deletions(-)

(limited to 'syz-cluster/pkg/db/migrations/1_initialize.up.sql')

diff --git a/syz-cluster/pkg/db/migrations/1_initialize.up.sql b/syz-cluster/pkg/db/migrations/1_initialize.up.sql
index ca91b343a..3100c9bb2 100644
--- a/syz-cluster/pkg/db/migrations/1_initialize.up.sql
+++ b/syz-cluster/pkg/db/migrations/1_initialize.up.sql
@@ -100,14 +100,23 @@ CREATE UNIQUE INDEX NoDupFindings ON Findings(SessionID, TestName, Title);
 
 -- Session's bug reports.
 CREATE TABLE SessionReports (
-    ID STRING(36) NOT NULL, -- UUID??
+    ID STRING(36) NOT NULL, -- UUID
     SessionID STRING(36) NOT NULL, -- UUID
     ReportedAt TIMESTAMP,
     Moderation BOOL,
-    MessageID STRING(256),
+    MessageID STRING(512),
     Reporter STRING(256),
     CONSTRAINT FK_SessionReports FOREIGN KEY (SessionID) REFERENCES Sessions (ID),
 ) PRIMARY KEY(ID);
 
 CREATE UNIQUE INDEX NoDupSessionReports ON SessionReports(SessionID, Moderation);
 CREATE INDEX SessionReportsByStatus ON SessionReports (Reporter, ReportedAt);
+CREATE INDEX SessionReportsByMessageID ON SessionReports(Reporter, MessageID);
+
+-- Replies on a session report.
+CREATE TABLE ReportReplies (
+    MessageID STRING(512) NOT NULL, -- Gmail sets a limit of 500 characters for Message-ID
+    ReportID STRING(36) NOT NULL, -- UUID
+    Time TIMESTAMP,
+    CONSTRAINT FK_ReplyReportID FOREIGN KEY (ReportID) REFERENCES SessionReports (ID),
+) PRIMARY KEY(MessageID, ReportID);
-- 
cgit mrf-deployment

