Skip to main content

MCP Tool: codeshare_sync_audit

Overview​

codeshare_sync_audit is a BigQuery MCP tool that performs a daily 1-to-1 mirror audit between an operating carrier's own-metal schedule and a specific codeshare partner's marketed schedule. It detects schedule discrepancies that require a partner change.

The audit flags one of three actions for each future flight date:

ActionMeaning
ADDOperating flight exists, but partner has no matching marketing record
DELETEPartner markets a flight with no operating record
UPDATEBoth records exist, but attributes differ

Example Prompts​

Run the codeshare sync audit for partner AF and operating carrier DL, flights 1 to 100.
Run the codeshare sync audit for all DL flights with partner AZ.
Check if BT's marketed flights match KM's operating schedule for flights 2800-2849.

Background​

Codeshare synchronisation is validated in both directions:

DirectionTableDEIPurpose
Operating carrier → partnerssim_4 on the operating record10, 50Link operating flight to partner marketing flight
Partner → operating carrierssim_4 on the partner record50, 127Link marketing flight back to operating flight

Both sides must be audited to catch ghost marketing records and missing partner links.

Key SQL invariants​

  • Whitespace-agnostic regex — handles variable DEI padding like DL 1 vs DL 1
  • INT64 casting — flight numbers are cast before comparison
  • COALESCE NULL date handling — open-ended schedules use the dataset maximum date
  • UTC normalisation — UTC offsets are compared as hours after dividing by 100
  • Future-only filter — only dates on or after CURRENT_DATE() are audited
  • Service type = J only — excludes surface and helicopter equipment

Parameters​

ParameterTypeRequiredDefaultDescription
dataset_idstring (template)Yes—Published SSIM dataset id (e.g. ssim_published_2026w21)
operating_carrierstringYes—IATA code of the operating carrier
flight_range_startintegerYes1Lower bound of operating flight number range
flight_range_endintegerYes9999Upper bound of operating flight number range
partner_carrierstringYes—IATA code of the marketing partner carrier

Flight range defaults​

Intentflight_range_startflight_range_end
All flights19999
Explicit rangespecifiedspecified
Start onlyspecified9999
End only1specified

operating_carrier and partner_carrier must always be provided.

Operating side

  • ssim_3 filtered by flight_airline_designator = operating_carrier
  • flight_number BETWEEN flight_range_start AND flight_range_end
  • service_type = 'J', operating_airline_disclosure IS NULL
  • join ssim_4 on data_element_identifier IN (10, 50)
  • filter DEI text for the partner carrier
  • extract and cast linked partner flight number to INT64

Partner side

  • ssim_3 filtered by flight_airline_designator = partner_carrier
  • service_type = 'J', operating_airline_disclosure IN ('L', 'S', 'Z')
  • join ssim_4 on data_element_identifier IN (50, 127)
  • filter DEI text for the operating carrier
  • extract and cast linked operating flight number to INT64

Output Columns​

Results are pattern-grouped: all consecutive dates requiring the same fix are collapsed into a single row.

ColumnDescription
MKT_CARRIERPartner carrier IATA code
MKT_FLTPartner marketing flight number
OP_FLTOperating carrier flight number
DEPDeparture station IATA code
ARRArrival station IATA code
SCHEDULER_ACTIONADD, DELETE, or UPDATE
MISMATCH_DETAILPipe-delimited list of differing attributes (UPDATE only)
START_DATEFirst date in this discrepancy block
END_DATELast date in this discrepancy block
TOTAL_DAYSNumber of dates in this block
OP_DEP_LOCALOperating carrier local departure time (HHMM integer)
OP_ARR_LOCALOperating carrier local arrival time (HHMM integer)
OP_UTC_DEPOperating carrier UTC departure offset (2-digit hours)
OP_UTC_ARROperating carrier UTC arrival offset (2-digit hours)
OP_EQUIPOperating carrier aircraft type code
OP_MEALSOperating carrier meal service code
OP_DEP_TERMOperating carrier departure terminal
OP_ARR_TERMOperating carrier arrival terminal

All headers are uppercase.

Row limit: 20 000 rows (source: bigquery-source-override-rows). The SQL imposes no additional LIMIT clause. If the result exceeds 50 rows, the analytics agent saves the data as a session artifact and passes it to the artifact agent before CSV export.

Agent Routing​

The root agent routes this tool to analytics_agent.

Trigger phrases:

  • "run the codeshare sync audit for [partner] and [operating carrier]"
  • "daily mirror audit for [carrier] flights [range] with partner [partner]"
  • "audit all [carrier] flights with partner [partner]"
  • "check if [partner]'s marketed flights match [carrier]'s operating schedule"
  • "find discrepancies between [carrier] operating flights and [partner] marketing"
  • "what needs to be added, deleted or updated in [partner]'s schedule for [carrier]?"
  • "run the schedule synchronisation audit for [carrier] / [partner]"

Large result handling​

codeshare_sync_audit is registered in the analytics agent's tools_to_csv list. After every call:

  • The result is saved as a session artifact named codeshare_sync_audit.csv.
  • If the result has ≤ 100 rows, the CSV data is returned directly.
  • If the result has > 100 rows, the callback returns only the row count and next-action prompt. The agent then follows post-results rules for CODESHARE_SYNC_AUDIT.

Direct Execution — No Confirmation Required​

The analytics agent calls this tool as soon as operating_carrier and partner_carrier are known. There is no confirmation round-trip.

Execution sequence:

  1. Call bigquery_list_dataset_ids to resolve dataset_id.
  2. Apply flight range defaults if no range was specified.
  3. Call codeshare_sync_audit immediately with all resolved parameters.

This replaces the previous mandatory two-turn handshake.

Dataset Resolution​

The current schedule dataset is the last entry when all dataset ids are sorted alphabetically (year takes priority over week number):

ssim_published_2025w52  ← previous
ssim_published_2026w21  ← current  ✓

Do not subtract 1 from the week number at year boundaries. 2026w01 - 1 ≠ 2026w00.

Example Prompts & Expected Tool Calls​

1. Audit AF-marketed flights for DL operating range 1–100​

User prompt:

Run the codeshare sync audit for partner AF, operating carrier DL, flights 1 to 100.

Agent calls immediately:

dataset_id          = ssim_published_2026w21
operating_carrier   = DL
flight_range_start  = 1
flight_range_end    = 100
partner_carrier     = AF

2. Audit all flights (no range specified)​

User prompt:

Run the codeshare sync audit for all DL flights with partner AF.

Agent calls immediately:

dataset_id          = ssim_published_2026w21
operating_carrier   = DL
flight_range_start  = 1
flight_range_end    = 9999
partner_carrier     = AF

3. Multi-carrier session — iterate through partners​

User prompt:

I want to run the daily mirror audit for all KM codeshare partners.
Start with AZ for KM operating flights 2200–2299.

Agent flow:

  1. Run audit for operating_carrier=KM, partner_carrier=AZ, flight_range_start=2200, flight_range_end=2299.
  2. After CSV export, ask whether to proceed to the next range or the next partner.
  3. Continue with additional partner ranges.

4. Single discrepancy check​

User prompt:

Find discrepancies between KM operating flights 2300-2399 and LH marketing.

Agent calls immediately:

dataset_id          = ssim_published_2026w21
operating_carrier   = KM
flight_range_start  = 2300
flight_range_end    = 2399
partner_carrier     = LH

5. Schedule synchronisation check​

User prompt:

Check if BT's marketed flights match KM's operating schedule for flights 2800-2849.

Agent calls immediately:

dataset_id          = ssim_published_2026w21
operating_carrier   = KM
flight_range_start  = 2800
flight_range_end    = 2849
partner_carrier     = BT

Chat Display Rules​

  • If the audit returns ≤ 10 discrepancy blocks, display all rows as a markdown table with uppercase headers.
  • If the audit returns > 10 blocks, show only the first 10 and say: "Showing first 10 of [Total] discrepancy blocks. Please export to CSV for the full report."
  • After displaying results, ask: "Would you like to export these results to [partner_carrier]_[range_start]-[range_end]_Audit.csv?"
  • If the user confirms export and the row count exceeds 50:
    • Transfer to artifact_agent with explicit instructions to call load_filter_artifact_data for rows 1 to total.
    • Do not ask artifact_agent for filter criteria.
    • Then transfer to csv_agent.
  • If the row count is ≤ 50, transfer directly to csv_agent.

SQL Design Notes​

The query uses five CTEs:

  • op_truth — operating carrier flights with confirmed DEI links to the partner.
  • op_expanded — expands operating flights into daily dates with null-date guards and day-of-week filtering.
  • mkt_reality — partner marketing flights whose DEI links point to the operating carrier.
  • mkt_expanded — expands marketing flights into daily dates with the same guards.
  • audit_join — full outer join on operating flight, partner flight, route, and date.

The final SELECT collapses consecutive matching dates into a single pattern block and filters SCHEDULER_ACTION != 'MATCH'. No SQL-level LIMIT is applied; row capping is handled by bigquery-source-override-rows.

Relationship to Other Tools​

ToolUse case
codeshare_sync_auditDaily bi-directional mirror audit between one operating carrier flight range and one partner carrier
codeshare_analysisCodeshare classification and operating carrier resolution at a station
hub_activity_extract_reportFull hub schedule extract for a single date
execute_sql_toolAd-hoc SQL for cases not covered by custom tools