DB Functions & Triggers¶
PostgreSQL functions and triggers used across NeoCore schemas. All migrations are managed by Flyway — these objects are created as part of V{n}__*.sql migration files.
customer_db¶
Function: fn_generate_cust_id()¶
Generates the next sequential customer ID in the format CUST-{yyyyMMdd}-{5-digit sequence}.
CREATE OR REPLACE FUNCTION customer_db.fn_generate_cust_id()
RETURNS VARCHAR AS $$
DECLARE
v_date VARCHAR := TO_CHAR(NOW(), 'YYYYMMDD');
v_seq INT;
v_cust_id VARCHAR;
BEGIN
SELECT COALESCE(MAX(CAST(SPLIT_PART(cust_id, '-', 3) AS INT)), 0) + 1
INTO v_seq
FROM customer_db.customer_master
WHERE cust_id LIKE 'CUST-' || v_date || '-%';
v_cust_id := 'CUST-' || v_date || '-' || LPAD(v_seq::TEXT, 5, '0');
RETURN v_cust_id;
END;
$$ LANGUAGE plpgsql;
Trigger: trg_customer_updated_at¶
Updates updated_at on any modification to customer_master.
CREATE OR REPLACE FUNCTION customer_db.fn_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at := NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_customer_updated_at
BEFORE UPDATE ON customer_db.customer_master
FOR EACH ROW EXECUTE FUNCTION customer_db.fn_set_updated_at();
Trigger: trg_block_list_audit¶
Inserts an audit row into customer_audit_log whenever a customer is added to or removed from the block list.
CREATE OR REPLACE FUNCTION customer_db.fn_block_list_audit()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO customer_db.customer_audit_log
(cust_id, action, performed_by, performed_at)
VALUES
(NEW.cust_id, TG_OP || '_BLOCK_LIST', current_user, NOW());
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_block_list_audit
AFTER INSERT OR DELETE ON customer_db.customer_block_list
FOR EACH ROW EXECUTE FUNCTION customer_db.fn_block_list_audit();
deposit_db¶
Function: fn_generate_deposit_number(branch_code VARCHAR)¶
Generates deposit numbers in the format TD-{branchCode}-{yyyyMMdd}-{5-digit seq}.
CREATE OR REPLACE FUNCTION deposit_db.fn_generate_deposit_number(p_branch_code VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
v_date VARCHAR := TO_CHAR(NOW(), 'YYYYMMDD');
v_seq INT;
v_dep_no VARCHAR;
BEGIN
SELECT COALESCE(MAX(CAST(SPLIT_PART(deposit_number, '-', 4) AS INT)), 0) + 1
INTO v_seq
FROM deposit_db.term_deposit
WHERE deposit_number LIKE 'TD-' || p_branch_code || '-' || v_date || '-%';
v_dep_no := 'TD-' || p_branch_code || '-' || v_date || '-' || LPAD(v_seq::TEXT, 5, '0');
RETURN v_dep_no;
END;
$$ LANGUAGE plpgsql;
Trigger: trg_deposit_status_history¶
Appends an entry to deposit_status_history on every status change to term_deposit.
CREATE OR REPLACE FUNCTION deposit_db.fn_deposit_status_history()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.status IS DISTINCT FROM NEW.status THEN
INSERT INTO deposit_db.deposit_status_history
(deposit_number, old_status, new_status, changed_at, changed_by)
VALUES
(NEW.deposit_number, OLD.status, NEW.status, NOW(), current_user);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_deposit_status_history
AFTER UPDATE ON deposit_db.term_deposit
FOR EACH ROW EXECUTE FUNCTION deposit_db.fn_deposit_status_history();
Function: fn_calculate_maturity_amount(principal NUMERIC, rate NUMERIC, tenure_months INT)¶
Calculates maturity amount using simple interest (overridden by service layer for compounding schemes).
CREATE OR REPLACE FUNCTION deposit_db.fn_calculate_maturity_amount(
p_principal NUMERIC,
p_rate NUMERIC,
p_tenure_months INT
)
RETURNS NUMERIC AS $$
BEGIN
RETURN ROUND(p_principal * (1 + (p_rate / 100) * (p_tenure_months / 12.0)), 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
transaction_db¶
Trigger: trg_transaction_immutable¶
Prevents UPDATE or DELETE on committed transactions — all corrections must go through reversal.
CREATE OR REPLACE FUNCTION transaction_db.fn_transaction_immutable()
RETURNS TRIGGER AS $$
BEGIN
RAISE EXCEPTION 'Committed transactions cannot be modified. Use reversal instead.';
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_transaction_immutable
BEFORE UPDATE OR DELETE ON transaction_db.transaction_master
FOR EACH ROW
WHEN (OLD.status = 'COMMITTED')
EXECUTE FUNCTION transaction_db.fn_transaction_immutable();
Function: fn_transall_balance_check(batch_ref VARCHAR)¶
Validates that a TransAll batch is balanced (sum of debits = sum of credits) before commit.
CREATE OR REPLACE FUNCTION transaction_db.fn_transall_balance_check(p_batch_ref VARCHAR)
RETURNS BOOLEAN AS $$
DECLARE
v_debit_sum NUMERIC;
v_credit_sum NUMERIC;
BEGIN
SELECT
COALESCE(SUM(CASE WHEN entry_type = 'DEBIT' THEN amount ELSE 0 END), 0),
COALESCE(SUM(CASE WHEN entry_type = 'CREDIT' THEN amount ELSE 0 END), 0)
INTO v_debit_sum, v_credit_sum
FROM transaction_db.transaction_transall_entry
WHERE batch_ref = p_batch_ref;
RETURN v_debit_sum = v_credit_sum;
END;
$$ LANGUAGE plpgsql;
generalledger_db¶
Trigger: trg_gl_balance_update¶
Maintains the running balance on gl_account_head after each posting entry.
CREATE OR REPLACE FUNCTION generalledger_db.fn_gl_balance_update()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.entry_type = 'CREDIT' THEN
UPDATE generalledger_db.gl_account_head
SET balance = balance + NEW.amount
WHERE account_head_code = NEW.account_head_code;
ELSE
UPDATE generalledger_db.gl_account_head
SET balance = balance - NEW.amount
WHERE account_head_code = NEW.account_head_code;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_gl_balance_update
AFTER INSERT ON generalledger_db.gl_posting_entry
FOR EACH ROW EXECUTE FUNCTION generalledger_db.fn_gl_balance_update();
Function: fn_gl_limit_check(account_head_code VARCHAR, entry_type VARCHAR, amount NUMERIC)¶
Returns FALSE if a posting would breach the configured debit or credit limit.
CREATE OR REPLACE FUNCTION generalledger_db.fn_gl_limit_check(
p_account_head_code VARCHAR,
p_entry_type VARCHAR,
p_amount NUMERIC
)
RETURNS BOOLEAN AS $$
DECLARE
v_balance NUMERIC;
v_debit_limit NUMERIC;
v_credit_limit NUMERIC;
BEGIN
SELECT balance, gl_limit.debit_limit, gl_limit.credit_limit
INTO v_balance, v_debit_limit, v_credit_limit
FROM generalledger_db.gl_account_head
LEFT JOIN generalledger_db.gl_limit USING (account_head_code)
WHERE gl_account_head.account_head_code = p_account_head_code;
IF p_entry_type = 'DEBIT' AND v_debit_limit IS NOT NULL THEN
RETURN (v_balance - p_amount) >= -v_debit_limit;
END IF;
IF p_entry_type = 'CREDIT' AND v_credit_limit IS NOT NULL THEN
RETURN (v_balance + p_amount) <= v_credit_limit;
END IF;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
common_db¶
Function: fn_is_working_day(check_date DATE, branch_code VARCHAR)¶
Returns FALSE if the given date is a Sunday or a registered holiday for the branch.
CREATE OR REPLACE FUNCTION common_db.fn_is_working_day(
p_date DATE,
p_branch_code VARCHAR
)
RETURNS BOOLEAN AS $$
BEGIN
IF EXTRACT(DOW FROM p_date) = 0 THEN
RETURN FALSE;
END IF;
RETURN NOT EXISTS (
SELECT 1 FROM common_db.common_holiday_calendar
WHERE holiday_date = p_date
AND (branch_code = p_branch_code OR branch_code = 'ALL')
);
END;
$$ LANGUAGE plpgsql;
Function: fn_next_working_day(start_date DATE, branch_code VARCHAR)¶
Advances start_date forward until it hits a working day.
CREATE OR REPLACE FUNCTION common_db.fn_next_working_day(
p_date DATE,
p_branch_code VARCHAR
)
RETURNS DATE AS $$
DECLARE
v_date DATE := p_date;
BEGIN
WHILE NOT common_db.fn_is_working_day(v_date, p_branch_code) LOOP
v_date := v_date + INTERVAL '1 day';
END LOOP;
RETURN v_date;
END;
$$ LANGUAGE plpgsql;
Flyway Migration Naming¶
All schema objects above are created inside Flyway migrations. Convention:
V{version}__{description}.sql
Examples:
V1__create_customer_tables.sql
V2__add_customer_audit_log.sql
V3__fn_generate_cust_id.sql
V4__trg_customer_updated_at.sql
Flyway runs automatically at service startup. Check migration status: