Backend plan

[zachpmanson]

Hi Zach,

Here's the full plan for adding local-first sync to Stash.

---

Overview

The data layer in src/db/ is already well-suited for sync — UUIDs as IDs, soft deletes via archived_at, and clean separation of concerns. The main gaps are: no updated_at tracking, hard deletes for items/folders, and no sync machinery. All of this is additive and low-risk.

---

1. Schema migrations (database.ts)

Add the following columns via the existing ALTER TABLE migration pattern:

• items: updated_at INTEGER, deleted_at INTEGER
• folders: updated_at INTEGER, deleted_at INTEGER
• item_folders: updated_at INTEGER, deleted_at INTEGER
• New table: sync_state — stores last_pulled_at INTEGER keyed by server URL

updated_at should be set to created_at on insert and bumped on every write.

---

2. Touch updated_at on every write (items.ts + folders.ts)

Every UPDATE statement needs updated_at = ? added. Affected functions:

• items.ts: archiveItem, unarchiveItem, updateItemArticleHtml, addItemToFolder, removeItemFromFolder
• folders.ts: archiveFolder, unarchiveFolder, updateFolderName, updateFolderIcon, touchFolder

---

3. Replace hard deletes with soft deletes

deleteItem and deleteFolder currently run DELETE statements — these need to become UPDATE ... SET deleted_at = ?, updated_at = ? so deletions propagate across devices.

All read queries (getItemsInFolder, getFolders, etc.) already filter on archived_at IS NULL — just add AND deleted_at IS NULL alongside those.

The ON DELETE CASCADE on item_folders should also be replaced with soft-delete logic.

---

4. New file: src/sync/syncService.ts

A single sync() function:

1. Read last_pulled_at from sync_state
2. Collect all local rows where updated_at > last_pulled_at
3. POST /sync/push { items, folders, item_folders }
4. GET /sync/pull?since=last_pulled_at
5. Upsert pulled rows — apply if incoming updated_at is newer than local
6. UPDATE sync_state SET last_pulled_at = server_now

Since it's single-user, last-write-wins is safe.

---

5. New file: src/sync/useSyncStore.ts

A Zustand store (consistent with existing state management) tracking isSyncing, lastSyncedAt, and syncError. Exposes a triggerSync() action that calls syncService.sync() and updates state.

---

6. Sync triggers in _layout.tsx

Call triggerSync() on:

• App foreground (AppState change to 'active')
• Network reconnect (@react-native-community/netinfo)
• After a successful save in the share flow (shareHandler.ts)

---

7. Settings screen (app/settings.tsx)

A small new screen for the user to enter a server URL and auth token, stored in expo-secure-store. The sync store reads credentials from there.

---

Server (new server/ pnpm workspace)

The project already uses pnpm workspaces, so a server/ package slots in naturally. Minimal Go server + Postgres:

• POST /sync/push — upserts batches, stamps server_updated_at
• GET /sync/pull?since= — returns rows where server_updated_at > since, scoped to user_id
• Auth via JWT middleware; user_id from the JWT scopes every query automatically

user_id lives on server tables only — local SQLite stays single-user.

---

Recommended order of implementation

1. Schema changes + updated_at touches (additive, nothing breaks)
2. Soft deletes replacing hard deletes
3. syncService.ts + useSyncStore.ts
4. Server
5. AppState/NetInfo triggers
6. Settings screen

Steps 1–2 can be done and validated entirely without a server.

---

Let me know if you want the actual code for any of these steps.

Claude