One person pays, and the room keeps a fence around it

A client-generated id only prevents duplicates if the server treats it as the primary key of the write, and the usual failure is that it does not.

Check what the replay endpoint does with the id. If it inserts a new row and stores the client id in a column beside it, then two devices replaying the same visit produce two rows with two different primary keys and one matching client id, which is exactly what you are seeing once a week. The fix is a unique constraint on that column and an upsert rather than an insert.

The second place to look is what the client does after a successful replay. If it clears the queue entry on the response rather than on an acknowledgement it has actually persisted, a reconnect during the response window replays the same entry again. That produces the same symptom from a different cause, and the unique constraint fixes it too.

The recommended shape is two statements rather than one.

First CREATE UNIQUE INDEX CONCURRENTLY on the column. That builds the index without taking a lock that blocks writes, at the cost of two passes over the table and a build that can fail part way and leave an invalid index behind, which you then drop and retry.

Then ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX, which adopts the index you already built instead of building a second one. That statement does take a brief exclusive lock, but it is a catalogue update rather than a scan, so it is short.

One thing to do before either: find the duplicates you already have. The concurrent build will fail at the end if any exist, after doing all of the work.

This gives you one row per duplicated client id, with the job it belongs to and how many copies exist:

select client_entry_id,
       min(job_id)              as job_id,
       count(*)                 as copies,
       min(created_at)          as first_seen,
       max(created_at)          as last_seen
  from visit_records
 where client_entry_id is not null
 group by client_entry_id
having count(*) > 1
 order by copies desc, last_seen desc;

Two notes before you run it. min(job_id) assumes the copies share a job; if they do not, that assumption is itself a finding, so run it once with count(distinct job_id) in place of min(job_id) and see whether any row comes back above 1.

And rows where client_entry_id is null are a separate problem. Those came from a client old enough not to have sent one, and no unique constraint will help them.