29 lines
1.4 KiB
MySQL
29 lines
1.4 KiB
MySQL
|
-- Add invitations and join_requests tables for organization management
|
||
|
|
|
||
|
|
CREATE TABLE invitations (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
||
|
|
invited_by UUID NOT NULL REFERENCES users(id),
|
||
|
|
username TEXT NOT NULL, -- username of the invited user
|
||
|
|
role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
expires_at TIMESTAMP WITH TIME ZONE DEFAULT (NOW() + INTERVAL '7 days'),
|
||
|
|
accepted_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
UNIQUE(org_id, username) -- prevent duplicate invites for same user in org
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE join_requests (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
||
|
|
user_id UUID NOT NULL REFERENCES users(id),
|
||
|
|
invite_token TEXT, -- optional, if from invite link
|
||
|
|
requested_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'accepted', 'rejected')),
|
||
|
|
UNIQUE(org_id, user_id) -- prevent duplicate requests
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Index for faster lookups
|
||
|
|
CREATE INDEX idx_invitations_org_id ON invitations(org_id);
|
||
|
|
CREATE INDEX idx_invitations_username ON invitations(username);
|
||
|
|
CREATE INDEX idx_join_requests_org_id ON join_requests(org_id);
|
||
|
|
CREATE INDEX idx_join_requests_user_id ON join_requests(user_id);
|