_🗄️ Data Integrity & Integration_ | _🟠 Major_ | _⚡ Quick win_

[Fabito97]

🗄️ Data Integrity & Integration | 🟠 Major | ⚡ Quick win

🧩 Analysis chain

🏁 Script executed:

#!/bin/bash
set -euo pipefail

# Locate the relevant files and inspect the exact waitlist model configuration.
git ls-files 'api-dotnet/src/Infrastructure/Migrations/*Waitlist*' \
  'api-dotnet/src/Infrastructure/*DbContext*.cs' \
  'api-dotnet/src/**/Waitlist.cs' \
  | sed -n '1,200p'

echo '--- VulnWatchDbContext.cs matches ---'
rg -n -C 4 'UseCollation|Email|ReferralCode|Waitlist' api-dotnet/src/Infrastructure/VulnWatchDbContext.cs

echo '--- Waitlist.cs matches ---'
rg -n -C 4 'Email|ReferralCode|ReferredByWaitlistId|Status' api-dotnet/src/**/Waitlist.cs

echo '--- Migration designer matches ---'
rg -n -C 4 'UseCollation|Email|ReferralCode|HasIndex\\("Email"|HasIndex\\("ReferralCode"' api-dotnet/src/Infrastructure/Migrations/20260627091000_AddWaitlistReferrals.Designer.cs

echo '--- Nearby migration snapshot/model excerpt ---'
python3 - <<'PY'
from pathlib import Path
p = Path('api-dotnet/src/Infrastructure/Migrations/20260627091000_AddWaitlistReferrals.Designer.cs')
text = p.read_text()
for needle in ['b.Property<string>("Email")', 'b.Property<string>("ReferralCode")', 'b.HasIndex("Email")', 'b.HasIndex("ReferralCode")']:
    i = text.find(needle)
    print(f'\n### {needle} @ {i}')
    if i >= 0:
        start = text.rfind('\n', 0, i-250)
        end = text.find('\n', i+400)
        print(text[max(0, start):min(len(text), end)])
PY

Repository: hngprojects/vulnwatch-api

Length of output: 883

---

🏁 Script executed:

#!/bin/bash
set -euo pipefail

echo '--- VulnWatchDbContext.cs ---'
rg -n -C 4 'UseCollation|Email|ReferralCode|Waitlist' api-dotnet/src/Infrastructure/Persistence/VulnWatchDbContext.cs

echo '--- Waitlist.cs ---'
rg -n -C 4 'Email|ReferralCode|ReferredByWaitlistId|Status' api-dotnet/src/Domain/Entities/Waitlist.cs

echo '--- Migration designer ---'
rg -n -C 4 'UseCollation|Email|ReferralCode|HasIndex\\("Email"|HasIndex\\("ReferralCode"' api-dotnet/src/Infrastructure/Migrations/20260627091000_AddWaitlistReferrals.Designer.cs

echo '--- Model snapshot ---'
rg -n -C 4 'UseCollation|Email|ReferralCode|HasIndex\\("Email"|HasIndex\\("ReferralCode"' api-dotnet/src/Infrastructure/Migrations/VulnWatchDbContextModelSnapshot.cs

Repository: hngprojects/vulnwatch-api

Length of output: 9476

---

🏁 Script executed:

#!/bin/bash
set -euo pipefail

echo '--- designer excerpt ---'
sed -n '915,1005p' api-dotnet/src/Infrastructure/Migrations/20260627091000_AddWaitlistReferrals.Designer.cs

echo '--- snapshot excerpt ---'
sed -n '380,520p' api-dotnet/src/Infrastructure/Migrations/VulnWatchDbContextModelSnapshot.cs

Repository: hngprojects/vulnwatch-api

Length of output: 8786

---

🏁 Script executed:

#!/bin/bash
set -euo pipefail

rg -n -C 6 'Entity\("Domain\.Entities\.Waitlist"|UseCollation|IX_Waitlists_Email|IX_Waitlists_ReferralCode|ToTable\("Waitlists"\)' \
  api-dotnet/src/Infrastructure/Migrations/VulnWatchDbContextModelSnapshot.cs

Repository: hngprojects/vulnwatch-api

Length of output: 2282

---

🏁 Script executed:

#!/bin/bash
set -euo pipefail

sed -n '1,260p' api-dotnet/src/Infrastructure/Migrations/20260627091000_AddWaitlistReferrals.cs

Repository: hngprojects/vulnwatch-api

Length of output: 3941

---

🏁 Script executed:

#!/bin/bash
set -euo pipefail

echo '--- migrations touching Waitlists ---'
rg -n -C 3 'ToTable\("Waitlists"|IX_Waitlists_Email|case_insensitive|ReferralCode|Email' \
  api-dotnet/src/Infrastructure/Migrations/*.cs

echo '--- prior Waitlists creation migration candidates ---'
sed -n '1,260p' api-dotnet/src/Infrastructure/Migrations/20260623003721_AddWaitlistComments.cs

Repository: hngprojects/vulnwatch-api

Length of output: 50382

---

🏁 Script executed:

#!/bin/bash
set -euo pipefail

sed -n '1,140p' api-dotnet/src/Infrastructure/Migrations/20260623003721_AddWaitlistComments.cs

Repository: hngprojects/vulnwatch-api

Length of output: 4726

---

Keep the waitlist collation metadata aligned with VulnWatchDbContext. VulnWatchDbContext marks both Email and ReferralCode as case_insensitive, but this generated Waitlist model drops that metadata. That leaves the migration snapshot/designer out of sync with the runtime model and can turn the unique checks case-sensitive in future migrations.

🤖 Prompt for AI Agents

Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

In
`@api-dotnet/src/Infrastructure/Migrations/20260627091000_AddWaitlistReferrals.Designer.cs`
around lines 921 - 998, The Waitlist entity metadata in this migration designer
is missing the case_insensitive collation used by VulnWatchDbContext for Email
and ReferralCode. Update the generated model snapshot/designer so the Waitlist
properties in the relevant entity configuration preserve that collation
metadata, keeping the migration snapshot aligned with the runtime model and the
unique indexes consistent across future migrations.

✅ Addressed in commits ae337a7 to 9e6c297

Originally posted by @coderabbitai[bot] in #145 (comment)