internal/store/activity.go

  1package store
  2
  3import (
  4	"database/sql"
  5	"errors"
  6)
  7
  8// UserIDByVerifiedEmail resolves a commit author email to an account, only
  9// through addresses the account has verified — the same trust rule as
 10// signature attribution.
 11func (s *Store) UserIDByVerifiedEmail(address string) (int64, bool) {
 12	var id int64
 13	err := s.DB.QueryRow(
 14		"SELECT user_id FROM emails WHERE address = ? AND verified_at IS NOT NULL", address).Scan(&id)
 15	if errors.Is(err, sql.ErrNoRows) || err != nil {
 16		return 0, false
 17	}
 18	return id, true
 19}
 20
 21// UsernameByVerifiedEmail resolves a commit author address to the account
 22// that has proven it, so the forge can show its own name for a person
 23// rather than whatever git config happened to be set.
 24func (s *Store) UsernameByVerifiedEmail(address string) (string, bool) {
 25	var name string
 26	err := s.DB.QueryRow(`
 27		SELECT u.username FROM emails e JOIN users u ON u.id = e.user_id
 28		WHERE e.address = ? AND e.verified_at IS NOT NULL`, address).Scan(&name)
 29	if err != nil {
 30		return "", false
 31	}
 32	return name, true
 33}
 34
 35// RecordCommitActivity is idempotent per (repo, sha); it reports whether
 36// this call recorded a new row.
 37func (s *Store) RecordCommitActivity(repoID int64, sha string, userID int64, day string) bool {
 38	res, err := s.DB.Exec(
 39		"INSERT INTO commit_activity (repo_id, sha, user_id, day) VALUES (?, ?, ?, ?) ON CONFLICT DO NOTHING",
 40		repoID, sha, userID, day)
 41	if err != nil {
 42		return false
 43	}
 44	n, _ := res.RowsAffected()
 45	return n > 0
 46}
 47
 48// ActivityByDay aggregates a user's activity per day since the given day:
 49// commits landed on default branches plus everything the events table
 50// attributes to them (issues, MRs, comments, releases, pushes).
 51func (s *Store) ActivityByDay(userID int64, sinceDay string) (map[string]int, error) {
 52	rows, err := s.DB.Query(`
 53		SELECT day, COUNT(*) FROM (
 54			SELECT day FROM commit_activity WHERE user_id = ?1 AND day >= ?2
 55			UNION ALL
 56			SELECT date(created_at) AS day FROM events
 57			WHERE actor_id = ?1 AND date(created_at) >= ?2 AND kind <> 'push'
 58		) GROUP BY day`, userID, sinceDay)
 59	if err != nil {
 60		return nil, err
 61	}
 62	defer rows.Close()
 63	out := map[string]int{}
 64	for rows.Next() {
 65		var day string
 66		var n int
 67		if err := rows.Scan(&day, &n); err != nil {
 68			return nil, err
 69		}
 70		out[day] = n
 71	}
 72	return out, rows.Err()
 73}
 74
 75// OrgActivityByDay aggregates activity across an org's repositories.
 76func (s *Store) OrgActivityByDay(orgID int64, sinceDay string) (map[string]int, error) {
 77	rows, err := s.DB.Query(`
 78		SELECT day, COUNT(*) FROM (
 79			SELECT ca.day FROM commit_activity ca
 80			JOIN repos r ON r.id = ca.repo_id
 81			WHERE r.owner_kind = 'org' AND r.owner_id = ?1 AND ca.day >= ?2
 82			UNION ALL
 83			SELECT date(e.created_at) AS day FROM events e
 84			JOIN repos r ON r.id = e.repo_id
 85			WHERE r.owner_kind = 'org' AND r.owner_id = ?1
 86			  AND date(e.created_at) >= ?2 AND e.kind <> 'push'
 87		) GROUP BY day`, orgID, sinceDay)
 88	if err != nil {
 89		return nil, err
 90	}
 91	defer rows.Close()
 92	out := map[string]int{}
 93	for rows.Next() {
 94		var day string
 95		var n int
 96		if err := rows.Scan(&day, &n); err != nil {
 97			return nil, err
 98		}
 99		out[day] = n
100	}
101	return out, rows.Err()
102}