schema.sql

-- E-Wallet Database Schema
-- This file is loaded by Docker on first startup via docker-entrypoint-initdb.d/
-- Hibernate ddl-auto=update will also create tables if they don't exist.
-- This file is provided for transparency and manual DB setup.

CREATE DATABASE IF NOT EXISTS ewallet_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ewallet_db;

-- ── Users ──────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    user_id      VARCHAR(36)  NOT NULL PRIMARY KEY,
    email        VARCHAR(255) NOT NULL UNIQUE,
    phone        VARCHAR(15)  NOT NULL UNIQUE,
    full_name    VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status       ENUM('ACTIVE', 'SUSPENDED', 'CLOSED') NOT NULL DEFAULT 'ACTIVE',
    created_at   DATETIME     NOT NULL,
    updated_at   DATETIME     NOT NULL,

    -- WHY INDEX ON EMAIL AND PHONE:
    -- Login lookup happens on email. Recipient lookup for transfers happens on phone.
    -- Both must be fast — O(1) with a unique index.
    INDEX idx_user_email (email),
    INDEX idx_user_phone (phone)
) ENGINE=InnoDB;

-- ── Wallets ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS wallets (
    wallet_id  VARCHAR(36)    NOT NULL PRIMARY KEY,
    user_id    VARCHAR(36)    NOT NULL UNIQUE,
    -- WHY DECIMAL(19,2):
    -- DECIMAL is exact. (19,2) supports up to ₹9,999,999,999,999,999.99
    -- which covers even the largest fintech volumes with no floating point errors.
    balance    DECIMAL(19,2)  NOT NULL DEFAULT 0.00,
    status     ENUM('ACTIVE', 'FROZEN', 'CLOSED') NOT NULL DEFAULT 'ACTIVE',
    -- WHY VERSION COLUMN:
    -- This is the optimistic locking counter. JPA increments it on every UPDATE.
    -- Stale reads are detected before any write completes.
    version    BIGINT         NOT NULL DEFAULT 0,
    created_at DATETIME       NOT NULL,
    updated_at DATETIME       NOT NULL,

    CONSTRAINT fk_wallet_user FOREIGN KEY (user_id) REFERENCES users(user_id),
    -- WHY INDEX ON USER_ID:
    -- Every wallet operation starts with "find wallet by userId" — this index
    -- makes that lookup O(log n) instead of O(n full table scan).
    INDEX idx_wallet_user_id (user_id)
) ENGINE=InnoDB;

-- ── Transaction Ledger ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS transaction_ledger (
    ledger_id        VARCHAR(36)    NOT NULL PRIMARY KEY,
    from_user_id     VARCHAR(36),
    to_user_id       VARCHAR(36),
    amount           DECIMAL(19,2)  NOT NULL,
    transaction_type ENUM('CREDIT', 'DEBIT', 'ADD_MONEY') NOT NULL,
    status           ENUM('PENDING', 'SUCCESS', 'FAILED', 'REVERSED') NOT NULL DEFAULT 'PENDING',
    -- WHY REFERENCE_ID:
    -- Groups the DEBIT + CREDIT pair from a single transfer.
    -- One referenceId → two ledger entries = complete audit trail for any transaction.
    reference_id     VARCHAR(36),
    -- WHY IDEMPOTENCY_KEY ON THE LEDGER (not just Redis):
    -- Redis is volatile. If Redis is flushed and a retry comes in,
    -- we'd process the payment again. DB is the permanent fallback guarantee.
    idempotency_key  VARCHAR(100) UNIQUE,
    description      VARCHAR(500),
    created_at       DATETIME       NOT NULL,

    -- WHY THESE INDEXES:
    -- from_user_id + to_user_id: transaction history queries filter by user
    -- reference_id: finding the pair of debit/credit entries for a transfer
    -- created_at: ordering by recency for history pagination
    -- idempotency_key: fast duplicate detection
    INDEX idx_ledger_from_user (from_user_id),
    INDEX idx_ledger_to_user (to_user_id),
    INDEX idx_ledger_reference (reference_id),
    INDEX idx_ledger_created_at (created_at),
    INDEX idx_ledger_idempotency (idempotency_key)
) ENGINE=InnoDB;