Use Timestamp in Deleted Field

[SoBeGuy]

Espo allows for creating unique indexes, which is beneficial to guarantee no duplicate records. However, if you delete a record, it's no longer possible to create a new record using the same value in a unique field as in the deleted record. This is because Espo "deletes" a record by setting the "deleted" field to 1, rather then physically deleting the record from the database. The obvious solution is to include the "deleted" field in the unique index. This would allow for two identical records, one active and one deleted. However, if you try to delete a second identical record, you're back to square one. It's not possible to have two identical deleted records with a unique index.

To solve this, I propose changing the "deleted" field to a timestamp field. When a record is deleted, instead of setting the deleted field to 1, Espo should set it to the current timestamp. This would allow for having an unlimited number of identical deleted records with conflicting unique index values, since each one would have a different timestamp. Since all active records have the deleted field set to 0, the unique index would work properly, preventing creation of active records that violate the unique index.

[yuri]

We already use a similar technique in the User entity type. You can replicate it for your entity type.

> instead of setting the deleted field to 1

We rather don't make such radical changes. The system is already widely used and customized. This would be a huge breaking change.

[yuri]

https://github.com/search?q=repo%3Aespocrm%2Fespocrm%20deleteId&type=code

[SoBeGuy]

We already use a similar technique in the User entity type. You can replicate it for your entity type.

Yes, your technique seems similar.

We rather don't make such radical changes. The system is already widely used and customized. This would be a huge breaking change.

It should be backwards compatible, Just treat any non-zero value as deleted.

[yuri]

Changing a boolean to integer|null is a compatibility break.

[SoBeGuy]

OK, no problem. I already got it working for the Contact entity using your example. Thank you again for your amazing support!

[yuri]

I'll consider making this feature configurable in the future. So that if a parameter is true in metadata, the deleteId attribute in automatically created.