@jpgilldev / steid

steid/src/infrastructure/repository/sqlite.rs
43.8 KBRaw
1//! SQLite repository implementations.
2//!
3//! Rows are reassembled with `from_trusted`: they were validated on the way in, and
4//! re-validating them would make a tightened rule turn old rows unreadable.
5
6use std::time::{Duration, SystemTime};
7
8use sqlx::{Row, SqlitePool, sqlite::SqliteRow};
9
10use crate::domain::{
11 Email, Membership, MembershipId, OrgId, OrgName, Organization, PasswordHash,
12 PersonalAccessToken, RepoId, RepoName, Repository, Role, Session, SessionTokenHash, TokenHash,
13 TokenId, User, UserId, Visibility,
14 repository::{
15 MembershipRepository, OrgRepository, RepoRepository, RepositoryError, RepositoryResult,
16 SessionRepository, TokenRepository, UserRepository,
17 },
18};
19
20fn backend(error: sqlx::Error) -> RepositoryError {
21 RepositoryError::backend(error)
22}
23
24#[derive(Debug, Clone)]
25pub struct SqliteUserRepo {
26 pool: SqlitePool,
27}
28
29impl SqliteUserRepo {
30 pub fn new(pool: SqlitePool) -> Self {
31 Self { pool }
32 }
33
34 fn map(row: &SqliteRow) -> User {
35 User::new(
36 UserId::from_trusted(row.get::<String, _>("id")),
37 Email::from_trusted(row.get::<String, _>("email")),
38 PasswordHash::from_trusted(row.get::<String, _>("password_hash")),
39 OrgId::from_trusted(row.get::<String, _>("personal_org_id")),
40 )
41 }
42}
43
44impl UserRepository for SqliteUserRepo {
45 async fn find_by_id(&self, id: &UserId) -> RepositoryResult<Option<User>> {
46 let row = sqlx::query("select * from users where id = ?")
47 .bind(id.as_str())
48 .fetch_optional(&self.pool)
49 .await
50 .map_err(backend)?;
51
52 Ok(row.as_ref().map(Self::map))
53 }
54
55 async fn find_by_email(&self, email: &Email) -> RepositoryResult<Option<User>> {
56 let row = sqlx::query("select * from users where email = ?")
57 .bind(email.as_str())
58 .fetch_optional(&self.pool)
59 .await
60 .map_err(backend)?;
61
62 Ok(row.as_ref().map(Self::map))
63 }
64
65 async fn save(&self, user: &User) -> RepositoryResult<()> {
66 sqlx::query(
67 "insert into users (id, email, password_hash, personal_org_id)
68 values (?, ?, ?, ?)
69 on conflict (id) do update set
70 email = excluded.email,
71 password_hash = excluded.password_hash,
72 personal_org_id = excluded.personal_org_id",
73 )
74 .bind(user.id.as_str())
75 .bind(user.email.as_str())
76 .bind(user.password_hash.as_str())
77 .bind(user.personal_org_id.as_str())
78 .execute(&self.pool)
79 .await
80 .map_err(backend)?;
81
82 Ok(())
83 }
84
85 async fn sole_user(&self) -> RepositoryResult<Option<User>> {
86 // `limit 2`, not `limit 1`: the query has to be able to see a second user in
87 // order to refuse, and `limit 1` would silently pick one.
88 let rows = sqlx::query("select * from users limit 2")
89 .fetch_all(&self.pool)
90 .await
91 .map_err(backend)?;
92
93 match rows.as_slice() {
94 [row] => Ok(Some(Self::map(row))),
95 _ => Ok(None),
96 }
97 }
98
99 async fn any_exist(&self) -> RepositoryResult<bool> {
100 let count: i64 = sqlx::query_scalar("select exists (select 1 from users)")
101 .fetch_one(&self.pool)
102 .await
103 .map_err(backend)?;
104
105 Ok(count != 0)
106 }
107}
108
109#[derive(Debug, Clone)]
110pub struct SqliteOrgRepo {
111 pool: SqlitePool,
112}
113
114impl SqliteOrgRepo {
115 pub fn new(pool: SqlitePool) -> Self {
116 Self { pool }
117 }
118
119 fn map(row: &SqliteRow) -> Organization {
120 Organization::from_trusted(
121 OrgId::from_trusted(row.get::<String, _>("id")),
122 OrgName::from_trusted(row.get::<String, _>("name")),
123 row.get::<Option<String>, _>("display_name"),
124 row.get::<Option<String>, _>("bio"),
125 )
126 }
127}
128
129impl OrgRepository for SqliteOrgRepo {
130 async fn find_by_id(&self, id: &OrgId) -> RepositoryResult<Option<Organization>> {
131 let row = sqlx::query("select * from orgs where id = ?")
132 .bind(id.as_str())
133 .fetch_optional(&self.pool)
134 .await
135 .map_err(backend)?;
136
137 Ok(row.as_ref().map(Self::map))
138 }
139
140 async fn find_by_name(&self, name: &OrgName) -> RepositoryResult<Option<Organization>> {
141 let row = sqlx::query("select * from orgs where name = ?")
142 .bind(name.as_str())
143 .fetch_optional(&self.pool)
144 .await
145 .map_err(backend)?;
146
147 Ok(row.as_ref().map(Self::map))
148 }
149
150 async fn save(&self, org: &Organization) -> RepositoryResult<()> {
151 sqlx::query(
152 "insert into orgs (id, name, display_name, bio)
153 values (?, ?, ?, ?)
154 on conflict (id) do update set
155 name = excluded.name,
156 display_name = excluded.display_name,
157 bio = excluded.bio",
158 )
159 .bind(org.id.as_str())
160 .bind(org.name.as_str())
161 .bind(org.display_name.as_deref())
162 .bind(org.bio.as_deref())
163 .execute(&self.pool)
164 .await
165 .map_err(backend)?;
166
167 Ok(())
168 }
169}
170
171#[derive(Debug, Clone)]
172pub struct SqliteMembershipRepo {
173 pool: SqlitePool,
174}
175
176impl SqliteMembershipRepo {
177 pub fn new(pool: SqlitePool) -> Self {
178 Self { pool }
179 }
180
181 /// An unparseable role is a storage fault, not a missing membership, so it
182 /// surfaces rather than silently downgrading the member's access.
183 fn map(row: &SqliteRow) -> RepositoryResult<Membership> {
184 let raw: String = row.get("role");
185 let role: Role = raw
186 .parse()
187 .map_err(|error| RepositoryError::backend(format!("{error}")))?;
188
189 Ok(Membership::new(
190 MembershipId::from_trusted(row.get::<String, _>("id")),
191 OrgId::from_trusted(row.get::<String, _>("org_id")),
192 UserId::from_trusted(row.get::<String, _>("user_id")),
193 role,
194 ))
195 }
196}
197
198impl MembershipRepository for SqliteMembershipRepo {
199 async fn find(&self, org_id: &OrgId, user_id: &UserId) -> RepositoryResult<Option<Membership>> {
200 let row = sqlx::query("select * from memberships where org_id = ? and user_id = ?")
201 .bind(org_id.as_str())
202 .bind(user_id.as_str())
203 .fetch_optional(&self.pool)
204 .await
205 .map_err(backend)?;
206
207 row.as_ref().map(Self::map).transpose()
208 }
209
210 async fn list_for_user(&self, user_id: &UserId) -> RepositoryResult<Vec<Membership>> {
211 let rows = sqlx::query("select * from memberships where user_id = ?")
212 .bind(user_id.as_str())
213 .fetch_all(&self.pool)
214 .await
215 .map_err(backend)?;
216
217 rows.iter().map(Self::map).collect()
218 }
219
220 async fn save(&self, membership: &Membership) -> RepositoryResult<()> {
221 sqlx::query(
222 "insert into memberships (id, org_id, user_id, role)
223 values (?, ?, ?, ?)
224 on conflict (id) do update set role = excluded.role",
225 )
226 .bind(membership.id.as_str())
227 .bind(membership.org_id.as_str())
228 .bind(membership.user_id.as_str())
229 .bind(membership.role.as_str())
230 .execute(&self.pool)
231 .await
232 .map_err(backend)?;
233
234 Ok(())
235 }
236}
237
238#[derive(Debug, Clone)]
239pub struct SqliteSessionRepo {
240 pool: SqlitePool,
241}
242
243impl SqliteSessionRepo {
244 pub fn new(pool: SqlitePool) -> Self {
245 Self { pool }
246 }
247}
248
249/// Unix seconds. Times before the epoch cannot occur here — sessions always expire in
250/// the future — so saturating at 0 is safe rather than lossy.
251fn to_unix(time: SystemTime) -> i64 {
252 time.duration_since(SystemTime::UNIX_EPOCH)
253 .map(|d| d.as_secs() as i64)
254 .unwrap_or(0)
255}
256
257fn from_unix(seconds: i64) -> SystemTime {
258 SystemTime::UNIX_EPOCH + Duration::from_secs(seconds.max(0) as u64)
259}
260
261impl SessionRepository for SqliteSessionRepo {
262 async fn find(&self, token_hash: &SessionTokenHash) -> RepositoryResult<Option<Session>> {
263 let row = sqlx::query("select * from sessions where token_hash = ?")
264 .bind(token_hash.as_str())
265 .fetch_optional(&self.pool)
266 .await
267 .map_err(backend)?;
268
269 Ok(row.map(|row| {
270 Session::new(
271 SessionTokenHash::from_trusted(row.get::<String, _>("token_hash")),
272 UserId::from_trusted(row.get::<String, _>("user_id")),
273 from_unix(row.get::<i64, _>("expires_at")),
274 )
275 }))
276 }
277
278 async fn save(&self, session: &Session) -> RepositoryResult<()> {
279 sqlx::query(
280 "insert into sessions (token_hash, user_id, expires_at)
281 values (?, ?, ?)
282 on conflict (token_hash) do update set
283 user_id = excluded.user_id,
284 expires_at = excluded.expires_at",
285 )
286 .bind(session.token_hash.as_str())
287 .bind(session.user_id.as_str())
288 .bind(to_unix(session.expires_at))
289 .execute(&self.pool)
290 .await
291 .map_err(backend)?;
292
293 Ok(())
294 }
295
296 async fn delete(&self, token_hash: &SessionTokenHash) -> RepositoryResult<()> {
297 sqlx::query("delete from sessions where token_hash = ?")
298 .bind(token_hash.as_str())
299 .execute(&self.pool)
300 .await
301 .map_err(backend)?;
302
303 Ok(())
304 }
305
306 async fn delete_expired(&self, now: SystemTime) -> RepositoryResult<u64> {
307 let result = sqlx::query("delete from sessions where expires_at <= ?")
308 .bind(to_unix(now))
309 .execute(&self.pool)
310 .await
311 .map_err(backend)?;
312
313 Ok(result.rows_affected())
314 }
315}
316
317#[derive(Debug, Clone)]
318pub struct SqliteRepoRepo {
319 pool: SqlitePool,
320}
321
322impl SqliteRepoRepo {
323 pub fn new(pool: SqlitePool) -> Self {
324 Self { pool }
325 }
326
327 /// An unparseable visibility is a storage fault, not a default.
328 ///
329 /// Falling back to `Public` would publish a repository whose row we cannot read;
330 /// falling back to `Private` would hide a public one. Neither is a guess worth
331 /// making, so the row surfaces as an error.
332 fn map(row: &SqliteRow) -> RepositoryResult<Repository> {
333 let raw: String = row.get("visibility");
334 let visibility: Visibility = raw
335 .parse()
336 .map_err(|error| RepositoryError::backend(format!("{error}")))?;
337
338 Ok(Repository::from_trusted(
339 RepoId::from_trusted(row.get::<String, _>("id")),
340 OrgId::from_trusted(row.get::<String, _>("org_id")),
341 RepoName::from_trusted(row.get::<String, _>("name")),
342 row.get::<Option<String>, _>("description"),
343 visibility,
344 from_unix(row.get::<i64, _>("updated_at")),
345 row.get::<i64, _>("pinned") != 0,
346 ))
347 }
348}
349
350impl RepoRepository for SqliteRepoRepo {
351 async fn find_by_id(&self, id: &RepoId) -> RepositoryResult<Option<Repository>> {
352 let row = sqlx::query("select * from repositories where id = ?")
353 .bind(id.as_str())
354 .fetch_optional(&self.pool)
355 .await
356 .map_err(backend)?;
357
358 row.as_ref().map(Self::map).transpose()
359 }
360
361 async fn find_by_org_and_name(
362 &self,
363 org_id: &OrgId,
364 name: &RepoName,
365 ) -> RepositoryResult<Option<Repository>> {
366 let row = sqlx::query("select * from repositories where org_id = ? and name = ?")
367 .bind(org_id.as_str())
368 .bind(name.as_str())
369 .fetch_optional(&self.pool)
370 .await
371 .map_err(backend)?;
372
373 row.as_ref().map(Self::map).transpose()
374 }
375
376 async fn list_by_org(&self, org_id: &OrgId) -> RepositoryResult<Vec<Repository>> {
377 // Newest first, name ascending as the tiebreak — `InMemoryRepoRepo::list_by_org`
378 // sorts identically, deliberately.
379 let rows = sqlx::query(
380 "select * from repositories where org_id = ? order by updated_at desc, name asc",
381 )
382 .bind(org_id.as_str())
383 .fetch_all(&self.pool)
384 .await
385 .map_err(backend)?;
386
387 rows.iter().map(Self::map).collect()
388 }
389
390 async fn save(&self, repo: &Repository) -> RepositoryResult<()> {
391 sqlx::query(
392 "insert into repositories (id, org_id, name, description, visibility, updated_at, pinned)
393 values (?, ?, ?, ?, ?, ?, ?)
394 on conflict (id) do update set
395 org_id = excluded.org_id,
396 name = excluded.name,
397 description = excluded.description,
398 visibility = excluded.visibility,
399 updated_at = excluded.updated_at,
400 pinned = excluded.pinned",
401 )
402 .bind(repo.id.as_str())
403 .bind(repo.org_id.as_str())
404 .bind(repo.name.as_str())
405 .bind(repo.description.as_deref())
406 .bind(repo.visibility.as_str())
407 .bind(to_unix(repo.updated_at))
408 .bind(i64::from(repo.pinned))
409 .execute(&self.pool)
410 .await
411 .map_err(backend)?;
412
413 Ok(())
414 }
415
416 async fn touch(&self, id: &RepoId, now: SystemTime) -> RepositoryResult<()> {
417 // A column update rather than a read-modify-write, so two pushes racing each
418 // other cannot restore an older timestamp.
419 sqlx::query("update repositories set updated_at = ? where id = ?")
420 .bind(to_unix(now))
421 .bind(id.as_str())
422 .execute(&self.pool)
423 .await
424 .map_err(backend)?;
425
426 Ok(())
427 }
428
429 async fn delete(&self, id: &RepoId) -> RepositoryResult<()> {
430 sqlx::query("delete from repositories where id = ?")
431 .bind(id.as_str())
432 .execute(&self.pool)
433 .await
434 .map_err(backend)?;
435
436 Ok(())
437 }
438}
439
440#[derive(Debug, Clone)]
441pub struct SqliteTokenRepo {
442 pool: SqlitePool,
443}
444
445impl SqliteTokenRepo {
446 pub fn new(pool: SqlitePool) -> Self {
447 Self { pool }
448 }
449
450 /// `from_trusted`, because these values were validated on the way in. Revalidating
451 /// stored rows means a tightened rule turns old rows unreadable.
452 fn hydrate(row: &SqliteRow) -> PersonalAccessToken {
453 PersonalAccessToken::from_trusted(
454 TokenId::from_trusted(row.get::<String, _>("id")),
455 UserId::from_trusted(row.get::<String, _>("user_id")),
456 row.get::<String, _>("name"),
457 row.get::<String, _>("prefix"),
458 TokenHash::from_trusted(row.get::<String, _>("token_hash")),
459 from_unix(row.get::<i64, _>("created_at")),
460 )
461 }
462}
463
464impl TokenRepository for SqliteTokenRepo {
465 async fn find_by_hash(
466 &self,
467 hash: &TokenHash,
468 ) -> RepositoryResult<Option<PersonalAccessToken>> {
469 // Matched in SQL by the hash, which is safe to compare with `=`: it is a digest,
470 // not the secret. The constant-time comparison guards the value a client
471 // presents, and that never reaches the database.
472 let row = sqlx::query("select * from tokens where token_hash = ?")
473 .bind(hash.as_str())
474 .fetch_optional(&self.pool)
475 .await
476 .map_err(backend)?;
477
478 Ok(row.as_ref().map(Self::hydrate))
479 }
480
481 async fn list_by_user(&self, user_id: &UserId) -> RepositoryResult<Vec<PersonalAccessToken>> {
482 let rows =
483 sqlx::query("select * from tokens where user_id = ? order by created_at desc, id asc")
484 .bind(user_id.as_str())
485 .fetch_all(&self.pool)
486 .await
487 .map_err(backend)?;
488
489 Ok(rows.iter().map(Self::hydrate).collect())
490 }
491
492 async fn save(&self, token: &PersonalAccessToken) -> RepositoryResult<()> {
493 sqlx::query(
494 "insert into tokens (id, user_id, name, prefix, token_hash, created_at)
495 values (?, ?, ?, ?, ?, ?)
496 on conflict (id) do update set
497 name = excluded.name",
498 )
499 .bind(token.id.as_str())
500 .bind(token.user_id.as_str())
501 .bind(&token.name)
502 .bind(&token.prefix)
503 .bind(token.token_hash.as_str())
504 .bind(to_unix(token.created_at))
505 .execute(&self.pool)
506 .await
507 .map_err(backend)?;
508
509 Ok(())
510 }
511
512 async fn delete(&self, id: &TokenId) -> RepositoryResult<()> {
513 sqlx::query("delete from tokens where id = ?")
514 .bind(id.as_str())
515 .execute(&self.pool)
516 .await
517 .map_err(backend)?;
518
519 Ok(())
520 }
521}
522
523#[cfg(test)]
524mod tests {
525 use super::*;
526
527 fn at(seconds: u64) -> SystemTime {
528 SystemTime::UNIX_EPOCH + Duration::from_secs(seconds)
529 }
530 use crate::{
531 application::{OwnerSpec, claim_instance, port::PasswordHasher},
532 domain::{SetupToken, TokenSecret},
533 infrastructure::{database::test_support::test_pool, password::StubHasher},
534 };
535
536 struct Repos {
537 users: SqliteUserRepo,
538 orgs: SqliteOrgRepo,
539 memberships: SqliteMembershipRepo,
540 }
541
542 async fn repos() -> Repos {
543 let pool = test_pool().await;
544 Repos {
545 users: SqliteUserRepo::new(pool.clone()),
546 orgs: SqliteOrgRepo::new(pool.clone()),
547 memberships: SqliteMembershipRepo::new(pool),
548 }
549 }
550
551 async fn saved_org(repos: &Repos, name: &str) -> Organization {
552 let org = Organization::new(OrgId::generate(), name, None).expect("valid org");
553 repos.orgs.save(&org).await.expect("save org");
554 org
555 }
556
557 async fn saved_user(repos: &Repos, email: &str, org: &Organization) -> User {
558 let user = User::new(
559 UserId::generate(),
560 Email::new(email).expect("valid email"),
561 PasswordHash::from_trusted("$argon2id$test"),
562 org.id.clone(),
563 );
564 repos.users.save(&user).await.expect("save user");
565 user
566 }
567
568 #[tokio::test]
569 async fn a_saved_user_round_trips() {
570 let repos = repos().await;
571 let org = saved_org(&repos, "james").await;
572 let user = saved_user(&repos, "dev@example.com", &org).await;
573
574 let found = repos
575 .users
576 .find_by_id(&user.id)
577 .await
578 .expect("lookup")
579 .expect("user should exist");
580
581 assert_eq!(found, user);
582 }
583
584 #[tokio::test]
585 async fn users_are_found_by_email_case_insensitively() {
586 let repos = repos().await;
587 let org = saved_org(&repos, "james").await;
588 saved_user(&repos, "dev@example.com", &org).await;
589
590 // Email lowercases on construction, but a row written before that rule would
591 // still need finding.
592 let found = repos
593 .users
594 .find_by_email(&Email::from_trusted("DEV@EXAMPLE.COM"))
595 .await
596 .expect("lookup");
597
598 assert!(found.is_some(), "collate nocase should make this match");
599 }
600
601 #[tokio::test]
602 async fn an_org_round_trips_with_its_display_name() {
603 let repos = repos().await;
604 let org = Organization::new(OrgId::generate(), "acme", Some("Acme".to_owned()))
605 .expect("valid org");
606 repos.orgs.save(&org).await.expect("save");
607
608 let found = repos
609 .orgs
610 .find_by_name(&org.name)
611 .await
612 .expect("lookup")
613 .expect("org should exist");
614
615 assert_eq!(found, org);
616 assert_eq!(found.label(), "Acme");
617 }
618
619 #[tokio::test]
620 async fn a_missing_org_is_none_not_an_error() {
621 let repos = repos().await;
622
623 let found = repos
624 .orgs
625 .find_by_name(&OrgName::new("nobody").unwrap())
626 .await
627 .expect("lookup");
628
629 assert_eq!(found, None);
630 }
631
632 #[tokio::test]
633 async fn a_membership_round_trips_with_its_role() {
634 let repos = repos().await;
635 let org = saved_org(&repos, "james").await;
636 let user = saved_user(&repos, "dev@example.com", &org).await;
637 let membership = Membership::new(
638 MembershipId::generate(),
639 org.id.clone(),
640 user.id.clone(),
641 Role::Owner,
642 );
643 repos.memberships.save(&membership).await.expect("save");
644
645 let found = repos
646 .memberships
647 .find(&org.id, &user.id)
648 .await
649 .expect("lookup")
650 .expect("membership should exist");
651
652 assert_eq!(found, membership);
653 assert!(found.can_write());
654 }
655
656 #[tokio::test]
657 async fn an_unreadable_role_surfaces_rather_than_downgrading_access() {
658 let repos = repos().await;
659 let pool = test_pool().await;
660 let memberships = SqliteMembershipRepo::new(pool.clone());
661 let org = Organization::new(OrgId::generate(), "james", None).expect("valid org");
662 SqliteOrgRepo::new(pool.clone())
663 .save(&org)
664 .await
665 .expect("save org");
666 let user = User::new(
667 UserId::generate(),
668 Email::new("dev@example.com").expect("valid email"),
669 PasswordHash::from_trusted("$argon2id$test"),
670 org.id.clone(),
671 );
672 SqliteUserRepo::new(pool.clone())
673 .save(&user)
674 .await
675 .expect("save user");
676
677 sqlx::query("insert into memberships (id, org_id, user_id, role) values (?, ?, ?, 'wat')")
678 .bind(MembershipId::generate().as_str())
679 .bind(org.id.as_str())
680 .bind(user.id.as_str())
681 .execute(&pool)
682 .await
683 .expect("insert");
684
685 let result = memberships.find(&org.id, &user.id).await;
686
687 assert!(
688 result.is_err(),
689 "a role we can't parse must not read as no membership"
690 );
691 drop(repos);
692 }
693
694 #[tokio::test]
695 async fn saving_a_user_before_its_org_is_refused() {
696 let repos = repos().await;
697 let orphan = User::new(
698 UserId::generate(),
699 Email::new("dev@example.com").expect("valid email"),
700 PasswordHash::from_trusted("$argon2id$test"),
701 OrgId::generate(),
702 );
703
704 let result = repos.users.save(&orphan).await;
705
706 assert!(
707 result.is_err(),
708 "the foreign key should reject a user whose org doesn't exist"
709 );
710 }
711
712 #[tokio::test]
713 async fn a_duplicate_handle_is_refused() {
714 let repos = repos().await;
715 saved_org(&repos, "james").await;
716
717 let clash = Organization::new(OrgId::generate(), "james", None).expect("valid org");
718 let result = repos.orgs.save(&clash).await;
719
720 assert!(result.is_err(), "orgs.name is unique");
721 }
722
723 #[tokio::test]
724 async fn a_differently_cased_handle_is_also_refused() {
725 let repos = repos().await;
726 saved_org(&repos, "james").await;
727
728 // OrgName lowercases, so this can only arrive via from_trusted -- but the
729 // constraint is what we're testing, not the value object.
730 let clash = Organization::from_trusted(
731 OrgId::generate(),
732 OrgName::from_trusted("JAMES"),
733 None,
734 None,
735 );
736 let result = repos.orgs.save(&clash).await;
737
738 assert!(result.is_err(), "collate nocase should catch this");
739 }
740
741 /// The claim use case checks `is_claimed` and then writes, which is TOCTOU. This
742 /// asserts the database is what actually stops a second owner being created.
743 #[tokio::test]
744 async fn a_second_claim_is_stopped_by_the_database_not_the_check() {
745 let repos = repos().await;
746 let token = SetupToken::generate();
747 let hasher = StubHasher::new();
748 let spec = OwnerSpec {
749 handle: "james".to_owned(),
750 email: "dev@example.com".to_owned(),
751 password: "hunter2".to_owned(),
752 };
753
754 claim_instance(
755 token.reveal(),
756 &token,
757 &spec,
758 &repos.users,
759 &repos.orgs,
760 &repos.memberships,
761 &hasher,
762 )
763 .await
764 .expect("first claim");
765
766 // Simulate the race: the second claimant passed is_claimed before the first
767 // committed, so it proceeds straight to the writes.
768 let intruder_org = Organization::new(OrgId::generate(), "james", None).expect("valid org");
769 let result = repos.orgs.save(&intruder_org).await;
770
771 assert!(
772 result.is_err(),
773 "unique(orgs.name) is what actually serialises concurrent claims"
774 );
775
776 let intruder_user = User::new(
777 UserId::generate(),
778 Email::new("dev@example.com").expect("valid email"),
779 hasher.hash("letmein").expect("hash"),
780 OrgId::generate(),
781 );
782 assert!(
783 repos.users.save(&intruder_user).await.is_err(),
784 "unique(users.email) closes the other half"
785 );
786 }
787 async fn org_with(orgs: &SqliteOrgRepo, name: &str) -> Organization {
788 let org = Organization::new(OrgId::generate(), name, None).expect("valid org");
789 orgs.save(&org).await.expect("save org");
790 org
791 }
792
793 async fn saved_repo(
794 repos: &SqliteRepoRepo,
795 org: &Organization,
796 name: &str,
797 visibility: Visibility,
798 ) -> Repository {
799 let repo = Repository::new(
800 RepoId::generate(),
801 org.id.clone(),
802 name,
803 None,
804 visibility,
805 at(1_000),
806 )
807 .expect("valid repo");
808 repos.save(&repo).await.expect("save repo");
809 repo
810 }
811
812 #[tokio::test]
813 async fn a_repository_round_trips() {
814 let pool = test_pool().await;
815 let orgs = SqliteOrgRepo::new(pool.clone());
816 let repos = SqliteRepoRepo::new(pool);
817 let org = org_with(&orgs, "acme").await;
818
819 let repo = Repository::new(
820 RepoId::generate(),
821 org.id.clone(),
822 "steid",
823 Some("A gitforge.".to_owned()),
824 Visibility::Private,
825 at(1_000),
826 )
827 .expect("valid repo");
828 repos.save(&repo).await.expect("save");
829
830 let found = repos
831 .find_by_id(&repo.id)
832 .await
833 .expect("lookup")
834 .expect("should exist");
835
836 assert_eq!(found, repo);
837 assert_eq!(found.visibility, Visibility::Private);
838 assert_eq!(found.description.as_deref(), Some("A gitforge."));
839 }
840
841 #[tokio::test]
842 async fn lookup_by_name_is_case_insensitive() {
843 let pool = test_pool().await;
844 let orgs = SqliteOrgRepo::new(pool.clone());
845 let repos = SqliteRepoRepo::new(pool);
846 let org = org_with(&orgs, "acme").await;
847 saved_repo(&repos, &org, "steid", Visibility::Public).await;
848
849 let found = repos
850 .find_by_org_and_name(&org.id, &RepoName::from_trusted("STEID"))
851 .await
852 .expect("lookup");
853
854 assert!(found.is_some(), "collate nocase should make this match");
855 }
856
857 #[tokio::test]
858 async fn one_owner_cannot_have_two_repositories_with_the_same_name() {
859 let pool = test_pool().await;
860 let orgs = SqliteOrgRepo::new(pool.clone());
861 let repos = SqliteRepoRepo::new(pool);
862 let org = org_with(&orgs, "acme").await;
863 saved_repo(&repos, &org, "steid", Visibility::Public).await;
864
865 let clash = Repository::new(
866 RepoId::generate(),
867 org.id.clone(),
868 "steid",
869 None,
870 Visibility::Public,
871 at(1_000),
872 )
873 .expect("valid repo");
874
875 assert!(repos.save(&clash).await.is_err(), "unique (org_id, name)");
876 }
877
878 #[tokio::test]
879 async fn two_owners_may_each_have_a_repository_of_the_same_name() {
880 let pool = test_pool().await;
881 let orgs = SqliteOrgRepo::new(pool.clone());
882 let repos = SqliteRepoRepo::new(pool);
883 let first = org_with(&orgs, "acme").await;
884 let second = org_with(&orgs, "globex").await;
885
886 saved_repo(&repos, &first, "steid", Visibility::Public).await;
887 saved_repo(&repos, &second, "steid", Visibility::Public).await;
888
889 // The constraint is a pair. On `name` alone, repository names would be globally
890 // unique across the whole installation.
891 assert_eq!(repos.list_by_org(&first.id).await.expect("list").len(), 1);
892 assert_eq!(repos.list_by_org(&second.id).await.expect("list").len(), 1);
893 }
894
895 #[tokio::test]
896 async fn a_repository_owned_by_a_missing_org_is_refused() {
897 let pool = test_pool().await;
898 let repos = SqliteRepoRepo::new(pool);
899 let orphan = Repository::new(
900 RepoId::generate(),
901 OrgId::generate(),
902 "steid",
903 None,
904 Visibility::Public,
905 at(1_000),
906 )
907 .expect("valid repo");
908
909 assert!(repos.save(&orphan).await.is_err(), "foreign key");
910 }
911
912 #[tokio::test]
913 async fn repositories_list_newest_first_breaking_ties_by_name() {
914 // The order the profile renders, and the same order `InMemoryRepoRepo` produces
915 // — the two are asserted against the same expectation on purpose.
916 let pool = test_pool().await;
917 let orgs = SqliteOrgRepo::new(pool.clone());
918 let repos = SqliteRepoRepo::new(pool);
919 let org = org_with(&orgs, "acme").await;
920
921 for (name, seconds) in [
922 ("alpha", 3_000),
923 ("older", 1_000),
924 ("newer", 2_000),
925 ("also-older", 1_000),
926 ] {
927 let mut repo = saved_repo(&repos, &org, name, Visibility::Public).await;
928 repo.updated_at = at(seconds);
929 repos.save(&repo).await.expect("restamp");
930 }
931
932 let listed = repos.list_by_org(&org.id).await.expect("list");
933
934 assert_eq!(
935 listed.iter().map(|r| r.name.as_str()).collect::<Vec<_>>(),
936 vec!["alpha", "newer", "also-older", "older"]
937 );
938 }
939
940 #[tokio::test]
941 async fn touching_moves_a_repository_to_the_supplied_time() {
942 let pool = test_pool().await;
943 let orgs = SqliteOrgRepo::new(pool.clone());
944 let repos = SqliteRepoRepo::new(pool);
945 let org = org_with(&orgs, "acme").await;
946 let repo = saved_repo(&repos, &org, "steid", Visibility::Public).await;
947
948 repos.touch(&repo.id, at(5_000)).await.expect("touch");
949
950 let found = repos
951 .find_by_id(&repo.id)
952 .await
953 .expect("lookup")
954 .expect("still there");
955 assert_eq!(found.updated_at, at(5_000));
956 assert_eq!(found.name, repo.name, "only the timestamp should move");
957 }
958
959 #[tokio::test]
960 async fn touching_a_repository_that_is_gone_succeeds() {
961 let pool = test_pool().await;
962 let repos = SqliteRepoRepo::new(pool);
963
964 assert!(repos.touch(&RepoId::generate(), at(5_000)).await.is_ok());
965 }
966
967 #[tokio::test]
968 async fn pinning_round_trips_through_an_integer_column() {
969 // SQLite has no boolean; a column read as anything but 0/1 would silently unpin.
970 let pool = test_pool().await;
971 let orgs = SqliteOrgRepo::new(pool.clone());
972 let repos = SqliteRepoRepo::new(pool);
973 let org = org_with(&orgs, "acme").await;
974 let mut repo = saved_repo(&repos, &org, "steid", Visibility::Public).await;
975
976 assert!(!repo.pinned, "a repository is not pinned by default");
977
978 repo.pinned = true;
979 repos.save(&repo).await.expect("save");
980
981 assert!(
982 repos
983 .find_by_id(&repo.id)
984 .await
985 .expect("lookup")
986 .expect("still there")
987 .pinned
988 );
989 }
990
991 /// The migration, applied to a row that predates it.
992 ///
993 /// The dev database and any deployed instance already hold repositories, and this is
994 /// the only chance to check what they look like afterwards: the table is created
995 /// with the new columns from scratch everywhere else.
996 #[tokio::test]
997 async fn the_recency_migration_leaves_existing_rows_looking_current() {
998 let pool = SqlitePool::connect("sqlite::memory:")
999 .await
1000 .expect("in-memory database should open");
1001
1002 // The table as it stood before this migration, with a repository already in it.
1003 sqlx::query(
1004 "create table repositories (
1005 id text primary key,
1006 org_id text not null,
1007 name text not null collate nocase,
1008 description text,
1009 visibility text not null,
1010 unique (org_id, name)
1011 )",
1012 )
1013 .execute(&pool)
1014 .await
1015 .expect("old schema");
1016 sqlx::query(
1017 "insert into repositories (id, org_id, name, visibility)
1018 values ('r1', 'o1', 'steid', 'public')",
1019 )
1020 .execute(&pool)
1021 .await
1022 .expect("an existing repository");
1023
1024 for statement in
1025 include_str!("../../../migrations/20260829090000_add_repository_recency.sql")
1026 .split(';')
1027 .filter(|statement| !statement.trim().is_empty())
1028 {
1029 sqlx::query(statement)
1030 .execute(&pool)
1031 .await
1032 .expect("the migration should apply");
1033 }
1034
1035 let repo = SqliteRepoRepo::new(pool)
1036 .find_by_id(&RepoId::from_trusted("r1"))
1037 .await
1038 .expect("lookup")
1039 .expect("the row survives");
1040
1041 assert!(
1042 !repo.pinned,
1043 "nothing becomes the lead without being chosen"
1044 );
1045
1046 // Stamped with the migration's own clock, not left at the epoch: an existing
1047 // repository must not read "updated 56 years ago" on the page this column exists
1048 // to order.
1049 let age = SystemTime::now()
1050 .duration_since(repo.updated_at)
1051 .expect("stamped in the past");
1052 assert!(
1053 age < Duration::from_secs(60),
1054 "expected a just-migrated timestamp, got one {age:?} old"
1055 );
1056 }
1057
1058 #[tokio::test]
1059 async fn an_unreadable_visibility_surfaces_rather_than_defaulting() {
1060 let pool = test_pool().await;
1061 let orgs = SqliteOrgRepo::new(pool.clone());
1062 let repos = SqliteRepoRepo::new(pool.clone());
1063 let org = org_with(&orgs, "acme").await;
1064
1065 sqlx::query(
1066 "insert into repositories (id, org_id, name, visibility)
1067 values ('r1', ?, 'secret', 'internal')",
1068 )
1069 .bind(org.id.as_str())
1070 .execute(&pool)
1071 .await
1072 .expect("insert");
1073
1074 assert!(
1075 repos.find_by_id(&RepoId::from_trusted("r1")).await.is_err(),
1076 "a visibility we cannot parse must not become a guess"
1077 );
1078 }
1079
1080 #[tokio::test]
1081 async fn listing_returns_only_that_org_ordered_by_name() {
1082 let pool = test_pool().await;
1083 let orgs = SqliteOrgRepo::new(pool.clone());
1084 let repos = SqliteRepoRepo::new(pool);
1085 let mine = org_with(&orgs, "acme").await;
1086 let theirs = org_with(&orgs, "globex").await;
1087
1088 for name in ["zebra", "alpha", "middle"] {
1089 saved_repo(&repos, &mine, name, Visibility::Public).await;
1090 }
1091 saved_repo(&repos, &theirs, "not-mine", Visibility::Public).await;
1092
1093 let listed = repos.list_by_org(&mine.id).await.expect("list");
1094
1095 let names: Vec<&str> = listed.iter().map(|repo| repo.name.as_str()).collect();
1096 assert_eq!(names, vec!["alpha", "middle", "zebra"]);
1097 }
1098
1099 #[tokio::test]
1100 async fn listing_includes_private_repositories() {
1101 let pool = test_pool().await;
1102 let orgs = SqliteOrgRepo::new(pool.clone());
1103 let repos = SqliteRepoRepo::new(pool);
1104 let org = org_with(&orgs, "acme").await;
1105 saved_repo(&repos, &org, "secret", Visibility::Private).await;
1106
1107 // The port returns everything; filtering by visibility is the use case's job, so
1108 // the page and /api cannot end up applying different rules.
1109 assert_eq!(repos.list_by_org(&org.id).await.expect("list").len(), 1);
1110 }
1111
1112 #[tokio::test]
1113 async fn a_missing_repository_is_none_not_an_error() {
1114 let pool = test_pool().await;
1115 let repos = SqliteRepoRepo::new(pool);
1116
1117 let found = repos
1118 .find_by_id(&RepoId::generate())
1119 .await
1120 .expect("lookup should not error");
1121
1122 assert_eq!(found, None);
1123 }
1124
1125 #[tokio::test]
1126 async fn a_deleted_repository_is_gone_and_frees_its_name() {
1127 let pool = test_pool().await;
1128 let orgs = SqliteOrgRepo::new(pool.clone());
1129 let repos = SqliteRepoRepo::new(pool);
1130 let org = org_with(&orgs, "acme").await;
1131 let repo = saved_repo(&repos, &org, "steid", Visibility::Public).await;
1132
1133 repos.delete(&repo.id).await.expect("delete");
1134
1135 assert_eq!(repos.find_by_id(&repo.id).await.expect("lookup"), None);
1136 // The unique (org_id, name) pair is what would fail if the row lingered.
1137 saved_repo(&repos, &org, "steid", Visibility::Private).await;
1138 }
1139
1140 #[tokio::test]
1141 async fn deleting_a_repository_that_is_not_there_succeeds() {
1142 // Idempotent, matching the fake: a second click must not become an error.
1143 let pool = test_pool().await;
1144 let repos = SqliteRepoRepo::new(pool);
1145
1146 assert!(repos.delete(&RepoId::generate()).await.is_ok());
1147 }
1148
1149 #[tokio::test]
1150 async fn deleting_one_repository_leaves_the_others() {
1151 let pool = test_pool().await;
1152 let orgs = SqliteOrgRepo::new(pool.clone());
1153 let repos = SqliteRepoRepo::new(pool);
1154 let org = org_with(&orgs, "acme").await;
1155 let doomed = saved_repo(&repos, &org, "alpha", Visibility::Public).await;
1156 saved_repo(&repos, &org, "zebra", Visibility::Public).await;
1157
1158 repos.delete(&doomed.id).await.expect("delete");
1159
1160 let listed = repos.list_by_org(&org.id).await.expect("list");
1161 assert_eq!(
1162 listed.iter().map(|r| r.name.as_str()).collect::<Vec<_>>(),
1163 vec!["zebra"]
1164 );
1165 }
1166
1167 // --- SqliteTokenRepo ---------------------------------------------------------
1168
1169 /// A pool plus a user to hang tokens off, since the foreign key runs that way.
1170 async fn token_fixture() -> (SqliteTokenRepo, User, User) {
1171 let pool = test_pool().await;
1172 let repos = Repos {
1173 users: SqliteUserRepo::new(pool.clone()),
1174 orgs: SqliteOrgRepo::new(pool.clone()),
1175 memberships: SqliteMembershipRepo::new(pool.clone()),
1176 };
1177
1178 let org = saved_org(&repos, "acme").await;
1179 let owner = saved_user(&repos, "owner@example.com", &org).await;
1180 let other = saved_user(&repos, "other@example.com", &org).await;
1181
1182 (SqliteTokenRepo::new(pool), owner, other)
1183 }
1184
1185 fn issued(user: &User, name: &str, at: u64) -> (PersonalAccessToken, TokenSecret) {
1186 let secret = TokenSecret::generate();
1187 let token = PersonalAccessToken::new(
1188 TokenId::generate(),
1189 user.id.clone(),
1190 name,
1191 &secret,
1192 SystemTime::UNIX_EPOCH + Duration::from_secs(at),
1193 )
1194 .expect("valid token");
1195
1196 (token, secret)
1197 }
1198
1199 #[tokio::test]
1200 async fn a_token_round_trips_through_its_hash() {
1201 let (tokens, owner, _) = token_fixture().await;
1202 let (token, secret) = issued(&owner, "laptop", 1_000);
1203 tokens.save(&token).await.expect("save");
1204
1205 let found = tokens
1206 .find_by_hash(&secret.hash())
1207 .await
1208 .expect("lookup")
1209 .expect("should be found");
1210
1211 assert_eq!(found, token);
1212 assert_eq!(found.name, "laptop");
1213 assert_eq!(found.prefix, secret.display_prefix());
1214 }
1215
1216 #[tokio::test]
1217 async fn an_unknown_hash_finds_nothing() {
1218 let (tokens, owner, _) = token_fixture().await;
1219 let (token, _) = issued(&owner, "laptop", 1_000);
1220 tokens.save(&token).await.expect("save");
1221
1222 let found = tokens
1223 .find_by_hash(&TokenSecret::generate().hash())
1224 .await
1225 .expect("lookup");
1226
1227 assert!(found.is_none());
1228 }
1229
1230 #[tokio::test]
1231 async fn tokens_are_listed_newest_first_and_only_the_users_own() {
1232 let (tokens, owner, other) = token_fixture().await;
1233
1234 for (user, name, at) in [
1235 (&owner, "old", 1_000),
1236 (&owner, "new", 3_000),
1237 (&owner, "middle", 2_000),
1238 (&other, "theirs", 4_000),
1239 ] {
1240 let (token, _) = issued(user, name, at);
1241 tokens.save(&token).await.expect("save");
1242 }
1243
1244 let listed = tokens.list_by_user(&owner.id).await.expect("list");
1245
1246 assert_eq!(
1247 listed
1248 .iter()
1249 .map(|token| token.name.as_str())
1250 .collect::<Vec<_>>(),
1251 vec!["new", "middle", "old"]
1252 );
1253 }
1254
1255 #[tokio::test]
1256 async fn deleting_a_token_stops_it_authenticating() {
1257 // Revocation is a delete, so the credential is gone rather than flagged.
1258 let (tokens, owner, _) = token_fixture().await;
1259 let (token, secret) = issued(&owner, "laptop", 1_000);
1260 tokens.save(&token).await.expect("save");
1261
1262 tokens.delete(&token.id).await.expect("delete");
1263
1264 assert!(
1265 tokens
1266 .find_by_hash(&secret.hash())
1267 .await
1268 .expect("lookup")
1269 .is_none()
1270 );
1271 }
1272
1273 #[tokio::test]
1274 async fn deleting_a_token_that_is_not_there_succeeds() {
1275 let (tokens, _, _) = token_fixture().await;
1276
1277 tokens
1278 .delete(&TokenId::generate())
1279 .await
1280 .expect("deleting nothing should not fail");
1281 }
1282
1283 #[tokio::test]
1284 async fn a_second_token_with_the_same_hash_is_refused() {
1285 // The unique constraint. Two rows answering one credential would make which
1286 // user a token authenticates depend on row order.
1287 let (tokens, owner, other) = token_fixture().await;
1288 let (first, secret) = issued(&owner, "laptop", 1_000);
1289 tokens.save(&first).await.expect("save");
1290
1291 let clash = PersonalAccessToken::new(
1292 TokenId::generate(),
1293 other.id.clone(),
1294 "clash",
1295 &secret,
1296 SystemTime::UNIX_EPOCH,
1297 )
1298 .expect("valid token");
1299
1300 assert!(tokens.save(&clash).await.is_err());
1301 }
1302
1303 #[tokio::test]
1304 async fn a_token_belonging_to_no_user_is_refused() {
1305 // The foreign key. Without `foreign_keys(true)` per connection SQLite ignores it.
1306 let (tokens, _, _) = token_fixture().await;
1307 let secret = TokenSecret::generate();
1308 let orphan = PersonalAccessToken::new(
1309 TokenId::generate(),
1310 UserId::generate(),
1311 "orphan",
1312 &secret,
1313 SystemTime::UNIX_EPOCH,
1314 )
1315 .expect("valid token");
1316
1317 assert!(tokens.save(&orphan).await.is_err());
1318 }
1319
1320 // --- sole_user ----------------------------------------------------------------
1321
1322 #[tokio::test]
1323 async fn there_is_no_sole_user_before_the_instance_is_claimed() {
1324 let repos = repos().await;
1325
1326 assert!(repos.users.sole_user().await.expect("lookup").is_none());
1327 }
1328
1329 #[tokio::test]
1330 async fn the_only_user_is_the_sole_user() {
1331 let repos = repos().await;
1332 let org = saved_org(&repos, "acme").await;
1333 let user = saved_user(&repos, "owner@example.com", &org).await;
1334
1335 let found = repos
1336 .users
1337 .sole_user()
1338 .await
1339 .expect("lookup")
1340 .expect("one user");
1341
1342 assert_eq!(found.id, user.id);
1343 }
1344
1345 #[tokio::test]
1346 async fn a_second_user_makes_the_owner_undefined_rather_than_arbitrary() {
1347 // The root redirects an anonymous visitor to "the owner". With two users that
1348 // question has no answer, and picking whichever row came back first would send
1349 // strangers to a person chosen by storage order.
1350 let repos = repos().await;
1351 let first = saved_org(&repos, "acme").await;
1352 let second = saved_org(&repos, "other").await;
1353 saved_user(&repos, "one@example.com", &first).await;
1354 saved_user(&repos, "two@example.com", &second).await;
1355
1356 assert!(repos.users.sole_user().await.expect("lookup").is_none());
1357 }
1358}