[Bug/Limitation] Manually created partial unique indexes are silently deleted during collection synchronization

English Forum
1

Describe the bug

During collection synchronization in NocoBase, manually created database indexes (specifically partial unique indexes created directly in PostgreSQL) are automatically deleted without warning or configuration option. The index is functional and causes no errors while present, but is removed during the sync operation. The deletion appears to be a consequence of NocoBase’s schema synchronization logic, which may not recognize manually created indexes as part of its managed schema definition.

This is problematic because:

  1. The index works perfectly — no errors, no conflicts, no problems detected while the index exists
  2. The deletion is silent — no warning, no error message, no option to prevent it
  3. The deletion is only triggered by sync — the index persists when NocoBase is not synchronizing; it only disappears after the sync operation completes
  4. The deletion is consistent — happens every time sync is run, 100% reproducible

Environment

  • NocoBase version: 2.0.53
  • Database type and version: PostgreSQL 16
  • OS: Ubuntu Server 24.04 LTS
  • Deployment Methods: Docker container (self-hosted)
  • Docker image version: 2.0.53
  • NodeJS version: N/A

How To Reproduce

Setup:

  • Create a PostgreSQL table with multiple records that can transition through different statuses (e.g., “open”, “assigned”, “accepted”)
  • One business rule must be enforced: only one record per parent entity can have status = ‘accepted’ at any time

Test Scenario 1: Manually created in PostgreSQL

  1. Create a partial unique index directly in PostgreSQL:

    CREATE UNIQUE INDEX idx_assignment_accepted_unique 
ON "assignment-table" (parent_entity_id) 
WHERE status = 'accepted';

  2. Verify the index exists:

    SELECT indexname FROM pg_indexes 
WHERE tablename = 'assignment-table'
AND indexname = 'idx_assignment_accepted_unique';

    Result before sync: Index exists :white_check_mark:

  3. In NocoBase UI, click “Synchronize” on the collection

  4. Verify after sync:

    SELECT indexname FROM pg_indexes 
WHERE tablename = 'assignment-table'
AND indexname = 'idx_assignment_accepted_unique';

    Result after sync: Index is deleted :x:

Test Scenario 2: Created via NocoBase API (ctx.sql.run)

We also tested creating the index programmatically via NocoBase’s JavaScript context using ctx.sql.run():

// In a NocoBase JavaScript block
await ctx.sql.run(
  `CREATE UNIQUE INDEX IF NOT EXISTS idx_assignment_accepted_unique 
   ON "assignment-table" (parent_entity_id) 
   WHERE status = 'accepted'`,
  { type: 'raw' }
);

  1. Verify the index was created:

    SELECT indexname FROM pg_indexes 
WHERE tablename = 'assignment-table'
AND indexname = 'idx_assignment_accepted_unique';

    Result: Index created successfully :white_check_mark:

  2. In NocoBase UI, click “Synchronize” on the collection

  3. Verify after sync:

    SELECT indexname FROM pg_indexes 
WHERE tablename = 'assignment-table'
AND indexname = 'idx_assignment_accepted_unique';

    Result after sync: Index is deleted :x:

Critical finding: Both indexes (manually created and API-created) are deleted during collection synchronization. This indicates that NocoBase removes any index that is not explicitly defined in its collection configuration, regardless of how the index was created.

Expected behavior

Option A (recommended): The index should not be deleted. Manually created database indexes should be preserved during collection synchronization, as they do not conflict with NocoBase’s operation and represent valid database constraints.

Option B (acceptable alternative): If NocoBase intends to manage all database objects (including indexes), there should be:

  • A warning message informing the user that external indexes will be removed during sync
  • A configuration option to prevent deletion of specific indexes
  • A way to define partial unique indexes within NocoBase’s collection configuration (currently not possible in v2.0.53)

Why this index is critical for data integrity

This is not a performance optimization; it is a hard business constraint that must be enforced at the database level:

In our use case, we have assignment records where:

  • Each assignment record belongs to a parent entity (e.g., a service request)
  • Each assignment can have a status: open, matched, accepted, completed
  • Business rule: Only one assignment per parent entity can be in accepted status at any given time

Without this constraint at the database level:

  • A race condition could allow two transactions to simultaneously accept different assignments for the same parent entity
  • Application-level checks alone are insufficient in high-concurrency scenarios
  • The partial unique index with WHERE status = 'accepted' is the cleanest, most efficient way to enforce this

The index is not optional; it is necessary for data consistency and cannot be replaced by application-level validation alone.

Questions for the NocoBase team

  1. Is this behavior intentional? Is NocoBase designed to own all DDL objects and clean up external indexes during sync?

  2. Is there a documented way to create partial unique indexes in NocoBase? (We could not find one in v2.0.53 documentation)

  3. Would it be possible to preserve manually created indexes during sync, or at minimum, provide a warning or opt-in configuration to prevent their deletion?

Additional context

  • This affects any use case where database-level constraints are required and cannot be fully managed through NocoBase’s UI
  • The index works perfectly during normal NocoBase operation; it only becomes a problem during schema synchronization
  • This is reproducible and consistent (100% reproduction rate)
  • No errors or warnings are logged to indicate why the index is being deleted

Suggestion for resolution

Consider one of the following approaches:

  1. Preserve external indexes: Modify the sync logic to not delete indexes that are not explicitly defined in NocoBase’s collection configuration
  2. Add partial index support: Extend the collection configuration to allow defining partial unique indexes with WHERE clauses in the UI
  3. Document the behavior: If this is intentional design, document it clearly so users know not to create manual indexes
  4. Add an opt-out: Provide a collection-level setting to exclude specific indexes from automatic cleanup

Closing

Thank you very much for taking the time to read and consider this report. We have searched extensively for a solution that works within NocoBase’s design philosophy, but unfortunately have not found one that allows us to maintain this critical database constraint while using NocoBase’s synchronization feature.

Of course, you know your product far better than we do. If there is an approach or feature we have overlooked, or if this behavior is intentional and there is a recommended way to handle database-level constraints in NocoBase, we would deeply appreciate your guidance.

We are grateful for the NocoBase project and the work you put into it. This report is not intended as criticism, but rather as a documentation of a limitation we encountered so that it might help you, other users, or future development efforts.

Thank you again for your time and consideration.