University Fees: 5,000 Students

1. Business Scenario

The payment window is 1–10 September in this illustrative semester. The maximum billed amount is 5,000 × ₹80,000 = ₹400,000,000 = ₹40 crore. A deadline policy may allow a late payment; it must be an explicit university decision, not a hidden change to history.

Rahul's invoice 5001 already has ₹30,000 confirmed and ₹50,000 remaining. His main request is Pay ₹50,000, not Pay ₹80,000 again. Priya's invoice is fully paid. Amit's unpaid ₹80,000 invoice is the separate browser-closed example.

The application must handle simultaneous students, double clicks, repeated callbacks, partial payment, unknown gateway outcomes, receipts and daily settlement differences. All identities and payment references here are fictional teaching records.

2. Real Database Tables

These are educational schema definitions, not tables to add to the job portal's live database. PK means primary key; FK means a foreign key to the named parent. Use DECIMAL(19,2) for stored money and integer minor units or decimal-safe arithmetic in application code.

Use MySQL InnoDB for these row-locking examples. NOT NULL, foreign keys and scoped UNIQUE constraints complement application validation. Monetary CHECK constraints require an engine/version that enforces them; the service must still reject invalid amounts.

students

course_id refers to the institution's course catalog.

fee_structures

UNIQUE(course_id, semester, academic_year). Invoice amounts are snapshots; changing a structure does not rewrite issued invoices.

student_fee_invoices

Validate net = gross - discount and balance = net - allocated paid amount. Refunds/credits need explicit allocation records.

payments

Scope provider references by provider/merchant in a multi-provider system. Verify callback signature, saved amount, currency and intent.

receipts

Receipt amount equals this payment, not the lifetime invoice total. Queue generation after commit; files are private.

audit_logs

Append-only access policy; timestamps and actor identity explain who changed a result. Avoid full bank details or secrets in metadata.

outbox_events

Write in the financial transaction; a dispatcher retries unpublished events. Consumers also deduplicate because queue delivery may repeat.

3. Sample Table Data

The rows below are fictional starting records. NULL means no confirmed value is known. Empty ledger/document tables are intentional before processing; their inserted records appear in Final Database State.

Large-population examples show representative rows, not all 2,000 or 10,000 records. Run totals refer to the complete illustrative population, not the displayed subset.

4. User Action / Request

The request is a business intent, not an instruction to trust browser-supplied status, owner or balance. The controller authorizes the actor, validates shape and currency, and resolves the saved obligation.

Button click to recorded result

1. Student STU1001
2. Invoice 5001: due 50000
3. Controller: Pay ₹50,000
4. Persist payment intent 100502
5. Gateway charges saved amount
6. Verified backend webhook
7. Allocate once to invoice
8. COMMIT
9. Receipt / notification queue

5. What Happens Inside the Application

The controller authorizes the student/invoice relationship and validates the requested amount against the invoice. It creates a unique payment intent before invoking the gateway. A double click with the same key returns the same intent; two different keys must not silently reserve or charge more than the remaining invoice.

The bank/gateway call runs outside a held invoice transaction. Treat the browser return as presentation only. A signed callback or provider status query supplies the external result; match merchant, payment reference, amount and INR currency to the intent.

The callback handler locks payment 100502 then invoice 5001. If the payment is already SUCCESS, return without another allocation. Save the confirmed gateway reference, update the invoice once, and create an audit event plus receipt outbox event in one local transaction. Verified external money with an allocation mismatch must remain traceable in an exception workflow.

Application responsibility boundaries

1. Request
2. Controller: authorize / validate
3. Service: business rules / idempotency
4. Database: locks / constraints / atomic writes
5. Adapter: external protocol
6. Callback / reconciler: verify evidence
7. Outbox and document worker

6. Database Transaction

Transaction boundaries

1. Verify callback signature and saved intent
2. BEGIN
3. Lock payments 100502
4. If SUCCESS: return recorded result
5. Lock invoice 5001
6. Check verified amount 50000 and balance 50000
7. Allocate, mark payment SUCCESS, invoice PAID
8. Create pending receipt / audit / outbox
9. COMMIT

Atomicity applies to the writes enclosed by one BEGIN/COMMIT. Consistency means the amount, status, ledger/allocation and audit invariants agree. Isolation prevents conflicting decisions on locked rows. Durability means a committed result survives a client disconnect.

On unexpected database errors, roll back the local transaction. A known business rejection can instead be recorded and committed without a financial mutation. Never swallow a database exception and accidentally commit half of the intended changes.

Do not hold an invoice/account/item lock across bank network calls. Persist the intent first, commit it, then call the adapter. Apply verified results in a new short transaction. A database rollback cannot recall money already moved by an external bank.

7. SQL Query

SQL fragments show the successful branch of an application-controlled transaction. Comments describing checks are real control-flow requirements: the handler must stop/rollback or record the specified exception when a guard fails. Do not paste all branches as one unconditional money-moving script.

Representative SQL and guarded updates

START TRANSACTION;
SELECT id, invoice_id, amount, currency, status FROM payments WHERE id = 100502 FOR UPDATE;
SELECT id, net_amount, paid_amount, balance_amount FROM student_fee_invoices WHERE id = 5001 FOR UPDATE;
-- Handler has verified the gateway event; stop if payment is already SUCCESS.
-- Execute the allocation branch ONLY if the locked balance >= verified amount.
UPDATE student_fee_invoices SET
 paid_amount = paid_amount + 50000.00,
 balance_amount = balance_amount - 50000.00
WHERE id = 5001 AND balance_amount >= 50000.00;
-- Handler checks exactly one row changed; otherwise roll back local allocation
-- and persist the external confirmation as a reconciliation exception.
UPDATE student_fee_invoices SET status = CASE WHEN balance_amount = 0 THEN 'PAID' ELSE 'PARTIALLY_PAID' END WHERE id = 5001;
UPDATE payments SET gateway_reference = 'GW-002', status = 'SUCCESS', paid_at = '2026-09-28 10:00:00' WHERE id = 100502;
INSERT INTO receipts (id, payment_id, receipt_no, amount, status)
VALUES (502, 100502, 'REC-2026-000502', 50000.00, 'PENDING');
-- Audit and receipt outbox event are inserted before COMMIT.
COMMIT;

Use prepared parameters in application code; literal IDs are used here only so learners can track the sample rows. Verify affected-row counts before the next financial write. Unique-reference violations require inspection of the existing saved outcome, not a new debit.

8. Concurrent Request Scenario

A waiting worker must re-read the row after it obtains the lock. A value read before waiting cannot authorize a later debit/allocation. A second delivery of one business request differs from a genuinely new request: the former returns the same outcome, while the latter must recheck current funds or remaining obligation.

9. Locking / Isolation

SELECT ... FOR UPDATE is a locking read inside an explicit InnoDB transaction. The row remains protected until COMMIT or ROLLBACK. Use indexed primary/unique lookups so the operation does not scan and lock unrelated data.

Every handler touching multiple entities must follow the same lock hierarchy. Define it once for workers, callbacks and reconcilers; opposite order can create deadlocks. A deadlock victim must restart the whole local transaction and re-read data.

Lock the saved business request/payment first, then its mutable account/invoice/item. Keep the transaction short and use current locked values, even if an earlier page showed a different balance.

Lock lifetime

1. BEGIN
2. Indexed locking read
3. Validate current row
4. Guarded mutation
5. COMMIT / ROLLBACK
6. Lock released

10. Calculation

Amounts shown in tables use two decimal places; commas and ₹ are display formatting, not database values. Round according to the documented currency/payroll rule once at the defined boundary. Do not add salary strings using floating-point arithmetic or silently change approved historical totals.

11. Table Update — Before / After

The after rows describe only committed outcomes. A rollback preserves the before financial rows; a separately committed failure/audit record can still describe the rejected request. Background document status changes do not rewrite the financial success.

12. Status Transition

Success branch

1. INITIATED / READY
2. SUBMITTED
3. PENDING / PROCESSING
4. Verified SUCCESS

Failure / investigation branch

1. SUBMITTED
2. Timeout → UNKNOWN
3. Query / callback / reconcile
4. Verified SUCCESS or confirmed FAILED

Refund branch

1. SUCCESS
2. Authorized REFUND_REQUESTED
3. Provider confirmation
4. REFUNDED; retain original payment

13. Success Flow

Success means the defined financial operation and its associated local records are committed. Return the persisted reference and outcome to the client; do not generate a new success on each retry.

Record verified provider/bank evidence before releasing a success notification. A merely submitted request stays pending or unknown until evidence resolves it.

Committed success to user

1. Saved financial result
2. COMMIT
3. Outbox dispatcher
4. Idempotent document job
5. Private document ready
6. Notification / authorized download

14. Failure Flow

For Amit's independent unpaid invoice 5003, the gateway charges ₹80,000 but the browser closes before success returns. The backend keeps PENDING/UNKNOWN until a verified webhook or query confirms the result. Then it records SUCCESS and PAID; it never treats a browser disconnect as bank failure. A declined payment has confirmed FAILED and leaves invoice 5003 unchanged.

₹80,000 charge, browser closed

1. Amit: invoice 5003 unpaid ₹80,000
2. Gateway charges ₹80,000
3. Browser closes — no frontend success
4. Verified gateway webhook
5. Backend locks payment and invoice
6. Payment SUCCESS / invoice PAID
7. Commit and receipt queue

If the provider confirms ₹90,000 for an ₹80,000 obligation, keep the full external receipt traceable. Do not erase ₹10,000 or falsely call the whole payment failed. Capture an exception and resolve the excess through explicit ledger/credit/refund records, authorized business rules and provider references.

15. Retry Flow

Repeat a status query or process the signed callback with the original PAY-100502 / FEE-5001-B identity. A duplicate notification does not create another payment. If the gateway definitively failed without charging, a customer-authorized new attempt may be recorded according to provider rules, linked to the original intent.

Safe retry decision

1. Find original saved reference
2. Was any external request sent?
3. Yes / uncertain → query original provider reference
4. Confirmed success → return; never repeat effect
5. Confirmed safe failure → bounded authorized retry
6. Exhausted / unresolved → investigation queue

Use bounded exponential backoff for transient technical errors and expose retry age/attempt count. An exhausted job becomes a failed/dead-letter item for an operator; a queue retry counter is not evidence of bank failure. A corrected obligation or authorized refund is a distinct linked business operation, not a stealth retry.

16. Idempotency

Persist a unique business key before processing and compare the request payload on key reuse. Apply another unique constraint to the external event/reference and to the document/outbox event. A unique key alone is not enough: the status check and financial mutation must be within the same transaction.

Callback authentication and idempotency solve different problems. Verify signatures/merchant context first; an attacker-controlled reference does not become trusted just because it is unique. Lock the request row before applying its result.

17. Queue / Background Processing

Durable background work

1. Financial transaction inserts outbox event
2. COMMIT
3. Dispatcher reads unpublished events
4. Queue accepts event
5. Idempotent worker executes
6. Mark completed / retry with same event key

Laravel afterCommit() prevents a worker from reading an uncommitted row, but a process can still crash between database commit and sending to a separate queue. A transactional outbox closes that publication gap; consumers must still handle redelivery.

Separate payment, document, notification and reconciliation queues allow independent retries and limits. Payroll/corporate schedulers paginate or chunk work rather than loading all 10,000 payments into one transaction. Do not submit external payments again just because their notification jobs failed.

Laravel dispatch after commit (illustrative job class)

GenerateFeeReceipt::dispatch($payment->id)->afterCommit();
// Job class is illustrative. Use a durable outbox for reliable publication.
// Deduplicate by the saved payment/request/document identity.

18. Receipt / Document Generation

REC-2026-000502 · Student STU1001 · Invoice INV-2026-5001 · Payment PAY-100502 · Amount ₹50,000 · 28 Sep 2026 · Payment SUCCESS / Invoice PAID. The earlier REC-2026-000501 remains ₹30,000. Two payment receipts total ₹80,000; do not issue a misleading ₹80,000 receipt for the ₹50,000 installment.

Private document lifecycle

1. Confirmed SUCCESS
2. Database COMMIT
3. Pending document / outbox
4. Queue PDF renderer
5. Private storage
6. GENERATED
7. Authorized download

Render from the committed immutable amount/reference and approved payroll snapshot, not client input. Reserve a unique receipt/slip number and link one document to one payment. A failed write to private storage cannot turn the already-confirmed financial payment into FAILED.

19. Reconciliation

Illustrative daily totals: internal SUCCESS 4,980, gateway SUCCESS 4,981, bank settlement entries 4,981. Counts alone only flag a discrepancy. Join by provider/payment reference, INR amount, settlement date and fees to locate the missing allocation, verify its authenticity, then apply it once. Settlement lag and net-of-fee values must be compared under a defined policy.

Three-way evidence check

1. Internal request / ledger / allocation
2. Compare provider confirmation by reference
3. Compare bank settlement by date/currency/net policy
4. Create discrepancy record
5. Investigate with evidence
6. Apply idempotent authorized correction
7. Close exception with audit

Investigate outcomes, never blindly overwrite

-- Grouping is a monitoring signal; investigate individual references.
SELECT status, COUNT(*) AS rows_found, SUM(amount) AS amount_total
FROM payments
GROUP BY status;

Count mismatches are clues, not sufficient proof. Match identifiers, amounts, currency, beneficiary and dates. Handle settlement lag, fees and refunds explicitly; never change thousands of rows to SUCCESS merely to make dashboard totals agree.

20. Audit Log

Each financial status change appends an audit event inside the same local transaction. Metadata includes the request ID, correlation ID, provider evidence reference and reason, with secrets and full beneficiary details removed.

Restrict update/delete permissions on audit history and define retention. A manual reconciliation resolution records the authorized operator, evidence and correction reference. Audit explains who changed a value; the ledger explains the financial entries.

21. Final Database State

These tables describe the completed illustrative main scenario. Independent failure/overpayment exercises above do not silently alter these rows. Batch counts below are derived reporting values where marked.

22. Interview Questions

23. Related SQL Concepts

Connect the sample to ACID, row locks, isolation levels, foreign keys, UNIQUE constraints, conditional UPDATE, SUM/GROUP BY, joins, NULL external references and state transitions. Use window functions to review ordered attempts while preserving each payment's business identity.