What distribution for i.i.d. $X_i$ satisfies $\frac{\sum_i ^N X_i }{\sqrt{N}}=O(1)$? What kind of statistical properties does $X$ need to have in order to satisfy $\frac{\sum_i ^N X_i }{\sqrt{N}}=O(1)$ where $X_i$'s are i.i.d.? Zero-mean?

A distribution that has a mean of 0 and finite variance satisfies that relation.
From CLT, we have that if $X_i$ are i.i.d., then for $N\rightarrow\infty$:
\begin{equation}
\frac{1}{N}\sum_iX_i \sim \mathcal{N}(\mu, \frac{\sigma}{\sqrt{N}}),
\end{equation}
where $\mu$ and $\sigma$ are respectively the mean and standard deviation of the population. Therefore, we have:
\begin{equation}
\frac{1}{\sqrt{N}}\sum_iX_i \sim \sqrt{N}\mathcal{N}(\mu, \frac{\sigma}{\sqrt{N}})\sim\mathcal{N}(\sqrt{N}\mu, \sigma).
\end{equation}
Finally, $\mathcal{N}(\sqrt{N}\mu, \sigma)$ is of order $\mathcal{O}(\sqrt{N})$ unless $\mu$ is strictly 0 and $\sigma$ is finite. In this case we have indeed the desired relation.
Interesting note: An interesting application of this relationship is for the famous attention mechanism in deep learning. The 2017 Vaswani et al. paper uses this square-root normalization (see Eq. (1) of their paper) for their dot product in order to ensure the $\mathcal{O}(1)$ result which in their case is to avoid numerical divergences in the dot product.
