shithub Public

Watch 1 Fork 0 Star 0
MySQL · 7960 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2 --
3 -- Admin-only query surface for /admin/*. Non-admin handlers should
4 -- use the per-domain packages (usersdb, reposdb, …) directly. This
5 -- file collects the cross-cutting reads + writes the admin UI needs:
6 -- * dashboard counts
7 -- * user/repo listings with admin-only filters (suspended, deleted)
8 -- * site-admin flag toggle
9 -- * transactional_email_log read/write
10 --
11 -- Queries that already exist in their domain package (e.g.
12 -- users.SuspendUser) are reused directly — no duplication here.
13
14 -- ─── dashboard counts ────────────────────────────────────────────
15
16 -- name: CountActiveUsers :one
17 SELECT COUNT(*) FROM users WHERE deleted_at IS NULL;
18
19 -- name: CountSuspendedUsers :one
20 SELECT COUNT(*) FROM users WHERE suspended_at IS NOT NULL AND deleted_at IS NULL;
21
22 -- name: CountSiteAdmins :one
23 SELECT COUNT(*) FROM users WHERE is_site_admin = true AND deleted_at IS NULL;
24
25 -- name: CountActiveRepos :one
26 SELECT COUNT(*) FROM repos WHERE deleted_at IS NULL;
27
28 -- name: CountActiveOrgs :one
29 SELECT COUNT(*) FROM orgs WHERE deleted_at IS NULL;
30
31 -- name: CountJobsByStatus :many
32 -- Status is derived from the timestamp columns since the jobs table
33 -- doesn't carry an enum: completed > failed > running > queued.
34 SELECT
35 CASE
36 WHEN completed_at IS NOT NULL THEN 'completed'
37 WHEN failed_at IS NOT NULL THEN 'failed'
38 WHEN locked_at IS NOT NULL THEN 'running'
39 ELSE 'queued'
40 END AS status,
41 COUNT(*)::bigint AS n
42 FROM jobs
43 GROUP BY 1;
44
45 -- ─── users list ──────────────────────────────────────────────────
46
47 -- name: ListUsersForAdmin :many
48 -- Filters: optional username prefix, optional suspended/deleted-only.
49 -- All filters use sqlc.narg so the empty case is "no filter".
50 SELECT id, username, display_name, primary_email_id,
51 suspended_at, deleted_at, last_login_at, is_site_admin,
52 created_at
53 FROM users
54 WHERE
55 (sqlc.narg(username_prefix)::text IS NULL OR username::text ILIKE sqlc.narg(username_prefix)::text || '%')
56 AND (sqlc.narg(suspended_only)::bool IS NOT TRUE OR suspended_at IS NOT NULL)
57 AND (sqlc.narg(deleted_only)::bool IS NOT TRUE OR deleted_at IS NOT NULL)
58 ORDER BY id DESC
59 LIMIT $1 OFFSET $2;
60
61 -- name: SetUserSiteAdmin :exec
62 UPDATE users
63 SET is_site_admin = $2,
64 updated_at = now()
65 WHERE id = $1;
66
67 -- ─── repos list ──────────────────────────────────────────────────
68
69 -- name: ListReposForAdmin :many
70 SELECT r.id, r.owner_user_id, r.owner_org_id, r.name, r.visibility,
71 r.is_archived, r.deleted_at, r.disk_used_bytes, r.created_at,
72 u.username AS owner_user_username,
73 o.slug AS owner_org_slug
74 FROM repos r
75 LEFT JOIN users u ON u.id = r.owner_user_id
76 LEFT JOIN orgs o ON o.id = r.owner_org_id
77 WHERE
78 (sqlc.narg(name_prefix)::text IS NULL OR r.name ILIKE sqlc.narg(name_prefix)::text || '%')
79 AND (sqlc.narg(deleted_only)::bool IS NOT TRUE OR r.deleted_at IS NOT NULL)
80 AND (sqlc.narg(archived_only)::bool IS NOT TRUE OR r.is_archived = true)
81 AND (sqlc.narg(visibility_filter)::repo_visibility IS NULL OR r.visibility = sqlc.narg(visibility_filter)::repo_visibility)
82 ORDER BY r.id DESC
83 LIMIT $1 OFFSET $2;
84
85 -- ─── jobs queue inspector ────────────────────────────────────────
86
87 -- name: ListJobsForAdmin :many
88 -- The admin can filter by kind + status (computed from the timestamp
89 -- columns). Status filter values: queued | running | failed | completed.
90 SELECT id, kind, payload, run_at, attempts, max_attempts,
91 last_error, locked_by, locked_at, completed_at, failed_at, created_at,
92 CASE
93 WHEN completed_at IS NOT NULL THEN 'completed'
94 WHEN failed_at IS NOT NULL THEN 'failed'
95 WHEN locked_at IS NOT NULL THEN 'running'
96 ELSE 'queued'
97 END AS status
98 FROM jobs
99 WHERE
100 (sqlc.narg(kind)::text IS NULL OR kind = sqlc.narg(kind)::text)
101 AND (
102 sqlc.narg(status_filter)::text IS NULL
103 OR (sqlc.narg(status_filter)::text = 'queued' AND completed_at IS NULL AND failed_at IS NULL AND locked_at IS NULL)
104 OR (sqlc.narg(status_filter)::text = 'running' AND completed_at IS NULL AND failed_at IS NULL AND locked_at IS NOT NULL)
105 OR (sqlc.narg(status_filter)::text = 'failed' AND failed_at IS NOT NULL)
106 OR (sqlc.narg(status_filter)::text = 'completed' AND completed_at IS NOT NULL)
107 )
108 ORDER BY id DESC
109 LIMIT $1 OFFSET $2;
110
111 -- name: GetJobForAdmin :one
112 SELECT id, kind, payload, run_at, attempts, max_attempts,
113 last_error, locked_by, locked_at, completed_at, failed_at, created_at
114 FROM jobs
115 WHERE id = $1;
116
117 -- name: AdminRetryJob :exec
118 -- Re-arm a failed/dead job for immediate retry: clear failure state,
119 -- reset attempts, and set run_at to now() so the pool picks it up
120 -- on the next tick.
121 UPDATE jobs
122 SET run_at = now(),
123 attempts = 0,
124 last_error = NULL,
125 failed_at = NULL,
126 completed_at = NULL,
127 locked_by = NULL,
128 locked_at = NULL
129 WHERE id = $1;
130
131 -- name: AdminDiscardJob :exec
132 -- Mark a job dead without running it. Caller has already confirmed.
133 UPDATE jobs
134 SET failed_at = now(),
135 last_error = 'discarded by site admin'
136 WHERE id = $1;
137
138 -- ─── audit log viewer ────────────────────────────────────────────
139
140 -- name: ListAuditForAdmin :many
141 -- Filters: actor, action prefix, target type+id, time range. Keyset
142 -- pagination on (created_at, id) keeps deep pages cheap.
143 SELECT id, actor_id, action, target_type, target_id, meta, created_at
144 FROM auth_audit_log
145 WHERE
146 (sqlc.narg(actor_id)::bigint IS NULL OR actor_id = sqlc.narg(actor_id)::bigint)
147 AND (sqlc.narg(action_prefix)::text IS NULL OR action ILIKE sqlc.narg(action_prefix)::text || '%')
148 AND (sqlc.narg(target_type)::text IS NULL OR target_type = sqlc.narg(target_type)::text)
149 AND (sqlc.narg(target_id)::bigint IS NULL OR target_id = sqlc.narg(target_id)::bigint)
150 AND (sqlc.narg(since)::timestamptz IS NULL OR created_at >= sqlc.narg(since)::timestamptz)
151 AND (sqlc.narg(until)::timestamptz IS NULL OR created_at < sqlc.narg(until)::timestamptz)
152 ORDER BY created_at DESC, id DESC
153 LIMIT $1 OFFSET $2;
154
155 -- ─── transactional email log ─────────────────────────────────────
156
157 -- name: InsertTransactionalEmail :one
158 INSERT INTO transactional_email_log (
159 recipient_user_id, recipient_email, kind, subject, provider_id,
160 status, error_summary
161 ) VALUES (
162 sqlc.narg(recipient_user_id)::bigint,
163 sqlc.arg(recipient_email),
164 sqlc.arg(kind),
165 sqlc.arg(subject),
166 sqlc.arg(provider_id),
167 sqlc.arg(status),
168 sqlc.narg(error_summary)::text
169 )
170 RETURNING id;
171
172 -- name: ListRecentTransactionalEmails :many
173 -- Admin email-queue surface. Most recent first; status filter optional.
174 SELECT id, recipient_user_id, recipient_email, kind, subject,
175 provider_id, status, error_summary, sent_at, delivered_at
176 FROM transactional_email_log
177 WHERE (sqlc.narg(status)::transactional_email_status IS NULL
178 OR status = sqlc.narg(status)::transactional_email_status)
179 ORDER BY sent_at DESC
180 LIMIT $1;
181
182 -- name: MarkTransactionalEmailDelivered :exec
183 UPDATE transactional_email_log
184 SET status = 'sent',
185 delivered_at = now(),
186 error_summary = NULL
187 WHERE id = $1;
188
189 -- name: MarkTransactionalEmailFailed :exec
190 UPDATE transactional_email_log
191 SET status = $2,
192 error_summary = $3
193 WHERE id = $1;
194