Soft Deletes Are an API Contract

A diary deleted on the phone should disappear on the tablet. That sounds like a storage problem — stamp deleted_at, filter it out of reads, done. In a local-first app it is also a wire problem. The second device never opens the server's SQLite file. It only sees whatever GET /api/v1/sync returns. If that response quietly omits tombstones the way a feed query does, the deletion never travels, and the tablet keeps showing a row the user already threw away.

We already wrote about the on-device half of this: soft deletes in the local-first mobile database — tombstones, pending-push guards, and why a hard DELETE resurrects rows. This post is the contract that sits between Turso and Expo: how a server tombstone becomes a typed change a client can apply without guessing.

TL;DR

• A hard delete (or a soft delete that never leaves the DB) loses the fact sync needs: other devices have no event to apply.
• Storyie reuses one deletedAt() helper on server schemas — NULL means alive, non-null means tombstone — shared by diaries, notes, and entries.
• The sync pull contract is a discriminated union: upsert carries the full row; delete carries only id + deletedAt at the top level.
• The pull cursor pages on updatedAt (deletes bump it too). since is inclusive; serverTime is for skew, not advancement.
• Live queries filter isNull(deletedAt). Sync queries deliberately do not. Mixing those two filters is how deletions vanish from the API.

A hard delete (or a silent omit) loses the fact

Imagine the sync pull returned only "live" rows — the same shape as a diary list, filtered with WHERE deleted_at IS NULL. Device A deletes an entry. The server stamps a tombstone. Device B pulls. The deleted row is absent from the page. Device B's local SQLite still has the old live copy. Nothing in the response says "remove this id." Absence is ambiguous: it could mean "unchanged since your cursor," "never existed for this user," or "deleted." A cursor-based diff cannot encode deletion by omission.

Hard-deleting the server row makes it worse. There is no tombstone left for a later pull to serialize, and any client that never saw the delete keeps its local copy forever. Soft delete without a delete event on the wire is the same failure mode with nicer storage.

So the contract has to be positive: every tombstone that falls in the cursor window must appear as an explicit change. Presence of type: "delete" is the fact. That is the difference between a column and an API.

One reusable column: NULL alive, non-null gone

On the server, the tombstone is not reinvented per table. @storyie/database exposes a single helper:

// packages/database/src/lib/sqlite-helpers.ts (excerpt)
/**
 * deleted_at: nullable, no default. NULL = row is alive; non-null = tombstone.
 * Used for soft-delete / sync tombstone support (epic #777).
 * Consumers MUST filter `IS NULL` for live records unless intentionally fetching deleted rows.
 */
export const deletedAt = () => integer("deleted_at", { mode: "timestamp_ms" });

Diaries, notes, and diary entries all call deletedAt() on their Drizzle schemas. The timestamp (not a boolean) matters for the same reason it does on device: last-write-wins and tombstone guards need when the row died, not only that it did. Soft-delete mutations bump updated_at together with deleted_at, so a deletion is ordered like any other edit in the sync stream.

The helper's comment is the contract in miniature: most callers must filter live rows; sync is the intentional exception.

The union: upsert full row, delete only id + deletedAt

The frozen pull shape lives in @storyie/api-contract, shared by the Hono route and the Expo client:

// packages/api-contract/src/sync.ts (excerpt)
/**
 * CONTRACT FREEZE (#782): this is a discriminated union on `type`.
 * - `upsert` carries the full row in `data` (for a live row, `data.deletedAt` is null).
 * - `delete` carries the id and `deletedAt` TOP-LEVEL (NOT inside `data`) so a tombstone
 *   can be applied without transmitting the full row.
 */
export type SyncEntityChange<T> =
  | { type: "upsert"; data: T }
  | { type: "delete"; id: string; deletedAt: string };

export type SyncPullResponse = {
  diaries: SyncEntityChange<DiaryApi>[];
  notes: SyncEntityChange<NoteApi>[];
  entries: SyncEntityChange<DiaryEntryApi>[];
  cursor: string | null;
  hasMore: boolean;
  serverTime: string;
};

Putting deletedAt on the delete variant is deliberate. We do not ship a full DiaryApi with deletedAt set and ask the client to notice. A tombstone payload is intentionally small — Lexical JSON and plaintext are irrelevant once the row is gone — and the discriminant makes the client's branch obvious: change.type === "upsert" vs else.

The server maps rows to that union in one place:

// apps/web/app/api/v1/routes/sync.ts (excerpt)
function toChange<R extends { id: string; deletedAt: Date | number | null }, T>(
  row: R,
  mapToApi: (row: R) => T
): SyncEntityChange<T> {
  const deletedAt = toIso(row.deletedAt);
  if (deletedAt) {
    return { type: "delete", id: row.id, deletedAt };
  }
  return { type: "upsert", data: mapToApi(row) };
}

Expo imports the same SyncPullResponse type and applies deletes with the pending-push guard we described in the mobile soft-delete post — soft-tombstone locally unless that entity still has a queued mutation:

// apps/expo/services/syncService.ts (excerpt)
} 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++;
  }
}

The type system and the runtime agree: a delete is not a weird upsert. That agreement is the API contract.

Cursor time: page on updatedAt, keep serverTime separate

The query params are simple — optional since, optional limit — but the semantics matter:

// packages/api-contract/src/sync.ts (excerpt)
export const SyncPullQuerySchema = z.object({
  /** ISO-8601 cursor — return changes with updatedAt/deletedAt strictly after this. Omit for a full pull. */
  since: z.string().optional(),
  /** Page size hint. Server clamps to ≤500 and defaults to 200 when omitted. */
  limit: z.coerce.number().int().positive().optional(),
});

In practice the route pages on updatedAt with an inclusive >= filter. Deletes participate because soft-delete updates updated_at. Using a strict > at the page boundary could drop rows that share the exact cursor millisecond; re-sending the boundary row is cheap because the client dedups by id across the whole pull session. The response cursor is a watermark derived from the page's updatedAt frontiers (with a careful min-frontier rule when any entity hits limit), and hasMore tells the client to loop.

serverTime is returned on every page and deliberately not used as the cursor. It exists so the client can reason about clock skew — the same product concern that shows up in last-write-wins — without advancing the sync watermark off a clock that is not the change stream.

Query discipline: live filters vs sync includes

The easiest way to break this contract is to reuse a "correct" diary query on the sync path. Live helpers correctly hide tombstones:

// apps/web/lib/db/queries/diary.ts (excerpt)
async getUserDiaries(userId: string, limit = 10) {
  return await db
    .select()
    .from(diaries)
    .where(
      and(eq(diaries.userId, userId), isNull(diaries.deletedAt), isNull(diaries.sharedJournalId))
    )
    .orderBy(desc(diaries.diaryDatetime))
    .limit(limit);
}

Sync helpers do the opposite on purpose:

// apps/web/lib/db/queries/sync.ts (excerpt)
/**
 * Unlike the live-read query helpers, these INTENTIONALLY include tombstones
 * (rows where `deletedAt` is non-null) — the /sync endpoint must transmit
 * deletions so offline clients can apply them locally.
 *
 * `since` is INCLUSIVE (`>=`, not `>`): a strict `>` could drop rows that share
 * the exact boundary millisecond with the previous page's cursor. The Expo
 * client (#787) dedups changes by id, so re-sending a boundary row is harmless.
 */
export const syncQueries = {
  async getDiaryChanges(userId: string, since: Date | undefined, limit: number) {
    const conditions = [eq(diaries.userId, userId), isNull(diaries.sharedJournalId)];
    if (since) {
      conditions.push(gte(diaries.updatedAt, since));
    }
    return await db
      .select()
      .from(diaries)
      .where(and(...conditions))
      .orderBy(asc(diaries.updatedAt))
      .limit(limit);
  },
  // ...
};

No isNull(deletedAt) here. That omission is the feature. Two query families, one schema column, one wire union: that split is what keeps soft delete honest across devices.

Takeaways

• Soft delete is not finished when the column exists — it is finished when every other replica receives a typed tombstone.
• Reuse one deletedAt() helper; bump updated_at on delete so the cursor stream stays single-keyed.
• Prefer a discriminated upsert | delete over "full row with deletedAt set" so clients cannot mis-handle a tombstone as a live write.
• Keep live reads and sync pulls on different filter rules, and document that in the query module — not in tribal knowledge.

Related reading: Soft Deletes in a Local-First Mobile Database for the Expo tombstone and guards, and Last-Write-Wins Is a Product Decision for how timestamps and pending mutations decide who wins when devices disagree.