Intermodal Container Demurrage and Detention SQL Engine Specification

Role: Principal Supply Chain Database Architect specialising in intermodal billing engines and transactional state machine SQL.

Context

• Operator: {{freight_operator_name}}
• SQL Engine: {{sql_engine_version}}
• Lifecycle Milestones: {{container_event_lifecycle_states}}
• Calendar Logic: {{free_time_calendar_rule_type}}
• Tariff Escalation: {{tariff_tier_structure}}
• Compliance / Audit: {{audit_trail_requirements}}

Task

Draft a rigorous technical specification for an auditable SQL calculation engine that models container state machines, evaluates complex free-time calendar exceptions, and computes tiered demurrage and detention liabilities for intermodal cargo movements.

Method

1. Define the state-machine transition table mapping all valid status sequences across {{container_event_lifecycle_states}}.
2. Structure a calendar dimension join mechanism to dynamically exclude terminal non-working days under {{free_time_calendar_rule_type}}.
3. Write recursive or window-based SQL logic to isolate clock-start and clock-stop events for demurrage (terminal) and detention (equipment).
4. Implement the stepped rate matrix calculation matching {{tariff_tier_structure}} using conditional mathematical aggregation.
5. Build exception-handling CTEs that flag orphan events, retroactively adjusted gate times, and disputed hold periods.
6. Architect line-item audit trail views detailing daily accrued charges, active tariff tier IDs, and rule-trigger logs to fulfill {{audit_trail_requirements}}.
7. Formulate index and execution plan optimization strategies for high-throughput reconciliation batch runs.

Constraints

• MUST calculate day boundaries using deterministic local terminal timestamps rather than client application clocks.
• MUST NOT evaluate tariff charges on dates covered by approved terminal equipment holds or customs inspections.
• SQL syntax must be fully validated against {{sql_engine_version}} standards.
• Every charge calculation line must maintain backward lineage to the specific raw event ID that triggered the charge.

Output format

• 1. State Machine & Event Table Schema (DDL & Constraint Definitions)
• 2. Calendar Logic & Free Time Engine SQL Models
• 3. Tiered Tariff Calculation SQL Stored Procedure or View Pipeline
• 4. Dispute Audit Logging Specification & Reconciliation Test Suite Deliverable length should fall between 850 and 1250 words.

Self-review

• Verify that the rate calculation accurately steps through all tiers specified in {{tariff_tier_structure}}.
• Confirm that non-working day exclusions align with {{free_time_calendar_rule_type}} without dropping valid milestone dates.
• Ensure the audit trail structure strictly satisfies {{audit_trail_requirements}}.