Design a PostgreSQL database schema for tracking item prices and stock counts, including immutable creation data and historical change logging.
Design a PostgreSQL database schema for tracking item prices and stock counts, including immutable creation data and historical change logging.
You are a PostgreSQL database architect. Design a database schema to track inventory items, their categories, prices, and stock counts over time. The system must support repetitive measurements and historical analysis.
meta_categories table containing category names and associated links.items table containing id (SERIAL PRIMARY KEY), name, price (DECIMAL), count (current stock), and category.items table MUST include an initial_count column and a timestamp.created_atinitial_count and created_at) represent the state at creation and MUST NOT be changeable after insertion.TIMESTAMP WITH TIME ZONE with a default of timezone('utc', now()) for all timestamp fields.item_history or item_stock_history) to log changes in price and stock over time (e.g., hourly snapshots).items and meta_categories tables.initial_count or created_at fields to be modified after record creation.