github-starred/pangolin
2
0
Fork 0
mirror of https://github.com/fosrl/pangolin.git synced 2026-07-16 06:08:33 -05:00
Code Issues 1.6k Packages Projects Releases 79 Wiki Activity

[PR #3013] [CLOSED] Add unique index on roles (orgId, name) #21464

New Issue
Closed
opened 2026-05-29 02:52:24 -05:00 by GiteaMirror · 0 comments
GiteaMirror commented 2026-05-29 02:52:24 -05:00
Owner
Copy Link

📋 Pull Request Information

Original PR: https://github.com/fosrl/pangolin/pull/3013
Author: @rinseaid
Created: 5/6/2026
Status: ❌ Closed

Base: main ← Head: roles-unique-orgid-name-index

📝 Commits (1)

  • 39f6973 feat: add unique index on roles (orgId, name) with data migration

📊 Changes

6 files changed (+369 additions, -5 deletions)

View changed files

📝 server/db/pg/schema/schema.ts (+4 -1)
📝 server/db/sqlite/schema/schema.ts (+5 -2)
📝 server/setup/migrationsPg.ts (+3 -1)
📝 server/setup/migrationsSqlite.ts (+3 -1)
➕ server/setup/scriptsPg/1.19.0.ts (+170 -0)
➕ server/setup/scriptsSqlite/1.19.0.ts (+184 -0)

📄 Description

By creating this pull request, I grant the project maintainers an unlimited,
perpetual license to use, modify, and redistribute these contributions under any terms they
choose, including both the AGPLv3 and the Fossorial Commercial license terms. I
represent that I have the right to grant this license for all contributed content.

Preface

This PR was developed with Claude Code (claude-opus-4-6). The migration logic, edge case handling, and scoped dedup approach went through multiple review rounds (11 agents across 2 passes) that caught several bugs that would have caused data loss in production, including a type coercion bug where postgres returns MIN() as a string instead of an integer, and a connection pool race where raw BEGIN/COMMIT doesn't pin a connection. Both PostgreSQL and SQLite migrations were tested against real databases with intentionally messy data (triple duplicates, PK conflicts, legitimate duplicate rows in FK tables that need to survive). I reviewed, tested, and validated all changes.

Description

The roles table has no unique constraint on (orgId, name). Multiple code paths use a select-then-insert pattern that can produce duplicate rows under concurrent requests: the createRole API handler and the blueprint auto-create logic in #2980.

This adds a versioned migration (1.19.0, both PG and SQLite) that cleans up any existing duplicates before creating the index. For each duplicate group (same orgId + name), the migration keeps the lowest roleId and repoints all foreign key references from the duplicates to the survivor across the 7 tables that reference roles.roleId (userOrgRoles, roleSiteResources, roleActions, roleSites, roleResources, userInviteRoles, roleClients). Any rows that become identical after the repoint are deduplicated. The duplicate roles are then deleted and the unique index is created.

Schema files are updated to declare the uniqueIndex so Drizzle stays in sync with what the migration creates.

How to test?

  • Deploy against a database with duplicate roles (same orgId + name). Verify duplicates are merged, the lowest roleId survives, and all FK references are repointed.
  • Verify no orphan references remain in any of the 7 FK tables.
  • Verify legitimate duplicate rows in FK tables for unrelated roles are not collapsed.
  • Attempt to insert a duplicate role (same orgId + name). Verify it is rejected.
  • Insert a role with the same name in a different org. Verify it succeeds.

🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.

## 📋 Pull Request Information **Original PR:** https://github.com/fosrl/pangolin/pull/3013 **Author:** [@rinseaid](https://github.com/rinseaid) **Created:** 5/6/2026 **Status:** ❌ Closed **Base:** `main` ← **Head:** `roles-unique-orgid-name-index` --- ### 📝 Commits (1) - [`39f6973`](https://github.com/fosrl/pangolin/commit/39f69731dea0085f7813405498e6af2a7982b85a) feat: add unique index on roles (orgId, name) with data migration ### 📊 Changes **6 files changed** (+369 additions, -5 deletions) <details> <summary>View changed files</summary> 📝 `server/db/pg/schema/schema.ts` (+4 -1) 📝 `server/db/sqlite/schema/schema.ts` (+5 -2) 📝 `server/setup/migrationsPg.ts` (+3 -1) 📝 `server/setup/migrationsSqlite.ts` (+3 -1) ➕ `server/setup/scriptsPg/1.19.0.ts` (+170 -0) ➕ `server/setup/scriptsSqlite/1.19.0.ts` (+184 -0) </details> ### 📄 Description By creating this pull request, I grant the project maintainers an unlimited, perpetual license to use, modify, and redistribute these contributions under any terms they choose, including both the AGPLv3 and the Fossorial Commercial license terms. I represent that I have the right to grant this license for all contributed content. ### Preface This PR was developed with Claude Code (claude-opus-4-6). The migration logic, edge case handling, and scoped dedup approach went through multiple review rounds (11 agents across 2 passes) that caught several bugs that would have caused data loss in production, including a type coercion bug where postgres returns `MIN()` as a string instead of an integer, and a connection pool race where raw `BEGIN`/`COMMIT` doesn't pin a connection. Both PostgreSQL and SQLite migrations were tested against real databases with intentionally messy data (triple duplicates, PK conflicts, legitimate duplicate rows in FK tables that need to survive). I reviewed, tested, and validated all changes. ## Description The `roles` table has no unique constraint on `(orgId, name)`. Multiple code paths use a select-then-insert pattern that can produce duplicate rows under concurrent requests: the `createRole` API handler and the blueprint auto-create logic in #2980. This adds a versioned migration (1.19.0, both PG and SQLite) that cleans up any existing duplicates before creating the index. For each duplicate group (same orgId + name), the migration keeps the lowest roleId and repoints all foreign key references from the duplicates to the survivor across the 7 tables that reference `roles.roleId` (userOrgRoles, roleSiteResources, roleActions, roleSites, roleResources, userInviteRoles, roleClients). Any rows that become identical after the repoint are deduplicated. The duplicate roles are then deleted and the unique index is created. Schema files are updated to declare the `uniqueIndex` so Drizzle stays in sync with what the migration creates. ## How to test? - Deploy against a database with duplicate roles (same orgId + name). Verify duplicates are merged, the lowest roleId survives, and all FK references are repointed. - Verify no orphan references remain in any of the 7 FK tables. - Verify legitimate duplicate rows in FK tables for unrelated roles are not collapsed. - Attempt to insert a duplicate role (same orgId + name). Verify it is rejected. - Insert a role with the same name in a different org. Verify it succeeds. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>
GiteaMirror added the pull-request label 2026-05-29 02:52:24 -05:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
api
authentication
bug
config
dependencies
docker
documentation
enhancement
good first issue
help wanted
Improvement
Look Into
needs investigating
networking
new feature
non-critical bug
potential bug
pull-request
Mirrored from GitHub Pull Request
 question
reverse proxy
Security
stale
ui
wontfix
No Label pull-request
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/pangolin#21464
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?