Constraints and Upserts as Concurrency Control

def subscribe(customer, plan):
    if Subscription.objects.filter(customer=customer, status="active").exists():
        raise AlreadySubscribed()
    return Subscription.objects.create(customer=customer, plan=plan, status="active")

def count_signup(day):
    stat, _ = DailyStat.objects.get_or_create(day=day)    # races: two creates for the same day
    stat.signups += 1                                      # and a lost update on top
    stat.save()

@Transactional
public Subscription subscribe(long customerId, String plan) {
    if (subscriptions.existsByCustomerIdAndStatus(customerId, ACTIVE)) throw new AlreadySubscribedException();
    return subscriptions.save(new Subscription(customerId, plan, ACTIVE));
}

const existing = await prisma.subscription.findFirst({ where: { customerId, status: "ACTIVE" } });
if (!existing) await prisma.subscription.create({ data: { customerId, plan, status: "ACTIVE" } });

CREATE UNIQUE INDEX one_active_subscription ON subscriptions (customer_id) WHERE status = 'active';
CREATE UNIQUE INDEX webhook_once ON processed_webhooks (provider, event_id);

INSERT INTO daily_stats (day, signups) VALUES (current_date, 1)
ON CONFLICT (day) DO UPDATE SET signups = daily_stats.signups + 1;

INSERT INTO processed_webhooks (provider, event_id) VALUES ($1, $2)
ON CONFLICT DO NOTHING RETURNING id;      -- no row returned → already processed

-- no partial indexes: use a nullable generated column that is only set for active rows
ALTER TABLE subscriptions ADD active_customer_id BIGINT
  GENERATED ALWAYS AS (IF(status = 'active', customer_id, NULL)) STORED,
  ADD UNIQUE KEY one_active (active_customer_id);

INSERT INTO daily_stats (day, signups) VALUES (CURRENT_DATE, 1) AS new
ON DUPLICATE KEY UPDATE signups = daily_stats.signups + new.signups;

try:
    with transaction.atomic():
        sub = Subscription.objects.create(customer=customer, plan=plan, status="active")
except IntegrityError:
    raise AlreadySubscribed()                    # the index said no

DailyStat.objects.bulk_create(
    [DailyStat(day=day, signups=1)],
    update_conflicts=True, unique_fields=["day"], update_fields=["signups"])   # simple upsert (sets, not adds)
# for "add 1", use raw SQL ON CONFLICT … SET signups = daily_stats.signups + 1

try {
    return subscriptions.saveAndFlush(new Subscription(customerId, plan, ACTIVE));
} catch (DataIntegrityViolationException e) {
    throw new AlreadySubscribedException();
}

DB::table('daily_stats')->upsert(
    [['day' => today(), 'signups' => 1]], ['day'], ['signups' => DB::raw('daily_stats.signups + 1')]
);

await prisma.dailyStat.upsert({
  where: { day }, create: { day, signups: 1 }, update: { signups: { increment: 1 } },
});
try { await prisma.subscription.create({ data }); }
catch (e: any) { if (e.code === "P2002") throw new AlreadySubscribed(); throw e; }  // unique violation