pguerrerox
1f7737e5cb
Normalize the UI with shared primitives, add a workspace-backed account surface, and improve authenticated mobile navigation, map behavior, and dashboard browsing.
60 lines
2.1 KiB
SQL
60 lines
2.1 KiB
SQL
create table if not exists public.workspaces (
|
|
id uuid primary key default gen_random_uuid(),
|
|
name text not null,
|
|
workspace_type text not null check (workspace_type in ('personal', 'company')),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists public.workspace_memberships (
|
|
id uuid primary key default gen_random_uuid(),
|
|
workspace_id uuid not null references public.workspaces (id) on delete cascade,
|
|
user_id uuid not null references public.users (id) on delete cascade,
|
|
role text not null check (role in ('owner', 'admin', 'member')),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint workspace_memberships_workspace_user_key unique (workspace_id, user_id)
|
|
);
|
|
|
|
create index if not exists workspace_memberships_user_id_idx on public.workspace_memberships (user_id);
|
|
create index if not exists workspace_memberships_workspace_id_idx on public.workspace_memberships (workspace_id);
|
|
|
|
drop trigger if exists set_workspaces_updated_at on public.workspaces;
|
|
create trigger set_workspaces_updated_at
|
|
before update on public.workspaces
|
|
for each row
|
|
execute function public.set_updated_at();
|
|
|
|
drop trigger if exists set_workspace_memberships_updated_at on public.workspace_memberships;
|
|
create trigger set_workspace_memberships_updated_at
|
|
before update on public.workspace_memberships
|
|
for each row
|
|
execute function public.set_updated_at();
|
|
|
|
do $$
|
|
declare
|
|
workspace_user record;
|
|
next_workspace_id uuid;
|
|
begin
|
|
for workspace_user in
|
|
select u.id, u.email, u.display_name
|
|
from public.users u
|
|
where not exists (
|
|
select 1
|
|
from public.workspace_memberships wm
|
|
where wm.user_id = u.id
|
|
)
|
|
loop
|
|
insert into public.workspaces (name, workspace_type)
|
|
values (
|
|
concat(coalesce(nullif(workspace_user.display_name, ''), split_part(workspace_user.email, '@', 1), 'User'), '''s Workspace'),
|
|
'personal'
|
|
)
|
|
returning id into next_workspace_id;
|
|
|
|
insert into public.workspace_memberships (workspace_id, user_id, role)
|
|
values (next_workspace_id, workspace_user.id, 'owner');
|
|
end loop;
|
|
end
|
|
$$;
|