Designing Metered Usage-Based SaaS Billing: MySQL & TypeScript

1. Introduction: The Complexity of Metered SaaS Billing

The software industry has largely outgrown flat, seat-based subscription tiers. Powered by the proliferation of specialized API services and generative AI tools, modern enterprise systems rely on usage-based and hybrid-AI payment structures. For example, a document platform like PDFaiGen does not charge merely per user; instead, it meters consumption based on specific, high-cost activities like raw LLM input/output tokens processed and PDF page generation tasks.

To support this shifting model, engineers must build a highly resilient, low-latency metered billing architecture. Unlike a standard e-commerce checkout flow, a metered engine must handle hundreds of events per second per tenant without choking the database or degrading user experience. The primary challenges in designing a reliable metered engine include:

• High-Frequency Ingestion: Collecting, parsing, and storing usage events without inducing high database write amplification.

• Race Conditions: Concurrent API calls attempting to update remaining credits or usage balances simultaneously.

• Double-Billing: Network failures, retry policies, and out-of-order queue events causing the same metric payload to be recorded twice.

• Accurate Multi-Tenant Rollups: Aggregating usage statistics across thousands of separate organizations in real-time, matching those rollups against complex billing-period boundaries.

A poorly constructed system causes revenue leakage or incorrect billing statements—both of which destroy customer trust. Building an immutable ledger to track usage events ensures absolute reproducibility and auditability, making the billing engine a solid asset for enterprise-grade SaaS platforms.

2. The High-Level Architecture: Event Ingestion to Ledger

A reliable saas multi tenant billing architecture must decouples event generation from transaction processing. Writing directly to the primary transactional database synchronously on every API gateway hit creates severe bottlenecking and exposes the core application database to Denial of Service (DoS) risks during traffic surges.

The proposed workflow uses a microservices pattern on Google Cloud Platform (GCP) to isolate and secure each ingestion stage:

1. Ingestion Layer: When a tenant performs an action (e.g., executing an AI search pipeline or processing an OCR scan), the client agent or edge router transmits an HTTP POST request containing a unique client-generated payload. A lightweight API gateway deployed to GCP Cloud Run processes this request, validating the tenant identity and authorization token.

2. Buffering & Deduplication: Instead of processing the event immediately, Cloud Run publishes the payload to Google Cloud Pub/Sub. Pub/Sub acts as an elastic buffer, isolating ingestion from database writes. This decoupled queue model protects downstream processes from sudden throughput spikes. A pull subscription handles delivery to a fleet of ingestion workers running on Cloud Run, which manage gcp usage tracking and enforce exactly-once delivery guarantees.

3. Storage & Rollup Engine: The ingestion workers run in NodeJS/TypeScript. They batch individual events, deduplicate them against an in-memory Redis cluster (GCP Memorystore), and write the deduplicated events directly to Cloud SQL (MySQL 8.x). Instead of updating mutable balances, these events are stored in an append-only transaction ledger. Asynchronously, a worker processes these events into hourly and daily summary records to keep aggregation queries fast.

To downstream these analytical datasets to other external warehouses or operational monitoring dashboards, engineers can deploy a change data capture model. To explore high-throughput data replication strategies, see Architecting High-Throughput CDC Pipelines: MySQL, Kafka, TypeScript.

3. Database Schema Design: The Immutable Usage Ledger

The most common mistake when building billing databases is using a mutable balance column in a tenant table that is decremented on every usage event. This approach creates heavy lock contention on row updates, causes deadlocks, and removes the audit trail needed to resolve billing discrepancies.

The core principle of a solid metered billing ledger is immutability. Balance state is a derived value calculated by summing an append-only stream of event records within a specific billing window. To make this architecture fast and scalable under heavy workloads, we split database storage into three core tables: tenants/subscriptions, the raw usage events ledger, and pre-computed usage rollups. Here is the optimized schema layout:

CREATE TABLE tenants (
    tenant_id VARCHAR(36) PRIMARY KEY,
    company_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE subscriptions (
    subscription_id VARCHAR(36) PRIMARY KEY,
    tenant_id VARCHAR(36) NOT NULL,
    tier_name VARCHAR(50) NOT NULL,
    billing_period_start TIMESTAMP NOT NULL,
    billing_period_end TIMESTAMP NOT NULL,
    token_allowance BIGINT NOT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id),
    INDEX idx_tenant_period (tenant_id, billing_period_start, billing_period_end)
) ENGINE=InnoDB;

CREATE TABLE usage_events (
    event_id VARCHAR(64) PRIMARY KEY, -- Idempotency key from client/edge
    tenant_id VARCHAR(36) NOT NULL,
    metric_name VARCHAR(50) NOT NULL,  -- e.g., 'llm_tokens', 'api_calls', 'pdf_pages_processed'
    quantity INT NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_metric_time (tenant_id, metric_name, timestamp)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(timestamp)) (
    PARTITION p2023_11 VALUES LESS THAN (TO_DAYS('2023-12-01')),
    PARTITION p2023_12 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

CREATE TABLE usage_rollups (
    tenant_id VARCHAR(36) NOT NULL,
    metric_name VARCHAR(50) NOT NULL,
    rollup_window_start TIMESTAMP NOT NULL,
    total_quantity BIGINT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (tenant_id, metric_name, rollup_window_start),
    INDEX idx_rollup_window (rollup_window_start)
) ENGINE=InnoDB;

By partitioning the usage_events table by range on the timestamp column, historical data older than 90 days can be quickly dropped or moved to cold archival storage using high-speed partition-level queries rather than resource-heavy DELETE queries. To maintain peak performance, a company scaling their partition limits may want to hire a MySQL developer who specializes in high-volume transaction isolation and range partitioning.

4. Building the Ingestion & Accumulation Engine in TypeScript

To safely handle incoming event spikes, your TypeScript ingestion worker must process events in batches while guaranteeing absolute idempotency. This is done through a multi-tier deduplication pipeline:

1. In-Memory Cache (First Check): The system uses a fast key-value lookup in Redis with an absolute idempotency key generated by the client edge (such as tenantId:sha256(payload)).

2. Database-Level Isolation (Second Check): Because Redis keys can expire or the cache can fail over, the SQL tier serves as the ultimate source of truth. We use INSERT IGNORE in batch writes to automatically skip events with duplicate keys, preventing double-billing during system retries.

Here is an enterprise-ready implementation of the ingestion pipeline in TypeScript:

import { Pool } from 'mysql2/promise';
import Redis from 'ioredis';

interface UsageEvent {
  eventId: string;
  tenantId: string;
  metricName: string;
  quantity: number;
  timestamp: string;
}

export class UsageIngestionEngine {
  constructor(private dbPool: Pool, private redisClient: Redis) {}

  async processEventsBatch(events: UsageEvent[]): Promise<{ processed: number; duplicates: number }> {
    let duplicates = 0;
    let processed = 0;

    // 1. Filter out duplicates using Redis sliding window cache (First Line of Defense)
    const pipeline = this.redisClient.pipeline();
    for (const event of events) {
      pipeline.set(`dedup:${event.eventId}`, '1', 'EX', 86400, 'NX');
    }
    const cacheResults = await pipeline.exec();

    const uniqueEvents: UsageEvent[] = [];
    events.forEach((event, idx) => {
      const isNew = cacheResults?.[idx]?.[1] === 'OK';
      if (isNew) {
        uniqueEvents.push(event);
      } else {
        duplicates++;
      }
    });

    if (uniqueEvents.length === 0) {
      return { processed, duplicates };
    }

    // 2. Perform Batch Insert with INSERT IGNORE to ensure SQL-level idempotency
    const query = `
      INSERT IGNORE INTO usage_events (event_id, tenant_id, metric_name, quantity, timestamp)
      VALUES ?
    `;
    
    const values = uniqueEvents.map(event => [
      event.eventId,
      event.tenantId,
      event.metricName,
      event.quantity,
      new Date(event.timestamp)
    ]);

    const [result] = await this.dbPool.query<any>(query, [values]);
    const sqlInsertedRows = result.affectedRows || 0;
    
    processed = sqlInsertedRows;
    duplicates += (uniqueEvents.length - sqlInsertedRows);

    // 3. Increment memory rollups asynchronously in Redis to keep instant telemetry
    const rollupPipeline = this.redisClient.pipeline();
    for (const event of uniqueEvents) {
      const roundedHour = new Date(event.timestamp).setMinutes(0, 0, 0);
      const rollupKey = `rollup:${event.tenantId}:${event.metricName}:${roundedHour}`;
      rollupPipeline.incrby(rollupKey, event.quantity);
      rollupPipeline.expire(rollupKey, 172800); // 48-hour life
    }
    await rollupPipeline.exec();

    return { processed, duplicates };
  }
}

To safely run asynchronous pipeline tasks, handle massive data processing, and design fault-tolerant applications, you may want to hire a TypeScript developer who is skilled in building durable microservices and distributed messaging systems.

5. Preventing Edge Cases: Idempotency, Race Conditions, and Double-Billing

Managing high-volume financial logs requires strict protection against common edge cases. Outlined below are strategies to address typical vulnerabilities in a usage tracking environment:

Absolute Idempotency Key Design

Every event passed into the gcp usage tracking pipeline must contain a deterministic eventId. If an upstream client does not supply one, the gateway should reject the payload. A reliable key format is a deterministic UUID v5 built using a hash of the tenant ID, action name, precise timestamp, and a sequence identifier. This prevents double-billing even if a network timeout causes a client to send the identical payload multiple times.

Distributed Locks for Billing Resets

At the end of a billing period, a tenant’s usage stats must be aggregated, billed, and reset. To avoid double-charging, this transition must happen inside a critical section. We use Redis-based distributed locking (such as Redlock) to ensure only one billing worker can process a tenant's end-of-period rollup at a time:

const lockKey = `lock:billing:rollup:${tenantId}`;
const acquired = await redis.set(lockKey, workerId, 'NX', 'PX', 10000);

if (acquired) {
  try {
    // Execute transactional MySQL aggregation and invoice generation
  } finally {
    // Safely release key only if value matches workerId (prevents premature unlock)
    await redis.eval(releaseScript, 1, lockKey, workerId);
  }
}

Enforcing Usage Limits

SaaS models typically support two styles of limits:

• Soft Limits: When a tenant exceeds their plan threshold, the system triggers warning emails or webhook alerts, but billing operations and services continue to run.

• Hard Limits: API operations are immediately blocked when limits are reached. To enforce hard limits without adding query strain to the transactional database on every call, the API gateway can verify a cached allowance value stored in Redis. This value is updated by our async background rollup workers.

6. Exposing Usage Metrics to the Admin Dashboard

Customers expect transparent, real-time feedback on their platform usage. However, running complex SUM(...) aggregations over millions of rows in your transactional database on every dashboard reload will slow down the application.

To keep rendering times fast, aggregate queries should target the pre-compiled usage_rollups table rather than the raw usage_events ledger:

SELECT 
    rollup_window_start AS time_bucket,
    SUM(total_quantity) AS total_consumed
FROM usage_rollups
WHERE tenant_id = 'tenant-uuid-123'
  AND metric_name = 'llm_tokens'
  AND rollup_window_start >= '2023-11-01 00:00:00'
GROUP BY rollup_window_start
ORDER BY rollup_window_start ASC;

To scale read performance even further under high traffic, direct dashboard analytics to a dedicated, read-only database replica. To learn how to route read queries to replicas dynamically in NodeJS, read our guide on High-Availability Read-Replica Routing in TypeScript with MySQL on GCP.

7. Performance Comparison / Benchmarks

The table below compares the performance of a traditional mutable table design with the optimized append-only ledger pattern backed by a Redis sliding-window cache:

Write ThroughputDatabase CPU StrainHistorical Audit TrailDashboard Query Speed

8. Security Considerations & Production Best Practices

Because billing databases manage financial and customer consumption data, security and data integrity are top priorities:

• Payload Sanitization and JWT Validation: Validate all ingestion requests at your API gateway using JSON schemas. Check JWT parameters on every request to ensure callers cannot spoof tenant IDs or inject malicious SQL commands.

• VPC Service Controls: Run Cloud SQL instances inside a private VPC. Ingestion workers deployed on Cloud Run should connect to the database securely using the Cloud SQL Auth Proxy over private internal IP addresses rather than public internet endpoints.

• SQL Injection Defenses: Always use parameterized SQL queries in TypeScript. Avoid string concatenation when building SQL commands to block injection attacks.

9. Operational Checklist & Best Practices

Deploying a billing platform requires continuous monitoring and regular validation. Below is an operational checklist for production environments:

• Automated Ledger Reconciliation: Run a daily reconciliation cron job (e.g., via Cloud Scheduler) that compares aggregate database ledger records with the charge states in your payment provider (like Stripe). If any discrepancies are found, trigger alert webhooks immediately.

• Resilience to Network Outages: Ensure your GCP Cloud Pub/Sub subscriptions are configured with dead-letter topics. If an ingestion worker repeatedly fails to process an event due to a database outage, Pub/Sub will route the failed message to a dead-letter queue rather than losing it.

• Audit Log Tracking: Use Cloud Logging to record administrative balance overrides, billing tier updates, and system configuration adjustments to keep a clear history of all changes.

10. FAQ (Frequently Asked Questions)

Q1: Why partition the usage table instead of using a standard index?

A1: Indexing works well for fast query lookups, but deleting millions of expired historical records using standard DELETE statements causes significant transaction lock overhead and fragments your tables. Partitioning lets you drop old data instantly at the file system level with negligible impact on database performance.

Q2: How do you handle usage events that arrive late due to network lag?

A2: Events should always be recorded using their actual physical creation timestamp. If an event is written late, background reconciliation jobs must recalculate the affected hourly and daily rollups within the target billing period to ensure the accurate data is captured.

Q3: How do we prevent Redis cache data loss if a node crashes?

A3: Configure GCP Memorystore for Redis with High Availability (HA) enabled, which replicates data across multiple zones. Additionally, treat the MySQL database as the ultimate source of truth; if Redis experiences data loss, you can reconstruct cache states by querying the primary database.

11. Summary

Designing a reliable metered billing system requires balancing database performance with absolute data consistency. By combining GCP Cloud Pub/Sub, Redis caching, and an append-only MySQL ledger, engineers can build a system capable of handling thousands of events per second with an audit trail that guarantees accurate billing. This structure prevents double-billing, keeps user dashboards snappy, and easily accommodates multi-tenant plans and dynamic AI pricing models.

Code Snapshots

Immutable Ledger Database Schema

CREATE TABLE tenants (
    tenant_id VARCHAR(36) PRIMARY KEY,
    company_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE subscriptions (
    subscription_id VARCHAR(36) PRIMARY KEY,
    tenant_id VARCHAR(36) NOT NULL,
    tier_name VARCHAR(50) NOT NULL,
    billing_period_start TIMESTAMP NOT NULL,
    billing_period_end TIMESTAMP NOT NULL,
    token_allowance BIGINT NOT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id),
    INDEX idx_tenant_period (tenant_id, billing_period_start, billing_period_end)
) ENGINE=InnoDB;

CREATE TABLE usage_events (
    event_id VARCHAR(64) PRIMARY KEY, -- Idempotency key from client/edge
    tenant_id VARCHAR(36) NOT NULL,
    metric_name VARCHAR(50) NOT NULL,  -- e.g., 'llm_tokens', 'api_calls', 'pdf_pages_processed'
    quantity INT NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_metric_time (tenant_id, metric_name, timestamp)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(timestamp)) (
    PARTITION p2023_11 VALUES LESS THAN (TO_DAYS('2023-12-01')),
    PARTITION p2023_12 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

CREATE TABLE usage_rollups (
    tenant_id VARCHAR(36) NOT NULL,
    metric_name VARCHAR(50) NOT NULL,
    rollup_window_start TIMESTAMP NOT NULL,
    total_quantity BIGINT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (tenant_id, metric_name, rollup_window_start),
    INDEX idx_rollup_window (rollup_window_start)
) ENGINE=InnoDB;

Idempotent Event Ingestion Worker in TypeScript

import { Pool } from 'mysql2/promise';
import Redis from 'ioredis';

interface UsageEvent {
  eventId: string;
  tenantId: string;
  metricName: string;
  quantity: number;
  timestamp: string;
}

export class UsageIngestionEngine {
  constructor(private dbPool: Pool, private redisClient: Redis) {}

  async processEventsBatch(events: UsageEvent[]): Promise<{ processed: number; duplicates: number }> {
    let duplicates = 0;
    let processed = 0;

    // 1. Filter out duplicates using Redis sliding window cache (First Line of Defense)
    const pipeline = this.redisClient.pipeline();
    for (const event of events) {
      pipeline.set(`dedup:${event.eventId}`, '1', 'EX', 86400, 'NX');
    }
    const cacheResults = await pipeline.exec();

    const uniqueEvents: UsageEvent[] = [];
    events.forEach((event, idx) => {
      const isNew = cacheResults?.[idx]?.[1] === 'OK';
      if (isNew) {
        uniqueEvents.push(event);
      } else {
        duplicates++;
      }
    });

    if (uniqueEvents.length === 0) {
      return { processed, duplicates };
    }

    // 2. Perform Batch Insert with INSERT IGNORE to ensure SQL-level idempotency
    const query = `
      INSERT IGNORE INTO usage_events (event_id, tenant_id, metric_name, quantity, timestamp)
      VALUES ?
    `;
    
    const values = uniqueEvents.map(event => [
      event.eventId,
      event.tenantId,
      event.metricName,
      event.quantity,
      new Date(event.timestamp)
    ]);

    const [result] = await this.dbPool.query(query, [values]);
    const sqlInsertedRows = result.affectedRows || 0;
    
    processed = sqlInsertedRows;
    duplicates += (uniqueEvents.length - sqlInsertedRows);

    // 3. Increment memory rollups asynchronously in Redis to keep instant telemetry
    const rollupPipeline = this.redisClient.pipeline();
    for (const event of uniqueEvents) {
      const roundedHour = new Date(event.timestamp).setMinutes(0, 0, 0);
      const rollupKey = `rollup:${event.tenantId}:${event.metricName}:${roundedHour}`;
      rollupPipeline.incrby(rollupKey, event.quantity);
      rollupPipeline.expire(rollupKey, 172800); // 48-hour life
    }
    await rollupPipeline.exec();

    return { processed, duplicates };
  }
}

Relevant Content Suggestions

• High-Availability Read-Replica Routing in TypeScript with MySQL on GCP: Offloading analytical aggregate queries for usage dashboards from the primary transaction database using read replicas.

• Architecting High-Throughput CDC Pipelines: MySQL, Kafka, TypeScript: Streaming immutable billing ledger event streams downstream for financial auditing and third-party SaaS synchronization.

• Architecting Enterprise Copilot Agents: MCP in TypeScript: Contextualizing usage aggregation when executing expensive LLM-based autonomous agent tasks.