Data Models¶

ER diagrams and table definitions for every NeoCore schema. All schemas live in PostgreSQL at 103.216.94.145:5443 / neocoredb.

Schema naming convention: {service-name}_db-linux (e.g. customer-service_db-linux)

customer-service_db-linux¶

13 tables — customer master, KYC, address, contact, nominee, joint account, occupation, security, block list, death marking, customer type

erDiagram
    customer_master {
        bigint id PK
        varchar cust_id UK
        varchar first_name
        varchar last_name
        date date_of_birth
        varchar gender
        varchar cust_type
        varchar status
        timestamp created_at
        timestamp updated_at
    }
    customer_kyc {
        bigint id PK
        varchar cust_id FK
        varchar id_type
        varchar id_number UK
        varchar kyc_status
        date kyc_date
    }
    customer_address {
        bigint id PK
        varchar cust_id FK
        varchar address_type
        varchar address_line1
        varchar address_line2
        varchar city
        varchar state
        varchar pincode
        varchar country
    }
    customer_contact {
        bigint id PK
        varchar cust_id FK
        varchar mobile_number UK
        varchar email
        varchar landline
    }
    customer_nominee {
        bigint id PK
        varchar cust_id FK
        varchar nominee_name
        date nominee_dob
        varchar relationship
        numeric percentage
        varchar guardian_name
    }
    customer_occupation {
        bigint id PK
        varchar cust_id FK
        varchar occupation_type
        varchar employer_name
        numeric annual_income
    }
    customer_security {
        bigint id PK
        varchar cust_id FK
        varchar security_question
        varchar security_answer_hash
        boolean is_watchlisted
        boolean is_blacklisted
    }
    customer_block_list {
        bigint id PK
        varchar cust_id FK
        varchar reason
        timestamp blocked_at
        varchar blocked_by
    }
    customer_death_marking {
        bigint id PK
        varchar cust_id FK
        date death_date
        varchar death_certificate_no
        timestamp marked_at
        varchar marked_by
    }

    customer_master ||--o{ customer_kyc : "has"
    customer_master ||--o{ customer_address : "has"
    customer_master ||--o{ customer_contact : "has"
    customer_master ||--o{ customer_nominee : "has"
    customer_master ||--o{ customer_occupation : "has"
    customer_master ||--|| customer_security : "has"
    customer_master ||--o{ customer_block_list : "has"
    customer_master ||--o| customer_death_marking : "has"

product-service_db-linux¶

10 tables — deposit scheme, rate slabs, loan product, NPA classification, subsidy, GL account linkage, renewal parameters

erDiagram
    deposit_scheme {
        bigint id PK
        varchar scheme_code UK
        varchar scheme_name
        varchar scheme_type
        numeric min_amount
        numeric max_amount
        int min_tenure_months
        int max_tenure_months
        varchar interest_payment_frequency
        varchar status
        varchar gl_account_head
    }
    deposit_rate_slab {
        bigint id PK
        bigint scheme_id FK
        int tenure_from_months
        int tenure_to_months
        numeric rate
        numeric senior_rate
    }
    deposit_renewal_params {
        bigint id PK
        bigint scheme_id FK
        varchar renewal_type
        int notice_period_days
        boolean auto_renew
        numeric premature_penalty_rate
    }
    deposit_ac_head {
        bigint id PK
        bigint scheme_id FK
        varchar gl_account_head
        varchar posting_type
    }
    loan_product {
        bigint id PK
        varchar product_code UK
        varchar product_name
        varchar product_type
        numeric min_amount
        numeric max_amount
        int max_tenure_months
        numeric interest_rate
        varchar repayment_frequency
        varchar status
    }
    loan_npa_classification {
        bigint id PK
        bigint loan_product_id FK
        varchar classification
        int overdue_days_from
        int overdue_days_to
        numeric provision_rate
    }
    loan_subsidy {
        bigint id PK
        bigint loan_product_id FK
        varchar scheme_name
        numeric subsidy_rate
        varchar applicable_category
    }

    deposit_scheme ||--o{ deposit_rate_slab : "has slabs"
    deposit_scheme ||--|| deposit_renewal_params : "has"
    deposit_scheme ||--o{ deposit_ac_head : "linked to"
    loan_product ||--o{ loan_npa_classification : "classified by"
    loan_product ||--o{ loan_subsidy : "eligible for"

deposit-service_db-linux¶

5 tables — term deposit master, sub-account info, joint holders, nominees, authorize, closing

erDiagram
    term_deposit {
        bigint id PK
        varchar deposit_number UK
        varchar cust_id
        varchar product_code
        numeric principal_amount
        numeric interest_rate
        int tenure_months
        date deposit_date
        date maturity_date
        varchar maturity_instructions
        varchar status
        varchar branch_code
        timestamp created_at
    }
    deposit_joint_holder {
        bigint id PK
        varchar deposit_number FK
        varchar joint_cust_id
        int sequence
    }
    deposit_nominee {
        bigint id PK
        varchar deposit_number FK
        varchar nominee_name
        date nominee_dob
        varchar relationship
        numeric percentage
    }
    deposit_authorize {
        bigint id PK
        varchar deposit_number FK
        varchar authorized_by
        timestamp authorized_at
        varchar status
        varchar remarks
    }
    deposit_closing {
        bigint id PK
        varchar deposit_number FK
        date closing_date
        numeric accrued_interest
        numeric tds_amount
        numeric penalty_amount
        numeric net_payout
        varchar transaction_ref
        varchar status
    }
    deposit_sub_acinfo {
        bigint id PK
        varchar deposit_number FK
        varchar parent_deposit_number
        varchar relationship_type
    }

    term_deposit ||--o{ deposit_joint_holder : "has"
    term_deposit ||--o{ deposit_nominee : "has"
    term_deposit ||--|| deposit_authorize : "authorized via"
    term_deposit ||--o| deposit_closing : "closed via"
    term_deposit ||--o| deposit_sub_acinfo : "linked to"

transaction-service_db-linux¶

8 tables — transaction master, cash, transfer, TransAll (composite), exception, reversal, narration, audit

erDiagram
    transaction_master {
        bigint id PK
        varchar transaction_ref UK
        varchar transaction_type
        numeric amount
        varchar currency
        varchar reference_account_id
        date transaction_date
        varchar narration
        varchar status
        timestamp created_at
    }
    transaction_cash {
        bigint id PK
        varchar transaction_ref FK
        varchar denomination_breakup
        varchar cash_flow_type
    }
    transaction_transfer {
        bigint id PK
        varchar transaction_ref FK
        varchar from_account
        varchar to_account
        varchar transfer_mode
    }
    transaction_transall {
        bigint id PK
        varchar batch_ref UK
        varchar narration
        numeric total_amount
        varchar status
    }
    transaction_transall_entry {
        bigint id PK
        varchar batch_ref FK
        varchar account_head
        varchar entry_type
        numeric amount
    }
    transaction_exception {
        bigint id PK
        varchar transaction_ref FK
        varchar exception_reason
        varchar approved_by
        timestamp approved_at
    }

    transaction_master ||--o| transaction_cash : "is"
    transaction_master ||--o| transaction_transfer : "is"
    transaction_master ||--o| transaction_exception : "has"
    transaction_transall ||--o{ transaction_transall_entry : "composed of"

generalledger-service_db-linux¶

9 tables — major head, sub head, account head, GL limit, branch-wise GL, posting entry, audit

erDiagram
    gl_major_head {
        bigint id PK
        varchar major_head_code UK
        varchar major_head_name
        varchar head_type
        varchar status
    }
    gl_sub_head {
        bigint id PK
        varchar sub_head_code UK
        bigint major_head_id FK
        varchar sub_head_name
        varchar status
    }
    gl_account_head {
        bigint id PK
        varchar account_head_code UK
        bigint sub_head_id FK
        varchar account_head_name
        varchar account_type
        numeric balance
        varchar status
        varchar branch_code
    }
    gl_limit {
        bigint id PK
        varchar account_head_code FK
        numeric debit_limit
        numeric credit_limit
        date effective_from
    }
    gl_posting_entry {
        bigint id PK
        varchar account_head_code FK
        varchar entry_type
        numeric amount
        date value_date
        varchar narration
        varchar transaction_ref
        varchar posted_by
        timestamp posted_at
    }

    gl_major_head ||--o{ gl_sub_head : "has"
    gl_sub_head ||--o{ gl_account_head : "has"
    gl_account_head ||--o{ gl_limit : "limited by"
    gl_account_head ||--o{ gl_posting_entry : "receives"

clearing-service_db-linux¶

1 table — cheque clearing master

erDiagram
    clearing_cheque {
        bigint id PK
        varchar cheque_number UK
        varchar micr_code
        varchar drawer_account
        varchar drawee_bank
        numeric amount
        date cheque_date
        varchar clearing_status
        varchar return_reason
        timestamp processed_at
    }

common_db (localhost:5432)¶

3 tables — shared configuration, lookup codes, system parameters

erDiagram
    common_lookup {
        bigint id PK
        varchar lookup_type
        varchar lookup_code UK
        varchar lookup_value
        int display_order
        boolean is_active
    }
    common_system_param {
        bigint id PK
        varchar param_key UK
        varchar param_value
        varchar description
        boolean is_editable
    }
    common_holiday_calendar {
        bigint id PK
        date holiday_date
        varchar holiday_name
        varchar branch_code
        int year
    }

2 tables — share configuration, share account

erDiagram
    share_config {
        bigint id PK
        numeric face_value
        int max_shares_per_member
        numeric admission_fee
        date effective_date
    }
    share_account {
        bigint id PK
        varchar account_number UK
        varchar member_id
        int number_of_shares
        numeric total_value
        varchar status
        date opened_date
        timestamp created_at
    }

    share_config ||--o{ share_account : "governs"

system-setup-service_db-linux¶

1 table — branch configuration master

erDiagram
    branch_configuration {
        bigint id PK
        varchar branch_code UK
        varchar branch_name
        varchar ifsc_code
        varchar address
        varchar working_days
        time opening_time
        time closing_time
        boolean is_head_office
        varchar status
    }

Cross-Schema References¶

These are application-level references (not foreign key constraints) that link data across schemas.

Source Schema	Source Field	Target Schema	Target Field	Usage
`deposit-service_db-linux.term_deposit`	`cust_id`	`customer-service_db-linux.customer_master`	`cust_id`	Customer validation on deposit creation
`deposit-service_db-linux.term_deposit`	`product_code`	`product-service_db-linux.deposit_scheme`	`scheme_code`	Product rate lookup
`deposit-service_db-linux.deposit_closing`	`transaction_ref`	`transaction-service_db-linux.transaction_master`	`transaction_ref`	Closing payout posting
`deposit-service_db-linux.deposit_authorize`	`deposit_number`	`transaction-service_db-linux.transaction_master`	`reference_account_id`	Authorization transaction
`generalledger-service_db-linux.gl_account_head`	`account_head_code`	`product-service_db-linux.deposit_ac_head`	`gl_account_head`	Product → GL head mapping
`generalledger-service_db-linux.gl_account_head`	`account_head_code`	`product-service_db-linux.loan_product`	(via loan_ac_head)	Loan → GL head mapping
`transaction-service_db-linux.transaction_master`	`reference_account_id`	`deposit-service_db-linux.term_deposit`	`deposit_number`	Deposit transaction reference
`share-service_db-linux.share_account`	`member_id`	`customer-service_db-linux.customer_master`	`cust_id`	Member → Customer linkage
`transaction-service_db-linux.transaction_transall_entry`	`account_head`	`generalledger-service_db-linux.gl_account_head`	`account_head_code`	TransAll GL postings