Invitation log entry global unique constraint causes batch failures

[mroderick]

Invitation Log Entry Global Unique Constraint Failure

Date: 2026-04-10
Status: Fixed in PR #2560

Problem

Invitation batch #28 for Berlin chapter students (Workshop #3668) became stuck in running status after processing only 5 of 431 eligible students. The batch crashed with:

ActiveRecord::RecordInvalid: Validation failed: Entries is invalid
InvitationLogger#fail_batch(/app/app/services/invitation_logger.rb:71)

Investigation

Batch State Analysis

Workshop #3668 (Berlin Chapter):

• 563 students subscribed to "Students" group
• 133 students already had invitations (from previous "everyone" batches)
• 431 students still needed invitations
• Batch member waiting list (sessions) #28 processed only 5 students before crashing

Batch #28 Details:

• Started: 2026-04-10 08:44:14
• Status: running (stuck)
• Success count: 5
• Audience: "students"
• Only 5 log entries created (members 14756, 29864, 19137, 25821, 28609)

Root Cause

The database schema has a mismatch between the model validation and the database constraint:

Database constraint (migration line 40):

add_index :invitation_log_entries, %i[invitation_type invitation_id], unique: true

This enforces global uniqueness - each invitation can only have ONE log entry across ALL batches.

Model validation:

validates :member_id, uniqueness: { scope: %i[invitation_type invitation_id] }, allow_nil: true

This validates (member_id, invitation_type, invitation_id) is unique - each member can only have one entry per invitation.

The constraint is MORE restrictive than the validation.

Failure Sequence

1. A previous batch (e.g., "everyone" batch Moves seats from sessions to sponsor #22, digital-science #24, Add gittip #25, or add spots available email #26) created log entries for some invitations
2. Batch member waiting list (sessions) #28 ("students") starts processing Berlin students
3. For each student, find_or_build_entry attempts to create a log entry
4. When the database INSERT hits the unique constraint violation, find_or_create_by raises ActiveRecord::RecordNotUnique
5. The exception triggers fail_batch(e) in the rescue block
6. fail_batch tries to update the log: @log.update!(status: :failed, ...)
7. Rails validates all entries in @log.entries, including the unsaved invalid entry
8. Validation fails: "Entries is invalid"
9. The log remains stuck in running status

Why the Constraint Was Wrong

1. Multiple audiences: Different batches (students, coaches, everyone) may process overlapping member lists
2. Retry scenarios: Failed batches can be retried, requiring re-logging of entries
3. Within-batch uniqueness: Already handled by find_or_create_by(member: member, invitation: invitation) + processed_at check in the PR fix: handle duplicate InvitationLogEntry on retry (v2) #2558 fix

The global constraint prevents legitimate use cases:

• Member subscribed to both "Students" and "Coaches" groups (sends to both groups)
• Retrying a failed batch (entries can be re-logged)
• Running separate "students" and "coaches" batches for the same workshop

Solution

Migration: Remove Global Unique Constraint

Remove the overly restrictive unique index and add a batch-scoped index for performance.

Model: Remove Validation

The validation matched the wrong constraint. Within-batch uniqueness is enforced by find_or_create_by on @log.entries.

Post-Deployment Actions

1. Clear stuck batch: UPDATE invitation_logs SET status = 'failed' WHERE id = 28;
2. Verify affected workshops: SELECT id, loggable_id, audience, status, success_count FROM invitation_logs WHERE status = 'running';
3. Trigger new invitation batches for any workshops with stuck batches

Related Issues

• PR fix: handle duplicate InvitationLogEntry on retry (v2) #2558: Handle duplicate InvitationLogEntry on retry (introduced find_or_create_by + processed_at check)
• PR fix(invitations): remove overly restrictive unique constraint on invitation log entries #2560: Fix for this constraint issue

Lessons Learned

1. Database constraints must match business logic: The global unique constraint was more restrictive than the model validation, creating a hidden bug
2. Test cross-batch scenarios: Tests only covered within-batch uniqueness, not cross-batch logging
3. Index naming matters: Random hash suffixes in index names (_6d6ef495e6) make debugging harder
4. Consider retry semantics early: If batches can be retried, log entries must allow duplicates across batches