Builing an offline-first app with build-from-scratch Sync Engine

It's 3 AM, and I'm staring at my laptop screen with the kind of glazed expression that only comes from too much coffee and too little sleep. My bed is littered with empty snack wrappers and energy drink cans, my stress-coping mechanisms laid bare. I'm questioning every life choice that led me to this moment when suddenly it hits me: "What if we're thinking about this all wrong?"

But let me back up and tell you how I got here.

The Problem That Wouldn't Go Away

Several months ago, I found myself in the comfortable chaos of a state-owned company in Indonesia, working as a freelance software consultant. The existing system was exactly what you'd expect from a large organization that needed quick development: a monolithic Laravel application, battle-tested and reliable, sitting comfortably on top of a MySQL database.

Everything was working fine, until it wasn't.

The request came during what I thought would be a routine planning meeting: "We need a desktop application that works offline on both Windows and Mac. Our field workers spend weeks in remote areas where internet is... let's call it 'unreliable.' They need to collect critical data that can't wait for a connection."

I nodded confidently, mentally cataloging all the offline-first solutions I'd bookmarked over the years. How hard could it be, right? Note: It was about to get very hard.

The field workers weren't just dealing with slow internet, they were operating in areas where connectivity was measured in minutes per day, not hours. Picture trying to upload weeks of critical data through a connection that disappears the moment a cloud passes overhead. When they finally got a signal, they needed to sync everything reliably and efficiently. This wasn't a nice-to-have feature; it was mission-critical infrastructure for people doing important work in genuinely challenging conditions.

Down the Rabbit Hole of "Obvious" Solutions

Like any other good developer, I started with research. The internet was full of promising solutions: Electric SQL looked incredibly polished with its real-time sync magic, PouchDB had years of battle-testing under its belt, and there were dozens of other frameworks promising to solve exactly this problem with elegant, well-documented APIs.

I spent days diving deep into documentation, spinning up proof-of-concepts multiple times, and getting increasingly excited about the sophisticated solutions these tools offered. Electric SQL's conflict resolution looked like black magic. PouchDB's offline-first philosophy seemed tailor-made for this use case. Everything appeared perfect on paper.

Then I hit the wall, very very hard.

"PostgreSQL only." "Requires CouchDB." "Not compatible with MySQL." "Complete database migration required."

One by one, every mainstream offline-first solution fell away like dominoes. The existing Laravel monolith wasn't going anywhere, it was already deeply integrated into the company's operations and represented months of accumulated business logic and bug fixes. Migrating the entire system to PostgreSQL wasn't just impractical; it was impossible within any reasonable timeline, budget, or risk tolerance.

I’ve seen this movie before, I found myself in that familiar feeling that feels like developer purgatory: knowing exactly what needed to be built, but discovering that none of the "right" tools actually fit the messy constraints of the real world.

The TanStack Query Experiment

After a week of dead ends and growing panic, I had what felt like a genuine breakthrough. TanStack Query offered something different: instead of trying to sync entire databases, it focused on managing server state in client applications. It had offline support, intelligent caching, and background synchronization. Best of all, it didn't care what database was running on the server, it just worked with HTTP APIs.

I dove in with the kind of desperate enthusiasm that only comes from finding hope after days of frustration. The initial implementation was surprisingly smooth, TanStack Query made it trivial to cache API responses, handle loading states, and manage data fetching with its elegant hooks. I built a proof-of-concept that looked genuinely promising: CRUD operations worked seamlessly, offline caching functioned as advertised, and the user experience felt responsive and modern.

Then came the reality check.

I simulated going offline, made a new data and then tried to edit that new data before going back online. That's when I discovered TanStack Query's Achilles heel: you can't cancel mutations in offline mode. Once you're offline and trigger a mutation, you're committed to that operation—even if you immediately realize it was a mistake, contained incorrect data, or was triggered accidentally.

For most applications, this might be a minor inconvenience. But for field workers who might be offline for days at a time, making dozens of data entries in challenging conditions, this was a complete deal-breaker. The ability to review, modify, or cancel operations before syncing wasn't just nice to have, it was essential for data integrity.

I stared at my proof-of-concept, so tantalizingly close to working but fundamentally flawed for our specific use case. The frustration was crushing. I was screaming internally while maintaining my professional composure in Discord updates.

The 3 AM Paradigm Shift

That's how I ended up on my bed at 3 AM, surrounded by empty snack wrappers and the digital graveyard of failed approaches. I'd been thinking about the problem for hours, running through the same constraints over and over again like a broken record.

Then it hit me with startling clarity: I'd been thinking about this entirely wrong.

Every solution I'd tried was focused on database synchronization, keeping a local database in perfect sync with a remote one. But why did it have to be database-to-database sync? What if I could get the flexibility of TanStack Query's state management but with complete control over when and how mutations were executed?

The insight was elegantly simple: treat the MySQL database as what it already was, an API endpoint, not a sync target. The desktop application would maintain its own local state using IndexedDB, and synchronization would happen at the application layer, not the database layer. I could build a queue system for mutations, allowing users to review, modify, or cancel operations before they ever reached the server.

So, the advantages would be:

• No need to modify the existing Laravel/MySQL infrastructure
• Complete control over sync logic and conflict resolution
• Ability to implement sophisticated offline behavior tailored to our needs
• Freedom to cancel, modify, or queue operations as needed
• Users could work completely offline and review everything before syncing

The more I thought about it, the more sense it made. IndexedDB would provide reliable local storage, Dexie.js would make it pleasant to work with, and I could build exactly the sync behavior our use case required without fighting against framework limitations.

Building the Solution

With the paradigm shift came surprising clarity about the architecture. Instead of fighting against our constraints, I'd embrace and work with them.

The core components were straightforward:

Local-First Data Storage: IndexedDB would be the single source of truth for the desktop application. Every operation, create, read, update, delete, would happen locally first, ensuring the app remained responsive regardless of network conditions or server availability.

Sync Queue: Instead of immediate database synchronization, operations would be queued with rich metadata about their type, payload, timestamp, and status. This queue could be inspected, modified, reordered, or even cancelled before synchronization, exactly what TanStack Query couldn't provide.

Optimistic Updates: The UI would update immediately when users made changes, providing instant feedback even when offline. If sync operations failed later, the app could handle reconciliation gracefully without leaving users in limbo.

API-Layer Synchronization: When connectivity returned, the sync engine would intelligently process the queue, making standard HTTP requests to the existing Laravel API endpoints. The server didn't need to know or care about the offline behavior—it just received normal API calls.

I wasn't trying to solve the general problem of database synchronization; I was solving the specific problem of keeping a desktop application in sync with a web API while giving users complete control over the process.

Here's how the data flow worked:

1. User performs action (create, edit, delete)
2. Save operation locally to IndexedDB immediately
3. Update UI optimistically for instant feedback
4. Queue sync operation with appropriate metadata
5. When online, process sync queue by making API calls
6. Handle success/failure responses appropriately

The flow would be like below:

How It Works Under The Hood

Okay, talk is cheap. Let me show you how this whole thing actually works under the hood. At this point, I'd like to walk you through the POC I built, a React todo list application that demonstrates the core concepts. You can find the repository at GitHub

The Database Layer

To start, we only need two tables in our IndexedDB database:

The Todos Table: This is your standard todo structure, but with a twist. Beyond the usual suspects (id, text, completed, createdAt, updatedAt), we add crucial metadata that acts as stamps for our sync engine:

syncStatus: 'pending' | 'synced' | 'failed';
isModified: boolean;
isNew: boolean;
isDeleted: boolean;

The syncStatus is self-explanatory, it categorizes the relationship between local data and the server. But the real magic happens with the other three fields. Think of them as breadcrumbs that tell our sync engine exactly what happened to each record:

• isNew: "Hey, this record was created offline and doesn't exist on the server yet"
• isModified: "This record was changed locally and needs to be updated on the server"
• isDeleted: "This record was deleted locally but still exists on the server"

These boolean flags eliminate the guesswork. No complex diffing algorithms, no timestamp comparisons that break when clocks are out of sync, just simple, reliable state tracking.

The Sync Queue Table: This table would holds every user’s action as a queue item:

export interface SyncQueueItem {
    id?: number;
    operation: 'CREATE' | 'UPDATE' | 'DELETE';
    endpoint: string;
    payload?: any;
    timestamp: number;
    retryCount: number;
    status: 'pending' | 'processing' | 'failed';
    error?: string;
    recordId?: string | number;
}

The operation field tells us exactly what HTTP method to use when we sync. The endpoint specifies where to send the request. The payload contains the actual data to be synced. The timestamp helps with ordering operations correctly and serves as our conflict resolution mechanism. The retryCount and status fields enable error handling and retry logic. And recordId links the sync operation back to the local record, creating a clear relationship between queued actions and actual data.

This queue is the heart of our cancellable mutation system, something TanStack Query couldn't give us.

The Queue Manager

Every time a user performs an action, we add it to our sync queue through this function:

export const addToSyncQueue = async (
    operation: 'CREATE' | 'UPDATE' | 'DELETE',
    endpoint: string,
    payload?: any,
    recordId?: string | number
): Promise<void> => {
    const syncItem: SyncQueueItem = {
        operation,
        endpoint,
        payload,
        timestamp: Date.now(),
        retryCount: 0,
        status: 'pending',
        recordId
    };

    await db.syncQueue.add(syncItem);
    console.log(`Added to sync queue: ${operation} ${endpoint}`, syncItem);

    // Try to sync immediately if online
    if (currentOnlineStatus) {
        processAllPendingSyncItems();
    }
};

This function is the bridge between user actions and eventual server synchronization. It captures the user's intent in a structured way, stores it locally, and then tries to sync immediately if we're online. If we're offline, the operation just sits in the queue, waiting patiently for connectivity to return.

Traffic Control

One tricky aspect of building a reliable sync engine is avoiding duplicate requests. When you're dealing with potentially flaky network connections, it's easy to accidentally send the same operation multiple times if the user clicks "retry" while a request is still processing.

That's where inFlightRequests comes in, it's just a simple Set that tracks which operations are currently being processed:

const inFlightRequests = new Set<string>();

Before starting any sync operation, we generate a unique key (${item.operation}_${item.endpoint}_${item.recordId}) and check if it's already in the set. If it is, we skip it. If not, we add it to the set, process the operation, and then clean up. It's a simple but effective way to ensure exactly-once delivery semantics, even in the face of network hiccups and impatient users.

Success Handling

When a sync operation succeeds, we need to update our local records to reflect the new state. This function handles the bookkeeping:

const updateLocalRecordAfterSync = async (item: SyncQueueItem, serverData: any): Promise<void> => {
    try {
        if (item.operation === 'CREATE') {
            // Update local record with server ID and mark as synced
            await db.todos.where('id').equals(item.recordId as number).modify({
                id: serverData.id,
                syncStatus: 'synced',
                isModified: false,
                isNew: false
            });
        } else if (item.operation === 'UPDATE') {
            // Mark as synced
            await db.todos.where('id').equals(item.recordId as number).modify({
                syncStatus: 'synced',
                isModified: false,
                updatedAt: serverData.updatedAt || new Date().toISOString()
            });
        }
    } catch (error) {
        console.error('Error updating local record after sync:', error);
    }
};

This is where we clear those sync metadata flags we talked about earlier. For CREATE operations, we get the server-generated ID and update our local record. For UPDATE operations, we clear the modification flags and sync any server-side timestamps. Once an operation succeeds, the local record is no longer "new" or "modified", it's perfectly in sync with the server.

The Individual Item Processor

This function handles the actual HTTP request for each individual sync operation:

const processSyncQueueItem = async (item: SyncQueueItem): Promise<void> => {
    try {
        await db.syncQueue.update(item.id!, { status: 'processing' });
        notifyProgress(item.id!, 0);

        const requestKey = `${item.operation}_${item.endpoint}_${item.recordId}`;

        if (inFlightRequests.has(requestKey)) {
            console.log(`Request already in flight: ${requestKey}`);
            return;
        }

        inFlightRequests.add(requestKey);
        // ... HTTP request logic ...
        // ... progress notifications at 30%, 70%, 100% ...
    } catch (error) {
        // ... retry logic with exponential backoff ...
    }
};

The function includes progress tracking (notifying listeners at 0%, 30%, 70%, and 100% completion), proper error handling with retry logic (up to 3 attempts), and cleanup of the in-flight requests set.

The Orchestrator

This is the heart of our sync engine, the function that actually processes the queue and makes things happen:

export const processAllPendingSyncItems = async (): Promise<void> => {
    if (isSyncInProgress || !currentOnlineStatus) {
        console.log("Sync already in progress or offline, skipping");
        return;
    }

    // ... debouncing with setTimeout ...

    const pendingItems = await db.syncQueue
        .where('status')
        .equals('pending')
        .toArray();

    // Group by endpoint to prevent duplicates
    const endpointGroups = new Map<string, SyncQueueItem[]>();

    for (const item of pendingItems) {
        const key = `${item.operation}_${item.endpoint}_${item.recordId}`;
        if (!endpointGroups.has(key)) {
            endpointGroups.set(key, []);
        }
        endpointGroups.get(key)!.push(item);
    }

    // Process one item per unique key, starting with the most recent
    for (const [_, items] of endpointGroups) {
        const sortedItems = items.sort((a, b) => b.timestamp - a.timestamp);
        const item = sortedItems[0];

        await processSyncQueueItem(item);
        await new Promise(resolve => setTimeout(resolve, 500));
    }
};

The function includes several optimizations: it groups operations by their unique key to prevent duplicate processing, it processes only the most recent operation for each unique record (if a user creates then immediately updates a todo, we only need to send the final state), and it includes a 500ms delay between operations to avoid overwhelming the server.

The function is also idempotent and includes proper guards against concurrent execution, you can call it multiple times safely, and it will only process items that actually need processing.

The API Layer

Finally, we have our offlineApi.ts the clean interface that the React components actually interact with

async addTodo(text: string): Promise<Todo> {
    const now = new Date().toISOString();

    const newTodo: Todo = {
        text: text.trim(),
        completed: false,
        createdAt: now,
        updatedAt: now,
        syncStatus: 'pending',
        isModified: false,
        isNew: true,
        isDeleted: false
    };

    // Save to local database first
    const localId = await db.todos.add(newTodo);
    const todoWithId = { ...newTodo, id: localId };

    // Add to sync queue
    await addToSyncQueue('CREATE', '/todos', {
        text: text.trim(),
        completed: false,
        createdAt: now,
        updatedAt: now
    }, localId);

    return todoWithId;
}

Notice the order of operations: save locally first, then queue for sync. This ensures that the UI can update immediately with the new data, regardless of network status. The user sees their todo appear instantly, and the sync happens in the background (or gets queued for later if offline).

Every operation follows this pattern:

1. Save to local database immediately
2. Update UI optimistically (via Dexie's live queries)
3. Queue the operation for sync with proper metadata
4. Let the sync engine handle the server communication asynchronously

The technical flow creates a seamless user experience where the app feels snappier. The complexity is hidden behind clean and structuredAPIs, whereas the sync engine handles all the messy details of network failures, retries, progress tracking, and state reconciliation.

This architecture gives us complete control over when operations execute, the ability to cancel or modify queued operations, transparent visibility into what's happening behind the scenes, and sophisticated retry logic with progress feedback. It's not as generically elegant as a framework solution tho, but it's perfectly tailored to our specific needs, and yes, sometimes, that's exactly what you need.

The Devil in the Details

Of course, the devil was in the implementation details. Building a reliable sync engine turned out to be more nuanced than my 3 AM epiphany had suggested.

Network State Detection: Determining when the app was truly "online" proved trickier than expected. The browser's navigator.onLine was laughably unreliable, often showing false positives when the device had a connection but no actual internet access. I ended up implementing a Google ping strategy—periodically attempting to fetch a small resource from Google's servers to verify real connectivity.

Operation Ordering: Some operations depended on others completing first. Creating a todo item and then immediately editing it required careful queue management to ensure operations were processed in the correct order, avoiding race conditions and data inconsistencies.

Conflict Resolution: What happens when the same data is modified both locally and on the server while offline? I implemented a "last-write-wins" strategy with clear user notification for conflicts, but more sophisticated approaches like operational transforms were certainly possible for more complex scenarios.

Progress Feedback: Users needed to understand what was happening during sync operations, especially when processing large queues. I built a comprehensive notification system that showed real-time progress, including which operations were pending, in progress, completed, or failed.

Retry Logic: Network operations fail in creative and unexpected ways. The sync engine needed robust retry logic with exponential backoff, maximum retry limits, and clear user feedback about what went wrong and what options were available.

The most satisfying part was implementing the sync notification system. When users came back online after days of offline work, they'd see a beautiful, informative progress indicator showing exactly what was being synchronized. Operations were color-coded (pending, in progress, completed, failed), and users could even retry failed operations manually or inspect the details of what went wrong.

Lessons Learned

This project taught me several lessons that extend far beyond offline synchronization:

Custom solutions aren't always technical debt

There's a strong bias in our industry toward using existing, well-tested solutions. And rightly so, reinventing the wheel is usually a mistake that leads to bugs, security issues, and maintenance nightmares. But sometimes your wheel is a fundamentally different shape than everyone else's, and that's okay. The key is understanding your constraints clearly before choosing tools, not after you've already committed to them.

Constraints can be creative catalysts

The MySQL/Laravel constraint that initially seemed like a limitation actually led to a more elegant and maintainable solution. Working within constraints often produces better results than having unlimited options because it forces you to focus on what's truly essential.

The Bigger Picture

This approach, treating legacy databases as API endpoints rather than sync targets, has broader applications beyond my specific use case. Many organizations have similar constraints: critical legacy systems that can't be easily modified, combined with modern requirements for offline functionality and mobile-first experiences.

The pattern is generalizable to many scenarios:

1. Identify what actually needs to be synchronized (state, not databases)
2. Use local storage technologies appropriate for your platform and requirements
3. Build sync logic at the application layer where you have full control
4. Design for transparency and user control over the sync process

Conclusion

At the end of the day, the field workers have confidence in their data, the company has the offline capability they desperately needed, and the existing Laravel infrastructure continues serving its other users without any modifications or disruptions.

But the real victory wasn't technical, it was rather philosophical. This project reminded me why I fell in love with software development in the first place: those magical moments when you realize that an "impossible" problem just requires a completely different way of thinking about the solution space.

If you're facing a similar challenge, legacy systems, offline requirements, constraints that seem to rule out all the "right" solutions, I encourage you to step back and question your fundamental assumptions. Maybe you don't need to sync databases. Maybe you don't need to use the mainstream tool that everyone recommends. Maybe your "limitation" is actually pointing toward a better, more elegant solution that nobody else has thought of yet.

The consulting world has taught me that the most interesting problems are often the ones that can't be solved with off-the-shelf solutions. They require creativity, domain knowledge, and the willingness to build something custom when the situation demands it.

Anyway, for reminder, the code for this proof-of-concept is available on GitHub, built with React, IndexedDB, Dexie.js, and a healthy dose of 3 AM inspiration fueled by way too much caffeine. It's not perfect, and it's certainly not a general-purpose solution, but it works exactly as needed for this specific use case. And sometimes, that's exactly what the world needs, not another generic framework, but a solution that fits the problem.