Excel Sync and Persistence Engine

Excel Sync & Persistence Engine

Storage Invariant: Zero server round-trips. ProjectPulse guarantees durable data survival across browser crashes, reboots, and multi-tab sessions using a dual-tier client-side storage architecture coupled with a bi-directional Excel reconciler.

---

💾 1. Global State Architecture (P)

The central state object window.P serves as the single source of truth for the entire application. All mutations flow through pure helper functions that update P and trigger the debounced persistence scheduler.

classDiagram
    class ProjectState {
        +String projectName
        +String version
        +Object settings
        +Array~Task~ tasks
        +Array~Release~ releases
        +Array~Defect~ defects
        +Array~RAIDItem~ raid
        +Array~Member~ members
        +Array~HistoryEntry~ auditLog
    }

    class Task {
        +String id
        +String name
        +String parentId
        +String status
        +Number progress
        +Date startDate
        +Date dueDate
        +String assignee
        +String module
        +String moduleType
        +Number estEffort
        +Number actEffort
    }

    class Release {
        +String id
        +String name
        +Date releaseDate
        +String status
        +Array~String~ taskIds
    }

    ProjectState "1" *-- "*" Task
    ProjectState "1" *-- "*" Release

Loading

Core Schema Fields

• P.settings: Holds fiscal start dates, default effort units (Hours, Days, Months), standard work week hours (e.g. 40), and currency symbols.
• P.tasks: Flat array of task objects representing the WBS tree via parentId references.
• P.releases: Delivery packages and Project Delivery Notes (PDN) metadata.
• P.raid: Risks, assumptions, issues, and dependencies.
• P.defects: Logged quality defects linked to tasks.
• P.members: Team roster, skills, and FTE capacities.

---

🛡️ 2. Dual-Tier Storage Architecture

ProjectPulse combines localStorage and IndexedDB to ensure fast synchronous boot times with scalable storage capacity:

flowchart TD
    Mutation["UI Edit / State Change"] --> Debounce["Debounce Timer\n(300ms Window)"]
    Debounce --> PrimaryStore["Tier 1: localStorage\n('pp-data' JSON String)"]
    
    PrimaryStore --> SizeCheck{"Payload > 4MB?"}
    SizeCheck -->|"No"| FlushDone["Persistence Complete"]
    SizeCheck -->|"Yes"| SecondaryStore["Tier 2: IndexedDB\n('ProjectPulseDB' ObjectStore)"]
    SecondaryStore --> FlushDone

Loading

Storage Characteristics

1. Tier 1 (localStorage pp-data):
   • High-speed synchronous reads during application bootstrap.
   • Saves serialized JSON payload of the entire P object.
   • Throttled with a 300ms trailing debounce timer to avoid blocking the main thread during rapid user input (e.g., typing task descriptions).
2. Tier 2 (IndexedDB ProjectPulseDB):
   • Asynchronous storage tier used when project size, attachments, or audit histories exceed browser localStorage limits (typically 5MB).
   • Maintains rolling snapshot backups for state recovery.

---

📈 3. Bi-directional SheetJS Sync Engine

ProjectPulse integrates an embedded SheetJS runtime for lossless, bi-directional Excel synchronization.

sequenceDiagram
    autonumber
    actor User as Project Manager
    participant App as ProjectPulse
    participant Parser as SheetJS Engine
    participant DB as LocalStorage / IndexedDB

    rect rgb(240, 248, 255)
    Note over User,DB: Import Flow (Excel to Browser)
    User->>App: Drag & drop Project_Plan.xlsx
    App->>Parser: Read ArrayBuffer via FileReader
    Parser->>App: Normalized JSON (Tasks, RAID, Defects)
    App->>App: Reconcile deltas & validate schemas
    App->>DB: Commit unified state
    App-->>User: Refresh timeline & dashboards
    end

    rect rgb(245, 255, 245)
    Note over User,DB: Export Flow (Browser to Excel)
    User->>App: Click "Export to Excel"
    App->>Parser: Build workbook from 'P' state
    Parser->>Parser: Apply header styles & column widths
    Parser-->>User: Trigger browser download (.xlsx)
    end

Loading

Import Capabilities

• Fuzzy Column Header Mapping: Automatically identifies column variations (e.g., Task Name, Title, Item, Task all resolve to name).
• Date Normalization: Parses both ISO string dates and raw Excel serial date numbers (e.g., 45231), correctly mapping them to local date boundaries.
• Delta Reconciliation: Uses unique task IDs (id) to match existing records, updating changed fields while preserving browser-only metadata (e.g., UI colors or custom notes).

Export Capabilities

• Multi-Tab Architecture: Generates distinct tabs: Overview, Tasks, Deliveries, RAID, and Team.
• Professional Typography & Fills: Injects branded table header fills, alternating zebra stripes, and formatted percentage/date cells.
• Formula Preservation: Computes summary formulas directly in Excel syntax (=SUM(...), =AVERAGE(...)) so exported workbooks remain functional in Microsoft Excel.