Skip to main content

EPIC-4 Database Schema

Entity Relationship Diagram​

Core Tables​

fee_heads​

CREATE TABLE fee_heads (
    id UUID PRIMARY KEY,
    code VARCHAR(20) UNIQUE,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(30) NOT NULL, -- TUITION, TRANSPORT, etc.
    is_refundable BOOLEAN DEFAULT false,
    tax_applicable BOOLEAN DEFAULT false,
    tax_percentage DECIMAL(5,2) DEFAULT 0
);

fee_structures​

CREATE TABLE fee_structures (
    id UUID PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    academic_year VARCHAR(9) NOT NULL,
    class_id UUID REFERENCES classes(id),
    category VARCHAR(30) DEFAULT 'GENERAL',
    UNIQUE(academic_year, class_id, category)
);

invoices​

CREATE TABLE invoices (
    id UUID PRIMARY KEY,
    invoice_number VARCHAR(30) UNIQUE,
    student_id UUID REFERENCES students(id),
    total_amount DECIMAL(12,2) NOT NULL,
    paid_amount DECIMAL(12,2) DEFAULT 0,
    balance_amount DECIMAL(12,2) GENERATED ALWAYS AS (total_amount - paid_amount),
    due_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'PENDING'
);

payments​

CREATE TABLE payments (
    id UUID PRIMARY KEY,
    payment_number VARCHAR(30) UNIQUE,
    invoice_id UUID REFERENCES invoices(id),
    amount DECIMAL(12,2) NOT NULL,
    payment_method VARCHAR(20) NOT NULL, -- CASH, UPI, CARD
    gateway VARCHAR(20), -- RAZORPAY, CASHFREE
    gateway_payment_id VARCHAR(100),
    status VARCHAR(20) DEFAULT 'PENDING'
);

Double-Entry Tables​

accounts (Chart of Accounts)​

CREATE TABLE accounts (
    id UUID PRIMARY KEY,
    code VARCHAR(20) UNIQUE,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL, -- ASSET, LIABILITY, REVENUE, EXPENSE
    parent_id UUID REFERENCES accounts(id)
);

ledger_entries​

CREATE TABLE ledger_entries (
    id UUID PRIMARY KEY,
    transaction_id UUID REFERENCES transactions(id),
    account_id UUID REFERENCES accounts(id),
    debit DECIMAL(12,2) DEFAULT 0,
    credit DECIMAL(12,2) DEFAULT 0,
    balance_after DECIMAL(14,2),
    CONSTRAINT check_debit_or_credit CHECK (
        (debit > 0 AND credit = 0) OR (debit = 0 AND credit > 0)
    )
);