SOP: Note Deletion (Backup-First)

sop-note-deletion Sop

sop active

Project

Purpose

Notes are the institutional memory of the platform. Deleting notes is irreversible at the API level. This SOP ensures that every deletion — whether single or bulk — has a recovery path.

When This Applies

Single note deletion — archiving a stale note, removing a duplicate
Bulk deletion — removing a category of notes (e.g., migrated issue notes)
Any call to delete_note() — this SOP applies universally

Before Deleting: Backup

For single notes

Read the note — get_note(slug=...) and confirm it's the right note
Verify no children — list_notes(parent_slug=...) — deletion will fail if children exist (ON DELETE RESTRICT)
CNPG WAL is sufficient — continuous backup to MinIO means PITR recovery is always available. Note the current timestamp before deleting.

For bulk deletions (5+ notes)

Force WAL switch — ensures latest state is in MinIO:

kubectl exec -n postgres pal-e-postgres-1 -c postgres -- psql -U postgres -c "SELECT pg_switch_wal();"

Export the target notes as JSON — human-readable backup:

kubectl exec -n postgres pal-e-postgres-1 -c postgres -- psql -U postgres -d paledocs -t -A -c "
SELECT json_agg(row_to_json(t))
FROM (
  SELECT id, slug, title, html_content, note_type, status, parent_note_id, is_public, project_id, created_at, updated_at
  FROM notes WHERE <your_filter> ORDER BY slug
) t;" > ~/backups/<descriptive_name>_$(date +%Y%m%d).json

Full pg_dump (belt and suspenders):

kubectl exec -n postgres pal-e-postgres-1 -c postgres -- pg_dump -U postgres paledocs | gzip > ~/backups/paledocs_pre_<operation>_$(date +%Y%m%d_%H%M%S).sql.gz

Verify backup files — check file sizes, parse JSON, confirm count matches expectation

Push to MinIO — backups on local NVMe are not durable. Push to the backups bucket:

source ~/secrets/minio/credentials.env
AWS_ACCESS_KEY_ID="$MINIO_ROOT_USER" AWS_SECRET_ACCESS_KEY="$MINIO_ROOT_PASSWORD" \
  aws s3 cp ~/backups/ s3://backups/pal-e-docs/<operation>/ \
  --endpoint-url https://minio-api.tail5b443a.ts.net --recursive

Bucket structure: s3://backups/{service}/{operation}/. Verify upload with aws s3 ls.

Performing the Deletion

Via MCP API (preferred for small batches)

Via SQL (for bulk operations)

Note: SQL DELETE cascades to blocks, compiled_pages, note_links, note_revisions, and note_tags. Only parent_note_id has ON DELETE RESTRICT — verify no children exist first.

After Deleting: Verify

Confirm count — verify the expected number of notes were removed
Spot-check — try get_note(slug=...) on a deleted slug to confirm 404
Check for orphaned data — unlikely with CASCADE, but verify if doing manual SQL

Recovery

If a deletion was a mistake:

From JSON export: Re-create notes via create_note() using the exported data. Tags and blocks will need to be re-created separately.
From pg_dump: Restore to a separate database, extract the needed rows, insert into production.
From CNPG PITR: Full cluster restore to a point before the deletion. See sop-postgres-restore. This is the nuclear option — restores everything, not just the deleted notes.
From MinIO: Download backups with aws s3 cp s3://backups/pal-e-docs/<operation>/ ~/restore/ --endpoint-url https://minio-api.tail5b443a.ts.net --recursive

Backup Destinations

Location	Purpose	Durability
<code>~/backups/</code>	Working copy during operation	Low — local NVMe only
<code>s3://backups/pal-e-docs/</code>	Persistent off-cluster backup	High — MinIO with disk persistence
CNPG WAL archive (<code>s3://postgres-wal/</code>)	Point-in-time recovery	High — continuous, automatic

Hook Enforcement (TODO)

A PreToolUse hook on mcp__pal-e-docs__delete_note should display a warning reminding the operator to verify backup before confirming. See todo-delete-note-warning-hook.

sop-postgres-restore — full CNPG restore procedure
sop-secrets-management — where MinIO credentials live
todo-delete-note-warning-hook — planned PreToolUse hook enforcement