Open Source Monorail

appengine/monorail/sql/tracker.sql

Index: appengine/monorail/sql/tracker.sql
diff --git a/appengine/monorail/sql/tracker.sql b/appengine/monorail/sql/tracker.sql
new file mode 100644
index 0000000000000000000000000000000000000000..27c9e444106fbfa5f77822b154be407af1065271
--- /dev/null
+++ b/appengine/monorail/sql/tracker.sql
@@ -0,0 +1,646 @@
+-- Copyright 2016 The Chromium Authors. All Rights Reserved.
+--
+-- Use of this source code is governed by a BSD-style
+-- license that can be found in the LICENSE file or at
+-- https://developers.google.com/open-source/licenses/bsd
+
+
+-- Create issue-realted tables in monorail db.
+
+
+CREATE TABLE StatusDef (
+  id INT NOT NULL AUTO_INCREMENT,
+  project_id SMALLINT UNSIGNED NOT NULL,
+  status VARCHAR(80) BINARY NOT NULL,
+  rank SMALLINT UNSIGNED,
+  means_open BOOLEAN,
+  docstring TEXT,
+  deprecated BOOLEAN DEFAULT FALSE,
+
+  PRIMARY KEY (id),
+  UNIQUE KEY (project_id, status),
+  FOREIGN KEY (project_id) REFERENCES Project(project_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE ComponentDef (
+  id INT NOT NULL AUTO_INCREMENT,
+  project_id SMALLINT UNSIGNED NOT NULL,
+
+  -- Note: parent components have paths that are prefixes of child components.
+  path VARCHAR(255) BINARY NOT NULL,
+  docstring TEXT,
+  deprecated BOOLEAN DEFAULT FALSE,
+  created INT,
+  creator_id INT UNSIGNED,
+  modified INT,
+  modifier_id INT UNSIGNED,
+
+  PRIMARY KEY (id),
+  UNIQUE KEY (project_id, path),
+  FOREIGN KEY (project_id) REFERENCES Project(project_id),
+  FOREIGN KEY (creator_id) REFERENCES User(user_id),
+  FOREIGN KEY (modifier_id) REFERENCES User(user_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Component2Admin (
+  component_id INT NOT NULL,
+  admin_id INT UNSIGNED NOT NULL,
+
+  PRIMARY KEY (component_id, admin_id),
+
+  FOREIGN KEY (component_id) REFERENCES ComponentDef(id),
+  FOREIGN KEY (admin_id) REFERENCES User(user_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Component2Cc (
+  component_id INT NOT NULL,
+  cc_id INT UNSIGNED NOT NULL,
+
+  PRIMARY KEY (component_id, cc_id),
+
+  FOREIGN KEY (component_id) REFERENCES ComponentDef(id),
+  FOREIGN KEY (cc_id) REFERENCES User(user_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE LabelDef (
+  id INT NOT NULL AUTO_INCREMENT,
+  project_id SMALLINT UNSIGNED NOT NULL,
+  label VARCHAR(80) BINARY NOT NULL,
+  rank SMALLINT UNSIGNED,
+  docstring TEXT,
+  deprecated BOOLEAN DEFAULT FALSE,
+
+  PRIMARY KEY (id),
+  UNIQUE KEY (project_id, label),
+  FOREIGN KEY (project_id) REFERENCES Project(project_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE FieldDef (
+  id INT NOT NULL AUTO_INCREMENT,
+  project_id SMALLINT UNSIGNED NOT NULL,
+  rank SMALLINT UNSIGNED,
+
+  field_name VARCHAR(80) BINARY NOT NULL,
+  -- TODO(jrobbins): more types
+  field_type ENUM ('enum_type', 'int_type', 'str_type', 'user_type') NOT NULL,
+  applicable_type VARCHAR(80),   -- No value means: offered for all issue types
+  applicable_predicate TEXT,   -- No value means: TRUE
+  is_required BOOLEAN,  -- true means required if applicable
+  is_multivalued BOOLEAN,
+  -- TODO(jrobbins): access controls: restrict, grant
+  -- Validation for int_type fields
+  min_value INT,
+  max_value INT,
+  -- Validation for str_type fields
+  regex VARCHAR(80),
+  -- Validation for user_type fields
+  needs_member BOOLEAN,  -- User value can only be set to users who are members
+  needs_perm VARCHAR(80),  -- User value can only be set to users w/ that perm
+  grants_perm VARCHAR(80),  -- User named in this field gains this perm in the issue
+  -- notification options for user_type fields
+  notify_on ENUM ('never', 'any_comment') DEFAULT 'never' NOT NULL,
+
+  -- TODO(jrobbins): default value
+  -- TODO(jrobbins): deprecated boolean?
+  docstring TEXT,
+  is_deleted BOOLEAN,  -- If true, reap this field def after all values reaped.
+
+  PRIMARY KEY (id),
+  UNIQUE KEY (project_id, field_name),
+  FOREIGN KEY (project_id) REFERENCES Project(project_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE FieldDef2Admin (
+  field_id INT NOT NULL,
+  admin_id INT UNSIGNED NOT NULL,
+
+  PRIMARY KEY (field_id, admin_id),
+  FOREIGN KEY (field_id) REFERENCES FieldDef(id),
+  FOREIGN KEY (admin_id) REFERENCES User(user_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Issue (
+  id INT NOT NULL AUTO_INCREMENT,
+  shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
+  project_id SMALLINT UNSIGNED NOT NULL,
+  local_id INT NOT NULL,
+
+  reporter_id INT UNSIGNED NOT NULL,
+  owner_id INT UNSIGNED,
+  status_id INT,
+
+  -- These are each timestamps in seconds since the epoch.
+  modified INT NOT NULL,
+  opened INT,
+  closed INT,
+
+  derived_owner_id INT UNSIGNED,
+  derived_status_id INT,
+
+  deleted BOOLEAN,
+
+  -- These are denormalized fields that should be updated when child
+  -- records are added or removed for stars or attachments.  If they
+  -- get out of sync, they can be updated via an UPDATE ... SELECT statement.
+  star_count INT DEFAULT 0,
+  attachment_count INT DEFAULT 0,
+
+  is_spam BOOLEAN DEFAULT FALSE,
+
+  PRIMARY KEY(id),
+  UNIQUE KEY (project_id, local_id),
+  INDEX (shard, status_id),
+  INDEX (shard, project_id),
+
+  FOREIGN KEY (project_id) REFERENCES Project(project_id),
+  FOREIGN KEY (reporter_id) REFERENCES User(user_id),
+  FOREIGN KEY (owner_id) REFERENCES User(user_id),
+  FOREIGN KEY (status_id) REFERENCES StatusDef(id),
+  FOREIGN KEY (derived_owner_id) REFERENCES User(user_id)
+) ENGINE=INNODB;
+
+
+-- This is a parallel table to the Issue table because we don't want
+-- any very wide columns in the Issue table that would slow it down.
+CREATE TABLE IssueSummary (
+  issue_id INT NOT NULL,
+  summary TEXT,
+
+  PRIMARY KEY (issue_id),
+  FOREIGN KEY (issue_id) REFERENCES Issue(id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Issue2Component (
+  issue_id INT NOT NULL,
+  issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
+  component_id INT NOT NULL,
+  derived BOOLEAN DEFAULT FALSE,
+
+  PRIMARY KEY (issue_id, component_id, derived),
+  INDEX (component_id, issue_shard),
+
+  FOREIGN KEY (issue_id) REFERENCES Issue(id),
+  FOREIGN KEY (component_id) REFERENCES ComponentDef(id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Issue2Label (
+  issue_id INT NOT NULL,
+  issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
+  label_id INT NOT NULL,
+  derived BOOLEAN DEFAULT FALSE,
+
+  PRIMARY KEY (issue_id, label_id, derived),
+  INDEX (label_id, issue_shard),
+
+  FOREIGN KEY (issue_id) REFERENCES Issue(id),
+  FOREIGN KEY (label_id) REFERENCES LabelDef(id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Issue2FieldValue (
+  issue_id INT NOT NULL,
+  issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
+  field_id INT NOT NULL,
+
+  int_value INT,
+  str_value VARCHAR(1024),
+  user_id INT UNSIGNED,
+
+  derived BOOLEAN DEFAULT FALSE,
+
+  INDEX (issue_id, field_id),
+  INDEX (field_id, issue_shard, int_value),
+  INDEX (field_id, issue_shard, str_value(255)),
+  INDEX (field_id, issue_shard, user_id),
+
+  FOREIGN KEY (issue_id) REFERENCES Issue(id),
+  FOREIGN KEY (field_id) REFERENCES FieldDef(id),
+  FOREIGN KEY (user_id) REFERENCES User(user_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Issue2Cc (
+  issue_id INT NOT NULL,
+  issue_shard SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
+  cc_id INT UNSIGNED NOT NULL,
+  derived BOOLEAN DEFAULT FALSE,
+
+  PRIMARY KEY (issue_id, cc_id),
+  INDEX (cc_id, issue_shard),
+
+  FOREIGN KEY (issue_id) REFERENCES Issue(id),
+  FOREIGN KEY (cc_id) REFERENCES User(user_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Issue2Notify (
+  issue_id INT NOT NULL,
+  email VARCHAR(80) NOT NULL,
+
+  PRIMARY KEY (issue_id, email),
+
+  FOREIGN KEY (issue_id) REFERENCES Issue(id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE IssueStar (
+  issue_id INT NOT NULL,
+  user_id INT UNSIGNED NOT NULL,
+
+  PRIMARY KEY (issue_id, user_id),
+  INDEX (user_id),
+  FOREIGN KEY (issue_id) REFERENCES Issue(id),
+  FOREIGN KEY (user_id) REFERENCES User(user_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE IssueRelation (
+  issue_id INT NOT NULL,
+  dst_issue_id INT NOT NULL,
+
+  -- Read as: src issue is blocked on dst issue.
+  kind ENUM ('blockedon', 'mergedinto') NOT NULL,
+
+  PRIMARY KEY (issue_id, dst_issue_id, kind),
+  INDEX (issue_id),
+  INDEX (dst_issue_id),
+  FOREIGN KEY (issue_id) REFERENCES Issue(id),
+  FOREIGN KEY (dst_issue_id) REFERENCES Issue(id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE DanglingIssueRelation (
+  issue_id INT NOT NULL,
+  dst_issue_project VARCHAR(80),
+  dst_issue_local_id INT,
+
+  -- This table uses 'blocking' so that it can guarantee the src issue
+  -- always exists, while the dst issue is always the dangling one.
+  kind ENUM ('blockedon', 'blocking', 'mergedinto') NOT NULL,
+
+  PRIMARY KEY (issue_id, dst_issue_project, dst_issue_local_id, kind),
+  INDEX (issue_id),
+  FOREIGN KEY (issue_id) REFERENCES Issue(id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Comment (
+  id INT NOT NULL AUTO_INCREMENT,
+  issue_id INT NOT NULL,
+  created INT NOT NULL,
+  project_id SMALLINT UNSIGNED NOT NULL,
+
+  commenter_id INT UNSIGNED NOT NULL,
+  content TEXT,
+  inbound_message TEXT,
+
+  was_escaped BOOLEAN DEFAULT FALSE,
+  deleted_by INT UNSIGNED,
+  is_spam BOOLEAN DEFAULT FALSE,
+
+  PRIMARY KEY(id),
+  INDEX (is_spam, project_id, created),
+  INDEX (commenter_id, created),
+
+  FOREIGN KEY (project_id) REFERENCES Project(project_id),
+  FOREIGN KEY (issue_id) REFERENCES Issue(id),
+  FOREIGN KEY (commenter_id) REFERENCES User(user_id),
+  FOREIGN KEY (deleted_by) REFERENCES User(user_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Attachment (
+  id INT NOT NULL AUTO_INCREMENT,
+
+  issue_id INT NOT NULL,
+  comment_id INT,
+
+  filename VARCHAR(255) NOT NULL,
+  filesize INT NOT NULL,
+  mimetype VARCHAR(255) NOT NULL,
+  deleted BOOLEAN,
+  gcs_object_id VARCHAR(1024) NOT NULL,
+
+  PRIMARY KEY (id),
+  INDEX (issue_id),
+  INDEX (comment_id),
+  FOREIGN KEY (issue_id) REFERENCES Issue(id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE IssueUpdate (
+  id INT NOT NULL AUTO_INCREMENT,
+  issue_id INT NOT NULL,
+  comment_id INT,
+
+  field ENUM (
+  'summary', 'status', 'owner', 'cc', 'labels', 'blockedon', 'blocking', 'mergedinto',
+  'project', 'components', 'custom', 'is_spam' ) NOT NULL,
+  old_value TEXT,
+  new_value TEXT,
+  added_user_id INT UNSIGNED,
+  removed_user_id INT UNSIGNED,
+  custom_field_name VARCHAR(255),
+  is_spam BOOLEAN DEFAULT FALSE,
+
+  PRIMARY KEY (id),
+  INDEX (issue_id),
+  INDEX (comment_id),
+  FOREIGN KEY (issue_id) REFERENCES Issue(id)
+  -- FOREIGN KEY (added_user_id) REFERENCES User(user_id),
+  -- FOREIGN KEY (removed_user_id) REFERENCES User(user_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE IssueFormerLocations (
+  issue_id INT NOT NULL,
+  project_id SMALLINT UNSIGNED NOT NULL,
+  local_id INT NOT NULL,
+
+  INDEX (issue_id),
+  UNIQUE KEY (project_id, local_id),
+  FOREIGN KEY (issue_id) REFERENCES Issue(id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Template (
+  id INT NOT NULL AUTO_INCREMENT,
+  project_id SMALLINT UNSIGNED NOT NULL,
+  name VARCHAR(255) BINARY NOT NULL,
+
+  content TEXT,
+  summary TEXT,
+  summary_must_be_edited BOOLEAN,
+  owner_id INT UNSIGNED,
+  status VARCHAR(255),
+  members_only BOOLEAN,
+  owner_defaults_to_member BOOLEAN,
+  component_required BOOLEAN DEFAULT FALSE,
+
+  PRIMARY KEY (id),
+  UNIQUE KEY (project_id, name),
+  FOREIGN KEY (project_id) REFERENCES Project(project_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Template2Label (
+  template_id INT NOT NULL,
+  label VARCHAR(255) NOT NULL,
+
+  PRIMARY KEY (template_id, label),
+  FOREIGN KEY (template_id) REFERENCES Template(id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Template2Admin (
+  template_id INT NOT NULL,
+  admin_id INT UNSIGNED NOT NULL,
+
+  PRIMARY KEY (template_id, admin_id),
+  FOREIGN KEY (template_id) REFERENCES Template(id),
+  FOREIGN KEY (admin_id) REFERENCES User(user_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Template2FieldValue (
+  template_id INT NOT NULL,
+  field_id INT NOT NULL,
+
+  int_value INT,
+  str_value VARCHAR(1024),
+  user_id INT UNSIGNED,
+
+  INDEX (template_id, field_id),
+
+  FOREIGN KEY (template_id) REFERENCES Template(id),
+  FOREIGN KEY (field_id) REFERENCES FieldDef(id),
+  FOREIGN KEY (user_id) REFERENCES User(user_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE Template2Component (
+  template_id INT NOT NULL,
+  component_id INT NOT NULL,
+
+  PRIMARY KEY (template_id, component_id),
+
+  FOREIGN KEY (template_id) REFERENCES Template(id),
+  FOREIGN KEY (component_id) REFERENCES ComponentDef(id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE ProjectIssueConfig (
+  project_id SMALLINT UNSIGNED NOT NULL,
+
+  statuses_offer_merge VARCHAR(255) NOT NULL,
+  exclusive_label_prefixes VARCHAR(255) NOT NULL,
+  default_template_for_developers INT NOT NULL,
+  default_template_for_users INT NOT NULL,
+
+  default_col_spec TEXT,
+  default_sort_spec TEXT,
+  default_x_attr TEXT,
+  default_y_attr TEXT,
+
+  custom_issue_entry_url TEXT,
+
+  PRIMARY KEY (project_id),
+  FOREIGN KEY (project_id) REFERENCES Project(project_id)
+) ENGINE=INNODB;
+
+
+CREATE TABLE FilterRule (
+  project_id SMALLINT UNSIGNED NOT NULL,
+  rank SMALLINT UNSIGNED,
+
+  -- TODO: or should this be broken down into structured fields?
+  predicate TEXT NOT NULL,
+  -- TODO: or should this be broken down into structured fields?
+  consequence TEXT NOT NULL,
+
+  INDEX (project_id),
+  FOREIGN KEY (project_id) REFERENCES Project(project_id)
+) ENGINE=INNODB;
+
+
+-- Each row in this table indicates an issue that needs to be reindexed
+-- in the GAE fulltext index by our batch indexing cron job.
+CREATE TABLE ReindexQueue (
+  issue_id INT NOT NULL,
+  created TIMESTAMP,
+
+  PRIMARY KEY (issue_id),
+  FOREIGN KEY (issue_id) REFERENCES Issue(id)
+) ENGINE=INNODB;
+
+
+-- This holds counters with the highest issue local_id that is
+-- already used in each project.  Clients should atomically increment
+-- the value for current project and then use the new counter value
+-- when creating an issue.
+CREATE TABLE LocalIDCounter (
+  project_id SMALLINT UNSIGNED NOT NULL,
+  used_local_id INT NOT NULL,
+  used_spam_id INT NOT NULL,
+
+  PRIMARY KEY (project_id),
+  FOREIGN KEY (project_id) REFERENCES Project(project_id)
+) ENGINE=INNODB;
+
+
+-- This is a saved query.  It can be configured by a project owner to
+-- be used by all visitors to that project.  Or, it can be a a
+-- personal saved query that appears on a user's "Saved queries" page
+-- and executes in the scope of one or more projects.
+CREATE TABLE SavedQuery (
+  id INT NOT NULL AUTO_INCREMENT,
+  name VARCHAR(80) NOT NULL,
+
+  -- For now, we only allow saved queries to be based off ane of the built-in
+  -- query scopes, and those can never be deleted, so there can be no nesting,
+  -- dangling references, and thus no need for cascading deletes.
+  base_query_id INT,
+  query TEXT NOT NULL,
+
+  PRIMARY KEY (id)
+) ENGINE=INNODB;
+
+
+-- Rows for built-in queries.  These are in the database soley so that
+-- foreign key constraints are satisfied. These rows ar never read or updated.
+INSERT IGNORE INTO SavedQuery VALUES
+  (1, 'All issues', 0, ''),
+  (2, 'Open issues', 0, 'is:open'),
+  (3, 'Open and owned by me', 0, 'is:open owner:me'),
+  (4, 'Open and reported by me', 0, 'is:open reporter:me'),
+  (5, 'Open and starred by me', 0, 'is:open is:starred'),
+  (6, 'New issues', 0, 'status:new'),
+  (7, 'Issues to verify', 0, 'status=fixed,done'),
+  (8, 'Open with comment by me', 0, 'is:open commentby:me');
+
+-- The sole purpose of this statement is to force user defined saved queries
+-- to have IDs greater than 100 so that 1-100 are reserved for built-ins.
+INSERT IGNORE INTO SavedQuery VALUES (100, '', 0, '');
+
+
+-- User personal queries default to executing in the context of the
+-- project where they were created, but the user can edit them to make
+-- them into cross-project queries.  Project saved queries always
+-- implicitly execute in the context of a project.
+CREATE TABLE SavedQueryExecutesInProject (
+  query_id INT NOT NULL,
+  project_id SMALLINT UNSIGNED NOT NULL,
+
+  PRIMARY KEY (query_id, project_id),
+  INDEX (project_id),
+  FOREIGN KEY (project_id) REFERENCES Project(project_id),
+  FOREIGN KEY (query_id) REFERENCES SavedQuery(id)
+) ENGINE=INNODB;
+
+
+-- These are the queries edited by the project owner on the project
+-- admin pages.
+CREATE TABLE Project2SavedQuery (
+  project_id SMALLINT UNSIGNED NOT NULL,
+  rank SMALLINT UNSIGNED NOT NULL,
+  query_id INT NOT NULL,
+
+  -- TODO(jrobbins): visibility: owners, committers, contributors, anyone
+
+  PRIMARY KEY (project_id, rank),
+  FOREIGN KEY (project_id) REFERENCES Project(project_id),
+  FOREIGN KEY (query_id) REFERENCES SavedQuery(id)
+) ENGINE=INNODB;
+
+
+-- These are personal saved queries.
+CREATE TABLE User2SavedQuery (
+  user_id INT UNSIGNED NOT NULL,
+  rank SMALLINT UNSIGNED NOT NULL,
+  query_id INT NOT NULL,
+
+  -- TODO(jrobbins): daily and weekly digests, and the ability to have
+  -- certain subscriptions go to username+SOMETHING@example.com.
+  subscription_mode ENUM ('noemail', 'immediate') DEFAULT 'noemail' NOT NULL,
+
+  PRIMARY KEY (user_id, rank),
+  FOREIGN KEY (user_id) REFERENCES User(user_id),
+  FOREIGN KEY (query_id) REFERENCES SavedQuery(id)
+) ENGINE=INNODB;
+
+
+-- Created whenever a user reports an issue or comment as spam.
+-- Note this is distinct from a SpamVerdict, which is issued by
+-- the system rather than a human user.
+CREATE TABLE SpamReport (
+  -- when this report was generated
+  created TIMESTAMP NOT NULL,
+  -- when the reported content was generated
+  -- TODO(jrobbins): needs default current_time in MySQL 5.7.
+  content_created TIMESTAMP NOT NULL,
+  -- id of the reporting user
+  user_id INT UNSIGNED NOT NULL,
+  -- id of the reported user
+  reported_user_id INT UNSIGNED NOT NULL,
+  -- either this or issue_id must be set
+  comment_id INT,
+  -- either this or comment_id must be set
+  issue_id INT,
+
+  INDEX (issue_id),
+  INDEX (comment_id),
+  FOREIGN KEY (issue_id) REFERENCES Issue(id),
+  FOREIGN KEY (comment_id) REFERENCES Comment(id)
+) ENGINE=INNODB;
+
+
+-- Any time a human or the system sets is_spam to true,
+-- or changes it from true to false, we want to have a
+-- record of who did it and why.
+CREATE TABLE SpamVerdict (
+  -- when this verdict was generated
+  created TIMESTAMP NOT NULL,
+
+  -- id of the reporting user, may be null if it was
+  -- an automatic classification.
+  user_id INT UNSIGNED,
+
+  -- id of the containing project.
+  project_id INT NOT NULL,
+
+  -- either this or issue_id must be set.
+  comment_id INT,
+
+  -- either this or comment_id must be set.
+  issue_id INT,
+
+  -- If the classifier issued the verdict, this should be set.
+  classifier_confidence FLOAT,
+
+  -- This should reflect the new is_spam value that was applied
+  -- by this verdict, not the value it had prior.
+  is_spam BOOLEAN NOT NULL,
+
+  -- owner: a project owner marked it as spam
+  -- threshhold: number of SpamReports from non-members was exceeded.
+  -- classifier: the automatic classifier reports it as spam.
+  reason ENUM ("manual", "threshold", "classifier") NOT NULL,
+
+  overruled BOOL NOT NULL,
+
+  INDEX (issue_id),
+  INDEX (comment_id),
+  INDEX (classifier_confidence),
+  FOREIGN KEY (issue_id) REFERENCES Issue(id),
+  FOREIGN KEY (comment_id) REFERENCES Comment(id)
+
+) ENGINE=INNODB;