References to records outside the sync

A reference field points to records in another table. Apps call these by different names, such as linked records, foreign keys, relations, or associations.

When you sync a table through a filtered view (such as an Airtable view), records in other tables can still reference records that aren't in the view. This page covers what Whalesync does with those references.

The examples use a Projects table synced through a view of active projects, and a Tasks table whose Project field references it. Both are in Airtable, and both sync to Postgres.

What syncs

Whalesync only syncs a reference when both records are part of the sync. If a task references a project outside the view, that reference is left out on the other side:

• A field that holds one reference, such as a Postgres foreign key column, is left empty. If the task also references synced projects, the field gets the first of those instead.
• A field that holds several references gets only the references to synced records.

The task still syncs with all its other fields. A left-out reference doesn't create an issue or hold the task back, as long as the field on the other side can be empty.

When the referenced record joins the sync

If the project later becomes part of the sync, for example because it enters the view, the reference fills in the next time Whalesync reads the task from Airtable, such as when the task changes or at the next full sync.

Editing the reference field on the other side

When the reference field syncs both ways and someone edits it on the other side, Whalesync writes that side's value back. That replaces the whole field. The other side never had the references to records outside the sync, so those references are removed. In the example, editing a task's Project field in Postgres removes the task's references to projects outside the view from Airtable.

Only edits to the reference field itself do this. Edits to the record's other fields don't touch it. When the field holds a single reference in both apps, this is just the edit the person made.

When a referenced record leaves the view

A record that leaves the synced view is treated as deleted. Whalesync deletes it on the other side unless Delete Protection is on for the table there, or the delete approval queue holds the delete until you approve it.

Once Whalesync acts on the delete, references to that record are left out like any other reference to a record outside the sync. In the example, each task that referenced the project gets an empty Project field in Postgres the next time Whalesync reads the task from Airtable. This happens even when the reference field syncs one way.

Foreign keys in Postgres and Supabase

Databases enforce their foreign keys, which adds two things to plan for when one is on the other side of the sync.

The foreign key column must allow NULL. Whalesync writes NULL for a reference it left out. If the column is NOT NULL, the database rejects that record, and it shows up as an issue.

By default, the database refuses to delete a row that other rows still point to. So when a project leaves the view, deleting its row in Postgres fails while tasks still reference it. The failed delete shows up as an issue on that project, and Whalesync doesn't retry it on its own. Retrying it works once no tasks reference the project anymore.

Creating the foreign key with ON DELETE SET NULL lets the delete go through and sets project_id to NULL on the tasks that pointed to it:

CREATE TABLE tasks (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text,
  project_id uuid REFERENCES projects(id) ON DELETE SET NULL
);

Whalesync sees that as an ordinary edit to each task. If the reference field syncs both ways, the reference can then be removed in Airtable too, even though the project still exists there. It only left the view.

Sync the reference field one way

When the referenced table is synced through a filtered view, sync the reference field one way, from the app that has the view to the other app. In the field mapping, set its direction arrow to point away from that app. Then neither an edit on the other side nor ON DELETE SET NULL can remove references in the app that has the view.

The tradeoff is that changes made to that field on the other side don't reach the app that has the view. Whalesync overwrites them the next time it writes the field from that app, such as when the reference changes there, or when a record it references leaves or joins the sync.