📖 Guide 09 — Database Schema and API Reference

What you’ll learn: Every DynamoDB table, every MySQL table, and every REST API endpoint in EPIC — with field descriptions and example data.

Section 1 — DynamoDB Tables (NoSQL)

DynamoDB is Amazon’s NoSQL database. Think of it as a big JSON store. Each item is a JSON object.

How DynamoDB works:

• Each table has a Primary Key (and optional Sort Key)
• Items with same PK but different SK are grouped
• For EPIC: most tables use {ResourceId, VersionId} as PK+SK (versioned storage)

Table 1: FleetTable

Purpose: Stores fleet configurations
Primary Key: FleetId (String)
Sort Key: VersionId (Number)

{
    "FleetId": "FORTRESSService-NA-X0",
    "VersionId": 5,
    "LatestVersionId": 5,
    "ServiceId": "FORTRESSService",
    "AuditMetadata": {
        "Message": "Updated host throughput",
        "User": "jsmith",
        "Timestamp": "2024-06-01T10:00:00Z"
    },
    "FleetConfiguration": {
        "ApolloName": "FORTRESSService_NA_X0",
        "Region": "us-east-1",
        "HostThroughPutTPM": 250,
        "BufferPercentage": 10,
        "CTFactor": 1.5,
        "MaxHostCount": 200,
        "HostType": "c5.2xlarge",
        "ASGProperties": [
            {
                "AutoScalingGroupName": "FORTRESSService/NA/X0/Prod/1a",
                "AvailabilityZone": "us-east-1a",
                "MaxInstance": 50,
                "MinInstance": 5,
                "DesiredCapacity": 50
            },
            {
                "AutoScalingGroupName": "FORTRESSService/NA/X0/Prod/1b",
                "AvailabilityZone": "us-east-1b",
                "MaxInstance": 50,
                "DesiredCapacity": 50
            }
        ],
        "ChildApolloEnvs": [
            { "ApolloName": "FORTRESSService_NA_X0_Child1", "SumDesiredCapacity": 10 }
        ],
        "EAPEnrolledStatus": true,
        "Homogeneous": true
    },
    "EventId": "PD2024",
    "FleetProjection": [
        {
            "TotalInputTPMProperties": {
                "TotalInputTPM": 50000,
                "TotalInputTPMLinkVerifiedByCustomer": true
            },
            "ProjectionType": "CloudTune"
        }
    ],
    "HostOrderStatuses": [
        {
            "HostsPendingApproval": {
                "us-east-1a": 20,
                "us-east-1b": 20,
                "us-east-1c": 10
            }
        }
    ]
}

Key fields explained:

• LatestVersionId — always equals the highest VersionId; DynamoDB stores ALL versions
• ApolloName — name used to look up this fleet in Apollo system
• HostThroughPutTPM — how many transactions per minute ONE host can handle
• CTFactor — CloudTune peak factor (how much traffic grows at peak vs BAU)
• BufferPercentage — safety buffer (10 = 10% extra hosts)
• ASGProperties — list of Auto Scaling Groups with their configuration

Table 2: ServiceTable

Purpose: Stores service configurations
Primary Key: ServiceId (String)
Sort Key: VersionId (Number)

{
    "ServiceId": "FORTRESSService",
    "VersionId": 3,
    "LatestVersionId": 3,
    "ServiceType": "Registered",
    "Owner": "fortress-owner@amazon.com",
    "LeaderAlias": "jsmith",
    "Fleet": [
        {
            "Region": "NA",
            "Fleets": ["FORTRESSService-NA-X0", "FORTRESSService-NA-X6"]
        },
        {
            "Region": "EU",
            "Fleets": ["FORTRESSService-EU-X0"]
        }
    ],
    "Philosophy": {
        "MaxHosts": 500,
        "MinHosts": 10,
        "CapacityStrategy": "PerformanceBased"
    },
    "OnboardingStatus": "Completed",
    "ServiceTier": "Tier1"
}

Table 3: EventTable

Purpose: Stores peak event configurations
Primary Key: EventId (String)
Sort Key: VersionId (Number)

{
    "EventId": "PD2024",
    "VersionId": 2,
    "LatestVersionId": 2,
    "EventName": "Prime Day 2024",
    "EventType": "Peak",
    "RegionList": ["us-east-1", "eu-west-1", "us-west-2"],
    "SPCOEventDatesByRegion": {
        "us-east-1": {
            "SPCOStartDate": "2024-05-15",
            "SPCOEndDate": "2024-07-20",
            "GameDayDates": ["2024-06-01", "2024-06-15"]
        },
        "eu-west-1": {
            "SPCOStartDate": "2024-05-15",
            "SPCOEndDate": "2024-07-20"
        }
    },
    "PeakStartDate": "2024-07-16",
    "PeakEndDate": "2024-07-17",
    "IsActive": true,
    "EmergentScaleStartDate": "2024-07-09"
}

Key field: EmergentScaleStartDate — after this date, HOTW treats all orders as emergent (urgent, Sev2 ticket opened)

Table 4: EventPlanTable ⭐ (Most Important for Milestones)

Purpose: Tracks milestone completion for each fleet × event
Primary Key: EventPlanId (String) = {EventId}#{FleetId}
Sort Key: VersionId (Number)

{
    "EventPlanId": "PD2024#FORTRESSService-NA-X0",
    "VersionId": 8,
    "LatestVersionId": 8,
    "FleetId": "FORTRESSService-NA-X0",
    "EventId": "PD2024",
    "ServiceId": "FORTRESSService",
    "EventMilestone": [
        {
            "EventMilestoneId": "GatherProjectionFromUpstream",
            "MilestoneCompletionStatus": "Completed",
            "MilestoneMessage": "All upstream projections verified",
            "PeakReadyStatus": "Green",
            "ETA": "2024-06-01",
            "LastUpdatedTimestamp": "2024-06-01T12:00:00Z"
        },
        {
            "EventMilestoneId": "HardwareOrder",
            "MilestoneCompletionStatus": "Completed",
            "MilestoneMessage": "[Automated] Hardware order placed: SPCO-789",
            "PeakReadyStatus": "Green",
            "SubMilestones": [
                {
                    "EventMilestoneId": "PlaceHardwareOrder",
                    "MilestoneCompletionStatus": "Completed",
                    "MilestoneMessage": "50 hosts ordered via HOTW"
                },
                {
                    "EventMilestoneId": "HardwareOrderApproval",
                    "MilestoneCompletionStatus": "Completed",
                    "MilestoneMessage": "Order approved by AWS"
                }
            ]
        },
        {
            "EventMilestoneId": "HardwareFulfillment",
            "MilestoneCompletionStatus": "InProgress",
            "MilestoneMessage": "1 of 4 orders pending",
            "PeakReadyStatus": "Yellow"
        },
        {
            "EventMilestoneId": "CommunicateTPMToDownstream",
            "MilestoneCompletionStatus": "NotStarted",
            "PeakReadyStatus": "Red"
        },
        {
            "EventMilestoneId": "UpdateThrottlingInSDC",
            "MilestoneCompletionStatus": "NotStarted",
            "PeakReadyStatus": "Red"
        }
    ],
    "EventDescaleMilestone": [
        {
            "EventMilestoneId": "GatherDescaleProjections",
            "MilestoneCompletionStatus": "NotStarted"
        }
    ],
    "AuditMetadata": {
        "Message": "HOTW placed hardware order",
        "User": "HOTWAutomation",
        "Timestamp": "2024-06-15T09:00:00Z"
    }
}

Milestone IDs (in order):

1. GatherProjectionFromUpstream — collect traffic projections
2. HardwareOrder — place SPCO hardware order
   • Sub: PlaceHardwareOrder
   • Sub: HardwareOrderApproval
3. HardwareFulfillment — hardware delivered
4. CommunicateTPMToDownstream — tell downstream services your TPM
5. UpdateThrottlingInSDC — configure Gizmo throttling

Status values: NotStarted, InProgress, Completed, PendingOnEPIC, PendingOnServiceOwner, Blocked

PeakReadyStatus: Red (not ready), Yellow (in progress), Green (ready)

Table 5: ProjectionsTable

Purpose: Stores traffic projections
Primary Key: ProjectionId (String) = {FleetId}#{EventId}#{ProjectionType}
Sort Key: VersionId (Number)

{
    "ProjectionId": "FORTRESSService-NA-X0#PD2024#Peak",
    "VersionId": 3,
    "FleetId": "FORTRESSService-NA-X0",
    "EventId": "PD2024",
    "ProjectionType": "Peak",
    "TotalInputTPM": 50000,
    "IsVerifiedByOwner": true,
    "CloudTuneProjection": 48000,
    "ManualOverride": 50000,
    "ProjectionSource": "Manual",
    "UpstreamProjections": [
        {
            "UpstreamServiceId": "OrderService",
            "UpstreamTPM": 20000,
            "IsVerified": true
        }
    ]
}

Table 6: SchemaTable

Purpose: Stores data schemas (for dimensional projections)
Primary Key: SchemaId (String)

Table 7: EventProfileTable

Purpose: Stores event profile templates (pre-configured event settings)

Table 8: ExceptionTable

Purpose: Stores buffer factor exception requests

{
    "ExceptionId": "EXCEPTION#FORTRESSService-NA-X0#PD2024",
    "FleetId": "FORTRESSService-NA-X0",
    "EventId": "PD2024",
    "ExceptionType": "BufferFactor",
    "RequestedValue": 1.3,
    "CurrentValue": 1.1,
    "BusinessReason": "30% traffic variance due to external APIs",
    "BusinessDrivers": ["ThirdPartyDependency"],
    "Status": "Approved",
    "RequestedBy": "owner@amazon.com",
    "ReviewedBy": "epic-team@amazon.com",
    "ReviewTimestamp": "2024-05-20T15:00:00Z"
}

Table 9: FleetLockTable

Purpose: Prevents concurrent HOTW runs for the same fleet
Primary Key: FleetId (String)

{
    "FleetId": "FORTRESSService-NA-X0",
    "LockTimestamp": "2024-06-15T09:00:00Z",
    "LockedBy": "HOTW-Run-5"
}

Table 10: BAUServiceDashboard

Purpose: BAU capacity tracking
Primary Key: ServiceId (String)

Section 2 — MySQL Tables

MySQL is used for HOTW operational data because it needs complex queries.

hotw_run Table

Tracks each HOTW automation run:

-- Create a run
INSERT INTO hotw_run (EventIndexId, RunStatus) VALUES (42, 'Running');
-- Returns RunId = 5

-- Update with order type  
UPDATE hotw_run SET OrderType = 'Standard' 
WHERE EventIndexId = 42 AND RunId = 5;

hotw_execution Table

Detailed results for each fleet × run:

asg_details Table

Latest ASG configuration per fleet:

asg_details_run Table

Snapshot of ASG state at time of a specific HOTW run (historical):

Same columns as asg_details plus:

• RunId — which run this snapshot was taken at
• RecommendedOverrideValue — how many hosts HOTW recommends for this ASG

capacity_override_details Table

SPCO capacity override records:

fulfillment_details Table

FMC hardware delivery tracking:

preferred_asg Table

User-configured preferred ASGs for HOTW:

hotw_dashboard Table

Summary view for the HOTW dashboard:

Section 3 — REST API Reference

Complete list of all API endpoints:

Service APIs

Fleet APIs

Event APIs

EventPlan APIs

HOTW APIs (Node.js)

HOTW APIs (Java - Triggers)

Projection APIs

Throttling APIs

Ticket APIs

Other APIs

Section 4 — Index Tables (MySQL)

DynamoDB uses string IDs but MySQL needs numeric indexes for efficient joins. EPIC maintains index mappings:

How it’s used in Node.js:

// FleetOperations.getFleetIndex("RIPE-NA")
// → SQL: SELECT FleetIndexId FROM fleet_index WHERE FleetId = "RIPE-NA"
// → Returns: 42

// Then MySQL query uses the number:
// SELECT * FROM hotw_execution WHERE FleetIndexId = 42

Section 5 — DynamoDB Access Patterns

DynamoDB is optimized for specific access patterns. EPIC uses:

1. Get latest version of a resource

// DynamoDB Query by PartitionKey + SortKey
await dynamoDB.query({
    TableName: 'FleetTable',
    KeyConditionExpression: 'FleetId = :fleetId AND VersionId = :latestVersion',
    ExpressionAttributeValues: {
        ':fleetId': fleetId,
        ':latestVersion': latestVersionId
    }
});

2. Get all versions (history)

// Query all items with same FleetId (sorted by VersionId)
await dynamoDB.query({
    TableName: 'FleetTable',
    KeyConditionExpression: 'FleetId = :fleetId',
    ExpressionAttributeValues: { ':fleetId': fleetId }
});

3. Batch get multiple EventPlans

// Get EventPlans for 10+ fleets at once
await dynamoDB.batchGet({
    RequestItems: {
        'EventPlanTable': {
            Keys: fleetIds.map(fleetId => ({
                EventPlanId: `${eventId}#${fleetId}`,
                VersionId: latestVersionId
            }))
        }
    }
});

4. Conditional update (optimistic locking)

// Only update if VersionId hasn't changed (prevents concurrent conflicts)
await dynamoDB.put({
    TableName: 'FleetTable',
    Item: { ...updatedFleet, VersionId: currentVersion + 1 },
    ConditionExpression: 'VersionId = :currentVersion',
    ExpressionAttributeValues: { ':currentVersion': currentVersion }
});