create group streak schema and db scaffold

4 files changed, 78 insertions, 0 deletions

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();