fix(memberships): collapse existing and prevent duplicate memberships by scott-ray-wilson · Pull Request #5740 · Infisical/infisical

scott-ray-wilson · 2026-03-17T20:03:31Z

Context

This PR adds a migration to collapse all existing duplicate memberships at each scope and add a single membership exclusivity constraint on projects and orgs

Duplicate project memberships will have their roles collapsed, while org membership roles will preserve the earliest membership role

Screenshots

N/A

Steps to verify the change

PRIOR TO MIGRATION:

create an org, project and project custom role; replace variable values and run below statement
run migration and verify collapse

-- =============================================================================
-- Seed script: Duplicate membership test data
-- =============================================================================

DO $$
DECLARE
  v_org_id         uuid := '17102270-64e6-4113-8529-66b039f959ae';
  v_project_id     uuid := '899f4b17-c561-407d-8390-8c1a822a33cb';
  v_custom_role_id uuid := '145c0826-bea3-44d4-b4c6-4837bcd125fc';
BEGIN

-- ===================== PREREQUISITE RESOURCES ================================

-- Test user
INSERT INTO users (id, email, username, "isGhost", "isAccepted", "firstName", "lastName")
VALUES ('38713f28-012c-4a7c-9f5c-12ac62f32297', '[email protected]', '[email protected]', false, true, 'Test', 'User')
ON CONFLICT (id) DO NOTHING;

-- Test identity
INSERT INTO identities (id, name, "orgId")
VALUES ('192adf0d-f767-41b3-a327-b5770308b08b', 'test-identity', v_org_id)
ON CONFLICT (id) DO NOTHING;

-- Test group
INSERT INTO groups (id, "orgId", name, slug)
VALUES ('b3c4d5e6-0000-4000-8000-000000000001', v_org_id, 'test-group', 'test-group')
ON CONFLICT (id) DO NOTHING;

-- Custom role (referenced by some membership_roles)
INSERT INTO roles (id, name, slug, permissions, "projectId")
VALUES (v_custom_role_id, 'test-custom-role', 'test-custom-role', '[]', v_project_id)
ON CONFLICT (id) DO NOTHING;

-- ===================== USER MEMBERSHIPS ======================================

-- User org membership (original)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", status, "inviteEmail", "createdAt", "updatedAt")
VALUES ('20972fda-cd76-47df-a686-aeac9b2c2668', 'organization', '38713f28-012c-4a7c-9f5c-12ac62f32297', NULL, NULL, v_org_id, NULL, true, 'invited', '[email protected]', '2026-03-17 18:43:26.580639+00', '2026-03-17 18:43:26.580639+00');

-- User org membership (duplicate)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", status, "inviteEmail", "createdAt", "updatedAt")
VALUES ('24b4250f-bb3b-4e0f-b342-269e074b8d4e', 'organization', '38713f28-012c-4a7c-9f5c-12ac62f32297', NULL, NULL, v_org_id, NULL, true, 'invited', '[email protected]', '2026-03-17 18:43:26.580639+00', '2026-03-17 18:43:26.580639+00');

-- User project membership (original)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('9a70600e-9478-4b26-b0cc-5b019d4c812c', 'project', '38713f28-012c-4a7c-9f5c-12ac62f32297', NULL, NULL, v_org_id, v_project_id, true, '2026-03-17 18:43:50.881687+00', '2026-03-17 18:43:50.881687+00');

-- User project membership (duplicate 1)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('11b3d2ea-5dfc-4ea7-afe9-4d6f7ed2f482', 'project', '38713f28-012c-4a7c-9f5c-12ac62f32297', NULL, NULL, v_org_id, v_project_id, true, '2026-03-17 18:43:50.881687+00', '2026-03-17 18:43:50.881687+00');

-- User project membership (duplicate 2)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('45df0ebd-2cf9-4caa-a33d-13f00e015b37', 'project', '38713f28-012c-4a7c-9f5c-12ac62f32297', NULL, NULL, v_org_id, v_project_id, true, '2026-03-17 18:43:50.881687+00', '2026-03-17 18:43:50.881687+00');

-- User org roles
INSERT INTO membership_roles (id, role, "isTemporary", "membershipId", "createdAt", "updatedAt")
VALUES ('ed31d650-df46-4db7-aa4d-0cac9274d31d', 'member', false, '20972fda-cd76-47df-a686-aeac9b2c2668', '2026-03-17 18:43:26.580639+00', '2026-03-17 18:43:26.580639+00');

INSERT INTO membership_roles (id, role, "isTemporary", "membershipId", "createdAt", "updatedAt")
VALUES ('fd5d564e-f68d-4129-bbb3-547db4052e4c', 'admin', false, '24b4250f-bb3b-4e0f-b342-269e074b8d4e', '2026-03-17 18:43:26.580639+00', '2026-03-17 18:43:26.580639+00');

-- User project roles (each on a different membership)
INSERT INTO membership_roles (id, role, "isTemporary", "customRoleId", "membershipId", "createdAt", "updatedAt")
VALUES ('05600fda-ad24-48a1-b43f-326ea091896d', 'custom', false, v_custom_role_id, '9a70600e-9478-4b26-b0cc-5b019d4c812c', '2026-03-17 18:45:43.619264+00', '2026-03-17 18:45:43.619264+00');

INSERT INTO membership_roles (id, role, "isTemporary", "membershipId", "createdAt", "updatedAt")
VALUES ('79758f0b-0b7f-4932-abc6-f6149dccb01b', 'member', false, '11b3d2ea-5dfc-4ea7-afe9-4d6f7ed2f482', '2026-03-17 18:45:43.619264+00', '2026-03-17 18:45:43.619264+00');

INSERT INTO membership_roles (id, role, "isTemporary", "membershipId", "createdAt", "updatedAt")
VALUES ('b9e4c053-9826-429c-b33d-d0c7ac7ecb55', 'member', false, '45df0ebd-2cf9-4caa-a33d-13f00e015b37', '2026-03-17 18:45:43.619264+00', '2026-03-17 18:45:43.619264+00');

-- ===================== IDENTITY MEMBERSHIPS ==================================

-- Identity org membership (original)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('5182f39d-0542-4433-bdcf-c791f4b187b7', 'organization', NULL, '192adf0d-f767-41b3-a327-b5770308b08b', NULL, v_org_id, NULL, true, '2026-03-17 18:54:38.854952+00', '2026-03-17 18:54:38.854952+00');

-- Identity org membership (duplicate)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('d9e4b3ff-c44f-434a-89a2-d0e20b026afd', 'organization', NULL, '192adf0d-f767-41b3-a327-b5770308b08b', NULL, v_org_id, NULL, true, '2026-03-17 18:54:38.854952+00', '2026-03-17 18:54:38.854952+00');

-- Identity project membership (original)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('feda660b-0949-4f33-ad98-e88d4daef6f9', 'project', NULL, '192adf0d-f767-41b3-a327-b5770308b08b', NULL, v_org_id, v_project_id, true, '2026-03-17 18:59:51.549374+00', '2026-03-17 18:59:51.549374+00');

-- Identity project membership (duplicate 1)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('2a2c35b7-0498-4a85-bf01-c84415ed7203', 'project', NULL, '192adf0d-f767-41b3-a327-b5770308b08b', NULL, v_org_id, v_project_id, true, '2026-03-17 18:59:51.549374+00', '2026-03-17 18:59:51.549374+00');

-- Identity project membership (duplicate 2)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('007ebcc1-bf79-44b9-a418-a69ad43182eb', 'project', NULL, '192adf0d-f767-41b3-a327-b5770308b08b', NULL, v_org_id, v_project_id, true, '2026-03-17 18:59:51.549374+00', '2026-03-17 18:59:51.549374+00');

-- Identity org roles
INSERT INTO membership_roles (id, role, "isTemporary", "membershipId", "createdAt", "updatedAt")
VALUES ('2f0b6dd5-2806-4e9b-bb35-65e8c52598ca', 'member', false, '5182f39d-0542-4433-bdcf-c791f4b187b7', '2026-03-17 18:54:38.854952+00', '2026-03-17 18:54:38.854952+00');

INSERT INTO membership_roles (id, role, "isTemporary", "membershipId", "createdAt", "updatedAt")
VALUES ('93bec943-3505-45c8-96c9-6cac52513dfb', 'admin', false, 'd9e4b3ff-c44f-434a-89a2-d0e20b026afd', '2026-03-17 18:54:38.854952+00', '2026-03-17 18:54:38.854952+00');

-- Identity project roles
INSERT INTO membership_roles (id, role, "isTemporary", "customRoleId", "membershipId", "createdAt", "updatedAt")
VALUES ('c4f737c1-3aaf-47d5-90c3-d83a24d67939', 'custom', false, v_custom_role_id, 'feda660b-0949-4f33-ad98-e88d4daef6f9', '2026-03-17 18:59:51.549374+00', '2026-03-17 18:59:51.549374+00');

INSERT INTO membership_roles (id, role, "isTemporary", "membershipId", "createdAt", "updatedAt")
VALUES ('596319d0-32aa-4f99-9cb4-f37b181e97f6', 'admin', false, '2a2c35b7-0498-4a85-bf01-c84415ed7203', '2026-03-17 18:59:51.549374+00', '2026-03-17 18:59:51.549374+00');

INSERT INTO membership_roles (id, role, "isTemporary", "membershipId", "createdAt", "updatedAt")
VALUES ('afdec0fa-768e-42d8-a709-4dd02e166dc9', 'member', false, '007ebcc1-bf79-44b9-a418-a69ad43182eb', '2026-03-17 18:59:51.549374+00', '2026-03-17 18:59:51.549374+00');

-- ===================== GROUP MEMBERSHIPS =====================================

-- Group org membership (original)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('a1b2c3d4-1111-4000-8000-000000000001', 'organization', NULL, NULL, 'b3c4d5e6-0000-4000-8000-000000000001', v_org_id, NULL, true, '2026-03-17 19:10:00.000000+00', '2026-03-17 19:10:00.000000+00');

-- Group org membership (duplicate)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('a1b2c3d4-2222-4000-8000-000000000002', 'organization', NULL, NULL, 'b3c4d5e6-0000-4000-8000-000000000001', v_org_id, NULL, true, '2026-03-17 19:10:00.000000+00', '2026-03-17 19:10:00.000000+00');

-- Group project membership (original)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('a1b2c3d4-3333-4000-8000-000000000003', 'project', NULL, NULL, 'b3c4d5e6-0000-4000-8000-000000000001', v_org_id, v_project_id, true, '2026-03-17 19:10:30.000000+00', '2026-03-17 19:10:30.000000+00');

-- Group project membership (duplicate 1)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('a1b2c3d4-4444-4000-8000-000000000004', 'project', NULL, NULL, 'b3c4d5e6-0000-4000-8000-000000000001', v_org_id, v_project_id, true, '2026-03-17 19:10:30.000000+00', '2026-03-17 19:10:30.000000+00');

-- Group project membership (duplicate 2)
INSERT INTO memberships (id, scope, "actorUserId", "actorIdentityId", "actorGroupId", "scopeOrgId", "scopeProjectId", "isActive", "createdAt", "updatedAt")
VALUES ('a1b2c3d4-5555-4000-8000-000000000005', 'project', NULL, NULL, 'b3c4d5e6-0000-4000-8000-000000000001', v_org_id, v_project_id, true, '2026-03-17 19:10:30.000000+00', '2026-03-17 19:10:30.000000+00');

-- Group org roles
INSERT INTO membership_roles (id, role, "isTemporary", "membershipId", "createdAt", "updatedAt")
VALUES ('a1b2c3d4-6666-4000-8000-000000000006', 'member', false, 'a1b2c3d4-1111-4000-8000-000000000001', '2026-03-17 19:10:00.000000+00', '2026-03-17 19:10:00.000000+00');

INSERT INTO membership_roles (id, role, "isTemporary", "membershipId", "createdAt", "updatedAt")
VALUES ('a1b2c3d4-7777-4000-8000-000000000007', 'admin', false, 'a1b2c3d4-2222-4000-8000-000000000002', '2026-03-17 19:10:00.000000+00', '2026-03-17 19:10:00.000000+00');

-- Group project roles
INSERT INTO membership_roles (id, role, "isTemporary", "customRoleId", "membershipId", "createdAt", "updatedAt")
VALUES ('a1b2c3d4-8888-4000-8000-000000000008', 'custom', false, v_custom_role_id, 'a1b2c3d4-3333-4000-8000-000000000003', '2026-03-17 19:10:30.000000+00', '2026-03-17 19:10:30.000000+00');

INSERT INTO membership_roles (id, role, "isTemporary", "membershipId", "createdAt", "updatedAt")
VALUES ('a1b2c3d4-9999-4000-8000-000000000009', 'admin', false, 'a1b2c3d4-4444-4000-8000-000000000004', '2026-03-17 19:10:30.000000+00', '2026-03-17 19:10:30.000000+00');

INSERT INTO membership_roles (id, role, "isTemporary", "membershipId", "createdAt", "updatedAt")
VALUES ('a1b2c3d4-aaaa-4000-8000-000000000010', 'member', false, 'a1b2c3d4-5555-4000-8000-000000000005', '2026-03-17 19:10:30.000000+00', '2026-03-17 19:10:30.000000+00');

END $$;

Type

Checklist

Title follows the conventional commit format: type(scope): short description (scope is optional, e.g., fix: prevent crash on sync or fix(api): handle null response).
Tested locally
Updated docs (if needed)
Updated CLAUDE.md files (if needed)
Read the contributing guide

maidul98 · 2026-03-17T20:04:13Z

✅ Snyk checks have passed. No issues have been found so far.

Status	Scan Engine	Critical	High	Medium	Low	Total (0)
✅	Open Source Security	0	0	0	0	0 issues

💻 Catch issues earlier using the plugins for VS Code, JetBrains IDEs, Visual Studio, and Eclipse.

greptile-apps · 2026-03-17T20:06:52Z

Greptile Summary

This PR introduces a data migration that collapses pre-existing duplicate memberships rows and then enforces uniqueness via six partial indexes (one per actor type × scope combination). For project-scoped duplicates, roles are re-parented onto the earliest membership before the extras are deleted; for org-scoped duplicates, only the earliest membership is retained.

The migration is well-structured and previously raised concerns (idempotent IF NOT EXISTS on all six indexes, inclusion of isTemporary in the role dedup key) have been addressed. One remaining concern is that the keeper selection in findDuplicateGroups uses only orderBy("createdAt", "asc"), which is non-deterministic when duplicate memberships share the same timestamp — a scenario that is common in practice and is even demonstrated by the seed data in the PR description. A secondary sort on id would guarantee a stable, reproducible choice on any retry.

Confidence Score: 3/5

Safe to merge with low risk; the non-deterministic tie-breaking is a correctness concern on retries but will not cause data loss on a clean first run.
Core logic is sound and idempotency issues from earlier rounds are resolved. The remaining concern — non-deterministic keeper selection when createdAt ties exist (a common case per the seed data) — could yield a different keeper on migration retry or partial failure, potentially re-parenting a different set of roles. This doesn't cause data loss on a successful first run but reduces reliability on retries.
backend/src/db/migrations/20260317200000_membership-unique-constraints.ts — specifically the orderBy in findDuplicateGroups

Important Files Changed

Filename	Overview
backend/src/db/migrations/20260317200000_membership-unique-constraints.ts	Adds a migration to collapse duplicate memberships and enforce uniqueness via partial indexes. Previous issues (IF NOT EXISTS, isTemporary key) are addressed; one new concern remains: non-deterministic keeper selection when createdAt timestamps are equal, which could produce different outcomes on a retry.

_{Last reviewed commit: 42e9bfa}

backend/src/db/migrations/20260317200000_membership-unique-constraints.ts

scott-ray-wilson · 2026-03-17T20:36:01Z

@greptile can you re-review the latest changes addressing your comments

backend/src/db/migrations/20260317200000_membership-unique-constraints.ts

sheensantoscapadngan

LGTM

fix(memberships): collapse existing and prevent duplicate memberships

3e78238

greptile-apps bot reviewed Mar 17, 2026

View reviewed changes

improvements: address feedback

42e9bfa

sheensantoscapadngan reviewed Mar 17, 2026

View reviewed changes

backend/src/db/migrations/20260317200000_membership-unique-constraints.ts Show resolved Hide resolved

improvement: make index creation concurrent

b50e570

sheensantoscapadngan approved these changes Mar 17, 2026

View reviewed changes

scott-ray-wilson merged commit ed4a174 into main Mar 17, 2026
11 of 12 checks passed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

fix(memberships): collapse existing and prevent duplicate memberships#5740

fix(memberships): collapse existing and prevent duplicate memberships#5740
scott-ray-wilson merged 3 commits intomainfrom
fix-duplicate-memberships

scott-ray-wilson commented Mar 17, 2026 •

edited

Loading

Uh oh!

maidul98 commented Mar 17, 2026 •

edited

Loading

Uh oh!

greptile-apps bot commented Mar 17, 2026 •

edited

Loading

Uh oh!

Uh oh!

Uh oh!

Uh oh!

scott-ray-wilson commented Mar 17, 2026

Uh oh!

Uh oh!

sheensantoscapadngan left a comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

scott-ray-wilson commented Mar 17, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Context

Screenshots

Steps to verify the change

Type

Checklist

Uh oh!

maidul98 commented Mar 17, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

✅ Snyk checks have passed. No issues have been found so far.

Uh oh!

greptile-apps bot commented Mar 17, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Greptile Summary

Confidence Score: 3/5

Important Files Changed

Uh oh!

Uh oh!

Uh oh!

Uh oh!

scott-ray-wilson commented Mar 17, 2026

Uh oh!

Uh oh!

sheensantoscapadngan left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

scott-ray-wilson commented Mar 17, 2026 •

edited

Loading

maidul98 commented Mar 17, 2026 •

edited

Loading

greptile-apps bot commented Mar 17, 2026 •

edited

Loading