db/sensorHistoryDb.ts

Purpose

This module provides persistent local storage for historical core temperature sensor readings using Expo SQLite.

It is designed to:

• record timestamped temperature samples
• retain recent history within a fixed time window
• support historical trend analysis
• provide efficient local persistence for sensor telemetry

The module uses a lightweight SQLite-backed rolling history store optimized for append-heavy workloads.

Architecture Overview

The module implements:

• lazy singleton database initialization
• automatic schema creation
• persistent sensor history storage
• time-window-based retention
• async-safe SQLite access

The storage model is append-only with periodic pruning of expired rows.

Database Schema

Database

Table

Columns

Invariants

The module maintains the following invariants.

1. Database Access Uses a Singleton Promise

The database connection is memoized:

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

This guarantees:

• only one open operation
• shared database access
• reduced SQLite overhead
• safe concurrent initialization

If database opening fails, the singleton resets so initialization can retry later.

2. WAL Mode Is Always Enabled

During initialization the module enables SQLite Write-Ahead Logging:

PRAGMA journal_mode = WAL;

Benefits include:

• improved write performance
• safer concurrent access
• better background-task reliability
• reduced database lock contention

3. Schema Is Idempotent

Initialization always uses:

CREATE TABLE IF NOT EXISTS

This guarantees repeated initialization is safe.

Consumers may call initialization multiple times without side effects.

4. Stored Temperatures Must Be Finite Numbers

The helper:

assertFiniteNumber(...)

ensures recorded temperatures are valid finite numeric values.

Rejected inputs include:

• NaN
• Infinity
• -Infinity

Invalid values throw:

RangeError

before any database write occurs.

5. Historical Data Is Time-Bounded

The database only retains rows newer than:

KEEP_MS = 7 * 24 * 60 * 60 * 1000

which equals:

7 days

Older rows are automatically pruned after every insert.

This guarantees bounded storage growth.

6. Query Results Are Chronologically Ordered

History queries always return rows ordered ascending by timestamp:

ORDER BY timestamp ASC

Consumers can rely on:

• oldest entries appearing first
• stable chronological ordering
• direct compatibility with charting systems

Variants

Query Variants

Full History Query

When no timestamp filter is provided:

getCoreTempHistory()

returns all retained history.

Time-Filtered Query

When sinceMs is provided:

getCoreTempHistory(sinceMs)

only rows newer than or equal to the supplied timestamp are returned.

This supports:

• rolling chart windows
• incremental sync
• recent telemetry analysis

Retention Variants

The module uses time-based retention rather than count-based retention.

Current Retention Window

Eviction Strategy

Rows older than:

now - KEEP_MS

are deleted during every insert operation.

Exported Functions

initSensorHistoryDb()

export async function initSensorHistoryDb()

Initializes the sensor history database schema.

Responsibilities

• opens database connection
• enables WAL mode
• creates required tables

Schema Created

core_temp_history

Example

await initSensorHistoryDb();

Notes

• safe to call repeatedly
• idempotent initialization
• typically called during app startup

recordCoreTemp(tempC)

export async function recordCoreTemp(
  tempC: number
): Promise<void>

Records a new core temperature sample.

Parameters

Responsibilities

• validates numeric input
• timestamps the reading
• inserts new history row
• prunes expired history entries

Timestamp Behavior

Timestamps are generated automatically:

const now = Date.now();

Retention Behavior

After insertion:

DELETE FROM core_temp_history
WHERE timestamp < ?

removes expired rows.

Example

await recordCoreTemp(37.4);

Common Use Cases

• biometric telemetry
• sensor monitoring
• historical trend analysis
• thermal state tracking
• chart visualization

getCoreTempHistory(sinceMs?)

export async function getCoreTempHistory(
  sinceMs?: number
): Promise<{ timestamp: number; tempC: number }[]>

Returns persisted temperature history.

Parameters

Return Type

{
  timestamp: number;
  tempC: number;
}[]

Query Ordering

Results are returned oldest-first:

ORDER BY timestamp ASC

Response Mapping

Database rows:

temp_c

are transformed into API-facing fields:

tempC

for TypeScript consistency.

Example

Full History

const history = await getCoreTempHistory();

Recent History

const recent = await getCoreTempHistory(
  Date.now() - 60 * 60 * 1000
);

Example Result

[
  {
    timestamp: 1716500000000,
    tempC: 36.9
  },
  {
    timestamp: 1716500060000,
    tempC: 37.1
  }
]

Common Use Cases

• graph rendering
• rolling analytics windows
• anomaly detection
• health telemetry review

Internal Helpers

getDb()

async function getDb()

Lazy-loads and memoizes the SQLite database connection.

Responsibilities

• opens database only once
• caches connection promise
• resets cache on initialization failure

assertFiniteNumber(value, name)

Runtime validation helper that ensures numeric safety.

Throws:

RangeError

when invalid numeric values are supplied.

Retention Strategy

The module uses rolling time-based retention.

Configuration

KEEP_MS = 7 days

Characteristics

Concurrency Notes

Because all operations share a memoized database promise:

dbPromise

multiple concurrent operations safely reuse the same initialization flow.

This avoids:

• duplicate database opens
• initialization races
• redundant SQLite handles

Data Flow

Insert Flow

recordCoreTemp()
  → validate input
  → insert row
  → prune expired rows

Read Flow

getCoreTempHistory()
  → query rows
  → map temp_c → tempC
  → return chronological history

Example Integration

await initSensorHistoryDb();

await recordCoreTemp(36.8);

const history = await getCoreTempHistory(
  Date.now() - 24 * 60 * 60 * 1000
);