| 1 |
CREATE TABLE memberships ( |
| 2 |
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), |
| 3 |
user_id UUID NOT NULL REFERENCES users(mnw_account_id) ON DELETE CASCADE, |
| 4 |
community_id UUID NOT NULL REFERENCES communities(id) ON DELETE CASCADE, |
| 5 |
role TEXT NOT NULL DEFAULT 'member' |
| 6 |
CHECK (role IN ('owner', 'moderator', 'member', 'guest')), |
| 7 |
joined_at TIMESTAMPTZ NOT NULL DEFAULT now(), |
| 8 |
|
| 9 |
UNIQUE (user_id, community_id) |
| 10 |
); |
| 11 |
|
| 12 |
CREATE INDEX idx_memberships_community ON memberships (community_id); |
| 13 |
CREATE INDEX idx_memberships_user ON memberships (user_id); |
| 14 |
|