Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Database schema for repo statistics
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'.
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment