From b0db0e55c0c400bc948a4934b0e4fddfbfdcca51 Mon Sep 17 00:00:00 2001
From: Jan Tuomi <jan@jantuomi.fi>
Date: Tue, 7 Oct 2025 13:04:03 +0300
Subject: Rewrite to run as a traditional script, update deps

---
 src/db.ts | 143 +++++++++++++++++++++++++++++++++++++++++++++++---------------
 1 file changed, 110 insertions(+), 33 deletions(-)

(limited to 'src/db.ts')

diff --git a/src/db.ts b/src/db.ts
index f649d2d..511b519 100644
--- a/src/db.ts
+++ b/src/db.ts
@@ -1,9 +1,23 @@
-import {
-  createPool,
-  QueryResultType,
-  sql,
-} from "slonik";
-import config from "./config";
+/* SQLite3 persistence layer replacing previous PG / Slonik implementation.
+ *
+ * Features:
+ * - Uses better-sqlite3 (synchronous, high-performance, safe for WAL mode)
+ * - Separate "write" connection (single) and a small pool of readonly connections for parallel reads
+ * - Applies requested PRAGMAs on every connection
+ * - Uses BEGIN IMMEDIATE transactions for write operations
+ * - STRICT tables (requires SQLite 3.37+)
+ *
+ * Environment:
+ *   SQLITE_PATH (optional) - path to DB file. Defaults to ./data/hommabot.db
+ *
+ * Schema (mirrors previous PG schema):
+ *   active_chats(id INTEGER PRIMARY KEY)
+ *   permitted_users(id INTEGER PRIMARY KEY)
+ */
+
+import fs from "fs";
+import path from "path";
+import Database, { Database as BetterSqliteDb } from "better-sqlite3";
 
 interface ActiveChat {
   id: number;
@@ -13,30 +27,93 @@ interface PermittedUser {
   id: number;
 }
 
-const connection = createPool(config.pgConnectionUri);
-
-export const getActiveChats = async (): Promise<readonly ActiveChat[]> =>
-  connection.any(sql`
-    SELECT *
-    FROM active_chats
-  `);
-
-export const addActiveChat = async (id: number): Promise<QueryResultType<void>> =>
-  connection.query<void>(sql`
-    INSERT INTO active_chats
-    (id) VALUES
-    (${id})
-    ON CONFLICT DO NOTHING
-  `);
-
-export const removeActiveChat = async (id: number): Promise<QueryResultType<void>> =>
-  connection.query<void>(sql`
-    DELETE FROM active_chats
-    WHERE id = ${id}
-  `);
-
-export const getPermittedUsers = async (): Promise<readonly PermittedUser[]> =>
-  connection.any(sql`
-    SELECT *
-    FROM permitted_users
-  `);
+const DB_FILE =
+  process.env.SQLITE_PATH || path.join(process.cwd(), "data", "hommabot.db");
+
+ensurePath();
+const db = new Database(DB_FILE, {
+  fileMustExist: false,
+  readonly: false,
+});
+db.pragma("journal_mode = WAL");
+db.pragma("busy_timeout = 5000");
+db.pragma("synchronous = NORMAL");
+db.pragma("cache_size = 1000000000");
+db.pragma("foreign_keys = ON");
+db.pragma("temp_store = MEMORY");
+
+const ddl = `
+  BEGIN IMMEDIATE;
+  CREATE TABLE IF NOT EXISTS active_chats (
+    id INTEGER PRIMARY KEY
+  ) STRICT;
+
+  CREATE TABLE IF NOT EXISTS permitted_users (
+    id INTEGER PRIMARY KEY
+  ) STRICT;
+  COMMIT;
+`;
+db.exec(ddl);
+
+/**
+ * Initialize directory for DB file if required.
+ */
+function ensurePath() {
+  const dir = path.dirname(DB_FILE);
+  if (!fs.existsSync(dir)) {
+    fs.mkdirSync(dir, { recursive: true });
+  }
+}
+
+/**
+ * Wrap a write operation in a BEGIN IMMEDIATE transaction.
+ */
+function withWriteTx<T>(fn: (db: BetterSqliteDb) => T): T {
+  const begin = db.prepare("BEGIN IMMEDIATE");
+  const commit = db.prepare("COMMIT");
+  const rollback = db.prepare("ROLLBACK");
+
+  begin.run();
+  try {
+    const result = fn(db);
+    commit.run();
+    return result;
+  } catch (err) {
+    try {
+      rollback.run();
+    } catch {
+      /* ignore */
+    }
+    throw err;
+  }
+}
+
+/* Public API (mirrors old Slonik-based version) */
+
+export const getActiveChats = async (): Promise<readonly ActiveChat[]> => {
+  const stmt = db.prepare("SELECT id FROM active_chats");
+  return stmt.all() as ActiveChat[];
+};
+
+export const addActiveChat = async (id: number): Promise<void> => {
+  withWriteTx((db) => {
+    const stmt = db.prepare(
+      "INSERT OR IGNORE INTO active_chats (id) VALUES (?)",
+    );
+    stmt.run(id);
+  });
+};
+
+export const removeActiveChat = async (id: number): Promise<void> => {
+  withWriteTx((db) => {
+    const stmt = db.prepare("DELETE FROM active_chats WHERE id = ?");
+    stmt.run(id);
+  });
+};
+
+export const getPermittedUsers = async (): Promise<
+  readonly PermittedUser[]
+> => {
+  const stmt = db.prepare("SELECT id FROM permitted_users");
+  return stmt.all() as PermittedUser[];
+};
-- 
cgit v1.3