Temporal Price History
Design a PostgreSQL database that remembers every price a product has ever had, and exactly when each price was valid. You'll be able to answer questions like "what did this product cost on 3 March at noon?", "what price was charged on this old order?" and "who changed this price and when?". Each price is stored with its validity period as a range, and an exclusion constraint makes it impossible for two prices of the same product to overlap. Price changes, including ones scheduled for the future and corrections of past mistakes, go through functions that keep the history consistent and write an audit trail. You'll learn temporal data modeling, range types and exclusion constraints, point-in-time queries, audit logging, handling concurrent changes, and testing with psql.
- Estimate
- ~12h
- Steps
- 7
- Completed by
- 0
- Proposed by
- codeseed.app
PostgreSQL · psql · PL/pgSQL
Project roadmap
- 01
Design the model
~2hWrite DESIGN.md: product_prices (product, price in cents, currency, valid_during as a tstzrange, created by, reason), and price_audit (every change with the old and new values, who and when). Explain the difference between when a price is valid and when it was recorded, how a future price change and a correction of a past mistake are handled, and why periods must never overlap. Done when: DESIGN.md explains the model with examples of each kind of change.
- 02
Create the tables and constraints
~1.5hEnable btree_gist and create product_prices with an exclusion constraint EXCLUDE USING gist (product_id WITH =, valid_during WITH &&), so overlapping prices for one product are rejected by the database. Add checks for positive prices and non-empty ranges, and a GiST index for point-in-time lookups. Done when: inserting two overlapping prices for a product fails.
- 03
Write the change functions
~2.5hWrite PL/pgSQL functions: set_price(product, price, from_time, user, reason), which ends the current open-ended period at from_time and starts a new one (working for future times too), and correct_price(product, period, price, user, reason) for fixing past mistakes by splitting the affected periods. Both lock the product's rows first (SELECT ... FOR UPDATE) so two simultaneous changes can't interleave, and both write to price_audit. Done when: a series of changes leaves a gap-free, non-overlapping history.
- 04
Write point-in-time queries
~1.5hWrite price_at(product, time) using valid_during @> time, a query for all products' prices at a given moment, and the full price history of a product with each period's start and end. Link orders to prices by the order's time and show the price that was charged on each order line. Done when: point-in-time queries return the right price for times before, during and after changes.
- 05
Load and check history
~1.5hGenerate a few years of history for 1,000 products with frequent changes and some scheduled future prices. Write consistency checks: no overlaps (guaranteed by the constraint), no gaps between a product's first and last period, and every change present in the audit. Measure point-in-time queries with EXPLAIN ANALYZE to confirm they use the index. Done when: the checks pass on the generated data, and lookups use the GiST index.
- 06
Handle concurrent changes
~1.5hOpen two psql sessions and call set_price for the same product at the same time; confirm that one waits for the other and the history stays correct. Also try inserting directly around the functions and confirm the exclusion constraint still protects the data. Document the results in README.md. Done when: concurrent changes never produce overlaps or gaps.
- 07
Test with psql
~1.5hWrite test.sql that runs inside a transaction: create a product, set prices, schedule a future price, correct a past period, and check price_at at many moments, the audit entries, and that invalid changes (a negative price or an empty range) fail, then roll back. Done when: psql -f test.sql passes.
Resources
Ready to build this?
Get a GitHub repo and start building. Your AI reviewer checks each step as you go.
Tech stack