-- Enable UUID extension for secure, non-sequential primary keys CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; --- --- 1. CLIENTS TABLE --- CREATE TABLE clients ( client_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, phone_number VARCHAR(20), card_number VARCHAR(20) UNIQUE NOT NULL, -- The physical or digital Heritage Card ID membership_tier VARCHAR(20) DEFAULT 'Silver' CHECK (membership_tier IN ('Silver', 'Gold', 'Royal')), is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); --- --- 2. BUSINESS PARTNERS (MERCHANTS) TABLE --- CREATE TABLE business_partners ( partner_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), company_name VARCHAR(100) NOT NULL, industry_category VARCHAR(50) NOT NULL, -- e.g., 'Hospitality', 'Retail', 'Aviation' contact_email VARCHAR(100) UNIQUE NOT NULL, contact_phone VARCHAR(20), point_multiplier NUMERIC(3, 2) DEFAULT 1.00, -- Custom multiplier for special partner campaigns (e.g., 1.5x points) is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); --- --- 3. TRANSACTIONS TABLE --- CREATE TABLE transactions ( transaction_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), client_id UUID NOT NULL REFERENCES clients(client_id) ON DELETE RESTRICT, partner_id UUID NOT NULL REFERENCES business_partners(partner_id) ON DELETE RESTRICT, transaction_reference VARCHAR(100) UNIQUE NOT NULL, -- POS or external receipt ID gross_amount_cents INTEGER NOT NULL, -- Stored in cents to avoid floating-point rounding errors ($100.00 = 10000) currency VARCHAR(3) DEFAULT 'USD', transaction_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); --- --- 4. POINTS LEDGER TABLE (Financial-grade audit trail) --- CREATE TABLE points_ledger ( ledger_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), client_id UUID NOT NULL REFERENCES clients(client_id) ON DELETE RESTRICT, partner_id UUID NOT NULL REFERENCES business_partners(partner_id) ON DELETE RESTRICT, transaction_id UUID REFERENCES transactions(transaction_id) ON DELETE SET NULL, -- Can be NULL for manual adjustments points_delta INTEGER NOT NULL, -- Positive for Earned, Negative for Redeemed/Expired transaction_type VARCHAR(20) NOT NULL CHECK (transaction_type IN ('Earned', 'Redeemed', 'Adjustment', 'Expired')), description TEXT, -- e.g., "Points earned at Partner X", "Points redeemed for Royal Tier reward" created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); --- --- 5. PERFORMANCE AND SEARCH INDEXES --- CREATE INDEX idx_clients_card_number ON clients(card_number); CREATE INDEX idx_transactions_client_id ON transactions(client_id); CREATE INDEX idx_transactions_partner_id ON transactions(partner_id); CREATE INDEX idx_points_ledger_client_id ON points_ledger(client_id);