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

I've spent the last several months building Nookly, a local-first notes app (pages, block editor, tables/kanban, full-text search) in Flutter, backed by Drift/SQLite. No account, no cloud — everything lives on the user's device.

This post isn't really about the app itself. It's about two engineering decisions that might be useful outside this specific project: how to design a schema for sync you haven't built yet, and how to ship a landing page with literally zero build tooling.

TL;DR

• Every table gets id (uuid), updatedAt, isDeleted, version from day one — sync becomes a data-layer swap later, not a rewrite.
• Reordering (drag-and-drop) uses fractional indices instead of reindexing siblings on every move.
• Search is SQLite FTS5 kept in sync via SQL triggers, not application code.
• The landing page is a single index.html — React + Tailwind loaded straight from CDNs, JSX compiled in-browser. No npm, no bundler.

Schema designed for sync that isn't built yet

There's no multi-device sync in Nookly right now — it's on the roadmap. But retrofitting it later usually means touching every table and half the data layer. So from the start, every table carries these through a shared mixin:

mixin SyncColumns on Table {
  TextColumn get id => text().clientDefault(() => uuid.v4())();
  DateTimeColumn get createdAt => dateTime().clientDefault(() => DateTime.now())();
  DateTimeColumn get updatedAt => dateTime().clientDefault(() => DateTime.now())();
  BoolColumn get isDeleted => boolean().withDefault(const Constant(false))();
  IntColumn get version => integer().withDefault(const Constant(1))();
}

• UUID instead of autoincrement — no collisions once you have multiple devices generating records independently.
• isDeleted instead of a real DELETE — you need the fact of a deletion to propagate to other devices; a hard delete just erases that information.
• version, bumped on every write — not used for anything yet, but it's the natural hook for conflict resolution later.

Repository interfaces live in the domain layer and know nothing about Drift specifically, so in theory the data layer is swappable for a syncing backend without touching UI or business logic.

Reordering without touching every sibling row

Drag-and-drop reordering for pages and blocks is a frequent operation. The naive approach — integer order column, incrementing/decrementing every sibling on each move — turns every drag into a burst of UPDATE statements across half the table.

Fractional indices avoid that. Inserting between two neighbors is just their average:

double between(double before, double after) => (before + after) / 2;

Drop something between positions 1.0 and 2.0 → it gets 1.5. Drop again between 1.0 and 1.5 → 1.25. Neighboring rows are never touched. The one caveat: floating-point numbers aren't infinitely divisible, so heavy repeated reordering in the same spot can eventually erode precision — a production system doing this at scale should plan for periodic rebalancing of the whole collection's indices.

Search that maintains itself

Full-text search runs on SQLite's FTS5 virtual table. The usual pain with FTS indices is drift from the source data — you update a row, forget to update the index, and search quietly goes stale. The fix is to not rely on application code remembering to do it at all — SQL triggers handle it at the database level:

CREATE TRIGGER pages_ai AFTER INSERT ON pages BEGIN
  INSERT INTO search_index(rowid, title) VALUES (new.rowid, new.title);
END;

CREATE TRIGGER pages_au AFTER UPDATE ON pages BEGIN
  UPDATE search_index SET title = new.title WHERE rowid = new.rowid;
END;

The index updates atomically with the data change, at the database layer — no Dart code that could forget to do it.

The landing page: one HTML file, no build step

Separate little side-quest: the app needed a landing page. Unlike the app itself, this really didn't need a build pipeline — just something that opens in a browser and deploys anywhere by copying one file.

React, ReactDOM and Babel Standalone loaded directly from CDN:

<script src="https://cdnjs.cloudflare.com/ajax/libs/react/18.2.0/umd/react.production.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/react-dom/18.2.0/umd/react-dom.production.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/babel-standalone/7.23.5/babel.min.js"></script>
<script src="https://cdn.tailwindcss.com"></script>

<script type="text/babel">
  function App(){ return <div className="text-3xl font-bold">Hello</div>; }
  ReactDOM.createRoot(document.getElementById('root')).render(<App />);
</script>

JSX gets transpiled in-browser via Babel Standalone. Tailwind's Play CDN script watches the DOM through a MutationObserver and generates classes on the fly, so it works fine even with React re-rendering the tree dynamically.

The obvious downside: runtime Babel transpilation isn't free, and this approach would be a bad idea for anything bigger than a single page — it'll show on low-end devices. But for a one-pager it's a reasonable trade: zero config, and deployment is literally copying one file.

The interactive bits on the page aren't decorative — they're tied to real features. The theme toggle swaps between two actual screenshots of the same page in light/dark mode (matching the app's real switch behavior), and the search widget is a working filter over a small dataset with match highlighting, mirroring the app's actual Ctrl+K search.

Wrapping up

None of this is individually novel — UUID + soft-delete + version columns for future sync, fractional indices for drag-and-drop, and trigger-maintained FTS indices all show up repeatedly in local-first app writeups. But since they keep coming up, it seemed worth writing down in one place.

The app itself: Nookly. Source repo (showcase, releases): GitHub.