summaryrefslogtreecommitdiff
path: root/api/db
diff options
context:
space:
mode:
Diffstat (limited to 'api/db')
-rw-r--r--api/db/_addAuthenticator.ts32
-rw-r--r--api/db/_addUser.ts20
-rw-r--r--api/db/_getAuthenticator.ts29
-rw-r--r--api/db/_getAuthenticators.ts26
-rw-r--r--api/db/_getCurrentChallenge.ts24
-rw-r--r--api/db/_getUser.ts29
-rw-r--r--api/db/_setCurrentChallenge.ts35
-rw-r--r--api/db/_tables.ts50
-rw-r--r--api/db/_updateAuthenticator.ts23
9 files changed, 268 insertions, 0 deletions
diff --git a/api/db/_addAuthenticator.ts b/api/db/_addAuthenticator.ts
new file mode 100644
index 0000000..ad76037
--- /dev/null
+++ b/api/db/_addAuthenticator.ts
@@ -0,0 +1,32 @@
+import type Authenticator from '../types/Authenticator';
+
+import { sql } from '@vercel/postgres';
+import { AUTHENTICATORS } from './_tables';
+
+export default async function addAuthenticator(authenticator: Authenticator) {
+ try {
+ await sql.query(`
+ INSERT INTO ${AUTHENTICATORS.name} (
+ ${AUTHENTICATORS.fields.id},
+ ${AUTHENTICATORS.fields.backedUp},
+ ${AUTHENTICATORS.fields.counter},
+ ${AUTHENTICATORS.fields.deviceType},
+ ${AUTHENTICATORS.fields.publicKey}
+ ${AUTHENTICATORS.fields.transports},
+ ${AUTHENTICATORS.fields.type},
+ ${AUTHENTICATORS.fields.userId}
+ ) VALUES (
+ ${authenticator.id},
+ ${authenticator.backedUp},
+ ${authenticator.counter},
+ ${authenticator.deviceType},
+ ${authenticator.publicKey}
+ { ${authenticator.transports.join(', ')} },
+ ${authenticator.type},
+ ${authenticator.userId}
+ );
+ `);
+ } catch (err) {
+ throw new Error(`Failed to insert authenticator into database: ${JSON.stringify(authenticator)}.`, err);
+ }
+} \ No newline at end of file
diff --git a/api/db/_addUser.ts b/api/db/_addUser.ts
new file mode 100644
index 0000000..ef72447
--- /dev/null
+++ b/api/db/_addUser.ts
@@ -0,0 +1,20 @@
+import type User from '../types/User';
+
+import { sql } from '@vercel/postgres';
+import { USERS } from './_tables';
+
+export default async function addUser(user: User) {
+ try {
+ await sql.query(`
+ INSERT INTO ${USERS.name} (
+ ${USERS.fields.id},
+ ${USERS.fields.displayName}
+ ) VALUES (
+ '${user.id}',
+ '${user.displayName}'
+ );
+ `);
+ } catch (err) {
+ throw new Error(`Failed to insert user into database: ${JSON.stringify(user)}.`, err);
+ }
+} \ No newline at end of file
diff --git a/api/db/_getAuthenticator.ts b/api/db/_getAuthenticator.ts
new file mode 100644
index 0000000..55c4b2f
--- /dev/null
+++ b/api/db/_getAuthenticator.ts
@@ -0,0 +1,29 @@
+import { sql } from '@vercel/postgres';
+import { AUTHENTICATORS } from './_tables';
+import Authenticator from '../types/Authenticator';
+
+export default async function getAuthenticator<
+ T extends keyof Authenticator
+>(id: string, fields: T[]): Promise<Pick<Authenticator, T>> {
+ try {
+ const result = await sql.query(`
+ SELECT ${fields.join(', ')}
+ FROM ${AUTHENTICATORS.name}
+ WHERE ${AUTHENTICATORS.fields.id} = '${id}';
+ `);
+
+ const row = result.rows.at(0);
+ if (!row) {
+ throw new Error(`No authenticator with ID ${id} found in database.`);
+ }
+
+ const authenticator: Partial<Pick<Authenticator, T>> = {};
+ for (const field in fields) {
+ // Assume type conversion is already done by node-postgres
+ authenticator[field] = row[AUTHENTICATORS.fields[field]];
+ }
+ return authenticator as Pick<Authenticator, T>;
+ } catch (err) {
+ throw new Error(`Failed to get authenticator with ID ${id} from database.`, err);
+ }
+} \ No newline at end of file
diff --git a/api/db/_getAuthenticators.ts b/api/db/_getAuthenticators.ts
new file mode 100644
index 0000000..c718989
--- /dev/null
+++ b/api/db/_getAuthenticators.ts
@@ -0,0 +1,26 @@
+import { sql } from '@vercel/postgres';
+import { AUTHENTICATORS } from './_tables';
+import Authenticator from '../types/Authenticator';
+
+export default async function getAuthenticators<
+ T extends keyof Authenticator
+>(userId: string, fields: T[]): Promise<Pick<Authenticator, T>[]> {
+ try {
+ const result = await sql.query(`
+ SELECT ${fields.join(', ')}
+ FROM ${AUTHENTICATORS.name}
+ WHERE ${AUTHENTICATORS.fields.userId} = '${userId}';
+ `);
+
+ return result.rows.map((row) => {
+ const authenticator: Partial<Pick<Authenticator, T>> = {};
+ for (const field in fields) {
+ // Assume type conversion is already done by node-postgres
+ authenticator[field] = row[AUTHENTICATORS.fields[field]];
+ }
+ return authenticator as Pick<Authenticator, T>;
+ });
+ } catch (err) {
+ throw new Error(`Failed to get authenticators for user with ID ${userId} from database.`, err);
+ }
+} \ No newline at end of file
diff --git a/api/db/_getCurrentChallenge.ts b/api/db/_getCurrentChallenge.ts
new file mode 100644
index 0000000..ec82e4d
--- /dev/null
+++ b/api/db/_getCurrentChallenge.ts
@@ -0,0 +1,24 @@
+import { QueryResultRow, sql } from '@vercel/postgres';
+import { CURRENT_CHALLENGES } from './_tables';
+
+export default async function getCurrentChallenge(userId: string): Promise<string | null> {
+ try {
+ const result = await sql.query(`
+ SELECT ${CURRENT_CHALLENGES.fields.currentChallenge}
+ FROM ${CURRENT_CHALLENGES.name}
+ WHERE ${CURRENT_CHALLENGES.fields.userId} = '${userId}';
+ `);
+
+ if (!result.rows.length) {
+ return null;
+ }
+
+ const row: QueryResultRow = result.rows.at(0);
+
+ // If the result is NULL in Postgres, it should be JavaScript `null` here.
+ // See https://github.com/brianc/node-postgres/blob/master/packages/pg/lib/result.js
+ return row[CURRENT_CHALLENGES.fields.currentChallenge];
+ } catch (err) {
+ throw new Error(`Failed to get current challenge for user with ID ${userId} from database.`, err);
+ }
+} \ No newline at end of file
diff --git a/api/db/_getUser.ts b/api/db/_getUser.ts
new file mode 100644
index 0000000..30a879e
--- /dev/null
+++ b/api/db/_getUser.ts
@@ -0,0 +1,29 @@
+import { QueryResultRow, sql } from '@vercel/postgres';
+import User from '../types/User';
+import { USERS } from './_tables';
+
+export default async function getUser(
+ userId: string
+): Promise<Pick<User, 'id' | 'displayName'> | null> {
+ try {
+ const result = await sql.query(`
+ SELECT
+ ${USERS.fields.displayName}
+ FROM ${USERS.name}
+ WHERE ${USERS.fields.id} = '${userId}';
+ `);
+
+ if (!result.rows.length) {
+ return null;
+ }
+
+ const row: QueryResultRow = result.rows.at(0);
+
+ return {
+ id: userId,
+ displayName: row[USERS.fields.displayName],
+ };
+ } catch (err) {
+ throw new Error(`Failed to get user with ID ${userId} from database.`, err);
+ }
+} \ No newline at end of file
diff --git a/api/db/_setCurrentChallenge.ts b/api/db/_setCurrentChallenge.ts
new file mode 100644
index 0000000..7d9450b
--- /dev/null
+++ b/api/db/_setCurrentChallenge.ts
@@ -0,0 +1,35 @@
+import { sql } from '@vercel/postgres';
+import { CURRENT_CHALLENGES } from './_tables';
+
+export default async function setCurrentChallenge(userId: string, challenge: string) {
+ try {
+ const insert = `
+ INSERT INTO ${CURRENT_CHALLENGES.name} (
+ ${CURRENT_CHALLENGES.fields.userId},
+ ${CURRENT_CHALLENGES.fields.currentChallenge}
+ ) VALUES (
+ ${userId},
+ ${challenge}
+ );
+ `);
+
+ const update = `
+ UPDATE TABLE ${CURRENT_CHALLENGES.name}
+ SET ${CURRENT_CHALLENGES.fields.currentChallenge} = ${challenge}
+ WHERE ${CURRENT_CHALLENGES.fields.userId} = '${userId}';
+ `);
+
+ await sql.query(`
+ BEGIN
+ IF EXISTS (
+ SELECT ${CURRENT_CHALLENGES.fields.userId} FROM ${CURRENT_CHALLENGES.name}
+ WHERE ${CURRENT_CHALLENGES.fields.userId} = ${userId}
+ )
+ ${update};
+ ELSE
+ ${insert};
+ `
+ } catch (err) {
+ throw new Error(`Failed to set current challenge for user with ID ${userId} in database.`, err);
+ }
+} \ No newline at end of file
diff --git a/api/db/_tables.ts b/api/db/_tables.ts
new file mode 100644
index 0000000..8ec5cb5
--- /dev/null
+++ b/api/db/_tables.ts
@@ -0,0 +1,50 @@
+import type Authenticator from '../types/Authenticator';
+import type Subscription from '../types/Subscription';
+import type User from '../types/User';
+
+import env from '../_env';
+
+const prefix = env.ENVIRONMENT === 'prod' ? '' : 'dev_';
+
+type TableSchema<Model> = {
+ name: string,
+ fields: { [key in keyof Model]: string },
+};
+
+export const AUTHENTICATORS: TableSchema<Authenticator> = {
+ name: `${prefix}authenticators`,
+ fields: {
+ id: 'id',
+ backedUp: 'backed_up',
+ counter: 'counter',
+ deviceType: 'device_type',
+ publicKey: 'public_key',
+ transports: 'transports',
+ type: 'type',
+ userId: 'user_id',
+ },
+};
+
+export const CURRENT_CHALLENGES: TableSchema<{ userId: User['id'], currentChallenge: string }> = {
+ name: `${prefix}current_challenges`,
+ fields: {
+ userId: 'user_id',
+ currentChallenge: 'current_challenge',
+ },
+};
+
+export const SUBSCRIPTIONS: TableSchema<Subscription> = {
+ name: `${prefix}subscriptions`,
+ fields: {
+ id: 'id',
+ emailAddress: 'email_address',
+ },
+};
+
+export const USERS: TableSchema<User> = {
+ name: `${prefix}users`,
+ fields: {
+ id: 'id',
+ displayName: 'display_name',
+ },
+};
diff --git a/api/db/_updateAuthenticator.ts b/api/db/_updateAuthenticator.ts
new file mode 100644
index 0000000..45d094c
--- /dev/null
+++ b/api/db/_updateAuthenticator.ts
@@ -0,0 +1,23 @@
+import type Authenticator from '../types/Authenticator';
+
+import { sql } from '@vercel/postgres';
+import { AUTHENTICATORS } from './_tables';
+
+export default async function updateAuthenticator<
+ T extends keyof Omit<Authenticator, 'id'>
+>(id: string, updatedFields: Pick<Authenticator, T>): Promise<void> {
+ try {
+ const assignments = Object.entries(updatedFields)
+ .filter(([key]) => key !== 'id') // never update the ID
+ .map(([key, value]) => `${key}=${value}`)
+ .join(', ');
+
+ await sql.query(`
+ UPDATE ${AUTHENTICATORS.name}
+ SET ${assignments}
+ WHERE ${AUTHENTICATORS.fields.id} = '${id}';
+ `);
+ } catch (err) {
+ throw new Error(`Failed to update authenticator with ID ${id} in database.`, err);
+ }
+}
generated by cgit v1.2.3 (git 2.25.1) at 2026-09-19 21:21:24 +0000