1. Data Schema (Database Model)
Single source of truth: one database links members (PNO), loans, monthly deductions (NONOPR), employees, salary, and settlement.
erDiagram
members ||--o{ loans : "has"
members ||--o| member_share : "has"
members ||--o| member_deposits : "has"
members ||--o{ member_dividend : "has"
members ||--o{ member_monthly_deduction : "monthly"
members ||--o| settlement_payment : "exit"
loans ||--o{ loan_schedule : "per month"
employees ||--o{ salary_inputs : "per month"
employees ||--o{ salary_result : "per month"
members {
string pno PK
string name
string code
string branch
string bank_account
string ifsc
}
loans {
int id PK
string pno FK
float principal_amt
int no_installments
float installment_amt
float interest_rate_monthly
}
loan_schedule {
int id PK
int loan_id FK
string month_year
float principal_balance
float interest_due
float total_due
float rest_balance
}
member_monthly_deduction {
int id PK
string pno FK
string month_year
float prv_balance
float total
float rec_amt
float bal_amt
}
employees {
string employee_no PK
string name
string designation
float pb_rate
string bank_name
}
salary_result {
int id PK
string employee_no FK
string month_year
float gross_salary
float net_salary
float bank_canara
float bank_coop
float bank_sbi
}
settlement_payment {
int id PK
string pno FK
string settlement_date
float amount_realisable
float net_payable
string bank_account
}
Core Tables
| Table |
Key |
Purpose |
| members | pno (PK) | Member master: name, branch, bank, status |
| employees | employee_no (PK) | Staff master: name, designation, bank, basic, pb_rate |
| loans | id (PK), pno (FK) | One row per loan; principal, installments, rate |
| loan_schedule | id (PK), loan_id (FK) | Monthly snapshot: balance, interest due, rest balance |
| member_share | pno (FK) | Share: book, page, amount (for settlement) |
| member_deposits | pno (FK) | CD balance, interest @6.25% P.A (for settlement) |
| member_monthly_deduction | month_year, pno | NONOPR: prv_balance, total, rec_amt, bal_amt |
| salary_result | month_year, employee_no | Gross, deductions, net, bank-wise split |
| settlement_payment | pno (FK) | Amount realisable, deductions, net payable, cheque |
| E-Store: product, product_category, cart, cart_item, order, order_item, invoice — order.pno links to members for “charge to member account”. See E-STORE_DESIGN.md. |
2. System Architecture (How It All Connects)
flowchart TB
subgraph User[" "]
Browser[Users - Browser]
end
subgraph Web["Web Application"]
UI[Web UI - Admin, Month-end, Reports]
Store[Online Store - Products, Cart, Order, Invoice]
end
subgraph Backend["Backend API"]
API[REST API]
Calc[Calculation Engine - Interest, Installment, Salary, Settlement]
EStore[E-Store API - Catalog, Cart, Orders, Member account]
end
subgraph Data["Data & Storage"]
DB[(Database - RDS PostgreSQL)]
S3[(S3 - Reports, PDFs)]
end
Browser --> UI
Browser --> Store
UI --> API
Store --> EStore
API --> Calc
API --> EStore
Calc --> DB
EStore --> DB
Calc --> S3
EStore --> S3
API --> DB
Data Flow (Month-End)
- Installment run — Reads members + loans + last loan_schedule → computes new balance, interest, total due → writes loan_schedule for the month.
- NONOPR run — Reads previous month BAL AMT (PRV BALANCE) + loan_schedule (installment due) + branch charges → computes TOTAL → user enters REC AMT → writes member_monthly_deduction (BAL AMT).
- Salary run — Reads employees + salary_inputs (attendance, sales) → computes gross, deductions, debtor recovery, net → writes salary_result; generates PDF slips.
- Settlement — On demand: reads member, share, deposits, dividend, loans, member_monthly_deduction for PNO → computes amount realisable, deductions, net payable → writes settlement_payment; generates Settlement form PDF.
- E-Store — Browse products → add to cart → place order (delivery address, payment: Online / COD / Member account with PNO) → create order + order_items; generate invoice (PDF); if member account, feed into member_monthly_deduction.
3. Technology Stack
| Layer |
Technology |
Purpose |
| Frontend | HTML5, CSS3, JavaScript (or React) | Web UI: data entry, month-end runs, reports |
| Backend | Node.js (Express) or Python (FastAPI) | REST API, calculation engine, auth |
| Database | PostgreSQL | Single source of truth (members, loans, salary, settlement) |
| File storage | AWS S3 | Generated reports, PDF slips, Settlement forms |
| Compute | AWS EC2 or App Runner / ECS | Run backend + optional static frontend |
| Database hosting | Amazon RDS (PostgreSQL) or Aurora | Managed DB, backups, scaling |
| Auth | JWT / session; optional AWS Cognito | Login, roles (admin, accounts, read-only) |
| Reports / PDF | Server-side (Puppeteer / report lib) | Installment, NONOPR, salary slip, Settlement form, E-store invoice |
| E-Store | Same stack + S3/CloudFront for product images | Product listing, cart, orders, invoice; optional Payment Gateway |
4. Backend Architecture
- API layer — REST endpoints for CRUD (members, employees, loans, etc.) and for “run” actions (run installment, run NONOPR, run salary, create settlement).
- Calculation service — Stateless functions implementing your rules (interest on balance, TOTAL/BAL AMT, gross = max(A,B), settlement formula). Same logic for web and future integrations.
- Data access layer — Single place to read/write the database; no business logic in SQL beyond queries.
- Audit — Log every “run” (who, when, month) in audit_log or CloudWatch.
- Reports — On “generate report”, backend builds data from DB, renders PDF, stores in S3 (optional), returns download link or file.
- E-Store API — Catalog (products, categories), cart (session/user), orders (create, status), invoice (generate PDF); if payment = member account, create/update member_monthly_deduction or member_orders.
5. AWS Cloud Architecture
All components run on AWS: compute, database, and file storage in the same cloud for security, backups, and scaling.
flowchart TB
Internet[Internet - Users]
ALB[Application Load Balancer - HTTPS]
EC2[EC2 / App Runner - Backend API]
RDS[(Amazon RDS - PostgreSQL)]
S3Web[S3 - Static Web / PDFs]
S3Reports[S3 - Reports & Backups]
Secrets[AWS Secrets Manager - DB credentials]
Internet --> ALB
ALB --> EC2
EC2 --> RDS
EC2 --> S3Web
EC2 --> S3Reports
EC2 --> Secrets
AWS Services Used
| AWS Service |
Use |
| Amazon VPC | Private network; subnets for ALB (public), backend and RDS (private). |
| EC2 or App Runner / ECS | Run backend API; optional static frontend. |
| Application Load Balancer (ALB) | HTTPS traffic to backend; SSL termination (ACM certificate). |
| Amazon RDS (PostgreSQL) or Aurora | Managed database; automated backups, patches. |
| Amazon S3 | Generated PDFs (reports, slips, Settlement form); static website if needed; backups. |
| Secrets Manager / Parameter Store | DB credentials, API keys (not in code). |
| CloudWatch | Logs, metrics, alarms. |
| IAM | Roles for EC2/App Runner to access RDS and S3; no long-term keys on disk. |
How It Connects in the Cloud
- User → Browser → ALB (HTTPS) → EC2 / App Runner (backend).
- Backend → RDS (private subnet) for all reads/writes; credentials from Secrets Manager.
- Backend → S3: upload PDFs; return signed URL or stream for download.
- Optional: Static frontend (HTML/JS) from S3 + CloudFront; API calls go to ALB.
- Backups: RDS automated backups; S3 for report archive; DB snapshots for disaster recovery.
6. Online E-Store
Customers can browse products, add to cart, place order, and get an invoice. Members can optionally pay via member account (PNO); order amount then feeds into the same deduction system (NONOPR).
- Product listing — Categories, search, product detail (name, image, price, stock).
- Cart — Add/remove, update quantity; persist by session or user.
- Checkout — Delivery address, payment: Online / COD / Member account (PNO).
- Order & invoice — Order number, items, totals; PDF invoice (same S3/report engine).
Full specification (web): → E-Commerce Full Specification — product details, catalog, customer & admin pages, accounts, order flows, architecture (all in one page).
7. Security & Access
- Network: RDS not exposed to internet; only backend in VPC can connect.
- Auth: Users log in via backend (or Cognito); JWT/session for API calls.
- Roles: Admin (full), Accounts (run month-end, edit data), Auditor (read-only).
- Data in transit: HTTPS only (ALB + ACM certificate).
- Data at rest: RDS encryption; S3 server-side encryption.