-- 02_surrogate_key_identity.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Surrogate PK using GENERATED AS IDENTITY for bank tables.
-- 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.
SET search_path = bank, public;

-- Show identity on auxiliary table
CREATE TABLE IF NOT EXISTS bank.audit_log (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  entity TEXT NOT NULL,
  entity_id BIGINT NOT NULL,
  action TEXT NOT NULL,
  occurred_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO bank.audit_log (entity, entity_id, action) VALUES ('customers', 1, 'seed') RETURNING id;
