Soft-delete Archived trash + confirm before delete + Railway DB backups

[chasehuh]

Summary

Stop hard-deleting notes on sidebar ×. Match note-app best practice used by Apple Notes / Notion at our scale: confirm before delete → soft-delete into Archived (Recently Deleted) with restore → auto-purge after 30 days, plus Railway Postgres volume backups as the ops backstop for disasters (not for routine undo).

Why This Matters

On 2026-07-16 we lost a production daily note (0715.md, id dif-espx-vmb) after a one-click hard DELETE. There is no trash, no confirm, and Railway volume backups/schedules were empty — so the note was unrecoverable. For a small synced notepad where notes are the product, accidental delete is a data-loss event, not a UX inconvenience.

Conversation Context

• User accidentally deleted 0715.md and asked whether delete archives or wipes. Investigation showed hard delete only; no row, no local cache body, no Railway backups.
• Immediate ask: (1) ask once before delete, (2) make recovery real via archive/soft-delete + proper backup setup.
• Product constraint: keep agentnote minimal (Zed-like chrome). Do not build a heavy admin restore console; a small Archived section in the existing sidebar is enough.
• Ops constraint: Postgres lives on Railway project chasehuh / service Postgres / volume postgres-volume. App is Vercel (cwhuh/memo), live at https://www.agentnote.dev.

Best-practice decision (for this size)

Peers and common SaaS guidance converge on a two-layer model:

Layer	Purpose	Recommended for agentnote
Product trash / soft-delete	User mistakes, late regret	Yes — primary
Confirm before destructive UI	Catch mis-clicks on rare, high-cost actions	Yes — user-requested; notes are high-cost
Short undo toast (5–10s)	Instant “oops”	Optional nice-to-have; not required in v1 if confirm + archive exist
Infra volume/DB backups	Disk wipe, bad deploy, account catastrophe	Yes — ops backstop, not a substitute for trash

Source anchors:

• Apple Notes: delete → Recently Deleted, recover for 30 days, then permanent (Apple Notes Mac).
• Notion: delete → Trash ~30 days, then permanent; they also keep DB backups for support restores (Notion Help).
• Soft-delete data shape: prefer deleted_at TIMESTAMPTZ NULL over boolean; live queries filter deleted_at IS NULL; purge by age (soft-delete lifecycle patterns).
• UX literature often prefers undo over confirm for frequent deletes; for agentnote, delete is infrequent and note content is high-value, so confirm + archive is the right default. Do not rely on confirm alone (users click through).

Chosen BP for agentnote v1

1. Click × → confirm dialog (“Move to Archived? You can restore for 30 days.”).
2. Confirm → soft-delete (deleted_at = now()), remove from main list, revoke publish if public.
3. Sidebar gains an Archived group (collapsed by default) with Restore + Delete forever (second confirm).
4. Nightly (or on-request) purge: hard-delete rows with deleted_at < now() - 30 days.
5. Ops: enable Railway volume daily backups on Postgres; document restore runbook. This is independent of product trash.

Current Behavior

• Sidebar × calls removeNote with no confirm → DELETE /api/notes/[id] → deleteNote() runs DELETE FROM notes WHERE id = $1 AND user_id = $2.
• No deleted_at / archive table / trash UI.
• note_aliases.note_id is REFERENCES notes(id) ON DELETE CASCADE — hard delete drops aliases immediately.
• Public notes: hard delete removes the row, so /p/{handle}/{id} 404s; there is no staged revoke-before-delete path today.
• Cross-tab sync broadcasts { type: "delete", id } and drops the note from other tabs’ lists.
• Railway: volume backups/schedules were empty at incident time (verified via GraphQL volumeInstanceBackupList / volumeInstanceBackupScheduleList).

Desired Behavior

Product

1. Confirm before moving a note out of the main list.
2. Archive (soft-delete): note disappears from main Notes list; appears under Archived with deleted time / “deletes forever in N days”.
3. Restore: clears deleted_at, returns to main list (same id, aliases intact), selected if it was active.
4. Delete forever (from Archived only): second confirm → hard DELETE (+ cascade aliases). Optional: skip confirm if retention already expired and purge job is running.
5. Publish interaction: archiving a public note must unpublish (same hard-revoke semantics as unpublishNote today) so public links die immediately even while the row sits in trash. Restoring does not auto-republish.
6. Deep links: /n/{id} for archived notes → 404 or a dedicated “Archived — restore?” empty state (prefer 404 for simplicity unless restore-from-URL is easy).
7. List APIs: default list excludes archived; archived list is explicit.

Ops

1. Railway Postgres volume: enable daily backup schedule (keep per Railway defaults).
2. Document in README: how to list/restore backups; warn that volume restore is disruptive (stages a new volume) — use for disasters, not single-note recovery.
3. Optional follow-up (non-blocking): scripted pg_dump to object storage. Not required if Railway daily backups are on.

Source Of Truth

Internal repo/source

• lib/notes.ts — listNotes, getNote, deleteNote, publishNote, unpublishNote
• lib/db.ts — ensureSchema() (notes + note_aliases + publish columns)
• app/api/notes/[id]/route.ts — DELETE handler
• app/api/notes/route.ts — list/create (needs archived filter / optional query)
• components/agentnote-app.tsx — removeNote, sidebar ×, BroadcastChannel sync
• lib/types.ts — Note type
• Public read path: getPublicNoteByToken / /p/[handle]/[id] must ignore archived rows (and/or require is_public which archive clears)

External docs/source

• Apple Notes — Delete a note — Recently Deleted, 30 days
• Notion — Delete & restore — Trash, 30 days + backup backstop
• Railway volume backups — daily/weekly/monthly schedules; restore stages a new volume
• Soft-delete lifecycle: deleted_at + retention purge (lifecycle writeup)

Proposed API / Schema

Schema migration (ensureSchema)

ALTER TABLE notes
  ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;

CREATE INDEX IF NOT EXISTS notes_user_live_updated_at_idx
  ON notes (user_id, updated_at DESC)
  WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS notes_user_deleted_at_idx
  ON notes (user_id, deleted_at DESC)
  WHERE deleted_at IS NOT NULL;

Do not move rows to a separate archived_notes table at this scale (single-digit / hundreds of notes per user). In-table tombstone is enough.

Live note queries

All current “active” reads become:

WHERE user_id = $1 AND deleted_at IS NULL

resolveCanonicalNoteId should optionally resolve archived rows for restore/delete-forever; default get for editor should treat archived as not found (or return with deleted_at for trash UI).

Note JSON (additive)

{
  "id": "dif-espx-vmb",
  "title": "0715.md",
  "body": "...",
  "created_at": "2026-07-15T00:00:00.000Z",
  "updated_at": "2026-07-15T12:00:00.000Z",
  "deleted_at": null,
  "is_public": false,
  "public_id": null,
  "published_at": null,
  "author_handle": null
}

Archived example: "deleted_at": "2026-07-16T12:00:00.000Z".

Endpoints

Archive (replaces today’s hard delete semantics for DELETE /api/notes/[id])

DELETE /api/notes/{id}

Response:

{ "ok": true, "note": { "id": "dif-espx-vmb", "deleted_at": "2026-07-16T12:00:00.000Z", "is_public": false } }

Behavior:

1. Auth + ownership checks (unchanged).
2. Set deleted_at = NOW().
3. Clear publish fields (is_public=false, public_id=null, published_at=null, author_handle=null) in the same transaction.
4. Keep note_aliases rows (no hard delete).

List archived

GET /api/notes?archived=1

or

GET /api/notes/archived

Prefer query flag on existing list route to avoid extra surface area.

Response: { "notes": [ /* deleted_at IS NOT NULL, newest deleted first */ ] }

Restore

POST /api/notes/{id}/restore

Response: { "note": { ..., "deleted_at": null } }

Hard delete (permanent)

DELETE /api/notes/{id}?permanent=1

or

DELETE /api/notes/{id}/permanent

Only allowed when deleted_at IS NOT NULL (or allow permanent from live only after confirm — prefer Archived-only permanent delete to reduce footguns).

Purge job

• Vercel Cron (vercel.json) hitting POST /api/cron/purge-archived with CRON_SECRET, or
• Simple on-read opportunistic purge when listing archived (acceptable at tiny scale), or
• Documented manual SQL for v1.

Prefer Vercel Cron if already easy; otherwise opportunistic purge in listArchivedNotes / ensureSchema wake path is fine for current size. Needs verification which is cleaner in this repo (no vercel.json today).

Validation Rules

• Default GET /api/notes never returns archived rows.
• DELETE without permanent flag is always soft-delete (idempotent if already archived).
• Permanent delete requires archived state (recommended) + auth.
• Public getters must not return archived notes even if publish flags were somehow left set (defense in depth: AND deleted_at IS NULL).
• Backward compatibility: existing clients calling DELETE still “remove from UI”; response may grow a note object — keep { ok: true } and add fields additively.

Implementation Notes

Likely files to modify

• lib/db.ts — add deleted_at + indexes in ensureSchema
• lib/types.ts — deleted_at: string | null on Note
• lib/notes.ts
  • listNotes → filter live
  • listArchivedNotes
  • archiveNote (rename/replace deleteNote soft path)
  • restoreNote
  • purgeNote / purgeExpiredArchivedNotes
  • getPublicNoteByToken → require deleted_at IS NULL
  • resolveCanonicalNoteId → support includeArchived option
• app/api/notes/route.ts — ?archived=1
• app/api/notes/[id]/route.ts — soft DELETE; permanent flag
• app/api/notes/[id]/restore/route.ts — new
• app/api/cron/purge-archived/route.ts — optional
• components/agentnote-app.tsx
  • confirm dialog before archive
  • Archived section UI (restore / delete forever)
  • BroadcastChannel: prefer { type: "archive", note } / { type: "restore", note } (or keep delete meaning “leave main list” and refetch)
• app/globals.css — minimal Archived styles (no card chrome; match Zed panel)
• README.md — trash retention + Railway backup runbook

New files

• app/api/notes/[id]/restore/route.ts
• Optional: app/api/cron/purge-archived/route.ts, vercel.json
• Optional: lib/notes-archive.test.ts if repo has a test runner pattern; otherwise API smoke in QA plan

Flow

1. User clicks × → confirm (“Move {title} to Archived?” / Cancel default focus).
2. On confirm → DELETE /api/notes/{id} soft-archives + unpublishes.
3. Client removes from main list, syncs other tabs, optionally shows Archived section open briefly.
4. User opens Archived → Restore (POST .../restore) or Delete forever (confirm → permanent DELETE).
5. Purge job hard-deletes deleted_at < now() - interval '30 days'.
6. Ops: Railway dashboard/API → enable daily volume backup on Postgres volume instance.

UI copy (keep minimal)

• Confirm archive: Move to Archived? / secondary: You can restore for 30 days.
• Confirm permanent: Delete forever? This cannot be undone.
• Archived empty: hide the section entirely when count is 0.

Tests

• Archive hides from listNotes, appears in archived list.
• Restore returns to live list; same id.
• Archive clears publish fields; public URL 404s.
• Permanent delete removes row + aliases.
• Purge only removes rows older than 30 days.
• Auth: cannot archive/restore another user’s note.
• Confirm: unit/UI not required if no component test harness; manual QA checklist below.

Edge Cases And Risks

• Mis-click storm: confirm alone is insufficient — archive is the real safety net.
• Public links: must die on archive; do not leave is_public=true on tombstones.
• Alias FK: soft-delete must not cascade-delete aliases; only permanent delete should.
• Meet-style id reuse: never reuse archived ids while tombstone exists; after hard purge, id space can recycle (Meet ids are random — collision risk negligible).
• Multi-tab sync: other tabs must drop/restore consistently; stale editor buffer if one tab archives the active note.
• Railway restore: restoring a volume backup rewinds entire DB — dangerous if used for one note; document “export single row from restored clone” if ever needed.
• GDPR / account delete (future): account erasure should hard-delete, bypassing retention. Out of scope unless already have account deletion.
• Migration safety: additive nullable deleted_at; no downtime expected.

Non-Goals

• Full version history / CRDT / per-edit snapshots (Notion version history).
• Separate archive DB, S3 note dumps, or admin support console.
• Typed “DELETE” confirmation for archive (reserve typed confirm for account-level destruction only).
• Replacing confirm with undo-toast-only UX (can add toast later; do not drop confirm in v1).
• Changing publish product model beyond “archive ⇒ unpublish”.
• Automatic Time Machine / client-local drafts (optional later).

Acceptance Criteria

• Sidebar × shows a confirm step; Cancel leaves the note untouched.
• Confirm moves note to Archived (deleted_at set); main list excludes it.
• Archived notes can be restored to the main list with the same id and body.
• Archiving a published note unpublishes it; /p/... 404s while archived and after.
• Delete forever from Archived hard-deletes after a second confirm.
• Notes with deleted_at older than 30 days are purged (cron or documented equivalent).
• Default APIs/UI never show archived notes in the main list.
• README documents 30-day retention + Railway daily backup setup.
• Railway Postgres volume has a daily backup schedule enabled (ops checklist item; can be done outside the PR but must be completed before closing the issue).

QA Plan

1. Local: create note → archive via UI → confirm row has deleted_at and is absent from GET /api/notes.
2. GET /api/notes?archived=1 returns it; restore; main list shows it again.
3. Publish a note → archive → hit /p/{handle}/{id} → 404; restore → still unpublished until user publishes again.
4. Archive → delete forever → GET 404 and no row in SQL.
5. Two browser tabs: archive in A, confirm B drops it from list via sync.
6. Ops: Railway → Postgres → Backups → Daily enabled; create one manual backup smoke.

Suggested PR Scope

M — one PR preferred (schema + API + sidebar Archived + confirm + README).

Split only if needed:

1. PR1: schema + soft-delete/restore/permanent API (no UI polish)
2. PR2: confirm + Archived UI + sync
3. Ops checklist (Railway schedule) can land with PR1 docs

Next agent: $worktree-task or $generate-pr against chasehuh/agentnote.

Ops checklist (do even if code ships later)

• Railway project chasehuh / env production / volume instance for Postgres: enable Daily backup schedule
• Create one manual backup after enabling
• Store note of volume instance id / restore docs link in README