Data Architecture

---

Module: Tuner Component: Data Layer Version: 1.0.0-RELEASE Last Updated: October 26, 2025 Document Type: Data Architecture (Database Reference)

---

Table of Contents

1. Overview
2. MongoDB Schemas
3. MySQL Schemas
4. Snowflake Analytics
5. Redis Cache Strategy
6. Data Flows
7. Backup & Recovery

---

Overview

Multi-Database Strategy

AWS Tuner uses 4 databases, each optimized for specific data patterns:

Database	Purpose	Data Types	Scale
MongoDB	Recommendations, schedules, resource cache	Document store (flexible schema)	10M+ documents
MySQL	Accounts, users, audit logs	Relational data (ACID transactions)	100K+ rows
Snowflake	Cost analytics, historical trends	Data warehouse (time-series)	100GB+
Redis	API cache, session management	Key-value (in-memory)	10GB+

Database Selection Rationale

---

MongoDB Schemas

Database: tuner_db

Collection: recommendations

Purpose: Store generated recommendations

Schema:

{
  _id: ObjectId("507f1f77bcf86cd799439011"),
  id: "rec_abcd1234",  // UUID for external reference
  accountId: "123456789012",
  resourceId: "i-0abcd1234efgh5678",
  resourceName: "api-server-prod-3",
  resourceType: "EC2_INSTANCE",
  region: "us-east-1",

  // Categorization
  category: "OverProvisioned",  // Cleaner, OverProvisioned, Modernization, PotentialBenefits
  service: "EC2",
  factorKey: "OVER_PROVISIONED_EC2",
  riskLevel: "MEDIUM",  // LOW, MEDIUM, HIGH

  // Recommendation details
  description: "Maximum CPU utilisation of EC2 is 12%\nThe instance has been overprovisioned for the past 30 days",
  action: "Downsize EC2 instance type from m5.2xlarge to m5.large",
  message: "EC2 instance [i-0abcd1234efgh5678] is overprovisioned...",

  // Financial impact
  currentCost: 280.32,        // Monthly cost (USD)
  recommendedCost: 70.08,     // After optimization
  monthlySavings: 210.24,
  annualSavings: 2522.88,

  // Metadata (varies by recommendation type)
  metadata: {
    instanceType: "m5.2xlarge",
    cpu: 8,
    memory: 32768,
    cpuUtilization: 12,
    memoryUtilization: 25,
    recommendedInstanceType: "m5.large",
    tenancy: "default",
    architecture: "x86_64",
    platformDetails: "Linux/UNIX"
  },

  // State management
  status: "GENERATED",  // GENERATED, SNOOZED, IMPLEMENTED, DISMISSED
  snoozedUntil: null,
  snoozeReason: null,
  implementedBy: null,
  implementedAt: null,
  dismissedBy: null,
  dismissedAt: null,
  dismissReason: null,

  // Audit
  createdAt: ISODate("2025-10-26T10:00:00Z"),
  updatedAt: ISODate("2025-10-26T10:00:00Z"),
  generatedBy: "OverProvisionedEc2RecommendationJob"
}

Indexes:

db.recommendations.createIndex({ accountId: 1, status: 1, category: 1 });
db.recommendations.createIndex({ resourceId: 1 });
db.recommendations.createIndex({ createdAt: -1 });
db.recommendations.createIndex({ "metadata.instanceType": 1 });
db.recommendations.createIndex({ snoozedUntil: 1 }, { sparse: true });

Document Size: Typical 1-3 KB per recommendation

---

Collection: scheduler_configs

Purpose: Store resource schedule definitions

Schema:

{
  _id: ObjectId("507f1f77bcf86cd799439012"),
  id: "sched_abc123",
  name: "Dev Environment Schedule",

  // Schedule type
  type: "TAG_BASED",  // ACCOUNT, RESOURCE, TAG_BASED

  // Scope
  accountId: "123456789012",
  region: "us-east-1",

  // Tag-based rules (if type = TAG_BASED)
  tagRules: {
    Environment: "Dev",
    Scheduler: "Enabled"
  },

  // Resource-based rules (if type = RESOURCE)
  resourceIds: ["i-0abcd1234", "i-0efgh5678"],

  // Schedule definition
  schedule: {
    startTime: "07:00",    // 24-hour format
    stopTime: "19:00",
    timezone: "America/New_York",
    weekdays: [1, 2, 3, 4, 5],  // Monday-Friday (1-7)
    excludeDates: ["2025-12-25", "2026-01-01"]  // Holidays
  },

  // Resource types to schedule
  resourceTypes: ["EC2", "RDS"],

  // State
  status: "ACTIVE",  // ACTIVE, PAUSED, DELETED
  lastExecutionAt: ISODate("2025-10-26T07:00:00Z"),
  nextExecutionAt: ISODate("2025-10-27T07:00:00Z"),

  // Savings tracking
  projectedMonthlySavings: 3452.00,
  actualMonthlySavings: 3387.00,

  // Audit
  createdAt: ISODate("2025-01-10T10:00:00Z"),
  createdBy: "john.doe@company.com",
  updatedAt: ISODate("2025-10-26T08:00:00Z")
}

Indexes:

db.scheduler_configs.createIndex({ accountId: 1, status: 1 });
db.scheduler_configs.createIndex({ nextExecutionAt: 1 });
db.scheduler_configs.createIndex({ "tagRules.Environment": 1 });

---

Collection: ec2_resources

Purpose: Cache EC2 instance metadata for faster recommendation generation

Schema:

{
  _id: ObjectId("507f1f77bcf86cd799439013"),
  accountId: "123456789012",
  resourceId: "i-0abcd1234efgh5678",
  region: "us-east-1",

  // Resource info (embedded from AWS API)
  resourceInfo: {
    resourceId: "i-0abcd1234efgh5678",
    instanceName: "api-server-prod-3",
    instanceType: "m5.2xlarge",
    state: "running",
    launchTime: ISODate("2024-06-15T10:00:00Z"),

    // Specs
    cpu: 8,
    memory: 32768,
    architecture: "x86_64",
    platformDetails: "Linux/UNIX",
    tenancy: "default",

    // Network
    vpcId: "vpc-12345678",
    subnetId: "subnet-abcd1234",
    privateIpAddress: "10.0.1.50",
    publicIpAddress: "54.123.45.67",

    // Tags
    tags: {
      Name: "api-server-prod-3",
      Environment: "Production",
      Team: "Platform",
      CostCenter: "Engineering"
    },

    // Pricing
    odCostPerHour: 0.384,
    monthlyCost: 280.32
  },

  // Sync tracking
  lastSyncAt: ISODate("2025-10-26T06:00:00Z"),
  syncSource: "SyncEc2ResourcesJob"
}

Indexes:

db.ec2_resources.createIndex({ accountId: 1, region: 1 });
db.ec2_resources.createIndex({ resourceId: 1 });
db.ec2_resources.createIndex({ lastSyncAt: -1 });
db.ec2_resources.createIndex({ "resourceInfo.instanceType": 1 });

TTL Index (auto-delete stale data after 7 days):

db.ec2_resources.createIndex(
  { lastSyncAt: 1 },
  { expireAfterSeconds: 604800 }  // 7 days
);

---

Collection: rds_resources

Similar to ec2_resources but for RDS databases.

---

Collection: quartz_event_logs

Purpose: Track scheduler execution events

Schema:

{
  _id: ObjectId("507f1f77bcf86cd799439014"),
  scheduleId: "sched_abc123",
  accountId: "123456789012",
  resourceId: "i-0abcd1234",
  resourceType: "EC2",

  // Event details
  eventType: "STOP",  // START, STOP
  status: "SUCCESS",  // SUCCESS, FAILED, RETRYING

  // Execution
  executedAt: ISODate("2025-10-26T19:00:00Z"),
  duration: 2.5,  // seconds

  // Error handling
  error: null,
  retryCount: 0,

  // Cost tracking
  costSaved: 0.384,  // Cost saved for this event (hourly rate)

  createdAt: ISODate("2025-10-26T19:00:00Z")
}

Indexes:

db.quartz_event_logs.createIndex({ scheduleId: 1, executedAt: -1 });
db.quartz_event_logs.createIndex({ accountId: 1, resourceId: 1 });

---

MySQL Schemas

Database: tuner_schema

Table: accounts

Purpose: Store AWS account metadata

CREATE TABLE accounts (
  id VARCHAR(36) PRIMARY KEY,
  account_id VARCHAR(12) NOT NULL UNIQUE,
  account_name VARCHAR(255) NOT NULL,

  -- IAM Role
  role_arn VARCHAR(512) NOT NULL,
  external_id VARCHAR(255) NOT NULL,

  -- Regions
  regions JSON NOT NULL,  -- ["us-east-1", "us-west-2"]

  -- Status
  status ENUM('ACTIVE', 'INACTIVE', 'VERIFYING', 'ERROR') NOT NULL DEFAULT 'VERIFYING',
  last_sync_at TIMESTAMP NULL,

  -- Organization
  organization_id VARCHAR(36) NOT NULL,

  -- Audit
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by VARCHAR(255) NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_organization (organization_id),
  INDEX idx_status (status),
  INDEX idx_last_sync (last_sync_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---

Table: users

Purpose: User authentication and profile

CREATE TABLE users (
  id VARCHAR(36) PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,

  -- Profile
  first_name VARCHAR(100),
  last_name VARCHAR(100),

  -- Organization
  organization_id VARCHAR(36) NOT NULL,

  -- Roles
  roles JSON NOT NULL,  -- ["TUNER_USER", "TUNER_ADMIN"]

  -- MFA
  mfa_enabled BOOLEAN NOT NULL DEFAULT FALSE,
  mfa_secret VARCHAR(255),

  -- Status
  status ENUM('ACTIVE', 'INACTIVE', 'LOCKED') NOT NULL DEFAULT 'ACTIVE',
  last_login_at TIMESTAMP NULL,

  -- Audit
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_organization (organization_id),
  INDEX idx_email (email),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---

Table: audit_logs

Purpose: Audit trail of all user actions

CREATE TABLE audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,

  -- Who
  user_id VARCHAR(36) NOT NULL,
  user_email VARCHAR(255) NOT NULL,

  -- What
  action VARCHAR(255) NOT NULL,
  resource_type VARCHAR(100),
  resource_id VARCHAR(255),

  -- Details
  details JSON,

  -- Request
  ip_address VARCHAR(45),
  user_agent VARCHAR(500),
  request_id VARCHAR(36),

  -- When
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_user (user_id, created_at DESC),
  INDEX idx_resource (resource_type, resource_id),
  INDEX idx_action (action),
  INDEX idx_created (created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Retention: 7 years (compliance requirement)

---

Table: account_permissions

Purpose: RBAC - User access to specific accounts

CREATE TABLE account_permissions (
  id VARCHAR(36) PRIMARY KEY,
  user_id VARCHAR(36) NOT NULL,
  account_id VARCHAR(12) NOT NULL,

  -- Permissions
  can_view BOOLEAN NOT NULL DEFAULT TRUE,
  can_implement BOOLEAN NOT NULL DEFAULT FALSE,
  can_configure BOOLEAN NOT NULL DEFAULT FALSE,

  -- Audit
  granted_by VARCHAR(36) NOT NULL,
  granted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at TIMESTAMP NULL,

  INDEX idx_user_account (user_id, account_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  UNIQUE KEY unique_user_account (user_id, account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---

Snowflake Analytics

Database: CLOUDKEEPER_ANALYTICS

Table: COST_AND_USAGE_REPORTS

Purpose: AWS Cost & Usage Report (CUR) data

CREATE TABLE COST_AND_USAGE_REPORTS (
  -- Time dimension
  LINE_ITEM_USAGE_START_DATE TIMESTAMP_NTZ,
  LINE_ITEM_USAGE_END_DATE TIMESTAMP_NTZ,
  BILL_BILLING_PERIOD_START_DATE DATE,

  -- Account dimension
  LINE_ITEM_USAGE_ACCOUNT_ID VARCHAR(12),

  -- Resource dimension
  LINE_ITEM_RESOURCE_ID VARCHAR(512),
  PRODUCT_INSTANCE_TYPE VARCHAR(100),
  PRODUCT_REGION VARCHAR(50),

  -- Service dimension
  PRODUCT_PRODUCT_NAME VARCHAR(100),
  PRODUCT_SERVICE_CODE VARCHAR(100),

  -- Cost metrics
  LINE_ITEM_UNBLENDED_COST DECIMAL(18,6),
  LINE_ITEM_USAGE_AMOUNT DECIMAL(18,6),
  LINE_ITEM_USAGE_TYPE VARCHAR(200),

  -- Pricing
  PRICING_UNIT VARCHAR(50),
  PRICING_PUBLIC_ON_DEMAND_RATE DECIMAL(18,9),

  -- Tags
  RESOURCE_TAGS VARIANT,  -- JSON object

  -- Clustering
  CLUSTER BY (BILL_BILLING_PERIOD_START_DATE, LINE_ITEM_USAGE_ACCOUNT_ID)
);

Size: 100GB+ (millions of line items per month)

Queries (used by Tuner):

-- Scheduler savings validation
SELECT
  DATE_TRUNC('month', line_item_usage_start_date) AS month,
  line_item_resource_id,
  SUM(line_item_unblended_cost) AS actual_cost
FROM cost_and_usage_reports
WHERE line_item_usage_account_id = '123456789012'
  AND line_item_resource_id IN ('i-0abcd1234', 'i-0efgh5678')
  AND bill_billing_period_start_date >= '2025-01-01'
GROUP BY 1, 2;

-- Instance type cost analysis
SELECT
  product_instance_type,
  COUNT(*) AS instance_count,
  SUM(line_item_unblended_cost) AS total_cost
FROM cost_and_usage_reports
WHERE product_product_name = 'Amazon Elastic Compute Cloud'
  AND line_item_usage_account_id = '123456789012'
  AND bill_billing_period_start_date = DATEADD(month, -1, CURRENT_DATE())
GROUP BY 1
ORDER BY 3 DESC;

---

Redis Cache Strategy

Key Patterns

Key Pattern	Value Type	TTL	Purpose
rec:{accountId}:*	JSON	1 hour	Recommendation lists
pricing:{instanceType}:{region}	String	24 hours	AWS pricing data
session:{userId}	JSON	Session timeout	User sessions
metrics:{resourceId}:{date}	JSON	6 hours	CloudWatch metrics
rate_limit:{ip}:{endpoint}	Counter	1 minute	Rate limiting

Cache Examples

Recommendation List:

Key: rec:123456789012:cleaner
Value: {
  "recommendations": [...],
  "totalSavings": 48645,
  "count": 102,
  "cachedAt": "2025-10-26T12:00:00Z"
}
TTL: 3600 seconds

Pricing Data:

Key: pricing:m5.large:us-east-1
Value: "0.096"
TTL: 86400 seconds

Rate Limiting:

Key: rate_limit:54.123.45.67:/api/recommendations
Value: 87  (request count)
TTL: 60 seconds

---

Data Flows

Recommendation Generation Flow

For detailed 7-phase flow, see Architecture Overview

Scheduler Execution Flow

---

Backup & Recovery

MongoDB Backups

Strategy: Continuous backups with point-in-time recovery

Backup Schedule:
  - Full Backup: Daily at 2 AM UTC
  - Incremental Backup: Every 6 hours
  - Oplog Backup: Continuous (real-time)

Retention:
  - Daily Backups: 30 days
  - Weekly Backups: 12 weeks
  - Monthly Backups: 12 months

Recovery Time Objective (RTO): 1 hour
Recovery Point Objective (RPO): 15 minutes

MySQL Backups

Strategy: Binary log replication + snapshots

Backup Schedule:
  - Full Snapshot: Daily at 3 AM UTC
  - Binary Logs: Continuous replication

Retention:
  - Daily Snapshots: 7 days
  - Weekly Snapshots: 4 weeks
  - Monthly Snapshots: 12 months

RTO: 30 minutes
RPO: 5 minutes

Snowflake Backups

Strategy: Time Travel + Fail-safe

-- Snowflake automatic Time Travel (7 days)
SELECT *
FROM cost_and_usage_reports
AT(OFFSET => -86400);  -- 1 day ago

-- Fail-safe: Additional 7 days (Snowflake-managed)

Total Data Protection: 14 days