This module handles usage tracking and credit management for the speech synthesis API, with support for per-request sandbox/production environment filtering.

Column	Type	Description
id	INTEGER PRIMARY KEY	Auto-incrementing unique identifier
customer_id	TEXT NOT NULL	Customer ID from RevenueCat
character_count	INTEGER NOT NULL	Number of characters in the request
request_timestamp	TEXT NOT NULL	ISO timestamp when request was made
created_at	TEXT DEFAULT CURRENT_TIMESTAMP	Database insertion timestamp

Indexes:

• customer_id - For efficient customer lookups
• request_timestamp - For efficient date range queries

Column	Type	Description
id	INTEGER PRIMARY KEY	Auto-incrementing unique identifier
customer_id	TEXT NOT NULL	Customer ID from RevenueCat
source	TEXT NOT NULL	Grant source (free_grant, iap, admin, refund)
product_lookup_key	TEXT	RevenueCat product ID for purchases
revenuecat_tx_id	TEXT	Transaction ID for idempotency
characters_granted	INTEGER NOT NULL	Characters granted (negative for refunds)
price_usd	REAL	Price paid for the credits
granted_at	TEXT DEFAULT CURRENT_TIMESTAMP	Grant timestamp
created_at	TEXT DEFAULT CURRENT_TIMESTAMP	Database insertion timestamp
is_consolidated	BOOLEAN DEFAULT FALSE	Tracks alias consolidation
environment	TEXT DEFAULT 'PRODUCTION'	Environment (SANDBOX or PRODUCTION)

Indexes:

• customer_id - For efficient customer lookups
• revenuecat_tx_id (UNIQUE) - For idempotent webhook processing
• customer_id, is_consolidated - For alias consolidation queries

Column	Type	Description
id	INTEGER PRIMARY KEY	Auto-incrementing unique identifier
customer_id	TEXT NOT NULL	Customer ID from RevenueCat
usage_id	INTEGER	Foreign key to customer_usage_v1
characters_consumed	INTEGER NOT NULL	Non-expiring characters consumed
consumed_at	TEXT DEFAULT CURRENT_TIMESTAMP	Consumption timestamp

Indexes:

• customer_id - For efficient customer lookups

Creates the usage tracking table if it doesn't exist. Called automatically when the module is imported.

Records a usage event for a customer.

Returns the total character count used by a customer in the current calendar month.

Returns detailed usage history for a customer (useful for debugging or admin dashboards).

Monthly usage is calculated based on calendar months:

• Start: 1st day of the month at 00:00:00
• End: Last day of the month at 23:59:59.999

This means customers get a fresh allowance on the 1st of each month, regardless of when they subscribed.

The credit system supports per-request sandbox/production environment filtering:

• Request Configuration: Environment is specified per-request via:
  • X-Environment header (e.g., X-Environment: SANDBOX)
  • Query parameter (e.g., ?environment=sandbox)
  • Default: PRODUCTION when not specified
• Data Isolation: Sandbox and production credits are completely isolated
• Webhook Processing: All webhook events are stored with their actual environment from RevenueCat
• Query-Time Filtering: Environment filtering happens at query time based on the request's environment parameter

This allows for:

• Complete audit trail of all transactions
• Different clients using different environments simultaneously
• Production clients work by default without code changes
• Test devices can opt into sandbox mode on a per-request basis
• No global configuration changes needed for testing

• Indexes on customer_id and request_timestamp ensure fast queries
• Monthly usage queries use date range filtering for efficiency
• Table name includes version suffix (_v1) for easy schema migrations
• Environment filtering uses indexed columns for efficient queries