馃 distributed transcription service
thistle.dunkirk.sh
dunkirk.sh
1import { Database } from "bun:sqlite";
2import { afterEach, beforeEach, expect, test } from "bun:test";
3
4let testDb: Database;
5
6beforeEach(() => {
7 testDb = new Database(":memory:");
8
9 testDb.run(`
10 CREATE TABLE users (
11 id INTEGER PRIMARY KEY AUTOINCREMENT,
12 email TEXT UNIQUE NOT NULL,
13 password_hash TEXT,
14 name TEXT,
15 avatar TEXT DEFAULT 'd',
16 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
17 role TEXT NOT NULL DEFAULT 'user'
18 )
19 `);
20
21 testDb.run(`
22 CREATE TABLE passkeys (
23 id TEXT PRIMARY KEY,
24 user_id INTEGER NOT NULL,
25 credential_id TEXT NOT NULL UNIQUE,
26 public_key TEXT NOT NULL,
27 counter INTEGER NOT NULL DEFAULT 0,
28 transports TEXT,
29 name TEXT,
30 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
31 last_used_at INTEGER,
32 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
33 )
34 `);
35});
36
37afterEach(() => {
38 testDb.close();
39});
40
41test("admin can update user name", async () => {
42 const result = testDb.run(
43 "INSERT INTO users (email, password_hash, name, avatar) VALUES (?, ?, ?, ?)",
44 ["test@example.com", "password123", "Old Name", "avatar1"],
45 );
46
47 const userId = Number(result.lastInsertRowid);
48
49 testDb.run("UPDATE users SET name = ? WHERE id = ?", ["New Name", userId]);
50
51 const user = testDb
52 .query<{ name: string }, [number]>("SELECT name FROM users WHERE id = ?")
53 .get(userId);
54
55 expect(user?.name).toBe("New Name");
56});
57
58test("admin can update user password", async () => {
59 const result = testDb.run(
60 "INSERT INTO users (email, password_hash) VALUES (?, ?)",
61 ["test@example.com", "password123"],
62 );
63
64 const userId = Number(result.lastInsertRowid);
65
66 testDb.run("UPDATE users SET password_hash = ? WHERE id = ?", [
67 "newpassword456",
68 userId,
69 ]);
70
71 const user = testDb
72 .query<{ password_hash: string }, [number]>(
73 "SELECT password_hash FROM users WHERE id = ?",
74 )
75 .get(userId);
76
77 expect(user?.password_hash).toBe("newpassword456");
78});
79
80test("admin can view user passkeys", async () => {
81 const result = testDb.run(
82 "INSERT INTO users (email, password_hash) VALUES (?, ?)",
83 ["test@example.com", "password123"],
84 );
85
86 const userId = Number(result.lastInsertRowid);
87
88 testDb.run(
89 "INSERT INTO passkeys (id, user_id, credential_id, public_key, counter) VALUES (?, ?, ?, ?, ?)",
90 ["pk1", userId, "cred1", "pubkey1", 0],
91 );
92
93 testDb.run(
94 "INSERT INTO passkeys (id, user_id, credential_id, public_key, counter) VALUES (?, ?, ?, ?, ?)",
95 ["pk2", userId, "cred2", "pubkey2", 0],
96 );
97
98 const passkeys = testDb
99 .query<{ id: string }, [number]>(
100 "SELECT id FROM passkeys WHERE user_id = ?",
101 )
102 .all(userId);
103
104 expect(passkeys.length).toBe(2);
105});
106
107test("admin can revoke user passkey", async () => {
108 const result = testDb.run(
109 "INSERT INTO users (email, password_hash) VALUES (?, ?)",
110 ["test@example.com", "password123"],
111 );
112
113 const userId = Number(result.lastInsertRowid);
114
115 testDb.run(
116 "INSERT INTO passkeys (id, user_id, credential_id, public_key, counter) VALUES (?, ?, ?, ?, ?)",
117 ["pk1", userId, "cred1", "pubkey1", 0],
118 );
119
120 let passkeys = testDb
121 .query<{ id: string }, [number]>(
122 "SELECT id FROM passkeys WHERE user_id = ?",
123 )
124 .all(userId);
125 expect(passkeys.length).toBe(1);
126
127 testDb.run("DELETE FROM passkeys WHERE id = ? AND user_id = ?", [
128 "pk1",
129 userId,
130 ]);
131
132 passkeys = testDb
133 .query<{ id: string }, [number]>(
134 "SELECT id FROM passkeys WHERE user_id = ?",
135 )
136 .all(userId);
137 expect(passkeys.length).toBe(0);
138});
139
140test("updating password clears user sessions", async () => {
141 testDb.run(`
142 CREATE TABLE sessions (
143 id TEXT PRIMARY KEY,
144 user_id INTEGER NOT NULL,
145 ip_address TEXT,
146 user_agent TEXT,
147 created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
148 expires_at INTEGER NOT NULL,
149 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
150 )
151 `);
152
153 const result = testDb.run(
154 "INSERT INTO users (email, password_hash) VALUES (?, ?)",
155 ["test@example.com", "password123"],
156 );
157
158 const userId = Number(result.lastInsertRowid);
159
160 const expiresAt = Math.floor(Date.now() / 1000) + 3600;
161 testDb.run(
162 "INSERT INTO sessions (id, user_id, expires_at) VALUES (?, ?, ?)",
163 ["session1", userId, expiresAt],
164 );
165
166 let sessions = testDb
167 .query<{ id: string }, [number]>(
168 "SELECT id FROM sessions WHERE user_id = ?",
169 )
170 .all(userId);
171 expect(sessions.length).toBe(1);
172
173 testDb.run("UPDATE users SET password_hash = ? WHERE id = ?", [
174 "newpassword",
175 userId,
176 ]);
177 testDb.run("DELETE FROM sessions WHERE user_id = ?", [userId]);
178
179 sessions = testDb
180 .query<{ id: string }, [number]>(
181 "SELECT id FROM sessions WHERE user_id = ?",
182 )
183 .all(userId);
184 expect(sessions.length).toBe(0);
185});