docs/database/FOREIGN_KEY_FIXES_SUMMARY.md

Foreign Key Fixes Summary

Issue Resolved

Error: Foreign key associated with column 'scm_trade_shipment.order_id' could not find table 'scm_po' with which to generate a foreign key to target column 'po_id'

Root Cause:

1. Multiple Base instances across different model files
2. Foreign key references not including schema prefixes
3. Tables in different schemas causing reference issues

Solutions Applied

1. Created Shared Database Base

File: app/core/database.py

from sqlalchemy.orm import declarative_base

# Shared Base for all SQLAlchemy models
Base = declarative_base()

2. Updated All Models to Use Shared Base

Before:

# Each model file had its own Base
from sqlalchemy.orm import declarative_base
Base = declarative_base()

After:

# All models now use shared Base
from app.core.database import Base

Files Updated:

• app/catalogues/models/model.py
• app/inventory/stock/models/model.py
• app/purchases/orders/models/model.py
• app/purchases/receipts/models/model.py
• app/trade_sales/models/model.py

3. Fixed Foreign Key Schema References

Before:

ForeignKey('scm_po.po_id')
ForeignKey('scm_grn.grn_id')

After:

ForeignKey('trans.scm_po.po_id')
ForeignKey('trans.scm_grn.grn_id')

Foreign Keys Fixed:

app/trade_sales/models/model.py:

• order_id: 'scm_po.po_id' → 'trans.scm_po.po_id'
• shipment_id: 'scm_trade_shipment.shipment_id' → 'trans.scm_trade_shipment.shipment_id'
• po_item_id: 'scm_po_item.po_item_id' → 'trans.scm_po_item.po_item_id'

app/inventory/stock/models/model.py:

• ledger_id: 'scm_stock_ledger.ledger_id' → 'trans.scm_stock_ledger.ledger_id'
• adjustment_id: 'scm_stock_adjustment.adjustment_id' → 'trans.scm_stock_adjustment.adjustment_id'

app/purchases/orders/models/model.py:

• po_id: 'scm_po.po_id' → 'trans.scm_po.po_id'

app/purchases/receipts/models/model.py:

• po_id: 'scm_po.po_id' → 'trans.scm_po.po_id'
• grn_id: 'scm_grn.grn_id' → 'trans.scm_grn.grn_id'
• po_item_id: 'scm_po_item.po_item_id' → 'trans.scm_po_item.po_item_id'
• grn_item_id: 'scm_grn_item.grn_item_id' → 'trans.scm_grn_item.grn_item_id'

4. Updated Table Creation Logic

File: app/sql.py

Before:

from app.purchases.orders.models.model import Base

After:

from app.core.database import Base

# Import all models to ensure they're registered with Base
from app.catalogues.models.model import CatalogueRef
from app.inventory.stock.models.model import ScmStock, ScmStockLedger, ScmStockAdjustment, ScmStockTake
from app.purchases.orders.models.model import ScmPo, ScmPoItem, ScmPoStatusLog
from app.purchases.receipts.models.model import ScmGrn, ScmGrnItem, ScmGrnIssue
from app.trade_sales.models.model import ScmTradeShipment, ScmTradeShipmentItem

Database Configuration

Current Setup:

• Database: cuatrolabs
• Schema: trans (for all SCM tables)
• Shared Base: All models use app.core.database.Base

Tables in trans Schema:

1. catalogue_ref - Catalogue reference with pricing_levels
2. scm_stock - Stock snapshots
3. scm_stock_ledger - Stock movement ledger
4. scm_stock_adjustment - Stock adjustments
5. scm_stock_take - Physical stock audits
6. scm_po - Purchase orders
7. scm_po_item - Purchase order items
8. scm_po_status_log - PO status changes
9. scm_grn - Goods receipt notes
10. scm_grn_item - GRN items
11. scm_grn_issue - GRN issues/discrepancies
12. scm_trade_shipment - Trade shipments
13. scm_trade_shipment_item - Trade shipment items

Verification Results

✅ All Tests Passed:

1. Model Import: All models import successfully with shared Base
2. Foreign Keys: All foreign key relationships resolve correctly
3. Table Creation: create_tables() executes without errors
4. Application Startup: FastAPI app starts without foreign key errors
5. Schema Consistency: All tables use trans schema consistently

✅ Foreign Key Relationships Working:

• Trade shipments → Purchase orders
• Trade shipment items → PO items
• GRN items → Purchase orders
• Stock adjustments → Stock ledger
• All cross-table relationships functional

Benefits Achieved

1. Eliminated Foreign Key Errors: All table relationships work correctly
2. Schema Consistency: All SCM tables in trans schema
3. Maintainable Code: Single shared Base for all models
4. Database Integrity: Proper foreign key constraints enforced
5. Application Stability: No startup errors related to table relationships

Future Maintenance

When Adding New Models:

1. Always import from app.core.database import Base
2. Include schema in __table_args__ = {'schema': 'trans'}
3. Use full schema.table format in foreign keys: 'trans.table_name.column'
4. Add model imports to app/sql.py create_tables function

Schema Migration Best Practices:

1. Use consistent schema across all related tables
2. Always specify schema explicitly in models
3. Include schema in foreign key references
4. Test foreign key relationships after schema changes