Architecture Note: Local SQLite Persistence with Titanium's Ti.Database

Requirements

The mobile application must store user‑generated data locally so that it remains available when the device is offline. The persistence layer needs to:

• Provide a single source of truth for all offline data.
• Support create, read, update, and delete (CRUD) operations with ACID guarantees for batch writes.
• Isolate sensitive fields (e.g., passwords, personal identifiers) from plain‑storage exposure.
• Allow the business logic to remain agnostic of the underlying storage technology so that a future migration path is possible.
• Operate within the Titanium runtime constraints, respecting the deprecation status of Ti.Database in newer SDK releases.

Smallest Suitable Design

The minimal architecture that satisfies the requirements consists of three layers:

1. Data Access Object (DAO) layer – a thin JavaScript abstraction that exposes methods like getItem(id), saveItem(item), deleteItem(id), and listItems(). All SQL strings are built inside this layer.
2. Transaction service – a helper that wraps DAO calls in explicit Ti.Database transactions when multiple rows must be written atomically.
3. Encryption wrapper – optional functions that encrypt/decrypt selected column values using Ti.Security (or a vetted third‑party crypto module) before they are passed to the DAO.

The DAO obtains a single database connection via Ti.Database.open('appdata'). The connection is kept open for the lifetime of the app (or reopened on each launch) to avoid the overhead of repeated file opens.

Example DAO skeleton

// dao.js
var db = Ti.Database.open('appdata');
// Ensure table exists on first open
db.execute('CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, payload TEXT, secret BLOB)');

function _encrypt(value) {
  // Placeholder: replace with actual Ti.Security or crypto call
  return Ti.Security.encodeBase64(value); // example only
}

function _decrypt(blob) {
  return Ti.Security.decodeBase64(blob);
}

exports.getItem = function(id) {
  var rows = db.execute('SELECT payload, secret FROM items WHERE id = ?', id);
  if (rows.isValidRow()) {
    var payload = rows.fieldByName('payload');
    var secret = _decrypt(rows.fieldByName('secret'));
    rows.close();
    return {id: id, payload: payload, secret: secret};
  }
  rows.close();
  return null;
};

exports.saveItem = function(item) {
  var secretBlob = _encrypt(item.secret);
  if (item.id) {
    db.execute('UPDATE items SET payload = ?, secret = ? WHERE id = ?',
               item.payload, secretBlob, item.id);
  } else {
    db.execute('INSERT INTO items (payload, secret) VALUES (?, ?)',
               item.payload, secretBlob);
    item.id = db.lastInsertRowId;
  }
  return item;
};

exports.deleteItem = function(id) {
  db.execute('DELETE FROM items WHERE id = ?', id);
};

exports.listItems = function() {
  var rows = db.execute('SELECT id, payload, secret FROM items');
  var result = [];
  while (rows.isValidRow()) {
    result.push({
      id: rows.fieldByName('id'),
      payload: rows.fieldByName('payload'),
      secret: _decrypt(rows.fieldByName('secret'))
    });
    rows.next();
  }
  rows.close();
  return result;
};

// Call when app terminates to release the handle
exports.close = function() {
  db.close();
};

Trust/Data Boundaries

The DAO layer is the only component that directly interacts with Ti.Database. All other modules (UI controllers, sync service, business logic) trust the DAO to return correctly typed data and to handle errors. The encryption wrapper creates a trust boundary: plaintext secrets never leave the wrapper; only encrypted blobs are stored in the SQLite file. This limits exposure if the device is rooted or jail‑broken and an attacker gains file‑system access.

Operational Checks

To verify that the persistence layer behaves as expected during development and in production, implement the following lightweight checks:

• Startup health check – after opening the database, execute a simple query (SELECT COUNT(*) FROM items) and log the result. Failure to execute indicates a corrupted or missing database file.
• Transaction latency monitoring – wrap each explicit transaction with a timer (e.g., Date.now() before and after) and emit a warning if the duration exceeds a configurable threshold (e.g., 200 ms). This helps detect write‑throughput bottlenecks.
• Encryption integrity test – on each read, decrypt a known test value (if present) and compare it to the expected plaintext. A mismatch flags a possible key corruption or tampering.
• File‑size watchdog – periodically check the size of the SQLite file (Ti.Filesystem.getFile(Ti.Filesystem.applicationDataDirectory, 'appdata.sqlite')). Unbounded growth may signal runaway inserts or missing vacuum operations.

Failure Modes

Understanding how the design can fail informs both testing and runtime mitigation:

• Database corruption – sudden power loss during a write can leave the SQLite journal in an inconsistent state. SQLite’s rollback journal usually recovers on the next open, but if the journal itself is corrupted the open may throw an exception. Mitigation: catch Ti.Database.Error on open, attempt to delete the corrupted file and re‑initialize with an empty schema.
• Write‑throughput saturation – high‑frequency logging (e.g., telemetry every second) can cause the UI thread to block if transactions are run synchronously. Mitigation: off‑load batch writes to a background thread using Ti.Thread or a simple setTimeout‑based queue, and keep transactions small (e.g., ≤ 50 rows per transaction).
• Key loss or change – if the encryption key is derived from a device‑specific secret that changes after a factory reset, previously stored secrets become unrecoverable. Mitigation: store a non‑secret key identifier alongside each encrypted blob and support key rotation by re‑encrypting when the identifier mismatches.
• SDK deprecation impact – future Titanium releases may remove Ti.Database entirely. Because the DAO layer isolates SQL calls, migrating to an alternative persistence API (e.g., a community‑maintained SQLite wrapper or asynchronous storage) only requires rewriting the DAO implementation while keeping the rest of the app unchanged.

Conditions That Would Change the Design

The current smallest design would be revisited if any of the following conditions become true:

1. The application requires complex queries with joins, full‑text search, or geospatial indexes that exceed SQLite’s convenience in the Titanium bridge.
2. Regulatory mandates demand hardware‑backed keystore encryption (e.g., Android Keystore, iOS Secure Enclave) rather than software‑only encryption.
3. The app’s offline dataset grows beyond a few megabytes and begins to affect launch time; a switch to a partitioned database or a NoSQL‑style store (e.g., Realm) might be warranted.
4. The Titanium team provides an officially supported, asynchronous persistence API that eliminates the need for manual thread management.

When any of these triggers appear, revisit the trust boundaries (e.g., move encryption into the storage layer) and consider introducing a repository pattern that can swap implementations without touching business logic.