1 files changed, 179 insertions, 0 deletions
diff --git a/services/docker/mail-server/relay/db.ts b/services/docker/mail-server/relay/db.ts
new file mode 100644
index 0000000..ee8bad1
--- /dev/null
+++ b/services/docker/mail-server/relay/db.ts
@@ -0,0 +1,179 @@
+// spellchecker: words kysely insertable updateable introspector
+
+import {
+  Generated,
+  Insertable,
+  Kysely,
+  Migration,
+  Migrator,
+  SqliteDatabase,
+  SqliteDialect,
+  SqliteStatement,
+} from "kysely";
+import * as sqlite from "@db/sqlite";
+
+class SqliteStatementAdapter implements SqliteStatement {
+  constructor(public readonly stmt: sqlite.Statement) {}
+
+  get reader(): boolean {
+    return this.stmt.columnNames().length >= 1;
+  }
+
+  all(parameters: readonly unknown[]): unknown[] {
+    return this.stmt.all(...parameters as sqlite.BindValue[]);
+  }
+
+  iterate(parameters: readonly unknown[]): IterableIterator<unknown> {
+    return this.stmt.iter(...parameters as sqlite.BindValue[]);
+  }
+
+  run(
+    parameters: readonly unknown[],
+  ): { changes: number | bigint; lastInsertRowid: number | bigint } {
+    const { db } = this.stmt;
+    const totalChangesBefore = db.totalChanges;
+    const changes = this.stmt.run(...parameters as sqlite.BindValue[]);
+    return {
+      changes: totalChangesBefore === db.totalChanges ? 0 : changes,
+      lastInsertRowid: db.lastInsertRowId,
+    };
+  }
+}
+
+class SqliteDatabaseAdapter implements SqliteDatabase {
+  constructor(public readonly db: sqlite.Database) {}
+
+  prepare(sql: string): SqliteStatementAdapter {
+    return new SqliteStatementAdapter(this.db.prepare(sql));
+  }
+
+  close(): void {
+    this.db.close();
+  }
+}
+
+export class DbError extends Error {
+}
+
+const NAMES = {
+  mail: {
+    table: "mail",
+    columns: {
+      id: "id",
+      messageId: "message_id",
+      awsMessageId: "aws_message_id",
+      date: "date",
+      raw: "raw",
+    },
+  },
+} as const;
+
+interface MailTable {
+  [NAMES.mail.columns.id]: Generated<number>;
+  [NAMES.mail.columns.messageId]: string;
+  [NAMES.mail.columns.awsMessageId]: string | null;
+  [NAMES.mail.columns.date]: string | null;
+  [NAMES.mail.columns.raw]: string;
+}
+
+interface Database {
+  [NAMES.mail.table]: MailTable;
+}
+
+const migrations: Record<string, Migration> = {
+  "0001-init": {
+    // deno-lint-ignore no-explicit-any
+    async up(db: Kysely<any>): Promise<void> {
+      await db.schema
+        .createTable(NAMES.mail.table)
+        .addColumn(
+          NAMES.mail.columns.id,
+          "integer",
+          (col) => col.primaryKey().autoIncrement(),
+        )
+        .addColumn(
+          NAMES.mail.columns.messageId,
+          "text",
+          (col) => col.notNull().unique(),
+        )
+        .addColumn(
+          NAMES.mail.columns.awsMessageId,
+          "text",
+          (col) => col.unique(),
+        )
+        .addColumn(NAMES.mail.columns.date, "text")
+        .addColumn(NAMES.mail.columns.raw, "text", (col) => col.notNull())
+        .execute();
+
+      for (
+        const column of [
+          NAMES.mail.columns.messageId,
+          NAMES.mail.columns.awsMessageId,
+        ]
+      ) {
+        await db.schema
+          .createIndex(`${NAMES.mail.table}_${column}`)
+          .on(NAMES.mail.table)
+          .column(column)
+          .execute();
+      }
+    },
+
+    // deno-lint-ignore no-explicit-any
+    async down(db: Kysely<any>): Promise<void> {
+      await db.schema.dropTable(NAMES.mail.table).execute();
+    },
+  },
+};
+
+export class DbService {
+  #db;
+  #kysely;
+  #migrator;
+
+  constructor(public readonly path: string) {
+    this.#db = new sqlite.Database(path);
+    this.#kysely = new Kysely<Database>({
+      dialect: new SqliteDialect(
+        { database: new SqliteDatabaseAdapter(this.#db) },
+      ),
+    });
+    this.#migrator = new Migrator({
+      db: this.#kysely,
+      provider: {
+        getMigrations(): Promise<Record<string, Migration>> {
+          return Promise.resolve(migrations);
+        },
+      },
+    });
+  }
+
+  async migrate(): Promise<void> {
+    await this.#migrator.migrateToLatest();
+  }
+
+  async addMail(mail: Insertable<MailTable>): Promise<number> {
+    const inserted = await this.#kysely.insertInto(NAMES.mail.table).values(
+      mail,
+    ).executeTakeFirstOrThrow();
+    return Number(inserted.insertId!);
+  }
+
+  async messageIdToAws(messageId: string): Promise<string | null> {
+    const row = await this.#kysely.selectFrom(NAMES.mail.table).where(
+      NAMES.mail.columns.messageId,
+      "=",
+      messageId,
+    ).select(NAMES.mail.columns.awsMessageId).executeTakeFirst();
+    return row?.aws_message_id ?? null;
+  }
+
+  async messageIdFromAws(awsMessageId: string): Promise<string | null> {
+    const row = await this.#kysely.selectFrom(NAMES.mail.table).where(
+      NAMES.mail.columns.awsMessageId,
+      "=",
+      awsMessageId,
+    ).select(NAMES.mail.columns.messageId).executeTakeFirst();
+    return row?.message_id ?? null;
+  }
+}