Does Kafka not get you halfway there? It will guarantee exactly once semantics. Use MSK or Confluent cloud if you can use managed services.

It’s a more future proof than building this on top of Postgres.