fix: add BTREE index on position column for custom objects

https://sonarly.com/issue/19173?type=bug Creating a record via REST API triggers a `SELECT MIN(position)` query that does a full table scan on custom object tables lacking an index on the `position` column, causing 2+ second latency. Fix: Added a BTREE index on the `position` column to the default indexes created for custom objects. **What changed:** In `build-default-index-for-custom-object.util.ts`, added a second index (`positionFlatIndex`) alongside the existing `tsVectorFlatIndex`. The new index uses `IndexType.BTREE` on the `position` field, following the identical structure as the existing GIN index on `searchVector`. **Why:** Every record creation triggers `SELECT MIN(position)` or `SELECT MAX(position)` via `RecordPositionService.findMinPosition()`/`findMaxPosition()`. Without an index, this is a full table scan. For large custom object tables, this causes multi-second latency (2044ms observed in the Sentry trace). BTREE indexes automatically include `deletedAt` in this codebase (handled by `getColumnsForIndex`), which matches the `WHERE deletedAt IS NULL` filter in the query. **Limitation:** This fix only applies to newly created custom objects. Existing workspaces with large custom object tables will still lack this index. A follow-up migration command should be created to retroactively add BTREE indexes on `position` for all existing custom objects — similar to how the codebase handles other backfill commands in `packages/twenty-server/src/database/commands/`.
2026-03-28 00:23:13 +00:00
1 changed files with 31 additions and 0 deletions
@@ -17,6 +17,7 @@ export const buildDefaultIndexesForCustomObject = ({
  defaultFlatFieldForCustomObjectMaps: DefaultFlatFieldForCustomObjectMaps;
 }) => {
  const tsFlatVectorIndexUniversalIdentifier = v4();
+  const positionIndexUniversalIdentifier = v4();
  const createdAt = new Date();
  const tsVectorFlatIndex = generateFlatIndexMetadataWithNameOrThrow({
    objectFlatFieldMetadatas,
@@ -48,9 +49,39 @@ export const buildDefaultIndexesForCustomObject = ({
    flatObjectMetadata,
  });

+  const positionFlatIndex = generateFlatIndexMetadataWithNameOrThrow({
+    objectFlatFieldMetadatas,
+    flatIndex: {
+      createdAt: createdAt.toISOString(),
+      universalFlatIndexFieldMetadatas: [
+        {
+          createdAt: createdAt.toISOString(),
+          fieldMetadataUniversalIdentifier:
+            defaultFlatFieldForCustomObjectMaps.fields.position
+              .universalIdentifier,
+          indexMetadataUniversalIdentifier: positionIndexUniversalIdentifier,
+          order: 0,
+          updatedAt: createdAt.toISOString(),
+        },
+      ],
+
+      indexType: IndexType.BTREE,
+      indexWhereClause: null,
+      isCustom: false,
+      isUnique: false,
+      objectMetadataUniversalIdentifier: flatObjectMetadata.universalIdentifier,
+      universalIdentifier: positionIndexUniversalIdentifier,
+      updatedAt: createdAt.toISOString(),
+      applicationUniversalIdentifier:
+        flatObjectMetadata.applicationUniversalIdentifier,
+    },
+    flatObjectMetadata,
+  });
+
  return {
    indexes: {
      tsVectorFlatIndex,
+      positionFlatIndex,
    },
  } as const satisfies { indexes: Record<string, UniversalFlatIndexMetadata> };
 };