orders_schema_1.sql (14577B)
1 -- ============================================================ 2 -- ORDERS SCHEMA - ORDERS & CHECKOUT SERVICE 3 -- ============================================================ 4 -- Handles: Orders, payments, shipping, guest checkout 5 -- Schema: orders 6 -- Port: 3003 7 8 -- Set search path to orders schema (with access to products schema) 9 SET search_path TO orders, products, public; 10 11 -- ============================================================ 12 -- ORDERS TABLE 13 -- ============================================================ 14 CREATE TABLE orders ( 15 id SERIAL PRIMARY KEY, 16 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 17 18 -- Order identification 19 order_number VARCHAR(50) UNIQUE NOT NULL, -- e.g., "ORD-20241118-001" 20 21 -- Customer info (guest checkout - no user_id needed) 22 customer_email VARCHAR(255) NOT NULL, 23 customer_phone VARCHAR(50), 24 is_guest_order BOOLEAN DEFAULT true, 25 26 -- Pricing breakdown 27 subtotal DECIMAL(10, 2) NOT NULL, -- Sum of item prices 28 tax_amount DECIMAL(10, 2) NOT NULL DEFAULT 0, 29 shipping_amount DECIMAL(10, 2) NOT NULL DEFAULT 0, 30 total DECIMAL(10, 2) NOT NULL, -- subtotal + tax + shipping 31 32 -- Order status 33 status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'processing', 'shipped', 'delivered', 'cancelled', 'failed' 34 payment_status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'paid', 'failed', 'refunded' 35 36 -- Timestamps 37 ordered_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 38 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 39 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 40 ); 41 42 CREATE INDEX idx_orders_uuid ON orders(uuid); 43 CREATE INDEX idx_orders_order_number ON orders(order_number); 44 CREATE INDEX idx_orders_customer_email ON orders(customer_email); 45 CREATE INDEX idx_orders_status ON orders(status); 46 CREATE INDEX idx_orders_payment_status ON orders(payment_status); 47 CREATE INDEX idx_orders_ordered_at ON orders(ordered_at); 48 49 COMMENT ON TABLE orders IS 'Customer orders - Orders Service'; 50 COMMENT ON COLUMN orders.uuid IS 'External UUID (exposed as eid in API)'; 51 COMMENT ON COLUMN orders.order_number IS 'Human-readable order number'; 52 COMMENT ON COLUMN orders.is_guest_order IS 'True for guest checkout (no user account)'; 53 54 -- ============================================================ 55 -- ORDER_ITEMS TABLE 56 -- ============================================================ 57 CREATE TABLE order_items ( 58 id SERIAL PRIMARY KEY, 59 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 60 61 -- Order reference 62 order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE, 63 64 -- Product reference (from Products Service via UUID) 65 product_uuid UUID NOT NULL, -- References products.uuid from Products Service 66 product_asin VARCHAR(20), 67 68 -- Product snapshot (data at time of order) 69 product_name VARCHAR(500) NOT NULL, 70 product_sku VARCHAR(100), 71 72 -- Pricing 73 quantity INTEGER NOT NULL DEFAULT 1, 74 unit_price DECIMAL(10, 2) NOT NULL, 75 total_price DECIMAL(10, 2) NOT NULL, -- unit_price * quantity 76 77 -- Timestamps 78 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP 79 ); 80 81 CREATE INDEX idx_order_items_uuid ON order_items(uuid); 82 CREATE INDEX idx_order_items_order_id ON order_items(order_id); 83 CREATE INDEX idx_order_items_product_uuid ON order_items(product_uuid); 84 CREATE INDEX idx_order_items_product_asin ON order_items(product_asin); 85 86 COMMENT ON TABLE order_items IS 'Line items in orders with product snapshots'; 87 COMMENT ON COLUMN order_items.product_uuid IS 'References products.uuid from Products Service'; 88 COMMENT ON COLUMN order_items.product_name IS 'Product name snapshot at time of order'; 89 90 -- ============================================================ 91 -- SHIPPING_ADDRESSES TABLE 92 -- ============================================================ 93 CREATE TABLE shipping_addresses ( 94 id SERIAL PRIMARY KEY, 95 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 96 97 -- Order reference 98 order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE, 99 100 -- Recipient 101 first_name VARCHAR(100) NOT NULL, 102 last_name VARCHAR(100) NOT NULL, 103 104 -- Address 105 address_line1 VARCHAR(255) NOT NULL, 106 address_line2 VARCHAR(255), 107 city VARCHAR(100) NOT NULL, 108 state VARCHAR(100) NOT NULL, 109 postal_code VARCHAR(20) NOT NULL, 110 country VARCHAR(100) NOT NULL DEFAULT 'US', 111 112 -- Contact 113 phone VARCHAR(50), 114 115 -- Timestamps 116 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 117 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 118 119 UNIQUE(order_id) -- One shipping address per order 120 ); 121 122 CREATE INDEX idx_shipping_addresses_uuid ON shipping_addresses(uuid); 123 CREATE INDEX idx_shipping_addresses_order_id ON shipping_addresses(order_id); 124 125 COMMENT ON TABLE shipping_addresses IS 'Shipping addresses for orders'; 126 COMMENT ON COLUMN shipping_addresses.uuid IS 'External UUID (exposed as eid in API)'; 127 128 -- ============================================================ 129 -- PAYMENTS TABLE 130 -- ============================================================ 131 CREATE TABLE payments ( 132 id SERIAL PRIMARY KEY, 133 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 134 135 -- Order reference 136 order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE, 137 138 -- Payment details 139 payment_method VARCHAR(50) NOT NULL, -- 'credit_card', 'debit_card' 140 amount DECIMAL(10, 2) NOT NULL, 141 142 -- Payment status 143 status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'paid', 'failed', 'refunded', 'cancelled' 144 145 -- Simulated payment info (not real payment processing) 146 payment_reference VARCHAR(255), -- Simulated transaction ID 147 card_last4 VARCHAR(4), -- Last 4 digits (for display) 148 card_brand VARCHAR(50), -- 'visa', 'mastercard', 'amex', etc. 149 150 -- Timestamps 151 processed_at TIMESTAMP WITH TIME ZONE, 152 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 153 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 154 155 UNIQUE(order_id) -- One payment per order (simplified) 156 ); 157 158 CREATE INDEX idx_payments_uuid ON payments(uuid); 159 CREATE INDEX idx_payments_order_id ON payments(order_id); 160 CREATE INDEX idx_payments_status ON payments(status); 161 CREATE INDEX idx_payments_payment_method ON payments(payment_method); 162 163 COMMENT ON TABLE payments IS 'Simulated payment records'; 164 COMMENT ON COLUMN payments.uuid IS 'External UUID (exposed as eid in API)'; 165 COMMENT ON COLUMN payments.payment_reference IS 'Simulated transaction ID (not real)'; 166 167 -- ============================================================ 168 -- SHIPMENTS TABLE (Optional - for tracking) 169 -- ============================================================ 170 CREATE TABLE shipments ( 171 id SERIAL PRIMARY KEY, 172 uuid UUID NOT NULL DEFAULT uuid_generate_v4() UNIQUE, 173 174 -- Order reference 175 order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE, 176 177 -- Shipping carrier (from client: 'ups', 'fedex', 'dhl') 178 carrier VARCHAR(50) NOT NULL, 179 tracking_number VARCHAR(255), 180 181 -- Shipment status 182 status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'shipped', 'in_transit', 'delivered' 183 184 -- Delivery dates 185 estimated_delivery_date DATE, 186 actual_delivery_date DATE, 187 188 -- Timestamps 189 shipped_at TIMESTAMP WITH TIME ZONE, 190 delivered_at TIMESTAMP WITH TIME ZONE, 191 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 192 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, 193 194 UNIQUE(order_id) -- One shipment per order (simplified) 195 ); 196 197 CREATE INDEX idx_shipments_uuid ON shipments(uuid); 198 CREATE INDEX idx_shipments_order_id ON shipments(order_id); 199 CREATE INDEX idx_shipments_tracking_number ON shipments(tracking_number); 200 CREATE INDEX idx_shipments_status ON shipments(status); 201 CREATE INDEX idx_shipments_carrier ON shipments(carrier); 202 203 COMMENT ON TABLE shipments IS 'Order shipment tracking'; 204 COMMENT ON COLUMN shipments.uuid IS 'External UUID (exposed as eid in API)'; 205 206 -- ============================================================ 207 -- TRIGGERS FOR UPDATED_AT 208 -- ============================================================ 209 210 CREATE OR REPLACE FUNCTION update_updated_at_column() 211 RETURNS TRIGGER AS $$ 212 BEGIN 213 NEW.updated_at = CURRENT_TIMESTAMP; 214 RETURN NEW; 215 END; 216 $$ language 'plpgsql'; 217 218 CREATE TRIGGER update_orders_updated_at BEFORE UPDATE ON orders 219 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 220 221 CREATE TRIGGER update_shipping_addresses_updated_at BEFORE UPDATE ON shipping_addresses 222 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 223 224 CREATE TRIGGER update_payments_updated_at BEFORE UPDATE ON payments 225 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 226 227 CREATE TRIGGER update_shipments_updated_at BEFORE UPDATE ON shipments 228 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); 229 230 -- ============================================================ 231 -- FUNCTION: Generate order number 232 -- ============================================================ 233 234 CREATE OR REPLACE FUNCTION generate_order_number() 235 RETURNS TEXT AS $$ 236 DECLARE 237 v_date TEXT; 238 v_sequence TEXT; 239 v_order_number TEXT; 240 BEGIN 241 -- Format: ORD-YYYYMMDD-XXX 242 v_date := TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDD'); 243 244 -- Get next sequence number for today 245 SELECT LPAD((COUNT(*) + 1)::TEXT, 3, '0') 246 INTO v_sequence 247 FROM orders 248 WHERE order_number LIKE 'ORD-' || v_date || '-%'; 249 250 v_order_number := 'ORD-' || v_date || '-' || v_sequence; 251 252 RETURN v_order_number; 253 END; 254 $$ LANGUAGE plpgsql; 255 256 COMMENT ON FUNCTION generate_order_number IS 'Generate unique order number: ORD-YYYYMMDD-001'; 257 258 -- ============================================================ 259 -- FUNCTION: Generate simulated payment reference 260 -- ============================================================ 261 262 CREATE OR REPLACE FUNCTION generate_payment_reference() 263 RETURNS TEXT AS $$ 264 BEGIN 265 -- Format: PAY-TIMESTAMP-RANDOM 266 RETURN 'PAY-' || 267 EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)::BIGINT::TEXT || '-' || 268 SUBSTRING(MD5(RANDOM()::TEXT), 1, 8); 269 END; 270 $$ LANGUAGE plpgsql; 271 272 COMMENT ON FUNCTION generate_payment_reference IS 'Generate simulated payment transaction ID'; 273 274 -- ============================================================ 275 -- FUNCTION: Calculate order total 276 -- ============================================================ 277 278 CREATE OR REPLACE FUNCTION calculate_order_total(p_order_id INTEGER) 279 RETURNS DECIMAL AS $$ 280 DECLARE 281 v_subtotal DECIMAL; 282 v_tax DECIMAL; 283 v_shipping DECIMAL; 284 v_total DECIMAL; 285 BEGIN 286 SELECT subtotal, tax_amount, shipping_amount 287 INTO v_subtotal, v_tax, v_shipping 288 FROM orders 289 WHERE id = p_order_id; 290 291 v_total := v_subtotal + v_tax + v_shipping; 292 293 UPDATE orders 294 SET total = v_total 295 WHERE id = p_order_id; 296 297 RETURN v_total; 298 END; 299 $$ LANGUAGE plpgsql; 300 301 COMMENT ON FUNCTION calculate_order_total IS 'Calculate and update order total'; 302 303 -- ============================================================ 304 -- VIEWS 305 -- ============================================================ 306 307 -- Complete order view with all related data 308 CREATE VIEW v_orders_complete AS 309 SELECT 310 o.id, 311 o.uuid as eid, 312 o.order_number, 313 o.customer_email, 314 o.customer_phone, 315 o.subtotal, 316 o.tax_amount, 317 o.shipping_amount, 318 o.total, 319 o.status, 320 o.payment_status, 321 o.is_guest_order, 322 o.ordered_at, 323 o.created_at, 324 o.updated_at, 325 326 -- Shipping address (as JSON) 327 jsonb_build_object( 328 'eid', sa.uuid, 329 'first_name', sa.first_name, 330 'last_name', sa.last_name, 331 'address_line1', sa.address_line1, 332 'address_line2', sa.address_line2, 333 'city', sa.city, 334 'state', sa.state, 335 'postal_code', sa.postal_code, 336 'country', sa.country, 337 'phone', sa.phone 338 ) as shipping_address, 339 340 -- Payment info (as JSON) 341 jsonb_build_object( 342 'eid', p.uuid, 343 'payment_method', p.payment_method, 344 'amount', p.amount, 345 'status', p.status, 346 'payment_reference', p.payment_reference, 347 'card_last4', p.card_last4, 348 'card_brand', p.card_brand, 349 'processed_at', p.processed_at 350 ) as payment, 351 352 -- Shipment info (as JSON, if exists) 353 CASE 354 WHEN s.id IS NOT NULL THEN 355 jsonb_build_object( 356 'eid', s.uuid, 357 'carrier', s.carrier, 358 'tracking_number', s.tracking_number, 359 'status', s.status, 360 'estimated_delivery_date', s.estimated_delivery_date, 361 'actual_delivery_date', s.actual_delivery_date, 362 'shipped_at', s.shipped_at, 363 'delivered_at', s.delivered_at 364 ) 365 ELSE NULL 366 END as shipment, 367 368 -- Item count 369 (SELECT COUNT(*) FROM order_items WHERE order_id = o.id) as item_count, 370 (SELECT SUM(quantity) FROM order_items WHERE order_id = o.id) as total_quantity 371 372 FROM orders o 373 LEFT JOIN shipping_addresses sa ON o.id = sa.order_id 374 LEFT JOIN payments p ON o.id = p.order_id 375 LEFT JOIN shipments s ON o.id = s.order_id; 376 377 -- Order items with product info 378 CREATE VIEW v_order_items_detail AS 379 SELECT 380 oi.id, 381 oi.uuid as eid, 382 oi.order_id, 383 o.order_number, 384 oi.product_uuid, 385 oi.product_asin, 386 oi.product_name, 387 oi.product_sku, 388 oi.quantity, 389 oi.unit_price, 390 oi.total_price, 391 oi.created_at 392 FROM order_items oi 393 INNER JOIN orders o ON oi.order_id = o.id; 394 395 -- Recent orders summary 396 CREATE VIEW v_recent_orders AS 397 SELECT 398 uuid as eid, 399 order_number, 400 customer_email, 401 total, 402 status, 403 payment_status, 404 ordered_at 405 FROM orders 406 ORDER BY ordered_at DESC 407 LIMIT 100; 408 409 COMMENT ON VIEW v_orders_complete IS 'Complete order data with shipping, payment, and shipment info'; 410 COMMENT ON VIEW v_order_items_detail IS 'Order items with order information'; 411 COMMENT ON VIEW v_recent_orders IS 'Most recent 100 orders'; 412 413 -- ============================================================ 414 -- CONSTRAINTS & CHECKS 415 -- ============================================================ 416 417 -- Ensure total matches subtotal + tax + shipping 418 ALTER TABLE orders ADD CONSTRAINT check_order_total 419 CHECK (ABS(total - (subtotal + tax_amount + shipping_amount)) < 0.01); 420 421 -- Payment amount validation is handled at application level 422 423 -- Ensure quantity is positive 424 ALTER TABLE order_items ADD CONSTRAINT check_quantity_positive 425 CHECK (quantity > 0); 426 427 -- Ensure prices are non-negative 428 ALTER TABLE order_items ADD CONSTRAINT check_prices_non_negative 429 CHECK (unit_price >= 0 AND total_price >= 0);