VATSWIM (System Wide Information Management)

Design Document v1.3

Document Status: PHASE 4 COMPLETE (Data Isolation) Version: 2.0 Date: 2026-03-14
Author: vATCSCC Development Team
Classification: Public

Table of Contents

1. Executive Summary
2. Vision & Objectives
3. Architecture Overview
4. Infrastructure & Cost
5. Unified Flight Record
6. API Design
7. Security & Authentication
8. Implementation Status
9. Implementation Roadmap
10. Appendices

1. Executive Summary

1.1 Purpose

VATSWIM (System Wide Information Management) is a centralized data exchange hub enabling real-time flight information sharing across the VATSIM ecosystem. It provides a single source of truth for flight data, enabling consistent Traffic Management Initiative (TMI) implementation, synchronized arrival/departure times, and seamless data exchange between all VATSIM systems.

1.2 Problem Statement

Currently, VATSIM systems operate in silos:

• vATCSCC/PERTI calculates demand, manages TMIs, tracks OOOI times
• vNAS manages ATC automation, tracks, clearances, handoffs
• CRC/EuroScope display flight data but lack TMI awareness
• SimTraffic computes metering times independently
• Pilot clients have no visibility into TMI status
• Virtual airlines cannot access real-time flight tracking

1.3 Solution

SWIM provides:

• Unified Flight Record: Single document per flight with data from all sources
• Real-Time Distribution: Sub-second updates via WebSocket/Event streaming
• Authoritative Data Model: Clear ownership rules for each data domain
• Open API: Enabling innovation across the VATSIM ecosystem
• Cost-Optimized Infrastructure: Full data isolation — all API endpoints query only the dedicated SWIM_API database; sync daemons keep data fresh from internal databases

2. Vision & Objectives

2.1 Vision Statement

"Enable every VATSIM system to access consistent, real-time flight information, creating a seamlessly integrated virtual airspace where TMIs are automatically applied, metering is coordinated, and all participants share common situational awareness."

2.2 Strategic Objectives

1. Unified Data: Single authoritative record for each flight
2. Real-Time Sync: All systems see the same data simultaneously
3. TMI Consistency: Ground stops and delays applied uniformly
4. Open Ecosystem: Enable third-party innovation
5. Cost Efficiency: Dedicated infrastructure prevents API load from impacting internal systems

3. Architecture Overview

3.1 High-Level Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│                           INTERNAL SYSTEMS                                  │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                                                                     │   │
│  │   ┌─────────────┐     ┌─────────────────────────────────────┐      │   │
│  │   │   VATSIM    │     │        VATSIM_ADL (Azure SQL)       │      │   │
│  │   │    API      │────▶│           Serverless                │      │   │
│  │   └─────────────┘     │   (Internal use only - expensive)   │      │   │
│  │                       └──────────────────┬──────────────────┘      │   │
│  │                                          │                         │   │
│  │   ┌─────────────┐                        │ (sync every 15 sec)     │   │
│  │   │   MySQL     │                        │                         │   │
│  │   │  (PERTI)    │                        ▼                         │   │
│  │   └──────┬──────┘     ┌──────────────────────────────────────┐    │   │
│  │          │            │     SWIM_API Database (Azure SQL)    │    │   │
│  │          │            │        Basic Tier ($5/month)         │    │   │
│  │          └───────────▶│    (Dedicated for public API)        │    │   │
│  │                       └──────────────────┬───────────────────┘    │   │
│  │                                          │                         │   │
│  └──────────────────────────────────────────┼─────────────────────────┘   │
│                                             │                              │
└─────────────────────────────────────────────┼──────────────────────────────┘
                                              │
                                              ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                           PUBLIC SWIM API                                   │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                                                                     │   │
│  │   ┌─────────────────┐    ┌─────────────────┐                       │   │
│  │   │  REST API       │    │  WebSocket      │                       │   │
│  │   │  /api/swim/v1/  │    │  /api/swim/v1/  │                       │   │
│  │   │                 │    │  stream         │                       │   │
│  │   └────────┬────────┘    └────────┬────────┘                       │   │
│  │            │                      │                                 │   │
│  │            └──────────┬───────────┘                                │   │
│  │                       │                                             │   │
│  │                       ▼                                             │   │
│  │   ┌─────────────────────────────────────────────────────────────┐  │   │
│  │   │                    SUBSCRIBERS                              │  │   │
│  │   │  CRC │ EuroScope │ vPilot │ SimAware │ VAs │ Analytics     │  │   │
│  │   └─────────────────────────────────────────────────────────────┘  │   │
│  │                                                                     │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

v2.0 Update (March 2026): Full data isolation achieved. All SWIM API endpoints now query exclusively from SWIM_API. The database contains 25+ mirror tables synced from VATSIM_TMI, VATSIM_ADL, VATSIM_REF, and MySQL by three sync daemons (swim_sync_daemon.php every 2min, swim_tmi_sync_daemon.php every 5min, refdata_sync_daemon.php daily). The sp_Swim_BulkUpsert procedure uses row-hash comparison to skip unchanged rows, reducing write amplification on the 5 DTU Basic tier. A swim_change_feed table provides monotonic event replay for multi-consumer streaming.

3.2 Key Design Principle: Database Isolation

CRITICAL: The public SWIM API must NEVER query VATSIM_ADL directly.

Why?

• VATSIM_ADL Serverless charges per vCore-second of usage
• External API traffic could cost $500-7,500/month if querying directly
• Dedicated Basic tier has fixed $5/month cost regardless of query volume
• Isolates public load from internal processing

3.3 Data Flow

1. VATSIM API → VATSIM_ADL (every 15 sec via sp_Adl_RefreshFromVatsim_Normalized)
2. VATSIM_ADL → SWIM_API (every 15 sec via sp_Swim_SyncFromAdl)
3. MySQL → SWIM_API (every 15 sec for ground stops)
4. SWIM_API → Public REST/WebSocket API

4. Infrastructure & Cost

4.1 Target Monthly Cost: ~$21-47/month

4.2 SWIM_API Database Schema

The dedicated SWIM_API database contains denormalized, read-optimized tables:

-- Core flight data (synced from VATSIM_ADL normalized tables)
CREATE TABLE swim_flights (
    flight_uid BIGINT PRIMARY KEY,
    flight_key NVARCHAR(64),
    gufi NVARCHAR(64),
    callsign NVARCHAR(16),
    cid INT,

    -- Position
    lat DECIMAL(9,6),
    lon DECIMAL(10,6),
    altitude_ft INT,
    heading_deg SMALLINT,
    groundspeed_kts INT,

    -- Flight Plan
    fp_dept_icao CHAR(4),
    fp_dest_icao CHAR(4),
    fp_altitude_ft INT,
    fp_route NVARCHAR(MAX),
    fp_dept_artcc NVARCHAR(8),
    fp_dest_artcc NVARCHAR(8),

    -- Progress
    phase NVARCHAR(16),
    is_active BIT,
    dist_to_dest_nm DECIMAL(10,2),
    pct_complete DECIMAL(5,2),

    -- Times
    eta_utc DATETIME2,
    out_utc DATETIME2,
    off_utc DATETIME2,
    on_utc DATETIME2,
    in_utc DATETIME2,

    -- TMI
    gs_held BIT,
    ctl_type NVARCHAR(8),
    ctl_prgm NVARCHAR(32),
    slot_time_utc DATETIME2,
    delay_minutes INT,

    -- Aircraft
    aircraft_type NVARCHAR(8),
    weight_class NCHAR(1),
    airline_icao NVARCHAR(4),

    -- Metadata
    last_sync_utc DATETIME2 DEFAULT GETUTCDATE(),

    INDEX IX_swim_flights_active (is_active, callsign),
    INDEX IX_swim_flights_dept (fp_dept_icao),
    INDEX IX_swim_flights_dest (fp_dest_icao),
    INDEX IX_swim_flights_artcc (fp_dest_artcc)
);

-- API keys (can stay in SWIM_API or VATSIM_ADL)
CREATE TABLE swim_api_keys (...);

-- Audit log
CREATE TABLE swim_audit_log (...);

4.3 Sync Implementation

Note: Azure SQL Basic tier doesn't support cross-database queries, so sync is implemented via PHP rather than a cross-database stored procedure.

Sync Flow:

1. ADL daemon refreshes VATSIM_ADL (every 15 seconds)
2. Every 8th cycle (2 minutes), daemon triggers SWIM sync
3. PHP reads from VATSIM_ADL normalized tables
4. PHP encodes ~2,000 flights as JSON (~3MB)
5. PHP calls sp_Swim_BulkUpsert on SWIM_API
6. SP parses JSON, performs MERGE, returns stats

Files:

• scripts/swim_sync.php - PHP sync logic (V2 with batch SP)
• scripts/vatsim_adl_daemon.php - Integration point
• database/migrations/swim/004_swim_bulk_upsert_sp.sql - Batch upsert SP

sp_Swim_BulkUpsert (SWIM_API):

CREATE PROCEDURE dbo.sp_Swim_BulkUpsert @Json NVARCHAR(MAX)
AS
BEGIN
    -- Parse JSON into temp table
    SELECT ... INTO #flights FROM OPENJSON(@Json);

    -- MERGE: Insert new, update existing
    MERGE dbo.swim_flights AS target
    USING #flights AS source ON target.flight_uid = source.flight_uid
    WHEN MATCHED THEN UPDATE SET ...
    WHEN NOT MATCHED THEN INSERT ...;

    -- Delete stale flights (inactive >2 hours)
    DELETE FROM dbo.swim_flights 
    WHERE is_active = 0 AND last_sync_utc < DATEADD(HOUR, -2, SYSUTCDATETIME());

    -- Return stats
    SELECT @inserted AS inserted, @updated AS updated, @deleted AS deleted;
END;

4.4 Cost Comparison

5. Unified Flight Record

5.1 GUFI (Globally Unique Flight Identifier)

Format: VAT-YYYYMMDD-CALLSIGN-DEPT-DEST

Example: VAT-20260115-UAL123-KJFK-KLAX

5.2 Data Authority Matrix

6. API Design

6.1 Base URL

Production: https://perti.vatcscc.org/api/swim/v1

6.2 Authentication

Authorization: Bearer {api_key}

6.3 API Key Tiers

6.4 Endpoints

7. Security & Authentication

7.1 Authentication Flow

1. Client sends request with Authorization: Bearer {api_key} header
2. auth.php middleware validates key format and tier
3. Key lookup in swim_api_keys table
4. Rate limit check via APCu cache
5. Access logged to swim_audit_log

7.2 CORS Configuration

Allowed origins:

• https://perti.vatcscc.org
• https://vatcscc.org
• https://swim.vatcscc.org
• http://localhost:* (development)

8. Implementation Status

8.1 Current State (v1.3 - Infrastructure Complete)

✅ Infrastructure migration complete. SWIM_API database deployed with dedicated sync from ADL daemon.

8.2 Sync Performance

8.3 Completed Components

9. Implementation Roadmap

Phase 0: Infrastructure ✅ COMPLETE

Phase 1: Foundation ✅ COMPLETE

Phase 2: Real-Time Distribution (Weeks 5-8)

Phase 3: Partner Integrations (Weeks 9-12)

10. Appendices

10.1 Glossary

10.2 File Structure

VATSIM PERTI/PERTI/
├── api/swim/v1/
│   ├── auth.php              # Authentication middleware
│   ├── index.php             # API router
│   ├── flights.php           # Flights endpoint
│   ├── flight.php            # Single flight endpoint
│   ├── positions.php         # GeoJSON positions
│   ├── ingest/
│   │   └── adl.php           # ADL ingest
│   └── tmi/
│       ├── programs.php      # TMI programs
│       └── controlled.php    # TMI controlled flights
├── database/migrations/swim/
│   ├── 001_swim_tables.sql   # API keys, audit tables
│   └── 002_swim_api_database.sql  # Dedicated SWIM_API schema
├── docs/swim/
│   ├── README.md             # Overview
│   ├── VATSIM_SWIM_Design_Document_v1.md
│   └── SWIM_TODO.md          # Implementation tracker
└── load/
    └── swim_config.php       # Configuration

10.3 Related Documents

• SWIM README - Quick start guide
• SWIM TODO - Implementation tracker
• Normalized Schema Reference - Source data schema

Contact: dev@vatcscc.org
Repository: VATSIM PERTI/PERTI

End of Document