| 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, o.repo_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 work_items i LEFT JOIN work_item_repos o ON o.item_id = i.id AND o.role = 'origin' |
| 152 | WHERE COALESCE(i.requested_by_id, i.author_id) = {author} AND i.number IS NOT NULL |
| 153 | AND (o.repo_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 | ) |
| 166 | } |
| 167 | |
| 168 | const COUNTS_SQL: &str = "SELECT |
| 169 | (SELECT count(*) FROM pulls WHERE COALESCE(requested_by_id, author_id) = ?1 |
| 170 | AND repo_id IN (SELECT value FROM json_each(?2)) AND status = 'merged') AS pulls_merged, |
| 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 IN ('draft', 'open')) AS pulls_open, |
| 173 | (SELECT count(*) FROM pulls WHERE COALESCE(requested_by_id, author_id) = ?1 |
| 174 | AND repo_id IN (SELECT value FROM json_each(?2))) AS pulls, |
| 175 | (SELECT count(*) FROM work_items i WHERE COALESCE(i.requested_by_id, i.author_id) = ?1 AND i.number IS NOT NULL |
| 176 | AND (i.workspace IN (SELECT value FROM json_each(?3)) OR EXISTS (SELECT 1 FROM work_item_repos o |
| 177 | WHERE o.item_id = i.id AND o.role = 'origin' AND o.repo_id IN (SELECT value FROM json_each(?2))))) AS items, |
| 178 | (SELECT count(*) FROM work_items i WHERE COALESCE(i.requested_by_id, i.author_id) = ?1 AND i.number IS NOT NULL |
| 179 | AND i.state = 'open' |
| 180 | AND (i.workspace IN (SELECT value FROM json_each(?3)) OR EXISTS (SELECT 1 FROM work_item_repos o |
| 181 | WHERE o.item_id = i.id AND o.role = 'origin' AND o.repo_id IN (SELECT value FROM json_each(?2))))) AS items_open"; |
| 182 | |
| 183 | #[derive(Deserialize)] |
| 184 | #[serde(rename_all = "snake_case")] |
| 185 | struct CountsRow { |
| 186 | pulls_merged: u32, |
| 187 | pulls_open: u32, |
| 188 | pulls: u32, |
| 189 | items: u32, |
| 190 | items_open: u32, |
| 191 | } |
| 192 | |
| 193 | /// Whether `repo` is the one named `namespace/name`. |
| 194 | fn is_named(repo: &Repo, name: &str) -> bool { |
| 195 | name.split_once('/').is_some_and(|(namespace, name)| { |
| 196 | repo.namespace.eq_ignore_ascii_case(namespace) && repo.name.eq_ignore_ascii_case(name) |
| 197 | }) |
| 198 | } |
| 199 | |
| 200 | impl Work { |
| 201 | pub(crate) async fn by_author(&self, a: ByAuthorArgs) -> Result<Outcome<Authored>> { |
| 202 | let person: Viewer = g1t_kit::call( |
| 203 | &self.identity, |
| 204 | "user_by_username", |
| 205 | &UsernameArgs { |
| 206 | username: a.username.trim().to_lowercase(), |
| 207 | }, |
| 208 | ) |
| 209 | .await?; |
| 210 | let Some(person) = person else { |
| 211 | return Ok(Outcome::fail(FailureCode::NotFound, "There is no such account.")); |
| 212 | }; |
| 213 | |
| 214 | // Every repository they have opened work in, busiest first... |
| 215 | let touched = self |
| 216 | .db |
| 217 | .prepare( |
| 218 | "SELECT repo_id, count(*) AS n FROM ( |
| 219 | SELECT o.repo_id AS repo_id FROM work_items i JOIN work_item_repos o ON o.item_id = i.id AND o.role = 'origin' |
| 220 | WHERE COALESCE(i.requested_by_id, i.author_id) = ?1 |
| 221 | UNION ALL SELECT repo_id FROM pulls WHERE COALESCE(requested_by_id, author_id) = ?1 |
| 222 | ) GROUP BY repo_id ORDER BY n DESC LIMIT ?2", |
| 223 | ) |
| 224 | .bind(&[person.id.as_str().into(), (MAX_READABLE as u32).into()])? |
| 225 | .all() |
| 226 | .await? |
| 227 | .results::<RepoCount>()?; |
| 228 | // The workspaces whose work items the viewer sees as a member. |
| 229 | let members: Vec<String> = a |
| 230 | .viewer |
| 231 | .as_ref() |
| 232 | .map(|viewer| { |
| 233 | viewer |
| 234 | .workspaces |
| 235 | .iter() |
| 236 | .map(|membership| membership.slug.to_lowercase()) |
| 237 | .filter(|slug| g1t_contracts::access::workspace_role(viewer, slug).is_some()) |
| 238 | .collect() |
| 239 | }) |
| 240 | .unwrap_or_default(); |
| 241 | if touched.is_empty() && members.is_empty() { |
| 242 | return Ok(Outcome::Ok(Authored::default())); |
| 243 | } |
| 244 | // ...and of those, the ones the viewer may read. |
| 245 | let readable: Vec<Repo> = g1t_kit::call( |
| 246 | &self.repos, |
| 247 | "readable", |
| 248 | &ReadableArgs { |
| 249 | ids: touched.iter().map(|row| row.repo_id.clone()).collect(), |
| 250 | viewer: a.viewer.clone(), |
| 251 | }, |
| 252 | ) |
| 253 | .await?; |
| 254 | let by_id: HashMap<&str, &Repo> = readable.iter().map(|repo| (repo.id.as_str(), repo)).collect(); |
| 255 | let repos: Vec<AuthoredRepo> = touched |
| 256 | .iter() |
| 257 | .filter_map(|row| { |
| 258 | by_id.get(row.repo_id.as_str()).map(|repo| AuthoredRepo { |
| 259 | repo: RepoPath { |
| 260 | namespace: repo.namespace.clone(), |
| 261 | name: repo.name.clone(), |
| 262 | }, |
| 263 | count: row.n, |
| 264 | }) |
| 265 | }) |
| 266 | .collect(); |
| 267 | if readable.is_empty() && members.is_empty() { |
| 268 | return Ok(Outcome::Ok(Authored::default())); |
| 269 | } |
| 270 | |
| 271 | let all_ids: Vec<&str> = readable.iter().map(|repo| repo.id.as_str()).collect(); |
| 272 | let all = serde_json::to_string(&all_ids)?; |
| 273 | let member_list = serde_json::to_string(&members)?; |
| 274 | let counts = self |
| 275 | .db |
| 276 | .prepare(COUNTS_SQL) |
| 277 | .bind(&[person.id.as_str().into(), all.as_str().into(), member_list.as_str().into()])? |
| 278 | .first::<CountsRow>(None) |
| 279 | .await? |
| 280 | .map(|row| AuthoredCounts { |
| 281 | pulls_merged: row.pulls_merged, |
| 282 | pulls_open: row.pulls_open, |
| 283 | pulls: row.pulls, |
| 284 | items: row.items, |
| 285 | items_open: row.items_open, |
| 286 | }) |
| 287 | .unwrap_or_default(); |
| 288 | |
| 289 | // A repository filter narrows the set to its pull requests and the |
| 290 | // work items filed from it; one the viewer cannot read, or the |
| 291 | // person never worked in, leaves nothing. |
| 292 | let (shown, member_list) = match a.repo.as_deref().map(str::trim).filter(|name| !name.is_empty()) { |
| 293 | Some(name) => ( |
| 294 | serde_json::to_string( |
| 295 | &readable |
| 296 | .iter() |
| 297 | .filter(|repo| is_named(repo, name)) |
| 298 | .map(|repo| repo.id.as_str()) |
| 299 | .collect::<Vec<_>>(), |
| 300 | )?, |
| 301 | "[]".to_owned(), |
| 302 | ), |
| 303 | None => (all, member_list), |
| 304 | }; |
| 305 | let limit = a.limit.unwrap_or(AUTHORED_PAGE).clamp(1, AUTHORED_PAGE); |
| 306 | let mut params = Params(Vec::new()); |
| 307 | let sql = page_sql(&a, &mut params, &person.id, &shown, &member_list, limit); |
| 308 | let mut rows = self |
| 309 | .db |
| 310 | .prepare(sql) |
| 311 | .bind(¶ms.values())? |
| 312 | .all() |
| 313 | .await? |
| 314 | .results::<ItemRow>()?; |
| 315 | let next = if rows.len() > limit as usize { |
| 316 | rows.truncate(limit as usize); |
| 317 | rows.last().map(|row| cursor(a.sort, row)) |
| 318 | } else { |
| 319 | None |
| 320 | }; |
| 321 | let items = rows |
| 322 | .into_iter() |
| 323 | .filter_map(|row| { |
| 324 | let repo = row.repo_id.as_deref().and_then(|id| by_id.get(id)); |
| 325 | // A pull request is shown only with its repository. |
| 326 | if row.kind == AuthoredKind::Pull && repo.is_none() { |
| 327 | return None; |
| 328 | } |
| 329 | let workspace = row.workspace.clone().or_else(|| repo.map(|repo| repo.namespace.to_lowercase()))?; |
| 330 | Some(AuthoredItem { |
| 331 | kind: row.kind, |
| 332 | workspace, |
| 333 | repo: repo.map(|repo| RepoPath { namespace: repo.namespace.clone(), name: repo.name.clone() }), |
| 334 | number: row.number, |
| 335 | title: row.title, |
| 336 | state: row.state, |
| 337 | draft: row.status == Some(PullStatus::Draft), |
| 338 | merged: row.status == Some(PullStatus::Merged), |
| 339 | status: row.status, |
| 340 | reason: row.reason, |
| 341 | created_at: row.created_at, |
| 342 | updated_at: row.updated_at, |
| 343 | merged_at: row.merged_at, |
| 344 | }) |
| 345 | }) |
| 346 | .collect(); |
| 347 | Ok(Outcome::Ok(Authored { |
| 348 | items, |
| 349 | next, |
| 350 | counts, |
| 351 | repos, |
| 352 | })) |
| 353 | } |
| 354 | } |
| 355 | |
| 356 | #[cfg(test)] |
| 357 | mod tests { |
| 358 | use super::*; |
| 359 | |
| 360 | fn args() -> ByAuthorArgs { |
| 361 | ByAuthorArgs { |
| 362 | username: "ada".into(), |
| 363 | viewer: None, |
| 364 | kind: None, |
| 365 | state: None, |
| 366 | repo: None, |
| 367 | sort: AuthoredSort::Created, |
| 368 | before: None, |
| 369 | limit: None, |
| 370 | } |
| 371 | } |
| 372 | |
| 373 | fn row(created: &str, updated: &str, id: &str) -> ItemRow { |
| 374 | ItemRow { |
| 375 | kind: AuthoredKind::Pull, |
| 376 | id: id.into(), |
| 377 | repo_id: Some("rep_1".into()), |
| 378 | workspace: None, |
| 379 | number: 1, |
| 380 | title: "t".into(), |
| 381 | state: State::Open, |
| 382 | reason: None, |
| 383 | status: Some(PullStatus::Open), |
| 384 | created_at: created.into(), |
| 385 | updated_at: updated.into(), |
| 386 | merged_at: None, |
| 387 | } |
| 388 | } |
| 389 | |
| 390 | #[test] |
| 391 | fn every_query_is_confined_to_the_readable_set() { |
| 392 | let mut params = Params(Vec::new()); |
| 393 | let sql = page_sql(&args(), &mut params, "usr_1", "[]", "[]", 25); |
| 394 | assert_eq!(sql.matches("json_each(?2)").count(), 2); |
| 395 | assert_eq!(sql.matches("json_each(?3)").count(), 1); |
| 396 | assert_eq!(sql.matches("requested_by_id, ").count(), 2); |
| 397 | assert_eq!(COUNTS_SQL.matches("json_each(?2)").count(), 5); |
| 398 | assert!(sql.contains("LIMIT ?4")); |
| 399 | assert!(sql.contains("ORDER BY created_at DESC, id DESC")); |
| 400 | } |
| 401 | |
| 402 | #[test] |
| 403 | fn filters_by_kind_state_and_page() { |
| 404 | let mut params = Params(Vec::new()); |
| 405 | let a = ByAuthorArgs { |
| 406 | kind: Some(AuthoredKind::Pull), |
| 407 | state: Some(AuthoredState::Merged), |
| 408 | sort: AuthoredSort::Oldest, |
| 409 | before: Some("2026-10-01T00:00:00.000Z|pul_9".into()), |
| 410 | ..args() |
| 411 | }; |
| 412 | let sql = page_sql(&a, &mut params, "usr_1", "[]", "[]", 10); |
| 413 | assert!(sql.contains("kind = ?4")); |
| 414 | assert!(sql.contains("status = 'merged'")); |
| 415 | assert!(sql.contains("(created_at > ?5 OR (created_at = ?5 AND id > ?6))")); |
| 416 | assert!(sql.contains("ORDER BY created_at ASC, id ASC")); |
| 417 | assert!(sql.contains("LIMIT ?7")); |
| 418 | assert_eq!(params.0.len(), 7); |
| 419 | } |
| 420 | |
| 421 | #[test] |
| 422 | fn ignores_a_cursor_that_is_not_one() { |
| 423 | let mut params = Params(Vec::new()); |
| 424 | let a = ByAuthorArgs { |
| 425 | before: Some("nonsense".into()), |
| 426 | ..args() |
| 427 | }; |
| 428 | let sql = page_sql(&a, &mut params, "usr_1", "[]", "[]", 10); |
| 429 | assert!(!sql.contains("id < ")); |
| 430 | assert_eq!(parse_cursor("a|b|c"), None); |
| 431 | assert_eq!(parse_cursor("|b"), None); |
| 432 | assert_eq!(parse_cursor("k|i"), Some(("k", "i"))); |
| 433 | } |
| 434 | |
| 435 | #[test] |
| 436 | fn a_cursor_names_the_sort_key() { |
| 437 | let r = row("2026-01-01T00:00:00.000Z", "2026-02-01T00:00:00.000Z", "pul_1"); |
| 438 | assert_eq!(cursor(AuthoredSort::Created, &r), "2026-01-01T00:00:00.000Z|pul_1"); |
| 439 | assert_eq!(cursor(AuthoredSort::Updated, &r), "2026-02-01T00:00:00.000Z|pul_1"); |
| 440 | assert_eq!(parse_cursor(&cursor(AuthoredSort::Oldest, &r)), Some(("2026-01-01T00:00:00.000Z", "pul_1"))); |
| 441 | } |
| 442 | |
| 443 | #[test] |
| 444 | fn names_a_repository_by_its_path() { |
| 445 | let repo = Repo { |
| 446 | id: "rep_1".into(), |
| 447 | namespace: "acme".into(), |
| 448 | name: "Rocket".into(), |
| 449 | is_private: false, |
| 450 | owner_id: "usr_1".into(), |
| 451 | default_branch: "main".into(), |
| 452 | fork_of: None, |
| 453 | parent_id: None, |
| 454 | forked_from: None, |
| 455 | parent: None, |
| 456 | allow_forking: false, |
| 457 | max_ref_updates: None, |
| 458 | protected: false, |
| 459 | created_at: String::new(), |
| 460 | topics: Vec::new(), |
| 461 | website: None, |
| 462 | archived_at: None, |
| 463 | }; |
| 464 | assert!(is_named(&repo, "acme/rocket")); |
| 465 | assert!(is_named(&repo, "ACME/Rocket")); |
| 466 | assert!(!is_named(&repo, "acme")); |
| 467 | assert!(!is_named(&repo, "other/rocket")); |
| 468 | } |
| 469 | } |