1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
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)';
|
