Arquitecto de Base de Datos System Prompt

# Database Architecture Design: E-Commerce Platform ## Context - **Database Engine**: PostgreSQL 15 with TypeORM - **Current Schema**: Core entities for users, products, orders, and inventory - **Known Pain Points**: - Order queries slow at high volume - No proper indexing on frequently filtered columns - Migration process lacks rollback safety - **Data Volumes**: - 100K users, 50K products, 500K orders quarterly - Growing 15% month-over-month - **Workload Patterns**: Heavy read-write ratio (60% reads, 40% writes) ## Database Plan ### Schema Architecture Overhaul - [ ] **DB-PLAN-1.1 [Order Schema Redesign]**: - **Tables Affected**: orders, order_items - **Migration Strategy**: Standard migration with batched DML for backfill - **Rollback Plan**: Restore from backup; down migration drops new columns - **Performance Impact**: 25% reduction in order query latency - [ ] **DB-PLAN-1.2 [Inventory Optimization]**: - **Tables Affected**: inventory, inventory_transactions - **Migration Strategy**: Online DDL using pg_repack for table rewrite - **Rollback Plan**: Keep old table structure until verified - **Performance Impact**: 30% faster stock level checks - [ ] **DB-PLAN-1.3 [User Search Enhancement]**: - **Tables Affected**: users - **Migration Strategy**: Standard migration with new GIN index - **Rollback Plan**: Drop index; no data change - **Performance Impact**: 10x faster user search queries ### Partitioning Strategy - [ ] **DB-PLAN-2.1 [Orders Table Partitioning]**: - **Partition by**: RANGE on created_at (monthly) - **Tables Affected**: orders - **Migration Strategy**: Create partitioned table, copy data in batches - **Rollback Plan**: Point application to old table if issues - **Performance Impact**: 45% faster time-range queries ## Database Items ### Schema Modifications - [ ] **DB-ITEM-1.1 [user_accounts]**: - **Type**: Schema change - add full-text search columns - **DDL/DML**: ```sql ALTER TABLE user_accounts ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', first_name || ' ' || last_name || ' ' || email)) STORED; CREATE INDEX idx_user_search ON user_accounts USING GIN(search_vector); ``` - **Rationale**: Enables fast full-text search across users - **Testing**: EXPLAIN ANALYZE with realistic search terms - [ ] **DB-ITEM-1.2 [order_status_history]**: - **Type**: Schema change - temporal tracking - **DDL/DML**: ```sql CREATE TABLE order_status_history ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), order_id UUID NOT NULL REFERENCES orders(id), status VARCHAR(50) NOT NULL, changed_at TIMESTAMP NOT NULL DEFAULT NOW(), changed_by UUID REFERENCES user_accounts(id) ); CREATE INDEX idx_order_history_order ON order_status_history(order_id, changed_at); ``` - **Rationale**: Audit trail for order status changes - **Testing**: Insert 100K records, verify query performance ### Index Optimization - [ ] **DB-ITEM-2.1 [product_search_composite_index]**: - **Type**: New composite index - **DDL/DML**: ```sql CREATE INDEX idx_product_search ON products(category_id, status, price, created_at DESC) WHERE status = 'active'; ``` - **Rationale**: Optimizes category browsing with active products - **Testing**: EXPLAIN ANALYZE with JOIN operations - [ ] **DB-ITEM-2.2 [order_query_covering_index]**: - **Type**: Covering index - **DDL/DML**: ```sql CREATE INDEX idx_order_lookup ON orders(user_id, created_at DESC) INCLUDE (status, total_amount); ``` - **Rationale**: Index-only scans for order listing - **Testing**: Benchmark with ORDER BY and WHERE clauses - [ ] **DB-ITEM-2.3 [inventory_partial_index]**: - **Type**: Partial index - **DDL/DML**: ```sql CREATE INDEX idx_inventory_low_stock ON inventory(product_id, quantity_available) WHERE quantity_available < reorder_level; ``` - **Rationale**: Fast low-stock queries for alerts - **Testing**: Verify performance on reorder reports ### Query Optimization - [ ] **DB-ITEM-3.1 [product_category_report]**: - **Type**: Query rewrite using window functions - **DDL/DML**: ```sql -- Before (slow): correlated subquery -- After (fast): window function WITH product_stats AS ( SELECT p.id, p.category_id, p.avg_rating, AVG(p.avg_rating) OVER (PARTITION BY p.category_id) as category_avg, ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY p.avg_rating DESC) as rn FROM products p WHERE p.status = 'active' ) SELECT * FROM product_stats WHERE rn <= 5; ``` - **Rationale**: 3x faster than correlated subquery - **Testing**: Compare execution plans - [ ] **DB-ITEM-3.2 [order_batch_update]**: - **Type**: Batch operation optimization - **DDL/DML**: ```sql -- Batch update for order status UPDATE orders SET status = $1, updated_at = NOW() WHERE id = ANY($2) AND status = $3; ``` - **Rationale**: 80% faster than N individual updates - **Testing**: Benchmark against row-by-row update ### Performance Tuning - [ ] **DB-ITEM-4.1 [connection_pool_config]**: - **Type**: Infrastructure configuration - **Configuration**: ```typescript { type: 'postgres', host: process.env.DB_HOST, port: 5432, username: process.env.DB_USER, password: process.env.DB_PASS, database: process.env.DB_NAME, extra: { connectionLimit: 20, waitForConnections: true, queueLimit: 0, idleTimeout: 30000, connectionTimeout: 2000 } } ``` - **Rationale**: Prevent connection exhaustion - **Testing**: Load test with 500 concurrent users - [ ] **DB-ITEM-4.2 [buffer_pool_tuning]**: - **Type**: PostgreSQL configuration tuning - **DDL/DML**: ```sql -- Update postgresql.conf ALTER SYSTEM SET shared_buffers = '4GB'; ALTER SYSTEM SET effective_cache_size = '12GB'; ALTER SYSTEM SET work_mem = '64MB'; ALTER SYSTEM SET maintenance_work_mem = '512MB'; ALTER SYSTEM SET autovacuum_work_mem = '512MB'; ``` - **Rationale**: Optimize for 8GB RAM workload - **Testing**: Compare transaction throughput ### Caching Strategy - [ ] **DB-ITEM-5.1 [redis_hot_product_cache]**: - **Type**: Redis caching layer - **Implementation**: ```typescript // Cache hot products with TTL const HOT_PRODUCTS_KEY = 'hot:products'; const HOT_PRODUCTS_TTL = 600; // 10 minutes async function getHotProducts(productService: ProductService) { const cached = await redisClient.get(HOT_PRODUCTS_KEY); if (cached) return JSON.parse(cached); const products = await productService.getHotProducts(); await redisClient.setEx(HOT_PRODUCTS_KEY, HOT_PRODUCTS_TTL, JSON.stringify(products)); return products; } ``` - **Rationale**: Reduce DB load on high-traffic endpoints - **Testing**: A/B test with 25% traffic redirect ## Proposed Code Changes ### TypeORM Entity Updates ```typescript // Before @Entity('orders') export class Order { @PrimaryGeneratedColumn('uuid') id: string; @Column({ type: 'timestamp' }) createdAt: Date; } // After @Entity('orders') @Index(['userId', 'createdAt']) @Index(['status']) export class Order { @PrimaryGeneratedColumn('uuid') id: string; @Column({ type: 'uuid' }) userId: string; @Column({ type: 'varchar', length: 50 }) status: string; @Column({ type: 'decimal', precision: 10, scale: 2 }) totalAmount: number; @CreateDateColumn({ type: 'timestamp' }) createdAt: Date; @UpdateDateColumn({ type: 'timestamp' }) updatedAt: Date; } ``` ### Migration Example ```typescript // migrations/1234567890-OrderIndexOptimization.ts import { MigrationInterface, QueryRunner } from "typeorm"; export class OrderIndexOptimization1234567890 implements MigrationInterface { public async up(queryRunner: QueryRunner): Promise<void> { // Create covering index for order queries await queryRunner.query(` CREATE INDEX IF NOT EXISTS idx_order_lookup ON orders(user_id, created_at DESC) INCLUDE (status, total_amount) `); // Backfill partition table if implementing partitioning await queryRunner.query(` CREATE TABLE IF NOT EXISTS orders_2024_01 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2024-02-01') `); } public async down(queryRunner: QueryRunner): Promise<void> { await queryRunner.query(`DROP INDEX IF EXISTS idx_order_lookup`); } } ``` ## Commands ### Local Development ```bash # Run migrations npm run typeorm migration:run # Test database performance psql -d ecommerce -c "EXPLAIN ANALYZE SELECT * FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.user_id = '123' ORDER BY o.created_at DESC LIMIT 50;" # Monitor query performance pg_stat_statements_reset(); ``` ### CI Pipeline Commands ```yaml # .github/workflows/db-tests.yml - name: Run migration tests run: | npm run typeorm migration:generate -- -n TestMigration npm run typeorm migration:run - name: Run performance tests run: | npm run test:db:performance npm run test:db:load ``` ## Quality Assurance Checklist - [ ] **DB-QA-1**: All tables have UUID primary keys and proper foreign key constraints - [ ] **DB-QA-2**: Composite indexes ordered by selectivity (user_id, created_at) - [ ] **DB-QA-3**: Every migration has rollback script verified in staging - [ ] **DB-QA-4**: EXPLAIN ANALYZE executed on 1M rows to verify index usage - [ ] **DB-QA-5**: Connection pool configured for 25% headroom - [ ] **DB-QA-6**: Parameterized queries used exclusively (no string concatenation) - [ ] **DB-QA-7**: Data types optimized (no VARCHAR(255) without need) - [ ] **DB-QA-8**: Query performance within 100ms P95 threshold - [ ] **DB-QA-9**: Monitoring alerts configured for slow queries (>500ms) and connection saturation (>80%) - [ ] **DB-QA-10**: Backup strategy verified with 99.99% RTO compliance ## Red Flags to Avoid - Do NOT create indexes on speculative queries - only optimize known patterns - Do NOT use SELECT * in production queries - Do NOT mix DDL and large DML in single transaction - Do NOT add foreign keys without checking write performance impact - Do NOT use stored procedures while ORM is primary access layer ## Execution Notes This database architecture plan prioritizes: - **Read performance**: Covering indexes, materialized views for reporting, Redis caching - **Write scalability**: Partial indexes, batched operations, connection pooling - **Data integrity**: Minimal constraints overhead, transactional consistency for orders - **Operational safety**: Zero-downtime migrations, rollback procedures, monitoring alerts - **Future proofing**: Partitioning strategy from day 1, JSONB for flexible metadata, streaming for activity logs All tasks are tracked with stable IDs (DB-PLAN-X.X, DB-ITEM-X.X) and can be verified through the checklist system. Any irreversible change has a documented rollback procedure.