Skip to main content

Data Models & Schema Reference

The pricing engine extends the core database schema with seven new models. These structures enforce strict multi-tenant constraints, chronological auditability, and mathematical reproducibility.

📊 Entity Relationship Diagram​

🗃️ Indexing & Performance Strategy​

To ensure zero latency overhead on high-frequency transactions, the following indices have been implemented:

  1. PricingRegion:

    • [regionGroup]: Optimized for fast region groupings.
    • [countryCode]: Ensures immediate country resolution.
    • [isActive]: Excludes inactive locations from initial loading scans.

  2. PricingRate:

    • [category, isActive]: Speeds up retrieval of active rates during base estimation.
    • [effectiveFrom, effectiveTo]: Filters out stale rate schedules.

  3. PlatformFeeRule:

    • [vendorId]: Instantly resolves enterprise overrides.
    • [isDefault, isActive]: Falls back efficiently to global rules.

  4. Quotation:

    • [vendorId, status]: Optimizes dashboard workspace listings.
    • [validUntil]: Used by the daily 3 AM cron cleanup query (status: 'ISSUED' AND validUntil < NOW) to eliminate lock contention on processing tables.