How would you implement soft deletes in Clarity to preserve history?

How would you implement soft deletes in Clarity to preserve history?

• A. Hard delete only
• B. Archive nightly
• C. Use a deletion flag or status and filter in queries
• D. Use a temporary table

Answer: (C)

Soft deletes are implemented by marking a row as deleted with a flag or status, rather than removing it from the table, so the history is preserved. In practice, you add a deletion indicator, like a boolean is_deleted or a status field such as 'active' vs 'deleted'. When you delete, you update that flag to indicate deletion instead of performing a physical delete, and your queries filter to show only non-deleted records. This approach gives you an auditable trail you can restore from if needed, supports historical reporting, and keeps referential integrity intact since nothing is physically removed. For performance and usability, index the deletion flag and apply a consistent filter (or use a view/global query filter) so active data excludes deleted rows automatically while still retaining access to the full history for audits or undelete operations. Hard deletes erase history, which makes auditing and restoration impossible. Archiving data nightly can preserve history but adds complexity and latency for current queries. A temporary table would not provide durable history. The deletion flag method strikes a balance between preserving history and keeping active queries clean and efficient.

Soft deletes are implemented by marking a row as deleted with a flag or status, rather than removing it from the table, so the history is preserved. In practice, you add a deletion indicator, like a boolean is_deleted or a status field such as 'active' vs 'deleted'. When you delete, you update that flag to indicate deletion instead of performing a physical delete, and your queries filter to show only non-deleted records.

This approach gives you an auditable trail you can restore from if needed, supports historical reporting, and keeps referential integrity intact since nothing is physically removed. For performance and usability, index the deletion flag and apply a consistent filter (or use a view/global query filter) so active data excludes deleted rows automatically while still retaining access to the full history for audits or undelete operations.

Hard deletes erase history, which makes auditing and restoration impossible. Archiving data nightly can preserve history but adds complexity and latency for current queries. A temporary table would not provide durable history. The deletion flag method strikes a balance between preserving history and keeping active queries clean and efficient.