1 files changed, 53 insertions, 0 deletions
diff --git a/makima/migrations/20250110000002_create_groups_tables.sql b/makima/migrations/20250110000002_create_groups_tables.sql
new file mode 100644
index 0000000..88e00a1
--- /dev/null
+++ b/makima/migrations/20250110000002_create_groups_tables.sql
@@ -0,0 +1,53 @@
+-- Create groups table for organizing users under an owner
+CREATE TABLE IF NOT EXISTS groups (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    owner_id UUID NOT NULL REFERENCES owners(id) ON DELETE CASCADE,
+    name VARCHAR(255) NOT NULL,
+    description TEXT,
+    -- True for the auto-created personal group on signup
+    is_default BOOLEAN NOT NULL DEFAULT false,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+
+    -- Each owner can only have one group with a given name
+    UNIQUE(owner_id, name)
+);
+
+CREATE INDEX idx_groups_owner_id ON groups(owner_id);
+CREATE INDEX idx_groups_is_default ON groups(is_default) WHERE is_default = true;
+
+CREATE TRIGGER update_groups_updated_at
+    BEFORE UPDATE ON groups
+    FOR EACH ROW
+    EXECUTE FUNCTION update_updated_at_column();
+
+-- Create group_members junction table
+CREATE TABLE IF NOT EXISTS group_members (
+    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    group_id UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
+    -- References Supabase auth.users(id) via our users table
+    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    -- Role within the group
+    role VARCHAR(32) NOT NULL DEFAULT 'member',
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+
+    -- Each user can only be in a group once
+    UNIQUE(group_id, user_id),
+
+    CONSTRAINT valid_role CHECK (role IN ('owner', 'admin', 'member'))
+);
+
+CREATE INDEX idx_group_members_group_id ON group_members(group_id);
+CREATE INDEX idx_group_members_user_id ON group_members(user_id);
+CREATE INDEX idx_group_members_role ON group_members(role);
+
+CREATE TRIGGER update_group_members_updated_at
+    BEFORE UPDATE ON group_members
+    FOR EACH ROW
+    EXECUTE FUNCTION update_updated_at_column();
+
+COMMENT ON TABLE groups IS 'Groups organize users under an owner for access control';
+COMMENT ON COLUMN groups.is_default IS 'True for the auto-created personal group on signup';
+COMMENT ON TABLE group_members IS 'Junction table for user membership in groups';
+COMMENT ON COLUMN group_members.role IS 'Role in group: owner (full control), admin (manage members), member (access only)';