Warehouse Picking Dashboard

Project roadmap

1. 01

   Design the schema

   ~2h

   Write schema.sql with tables for pickers, bins (code, zone, capacity in units), products, orders (id, created_at, due_at, priority) and pick_events (id, order_id, picker_id, bin_id, product_id, quantity, event_type of assigned, picked, packed or shipped, occurred_at). Add foreign keys, check constraints (quantity above 0, known event types) and a table of current bin stock. Done when: psql -f schema.sql creates every table without errors.

2. 02

   Generate realistic data

   ~2h

   Write a PL/pgSQL function (or a generate_series script) that creates 20 pickers, 200 bins, 500 products and a week of activity: about 5,000 orders with pick events over working hours, with some pickers faster than others and about 10% of orders shipped after their due time. Done when: running the seed fills the tables, and simple counts look realistic.

3. 03

   Report picker throughput

   ~2h

   Write a view picker_throughput showing, for each picker and each hour (using date_trunc('hour', occurred_at)), the number of picks and units picked, and a query ranking pickers by units per hour over a chosen day with RANK(). Also show each picker's average time between two picks using LAG. Done when: the view returns plausible numbers, and the fastest and slowest pickers stand out.

4. 04

   Find late orders

   ~2h

   Write a view late_orders that lists shipped orders whose shipped time is after due_at, with how many minutes late they were, and open orders that are already past due_at. Then write a query showing the percentage of late orders by priority and by hour of the day they were created. Done when: the roughly 10% of late orders from the seed data appear correctly.

5. 05

   Measure bin utilization

   ~1.5h

   Write a query that shows each bin's current stock as a percentage of its capacity, grouped by zone, and lists bins over 90% full and bins empty for more than a day. Create a materialized view for the zone summary and a function refresh_dashboard() that refreshes it. Done when: the utilization numbers match the stock table, and refreshing the materialized view works.

6. 06

   Make the queries fast

   ~2h

   Run each report with EXPLAIN (ANALYZE, BUFFERS). Add indexes that help the slow ones, for example on pick_events (picker_id, occurred_at) and (order_id, event_type), and compare the timings before and after. Write the findings in PERFORMANCE.md. Done when: every report runs in well under a second on the seed data, and PERFORMANCE.md shows before and after timings.

7. 07

   Test the reports

   ~1.5h

   Write tests.sql that builds a tiny, hand-made dataset inside a transaction, where you know the right answers (for example 2 late orders and one picker with 12 picks in one hour), runs each view and query, and raises an exception when a result is wrong. Explain how to run it with psql in the README. Done when: running tests.sql finishes without errors.

Resources

• DocsPostgreSQL: window functions tutorial
• DocsPostgreSQL: materialized views
• DocsPostgreSQL: using EXPLAIN
• DocsPostgreSQL: PL/pgSQL
• Docspsql documentation