crowd-funder-for-time-pwa/doc/secure-storage-implementation.md

Secure Storage Implementation Guide for TimeSafari App

Overview

This document outlines the implementation of secure storage for the TimeSafari app. The implementation focuses on:

1. Platform-Specific Storage Solutions:

   • Web: SQLite with IndexedDB backend (absurd-sql)
   • Electron: SQLite with Node.js backend
   • Native: (Planned) SQLCipher with platform-specific secure storage

2. Key Features:

   • SQLite-based storage using absurd-sql for web
   • Platform-specific service factory pattern
   • Consistent API across platforms
   • Migration support from Dexie.js

Quick Start

1. Installation

# Core dependencies
npm install @jlongster/sql.js
npm install absurd-sql

# Platform-specific dependencies (for future native support)
npm install @capacitor/preferences
npm install @capacitor-community/biometric-auth

2. Basic Usage

// Using the platform service
import { PlatformServiceFactory } from '../services/PlatformServiceFactory';

// Get platform-specific service instance
const platformService = PlatformServiceFactory.getInstance();

// Example database operations
async function example() {
  try {
    // Query example
    const result = await platformService.dbQuery(
      "SELECT * FROM accounts WHERE did = ?",
      [did]
    );

    // Execute example
    await platformService.dbExec(
      "INSERT INTO accounts (did, public_key_hex) VALUES (?, ?)",
      [did, publicKeyHex]
    );

    } catch (error) {
    console.error('Database operation failed:', error);
  }
}

3. Platform Detection

// src/services/PlatformServiceFactory.ts
export class PlatformServiceFactory {
  static getInstance(): PlatformService {
    if (process.env.ELECTRON) {
      // Electron platform
      return new ElectronPlatformService();
    } else {
      // Web platform (default)
      return new AbsurdSqlDatabaseService();
    }
  }
}

4. Current Implementation Details

Web Platform (AbsurdSqlDatabaseService)

The web platform uses absurd-sql with IndexedDB backend:

// src/services/AbsurdSqlDatabaseService.ts
export class AbsurdSqlDatabaseService implements PlatformService {
  private static instance: AbsurdSqlDatabaseService | null = null;
  private db: AbsurdSqlDatabase | null = null;
  private initialized: boolean = false;
      
  // Singleton pattern
  static getInstance(): AbsurdSqlDatabaseService {
    if (!AbsurdSqlDatabaseService.instance) {
      AbsurdSqlDatabaseService.instance = new AbsurdSqlDatabaseService();
    }
    return AbsurdSqlDatabaseService.instance;
  }

  // Database operations
  async dbQuery(sql: string, params: unknown[] = []): Promise<QueryExecResult[]> {
    await this.waitForInitialization();
    return this.queueOperation<QueryExecResult[]>("query", sql, params);
    }

  async dbExec(sql: string, params: unknown[] = []): Promise<void> {
    await this.waitForInitialization();
    await this.queueOperation<void>("run", sql, params);
  }
}

Key features:

• Uses absurd-sql for SQLite in the browser
• Implements operation queuing for thread safety
• Handles initialization and connection management
• Provides consistent API across platforms

5. Migration from Dexie.js

The current implementation supports gradual migration from Dexie.js:

// Example of dual-storage pattern
async function getAccount(did: string): Promise<Account | undefined> {
  // Try SQLite first
  const platform = PlatformServiceFactory.getInstance();
  let account = await platform.dbQuery(
    "SELECT * FROM accounts WHERE did = ?",
    [did]
  );

  // Fallback to Dexie if needed
  if (USE_DEXIE_DB) {
    account = await db.accounts.get(did);
  }

  return account;
   }

A. Modifying Code

When converting from Dexie.js to SQL-based implementation, follow these patterns:

1. Database Access Pattern

   // Before (Dexie)
   const result = await db.table.where("field").equals(value).first();
   
   // After (SQL)
   const platform = PlatformServiceFactory.getInstance();
   let result = await platform.dbQuery(
     "SELECT * FROM table WHERE field = ?",
     [value]
   );
   result = databaseUtil.mapQueryResultToValues(result);
   
   // Fallback to Dexie if needed
   if (USE_DEXIE_DB) {
     result = await db.table.where("field").equals(value).first();
   }
   

2. Update Operations

   // Before (Dexie)
   await db.table.where("id").equals(id).modify(changes);
   
   // After (SQL)
   // For settings updates, use the utility methods:
   await databaseUtil.updateDefaultSettings(changes);
   // OR
   await databaseUtil.updateAccountSettings(did, changes);
   
   // For other tables, use direct SQL:
   const platform = PlatformServiceFactory.getInstance();
   await platform.dbExec(
     "UPDATE table SET field1 = ?, field2 = ? WHERE id = ?",
     [changes.field1, changes.field2, id]
   );
   
   // Fallback to Dexie if needed
   if (USE_DEXIE_DB) {
     await db.table.where("id").equals(id).modify(changes);
   }
   

3. Insert Operations

   // Before (Dexie)
   await db.table.add(item);
   
   // After (SQL)
   const platform = PlatformServiceFactory.getInstance();
   const columns = Object.keys(item);
   const values = Object.values(item);
   const placeholders = values.map(() => '?').join(', ');
   const sql = `INSERT INTO table (${columns.join(', ')}) VALUES (${placeholders})`;
   await platform.dbExec(sql, values);
   
   // Fallback to Dexie if needed
   if (USE_DEXIE_DB) {
     await db.table.add(item);
   }
   

4. Delete Operations

   // Before (Dexie)
   await db.table.where("id").equals(id).delete();
   
   // After (SQL)
   const platform = PlatformServiceFactory.getInstance();
   await platform.dbExec("DELETE FROM table WHERE id = ?", [id]);
   
   // Fallback to Dexie if needed
   if (USE_DEXIE_DB) {
     await db.table.where("id").equals(id).delete();
   }
   

5. Result Processing

   // Before (Dexie)
   const items = await db.table.toArray();
   
   // After (SQL)
   const platform = PlatformServiceFactory.getInstance();
   let items = await platform.dbQuery("SELECT * FROM table");
   items = databaseUtil.mapQueryResultToValues(items);
   
   // Fallback to Dexie if needed
   if (USE_DEXIE_DB) {
     items = await db.table.toArray();
   }
   

6. Using Utility Methods

When working with settings or other common operations, use the utility methods in db/index.ts:

// Settings operations
await databaseUtil.updateDefaultSettings(settings);
await databaseUtil.updateAccountSettings(did, settings);
const settings = await databaseUtil.retrieveSettingsForDefaultAccount();
const settings = await databaseUtil.retrieveSettingsForActiveAccount();

// Logging operations
await databaseUtil.logToDb(message);
await databaseUtil.logConsoleAndDb(message, showInConsole);

Key Considerations:

• Always use databaseUtil.mapQueryResultToValues() to process SQL query results
• Use utility methods from db/index.ts when available instead of direct SQL
• Keep Dexie fallbacks wrapped in if (USE_DEXIE_DB) checks
• For queries that return results, use let variables to allow Dexie fallback to override
• For updates/inserts/deletes, execute both SQL and Dexie operations when USE_DEXIE_DB is true

Example Migration:

// Before (Dexie)
export async function updateSettings(settings: Settings): Promise<void> {
  await db.settings.put(settings);
}

// After (SQL)
export async function updateSettings(settings: Settings): Promise<void> {
  const platform = PlatformServiceFactory.getInstance();
  const { sql, params } = generateUpdateStatement(
    settings,
    "settings",
    "id = ?",
    [settings.id]
  );
  await platform.dbExec(sql, params);
}

Remember to:

• Create database access code to use the platform service, putting it in front of the Dexie version

• Instead of removing Dexie-specific code, keep it.

  • For creates & updates & deletes, the duplicate code is fine.

  • For queries where we use the results, make the setting from SQL into a 'let' variable, then wrap the Dexie code in a check for USE_DEXIE_DB from app.ts and if it's true then use that result instead of the SQL code's result.

• Consider data migration needs, and warn if there are any potential migration problems

Success Criteria

1. Functionality

   • Basic CRUD operations work correctly
   • Platform service factory pattern implemented
   • Error handling in place
   • Native platform support (planned)

2. Performance

   • Database operations complete within acceptable time
   • Operation queuing for thread safety
   • Proper initialization handling
   • Performance monitoring (planned)

3. Security

   • Basic data integrity
   • Encryption (planned for native platforms)
   • Secure key storage (planned)
   • Platform-specific security features (planned)

4. Testing

   • Basic unit tests
   • Comprehensive integration tests (planned)
   • Platform-specific tests (planned)
   • Migration tests (planned)

Next Steps

1. Native Platform Support

   • Implement SQLCipher for iOS/Android
   • Add platform-specific secure storage
   • Implement biometric authentication

2. Enhanced Security

   • Add encryption for sensitive data
   • Implement secure key storage
   • Add platform-specific security features

3. Testing and Monitoring

   • Add comprehensive test coverage
   • Implement performance monitoring
   • Add error tracking and analytics

4. Documentation

   • Add API documentation
   • Create migration guides
   • Document security measures