cbsnexora-backend/sql/kyc/create_kyc_tables_postgresql.sql

-- =============================================================================
-- KYC Module – Table Creation Script (PostgreSQL)
-- =============================================================================
-- DB Connection (from config/docker/env/postgresql.env & application.properties):
--   Host     : localhost
--   Port     : 5432
--   Database : fineract_default   ← tenant database (your actual data lives here)
--   Username : postgres
--   Password : skdcnwauicn2ucnaecasdsajdnizucawencascdca
--
-- Run command:
--   psql -h localhost -p 5432 -U postgres -d fineract_default -f create_kyc_tables_postgresql.sql
--
-- (It will prompt for password: skdcnwauicn2ucnaecasdsajdnizucawencascdca)
-- =============================================================================

\connect fineract_default;

-- =============================================================================
-- TABLE: m_client_kyc
-- One KYC record per client (enforced by UNIQUE on client_id).
-- =============================================================================

CREATE TABLE IF NOT EXISTS m_client_kyc (
    id                      BIGSERIAL       NOT NULL,
    client_id               BIGINT          NOT NULL,

    -- kyc_status : 100=PENDING | 200=IN_PROGRESS | 300=VERIFIED | 400=REJECTED | 500=EXPIRED
    kyc_status              SMALLINT        NOT NULL DEFAULT 100,

    -- kyc_type   : 1=INDIVIDUAL | 2=NON_INDIVIDUAL
    kyc_type                SMALLINT        NOT NULL,

    -- risk_category : 1=LOW (re-KYC 10yr) | 2=MEDIUM (8yr) | 3=HIGH (2yr)
    risk_category           SMALLINT        NOT NULL DEFAULT 1,

    -- PAN (Permanent Account Number – Income Tax Dept)
    -- Format: 5 uppercase letters + 4 digits + 1 uppercase letter  e.g. ABCDE1234F
    pan_number              VARCHAR(10),
    pan_verified            BOOLEAN         NOT NULL DEFAULT FALSE,
    pan_verified_on         DATE,

    -- Aadhaar – ONLY masked form stored (last 4 digits visible) per UIDAI directive
    aadhaar_number_masked   VARCHAR(12),
    aadhaar_verified        BOOLEAN         NOT NULL DEFAULT FALSE,
    aadhaar_verified_on     DATE,

    -- GSTIN (15-char GST Identification Number – mandatory for NON_INDIVIDUAL)
    gstin_number            VARCHAR(15),
    gstin_verified          BOOLEAN         NOT NULL DEFAULT FALSE,
    gstin_verified_on       DATE,

    -- CKYCRR – Central KYC Records Registry number (issued by CERSAI, 14 digits)
    ckyc_number             VARCHAR(14),

    -- Lifecycle timestamps
    initiated_on            DATE,
    verified_on             DATE,
    rejected_on             DATE,
    expiry_date             DATE,
    remarks                 VARCHAR(500),

    -- Audit fields (AbstractAuditableWithUTCDateTimeCustom)
    created_by              BIGINT          NOT NULL,
    created_on_utc          TIMESTAMP WITH TIME ZONE NOT NULL,
    last_modified_by        BIGINT          NOT NULL,
    last_modified_on_utc    TIMESTAMP WITH TIME ZONE NOT NULL,
    version                 BIGINT          NOT NULL DEFAULT 0,

    CONSTRAINT pk_m_client_kyc PRIMARY KEY (id),
    CONSTRAINT uq_m_client_kyc_client_id UNIQUE (client_id),
    CONSTRAINT fk_m_client_kyc_client_id
        FOREIGN KEY (client_id) REFERENCES m_client(id)
        ON DELETE RESTRICT ON UPDATE RESTRICT
);

COMMENT ON TABLE  m_client_kyc                  IS 'RBI-mandated KYC record – one per client';
COMMENT ON COLUMN m_client_kyc.kyc_status       IS '100=PENDING,200=IN_PROGRESS,300=VERIFIED,400=REJECTED,500=EXPIRED';
COMMENT ON COLUMN m_client_kyc.kyc_type         IS '1=INDIVIDUAL, 2=NON_INDIVIDUAL';
COMMENT ON COLUMN m_client_kyc.risk_category    IS '1=LOW(10yr), 2=MEDIUM(8yr), 3=HIGH(2yr) re-KYC period';
COMMENT ON COLUMN m_client_kyc.aadhaar_number_masked IS 'Masked Aadhaar: first 8 digits X, last 4 visible. Full number must NOT be stored per UIDAI directive.';


-- =============================================================================
-- TABLE: m_client_kyc_verification_log
-- Immutable audit trail for every 3rd-party API call.
-- =============================================================================

CREATE TABLE IF NOT EXISTS m_client_kyc_verification_log (
    id                      BIGSERIAL       NOT NULL,
    kyc_id                  BIGINT          NOT NULL,

    -- verification_type: PAN | AADHAAR | GSTIN
    verification_type       VARCHAR(20)     NOT NULL,

    -- verification_status: SUCCESS | FAILURE | PENDING | ERROR
    verification_status     VARCHAR(20)     NOT NULL,

    request_reference       VARCHAR(100),
    api_response            TEXT,
    remarks                 VARCHAR(500),

    -- Audit fields
    created_by              BIGINT          NOT NULL,
    created_on_utc          TIMESTAMP WITH TIME ZONE NOT NULL,
    last_modified_by        BIGINT          NOT NULL,
    last_modified_on_utc    TIMESTAMP WITH TIME ZONE NOT NULL,
    version                 BIGINT          NOT NULL DEFAULT 0,

    CONSTRAINT pk_m_client_kyc_verification_log PRIMARY KEY (id),
    CONSTRAINT fk_m_kyc_verification_log_kyc_id
        FOREIGN KEY (kyc_id) REFERENCES m_client_kyc(id)
        ON DELETE CASCADE ON UPDATE RESTRICT
);

COMMENT ON TABLE m_client_kyc_verification_log IS 'Audit log of all 3rd-party KYC API calls (PAN/Aadhaar/GSTIN)';


-- =============================================================================
-- PERMISSIONS
-- =============================================================================

INSERT INTO m_permission (grouping, code, entity_name, action_name, can_maker_checker)
SELECT 'portfolio', 'READ_CLIENTKYC',   'CLIENTKYC', 'READ',   FALSE
WHERE NOT EXISTS (SELECT 1 FROM m_permission WHERE code = 'READ_CLIENTKYC');

INSERT INTO m_permission (grouping, code, entity_name, action_name, can_maker_checker)
SELECT 'portfolio', 'CREATE_CLIENTKYC', 'CLIENTKYC', 'CREATE', TRUE
WHERE NOT EXISTS (SELECT 1 FROM m_permission WHERE code = 'CREATE_CLIENTKYC');

INSERT INTO m_permission (grouping, code, entity_name, action_name, can_maker_checker)
SELECT 'portfolio', 'UPDATE_CLIENTKYC', 'CLIENTKYC', 'UPDATE', TRUE
WHERE NOT EXISTS (SELECT 1 FROM m_permission WHERE code = 'UPDATE_CLIENTKYC');

INSERT INTO m_permission (grouping, code, entity_name, action_name, can_maker_checker)
SELECT 'portfolio', 'VERIFY_CLIENTKYC', 'CLIENTKYC', 'VERIFY', TRUE
WHERE NOT EXISTS (SELECT 1 FROM m_permission WHERE code = 'VERIFY_CLIENTKYC');

INSERT INTO m_permission (grouping, code, entity_name, action_name, can_maker_checker)
SELECT 'portfolio', 'REJECT_CLIENTKYC', 'CLIENTKYC', 'REJECT', TRUE
WHERE NOT EXISTS (SELECT 1 FROM m_permission WHERE code = 'REJECT_CLIENTKYC');


-- =============================================================================
-- VERIFY CREATION
-- =============================================================================

SELECT tablename AS "Tables created"
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename LIKE 'm_client_kyc%';

SELECT code, entity_name, action_name
FROM m_permission
WHERE entity_name = 'CLIENTKYC';