32 lines
1.2 KiB
SQL
32 lines
1.2 KiB
SQL
CREATE TYPE public.membership_role AS ENUM ('owner', 'admin', 'member', 'viewer');
|
|
|
|
CREATE TABLE public.profiles (
|
|
id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
email text NOT NULL,
|
|
display_name text,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE public.tenants (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name text NOT NULL,
|
|
slug text NOT NULL,
|
|
status text NOT NULL DEFAULT 'active',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT tenants_slug_format_check CHECK (slug ~ '^[a-z0-9]([a-z0-9-]*[a-z0-9])?$'),
|
|
CONSTRAINT tenants_status_check CHECK (status IN ('active', 'suspended'))
|
|
);
|
|
|
|
CREATE UNIQUE INDEX tenants_slug_key ON public.tenants (slug);
|
|
|
|
CREATE TABLE public.memberships (
|
|
profile_id uuid NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
|
|
tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
|
|
role public.membership_role NOT NULL DEFAULT 'member',
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (profile_id, tenant_id)
|
|
);
|
|
|
|
CREATE INDEX memberships_tenant_id_idx ON public.memberships (tenant_id);
|
|
CREATE INDEX memberships_profile_id_idx ON public.memberships (profile_id);
|