From 1f52e962086856ddd6981139e2d4aca20201e9cb Mon Sep 17 00:00:00 2001 From: Pinapelz Date: Mon, 3 Aug 2026 12:34:55 -0700 Subject: create group streak schema and db scaffold --- server/db/schema.ts | 42 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 42 insertions(+) create mode 100644 server/db/schema.ts (limited to 'server/db/schema.ts') 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 +); +`); -- cgit v1.2.3