From 5e3a28bafde748367a0566d739e1c098af2c0a0f Mon Sep 17 00:00:00 2001
From: Mathias Magnusson <mathias@magnusson.space>
Date: Mon, 18 Aug 2025 23:50:03 +0200
Subject: Group <-> User memberships

---
 src/db/schema.ts | 25 ++++++++++++++++++++-----
 1 file changed, 20 insertions(+), 5 deletions(-)

(limited to 'src/db/schema.ts')

diff --git a/src/db/schema.ts b/src/db/schema.ts
index 339dfc0..affe19f 100644
--- a/src/db/schema.ts
+++ b/src/db/schema.ts
@@ -1,11 +1,15 @@
 import { relations, sql } from "drizzle-orm";
-import { text, sqliteTable, integer } from "drizzle-orm/sqlite-core";
+import { text, sqliteTable, integer, primaryKey } from "drizzle-orm/sqlite-core";
 
 export const groupTable = sqliteTable("groups", {
     id: integer().primaryKey(),
     name: text().notNull(),
 });
 
+export const groupRelations = relations(groupTable, ({ many }) => ({
+    members: many(groupMembershipTable),
+}));
+
 export const userTable = sqliteTable("users", {
     id: integer().primaryKey(),
     name: text().unique().notNull(),
@@ -13,6 +17,20 @@ export const userTable = sqliteTable("users", {
     passkeyId: text("passkey_id").notNull(),
 });
 
+export const userRelations = relations(userTable, ({ many }) => ({
+    groups: many(groupMembershipTable),
+}));
+
+export const groupMembershipTable = sqliteTable("users_groups", {
+    userId: integer("user_id").notNull().references(() => userTable.id),
+    groupId: integer("group_id").notNull().references(() => groupTable.id),
+}, table => [primaryKey({ columns: [table.userId, table.groupId] })]);
+
+export const groupMembershipRelations = relations(groupMembershipTable, ({ one }) => ({
+    group: one(groupTable, { fields: [groupMembershipTable.groupId], references: [groupTable.id] }),
+    user: one(userTable, { fields: [groupMembershipTable.userId], references: [userTable.id] }),
+}));
+
 export const sessionTable = sqliteTable("sessions", {
     id: integer().primaryKey(),
     uuid: text().unique().notNull(),
@@ -21,10 +39,7 @@ export const sessionTable = sqliteTable("sessions", {
 });
 
 export const sessionRelations = relations(sessionTable, ({ one }) => ({
-    user: one(userTable, {
-        fields: [sessionTable.userId],
-        references: [userTable.id],
-    }),
+    user: one(userTable, { fields: [sessionTable.userId], references: [userTable.id] }),
 }));
 
 export const webauthnChallenges = sqliteTable("webauthn_challenges", {
-- 
cgit v1.2.3