How We Added E2E Encryption on Top of a Local-First Architecture

I used to track my finances in an app. Salary, loans, small transfers, all of it. At some point I got curious whether the team behind it could actually see those numbers in their database. So I wrote them and asked. They never replied.

That stuck with me. When we started building Finsight, I did not want our users in that same spot, wondering what happens to their data on someone else's server. So privacy went into the architecture from day one, not added later, not bolted on before launch.

The goal was simple: data should be inaccessible not because the team promises to behave, but because technically the team cannot read it even if they wanted to.

HTTPS Is Not End-to-End

HTTPS protects the connection between the phone and the server. That is necessary, but it only covers the wire. Once the request lands on the server, the data sits there in plain text. If a transaction amount of 530 arrives, the server sees 530, period.

Encrypting the database on the server does not help either. It protects against stolen disks and leaked backups, but while the server is running it decrypts data on the fly and reads it like anything else. Inside the application layer it is still plaintext.

So at some point the server sees balances, notes, and amounts anyway. We wanted to eliminate that moment entirely.

Where We Drew the Line

In a previous article we covered the move to local-first. The short version: every user has their own SQLite database on their device. The UI reads and writes directly to it, so everything opens instantly and works offline. PowerSync runs in the background and keeps the local database in sync with the server.

That solved the performance problem. But it raised another one: data travels constantly between the device and the server, and anyone along that path could potentially read it.

Our answer was to encrypt sensitive fields directly on the device before PowerSync sends anything. What reaches the server is already scrambled. Only a client holding the key can decrypt it. The server never sees the key.

input  --> plaintext into local SQLite
       --> encryption on the client
       --> ciphertext into the sync table
       --> PowerSync carries ciphertext to the server
       --> another device receives ciphertext
       --> client decrypts locally
       --> UI reads normal plaintext again

For PowerSync these are just rows. It does not need to understand what is inside them.

Two Tables Instead of One

If we encrypt data before sending it, the UI has a problem: it needs the values in readable form. You cannot render a transaction list from scrambled strings, and a monthly expense filter would not work on them either.

So for every sensitive entity we maintain two tables.

One is local, with plain values, and the UI reads from it. In PowerSync these tables are marked as `localOnly` and never leave the device. The second is the sync table, with the same records but sensitive fields already encrypted. PowerSync moves this one between the device and the server.

For an account the schema looks like this:

// Local table with plain values, never leaves the device
export const accounts = new Table(
  {
    organization_id: column.text,
    name: column.text,    // plaintext
    balance: column.real, // regular number
    currency_id: column.text,
    // ...
  },
  { localOnly: true }
);

// Sync table with the same fields, but name and balance hold ciphertext
export const accountsEncrypt = new Table({
  organization_id: column.text,
  name: column.text,    // encrypted
  balance: column.text, // text instead of real because it holds ciphertext
  currency_id: column.text,
  // ...
});

The same pair exists for every sensitive entity:

accounts        --> accounts_encrypt
transactions    --> transactions_encrypt
debts           --> debts_encrypt
loans           --> loans_encrypt
loan_payments   --> loan_payments_encrypt

We do not encrypt entire rows, only specific fields: amounts, balances, notes, lender names, interest rates. Things like dates, user and organization IDs, and relations between records stay visible. Without them the server cannot route rows to the right devices.

That is a tradeoff, and there is more on it below.

How It Works in Code

When a user creates an account, the UI writes a row to the plain `accounts` table. No encryption yet, no sync. Just a normal insert into the local database.

The database itself is a `PowerSyncDatabase` instance. On initialization it gets a schema with all the tables and a filename where SQLite will store the data on the device:

import { PowerSyncDatabase } from '@powersync/web';

const db = new PowerSyncDatabase({
  database: { dbFilename: 'finsight.db' },
  schema: AppSchema, // all tables, both local-only and sync
});

From there it gets interesting. PowerSync can subscribe to changes and notify which tables just changed. We use that as a trigger to run the crypto layer:

// Fires every time one of the tables changes
db.onChange({
  onChange: async ({ changedTables }) => {
    for (const table of changedTables) {
      await handleTableChange(table);
    }
  }
});

The `handleTableChange` function is a simple router. It looks at the table name and decides which direction to go: encrypt freshly written data before it gets sent, or decrypt incoming data from the server so the UI can display it.

async function handleTableChange(table) {
  // User changed something locally, encrypt and write to the sync copy
  if (table === 'accounts')     return encryptAccounts();
  if (table === 'transactions') return encryptTransactions();

  // Encrypted row arrived from the server, decrypt for the UI
  if (table === 'accounts_encrypt')     return decryptAccounts();
  if (table === 'transactions_encrypt') return decryptTransactions();

  // ...debts, loans, payments follow the same pattern
}

Both directions are needed. The first makes sure data gets encrypted before PowerSync picks it up. The second makes sure anything that arrived from another device gets turned back into readable numbers and strings so the UI can show it right away.

After that, PowerSync looks only at the encrypted tables and sends accumulated changes to the server in batches. On the backend they map to regular Django models. The `Transaction.amount` field is declared as `TextField` rather than a number because it holds ciphertext. The server stores the string, returns it on request, and cannot read what is inside.

Keys

Everything depends on a key the server does not know. If the server had it, e2e would be pointless.

We use a two-layer scheme. Each organization has a main key called the DEK (data encryption key). It is what actually encrypts the financial fields. The DEK is a random sequence of bytes that no one types or memorizes.

The DEK itself is also encrypted, wrapped by another key called the KEK (key encryption key). The KEK is derived from the user's secret key, a passphrase they set specifically for data encryption and separate from their account password. The derivation uses Argon2id from libsodium, an algorithm designed to make passphrase brute-forcing computationally expensive.

secret key + salt  -->  KEK
random DEK         -->  encrypted DEK (wrapped by KEK)
DEK                -->  encrypted financial fields

The server stores only the encrypted DEK and its metadata. It never sees the raw DEK, the KEK, or the secret key.

The upside is that changing the secret key is cheap. Instead of re-encrypting every transaction and account, we just unwrap the DEK with the old KEK and re-wrap it with a new one derived from the new key. The DEK stays the same, so all the encrypted data stays untouched.

The downside is that without the secret key and without a backup, the data is gone. The server has no plaintext copy to recover from. No support ticket fixes this, because the server genuinely does not have the key.

What Gets Harder

The `encrypt()` call itself takes three lines. The complexity lives around it.

Validation. Before e2e, most business rules ran on the server. An amount came in, the server checked it against the balance and the constraints. Now the server sees a string like `eyJhbGciOiJBMjU2R0NN...` and cannot say anything meaningful about it. Some checks moved to the client. The server still handles access control, relational integrity, quotas, and structure, but it can no longer verify what is actually inside an encrypted field.

Debugging. When a user reports a discrepancy, it used to take two minutes to look up the row in PostgreSQL. Now those fields are base64 strings unreadable without the user's key. Reproducing the issue means working on the client: inspecting local SQLite, checking the upload queue, looking at sync state. The server database stopped being the place where you look for answers.

Logging. The server should never see plaintext. But the client sees it constantly, before encryption and after decryption. Client logging has to be designed deliberately so a routine log line does not accidentally capture an amount or a note.

Device load on first sync. When a user signs into a fresh device, the full history has to come down and every single row needs to be decrypted locally. The server cannot help with that part. Early on we hit real bugs from this: rows arriving in batches, decryption running in parallel, race conditions and UI stalls while everything was still catching up.

What the Server Still Sees

With e2e the server does not see nothing. It sees a lot of metadata, and that is worth saying plainly.

The server knows who the user is and which organization they belong to, the IDs of every record, the fact that transactions and accounts and debts exist, their dates and types, relations between records, creation and update timestamps, and the size of each encrypted value.

Metadata alone can say quite a bit. The server may not know the transaction amount, but it can see that health-related transactions tripled this month. Or that a new currency appeared in the account list.

So I do not call e2e full invisibility. The heaviest financial values, amounts, balances, rates, and notes, do not reach the server in plain form. But the shape of the data, who has what and when, is visible to the server. That is the actual line we drew, nothing more.

---

E2e in production is not a weekend project. It shifts how you think about the backend, the client, validation, and debugging. The backend knows less. The client takes on more responsibility.

If your product does not handle sensitive data, this complexity is probably not worth it. But when a finance app holds a real piece of someone's life, it becomes clear why you would build it this way. That is what we did in Finsight.