docs/TRADE_RELATIONSHIPS_README.md

Trade Relationships Module - Implementation Guide

🎯 Overview

The Trade Relationship module defines who can transact with whom in the supply chain. It serves as the single source of truth for:

• ✅ Supplier eligibility for buying
• 💰 Commercial rules (pricing level, payment terms, credit)
• 🌍 Regional & category constraints
• 🔒 Transaction authorization (PO, Invoice, Returns, CN/DN)

Critical Rule: No Purchase Order, Invoice, Return, or Credit/Debit Note can be created unless an active trade relationship exists.

📁 Module Structure

app/trade_relationships/
├── __init__.py                     # Module initialization
├── constants.py                    # Enums, validation patterns, business rules
├── controllers/
│   ├── __init__.py
│   └── router.py                   # FastAPI endpoints
├── services/
│   ├── __init__.py
│   └── service.py                  # Business logic & CRUD operations
├── models/
│   ├── __init__.py
│   └── model.py                    # SQLAlchemy PostgreSQL model
└── schemas/
    ├── __init__.py
    └── schema.py                   # Pydantic request/response schemas

🗄️ Database Schema

Table: trans.scm_trade_relationship

The relationship is directional and explicit:

Buyer Merchant → Supplier Merchant

Key Fields:

• Parties: from_merchant_id (buyer) → to_merchant_id (supplier)
• Status: draft | active | suspended | expired | terminated
• Commercial: pricing_level, payment_terms, credit_allowed, credit_limit
• Constraints: allowed_regions[], allowed_categories[]
• Validity: valid_from, valid_to (NULL = indefinite)

Business Constraints:

• ✅ Unique Relationship: One relationship per merchant pair
• ✅ Different Merchants: Buyer ≠ Supplier
• ✅ Credit Logic: If credit allowed, limit must be > 0
• ✅ Date Logic: valid_to ≥ valid_from

🏗️ Setup Instructions

1. Run Database Migration

# From the project root directory
python migrate_trade_relationships.py

This will:

• ✅ Create the scm_trade_relationship table in trans schema
• 🔍 Add 6 performance indexes
• ✅ Apply 4 business rule constraints
• 📝 Add column documentation
• 🧪 Insert sample test data

2. Verify Installation

After running migration, restart your application and check:

# Check if endpoints are available
curl http://localhost:9101/docs

# Look for "trade-relationships" section in Swagger UI

3. Test Basic Functionality

# Health check
curl http://localhost:9101/trade-relationships/health

# List relationships (requires auth)
curl -H "Authorization: Bearer YOUR_TOKEN" \
     -X POST http://localhost:9101/trade-relationships/list \
     -H "Content-Type: application/json" \
     -d '{"skip": 0, "limit": 10}'

🔧 API Endpoints

Core CRUD Operations

Method	Endpoint	Description
POST	/trade-relationships	Create new relationship
GET	/trade-relationships/{id}	Get relationship details
PUT	/trade-relationships/{id}	Update relationship
DELETE	/trade-relationships/{id}	Delete relationship

Status Management

Method	Endpoint	Description
PUT	/trade-relationships/{id}/status	Change status (draft→active→suspended, etc.)

Supplier Discovery (Key Feature)

Method	Endpoint	Description
POST	/trade-relationships/suppliers/discover	Find suppliers for buyer (POST body)
GET	/trade-relationships/suppliers?buyer_merchant_id=X	Find suppliers (query params)

Relationship Validation

Method	Endpoint	Description
POST	/trade-relationships/validate	Validate buyer→supplier relationship
POST	/trade-relationships/list	List with filters and pagination

📋 Usage Examples

1. Create a Trade Relationship

POST /trade-relationships
{
  "from_merchant_id": "mch_ncnf_mumbai_001",
  "to_merchant_id": "mch_company_hq_001", 
  "pricing_level": "NCNF",
  "credit": {
    "allowed": true,
    "limit": 10000000,
    "payment_terms": "NET_30"
  },
  "allowed_regions": ["IN-MH-MUM", "IN-GJ-AHM"],
  "allowed_categories": ["Haircare", "Skincare"],
  "remarks": "Primary procurement relationship"
}

2. Discover Suppliers for Purchase Order

POST /trade-relationships/suppliers/discover
{
  "buyer_merchant_id": "mch_ncnf_mumbai_001",
  "region": "IN-MH-MUM",
  "category": "Haircare",
  "active_only": true
}

Response:

{
  "buyer_merchant_id": "mch_ncnf_mumbai_001",
  "suppliers": [
    {
      "merchant_id": "mch_company_hq_001",
      "relationship_id": "550e8400-e29b-41d4-a716-446655440000",
      "pricing_level": "NCNF",
      "payment_terms": "NET_30",
      "credit_allowed": true,
      "credit_limit": 10000000
    }
  ],
  "total_count": 1
}

3. Validate Relationship Before Transaction

POST /trade-relationships/validate
{
  "buyer_id": "mch_ncnf_mumbai_001",
  "supplier_id": "mch_company_hq_001",
  "region": "IN-MH-MUM",
  "category": "Haircare"
}

4. Change Relationship Status

PUT /trade-relationships/{id}/status
{
  "status": "suspended",
  "remarks": "Temporary suspension for compliance review"
}

🔒 Authorization Logic

The Authoritative Rule

Every transaction (PO, Invoice, Return, CN/DN) must validate:

-- This query MUST return a relationship for transaction to proceed
SELECT * FROM trans.scm_trade_relationship 
WHERE from_merchant_id = :buyer_id 
  AND to_merchant_id = :supplier_id
  AND status = 'active'
  AND valid_from <= CURRENT_DATE
  AND (valid_to IS NULL OR valid_to >= CURRENT_DATE)
  AND (allowed_regions IS NULL OR allowed_regions = ARRAY[] OR :region = ANY(allowed_regions))
  AND (allowed_categories IS NULL OR allowed_categories = ARRAY[] OR :category = ANY(allowed_categories));

Integration with Other Modules

Purchase Orders: Must validate relationship before PO creation

# In PO creation service
relationship = await TradeRelationshipService.validate_relationship(
    buyer_id=po_data.buyer_id,
    supplier_id=po_data.supplier_id,
    region=po_data.delivery_region,
    category=item.category
)
if not relationship:
    raise HTTPException(400, "No valid trade relationship")

Credit Validation: Check credit limits during invoice processing

if relationship.credit_allowed:
    if outstanding_amount + invoice_total > relationship.credit_limit:
        raise HTTPException(400, "Credit limit exceeded")
else:
    # Must be prepaid
    if payment_terms != "PREPAID":
        raise HTTPException(400, "Prepaid payment required")

📊 Business Rules

Status Lifecycle

Draft → Active → Suspended → Expired → Terminated
  ↓       ↓         ↓         ↓
  Active  Terminated Active   Terminated
  ↓       ↓         ↓
  Terminated      Terminated

Commercial Rules

• Credit Allowed = True: Credit limit required, allows NET_X payment terms
• Credit Allowed = False: Only PREPAID allowed, no credit limit
• Empty Regions/Categories: Unrestricted (can trade anywhere/anything)
• Populated Regions/Categories: Restricted to specified values

Operational Constraints

• Regions: Format IN-MH-MUM (Country-State-City)
• Categories: Format Haircare, Skincare (PascalCase)
• Validity: valid_to NULL means indefinite

🔍 Performance Features

Optimized Indexes

• Buyer Lookup: Fast supplier discovery
• Supplier Lookup: Sales-side queries
• Status Filtering: Active relationship queries
• Date Filtering: Validity checks
• Array Lookups: Region/category constraints (GIN indexes)

Caching Strategy

# Future enhancement: Redis caching for frequently accessed relationships
@cached(ttl=300)  # 5 minutes
async def get_cached_relationship(buyer_id: str, supplier_id: str):
    # Implementation for high-frequency validation

🧪 Testing

Sample Data Included

The migration creates 3 test relationships:

1. NCNF Mumbai → Company HQ (NET_30, ₹1Cr credit)
2. CNF Pune → NCNF Mumbai (NET_15, ₹50L credit)
3. Distributor Nashik → CNF Pune (PREPAID, no credit)

Test Scenarios

# Test supplier discovery
curl -X POST http://localhost:9101/trade-relationships/suppliers/discover \
  -H "Content-Type: application/json" \
  -d '{"buyer_merchant_id": "mch_ncnf_mumbai_001"}'

# Test relationship validation  
curl -X POST http://localhost:9101/trade-relationships/validate \
  -H "Content-Type: application/json" \
  -d '{"buyer_id": "mch_ncnf_mumbai_001", "supplier_id": "mch_company_hq_001"}'

🚨 Important Notes

Hard Guards (Non-Negotiable)

• ❌ No PO without relationship: All PO creation must validate first
• ❌ No Invoice without relationship: All invoicing must check
• ❌ No Returns without relationship: Return processing must verify
• ❌ No CN/DN without relationship: Credit/Debit notes must validate

Status Check Pattern

# Use this pattern in ALL transaction modules
if not relationship or not relationship.is_valid:
    raise HTTPException(
        status_code=400,
        detail="No valid trade relationship exists"
    )

Regional/Category Enforcement

# Validate constraints when specified
if relationship.allowed_regions and region not in relationship.allowed_regions:
    raise HTTPException(400, f"Region {region} not allowed")

if relationship.allowed_categories and category not in relationship.allowed_categories:
    raise HTTPException(400, f"Category {category} not allowed")

📈 Future Enhancements

Planned Features

1. Automatic Expiry: Background job to expire relationships
2. Credit Monitoring: Real-time outstanding balance tracking
3. Approval Workflows: Multi-step relationship approval
4. Bulk Operations: CSV import/export for relationship management
5. Integration Hooks: Webhooks for relationship status changes
6. Analytics Dashboard: Relationship performance metrics

Integration Roadmap

1. Phase 1: PO module integration (validate before PO creation)
2. Phase 2: Invoice module integration (credit limit enforcement)
3. Phase 3: Returns module integration (return authorization)
4. Phase 4: CN/DN module integration (credit/debit authorization)
5. Phase 5: Real-time credit monitoring and alerts

🆘 Troubleshooting

Common Issues

Issue: "Trade relationship not found" error

# Check if relationship exists and is active
SELECT * FROM trans.scm_trade_relationship 
WHERE from_merchant_id = 'buyer_id' 
  AND to_merchant_id = 'supplier_id';

Issue: "Credit limit exceeded" error

# Check current credit configuration
SELECT credit_allowed, credit_limit 
FROM trans.scm_trade_relationship 
WHERE relationship_id = 'relationship_id';

Issue: Foreign key errors during startup

# Ensure model is imported in sql.py
# Check that Base.metadata.create_all() includes the model

Support Contacts

• Database Issues: Check migration logs and database connectivity
• API Issues: Verify authentication and request format
• Business Logic: Review relationship status and validity dates

---

📝 Summary

The Trade Relationships module is now fully implemented and ready for integration:

✅ Complete CRUD API with validation and error handling
✅ PostgreSQL model with constraints and performance indexes
✅ Supplier discovery for PO creation workflows
✅ Relationship validation for transaction authorization
✅ Status management with proper lifecycle controls
✅ Migration script with sample data for testing
✅ Comprehensive documentation and usage examples

Next Step: Integrate relationship validation into PO, Invoice, Returns, and CN/DN modules to enforce the business rules.

The module follows all existing patterns and is ready for production use! 🎉