I built a compliance engine that checks names against global sanctions lists (OFAC, UK, EU, UN). It runs entirely on Cloudflare Workers and D1. It’s fast, cheap, and mostly runs itself.
Until I woke up to a completely dead ingestion pipeline.
Here is the exact error that greeted me in my GitHub Actions logs:
{
"success": false,
"errors": [{
"code": 7500,
"message": "Your account has exceeded D1's free tier daily row write limit. Upgrade to a paid plan or wait until tomorrow (midnight UTC) to continue."
}]
}
My first thought was a bug in my code. But the truth is simpler: D1's free tier allows 100,000 row writes per day, and my ingestion scripts ate through them before lunch.
Here is what happened, why it’s a common trap, and the architecture changes I made to fix it.
The Trap of "Just Upsert Everything"
My initial ingestion logic was naive. I used a standard INSERT ... ON CONFLICT DO UPDATE (upsert) to sync records from the UN, OFAC, and GLEIF lists into my D1 database.
I thought: "If the record hasn't changed, it won't cost me anything."
That's true for standard Postgres, but D1 charges you for the INSERT attempt, even if the ON CONFLICT clause ends up doing nothing.
When I pulled the UN consolidated list (about 2,000 records) and the GLEIF database (tens of thousands of entities), my script tried to upsert everything at once.
33,000+ sanctions records
Plus daily cron jobs for OFAC, PEP, and UK lists
Plus API search logs
Very quickly, I hit the 100,000 writes/day ceiling. D1 locked my database in read-only mode.
How to Fix It (The 3-Layer Approach)
To survive on the free tier, you have to treat D1 writes like gold. Here is how I refactored my ingestion pipeline.
The WHERE Clause is Your Best Friend
If you are doing an upsert, you should only actually write to the row if the data has changed. You can add a WHERE clause to your DO UPDATE statement.
INSERT INTO sanctions (
list_code, entity_id, name, name_normalized, programs
) VALUES (?, ?, ?, ?, ?)
ON CONFLICT(list_code, entity_id) DO UPDATE SET
name = excluded.name,
programs = excluded.programs
WHERE
sanctions.name != excluded.name OR
IFNULL(sanctions.programs, '') != IFNULL(excluded.programs, '');
By adding this WHERE clause, D1 skips the write if the row is identical. This alone saved me thousands of writes on daily runs where only 1% of the data actually changed.Chunking and MAX_PAGES_PER_RUN
My second mistake was trying to ingest the whole world in one run. GitHub Actions have a 6-hour limit, but your D1 quota doesn't care about your workflow time; it cares about rows.
If you have a massive dataset (like GLEIF), you need an incremental cap. I added an environment variable to my workflow:
env:
MAX_PAGES_PER_RUN: '20' # Never ingest more than 20 pages in a single run
Then, in the script, I break the loop once the limit is reached, saving the offset for the next run. It’s slower to backfill, but it guarantees your database won't go offline.
- Separate the "Read" and "Write" Phases Originally, I fetched data from the API and wrote it to D1 in the same loop. If the API rate-limited me halfway through, I’d have a half-written database.
Now, I use a two-step pipeline:
Fetch Phase: Download the raw XML/CSV to Cloudflare R2 (Object Storage). R2 has a very generous free tier (10GB/month).
Parse & Ingest Phase: Read the file from R2, parse it, and batch-write to D1.
This decoupling means if D1 hits a quota limit, the fetch phase still succeeds. Tomorrow, I can just run the parse phase against the data sitting in R2 without re-downloading it.
What If You Hit the Limit Today?
If you are already locked out, there is no magic bypass. You have to wait until 00:00 UTC.
But you can prepare. Here is the exact check I run in my terminal before triggering a heavy job:
npx wrangler d1 execute compliance-db --remote --command="SELECT COUNT(*) FROM sanctions"
If it's read-only, the command will fail with code 7500 or code 7403. Don't panic—it's just Cloudflare telling you to take a break.
Final Thoughts
Cloudflare D1 is incredible for read-heavy edge applications. It’s practically free and sits right next to your Workers. But it’s not a Postgres replacement for heavy ingestion pipelines.
If you are building a scraper or an ETL pipeline on D1, idempotent upserts and strict batch sizes are not optional. They are the difference between a running app and a dead one.
(If you're building compliance or data tools on Cloudflare and want to compare notes on D1 limits, feel free to drop a comment or reach out.)
Top comments (1)
Just wanted to add some real-world numbers to this post. After waiting for the UTC reset, I ran the new chunked pipeline (MAX_PAGES_PER_RUN and the WHERE clause).
It successfully processed 33,082 records (across OFAC, UK, EU, and UN lists) without even coming close to the limit.
If anyone else is building ETL on D1, this approach definitely works. Good luck out there!