Small Shop Inventory Tracker
Design a PostgreSQL database for a small shop's stock, where the stock level of each product isn't stored directly but calculated from every movement: deliveries from suppliers add stock, sales and damaged items remove it, and stock counts correct it. This way, you can always see why the stock is what it is. You'll write queries for the current stock of every product, which items have fallen below their reorder level, and how much each supplier has delivered over time. It's a friendly introduction to SQL: you'll learn designing related tables, the difference between storing and deriving data, INSERT and UPDATE, joins, and SUM with GROUP BY.
- Estimate
- ~6.5h
- Steps
- 6
- Completed by
- 0
- Proposed by
- codeseed.app
PostgreSQL · pgAdmin
Project roadmap
- 01
Design the tables
~1.5hInstall PostgreSQL and pgAdmin. Write schema.sql with suppliers, products (name, SKU unique, supplier, unit price and reorder level) and stock_movements (product, type delivery, sale, damage or adjustment, a quantity that's positive for incoming and negative for outgoing, the date and a note). Add checks: sales and damage must be negative, and deliveries positive. Done when: the schema loads without errors.
- 02
Add sample data
~1hWrite seed.sql with 5 suppliers, 30 products and a few months of movements: regular deliveries, daily sales, a few damaged items and one stock count adjustment. Make some products end below their reorder level. Done when: the data loads, and each movement type is present.
- 03
Calculate current stock
~1hWrite a query that sums each product's movements to get its current stock, using a LEFT JOIN so products with no movements show 0, and save it as a view current_stock. Add the stock value (stock × unit price) and the total value of the shop's stock. Done when: the stock for a few products matches a calculation by hand.
- 04
Flag low stock
~1hWrite a query listing products at or below their reorder level with their current stock, the reorder level, how many to order to reach twice the reorder level, and the supplier's name and email, grouped by supplier so orders can be sent. Done when: the query lists exactly the products that need reordering.
- 05
Report supplier deliveries
~1hWrite a query showing, per supplier and month, the number of deliveries and the total quantity and value delivered, using date_trunc and GROUP BY. Add another showing each product's best-selling month. Done when: the reports show sensible totals for the sample data.
- 06
Test the queries
~1hWrite test.sql that, inside a transaction, adds a delivery and a sale for a product, checks the current stock changes by the right amounts and the low-stock list updates, and confirms a positive sale quantity is rejected, raising an error with a DO block on any failure, then rolls back. Done when: psql -f test.sql runs without errors.
Ready to build this?
Get a GitHub repo and start building. Your AI reviewer checks each step as you go.
Tech stack