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// RecordCommitActivity is idempotent per (repo, sha); it reports whether
22// this call recorded a new row.
23func (s *Store) RecordCommitActivity(repoID int64, sha string, userID int64, day string) bool {
24	res, err := s.DB.Exec(
25		"INSERT INTO commit_activity (repo_id, sha, user_id, day) VALUES (?, ?, ?, ?) ON CONFLICT DO NOTHING",
26		repoID, sha, userID, day)
27	if err != nil {
28		return false
29	}
30	n, _ := res.RowsAffected()
31	return n > 0
32}
33
34// ActivityByDay aggregates a user's activity per day since the given day:
35// commits landed on default branches plus everything the events table
36// attributes to them (issues, MRs, comments, releases, pushes).
37func (s *Store) ActivityByDay(userID int64, sinceDay string) (map[string]int, error) {
38	rows, err := s.DB.Query(`
39		SELECT day, COUNT(*) FROM (
40			SELECT day FROM commit_activity WHERE user_id = ?1 AND day >= ?2
41			UNION ALL
42			SELECT date(created_at) AS day FROM events
43			WHERE actor_id = ?1 AND date(created_at) >= ?2 AND kind <> 'push'
44		) GROUP BY day`, userID, sinceDay)
45	if err != nil {
46		return nil, err
47	}
48	defer rows.Close()
49	out := map[string]int{}
50	for rows.Next() {
51		var day string
52		var n int
53		if err := rows.Scan(&day, &n); err != nil {
54			return nil, err
55		}
56		out[day] = n
57	}
58	return out, rows.Err()
59}
60
61// OrgActivityByDay aggregates activity across an org's repositories.
62func (s *Store) OrgActivityByDay(orgID int64, sinceDay string) (map[string]int, error) {
63	rows, err := s.DB.Query(`
64		SELECT day, COUNT(*) FROM (
65			SELECT ca.day FROM commit_activity ca
66			JOIN repos r ON r.id = ca.repo_id
67			WHERE r.owner_kind = 'org' AND r.owner_id = ?1 AND ca.day >= ?2
68			UNION ALL
69			SELECT date(e.created_at) AS day FROM events e
70			JOIN repos r ON r.id = e.repo_id
71			WHERE r.owner_kind = 'org' AND r.owner_id = ?1
72			  AND date(e.created_at) >= ?2 AND e.kind <> 'push'
73		) GROUP BY day`, orgID, sinceDay)
74	if err != nil {
75		return nil, err
76	}
77	defer rows.Close()
78	out := map[string]int{}
79	for rows.Next() {
80		var day string
81		var n int
82		if err := rows.Scan(&day, &n); err != nil {
83			return nil, err
84		}
85		out[day] = n
86	}
87	return out, rows.Err()
88}