Silent Data Loss: Catching a 200-Row Migration Cap
Silent data loss migration bugs are the worst kind of production incident because there's no error. No stack trace. No alert fires. The system reports success and you move on, completely unaware that a meaningful chunk of your data simply stopped existing.
That's exactly what happened during a Notion migration for Nozio. We were moving 794 pages out of Notion and into our own data layer. The migration script ran, reported completion, and everything looked fine in the UI. Except when we started doing row counts, something was very wrong. Every page past position 200 was gone. Not malformed. Not truncated. Gone.
This post is a direct comparison of two approaches: the naive migration pattern (run it, trust it, ship it) versus the dry-run plus idempotency pattern we built after this incident. One of these approaches lets silent data loss migration bugs slip into production. The other catches them before a customer ever sees the damage.
The Naive Approach: Run It and Trust It
The naive migration pattern looks roughly like this in practice. You write a script that pulls data from the source, transforms it, and writes it to the destination. You run it against staging, eyeball the results, and if the UI looks right, you call it done.
The failure mode is obvious in hindsight: you're validating the UI, not the data. If the UI only renders what it received, and the migration silently dropped rows, the UI will look correct for everything it actually has. You won't know about the missing rows unless you're explicitly comparing counts between source and destination.
With Nozio, the Notion API was returning paginated results. The migration script was fetching pages and writing them out, but it had an implicit cap at 200 rows per block query. The Notion API uses cursor-based pagination with a page_size parameter that maxes out at 100 per request, which means you need to follow next_cursor tokens across multiple requests to get a full result set. The script wasn't doing that. It made one request, got up to 200 rows (two pages of 100), and stopped. Silently.
The Notion API documentation is explicit about this. Pagination is cursor-based. If has_more is true in the response, there are more results. If you don't check that field and follow the cursor, you're leaving data behind. No error is thrown. The API did exactly what it was asked to do.
The result: a 794-page database was migrated as a 200-page database. The missing 594 pages didn't error out. They just didn't exist.
The Better Approach: Dry-Run Plus Idempotency
The fix has two parts. They work together, and you need both.
Part 1: The Dry-Run Pass
Before writing anything to the destination, run a complete traversal of the source and collect every record identifier. This is your manifest. Compare that manifest against what you actually wrote after the migration completes. Any identifier in the manifest that isn't in the destination is a drop.
Here's the actual pattern we use now:
async function buildSourceManifest(notionClient: Client, databaseId: string): Promise<Set<string>> {
const manifest = new Set<string>();
let cursor: string | undefined = undefined;
do {
const response = await notionClient.databases.query({
database_id: databaseId,
start_cursor: cursor,
page_size: 100,
});
for (const page of response.results) {
manifest.add(page.id);
}
cursor = response.has_more ? response.next_cursor ?? undefined : undefined;
} while (cursor !== undefined);
return manifest;
}
async function validateMigration(
manifest: Set<string>,
destinationIds: Set<string>
): Promise<{ missing: string[]; extra: string[] }> {
const missing = [...manifest].filter(id => !destinationIds.has(id));
const extra = [...destinationIds].filter(id => !manifest.has(id));
return { missing, extra };
}
The do...while loop is the critical piece. It keeps following next_cursor until has_more is false. This is what the original script was missing entirely.
Run buildSourceManifest before you touch the destination. Store that manifest. After the migration, run validateMigration. If missing has any entries, the migration failed and you haven't written bad state anywhere because the dry run was read-only.
Part 2: Idempotent Writes
The second part is making the migration itself safe to re-run. If your migration script can run multiple times against the same destination without creating duplicates or partial states, you can iterate on it until validateMigration returns empty arrays.
The pattern is upsert-on-external-id:
async function upsertPage(
db: Database,
notionPageId: string,
payload: PagePayload
): Promise<void> {
await db.query(
`INSERT INTO pages (notion_id, title, content, updated_at)
VALUES ($1, $2, $3, NOW())
ON CONFLICT (notion_id)
DO UPDATE SET
title = EXCLUDED.title,
content = EXCLUDED.content,
updated_at = EXCLUDED.updated_at`,
[notionPageId, payload.title, payload.content]
);
}
The notion_id column has a unique constraint. Every write is an upsert. If the migration dies halfway through and you re-run it, already-written rows get updated (idempotent) and missing rows get inserted. When validateMigration comes back clean, you're done.
This also means you can run the migration in chunks, restart it after a transient API error, or run it incrementally as new Notion pages are added. None of those scenarios create inconsistent state.
Direct Comparison: Naive vs. Dry-Run + Idempotency
| Dimension | Naive (Run and Trust) | Dry-Run + Idempotency |
|---|---|---|
| Detects silent drops | No | Yes, before any write |
| Safe to re-run | No (duplicates or errors) | Yes |
| Validates pagination | Only if you remember to | Structurally enforced |
| Failure recovery | Manual cleanup required | Re-run the script |
| Source of truth | The destination (wrong) | The manifest (correct) |
| Time to detect data loss | After a customer reports it | Before the first write |
The naive approach isn't wrong because it's lazy. It's wrong because it validates the wrong thing. Checking that the UI looks right after a migration is checking the output of the output. You need to check the output against the input directly.
Infrastructure Considerations for Larger Migrations
The Nozio migration was 794 pages, which is small enough to fit in memory for the manifest pass. At larger scale, you'd want to stream the manifest to disk or a scratch table rather than holding it in a Set. The validation logic is the same; the storage medium changes.
For migrations with external API rate limits (Notion's API allows 3 requests per second on the free tier), you'll also want a delay between paginated requests:
function sleep(ms: number): Promise<void> {
return new Promise(resolve => setTimeout(resolve, ms));
}
// Inside the pagination loop, after each request:
await sleep(350); // ~2.8 req/s, safely under the 3 req/s limit
The Notion rate limit documentation is worth reading before you run any bulk operation. Hitting the rate limit mid-migration and failing silently is another vector for partial data.
One more thing worth adding: log the manifest size before the migration starts and the destination count after. Even without the full validation pass, a one-line log that says "source: 794, destination: 200" would have caught this immediately. That's a five-minute addition with obvious value.
The Verdict
Use the dry-run plus idempotency pattern. Always. There's no scenario where the naive approach is better. It's not faster to write in any meaningful way, and the cost of discovering a silent data loss migration in production (data reconstruction, customer trust, incident response) is orders of magnitude higher than the cost of adding a manifest pass and switching your writes to upserts.
The specific failure we hit at Nozio (an implicit row cap from missed pagination) is common enough that I'd treat it as a default assumption for any migration touching a paginated API. The Notion API is well-documented about this behavior. The bug wasn't in the API. It was in our assumption that a single request returned a complete result set.
The broader pattern holds for any migration: S3 listing APIs paginate. Stripe's API paginates. Salesforce paginates. If you're migrating from any system with paginated reads, your source traversal must follow cursors to completion, and your validation must compare source identifiers to destination identifiers directly. Not UI state. Not record counts alone (counts can match even with different records). Identifiers.
Build the manifest. Validate against it. Make your writes idempotent. Run it until it's clean. That's the whole thing.
This kind of infrastructure work is the majority of what makes migrations safe at scale. The code is not glamorous. The do...while loop above is not exciting. But it's the difference between a migration that silently drops 75% of your data and one that catches the problem before a single row hits the destination. At Bedda.tech, this pattern is now the starting point for any migration we run, not an afterthought we add when something goes wrong.