how to handle 1 to many outbox message with Kafka Outbox pattern?

Question

i have a kafka APP using outbox pattern, becoz i wanna take the advantage for deduplication by using CDC (change data capture)

The pattern workflow will look like:

1. poll message
2. begin db transaction
3. get api records
4. prepare db insert records (multiple records)
5. commit db transaction
6. commit kafka offset if step 5 success
7. rollback db transaction if step 5 fail

the problem is:

1. if 1 of the record in step 4 is duplicated in database, then step 5 will fail, and the message will not commit, it will block other success data

2. if i move the db commit to each insert, and commit the offset at the end, the fail record will never able to retry, as the offset commit

it look like outbox pattern is only fit when u consume 1 message and assume to produce 1 message

any advice when having such requirement?