LWW Metadata Sync: Timestamp Preservation and Cascade Semantics

LWW Metadata Sync: Timestamp Preservation and Cascade Semantics

Problem

Two distinct but related issues in page metadata synchronization:

1. LWW timestamp overwrite — When applying remote metadata updates locally, the code used strftime('now') (local clock) instead of preserving the remote changed_at timestamp. This breaks Last-Write-Wins ordering because subsequent pulls from other devices see the wrong “last update” time.

2. Missing cascade soft-delete — When a parent page deletion arrives from a remote device, only the parent was soft-deleted. Child pages became orphaned locally, could re-sync to cloud, and create inconsistencies across devices.

Symptoms:

• Page renames from Device A appear to “revert” on Device B after sync
• Metadata field values oscillate between two values across sync cycles
• Deleted parent’s children still visible on receiving device
• Orphaned pages appear in cloud sync tables from other devices

Root Cause

Timestamp Overwrite

The apply_metadata_update method was generating a new timestamp at apply time:

// WRONG: overwrites remote timestamp with local time
conn.execute(
    "INSERT OR REPLACE INTO page_metadata_sync
     (page_id, field, value, changed_at, device_id)
     VALUES (?1, ?2, ?3, strftime('%Y-%m-%dT%H:%M:%f', 'now'), 'sync')",
    params![page_id, field, value],
)

Why this breaks LWW: Consider three devices A, B, C. Device A renames a page at T=10:00. Device B renames it at T=10:05. The correct LWW winner is B (later timestamp). But if Device C applies A’s change at T=10:10 (its local clock), C now records changed_at = 10:10 — making A’s change appear to be the latest. When B’s change arrives, C’s LWW comparison sees 10:10 > 10:05 and incorrectly keeps A’s value.

Missing Cascade Delete

The deletion handler was a simple single-row soft-delete:

// WRONG: only deletes the target page
conn.execute(
    "UPDATE pages SET is_deleted = 1 WHERE slug = ?1",
    params![page_id],
)

This violates the domain rule that deleting a page must cascade to all descendants. When a sync deletion arrives, the receiving device must apply the same cascade semantics as a local delete.

Solution

1. Pass Remote Timestamps Through Unchanged

The trait signature was updated to accept changed_at and device_id as pass-through parameters:

// In MetadataStorageRepository trait (services.rs)
fn apply_metadata_update(
    &self,
    workspace_path: &Path,
    page_id: &str,
    field: &str,
    value: Option<&str>,
    changed_at: &str,   // Remote timestamp — pass through, never regenerate
    device_id: &str,     // Remote device — pass through for tie-breaking
) -> SyncResult<()>;

The SQLite implementation now preserves the original values:

// CORRECT: preserves remote changed_at and device_id
conn.execute(
    "INSERT OR REPLACE INTO page_metadata_sync
     (page_id, field, value, changed_at, device_id)
     VALUES (?1, ?2, ?3, ?4, ?5)",
    params![page_id, field, value, changed_at, device_id],
)

Additionally, the apply_to_pages_table helper applies the value to the actual pages table column using an allowlisted field-to-column mapping:

fn apply_to_pages_table(
    conn: &rusqlite::Connection,
    page_id: &str,
    field: &str,
    value: Option<&str>,
) -> SyncResult<()> {
    let column = match field {
        "title" => "title",
        "parent_slug" => "parent_slug",
        "page_type" => "page_type",
        "icon" => "icon",
        "icon_color" => "icon_color",
        "template" => "template",
        "sort_order" => "sort_order",
        _ => return Ok(()), // Unknown fields stored in tracking table only
    };
    // Dynamic SQL with allowlisted column (safe)
    let sql = format!("UPDATE pages SET {column} = ?1 WHERE slug = ?2");
    conn.execute(&sql, params![value, page_id])?;
    Ok(())
}

2. Recursive Cascade Soft-Delete

Delete propagation uses a recursive CTE to find and soft-delete all descendants:

WITH RECURSIVE descendants(slug) AS (
    SELECT slug FROM pages WHERE slug = ?1 AND is_deleted = 0
    UNION ALL
    SELECT p.slug FROM pages p
    INNER JOIN descendants d ON p.parent_slug = d.slug
    WHERE p.is_deleted = 0
)
UPDATE pages SET is_deleted = 1,
    deleted_at = strftime('%Y-%m-%dT%H:%M:%f', 'now')
WHERE slug IN (SELECT slug FROM descendants)

A second cleanup pass removes orphaned sync queue entries for the deleted pages:

WITH RECURSIVE descendants(slug) AS (
    SELECT slug FROM pages WHERE slug = ?1
    UNION ALL
    SELECT p.slug FROM pages p
    INNER JOIN descendants d ON p.parent_slug = d.slug
)
DELETE FROM sync_queue WHERE block_id IN (SELECT slug FROM descendants)

This prevents re-pushing updates for pages that no longer exist, which would waste sync cycles and potentially create ghost entries in the cloud.

Prevention

LWW Timestamp Rule

In any LWW system, source timestamps must flow through the entire pipeline unchanged. The timestamp represents “when the original edit happened”, not “when I learned about it”. Any layer that regenerates timestamps breaks the causal ordering that LWW depends on.

Design checklist for LWW fields:

• Trait/interface accepts changed_at and device_id as parameters
• Storage layer passes them through to the database unchanged
• No strftime('now') or DateTime::now() in the apply path
• Tie-breaking uses device_id (lexicographic) when timestamps are equal
• Unit tests verify round-trip timestamp preservation

Cascade Delete Rule

Sync operations cannot be independent of domain rules. Any deletion in the sync layer must apply the same cascade semantics as a local delete. This means:

• Delete handlers query for descendants (recursive CTE or iterative)
• All descendants are soft-deleted atomically
• Orphaned queue entries are cleaned up
• Test fixtures include multi-level parent-child hierarchies

Warning Signs

• strftime('now') or Utc::now() in any metadata apply/write path
• Single-row DELETE/UPDATE for operations that should cascade
• Sync queue entries for pages that don’t exist locally
• Metadata values oscillating between two values across devices

References

• Commit df82ad7 — schema and data integrity remediation (INK-72)
• crates/infrastructure/sqlite/src/sync/metadata_storage.rs — LWW storage
• crates/infrastructure/sqlite/src/sync/deletion_storage.rs — cascade delete
• crates/application/src/sync/pull_page_metadata.rs — pull + LWW merge logic
• crates/application/src/sync/pull_page_deletions.rs — deletion pull + tombstones