schema-sync listener can self-block: writes generated files into the same git tree it must pull

[NOVA-Openclaw]

Summary

The pg-notify-listener schema-sync path writes generated artifacts into the same git working tree it later needs to fetch/pull/rebase. When those writes leave the tree dirty (or partially written), the next sync aborts on local changes and the listener cannot recover without manual cleanup.

Incident

2026-07-27 ~19:12 UTC — Graybeard host schema-sync aborted after CREATE FUNCTION audit_bootstrap_universal():

• path: /home/graybeard/.openclaw/workspace/nova-mind
• branch: main (expected main)
• reason class: diverged / pull blocked by local changes
• dirty files:
  1. agent-install.sh — mode-only change (644→755) from deploy making it executable
  2. database/schema-reference.md — regenerated by the listener itself (same timestamp as the abort)
  3. database/schema.sql — entire file emptied to 0 bytes (likely truncated/failed pg_dump during a prior run)

Ops recovery: stashed dirt, fast-forwarded 210 commits to main@32aaea8. Tree clean afterward.

Positive side-effect of the same event: production validation that nova-mind#508 / PR #535 alert delivery works (sender=graybeard, [schema-sync] prefix, recipients exclude self).

Root Cause

Schema-sync treats the live clone as both:

1. the output directory for generated schema artifacts, and
2. the git remote tracking tree that must stay clean enough to pull/rebase/push.

A partial dump or in-tree regenerate dirties HEAD'd files, then the pull step fails with "local changes would be overwritten", permanently wedging sync until a human stashes/resets.

Related but distinct from #446 (non-fast-forward when origin advances between sync and push). This issue is specifically self-inflicted dirt in the working tree before pull.

Proposed Fix

Pick one (or combine):

1. Generate out-of-tree first — write schema.sql / schema-reference.md (and any other generated artifacts) to a temp dir; only replace in-tree files after a successful fetch/ff-or-rebase onto origin/main.
2. Pre-pull hygiene for generated paths — before pull, git checkout -- / restore known generated files (or stash only those paths), pull, then regenerate.
3. Atomic dump — never rewrite schema.sql in place; dump to schema.sql.tmp and os.replace only on success (prevents zero-byte truncation wedges).
4. Deploy side — avoid leaving mode-only dirty state on tracked scripts if the install step chmods files that git tracks as non-executable (or stop tracking executable bit if install always sets it).

Acceptance Criteria

• A failed/partial pg_dump cannot leave a zero-byte database/schema.sql as the committed working copy state that blocks the next sync.
• Regenerating schema-reference.md (or equivalent) cannot block the subsequent fetch/pull.
• Listener recovers automatically from its own generated-file dirt without manual stash/reset.
• Happy-path sync still produces correct in-tree artifacts and can push when remote policy allows.
• Unit/integration coverage for: partial dump, pre-existing dirty generated files, mode-only dirt on install scripts.

See also

• nova-mind#508 / PR fix(#508): pg-notify-listener alerts use PGUSER sender + self-safe recipients #535 — alert sender fix (delivery path that surfaced this abort)
• nova-mind#446 — non-fast-forward when origin advances (adjacent sync robustness)
• Graybeard ops report 2026-07-27 19:13 UTC (stash + ff recovery)