Skip to content

architecture.md

Latest commit

 

History

History
1807 lines (1478 loc) · 56.8 KB

architecture.md

File metadata and controls

1807 lines (1478 loc) · 56.8 KB
Raw

Architecture Documentation

Table of Contents

  1. System Overview
  2. Design Principles
  3. Component Architecture
  4. Cross-Platform Abstraction Layer
  5. Julia Backend Architecture
  6. TypeScript/ReScript Frontend Architecture
  7. Data Flow
  8. Caching Strategy
  9. Rate Limiting Implementation
  10. Retry Logic and Error Handling
  11. API Endpoints
  12. Security Considerations
  13. Performance Optimizations
  14. Future Architecture Improvements

System Overview

The Excel Economic Number Tool is a cross-platform add-in for Microsoft Excel and LibreOffice Calc, designed for economic modeling and investigative research. It provides access to 10+ free economic data sources, custom economic formulas, and constraint propagation capabilities.

Key Characteristics

  • Cross-Platform: Single codebase supports both Excel (Office.js) and LibreOffice (UNO API)
  • Client-Server Architecture: TypeScript/ReScript frontend communicates with Julia backend via HTTP
  • High Performance: Julia backend provides fast numerical computations
  • Resilient: Built-in caching, rate limiting, and retry mechanisms
  • Extensible: Plugin-based data source architecture

Technology Stack

Layer Technology Purpose
Frontend TypeScript/ReScript Cross-platform spreadsheet integration
Backend Julia 1.10+ Data fetching, caching, formulas, API server
Data Storage SQLite Persistent cache with TTL
Communication HTTP/REST JSON-based API
Build Tools Webpack, npm, Just Build automation

Design Principles

1. Separation of Concerns

  • Frontend: User interface and spreadsheet integration only
  • Backend: Business logic, data fetching, caching, and computation
  • Abstraction Layer: Platform-specific details isolated behind interfaces

2. Interface-Based Design

All platform-specific implementations conform to ISpreadsheetAdapter interface, enabling:

  • Single codebase for multiple platforms
  • Easy testing with mock adapters
  • Future platform support without refactoring

3. Fail-Safe Operation

  • Cache fallback when APIs are unavailable
  • Graceful degradation on network failures
  • User-friendly error messages

4. Performance First

  • Request batching and deduplication
  • Persistent caching with configurable TTL
  • Rate limiting to prevent API throttling
  • Lazy loading and on-demand computation

5. Developer Experience

  • Clear, documented interfaces
  • Modular architecture
  • Hot reload in development
  • Comprehensive error handling

Component Architecture

┌─────────────────────────────────────────────────────────────────────┐
│                         USER INTERFACE                               │
│                  (Microsoft Excel / LibreOffice Calc)               │
└─────────────────────────────────────────────────────────────────────┘
                                   │
                                   │ Office.js / UNO API
                                   ▼
┌─────────────────────────────────────────────────────────────────────┐
│               CROSS-PLATFORM ABSTRACTION LAYER                      │
│                                                                      │
│  ┌──────────────────────┐        ┌──────────────────────┐          │
│  │  ISpreadsheetAdapter │◄───────┤   createAdapter()    │          │
│  │    (Interface)       │        │   (Factory Pattern)  │          │
│  └──────────┬───────────┘        └──────────────────────┘          │
│             │                                                        │
│    ┌────────┴────────┐                                              │
│    ▼                 ▼                                              │
│  ┌─────────────┐  ┌─────────────┐                                  │
│  │OfficeJs     │  │UnoAdapter   │                                  │
│  │Adapter      │  │             │                                  │
│  │(Excel)      │  │(LibreOffice)│                                  │
│  └─────────────┘  └─────────────┘                                  │
│                                                                      │
│  TypeScript/ReScript Frontend                                       │
└─────────────────────────────────────────────────────────────────────┘
                                   │
                                   │ HTTP/REST (JSON)
                                   ▼
┌─────────────────────────────────────────────────────────────────────┐
│                    JULIA BACKEND SERVER                             │
│                                                                      │
│  ┌────────────────────────────────────────────────────────────┐    │
│  │                    HTTP Router                              │    │
│  │            (GET/POST endpoints @ port 8080)                 │    │
│  └────────────────────────────────────────────────────────────┘    │
│         │              │              │              │              │
│         ▼              ▼              ▼              ▼              │
│  ┌───────────┐  ┌───────────┐  ┌───────────┐  ┌───────────┐       │
│  │   Data    │  │ Formulas  │  │   Cache   │  │   Utils   │       │
│  │  Sources  │  │           │  │  Manager  │  │           │       │
│  └───────────┘  └───────────┘  └───────────┘  └───────────┘       │
│         │              │              │              │              │
│         ▼              │              ▼              ▼              │
│  ┌───────────┐         │        ┌───────────┐  ┌───────────┐       │
│  │ Rate      │         │        │  SQLite   │  │  Retry    │       │
│  │ Limiter   │         │        │  Cache    │  │  Logic    │       │
│  └───────────┘         │        └───────────┘  └───────────┘       │
│                        ▼                                            │
│                  ┌───────────┐                                      │
│                  │GDP Growth │                                      │
│                  │Elasticity │                                      │
│                  │Lorenz     │                                      │
│                  │Constraints│                                      │
│                  └───────────┘                                      │
└─────────────────────────────────────────────────────────────────────┘
                                   │
                                   │ HTTPS
                                   ▼
┌─────────────────────────────────────────────────────────────────────┐
│                     EXTERNAL DATA SOURCES                           │
│                                                                      │
│  FRED │ World Bank │ IMF │ OECD │ DBnomics │ ECB │ BEA │ Census   │
│                       Eurostat │ BIS                               │
└─────────────────────────────────────────────────────────────────────┘

Cross-Platform Abstraction Layer

Overview

The abstraction layer enables a single codebase to work across both Microsoft Excel and LibreOffice Calc by defining a common interface (ISpreadsheetAdapter) that platform-specific implementations must follow.

Architecture Pattern

┌─────────────────────────────────────────────────────┐
│         ISpreadsheetAdapter (Interface)             │
├─────────────────────────────────────────────────────┤
│ + getPlatform(): Platform                           │
│ + isReady(): Promise<boolean>                       │
│                                                      │
│ Cell Operations:                                     │
│ + getCellValue(address): Promise<CellValue>         │
│ + setCellValue(address, value): Promise<void>       │
│ + getRange(start, end): Promise<CellMatrix>         │
│ + setRange(start, data): Promise<void>              │
│ + clearRange(start, end): Promise<void>             │
│                                                      │
│ Custom Functions:                                    │
│ + registerFunction(metadata, impl): void            │
│ + callFunction(name, ...args): Promise<any>         │
│                                                      │
│ Events:                                              │
│ + onSelectionChange(handler): () => void            │
│ + onCalculate(handler): () => void                  │
│ + onSheetChange(handler): () => void                │
│                                                      │
│ UI:                                                  │
│ + showDialog(content, options): Promise<void>       │
│ + showTaskPane(component, options): Promise<void>   │
│ + showNotification(msg, type): Promise<void>        │
│                                                      │
│ Sheets:                                              │
│ + getSheetNames(): Promise<string[]>                │
│ + getActiveSheetName(): Promise<string>             │
│ + createSheet(name): Promise<void>                  │
│ + deleteSheet(name): Promise<void>                  │
│                                                      │
│ Utilities:                                           │
│ + getSelectedRange(): Promise<RangeAddress>         │
│ + setSelectedRange(address): Promise<void>          │
│ + batch<T>(operations): Promise<T>                  │
│ + recalculate(): Promise<void>                      │
└─────────────────────────────────────────────────────┘
              ▲                           ▲
              │                           │
              │                           │
    ┌─────────┴─────────┐       ┌────────┴──────────┐
    │  OfficeJsAdapter  │       │   UnoAdapter      │
    ├───────────────────┤       ├───────────────────┤
    │ Excel-specific    │       │ LibreOffice-      │
    │ implementation    │       │ specific impl     │
    │ using Office.js   │       │ using UNO API     │
    └───────────────────┘       └───────────────────┘

Platform Detection

export function detectPlatform(): Platform {
    if (typeof (window as any).Office !== 'undefined') {
        return Platform.Excel;
    } else if (typeof (window as any).XSCRIPTCONTEXT !== 'undefined') {
        return Platform.LibreOffice;
    } else if (typeof window !== 'undefined') {
        return Platform.Web;
    } else {
        return Platform.Unknown;
    }
}

Factory Pattern

The createAdapter() function automatically detects the platform and returns the appropriate adapter:

export function createAdapter(): ISpreadsheetAdapter {
    if (typeof (window as any).Office !== 'undefined') {
        return new OfficeJsAdapter();
    } else if (typeof (window as any).XSCRIPTCONTEXT !== 'undefined') {
        return new UnoAdapter();
    } else {
        throw new Error('Unknown platform');
    }
}

Type Definitions

export type CellAddress = string;           // e.g., "B5", "Sheet1!C10"
export type RangeAddress = string;          // e.g., "A1:B10", "Sheet1!C5:E15"
export type CellValue = string | number | boolean | Date | null | undefined;
export type CellMatrix = CellValue[][];     // 2D array

Benefits

  1. Single Codebase: Write once, run on both platforms
  2. Easy Testing: Mock adapters for unit testing
  3. Future-Proof: Add new platforms without refactoring
  4. Type Safety: TypeScript ensures all implementations match interface
  5. Consistent API: Same function calls regardless of platform

Julia Backend Architecture

Module Structure

EconomicToolkit (Main Module)
│
├── utils/
│   ├── rate_limiter.jl     # Sliding window rate limiting
│   └── retry.jl            # Exponential backoff retry logic
│
├── cache/
│   └── sqlite_cache.jl     # Persistent SQLite cache with TTL
│
├── data_sources/
│   ├── FRED.jl             # Federal Reserve Economic Data
│   ├── WorldBank.jl        # World Bank Open Data
│   ├── IMF.jl              # International Monetary Fund
│   ├── OECD.jl             # OECD Statistics
│   ├── DBnomics.jl         # DBnomics aggregator
│   ├── ECB.jl              # European Central Bank
│   ├── BEA.jl              # Bureau of Economic Analysis
│   ├── Census.jl           # US Census Bureau
│   ├── Eurostat.jl         # European Statistics
│   └── BIS.jl              # Bank for International Settlements
│
└── formulas/
    ├── elasticity.jl       # Price/income elasticity calculations
    ├── gdp_growth.jl       # GDP growth rate formulas
    ├── lorenz.jl           # Lorenz curve and Gini coefficient
    └── constraints.jl      # Constraint propagation solver

Data Source Client Pattern

All data source clients follow a consistent pattern:

struct FREDClient
    base_url::String
    api_key::Union{String, Nothing}
    rate_limiter::RateLimiter
    cache::SQLiteCache
    retry_config::RetryConfig
end

Key Methods:

  • fetch_series(client, series_id, start_date, end_date) -> DataFrame
  • search_series(client, query; limit=100) -> Vector{Dict}
  • get_series_info(client, series_id) -> Dict

HTTP Server

The Julia backend runs an HTTP server (default port 8080) with a routing layer:

function start_server(port::Int=8080; host::String="127.0.0.1")
    # Initialize clients
    clients = Dict(
        "fred" => FREDClient(),
        "worldbank" => WorldBankClient(),
        # ... more clients
    )

    # Create router
    router = HTTP.Router()

    # Register routes
    HTTP.register!(router, "GET", "/api/v1/sources")
    HTTP.register!(router, "GET", "/api/v1/sources/:source/search")
    HTTP.register!(router, "GET", "/api/v1/sources/:source/series/:id")

    # Start server
    HTTP.serve(router, host, port)
end

Dependencies

using HTTP          # HTTP server and client
using JSON3         # Fast JSON serialization
using SQLite        # Cache database
using DataFrames    # Tabular data structure
using Dates         # Date/time handling
using Statistics    # Statistical functions
using LinearAlgebra # Linear algebra operations
using SHA           # Hash functions for cache keys

Concurrency Model

  • Thread-Safe: Rate limiter uses ReentrantLock for thread safety
  • Async HTTP: HTTP.jl supports concurrent requests
  • Parallel Formulas: Economic calculations can leverage Julia's parallel computing

TypeScript/ReScript Frontend Architecture

Layer Structure

Frontend Architecture
│
├── adapters/
│   ├── ISpreadsheetAdapter.ts    # Interface definition
│   ├── OfficeJsAdapter.ts        # Excel implementation
│   └── UnoAdapter.js             # LibreOffice implementation
│
├── services/
│   ├── ApiClient.ts              # HTTP client for Julia backend
│   ├── DataSourceService.ts     # Data source operations
│   └── FormulaService.ts        # Custom formula handlers
│
├── ui/
│   ├── TaskPane.tsx             # Main task pane UI
│   ├── SearchDialog.tsx         # Series search interface
│   └── SettingsPanel.tsx        # Configuration UI
│
└── utils/
    ├── validators.ts            # Input validation
    ├── formatters.ts            # Number/date formatting
    └── logger.ts                # Error logging

OfficeJsAdapter Implementation Highlights

Initialization:

private async initialize(): Promise<void> {
    if (!this.initPromise) {
        this.initPromise = new Promise<void>((resolve, reject) => {
            Office.onReady(() => {
                this.initialized = true;
                resolve();
            });
        });
    }
    return this.initPromise;
}

Batch Operations:

async batch<T>(operations: () => Promise<T>): Promise<T> {
    return await Excel.run(async (context) => {
        const result = await operations();
        await context.sync();  // Single sync for all operations
        return result;
    });
}

Event Handling:

  • Selection change events
  • Calculation complete events
  • Sheet activation events

Build Configuration

Webpack bundles separate builds for:

  • Excel (webpack.excel.config.js)
  • LibreOffice (webpack.libre.config.js)
  • Development server with hot reload

TypeScript configuration:

  • Target: ES2020
  • Module: ESNext
  • Strict mode enabled
  • Source maps for debugging

ReScript Integration

ReScript is used for:

  • Type-safe functional programming
  • Advanced pattern matching
  • Compiled to JavaScript (interops with TypeScript)

Data Flow

Fetch Series Data Flow

┌─────────────┐
│  User       │
│  Action     │
│  in Excel   │
└──────┬──────┘
       │
       ▼
┌─────────────────────────────────────────────────┐
│  Frontend (TypeScript)                          │
│                                                  │
│  1. User calls custom function                  │
│     =ECONOMIC_FETCH("FRED", "GDPC1", ...)       │
│                                                  │
│  2. Adapter captures function call              │
│     adapter.callFunction(...)                   │
│                                                  │
│  3. ApiClient makes HTTP request                │
│     GET /api/v1/sources/fred/series/GDPC1       │
└─────────────────┬───────────────────────────────┘
                  │
                  │ HTTP Request
                  ▼
┌─────────────────────────────────────────────────┐
│  Julia Backend                                  │
│                                                  │
│  4. Router receives request                     │
│     HTTP.register! matches route                │
│                                                  │
│  5. Extract parameters                          │
│     source = "fred"                             │
│     series_id = "GDPC1"                         │
│     start_date, end_date from query params      │
│                                                  │
│  6. Check cache                                 │
│     key = cache_key("fred", "GDPC1", ...)       │
│     cached = get_cached(cache, key)             │
│                                                  │
│     ┌─────────────────┐                         │
│     │ Cache Hit?      │                         │
│     └────┬────────┬───┘                         │
│          │ YES    │ NO                          │
│          ▼        ▼                             │
│     ┌────────┐  ┌──────────────────────┐        │
│     │ Return │  │ 7. Check rate limit  │        │
│     │ cached │  │    wait_if_needed()  │        │
│     │  data  │  └──────────┬───────────┘        │
│     └────────┘             │                    │
│                            ▼                    │
│                   ┌─────────────────────┐       │
│                   │ 8. Fetch from API   │       │
│                   │    with_retry()     │       │
│                   └──────────┬──────────┘       │
│                              │                  │
│                   ┌──────────▼──────────┐       │
│                   │ Success?            │       │
│                   └──┬────────────┬─────┘       │
│                      │ YES        │ NO          │
│                      ▼            ▼             │
│                ┌──────────┐  ┌────────────┐     │
│                │9. Parse  │  │ Retry with │     │
│                │  response│  │ exponential│     │
│                │          │  │  backoff   │     │
│                │10. Cache │  │            │     │
│                │   result │  │ Final fail:│     │
│                └────┬─────┘  │ Return old │     │
│                     │        │  cache     │     │
│                     ▼        └────────────┘     │
│                ┌──────────┐                     │
│                │11. Return│                     │
│                │ DataFrame│                     │
│                └──────────┘                     │
└─────────────────┬───────────────────────────────┘
                  │
                  │ HTTP Response (JSON)
                  ▼
┌─────────────────────────────────────────────────┐
│  Frontend (TypeScript)                          │
│                                                  │
│  12. Parse JSON response                        │
│      Convert to CellMatrix                      │
│                                                  │
│  13. Write to spreadsheet                       │
│      adapter.setRange(address, data)            │
│                                                  │
│  14. Update UI                                  │
│      Show success notification                  │
└─────────────────┬───────────────────────────────┘
                  │
                  ▼
┌─────────────────────────────────────────────────┐
│  Excel/LibreOffice                              │
│                                                  │
│  15. Display data in cells                      │
│      User sees economic time series             │
└─────────────────────────────────────────────────┘

Search Series Data Flow

User Input → Frontend → Backend → Check Cache
                                       ↓
                                  Cache Miss?
                                       ↓
                              Rate Limit Check
                                       ↓
                              API Search Request
                                       ↓
                              Parse Results
                                       ↓
                              Cache Results (1hr TTL)
                                       ↓
                              Return JSON
                                       ↓
                          Frontend → Display in Dialog

Caching Strategy

Cache Architecture

┌────────────────────────────────────────────────┐
│         SQLiteCache                            │
├────────────────────────────────────────────────┤
│  Database: ~/.economic-toolkit/cache/data.db  │
│                                                 │
│  Table: cache                                   │
│  ├── key (TEXT, PRIMARY KEY)                   │
│  ├── value (TEXT) - JSON serialized            │
│  ├── created_at (INTEGER) - Unix timestamp     │
│  ├── expires_at (INTEGER) - Unix timestamp     │
│  ├── source (TEXT) - e.g., "fred"              │
│  ├── series_id (TEXT) - e.g., "GDPC1"          │
│  └── metadata (TEXT) - Additional JSON data    │
│                                                 │
│  Index: idx_expires_at on expires_at           │
└────────────────────────────────────────────────┘

Cache Key Generation

function cache_key(source::String, series_id::String,
                   start_date::Date, end_date::Date)::String
    data = "$source|$series_id|$start_date|$end_date"
    return bytes2hex(sha256(data))
end

Example:

  • Input: "fred", "GDPC1", Date(2020,1,1), Date(2023,12,31)
  • Output: "a7b3c9d2e5f1..." (64-character hex string)

Time-To-Live (TTL) Strategy

Data Type Default TTL Rationale
Time series data 24 hours Economic data updates daily
Search results 1 hour Search indices change frequently
Metadata 7 days Series metadata rarely changes
Formula results None Calculated on-demand

Cache Operations

Get from cache:

function get_cached(cache::SQLiteCache, key::String)::Union{String, Nothing}
    now_unix = Int(floor(datetime2unix(now())))
    result = execute(cache.db, """
        SELECT value FROM cache
        WHERE key = ? AND expires_at > ?
    """, (key, now_unix))

    row = first(result, nothing)
    return row === nothing ? nothing : row.value
end

Set to cache:

function set_cached(cache::SQLiteCache, key::String, value::String;
                    ttl::Union{Int, Nothing}=nothing,
                    metadata::Dict=Dict())
    ttl_seconds = ttl === nothing ? cache.default_ttl : ttl
    now_unix = Int(floor(datetime2unix(now())))
    expires_at = now_unix + ttl_seconds

    execute(cache.db, """
        INSERT OR REPLACE INTO cache
        (key, value, created_at, expires_at, source, series_id, metadata)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, (key, value, now_unix, expires_at, ...))
end

Cache Maintenance

Cleanup expired entries:

function clear_expired(cache::SQLiteCache)::Int
    now_unix = Int(floor(datetime2unix(now())))
    execute(cache.db, "DELETE FROM cache WHERE expires_at <= ?", (now_unix,))
    return SQLite.changes(cache.db)
end

Cache statistics:

function get_stats(cache::SQLiteCache)::Dict
    return Dict(
        "total" => total_entries,
        "active" => active_entries,
        "expired" => expired_entries,
        "by_source" => Dict("fred" => 150, "worldbank" => 80, ...),
        "db_size_mb" => 12.45
    )
end

Cache Location

  • Path: ~/.economic-toolkit/cache/data.db
  • Persistent: Survives application restarts
  • Cross-session: Shared across all Excel/LibreOffice instances

Cache Benefits

  1. Performance: Instant responses for cached data
  2. Offline Support: Works without internet (if data is cached)
  3. API Conservation: Reduces external API calls
  4. Cost Reduction: Minimizes rate limit exhaustion
  5. Resilience: Fallback when APIs are down

Rate Limiting Implementation

Sliding Window Algorithm

Time Window: 60 seconds
Limit: 120 requests (for FRED with API key)

Timeline:
─────────────────────────────────────────────────────────────►
0s        15s        30s        45s        60s        75s
│          │          │          │          │          │
├──────────┼──────────┼──────────┼──────────┼──────────┤
│ 30 reqs  │ 40 reqs  │ 35 reqs  │ 15 reqs  │ expired  │
│          │          │          │          │          │
│◄─────────────── 60s window ────────────►│
                                  Current count: 120
                                  Can proceed: YES

                                  New request at 61s:
│          │          │          │          ├─── X ────┤
│ expired  │ 40 reqs  │ 35 reqs  │ 15 reqs  │ 1 new    │
│          │          │          │          │          │
│          │◄─────────────── 60s window ────────────►│
                                  Current count: 91
                                  Can proceed: YES

RateLimiter Implementation

mutable struct RateLimiter
    limit::Int                    # Max requests per window
    window_seconds::Int           # Window duration (default: 60)
    timestamps::Vector{DateTime}  # Request timestamps
    lock::ReentrantLock          # Thread-safe access
end

Key Methods

Check if can proceed:

function can_proceed(limiter::RateLimiter)::Bool
    lock(limiter.lock) do
        # Remove timestamps outside the window
        cutoff = now() - Second(limiter.window_seconds)
        filter!(t -> t > cutoff, limiter.timestamps)

        # Check if under limit
        return length(limiter.timestamps) < limiter.limit
    end
end

Wait if needed:

function wait_if_needed(limiter::RateLimiter; max_wait::Int=120)::Bool
    start_time = now()

    while !can_proceed(limiter)
        # Check max wait timeout
        if (now() - start_time).value / 1000 > max_wait
            return false
        end

        # Calculate sleep time until oldest request expires
        lock(limiter.lock) do
            oldest = minimum(limiter.timestamps)
            wait_until = oldest + Second(limiter.window_seconds)
            sleep_time = max(0.1, (wait_until - now()).value / 1000)
            sleep(sleep_time)
        end
    end

    # Record this request
    record_request(limiter)
    return true
end

Per-Source Rate Limits

Data Source Limit Window Notes
FRED (with key) 120 60s Official limit
FRED (no key) 5 60s Reduced limit
World Bank 120 60s Conservative
IMF 60 60s Undocumented
OECD 100 60s Conservative
DBnomics 300 60s High throughput

Thread Safety

  • ReentrantLock: Prevents race conditions in multi-threaded environment
  • Atomic Operations: All timestamp modifications are protected
  • Concurrent Safe: Multiple clients can share rate limiter

Benefits

  1. API Compliance: Never exceed provider limits
  2. Automatic Throttling: Sleeps when limit reached
  3. Fair Queuing: FIFO request processing
  4. Graceful Degradation: Returns false if timeout exceeded

Retry Logic and Error Handling

Exponential Backoff Strategy

Attempt 1:  Immediate
            ↓ (fails)
Attempt 2:  Wait 2.0s
            ↓ (fails)
Attempt 3:  Wait 4.0s
            ↓ (fails)
Attempt 4:  Wait 8.0s
            ↓ (fails)
Max retries reached → Check cache fallback

RetryConfig

struct RetryConfig
    max_retries::Int              # Default: 3
    initial_delay::Float64        # Default: 2.0s
    max_delay::Float64            # Default: 32.0s
    backoff_factor::Float64       # Default: 2.0
    retry_on::Vector{Int}         # HTTP status codes: [429, 500, 502, 503, 504]
end

Retry Decision Logic

function should_retry(e::Exception, config::RetryConfig)::Bool
    # HTTP status errors (429 Too Many Requests, 5xx Server Errors)
    if e isa HTTP.Exceptions.StatusError
        return e.status in config.retry_on
    end

    # Network errors (timeout, connection failure)
    if e isa Base.IOError || e isa Base.EOFError
        return true
    end

    # Timeout errors
    if e isa HTTP.Exceptions.TimeoutError ||
       e isa HTTP.Exceptions.ConnectError
        return true
    end

    # Don't retry 4xx errors (except 429)
    return false
end

Retry with Cache Fallback

function with_retry_and_cache(f::Function, cache::SQLiteCache,
                               cache_key::String,
                               config::RetryConfig=RetryConfig())
    try
        # Try with exponential backoff
        result = with_retry(f, config)
        return (result, false)  # Success, not from cache
    catch e
        @warn "All retries failed, attempting cache fallback" exception=e

        # Try to get from cache (even expired data is better than none)
        cached = get_cached(cache, cache_key)
        if cached !== nothing
            @info "Returning cached data after API failure"
            return (JSON3.read(cached), true)  # From cache
        else
            @error "No cached data available for fallback"
            rethrow(e)
        end
    end
end

Error Handling Flow

API Request
    │
    ▼
Try Request
    │
    ├─ Success → Return Result
    │
    ├─ Retryable Error? (429, 500, 503, timeout)
    │   │
    │   ├─ Attempt < Max Retries?
    │   │   │
    │   │   ├─ Yes → Wait (exponential backoff)
    │   │   │        Retry Request
    │   │   │
    │   │   └─ No → Check Cache
    │   │            │
    │   │            ├─ Cache Hit → Return Cached Data (stale)
    │   │            └─ Cache Miss → Throw Error
    │   │
    │   └─ Not Retryable → Throw Error Immediately
    │
    └─ Other Error → Throw Error Immediately

Error Types and Handling

Error Type Retry? Backoff Cache Fallback User Action
HTTP 429 (Rate Limit) Yes Exponential Yes Wait or use cache
HTTP 500-504 (Server) Yes Exponential Yes Retry automatic
HTTP 401/403 (Auth) No N/A No Check API key
HTTP 404 (Not Found) No N/A No Invalid series ID
Network Timeout Yes Exponential Yes Check connection
Connection Refused Yes Exponential Yes Backend offline
Parse Error No N/A No Bug in code

Logging

# Informational
@info "Retrying after error" attempt=2 delay_seconds=4.0 exception=e

# Warning
@warn "Max retries exhausted" exception=e max_retries=3

# Error
@error "No cached data available for fallback" series_id="GDPC1"

Benefits

  1. Resilience: Handles transient failures automatically
  2. User Experience: Transparent retries without user intervention
  3. Data Availability: Cache fallback ensures some data is returned
  4. Smart Backoff: Reduces server load during outages
  5. Debugging: Comprehensive logging for troubleshooting

API Endpoints

Base URL

http://127.0.0.1:8080/api/v1

Endpoints

1. List Data Sources

GET /api/v1/sources

Response:

[
  {
    "id": "fred",
    "name": "Federal Reserve Economic Data",
    "status": "active"
  },
  {
    "id": "worldbank",
    "name": "World Bank",
    "status": "active"
  }
]

2. Search Series

GET /api/v1/sources/:source/search?q=GDP

Parameters:

  • source (path): Data source ID (e.g., "fred", "worldbank")
  • q (query): Search query string

Response:

[
  {
    "id": "GDPC1",
    "title": "Real Gross Domestic Product",
    "frequency": "Quarterly",
    "units": "Billions of Chained 2012 Dollars",
    "seasonal_adjustment": "Seasonally Adjusted Annual Rate",
    "last_updated": "2024-01-30"
  }
]

3. Fetch Series Data

GET /api/v1/sources/:source/series/:id?start=2020-01-01&end=2023-12-31

Parameters:

  • source (path): Data source ID
  • id (path): Series ID
  • start (query, optional): Start date (YYYY-MM-DD), default: 1900-01-01
  • end (query, optional): End date (YYYY-MM-DD), default: today

Response:

{
  "date": ["2020-01-01", "2020-04-01", "2020-07-01"],
  "value": [19032.5, 17258.2, 18560.8]
}

4. Calculate Elasticity

POST /api/v1/formulas/elasticity
Content-Type: application/json

{
  "quantity": [100, 90, 85],
  "price": [10, 12, 13]
}

Response:

{
  "elasticity": -0.54,
  "type": "inelastic"
}

5. Calculate GDP Growth

POST /api/v1/formulas/growth
Content-Type: application/json

{
  "values": [1000, 1050, 1100, 1120],
  "type": "yoy"  // "yoy", "mom", "qoq"
}

Response:

{
  "growth_rates": [null, 0.05, 0.0476, 0.0182],
  "average": 0.0386
}

6. Calculate Gini Coefficient

POST /api/v1/formulas/gini
Content-Type: application/json

{
  "incomes": [10000, 20000, 30000, 50000, 100000]
}

Response:

{
  "gini": 0.38,
  "lorenz_curve": [[0, 0], [0.2, 0.048], [0.4, 0.143], ...]
}

7. Solve Constraints

POST /api/v1/constraints/solve
Content-Type: application/json

{
  "constraints": [
    {"type": "sum", "cells": ["A1", "A2", "A3"], "equals": 100},
    {"type": "ratio", "cell1": "A1", "cell2": "A2", "ratio": 0.5}
  ],
  "initial_values": {"A1": 20, "A2": 40, "A3": 40}
}

Response:

{
  "solution": {"A1": 25, "A2": 50, "A3": 25},
  "converged": true,
  "iterations": 15
}

8. Cache Statistics

GET /api/v1/cache/stats

Response:

{
  "total": 1523,
  "active": 1421,
  "expired": 102,
  "by_source": {
    "fred": 850,
    "worldbank": 321,
    "imf": 250
  },
  "db_size_mb": 45.67
}

9. Clear Cache

DELETE /api/v1/cache

Response:

{
  "deleted": 1523,
  "status": "ok"
}

10. Health Check

GET /health

Response:

{
  "status": "ok",
  "version": "2.0.0"
}

Error Responses

All endpoints return errors in consistent format:

{
  "error": "Series not found",
  "code": "SERIES_NOT_FOUND",
  "details": {
    "series_id": "INVALID123",
    "source": "fred"
  }
}

HTTP Status Codes:

  • 200 - Success
  • 400 - Bad Request (invalid parameters)
  • 401 - Unauthorized (API key required/invalid)
  • 404 - Not Found (series/source doesn't exist)
  • 429 - Too Many Requests (rate limit exceeded)
  • 500 - Internal Server Error
  • 503 - Service Unavailable (backend offline)

Security Considerations

1. API Key Management

Storage:

  • API keys stored in environment variables (never in code)
  • Example: FRED_API_KEY, WORLDBANK_API_KEY

Access:

api_key = get(ENV, "FRED_API_KEY", nothing)

Security Practices:

  • Never log API keys
  • Never include in error messages
  • Never commit to version control
  • Use .env files for local development (in .gitignore)

2. HTTPS for External APIs

All external data source requests use HTTPS:

base_url = "https://api.stlouisfed.org/fred"  # NOT http://

3. Input Validation

Series ID Validation:

function validateSeriesId(id: string): boolean {
    // Alphanumeric, underscores, hyphens only
    return /^[A-Za-z0-9_-]+$/.test(id);
}

Date Validation:

function validateDateRange(start: Date, end: Date): boolean {
    if (start > end) {
        throw new Error('Start date must be before end date');
    }
    if (start < new Date('1900-01-01')) {
        throw new Error('Start date too far in the past');
    }
    return true;
}

4. SQL Injection Prevention

All SQLite queries use parameterized statements:

# SAFE - parameterized
execute(db, "SELECT * FROM cache WHERE key = ?", (key,))

# UNSAFE - string interpolation (NEVER do this)
# execute(db, "SELECT * FROM cache WHERE key = '$key'")

5. Cross-Origin Resource Sharing (CORS)

Backend allows requests only from trusted origins:

# In production, restrict to specific domains
allowed_origins = ["https://excel.office.com", "https://localhost:3000"]

6. Rate Limiting (Security Aspect)

Prevents:

  • Denial of Service (DoS) attacks
  • Accidental infinite loops
  • API key quota exhaustion

7. Data Sanitization

Output Sanitization:

function sanitizeForExcel(value: string): string {
    // Remove formula injection characters
    if (value.startsWith('=') || value.startsWith('+') ||
        value.startsWith('-') || value.startsWith('@')) {
        return "'" + value;  // Prefix with single quote
    }
    return value;
}

8. Error Message Sanitization

Don't expose:

  • File system paths
  • Internal IP addresses
  • Stack traces to end users
  • Database structure

Do expose:

  • User-friendly error messages
  • Actionable guidance
  • Error codes for support

9. Cache Security

  • Cache stored in user's home directory (~/.economic-toolkit/)
  • File permissions: User read/write only (chmod 600)
  • No sensitive data cached (only public economic data)

10. Dependency Security

Regular Updates:

# Check for vulnerable dependencies
npm audit
julia> using Pkg; Pkg.audit()

Lock Files:

  • package-lock.json (npm)
  • Project.toml + Manifest.toml (Julia)

11. Code Injection Prevention

TypeScript:

  • No eval() usage
  • No Function() constructor
  • Strict TypeScript mode enabled

Julia:

  • No eval() of user input
  • No include() of untrusted files

12. Authentication (Future)

Planned for v3.0:

  • User accounts
  • OAuth2 integration
  • Role-based access control (RBAC)

Security Checklist

  • API keys in environment variables
  • HTTPS for external requests
  • Parameterized SQL queries
  • Input validation
  • Rate limiting
  • Error sanitization
  • No code evaluation
  • Dependency auditing
  • CORS configuration (production)
  • Authentication system (v3.0)
  • Encrypted cache (v3.0)

Performance Optimizations

1. Request Batching

Problem: Multiple series fetched sequentially → slow

Solution: Batch requests in single HTTP call

// Instead of:
await fetch("GDPC1");
await fetch("UNRATE");
await fetch("CPIAUCSL");

// Do:
await fetchBatch(["GDPC1", "UNRATE", "CPIAUCSL"]);

Backend Support:

HTTP.register!(router, "POST", "/api/v1/sources/:source/batch") do req
    series_ids = JSON3.read(req.body).series_ids
    results = map(id -> fetch_series(client, id, start, end), series_ids)
    return HTTP.Response(200, JSON3.write(results))
end

2. Persistent Cache

Benefit: Avoid redundant API calls

Performance Gain:

  • Cache hit: < 1ms
  • API call: 200-500ms
  • 200-500x faster for cached data

3. SQLite Indexing

CREATE INDEX idx_expires_at ON cache(expires_at);
CREATE INDEX idx_source_series ON cache(source, series_id);

Impact: O(log n) lookups instead of O(n)

4. Lazy Loading

Frontend: Load data only when visible

// Don't fetch all sheets at startup
async getSheetNames(): Promise<string[]> {
    // Only load when user opens sheet selector
}

5. Excel Batch Operations

// SLOW - Multiple context syncs
for (let i = 0; i < 100; i++) {
    await setCellValue(`A${i}`, values[i]);  // 100 syncs
}

// FAST - Single context sync
await setRange("A1", values);  // 1 sync

Performance: 100x faster for large datasets

6. Compression

HTTP Responses:

# Future: Enable gzip compression
HTTP.serve(router, host, port, compress=true)

Benefit: 70-90% size reduction for JSON responses

7. Connection Pooling

# Reuse HTTP connections
client = HTTP.Client(
    connection_limit=10,
    keepalive=true
)

8. Julia Precompilation

# Compile module ahead of time
using PackageCompiler
create_sysimage([:EconomicToolkit], sysimage_path="toolkit.so")

Impact: 2-5x faster startup time

9. Async/Await

// Parallel API calls
const [fred, wb, imf] = await Promise.all([
    fetchFRED("GDPC1"),
    fetchWorldBank("NY.GDP.MKTP.CD"),
    fetchIMF("NGDP_R")
]);

10. Memory Management

DataFrame Pooling (Julia):

# Reuse DataFrame memory
function fetch_series_pooled(client, series_id, start, end)
    df = get_pooled_dataframe()
    # Fill df instead of allocating new
    return df
end

11. Debouncing User Input

// Wait 300ms after user stops typing before searching
const debouncedSearch = debounce(searchSeries, 300);

Benefit: Reduces API calls by 80-90% during typing

12. Incremental Updates

# Only fetch new data since last update
function fetch_incremental(client, series_id, last_date)
    # Fetch only dates > last_date
    # Merge with cached data
end

Performance Metrics

Operation Before Optimization After Optimization Improvement
Fetch cached series 200ms < 1ms 200x
Batch 10 series 3000ms 600ms 5x
Write 1000 cells 2000ms 20ms 100x
Search series 500ms 50ms (cached) 10x
Startup time 5s 1s 5x

Profiling Tools

Julia:

using Profile
@profile fetch_series(client, "GDPC1", start, end)
Profile.print()

TypeScript:

console.time("fetch");
await fetchData();
console.timeEnd("fetch");

Chrome DevTools:

  • Network tab: API call timing
  • Performance tab: JavaScript execution
  • Memory tab: Leak detection

Future Architecture Improvements

1. Microservices Architecture

Current: Monolithic Julia backend

Future: Split into specialized services

┌─────────────┐  ┌─────────────┐  ┌─────────────┐
│   Data      │  │  Formula    │  │   Cache     │
│  Service    │  │  Service    │  │  Service    │
│ (Port 8081) │  │ (Port 8082) │  │ (Port 8083) │
└─────────────┘  └─────────────┘  └─────────────┘
       │                │                │
       └────────────────┴────────────────┘
                        │
              ┌─────────▼─────────┐
              │   API Gateway     │
              │   (Port 8080)     │
              └───────────────────┘

Benefits:

  • Independent scaling
  • Fault isolation
  • Technology flexibility
  • Easier deployment

2. GraphQL API

Current: REST API with fixed endpoints

Future: GraphQL for flexible queries

query {
  series(source: "fred", id: "GDPC1") {
    data(start: "2020-01-01", end: "2023-12-31") {
      date
      value
    }
    metadata {
      title
      frequency
      units
    }
  }
}

Benefits:

  • Client-driven queries
  • Reduced over-fetching
  • Single request for complex data
  • Strong typing

3. WebSocket Support

Current: HTTP request/response

Future: WebSocket for real-time updates

const ws = new WebSocket('ws://localhost:8080/stream');
ws.on('data-update', (update) => {
    // Real-time data updates
    adapter.setCellValue('A1', update.value);
});

Use Cases:

  • Live economic indicators
  • Real-time market data
  • Collaborative editing

4. Distributed Caching

Current: Local SQLite cache

Future: Redis cluster

┌──────────┐  ┌──────────┐  ┌──────────┐
│ Redis 1  │  │ Redis 2  │  │ Redis 3  │
│ (Master) │──│(Replica) │──│(Replica) │
└──────────┘  └──────────┘  └──────────┘

Benefits:

  • Shared cache across instances
  • Faster access (in-memory)
  • Automatic replication
  • Pub/sub for cache invalidation

5. Message Queue

Current: Synchronous API calls

Future: Async job queue (e.g., RabbitMQ, Redis Queue)

Frontend → API → Queue → Worker → Result → Callback

Benefits:

  • Non-blocking operations
  • Job prioritization
  • Retry management
  • Load leveling

6. Container Orchestration

Current: Manual process management

Future: Kubernetes deployment

apiVersion: apps/v1
kind: Deployment
metadata:
  name: economic-toolkit-backend
spec:
  replicas: 3
  template:
    spec:
      containers:
      - name: julia-backend
        image: economic-toolkit:2.0

Benefits:

  • Auto-scaling
  • Self-healing
  • Rolling updates
  • Load balancing

7. Content Delivery Network (CDN)

Current: Direct API responses

Future: CDN for static data

User → CDN (cached) → Origin Server

Benefits:

  • Global distribution
  • Reduced latency
  • Lower bandwidth costs
  • DDoS protection

8. Machine Learning Integration

Current: Manual data analysis

Future: ML-powered features

  • Anomaly detection in time series
  • Forecast generation
  • Pattern recognition
  • Automated insights

Example:

function detect_anomalies(series::Vector{Float64})::Vector{Int}
    # Use statistical models or ML to identify outliers
    model = load_anomaly_detector()
    return model.predict(series)
end

9. Event Sourcing

Current: State-based storage

Future: Event log architecture

# Store events, not final state
events = [
    (timestamp=t1, event="series_fetched", data=...),
    (timestamp=t2, event="cache_hit", data=...),
    (timestamp=t3, event="formula_calculated", data=...)
]

Benefits:

  • Complete audit trail
  • Time travel (replay events)
  • Analytics on user behavior
  • Debugging

10. Multi-Region Deployment

Current: Single server instance

Future: Global deployment

┌──────────────┐  ┌──────────────┐  ┌──────────────┐
│   US East    │  │    Europe    │  │  Asia-Pac    │
│  (Primary)   │  │  (Replica)   │  │  (Replica)   │
└──────────────┘  └──────────────┘  └──────────────┘

Benefits:

  • Lower latency worldwide
  • High availability
  • Disaster recovery
  • Compliance (data residency)

11. Observability Stack

Current: Basic logging

Future: Full observability

Metrics:  Prometheus + Grafana
Logs:     ELK Stack (Elasticsearch, Logstash, Kibana)
Traces:   Jaeger / OpenTelemetry
Alerts:   PagerDuty / Opsgenie

Dashboards:

  • API latency percentiles (p50, p95, p99)
  • Cache hit rates
  • Error rates by source
  • User activity heatmaps

12. Plugin Architecture

Current: Hardcoded data sources

Future: Plugin system

# Load plugins dynamically
register_plugin(CustomDataSource(
    name="MyEconData",
    fetch_func=my_fetch_function,
    search_func=my_search_function
))

Benefits:

  • Community extensions
  • Private data sources
  • Custom formulas
  • Easy experimentation

13. Progressive Web App (PWA)

Current: Platform-specific add-ins

Future: Standalone PWA

Excel Add-in ─┐
              ├─→ Shared Core Logic (WebAssembly)
LibreOffice ──┤
              │
Standalone PWA┘

Benefits:

  • Works without Excel/LibreOffice
  • Mobile support
  • Offline capabilities
  • Cross-platform consistency

Roadmap Priority

Improvement Priority Complexity Impact Timeline
GraphQL API High Medium High Q2 2025
Redis Cache High Low High Q1 2025
WebSocket Medium Medium Medium Q3 2025
ML Integration Medium High Medium Q4 2025
Kubernetes Low High Low 2026
PWA Low Medium Medium 2026

Conclusion

This architecture documentation provides a comprehensive overview of the Excel Economic Number Tool's design, implementation, and future direction. The system is built on solid principles of separation of concerns, interface-based design, and fail-safe operation.

Key Architectural Strengths:

  1. Cross-platform abstraction enables single codebase for multiple platforms
  2. Julia backend provides high-performance numerical computing
  3. Persistent caching with intelligent TTL reduces API calls and improves performance
  4. Rate limiting and retry logic ensure resilient operation
  5. Modular design allows for easy extension and maintenance

For Developers:

For Operators:

This architecture is designed to be scalable, maintainable, and extensible while providing a reliable and performant experience for users conducting economic research.

Version: 2.0.0 Last Updated: 2025-11-22 Maintained by: Hyperpolymath Repository: github.com/Hyperpolymath/excel-economic-number-tool-