README.md (14344B)
1 # PostgreSQL Multi-Schema Database for Ecommerce Microservices 2 3 Single PostgreSQL database with **4 schemas** (namespaces) for microservices architecture. 4 5 ## Architecture 6 7 ``` 8 ┌────────────────────────────────────────────────────────────────────────┐ 9 │ PostgreSQL Database: ecommerce │ 10 ├────────────────────────────────────────────────────────────────────────┤ 11 │ │ 12 │ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────────────────┐ │ 13 │ │ products │ │inventory │ │ orders │ │ users │ │ 14 │ │ schema │ │ schema │ │ schema │ │ schema │ │ 15 │ ├──────────┤ ├──────────┤ ├──────────┤ ├──────────────────────┤ │ 16 │ │• categrs │ │• product_│ │• orders │ │• users │ │ 17 │ │• products│ │ invntry │ │• order_ │ │• user_sessions │ │ 18 │ │• product_│ │• product_│ │ items │ │• user_addresses │ │ 19 │ │ specs │ │ pricing │ │• shipping│ │• user_profiles │ │ 20 │ │• product_│ │• price_ │ │ _addrs │ │• guest_users │ │ 21 │ │ reviews │ │ history │ │• payments│ │ │ │ 22 │ │ │ │• invntry_│ │• shipmnts│ │ │ │ 23 │ │(4 tables)│ │ trans │ │ │ │(5 tables) │ │ 24 │ │ │ │ │ │(5 tables)│ │ │ │ 25 │ │ │ │(4 tables)│ │ │ │ │ │ 26 │ └──────────┘ └──────────┘ └──────────┘ └──────────────────────┘ │ 27 │ │ 28 └────────────────────────────────────────────────────────────────────────┘ 29 ↑ ↑ ↑ ↑ 30 │ │ │ │ 31 Products Inventory Orders Users/Auth 32 Service Service Service Service 33 (Port 3001) (Port 3002) (Port 3003) (Port 3004) 34 ``` 35 36 **Total: 18 tables across 4 schemas in 1 database** 37 38 --- 39 40 ## Quick Start 41 42 ### 1. Build and Run with Docker Compose 43 44 ```bash 45 cd schemas 46 47 # Copy environment file 48 cp .env.example .env 49 50 # Build and start the database 51 docker-compose up -d 52 53 # Check logs 54 docker-compose logs -f 55 56 # Verify schemas created 57 docker-compose exec postgres psql -U ecommerce_user -d ecommerce -c "\dn" 58 ``` 59 60 ### 2. Build Docker Image Only 61 62 ```bash 63 cd schemas 64 65 # Build the image 66 docker build -t ecommerce-postgres:latest . 67 68 # Run the container 69 docker run -d \ 70 --name ecommerce_postgres \ 71 -e POSTGRES_DB=ecommerce \ 72 -e POSTGRES_USER=ecommerce_user \ 73 -e POSTGRES_PASSWORD=ecommerce_password \ 74 -p 5432:5432 \ 75 -v postgres_data:/var/lib/postgresql/data \ 76 ecommerce-postgres:latest 77 78 # Check logs 79 docker logs -f ecommerce_postgres 80 ``` 81 82 ### 3. Connect to Database 83 84 ```bash 85 # Using docker-compose 86 docker-compose exec postgres psql -U ecommerce_user -d ecommerce 87 88 # Using docker run 89 docker exec -it ecommerce_postgres psql -U ecommerce_user -d ecommerce 90 91 # From host machine (if psql is installed) 92 psql -h localhost -U ecommerce_user -d ecommerce 93 ``` 94 95 --- 96 97 ## Database Schema Details 98 99 ### Schema: `products` 100 101 **Tables (4):** 102 - `categories` - Product categories 103 - `products` - Product catalog with full-text search 104 - `product_specifications` - Detailed product specs 105 - `product_reviews` - Customer ratings and reviews 106 107 **Views:** 108 - `v_products_with_category` - Products with category info 109 - `v_product_ratings` - Aggregated ratings 110 111 **Sample Data:** 112 - 8 default categories pre-loaded 113 114 ### Schema: `inventory` 115 116 **Tables (4):** 117 - `product_inventory` - Stock levels and availability 118 - `product_pricing` - Current prices and discounts 119 - `price_history` - Historical pricing data 120 - `inventory_transactions` - Audit trail 121 122 **Functions:** 123 - `reserve_stock(product_uuid, quantity)` - Reserve stock for orders 124 - `release_stock(product_uuid, quantity)` - Release reserved stock 125 - `confirm_stock_sale(product_uuid, quantity, order_uuid)` - Confirm sale 126 127 **Views:** 128 - `v_product_inventory_pricing` - Combined inventory + pricing 129 - `v_low_stock_products` - Products needing restock 130 131 **Triggers:** 132 - Auto-calculate discounts 133 - Auto-update stock status 134 135 ### Schema: `orders` 136 137 **Tables (5):** 138 - `orders` - Customer orders 139 - `order_items` - Line items with product snapshots 140 - `shipping_addresses` - Delivery addresses 141 - `payments` - Simulated payment records 142 - `shipments` - Shipment tracking 143 144 **Functions:** 145 - `generate_order_number()` - Creates order number (ORD-YYYYMMDD-XXX) 146 - `generate_payment_reference()` - Creates payment transaction ID 147 - `calculate_order_total(order_id)` - Calculates order total 148 149 **Views:** 150 - `v_orders_complete` - Complete order data with all relations 151 - `v_order_items_detail` - Order items with order info 152 - `v_recent_orders` - Last 100 orders 153 154 **Features:** 155 - Guest checkout support (no user authentication needed) 156 - Simulated payment processing 157 - One shipment per order 158 159 ### Schema: `users` 160 161 **Tables (5):** 162 - `users` - User accounts with authentication 163 - `user_sessions` - Active user sessions with JWT tokens 164 - `user_addresses` - Multiple saved addresses per user 165 - `user_profiles` - User information and preferences 166 - `guest_users` - Guest checkout tracking and conversion 167 168 **Functions:** 169 - `generate_session_token()` - Creates session token 170 - `revoke_user_session(token)` - Logout/revoke session 171 - `convert_guest_to_user(guest_uuid, user_id)` - Convert guest to registered user 172 173 **Views:** 174 - `v_users_with_addresses` - Users with default shipping/billing addresses 175 - `v_guest_users` - Guest users with conversion status 176 177 **Features:** 178 - Email/password authentication 179 - Session management (login/logout) 180 - Multiple addresses per user (shipping/billing) 181 - Guest user conversion to registered users 182 - Email verification support 183 - Password reset support 184 185 --- 186 187 ## Connecting from Microservices 188 189 ### Connection String 190 191 ``` 192 postgres://ecommerce_user:ecommerce_password@postgres:5432/ecommerce 193 ``` 194 195 **For localhost (development):** 196 ``` 197 postgres://ecommerce_user:ecommerce_password@localhost:5432/ecommerce 198 ``` 199 200 ### Schema-Specific Connections 201 202 Each microservice should set its own search_path: 203 204 **Products Service (Rust example):** 205 ```rust 206 // Set search path after connection 207 sqlx::query("SET search_path TO products, public") 208 .execute(&pool) 209 .await?; 210 ``` 211 212 **Inventory Service (Rust example):** 213 ```rust 214 // Inventory needs access to products schema for UUID references 215 sqlx::query("SET search_path TO inventory, products, public") 216 .execute(&pool) 217 .await?; 218 ``` 219 220 **Orders Service (Rust example):** 221 ```rust 222 // Orders needs access to products schema for UUID references 223 sqlx::query("SET search_path TO orders, products, public") 224 .execute(&pool) 225 .await?; 226 ``` 227 228 **Users Service (Rust example):** 229 ```rust 230 // Users service is independent, no cross-schema references needed 231 sqlx::query("SET search_path TO users, public") 232 .execute(&pool) 233 .await?; 234 ``` 235 236 ### Cross-Schema References 237 238 Tables reference each other via **UUIDs** (not integer IDs): 239 240 ```sql 241 -- Inventory service referencing Products service 242 inventory.product_inventory.product_uuid → products.products.uuid 243 244 -- Orders service referencing Products service 245 orders.order_items.product_uuid → products.products.uuid 246 ``` 247 248 --- 249 250 ## Database Management 251 252 ### View All Schemas 253 254 ```sql 255 \dn 256 -- or 257 SELECT schema_name FROM information_schema.schemata; 258 ``` 259 260 ### View Tables in a Schema 261 262 ```sql 263 \dt products.* 264 \dt inventory.* 265 \dt orders.* 266 ``` 267 268 ### Switch Between Schemas 269 270 ```sql 271 SET search_path TO products; 272 \dt -- shows products schema tables 273 274 SET search_path TO inventory; 275 \dt -- shows inventory schema tables 276 ``` 277 278 ### Query Across Schemas 279 280 ```sql 281 -- Get product with pricing 282 SELECT 283 p.product_name, 284 pr.final_price, 285 pi.stock_quantity 286 FROM products.products p 287 JOIN inventory.product_pricing pr ON p.uuid = pr.product_uuid 288 JOIN inventory.product_inventory pi ON p.uuid = pi.product_uuid 289 WHERE p.is_active = true; 290 ``` 291 292 --- 293 294 ## File Structure 295 296 ``` 297 schemas/ 298 ├── Dockerfile # PostgreSQL container definition 299 ├── docker-compose.yml # Compose file for easy deployment 300 ├── .env.example # Environment variables template 301 ├── README.md # This file 302 └── init/ # SQL initialization scripts 303 ├── 00_init.sql # Creates schemas and extensions 304 ├── inventory_schema_1.sql # Inventory schema tables 305 ├── orders_schema_1.sql # Orders schema tables 306 └── products_schema_1.sql # Products schema tables 307 ``` 308 309 **Execution Order:** 310 1. `00_init.sql` - Creates schemas 311 2. `inventory_schema_1.sql` - Creates inventory tables 312 3. `orders_schema_1.sql` - Creates orders tables 313 4. `products_schema_1.sql` - Creates products tables (includes sample data) 314 315 --- 316 317 ## Environment Variables 318 319 | Variable | Default | Description | 320 |----------|---------|-------------| 321 | `POSTGRES_DB` | `ecommerce` | Database name | 322 | `POSTGRES_USER` | `ecommerce_user` | Database user | 323 | `POSTGRES_PASSWORD` | `ecommerce_password` | User password | 324 | `POSTGRES_PORT` | `5432` | External port mapping | 325 326 --- 327 328 ## Docker Commands 329 330 ### Start Database 331 ```bash 332 docker-compose up -d 333 ``` 334 335 ### Stop Database 336 ```bash 337 docker-compose down 338 ``` 339 340 ### Stop and Remove Data 341 ```bash 342 docker-compose down -v # WARNING: Deletes all data 343 ``` 344 345 ### View Logs 346 ```bash 347 docker-compose logs -f postgres 348 ``` 349 350 ### Restart Database 351 ```bash 352 docker-compose restart postgres 353 ``` 354 355 ### Execute SQL Script 356 ```bash 357 docker-compose exec postgres psql -U ecommerce_user -d ecommerce -f /path/to/script.sql 358 ``` 359 360 ### Backup Database 361 ```bash 362 docker-compose exec postgres pg_dump -U ecommerce_user ecommerce > backup.sql 363 ``` 364 365 ### Restore Database 366 ```bash 367 docker-compose exec -T postgres psql -U ecommerce_user -d ecommerce < backup.sql 368 ``` 369 370 --- 371 372 ## Health Check 373 374 The container includes a built-in health check: 375 376 ```bash 377 # Check container health status 378 docker-compose ps 379 380 # Manual health check 381 docker-compose exec postgres pg_isready -U ecommerce_user -d ecommerce 382 ``` 383 384 --- 385 386 ## Network Configuration 387 388 The database is attached to the `ecommerce_network` Docker network, allowing microservices to connect using the service name: 389 390 ```yaml 391 # In microservice docker-compose.yml 392 services: 393 products-service: 394 environment: 395 DATABASE_URL: postgres://ecommerce_user:ecommerce_password@postgres:5432/ecommerce 396 networks: 397 - ecommerce_network 398 399 networks: 400 ecommerce_network: 401 external: true 402 ``` 403 404 --- 405 406 ## Troubleshooting 407 408 ### Database Won't Start 409 410 ```bash 411 # Check logs 412 docker-compose logs postgres 413 414 # Verify init scripts 415 ls -la init/ 416 417 # Check permissions 418 docker-compose exec postgres ls -la /docker-entrypoint-initdb.d/ 419 ``` 420 421 ### Connection Refused 422 423 ```bash 424 # Check if container is running 425 docker-compose ps 426 427 # Check if port is exposed 428 docker-compose port postgres 5432 429 430 # Test connection from host 431 telnet localhost 5432 432 ``` 433 434 ### Schemas Not Created 435 436 ```bash 437 # Connect and check schemas 438 docker-compose exec postgres psql -U ecommerce_user -d ecommerce -c "\dn" 439 440 # Re-initialize database 441 docker-compose down -v 442 docker-compose up -d 443 ``` 444 445 ### Reset Database Completely 446 447 ```bash 448 # Stop and remove everything 449 docker-compose down -v 450 451 # Remove Docker volume 452 docker volume rm ecommerce_postgres_data 453 454 # Rebuild and start 455 docker-compose up -d --build 456 ``` 457 458 --- 459 460 ## Production Considerations 461 462 ### Security 463 464 1. **Change default credentials** in production: 465 ```bash 466 POSTGRES_PASSWORD=$(openssl rand -base64 32) 467 ``` 468 469 2. **Use secrets management** (Docker Swarm, Kubernetes): 470 ```yaml 471 secrets: 472 - postgres_password 473 ``` 474 475 3. **Restrict network access**: 476 - Don't expose port 5432 publicly 477 - Use internal Docker network only 478 479 ### Performance 480 481 1. **Tune PostgreSQL settings** (add to Dockerfile): 482 ```dockerfile 483 ENV POSTGRES_SHARED_BUFFERS=256MB \ 484 POSTGRES_MAX_CONNECTIONS=200 485 ``` 486 487 2. **Use connection pooling** in microservices (PgBouncer) 488 489 3. **Monitor query performance**: 490 ```sql 491 -- Enable query logging 492 ALTER SYSTEM SET log_statement = 'all'; 493 ``` 494 495 ### Backup & Recovery 496 497 1. **Automated backups**: 498 ```bash 499 # Cron job for daily backups 500 0 2 * * * docker-compose exec postgres pg_dump -U ecommerce_user ecommerce > /backups/ecommerce_$(date +\%Y\%m\%d).sql 501 ``` 502 503 2. **Point-in-time recovery** (enable WAL archiving) 504 505 ### High Availability 506 507 For production, consider: 508 - PostgreSQL replication (streaming replication) 509 - Connection pooling (PgBouncer) 510 - Load balancing (HAProxy) 511 - Managed PostgreSQL (AWS RDS, Google Cloud SQL, Azure Database) 512 513 --- 514 515 ## Next Steps 516 517 1. ✅ **Database is ready** - All schemas created 518 2. 🔲 **Import product data** - Load amazon-products.csv 519 3. 🔲 **Implement microservices** - Build 3 Rust services 520 4. 🔲 **Connect services** - Configure database connections 521 5. 🔲 **Test with client** - Connect Angular frontend 522 523 --- 524 525 ## Support 526 527 For issues or questions: 528 - Check logs: `docker-compose logs postgres` 529 - Verify connection: `docker-compose exec postgres psql -U ecommerce_user -d ecommerce` 530 - Reset database: `docker-compose down -v && docker-compose up -d`