Designing an offline notes app for sync that doesn't exist yet (and a landing page with zero build tools)

Over the past few months, the author has been developing Nookly, a local-first notes app built with Flutter, featuring a block editor, tables/kanban, and full-text search. The app operates entirely on the user's device without the need for any account or cloud storage. While the focus of this article is not on the app itself, it does delve into two engineering decisions that could be applied to future projects.

These decisions center around designing a schema for sync, which has yet to be implemented in Nookly but is planned for the future. Additionally, the article discusses how to create a landing page using zero build tools.

In terms of schema design, each table in Nookly includes an ID (UUID), updatedAt timestamp, isDeleted flag, and a version number. UUIDs are used instead of auto-incrementing values to prevent collision across multiple devices. The isDeleted flag allows for soft deletion, enabling the deletion status to be propagated to other devices. The version number is incremented with every write and serves as a hook for conflict resolution in the future.

When it comes to reordering items (such as pages and blocks), the author avoids updating every sibling row with each move. Instead, they employ fractional indices, calculating the average position between two existing rows. This approach eliminates the need to reindex all surrounding rows, significantly reducing the number of UPDATE statements required during drag-and-drop operations.

However, this method has a limitation: as rows are reordered repeatedly, floating-point precision may degrade, requiring periodic rebalancing of the indices in production environments.

Full-text search is handled by SQLite's FTS5 virtual table, with triggers ensuring that the search index remains in sync with the data layer at the database level. No additional Dart code is needed to maintain the search index.

The landing page for Nookly is a single HTML file, with React and Tailwind CSS loaded directly from CDN sources using Babel Standalone. There is no build process involved, as the JSX gets transpiled in the browser. While runtime Babel transpilation adds a slight overhead, it is considered acceptable for a single-page landing site. The interactive elements, such as the theme toggle and search widget, are fully functional and mirror the behavior of the actual app.