[BUG] Desktop 2.7.1 Remote Sync fails with 500 on /sync/hosts once any host is nested as a sub-host — parentHostId (new numeric FK in 2.7.0) is synced verbatim, same class as #1070

Title

Desktop 2.7.1 Remote Sync fails with 500 on /sync/hosts once any host is nested as a sub-host — parentHostId (new numeric FK in 2.7.0) is synced verbatim, same class as #1070

Platform

Desktop App - Windows

Server Installation Method

Proxmox (Community Scripts)

Version

2.7.1

CLI Installation Method

None

CLI Version

No response

Troubleshooting

• I have examined logs and tried to find the issue
• I have reviewed opened and closed issues
• I have tried restarting the application
• I have checked open issues and ensured this is not a duplicate

The Problem

Remote Sync had been working (last good cycle 2026-09-08T23:53:43Z per remote-sync-config.json). Two minutes later I used the sidebar to turn a set of folder-grouped hosts into sub-hosts (the embedded backend log shows a run of SSH host updated [op:host_update_success, host:64 … host:86] from 23:55:45Z to 23:58:53Z). From that point every sync cycle fails with:

Sync error: Request failed (500): https://<server>/sync/hosts

remote-sync-config.json now holds lastSyncStatus: "error", lastSyncError: "Request failed (500): https://<server>/sync/hosts", and remote-sync-state.json never advances past the last good cycle. Because the engine aborts the whole cycle on the first failed entity (electron/remote-sync.cjs, the SYNCED_ENTITY_TYPES loop), snippets, dashboardServiceLinks, homepageItems and userPreferences — everything after hosts in the order — stop syncing too.

Root cause

This is #1070 again, for the column that 2.7.0 added.

• 2.7.0 introduced sub-host nesting as a numeric self-referential foreign key: parentHostId: integer("parent_host_id").references(() => hosts.id, { onDelete: "set null" }) — src/backend/database/db/schema.ts L147–150 at release-2.7.1-tag. SQLite runs with PRAGMA foreign_keys = ON (src/backend/database/db/index.ts L215).
• The fix for #1070 (2.6.1) added src/backend/database/routes/sync-references.ts, which translates numeric FK ids to sync ids on the wire. Its HOST_REFERENCES list covers exactly five fields — credentialId, rdpCredentialId, vncCredentialId, telnetCredentialId, vaultProfileId — and was not extended when parentHostId was added. (Still true on main as of b6cd2a6.)
• stripWritePayload() in src/backend/database/routes/sync.ts only drops id, userId, syncId and connectionOrigin (readOnlyFields for hosts, L64), so parentHostId reaches the insert/update as the sender's local autoincrement id.
• The sync upsert also bypasses validateParentHostId() (src/backend/database/routes/host-parent-validation.ts), which the normal /host and bulk PATCH routes use to reject a parent that doesn't exist / isn't owned by the same user / would form a cycle.

Outcome on the receiving side, per pushed sub-host:

1. The local parent id does not exist in the receiver's ssh_data → SqliteError: FOREIGN KEY constraint failed inside the upsert try → logged as Failed to upsert sync row for hosts [op:sync_upsert] → 500 {"error":"Failed to upsert row"} → the cycle aborts and repeats forever. This is what I'm hitting.
2. The local parent id happens to exist in the receiver's ssh_data → the FK passes and the host is silently nested under an unrelated host. The FK is not user-scoped, so that can even be another user's row. Rows pushed earlier in the same cycle have already committed before the first failure, so a mix of (1) and (2) is possible in one cycle.

The same happens in the other direction (server → embedded backend), so in 2.7.x sub-host nesting and Remote Sync cannot be used together at all.

How to Reproduce

1. Desktop 2.7.1 connected to a 2.7.1 server via Settings → Remote Sync, with at least a few hosts already syncing cleanly.
2. On the desktop, nest one host under another (sub-host) from the sidebar.
3. Wait for the next sync cycle or press Sync Now.
4. Sync fails with Request failed (500): https://<server>/sync/hosts and keeps failing every cycle; the server log shows Failed to upsert sync row for hosts with SQLITE_CONSTRAINT_FOREIGNKEY.
5. Un-nest the host (move it back into a folder) and the next cycle succeeds.

Additional Context

Suggested fix

• Add parentHostId ↔ parentHostSyncId to HOST_REFERENCES with entityType: "hosts" (needs a hosts branch in findReferenceSyncId / findReferenceId, which currently handle only sshCredentials and vaultProfiles).
• Because the reference is self-referential, the pusher must send parents before children within the hosts entity (topological order on parentHostId in syncEntity), or the receiver should tolerate a not-yet-present parent by writing null and letting the next cycle repair it, rather than throwing.
• Run validateParentHostId() on the sync upsert as well, so a bad reference is a 400 with a reason instead of a 500 or a silent mis-nest.

Workaround

Clear the sub-host nesting on the desktop (move those hosts back into folders); the next cycle succeeds. Check the server's sidebar afterwards for hosts that landed under the wrong parent via case (2).

Start with src/backend/database/routes/sync-references.ts and sync.ts, then inspect schema.ts and host-parent-validation.ts. Trace parentHostId through syncEntity and the /sync/hosts upsert in both directions, including ordering and validation behavior. Done means nested hosts sync without a 500, the cycle advances, and invalid or unresolved parents cannot silently create mis-nesting.