-- Soft delete: add deleted_at timestamp
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMP;

-- Soft delete a product
UPDATE products
SET deleted_at = NOW()
WHERE product_id = 42;

-- Active products query (excludes soft-deleted)
SELECT * FROM products WHERE deleted_at IS NULL;

-- Create a view for convenience
CREATE OR REPLACE VIEW active_products AS
SELECT * FROM products WHERE deleted_at IS NULL;

-- Hard delete old soft-deleted records (cleanup job)
DELETE FROM products
WHERE deleted_at < NOW() - INTERVAL '90 days';