High-Performance SQLite Storage Engine

June leverages the native node:sqlite engine introduced in Node.js 22 LTS. By eliminating third-party native compilation toolchains (e.g. node-gyp, Python bindings), June achieves platform portability, zero-dependency builds, and direct V8-to-C++ execution speed.

---

Storage Architecture Overview

All bot persistent state—including guild authorizations, OAuth tokens, temporary mail leases, custom bins, receipts, and user preferences—is maintained in a centralized SQLite database (src/db/database.js).

graph TD
    App["June Application Core"]
    
    subgraph StorageEngine ["Storage Engine (src/db/database.js)"]
        Cache["In-Memory Write-Through Cache\n(Admins, Whitelists, User Preferences)"]
        Driver["node:sqlite (DatabaseSync)"]
        WAL["Write-Ahead Log (WAL)\njune.sqlite3-wal"]
        MainDB[("Main Database File\njune.sqlite3")]
    end

    App <-->|Hot Reads| Cache
    App -->|Mutations| Driver
    Driver -->|Atomic Commit| WAL
    Driver -->|Synchronous Update| Cache
    Driver -->|Automatic Checkpoint| MainDB
    
    subgraph Maintenance ["Operational Maintenance"]
        Backup["scripts/backup-database.js\n(Atomic SQLite VACUUM INTO)"]
        Prune["scripts/prune-backups.js\n(Rolling Retention Policy)"]
    end

    MainDB -.-> Backup --> Prune

---

Core Pragma Tuning & Durability Trade-offs

On database initialization, June configures high-throughput SQLite pragmas:

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA wal_autocheckpoint = 1000;
PRAGMA temp_store = MEMORY;

Durability & Performance Analysis:

1. journal_mode = WAL (Write-Ahead Logging):
   • Reads do not block writes, and writes do not block reads.
   • Reader threads access committed pages concurrently without waiting on database locks.
2. synchronous = NORMAL (The Durability Trade-off):
   • In WAL mode, NORMAL guarantees data integrity across application-level crashes while eliminating disk fsync calls on every individual commit.
   • Trade-off: In the event of a sudden operating system crash or physical host power failure, transactions committed in the WAL file since the last disk sync may be lost. For a Discord utility and intelligence platform, this is an intentional, optimal trade-off prioritizing sustained high write throughput over absolute power-loss durability.
3. busy_timeout = 5000:
   • Instead of throwing an immediate SQLITE_BUSY error during burst workloads, database queries automatically retry for up to 5,000ms until the lock clears.
4. temp_store = MEMORY:
   • Intermediate query sorting tables, temporary views, and transient indexes reside strictly in RAM rather than generating disk I/O.

---

Schema & Migration Strategy

June employs a linear migration pipeline governed by SCHEMA_VERSION:

const SCHEMA_VERSION = 7;

When June boots, it checks the metadata table for the current schema_version. If the database is new or lagging behind the application build, incremental migration transactions execute atomically:

stateDiagram-v2
    [*] --> CheckVersion: Inspect metadata table
    CheckVersion --> V1_Initial: Version < 1
    V1_Initial --> V2_AddGuildWhitelist: Version < 2
    V2_AddGuildWhitelist --> V3_AddOAuthGrants: Version < 3
    V3_AddOAuthGrants --> V4_AddManagedBins: Version < 4
    V4_AddManagedBins --> V5_AddTempMailboxes: Version < 5
    V5_AddTempMailboxes --> V6_AddUserSettings: Version < 6
    V6_AddUserSettings --> V7_Current: Version < 7
    V7_Current --> Ready: Schema Synchronized

All schema upgrades run inside transactions: if any migration statement encounters an anomaly, the transaction rolls back cleanly, preventing database corruption.

---

In-Memory Write-Through Cache

To ensure Discord commands respond within Discord's 3,000ms interaction deadline without database latency, hot configuration tables are mirrored in-memory:

• Global Whitelist: Cached in a JavaScript Set<string>.
• Guild Whitelists: Cached in a Map with guild metadata.
• Admin Users: Cached in a Set<string>.
• User Preferences: Cached in an LRU Map with a TTL.

Write-Through Pattern:

Mutations (e.g. /admin whitelist add or /settings timezone) perform an explicit write-through:

1. The atomic SQLite prepared statement executes first inside a transaction.
2. Upon successful transaction commit, the in-memory cache data structure is updated immediately.
3. Any read request immediately sees the updated state without a cache miss or database query.

---

Automated Backup & Disaster Recovery

Data safety is maintained via automated snapshotting:

1. Zero-Lock Backups: Utilizing SQLite's native VACUUM INTO 'backup-path.sqlite3' statement, backups are taken live without blocking active reads or writes.
2. Rolling Retention: Backups older than the retention threshold (e.g., 7 days) are pruned automatically via scheduled maintenance tasks.