Login operation does not run in a transaction, and causing potential loss of data if array fields are present on auth collection

[rjohnsonbade-corrivium]

Describe the Bug

We are using the Multi-Tenant plugin, and as such we have a tenant array field present on our auth collection.

We had an instance where a user lost it's tenant relationship field, the following errors were reported in the postgresql database:

2025-11-12 20:19:41 UTC:10.1.3.152(54446):payload@clplatform:[13007]:ERROR:  duplicate key value violates unique constraint "api_accounts_sessions_pkey"
2025-11-12 20:19:41 UTC:10.1.3.152(54446):payload@clplatform:[13007]:DETAIL:  Key (id)=(3e4377e9-3e13-4f22-8a7d-c0a332b8c8b3) already exists.
2025-11-12 20:19:41 UTC:10.1.3.152(54446):payload@clplatform:[13007]:STATEMENT:  insert into "payload"."api_accounts_sessions" ("_order", "_parent_id", "id", "created_at", "expires_at") values ($1, $2, $3, $4, $5), ($6, $7, $8, $9, $10), ($11, $12, $13, $14, $15), ($16, $17, $18, $19, $20), ($21, $22, $23, $24, $25), ($26, $27, $28, $29, $30), ($31, $32, $33, $34, $35), ($36, $37, $38, $39, $40), ($41, $42, $43, $44, $45), ($46, $47, $48, $49, $50), ($51, $52, $53, $54, $55), ($56, $57, $58, $59, $60), ($61, $62, $63, $64, $65), ($66, $67, $68, $69, $70), ($71, $72, $73, $74, $75), ($76, $77, $78, $79, $80), ($81, $82, $83, $84, $85), ($86, $87, $88, $89, $90), ($91, $92, $93, $94, $95), ($96, $97, $98, $99, $100), ($101, $102, $103, $104, $105) returning "_order", "_parent_id", "id", "created_at", "expires_at"
2025-11-12 20:36:40 UTC:10.1.5.186(33182):payload@clplatform:[21804]:ERROR:  duplicate key value violates unique constraint "api_accounts_organisations_pkey"
2025-11-12 20:36:40 UTC:10.1.5.186(33182):payload@clplatform:[21804]:DETAIL:  Key (id)=(6886e646a8f1e028fdd604c1) already exists.
2025-11-12 20:36:40 UTC:10.1.5.186(33182):payload@clplatform:[21804]:STATEMENT:  insert into "payload"."api_accounts_organisations" ("_order", "_parent_id", "id", "organisation_id") values ($1, $2, $3, $4) returning "_order", "_parent_id", "id", "organisation_id"
2025-11-12 20:37:41 UTC:10.1.3.152(40168):payload@clplatform:[22772]:ERROR:  duplicate key value violates unique constraint "api_accounts_organisations_pkey"
2025-11-12 20:37:41 UTC:10.1.3.152(40168):payload@clplatform:[22772]:DETAIL:  Key (id)=(6886e646a8f1e028fdd604c1) already exists.
2025-11-12 20:37:41 UTC:10.1.3.152(40168):payload@clplatform:[22772]:STATEMENT:  insert into "payload"."api_accounts_organisations" ("_order", "_parent_id", "id", "organisation_id") values ($1, $2, $3, $4) returning "_order", "_parent_id", "id", "organisation_id"
2025-11-12 20:53:40 UTC:10.1.3.152(60098):payload@clplatform:[29348]:ERROR:  duplicate key value violates unique constraint "api_accounts_organisations_pkey"
2025-11-12 20:53:40 UTC:10.1.3.152(60098):payload@clplatform:[29348]:DETAIL:  Key (id)=(6886e646a8f1e028fdd604c1) already exists.
2025-11-12 20:53:40 UTC:10.1.3.152(60098):payload@clplatform:[29348]:STATEMENT:  insert into "payload"."api_accounts_organisations" ("_order", "_parent_id", "id", "organisation_id") values ($1, $2, $3, $4) returning "_order", "_parent_id", "id", "organisation_id"
2025-11-12 20:56:40 UTC:10.1.5.186(54838):payload@clplatform:[28461]:ERROR:  duplicate key value violates unique constraint "api_accounts_organisations_pkey"
2025-11-12 20:56:40 UTC:10.1.5.186(54838):payload@clplatform:[28461]:DETAIL:  Key (id)=(6886e646a8f1e028fdd604c1) already exists.
2025-11-12 20:56:40 UTC:10.1.5.186(54838):payload@clplatform:[28461]:STATEMENT:  insert into "payload"."api_accounts_organisations" ("_order", "_parent_id", "id", "organisation_id") values ($1, $2, $3, $4) returning "_order", "_parent_id", "id", "organisation_id"

Upon investigation, we discovered that the login operation DELETES all of the tenant array field rows, and then re-inserts them.

None of this is done in a transaction.

This opens the possibility of data loss, as they client may drop out between the DELETE FROM and INSERT statements, leaving the user row in a broken state (now missing it's tenant relationship).

Considering the login operation is destructive and is dependent on these requests all succeeding to maintain data integrity, this should be run in a transaction.

For the moment, we have manually created a transaction are a calling login on a custom endpoint.

Link to the code that reproduces this issue

https://github.com/rjohnsonbade-corrivium/payload-login-transaction

Reproduction Steps

1. Clone repo: git clone git@github.com:rjohnsonbade-corrivium/payload-login-transaction.git
2. Start postgres db: docker compose up -d
3. pnpm install
4. pnpm run dev
5. Browse http://localhost:3000/admin
6. Create test user,
   • Username test@example.com
   • Password test
   • Add some text to the array field
7. Logout
8. Login
9. Observe DELETE and INSERT queries running without a transaction

Query: select "users"."id", "users"."updated_at", "users"."created_at", "users"."email", "users"."reset_password_token", "users"."reset_password_expiration", "users"."salt", "users"."hash", "users"."login_attempts", "users"."lock_until", "users_arrayTest"."data" as "arrayTest", "users_sessions"."data" as "sessions" from "users" "users" left join lateral (select coalesce(json_agg(json_build_array("users_arrayTest"."_order", "users_arrayTest"."id", "users_arrayTest"."test") order by "users_arrayTest"."_order" asc), '[]'::json) as "data" from (select * from "users_array_test" "users_arrayTest" where "users_arrayTest"."_parent_id" = "users"."id" order by "users_arrayTest"."_order" asc) "users_arrayTest") "users_arrayTest" on true left join lateral (select coalesce(json_agg(json_build_array("users_sessions"."_order", "users_sessions"."id", "users_sessions"."created_at", "users_sessions"."expires_at") order by "users_sessions"."_order" asc), '[]'::json) as "data" from (select * from "users_sessions" "users_sessions" where "users_sessions"."_parent_id" = "users"."id" order by "users_sessions"."_order" asc) "users_sessions") "users_sessions" on true where "users"."id" = $1 order by "users"."created_at" desc limit $2 -- params: [1, 1]
Query: select "users"."id", "users"."updated_at", "users"."created_at", "users"."email", "users"."reset_password_token", "users"."reset_password_expiration", "users"."salt", "users"."hash", "users"."login_attempts", "users"."lock_until", "users_arrayTest"."data" as "arrayTest", "users_sessions"."data" as "sessions" from "users" "users" left join lateral (select coalesce(json_agg(json_build_array("users_arrayTest"."_order", "users_arrayTest"."id", "users_arrayTest"."test") order by "users_arrayTest"."_order" asc), '[]'::json) as "data" from (select * from "users_array_test" "users_arrayTest" where "users_arrayTest"."_parent_id" = "users"."id" order by "users_arrayTest"."_order" asc) "users_arrayTest") "users_arrayTest" on true left join lateral (select coalesce(json_agg(json_build_array("users_sessions"."_order", "users_sessions"."id", "users_sessions"."created_at", "users_sessions"."expires_at") order by "users_sessions"."_order" asc), '[]'::json) as "data" from (select * from "users_sessions" "users_sessions" where "users_sessions"."_parent_id" = "users"."id" order by "users_sessions"."_order" asc) "users_sessions") "users_sessions" on true where "users"."id" = $1 order by "users"."created_at" desc limit $2 -- params: [1, 1]
Query: insert into "users" ("id", "updated_at", "created_at", "email", "reset_password_token", "reset_password_expiration", "salt", "hash", "login_attempts", "lock_until") values ($1, default, $2, $3, $4, $5, $6, $7, $8, $9) on conflict ("id") do update set "id" = $10, "created_at" = $11, "email" = $12, "reset_password_token" = $13, "reset_password_expiration" = $14, "salt" = $15, "hash" = $16, "login_attempts" = $17, "lock_until" = $18 returning "id", "updated_at", "created_at", "email", "reset_password_token", "reset_password_expiration", "salt", "hash", "login_attempts", "lock_until" -- params: [1, "2025-11-14T03:22:07.610Z", "test@example.com", null, null, "bbab84d7e6111e64806ea837c80b04eaa5435a5d476fd387fe11eecae77d7905", "58a624075a817388c8f896b45874c11df8d82d27d2d3366030dc42a9317b8a67755dab545ac1f9530aebc1ee48a2b5bea7aa6cc5e21581f9565a9dc187e885544b7d8fa62b458db7a4ef0dee6d0a037660a9d1e91dcb79feafaef4be09e410e705d698c28c3f418faf9a69068f07eb3b31ef27bba2be819ac359654b01242abfd87d3fb7843298e07928d66a13514c6d3fa84a422eccb431fe98b7fffa39eb7dad58546241c3e44c27deb987a2a5d84d5d539ffca8157707db35494d3881dbefc609d4bc21863c7a8b312b5a412c19c1ece57e4607cea5ee22c5e91fbbe191028dd998c007e02a98387f6a0d549e1beed91d36cd92415601b578bf8c75e189c9e43f30c32441c05af3063b8681aed5a495d218bf4aab2ef352b0e9c72b7f9ed13a55bbebb035123d84d613580f71f61cf773d5488ac15ad335b39a341d788cf8ede9794ed211fcf5f1ceedf959b8845102d07815ca34bdae52dc0d599ffa09e00a0f03ba502be9a178cadad684c69636bb321a412f46b6220ba8c35bf02e49172c9399e2954906d0e0ea761983615eb7905d88f04a61760425ec67cdd6a687c03f257dd3c4b64ded58fd042f22b7b7a6fbd782bc3998204cc899aa959b2ad0a766ed42ded2de677362f1248b9434b5aba714fe3ff50bdf074999248e1390e56bd7da3c00deee975c0217602e6d3a0e8673aa7ae4af5b0d20a5b647a7e97d5e5b", "0", null, 1, "2025-11-14T03:22:07.610Z", "test@example.com", null, null, "bbab84d7e6111e64806ea837c80b04eaa5435a5d476fd387fe11eecae77d7905", "58a624075a817388c8f896b45874c11df8d82d27d2d3366030dc42a9317b8a67755dab545ac1f9530aebc1ee48a2b5bea7aa6cc5e21581f9565a9dc187e885544b7d8fa62b458db7a4ef0dee6d0a037660a9d1e91dcb79feafaef4be09e410e705d698c28c3f418faf9a69068f07eb3b31ef27bba2be819ac359654b01242abfd87d3fb7843298e07928d66a13514c6d3fa84a422eccb431fe98b7fffa39eb7dad58546241c3e44c27deb987a2a5d84d5d539ffca8157707db35494d3881dbefc609d4bc21863c7a8b312b5a412c19c1ece57e4607cea5ee22c5e91fbbe191028dd998c007e02a98387f6a0d549e1beed91d36cd92415601b578bf8c75e189c9e43f30c32441c05af3063b8681aed5a495d218bf4aab2ef352b0e9c72b7f9ed13a55bbebb035123d84d613580f71f61cf773d5488ac15ad335b39a341d788cf8ede9794ed211fcf5f1ceedf959b8845102d07815ca34bdae52dc0d599ffa09e00a0f03ba502be9a178cadad684c69636bb321a412f46b6220ba8c35bf02e49172c9399e2954906d0e0ea761983615eb7905d88f04a61760425ec67cdd6a687c03f257dd3c4b64ded58fd042f22b7b7a6fbd782bc3998204cc899aa959b2ad0a766ed42ded2de677362f1248b9434b5aba714fe3ff50bdf074999248e1390e56bd7da3c00deee975c0217602e6d3a0e8673aa7ae4af5b0d20a5b647a7e97d5e5b", "0", null]
Query: delete from "users_array_test" where "users_array_test"."_parent_id" = $1 -- params: [1]
Query: delete from "users_sessions" where "users_sessions"."_parent_id" = $1 -- params: [1]
Query: insert into "users_array_test" ("_order", "_parent_id", "id", "test") values ($1, $2, $3, $4) returning "_order", "_parent_id", "id", "test" -- params: [1, 1, "6916a05d5ee510bcf319040c", "test123"]
 POST /api/users/logout 200 in 36ms
Query: select "users"."id", "users"."updated_at", "users"."created_at", "users"."email", "users"."reset_password_token", "users"."reset_password_expiration", "users"."salt", "users"."hash", "users"."login_attempts", "users"."lock_until", "users_arrayTest"."data" as "arrayTest", "users_sessions"."data" as "sessions" from "users" "users" left join lateral (select coalesce(json_agg(json_build_array("users_arrayTest"."_order", "users_arrayTest"."id", "users_arrayTest"."test") order by "users_arrayTest"."_order" asc), '[]'::json) as "data" from (select * from "users_array_test" "users_arrayTest" where "users_arrayTest"."_parent_id" = "users"."id" order by "users_arrayTest"."_order" asc) "users_arrayTest") "users_arrayTest" on true left join lateral (select coalesce(json_agg(json_build_array("users_sessions"."_order", "users_sessions"."id", "users_sessions"."created_at", "users_sessions"."expires_at") order by "users_sessions"."_order" asc), '[]'::json) as "data" from (select * from "users_sessions" "users_sessions" where "users_sessions"."_parent_id" = "users"."id" order by "users_sessions"."_order" asc) "users_sessions") "users_sessions" on true order by "users"."created_at" desc limit $1 -- params: [1]
 GET /admin/login 200 in 26ms
Query: select "users"."id", "users"."updated_at", "users"."created_at", "users"."email", "users"."reset_password_token", "users"."reset_password_expiration", "users"."salt", "users"."hash", "users"."login_attempts", "users"."lock_until", "users_arrayTest"."data" as "arrayTest", "users_sessions"."data" as "sessions" from "users" "users" left join lateral (select coalesce(json_agg(json_build_array("users_arrayTest"."_order", "users_arrayTest"."id", "users_arrayTest"."test") order by "users_arrayTest"."_order" asc), '[]'::json) as "data" from (select * from "users_array_test" "users_arrayTest" where "users_arrayTest"."_parent_id" = "users"."id" order by "users_arrayTest"."_order" asc) "users_arrayTest") "users_arrayTest" on true left join lateral (select coalesce(json_agg(json_build_array("users_sessions"."_order", "users_sessions"."id", "users_sessions"."created_at", "users_sessions"."expires_at") order by "users_sessions"."_order" asc), '[]'::json) as "data" from (select * from "users_sessions" "users_sessions" where "users_sessions"."_parent_id" = "users"."id" order by "users_sessions"."_order" asc) "users_sessions") "users_sessions" on true where "users"."email" = $1 order by "users"."created_at" desc limit $2 -- params: ["test@example.com", 1]
Query: select "id", "login_attempts", "lock_until" from "users" "users" where "users"."id" = $1 order by "users"."created_at" desc limit $2 -- params: [1, 1]
Query: insert into "users" ("id", "updated_at", "created_at", "email", "reset_password_token", "reset_password_expiration", "salt", "hash", "login_attempts", "lock_until") values ($1, default, $2, $3, $4, $5, $6, $7, $8, $9) on conflict ("id") do update set "id" = $10, "created_at" = $11, "email" = $12, "reset_password_token" = $13, "reset_password_expiration" = $14, "salt" = $15, "hash" = $16, "login_attempts" = $17, "lock_until" = $18 returning "id", "updated_at", "created_at", "email", "reset_password_token", "reset_password_expiration", "salt", "hash", "login_attempts", "lock_until" -- params: [1, "2025-11-14T03:22:07.610Z", "test@example.com", null, null, "bbab84d7e6111e64806ea837c80b04eaa5435a5d476fd387fe11eecae77d7905", "58a624075a817388c8f896b45874c11df8d82d27d2d3366030dc42a9317b8a67755dab545ac1f9530aebc1ee48a2b5bea7aa6cc5e21581f9565a9dc187e885544b7d8fa62b458db7a4ef0dee6d0a037660a9d1e91dcb79feafaef4be09e410e705d698c28c3f418faf9a69068f07eb3b31ef27bba2be819ac359654b01242abfd87d3fb7843298e07928d66a13514c6d3fa84a422eccb431fe98b7fffa39eb7dad58546241c3e44c27deb987a2a5d84d5d539ffca8157707db35494d3881dbefc609d4bc21863c7a8b312b5a412c19c1ece57e4607cea5ee22c5e91fbbe191028dd998c007e02a98387f6a0d549e1beed91d36cd92415601b578bf8c75e189c9e43f30c32441c05af3063b8681aed5a495d218bf4aab2ef352b0e9c72b7f9ed13a55bbebb035123d84d613580f71f61cf773d5488ac15ad335b39a341d788cf8ede9794ed211fcf5f1ceedf959b8845102d07815ca34bdae52dc0d599ffa09e00a0f03ba502be9a178cadad684c69636bb321a412f46b6220ba8c35bf02e49172c9399e2954906d0e0ea761983615eb7905d88f04a61760425ec67cdd6a687c03f257dd3c4b64ded58fd042f22b7b7a6fbd782bc3998204cc899aa959b2ad0a766ed42ded2de677362f1248b9434b5aba714fe3ff50bdf074999248e1390e56bd7da3c00deee975c0217602e6d3a0e8673aa7ae4af5b0d20a5b647a7e97d5e5b", "0", null, 1, "2025-11-14T03:22:07.610Z", "test@example.com", null, null, "bbab84d7e6111e64806ea837c80b04eaa5435a5d476fd387fe11eecae77d7905", "58a624075a817388c8f896b45874c11df8d82d27d2d3366030dc42a9317b8a67755dab545ac1f9530aebc1ee48a2b5bea7aa6cc5e21581f9565a9dc187e885544b7d8fa62b458db7a4ef0dee6d0a037660a9d1e91dcb79feafaef4be09e410e705d698c28c3f418faf9a69068f07eb3b31ef27bba2be819ac359654b01242abfd87d3fb7843298e07928d66a13514c6d3fa84a422eccb431fe98b7fffa39eb7dad58546241c3e44c27deb987a2a5d84d5d539ffca8157707db35494d3881dbefc609d4bc21863c7a8b312b5a412c19c1ece57e4607cea5ee22c5e91fbbe191028dd998c007e02a98387f6a0d549e1beed91d36cd92415601b578bf8c75e189c9e43f30c32441c05af3063b8681aed5a495d218bf4aab2ef352b0e9c72b7f9ed13a55bbebb035123d84d613580f71f61cf773d5488ac15ad335b39a341d788cf8ede9794ed211fcf5f1ceedf959b8845102d07815ca34bdae52dc0d599ffa09e00a0f03ba502be9a178cadad684c69636bb321a412f46b6220ba8c35bf02e49172c9399e2954906d0e0ea761983615eb7905d88f04a61760425ec67cdd6a687c03f257dd3c4b64ded58fd042f22b7b7a6fbd782bc3998204cc899aa959b2ad0a766ed42ded2de677362f1248b9434b5aba714fe3ff50bdf074999248e1390e56bd7da3c00deee975c0217602e6d3a0e8673aa7ae4af5b0d20a5b647a7e97d5e5b", "0", null]
Query: delete from "users_array_test" where "users_array_test"."_parent_id" = $1 -- params: [1]
Query: delete from "users_sessions" where "users_sessions"."_parent_id" = $1 -- params: [1]
Query: insert into "users_array_test" ("_order", "_parent_id", "id", "test") values ($1, $2, $3, $4) returning "_order", "_parent_id", "id", "test" -- params: [1, 1, "6916a05d5ee510bcf319040c", "test123"]
Query: insert into "users_sessions" ("_order", "_parent_id", "id", "created_at", "expires_at") values ($1, $2, $3, $4, $5) returning "_order", "_parent_id", "id", "created_at", "expires_at" -- params: [1, 1, "b94aaac3-7a89-4610-b666-e912f7436249", "2025-11-14T03:22:16.459Z", "2025-11-14T05:22:16.459Z"]
 POST /api/users/login 200 in 115ms
Query: select "users"."id", "users"."updated_at", "users"."created_at", "users"."email", "users"."reset_password_token", "users"."reset_password_expiration", "users"."salt", "users"."hash", "users"."login_attempts", "users"."lock_until", "users_arrayTest"."data" as "arrayTest", "users_sessions"."data" as "sessions" from "users" "users" left join lateral (select coalesce(json_agg(json_build_array("users_arrayTest"."_order", "users_arrayTest"."id", "users_arrayTest"."test") order by "users_arrayTest"."_order" asc), '[]'::json) as "data" from (select * from "users_array_test" "users_arrayTest" where "users_arrayTest"."_parent_id" = "users"."id" order by "users_arrayTest"."_order" asc) "users_arrayTest") "users_arrayTest" on true left join lateral (select coalesce(json_agg(json_build_array("users_sessions"."_order", "users_sessions"."id", "users_sessions"."created_at", "users_sessions"."expires_at") order by "users_sessions"."_order" asc), '[]'::json) as "data" from (select * from "users_sessions" "users_sessions" where "users_sessions"."_parent_id" = "users"."id" order by "users_sessions"."_order" asc) "users_sessions") "users_sessions" on true where "users"."id" = $1 order by "users"."created_at" desc limit $2 -- params: [1, 1]
Query: select distinct "payload_preferences"."id", "payload_preferences"."created_at", "payload_preferences"."created_at" from "payload_preferences" left join "payload_preferences_rels" "f9f156b0_0ec3_456d_8b4b_8b4aa2d6bef6" on ("payload_preferences"."id" = "f9f156b0_0ec3_456d_8b4b_8b4aa2d6bef6"."parent_id" and "f9f156b0_0ec3_456d_8b4b_8b4aa2d6bef6"."path" like $1) where ("payload_preferences"."key" = $2 and "f9f156b0_0ec3_456d_8b4b_8b4aa2d6bef6"."users_id" is not null and "f9f156b0_0ec3_456d_8b4b_8b4aa2d6bef6"."users_id" = $3) order by "payload_preferences"."created_at" desc limit $4 -- params: ["user", "nav", 1, 1]

Which area(s) are affected? (Select all that apply)

area: core

Environment Info

Fresh install using create app.

Binaries:
  Node: 22.12.0
  npm: 11.1.0
  Yarn: N/A
  pnpm: 10.15.0
Relevant Packages:
  payload: 3.64.0
  next: 15.4.7
  @payloadcms/db-postgres: 3.64.0
  @payloadcms/drizzle: 3.64.0
  @payloadcms/graphql: 3.64.0
  @payloadcms/next/utilities: 3.64.0
  @payloadcms/richtext-lexical: 3.64.0
  @payloadcms/translations: 3.64.0
  @payloadcms/ui/shared: 3.64.0
  react: 19.1.0
  react-dom: 19.1.0
Operating System:
  Platform: linux
  Arch: x64
  Version: #1 SMP PREEMPT_DYNAMIC Sun Nov  2 17:43:34 UTC 2025
  Available memory (MB): 31791
  Available CPU cores: 16