create group streak schema and db scaffold

7 files changed, 90 insertions, 0 deletions

diff --git a/.gitignore b/.gitignore
index b66d881..1266017 100644
--- a/.gitignore
+++ b/.gitignore
@@ -38,3 +38,4 @@ yarn-error.log*
 TODO
 dist
 .env
+k-heardle.db
diff --git a/package.json b/package.json
index 043b809..0c02cf9 100644
--- a/package.json
+++ b/package.json
@@ -65,6 +65,7 @@
   "devDependencies": {
     "@babel/core": "^7.29.7",
     "@babel/plugin-proposal-private-property-in-object": "^7.21.11",
+    "@types/better-sqlite3": "^9.6.0",
     "@types/cors": "^2.8.19",
     "@types/express": "^5.0.6",
     "@types/lodash": "^4.17.25",
diff --git a/pnpm-lock.yaml b/pnpm-lock.yaml
index 66654b2..790da68 100644
--- a/pnpm-lock.yaml
+++ b/pnpm-lock.yaml
@@ -93,6 +93,9 @@ importers:
       '@babel/plugin-proposal-private-property-in-object':
         specifier: ^7.21.11
         version: 7.21.11(@babel/core@7.29.7(supports-color@10.2.2))(supports-color@10.2.2)
+      '@types/better-sqlite3':
+        specifier: ^9.6.0
+        version: 9.6.0
       '@types/cors':
         specifier: ^2.8.19
         version: 2.8.19
@@ -609,6 +612,9 @@ packages:
   '@types/aria-query@5.0.4':
     resolution: {integrity: sha512-rfT93uj5s0PRL7EzccGMs3brplhcrghnDoV26NqKhCAS1hVo+WdNsPvE/yb6ilfr5hi2MEk6d5EWJTKdxg8jVw==}
 
+  '@types/better-sqlite3@9.6.0':
+    resolution: {integrity: sha512-ZEEwBSgMu7GYJOynoagg5X9JbxfL6dTJDsgViJIqh67jV44kyOr9RXfmFjLK5rzC4MWssP06t9hu/JwGDnUbCg==}
+
   '@types/body-parser@1.19.6':
     resolution: {integrity: sha512-HLFeCYgz89uk22N5Qg3dvGvsv46B8GLvKKo1zKG4NybA8U2DiEO3w9lqGg29t/tfLRJpJ6iQxnVw4OnB7MoM9g==}
 
@@ -2546,6 +2552,10 @@ snapshots:
 
   '@types/aria-query@5.0.4': {}
 
+  '@types/better-sqlite3@9.6.0':
+    dependencies:
+      '@types/node': 25.9.5
+
   '@types/body-parser@1.19.6':
     dependencies:
       '@types/connect': 3.4.38
diff --git a/server/db/groups.ts b/server/db/groups.ts
new file mode 100644
index 0000000..1060501
--- /dev/null
+++ b/server/db/groups.ts
@@ -0,0 +1,27 @@
+import { db } from "./index";
+import crypto from "node:crypto";
+
+export function createGroup(name: string) {
+    const id = crypto.randomUUID();
+    const joinCode = crypto
+        .randomBytes(3)
+        .toString("hex")
+        .toUpperCase();
+
+    db.prepare(`
+        INSERT INTO groups
+        (id, name, join_code, created_at)
+        VALUES (?, ?, ?, ?)
+    `).run(
+        id,
+        name,
+        joinCode,
+        Date.now()
+    );
+
+    return {
+        id,
+        name,
+        joinCode
+    };
+}
diff --git a/server/db/index.ts b/server/db/index.ts
new file mode 100644
index 0000000..e8d0392
--- /dev/null
+++ b/server/db/index.ts
@@ -0,0 +1,8 @@
+import Database from "better-sqlite3";
+import path from "node:path";
+
+const dbPath = path.join(process.cwd(), "k-heardle.db");
+
+export const db = new Database(dbPath);
+
+db.pragma("foreign_keys = ON");
diff --git a/server/db/schema.ts b/server/db/schema.ts
new file mode 100644
index 0000000..8fc2532
--- /dev/null
+++ b/server/db/schema.ts
@@ -0,0 +1,42 @@
+import { db } from "./index";
+
+db.exec(`
+CREATE TABLE IF NOT EXISTS groups (
+    id TEXT PRIMARY KEY,
+    name TEXT NOT NULL,
+    join_code TEXT UNIQUE NOT NULL,
+    current_streak INTEGER NOT NULL DEFAULT 0,
+    last_completed TEXT,
+    created_at INTEGER NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS group_members (
+    group_id TEXT NOT NULL,
+    username TEXT NOT NULL,
+    joined_at INTEGER NOT NULL,
+
+    PRIMARY KEY (group_id, username),
+
+    FOREIGN KEY(group_id) REFERENCES groups(id) ON DELETE CASCADE
+);
+
+CREATE TABLE IF NOT EXISTS daily_solves (
+    group_id TEXT NOT NULL,
+    username TEXT NOT NULL,
+    date TEXT NOT NULL,
+    solved INTEGER NOT NULL,
+    attempts INTEGER NOT NULL,
+    PRIMARY KEY (group_id, username, date),
+    FOREIGN KEY(group_id) REFERENCES groups(id) ON DELETE CASCADE
+);
+
+CREATE TABLE IF NOT EXISTS mv_solves (
+    group_id TEXT NOT NULL,
+    username TEXT NOT NULL,
+    date TEXT NOT NULL,
+    solved INTEGER NOT NULL,
+    attempts INTEGER NOT NULL,
+    PRIMARY KEY (group_id, username, date),
+    FOREIGN KEY(group_id) REFERENCES groups(id) ON DELETE CASCADE
+);
+`);
diff --git a/server/index.ts b/server/index.ts
index 5799881..b076de0 100644
--- a/server/index.ts
+++ b/server/index.ts
@@ -3,6 +3,7 @@ import cors from "cors";
 import dotenv from "dotenv";
 import { dailyRouter } from "./daily";
 import { selectRouter } from "./select";
+import "./db/schema"
 
 dotenv.config();