SSO: make domain uniqueness atomic — dedup ssoProvider rows + add lower(domain) unique index

[waleedlatif1]

Background

The SSO domain-registration hardening (PR #4813, shipped via #4815) added an application-level cross-tenant uniqueness check in apps/sim/app/api/auth/sso/register/route.ts: before registering, it rejects (409) a domain already registered by a different tenant. It also normalizes domains (apps/sim/lib/auth/sso/domain.ts).

This closes the practical cross-tenant shadowing path, but two residual gaps remain that can only be fully closed at the database level.

Residual gaps

1. TOCTOU race (not atomic). The conflict check is a SELECT and the actual write happens inside Better Auth's adapter (auth.api.registerSSOProvider), outside our transaction. Two tenants registering the same domain concurrently can both pass the check before either commits. The window was narrowed (the check is re-run immediately before the write — commit ed7f0b4fa), but not eliminated.

2. Same-owner duplicate rows. Nothing enforces one row per domain at the DB level. Production already contains duplicate ssoProvider rows for the same domain/org/providerId (observed: 2 rows / 1 domain / 1 org / 1 providerId), which Better Auth itself appears to have created.

Proposed fix (the real atomic guarantee)

1. Dedup existing rows. Decide a strategy (e.g. keep the most recently updated row per lower(domain), or per (lower(domain), organizationId)), and remove the rest. Needs care because Better Auth reads these rows at sign-in.
2. Add a unique index on lower(domain) (or (lower(domain), organizationId) if we want to allow the same domain across orgs — decide the intended invariant). This makes registration atomic: a concurrent duplicate fails at the DB constraint, and the app can surface a clean 409.

Notes / open questions

• The unique index cannot be added until the dedup migration runs (current duplicates would violate it).
• Confirm Better Auth's registerSSOProvider upsert semantics (keys on providerId?) so the constraint + app logic don't conflict with how it writes.
• Decide the intended uniqueness scope: globally one provider per domain, or one per org.

References

• Route: apps/sim/app/api/auth/sso/register/route.ts
• Normalizer: apps/sim/lib/auth/sso/domain.ts
• Reviewer threads: PR v0.6.98: redundant index, security hardening, new copilot messages table, integration icons audit #4815 (greptile P2 on the above two files)