AWS Timestream for MCP Servers: Data Model, Write Path, Queries, and Choosing the Right Backend

Pattern 1 — The Data Model and Infrastructure Layer

Why time-series databases matter for MCP monitoring at scale

A monitoring system that checks 1,000 MCP endpoints every 60 seconds produces one probe record per second. Over 90 days that is 130 million rows. SQLite handles this without complaint on a single machine — append-only workloads with modest read traffic are SQLite's sweet spot. The problem is not write throughput; it is operational cost as the database grows.

In a relational database, 130 million unpartitioned rows cause three compounding problems: VACUUM runs become disruptive (PostgreSQL) or impossible (SQLite), index size grows proportionally to table size even with time-bounded queries, and query plans for "last 7 days" scans degrade as the optimizer re-estimates based on stale statistics. Managing these problems manually — partitioning, VACUUM schedules, index maintenance — takes engineering time that a serverless time-series database eliminates entirely.

Timestream's automatic two-tier storage solves exactly this problem. Recent data lives in the memory store (1 hour to 1 year, configurable, sub-millisecond query latency). Older data is automatically tiered to the magnetic store (up to 200 years, compressed, 10–500ms query latency, ~$0.03/GB/month). You never run VACUUM, manage partitions, or worry about index bloat. The probe worker appends records; Timestream handles the rest.

The MCP probe data model

Timestream records have three structural components: dimensions (categorical metadata, VARCHAR only, used in WHERE and GROUP BY), measure values (the numeric or string data you are measuring), and a time field (nanosecond precision). For MCP server health monitoring:

-- Dimensions: low-cardinality categorical identifiers (used in WHERE/GROUP BY)
server_slug     VARCHAR   -- "github-mcp", "stripe-mcp", etc.
region          VARCHAR   -- "us-east-1" (probe origin region)
protocol        VARCHAR   -- "stdio" | "sse" | "http"
probe_version   VARCHAR   -- "v2" (probe software version for compatibility tracking)

-- Multi-measure record values (one record = one probe cycle = five values)
response_ms     BIGINT    -- round-trip time in milliseconds
status_code     BIGINT    -- HTTP status (0 if connection refused)
is_up           VARCHAR   -- "true" | "false" (VARCHAR, not BOOLEAN, for SDK compatibility)
schema_checksum VARCHAR   -- SHA-256 of tools/list response (for drift detection)
error_class     VARCHAR   -- "" | "connection_refused" | "timeout" | "auth_error" | "json_error"

-- Time
check_time      TIMESTAMP -- nanosecond-precision epoch when probe ran

Two design decisions have long-term consequences and cannot be changed after the first write:

is_up as VARCHAR, not BOOLEAN. Multi-measure records (MeasureValueType.MULTI) use VARCHAR for the MULTI type in some SDK versions. Storing "true"/"false" as VARCHAR avoids a RejectedRecordsException caused by type mismatch on BOOLEAN fields in older SDK versions. Use COUNT_IF(is_up = 'true') in queries — the string comparison is explicit and does not depend on implicit type coercion.

server_slug as a dimension, not a measure. Timestream is designed to filter and group by dimensions efficiently. Server slug belongs in the WHERE clause of nearly every query; placing it as a dimension (not a measure value) tells the storage engine to index it appropriately. Dimensions are always VARCHAR — this is not a limitation for slugs.

CDK setup: database, table, and retention

The retention configuration is the most consequential CDK decision. Memory store retention drives cost linearly — every additional hour of memory store retention adds to the GB-hr billing. Magnetic store retention determines your SLA reporting window.

import * as timestream from 'aws-cdk-lib/aws-timestream';
import * as s3 from 'aws-cdk-lib/aws-s3';
import { Stack, StackProps, RemovalPolicy, Duration } from 'aws-cdk-lib';

export class McpTimestreamStack extends Stack {
  constructor(scope: Construct, id: string, props?: StackProps) {
    super(scope, id, props);

    const db = new timestream.CfnDatabase(this, 'McpProbeDb', {
      databaseName: 'mcp-probe-metrics',
    });

    // S3 bucket: captures records rejected from both memory and magnetic store
    const errorBucket = new s3.Bucket(this, 'TimestreamErrors', {
      removalPolicy: RemovalPolicy.RETAIN,
      lifecycleRules: [{ expiration: Duration.days(30) }],
    });

    const table = new timestream.CfnTable(this, 'McpProbeTable', {
      databaseName: db.databaseName!,
      tableName: 'probe-results',
      retentionProperties: {
        // 24h memory store: covers real-time dashboards and alert evaluation
        memoryStoreRetentionPeriodInHours: 24,
        // 93 days magnetic store: covers a full calendar quarter for SLA reports
        magneticStoreRetentionPeriodInDays: 93,
      },
      magneticStoreWriteProperties: {
        // Accept late arrivals (probe workers that crash and resume after >24h)
        enableMagneticStoreWrites: true,
        magneticStoreRejectedDataLocation: {
          s3Configuration: {
            bucketName: errorBucket.bucketName,
            objectKeyPrefix: 'rejected/',
            encryptionOption: 'SSE_S3',
          },
        },
      },
    });
    table.addDependency(db);
  }
}

The memory store calculation is worth running once: at 1,000 endpoints writing one multi-measure record per minute, the table produces approximately 1.44 million records per day. At roughly 200 bytes per multi-measure record, that is 288 MB/day or about 2 GB in 7 days. Seven days of memory store retention would cost ~$0.036 × 2 GB × 168 hours ≈ $12/week in memory store alone. Keeping memory store at 24–48 hours and pushing older queries to the magnetic store saves an order of magnitude in cost.

The critical importance of enableMagneticStoreWrites

The default Timestream table configuration silently rejects any record timestamped before the memory store window. A probe worker that is offline for 25 hours and then resumes — replaying its buffered probe results — loses all of those records without any error visible to the application. The worker's write call succeeds (HTTP 200); the records disappear.

Enabling enableMagneticStoreWrites: true routes late arrivals directly to the magnetic store. The S3 error bucket captures records that are rejected even from the magnetic store (schema conflicts, records older than the 93-day magnetic retention). Check this bucket periodically — S3 objects written here are the only observable signal for silent data quality problems in the probe pipeline.

When late arrivals land in the magnetic store, they are not visible in queries that target only the memory store window. Timestream SQL automatically spans both tiers when you use WHERE time BETWEEN ago(90d) AND now() — no special handling is needed in the query layer.

Cost model: Timestream vs. CloudWatch Custom Metrics

The cost argument for Timestream is clearest against CloudWatch Custom Metrics, which bills $0.30 per metric per month regardless of write frequency:

Timestream write pricing is approximately $0.50 per million records. Magnetic storage is approximately $0.03/GB/month. The cost advantage is not marginal — at 1,000 endpoints Timestream costs 600× less than CloudWatch Custom Metrics for the same data.

Pattern 2 — The Write Path

Multi-measure records: the only reasonable choice for monitoring

Timestream supports two record formats. Single-measure records store one metric per record: a probe cycle with five metrics (response_ms, status_code, is_up, schema_checksum, error_class) produces five separate records. Multi-measure records store all metrics for a single event in one record with a MeasureValues array.

For MCP monitoring, always use multi-measure records. The reasons are concrete:

• Single-measure uses 5× the write units for the same probe data, multiplying monthly write costs by 5.
• Single-measure stores correlated probe data in separate locations — queries that combine latency and status (the most common monitoring queries) require time-join operations across five rows instead of selecting five columns from one row.
• Single-measure returns five rows per probe cycle, multiplying the result set size for time-windowed queries and forcing client-side aggregation of related metrics.

// WRONG: single-measure — 5 records per probe, expensive to write and awkward to query
const single = [
  { MeasureName: 'response_ms', MeasureValue: '142', MeasureValueType: 'BIGINT', Time: ts },
  { MeasureName: 'is_up',       MeasureValue: 'true', MeasureValueType: 'VARCHAR', Time: ts },
  // ... three more records for status_code, schema_checksum, error_class
];

// RIGHT: multi-measure — 1 record per probe cycle, efficient and natural to query
import { MeasureValueType, TimeUnit } from '@aws-sdk/client-timestream-write';

const multi = {
  Dimensions: [
    { Name: 'server_slug', Value: 'github-mcp' },
    { Name: 'region',      Value: 'us-east-1' },
    { Name: 'protocol',    Value: 'sse' },
    { Name: 'probe_version', Value: 'v2' },
  ],
  MeasureName: 'probe',
  MeasureValueType: MeasureValueType.MULTI,
  MeasureValues: [
    { Name: 'response_ms',     Value: '142',    Type: MeasureValueType.BIGINT },
    { Name: 'status_code',     Value: '200',    Type: MeasureValueType.BIGINT },
    { Name: 'is_up',           Value: 'true',   Type: MeasureValueType.VARCHAR },
    { Name: 'schema_checksum', Value: 'a3f9b…', Type: MeasureValueType.VARCHAR },
    { Name: 'error_class',     Value: '',       Type: MeasureValueType.VARCHAR },
  ],
  Time: String(Date.now()),
  TimeUnit: TimeUnit.MILLISECONDS,
};

CommonAttributes: reduce payload for same-dimension batches

When a batch of 100 records all originate from the same probe region and run the same probe version, those two dimensions repeat identically across every record. The CommonAttributes field lets you move shared dimensions out of individual records — the service merges them before storing, saving approximately 30% payload size on typical probe batches:

import {
  TimestreamWriteClient,
  WriteRecordsCommand,
  type _Record,
} from '@aws-sdk/client-timestream-write';

const client = new TimestreamWriteClient({ region: 'us-east-1' });

async function flushBatch(
  results: ProbeResult[],
  probeRegion: string,
  probeVersion: string,
): Promise<void> {
  const records: _Record[] = results.map(r => ({
    // Only per-endpoint dimensions in the record; shared ones go in CommonAttributes
    Dimensions: [
      { Name: 'server_slug', Value: r.serverSlug },
      { Name: 'protocol',    Value: r.protocol },
    ],
    MeasureName: 'probe',
    MeasureValueType: 'MULTI',
    MeasureValues: [
      { Name: 'response_ms',     Value: String(r.responseMs),    Type: 'BIGINT' },
      { Name: 'status_code',     Value: String(r.statusCode),    Type: 'BIGINT' },
      { Name: 'is_up',           Value: String(r.isUp),          Type: 'VARCHAR' },
      { Name: 'schema_checksum', Value: r.schemaChecksum,        Type: 'VARCHAR' },
      { Name: 'error_class',     Value: r.errorClass,            Type: 'VARCHAR' },
    ],
    Time: String(r.checkTime.getTime()),
    TimeUnit: 'MILLISECONDS',
  }));

  await client.send(new WriteRecordsCommand({
    DatabaseName: 'mcp-probe-metrics',
    TableName:    'probe-results',
    Records:      records,
    CommonAttributes: {
      Dimensions: [
        { Name: 'region',       Value: probeRegion },
        { Name: 'probe_version', Value: probeVersion },
      ],
    },
  }));
}

If a record's own Dimensions array contains a dimension name that also appears in CommonAttributes.Dimensions, the record-level value wins. A multi-region probe worker can use this override to set a common region for most records while overriding it on records from a secondary region.

Handling RejectedRecordsException without data loss

RejectedRecordsException is the most mishandled Timestream error. It is a partial-failure response: records not listed in err.RejectedRecords[] were written successfully. Retrying the entire batch on this error re-writes the accepted records as duplicate entries and causes version conflicts — making the problem worse, not better.

async function writeBatchWithRejectionHandling(
  results: ProbeResult[],
): Promise<void> {
  const records = results.map(toTimestreamRecord);

  try {
    await client.send(new WriteRecordsCommand({
      DatabaseName: 'mcp-probe-metrics',
      TableName:    'probe-results',
      Records:      records,
    }));
  } catch (err: any) {
    if (err.name === 'RejectedRecordsException') {
      const rejected: Array<{ RecordIndex: number; Reason: string }> =
        err.RejectedRecords ?? [];

      for (const r of rejected) {
        const source = results[r.RecordIndex];
        // Common reasons:
        // "The record timestamp is outside the time range of the table's retention settings"
        //   → late arrival with enableMagneticStoreWrites disabled
        // "The record has multiple measure value types for the same measure name"
        //   → you changed a measure value type since the first write
        // "Version must be greater than the existing version for upsert"
        //   → duplicate record with stale version number
        console.error(
          `[ts-reject] slug=${source.serverSlug} ` +
          `time=${source.checkTime.toISOString()} reason=${r.Reason}`
        );
        await deadLetterQueue.send(JSON.stringify({ source, reason: r.Reason }));
      }

      // Non-rejected records were written — do NOT retry the entire batch
      console.info(
        `[ts] wrote ${records.length - rejected.length}/${records.length} records`
      );
    } else {
      throw err;
    }
  }
}

Queue-and-drain pattern for high-throughput deployments

At 50,000 monitored endpoints, the probe worker produces 50,000 records per 60-second window — 833 records/second sustained, with bursts during the first second of each probe cycle. Naively issuing one WriteRecordsCommand per probe result produces 50,000 individual API calls per minute and exceeds the default 1,000 records/second table quota immediately. The queue-and-drain pattern solves both problems:

class TimestreamBatchWriter {
  private queue: _Record[] = [];
  private flushTimer: ReturnType<typeof setInterval> | null = null;

  constructor(
    private client: TimestreamWriteClient,
    private dbName: string,
    private tableName: string,
    private maxBatchSize = 100,
    private flushIntervalMs = 1000,
  ) {
    this.flushTimer = setInterval(() => this.flush(), this.flushIntervalMs);
  }

  enqueue(record: _Record): void {
    this.queue.push(record);
    // Flush immediately when full — don't wait for the timer
    if (this.queue.length >= this.maxBatchSize) {
      setImmediate(() => this.flush());
    }
  }

  private async flush(): Promise<void> {
    if (this.queue.length === 0) return;
    const batch = this.queue.splice(0, this.maxBatchSize);
    try {
      await this.client.send(new WriteRecordsCommand({
        DatabaseName: this.dbName,
        TableName:    this.tableName,
        Records:      batch,
      }));
    } catch (err) {
      // Re-enqueue on transient errors (ThrottlingException, ServiceUnavailableException)
      this.queue.unshift(...batch);
      throw err;
    }
  }

  destroy(): void {
    if (this.flushTimer) clearInterval(this.flushTimer);
  }
}

The 1-second flush interval keeps queue depth bounded (at 50,000 endpoints writing at 833 records/second, the queue fills 8 batches per second — comfortably within the 1,000 records/second table quota). The immediate flush on maxBatchSize prevents memory buildup during burst periods.

IAM policy scoping for the write path

The probe worker needs exactly two permissions: timestream:WriteRecords on the specific table ARN and timestream:DescribeEndpoints on * (required for SDK endpoint discovery — the SDK calls this automatically on startup and caches the response for 1 hour). Nothing else:

{
  "Statement": [
    {
      "Sid": "TimestreamProbeWrite",
      "Effect": "Allow",
      "Action": ["timestream:WriteRecords"],
      "Resource": "arn:aws:timestream:us-east-1:123456789012:database/mcp-probe-metrics/table/probe-results"
    },
    {
      "Sid": "TimestreamEndpointDiscovery",
      "Effect": "Allow",
      "Action": ["timestream:DescribeEndpoints"],
      "Resource": "*"
    }
  ]
}

Scoping to the specific table ARN means a compromised probe worker cannot read historical data, write to other tables, or create/delete databases. If you run separate probe workers per customer (e.g. one per Team plan tenant), each worker gets its own IAM role scoped to its own table — a cross-tenant data injection requires compromising a different role, not just the shared service role.

Pattern 3 — The Query Layer and Backend Selection

TypeScript query client with pagination

The TimestreamQueryClient uses a separate endpoint from the write client — both require timestream:DescribeEndpoints for endpoint discovery, but they talk to different regional endpoints. For large result sets, NextToken pagination is mandatory; dashboard queries typically return under 100 rows (one per server per time bucket) and fit in a single page:

import {
  TimestreamQueryClient,
  QueryCommand,
  type QueryResponse,
  type Row,
  type ColumnInfo,
} from '@aws-sdk/client-timestream-query';

const tsQuery = new TimestreamQueryClient({ region: process.env.AWS_REGION ?? 'us-east-1' });

async function* queryAllRows(queryString: string): AsyncGenerator<Row[]> {
  let nextToken: string | undefined;

  do {
    const response: QueryResponse = await tsQuery.send(
      new QueryCommand({ QueryString: queryString, NextToken: nextToken })
    );
    yield response.Rows ?? [];
    nextToken = response.NextToken;
  } while (nextToken);
}

function rowToObject(row: Row, columns: ColumnInfo[]): Record<string, string> {
  return Object.fromEntries(
    columns.map((col, i) => [col.Name!, row.Data?.[i]?.ScalarValue ?? ''])
  );
}

Uptime percentage: 24-hour, 7-day, and 90-day windows

The COUNT_IF + NULLIF pattern is the standard Timestream uptime query. The NULLIF(COUNT(*), 0) guard prevents division-by-zero for newly registered endpoints with no probe history in the window. The ago() function evaluates at query execution time — it does not need to be replaced with a static timestamp, and it is not a constant that could be cached incorrectly:

-- 24-hour uptime percentage per server (fast — reads from memory store)
SELECT
  server_slug,
  ROUND(
    COUNT_IF(is_up = 'true') * 100.0 / NULLIF(COUNT(*), 0),
    2
  ) AS uptime_pct_24h,
  COUNT(*) AS total_probes,
  COUNT_IF(is_up = 'false') AS failed_probes
FROM "mcp-probe-metrics"."probe-results"
WHERE time BETWEEN ago(24h) AND now()
GROUP BY server_slug
ORDER BY uptime_pct_24h ASC;

-- 90-day uptime (queries magnetic store — slower, use scheduled query for SLA PDFs)
SELECT
  server_slug,
  ROUND(COUNT_IF(is_up = 'true') * 100.0 / NULLIF(COUNT(*), 0), 2) AS uptime_pct_90d
FROM "mcp-probe-metrics"."probe-results"
WHERE time BETWEEN ago(90d) AND now()
GROUP BY server_slug
ORDER BY server_slug;

Binned latency with bin() and APPROX_PERCENTILE

The bin(time, interval) function rounds each timestamp down to the start of its interval bucket — equivalent to PostgreSQL's date_trunc() or TimescaleDB's time_bucket(). The interval is lowercase (1h, 5m, 1d) — uppercase interval units cause a QueryException: Failed to parse query that is not obviously related to case sensitivity:

-- Hourly P50/P95/P99 for a specific server over the last 7 days
SELECT
  bin(time, 1h) AS hour,
  APPROX_PERCENTILE(response_ms, 0.50) AS p50_ms,
  APPROX_PERCENTILE(response_ms, 0.95) AS p95_ms,
  APPROX_PERCENTILE(response_ms, 0.99) AS p99_ms,
  MAX(response_ms) AS max_ms,
  COUNT(*) AS probe_count
FROM "mcp-probe-metrics"."probe-results"
WHERE
  server_slug = 'github-mcp'
  AND time BETWEEN ago(7d) AND now()
  AND is_up = 'true'  -- exclude failed probes from latency stats
GROUP BY bin(time, 1h)
ORDER BY hour ASC;

-- 5-minute granularity for the live dashboard (last 3 hours — memory store)
SELECT
  bin(time, 5m) AS bucket,
  AVG(response_ms) AS avg_ms,
  COUNT_IF(is_up = 'false') AS errors
FROM "mcp-probe-metrics"."probe-results"
WHERE
  server_slug = 'stripe-mcp'
  AND time BETWEEN ago(3h) AND now()
GROUP BY bin(time, 5m)
ORDER BY bucket ASC;

APPROX_PERCENTILE uses a t-digest algorithm accurate to ±1% relative error. It is significantly faster and cheaper than exact percentile calculations on large magnetic store scans. Use exact PERCENTILE_CONT only for compliance reports where the precise value is legally relevant — for operational dashboards, ±1% is indistinguishable from exact.

Schema drift detection with LAG()

AliveMCP stores a SHA-256 checksum of each server's tools/list response in every probe record. When consecutive successful probes produce different checksums, the server's tool schema has changed — tools were added, removed, or renamed. Timestream's LAG() window function detects these changes without a self-join:

-- Schema changes in the last 7 days (checksum change = tool schema drift event)
SELECT
  server_slug,
  time AS drift_detected_at,
  schema_checksum AS new_checksum,
  prev_checksum
FROM (
  SELECT
    server_slug,
    time,
    schema_checksum,
    LAG(schema_checksum) OVER (
      PARTITION BY server_slug
      ORDER BY time ASC
    ) AS prev_checksum
  FROM "mcp-probe-metrics"."probe-results"
  WHERE
    time BETWEEN ago(7d) AND now()
    AND is_up = 'true'  -- compare only successful probe checksums
)
WHERE
  prev_checksum IS NOT NULL
  AND schema_checksum != prev_checksum
ORDER BY server_slug, drift_detected_at ASC;

The is_up = 'true' filter is important: a probe that fails due to a connection error may return an empty or truncated tools/list response that produces a different checksum from the healthy response, causing false-positive drift events. Filtering to successful probes ensures the checksum comparison is between two complete, valid responses.

Downtime window extraction

Extracting the start and end timestamps of each downtime window — the data behind "was down for 23 minutes starting at 14:37 UTC" — requires identifying consecutive runs of is_up = 'false' records. Timestream SQL does not have a native SESSIONIZE function, but the transition-detection approach works with standard window functions:

WITH ordered AS (
  SELECT
    server_slug, time, is_up,
    ROW_NUMBER() OVER (PARTITION BY server_slug ORDER BY time) AS rn
  FROM "mcp-probe-metrics"."probe-results"
  WHERE server_slug = 'github-mcp'
    AND time BETWEEN ago(7d) AND now()
),
transitions AS (
  SELECT
    cur.server_slug, cur.time AS event_time, cur.is_up AS current_status,
    LAG(cur.is_up) OVER (PARTITION BY cur.server_slug ORDER BY cur.time) AS prev_status
  FROM ordered cur
),
down_starts AS (
  SELECT server_slug, event_time AS down_start
  FROM transitions
  WHERE current_status = 'false' AND (prev_status = 'true' OR prev_status IS NULL)
),
down_ends AS (
  SELECT server_slug, event_time AS down_end,
    LAG(event_time) OVER (PARTITION BY server_slug ORDER BY event_time) AS prior_down_start
  FROM transitions
  WHERE current_status = 'true' AND prev_status = 'false'
)
SELECT
  ds.server_slug,
  ds.down_start,
  de.down_end,
  date_diff('minute', ds.down_start, COALESCE(de.down_end, now())) AS duration_minutes
FROM down_starts ds
LEFT JOIN down_ends de
  ON ds.server_slug = de.server_slug
  AND de.down_end > ds.down_start
ORDER BY ds.down_start ASC;

The COALESCE(de.down_end, now()) handles ongoing outages: if the server is still down at query time, there is no end timestamp yet, and now() gives a live duration estimate. This is the query behind the "currently down for X minutes" label on AliveMCP status pages.

Scheduled queries: materializing SLA aggregates

Queries that scan 90 days of magnetic store data for all monitored servers are expensive in both cost and latency. For SLA dashboards and weekly digest emails, pre-materialize these aggregates into a Timestream derived table using Scheduled Queries. The scheduled query runs on a cron expression and writes its output directly to a second table; customer-facing queries read from the derived table, scanning kilobytes instead of gigabytes:

import * as timestream from 'aws-cdk-lib/aws-timestream';

const scheduledQuery = new timestream.CfnScheduledQuery(this, 'UptimeSummaryQuery', {
  queryString: `
    SELECT
      bin(time, 1h) AS interval_start,
      server_slug,
      ROUND(COUNT_IF(is_up = 'true') * 100.0 / NULLIF(COUNT(*), 0), 2) AS uptime_pct_1h,
      APPROX_PERCENTILE(response_ms, 0.95) AS p95_response_ms,
      COUNT(*) AS probe_count
    FROM "mcp-probe-metrics"."probe-results"
    WHERE time BETWEEN ago(2h) AND now()
    GROUP BY bin(time, 1h), server_slug
  `,
  scheduleConfiguration: {
    scheduleExpression: 'rate(1 hour)',
  },
  targetConfiguration: {
    timestreamConfiguration: {
      databaseName: 'mcp-probe-metrics',
      tableName:    'uptime-hourly-summary',
      timeColumn:   'interval_start',
      dimensionMappings: [
        { name: 'server_slug', dimensionValueType: 'VARCHAR' },
      ],
      multiMeasureMappings: {
        targetMultiMeasureName: 'summary',
        multiMeasureAttributeMappings: [
          { sourceColumn: 'uptime_pct_1h',    measureValueType: 'DOUBLE' },
          { sourceColumn: 'p95_response_ms',  measureValueType: 'DOUBLE' },
          { sourceColumn: 'probe_count',      measureValueType: 'BIGINT' },
        ],
      },
    },
  },
  notificationConfiguration: {
    snsConfiguration: { topicArn: alertTopic.topicArn },
  },
  scheduledQueryExecutionRoleArn: scheduledQueryRole.roleArn,
  errorReportConfiguration: {
    s3Configuration: {
      bucketName:     errorBucket.bucketName,
      objectKeyPrefix: 'scheduled-query-errors/',
      encryptionOption: 'SSE_S3',
    },
  },
});

The query scans ago(2h) AND now() while running every hour — the 1-hour overlap ensures no gaps even if a run is delayed by a few minutes. Dashboard queries reading from uptime-hourly-summary see the same data as a full magnetic store scan but at a tiny fraction of the cost.

One non-obvious requirement: the scheduled query execution role needs timestream:WriteRecords on the derived table ARN, and the derived table must exist before the scheduled query is enabled. If either condition is missing, the scheduled query fails silently — the only evidence is an error object written to the S3 error report bucket.

Grafana integration

The grafana-timestream-datasource plugin connects Grafana to Timestream using standard IAM credentials. Query panels use Timestream SQL directly — there is no PromQL compatibility layer. The plugin provides two macros: $__timeFilter (expands to time BETWEEN @start AND @end based on the dashboard time range) and $__interval (the current panel resolution step):

-- Example Grafana panel: P95 response time by server slug
SELECT
  bin(time, $__interval) AS time,
  server_slug,
  APPROX_PERCENTILE(response_ms, 0.95) AS p95_ms
FROM "mcp-probe-metrics"."probe-results"
WHERE
  $__timeFilter
  AND is_up = 'true'
GROUP BY bin(time, $__interval), server_slug
ORDER BY time ASC

Amazon Managed Grafana (AMG) has native Timestream integration without a plugin install — it appears as a built-in data source. AMG with IAM Identity Center authentication is the zero-infrastructure-management path to a live monitoring dashboard for an AWS-native MCP monitoring stack.

Backend selection: when Timestream is and isn't the right choice

Timestream is the right choice for a specific combination of requirements. The decision framework:

1. AWS stack + monitoring >500 endpoints: Timestream. CloudWatch Custom Metrics cost explodes above this scale (500 metrics × $0.30 = $150/month); Timestream stays under $1/month. This is not a marginal preference — it is a 150× cost difference.
2. Need to JOIN probe data with relational application data: TimescaleDB. Timestream has no cross-table join support. If you need to filter probe results by customer plan tier, join with an application user table, or correlate probe failures with deployment events stored in a relational database, TimescaleDB (PostgreSQL + hypertable extension) supports standard SQL JOINs. The operational trade-off: you manage a PostgreSQL server.
3. Already invested in Grafana + InfluxQL/Flux: InfluxDB Cloud. The InfluxDB line protocol is simpler than the Timestream SDK, the Flux query language has native monitoring primitives, and staying in an existing ecosystem is usually the right call. The trade-off: third-party SaaS dependency, less direct AWS IAM integration, less predictable cost model at scale.
4. Single machine, <500 endpoints: SQLite. Zero cost, zero ops, full SQL, trivially backed up with a file copy, no AWS dependency. Migrate to Timestream when you outgrow it — the migration path is well-defined (dual-write phase 1, backfill phase 2).
5. CloudWatch Alarms integration without a new data store: CloudWatch Custom Metrics. PutMetricData gets you native alarms, anomaly detection, and dashboard widgets without introducing a new database. Limit this to endpoints you actively alarm on (not all 1,000) — at $0.30/metric/month the ceiling is real.

Migration path: SQLite to Timestream

The migration has two phases. Phase 1 is dual-write: add a Timestream write path alongside the existing SQLite path without removing SQLite. This gives the new Timestream table real probe data for the last N days without a historical migration, and lets you validate that Timestream query results match SQLite before cutting over dashboards:

export async function recordProbeResult(result: ProbeResult): Promise<void> {
  // Existing SQLite write (synchronous, in-process)
  db.prepare(`
    INSERT INTO probe_results (server_slug, check_time, response_ms, is_up, status_code)
    VALUES (?, ?, ?, ?, ?)
  `).run(result.serverSlug, result.checkTime.toISOString(),
         result.responseMs, result.isUp, result.statusCode);

  // New Timestream write (async, best-effort during transition)
  probeBuffer.enqueue(toTimestreamRecord(result));
}

Phase 2 is backfill and cutover: export SQLite rows as Timestream records, batch-write them respecting the magnetic store write rate limit, then switch dashboards to Timestream queries and disable the SQLite write path. Historical data older than 93 days (the magnetic store retention) is dropped unless you extend retention before starting the backfill:

// Phase 2 backfill (run once, not on the probe worker)
import Database from 'better-sqlite3';
const db = new Database('./data.db');

const rows = db.prepare(`
  SELECT * FROM probe_results
  WHERE check_time > datetime('now', '-93 days')
  ORDER BY check_time ASC
`).all();

for (let i = 0; i < rows.length; i += 100) {
  const batch = rows.slice(i, i + 100).map(r => toTimestreamRecord(r));
  await writeBatch(batch);
  // Rate-limit to avoid ThrottlingException during bulk backfill
  if (i % 1000 === 0) await new Promise(res => setTimeout(res, 100));
}

Consolidated failure modes