Database schema for repo statistics

stats_tables.sql

CREATE TABLE issues (
    recording_date TEXT NOT NULL DEFAULT CURRENT_DATE,
    gh_num INTEGER NOT NULL CHECK(gh_num >= 1),
    url TEXT NOT NULL,
    title TEXT NOT NULL,
    created TEXT NOT NULL,  -- Date of issue creation
    by_team INT NOT NULL DEFAULT 0 CHECK(upvotes >= 0),
    assignee TEXT NULL,  -- Who the issue is assigned to
    last_OP_comment TEXT NULL,  -- When the last comment from the OP was
    last_team_comment TEXT NULL,  -- When the last time a "member" commented
    type_ TEXT NULL CHECK(type_ IN ('bug', 'enhancement', 'code health')),
    needs TEXT NULL CHECK(needs IN ('classify', 'triage', 'decision', 'spike', 'spec', 'PR', 'experimenting')),
    priority INT CHECK(priority >= 0), -- NULL if not specified.
    reason TEXT NULL CHECK(reason IN ('regression', 'preexisting', 'external', NULL)),
    partner_ask INT NOT NULL DEFAULT 0 CHECK(partner_ask = 0 OR partner_ask = 1),
    upvotes INT NOT NULL DEFAULT 0 CHECK(upvotes >= 0),
    UNIQUE (recording_date, gh_num)  -- Don't record an issue twice in the same day.
);


INSERT INTO issues (gh_num, title, assigned, type, needs, ack, priority, regression, partner_ask, upvotes)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT (gh_num, recording_date) DO NOTHING;

/* Bug count for the day. */
SELECT COUNT(*) FROM issues WHERE recording_date = date('now') AND type = 'bug';
/* P0s. Tweak for P1s. */
SELECT gh_num, title FROM issues WHERE recording_date = date('now') AND priority = 0;
/* Regressions. Tweak for partner asks. */
SELECT gh_num, title FROM issues WHERE recording_date = date('now') AND regression = 1;
/* Top requested bugs. Tweak for enhancements. */
SELECT gh_num, title FROM issues WHERE recording_date = date('now') AND type = 'bug' ORDER BY upvotes DESC LIMIT 5;
/* ACK SLA of 3 days. */
SELECT gh_num, created FROM issues WHERE by_team = 0 AND last_team_comment IS NULL AND created < datetime('now', '-3 day');

/* XXX These could potentially be done faster as a GH Action.
    Query to check for NULL 'type_' that aren't 'classify'.
    Query to check for NULL 'needs'.
*/