db/weatherCacheDb.ts

Purpose

This module provides a persistent SQLite-backed weather forecast cache using Expo SQLite.

It is responsible for:

• storing forecast data by grid cell and forecast hour
• validating cached payload integrity
• supporting efficient point lookups
• tracking row access recency
• implementing LRU-based cache eviction
• providing durable offline weather forecast access

The cache is optimized for repeated forecast reads across spatial cells and hourly forecast windows.

Architecture Overview

The module implements:

• lazy singleton SQLite initialization
• structured weather forecast persistence
• JSON payload serialization
• runtime payload validation
• least-recently-used (LRU) eviction
• cache corruption self-healing
• WAL-enabled SQLite reliability

The cache key consists of:

(cell_id, forecast_time)

allowing unique forecast storage per grid cell and forecast hour.

Database Schema

Database

Table

Columns

Primary Key

PRIMARY KEY (cell_id, forecast_time)

This guarantees uniqueness for each forecast hour within a cache cell.

Invariants

The module maintains the following invariants.

1. Database Connection Is Singleton-Based

The database handle is lazily memoized:

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

This guarantees:

• single database initialization
• shared async connection reuse
• reduced SQLite overhead
• safe concurrent access

If initialization fails, the singleton resets to allow retries.

2. WAL Mode Is Always Enabled

Initialization enables SQLite Write-Ahead Logging:

PRAGMA journal_mode = WAL;

Benefits include:

• safer concurrent reads/writes
• improved write throughput
• better reliability for async cache access

3. Forecast Rows Are Unique Per Cell/Hour

Each row is uniquely identified by:

(cell_id, forecast_time)

Duplicate inserts replace existing values through:

INSERT OR REPLACE

This guarantees only one cached forecast exists for a given spatial/temporal coordinate.

4. Cached Payloads Must Be Valid WeatherPoint Objects

All cached JSON payloads are validated through:

isWeatherPoint(...)

Required fields:

All numeric values must be finite.

Invalid payloads are rejected.

5. Corrupted Cache Rows Are Automatically Removed

If a cached payload:

• fails JSON parsing
• fails runtime validation

the row is deleted automatically.

Example cleanup path:

DELETE FROM forecast_points
WHERE cell_id = ? AND forecast_time = ?

or:

DELETE FROM forecast_points
WHERE rowid = ?

This creates self-healing cache behavior.

6. Access Timestamps Reflect Successful Reads

last_accessed is updated only after payload validation succeeds:

UPDATE forecast_points
SET last_accessed = ?

Corrupted rows therefore never receive refreshed LRU timestamps.

7. LRU Eviction Is Deterministic

Eviction always removes rows ordered by:

ORDER BY last_accessed ASC

This guarantees:

• least-recently-used rows are removed first
• recently accessed forecasts remain cached

8. Forecast Times Are Normalized

All forecast timestamps are normalized through:

toHourKey(...)

This ensures:

• consistent cache keys
• hour-level granularity
• deduplicated forecast storage

Variants

Query Variants

Exact Cell + Hour Lookup

getForecastPoint(cellId, forecastTime)

Returns one cached forecast row or null.

Hour-Wide Lookup

getForecastPointsForHour(forecastTime)

Returns all cached forecasts matching a normalized hour.

Useful for:

• regional map rendering
• multi-cell forecast aggregation
• spatial forecast interpolation

Mutation Variants

Single Row Deletion

deleteForecastPoint(...)

Deletes one forecast entry.

Batch Upsert

putForecastPoints(...)

Efficiently inserts or replaces multiple rows sharing:

• a cache cell
• a validity window

Access Tracking

touchForecastPointAccess(...)

Updates LRU access metadata without modifying payloads.

Eviction Variants

Size-Based LRU Eviction

evictLRU(maxRows)

Evicts rows until total cache size is below a configured threshold.

Exported Types

CachedForecastPoint

Represents a validated materialized cache row.

export type CachedForecastPoint = {
  cellId: string;
  validTo: number;
  point: WeatherPoint;
};

Exported Functions

initWeatherCacheDb()

export async function initWeatherCacheDb()

Initializes the weather cache database schema.

Responsibilities

• opens SQLite connection
• enables WAL mode
• creates cache tables

Example

await initWeatherCacheDb();

Notes

• idempotent
• safe to call repeatedly

getForecastPoint(cellId, forecastTime)

export async function getForecastPoint(
  cellId: string,
  forecastTime: string
): Promise<CachedForecastPoint | null>

Returns one cached forecast row for an exact cell/hour pair.

Parameters

Responsibilities

• normalizes forecast hour
• loads cached payload
• validates JSON integrity
• updates LRU access timestamp

Return Value

Corruption Handling

Invalid rows are automatically deleted before rethrowing the validation error.

Example

const point = await getForecastPoint(
  "grid_12_8",
  "2026-05-23T15:00:00Z"
);

deleteForecastPoint(cellId, forecastTime)

export async function deleteForecastPoint(
  cellId: string,
  forecastTime: string
): Promise<void>

Deletes one cached forecast row.

Example

await deleteForecastPoint(
  "grid_12_8",
  "2026-05-23T15:00:00Z"
);

getForecastPointsForHour(forecastTime)

export async function getForecastPointsForHour(
  forecastTime: string
): Promise<CachedForecastPoint[]>

Returns all forecast rows matching a normalized forecast hour.

Responsibilities

• loads all rows for a forecast hour
• validates payload integrity
• removes corrupted rows
• returns only valid cache entries

Ordering

No explicit ordering guarantee is provided.

Example

const points = await getForecastPointsForHour(
  "2026-05-23T15:00:00Z"
);

Common Use Cases

• map overlays
• regional forecast snapshots
• grid aggregation

touchForecastPointAccess(cellId, forecastTime)

export async function touchForecastPointAccess(
  cellId: string,
  forecastTime: string
): Promise<void>

Updates the last_accessed timestamp for one cache row.

Purpose

Used to maintain accurate LRU eviction ordering.

Example

await touchForecastPointAccess(
  "grid_12_8",
  "2026-05-23T15:00:00Z"
);

putForecastPoints(cellId, points, validTo)

export async function putForecastPoints(
  cellId: string,
  points: WeatherPoint[],
  validTo: number
)

Batch inserts or replaces forecast rows.

Parameters

Responsibilities

• normalizes forecast hours
• serializes payloads to JSON
• performs upsert operations
• initializes access timestamps

Storage Model

Forecast payloads are stored as serialized JSON:

JSON.stringify(point)

Logging

Successful batch writes emit structured informational logs.

Example

await putForecastPoints(
  "grid_12_8",
  forecastPoints,
  Date.now() + 3600000
);

evictLRU(maxRows)

export async function evictLRU(
  maxRows: number
)

Evicts least-recently-used rows until the cache contains at most maxRows.

Parameters

Validation

maxRows must be a positive integer.

Invalid values throw:

RangeError

Eviction Strategy

Rows with the oldest last_accessed timestamps are removed first.

Concurrency Safety

Deletion count is computed entirely inside SQLite:

SELECT CASE
  WHEN COUNT(*) > ? THEN COUNT(*) - ?
  ELSE 0
END

This prevents over-eviction during concurrent callers.

Logging

Eviction operations emit informational logs including:

• deleted row count
• before/after row totals
• configured max size

Example

await evictLRU(1000);

Internal Helpers

getDb()

Lazy-loads and memoizes the SQLite database connection.

Responsibilities

• opens database once
• shares async connection promise
• resets state on initialization failure

isWeatherPoint(value)

Runtime type guard validating cached weather payloads.

parseWeatherPointPayload(payload, context)

Safely parses and validates serialized forecast payloads.

Failure Cases

Throws when:

• JSON is malformed
• payload structure is invalid

assertPositiveInteger(value, name)

Ensures numeric configuration values are positive integers.

Throws:

RangeError

for invalid inputs.

logWeatherDbInfo(message, context)

Structured informational logging helper for cache lifecycle events.

Cache Design

Cache Key

(cell_id, forecast_time)

Cache Granularity

Payload Format

Weather forecast rows are stored as serialized JSON blobs.

This allows schema flexibility while maintaining runtime validation safety.

Corruption Recovery Strategy

The cache is intentionally self-healing.

If corrupted rows are detected:

1. parsing fails
2. invalid rows are deleted
3. valid rows continue processing
4. errors propagate upward

This prevents permanent cache poisoning.

Concurrency Notes

Because the database connection is memoized:

dbPromise

multiple concurrent callers safely share the same initialization flow.

Additionally, eviction count computation occurs entirely inside SQLite to avoid race-condition over-deletion.

Example Integration

await initWeatherCacheDb();

await putForecastPoints(
  "grid_12_8",
  forecastPoints,
  Date.now() + 3600000
);

const point = await getForecastPoint(
  "grid_12_8",
  "2026-05-23T15:00:00Z"
);

await evictLRU(5000);