db/debugLogDb.ts

Purpose

This module provides a persistent local logging system using Expo SQLite.

It is intended for development and diagnostics in environments where standard console access is unavailable, especially:

• background tasks
• suspended app execution
• device testing without Xcode/ADB attached
• production-adjacent debugging

The module stores structured debug logs in SQLite and automatically initializes itself on first use.

It is only intended to run when:

EXPO_PUBLIC_DEBUG=true

Architecture Overview

The module implements:

• a lazily initialized SQLite database
• automatic schema creation
• persistent structured log storage
• bounded retention pruning
• async-safe singleton database access

The database is optimized for append-heavy logging workloads.

Database Schema

Database

Table

Columns

Invariants

The module maintains the following invariants.

1. Database Connection Is Singleton-Based

Only one database connection promise exists at a time:

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

This guarantees:

• shared database access
• no duplicate initialization
• reduced SQLite open overhead

If initialization fails, the promise is reset to allow future retries.

2. Schema Is Self-Initializing

The database schema is automatically created during first access:

CREATE TABLE IF NOT EXISTS debug_logs

Consumers never need to manually initialize the database.

This is especially important for:

• background workers
• headless tasks
• startup race conditions

3. WAL Mode Is Always Enabled

The database enables SQLite Write-Ahead Logging:

PRAGMA journal_mode = WAL;

This improves:

• concurrent read/write behavior
• write reliability
• background-task resilience
• performance under frequent inserts

4. Log Retention Is Bounded

The database never stores more than:

MAX_ENTRIES = 500

After each insert, old rows are pruned automatically.

This guarantees:

• bounded storage growth
• predictable disk usage
• stable query performance

5. Newest Logs Are Preserved

Pruning keeps the newest rows:

ORDER BY id DESC LIMIT ?

Older entries are discarded first.

This creates rolling log retention behavior.

6. Query Results Are Returned Newest First

All reads return logs sorted by descending timestamp:

ORDER BY timestamp DESC

Consumers can rely on:

• index 0 being the newest log
• reverse chronological ordering

Variants

Logging Severity Variants

The module accepts arbitrary log levels:

level: string

Typical variants include:

The schema intentionally does not restrict allowed values.

This enables compatibility with:

• custom logging systems
• server-style log levels
• application-defined severities

Runtime Variants

Foreground Usage

The module can persist logs generated during active app execution.

Background Task Usage

The module is specifically designed to function in background execution contexts where normal console access is unavailable.

Examples:

• location tasks
• background fetch
• sensor monitoring
• scheduled jobs

Exported Functions

insertDebugLog(level, message)

export async function insertDebugLog(
  level: string,
  message: string
): Promise<void>

Persists a new debug log entry.

Parameters

Responsibilities

• initializes database automatically
• inserts log entry
• timestamps the entry
• enforces retention pruning

Timestamp Behavior

Timestamps are generated internally:

const now = Date.now();

Retention Behavior

After every insert:

1. newest MAX_ENTRIES rows are retained
2. older rows are deleted

Example

await insertDebugLog(
  "INFO",
  "Background task started"
);

Common Use Cases

• background task tracing
• lifecycle diagnostics
• crash reproduction
• execution auditing
• debugging without tethered console access

getDebugLogs()

export async function getDebugLogs(): Promise<DebugLogRow[]>

Returns all persisted debug logs ordered newest first.

Return Type

Equivalent structure:

type DebugLogRow = {
  id: number;
  timestamp: number;
  level: string;
  message: string;
};

Query Ordering

ORDER BY timestamp DESC

Example

const logs = await getDebugLogs();

Example Result

[
  {
    id: 101,
    timestamp: 1716500000000,
    level: "ERROR",
    message: "Location task failed"
  }
]

Common Use Cases

• rendering in debug UI
• exporting diagnostics
• support tooling
• local troubleshooting

clearDebugLogs()

export async function clearDebugLogs(): Promise<void>

Deletes all stored log entries.

Behavior

Executes:

DELETE FROM debug_logs

Example

await clearDebugLogs();

Common Use Cases

• resetting diagnostics state
• clearing old sessions
• testing log pipelines
• reclaiming storage

Internal Helpers

getDb()

async function getDb(): Promise<SQLite.SQLiteDatabase>

Lazy-loads and memoizes the SQLite database connection.

Responsibilities

• opens the database once
• enables WAL mode
• creates schema automatically
• resets singleton state on failure

Failure Recovery

If initialization fails:

dbPromise = null;

allowing later retries.

Retention Strategy

The module uses rolling retention.

Configuration

MAX_ENTRIES = 500

Pruning Query

DELETE FROM debug_logs
WHERE id NOT IN (
  SELECT id
  FROM debug_logs
  ORDER BY id DESC
  LIMIT ?
)

Characteristics

Concurrency Notes

Because all operations share a memoized database promise:

dbPromise

multiple concurrent calls safely reuse the same initialization flow.

This avoids:

• duplicate schema creation
• race conditions
• redundant database opens

Background Execution Design

A primary design goal of this module is visibility into headless/background execution.

The database is intentionally:

• self-initializing
• append-oriented
• WAL-enabled
• persistent across app restarts

This allows developers to inspect logs after:

• app suspension
• background execution
• crashes
• task failures

without requiring a live debugger connection.

Example Integration

await insertDebugLog(
  "DEBUG",
  "Sensor poll completed"
);

const logs = await getDebugLogs();

await clearDebugLogs();