University Payroll: 2,000 Employees

1. Business Scenario

The illustrative university employs 2,000 people. Each example employee has basic ₹50,000, HRA ₹20,000, allowance ₹10,000 and deductions ₹5,000. Gross is ₹80,000 and net is ₹75,000. At this uniform illustrative rate, total net payroll is ₹150,000,000 = ₹15 crore.

Payroll calculation and money movement are separate workflows. Freeze the approved salary snapshot so an edit to next month's salary structure cannot change this month's approved bank request.

Ten scheduling groups contain employees 1–200, 201–400, 401–600, 601–800, 801–1000, 1001–1200, 1201–1400, 1401–1600, 1601–1800 and 1801–2000. A scheduling group is not one atomic bank transaction.

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.

employees

Bank account numbers are masked in teaching screens and access-controlled in real systems.

salary_structures

Select the structure effective for the payroll period; prevent overlapping effective ranges for an employee.

payroll_runs

Maker 11 and authorized checker 22 differ. Approval totals must equal the frozen item sums.

payroll_items

UNIQUE(payroll_run_id, employee_id). Store calculation snapshot/source version for audit.

bank_payment_requests

Persist before sending. Bank submissions use the saved reference; do not derive a new key on timeout.

salary_slips

Create after confirmed success; generated PDFs remain in private storage.

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. HR calculates September run
2. Service writes frozen payroll_items
3. Maker submits totals
4. Checker authorizes run
5. Outbox dispatches groups of 200
6. Worker claims one item
7. Bank adapter submits saved reference
8. Callback/query confirms
9. Slip and notification jobs

5. What Happens Inside the Application

The calculation service selects the salary structure valid for September, computes gross and net using decimal arithmetic, and writes 80000.00, 5000.00 and 75000.00 to payroll_items.id=20001. Run totals are sums of the persisted items, not a repeated estimate.

The approval service locks payroll_runs.id=1, verifies CALCULATED, checks maker/checker permissions and totals, then records approved_by/approved_at and durable outbox work. A second approval sees the new status and returns the existing result.

A worker atomically claims a READY request before contacting the bank outside the database transaction. A lease expiry or worker crash after a send creates uncertainty: reconcile the saved reference before another submission. The callback applies a terminal item outcome once; it does not reopen the historical payroll calculation.

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. BEGIN: lock bank request 30001
2. Claim READY → SUBMITTED with lease
3. COMMIT before bank HTTP call
4. Submit saved business reference
5. Verify callback / status query
6. BEGIN: lock request then payroll item
7. Set confirmed SUCCESS; audit + slip outbox
8. 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

-- Calculation snapshot before approval; bound values come from the effective salary structure.
START TRANSACTION;
SELECT id, status FROM payroll_runs WHERE id = 1 FOR UPDATE;
UPDATE payroll_items JOIN payroll_runs ON payroll_runs.id = payroll_items.payroll_run_id
SET payroll_items.gross_salary = 50000.00 + 20000.00 + 10000.00,
 payroll_items.deductions = 5000.00, payroll_items.net_salary = 50000.00 + 20000.00 + 10000.00 - 5000.00
WHERE payroll_items.id = 20001 AND payroll_items.status = 'READY'
AND payroll_runs.status IN ('DRAFT','CALCULATED');
-- Recalculation is allowed ONLY while the parent run is DRAFT/CALCULATED and locked.
SELECT SUM(gross_salary), SUM(deductions), SUM(net_salary) FROM payroll_items WHERE payroll_run_id = 1;
COMMIT;
-- The guard prevents recalculating the PROCESSING sample run.
-- Separate transaction after a verified bank SUCCESS:
START TRANSACTION;
SELECT * FROM bank_payment_requests WHERE id = 30001 FOR UPDATE;
SELECT id, status FROM payroll_items WHERE id = 20001 FOR UPDATE;
-- If already SUCCESS, return existing result without creating a second slip.
UPDATE bank_payment_requests SET status = 'SUCCESS', external_reference = 'BANK-SAL-001' WHERE id = 30001;
UPDATE payroll_items SET status = 'SUCCESS' WHERE id = 20001;
INSERT INTO salary_slips (id, payroll_item_id, slip_no, net_salary, status)
VALUES (40001, 20001, 'SLIP-2026-09-0001', 75000.00, 'PENDING');
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

Payroll run lifecycle

1. DRAFT: editable
2. CALCULATED: item totals ready
3. APPROVED: snapshot frozen
4. PROCESSING: individual bank requests
5. COMPLETED: all success / PARTIALLY_COMPLETED: final mixed results

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

Employee 700 returns ACCOUNT_CLOSED: mark only that request/item FAILED. The 1,999 successful payments remain successful. BANK_TIMEOUT is UNKNOWN, not a final failure, and prevents final closure until resolved. An approved payroll item is corrected through a separately audited adjustment, never silent recalculation.

15. Retry Flow

For a timed-out SAL-2026-09-EMP0001, query the bank by that same reference before retrying. A confirmed pre-submission technical failure can retry safely under the same identity; a changed beneficiary after ACCOUNT_CLOSED requires an authorized correction linked to the original obligation.

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)

GenerateSalarySlip::dispatch($payrollItem->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

SLIP-2026-09-0001 · Neha Verma / EMP0001 · September 2026 · Gross ₹80,000 · Deductions ₹5,000 · Net ₹75,000 · Bank reference BANK-SAL-001 · Payment SUCCESS. The PDF worker reads the frozen payroll item and does not recalculate from today's salary structure.

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

Expected net ₹150,000,000. With 1,999 confirmed successes, bank-paid total is 1999 × 75000 = ₹149,925,000; unpaid confirmed failure is ₹75,000. Match each reference to the bank report; distinguish authorized payroll liability from actual settled cash.

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 bank_payment_requests
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.