Blockchain Analytics Dashboard
An internal dashboard for tracking blockchain records and field-officer activity. I built the React front end and the backend behind it, including role-based access, a self-healing ~39,700-record import pipeline, and the SQL analytics.
This is the admin side of the same platform, and I worked on both ends of it. The dashboard lets internal staff watch how many records are being written to the blockchain, how many succeed or fail, and how active each field officer is. They can filter by date, search and sort, and download an Excel report.
The dashboard (React)
Built with React, Vite, and Tailwind. The main screen is a performance table with server-side search (debounced so it does not fire on every keystroke), sortable columns, pagination, and date filters for today, the last 7 or 30 days, or a custom range. React Query caches each filter combination, so going back to a previous view is instant. Auth is a JWT kept in Zustand, with Axios interceptors that attach the token and log the user out automatically on a 401. The export button streams an .xlsx file straight from the server and handles rate limits without breaking.
The backend behind it
I also built the backend that feeds the dashboard.
Access control. A two-tier role system (superadmin and viewer) enforced by middleware, with passwords hashed by bcrypt and admin accounts soft-deleted rather than removed.
Bulk import. A self-healing pipeline that loaded ~39,700 farmer records (largest single batch 36,172) plus ~478 field-officer records from messy Excel exports, at 99.59% and 99.79% success respectively. It detects the delimiter, repairs GPS coordinates (degrees-minutes-seconds and decimal-less integers), turns "NA" into a real null, caps out-of-range values so a phone number typed into a numeric column can't crash the insert, and falls back through officer then region when a location is missing. It dedupes with prebuilt sets (two queries per batch instead of one per row) and inserts in bulk with a row-by-row fallback and grouped error reporting that shows exactly which rows failed and why.
Analytics. The reports come from raw SQL. The newer version uses a CTE with ROW_NUMBER to collapse blockchain retry rows, so a farm that failed twice and then succeeded reads as one success instead of three failures.
WITH RankedAudit AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY farm_id ORDER BY created_at DESC) AS rn
FROM blockchain_audit WHERE 1=1 /* + date filter */
)
SELECT * FROM RankedAudit
WHERE tx_status = 'SUCCESS'
OR (rn = 1 AND tx_status != 'SUCCESS');
One detail I am glad I caught: the date filter sits on the JOIN, not the WHERE, so officers with no activity in a period still show up with a count of zero instead of dropping out of the report.
Login. I also moved sign-in from trusting the client to verifying the one-time code server-to-server against the company's identity system, which closed a hole where someone could have logged in as any officer.
What stuck with me
SQL is where the truth lives for this kind of work. Window functions and putting filters in the right place turn a noisy audit log into numbers a manager can actually trust.