2025-2026
JR Plus
Pharmacy e-commerce dashboard with order and warehouse workflow
Requirements & business rules
This was an admin dashboard for a Thai pharmacy distributor. My scope was three sections: Overview (KPI cards and 30-day revenue/order charts), Order management (the full workflow from payment verification through warehouse picking and packing to shipping), and Product management (with per-lot pricing and expiry tracking). The order side especially had a lot of moving parts - the client wanted to know exactly who changed what and when at every step.
Data model & database design
The database was PostgreSQL through Supabase. Most of the read-heavy pages pulled from pre-built views rather than raw tables - v_stats_card for the KPI overview, v_orders for the paginated order list, v_order_details for the order detail page, and v_product_details for the product list with thumbnail data. On the write side, one thing that caught me off guard was that payment status lives on payment_groups, not on orders directly. A separate payment_group_orders junction table ties a payment group to its orders. Shipping status has its own table (order_shipments), and every change gets logged to order_shipping_history with who made it and an optional note. Products support up to 3 lots via product_batches, each with independent quantity, cost, sale price, and expiry date. Per-item warehouse picks are tracked in order_item_picks with picked_by, picked_at, and a verified_quantity field. Order statuses also arrived as free text, so I added a normalization layer that maps 30+ free-text status values to 5 canonical states for consistent reporting.
Entity relationship diagram
- -Payment status lives on payment_groups, not on orders directly. Orders connect to a payment group through the payment_group_orders junction table. I only found this after my status updates weren't sticking - I was writing to the wrong table.
- -shipping_status_history records every status change with a changed_by name and an optional note. The client wanted a full audit trail so they could see exactly who updated an order and when.
- -product_batches saves using delete-all then re-insert on every save. Since batch_number (1, 2, or 3) is the only identifier and nothing else references batches by row ID, it's safe to wipe and rewrite. This kept the save logic simple without needing to diff old and new state.
Technology stack
| Language | TypeScript |
|---|---|
| Frontend | Next.js, TanStack Query, Zustand, Recharts, Tailwind CSS |
| Database | Supabase (PostgreSQL) |
TanStack Query was the right call here because the order detail page needed several data sources in parallel and cache invalidation needed to be tight - updating an order status should immediately refresh all the related views. Supabase gave me auth, Postgres, and storage without needing a separate backend. Recharts handled the line charts on the overview page.
Challenges & solutions
01 -Payment status hidden in a junction table
Problem
Payment status updates weren't sticking. I was writing to the orders table and the changes weren't reflected anywhere on the page.
Investigation
Traced through the schema and found that orders don't own payment status directly. A payment_groups table owns the status, and orders connect to it through a payment_group_orders junction table. My updates were targeting the wrong table and doing nothing.
Solution
Centralized all order mutations into a single useUpdateOrder hook. You pass a type ('payment', 'shipping', or 'region') and the hook resolves the correct chain of lookups and writes internally. The UI passes intent - the hook handles which tables are involved.
Result
Order status updates are reliable. The mutation logic lives in one place, and adding a new update type later is a single addition to the hook.
02 -Shipping audit trail without slowing down warehouse staff
Problem
The client needed a complete log of every shipping status change - who changed it, when, and optionally why. But warehouse staff update statuses throughout the day and I didn't want every action to feel like filing a form.
Solution
A modal appears before any shipping status update, asking for the staff member's name and an optional note. The hook then writes to both order_shipments and order_shipping_history in one call. The note field is optional - staff don't feel required to justify routine updates, but the field is there when it matters.
Result
A full audit trail exists for every shipping status change. The one-step modal is quick enough that staff actually use it.
03 -Saving product lots without a diff algorithm
Problem
Products support up to 3 lots with independent pricing and expiry. On save, the system needed to handle new lots, edited lots, and removed lots - without turning the save logic into a state diff.
Solution
Delete all existing batches for the product, then re-insert whatever the current form contains. Since batch_number (1, 2, or 3) is the only identifier and nothing else references batches by row ID, there's no referential integrity to break. The save logic stays flat and predictable.
Result
Product lot saves are reliable regardless of what changed. No diffing, no patch logic - just delete and rewrite.
Results & impact
Shipped the Overview, Order, and Product sections. Orders could be tracked from payment pending all the way through to delivered, with a full history of who touched each step. Products supported up to 3 lots with independent pricing and expiry, which the client needed for managing near-expiry stock.
Screenshots
