Disclaimer: while I looked into the design and implementation of ciphersweet back when it was published (or at least when I became aware of it), I didn't perform a full-blown audit & proof of security of the design, and I didn't look into the PHP implementation in depth (in large parts because I do not do much PHP work). Do not mistake this for an audit report (I only make those when I'm paid for it :P)
Ciphersweet uses a pretty sound, boring design; Scott explains it, along with Cyphersweet's security claims, in his answer so I won't go over it again.
The main point of Ciphersweet, IMO, is that it is safer and harder to mess-up than the alternatives.
The “duplicate entry leak” you mention does not apply to full records (those are encrypted with standard, non-deterministic encryption) but to the blind indexes:
if you index on, say, HIV status, then someone with read access to the database can figure out which records have the same HIV status, and from there likely recover HIV status for all records.
This is an information leak that is fundamental to blind indexes: if you have enough information to
SELECT all rows with a given (function of the) HIV status, you have enough information to check whether any 2 rows have the same HIV status, so fancier cryptography will not help there (incl. using deterministic encryption, order-preserving/revealing encryption, ...).
The good news is that, unlike other designs (like order-revealing encryption), keyed hashes (under an unknown key) do not reveal any more information than whether the values are equal.
Obviously, it is not sufficient (as shown with the HIV status example), so there are 3 main mitigations you can use (and Ciphersweet support them all):
the most obvious is not to add blind indexes on very-sensitive data: if you do not want to expose HIV status data, why are you creating an index for efficiently querying on them?
Use compound indexes: if the data you need to index on is too low-entropy to be safely put in a blind index (say, HIV status), you can hash it together with some other data (the documentation provides uses the SSN in that example), and support
SELECTing records with a given HIV status and a given SSN.
This is, IMO, the less-useful option, since you can directly
SELECT by SSN (assuming you have a blind index over SSNs) and then check the HIV status in the decrypted record. Reserve it for cases where either you cannot have an index over one of the fields (because they are all too low-entropy and/or high-sensitivity).
truncate the HMAC value, so as to reduce the information leak: let's say you have patient records, all with a unique name, and support selecting by it . I could check whether a certain patient exists by adding a record (through the application) with that name, then checking the database for a second record with the same name hash, even if the application itself wouldn't grant me the permission to search for patients by name.
With a truncated hash, you can make it so that each lookup in the blind index returns (on average) a small number of records; say, if you want avg. 3 records per query, out of 1 000 000 records, you would want a hash with size log2(10⁶/3) ~ 18 bits. This makes the scenario I described impossible.
I do not think Ciphersweet provides particular support for evolving the size of the blind indexes as the size of your database increases, though it should be doable. Thankfully, the only issue with not resizing a blind index as the database grows, is a slight performance overhead: if your DB gets 10× bigger and now contains 10 000 000 records, keeping the same 18 bit blind index results in avg. 30 records getting selected, which the application then decrypts and filters; decrypting 30 records to find the one you are interested in should still be quite fast.
 In a real-world use-case, you would probably support selecting by a normalized (lowercased, stripped of punctuation) version of the name; Ciphersweet supports functional indexes.
TL;DR: Ciphersweet is safe, probably much more so than most alternatives; there are some caveats you need to be aware of, which exists in all encrypted databases, and some operational concerns, but they are all very manageable.