DEV Community: Doszhan Mengaliyev

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

Doszhan Mengaliyev — Fri, 15 May 2026 14:28:42 +0000

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.

Why your app feels slow and how we fixed it with PowerSync

Doszhan Mengaliyev — Sun, 10 May 2026 15:37:42 +0000

The honeymoon phase of every MVP

You know the feeling: while you’re building an MVP, everything flies. A couple of users, an empty database, a fast server. The user clicks a button, the frontend sends a request, the backend responds, and the UI updates. It all feels predictable and straightforward.

At that stage, it’s easy to believe the architecture will scale just fine. Queries are fast, tables are small, and the user flows are simple. Every form saves in a split second. Every list opens right away.

Then the product grows up. Lists get longer, filters get more complex, analytics shows up, table relationships get messier, and the number of users keeps climbing.

We ran into that while building Finsight. In products like this, there are a lot of reads: transactions, categories, filters, totals, month views, quick edits. If every screen has to wait for the server, the whole product starts to feel heavier.

Before long, the user is spending more time staring at loaders than actually using the app. Open a list, wait. Change a field, wait again. The internet seems fine, the server is up, and yet the product still feels sluggish.

That’s a rough moment. Especially when, technically, everything seems to be built the right way.

The usual treatment

In that situation, we usually go down the well-worn path.

We check PostgreSQL indexes. Add pagination. Cache endpoints. Move heavy calculations out of hot paths. Run EXPLAIN ANALYZE. Remove unnecessary JOINs. Split large queries into smaller ones. Optimize serializers. Add debounce on the frontend.

All of that matters. And it often does help.

But in our case, it became clear that the problem was not just a slow backend. The real problem was the request-and-wait architecture itself.

The classic flow looked like this:

click -> request -> wait -> response -> update UI

As long as the network and the backend are fast, this feels fine. But the moment mobile internet gets shaky, the server takes a little longer, or the database pauses on a heavy query, the interface becomes trapped by the wait.

The user can’t keep going until the app gets its answer. Every action turns into a small negotiation with the network.

At some point, we decided we were done tolerating that and took a different path.

Local-first: when data is always close

We moved to an architecture where the main data source for the interface is a local SQLite database on the user’s device.

Important disclaimer: the backend did not go anywhere.

It still handles authentication, permissions, business rules, and validation. PostgreSQL remains the central store. But React no longer has to call the API every time it needs to show a list or update a field on screen.

The flow became:

React UI -> Local SQLite -> PowerSync -> Backend -> PostgreSQL

Now when the user hits save, the record lands in the local database first, the UI updates almost immediately, and PowerSync sends the change to the backend in the background.

click -> local write -> update UI -> sync in background

The network still matters. It just no longer stands between the user and the interface.

That was the real shift. Not speeding up one query, but removing the network wait from the user’s main interaction loop.

How it works inside

The frontend works with local SQLite through PowerSync. Components do not know about a separate API for every screen. They read through hooks or a DAL layer that runs SQL queries against the local database.

The backend changes roles too. It is no longer the layer returning JSON for every render. It becomes the place where permissions, constraints, relationships between entities, and incoming operations from the upload queue are checked.

PowerSync handles the synchronization layer. It delivers data to the client, keeps local SQLite in sync, and sends local changes back upstream.

Press enter or click to view image in full size

We do not download the whole database

One of the first questions people ask is whether the whole database ends up on the user’s device.

No.

A core part of PowerSync is partial replication. The client receives only the rows the user is allowed to access.

For example, if a user belongs to several workspaces, they get data only for those workspaces. Everything else never reaches the device.

A simplified sync_rules.yaml:

bucket_definitions:
  by_workspace:
    parameters: |
      SELECT workspace_id
      FROM workspace_memberships
      WHERE user_id = request.user_id()

    data:
      - SELECT * FROM records WHERE workspace_id = bucket.workspace_id
      - SELECT * FROM categories WHERE workspace_id = bucket.workspace_id

Extra data simply never gets synchronized. That gives you two big advantages.

First, the user never physically receives rows that belong to someone else.

Second, the backend and PostgreSQL are involved in far fewer routine reads. Lists, sorting, filters, and part of the analytics can all run locally.

For example, a list screen can open with a regular SQL query on the frontend:

SELECT *
FROM records
WHERE workspace_id = ?
ORDER BY created_at DESC
LIMIT 50;

If you need an index, that lives locally too:

CREATE INDEX records_workspace_created_at_idx
ON records (workspace_id, created_at);

This is a normal database sitting right next to the user. Not a just-in-case cache, but a real data source for the interface.

This is where the UI starts to feel faster. Opening a list no longer depends on a round trip to the server. A filter does not turn into another API call. Sorting does not wait for a database on the other side of the world. Analytics can run right on the device.

We did not do formal before-and-after benchmarks. In our case, the main problem was not that the backend responded too slowly. It was that the interface depended on network speed far too often.

To the user, this does not look like “we optimized a query.” The product simply behaves differently: the screen appears right away, transitions feel calmer, and loaders disappear from places where they used to feel inevitable.

The backend and PostgreSQL still matter. They are involved in synchronization, initial loading, permission checks, and persistence. But a routine screen read no longer has to go through the API every single time.

A separate token for sync

We separated the app’s normal authorization flow from access to the sync layer.

Download the Medium App
PowerSync uses a separate short-lived JWT. The client calls the regular API, the backend validates the user, and then issues a token specifically for sync.

class GetPowerSyncToken(APIView):
    permission_classes = [IsAuthenticated]

    def get(self, request):
        token = create_powersync_jwt(str(request.user.id))

        return Response({
            "token": token,
            "powersync_url": settings.POWERSYNC_URL,
        })

PowerSync checks the claims in that token and uses them when applying sync rules.

That split turned out to be convenient. The regular app session has its own lifecycle. Sync gets a separate short-lived pass.

Local mutations

The biggest shift on the frontend was that we stopped treating a save as an immediate POST to the backend.

The frontend updates the local database first.

await powerSync.writeTransaction(async (tx) => {
  await tx.execute(
    `INSERT INTO records (id, workspace_id, amount, created_at)
     VALUES (?, ?, ?, ?)`,
    [id, workspaceId, amount, createdAt]
  );

  await tx.execute(
    `UPDATE categories
        SET usage_count = COALESCE(usage_count, 0) + 1
      WHERE id = ?`,
    [categoryId]
  );
});

One local transaction can update several related entities.

The user does not have to wait for the server to confirm anything. They see the result immediately, while synchronization and validation catch up in the background.

Upload is a separate pipeline

Offline changes how people use the app.

The same record might be edited several times before the app gets a connection again.

update title
update amount
update category
update title again

If you send every intermediate state to the server, you create a lot of noise. In most cases, the backend needs the final version of the row, not the entire story of how the user got there.

So before upload, we compact the queue.

const transaction = await database.getNextCrudTransaction();
const byKey = new Map();

for (const item of transaction.crud || []) {
  const key = `${item.table}::${item.id}`;
  const previous = byKey.get(key);

  byKey.set(
    key,
    previous ? mergeOperations(previous, item) : item
  );
}

const batch = [...byKey.values()];

await postBatchWithRetries(uploadUrl, batch);
await transaction.complete();

We group operations by row and send only what actually needs to be applied on the server.

Less noise. Fewer duplicate operations. Fewer strange edge cases when the connection comes back.

The backend is still in charge

Local-first does not mean the frontend becomes trusted.

Yes, the user writes data locally first. Yes, the UI updates immediately. But the backend still validates every operation that arrives from the queue.

for index, operation in enumerate(batch):
    try:
        with transaction.atomic():
            action = operation["op"]
            table = operation["table"]
            row_id = operation["id"]
            data = operation.get("data", {})

            if action == "PUT":
                apply_put(table, row_id, data)
            elif action == "PATCH":
                apply_patch(table, row_id, data)
            elif action == "DELETE":
                apply_delete(table, row_id)
            else:
                raise ValidationError("Unsupported operation")

    except ValidationError as exc:
        errors.append({
            "index": index,
            "table": operation.get("table"),
            "id": operation.get("id"),
            "retryable": False,
            "detail": str(exc),
        })

Permissions, limits, entity relationships, field validation, all of that still lives on the server.

PowerSync helps move changes around. It should not become a way around business logic or security.

Cross-platform became simpler

Another practical upside is that the same code can be used across different platforms.

In our case, one approach works for web, PWA, Android TWA, and an iOS WebView wrapper. The shells are different, but the data logic stays shared.

Platform-specific details do not disappear. Storage, permissions, push notifications, background behavior, you still have to think about all of that, especially on mobile.

But the code itself does not have to be rewritten for every platform. Reads are local. Writes are local. Sync happens in the background.

For users, that feels much closer to a native app, even if there is still a web UI under the hood.

Minimal self-hosted deployment

You can run this architecture with Docker Compose.

At minimum, you need frontend, backend, PowerSync, and PostgreSQL.

services:
  frontend:
    build:
      context: ./frontend
    ports:
      - "4173:4173"

  backend:
    build:
      context: ./backend
    ports:
      - "8000:8000"

  powersync:
    build:
      context: ./powersync
    command: ["start", "-r", "unified"]
    ports:
      - "7001:7001"
    volumes:
      - ./powersync/config:/config

  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: app
      POSTGRES_USER: app
      POSTGRES_PASSWORD: change_me

A simplified PowerSync config:

replication:
  connections:
    - type: postgresql
      uri: !env PS_DATA_SOURCE_URI
      sslmode: disable

storage:
  type: postgresql
  uri: !env PS_STORAGE_PG_URI
  sslmode: disable

sync_rules:
  path: sync_rules.yaml

client_auth:
  jwks_uri: !env PS_JWKS_URL
  audience:
    - !env PS_AUDIENCE

PS_DATA_SOURCE_URI points to the main PostgreSQL database.

PS_STORAGE_PG_URI is used for PowerSync’s own storage.

PS_JWKS_URL lets PowerSync validate JWTs.

The tradeoffs

Nobody warned us how uncomfortable the middle ground would feel.

Migrations. We didn’t want to write migration files for the frontend — it felt wrong, like maintaining a database on the client. So we took the lazy path: on schema change, wipe the local database and rebuild from sync. It works. But the first time a user opened the app after an update and stared at a loading screen while everything re-synced, we felt it. Not a crash, not a bug — just a bad moment that didn’t have to happen.

Conflicts. We had a real one. User A edited a record while offline. User B edited the same record online — change landed on the server. User A came back online, upload queue fired, and quietly overwrote User B. Last Write Wins did exactly what it was supposed to do. That was the problem. Nobody lost data in a way the system could detect. It just disappeared.

The mental cost. The hardest part isn’t the code. It’s that you now have two databases to keep in sync — local and server — and when something looks wrong, you have no idea which one is lying. We’ve caught ourselves wanting to just add a normal API endpoint because it would be easier to reason about. Sometimes that instinct is right. Sometimes it’s just habit. Hard to tell in the moment.

Security is also an open question. The local database sits on the user’s device. PowerSync handles access control through sync rules, and the backend validates every upload. But the surface area is larger than a classic API. Something to think about before you go local-first with sensitive data.

Was it worth it?

Honestly, the first two weeks after the switch were rough. Local state, upload queue, sync layer, server — debugging something meant figuring out which of the four places was lying to you. We broke things. We fixed them. We broke them again in a different way.

But then it clicked.

And now, when I open Finsight on a shaky subway connection and the UI just reacts without a single loading spinner, I remember why we did it. The app finally feels “light” again, just like it did in the early MVP days, but now it’s built to scale.

Next time, I will write about how we added E2E encryption on top of this local database, so even we cannot see what users store.