3
4// User operations
5export async function createUser(user: Omit<User, "lastLoginAt">): Promise<void> {
6 await sqlite.execute(
7 `INSERT INTO users (id, email, name, isAdmin, createdAt) VALUES (?, ?, ?, ?, ?)`,
10}
11
12export async function getUserByEmail(email: string): Promise<User | null> {
13 try {
14 const result = await sqlite.execute(`SELECT * FROM users WHERE email = ?`, [email]);
30}
31
32export async function getUserById(id: string): Promise<User | null> {
33 const result = await sqlite.execute(`SELECT * FROM users WHERE id = ?`, [id]);
34 if (result.rows.length === 0) return null;
45}
46
47export async function updateUserLastLogin(userId: string): Promise<void> {
48 await sqlite.execute(
49 `UPDATE users SET lastLoginAt = ? WHERE id = ?`,
52}
53
54export async function makeUserAdmin(userId: string): Promise<void> {
55 await sqlite.execute(`UPDATE users SET isAdmin = TRUE WHERE id = ?`, [userId]);
56}
57
58export async function getAllUsers(): Promise<User[]> {
59 const result = await sqlite.execute(`SELECT * FROM users ORDER BY createdAt DESC`);
60 return result.rows.map(row => ({
69
70// WebAuthn credential operations
71export async function saveWebAuthnCredential(credential: WebAuthnCredential): Promise<void> {
72 await sqlite.execute(
73 `INSERT INTO webauthn_credentials (id, userId, credentialId, publicKey, counter, createdAt) VALUES (?, ?, ?, ?, ?, ?)`,
83}
84
85export async function getWebAuthnCredentialsByUserId(userId: string): Promise<WebAuthnCredential[]> {
86 const result = await sqlite.execute(`SELECT * FROM webauthn_credentials WHERE userId = ?`, [userId]);
87 return result.rows.map(row => ({
95}
96
97export async function getWebAuthnCredentialByCredentialId(credentialId: string): Promise<WebAuthnCredential | null> {
98 const result = await sqlite.execute(`SELECT * FROM webauthn_credentials WHERE credentialId = ?`, [credentialId]);
99 if (result.rows.length === 0) return null;
110}
111
112export async function updateWebAuthnCredentialCounter(credentialId: string, counter: number): Promise<void> {
113 await sqlite.execute(
114 `UPDATE webauthn_credentials SET counter = ? WHERE credentialId = ?`,
118
119// Challenge operations (temporary storage for WebAuthn)
120export async function saveChallenge(
121 id: string,
122 email: string,
131}
132
133export async function getChallenge(
134 email: string,
135 type: "registration" | "authentication",
148}
149
150export async function deleteChallenge(email: string, type: "registration" | "authentication"): Promise<void> {
151 await sqlite.execute(
152 `DELETE FROM webauthn_challenges WHERE email = ? AND type = ?`,
156
157// Clean up old challenges (older than 5 minutes)
158export async function cleanupOldChallenges(): Promise<void> {
159 const fiveMinutesAgo = new Date(Date.now() - 5 * 60 * 1000).toISOString();
160 await sqlite.execute(
165
166// Bearer token operations
167export async function createBearerToken(token: BearerToken): Promise<void> {
168 await sqlite.execute(
169 `INSERT INTO bearer_tokens (id, userId, token, name, createdAt) VALUES (?, ?, ?, ?, ?)`,
172}
173
174export async function getBearerTokensByUserId(userId: string): Promise<BearerToken[]> {
175 const result = await sqlite.execute(`SELECT * FROM bearer_tokens WHERE userId = ? ORDER BY createdAt DESC`, [userId]);
176 return result.rows.map(row => ({
184}
185
186export async function getUserByBearerToken(token: string): Promise<User | null> {
187 const result = await sqlite.execute(
188 `SELECT u.* FROM users u
204}
205
206export async function updateBearerTokenLastUsed(token: string): Promise<void> {
207 await sqlite.execute(
208 `UPDATE bearer_tokens SET lastUsedAt = ? WHERE token = ?`,
211}
212
213export async function deleteBearerToken(tokenId: string, userId: string): Promise<void> {
214 await sqlite.execute(
215 `DELETE FROM bearer_tokens WHERE id = ? AND userId = ?`,
219
220// Content operations
221export async function createContentItem(item: ContentItem): Promise<void> {
222 await sqlite.execute(
223 `INSERT INTO content_items (id, userId, type, content, title, metadata, createdAt) VALUES (?, ?, ?, ?, ?, ?, ?)`,
234}
235
236export async function getContentItemsByUserId(userId: string, limit = 50, offset = 0): Promise<ContentItem[]> {
237 const result = await sqlite.execute(
238 `SELECT * FROM content_items WHERE userId = ? ORDER BY createdAt DESC LIMIT ? OFFSET ?`,
250}
251
252export async function getAllContentItems(limit = 100, offset = 0): Promise<(ContentItem & { userEmail: string })[]> {
253 const result = await sqlite.execute(
254 `SELECT ci.*, u.email as userEmail FROM content_items ci
269}
270
271export async function deleteContentItem(itemId: string, userId: string): Promise<boolean> {
272 const result = await sqlite.execute(
273 `DELETE FROM content_items WHERE id = ? AND userId = ?`,
277}
278
279export async function getContentItemById(itemId: string): Promise<ContentItem | null> {
280 const result = await sqlite.execute(`SELECT * FROM content_items WHERE id = ?`, [itemId]);
281 if (result.length === 0) return null;
