🗄️ Database Architecture

Comprehensive documentation of the SkySpy database schema, including all models, relationships, indexes, and performance considerations.

📑 Quick Navigation

📊 Overview

SkySpy uses PostgreSQL as its primary database engine for production deployments, with SQLite supported for development and lightweight installations. The database schema is managed through Django's ORM and migrations system.

Key Characteristics

Database Configuration

# Production (PostgreSQL)
DATABASE_URL=postgresql://user:password@host:5432/adsb

# Development (SQLite)
DATABASE_URL=sqlite:///db.sqlite3

🔗 Entity Relationship Diagram

erDiagram
    %% ═══════════════════════════════════════════════════
    %% CORE AIRCRAFT TRACKING
    %% ═══════════════════════════════════════════════════
    AircraftSighting ||--o{ AircraftSession : "belongs to"
    AircraftSession ||--o| SessionTrackingQuality : "has quality metrics"
    AircraftSession ||--o{ PersonalRecord : "sets"
    AircraftSession ||--o{ RareSighting : "triggers"

    AircraftInfo ||--o{ AirframeSourceData : "has source data"

    %% ═══════════════════════════════════════════════════
    %% USER & AUTHENTICATION
    %% ═══════════════════════════════════════════════════
    User ||--o| SkyspyUser : "has profile"
    User ||--o{ UserRole : "has roles"
    Role ||--o{ UserRole : "assigned to"
    Role ||--o{ OIDCClaimMapping : "mapped from"
    User ||--o{ APIKey : "owns"
    User ||--o{ AircraftFavorite : "favorites"

    %% ═══════════════════════════════════════════════════
    %% ALERTS & NOTIFICATIONS
    %% ═══════════════════════════════════════════════════
    AlertRule ||--o{ AlertHistory : "triggers"
    AlertRule ||--o{ AlertSubscription : "has subscribers"
    AlertRule ||--o{ AlertAggregate : "aggregates"
    AlertRule }o--o{ NotificationChannel : "sends to"
    User ||--o{ AlertRule : "owns"

    NotificationChannel ||--o{ NotificationLog : "logs"
    NotificationChannel ||--o{ UserNotificationPreference : "preferences"

    %% ═══════════════════════════════════════════════════
    %% SAFETY & EVENTS
    %% ═══════════════════════════════════════════════════
    SafetyEvent }o--|| AircraftSession : "involves"

    %% ═══════════════════════════════════════════════════
    %% CANNONBALL MODE
    %% ═══════════════════════════════════════════════════
    CannonballSession ||--o{ CannonballPattern : "detects"
    CannonballSession ||--o{ CannonballAlert : "generates"
    CannonballKnownAircraft ||--o{ CannonballSession : "triggers"

    %% ═══════════════════════════════════════════════════
    %% AVIATION DATA
    %% ═══════════════════════════════════════════════════
    CachedAirport }o--o{ CachedPirep : "reports near"
    AirspaceAdvisory }o--o{ AirspaceBoundary : "within"

📁 Domain Model Groups

The database models are organized into the following domain groups:

📖 Model Reference

✈️ Aircraft Tracking

graph LR
    subgraph "Index Strategy"
        A["idx_sightings_icao_time"] --> B["(icao_hex, timestamp)"]
        C["idx_sighting_timestamp_desc"] --> D["timestamp DESC"]
        E["idx_sighting_distance"] --> F["distance_nm WHERE NOT NULL"]
        G["idx_sighting_military"] --> H["is_military WHERE TRUE"]
        I["idx_sighting_rssi"] --> J["rssi WHERE NOT NULL"]
    end

🔐 Authentication & Authorization

🔔 Alerts & Notifications

📨 Notification System

⚠️ Safety Events

🗺️ Aviation Data

🎙️ Audio Transmissions

📡 Antenna Analytics

⭐ Engagement Tracking

🏆 Gamification & Statistics

🚔 Cannonball Mode

Cannonball Mode provides law enforcement aircraft detection and pattern analysis.

flowchart LR
    subgraph Detection
        A[Known Aircraft DB] --> B{Match?}
        C[Pattern Analysis] --> B
    end

    subgraph Tracking
        B --> D[Cannonball Session]
        D --> E[Pattern Detection]
        D --> F[Alert Generation]
    end

    subgraph Analysis
        E --> G[Circling]
        E --> H[Loitering]
        E --> I[Grid Search]
        E --> J[Surveillance]
    end

⚡ Performance Optimization

Index Strategy

SkySpy uses a comprehensive indexing strategy optimized for common query patterns.

graph TD
    subgraph "Query Patterns"
        A[Time-Based] --> A1["Recent sightings"]
        A --> A2["Session history"]

        B[Aircraft Lookups] --> B1["Track by ICAO"]
        B --> B2["History queries"]

        C[Filtered Queries] --> C1["Military aircraft"]
        C --> C2["Distance range"]
        C --> C3["Signal strength"]
    end

    subgraph "Index Types"
        D[Composite] --> D1["Multi-column"]
        E[Partial] --> E1["Conditional"]
        F[Descending] --> F1["Time ordering"]
    end

    A1 --> F
    A2 --> F
    B1 --> D
    B2 --> D
    C1 --> E
    C2 --> E
    C3 --> E

Tip: Partial indexes in PostgreSQL dramatically reduce index size for sparse boolean columns like is_military (typically less than 5% of traffic).

Time-Based Queries

-- Most common pattern: Recent sightings ordered by time
CREATE INDEX idx_sighting_timestamp_desc ON aircraft_sightings(timestamp DESC);
CREATE INDEX idx_session_last_seen ON aircraft_sessions(last_seen DESC);

Aircraft Lookups

-- Track history for specific aircraft
CREATE INDEX idx_sighting_icao_timestamp ON aircraft_sightings(icao_hex, timestamp DESC);
CREATE INDEX idx_session_icao_lastseen ON aircraft_sessions(icao_hex, last_seen DESC);

Partial Indexes

-- Military aircraft filtering (typically less than 5% of traffic)
CREATE INDEX idx_sighting_military ON aircraft_sightings(is_military) WHERE is_military = TRUE;

-- Distance-based queries (exclude null values)
CREATE INDEX idx_sighting_distance ON aircraft_sightings(distance_nm) WHERE distance_nm IS NOT NULL;

Query Optimization Tips

Performance Best Practices

Connection Pooling

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'CONN_MAX_AGE': 60,  # Reuse connections for 60 seconds
    }
}

📦 Data Retention Policies

Active Data

Cached Data

Archive Strategy

# Mark expired NOTAMs as archived
CachedNotam.objects.filter(
    effective_end__lt=timezone.now() - timedelta(days=7),
    is_archived=False
).update(
    is_archived=True,
    archived_at=timezone.now(),
    archive_reason='expired'
)

🔄 Migration Strategy

Running Migrations

# Apply all pending migrations
python manage.py migrate

# Show migration status
python manage.py showmigrations

# Create new migration
python manage.py makemigrations skyspy

Migration Best Practices

Important Guidelines

1. Never edit applied migrations - Create new migrations for changes
2. Use atomic=False for index creation - Allows CREATE INDEX CONCURRENTLY
3. Test on copy of production data - Verify performance impact
4. Backup before major migrations - Enable rollback capability

PostgreSQL-Specific Migrations

class Migration(migrations.Migration):
    atomic = False  # Required for CONCURRENTLY

    operations = [
        PostgreSQLOnlyRunSQL(
            sql="CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_name ON table(column);",
            reverse_sql="DROP INDEX CONCURRENTLY IF EXISTS idx_name;",
        ),
    ]

Zero-Downtime Migrations

For large tables, use the following pattern:

1. Add new columns as nullable
2. Backfill data in batches
3. Add NOT NULL constraint if needed
4. Create indexes concurrently

📈 Database Monitoring

Key Metrics

Useful Queries

-- Active connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

-- Slow queries
SELECT query, calls, mean_time, max_time
FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;

-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Index usage
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

💾 Backup and Recovery

Backup Strategy

# Full database backup
pg_dump -h localhost -U adsb adsb > backup_$(date +%Y%m%d).sql

# Schema-only backup
pg_dump -h localhost -U adsb --schema-only adsb > schema.sql

# Data-only backup
pg_dump -h localhost -U adsb --data-only adsb > data.sql

Point-in-Time Recovery

Configure WAL archiving in PostgreSQL:

archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

Restore Procedure

# Restore from backup
psql -h localhost -U adsb adsb < backup.sql

# Restore to specific point in time
pg_restore --target-time="2024-01-15 10:00:00" -d adsb backup.dump

📜 Version History