KBFC Data Integration Workflow

Debug Overview

Follow these steps if something looks wrong or data is missing:

  1. Check Sync Status (sync_status.html) to see if ArcGIS and MySQL record counts match.
  2. Look at the Import Log (arcgis_import.html) to confirm when the last ArcGIS → MySQL import ran and whether it had errors.
  3. Check the API Upload Log (raw_api_log.html) for errors when pushing data to the KBFC API.
  4. If records are missing from KBFC API but present in MySQL, it is likely an API upload issue.
  5. If records are missing in MySQL, verify ArcGIS data and rerun the import script.
  6. For persistent errors, review the raw logs or database to confirm whether the globalid matches ArcGIS records.
View Workflow Diagram Workflow Diagram

Overview

This system automatically transfers data from ESRI Cloud to the Local KBFC database and then uploads it to the KBFC API.

The process runs in steps: fetching new records, saving them in the database, checking for differences, and logging results.

1. ESRI Cloud Data Fetching

Goal: Retrieve updated records from ESRI Cloud Feature Services.

  1. fetch_arcgis_data() queries ESRI Cloud layers using REST API with authentication token.
  2. Each configured ArcGIS layer in config.yml has survey, table_index, layer_url, and table_name.
  3. Data is retrieved in paged requests (default 1,000 records per page).
  4. Supports full sync (1=1) or incremental sync (EditDate >= cutoff).

2. Data Transformation (Mapping)

Goal: Map ESRI Cloud fields to MySQL table fields and normalize values.

  1. Each MySQL table has a model file in arcgis_map_models/:
    • model() → defines table fields.
    • map(row) → transforms ESRI Cloud record into MySQL-ready dict.
  2. Utility functions (safe_str, safe_int, convert_to_api_iso_datetime) ensure consistent data formats.

3. MySQL Insert/Update

Goal: Sync ESRI Cloud data to MySQL tables.

  1. insert_records() checks if a record exists in MySQL by globalid.
  2. If exists → UPDATE.
  3. If not exists → INSERT.

4. Audit & Sync Check

Goal: Verify MySQL table matches ESRI Cloud source.

  1. audit_sync_status() compares record counts and finds missing/orphaned records.
  2. Results are stored in audit_summaries.

5. Orphan Cleanup (Optional)

Goal: Remove MySQL rows that no longer exist in ESRI Cloud.

  1. cleanup_orphans.py checks all tables for records in MySQL but not in ESRI Cloud.
  2. Deletes orphan records based on globalid.

6. Logging & HTML Generation

Goal: Provide visibility of sync operations.

  1. Logs are written to logs/main.log.
  2. convert_log_to_html() → Converts ANSI logs to HTML (logs/arcgis_import.html).
  3. generate_import_summary_html() → Creates summary page showing last sync time, error count, sync status.
  4. generate_log_summary() → Generates combined HTML summary for Local KBFC DB → Collaborative KBFC DB uploads.

7. API Upload (Local KBFC DB → Collaborative KBFC DB)

Goal: Push MySQL data to KBFC API.

  1. Separate scripts call API endpoints (e.g., /mrrcaptures, /eventlog, /effort).
  2. Responses are logged in the event_log table in SQLite.

8. Status Dashboard

Goal: Provide a single entry point for monitoring.