jaydeep-pipaliya · 2026-08-25T06:51:03Z

Context

access_approval_requests (from 20240507162141_access.ts, May 2024) and access_approval_requests_reviewers were created without covering indexes for any of their FK columns, and none of the follow-up migrations added them either:

access_approval_requests:

policyId → access_approval_policies(id) CASCADE (notNullable)
privilegeId → additional privileges CASCADE/SET NULL (nullable)
requestedByUserId → users(id) CASCADE/SET NULL (notNullable, renamed from requestedBy in 20240724101056)
approvedByUserId → users(id) SET NULL (nullable, added in 20260402160741)
revokedByUserId → users(id) SET NULL (nullable, added in 20260402160741)

access_approval_requests_reviewers:

requestId → access_approval_requests(id) CASCADE (notNullable)
reviewerUserId → users(id) SET NULL (notNullable, replaces member since 20240724101056)

Postgres does not auto-index FK columns. Every parent DELETE — a user is removed, a policy is dropped, a privilege is cascaded, an access request is cleaned up — fires a per-row RI trigger that seq-scans these tables. Cheap today, but grows linearly with request/reviewer volume.

Changes

New migration 20260825064925_add-fk-indexes-access-approval-requests.ts that adds seven partial/full indexes:

Full indexes for notNullable FKs (every row participates): policyId, requestedByUserId, requestId, reviewerUserId.
Partial WHERE ... IS NOT NULL indexes for the mostly-NULL nullable FKs (populated only after approval/revocation): privilegeId, approvedByUserId, revokedByUserId — same shape used across the codebase per backend/CLAUDE.md.

Built CONCURRENTLY with the invalid-index rebuild guard used in #7357 so an interrupted concurrent build doesn't leave a never-used invalid index behind. Down path uses DROP INDEX CONCURRENTLY IF EXISTS.

Same shape as #7294 / #7357, applied to a different table pair.

Test plan

npm run migration:latest-dev from backend/

Verify indexes exist:

SELECT indexname FROM pg_indexes
WHERE tablename IN ('access_approval_requests', 'access_approval_requests_reviewers')
  AND indexname LIKE 'access_approval_requests%_idx'
ORDER BY indexname;

npm run migration:rollback-dev — indexes drop cleanly
Re-apply — still idempotent (IF NOT EXISTS on up, IF EXISTS on down)

access_approval_requests and access_approval_requests_reviewers were created without covering indexes for any of their FK columns. Postgres does not auto-index FK columns, so every parent DELETE (a user is removed, a policy is dropped, a privilege is cascaded, an access request is cleaned up) fires a per-row RI trigger that seq-scans these tables. Adds full indexes for the notNullable FKs (policyId, requestedByUserId, requestId, reviewerUserId) and partial WHERE ... IS NOT NULL indexes for the mostly-NULL nullable FKs (privilegeId, approvedByUserId, revokedByUserId — all populated only after approval/revocation). Built CONCURRENTLY with the same invalid-index rebuild guard used in 20260721093822 so an interrupted concurrent build doesn't leave a never-used invalid index behind.

infisical-cla-app · 2026-08-25T06:51:08Z

📝 Contributor License Agreement required

Before this PR can merge, every contributor must sign the Infisical CLA.
Signing is quick: sign in with GitHub, review the CLA, and accept.

👉 Sign the CLA

Still needs to sign:

@jaydeep-pipaliya

Once everyone has signed, the check updates automatically — no need to close and reopen the PR.

chatgpt-codex-connector

💡 Codex Review

Here are some automated review suggestions for this pull request.

Reviewed commit: f4402be184

ℹ️ About Codex in GitHub

Codex has been enabled to automatically review pull requests in this repo. Reviews are triggered when you

Open a pull request for review
Mark a draft as ready
Comment "@codex review".

If Codex has suggestions, it will comment; otherwise it will react with 👍.

When you sign up for Codex through ChatGPT, Codex can also answer questions or update the PR, like "@codex address that feedback".

chatgpt-codex-connector · 2026-08-25T06:53:17Z

+  }
+];


Index the editedByUserId foreign key too

This list omits editedByUserId, which 20250813020335_access-request-edit-cols.ts adds as a nullable users(id) foreign key without an index. When a user who edited access requests is deleted or updated, the FK trigger can still sequentially scan access_approval_requests, so this migration leaves one of the table's FK columns uncovered; add a partial index for editedByUserId IS NOT NULL. backend/CLAUDE.mdL35-L35

Useful? React with 👍 / 👎.

Good catch — added editedByUserId as a partial index in commit 2729d69. Same mostly-NULL shape as approvedByUserId/revokedByUserId (populated only when a reviewer edits a request).

greptile-apps · 2026-08-25T06:53:40Z

Greptile Summary

This PR adds a non-transactional PostgreSQL migration that creates seven concurrent indexes over foreign-key columns in access approval request and reviewer tables.

Uses full indexes for non-nullable foreign keys and partial indexes for nullable foreign keys.
Removes invalid remnants of interrupted concurrent builds before retrying.
Restores the connection’s statement and lock timeout settings and provides concurrent rollback operations.

Confidence Score: 4/5

The PR appears safe to merge, with only a non-blocking inaccurate source-code reference to correct.

The migration targets the current table and column definitions with suitable full or partial indexes, while its concurrent build, invalid-index cleanup, rollback, and timeout restoration follow established repository patterns.

Files Needing Attention: backend/src/db/migrations/20260825064925_add-fk-indexes-access-approval-requests.ts

Important Files Changed

Filename	Overview
backend/src/db/migrations/20260825064925_add-fk-indexes-access-approval-requests.ts	The index definitions and migration safeguards align with the current schema; only the nonexistent migration reference in the explanatory comment needs correction.

_{Reviews (1): Last reviewed commit: "fix(db): index FK columns on access_appr..." | Re-trigger Greptile}

greptile-apps · 2026-08-25T06:53:44Z

+// Built CONCURRENTLY so the deploy doesn't take a write-blocking lock — mirrors the pattern from
+// 20260721093822_add-fk-indexes-pki-signer-issuance-jobs.ts, including the invalid-index rebuild
+// guard for interrupted concurrent builds.


Nonexistent migration reference

The comment cites 20260721093822_add-fk-indexes-pki-signer-issuance-jobs.ts, which is absent from the repository, preventing maintainers from inspecting the claimed precedent for this safety-sensitive migration pattern.

Suggested change

// Built CONCURRENTLY so the deploy doesn't take a write-blocking lock — mirrors the pattern from

// 20260721093822_add-fk-indexes-pki-signer-issuance-jobs.ts, including the invalid-index rebuild

// guard for interrupted concurrent builds.

// Built CONCURRENTLY so the deploy doesn't take a write-blocking lock, including an invalid-index

// rebuild guard for interrupted concurrent builds.

Fair, dropped the sibling-migration reference in 2729d69 — the pattern description now stands on its own.

20250813020335_access-request-edit-cols added editedByUserId as a nullable users(id) SET NULL FK without a covering index — same mostly-NULL shape as approvedByUserId/revokedByUserId, populated only when a reviewer edits a request. Add a partial WHERE ... IS NOT NULL index. Also drop the reference to a not-yet-merged sibling migration from the header comment.

chatgpt-codex-connector Bot reviewed Aug 25, 2026

View reviewed changes

greptile-apps Bot reviewed Aug 25, 2026

View reviewed changes

Navigation Menu

Uh oh!

fix(db): index FK columns on access_approval_requests + reviewers - #7800