Writing categorical raster with dbf file does not contain pixel counts

I am trying to write a rasterize vector file with dbf file using the following code

library(terra)
library(foreign)

f <- system.file("ex/lux.shp", package="terra")
v <- vect(f)
plot(v)
r <- rast(system.file("ex/elev.tif", package="terra"))
y <- rasterize(v, r, "NAME_2")

plot(y)

writeRaster(y, filename="Categorical_raster.tif", overwrite=TRUE)
write.dbf(levels(y)[[1]], file = "Categorical_raster.tif.vat.dbf")

But when I am opening the raster file using QGIS, the pixel counts are missing from the raster attribute table.

How do I have the pixel counts in the raster attribute table?

While the attribute table is missing in ArcGIS.

You want to add another categorical variable to a SpatRaster. Specifically you want to count the number of raster cells for each category. In that case, you need to compute this, and add it to the categories data.frame. With your example data

library(terra)
f <- system.file("ex/lux.shp", package="terra")
v <- vect(f)
r <- rast(system.file("ex/elev.tif", package="terra"))
y <- rasterize(v, r, "NAME_2")

Now get the existing categories, and compute and add the variable of interest. Then save the file to disk with writeRaster.

x <- cats(y)[[1]]
f <- freq(y)
i <- match(f$value, x$NAME_2)
x$cellcount <- f$count[i]
levels(y) <- x
 
out <- writeRaster(y, "test.tif", overwrite=TRUE)

You should not add your own dbf file.