Database Architecture

Overview

Our architecture maintains two separate databases for better modularity, security, and maintainability:

Simulation Database

`balloon_sim.db`

Core simulation engine and technical operations

Flight trajectory data
Atmospheric models
Physics calculations
Simulation results

Technology: SQLite

Logistics Database

`logistics.db`

Business operations and inventory management

Balloon inventory
Campaign & mission data
Cost calculations
Vendor information

Technology: PostgreSQL with SQLAlchemy

Architecture Rationale

Why Separate Databases?

Simulation DB: Engineering-focused with frequently updated algorithms
Logistics DB: Business-focused with stable data structures

Adding balloons to simulation requires:

Physics validation
Burst altitude calculations
Drag coefficient modeling
Technical testing

Adding balloons to logistics only requires:

Cost information
Vendor details
Lead times

Different access patterns and permissions
Logistics data contains sensitive vendor/cost information
Simulation data is technical/scientific

Simulation DB optimized for computational queries
Logistics DB optimized for transactional operations
Can scale independently with different technologies

Database Schemas

-- Balloon Types
CREATE TABLE balloon_types (
    id SERIAL PRIMARY KEY,
    manufacturer VARCHAR(100),
    model VARCHAR(100),
    mass_grams INTEGER,
    burst_altitude_m INTEGER,
    unit_cost_usd DECIMAL(10,2),
    vendor_id INTEGER REFERENCES vendors(id)
);

-- Gas Types
CREATE TABLE gas_types (
    id SERIAL PRIMARY KEY,
    gas_type VARCHAR(50),
    purity_percent DECIMAL(5,2),
    cost_per_m3_usd DECIMAL(10,2),
    vendor_id INTEGER REFERENCES vendors(id)
);

-- Campaigns
CREATE TABLE campaigns (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    status VARCHAR(50),
    start_date DATE,
    end_date DATE
);

-- Missions
CREATE TABLE missions (
    id SERIAL PRIMARY KEY,
    campaign_id INTEGER REFERENCES campaigns(id),
    launch_lat DECIMAL(10,6),
    launch_lon DECIMAL(10,6),
    balloon_type_id INTEGER REFERENCES balloon_types(id)
);

# Simulation Results Table
class SimulationResult:
    id: int
    timestamp: datetime
    config: JSON  # Simulation configuration
    trajectory: JSON  # Flight path data
    max_altitude: float
    flight_duration: float
    landing_location: JSON
    status: str

Data Flow

Simulation to Logistics Integration

User Creates
Campaign

Logistics
Database

Simulation
Engine

Simulation
Database

Results &
Analysis

API Integration Points:

/api/simulate - Bridges logistics mission to simulation
/api/results/<id> - Retrieves simulation results
/logistics/api/missions/<id>/simulate - Triggers simulation
/campaign/api/campaigns/<id>/results - Aggregates results

Database Management

Initialization

# Simulation Database
python scripts/init_db.py

# Logistics Database
python scripts/init_logistics_db.py

Migrations

# Logistics (Alembic)
flask db upgrade

# Simulation (Manual)
python scripts/migrate_sim_db.py

Environment Configuration

# Development
SIMULATION_DB_PATH=./balloon_sim.db
DATABASE_URL=postgresql://user:pass@localhost/logistics_dev

# Production
SIMULATION_DB_URL=postgresql://user:pass@sim-db.example.com/simulations
DATABASE_URL=postgresql://user:pass@logistics-db.example.com/logistics

Security Considerations

Aspect	Simulation DB	Logistics DB
Access Pattern	Read-heavy, write restricted	Full CRUD operations
Data Sensitivity	Public/scientific data	Confidential pricing/vendor info
Compliance	Data integrity focus	SOC2/ISO compliance required

Monitoring & Health

Health Checks

/api/health
/logistics/api/health
/campaign/api/health

Key Metrics

Query performance
Storage growth
Connection pools

Troubleshooting

Connection errors
Migration failures
Performance issues