1 files changed, 126 insertions, 0 deletions
diff --git a/services/docker/mail-server/aws-sendmail/db.ts b/services/docker/mail-server/aws-sendmail/db.ts
new file mode 100644
index 0000000..05cc7b5
--- /dev/null
+++ b/services/docker/mail-server/aws-sendmail/db.ts
@@ -0,0 +1,126 @@
+// spellchecker: words sqlocal kysely insertable updateable
+
+import { SQLocalKysely } from "sqlocal/kysely";
+import {
+  Generated,
+  Insertable,
+  Kysely,
+  Migration,
+  Migrator,
+  Selectable,
+  Updateable,
+} from "kysely";
+
+const tableNames = {
+  mail: {
+    table: "mail",
+    columns: {
+      id: "id",
+      messageId: "message_id",
+      awsMessageId: "aws_message_id",
+      rawMail: "raw_mail",
+    },
+  },
+} as const;
+
+interface MailTable {
+  [tableNames.mail.columns.id]: Generated<number>;
+  [tableNames.mail.columns.messageId]: string;
+  [tableNames.mail.columns.awsMessageId]: string | null;
+  [tableNames.mail.columns.rawMail]: string;
+}
+
+export type Mail = Selectable<MailTable>;
+export type NewMail = Insertable<MailTable>;
+export type MailUpdate = Updateable<MailTable>;
+
+interface Database {
+  [tableNames.mail.table]: MailTable;
+}
+
+const migrations: Record<string, Migration> = {
+  "0001-init": {
+    // deno-lint-ignore no-explicit-any
+    async up(db: Kysely<any>): Promise<void> {
+      const names = tableNames.mail;
+
+      await db.schema
+        .createTable(names.table)
+        .addColumn(
+          names.columns.id,
+          "integer",
+          (col) => col.primaryKey().autoIncrement(),
+        )
+        .addColumn(
+          names.columns.messageId,
+          "text",
+          (col) => col.notNull().unique(),
+        )
+        .addColumn(names.columns.awsMessageId, "text", (col) => col.unique())
+        .addColumn(names.columns.rawMail, "text", (col) => col.notNull())
+        .execute();
+
+      for (
+        const column of [names.columns.messageId, names.columns.awsMessageId]
+      ) {
+        await db.schema
+          .createIndex(`${names.table}_${column}`)
+          .on(names.table)
+          .column(column)
+          .execute();
+      }
+    },
+
+    // deno-lint-ignore no-explicit-any
+    async down(db: Kysely<any>): Promise<void> {
+      await db.schema.dropTable(tableNames.mail.table).execute();
+    },
+  },
+};
+
+export class DbService {
+  private _sqlocal;
+  private _db;
+  private _migrator;
+
+  constructor(public readonly path: string) {
+    this._sqlocal = new SQLocalKysely("database.sqlite3");
+    const db = new Kysely<Database>({ dialect: this._sqlocal.dialect });
+    this._db = db;
+    this._migrator = new Migrator({
+      db,
+      provider: {
+        getMigrations(): Promise<Record<string, Migration>> {
+          return Promise.resolve(migrations);
+        },
+      },
+    });
+  }
+
+  async migrate(): Promise<void> {
+    await this._migrator.migrateToLatest();
+  }
+
+  async addMail(mail: NewMail): Promise<void> {
+    await this._db.insertInto(tableNames.mail.table).values(mail)
+      .executeTakeFirstOrThrow();
+  }
+
+  async messageIdToAws(messageId: string): Promise<string | null> {
+    const row = await this._db.selectFrom(tableNames.mail.table).where(
+      tableNames.mail.columns.messageId,
+      "=",
+      messageId,
+    ).select(tableNames.mail.columns.awsMessageId).executeTakeFirst();
+    return row?.aws_message_id ?? null;
+  }
+
+  async messageIdFromAws(awsMessageId: string): Promise<string | null> {
+    const row = await this._db.selectFrom(tableNames.mail.table).where(
+      tableNames.mail.columns.awsMessageId,
+      "=",
+      awsMessageId,
+    ).select(tableNames.mail.columns.messageId).executeTakeFirst();
+    return row?.message_id ?? null;
+  }
+}