schema.sql

CREATE DATABASE IF NOT EXISTS glooker;
USE glooker;

CREATE TABLE IF NOT EXISTS reports (
  id           VARCHAR(36)  NOT NULL PRIMARY KEY,
  org          VARCHAR(255) NOT NULL,
  period_days  INT          NOT NULL,
  status       ENUM('pending','running','completed','failed','stopped') NOT NULL DEFAULT 'pending',
  error        TEXT         NULL,
  run_metadata JSON         NULL,
  trigger_kind VARCHAR(16)  NULL,
  triggered_by VARCHAR(255) NULL,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMP    NULL
);

CREATE TABLE IF NOT EXISTS report_skip_allowlist (
  github_login  VARCHAR(255) NOT NULL PRIMARY KEY,
  reason        TEXT         NOT NULL,
  added_by      VARCHAR(255) NULL,
  added_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT IGNORE INTO report_skip_allowlist (github_login, reason, added_by) VALUES
  ('oshpak', 'Private GitHub profile; not in org-visible members for non-mutual permissions', 'seed');

CREATE TABLE IF NOT EXISTS developer_stats (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  report_id       VARCHAR(36)    NOT NULL,
  github_login    VARCHAR(255)   NOT NULL,
  github_name     VARCHAR(255)   NULL,
  avatar_url      VARCHAR(500)   NULL,
  total_prs       INT            NOT NULL DEFAULT 0,
  total_commits   INT            NOT NULL DEFAULT 0,
  lines_added     INT            NOT NULL DEFAULT 0,
  lines_removed   INT            NOT NULL DEFAULT 0,
  avg_complexity  DECIMAL(4,2)   NULL,
  impact_score    DECIMAL(4,2)   NULL,
  pr_percentage   INT            NOT NULL DEFAULT 0,
  ai_percentage   INT            NOT NULL DEFAULT 0,
  total_jira_issues INT          NOT NULL DEFAULT 0,
  type_breakdown  JSON           NULL,
  active_repos    JSON           NULL,
  FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE,
  UNIQUE KEY uq_report_dev (report_id, github_login)
);

CREATE TABLE IF NOT EXISTS commit_analyses (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  report_id       VARCHAR(36)  NOT NULL,
  github_login    VARCHAR(255) NOT NULL,
  author_email    VARCHAR(255) NULL,
  repo            VARCHAR(255) NOT NULL,
  commit_sha      VARCHAR(40)  NOT NULL,
  pr_number       INT          NULL,
  pr_title        VARCHAR(500) NULL,
  commit_message  TEXT         NULL,
  lines_added     INT          NOT NULL DEFAULT 0,
  lines_removed   INT          NOT NULL DEFAULT 0,
  complexity      TINYINT      NULL,
  type            ENUM('feature','bug','refactor','infra','docs','test','other') NULL,
  impact_summary  TEXT         NULL,
  risk_level      ENUM('low','medium','high') NULL,
  ai_co_authored  TINYINT(1)   NOT NULL DEFAULT 0,
  ai_tool_name    VARCHAR(50)  NULL,
  maybe_ai        TINYINT(1)   NOT NULL DEFAULT 0,
  committed_at    TIMESTAMP    NULL,
  FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE,
  UNIQUE KEY uq_report_commit (report_id, commit_sha)
);

CREATE TABLE IF NOT EXISTS jira_issues (
  id                        INT AUTO_INCREMENT PRIMARY KEY,
  report_id                 VARCHAR(36)  NOT NULL,
  github_login              VARCHAR(255) NOT NULL,
  jira_account_id           VARCHAR(128) NULL,
  jira_email                VARCHAR(255) NULL,
  project_key               VARCHAR(50)  NOT NULL,
  issue_key                 VARCHAR(50)  NOT NULL,
  issue_type                VARCHAR(100) NULL,
  summary                   VARCHAR(500) NULL,
  description               TEXT         NULL,
  status                    VARCHAR(100) NULL,
  labels                    TEXT         NULL,
  story_points              DECIMAL(6,2) NULL,
  original_estimate_seconds INT          NULL,
  issue_url                 VARCHAR(500) NULL,
  created_at                TIMESTAMP    NULL,
  resolved_at               TIMESTAMP    NULL,
  complexity                TINYINT      NULL,
  type                      ENUM('feature','bug','refactor','infra','docs','test','other') NULL,
  impact_summary            TEXT         NULL,
  FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE,
  UNIQUE KEY uq_report_issue (report_id, issue_key)
);

CREATE TABLE IF NOT EXISTS user_mappings (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  org             VARCHAR(255) NOT NULL,
  github_login    VARCHAR(255) NOT NULL,
  jira_account_id VARCHAR(128) NOT NULL,
  jira_email      VARCHAR(255) NULL,
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_org_gh_login (org, github_login)
);

CREATE TABLE IF NOT EXISTS report_comparisons (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  report_id_a     VARCHAR(36)  NOT NULL,
  report_id_b     VARCHAR(36)  NOT NULL,
  highlights_json JSON         NOT NULL,
  generated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (report_id_a) REFERENCES reports(id) ON DELETE CASCADE,
  FOREIGN KEY (report_id_b) REFERENCES reports(id) ON DELETE CASCADE,
  UNIQUE KEY uq_report_pair (report_id_a, report_id_b)
);

CREATE TABLE IF NOT EXISTS developer_summaries (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  report_id       VARCHAR(36)  NOT NULL,
  github_login    VARCHAR(255) NOT NULL,
  summary_text    TEXT         NOT NULL,
  badges_json     JSON         NULL,
  generated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE,
  UNIQUE KEY uq_report_dev_summary (report_id, github_login)
);

CREATE TABLE IF NOT EXISTS teams (
  id          VARCHAR(36)  NOT NULL PRIMARY KEY,
  org         VARCHAR(255) NOT NULL,
  name        VARCHAR(255) NOT NULL,
  color       VARCHAR(7)   NOT NULL DEFAULT '#3B82F6',
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_org_team (org, name)
);

CREATE TABLE IF NOT EXISTS team_members (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  team_id     VARCHAR(36)  NOT NULL,
  github_login VARCHAR(255) NOT NULL,
  added_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  UNIQUE KEY uq_team_member (team_id, github_login)
);

CREATE TABLE IF NOT EXISTS jira_projects (
  id            VARCHAR(36)  NOT NULL PRIMARY KEY,
  org           VARCHAR(255) NOT NULL,
  project_key   VARCHAR(64)  NOT NULL,
  display_name  VARCHAR(255) NOT NULL,
  active_status VARCHAR(255) NOT NULL,
  middle_status VARCHAR(255) NULL,
  hierarchy     VARCHAR(32)  NOT NULL DEFAULT 'goal-initiative',
  position      INT          NOT NULL DEFAULT 0,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_org_project (org, project_key)
);

-- GLOOK-43: vulnerability tracking. Instants are VARCHAR(20) ISO strings (see spec: Timestamps).
CREATE TABLE IF NOT EXISTS vulnerability_repos (
  repo_id                  BIGINT       NOT NULL PRIMARY KEY,
  org                      VARCHAR(255) NOT NULL,
  full_name                VARCHAR(255) NOT NULL,
  team                     VARCHAR(255) NULL,
  service_tier             VARCHAR(255) NULL,
  codebase_type            VARCHAR(255) NULL,
  archived                 TINYINT      NOT NULL DEFAULT 0,
  dependabot_status        VARCHAR(16)  NOT NULL DEFAULT 'ok',
  dependabot_status_detail TEXT         NULL,
  first_seen_at            VARCHAR(20)  NOT NULL,
  last_seen_at             VARCHAR(20)  NOT NULL,
  KEY idx_vuln_repos_org (org)
);

CREATE TABLE IF NOT EXISTS vulnerability_alerts (
  repo_id               BIGINT        NOT NULL,
  number                INT           NOT NULL,
  org                   VARCHAR(255)  NOT NULL,
  html_url              VARCHAR(500)  NOT NULL,
  state                 VARCHAR(16)   NOT NULL,
  severity              VARCHAR(16)   NOT NULL,
  severity_changed_at   VARCHAR(20)   NULL,
  ghsa_id               VARCHAR(64)   NULL,
  cve_id                VARCHAR(64)   NULL,
  summary               TEXT          NULL,
  cvss_score            DECIMAL(3,1)  NULL,
  epss_percentage       DECIMAL(7,5)  NULL,
  advisory_withdrawn_at VARCHAR(20)   NULL,
  package_name          VARCHAR(255)  NULL,
  ecosystem             VARCHAR(64)   NULL,
  manifest_path         VARCHAR(500)  NULL,
  relationship          VARCHAR(32)   NULL,
  scope                 VARCHAR(32)   NULL,
  first_patched_version VARCHAR(128)  NULL,
  created_at            VARCHAR(20)   NOT NULL,
  gh_updated_at         VARCHAR(20)   NULL,
  fixed_at              VARCHAR(20)   NULL,
  dismissed_at          VARCHAR(20)   NULL,
  auto_dismissed_at     VARCHAR(20)   NULL,
  dismissed_reason      VARCHAR(64)   NULL,
  reopened_count        INT           NOT NULL DEFAULT 0,
  last_reopened_at      VARCHAR(20)   NULL,
  missing_since         VARCHAR(20)   NULL,
  withheld_since        VARCHAR(20)   NULL,
  first_seen_sync_id    INT           NOT NULL,
  last_seen_sync_id     INT           NOT NULL,
  PRIMARY KEY (repo_id, number),
  KEY idx_vuln_alerts_org (org)
);

CREATE TABLE IF NOT EXISTS vulnerability_syncs (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  org            VARCHAR(255) NOT NULL,
  trigger_kind   VARCHAR(16)  NOT NULL,
  triggered_by   VARCHAR(255) NULL,
  status         VARCHAR(16)  NOT NULL,
  started_at     VARCHAR(20)  NOT NULL,
  finished_at    VARCHAR(20)  NULL,
  alerts_fetched INT          NULL,
  repos_checked  INT          NULL,
  new_count      INT          NULL,
  resolved_count INT          NULL,
  reopened_count INT          NULL,
  missing_count  INT          NULL,
  issues         TEXT         NULL,
  KEY idx_vuln_syncs_org (org)
);

CREATE TABLE IF NOT EXISTS vulnerability_repo_snapshots (
  id                            INT AUTO_INCREMENT PRIMARY KEY,
  org                           VARCHAR(255) NOT NULL,
  source                        VARCHAR(16)  NOT NULL,
  sync_id                       INT          NULL,
  source_file                   VARCHAR(255) NULL,
  taken_on                      VARCHAR(10)  NOT NULL,
  measured_at                   VARCHAR(20)  NOT NULL,
  repo_id                       BIGINT       NOT NULL,
  full_name                     VARCHAR(255) NOT NULL,
  team_at_time                  VARCHAR(255) NULL,
  service_tier_at_time          VARCHAR(255) NULL,
  codebase_type_at_time         VARCHAR(255) NULL,
  archived                      TINYINT      NOT NULL DEFAULT 0,
  open_critical                 INT          NOT NULL,
  open_high                     INT          NULL,
  resolved_critical_since_start INT          NOT NULL,
  resolved_high_since_start     INT          NULL,
  KEY idx_vuln_snap_org_taken (org, taken_on)
);

CREATE TABLE IF NOT EXISTS release_notes (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  latest_commit_sha VARCHAR(40) NOT NULL,
  summary         TEXT         NOT NULL,
  commit_count    INT          NOT NULL DEFAULT 0,
  generated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_commit_sha (latest_commit_sha)
);

CREATE TABLE IF NOT EXISTS epic_summaries (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  epic_key        VARCHAR(20)  NOT NULL,
  org             VARCHAR(255) NOT NULL,
  summary_text    TEXT         NOT NULL,
  jira_resolved   INT          NOT NULL DEFAULT 0,
  jira_remaining  INT          NOT NULL DEFAULT 0,
  commit_count    INT          NOT NULL DEFAULT 0,
  lines_added     INT          NOT NULL DEFAULT 0,
  lines_removed   INT          NOT NULL DEFAULT 0,
  repos           TEXT         NULL,
  generated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_epic_org (epic_key, org)
);

CREATE TABLE IF NOT EXISTS untracked_summaries (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  team_name       VARCHAR(255) NOT NULL,
  org             VARCHAR(255) NOT NULL,
  groups_json     TEXT         NOT NULL,
  total_commits   INT          NOT NULL DEFAULT 0,
  generated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_team_org (team_name, org)
);

CREATE TABLE IF NOT EXISTS epic_stats (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  epic_key        VARCHAR(20)  NOT NULL,
  org             VARCHAR(255) NOT NULL,
  total_jiras     INT          NOT NULL DEFAULT 0,
  resolved_jiras  INT          NOT NULL DEFAULT 0,
  remaining_jiras INT          NOT NULL DEFAULT 0,
  commit_count    INT          NOT NULL DEFAULT 0,
  dev_count       INT          NOT NULL DEFAULT 0,
  lines_added     INT          NOT NULL DEFAULT 0,
  lines_removed   INT          NOT NULL DEFAULT 0,
  repos           TEXT         NULL,
  generated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_epic_stats_org (epic_key, org)
);

CREATE TABLE IF NOT EXISTS team_pulse_summaries (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  report_id       VARCHAR(36)  NOT NULL,
  team_name       VARCHAR(255) NOT NULL,
  org             VARCHAR(255) NOT NULL,
  summary_text    TEXT         NOT NULL,
  health_json     JSON         NOT NULL,
  projects        JSON         NULL,
  prompt_version  VARCHAR(50)  NOT NULL,
  generated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE,
  -- One row per (report, team). prompt_version acts as a content invalidator,
  -- not a row discriminator — the runtime modules use the same shape. Bumping
  -- the version forces the SELECT in getTeamPulse to miss-and-replace this row.
  UNIQUE KEY uq_report_team_pulse (report_id, team_name)
);

CREATE INDEX idx_devstats_login ON developer_stats(github_login);
CREATE INDEX idx_reports_org_status_created ON reports(org, status, created_at);
CREATE INDEX idx_ca_login_report ON commit_analyses(github_login, report_id, commit_sha, complexity);
CREATE INDEX idx_ca_report_committed ON commit_analyses(report_id, committed_at);
CREATE INDEX idx_ca_sha_report ON commit_analyses(commit_sha, report_id, complexity);