-- 04_composite_pk_ledger_lines.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Composite PK for transaction ledger lines.
-- 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;

-- Composite PK for double‑entry ledger lines inside a transaction
CREATE TABLE IF NOT EXISTS bank.ledger_lines (
  tx_id BIGINT NOT NULL REFERENCES bank.transactions(tx_id),
  line_no SMALLINT NOT NULL,
  account_id BIGINT NOT NULL REFERENCES bank.accounts(account_id),
  amount NUMERIC(18,2) NOT NULL, -- positive for debit/credit as per convention
  side TEXT NOT NULL CHECK (side IN ('debit','credit')),
  PRIMARY KEY (tx_id, line_no)
);
