Building a Circular Gift Assignment System with PostgreSQL Constraints

T'was the week before Christmas (Don't worry the rest of the blog post won't rhyme). And the game night group hadn't done their Secret Santa yet. I brought it up in the group chat and the excuses started to come in on why we couldn't meet to exchange names for a secret santa.

That's when I decided to look at the services that already existed and then decide to build by my own secret santa app.

The Business Rules: What Makes Secret Santa Tricky

I really think this idea would be a wonderful interview question because on the surface the task seems easy.

Write down a list of people. Generate who should get what gift and send a notification out so people know. But there are a lot of small coding challenges that come into play.

• What happens when someone tries to sign up after the names have been generated?
• What about exclusions?

While the first one is easy (Add a constraint on signing up for a group after the deadline). That second one is harder than it seems. I thought of a couple ideas but they were either expensive for larger groups, or they opened then opportunity to lock someone from being able to match. Here were the core requirements.

1. No self-assignments - You can't buy a gift for yourself
2. One giver per person - Each person gives to exactly one recipient
3. Circular chain structure - A→B→C→D→A (everyone receives exactly one gift)
4. Exclusion rules - Family members/couples shouldn't be assigned to each other

This is why it's so hard. Without exclusions, there is N! (or n-factorial) number of permutations. Every exclusion reduces that number of valid options significantly but how do we generate one of the perfect lists (or more like avoid the not so perfect ones.)

I don't fully trust my or AI's code on this so the best thing we can do is ensure that the assignments are constrained at the database level.

Database Constraints: Your Safety Net

The first constraint is the easiest. You cannot give to yourself.

ALTER TABLE events_assignment
ADD CONSTRAINT no_self_assignment
CHECK (NOT (giver_id = receiver_id));

The second constraint also makes sense. One Gift Per Person (UniqueConstraint)

ALTER TABLE events_assignment
    ADD CONSTRAINT unique_giver_per_event UNIQUE (event_id, giver_id);

This ensures each participant is assigned to give to exactly one person per event.

Another unique constraint. One Participant Per Email (UniqueConstraint)

ALTER TABLE event_participants
    ADD CONSTRAINT unique_registration UNIQUE (event_id, participant_id);

This prevents (ideally) duplicate registrations and ensures everyone gets exactly one gift. Could someone register twice... Yes but...

Why Not Put exclusions in the Database as constraints?

I couldn't create exclusion rules as constraints because there would be so many!! Also, users add/remove exclusions at different times (I can't make the exclusion for my wife until after she registers for the group). The exclusions are also reciprocal so if I can't be assigned to her, she also cannot be assigned to me.

So we just make an exclusions table and add the reciprocal exclusions to the table.

What about the signup constraint? This is an interesting challenge but not one that we will implement on the database side at all. Simply put once the assignments are given, knowingly adding another person would need to trigger a reassignment. This would involve sending a message to each person (hoping they didn't already buy something), and then deleting all the assignments in the database, then getting their assignment again. It's a complicated process and one that should be manually triggered. So if someone attempts to sign up after the deadline. They should be warned and the administrator of the group should begin the reassignment process manually.

The Assignment Algorithm

This is what we really wanted to see right? Database constraints handle what's invalid, but application code determines what's optimal.

It seems it's actually faster on average to just randomly shuffle the groups and check for issues. Obviously this becomes a little more of a challenge if there are a lot of exclusions but is less of a challenge then trying to permute all the possibilities and then iterate through them.

def _generate_assignments(self, event, participants, max_retries=1000):
    # Build exclusion map from M2M relationships
    exclusion_map = {}
    for participant in participants:
        excluded_ids = set(participant.exclusions.values_list('id', flat=True))
        exclusion_map[participant.id] = excluded_ids

    for attempt in range(max_retries):
        shuffled = participants.copy()
        random.shuffle(shuffled)

        # Create circular chain: i gives to i+1, last gives to first
        assignments = []
        for i, giver in enumerate(shuffled):
            receiver = shuffled[(i + 1) % len(shuffled)]

            # Check exclusion rules
            if receiver.id in exclusion_map.get(giver.id, set()):
                break  # Invalid, retry with new shuffle

            assignments.append(Assignment(event=event, giver=giver, receiver=receiver))

        # Try to save - database constraints will validate
        try:
            with transaction.atomic():
                Assignment.objects.bulk_create(assignments)
            return True
        except IntergrityError:
         return False
        except Exception:    
            continue  # Constraint violation, retry

    return False

Testing Concurrent Assignment Generation

This works but could be slow for large groups or large inspection groups. What’s faster than a script randomly trying random permutations? MANY threads trying random permutations at the same time.

from django.db import transaction
from concurrent.futures import ThreadPoolExecutor

def test_concurrent_assignment_prevents_duplicates():
    """Only one assignment generation should succeed."""
    event = Event.objects.create(...)
    participants = [Participant.objects.create(...) for _ in range(3)]

    def generate():
        # Try to generate assignments
        return view._generate_assignments(event, participants)

    with ThreadPoolExecutor(max_workers=3) as executor:
        results = list(executor.map(generate, range(3)))

    # Only one should succeed
    assert results.count(True) == 1
    # Check database has exactly one set of assignments
    assert Assignment.objects.filter(event=event).count() == 3

When to Use Database Constraints vs. Application Logic

Decision Matrix

The Secret Santa Example

Our implementation uses:

• ✓ CHECK constraint preventing self-assignments
• ✓ UNIQUE constraints enforcing one-to-one relationships
• ✓ Python algorithm handling complex circular chain logic
• ✓ ManyToMany relationships for flexible exclusion rules
• ✓ Transactions ensuring all-or-nothing assignment generation

You can check out the project on my github repo.

You can use this approach to unwrap the complexity of many business logic problems. By treating the database as the ultimate source of truth and using application code to find an optimal solution, you ensure that even if the code goes naughty (tries to insert invalid data), the database stays nice (rejects it). So, as you prepare for your own holiday gatherings, may your code be bug-free, your assignments be valid, and your Secret Santa be tree--tastic! Happy holidays!