Database Schema Reference

The Dhanam Finance platform uses MongoDB as its primary database. This document details all collections, their schemas, relationships, and indexes.

Entity Relationship Diagram

Collections Overview

customers Collection

Stores customer profile information.

{
  _id: ObjectId,
  code: string,              // Unique, e.g., "DF-0001"
  name: string,
  dob: string,               // ISO date
  age: number,
  gender: "Male" | "Female" | "Other",
  phone: string,             // 10 digits
  email?: string,
  address: string,
  pincode: string,           // 6 digits
  education?: string,
  occupation?: string,
  branch: ObjectId,          // Reference to branches
  
  nominee: {
    name: string,
    relationship: string,
    phone: string,
    dob: string,
    age: number
  },
  
  demat?: {
    dpIdNo?: string,
    clientIdNo?: string
  },
  
  bank: {
    accountNo: string,
    bankName: string,
    IFSC: string,
    branch: string,
    accountHolderName: string
  },
  
  dateOfJoining: string,
  status: "pending" | "approved" | "rejected",
  createdBy: ObjectId,
  createdAt: Date,
  updatedAt: Date
}

Indexes

• { code: 1 } - Unique
• { phone: 1 }
• { branch: 1 }
• { status: 1 }
• { createdAt: -1 }

ncd_series Collection

Stores NCD series configurations with interest rates and tenure.

{
  _id: ObjectId,
  code: string,              // Unique, e.g., "NCD-2024-01"
  name: string,
  ISIN: string,              // 12 chars, e.g., "INE123A01012"
  interestRate: number,      // e.g., 12.5 for 12.5%
  seniorCitizenRate: number, // Additional rate for seniors
  tenureMonths: number,      // e.g., 36 for 3 years
  issueOpenDate: Date,
  maturityDate: Date,
  
  active: boolean,
  
  // Calculated totals
  totalInvestments?: number,
  totalAmount?: number,
  
  createdBy: ObjectId,
  createdAt: Date,
  updatedAt: Date
}

Indexes

• { code: 1 } - Unique
• { active: 1 }
• { issueOpenDate: 1 }
• { maturityDate: 1 }

ncd_investments Collection

Stores individual customer investments in NCD series.

{
  _id: ObjectId,
  customerId: ObjectId,      // Reference to customers
  seriesId: ObjectId,        // Reference to ncd_series
  
  folioNumber: string,       // Unique, e.g., "NCD-0001"
  applicationId: string,
  
  principal: number,         // Investment amount
  interestRate: number,      // Annual rate (may differ from series)
  
  investmentDate: Date,
  maturityDate: Date,
  
  // Interest payments
  interestPayments: [{
    dueDate: Date,
    amount: number,
    grossAmount: number,
    tds: number,
    status: "pending" | "paid",
    paidDate?: Date
  }],
  
  status: "active" | "matured" | "redeemed" | "transferred",
  
  // For transfers
  transferredFrom?: ObjectId,
  transferredTo?: ObjectId,
  
  // For rollovers
  rolledOverFrom?: ObjectId,
  
  createdBy: ObjectId,
  createdAt: Date,
  updatedAt: Date
}

Indexes

• { customerId: 1 }
• { seriesId: 1 }
• { folioNumber: 1 } - Unique
• { status: 1 }
• { maturityDate: 1 }

customer_requests Collection

Stores requests submitted by customers via mobile app.

{
  _id: ObjectId,
  customerId: ObjectId,
  
  type: "redeem" | "transfer" | "profile_update" | 
        "document_update" | "new_investment",
  
  status: "pending" | "approved" | "rejected" | 
          "sent_back" | "resubmitted",
  
  // Request details (varies by type)
  data: {
    investmentId?: ObjectId,
    amount?: number,
    reason?: string,
    changes?: object,
    // ... type-specific fields
  },
  
  // For transfers
  destinationCustomerId?: ObjectId,
  
  // Approval workflow
  approvedBy?: ObjectId,
  approvedAt?: Date,
  rejectedBy?: ObjectId,
  rejectedAt?: Date,
  rejectionReason?: string,
  
  createdAt: Date,
  updatedAt: Date
}

Audit Log Collections (TimeSeries)

MongoDB TimeSeries collections for efficient time-based querying of audit records.

// All audit collections follow this structure
{
  timestamp: Date,           // Time field (required for TimeSeries)
  metadata: {                // Meta field for grouping
    collectionType: string   // e.g., "customer", "ncd"
  },
  
  // Common fields
  actorId: ObjectId,         // User who performed action
  actorIp: string,
  action: string,            // e.g., "CUSTOMER_CREATE"
  
  // Entity-specific
  customerId?: ObjectId,
  ncdSeriesId?: ObjectId,
  investmentId?: ObjectId,
  
  // Change details
  details: {
    changes?: object,
    previousValue?: object,
    newValue?: object,
    reason?: string
  }
}

Audit Actions

Next Steps

• API Reference →
• Authentication & Authorization →