Soft Deletes in a Local-First Mobile Database

Deleting a diary entry in Storyie's mobile app feels instant: you swipe, it's gone, and the screen updates before any network call happens. That immediacy is the whole point of a local-first design — the on-device SQLite store is the source of truth, and the server catches up later. But "delete" is the one operation where local-first bites back. If you delete a row while offline and the device later pulls the server's copy of that row before it has pushed the deletion, the row comes back. The user deleted it; the sync engine resurrected it.

We hit exactly this, twice (issues #1366 and #1375), and the fix is not clever — it's a discipline. Every delete in the on-device store is a soft delete: a deleted_at timestamp, never a DELETE statement. This post walks through why a hard delete is unsafe here, the one-column tombstone contract, how deletes flow in both directions, and the two guards that keep a deleted row deleted. Every excerpt is from apps/expo/lib/db/ and apps/expo/services/syncService.ts.

TL;DR

• A hard DELETE in a local-first store loses the information sync needs: the next server pull re-inserts the row because nothing recorded that it was deleted.
• Storyie uses tombstones — a nullable deleted_at column (NULL = alive, non-null = deleted). All reads filter isNull(deletedAt), enforced in the query layer so no caller can forget.
• Pushing a delete: call the server first, tombstone locally only after it confirms; only a 404 ("already deleted elsewhere") is safe to ignore — every other error is retried.
• Pulling a delete: apply the server's tombstone locally unless there's a queued local mutation for that row — the pending-push guard stops a stale server copy from clobbering an uncommitted local delete.
• A second guard covers the case where the local delete's retries are exhausted: a write-back never overwrites a local tombstone that is at least as new as the server row.
• We do not compact tombstones on-device yet. That's a deliberately unpaid debt, discussed at the end.

Why a hard DELETE resurrects the row

The mobile app never talks to the database directly for its screens — it reads from the local SQLite mirror and syncs in the background. Reconnect sync has two halves, in a fixed order:

// apps/expo/services/syncService.ts (excerpt)
export async function refreshOnReconnect(_userId: string): Promise<void> {
  try {
    // ...
    // 1. Push any queued local mutations to the server.
    await processSyncQueue();
    // 2. Pull server changes since the last cursor.
    await pullSinceCursor();
    // ...
  } catch (error) {
    logger.error("[SyncService] Failed to refresh on reconnect:", error);
  }
}

Now imagine deleteDiary(id) ran a real DELETE FROM local_diaries WHERE id = ?. The row is gone locally. The deletion is queued for push. But the push and the pull are separate triggers — a foreground event, an interval, a reconnect can each fire pullSinceCursor() on its own. If a pull runs and the server still has that diary (because our push hasn't landed), the pull sees a row the local database doesn't have and does the only reasonable thing: it inserts it. The user's delete is undone by our own sync.

The root problem is that a hard delete throws away the fact of deletion. There is no local record that says "this row was intentionally removed," so the pull cannot distinguish "deleted" from "never seen." A tombstone keeps that fact around long enough for sync to act on it.

Tombstones: one nullable column, one invariant

The tombstone is a single nullable timestamp column, defined once in a shared helper and reused by every table:

// apps/expo/lib/db/helpers.ts (excerpt)
/**
 * deleted_at: nullable, no default. NULL = row is alive; non-null = tombstone.
 * Semantics identical to the server schema — consumers MUST filter IS NULL
 * for live records unless intentionally fetching deleted rows.
 */
export const deletedAt = () => integer("deleted_at", { mode: "timestamp_ms" });

Choosing a timestamp rather than a boolean is_deleted is not cosmetic: the when is load-bearing. The guard below checks whether the local row is a tombstone (deleted_at is non-null) and then compares updated_at to decide whether an incoming server row is newer than that tombstone. A boolean flag would throw away exactly the information those comparisons need. The semantics also match the server's Turso schema column-for-column, so a tombstone means the same thing on both sides of the wire.

The invariant that makes tombstones safe is that reads never surface them. We enforce it in the query layer, not in each screen:

// apps/expo/lib/db/queries/diaries.ts (excerpt)
/**
 * ALL selects MUST filter `isNull(deletedAt)` — tombstones are never surfaced
 * to callers.  This invariant is enforced here so no caller can forget.
 */
export async function getDiaryById(userId: string, id: string): Promise<LocalDiaryRow | undefined> {
  const db = await getLocalDb();
  const rows = await db
    .select()
    .from(localDiaries)
    .where(
      and(eq(localDiaries.userId, userId), eq(localDiaries.id, id), isNull(localDiaries.deletedAt))
    )
    .limit(1);
  return filterLiveRows(rows, "local_diaries")[0];
}

Soft-deleting is then just an update — the row is never physically removed on-device:

// apps/expo/lib/db/queries/diaries.ts (excerpt)
export async function softDeleteLocalDiary(id: string): Promise<void> {
  const db = await getLocalDb();
  const now = new Date();
  await db
    .update(localDiaries)
    .set({ deletedAt: now, updatedAt: now })
    .where(eq(localDiaries.id, id));
}

Note it bumps updated_at alongside deleted_at. The deletion is an edit like any other, and dating it correctly is what lets last-write-wins reason about it later.

Pushing a delete: tombstone only after the server confirms

When a queued delete is pushed, the ordering matters. We call the server first, and tombstone the local row only after the call resolves:

// apps/expo/services/syncService.ts (excerpt) — processDiarySync, "delete" case
case "delete": {
  // Delete on server. Only "not found" means the delete already happened
  // elsewhere — every other error (500, permission, network) must be
  // retried, keeping the entity in cache so we never diverge from server.
  try {
    await deleteDiaryOnServer(item.entityId);
  } catch (error) {
    if (!isNotFoundError(error)) {
      throw error;
    }
    logger.info(`[SyncService] Diary ${item.entityId} already deleted on server`);
  }
  // Tombstone in SQLite
  await softDeleteLocalDiary(item.entityId);
  break;
}

The subtlety is the error handling. A 404 is success for a delete — it means someone (another device, the web app) already removed it, so the outcome we wanted is already true. Every other failure — a 500, a permission error, a dropped connection — must throw, which marks the queue item failed and leaves it to be retried with backoff. If we tombstoned optimistically on any error, a transient 500 would hide the row locally while it still lived on the server, and the two would silently diverge. Confirm-then-tombstone keeps local and server in agreement.

Pulling a delete: the two guards that stop resurrection

The dangerous direction is the pull. pullSinceCursor walks the server's change feed and, for each delete change, applies a local tombstone — but only after checking that we don't have our own in-flight mutation for that row:

// apps/expo/services/syncService.ts (excerpt) — pullSinceCursor, diary deletes
} else {
  // delete tombstone
  const id = change.id;
  if (seenDiaries.has(id)) continue;
  seenDiaries.add(id);
  // Pending-push guard: don't clobber an uncommitted local delete.
  if (!(await hasPendingSyncForEntity("diary", id))) {
    await softDeleteLocalDiary(id);
    appliedCount++;
  }
}

hasPendingSyncForEntity is the whole guard. It answers one question — is there a queued mutation for this entity that hasn't finished pushing?

// apps/expo/lib/db/queries/sync-queue.ts (excerpt)
export async function hasPendingSyncForEntity(
  entityType: SyncQueueEntityType,
  entityId: string
): Promise<boolean> {
  const rows = await getSyncQueueRowsByEntity(entityType, entityId);
  return rows.some((r) => r.status === "pending" || r.status === "syncing");
}

The same guard protects upserts, not just deletes. When the pull tries to write a server record back into SQLite, upsertDiaryFromApi bails out if a local mutation is still queued — the local row is authoritative until the queue flushes. This is what prevents a stale server copy from overwriting an offline edit or resurrecting an offline delete that hasn't been pushed yet.

But there's a hole in that first guard, and it's the bug from #1366/#1375. A queued delete doesn't stay pending forever — if its retries are exhausted it ends in status failed, which hasPendingSyncForEntity does not count. The guard opens, and a subsequent write-back could resurrect the tombstone. So upsertDiaryFromApi carries a second, timestamp-based guard:

// apps/expo/lib/db/queries/diaries.ts (excerpt) — upsertDiaryFromApi
// LWW write-back guard — do not overwrite uncommitted local changes.
if (await hasPendingSyncForEntity("diary", row.id)) return;

const db = await getLocalDb();

// Tombstone guard (#1366): a queued delete op can end in status `failed`
// (retries exhausted), which clears the pending-sync guard above but must
// still stop this write-back from resurrecting the local tombstone. A
// genuinely newer server-side edit (from another device) still wins.
const [existing] = await db
  .select({ deletedAt: localDiaries.deletedAt, updatedAt: localDiaries.updatedAt })
  .from(localDiaries)
  .where(eq(localDiaries.id, row.id))
  .limit(1);
if (existing?.deletedAt != null && existing.updatedAt >= row.updatedAt) return;

Read the last line carefully — this is the trade-off, not a blanket "tombstones always win." If the local row is a tombstone and its updated_at is at least as new as the incoming server row, skip the write-back: our deletion is the latest word. But if the server row is genuinely newer — someone edited the diary on another device after we deleted it here — the guard lets the write through, and the row comes back. That's not resurrection by accident; it's last-write-wins doing its job, and the newer edit really is the last write. The two guards together cover the full lifecycle of a delete: pending/syncing (first guard) and failed (second guard).

This also explains the deletion-detection path for records that vanish without a change-feed entry. detectAndRemoveDeletedDiaries diffs the cached IDs against what the server returned for a month and tombstones the difference — again via softDeleteLocalDiary, never a hard delete, so the same guards apply on the next pull.

Compaction: the debt we chose not to pay yet

Tombstones solve resurrection by keeping deleted rows around. The obvious cost is that they never leave: softDeleteLocalDiary sets deleted_at and stops. There is no on-device job that purges old tombstones, no VACUUM, no compaction pass. Every diary you have ever deleted still occupies a row in the local SQLite file, invisible to reads but real on disk.

We left it that way on purpose, for now. The safe time to physically remove a tombstone is after every device has pulled the deletion — otherwise you reintroduce the original bug, because a device that never saw the tombstone will happily re-create the row. Getting that right needs a per-device acknowledgment cursor or a server-authoritative "safe to purge before timestamp T," and neither exists yet. Against that complexity, the actual cost today is tiny: a personal journaling app deletes rows rarely, and a few hundred stale tombstone rows in an on-device database is not a problem worth a distributed-GC design. When deletion volume or storage pressure makes it one, compaction becomes its own feature with its own tests. Until then, the honest state of the code is: we mark, we never sweep.

Takeaways

• In a local-first store, deletion is data. A hard DELETE discards the fact sync needs; a tombstone preserves it.
• Enforce "reads never see tombstones" in one place — the query layer — so no screen can leak a deleted row.
• Order the write: confirm on the server, then tombstone locally; treat only 404 as success.
• Resurrection has two windows — a delete that's still queued and a delete that has failed — and each needs its own guard. Timestamps, not booleans, are what let the second guard tell a stale copy from a genuinely newer edit.
• Not compacting is a valid answer as long as you can say why it's safe to defer.

This post is the deletion half of Storyie's sync model. For how the same last-write-wins comparison decides ordinary edit conflicts, see Last-Write-Wins Is a Product Decision. For how deletions cascade on the server side, see When Cascades Don't Cascade. And for the write path that produces these local rows in the first place, see Local-First Autosave Without Losing the Last Keystroke.