| 1 | //! A person's work, for their profile: the work items and pull requests |
| 2 | //! they opened, and those g1t opened for them (`requested_by`, see |
| 3 | //! Pull::owner), newest first, a page at a time. |
| 4 | //! |
| 5 | //! Only work the viewer may read is ever shown, counted or named: pull |
| 6 | //! requests of repositories they may read, and work items filed from one |
| 7 | //! of those or of a workspace they are a member of. Which repositories |
| 8 | //! those are is the repos service's decision (`readable`, the same check as |
| 9 | //! opening the repository), made once per request over every repository |
| 10 | //! the person has worked in; every query here is then confined to that |
| 11 | //! set. A private repository's titles, numbers and even its existence |
| 12 | //! never reach anyone who could not open it. |
| 13 | |
| 14 | use std::collections::HashMap; |
| 15 | |
| 16 | use g1t_contracts::identity::UsernameArgs; |
| 17 | use g1t_contracts::repos::{ReadableArgs, Repo, RepoPath, MAX_READABLE}; |
| 18 | use g1t_contracts::work::*; |
| 19 | use g1t_contracts::{FailureCode, Outcome, Viewer}; |
| 20 | use serde::Deserialize; |
| 21 | use worker::Result; |
| 22 | use worker::wasm_bindgen::JsValue; |
| 23 | |
| 24 | use crate::Work; |
| 25 | |
| 26 | #[derive(Deserialize)] |
| 27 | struct ItemRow { |
| 28 | kind: AuthoredKind, |
| 29 | id: String, |
| 30 | /// A pull request's repository, or a work item's origin. |
| 31 | repo_id: Option<String>, |
| 32 | /// A work item's workspace. |
| 33 | #[serde(default)] |
| 34 | workspace: Option<String>, |
| 35 | number: u32, |
| 36 | title: String, |
| 37 | state: State, |
| 38 | reason: Option<CloseReason>, |
| 39 | status: Option<PullStatus>, |
| 40 | created_at: String, |
| 41 | updated_at: String, |
| 42 | merged_at: Option<String>, |
| 43 | } |
| 44 | |
| 45 | #[derive(Deserialize)] |
| 46 | struct RepoCount { |
| 47 | repo_id: String, |
| 48 | n: u32, |
| 49 | } |
| 50 | |
| 51 | /// Where a page ends: the sort key and id of its last item. |
| 52 | fn cursor(sort: AuthoredSort, row: &ItemRow) -> String { |
| 53 | let key = match sort { |
| 54 | AuthoredSort::Updated => &row.updated_at, |
| 55 | AuthoredSort::Created | AuthoredSort::Oldest => &row.created_at, |
| 56 | }; |
| 57 | format!("{key}|{}", row.id) |
| 58 | } |
| 59 | |
| 60 | /// A cursor read back, or none if it is not one. |
| 61 | fn parse_cursor(value: &str) -> Option<(&str, &str)> { |
| 62 | let (key, id) = value.split_once('|')?; |
| 63 | (!key.is_empty() && !id.is_empty() && !id.contains('|')).then_some((key, id)) |
| 64 | } |
| 65 | |
| 66 | /// One bound value; made a `JsValue` only when bound, so the SQL can be |
| 67 | /// built and tested outside a Worker. |
| 68 | enum Param { |
| 69 | Text(String), |
| 70 | Number(u32), |
| 71 | } |
| 72 | |
| 73 | impl From<&str> for Param { |
| 74 | fn from(value: &str) -> Self { |
| 75 | Param::Text(value.to_owned()) |
| 76 | } |
| 77 | } |
| 78 | |
| 79 | impl From<u32> for Param { |
| 80 | fn from(value: u32) -> Self { |
| 81 | Param::Number(value) |
| 82 | } |
| 83 | } |
| 84 | |
| 85 | /// Parameters bound by number, so each can be used more than once. |
| 86 | struct Params(Vec<Param>); |
| 87 | |
| 88 | impl Params { |
| 89 | fn push(&mut self, value: impl Into<Param>) -> String { |
| 90 | self.0.push(value.into()); |
| 91 | format!("?{}", self.0.len()) |
| 92 | } |
| 93 | |
| 94 | fn values(&self) -> Vec<JsValue> { |
| 95 | self.0 |
| 96 | .iter() |
| 97 | .map(|param| match param { |
| 98 | Param::Text(text) => JsValue::from(text.as_str()), |
| 99 | Param::Number(n) => JsValue::from(*n), |
| 100 | }) |
| 101 | .collect() |
| 102 | } |
| 103 | } |
| 104 | |
| 105 | /// The work SQL for one page: the person's pull requests in `visible`, and |
| 106 | /// their work items filed from one of those or of a workspace in |
| 107 | /// `members`, as one list, filtered, ordered and cut to `limit + 1` rows. |
| 108 | fn page_sql(a: &ByAuthorArgs, params: &mut Params, author: &str, visible: &str, members: &str, limit: u32) -> String { |
| 109 | let author = params.push(author); |
| 110 | let visible = params.push(visible); |
| 111 | let members = params.push(members); |
| 112 | let mut conditions = Vec::new(); |
| 113 | if let Some(kind) = a.kind { |
| 114 | conditions.push(format!( |
| 115 | "kind = {}", |
| 116 | params.push(match kind { |
| 117 | AuthoredKind::Item => "item", |
| 118 | AuthoredKind::Pull => "pull", |
| 119 | }) |
| 120 | )); |
| 121 | } |
| 122 | match a.state { |
| 123 | Some(AuthoredState::Open) => conditions.push("state = 'open'".to_owned()), |
| 124 | Some(AuthoredState::Closed) => conditions.push("state = 'closed'".to_owned()), |
| 125 | Some(AuthoredState::Merged) => conditions.push("status = 'merged'".to_owned()), |
| 126 | None => {} |
| 127 | } |
| 128 | let (key, direction, beyond) = match a.sort { |
| 129 | AuthoredSort::Created => ("created_at", "DESC", "<"), |
| 130 | AuthoredSort::Updated => ("updated_at", "DESC", "<"), |
| 131 | AuthoredSort::Oldest => ("created_at", "ASC", ">"), |
| 132 | }; |
| 133 | if let Some((after_key, after_id)) = a.before.as_deref().and_then(parse_cursor) { |
| 134 | let after_key = params.push(after_key); |
| 135 | let after_id = params.push(after_id); |
| 136 | conditions.push(format!( |
| 137 | "({key} {beyond} {after_key} OR ({key} = {after_key} AND id {beyond} {after_id}))" |
| 138 | )); |
| 139 | } |
| 140 | let filter = if conditions.is_empty() { |
| 141 | String::new() |
| 142 | } else { |
| 143 | format!("WHERE {}", conditions.join(" AND ")) |
| 144 | }; |
| 145 | let limit = params.push(limit + 1); |
| 146 | format!( |
| 147 | "SELECT * FROM ( |
| 148 | SELECT 'item' AS kind, i.id AS id, i.origin_id AS repo_id, i.workspace AS workspace, i.number AS number, |
| 149 | i.title AS title, i.state AS state, i.reason AS reason, |
| 150 | NULL AS status, i.created_at AS created_at, i.updated_at AS updated_at, NULL AS merged_at |
| 151 | FROM {items} i |
| 152 | WHERE COALESCE(i.requested_by_id, i.author_id) = {author} AND i.number IS NOT NULL |
| 153 | AND (i.origin_id IN (SELECT value FROM json_each({visible})) |
| 154 | OR i.workspace IN (SELECT value FROM json_each({members}))) |
| 155 | UNION ALL |
| 156 | SELECT 'pull' AS kind, id, repo_id, NULL AS workspace, number, title, |
| 157 | CASE WHEN status IN ('draft', 'open') THEN 'open' ELSE 'closed' END AS state, |
| 158 | NULL AS reason, status, created_at, updated_at, merged_at |
| 159 | FROM pulls |
| 160 | WHERE COALESCE(requested_by_id, author_id) = {author} AND repo_id IN (SELECT value FROM json_each({visible})) |
| 161 | ) AS work |
| 162 | {filter} |
| 163 | ORDER BY {key} {direction}, id {direction} |
| 164 | LIMIT {limit}", |
| 165 | items = crate::items::sql::items_with_origin!(), |
| 166 | ) |
| 167 | } |
| 168 | |
| 169 | const COUNTS_SQL: &str = concat!( |
| 170 | "SELECT |
| 171 | (SELECT count(*) FROM pulls WHERE COALESCE(requested_by_id, author_id) = ?1 |
| 172 | AND repo_id IN (SELECT value FROM json_each(?2)) AND status = 'merged') AS pulls_merged, |
| 173 | (SELECT count(*) FROM pulls WHERE COALESCE(requested_by_id, author_id) = ?1 |
| 174 | AND repo_id IN (SELECT value FROM json_each(?2)) AND status IN ('draft', 'open')) AS pulls_open, |
| 175 | (SELECT count(*) FROM pulls WHERE COALESCE(requested_by_id, author_id) = ?1 |
| 176 | AND repo_id IN (SELECT value FROM json_each(?2))) AS pulls, |
| 177 | (SELECT count(*) FROM ", |
| 178 | crate::items::sql::items_with_origin!(), |
| 179 | " i WHERE COALESCE(i.requested_by_id, i.author_id) = ?1 AND i.number IS NOT NULL |
| 180 | AND (i.workspace IN (SELECT value FROM json_each(?3)) OR i.origin_id IN (SELECT value FROM json_each(?2)))) AS items, |
| 181 | (SELECT count(*) FROM ", |
| 182 | crate::items::sql::items_with_origin!(), |
| 183 | " i WHERE COALESCE(i.requested_by_id, i.author_id) = ?1 AND i.number IS NOT NULL |
| 184 | AND i.state = 'open' |
| 185 | AND (i.workspace IN (SELECT value FROM json_each(?3)) OR i.origin_id IN (SELECT value FROM json_each(?2)))) AS items_open" |
| 186 | ); |
| 187 | |
| 188 | #[derive(Deserialize)] |
| 189 | #[serde(rename_all = "snake_case")] |
| 190 | struct CountsRow { |
| 191 | pulls_merged: u32, |
| 192 | pulls_open: u32, |
| 193 | pulls: u32, |
| 194 | items: u32, |
| 195 | items_open: u32, |
| 196 | } |
| 197 | |
| 198 | /// Whether `repo` is the one named `namespace/name`. |
| 199 | fn is_named(repo: &Repo, name: &str) -> bool { |
| 200 | name.split_once('/').is_some_and(|(namespace, name)| { |
| 201 | repo.namespace.eq_ignore_ascii_case(namespace) && repo.name.eq_ignore_ascii_case(name) |
| 202 | }) |
| 203 | } |
| 204 | |
| 205 | impl Work { |
| 206 | pub(crate) async fn by_author(&self, a: ByAuthorArgs) -> Result<Outcome<Authored>> { |
| 207 | let person: Viewer = g1t_kit::call( |
| 208 | &self.identity, |
| 209 | "user_by_username", |
| 210 | &UsernameArgs { |
| 211 | username: a.username.trim().to_lowercase(), |
| 212 | }, |
| 213 | ) |
| 214 | .await?; |
| 215 | let Some(person) = person else { |
| 216 | return Ok(Outcome::fail(FailureCode::NotFound, "There is no such account.")); |
| 217 | }; |
| 218 | |
| 219 | // Every repository they have opened work in, busiest first... |
| 220 | let touched = self |
| 221 | .db |
| 222 | .prepare(concat!( |
| 223 | "SELECT repo_id, count(*) AS n FROM ( |
| 224 | SELECT i.origin_id AS repo_id FROM ", |
| 225 | crate::items::sql::items_with_origin!(), |
| 226 | " i |
| 227 | WHERE COALESCE(i.requested_by_id, i.author_id) = ?1 AND i.origin_id IS NOT NULL |
| 228 | UNION ALL SELECT repo_id FROM pulls WHERE COALESCE(requested_by_id, author_id) = ?1 |
| 229 | ) GROUP BY repo_id ORDER BY n DESC LIMIT ?2", |
| 230 | )) |
| 231 | .bind(&[person.id.as_str().into(), (MAX_READABLE as u32).into()])? |
| 232 | .all() |
| 233 | .await? |
| 234 | .results::<RepoCount>()?; |
| 235 | // The workspaces whose work items the viewer sees as a member. |
| 236 | let members: Vec<String> = a |
| 237 | .viewer |
| 238 | .as_ref() |
| 239 | .map(|viewer| { |
| 240 | viewer |
| 241 | .workspaces |
| 242 | .iter() |
| 243 | .map(|membership| membership.slug.to_lowercase()) |
| 244 | .filter(|slug| g1t_contracts::access::workspace_role(viewer, slug).is_some()) |
| 245 | .collect() |
| 246 | }) |
| 247 | .unwrap_or_default(); |
| 248 | if touched.is_empty() && members.is_empty() { |
| 249 | return Ok(Outcome::Ok(Authored::default())); |
| 250 | } |
| 251 | // ...and of those, the ones the viewer may read. |
| 252 | let readable: Vec<Repo> = g1t_kit::call( |
| 253 | &self.repos, |
| 254 | "readable", |
| 255 | &ReadableArgs { |
| 256 | ids: touched.iter().map(|row| row.repo_id.clone()).collect(), |
| 257 | viewer: a.viewer.clone(), |
| 258 | }, |
| 259 | ) |
| 260 | .await?; |
| 261 | let by_id: HashMap<&str, &Repo> = readable.iter().map(|repo| (repo.id.as_str(), repo)).collect(); |
| 262 | let repos: Vec<AuthoredRepo> = touched |
| 263 | .iter() |
| 264 | .filter_map(|row| { |
| 265 | by_id.get(row.repo_id.as_str()).map(|repo| AuthoredRepo { |
| 266 | repo: RepoPath { |
| 267 | namespace: repo.namespace.clone(), |
| 268 | name: repo.name.clone(), |
| 269 | }, |
| 270 | count: row.n, |
| 271 | }) |
| 272 | }) |
| 273 | .collect(); |
| 274 | if readable.is_empty() && members.is_empty() { |
| 275 | return Ok(Outcome::Ok(Authored::default())); |
| 276 | } |
| 277 | |
| 278 | let all_ids: Vec<&str> = readable.iter().map(|repo| repo.id.as_str()).collect(); |
| 279 | let all = serde_json::to_string(&all_ids)?; |
| 280 | let member_list = serde_json::to_string(&members)?; |
| 281 | let counts = self |
| 282 | .db |
| 283 | .prepare(COUNTS_SQL) |
| 284 | .bind(&[person.id.as_str().into(), all.as_str().into(), member_list.as_str().into()])? |
| 285 | .first::<CountsRow>(None) |
| 286 | .await? |
| 287 | .map(|row| AuthoredCounts { |
| 288 | pulls_merged: row.pulls_merged, |
| 289 | pulls_open: row.pulls_open, |
| 290 | pulls: row.pulls, |
| 291 | items: row.items, |
| 292 | items_open: row.items_open, |
| 293 | }) |
| 294 | .unwrap_or_default(); |
| 295 | |
| 296 | // A repository filter narrows the set to its pull requests and the |
| 297 | // work items filed from it; one the viewer cannot read, or the |
| 298 | // person never worked in, leaves nothing. |
| 299 | let (shown, member_list) = match a.repo.as_deref().map(str::trim).filter(|name| !name.is_empty()) { |
| 300 | Some(name) => ( |
| 301 | serde_json::to_string( |
| 302 | &readable |
| 303 | .iter() |
| 304 | .filter(|repo| is_named(repo, name)) |
| 305 | .map(|repo| repo.id.as_str()) |
| 306 | .collect::<Vec<_>>(), |
| 307 | )?, |
| 308 | "[]".to_owned(), |
| 309 | ), |
| 310 | None => (all, member_list), |
| 311 | }; |
| 312 | let limit = a.limit.unwrap_or(AUTHORED_PAGE).clamp(1, AUTHORED_PAGE); |
| 313 | let mut params = Params(Vec::new()); |
| 314 | let sql = page_sql(&a, &mut params, &person.id, &shown, &member_list, limit); |
| 315 | let mut rows = self |
| 316 | .db |
| 317 | .prepare(sql) |
| 318 | .bind(¶ms.values())? |
| 319 | .all() |
| 320 | .await? |
| 321 | .results::<ItemRow>()?; |
| 322 | let next = if rows.len() > limit as usize { |
| 323 | rows.truncate(limit as usize); |
| 324 | rows.last().map(|row| cursor(a.sort, row)) |
| 325 | } else { |
| 326 | None |
| 327 | }; |
| 328 | let items = rows |
| 329 | .into_iter() |
| 330 | .filter_map(|row| { |
| 331 | let repo = row.repo_id.as_deref().and_then(|id| by_id.get(id)); |
| 332 | // A pull request is shown only with its repository. |
| 333 | if row.kind == AuthoredKind::Pull && repo.is_none() { |
| 334 | return None; |
| 335 | } |
| 336 | let workspace = row.workspace.clone().or_else(|| repo.map(|repo| repo.namespace.to_lowercase()))?; |
| 337 | Some(AuthoredItem { |
| 338 | kind: row.kind, |
| 339 | workspace, |
| 340 | repo: repo.map(|repo| RepoPath { namespace: repo.namespace.clone(), name: repo.name.clone() }), |
| 341 | number: row.number, |
| 342 | title: row.title, |
| 343 | state: row.state, |
| 344 | draft: row.status == Some(PullStatus::Draft), |
| 345 | merged: row.status == Some(PullStatus::Merged), |
| 346 | status: row.status, |
| 347 | reason: row.reason, |
| 348 | created_at: row.created_at, |
| 349 | updated_at: row.updated_at, |
| 350 | merged_at: row.merged_at, |
| 351 | }) |
| 352 | }) |
| 353 | .collect(); |
| 354 | Ok(Outcome::Ok(Authored { |
| 355 | items, |
| 356 | next, |
| 357 | counts, |
| 358 | repos, |
| 359 | })) |
| 360 | } |
| 361 | } |
| 362 | |
| 363 | #[cfg(test)] |
| 364 | mod tests { |
| 365 | use super::*; |
| 366 | |
| 367 | fn args() -> ByAuthorArgs { |
| 368 | ByAuthorArgs { |
| 369 | username: "ada".into(), |
| 370 | viewer: None, |
| 371 | kind: None, |
| 372 | state: None, |
| 373 | repo: None, |
| 374 | sort: AuthoredSort::Created, |
| 375 | before: None, |
| 376 | limit: None, |
| 377 | } |
| 378 | } |
| 379 | |
| 380 | fn row(created: &str, updated: &str, id: &str) -> ItemRow { |
| 381 | ItemRow { |
| 382 | kind: AuthoredKind::Pull, |
| 383 | id: id.into(), |
| 384 | repo_id: Some("rep_1".into()), |
| 385 | workspace: None, |
| 386 | number: 1, |
| 387 | title: "t".into(), |
| 388 | state: State::Open, |
| 389 | reason: None, |
| 390 | status: Some(PullStatus::Open), |
| 391 | created_at: created.into(), |
| 392 | updated_at: updated.into(), |
| 393 | merged_at: None, |
| 394 | } |
| 395 | } |
| 396 | |
| 397 | #[test] |
| 398 | fn every_query_is_confined_to_the_readable_set() { |
| 399 | let mut params = Params(Vec::new()); |
| 400 | let sql = page_sql(&args(), &mut params, "usr_1", "[]", "[]", 25); |
| 401 | assert_eq!(sql.matches("json_each(?2)").count(), 2); |
| 402 | assert_eq!(sql.matches("json_each(?3)").count(), 1); |
| 403 | assert_eq!(sql.matches("requested_by_id, ").count(), 2); |
| 404 | assert_eq!(COUNTS_SQL.matches("json_each(?2)").count(), 5); |
| 405 | assert!(sql.contains("LIMIT ?4")); |
| 406 | assert!(sql.contains("ORDER BY created_at DESC, id DESC")); |
| 407 | } |
| 408 | |
| 409 | #[test] |
| 410 | fn filters_by_kind_state_and_page() { |
| 411 | let mut params = Params(Vec::new()); |
| 412 | let a = ByAuthorArgs { |
| 413 | kind: Some(AuthoredKind::Pull), |
| 414 | state: Some(AuthoredState::Merged), |
| 415 | sort: AuthoredSort::Oldest, |
| 416 | before: Some("2026-10-01T00:00:00.000Z|pul_9".into()), |
| 417 | ..args() |
| 418 | }; |
| 419 | let sql = page_sql(&a, &mut params, "usr_1", "[]", "[]", 10); |
| 420 | assert!(sql.contains("kind = ?4")); |
| 421 | assert!(sql.contains("status = 'merged'")); |
| 422 | assert!(sql.contains("(created_at > ?5 OR (created_at = ?5 AND id > ?6))")); |
| 423 | assert!(sql.contains("ORDER BY created_at ASC, id ASC")); |
| 424 | assert!(sql.contains("LIMIT ?7")); |
| 425 | assert_eq!(params.0.len(), 7); |
| 426 | } |
| 427 | |
| 428 | #[test] |
| 429 | fn ignores_a_cursor_that_is_not_one() { |
| 430 | let mut params = Params(Vec::new()); |
| 431 | let a = ByAuthorArgs { |
| 432 | before: Some("nonsense".into()), |
| 433 | ..args() |
| 434 | }; |
| 435 | let sql = page_sql(&a, &mut params, "usr_1", "[]", "[]", 10); |
| 436 | assert!(!sql.contains("id < ")); |
| 437 | assert_eq!(parse_cursor("a|b|c"), None); |
| 438 | assert_eq!(parse_cursor("|b"), None); |
| 439 | assert_eq!(parse_cursor("k|i"), Some(("k", "i"))); |
| 440 | } |
| 441 | |
| 442 | #[test] |
| 443 | fn a_cursor_names_the_sort_key() { |
| 444 | let r = row("2026-01-01T00:00:00.000Z", "2026-02-01T00:00:00.000Z", "pul_1"); |
| 445 | assert_eq!(cursor(AuthoredSort::Created, &r), "2026-01-01T00:00:00.000Z|pul_1"); |
| 446 | assert_eq!(cursor(AuthoredSort::Updated, &r), "2026-02-01T00:00:00.000Z|pul_1"); |
| 447 | assert_eq!(parse_cursor(&cursor(AuthoredSort::Oldest, &r)), Some(("2026-01-01T00:00:00.000Z", "pul_1"))); |
| 448 | } |
| 449 | |
| 450 | #[test] |
| 451 | fn names_a_repository_by_its_path() { |
| 452 | let repo = Repo { |
| 453 | id: "rep_1".into(), |
| 454 | namespace: "acme".into(), |
| 455 | name: "Rocket".into(), |
| 456 | is_private: false, |
| 457 | owner_id: "usr_1".into(), |
| 458 | default_branch: "main".into(), |
| 459 | fork_of: None, |
| 460 | parent_id: None, |
| 461 | forked_from: None, |
| 462 | parent: None, |
| 463 | allow_forking: false, |
| 464 | max_ref_updates: None, |
| 465 | protected: false, |
| 466 | created_at: String::new(), |
| 467 | topics: Vec::new(), |
| 468 | website: None, |
| 469 | archived_at: None, |
| 470 | }; |
| 471 | assert!(is_named(&repo, "acme/rocket")); |
| 472 | assert!(is_named(&repo, "ACME/Rocket")); |
| 473 | assert!(!is_named(&repo, "acme")); |
| 474 | assert!(!is_named(&repo, "other/rocket")); |
| 475 | } |
| 476 | } |