inventory_schema_1.sql (13911B)
1 -- ============================================================ 2 -- INVENTORY SCHEMA - INVENTORY SERVICE 3 -- ============================================================ 4 -- Handles: Stock management, pricing, discounts 5 -- Schema: inventory 6 -- ============================================================ 7 8 -- Set search path to inventory schema (with access to products schema) 9 SET search_path TO inventory, products, public; 10 11 -- ============================================================ 12 -- PRODUCT_INVENTORY TABLE 13 -- ============================================================ 14 CREATE TABLE product_inventory ( 15 id SERIAL PRIMARY KEY, 16 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 17 18 -- Product reference (from Products Service via UUID) 19 product_uuid UUID NOT NULL, -- References products.uuid in Products Service 20 product_asin VARCHAR(20), -- Secondary reference for easier lookup 21 22 -- Stock levels 23 stock_quantity INTEGER NOT NULL DEFAULT 0, 24 reserved_quantity INTEGER DEFAULT 0, -- Reserved for pending orders 25 available_quantity INTEGER GENERATED ALWAYS AS (stock_quantity - reserved_quantity) STORED, 26 27 -- Reorder thresholds 28 reorder_level INTEGER DEFAULT 10, 29 reorder_quantity INTEGER DEFAULT 50, 30 31 -- Stock status 32 stock_status VARCHAR(50) DEFAULT 'in_stock', -- 'in_stock', 'low_stock', 'out_of_stock' 33 34 -- Timestamps 35 last_restocked_at TIMESTAMP WITH TIME ZONE, 36 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 37 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 38 39 UNIQUE(product_uuid) 40 ); 41 42 CREATE INDEX idx_product_inventory_uuid ON product_inventory(uuid); 43 CREATE INDEX idx_product_inventory_product_uuid ON product_inventory(product_uuid); 44 CREATE INDEX idx_product_inventory_product_asin ON product_inventory(product_asin); 45 CREATE INDEX idx_product_inventory_stock_status ON product_inventory(stock_status); 46 CREATE INDEX idx_product_inventory_available_quantity ON product_inventory(available_quantity); 47 48 COMMENT ON TABLE product_inventory IS 'Product stock levels - Inventory Service'; 49 COMMENT ON COLUMN product_inventory.product_uuid IS 'References products.uuid from Products Service'; 50 COMMENT ON COLUMN product_inventory.reserved_quantity IS 'Quantity reserved for pending orders'; 51 COMMENT ON COLUMN product_inventory.available_quantity IS 'Computed: stock_quantity - reserved_quantity'; 52 53 -- ============================================================ 54 -- PRODUCT_PRICING TABLE 55 -- ============================================================ 56 CREATE TABLE product_pricing ( 57 id SERIAL PRIMARY KEY, 58 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 59 60 -- Product reference (from Products Service via UUID) 61 product_uuid UUID NOT NULL, -- References products.uuid in Products Service 62 product_asin VARCHAR(20), -- Secondary reference 63 64 -- Pricing 65 final_price DECIMAL(10, 2) NOT NULL, 66 initial_price DECIMAL(10, 2), -- Original price before discount 67 currency VARCHAR(10) DEFAULT 'USD', 68 69 -- Discount 70 discount_percentage DECIMAL(5, 2), -- e.g., 20.50 for 20.5% 71 discount_amount DECIMAL(10, 2), -- Calculated discount in currency 72 73 -- Validity 74 price_valid_from TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 75 price_valid_until TIMESTAMP WITH TIME ZONE, 76 77 -- Status 78 is_active BOOLEAN DEFAULT true, 79 80 -- Timestamps 81 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 82 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 83 ); 84 85 -- Only one active price per product 86 CREATE UNIQUE INDEX idx_product_pricing_unique_active ON product_pricing(product_uuid, is_active) WHERE is_active = true; 87 88 CREATE INDEX idx_product_pricing_uuid ON product_pricing(uuid); 89 CREATE INDEX idx_product_pricing_product_uuid ON product_pricing(product_uuid); 90 CREATE INDEX idx_product_pricing_product_asin ON product_pricing(product_asin); 91 CREATE INDEX idx_product_pricing_is_active ON product_pricing(is_active); 92 CREATE INDEX idx_product_pricing_final_price ON product_pricing(final_price); 93 94 COMMENT ON TABLE product_pricing IS 'Product pricing and discounts - Inventory Service'; 95 COMMENT ON COLUMN product_pricing.product_uuid IS 'References products.uuid from Products Service'; 96 COMMENT ON COLUMN product_pricing.discount_percentage IS 'Discount as percentage (e.g., 20.50 = 20.5%)'; 97 98 -- ============================================================ 99 -- PRICE_HISTORY TABLE (Optional - for tracking price changes) 100 -- ============================================================ 101 CREATE TABLE price_history ( 102 id SERIAL PRIMARY KEY, 103 product_uuid UUID NOT NULL, 104 product_asin VARCHAR(20), 105 106 -- Price snapshot 107 final_price DECIMAL(10, 2) NOT NULL, 108 initial_price DECIMAL(10, 2), 109 discount_percentage DECIMAL(5, 2), 110 currency VARCHAR(10) DEFAULT 'USD', 111 112 -- When this price was active 113 effective_from TIMESTAMP WITH TIME ZONE NOT NULL, 114 effective_until TIMESTAMP WITH TIME ZONE, 115 116 -- Metadata 117 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 118 ); 119 120 CREATE INDEX idx_price_history_product_uuid ON price_history(product_uuid); 121 CREATE INDEX idx_price_history_product_asin ON price_history(product_asin); 122 CREATE INDEX idx_price_history_effective_from ON price_history(effective_from); 123 124 COMMENT ON TABLE price_history IS 'Historical price data for analytics'; 125 126 -- ============================================================ 127 -- INVENTORY_TRANSACTIONS TABLE (Optional - audit trail) 128 -- ============================================================ 129 CREATE TABLE inventory_transactions ( 130 id SERIAL PRIMARY KEY, 131 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 132 133 -- Inventory reference 134 inventory_id INTEGER NOT NULL REFERENCES product_inventory(id) ON DELETE CASCADE, 135 product_uuid UUID NOT NULL, 136 137 -- Transaction details 138 transaction_type VARCHAR(50) NOT NULL, -- 'purchase', 'sale', 'return', 'adjustment', 'restock' 139 quantity_change INTEGER NOT NULL, -- Positive for increase, negative for decrease 140 quantity_before INTEGER NOT NULL, 141 quantity_after INTEGER NOT NULL, 142 143 -- Reference to source (e.g., order_uuid from Orders Service) 144 reference_type VARCHAR(50), -- 'order', 'manual', 'return' 145 reference_uuid UUID, 146 147 -- Notes 148 notes TEXT, 149 150 -- Timestamp 151 transaction_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 152 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 153 ); 154 155 CREATE INDEX idx_inventory_transactions_uuid ON inventory_transactions(uuid); 156 CREATE INDEX idx_inventory_transactions_inventory_id ON inventory_transactions(inventory_id); 157 CREATE INDEX idx_inventory_transactions_product_uuid ON inventory_transactions(product_uuid); 158 CREATE INDEX idx_inventory_transactions_type ON inventory_transactions(transaction_type); 159 CREATE INDEX idx_inventory_transactions_reference_uuid ON inventory_transactions(reference_uuid); 160 CREATE INDEX idx_inventory_transactions_date ON inventory_transactions(transaction_date); 161 162 COMMENT ON TABLE inventory_transactions IS 'Audit trail of all inventory changes'; 163 164 -- ============================================================ 165 -- TRIGGERS FOR UPDATED_AT 166 -- ============================================================ 167 168 CREATE OR REPLACE FUNCTION update_updated_at_column() 169 RETURNS TRIGGER AS $$ 170 BEGIN 171 NEW.updated_at = CURRENT_TIMESTAMP; 172 RETURN NEW; 173 END; 174 $$ language 'plpgsql'; 175 176 CREATE TRIGGER update_product_inventory_updated_at BEFORE UPDATE ON product_inventory 177 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 178 179 CREATE TRIGGER update_product_pricing_updated_at BEFORE UPDATE ON product_pricing 180 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 181 182 -- ============================================================ 183 -- FUNCTION: Update stock status based on quantity 184 -- ============================================================ 185 186 CREATE OR REPLACE FUNCTION update_stock_status() 187 RETURNS TRIGGER AS $$ 188 BEGIN 189 IF NEW.available_quantity <= 0 THEN 190 NEW.stock_status := 'out_of_stock'; 191 ELSIF NEW.available_quantity <= NEW.reorder_level THEN 192 NEW.stock_status := 'low_stock'; 193 ELSE 194 NEW.stock_status := 'in_stock'; 195 END IF; 196 RETURN NEW; 197 END; 198 $$ language 'plpgsql'; 199 200 CREATE TRIGGER update_inventory_stock_status BEFORE INSERT OR UPDATE ON product_inventory 201 FOR EACH ROW EXECUTE FUNCTION update_stock_status(); 202 203 -- ============================================================ 204 -- FUNCTION: Calculate discount amount 205 -- ============================================================ 206 207 CREATE OR REPLACE FUNCTION calculate_discount() 208 RETURNS TRIGGER AS $$ 209 BEGIN 210 IF NEW.initial_price IS NOT NULL AND NEW.initial_price > NEW.final_price THEN 211 NEW.discount_amount := NEW.initial_price - NEW.final_price; 212 NEW.discount_percentage := ROUND(((NEW.initial_price - NEW.final_price) / NEW.initial_price * 100), 2); 213 ELSE 214 NEW.discount_amount := 0; 215 NEW.discount_percentage := 0; 216 END IF; 217 RETURN NEW; 218 END; 219 $$ language 'plpgsql'; 220 221 CREATE TRIGGER calculate_pricing_discount BEFORE INSERT OR UPDATE ON product_pricing 222 FOR EACH ROW EXECUTE FUNCTION calculate_discount(); 223 224 -- ============================================================ 225 -- FUNCTION: Reserve stock for order 226 -- ============================================================ 227 228 CREATE OR REPLACE FUNCTION reserve_stock( 229 p_product_uuid UUID, 230 p_quantity INTEGER 231 ) RETURNS BOOLEAN AS $$ 232 DECLARE 233 v_available INTEGER; 234 BEGIN 235 -- Get current available quantity 236 SELECT available_quantity INTO v_available 237 FROM product_inventory 238 WHERE product_uuid = p_product_uuid 239 FOR UPDATE; -- Lock the row 240 241 -- Check if enough stock 242 IF v_available IS NULL OR v_available < p_quantity THEN 243 RETURN FALSE; 244 END IF; 245 246 -- Reserve the stock 247 UPDATE product_inventory 248 SET reserved_quantity = reserved_quantity + p_quantity 249 WHERE product_uuid = p_product_uuid; 250 251 RETURN TRUE; 252 END; 253 $$ LANGUAGE plpgsql; 254 255 COMMENT ON FUNCTION reserve_stock IS 'Reserve stock for an order (returns true if successful)'; 256 257 -- ============================================================ 258 -- FUNCTION: Release reserved stock 259 -- ============================================================ 260 261 CREATE OR REPLACE FUNCTION release_stock( 262 p_product_uuid UUID, 263 p_quantity INTEGER 264 ) RETURNS VOID AS $$ 265 BEGIN 266 UPDATE product_inventory 267 SET reserved_quantity = GREATEST(0, reserved_quantity - p_quantity) 268 WHERE product_uuid = p_product_uuid; 269 END; 270 $$ LANGUAGE plpgsql; 271 272 COMMENT ON FUNCTION release_stock IS 'Release reserved stock (e.g., when order is cancelled)'; 273 274 -- ============================================================ 275 -- FUNCTION: Confirm stock reservation (convert to sale) 276 -- ============================================================ 277 278 CREATE OR REPLACE FUNCTION confirm_stock_sale( 279 p_product_uuid UUID, 280 p_quantity INTEGER, 281 p_order_uuid UUID 282 ) RETURNS VOID AS $$ 283 BEGIN 284 -- Decrease both stock and reserved quantities 285 UPDATE product_inventory 286 SET 287 stock_quantity = stock_quantity - p_quantity, 288 reserved_quantity = GREATEST(0, reserved_quantity - p_quantity) 289 WHERE product_uuid = p_product_uuid; 290 291 -- Log transaction 292 INSERT INTO inventory_transactions ( 293 inventory_id, 294 product_uuid, 295 transaction_type, 296 quantity_change, 297 quantity_before, 298 quantity_after, 299 reference_type, 300 reference_uuid 301 ) 302 SELECT 303 id, 304 product_uuid, 305 'sale', 306 -p_quantity, 307 stock_quantity + p_quantity, 308 stock_quantity, 309 'order', 310 p_order_uuid 311 FROM product_inventory 312 WHERE product_uuid = p_product_uuid; 313 END; 314 $$ LANGUAGE plpgsql; 315 316 COMMENT ON FUNCTION confirm_stock_sale IS 'Confirm sale and decrease stock (called when order is paid)'; 317 318 -- ============================================================ 319 -- VIEWS 320 -- ============================================================ 321 322 -- Combined inventory and pricing view (for API responses) 323 CREATE VIEW v_product_inventory_pricing AS 324 SELECT 325 pi.product_uuid, 326 pi.product_asin, 327 pi.stock_quantity, 328 pi.reserved_quantity, 329 pi.available_quantity, 330 pi.stock_status, 331 pp.final_price, 332 pp.initial_price, 333 pp.currency, 334 pp.discount_percentage, 335 CASE 336 WHEN pp.discount_percentage > 0 THEN CONCAT(pp.discount_percentage::TEXT, '%') 337 ELSE NULL 338 END as discount, 339 pi.last_restocked_at, 340 pp.updated_at as price_updated_at 341 FROM product_inventory pi 342 LEFT JOIN product_pricing pp ON pi.product_uuid = pp.product_uuid AND pp.is_active = true; 343 344 -- Low stock products 345 CREATE VIEW v_low_stock_products AS 346 SELECT 347 product_uuid, 348 product_asin, 349 stock_quantity, 350 reserved_quantity, 351 available_quantity, 352 reorder_level, 353 stock_status 354 FROM product_inventory 355 WHERE stock_status IN ('low_stock', 'out_of_stock') 356 ORDER BY available_quantity ASC; 357 358 COMMENT ON VIEW v_product_inventory_pricing IS 'Combined inventory and pricing data for API responses'; 359 COMMENT ON VIEW v_low_stock_products IS 'Products that need restocking'; 360 361 -- ============================================================ 362 -- SAMPLE DATA (for testing) 363 -- ============================================================ 364 365 -- Note: Product UUIDs will come from Products Service 366 -- This is just a template for how data will be structured 367 368 -- Example: If a product in Products Service has uuid = '550e8400-e29b-41d4-a716-446655440000' 369 -- Then in Inventory Service: 370 371 /* 372 INSERT INTO product_inventory (product_uuid, product_asin, stock_quantity, reorder_level) 373 VALUES ('550e8400-e29b-41d4-a716-446655440000', 'B00ABC123', 100, 10); 374 375 INSERT INTO product_pricing (product_uuid, product_asin, final_price, initial_price, currency) 376 VALUES ('550e8400-e29b-41d4-a716-446655440000', 'B00ABC123', 29.99, 39.99, 'USD'); 377 */