I didn't expect an internal business application to teach me this much about database architecture.
The application receives hundreds of transactions every day. Cloudflare D1 was perfectly capable of handling the writes.
The problem started when the data grew.
Reports needed to process historical transactions, and new clients needed to download that same history to initialize their local database.
Instead of throwing a bigger database at the problem, I moved more of the workload to the client.
The final architecture uses Cloudflare D1, Workers, R2 and RxDB.
TL;DR
- D1 is the source of truth.
- Workers handle the API and synchronization.
- RxDB keeps a local copy of the data and runs reports locally.
- R2 stores periodic snapshots for fast initial synchronization.
- New clients load a snapshot and then sync only the changes after its checkpoint.
The important idea:
«Don't repeatedly read or transfer data that can already exist locally.»
Why D1?
The application has a relatively small number of users, so I didn't need a large database cluster.
D1 gave me a managed SQLite-based SQL database without having to manage:
- Database servers
- Backups
- Replication
- Database upgrades
- Infrastructure
For the transactional workload, it worked very well.
The problem wasn't writing data.
It was reading large amounts of data repeatedly.
Problem 1: Reports were becoming expensive
Let's use a simple example.
Suppose the application receives:
100 transactions/day
After 1,000 days:
100 × 1,000 = 100,000 transactions
Now imagine a report that needs to calculate balances from transaction history.
The final result might contain only 20 rows.
But the database may need to read thousands of transaction rows to calculate those 20 rows.
That's important with D1 because usage is based on rows read, not simply the number of queries.
So repeatedly running large analytical queries against D1 wasn't ideal.
Why not just cache the reports?
Because the underlying data changes constantly.
Every new transaction can affect balances and historical calculations.
I could build a complicated server-side aggregation/cache system, but this was an internal application and there was another interesting option:
The user already needs the data.
Why not calculate the report where the data is being consumed?
Enter RxDB
I used RxDB as the local database in the frontend.
RxDB is a JavaScript/TypeScript database designed for local-first applications. It provides local persistence, reactive queries and synchronization capabilities.
Instead of:
Frontend
↓
Workers
↓
D1
↓
Scan thousands of rows
↓
Return report
I changed it to:
D1
↓
Sync
↓
RxDB
↓
Local query
↓
Report
The D1 database remains the source of truth.
RxDB becomes the local working copy.
Now a report doesn't need to repeatedly ask D1 to scan historical transactions.
The calculation happens directly on the device.
This gave me two major benefits:
- Much faster report generation
- Far fewer D1 reads
And because the application is internal, keeping a substantial local dataset was a reasonable trade-off.
Problem 2: The first sync
Then I found another problem.
If RxDB needs the historical data, what happens when a new user joins?
Imagine the database now contains:
100,000+ transactions
A new device would traditionally need to download all of them:
D1
↓
100,000 records
↓
Network
↓
RxDB
That means a large initial transfer before the user can get the full local experience.
I needed a way to bootstrap the local database without replaying the entire history.
Checkpoint snapshots
The solution was to introduce snapshots.
Periodically, I generate a snapshot of the current state and store it in Cloudflare R2.
For example:
Current database
100,000 records
Snapshot
checkpoint = 98,000
A new client doesn't need to start from record 1.
It can:
- Download snapshot from R2
- Import snapshot into RxDB
- Tell the sync layer: "I'm at checkpoint 98,000"
- Pull changes after 98,000
So instead of:
100,000 records
the synchronization path becomes:
Snapshot
+
Records 98,001 → 100,000
If only 2,000 records changed after the snapshot, that's all the incremental sync needs to process.
A simple checkpoint model
The important part is that the snapshot and the synchronization system share a checkpoint.
For example:
type Snapshot = {
checkpoint: number
createdAt: number
data: Record[]
}
A snapshot might look conceptually like:
{
"checkpoint": 98000,
"createdAt": 1788150000000,
"data": [
// local database state
]
}
The client stores the checkpoint after importing the snapshot.
Then the sync request can ask for changes after that point:
const changes = await fetch(
/sync?checkpoint=${checkpoint}
).then(res => res.json())
The Worker can then query only the required changes from D1.
Conceptually:
SELECT *
FROM transactions
WHERE sequence_id > ?
ORDER BY sequence_id;
The exact implementation can vary, but the important part is the same:
the client doesn't need to replay the entire history.
The architecture
Cloudflare
│
┌──────────┴──────────┐
│ │
D1 R2
Source of Truth Snapshots
│
│
Workers
API + Sync Layer
│
│
▼
RxDB
Local Database
│
┌─────┴─────┐
│ │
Queries Reports
│ │
└─────┬─────┘
▼
Frontend
The responsibilities are deliberately separated:
D1
Persistent source of truth.
Workers
API, synchronization and business logic.
R2
Cheap snapshot distribution.
RxDB
Local persistence, reactive queries and reporting.
Frontend
User interface and local computation.
The interesting part
The solution wasn't about making D1 process 10× more rows.
It was about avoiding unnecessary work altogether.
Instead of asking:
«"How can I make the database calculate this report faster?"»
I asked:
«"Why does the database need to calculate this report every time?"»
And instead of asking:
«"How can I make initial synchronization transfer 100,000 records faster?"»
I asked:
«"Why does a new client need to transfer all 100,000 records?"»
Those two questions completely changed the architecture.
Move computation closer to the data.
Move initialization closer to a snapshot.
Keep the database as the source of truth.
That's what led me to this D1 + Workers + R2 + RxDB architecture.
Top comments (0)