OXYGENHUB

Postgres, step 2c: inbox threads by window functions, RETURNING, lower() 97b18869acf5

The inbox took each thread's newest notification from SQLite's bare columns
beside MAX(at_ms), which Postgres refuses; it now numbers each thread's rows
with ROW_NUMBER() and counts with window sums, which both engines run. The
participating and watching filters lose their last boolean SUM. A new session
reads its id with RETURNING, not last_insert_rowid, and actor and assignee
names are compared and sorted with lower() instead of COLLATE NOCASE.

Refs #211
Author
Andy McCall @andymccall <[email protected]>
via
agent Claude Code <[email protected]> · delegate for changes, until 2026-11-13 19:00Z · key ea5f78160088
When
· 2026-10-16 11:45 UTC
Signature
✔ signed by Claude Code
Hash
97b18869acf56b26684db983c1feea231157a7a1f11c42b2098e71080c934344
Depends on
0024ccb1b050 18e381ea88c2 19bb31ff1f5c 25ab8c73514a 5d90e0e045e2 cb71e18eb6f9 ea14c4ec5c3f eb0c44a9ad37
Depended on by
a22e4aec2e28 3b1bc27fe805
In bookmarks
api-unknown-fields cancel-landed-runs diffusion-pin-bookmark draft-ready main oh-comment-body-file proposal-list-fast
In tags
v0.6.0 v0.6.1 v0.7.0 v0.8.0 v0.9.0 v0.9.1 v0.9.11 v0.9.15 v0.9.16 v0.9.19 v0.9.2 v0.9.20 v0.9.21 v0.9.22 v0.9.23 v0.9.24 v0.9.3 v0.9.4 v0.9.5 v0.9.6 v0.9.7 v0.9.8 v0.9.9
Edits
13 atoms, 0 tree operations, 4 files

Browse the tree at this change

show whitespace changes
modified crates/hub/src/db/accounts.rs +1 −1
… 
439 unchanged lines
440 440 
           UNION SELECT account_id FROM collaborators WHERE repo_id = $2 AND account_id IS NOT NULL \
441 441 
           UNION SELECT tm.account_id FROM team_members tm JOIN collaborators c ON c.team_id = tm.team_id WHERE c.repo_id = $2 \
442 442 
           UNION SELECT m.account_id FROM org_members m JOIN organisations o ON o.id = m.org_id WHERE o.name = $1) \
443 - 
         ORDER BY username COLLATE NOCASE",
443 + 
         ORDER BY lower(username), username",
444 444 
    )
445 445 
    .bind(owner)
446 446 
    .bind(repo_id)
… 
512 unchanged lines
modified crates/hub/src/db/activity.rs +1 −1
… 
54 unchanged lines
55 55 
           UNION ALL SELECT i.repo_id, e.at_ms, e.actor, 'issue', e.event, i.title, i.number FROM issue_events e JOIN issues i ON i.id = e.issue_id) \
56 56 
         SELECT r.owner, r.name, ev.at_ms, ev.actor, ev.kind, ev.event, ev.title, ev.number \
57 57 
         FROM ev JOIN repositories r ON r.id = ev.repo_id WHERE ev.repo_id IN (SELECT id FROM seen) \
58 - 
           AND ($2 IS NULL OR ev.actor = $3 COLLATE NOCASE) \
58 + 
           AND ($2 IS NULL OR lower(ev.actor) = lower($3)) \
59 59 
         ORDER BY ev.at_ms DESC LIMIT $4",
60 60 
    )
61 61 
    .bind(ids_json(repo_ids))
… 
139 unchanged lines
modified crates/hub/src/db/notify.rs +15 −8
… 
106 unchanged lines
107 107 
}
108 108 

    

  
    

      109
      109
       
      
pub async fn threads(pool: &Pool, account_id: i64, f: &Filter, limit: i64) -> sqlx::Result<Vec<Thread>> {

    

  
    

      110
      
      -
      
    // SQLite takes bare columns from the row holding MAX(at_ms): the newest.

    

  
    

      
      110
      +
      
    // Each thread is shown as its newest notification (row 1 by time) with

    

  
    

      
      111
      +
      
    // counts over the whole thread: window functions, so the same SQL runs on

    

  
    

      
      112
      +
      
    // SQLite and Postgres.

    

  
    

      111
      113
       
      
    let mut sql = String::from(

    

  
    

      112
      
      -
      
        "SELECT n.id, MAX(n.at_ms) AS at_ms, n.kind, n.title, n.url, n.actor, n.thread, n.repo_id, \

    

  
    

      113
      
      -
      
         (SELECT r.owner || '/' || r.name FROM repositories r WHERE r.id = n.repo_id) AS repo, \

    

  
    

      114
      
      -
      
         COUNT(*) AS count, SUM(CASE WHEN n.read THEN 0 ELSE 1 END) AS unread, SUM(CASE WHEN n.reason = 'participating' THEN 1 ELSE 0 END) > 0 AS participating, n.done \

    

  
    

      
      114
      +
      
        "SELECT id, at_ms, kind, title, url, actor, thread, repo_id, \

    

  
    

      
      115
      +
      
         (SELECT r.owner || '/' || r.name FROM repositories r WHERE r.id = t.repo_id) AS repo, \

    

  
    

      
      116
      +
      
         count, unread, participating > 0 AS participating, done FROM (\

    

  
    

      
      117
      +
      
         SELECT n.id, n.at_ms, n.kind, n.title, n.url, n.actor, n.thread, n.repo_id, n.done, \

    

  
    

      
      118
      +
      
         ROW_NUMBER() OVER (PARTITION BY n.thread ORDER BY n.at_ms DESC, n.id DESC) AS newest, \

    

  
    

      
      119
      +
      
         COUNT(*) OVER (PARTITION BY n.thread) AS count, \

    

  
    

      
      120
      +
      
         SUM(CASE WHEN n.read THEN 0 ELSE 1 END) OVER (PARTITION BY n.thread) AS unread, \

    

  
    

      
      121
      +
      
         SUM(CASE WHEN n.reason = 'participating' THEN 1 ELSE 0 END) OVER (PARTITION BY n.thread) AS participating \

    

  
    

      115
      122
       
      
         FROM notifications n WHERE n.account_id = $1 AND n.done = $2",

    

  
    

      116
      123
       
      
    );

    

  
    

      117
      124
       
      
    // Placeholders numbered as they are added: $1 and $2 above.

    

  
    

      
      
      …
      
6 unchanged lines

    

  
    

      124
      131
       
      
        sql.push_str(&format!(" AND n.repo_id = ${next}"));

    

  
    

      125
      132
       
      
        next += 1;

    

  
    

      126
      133
       
      
    }

    

  
    

      127
      
      -
      
    sql.push_str(" GROUP BY n.thread HAVING 1");

    

  
    

      
      134
      +
      
    sql.push_str(") t WHERE newest = 1");

    

  
    

      128
      135
       
      
    if f.unread_only {

    

  
    

      129
      
      -
      
        sql.push_str(" AND SUM(CASE WHEN n.read THEN 0 ELSE 1 END) > 0");

    

  
    

      
      136
      +
      
        sql.push_str(" AND unread > 0");

    

  
    

      130
      137
       
      
    }

    

  
    

      131
      138
       
      
    match f.reason.as_deref() {

    

  
    

      132
      
      -
      
        Some("participating") => sql.push_str(" AND SUM(n.reason = 'participating') > 0"),

    

  
    

      133
      
      -
      
        Some("watching") => sql.push_str(" AND SUM(n.reason = 'participating') = 0"),

    

  
    

      
      139
      +
      
        Some("participating") => sql.push_str(" AND participating > 0"),

    

  
    

      
      140
      +
      
        Some("watching") => sql.push_str(" AND participating = 0"),

    

  
    

      134
      141
       
      
        _ => {}

    

  
    

      135
      142
       
      
    }

    

  
    

      136
      143
       
      
    sql.push_str(&format!(" ORDER BY at_ms DESC LIMIT ${next}"));

    

  
    

      
      
      …
      
209 unchanged lines

    

  
  

  





  
    modified
    crates/hub/src/db/sessions.rs
    +3 −4
  
  
  

  
    

      
      
      …
      
31 unchanged lines

    

  
    

      32
      32
       
      

    

  
    

      33
      33
       
      
pub async fn create(pool: &Pool, account_id: i64, hash: &str, salt: &str) -> sqlx::Result<i64> {

    

  
    

      34
      34
       
      
    let now = now_ms();

    

  
    

      35
      
      -
      
    let r = sqlx::query("INSERT INTO sessions (account_id, hash, salt, created_ms, seen_ms) VALUES ($1, $2, $3, $4, $5)")

    

  
    

      
      35
      +
      
    sqlx::query_scalar("INSERT INTO sessions (account_id, hash, salt, created_ms, seen_ms) VALUES ($1, $2, $3, $4, $5) RETURNING id")

    

  
    

      36
      36
       
      
        .bind(account_id)

    

  
    

      37
      37
       
      
        .bind(hash)

    

  
    

      38
      38
       
      
        .bind(salt)

    

  
    

      39
      39
       
      
        .bind(now)

    

  
    

      40
      40
       
      
        .bind(now)

    

  
    

      41
      
      -
      
        .execute(pool)

    

  
    

      42
      
      -
      
        .await?;

    

  
    

      43
      
      -
      
    Ok(r.last_insert_rowid())

    

  
    

      
      41
      +
      
        .fetch_one(pool)

    

  
    

      
      42
      +
      
        .await

    

  
    

      44
      43
       
      
}

    

  
    

      45
      44
       
      

    

  
    

      46
      45
       
      
pub async fn by_hash(pool: &Pool, hash: &str) -> sqlx::Result<Option<Session>> {

    

  
    

      
      
      …
      
78 unchanged lines