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.

1 files changed, 11 insertions, 2 deletions

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);