steid
Sign in

@jamesgill /

steid

feat: sqlite migrations for the identity schema

orgs, users, memberships, applied at startup by sqlx::migrate! so the binary
carries its own schema and there's no separate deploy step to forget.

Topcoat has no persistence layer -- sixteen crates and none of them touch a
database, and the session docs say outright that storage and schema are ours.
That suits the repository ports, which already sit at exactly that seam.

Two things worth knowing:

SQLite ignores foreign keys unless asked, per connection. Without
foreign_keys(true) the references in this schema would be documentation
rather than constraints, and the write ordering the use cases are careful
about would stop being enforced at all. There's a test that inserts a user
pointing at a nonexistent org and asserts it's refused -- if that ever
passes, the setting has been lost.

sqlx migrate add stamps versions to the second, so three invocations in one
second produced three identical versions. Renamed to distinct ones; with
equal versions the apply order between tables that reference each other
would have been ambiguous.

Unique on orgs.name and users.email is what actually serialises two
simultaneous claims -- the is_claimed check in the use case is TOCTOU on its
own.

Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
JamesPatrickGill authored 1 month agoparentc1a35b0Browse filese51b80c7276478bb00d4d8dcb8b111c7aac5bb9d

6 files changed+134 −4

Cargo.lock+2 −0View file
@@ -1734,6 +1734,7 @@ dependencies = [
17341734 "bitflags",
17351735 "byteorder",
17361736 "bytes",
1737+ "crc",
17371738 "digest 0.11.3",
17381739 "dotenvy",
17391740 "either",
@@ -1760,6 +1761,7 @@ dependencies = [
17601761 "base64 0.22.1",
17611762 "bitflags",
17621763 "byteorder",
1764+ "crc",
17631765 "dotenvy",
17641766 "etcetera",
17651767 "futures-channel",
Cargo.toml+1 −1View file
@@ -9,7 +9,7 @@ dotenvy = "0.15.7"
99 envy = "0.4.2"
1010 rand = "0.10.2"
1111 serde = { version = "1.0.229", features = ["derive"] }
12−sqlx = { version = "0.9.0", default-features = false, features = ["runtime-tokio", "sqlite", "macros"] }
12+sqlx = { version = "0.9.0", default-features = false, features = ["runtime-tokio", "sqlite", "macros", "migrate"] }
1313 subtle = "2.6.1"
1414 tokio = { version = "1.53.1", features = ["rt-multi-thread", "macros"] }
1515 topcoat = "0.5.0"
migrations/20260804093030_create_orgs.sql+11 −0View file
@@ -0,0 +1,11 @@
1+-- Organisations own everything. Every user gets a personal one, whose name is their
2+-- public handle and the {owner} segment of every URL.
3+--
4+-- `name` is unique and case-insensitive: OrgName lowercases on the way in, and nocase
5+-- stops a differently-cased duplicate slipping past that. This constraint is also what
6+-- actually serialises two simultaneous claims of an unclaimed instance.
7+create table orgs (
8+ id text primary key,
9+ name text not null unique collate nocase,
10+ display_name text
11+);
migrations/20260804093031_create_users.sql+8 −0View file
@@ -0,0 +1,8 @@
1+-- Users reference their personal organisation, so orgs must exist first. This is the
2+-- foreign key our use cases order their writes around.
3+create table users (
4+ id text primary key,
5+ email text not null unique collate nocase,
6+ password_hash text not null,
7+ personal_org_id text not null references orgs (id)
8+);
migrations/20260804093032_create_memberships.sql+11 −0View file
@@ -0,0 +1,11 @@
1+-- Links a user to an organisation with a role. The unique pair stops a user holding
2+-- two roles in the same organisation.
3+create table memberships (
4+ id text primary key,
5+ org_id text not null references orgs (id),
6+ user_id text not null references users (id),
7+ role text not null,
8+ unique (org_id, user_id)
9+);
10+
11+create index memberships_user_id on memberships (user_id);
src/infrastructure/database.rs+101 −3View file
@@ -1,10 +1,108 @@
1+use std::str::FromStr;
2+
13 use sqlx::SqlitePool;
2−use sqlx::sqlite::SqlitePoolOptions;
4+use sqlx::sqlite::{SqliteConnectOptions, SqlitePoolOptions};
35
4−/// Opens the SQLite pool.
6+/// Opens the SQLite pool and brings the schema up to date.
57 ///
68 /// The pool is cheap to clone and shared by reference across every request, so it is
79 /// registered once as Topcoat app context rather than rebuilt per handler.
810 pub async fn connect(database_url: &str) -> Result<SqlitePool, sqlx::Error> {
9− SqlitePoolOptions::new().connect(database_url).await
11+ // SQLite ignores foreign keys unless asked, per connection. Without this the
12+ // references in the schema are documentation rather than constraints, and the
13+ // ordering our use cases are careful about stops being enforced at all.
14+ let options = SqliteConnectOptions::from_str(database_url)?.foreign_keys(true);
15+
16+ let pool = SqlitePoolOptions::new().connect_with(options).await?;
17+
18+ migrate(&pool).await?;
19+
20+ Ok(pool)
21+}
22+
23+/// Applies any migrations the database has not seen.
24+///
25+/// Embedded at compile time, so the binary carries its own schema and there is no
26+/// separate migration step to forget on deploy.
27+pub async fn migrate(pool: &SqlitePool) -> Result<(), sqlx::Error> {
28+ sqlx::migrate!("./migrations")
29+ .run(pool)
30+ .await
31+ .map_err(|error| sqlx::Error::Migrate(Box::new(error)))
32+}
33+
34+#[cfg(test)]
35+pub(crate) mod test_support {
36+ use super::*;
37+
38+ /// A migrated, isolated in-memory database for tests.
39+ ///
40+ /// `:memory:` is per-connection, so the pool is capped at one connection —
41+ /// otherwise each checkout gets its own empty database and writes vanish.
42+ pub async fn test_pool() -> SqlitePool {
43+ let options = SqliteConnectOptions::from_str("sqlite::memory:")
44+ .expect("in-memory url should parse")
45+ .foreign_keys(true);
46+
47+ let pool = SqlitePoolOptions::new()
48+ .max_connections(1)
49+ .connect_with(options)
50+ .await
51+ .expect("in-memory database should open");
52+
53+ migrate(&pool).await.expect("migrations should apply");
54+
55+ pool
56+ }
57+}
58+
59+#[cfg(test)]
60+mod tests {
61+ use super::*;
62+ use sqlx::Row;
63+
64+ #[tokio::test]
65+ async fn migrations_apply_to_a_fresh_database() {
66+ let pool = test_support::test_pool().await;
67+
68+ let tables: Vec<String> =
69+ sqlx::query("select name from sqlite_master where type = 'table'")
70+ .fetch_all(&pool)
71+ .await
72+ .expect("query")
73+ .into_iter()
74+ .map(|row| row.get::<String, _>("name"))
75+ .collect();
76+
77+ for expected in ["orgs", "users", "memberships"] {
78+ assert!(
79+ tables.iter().any(|name| name == expected),
80+ "expected a {expected} table, got {tables:?}"
81+ );
82+ }
83+ }
84+
85+ #[tokio::test]
86+ async fn foreign_keys_are_enforced() {
87+ let pool = test_support::test_pool().await;
88+
89+ // A user pointing at an org that doesn't exist must be refused. If this ever
90+ // passes, `foreign_keys(true)` has been lost and the schema's references have
91+ // quietly become decorative.
92+ let result = sqlx::query(
93+ "insert into users (id, email, password_hash, personal_org_id)
94+ values ('u1', 'dev@example.com', 'hash', 'org-that-does-not-exist')",
95+ )
96+ .execute(&pool)
97+ .await;
98+
99+ assert!(result.is_err(), "expected the foreign key to be enforced");
100+ }
101+
102+ #[tokio::test]
103+ async fn migrations_are_idempotent() {
104+ let pool = test_support::test_pool().await;
105+
106+ migrate(&pool).await.expect("re-running should be a no-op");
107+ }
10108 }