Corporate Salary: 10,000 Employees

1. Business Scenario

A fictional corporation owes 10,000 employees an average ₹75,000: 10,000 × 75,000 = ₹750,000,000 = ₹75 crore. The batch is an operational grouping of independent payment obligations, not a single all-or-nothing bank transaction.

The bank reports 9,970 SUCCESS, 20 FAILED and 10 PENDING/UNKNOWN. This lesson displays status PARTIALLY_COMPLETED with processing_status PROCESSING and is_final = false. The label reports progress, not finality: all ten unresolved items remain visible.

The eventual illustrated outcome is 9,980 successes and 20 confirmed failures. Only then processing_status becomes CLOSED and is_final=true. Bank balance shown in a local cache is not an authority to spend without the bank's balance/reservation controls.

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.

corporate_accounts

₹80 crore is an illustrative bank-reported snapshot. Bank reservations and fees affect actual available cash.

salary_batches

created_by 11 != approved_by 22. A valid permission check is required in addition to different IDs.

salary_batch_items

Beneficiaries and bank codes are deliberately non-routable example tokens. Validate actual identifiers using the bank's supported scheme.

bank_payment_requests

The same business reference remains on every investigation or safe retry.

bank_responses

Append verified responses; prevent duplicate event application. A response is evidence, not permission to double-pay.

reconciliation_records

Resolve only after matching company, beneficiary, amount, currency, date and bank reference.

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. Corporate payroll freezes amounts
2. Maker 11 creates batch
3. Validate 10000 items and sum
4. Checker 22 approves
5. Outbox and payment orchestrator
6. Bank-specific adapter
7. Independent bank outcomes
8. Status query for EMP005
9. Reconciliation
10. Salary documents

5. What Happens Inside the Application

Validate count, sum, positive amounts, employee eligibility, beneficiary scheme and unique payment references before approval. Reject malformed rows without any financial operation. Bind the maker's payload and version to the checker approval so editing a batch invalidates approval.

The orchestrator claims one saved request, records submission intent, commits, and calls the bank adapter. Only the adapter knows the bank's protocol and authentication. No worker may silently invent SAL-2026-09-EMP005-RETRY-2 after a timeout.

The reconciler verifies response EVT-STATUS-005 against the saved ₹75,000 obligation. It locks request 60005 and item 50005 in the standard order, records BANK-005 and SUCCESS once, resolves exception 62005, and creates document/audit outbox work. Derived batch counts still retain the other nine unresolved items until each is confirmed.

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. Verified bank query returns BANK-005 SUCCESS
2. BEGIN
3. Lock request 60005
4. Lock item 50005
5. Check matching amount/reference and current state
6. UNKNOWN → SUCCESS, save bank reference
7. Resolve reconciliation 62005; audit/outbox
8. COMMIT
9. Recompute batch outcome counts

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 * FROM bank_payment_requests WHERE id = 60005 FOR UPDATE;
SELECT id, amount, payment_reference, status FROM salary_batch_items WHERE id = 50005 FOR UPDATE;
-- Apply only a verified matching bank result; an existing SUCCESS is a no-op.
UPDATE bank_payment_requests SET status = 'SUCCESS', external_reference = 'BANK-005' WHERE id = 60005;
UPDATE salary_batch_items SET status = 'SUCCESS', failure_code = NULL WHERE id = 50005;
UPDATE reconciliation_records SET status = 'RESOLVED', resolution = 'Verified BANK-005; no resubmission' WHERE id = 62005;
-- Audit/outbox entry records UNKNOWN -> SUCCESS.
COMMIT;
SELECT status, COUNT(*) AS items, SUM(amount) AS total
FROM salary_batch_items WHERE salary_batch_id = 50 GROUP BY status;
-- Close the parent only when PENDING/UNKNOWN/READY/SUBMITTED/PROCESSING count is zero.

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

Batch summary PARTIALLY_COMPLETED is deliberately non-final while processing_status=PROCESSING and is_final=false. Success, failed and unresolved counters remain visible. Close only after all unresolved items reach an evidenced final result.

Corporate parent batch lifecycle

1. DRAFT: editable
2. VALIDATED: counts and amounts checked
3. APPROVED: separate checker authorizes frozen version
4. SUBMITTED: no assumed success
5. PROCESSING: unresolved items remain
6. COMPLETED: all success / PARTIALLY_COMPLETED: inspect finality and counts

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

ACCOUNT_CLOSED, INVALID_ACCOUNT and BENEFICIARY_BLOCKED are confirmed failures only when the bank reports a terminal rejection. BANK_TIMEOUT is UNKNOWN. PENDING means the bank says processing; UNKNOWN means the application does not know. Record each item result and keep unresolved amounts visible.

15. Retry Flow

Do not create SAL-2026-09-EMP005-RETRY-2 merely to get a new request accepted. Query the original reference, await signed callback or compare the bank statement. Retry transmission only when the adapter/provider contract establishes it is safe; preserve the original business key and record each attempt separately.

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)

GenerateSalaryAdvice::dispatch($batchItem->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

Salary advice for EMP005 includes batch BATCH-2026-09-44, ₹75,000, business reference SAL-2026-09-EMP005 and bank reference BANK-005. Generate after confirmation and commit; restrict download to the employee or authorized payroll staff.

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

Join internal items and the bank report by business reference and bank reference, then verify beneficiary token, amount, currency and date. Initial successes 9970 differ from bank-confirmed 9980; the ten missing confirmations are resolved individually. Compare cash totals with settlement/fees, not just counts. Twenty confirmed failures remain liabilities to investigate.

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