Follow-up from chat#1841: the new organization member/domain endpoints (api#748, api#749) enforce their invariants app-side with check-then-insert, which is racy under concurrent requests. Flagged by review bots on both PRs; deferred there because the correct fix is database-level (this issue), not app-level locking.
Why: concurrent POST /api/organizations/members can insert duplicate membership rows; concurrent POST /api/organizations/domains can double-map a domain, which breaks selectOrgByDomain's .single() and therefore email-domain auto-join. The same check-then-insert race pre-exists in assignAccountToOrg.
Fix:
database migration: UNIQUE (account_id, organization_id) on account_organization_ids; UNIQUE (domain) on organization_domains. Dedupe any existing violations in the same migration (check first — none known as of 2026-07-03).api: convert the inserts (addAccountToOrganization, insertOrganizationDomain) to upsert/on-conflict so a lost race returns the existing row instead of an error.
Done when: two concurrent identical POST .../members (and .../domains) requests both return 200 with the same row id and exactly one row exists; the migration applies cleanly against prod data; existing endpoint tests still pass.
Follow-up from chat#1841: the new organization member/domain endpoints (api#748, api#749) enforce their invariants app-side with check-then-insert, which is racy under concurrent requests. Flagged by review bots on both PRs; deferred there because the correct fix is database-level (this issue), not app-level locking.
Why: concurrent
POST /api/organizations/memberscan insert duplicate membership rows; concurrentPOST /api/organizations/domainscan double-map a domain, which breaksselectOrgByDomain's.single()and therefore email-domain auto-join. The same check-then-insert race pre-exists inassignAccountToOrg.Fix:
databasemigration:UNIQUE (account_id, organization_id)onaccount_organization_ids;UNIQUE (domain)onorganization_domains. Dedupe any existing violations in the same migration (check first — none known as of 2026-07-03).api: convert the inserts (addAccountToOrganization,insertOrganizationDomain) to upsert/on-conflict so a lost race returns the existing row instead of an error.Done when: two concurrent identical
POST .../members(and.../domains) requests both return 200 with the same row id and exactly one row exists; the migration applies cleanly against prod data; existing endpoint tests still pass.