Skip to content

Technical Specifications

Overview

This document defines the technical specifications for CalcBridge, including API contracts, data models, performance benchmarks, and integration standards. Version 2.0 expands coverage to include reconciliation, insights, operational resilience, and formula engine APIs.

API Contract Specifications

API Design Principles

Principle Description
REST Architecture All APIs follow REST conventions with proper HTTP methods and status codes
JSON Format Request and response bodies use JSON with consistent formatting
Versioning API versioning via URL path (/api/v1/, /api/v2/)
Pagination Collection endpoints support cursor-based pagination
Filtering Query parameters for filtering, sorting, and field selection
HATEOAS Responses include hypermedia links for navigation
Idempotency POST/PUT operations support idempotency keys

Base URL Structure

Production:  https://api.calcbridge.io/api/v1/
Staging:     https://api.staging.calcbridge.io/api/v1/
Development: http://localhost:8000/api/v1/

Authentication

JWT Token Authentication

Header Value
Authorization Bearer <jwt_token>
Content-Type application/json
X-Tenant-ID <tenant_uuid>

Token Structure

{
  "sub": "user_uuid",
  "tenant_id": "tenant_uuid",
  "roles": ["analyst", "viewer"],
  "permissions": ["read:workbooks", "write:scenarios"],
  "exp": 1706227200,
  "iat": 1706140800
}

API Key Authentication

Header Value
X-API-Key <api_key>
Content-Type application/json

HTTP Status Codes

Code Meaning Usage
200 OK Successful GET, PUT, PATCH
201 Created Successful POST creating resource
204 No Content Successful DELETE
400 Bad Request Invalid request body or parameters
401 Unauthorized Missing or invalid authentication
403 Forbidden Insufficient permissions
404 Not Found Resource does not exist
409 Conflict Resource state conflict (duplicate, etc.)
422 Unprocessable Entity Validation errors
429 Too Many Requests Rate limit exceeded
500 Internal Server Error Unexpected server error
503 Service Unavailable Maintenance or overload

Core API Endpoints

Workbooks API

Method Endpoint Description
GET /workbooks List all workbooks
POST /workbooks Create empty workbook
POST /workbooks/upload Upload Excel workbook
GET /workbooks/{id} Get workbook details
PUT /workbooks/{id} Update workbook metadata
DELETE /workbooks/{id} Delete workbook
GET /workbooks/{id}/sheets List workbook sheets
GET /workbooks/{id}/sheets/{name}/data Get sheet data (paginated)
PUT /workbooks/{id}/sheets/{name}/data/{row} Update row
GET /workbooks/{id}/holdings Get holdings data
GET /workbooks/{id}/metrics Get aggregated metrics
POST /workbooks/{id}/recalculate Trigger recalculation
Workbook Upload Request
POST /api/v1/workbooks/upload
Content-Type: multipart/form-data

{
  "file": "<binary_data>",
  "name": "CLO_Portfolio_2026_01.xlsx",
  "mapping_profile_id": "uuid",
  "tags": ["monthly", "CLO-1"],
  "metadata": {
    "report_date": "2026-01-25",
    "source": "Bloomberg"
  }
}
Workbook Response
{
  "id": "550e8400-e29b-41d4-a716-446655440000",
  "name": "CLO_Portfolio_2026_01.xlsx",
  "status": "processed",
  "created_at": "2026-01-25T10:30:00Z",
  "updated_at": "2026-01-25T10:30:45Z",
  "sheets": [
    {
      "id": "sheet_uuid",
      "name": "Holdings",
      "row_count": 1500,
      "column_count": 45
    }
  ],
  "metrics": {
    "total_par": 500000000,
    "total_mv": 485000000,
    "holding_count": 150
  },
  "data_quality": {
    "score": 92.5,
    "issues": 3,
    "anomalies": 1
  },
  "links": {
    "self": "/api/v1/workbooks/550e8400-e29b-41d4-a716-446655440000",
    "holdings": "/api/v1/workbooks/550e8400-e29b-41d4-a716-446655440000/holdings",
    "compliance": "/api/v1/workbooks/550e8400-e29b-41d4-a716-446655440000/compliance",
    "reconciliation": "/api/v1/workbooks/550e8400-e29b-41d4-a716-446655440000/reconciliations"
  }
}

Compliance API

Method Endpoint Description
GET /workbooks/{id}/compliance Get all compliance test results
GET /workbooks/{id}/compliance/{test_id} Get specific test details
POST /workbooks/{id}/compliance/run Execute compliance tests
GET /workbooks/{id}/compliance/history Get historical results
GET /workbooks/{id}/compliance/trend Get compliance trends
GET /compliance/tests List available test definitions
POST /compliance/tests Create custom test definition
PUT /compliance/tests/{id} Update test definition
DELETE /compliance/tests/{id} Delete test definition
Compliance Test Results Response
{
  "workbook_id": "550e8400-e29b-41d4-a716-446655440000",
  "executed_at": "2026-01-25T10:31:00Z",
  "summary": {
    "total_tests": 52,
    "passed": 50,
    "failed": 1,
    "warning": 1
  },
  "tests": [
    {
      "id": "oc_senior",
      "name": "Senior OC Test",
      "category": "overcollateralization",
      "status": "passed",
      "current_value": 125.5,
      "trigger_level": 115.0,
      "target_level": 120.0,
      "cushion": 10.5,
      "cushion_pct": 8.75,
      "trend": "stable",
      "history": [
        {"date": "2026-01-25", "value": 125.5},
        {"date": "2025-12-25", "value": 124.8}
      ]
    },
    {
      "id": "warf_test",
      "name": "WARF Test",
      "category": "credit_quality",
      "status": "warning",
      "current_value": 2850,
      "trigger_level": 3000,
      "target_level": 2800,
      "cushion": 150,
      "cushion_pct": 5.0,
      "trend": "deteriorating"
    }
  ]
}

Scenarios API

Method Endpoint Description
GET /workbooks/{id}/scenarios List scenarios for workbook
POST /workbooks/{id}/scenarios Create new scenario
GET /scenarios/{id} Get scenario details
PUT /scenarios/{id} Update scenario
DELETE /scenarios/{id} Delete scenario
POST /scenarios/{id}/calculate Execute scenario calculations
GET /scenarios/{id}/compare Compare scenario to base
GET /scenarios/{id}/compliance Get compliance impact
POST /scenarios/{id}/trades Add trade to scenario
GET /scenarios/{id}/trades List trades in scenario
PUT /scenarios/{id}/trades/{trade_id} Update trade
DELETE /scenarios/{id}/trades/{trade_id} Remove trade
GET /scenarios/{id}/versions Get version history
POST /scenarios/{id}/export Export scenario
Create Scenario Request
POST /api/v1/workbooks/{workbook_id}/scenarios
{
  "name": "Add ABC Corp Position",
  "description": "Evaluate adding $5M ABC Corp senior secured",
  "changes": [
    {
      "type": "add_position",
      "data": {
        "issuer": "ABC Corporation",
        "cusip": "000361105",
        "par_amount": 5000000,
        "price": 98.5,
        "spread": 425,
        "rating_moodys": "B2",
        "rating_sp": "B",
        "maturity_date": "2029-06-15",
        "industry": "Healthcare"
      }
    },
    {
      "type": "remove_position",
      "data": {
        "cusip": "123456789",
        "par_amount": 3000000
      }
    }
  ]
}
Scenario Comparison Response
{
  "scenario_id": "scenario_uuid",
  "workbook_id": "workbook_uuid",
  "calculated_at": "2026-01-25T10:35:00Z",
  "comparison": {
    "metrics": [
      {
        "name": "Total Par",
        "base_value": 500000000,
        "scenario_value": 502000000,
        "delta": 2000000,
        "delta_pct": 0.4
      },
      {
        "name": "WAL",
        "base_value": 4.25,
        "scenario_value": 4.31,
        "delta": 0.06,
        "delta_pct": 1.41
      },
      {
        "name": "WARF",
        "base_value": 2850,
        "scenario_value": 2865,
        "delta": 15,
        "delta_pct": 0.53
      }
    ],
    "compliance": {
      "base": {
        "passed": 50,
        "failed": 1,
        "warning": 1
      },
      "scenario": {
        "passed": 51,
        "failed": 0,
        "warning": 1
      },
      "changes": [
        {
          "test_id": "ccc_bucket",
          "test_name": "CCC Bucket Test",
          "base_status": "failed",
          "scenario_status": "passed",
          "base_value": 8.2,
          "scenario_value": 7.4,
          "reason": "Reduced CCC exposure from 8.2% to 7.4%"
        }
      ]
    }
  }
}

Reconciliation API - NEW

Method Endpoint Description
POST /workbooks/{id}/reconcile Run reconciliation
GET /workbooks/{id}/reconciliations List reconciliation runs
GET /reconciliations/{id} Get reconciliation details
GET /reconciliations/{id}/variances Get all variances
PUT /reconciliations/{id}/variances/{variance_id} Update variance
POST /reconciliations/{id}/variances/{variance_id}/resolve Resolve variance
GET /reconciliations/{id}/report Generate reconciliation report
GET /reconciliation/tolerance Get tolerance settings
PUT /reconciliation/tolerance Update tolerance settings
GET /reconciliation/auto-rules Get auto-resolution rules
POST /reconciliation/auto-rules Create auto-resolution rule
Reconciliation Request
POST /api/v1/workbooks/{workbook_id}/reconcile
{
  "source_type": "trustee",
  "source_file": "<multipart file upload>",
  "source_format": "excel",
  "matching_fields": ["cusip", "isin"],
  "comparison_fields": ["par_amount", "price", "rating"],
  "tolerance_profile": "default"
}
Reconciliation Response
{
  "id": "recon_uuid",
  "workbook_id": "workbook_uuid",
  "source_type": "trustee",
  "executed_at": "2026-01-25T10:40:00Z",
  "status": "completed",
  "summary": {
    "total_positions": 150,
    "matched": 145,
    "unmatched_internal": 3,
    "unmatched_external": 2,
    "with_variances": 12,
    "within_tolerance": 8,
    "requires_review": 4
  },
  "variances": [
    {
      "id": "variance_uuid",
      "cusip": "000361105",
      "issuer": "ABC Corporation",
      "category": "par_variance",
      "internal_value": 5000000,
      "external_value": 5100000,
      "difference": -100000,
      "difference_pct": -1.96,
      "within_tolerance": false,
      "status": "open",
      "resolution": null
    },
    {
      "id": "variance_uuid_2",
      "cusip": "123456789",
      "issuer": "XYZ Corp",
      "category": "rating_variance",
      "internal_value": "B2",
      "external_value": "B1",
      "status": "open",
      "resolution": null
    }
  ],
  "links": {
    "report": "/api/v1/reconciliations/recon_uuid/report"
  }
}
Variance Resolution Request
POST /api/v1/reconciliations/{id}/variances/{variance_id}/resolve
{
  "resolution_type": "explained",
  "explanation": "Timing difference - trade settled T+1 in trustee system",
  "action": "no_action_required",
  "resolved_by": "user_uuid"
}

Insights API - NEW

Method Endpoint Description
GET /workbooks/{id}/insights Get insights for workbook
POST /workbooks/{id}/insights/generate Generate new insights
GET /workbooks/{id}/anomalies Get detected anomalies
GET /workbooks/{id}/data-quality Get data quality score
GET /workbooks/{id}/patterns Get identified patterns
GET /insights/{id} Get insight details
PUT /insights/{id}/dismiss Dismiss insight
GET /insights/trends Get trend analysis
GET /insights/correlations Get correlation analysis
Insights Response
{
  "workbook_id": "workbook_uuid",
  "generated_at": "2026-01-25T10:45:00Z",
  "data_quality": {
    "overall_score": 92.5,
    "dimensions": {
      "completeness": 98.0,
      "accuracy": 95.0,
      "consistency": 88.0,
      "timeliness": 100.0,
      "uniqueness": 96.0
    }
  },
  "insights": [
    {
      "id": "insight_uuid",
      "type": "anomaly",
      "severity": "warning",
      "title": "Unusual spread detected",
      "description": "Position XYZ Corp has spread 850bps, which is 3.2 standard deviations above portfolio average",
      "affected_positions": ["123456789"],
      "recommendation": "Verify spread is correctly captured from source",
      "created_at": "2026-01-25T10:45:00Z"
    },
    {
      "id": "insight_uuid_2",
      "type": "pattern",
      "severity": "info",
      "title": "Increasing CCC exposure trend",
      "description": "CCC bucket has increased from 6.5% to 7.8% over last 3 months",
      "trend_data": [
        {"date": "2025-11-25", "value": 6.5},
        {"date": "2025-12-25", "value": 7.2},
        {"date": "2026-01-25", "value": 7.8}
      ],
      "recommendation": "Monitor closely as approaching 8% covenant limit"
    }
  ],
  "anomalies": [
    {
      "field": "spread",
      "cusip": "123456789",
      "value": 850,
      "expected_range": [200, 500],
      "z_score": 3.2,
      "detection_method": "z_score"
    }
  ]
}

Dead Letter Queue API - NEW

Method Endpoint Description
GET /dlq List DLQ items
GET /dlq/stats Get DLQ statistics
GET /dlq/{id} Get DLQ item details
POST /dlq/{id}/retry Retry failed task
DELETE /dlq/{id} Delete DLQ item
POST /dlq/bulk-retry Bulk retry tasks
POST /dlq/bulk-delete Bulk delete tasks
DLQ List Response
{
  "items": [
    {
      "id": "dlq_uuid",
      "task_type": "workbook_parse",
      "task_id": "celery_task_id",
      "created_at": "2026-01-25T10:30:00Z",
      "failed_at": "2026-01-25T10:30:45Z",
      "retry_count": 3,
      "max_retries": 3,
      "error_type": "ParseError",
      "error_message": "Invalid Excel format in sheet 'Holdings'",
      "error_traceback": "...",
      "task_args": {
        "workbook_id": "workbook_uuid",
        "tenant_id": "tenant_uuid"
      },
      "status": "failed"
    }
  ],
  "stats": {
    "total": 5,
    "by_type": {
      "workbook_parse": 2,
      "calculation": 1,
      "reconciliation": 2
    },
    "oldest": "2026-01-24T08:00:00Z"
  },
  "pagination": {
    "cursor": "eyJpZCI6MTAwfQ==",
    "has_more": false,
    "total_count": 5
  }
}
DLQ Retry Request
POST /api/v1/dlq/{id}/retry
{
  "force": false,
  "modified_args": {}
}

Calculations API

Method Endpoint Description
POST /calculations/evaluate Evaluate single formula
POST /calculations/batch Evaluate multiple formulas
POST /calculations/validate Validate formula syntax
GET /calculations/translate Translate formula to Python
GET /calculations/dependencies Analyze formula dependencies
GET /calculations/functions List supported functions
GET /calculations/functions/{name} Get function details
POST /calculations/recalculate Trigger workbook recalculation
GET /calculations/jobs/{id} Get calculation job status
DELETE /calculations/jobs/{id} Cancel calculation job
Formula Evaluation Request
POST /api/v1/calculations/evaluate
{
  "formula": "=SUM(A1:A100) + IF(B1>0, C1, D1)",
  "context": {
    "A1:A100": [1, 2, 3, ...],
    "B1": 10,
    "C1": 100,
    "D1": 50
  },
  "options": {
    "precision": 6,
    "error_on_missing": true
  }
}
Formula Evaluation Response
{
  "result": 5150,
  "result_type": "number",
  "formula_parsed": {
    "type": "binary_op",
    "operator": "+",
    "left": {
      "type": "function_call",
      "name": "SUM",
      "args": [{"type": "range", "value": "A1:A100"}]
    },
    "right": {
      "type": "function_call",
      "name": "IF",
      "args": [...]
    }
  },
  "dependencies": ["A1:A100", "B1", "C1", "D1"],
  "execution_time_ms": 12
}

Mappings API

Method Endpoint Description
GET /mappings/profiles List mapping profiles
POST /mappings/profiles Create mapping profile
GET /mappings/profiles/{id} Get profile details
PUT /mappings/profiles/{id} Update profile
DELETE /mappings/profiles/{id} Delete profile
POST /mappings/auto-detect Auto-detect column mappings
GET /mappings/aliases List alias profiles
GET /mappings/aliases/{name} Get alias profile
GET /mappings/diagnostics Get mapping diagnostics
Mapping Profile Response
{
  "id": "profile_uuid",
  "name": "Bloomberg CLO Holdings",
  "description": "Mapping for Bloomberg CLO fund exports",
  "version": 3,
  "created_at": "2026-01-01T00:00:00Z",
  "updated_at": "2026-01-25T10:00:00Z",
  "mappings": {
    "cusip": {"source": "CUSIP_ID", "transform": null},
    "isin": {"source": "ISIN", "transform": null},
    "issuer_name": {"source": "Issuer Name", "transform": "trim"},
    "par_amount": {"source": "Face Value", "transform": "to_number"},
    "price": {"source": "Mid Price", "transform": "to_decimal"},
    "spread": {"source": "Spread (bps)", "transform": "to_integer"},
    "rating_moodys": {"source": "Moody's Rating", "transform": "normalize_rating"},
    "rating_sp": {"source": "S&P Rating", "transform": "normalize_rating"}
  },
  "alias_profile": "bloomberg_clo",
  "validation_rules": [
    {"field": "par_amount", "rule": "positive"},
    {"field": "price", "rule": "range", "min": 0, "max": 200}
  ]
}
Auto-Detect Request
POST /api/v1/mappings/auto-detect
Content-Type: multipart/form-data

{
  "file": "<excel file>",
  "sheet_name": "Holdings",
  "sample_rows": 100
}
Auto-Detect Response
{
  "detected_mappings": {
    "cusip": {
      "source_column": "CUSIP_ID",
      "confidence": 0.98,
      "sample_values": ["000361105", "123456789"]
    },
    "issuer_name": {
      "source_column": "Issuer Name",
      "confidence": 0.95,
      "sample_values": ["ABC Corporation", "XYZ Corp"]
    },
    "par_amount": {
      "source_column": "Face Value",
      "confidence": 0.92,
      "sample_values": [5000000, 3000000]
    }
  },
  "unmapped_columns": ["Custom Field 1", "Notes"],
  "unmapped_target_fields": ["industry_code", "country_code"],
  "schema_drift": {
    "new_columns": ["Custom Field 1"],
    "missing_columns": [],
    "type_changes": []
  }
}

Alerts API

Method Endpoint Description
GET /workbooks/{id}/alerts List alert configs
POST /workbooks/{id}/alerts Create alert config
PUT /alerts/{id} Update alert config
DELETE /alerts/{id} Delete alert config
GET /alerts/history Get alert execution history
GET /alerts/{id}/history Get specific alert history
POST /alerts/{id}/test Test alert configuration
Alert Configuration Request
POST /api/v1/workbooks/{workbook_id}/alerts
{
  "name": "OC Warning Alert",
  "description": "Alert when Senior OC drops below 120%",
  "condition": {
    "metric": "oc_senior",
    "operator": "less_than",
    "threshold": 120.0
  },
  "severity": "warning",
  "channels": ["email", "webhook"],
  "recipients": ["analyst@example.com"],
  "webhook_url": "https://hooks.slack.com/...",
  "cooldown_minutes": 60,
  "enabled": true
}

Reports API

Method Endpoint Description
GET /workbooks/{id}/reports List reports
POST /workbooks/{id}/reports/generate Generate report
GET /reports/{id} Get report details
GET /reports/{id}/download Download report file
DELETE /reports/{id} Delete report
GET /reports/templates List report templates
POST /reports/schedule Schedule report
Report Generation Request
POST /api/v1/workbooks/{workbook_id}/reports/generate
{
  "template": "trustee_monthly",
  "format": "pdf",
  "sections": ["summary", "compliance", "holdings", "transactions"],
  "filters": {
    "as_of_date": "2026-01-25"
  },
  "options": {
    "include_charts": true,
    "include_variance": true
  }
}

Health & Monitoring API

Method Endpoint Description
GET /health Basic health check
GET /health/ready Readiness probe
GET /health/live Liveness probe
GET /health/detailed Detailed health status
GET /metrics Prometheus metrics
Detailed Health Response
{
  "status": "healthy",
  "timestamp": "2026-01-25T10:50:00Z",
  "version": "2.0.0",
  "components": {
    "database": {
      "status": "healthy",
      "latency_ms": 5,
      "connection_pool": {
        "size": 20,
        "available": 18,
        "in_use": 2
      }
    },
    "cache": {
      "status": "healthy",
      "latency_ms": 1,
      "memory_usage_mb": 256
    },
    "celery": {
      "status": "healthy",
      "workers": 4,
      "queues": {
        "default": 0,
        "calculate": 2,
        "priority": 0
      }
    },
    "dlq": {
      "status": "healthy",
      "pending_items": 3,
      "oldest_item_age_hours": 2
    }
  }
}

WebSocket Specifications - NEW

Connection

wss://api.calcbridge.io/ws?token=<jwt_token>

Event Types

Event Direction Description
workbook.updated Server → Client Workbook data changed
compliance.changed Server → Client Compliance status changed
scenario.calculated Server → Client Scenario calculation completed
alert.triggered Server → Client Alert condition met
reconciliation.completed Server → Client Reconciliation finished
dlq.item_added Server → Client New DLQ item
subscribe Client → Server Subscribe to events
unsubscribe Client → Server Unsubscribe from events

Event Payload

{
  "type": "compliance.changed",
  "timestamp": "2026-01-25T10:51:00Z",
  "data": {
    "workbook_id": "workbook_uuid",
    "test_id": "oc_senior",
    "previous_status": "passed",
    "current_status": "warning",
    "current_value": 118.5,
    "trigger_level": 115.0
  }
}

Error Response Format

{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Request validation failed",
    "details": [
      {
        "field": "changes[0].data.par_amount",
        "message": "Par amount must be positive",
        "code": "POSITIVE_NUMBER_REQUIRED"
      }
    ],
    "request_id": "req_abc123",
    "timestamp": "2026-01-25T10:30:00Z",
    "documentation_url": "https://docs.calcbridge.io/errors/VALIDATION_ERROR"
  }
}

Error Codes

Code HTTP Status Description
AUTH_INVALID_TOKEN 401 Invalid or expired JWT
AUTH_INSUFFICIENT_PERMISSIONS 403 User lacks required permission
RESOURCE_NOT_FOUND 404 Requested resource does not exist
VALIDATION_ERROR 422 Request validation failed
RATE_LIMIT_EXCEEDED 429 Too many requests
PROCESSING_ERROR 500 Internal processing failure
SERVICE_UNAVAILABLE 503 Service temporarily unavailable
WORKBOOK_INVALID_FORMAT 422 Invalid Excel file format
WORKBOOK_TOO_LARGE 413 File exceeds size limit
MAPPING_INCOMPLETE 422 Required columns not mapped
CALCULATION_ERROR 500 Formula evaluation failed
RECONCILIATION_ERROR 500 Reconciliation processing failed
DLQ_RETRY_FAILED 500 DLQ retry attempt failed

Pagination

Request Parameters

Parameter Type Default Description
cursor string null Pagination cursor from previous response
limit integer 50 Items per page (max 200)
sort string created_at Sort field
order string desc Sort order (asc/desc)

Response Format

{
  "data": [...],
  "pagination": {
    "cursor": "eyJpZCI6MTAwfQ==",
    "has_more": true,
    "total_count": 1500
  }
}

Rate Limiting

Tier Requests/Second Requests/Hour Burst
Standard 10 5,000 50
Professional 50 25,000 200
Enterprise 200 100,000 500

Rate Limit Headers

X-RateLimit-Limit: 5000
X-RateLimit-Remaining: 4985
X-RateLimit-Reset: 1706227200
Retry-After: 60

Data Model Specifications

Entity Relationship Diagram

Tenant (1) ──────────────── (N) User
   │
   └─────────────────────── (N) Workbook
                                   │
                    ┌──────────────┼──────────────┬──────────────┐
                    │              │              │              │
                   (N)            (N)            (N)            (N)
                  Sheet        Scenario     ComplianceRun  Reconciliation
                    │              │              │              │
                   (N)            (N)            (N)            (N)
                 Holding    ScenarioChange  ComplianceResult  Variance

Core Entities

Tenant

Field Type Constraints Description
id UUID PK Unique identifier
name VARCHAR(255) NOT NULL Tenant display name
slug VARCHAR(100) UNIQUE, NOT NULL URL-safe identifier
status ENUM NOT NULL active, suspended, archived
tier ENUM NOT NULL standard, professional, enterprise
settings JSONB Tenant configuration
created_at TIMESTAMP NOT NULL Creation timestamp
updated_at TIMESTAMP NOT NULL Last update timestamp

User

Field Type Constraints Description
id UUID PK Unique identifier
tenant_id UUID FK, NOT NULL Parent tenant
email VARCHAR(255) UNIQUE, NOT NULL User email
name VARCHAR(255) NOT NULL Display name
role ENUM NOT NULL admin, manager, analyst, viewer, owner
status ENUM NOT NULL active, inactive, locked
permissions JSONB Feature-level permissions
last_login_at TIMESTAMP Last login timestamp
created_at TIMESTAMP NOT NULL Creation timestamp

Workbook

Field Type Constraints Description
id UUID PK Unique identifier
tenant_id UUID FK, NOT NULL Parent tenant
name VARCHAR(255) NOT NULL Workbook name
status ENUM NOT NULL pending, processing, processed, failed
file_path VARCHAR(500) NOT NULL Storage path
file_size BIGINT NOT NULL File size in bytes
checksum VARCHAR(64) NOT NULL SHA-256 checksum
metadata JSONB User-provided metadata
processing_stats JSONB Processing metrics
data_quality_score DECIMAL(5,2) Quality score 0-100
created_by UUID FK, NOT NULL Creator user
created_at TIMESTAMP NOT NULL Creation timestamp
updated_at TIMESTAMP NOT NULL Last update timestamp

Holding

Field Type Constraints Description
id UUID PK Unique identifier
workbook_id UUID FK, NOT NULL Parent workbook
sheet_id UUID FK, NOT NULL Source sheet
row_index INTEGER NOT NULL Source row
cusip VARCHAR(9) CUSIP identifier
isin VARCHAR(12) ISIN identifier
issuer_name VARCHAR(255) Issuer name
par_amount DECIMAL(18,2) Par/Face value
market_value DECIMAL(18,2) Market value
price DECIMAL(10,6) Current price
spread DECIMAL(10,4) Spread (bps)
rating_moodys VARCHAR(10) Moody's rating
rating_sp VARCHAR(10) S&P rating
rating_fitch VARCHAR(10) Fitch rating
maturity_date DATE Maturity date
industry VARCHAR(100) Industry classification
country VARCHAR(100) Country
is_defaulted BOOLEAN DEFAULT FALSE Default flag
raw_data JSONB NOT NULL All original fields
created_at TIMESTAMP NOT NULL Creation timestamp

Scenario (WhatIfScenario)

Field Type Constraints Description
id UUID PK Unique identifier
workbook_id UUID FK, NOT NULL Parent workbook
name VARCHAR(255) NOT NULL Scenario name
description TEXT Scenario description
status ENUM NOT NULL draft, calculated, archived
version INTEGER DEFAULT 1 Version number
created_by UUID FK, NOT NULL Creator user
calculated_at TIMESTAMP Last calculation time
results JSONB Calculation results
created_at TIMESTAMP NOT NULL Creation timestamp
updated_at TIMESTAMP NOT NULL Last update timestamp

ScenarioChange

Field Type Constraints Description
id UUID PK Unique identifier
scenario_id UUID FK, NOT NULL Parent scenario
change_type ENUM NOT NULL add_position, remove_position, modify_position, price_change, rating_change
target_cusip VARCHAR(9) Target CUSIP (for modify/remove)
change_data JSONB NOT NULL Change details
order_index INTEGER NOT NULL Execution order
created_at TIMESTAMP NOT NULL Creation timestamp

Reconciliation (TrusteeReconciliation) - NEW

Field Type Constraints Description
id UUID PK Unique identifier
workbook_id UUID FK, NOT NULL Target workbook
source_type ENUM NOT NULL trustee, geneva, bloomberg
source_file_path VARCHAR(500) Source file location
status ENUM NOT NULL pending, running, completed, failed
executed_at TIMESTAMP Execution timestamp
executed_by UUID FK User who triggered
summary JSONB Summary statistics
tolerance_profile VARCHAR(100) Applied tolerance profile
created_at TIMESTAMP NOT NULL Creation timestamp

Variance - NEW

Field Type Constraints Description
id UUID PK Unique identifier
reconciliation_id UUID FK, NOT NULL Parent reconciliation
cusip VARCHAR(9) Position CUSIP
issuer_name VARCHAR(255) Issuer name
category ENUM NOT NULL par, price, rating, identity, accrued
internal_value VARCHAR(100) Internal system value
external_value VARCHAR(100) External source value
difference DECIMAL(18,6) Numeric difference
difference_pct DECIMAL(10,4) Percentage difference
within_tolerance BOOLEAN Within configured tolerance
status ENUM NOT NULL open, explained, resolved, ignored
resolution_type ENUM explained, corrected, auto_resolved
resolution_notes TEXT Resolution explanation
resolved_by UUID FK User who resolved
resolved_at TIMESTAMP Resolution timestamp
created_at TIMESTAMP NOT NULL Creation timestamp

Insight - NEW

Field Type Constraints Description
id UUID PK Unique identifier
workbook_id UUID FK, NOT NULL Target workbook
type ENUM NOT NULL anomaly, pattern, trend, correlation
severity ENUM NOT NULL info, warning, critical
title VARCHAR(255) NOT NULL Insight title
description TEXT NOT NULL Detailed description
affected_positions JSONB List of affected CUSIPs
data JSONB Supporting data
recommendation TEXT Suggested action
status ENUM DEFAULT 'active' active, dismissed, resolved
created_at TIMESTAMP NOT NULL Creation timestamp
dismissed_at TIMESTAMP Dismissal timestamp
dismissed_by UUID FK User who dismissed

DLQItem - NEW

Field Type Constraints Description
id UUID PK Unique identifier
tenant_id UUID FK, NOT NULL Tenant context
task_type VARCHAR(100) NOT NULL Task type identifier
task_id VARCHAR(255) NOT NULL Celery task ID
task_args JSONB NOT NULL Task arguments
task_kwargs JSONB Task keyword arguments
error_type VARCHAR(255) NOT NULL Exception class name
error_message TEXT NOT NULL Error message
error_traceback TEXT Full traceback
retry_count INTEGER DEFAULT 0 Number of retries
max_retries INTEGER NOT NULL Max retry limit
status ENUM NOT NULL pending, retrying, failed, resolved
created_at TIMESTAMP NOT NULL Initial failure time
last_retry_at TIMESTAMP Last retry attempt
resolved_at TIMESTAMP Resolution time
resolved_by UUID FK User who resolved

Performance Benchmarks

Response Time Targets

Operation P50 Target P95 Target P99 Target Current
List Workbooks 50ms 100ms 200ms 45ms
Get Workbook 30ms 80ms 150ms 28ms
Upload Workbook (10MB) 3s 5s 8s 2.8s
List Holdings 100ms 200ms 400ms 95ms
Run Compliance Tests 1s 2s 3s 1.2s
Create Scenario 50ms 100ms 200ms 48ms
Calculate Scenario 1.5s 3s 5s 2.1s
Compare Scenarios 200ms 400ms 800ms 180ms
Run Reconciliation 30s 60s 90s 45s
Generate Insights 5s 10s 15s 8s
Evaluate Formula 20ms 50ms 100ms 25ms

Throughput Targets

Metric Target Current
API Requests/Second 1,000 1,200
Concurrent Users 500 600
Workbook Uploads/Minute 10 12
Compliance Runs/Minute 100 120
Calculations/Second 500 600
DLQ Retries/Minute 50 60

Resource Utilization

Resource Target Alert Threshold
CPU Utilization < 70% avg > 80%
Memory Utilization < 80% > 90%
Database Connections < 80% pool > 90%
Disk I/O < 70% capacity > 85%
DLQ Queue Depth < 100 > 500
Celery Queue Depth < 1000 > 5000

Integration Specifications

Geneva Integration

Aspect Specification
Protocol SFTP / HTTPS
Format XML (Geneva Portfolio Export)
Frequency Daily / On-demand
Authentication Certificate-based

Webhook Specifications

Event Types

Event Description Payload
workbook.uploaded Workbook upload completed workbook object
workbook.processed Processing completed workbook + metrics
compliance.completed Compliance run finished run + results summary
compliance.failed Test failure detected test details
compliance.warning Test approaching limit test details + trend
scenario.calculated Scenario calculation done scenario + comparison
reconciliation.completed Reconciliation finished summary + variance count
reconciliation.variance_detected New variance found variance details
alert.triggered Alert condition met alert + condition
dlq.item_added New DLQ item task details

Webhook Payload Format

{
  "event": "compliance.failed",
  "timestamp": "2026-01-25T10:30:00Z",
  "data": {
    "workbook_id": "uuid",
    "test_id": "oc_senior",
    "current_value": 114.5,
    "trigger_level": 115.0,
    "status": "failed"
  },
  "metadata": {
    "tenant_id": "uuid",
    "webhook_id": "uuid",
    "delivery_attempt": 1
  }
}

Retry Policy

Error Type Retry Backoff Max Attempts
5xx Errors Yes Exponential 3
429 Rate Limit Yes Respect Retry-After 3
4xx Client Errors No N/A 1
Network Errors Yes Exponential 3
DLQ Tasks Yes Exponential 3
Calculation Errors Yes Linear 2

API Versioning Strategy

Version Lifecycle

Version Status Support End Date
v1 Stable 2027-01-01
v2 Beta N/A

Breaking Change Policy

  • Breaking changes only in major versions
  • 12-month deprecation notice
  • Backward compatible changes in minor versions
  • Security fixes may break compatibility with notice

Deprecation Headers

Deprecation: true
Sunset: Sat, 01 Jan 2027 00:00:00 GMT
Link: </api/v2/workbooks>; rel="successor-version"

Last Updated: 2026-02-01 | Version 2.0.0