Turning Mine-Record Gaps into Follow-Up Cases

Mining operators track the same activity in several places: dispatch logs, e-Pass records (electronic transport permits), royalty payments, production and environmental limits, and regulatory returns. When those records disagree, or one is missing, someone has to notice and follow up.

I used synthetic data for 12 fictional mining leases to design a repeatable process for that job. SQL checks flag potential data gaps, and Salesforce Cases organise the follow-up. The result is a structured review queue where every flag carries its source references where the evidence exists, and a human reviewer decides what happens next.

Think of a shop that keeps stock in one notebook, sales in another and supplier payments in a third. When the numbers disagree, it could be a mistake, or it could be an entry nobody has written down yet. Mining records can have the same issue. Dispatches, e-Pass records, royalty payments and returns are updated at different times, so differences in reporting periods, missing evidence and inconsistent entries make manual review harder. Public material, including the Odisha i3MS procedures, a 2022 Directorate of Mines procurement document and the Indian Bureau of Mines Form F1 return, shows how many records and statuses are involved. None of it shows how an operator actually compares them, so I treated this as a hypothesis to test.

The challenge was to build a repeatable way to find differences across related records, separate missing evidence from differences worth investigating, trace each flag back to its source records, and give reviewers a place to record their outcomes.

A difference alone does not prove a violation. Every flag is a reason to look, and it is reviewed against the evidence before anyone draws a conclusion.

I took the project from an initial idea to a working prototype. I framed the problem, defined the scope and comparison rules, mapped the process in Miro, wrote the SQL checks, and configured Salesforce Cases with custom fields and validation rules for follow-up. I also verified the SQL output with a set of readiness checks.

The workflow has five steps. It first prepares the records by aligning identifiers, reporting periods, units and statuses. SQL then compares related records and classifies each result. Selected results become follow-up Cases, each with a summary and source references. Finally, a human reviewer investigates and records the outcome. The workflow prepares the evidence, and the reviewer makes the decision.

The most important design choice is the classification step. Instead of putting everything into one pile of “errors”, the workflow separates three outcomes: a potential difference (records that should agree but don’t), incomplete evidence (a record is missing or can’t be compared reliably), and a pending correction (a record that is waiting on an update).

This matters because a reviewer’s time is limited. Records that can’t be compared go back for better evidence instead of creating a misleading alert. The aim is for every flag to be traceable and worth investigating.

The synthetic source package contains 1,789 rows across nine CSV files, covering 12 fictional mining leases over FY 2025–26. The monthly data includes production, dispatch, e-Pass events, environmental limits, payment evidence and regulatory filings.

In MySQL Workbench, I first prepared the data for comparison. I had planted a duplicate in the dispatch records to test the cleaning step, and the deduplicated view brings 433 records down to 432. I then wrote checks for four areas: dispatch reconciliation, payment evidence, environmental limits and filing follow-ups.

The final query returns a review queue of 12 flagged records across 14 fields: four dispatch, two payment, three production-limit and three filing flags. Each flag carries a stable key and source references where available, so a reviewer can see why it was raised and trace it to the evidence. One payment flag has no reference because the finance evidence itself is missing, which is exactly the kind of gap the queue exists to surface.

I then ran nine readiness checks on the queue, and all nine passed, including the row count, key uniqueness and field lengths.

A list of flags is only useful if someone owns what happens next, so I used Salesforce Cases as the home for follow-up. I added custom fields for the review details, including exception category, review outcome, follow-up due date and evidence references, set the Case key as a unique external ID, and added three validation rules that block closing a Case until Product, Type, Resolution Summary and Review Outcome are completed.

To test it, I tried to close a synthetic Case with those fields empty. Salesforce blocked the closure and showed a validation error. Once I completed the fields, the Case closed.

This is a prototype. The SQL output and Salesforce are not linked by a live integration, and I have not yet shown the full journey from assignment to resolution.

The rules and thresholds in this prototype are my assumptions, so the next steps are about testing them:

1. Talk to the people who do this work. Interview operators and source owners to confirm how they reconcile records today, then adjust the comparison rules to match.
2. Test on real records. Run the checks on approved, representative data and see which flags hold up.
3. Complete the Salesforce workflow. Test assignment, investigation, reassignment and closure, including how duplicate Cases are prevented and how review decisions are recorded. Add severity and ownership rules so reviewers see the most important flags first.
4. Measure and then automate. Track review time, how often flags prove valid, and time to resolution. If the numbers justify it, connect the SQL queue to Salesforce with safeguards against duplicate records.

Writing the SQL was only one part of this project. Defining the comparison rules, separating missing evidence from real differences, and keeping every flag traceable to its source were just as important.

I also learned that spotting a problem and resolving it are different jobs. SQL surfaces the records that need attention, and Salesforce organises the follow-up and enforces the details a reviewer must record before closing a Case.

My main takeaway is that a useful Business Analyst solution does more than find data issues. It makes them understandable, traceable and actionable for the people who have to review them.