-- 08_soft_delete_flag.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Soft delete for records (e.g., archived cards).
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.

-- Soft delete pattern using deleted_at
ALTER TABLE IF EXISTS bank.cards ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;

-- Soft delete a card
UPDATE bank.cards SET active = FALSE, deleted_at = now() WHERE card_id = 1;

-- Index active cards with partial index
CREATE INDEX IF NOT EXISTS cards_active_idx ON bank.cards (account_id) WHERE active AND deleted_at IS NULL;
