A Failed Lookup Looks Like Nothing There

Some systems will take a record and work out for themselves whether it is new. Others make you find out first. The difference sounds like a detail of the interface and it is not: the moment you have to look before you write, you have taken ownership of a question the other system used to answer, including what happens when the looking goes wrong. This connector looks first, because there is no alternative, and this post is about what that costs.

Two requests where other connectors need one

The Airtable connector in this series sends one request per record. It says here is a record, merge it on this field, and the matching happens where the data lives.

Notion has no equivalent. Creating a page and updating a page are different operations, at different addresses, with different verbs and different request bodies — a create names the database it belongs to, an update must not.

So each record becomes a query for a page with that exact title, followed by either an update of what was found or the creation of a new page. Two calls, and the second one's shape depends on the first one's answer.

What null means

The lookup returns either the identifier of a matching page or nothing. Nothing means create.

Read that again with a network in mind, because there are two ways to get nothing. The query can succeed and find no match — the ordinary case for a new customer. Or the query can fail: a rate limit, a timeout, a bad gateway, a moment when Notion was busy.

Both produce nothing, and nothing means create. So a transient failure on the lookup does not surface as an error; it surfaces as a second page with the same title as the first, some weeks later, noticed by somebody wondering why there are two entries for the same supplier.

A lookup that fails and a lookup that finds nothing are the same value. The difference between them is a duplicate.

This is worth stating plainly rather than leaving in the code, because it is the characteristic failure of every read-then-write integration and it is invisible from both ends. Notion logged a request that failed and was retried by nobody. We logged a page created successfully. Both records are accurate.

Distinguishing the two states is a small change — the query already knows whether it succeeded, and treating a failed lookup as skip this record and try next run rather than create turns a silent duplicate into a record that syncs a few minutes later. It is a good example of the kind of improvement that is invisible until it is written down, which is the reason to write it down.

And the gap between the two calls

The second consequence of looking first is a window, and it is a real one rather than a theoretical one.

Two runs of the same sync starting close together — somebody presses the button while a scheduled run is already going — both query for the same title, both find nothing, and both create. There is no lock, and there is nothing in Notion preventing two pages from sharing a title.

A server-side merge cannot do this. The decision and the write are one operation, so there is no interval for a second caller to occupy. That is the real advantage of the one-request design, and it is not about speed.

The practical mitigation today is that the sync is queued rather than run inline, so the ordinary case is one run at a time. The honest description is that the guarantee comes from the arrangement rather than from the write itself.

One match, and only ever one

The lookup asks for a single result, which has a consequence worth knowing if duplicates already exist.

Where two pages share a title, the query returns the first and the sync updates it. The second is never touched — not deleted, not updated, not reported. It sits there indefinitely holding whatever it held when it was created.

That is the right default. Merging duplicates is a decision with data loss in it and it should be made by a person looking at both pages, not by a sync that noticed there were two. What it means in practice is that duplicates are cleaned up in Notion, once, and then stay cleaned up.

A value has to be shaped for its column

The other thing a two-request write inherits is that both branches take the same property shapes, and Notion's property shapes are typed.

An email address is not a string in a Notion payload. It is a structure whose form depends on whether the destination column is an email column, a phone column, a link column or a text column — four different envelopes around the same characters.

So the database's schema is read once per run, each optional value is looked up against it, and the value is wrapped in the envelope its column needs. A column that is absent is skipped, exactly as in the Airtable connector.

A column that is present but of a type this connector does not shape — an email held in a select column, say, or a phone number in a number column — is also skipped, and the page still lands with its title and whatever else was shapeable. A property it cannot express is left out rather than allowed to reject the page, which is the same degrade-rather-than-stop principle applied one level down.

The four shapes covered — email, phone, link and text — are the ones a contact record actually needs. The remaining types are mostly things a name and an email address have no business being put into, and adding a shape for a specific one is a contained piece of work when a workspace turns out to want it.

What a run costs

Two requests per record is worth pricing, because it is the number that decides how this behaves at volume.

1. One schema read per run

   The database structure is fetched once and reused, rather than once per record — which is the difference between one request and two hundred.

2. Two requests per record after that

   A lookup and a write. Two hundred customers is four hundred calls, and Notion rate-limits per integration, so the ceiling on a run is theirs rather than ours.

3. Two hundred records per type, newest first

   The same bound as the sibling connector, and the same statement about it: it is a number, and pushing a complete list is a different feature.

The one-per-record lookup is the obvious thing to improve, and the shape of the improvement is already in the code for a different fact: the schema is read once and reused. A single query returning the titles already present would replace two hundred lookups with one page of results, and it would close the failed-lookup ambiguity at the same time, because a list that arrived is distinguishable from a list that did not.

What is in place

Three positions held on purpose

• Duplicates in the destination are left alone. A sync that found two pages with one title updates the first and reports nothing about the second, because deciding which of two records survives is a judgement with data loss in it and it belongs to a person with both pages in front of them.
• A property we cannot express is dropped, not forced. Sending a malformed value would reject the whole page, so an unshapeable column costs that one field and nothing else.
• The two-request cost is stated rather than hidden. Four hundred calls to sync two hundred records is the price of a destination with no merge, and a single-query improvement is worth naming out loud because it closes the duplicate ambiguity as a side effect.

Frequently asked questions