# Sales Transaction Data Flow

This document outlines the complete data flow for processing a sale in the multi-tenant POS system.

## Overview
The sales process ensures data consistency through database transactions, proper inventory management, and comprehensive error handling.

## Step-by-Step Data Flow

### 1. **Request Reception**
- **Input**: POST request to `/api/sales` endpoint
- **Authentication**: JWT token validated and tenant_id extracted
- **Authorization**: Verify user has permission to create sales
- **Validation**: CreateSaleDto validation using class-validator

### 2. **Pre-Transaction Validation**
```typescript
// Validate request structure
- Verify tenantId matches authenticated user's tenant
- Validate all required fields (items array, payment_method, etc.)
- Ensure items array is not empty
- Validate each item has valid product_id and quantity > 0
```

### 3. **Database Transaction Start**
```sql
BEGIN TRANSACTION;
```

### 4. **Product Availability Check**
```typescript
// For each item in the sale:
- Query product by ID and tenant_id
- Verify product exists and is active
- Check if sufficient stock is available (product.stock >= requested_quantity)
- If any product fails validation, rollback transaction and return error
```

### 5. **Inventory Stock Validation**
```sql
-- Lock products for update to prevent race conditions
SELECT id, name, sku, price, cost, stock 
FROM products 
WHERE id = ANY($productIds) 
  AND tenant_id = $tenantId 
  AND is_active = true 
  AND deleted_at IS NULL
FOR UPDATE;
```

### 6. **Calculate Sale Totals**
```typescript
// Calculate financial totals
let subtotal = 0;
for each item:
  item.total_price = item.quantity * item.unit_price
  subtotal += item.total_price

// Apply business logic
const tax_amount = calculateTax(subtotal, tenant.tax_rate);
const total = subtotal + tax_amount - discount_amount;
```

### 7. **Generate Sale Number**
```sql
-- Use database function to generate unique sale number
SELECT generate_sale_number($tenantId);
-- Returns format: YYYYMMDD-0001, YYYYMMDD-0002, etc.
```

### 8. **Create Sales Record**
```sql
INSERT INTO sales (
  tenant_id, sale_number, subtotal, tax_amount, 
  discount_amount, total, payment_method, status, 
  customer_name, processed_by
) VALUES (...);
```

### 9. **Create Sale Items Records**
```sql
-- Insert each item with snapshot data
INSERT INTO sale_items (
  sale_id, product_id, product_name, product_sku,
  quantity, unit_price, total_price
) VALUES (...);
```

### 10. **Update Product Inventory**
```sql
-- Deduct stock for each product
UPDATE products 
SET stock = stock - $quantity,
    updated_at = NOW()
WHERE id = $productId 
  AND tenant_id = $tenantId;
```

### 11. **Create Inventory Adjustment Records**
```sql
-- Track inventory changes for auditing
INSERT INTO inventory_adjustments (
  tenant_id, product_id, adjustment_type, quantity_change,
  previous_stock, new_stock, reason, reference_id, adjusted_by
) VALUES (
  $tenantId, $productId, 'sale', -$quantity,
  $previousStock, $newStock, 'Sale transaction', $saleId, $userId
);
```

### 12. **Transaction Commit**
```sql
COMMIT;
```

### 13. **Response Generation**
```typescript
// Return complete sale object with items
return {
  id: sale.id,
  sale_number: sale.sale_number,
  total: sale.total,
  subtotal: sale.subtotal,
  tax_amount: sale.tax_amount,
  discount_amount: sale.discount_amount,
  payment_method: sale.payment_method,
  status: sale.status,
  items: sale.sale_items,
  created_at: sale.created_at
};
```

## Error Handling Scenarios

### 1. **Insufficient Stock**
```typescript
if (product.stock < requestedQuantity) {
  throw new ConflictException(
    `Insufficient stock for product ${product.name}. Available: ${product.stock}, Requested: ${requestedQuantity}`
  );
}
```

### 2. **Product Not Found**
```typescript
if (!product || product.tenant_id !== tenantId) {
  throw new NotFoundException(`Product not found or access denied`);
}
```

### 3. **Inactive Product**
```typescript
if (!product.is_active) {
  throw new BadRequestException(`Product ${product.name} is not active`);
}
```

### 4. **Database Transaction Failure**
```typescript
try {
  // Transaction operations
  await prisma.$transaction(async (tx) => {
    // All operations here
  });
} catch (error) {
  // Automatic rollback occurs
  throw new InternalServerErrorException('Sale processing failed');
}
```

### 5. **Concurrent Access Protection**
- Use `FOR UPDATE` locks on product records
- Handle deadlock scenarios with retry logic
- Validate stock again just before deduction

## Security Considerations

### 1. **Multi-Tenant Isolation**
- All queries include `tenant_id` filter
- JWT token tenant_id used (never from request body)
- Row-level security policies enforced

### 2. **Data Integrity**
- Foreign key constraints prevent invalid references
- Check constraints ensure positive quantities and prices
- Unique constraints on sale numbers per tenant

### 3. **Audit Trail**
- All inventory changes tracked in `inventory_adjustments`
- Timestamps on all records
- User attribution for all actions

## Performance Optimizations

### 1. **Database Indexes**
- Composite indexes on (tenant_id, created_at) for sales
- Product lookup optimized with (tenant_id, id) index
- Sale items indexed on sale_id and product_id

### 2. **Transaction Scope**
- Keep transactions as short as possible
- Lock only necessary rows
- Use appropriate isolation levels

### 3. **Bulk Operations**
- Process all sale items in single transaction
- Batch inventory updates where possible
- Efficient query planning with prepared statements

## Monitoring and Logging

### 1. **Key Metrics**
- Transaction success/failure rates
- Average transaction processing time
- Inventory discrepancy alerts

### 2. **Logging Requirements**
- Log all sale attempts (success and failure)
- Track inventory changes
- Monitor concurrent access patterns

### 3. **Alerting**
- Low stock notifications
- Failed transaction alerts
- Performance degradation warnings