db/cpmDb.ts

Purpose

This module provides a lightweight persistence layer for storing and querying CPM (cost-per-mile / cost-per-thousand impressions) history using Expo SQLite.

It is responsible for:

• Initializing a local SQLite database
• Persisting timestamped CPM values
• Querying recent CPM history
• Enforcing bounded storage retention
• Providing structured logging for database operations

The module is designed to be safe for repeated initialization and concurrent usage across the application lifecycle.

Architecture Overview

The database layer uses:

• A single SQLite database file: weather_cache.db
• One table: cpm_history
• A lazy singleton database connection (dbPromise)
• Automatic schema initialization before reads/writes
• FIFO-style retention trimming based on timestamp order

Database Schema

Database

Table

Columns

Invariants

The module maintains the following invariants at all times.

1. Database Singleton

Only one async database connection is created and reused:

let dbPromise: Promise<SQLite.SQLiteDatabase> | null = null;

This prevents:

• Duplicate database handles
• Concurrent open operations
• Excess SQLite initialization

If opening the database fails, the singleton is reset to allow retry.

2. Schema Always Exists Before Access

All exported read/write operations call:

await initCpmDb();

before interacting with the database.

This guarantees the table exists before use.

3. Stored Values Must Be Finite Numbers

The helper:

assertFiniteNumber(...)

enforces that all numeric inputs are finite.

Rejected values include:

• NaN
• Infinity
• -Infinity

This invariant applies to:

• cpm
• timestamp
• query limits

4. Row Count Never Exceeds MAX_ROWS

The module enforces bounded retention:

const MAX_ROWS = 40;

After every insert:

1. Total rows are counted
2. Excess rows are computed
3. Oldest rows are deleted

Eviction order:

ORDER BY timestamp ASC, rowid ASC

This guarantees:

• The newest records are preserved
• Storage growth is capped
• Query performance remains predictable

5. Query Results Are Ordered Newest First

All fetch operations return rows sorted by:

ORDER BY timestamp DESC, rowid DESC

Consumers can rely on:

• index 0 being the newest record
• deterministic ordering for equal timestamps

Variants

Logging Variants

The module supports multiple log levels through:

type LogLevel = "INFO" | "DEBUG" | "WARN" | "ERROR";

Usage

Logging is routed through:

global.log?.(...)

and namespaced with:

[cpmDb]

Query Variants

Time-Filtered Queries

Queries support filtering by minimum timestamp:

WHERE timestamp >= ?

Bounded Queries

Consumers specify a maximum row count:

LIMIT ?

The limit is normalized to:

Math.max(1, Math.round(limit))

ensuring:

• minimum limit = 1
• fractional values are rounded

Exported Types

CpmRow

Represents a persisted CPM sample.

export type CpmRow = {
  cpm: number;
  timestamp: number;
};

Fields

Exported Functions

initCpmDb()

export async function initCpmDb(): Promise<void>

Initializes the SQLite schema if it does not already exist.

Responsibilities

• Opens the database connection
• Creates the cpm_history table
• Ensures idempotent setup

Typical Usage

Call once during application startup:

await initCpmDb();

Notes

• Safe to call repeatedly
• Automatically called by all read/write APIs

insertCpmRow(cpm, timestamp?)

export async function insertCpmRow(
  cpm: number,
  timestamp?: number
): Promise<void>

Inserts a CPM sample into the database.

Parameters

Responsibilities

• Validates numeric inputs
• Inserts a new row
• Enforces retention policy
• Removes oldest rows when exceeding MAX_ROWS

Retention Behavior

After insertion:

if totalRows > MAX_ROWS:
    delete oldest rows

Example

await insertCpmRow(12.45);

await insertCpmRow(18.22, Date.now());

Common Use Cases

• Recording CPM snapshots
• Analytics sampling
• Historical trend tracking
• Local offline caching

getRecentCpmRows(sinceTimestamp, limit)

export async function getRecentCpmRows(
  sinceTimestamp: number,
  limit: number
): Promise<CpmRow[]>

Returns recent CPM rows newer than a given timestamp.

Parameters

Return Value

Promise<CpmRow[]>

Rows are returned:

• newest first
• bounded by limit

Example

const rows = await getRecentCpmRows(
  Date.now() - 60_000,
  10
);

Example Result

[
  { cpm: 21.4, timestamp: 1716500000000 },
  { cpm: 20.8, timestamp: 1716499990000 }
]

Common Use Cases

• Chart rendering
• Trend analysis
• Recent analytics windows
• Dashboard visualizations

Internal Helpers

getDb()

Lazy-loads and memoizes the SQLite database connection.

Behavior

• Opens database only once
• Reuses the same promise
• Resets memoized state on failure

assertFiniteNumber(value, name)

Runtime validation helper for numeric safety.

Throws:

RangeError

when invalid numbers are provided.

describeError(error)

Normalizes unknown thrown values into readable log strings.

logCpmDb(levels, message)

Internal logging wrapper with module namespacing.

Error Handling Strategy

All exported APIs:

• wrap operations in try/catch
• emit structured logs
• rethrow original errors

This ensures:

• observability
• upstream error visibility
• no silent database failures

Retention Strategy

The database uses a fixed-size rolling history buffer.

Current Configuration

MAX_ROWS = 40

Eviction Policy

Oldest rows are deleted first:

ORDER BY timestamp ASC, rowid ASC

Benefits

• Constant storage growth
• Predictable memory usage
• Fast recent-history queries
• Reduced local storage pressure

Concurrency Notes

Because the module memoizes a single async database connection:

dbPromise

multiple concurrent calls safely share the same database initialization flow.

This avoids race conditions during startup initialization.

Example Integration

await initCpmDb();

await insertCpmRow(15.2);

const recent = await getRecentCpmRows(
  Date.now() - 5 * 60 * 1000,
  20
);