Data Privacy via Encrypted SQLite

While the main application state lives in standard RDBMS solutions, Gift Moments takes a specialized approach to user-specific data repositories.

The Encrypted Vault

For sensitive user data, I implemented an Encrypted SQLite architecture.

• Security at Rest: The database file itself is encrypted using SQLCipher, ensuring that even if the physical file is accessed, the data remains unreadable without the specific keys.
• Portability: Using SQLite allows user vaults to be portable and easily backed up, behaving like secure documents rather than just database rows.

Implementation Details

The encryption logic is handled transparently by the connection manager. When a connection is requested, the system automatically applies the encryption key defined in the environment configuration.

// tc-be/src/common/services/sqlite/sqlite-connection-manager.ts

// Configure encryption if key is available
if (this.encryptionKey) {
    console.log(`Setting up encryption for database: ${dbPath}`);
    // Set to use standard SQLCipher encryption (compatible with DB Browser)
    db.pragma(`cipher='sqlcipher'`);
    db.pragma(`legacy=4`);
    
    // Check if this is a new database (doesn't exist or is empty)
    const isNewDb = !fs.existsSync(dbPath) || fs.statSync(dbPath).size === 0;
    
    if (isNewDb) {
        // For new database, set the key
        db.pragma(`key='${this.escapePragmaValue(this.encryptionKey)}'`);
    } else {
        // For existing database, try to open with key
        try {
            db.pragma(`key='${this.escapePragmaValue(this.encryptionKey)}'`);
            
            // Verify the database is readable (will throw if key is wrong)
            db.prepare('SELECT 1').get();
        } catch (error) {
            throw new Error(`Cannot decrypt database with provided key: ${dbPath}`);
        }
    }
}

User Isolation

Each user is assigned a dedicated SQLite database file. This architectural decision ensures strict data isolation; a user's data is physically separated from others, not just logically filtered by a WHERE clause.

// tc-be/src/common/services/sqlite/sqlite.service.ts

// * Constructs the path to the user's database file
private getUserDbPath(userId: number): string {
    const userIdStr = String(userId).padStart(8, '0');
    // Each user gets their own isolated directory and database file
    return path.join(this.dbPath, `${userIdStr}/user_${userIdStr}.db`);
}

This approach isolates user contexts and adds a critical layer of defense for personal information, separate from the main application logic.