Multi-Tenancy

create table public.tenants (
  id uuid primary key default gen_random_uuid(),
  name text not null check (char_length(name) between 1 and 120),
  slug text not null unique check (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$'),
  created_by uuid not null references public.profiles (id) on delete restrict,
  created_at timestamptz not null default now(),
  archived_at timestamptz
);

create index projects_tenant_idx on public.projects (tenant_id);

create or replace function public.create_tenant_with_owner(
  p_name text,
  p_slug text default null
)
returns public.tenants
language plpgsql
security definer
set search_path = public
as $$
declare
  created public.tenants;
  base_slug text;
  candidate_slug text;
  suffix integer := 2;
begin
  if auth.uid() is null then
    raise exception 'Not authenticated';
  end if;

  if p_name is null or char_length(trim(p_name)) = 0 then
    raise exception 'Tenant name is required';
  end if;

  base_slug := public.normalize_tenant_slug(coalesce(nullif(trim(p_slug), ''), p_name));
  candidate_slug := base_slug;

  loop
    begin
      insert into public.tenants (name, slug, created_by)
      values (trim(p_name), candidate_slug, auth.uid())
      returning * into created;

      exit;
    exception
      when unique_violation then
        candidate_slug := base_slug || '-' || suffix::text;
        suffix := suffix + 1;
    end;
  end loop;

  insert into public.tenant_members (tenant_id, user_id, role)
  values (created.id, auth.uid(), 'owner')
  on conflict (tenant_id, user_id) do update
  set role = 'owner';

  return created;
end;
$$;

create or replace function public.create_project_with_defaults(
  p_tenant_slug text,
  p_name text,
  p_description text
)
returns uuid

export interface TenantRepository {
  listTenants(userId: string): Promise<Tenant[]>;
  createTenantWithOwner(name: string, preferredSlug?: string): Promise<Tenant>;
}

export interface ProjectRepository {
  listProjects(userId: string, tenantSlug: string): Promise<Project[]>;
  createProjectWithDefaults(
    tenantSlug: string,
    input: CreateProjectInput
  ): Promise<Project>;
  // ...
}