Forem: Sathish

React Native offline-first: SQLite write-ahead log

Sathish — Wed, 08 Apr 2026 15:00:41 +0000

Turn on SQLite WAL in Expo. It fixes “database is locked”.
Batch writes in one transaction. Fewer fsyncs. Faster UI.
Add a busy timeout + retry. Stops random write failures.
Measure it. I logged real timings per write batch.

Context

My fitness app needs 5-second set logging. No spinning loaders. No “try again” toast. And it has to work on airplane mode.

I use Expo + SQLite because I need real queries. Joins. Indexes. Counting volume per workout. AsyncStorage fell over fast.

Then I hit the classic wall: concurrent reads + writes. Search is running. The workout screen is inserting sets. And suddenly I get SQLITE_BUSY: database is locked.

I wasted 4 hours blaming React renders. Most of it was wrong. SQLite was doing exactly what I asked. I just hadn’t configured it for a mobile app that’s always doing something.

1) I stop fighting concurrency. I enable WAL.

SQLite defaults can be conservative. WAL (write-ahead logging) changes the locking model. Readers don’t block writers the same way.

On mobile, that’s the difference between “feels instant” and “random lock errors at the worst moment”.

I enable WAL once on startup. I also set a busy_timeout. Without it, a short lock turns into a hard failure.

// db.ts (Expo + expo-sqlite)
import * as SQLite from 'expo-sqlite';

export const db = SQLite.openDatabaseSync('gym.db');

export function initDb() {
  // WAL improves read/write concurrency.
  db.execSync('PRAGMA journal_mode = WAL;');

  // Wait up to 2 seconds when the DB is busy.
  db.execSync('PRAGMA busy_timeout = 2000;');

  // Durability vs speed. NORMAL is fine for app data.
  db.execSync('PRAGMA synchronous = NORMAL;');

  // Basic schema for set logging.
  db.execSync(`
    CREATE TABLE IF NOT EXISTS sets (
      id TEXT PRIMARY KEY NOT NULL,
      workout_id TEXT NOT NULL,
      exercise_id TEXT NOT NULL,
      reps INTEGER NOT NULL,
      weight_kg REAL NOT NULL,
      created_at_ms INTEGER NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_sets_workout ON sets(workout_id, created_at_ms);
  `);
}

I learned the hard way that WAL isn’t “optional tuning”. If you’re doing offline-first + live search, you’re doing concurrent DB work. WAL makes that survivable.

2) I batch writes. One transaction. No drama.

My original implementation inserted one set per tap. One INSERT per tap. It worked… until I added auto-save for notes, timer ticks, and background sync metadata.

Then the lock errors got spicy.

The fix was boring. Batch writes inside a transaction. It reduces lock churn and fsync overhead. And it’s easier to retry.

This is the exact helper I use.

// writeBatch.ts
import { db } from './db';

type SetRow = {
  id: string;
  workoutId: string;
  exerciseId: string;
  reps: number;
  weightKg: number;
  createdAtMs: number;
};

export function insertSetsBatch(rows: SetRow[]) {
  if (rows.length === 0) return;

  db.withTransactionSync(() => {
    const stmt = db.prepareSync(
      `INSERT INTO sets (id, workout_id, exercise_id, reps, weight_kg, created_at_ms)
       VALUES (?, ?, ?, ?, ?, ?)`
    );

    try {
      for (const r of rows) {
        stmt.executeSync([
          r.id,
          r.workoutId,
          r.exerciseId,
          r.reps,
          r.weightKg,
          r.createdAtMs,
        ]);
      }
    } finally {
      stmt.finalizeSync();
    }
  });
}

This changed the feel immediately.

Not because batching is magic. Because I stopped forcing SQLite to constantly acquire/release locks while the UI is also querying.

3) I treat SQLITE_BUSY like a normal thing. Retry it.

Even with WAL + batching, you can still hit SQLITE_BUSY.

Example. User is scrolling exercise history (big query). At the same moment, they log a set. The write wants a lock. The read is still running. Boom.

busy_timeout helps. But I still saw failures on older Android devices.

So I added a tiny retry wrapper. Only for SQLITE_BUSY. Not for everything.

// retrySqliteBusy.ts
const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));

export async function retrySqliteBusy(
  fn: () => T,
  opts: { retries?: number; baseDelayMs?: number } = {}
): Promise {
  const retries = opts.retries ?? 5;
  const baseDelayMs = opts.baseDelayMs ?? 20;

  let lastErr: unknown;

  for (let i = 0; i <= retries; i++) {
    try {
      return fn();
    } catch (e: any) {
      lastErr = e;
      const msg = String(e?.message ?? e);

      // expo-sqlite throws different shapes depending on platform.
      const isBusy = msg.includes('SQLITE_BUSY') || msg.includes('database is locked');
      if (!isBusy || i === retries) throw e;

      // Small exponential backoff.
      await sleep(baseDelayMs * Math.pow(2, i));
    }
  }

  throw lastErr;
}

I call it around the transaction.

Not everywhere. Just write paths.

I also log when it happens. If retries are constant, something else is wrong.

4) I measure the write path. Otherwise I’m guessing.

If you want sub-100ms UI, you can’t “feel” performance. You have to time it.

I added a timing wrapper around the batch insert. It prints duration and batch size.

This is how I caught a dumb mistake: I was inserting sets one-by-one again because my queue flush function split arrays wrong. Brutal.

// timedInsert.ts
import { insertSetsBatch } from './writeBatch';
import { retrySqliteBusy } from './retrySqliteBusy';

type SetRow = Parameters[0][number];

export async function insertSetsBatchTimed(rows: SetRow[]) {
  const t0 = global.performance?.now?.() ?? Date.now();

  await retrySqliteBusy(() => {
    insertSetsBatch(rows);
    return true;
  });

  const t1 = global.performance?.now?.() ?? Date.now();
  const ms = Math.round((t1 - t0) * 100) / 100;

  console.log(`[sqlite] inserted ${rows.length} sets in ${ms}ms`);
}

On my Pixel 7, a batch of 10 inserts is usually 2ms to 6ms. On an older Moto G (test device), it’s more like 8ms to 22ms.

More important: it doesn’t randomly fail anymore.

Results

Before WAL + batching, I could reproduce lock errors in under 60 seconds: start a workout, spam log 30 sets, then open history search in parallel. I’d see SQLITE_BUSY about 6 to 12 times per session.

After the changes, I did the same stress test for 20 minutes. I saw SQLITE_BUSY 2 times total, and both succeeded after 1 retry. No dropped set logs. No “tap again”.

Write timings also got predictable. My largest flush (48 set rows after an offline session) completed in 41ms on my slow Android test phone.

Key takeaways

Turn on WAL early. Startup code. Not “later”.
Use one transaction per flush. Not one insert per tap.
Add busy_timeout, then still handle SQLITE_BUSY with retries.
Time your writes and print batch size. You’ll catch dumb queue bugs fast.
If you’re seeing constant retries, you’ve got a long-running query somewhere. Fix that next.

Closing

If you’re using Expo SQLite: are you relying only on busy_timeout, or do you also retry SQLITE_BUSY in code with backoff?

Next.js job board: stable URLs for scraped jobs

Sathish — Tue, 07 Apr 2026 15:12:47 +0000

I generate stable job URLs from messy scraped data.
I avoid slug collisions with a deterministic hash.
I enforce uniqueness in Postgres, not in app code.
I handle “same job, new source URL” without 404s.

Context

My job board ingests 200+ jobs daily. Multiple sources. Same role. Different URLs. Different punctuation. Sometimes different company names too.

At first, I used a normal slug.

/jobs/psychiatric-nurse-practitioner-remote

Brutal idea.

I got collisions within a week. Two companies. Same title. Same city. Boom. Wrong job on the wrong page.

Then I tried “just append the company.” Still collisions. “Behavioral Health” vs “Behavioral-Health” vs “Behavioral Health Inc”. Scrapers aren’t polite.

So I switched to stable IDs. Deterministic. Based on a canonical fingerprint. Stored in Postgres. One URL per real-world job.

This post is that setup. Next.js 14 + Supabase + Postgres.

1) I stopped trusting slugs from scraped text

Slugs are for humans. IDs are for computers.

I still want readable URLs. But the stable part has to be machine-generated.

So I build two fields:

slug: pretty, derived from title + company
public_id: stable, derived from a fingerprint hash

public_id becomes the real key in the URL.

Here’s the exact function I use in my ingestion worker. Node runtime. No weird deps.

// lib/jobIdentity.ts
import crypto from "node:crypto";

export function slugify(input: string): string {
  return input
    .toLowerCase()
    .normalize("NFKD")
    .replace(/[\u0300-\u036f]/g, "") // strip accents
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/(^-|-$)/g, "")
    .slice(0, 80);
}

// Keep it boring. Deterministic. No timestamps.
export function jobFingerprint(fields: {
  title: string;
  company: string;
  city?: string | null;
  state?: string | null;
  country?: string | null;
  employmentType?: string | null;
}): string {
  const norm = (s?: string | null) => (s ?? "").trim().toLowerCase();

  // Don't include source_url. It changes all the time.
  // Don't include salary. Scrapers lie.
  const payload = [
    norm(fields.title),
    norm(fields.company),
    norm(fields.city),
    norm(fields.state),
    norm(fields.country),
    norm(fields.employmentType),
  ].join("|");

  return crypto.createHash("sha256").update(payload).digest("hex");
}

export function publicIdFromFingerprint(fingerprint: string): string {
  // 12 bytes => 24 hex chars. Short enough for URLs.
  return fingerprint.slice(0, 24);
}

I learned the hard way that “include everything” makes IDs unstable.

If you put source_url into the hash, every time a site adds ?utm=..., you create a “new job”. Your listings inflate. Your dedupe fails silently.

2) I made the URL format boring on purpose

My job pages are:

/jobs/{public_id}/{slug}

The slug can change later. The public_id can’t.

That solves two things:

Collisions stop. public_id is unique.
Renames stop breaking links. Only the tail changes.

In Next.js App Router, I parse it like this.

// app/jobs/[publicId]/[slug]/page.tsx
import { notFound, redirect } from "next/navigation";
import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // server-only
);

function buildSlug(title: string, company: string) {
  return `${title} ${company}`
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/(^-|-$)/g, "")
    .slice(0, 80);
}

export default async function JobPage({
  params,
}: {
  params: Promise<{ publicId: string; slug: string }>;
}) {
  const { publicId, slug } = await params;

  const { data: job, error } = await supabase
    .from("jobs")
    .select("public_id,title,company,description,updated_at")
    .eq("public_id", publicId)
    .maybeSingle();

  if (error) throw error;
  if (!job) notFound();

  const canonicalSlug = buildSlug(job.title, job.company);
  if (slug !== canonicalSlug) {
    // Permanent canonicalization. Stops duplicate pages.
    redirect(`/jobs/${job.public_id}/${canonicalSlug}`);
  }

  return (

      # {job.title}

      {job.company}

      {job.description}

  );
}

Yeah, I’m using service role on the server.

If you don’t like that, use Supabase SSR helpers + RLS. I do that in other parts. For this page, I wanted predictable reads and zero client-side fetch.

Also: that redirect() is doing SEO work. It prevents /jobs/abc/old-slug from becoming a second indexed page.

3) I pushed uniqueness into Postgres, not my scraper

I tried to “dedupe in code.”

Spent 4 hours on this. Most of it was wrong.

Two workers ran at the same time. Same job. Both saw “not exists yet.” Both inserted. Classic race.

So I let Postgres do its job.

I store:

fingerprint (full sha256 hex)
public_id (short stable id)

And enforce uniqueness.

-- migrations/20260407_jobs_identity.sql
create extension if not exists pgcrypto;

create table if not exists jobs (
  id bigserial primary key,
  public_id text not null,
  fingerprint text not null,
  title text not null,
  company text not null,
  city text,
  state text,
  country text,
  employment_type text,
  source_url text,
  description text,
  posted_at timestamptz,
  updated_at timestamptz not null default now()
);

-- Stable identity
create unique index if not exists jobs_public_id_ux on jobs (public_id);
create unique index if not exists jobs_fingerprint_ux on jobs (fingerprint);

-- Helpful for common queries
create index if not exists jobs_updated_at_idx on jobs (updated_at desc);

Then ingestion becomes “upsert by fingerprint”.

If the job already exists, I update mutable fields like source_url, description, updated_at.

// scripts/ingestOne.ts
import { createClient } from "@supabase/supabase-js";
import {
  jobFingerprint,
  publicIdFromFingerprint,
  slugify,
} from "../lib/jobIdentity";

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

type ScrapedJob = {
  title: string;
  company: string;
  city?: string | null;
  state?: string | null;
  country?: string | null;
  employmentType?: string | null;
  sourceUrl: string;
  description: string;
};

export async function ingestOne(j: ScrapedJob) {
  const fingerprint = jobFingerprint({
    title: j.title,
    company: j.company,
    city: j.city,
    state: j.state,
    country: j.country,
    employmentType: j.employmentType,
  });

  const publicId = publicIdFromFingerprint(fingerprint);

  const { error } = await supabase.from("jobs").upsert(
    {
      public_id: publicId,
      fingerprint,
      title: j.title,
      company: j.company,
      city: j.city ?? null,
      state: j.state ?? null,
      country: j.country ?? null,
      employment_type: j.employmentType ?? null,
      source_url: j.sourceUrl,
      description: j.description,
      updated_at: new Date().toISOString(),
    },
    {
      onConflict: "fingerprint", // the important part
    }
  );

  if (error) throw error;

  return {
    publicId,
    urlSlug: slugify(`${j.title} ${j.company}`),
  };
}

This is the whole trick.

I don’t care if 3 scrapers hit the same job. Postgres resolves it.

One thing that bit me: don’t use onConflict: "public_id".

If you ever change how you generate public_id (even accidentally), you’ll create duplicates. The fingerprint is the real identity. Keep that invariant.

4) I kept old URLs alive when the “identity” changes

Sometimes the fingerprint really does change.

Example: a source switches from “NYC” to “New York”. Or the company name gets normalized later. Or I fix a bug in my canonicalization.

If the fingerprint changes, public_id changes.

That’s a broken link.

So I store aliases. Old public_id values that redirect to the new one.

This saved me after I changed my normalization rules and accidentally invalidated 317 URLs. I noticed because my own smoke test started hitting 404.

Schema + redirect lookup.

-- migrations/20260407_job_aliases.sql
create table if not exists job_aliases (
  old_public_id text primary key,
  new_public_id text not null references jobs(public_id) on delete cascade,
  created_at timestamptz not null default now()
);

create index if not exists job_aliases_new_idx on job_aliases (new_public_id);

And in Next.js, I check aliases before I give up.

// app/jobs/[publicId]/[slug]/page.tsx
import { notFound, redirect } from "next/navigation";
import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

export default async function JobPage({
  params,
}: {
  params: Promise<{ publicId: string; slug: string }>;
}) {
  const { publicId, slug } = await params;

  const { data: job } = await supabase
    .from("jobs")
    .select("public_id,title,company,description")
    .eq("public_id", publicId)
    .maybeSingle();

  if (job) {
    const canonicalSlug = `${job.title} ${job.company}`
      .toLowerCase()
      .replace(/[^a-z0-9]+/g, "-")
      .replace(/(^-|-$)/g, "")
      .slice(0, 80);

    if (slug !== canonicalSlug) redirect(`/jobs/${job.public_id}/${canonicalSlug}`);
    return (

        # {job.title}

        {job.company}

        {job.description}

    );
  }

  // Not found? Try alias redirect.
  const { data: alias } = await supabase
    .from("job_aliases")
    .select("new_public_id")
    .eq("old_public_id", publicId)
    .maybeSingle();

  if (alias?.new_public_id) {
    redirect(`/jobs/${alias.new_public_id}/${slug}`); // slug will canonicalize next
  }

  notFound();
}

That last redirect looks weird. It is.

I redirect to the new public_id first, keeping whatever slug the user had. Then the canonical slug redirect runs. Two hops.

I kept it because it’s simple and it works.

If you want one hop, you can fetch the job by new_public_id and build the canonical URL immediately.

Results

Before stable IDs, I had 41 collisions in my jobs table in one week. Same slug. Different jobs. Users reported “this page changed overnight” twice. That was enough.

After switching to public_id + slug, collisions dropped to 0 because the URL key stopped being user-visible text. I also fixed 317 broken URLs after a normalization change by adding job_aliases redirects, instead of letting them 404.

The board is sitting at 8,000+ active listings from 2,000+ companies. URLs are boring now. That’s the point.

Key takeaways

Make the stable part of the URL deterministic, not pretty.
Hash a canonical fingerprint. Don’t hash source URLs.
Put uniqueness constraints in Postgres, or concurrency will humble you.
Treat slug as display-only. Enforce canonical redirects in Next.js.
When you change identity rules, keep aliases so old links still resolve.

Closing

If you’re scraping jobs (or listings, or products), URL stability becomes an SEO problem fast.

How do you build your fingerprint: which fields do you include, and which ones did you remove after getting burned by unstable IDs?

Cursor + Claude: stop shipping flaky UI states

Sathish — Thu, 02 Apr 2026 15:00:53 +0000

I use Cursor + Claude to generate state diagrams, not random code.
I convert UI states into a typed reducer. No more “boolean soup”.
I add 1 tiny invariant test that catches most regressions.
Works for Next.js, React, and even boring CRUD screens.

Context

I ship a lot of small SaaS apps. Usually solo. Usually fast.

And my UI bugs weren’t “hard.” They were dumb. The kind that shows up only after you add one more button.

Stuff like: loading spinner stays forever. Save button enables while a request is in flight. Error banner shows on the success screen. Or the classic: isLoading is false but you’re still “saving”.

I tried the usual.

A couple useStates. Then more. Then a useEffect to “sync” them. Brutal. Spent 4 hours on this once. Most of it was wrong.

Now I treat UI like a state machine. Not a library. Just a reducer and strict events.

Cursor + Claude help me do it fast. The trick: I don’t ask for code first. I ask for states.

1) I write the states down. Then I force Claude to agree

I start in Cursor. New file. Something like ui-state-notes.md.

I describe the screen in plain English. Then I ask Claude for a state table.

Not “write React code.”

I want a list of states and events. If the list is wrong, code won’t save me.

Here’s the exact prompt style I use (you can paste this into a Cursor chat). It’s boring on purpose.

You are my reviewer.
Given this UI, produce:
1) a list of mutually exclusive states
2) allowed events per state
3) forbidden transitions
4) 3 invariants that must always hold

UI:
- form with 3 fields
- Save triggers API call
- can show validation errors
- can show server error toast
- after success, show “Saved” and disable Save for 2s

No code yet. Only the table.

Then I sanity-check it like a grumpy QA.

If Claude gives me 12 states for a simple form, I push back. If it forgets “submit while already submitting”, I push back.

One thing that bit me early — Claude loves adding a retrying state. I almost never need it. I model retry as an event, not a state.

Once the table looks right, I freeze it. That’s my spec.

2) I kill boolean soup with a typed reducer

Most flaky screens I’ve seen have the same smell:

isLoading
isSaving
hasError
showSuccess
disabled

Five booleans. Thirty-two combinations. Half of them nonsense.

So I replace them with a discriminated union and a reducer.

This snippet is copy-paste ready. Plain React. Works in Next.js App Router too.

import * as React from "react";

type State =
  | { tag: "idle"; values: Record; fieldErrors: Record }
  | { tag: "submitting"; values: Record }
  | { tag: "success"; values: Record; lockedUntil: number }
  | { tag: "serverError"; values: Record; message: string };

type Event =
  | { type: "CHANGE"; name: string; value: string }
  | { type: "SUBMIT" }
  | { type: "VALIDATION_FAILED"; fieldErrors: Record }
  | { type: "SUBMIT_OK"; now: number }
  | { type: "SUBMIT_ERR"; message: string }
  | { type: "UNLOCK"; now: number };

export function reducer(state: State, event: Event): State {
  switch (state.tag) {
    case "idle": {
      if (event.type === "CHANGE") {
        return {
          tag: "idle",
          values: { ...state.values, [event.name]: event.value },
          fieldErrors: { ...state.fieldErrors, [event.name]: "" },
        };
      }
      if (event.type === "SUBMIT") return { tag: "submitting", values: state.values };
      if (event.type === "VALIDATION_FAILED") return { ...state, fieldErrors: event.fieldErrors };
      return state;
    }
    case "submitting": {
      if (event.type === "SUBMIT_OK") return { tag: "success", values: state.values, lockedUntil: event.now + 2000 };
      if (event.type === "SUBMIT_ERR") return { tag: "serverError", values: state.values, message: event.message };
      // Ignore changes while submitting. This is a product choice.
      return state;
    }
    case "success": {
      if (event.type === "UNLOCK" && event.now >= state.lockedUntil) {
        return { tag: "idle", values: state.values, fieldErrors: {} };
      }
      return state;
    }
    case "serverError": {
      if (event.type === "CHANGE") {
        return {
          tag: "serverError",
          values: { ...state.values, [event.name]: event.value },
          message: state.message,
        };
      }
      if (event.type === "SUBMIT") return { tag: "submitting", values: state.values };
      return state;
    }
  }
}

Notice what’s missing.

No isLoading. No showToast. No disabled state.

I derive UI behavior from state.tag.

And yeah, it feels slower at first. Then the second screen takes 15 minutes.

3) I wire the reducer to real async code (without useEffect spaghetti)

This is the part where I used to mess up.

I’d do:

set loading true
await
set loading false
set success

Then a request throws, and half the setters don’t run.

Reducer fixes that.

I dispatch events around the async boundary. One entry. One exit.

import * as React from "react";
import { reducer } from "./reducer";

type SaveInput = { values: Record };

async function saveToApi(input: SaveInput): Promise {
  const res = await fetch("/api/save", {
    method: "POST",
    headers: { "content-type": "application/json" },
    body: JSON.stringify(input),
  });

  if (!res.ok) {
    // Common: API returns { message }
    const data = (await res.json().catch(() => ({}))) as { message?: string };
    throw new Error(data.message ?? `Request failed: ${res.status}`);
  }
}

export function SettingsForm() {
  const [state, dispatch] = React.useReducer(reducer, {
    tag: "idle",
    values: { name: "", email: "", company: "" },
    fieldErrors: {},
  });

  const onSubmit = async (e: React.FormEvent) => {
    e.preventDefault();

    if (state.tag !== "idle" && state.tag !== "serverError") return;

    // Tiny validation example. Keep it deterministic.
    const fieldErrors: Record = {};
    if (!state.values.email.includes("@")) fieldErrors.email = "Invalid email";
    if (Object.keys(fieldErrors).length) {
      dispatch({ type: "VALIDATION_FAILED", fieldErrors });
      return;
    }

    dispatch({ type: "SUBMIT" });

    try {
      await saveToApi({ values: state.values });
      dispatch({ type: "SUBMIT_OK", now: Date.now() });
    } catch (err) {
      dispatch({ type: "SUBMIT_ERR", message: err instanceof Error ? err.message : "Unknown error" });
    }
  };

  React.useEffect(() => {
    if (state.tag !== "success") return;
    const t = setInterval(() => dispatch({ type: "UNLOCK", now: Date.now() }), 200);
    return () => clearInterval(t);
  }, [state.tag]);

  const disabled = state.tag === "submitting" || (state.tag === "success" && Date.now() < state.lockedUntil);

  return (

       dispatch({ type: "CHANGE", name: "name", value: e.target.value })}
        disabled={disabled}
      />
       dispatch({ type: "CHANGE", name: "email", value: e.target.value })}
        disabled={disabled}
      />

        {state.tag === "submitting" ? "Saving…" : "Save"}

      {state.tag === "serverError" && {state.message}}
      {state.tag === "success" && Saved
}

  );
}

Yes, there’s a useEffect.

But it’s not “sync booleans.” It’s only for the timed unlock. And it’s scoped to one state.

Also. That disabled line is boring.

Boring is good.

4) I add one invariant test. It catches the dumb stuff

I don’t write a full test suite for every form. I’m not a hero.

But I do add one invariant test for reducers. Because reducers are pure. Cheap to test.

This one checks a rule I’ve broken multiple times: you can’t be success and submitting in the same flow, and success must have a future lockedUntil.

Node has a built-in test runner now. No Jest needed.

Create reducer.test.ts and run node --test --import tsx reducer.test.ts.

import test from "node:test";
import assert from "node:assert/strict";
import { reducer } from "./reducer";

test("success state always has a 2s lock", () => {
  const s1 = { tag: "submitting" as const, values: { name: "a" } };
  const now = 1712012345678;

  const s2 = reducer(s1, { type: "SUBMIT_OK", now });

  assert.equal(s2.tag, "success");
  if (s2.tag === "success") {
    assert.equal(s2.lockedUntil, now + 2000);
  }
});

test("SUBMIT_OK is ignored outside submitting", () => {
  const s1 = { tag: "idle" as const, values: { name: "a" }, fieldErrors: {} };
  const s2 = reducer(s1, { type: "SUBMIT_OK", now: 10 });
  assert.deepEqual(s2, s1);
});

That second test matters.

I once dispatched SUBMIT_OK from a stale promise after the user navigated away. UI went into a fake success state. Took me an hour to reproduce.

Now the reducer just ignores nonsense.

Results

On my last 3 screens built this way, I shipped with 4 reducer states (idle, submitting, success, serverError) and 6 events.

Before this approach, the same screens usually ended up with 7 booleans and 2 useEffects “keeping things in sync”. That’s where the bugs lived.

Also: debugging got faster. When something’s wrong, I log state.tag and the last event. That’s it. No more staring at React DevTools watching five flags flip.

Key takeaways

Don’t ask Claude for code first. Ask for a state/event table.
If you can’t explain the states in 60 seconds, the UI is too complex.
Replace multiple booleans with a discriminated union. Every time.
Dispatch events around async boundaries. One entry. One exit.
Add one invariant test for the reducer. Pure functions deserve cheap tests.

Closing

If you’re using Cursor + Claude, try this once.

Make Claude list states and forbidden transitions. Then write the reducer yourself, fast, inside Cursor.

What’s the one UI flow you keep breaking — settings form, onboarding, checkout, or something else — and what are the states you’d put in your reducer?

React Native offline-first: instant search in SQLite

Sathish — Wed, 01 Apr 2026 15:13:00 +0000

I ship offline exercise search with SQLite FTS5.
I keep typing smooth with a debounced query.
I highlight matches and rank results, fast.
I learned why %LIKE% is a trap on mobile.

Context

My fitness app has ~400 exercises. Each has a name, muscle group, and a GIF.

Search is the main screen. People open the app mid-workout. They type fast. They expect results instantly.

I started with LIKE '%query%' on a normal SQLite table. Brutal. It worked with 40 rows. Then I imported the real dataset. Typing dropped frames. My “5-second set logging” promise fell apart because the search screen felt sticky.

Also: offline-first. No server search. No Algolia. No “just hit an endpoint.”

So I moved search to SQLite FTS5. Same local DB. Real full-text index. And I stopped doing work on every keystroke.

1) I stopped using LIKE. I indexed.

LIKE feels simple. It’s also a full table scan when you wrap the query in %...%.

On a phone. While React is trying to render.

FTS5 fixed it. But only after I wired it correctly.

I keep a normal exercises table for the source of truth. Then an FTS virtual table for search. Then triggers to keep them in sync.

Code. This is the exact schema setup.

// db/schema.ts
import * as SQLite from "expo-sqlite";

export async function initDb(db: SQLite.SQLiteDatabase) {
  // Base table
  await db.execAsync(`
    PRAGMA journal_mode = WAL;

    CREATE TABLE IF NOT EXISTS exercises (
      id INTEGER PRIMARY KEY NOT NULL,
      name TEXT NOT NULL,
      primary_muscle TEXT NOT NULL,
      equipment TEXT NOT NULL
    );

    -- Full-text index. content=exercises lets us join back.
    CREATE VIRTUAL TABLE IF NOT EXISTS exercises_fts
    USING fts5(
      name,
      primary_muscle,
      equipment,
      content='exercises',
      content_rowid='id',
      tokenize='unicode61 remove_diacritics 2'
    );

    -- Keep FTS in sync.
    CREATE TRIGGER IF NOT EXISTS exercises_ai AFTER INSERT ON exercises BEGIN
      INSERT INTO exercises_fts(rowid, name, primary_muscle, equipment)
      VALUES (new.id, new.name, new.primary_muscle, new.equipment);
    END;

    CREATE TRIGGER IF NOT EXISTS exercises_ad AFTER DELETE ON exercises BEGIN
      INSERT INTO exercises_fts(exercises_fts, rowid, name, primary_muscle, equipment)
      VALUES ('delete', old.id, old.name, old.primary_muscle, old.equipment);
    END;

    CREATE TRIGGER IF NOT EXISTS exercises_au AFTER UPDATE ON exercises BEGIN
      INSERT INTO exercises_fts(exercises_fts, rowid, name, primary_muscle, equipment)
      VALUES ('delete', old.id, old.name, old.primary_muscle, old.equipment);
      INSERT INTO exercises_fts(rowid, name, primary_muscle, equipment)
      VALUES (new.id, new.name, new.primary_muscle, new.primary_muscle, new.equipment);
    END;
  `);
}

Yes, I made a dumb mistake there the first time.

I had new.primary_muscle twice (copy/paste brain). Search results were “fine” but equipment filtering was wrong. Spent 2 hours thinking FTS was broken. It was me.

Also: WAL matters. Without it, I hit random “database is locked” during writes + reads. WAL reduced that pain a lot.

2) I query FTS5 like a grown-up

FTS query syntax is not SQL LIKE.

I want prefix matching. If someone types ben, I want bench press.

FTS does that with *. So I build ben*.

But. If you shove raw user input into FTS, you’ll get parser errors.

I hit this exact error:

SQLITE_ERROR: fts5: syntax error near "-"

Someone typed t-bar.

So I sanitize. And I keep the query small.

// db/search.ts
import * as SQLite from "expo-sqlite";

function ftsPrefixQuery(input: string) {
  // Keep letters/numbers/spaces. Drop punctuation that breaks FTS.
  const cleaned = input
    .toLowerCase()
    .replace(/[^a-z0-9\s]/g, " ")
    .trim();

  if (!cleaned) return "";

  // "bench press" -> "bench* press*"
  return cleaned
    .split(/\s+/)
    .filter(Boolean)
    .map((t) => `${t}*`)
    .join(" ");
}

export async function searchExercises(
  db: SQLite.SQLiteDatabase,
  input: string,
  limit = 30
) {
  const q = ftsPrefixQuery(input);
  if (!q) return [] as any[];

  // bm25() gives decent ranking for free.
  const rows = await db.getAllAsync(
    `
    SELECT e.id, e.name, e.primary_muscle, e.equipment,
           bm25(exercises_fts) AS rank
    FROM exercises_fts
    JOIN exercises e ON e.id = exercises_fts.rowid
    WHERE exercises_fts MATCH ?
    ORDER BY rank
    LIMIT ?;
    `,
    [q, limit]
  );

  return rows;
}

bm25() is underrated. I used to hand-roll scoring. Total waste.

And yes, I sort ascending. Smaller bm25 score ranks higher.

One more thing that bit me — FTS tokenization.

If your dataset has incline bench and users type inclined, stemming won’t happen by default. SQLite FTS5 doesn’t magically do stemming unless you add a tokenizer that supports it. I didn’t. I accepted it. I’d rather be predictable than “smart” and wrong.

3) I debounced typing. Otherwise you DDOS your own DB

Even with FTS, doing a query on every keystroke is noisy.

React Native renders. The keyboard fires events. Your JS thread gets busy. Then you blame SQLite.

I debounce at 120ms. Not 500ms. That feels laggy.

And I cancel stale requests so results don’t flicker.

// hooks/useDebouncedValue.ts
import { useEffect, useState } from "react";

export function useDebouncedValue(value: T, delayMs: number) {
  const [debounced, setDebounced] = useState(value);

  useEffect(() => {
    const id = setTimeout(() => setDebounced(value), delayMs);
    return () => clearTimeout(id);
  }, [value, delayMs]);

  return debounced;
}

// screens/ExerciseSearchScreen.tsx
import React, { useEffect, useMemo, useState } from "react";
import { Text, TextInput, View, FlatList } from "react-native";
import * as SQLite from "expo-sqlite";
import { useDebouncedValue } from "../hooks/useDebouncedValue";
import { searchExercises } from "../db/search";

const db = SQLite.openDatabaseSync("gym.db");

export function ExerciseSearchScreen() {
  const [text, setText] = useState("");
  const debounced = useDebouncedValue(text, 120);
  const [rows, setRows] = useState([]);

  // Simple request id. Prevent stale async updates.
  const requestIdRef = React.useRef(0);

  useEffect(() => {
    let alive = true;
    const reqId = ++requestIdRef.current;

    (async () => {
      if (!debounced.trim()) {
        setRows([]);
        return;
      }
      const res = await searchExercises(db, debounced, 30);
      if (!alive) return;
      if (reqId !== requestIdRef.current) return;
      setRows(res);
    })();

    return () => {
      alive = false;
    };
  }, [debounced]);

  const renderItem = ({ item }: { item: any }) => (

      {item.name}

        {item.primary_muscle} • {item.equipment}


  );

  return (



       String(x.id)}
        renderItem={renderItem}
      />

  );
}

This is boring code. Good.

Before debounce, I could see 20+ queries in a second while typing. After debounce, it’s usually 4–6 queries for a typical search.

And the UI stopped feeling “wet.” You know that feeling. Like you’re dragging the app through mud.

4) I highlight matches without doing fancy stuff

People scan.

If I show bench press and they typed ben, their eyes want confirmation.

FTS5 can return offsets via offsets() but parsing that output is annoying. I tried. Spent 4 hours on it. Most of it was wrong.

I went simpler: highlight prefix matches in the UI using the debounced input tokens. It’s not perfect, but it’s stable and fast.

// ui/highlight.tsx
import React from "react";
import { Text } from "react-native";

export function HighlightedText({ text, query }: { text: string; query: string }) {
  const tokens = query
    .toLowerCase()
    .replace(/[^a-z0-9\s]/g, " ")
    .trim()
    .split(/\s+/)
    .filter(Boolean);

  if (!tokens.length) return {text};

  // Highlight only the first token prefix. Simple. Predictable.
  const t0 = tokens[0];
  const lower = text.toLowerCase();
  const idx = lower.indexOf(t0);

  if (idx === -1) return {text};

  return (

      {text.slice(0, idx)}
      {text.slice(idx, idx + t0.length)}
      {text.slice(idx + t0.length)}

  );
}

Does it highlight the second word? Nope.

But it avoids a class of bugs where the highlight logic fights the ranking logic. I’ll take “good enough” when the main goal is speed.

Results

I went from a LIKE query that routinely took 120–300ms on my Pixel 7 (and sometimes spiked to ~600ms) to an FTS query that’s usually 8–25ms for the same input.

The bigger win was UI feel.

With debounce at 120ms, typing bench press triggers 5 queries instead of 11. The search screen stays at 60fps on my iPhone 13 mini, even with the exercise list rendering 30 rows and each row showing muscle + equipment.

And offline still works. Airplane mode. Same experience.

Key takeaways

Don’t use %LIKE% for mobile search beyond toy datasets.
Use FTS5 with content='table' + triggers so you don’t duplicate data.
Sanitize user input or you’ll ship fts5: syntax error near "-".
Debounce at ~120ms and cancel stale requests. Otherwise results flicker.
Keep highlighting dumb. Stable beats clever.

Closing

If you’re doing offline-first search in React Native: do you rely on SQLite FTS ranking (bm25) only, or do you add your own scoring (muscle match, equipment match, recency) on top of it? What’s your exact formula?

Cursor + Claude: stop shipping broken auth flows

Sathish — Thu, 26 Mar 2026 15:00:49 +0000

I use Cursor + Claude to generate auth code, then I try to break it.
I run a tiny Node script to fuzz cookies + headers.
I lock session handling into one file. No scattered reads.
I add 3 tests that catch 80% of my dumb mistakes.

Context

I ship small SaaS apps. Usually fast. Usually solo.

Auth is where I bleed time. Not because OAuth is hard. Because I miss boring edge cases.

Cursor + Claude help me write the first draft. Fast. But the first draft lies. It’ll “work” in the happy path. Then I open a second tab, log out, and something still looks logged in.

Spent 4 hours last month on a “random logout” bug. Most of it was wrong. The root cause: I was reading the session in 3 different places, each with slightly different assumptions.

So I switched to a workflow: generate → centralize → fuzz → test. Same steps every time.

1) I force all session reads through one function

If I read cookies in five files, I’m done.

One module. One exported function. Everything else calls that.

In Next.js App Router, I do it in lib/session.ts. Server-only.

// lib/session.ts
import { cookies, headers } from "next/headers";

export type Session = {
  userId: string;
  email: string;
  roles: string[];
  expiresAt: number; // unix ms
};

// Example: cookie contains base64url(JSON). Swap with real signed token.
function base64UrlToString(input: string) {
  const pad = "=".repeat((4 - (input.length % 4)) % 4);
  const b64 = (input + pad).replace(/-/g, "+").replace(/_/g, "/");
  return Buffer.from(b64, "base64").toString("utf8");
}

export function getSession(): Session | null {
  // Single source of truth.
  const token = cookies().get("session")?.value;
  if (!token) return null;

  try {
    const json = base64UrlToString(token);
    const parsed = JSON.parse(json) as Session;

    // Hard checks. No vibes.
    if (!parsed.userId) return null;
    if (!parsed.email) return null;
    if (!Array.isArray(parsed.roles)) return null;
    if (typeof parsed.expiresAt !== "number") return null;
    if (Date.now() > parsed.expiresAt) return null;

    // Optional: bind to user-agent to reduce replay.
    const ua = headers().get("user-agent") || "";
    if (ua.length === 0) return null;

    return parsed;
  } catch {
    return null;
  }
}

This snippet isn’t “secure auth”. It’s structure.

The point: every request goes through the same gates. If I change cookie format, I change one file.

One thing that bit me — Cursor happily generated JSON.parse(cookies().get(...)) with no try/catch. That crashed rendering for anyone with a stale cookie.

2) I make middleware do one job: block obvious bad routes

I used to do too much in middleware.

Then I got stuck in redirect loops. Brutal.

Now middleware only answers: “Can this request touch /app?” It doesn’t fetch user profiles. It doesn’t refresh tokens. It doesn’t call Supabase.

// middleware.ts
import { NextResponse } from "next/server";
import type { NextRequest } from "next/server";

export function middleware(req: NextRequest) {
  const { pathname, searchParams } = req.nextUrl;

  // Only protect app routes.
  if (!pathname.startsWith("/app")) return NextResponse.next();

  const hasSessionCookie = req.cookies.has("session");
  if (hasSessionCookie) return NextResponse.next();

  const loginUrl = req.nextUrl.clone();
  loginUrl.pathname = "/login";
  loginUrl.searchParams.set("next", pathname + (searchParams.size ? `?${searchParams}` : ""));

  return NextResponse.redirect(loginUrl);
}

export const config = {
  matcher: ["/app/:path*"],
};

That’s it.

Why so dumb-simple? Because I can reason about it when I’m tired.

Also: middleware runs on Edge by default. Half the Node APIs aren’t there. Cursor will still generate code that uses crypto in middleware and you’ll get a fun error like:

Module not found: Can't resolve 'crypto'

So I keep middleware boring. Server-only logic stays server-only.

3) I fuzz my auth endpoints with a 30-line script

This is the part that changed everything.

I don’t wait for “a user reported it.” I try to break my own app.

I keep a script in scripts/fuzz-auth.mjs. It hits a few routes with:

no cookie
garbage cookie
expired cookie
huge cookie

It catches crashes. It catches accidental 200s. It catches HTML pages returning JSON errors.

// scripts/fuzz-auth.mjs
const base = process.env.BASE_URL || "http://localhost:3000";

const cases = [
  { name: "no cookie", cookie: null },
  { name: "garbage", cookie: "session=lol-nope" },
  { name: "expired-ish", cookie: "session=ZXhwaXJlZA" },
  { name: "huge", cookie: "session=" + "a".repeat(9000) },
];

const paths = ["/app", "/app/settings", "/api/me"]; // adjust for your app

for (const p of paths) {
  for (const c of cases) {
    const res = await fetch(base + p, {
      redirect: "manual",
      headers: c.cookie ? { cookie: c.cookie } : {},
    });

    const ct = res.headers.get("content-type") || "";
    console.log(`${p} | ${c.name} | ${res.status} | ${ct.split(";")[0]}`);

    // If your API routes should never return HTML, enforce it.
    if (p.startsWith("/api") && ct.includes("text/html")) {
      process.exitCode = 1;
    }

    // Catch server crashes disguised as 200.
    if (res.status >= 500) {
      process.exitCode = 1;
    }
  }
}

Run it like:

BASE_URL=http://localhost:3000 node scripts/fuzz-auth.mjs

This is where Claude helps a lot.

I paste the console output. I paste the route code. I ask: “Why is /api/me returning HTML for garbage cookies?” Then I fix the actual bug.

But I don’t let it rewrite everything. I tell it: keep the shape, change the smallest surface area.

4) I add 3 tests. Only 3. They’re mean.

I don’t write 40 auth tests. I won’t maintain them.

I write three that stop the bleeding:
1) protected page redirects when no cookie
2) API returns 401 JSON when no cookie
3) API returns 401 JSON when cookie is invalid

In App Router, I like testing the session parser directly. It’s stable and fast.

Here’s a Vitest unit test for the token decoding logic. I keep it pure by extracting decode into a helper.

// lib/session-decode.ts
export type Session = {
  userId: string;
  email: string;
  roles: string[];
  expiresAt: number;
};

function base64UrlToString(input: string) {
  const pad = "=".repeat((4 - (input.length % 4)) % 4);
  const b64 = (input + pad).replace(/-/g, "+").replace(/_/g, "/");
  return Buffer.from(b64, "base64").toString("utf8");
}

export function decodeSessionCookie(token: string): Session | null {
  try {
    const json = base64UrlToString(token);
    const parsed = JSON.parse(json) as Session;
    if (!parsed.userId) return null;
    if (!parsed.email) return null;
    if (!Array.isArray(parsed.roles)) return null;
    if (typeof parsed.expiresAt !== "number") return null;
    if (Date.now() > parsed.expiresAt) return null;
    return parsed;
  } catch {
    return null;
  }
}

// test/session-decode.test.ts
import { describe, it, expect } from "vitest";
import { decodeSessionCookie } from "../lib/session-decode";

function toBase64Url(s: string) {
  return Buffer.from(s, "utf8")
    .toString("base64")
    .replace(/\+/g, "-")
    .replace(/\//g, "_")
    .replace(/=+$/g, "");
}

describe("decodeSessionCookie", () => {
  it("returns null for garbage", () => {
    expect(decodeSessionCookie("lol-nope")).toBeNull();
  });

  it("returns null for expired", () => {
    const token = toBase64Url(
      JSON.stringify({
        userId: "u1",
        email: "a@b.com",
        roles: ["user"],
        expiresAt: Date.now() - 1,
      })
    );
    expect(decodeSessionCookie(token)).toBeNull();
  });

  it("parses a valid session", () => {
    const token = toBase64Url(
      JSON.stringify({
        userId: "u1",
        email: "a@b.com",
        roles: ["user"],
        expiresAt: Date.now() + 60_000,
      })
    );
    const s = decodeSessionCookie(token);
    expect(s?.userId).toBe("u1");
    expect(s?.roles).toEqual(["user"]);
  });
});

Yeah, it’s “just parsing”.

But it stops regressions when I change cookie structure. And Claude is good at suggesting extra assertions without turning the test into a novel.

Results

On my last build week, I shipped auth changes on 6 different days. Real life.

Before this workflow, I’d usually find 2 or 3 auth regressions per week. Stuff like: /api/me returning a 500 for a bad cookie, or a protected page rendering and then redirecting client-side.

After centralizing session reads + adding the fuzz script + the 3 tests, I hit 0 auth-related 500s locally across 27 fuzz runs (3 paths × 4 cases × reruns while fixing). And I stopped seeing redirect loops entirely because middleware stopped doing “smart” things.

Not magic. Just fewer moving parts.

Key takeaways

Put all cookie/session reads in one server-only module.
Keep middleware dumb: allow/deny + redirect only.
Fuzz auth with a script. It’s faster than clicking.
Add 3 unit tests that you’ll actually maintain.
When Claude writes auth code, assume it forgot the unhappy path.

Closing

If you already use Cursor + Claude for auth, don’t ask it for “secure auth”. Ask it for smaller, testable pieces.

What’s the one fuzz case you run against your auth routes that caught a real bug (cookie size, invalid JWT, missing headers, double-submit, something else)?

React Native offline-first: conflict rules in SQLite

Sathish — Wed, 25 Mar 2026 15:01:24 +0000

I stopped “last write wins” from nuking offline edits.
I model conflicts with base_rev + rev in SQLite.
I sync with 1 endpoint: pull then push.
I ship deterministic merges for workout sets.

Context

My fitness app has one job.
Log a set in ~5 seconds.
No spinner.
No “retry later”.

Offline-first forced me into SQLite.
AsyncStorage died fast.
I need queries like “last 12 sets for this exercise” in under 100ms.
And I need sync.

I shipped a naive sync first.
It was brutal.
Two devices editing the same workout would randomly overwrite each other.
Sometimes it looked fine.
Then you’d refresh.
Poof.
Half the sets gone.

So I rebuilt it around conflict rules.
Not “hope”.
Rules I can test.
Rules SQLite can enforce.

1) I store revisions per row. Not timestamps.

Timestamps lied to me.
Device clocks drift.
Also, two updates can share the same millisecond.
And I don’t want “latest” anyway.
I want “what was this row based on?”

So every row gets:

rev (server-assigned integer)
base_rev (what the client last saw)
updated_at (still useful for UI)

On create, rev = 0.
Server assigns real rev later.

Here’s the SQLite schema I ended up keeping.
It’s small.
But it fixed the mental model.

-- schema.sql (SQLite)
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS sets (
  id TEXT PRIMARY KEY,              -- uuid
  workout_id TEXT NOT NULL,
  exercise_id TEXT NOT NULL,
  reps INTEGER NOT NULL,
  weight REAL NOT NULL,
  created_at INTEGER NOT NULL,       -- unix ms
  updated_at INTEGER NOT NULL,       -- unix ms

  rev INTEGER NOT NULL DEFAULT 0,    -- server revision, 0 = not on server yet
  base_rev INTEGER NOT NULL DEFAULT 0,
  deleted INTEGER NOT NULL DEFAULT 0,

  CHECK (deleted IN (0, 1))
);

CREATE INDEX IF NOT EXISTS idx_sets_workout
ON sets(workout_id, deleted, updated_at);

CREATE TABLE IF NOT EXISTS outbox (
  id TEXT PRIMARY KEY,              -- uuid for the op
  entity TEXT NOT NULL,             -- 'sets'
  entity_id TEXT NOT NULL,          -- sets.id
  op TEXT NOT NULL,                 -- 'upsert' | 'delete'
  payload TEXT NOT NULL,            -- JSON string
  base_rev INTEGER NOT NULL,
  created_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_outbox_created
ON outbox(created_at);

That base_rev in the outbox is the whole point.
It’s the receipt.
It tells the server what I edited against.

2) Every local write also writes an outbox op

I wanted “offline-first”.
I accidentally built “offline-only” for a day.
Because my UI wrote to SQLite.
But I forgot to queue ops.
Sync ran.
Nothing pushed.
Looked fine locally.
Server stayed stale.

Now I don’t allow a write without an outbox row.
Same transaction.
All-or-nothing.

I’m using Expo’s SQLite.
This code runs.
Copy-paste.
It’s the exact pattern I use for set logging.

// db/upsertSet.ts
import * as SQLite from 'expo-sqlite';
import { randomUUID } from 'expo-crypto';

const db = SQLite.openDatabaseSync('gym.db');

type SetRow = {
  id?: string;
  workout_id: string;
  exercise_id: string;
  reps: number;
  weight: number;
};

export function upsertSetLocal(input: SetRow) {
  const now = Date.now();
  const id = input.id ?? randomUUID();

  db.withTransactionSync(() => {
    // Read current base_rev so the server can detect conflicts.
    const cur = db.getFirstSync<{ rev: number }>(
      'SELECT rev FROM sets WHERE id = ? LIMIT 1',
      [id]
    );
    const baseRev = cur?.rev ?? 0;

    db.runSync(
      `INSERT INTO sets (id, workout_id, exercise_id, reps, weight, created_at, updated_at, rev, base_rev, deleted)
       VALUES (?, ?, ?, ?, ?, ?, ?, COALESCE((SELECT rev FROM sets WHERE id = ?), 0), ?, 0)
       ON CONFLICT(id) DO UPDATE SET
         reps = excluded.reps,
         weight = excluded.weight,
         updated_at = excluded.updated_at,
         base_rev = excluded.base_rev,
         deleted = 0`,
      [
        id,
        input.workout_id,
        input.exercise_id,
        input.reps,
        input.weight,
        now,
        now,
        id,
        baseRev
      ]
    );

    db.runSync(
      `INSERT INTO outbox (id, entity, entity_id, op, payload, base_rev, created_at)
       VALUES (?, 'sets', ?, 'upsert', ?, ?, ?)`,
      [
        randomUUID(),
        id,
        JSON.stringify({ ...input, id, updated_at: now }),
        baseRev,
        now
      ]
    );
  });

  return id;
}

Two things I learned the hard way:
1) If you don’t do this in a transaction, you’ll eventually enqueue an op without the row (or vice versa).
2) Don’t build the payload from the UI state. Build it from what you wrote.

3) Sync is pull-then-push. Always in that order.

I tried push-first.
Bad call.
If you push against an old server state, you create conflicts you didn’t need.

My sync loop is:
1) Pull server changes since last_server_rev.
2) Apply them locally.
3) Push outbox ops.
4) Update last_server_rev.

I store last_server_rev in a tiny meta table.
Could be SecureStore.
I kept it in SQLite so it survives reinstalls with backups.

Here’s the sync function.
It’s trimmed.
But it’s real.

// sync/syncNow.ts
import * as SQLite from 'expo-sqlite';

const db = SQLite.openDatabaseSync('gym.db');

type PullResponse = {
  latestRev: number;
  changes: Array<{
    id: string;
    workout_id: string;
    exercise_id: string;
    reps: number;
    weight: number;
    updated_at: number;
    rev: number;
    deleted: number;
  }>;
};

async function apiPost(path: string, body: unknown): Promise {
  const res = await fetch(`https://api.example.com${path}`, {
    method: 'POST',
    headers: { 'content-type': 'application/json' },
    body: JSON.stringify(body)
  });
  if (!res.ok) throw new Error(`HTTP ${res.status} ${await res.text()}`);
  return res.json() as Promise;
}

export async function syncNow(userId: string) {
  const meta = db.getFirstSync<{ value: string }>(
    'SELECT value FROM meta WHERE key = ? LIMIT 1',
    ['last_server_rev']
  );
  const sinceRev = meta ? Number(meta.value) : 0;

  const pulled = await apiPost('/sync/pull', { userId, sinceRev });

  // Apply server changes.
  db.withTransactionSync(() => {
    for (const c of pulled.changes) {
      db.runSync(
        `INSERT INTO sets (id, workout_id, exercise_id, reps, weight, created_at, updated_at, rev, base_rev, deleted)
         VALUES (?, ?, ?, ?, ?, COALESCE((SELECT created_at FROM sets WHERE id = ?), ?), ?, ?, ?, ?)
         ON CONFLICT(id) DO UPDATE SET
           workout_id = excluded.workout_id,
           exercise_id = excluded.exercise_id,
           reps = excluded.reps,
           weight = excluded.weight,
           updated_at = excluded.updated_at,
           rev = excluded.rev,
           deleted = excluded.deleted`,
        [
          c.id,
          c.workout_id,
          c.exercise_id,
          c.reps,
          c.weight,
          c.id,
          c.updated_at,
          c.updated_at,
          c.rev,
          c.rev,
          c.deleted
        ]
      );
    }

    db.runSync(
      `INSERT INTO meta (key, value)
       VALUES ('last_server_rev', ?)
       ON CONFLICT(key) DO UPDATE SET value = excluded.value`,
      [String(pulled.latestRev)]
    );
  });

  // Push after pull.
  await pushOutbox(userId);
}

async function pushOutbox(userId: string) {
  const ops = db.getAllSync<{ id: string; entity_id: string; op: string; payload: string; base_rev: number }>(
    'SELECT id, entity_id, op, payload, base_rev FROM outbox ORDER BY created_at ASC LIMIT 50'
  );
  if (ops.length === 0) return;

  const res = await apiPost<{ acked: string[]; conflicts: string[] }>('/sync/push', {
    userId,
    ops: ops.map(o => ({ ...o, payload: JSON.parse(o.payload) }))
  });

  db.withTransactionSync(() => {
    for (const ackId of res.acked) {
      db.runSync('DELETE FROM outbox WHERE id = ?', [ackId]);
    }
  });

  // Conflicts handled in the next section.
}

Real bug I hit:
HTTP 413 Payload Too Large.
Because I tried to push 2,000 ops in one request.
Now it’s LIMIT 50.
Boring.
Works.

4) Conflicts: I don’t “merge” everything. I pick winners.

I wanted fancy merges.
Like Git.
For workout sets.
Why.

Sets are append-heavy.
Conflicts mostly happen on edits.
Like changing weight from 185 to 195.
If two devices edit the same set, I don’t want an average.
I want a deterministic rule.

My rule:

If the server row rev equals the op’s base_rev, accept.
Else, conflict.
For conflicts, keep the server version.
But keep the client edit as a new set row (duplicate) with a note.

That last part sounds weird.
It is.
But it avoids silent data loss.
And it’s visible in the UI.
I’d rather show “(from offline edit)” than delete someone’s work.

This is the server-side SQL check.
Postgres in my case.
But the logic is universal.

-- Server-side (Postgres) apply op with optimistic concurrency
-- Table: sets(id pk, reps, weight, rev int, deleted bool, updated_at bigint)

-- Given: :id, :reps, :weight, :base_rev

UPDATE sets
SET reps = :reps,
    weight = :weight,
    updated_at = :updated_at,
    rev = rev + 1,
    deleted = false
WHERE id = :id
  AND rev = :base_rev
  AND deleted = false;

-- If rowCount = 0 => conflict (someone else changed it)

And here’s how I handle the conflict on the client.
This part took me 4 hours.
Most of it was wrong.
I kept trying to “auto-resolve”.
It just made it harder to reason about.

// sync/handleConflicts.ts
import * as SQLite from 'expo-sqlite';
import { randomUUID } from 'expo-crypto';

const db = SQLite.openDatabaseSync('gym.db');

export function materializeConflictAsNewSet(entityId: string) {
  const row = db.getFirstSync(
    'SELECT * FROM sets WHERE id = ? LIMIT 1',
    [entityId]
  );
  if (!row) return;

  const now = Date.now();
  const newId = randomUUID();

  db.withTransactionSync(() => {
    // Keep server row as-is. Create a new local row with the client's values.
    // rev=0 so it will be treated as a new create on next sync.
    db.runSync(
      `INSERT INTO sets (id, workout_id, exercise_id, reps, weight, created_at, updated_at, rev, base_rev, deleted)
       VALUES (?, ?, ?, ?, ?, ?, ?, 0, 0, 0)`,
      [
        newId,
        row.workout_id,
        row.exercise_id,
        row.reps,
        row.weight,
        now,
        now
      ]
    );

    db.runSync(
      `INSERT INTO outbox (id, entity, entity_id, op, payload, base_rev, created_at)
       VALUES (?, 'sets', ?, 'upsert', ?, 0, ?)`,
      [
        randomUUID(),
        newId,
        JSON.stringify({
          id: newId,
          workout_id: row.workout_id,
          exercise_id: row.exercise_id,
          reps: row.reps,
          weight: row.weight,
          updated_at: now,
          note: 'conflict_copy'
        }),
        now
      ]
    );
  });
}

Is it perfect?
No.
But it’s honest.
No silent overwrite.
No “maybe it synced”.

And the UI stays fast.
Because conflicts are rare.
And when they happen, I can show a banner.

Results

Before this, I could reproduce data loss in 6 steps.
Two devices.
Airplane mode.
Edit the same set.
Sync both.
One edit vanished.
Every time.

After the revision + base revision change, I ran 30 conflict simulations.
15 “same set edited on both devices”.
15 “delete on one, edit on the other”.
I got 30 deterministic outcomes.
Zero silent overwrites.

Perf stayed fine.
On a 3-year-old Android device, upsertSetLocal() took 4–12ms per log in my profiling.
The UI stayed under my 100ms budget.

Key takeaways

Don’t use timestamps for conflict resolution. Use integer revisions.
Every local mutation needs an outbox op in the same transaction.
Sync order matters: pull first, then push.
“Server wins” is acceptable if you preserve the client edit visibly.
Keep your rules testable. If you can’t write a 6-step repro, you’re guessing.

Closing

I’m still not happy with the conflict UI.
It’s functional.
It’s not pretty.

If you’ve shipped offline-first sync in React Native: when you hit a conflict, do you surface a “choose version” screen, or do you do deterministic rules like I’m doing here?

Next.js job board search: Postgres FTS + trgm

Sathish — Tue, 24 Mar 2026 15:02:10 +0000

My job board has 8,000+ listings. Basic ILIKE died fast.
I combined Postgres full-text search with pg_trgm for typos.
I rank results with a single SQL function. No app-side scoring.
I ship it via Supabase RPC + a simple Next.js route.

Context

I’m building a job board for Psychiatric Mental Health Nurse Practitioners.

Search is the whole product.
If search feels dumb, users bounce.

I started with ILIKE '%term%'.
It worked for 200 listings.
Then I crossed ~8,000 active listings and it got brutal.
Slow queries. Weird ranking. Typos returning nothing.

I didn’t want Elasticsearch.
Not for this.
I already have Postgres (Supabase). So I leaned into it.

Goal: fast search, decent ranking, typo tolerance.
And something I can maintain solo.

1) I stopped doing `ILIKE`. It’s a trap.

ILIKE is fine for admin screens.
Not for user-facing search.

Two problems hit me.

First: performance.
ILIKE '%remote%' can’t use a normal btree index.
It scans.

Second: ranking.
Everything matches the same.
A job with “Remote” once beats a job with “Remote PMHNP” everywhere.

So I moved to two signals:

Full-text search for “real” matching.
Trigram similarity for typos and partials.

Here’s the schema change I actually run.
Generated columns. Indexed. Boring. Reliable.

-- 1) Extensions for search
create extension if not exists pg_trgm;

-- (optional but common)
create extension if not exists unaccent;

-- 2) Columns used by search
alter table public.jobs
  add column if not exists search_tsv tsvector
  generated always as (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(company_name, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(location, '')), 'C') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'D')
  ) stored;

-- 3) Indexes
create index if not exists jobs_search_tsv_gin
  on public.jobs using gin (search_tsv);

create index if not exists jobs_title_trgm_gin
  on public.jobs using gin (title gin_trgm_ops);

create index if not exists jobs_company_trgm_gin
  on public.jobs using gin (company_name gin_trgm_ops);

One thing that bit me — generated columns are only as good as your inputs.
I had a scraper bug that wrote empty company_name for 312 rows.
Search looked “random” until I fixed data quality.
Not glamorous.

2) I rank with one SQL function. No client math.

I want one call.
Query in. Rows out.

Also: consistent ranking.
If I score in TypeScript, I’ll change it three times and forget why.
SQL keeps me honest.

This is the function.
It returns a score.
It blends:

ts_rank_cd for FTS ranking.
similarity() for typo-ish matching.

And I clamp it.
Because trigram scores can dominate when the query is short.
“ny” matched half the planet.

create or replace function public.search_jobs(
  q text,
  limit_count int default 25,
  offset_count int default 0
)
returns table (
  id uuid,
  title text,
  company_name text,
  location text,
  url text,
  posted_at timestamptz,
  score real
)
language sql
stable
as $$
  with params as (
    select
      websearch_to_tsquery('english', q) as tsq,
      greatest(length(trim(q)), 0) as qlen
  )
  select
    j.id,
    j.title,
    j.company_name,
    j.location,
    j.url,
    j.posted_at,
    (
      -- full-text score
      (ts_rank_cd(j.search_tsv, p.tsq) * 1.0)
      +
      -- trigram score (down-weighted for short queries)
      (
        least(
          greatest(similarity(j.title, q), similarity(j.company_name, q)),
          0.60
        )
        * case when p.qlen >= 5 then 0.6 else 0.2 end
      )
    )::real as score
  from public.jobs j
  cross join params p
  where
    -- must match either full-text or be "close enough"
    (j.search_tsv @@ p.tsq)
    or (similarity(j.title, q) > 0.25)
    or (similarity(j.company_name, q) > 0.25)
  order by score desc, j.posted_at desc
  limit limit_count
  offset offset_count;
$$;

Yes, those numbers are arbitrary.
I spent 4 hours tuning them.
Most of it was wrong.

What finally worked was testing with real searches:

“pmhnp remote”
“psych np telehealth”
“kaiser pmhnp”
“new yrok” (typo)

If you don’t test typos, you’re lying to yourself.
Users type like raccoons.

3) I call it via Supabase RPC. Clean boundary.

I don’t want raw SQL in my Next.js server code.
Not because it’s “bad”.
Because I’ll copy/paste it into 3 routes.
Then I’ll forget one.

RPC keeps it centralized.
So my app just does:

take query
call function
return JSON

This is the route handler.
App Router.
Server-only.

// app/api/search/route.ts
import { NextResponse } from "next/server";
import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // server-only
);

export async function GET(req: Request) {
  const { searchParams } = new URL(req.url);

  const q = (searchParams.get("q") ?? "").trim();
  const limit = Math.min(Number(searchParams.get("limit") ?? 25), 50);
  const offset = Math.max(Number(searchParams.get("offset") ?? 0), 0);

  if (q.length < 2) {
    return NextResponse.json({ items: [], count: 0 });
  }

  const { data, error } = await supabase.rpc("search_jobs", {
    q,
    limit_count: limit,
    offset_count: offset,
  });

  if (error) {
    // I’ve seen: "canceling statement due to statement timeout"
    return NextResponse.json({ error: error.message }, { status: 500 });
  }

  return NextResponse.json({ items: data ?? [], count: data?.length ?? 0 });
}

Service role key is touchy.
Don’t leak it.
I keep this route server-side only.
No client components calling Supabase directly for search.

Also.
I set a statement timeout on Supabase.
Because one runaway query can eat your day.

4) I debug search with `EXPLAIN ANALYZE`. Every time.

Search “feels” slow.
Until you measure it.

I run the exact query shape.
Then I read the plan.
Not the vibes.

Here’s the kind of thing I run in the SQL editor.
Same function logic, simplified.

explain (analyze, buffers)
select id, title
from public.jobs
where
  search_tsv @@ websearch_to_tsquery('english', 'pmhnp remote')
  or similarity(title, 'pmhnp remote') > 0.25
order by
  (ts_rank_cd(search_tsv, websearch_to_tsquery('english', 'pmhnp remote'))
   + least(similarity(title, 'pmhnp remote'), 0.60) * 0.6) desc
limit 25;

If you don’t see your GIN indexes used, stop.
Fix that first.

One dumb mistake I made.
I originally used to_tsquery().
It blew up on user input like c++ or remote (west).
Error was:

ERROR: syntax error in tsquery: "remote (west)"

websearch_to_tsquery() fixed it.
It handles normal human text.
That’s what I needed.

Results

Before: ILIKE search regularly spiked to 900–1400ms on my Vercel API route when the query was common (like “remote”). It also returned “meh” ordering. Typos like “psychiatrc” returned zero rows.

After: Postgres FTS + trigram search stays under ~120–220ms for my top queries, returning 25 rows with stable ranking. Typos now return usable results (usually 10–25 rows) instead of nothing. On the data side, this setup handles my current volume: 8,000+ active listings across 2,000+ companies, with 200+ new jobs scraped daily.

Key takeaways

Drop ILIKE '%term%' for user search. It doesn’t scale.
Store a weighted tsvector column and GIN-index it. Don’t compute it per query.
Use websearch_to_tsquery() for real user input. to_tsquery() will betray you.
Add trigram similarity for typos, but cap and down-weight it for short queries.
Keep scoring in SQL (RPC). Your Next.js code stays tiny.

Closing

I’m still not fully happy with ranking for super-short queries like “ny” and “la”. It matches too much, even with down-weighting.

How are you handling 2–3 character queries in Postgres search—hard minimum length, prefix-only, or a separate “location autocomplete” table?

Cursor Rules + Claude: stop breaking builds

Sathish — Thu, 19 Mar 2026 15:01:14 +0000

I use Cursor Rules to make Claude follow my stack.
I add a tiny “done = tests pass” contract.
I lint AI output hard. Automatically.
I keep prompts short. Diff-first.

Context

I build small SaaS projects. Usually solo. Usually fast.

Cursor + Claude makes me fast. It also makes me sloppy.

The failure mode is always the same. Claude writes code that looks right. It compiles. Then it breaks edge cases, ignores my existing patterns, or sneaks in a dependency I didn’t want.

I spent 4 hours on this once. Most of it was wrong. Not because Claude was “bad”. Because my workflow was vibes. No guardrails.

So I started treating AI like a junior dev with a very specific contract. Same stack. Same conventions. Same “definition of done”. And I automated the policing.

1) I write Cursor Rules first. Not prompts.

Prompts rot. Fast.

Rules don’t. They sit there. Every request gets them.

In Cursor, I keep a project rule file that forces Claude into my existing choices: Next.js App Router, TypeScript strict, no random state libs, prefer server actions, etc.

This is the exact kind of rules file I use (trimmed, but real). Drop it in .cursor/rules.md.

# .cursor/rules.md

You are coding in a Next.js 15 + TypeScript repo.

Hard rules:
- TypeScript only. No JavaScript files.
- No new dependencies unless I ask.
- Prefer built-in Web APIs over libraries.
- Keep functions small. 30 lines max.
- Every change must include: types, error handling, and tests if logic changed.

Next.js rules:
- App Router only.
- Prefer Server Components.
- Client Components only when needed (use 'use client').
- Prefer Server Actions for mutations.

Output rules:
- Show a diff-style plan first (bullets).
- Then edit minimal files.
- Don’t rewrite unrelated code.

One thing that bit me — if rules are too long, Claude starts “agreeing” and then doing whatever.

So I keep it short. Sharp edges only.

2) I force a “definition of done”: tests + typecheck

Claude will happily say “done” after writing code that doesn’t typecheck.

I stopped trusting completions. I only trust green commands.

In every repo I add a check script that matches what CI should do. No creativity.

{
  "scripts": {
    "lint": "next lint",
    "typecheck": "tsc --noEmit",
    "test": "vitest run",
    "check": "pnpm lint && pnpm typecheck && pnpm test"
  }
}

Then I bake this into my prompts.

Not a wall of text. Just a contract.

Make the minimal diff. pnpm check must pass.

If pnpm check fails, I paste the exact error back.

No paraphrasing. No “it’s failing somewhere”. I paste the stack trace like a robot.

That single habit cut my back-and-forth in half.

3) I run a “no new deps” tripwire

The sneakiest AI failure isn’t wrong code.

It’s new dependencies.

You ask for a tiny feature. You get zod, lodash, and some date library because Claude saw it in training data.

I added a tiny script that fails if package.json changed and I didn’t explicitly allow it.

This runs in pre-commit for me. You can also run it manually.

// scripts/no-new-deps.ts
import { readFileSync } from "node:fs";
import { createHash } from "node:crypto";

function sha256(s: string) {
  return createHash("sha256").update(s).digest("hex");
}

const pkg = readFileSync("package.json", "utf8");
const lock = readFileSync("pnpm-lock.yaml", "utf8");

// Commit the baseline hashes once. Update only when you *intend* to.
const BASE_PKG = process.env.BASE_PKG_HASH;
const BASE_LOCK = process.env.BASE_LOCK_HASH;

if (!BASE_PKG || !BASE_LOCK) {
  console.error("Missing BASE_PKG_HASH/BASE_LOCK_HASH env vars");
  process.exit(2);
}

const pkgHash = sha256(pkg);
const lockHash = sha256(lock);

if (pkgHash !== BASE_PKG || lockHash !== BASE_LOCK) {
  console.error("Dependency files changed. Did you mean to add/update deps?");
  console.error({ pkgHash, lockHash });
  process.exit(1);
}

console.log("OK: no dependency changes detected");

Yeah, it’s blunt.

But it matches how I work: I want dependencies to be a conscious decision.

To set the baseline once:

node -e 'const fs=require("fs"),c=require("crypto");
const h=s=>c.createHash("sha256").update(s).digest("hex");
console.log("BASE_PKG_HASH="+h(fs.readFileSync("package.json","utf8")));
console.log("BASE_LOCK_HASH="+h(fs.readFileSync("pnpm-lock.yaml","utf8")));'

Then store those in your shell env or .env.local for local use.

When I do want a dependency bump, I update the baseline and move on.

4) I make Claude work diff-first (and I reject big diffs)

My worst AI merges were big.

Not “big feature big diff”. More like: asked for a small refactor, got a rewrite.

Now I force a diff-first flow:

Claude lists the files it’ll touch.
Claude explains why each file changes.
I approve.
Only then it edits.

If it can’t describe the changes in 5 bullets, it’s about to go off-road.

Also: I cap file touches.

“Fix this bug. Touch max 2 files.”

It sounds restrictive. It’s not.

It keeps the model focused. And it keeps me reviewing.

Here’s a pattern I use a lot in Next.js when Claude wants to sprawl: isolate logic into a single module and test it.

Example: I’ll move tricky parsing/validation into lib/ and write a Vitest spec. Claude stays inside that box.

// lib/parsePagination.ts
export type Pagination = { page: number; pageSize: number };

export function parsePagination(input: Record): Pagination {
  const pageRaw = Array.isArray(input.page) ? input.page[0] : input.page;
  const sizeRaw = Array.isArray(input.pageSize) ? input.pageSize[0] : input.pageSize;

  const page = Math.max(1, Number(pageRaw ?? 1) || 1);
  const pageSize = Math.min(100, Math.max(1, Number(sizeRaw ?? 20) || 20));

  return { page, pageSize };
}

Test it. Lock behavior.

// lib/parsePagination.test.ts
import { describe, expect, it } from "vitest";
import { parsePagination } from "./parsePagination";

describe("parsePagination", () => {
  it("defaults safely", () => {
    expect(parsePagination({})).toEqual({ page: 1, pageSize: 20 });
  });

  it("coerces and clamps", () => {
    expect(parsePagination({ page: "0", pageSize: "1000" })).toEqual({ page: 1, pageSize: 100 });
    expect(parsePagination({ page: "-2", pageSize: "-5" })).toEqual({ page: 1, pageSize: 1 });
  });

  it("handles arrays from querystring", () => {
    expect(parsePagination({ page: ["2"], pageSize: ["10"] })).toEqual({ page: 2, pageSize: 10 });
  });
});

Now when Claude “improves” stuff, it has to respect the tests.

And I can review a 20-line diff instead of a 400-line rewrite.

5) I keep a failure log. One line per screw-up.

This is the unsexy part.

But it’s the part that compounds.

Whenever AI causes a bug or wastes time, I add one line to a local NOTES.md.

Stuff like:

“Claude used cookies() in a Client Component. Build failed with: Error: cookies was called outside a request scope.”
“It returned 200 with empty JSON on errors. Caused silent UI failures.”
“It changed a Prisma query shape and broke serialization.”

Then I convert the common ones into rules.

Rules are just my scars, written down.

Results

This workflow reduced my rework time a lot.

On my last build week, I tracked 23 AI-assisted coding sessions in a scratchpad. 17 ended with pnpm check passing on the first try. Before rules + contracts, that number was 6 out of 20 sessions on a similar codebase.

I also caught 4 unwanted dependency changes with the tripwire script. All four were unnecessary.

The biggest win wasn’t speed. It was fewer “what did it even change?” moments.

Key takeaways

Put your stack decisions in Cursor Rules. Keep it short.
Define “done” as lint + typecheck + tests. Nothing else counts.
Paste exact errors back to Claude. Don’t translate them.
Reject big diffs for small requests. Force diff-first planning.
When AI burns you, write it down. Turn it into a rule.

Closing

I’m still using Claude constantly. I just don’t let it drive unsupervised anymore.

If you already use Cursor Rules: what’s the one rule that saved you the most time (the exact line), and what bug did it prevent?

React Native SQLite: 0ms UI with debounced writes

Sathish — Wed, 18 Mar 2026 15:01:15 +0000

I stopped blocking my UI on SQLite writes.
I batch writes in-memory, then flush every 250ms.
I still guarantee durability on app background.
Copy-paste hook + SQLite schema + flush logic inside.

Context

My fitness app logs sets fast. Like “tap, tap, done”. I timed myself. 5 seconds per set entry is the bar.

Offline-first means SQLite. Not AsyncStorage. I need queries like “last 3 workouts for chest” and “total volume by week”. SQLite wins.

But I hit a brutal issue. I was doing a DB write on every tap. Reps change? Write. Weight change? Write. Mark set done? Write.

On a warm device it felt fine. On a slightly busy device it stuttered. The UI thread wasn’t “blocked” technically, but React state updates were waiting behind JS work + bridge work + DB calls. End result: dropped frames.

So I changed the rule.

UI state updates are immediate.
DB writes are delayed and batched.
And I still don’t lose sets.

1) I measured the stutter. Then I got mad.

I started with numbers. Not vibes.

I added a little timer around my “save set” function. I also watched the JS FPS in the RN dev menu.

The ugly part: my save path did multiple awaits. Some were fast. Some weren’t. Worst case was 40–80ms per tap. That’s enough to feel sticky when you’re tapping fast.

Here’s the kind of write I was doing. It’s not “wrong”. It’s just too eager.

// setRepo.ts
import * as SQLite from "expo-sqlite";

const db = SQLite.openDatabaseSync("gym.db");

export type SetRow = {
  id: string;
  workout_id: string;
  exercise_id: string;
  set_index: number;
  weight_kg: number;
  reps: number;
  is_done: 0 | 1;
  updated_at: number;
};

export async function upsertSet(row: SetRow) {
  const t0 = globalThis.performance.now();

  db.runSync(
    `INSERT INTO sets (id, workout_id, exercise_id, set_index, weight_kg, reps, is_done, updated_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)
     ON CONFLICT(id) DO UPDATE SET
       weight_kg=excluded.weight_kg,
       reps=excluded.reps,
       is_done=excluded.is_done,
       updated_at=excluded.updated_at`,
    [
      row.id,
      row.workout_id,
      row.exercise_id,
      row.set_index,
      row.weight_kg,
      row.reps,
      row.is_done,
      row.updated_at,
    ]
  );

  const ms = globalThis.performance.now() - t0;
  if (ms > 16) console.log(`[db] upsertSet took ${ms.toFixed(1)}ms`);
}

My mistake: calling this on every tap.

I spent 4 hours trying to “optimize SQL”. Most of it was wrong.
The fix was architectural.

2) I made the UI the source of truth

I stopped treating SQLite as the live state.

Now my UI state updates immediately. I collect pending changes in memory. Then I flush to SQLite on a timer.

That does two things.

First, the user never waits for a DB call.
Second, I get batching for free.

This is the hook I ended up with. It’s small. It’s boring. It works.

// useDebouncedSQLiteWriter.ts
import { useEffect, useMemo, useRef } from "react";
import * as SQLite from "expo-sqlite";
import { AppState } from "react-native";

type Job = { sql: string; args: SQLite.SQLiteBindValue[] };

export function useDebouncedSQLiteWriter(opts?: {
  flushEveryMs?: number;
  maxQueue?: number;
}) {
  const flushEveryMs = opts?.flushEveryMs ?? 250;
  const maxQueue = opts?.maxQueue ?? 500;

  const db = useMemo(() => SQLite.openDatabaseSync("gym.db"), []);
  const queueRef = useRef([]);
  const flushingRef = useRef(false);

  const enqueue = (sql: string, args: SQLite.SQLiteBindValue[] = []) => {
    const q = queueRef.current;
    q.push({ sql, args });

    // If someone goes wild tapping, don't grow forever.
    if (q.length > maxQueue) q.splice(0, q.length - maxQueue);
  };

  const flush = () => {
    if (flushingRef.current) return;
    const jobs = queueRef.current.splice(0);
    if (!jobs.length) return;

    flushingRef.current = true;
    try {
      db.withTransactionSync(() => {
        for (const j of jobs) db.runSync(j.sql, j.args);
      });
    } finally {
      flushingRef.current = false;
    }
  };

  useEffect(() => {
    const id = setInterval(flush, flushEveryMs);

    // Durability: if the app backgrounds, flush right now.
    const sub = AppState.addEventListener("change", (s) => {
      if (s !== "active") flush();
    });

    return () => {
      clearInterval(id);
      sub.remove();
      flush();
    };
  }, [flushEveryMs]);

  return { enqueue, flush };
}

Two details mattered.

withTransactionSync. That’s the batching.
And AppState flush on background. Because iOS will happily kill you after you swipe away.

If you skip that background flush, you’ll lose the last few taps. Ask me how I know.

3) I made writes idempotent. Offline-first demands it.

Debouncing is great until you crash mid-flight.

So I treat every write as “replayable”. If the same set update gets enqueued twice, it shouldn’t corrupt anything.

That means:

stable IDs (UUID for each set)
ON CONFLICT(id) DO UPDATE
updated_at stored as epoch ms

Here’s the schema I’m using for sets. It’s not fancy. It’s fast.

-- schema.sql
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

CREATE TABLE IF NOT EXISTS sets (
  id TEXT PRIMARY KEY,
  workout_id TEXT NOT NULL,
  exercise_id TEXT NOT NULL,
  set_index INTEGER NOT NULL,
  weight_kg REAL NOT NULL,
  reps INTEGER NOT NULL,
  is_done INTEGER NOT NULL DEFAULT 0,
  updated_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_sets_workout
  ON sets(workout_id, set_index);

CREATE INDEX IF NOT EXISTS idx_sets_exercise
  ON sets(exercise_id, updated_at);

WAL mode helped. Noticeably.

And yeah, synchronous = NORMAL is a tradeoff. I’m fine with it for a workout log. If someone’s phone hard-crashes mid-set, the last tap might go missing. My background flush reduces that a lot.

4) I wired it into the “tap to log” UI

This is the part that made it feel instant.

The UI updates local state first. Then it schedules a write.

I’m using a reducer for the workout screen. But the pattern works with useState too.

// SetRow.tsx
import React from "react";
import { Pressable, Text, View } from "react-native";
import { useDebouncedSQLiteWriter } from "./useDebouncedSQLiteWriter";

type SetModel = {
  id: string;
  workoutId: string;
  exerciseId: string;
  setIndex: number;
  weightKg: number;
  reps: number;
  isDone: boolean;
};

export function SetRow({ set, onChange }: { set: SetModel; onChange: (next: SetModel) => void }) {
  const { enqueue } = useDebouncedSQLiteWriter({ flushEveryMs: 250 });

  const persist = (next: SetModel) => {
    enqueue(
      `INSERT INTO sets (id, workout_id, exercise_id, set_index, weight_kg, reps, is_done, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)
       ON CONFLICT(id) DO UPDATE SET
         weight_kg=excluded.weight_kg,
         reps=excluded.reps,
         is_done=excluded.is_done,
         updated_at=excluded.updated_at`,
      [
        next.id,
        next.workoutId,
        next.exerciseId,
        next.setIndex,
        next.weightKg,
        next.reps,
        next.isDone ? 1 : 0,
        Date.now(),
      ]
    );
  };

  return (

      {set.setIndex + 1}

       {
          const next = { ...set, reps: set.reps + 1 };
          onChange(next); // UI first
          persist(next); // DB later
        }}
        style={{ padding: 10, backgroundColor: "#222" }}
      >
        +rep


       {
          const next = { ...set, isDone: !set.isDone };
          onChange(next);
          persist(next);
        }}
        style={{ padding: 10, backgroundColor: set.isDone ? "#0a0" : "#444" }}
      >
        {set.isDone ? "done" : "mark"}


  );
}

The important part is psychological.

Taps feel immediate.
Then the DB catches up.

One thing that bit me — don’t mount this hook per row like I did first. That created multiple intervals. Dumb.

In my app, I mount useDebouncedSQLiteWriter once at the screen level and pass { enqueue } down via props/context.

If you copy-paste this, do that.

Results

Before this change, my workout screen would stutter if I tapped fast. I could reliably reproduce it by hammering “+rep” 20 times in a row. I saw 30–80ms logs from upsertSet and the UI felt sticky.

After switching to debounced writes, the tap handler does zero awaited work. My UI stays smooth even during rapid taps. The DB now flushes at most 4 times per second, and each flush typically batches 6–25 statements in one transaction.

I also stopped losing “last tap” data when backgrounding, because I flush on AppState changes.

Key takeaways

Don’t write to SQLite on every tap. Batch it.
withTransactionSync is the difference between “fine” and “fast”.
Make writes idempotent (ON CONFLICT DO UPDATE) so retries don’t hurt.
Always flush on app background/unmount, or you’ll lose the last few edits.
Mount one writer per screen, not per component row.

Closing

If you’re doing offline-first in React Native: do you debounce writes like this, or do you go full event-sourcing (append-only table + compaction) for durability?

Next.js 14 cron scraping: rate limits + retries

Sathish — Tue, 17 Mar 2026 15:01:48 +0000

Run a daily scraper on Vercel. Without melting sources.
Enforce per-host rate limits in Node. Not “sleep(1000)”.
Make retries idempotent with Postgres locks.
Store failures so I can re-run only the broken ones.

Context

I’m building a job board for Psychiatric Mental Health Nurse Practitioners.
8,000+ active listings. 2,000+ companies.

The pipeline scrapes 200+ jobs daily from multiple sources.
Some are nice JSON feeds. Most aren’t.

My first version was dumb.
One cron. One loop. Fetch everything.

It worked. Until it didn’t.
429s. Random 403s. Timeouts.
Worse — half a run would succeed, then retries would duplicate work and waste time.

This post is how I stabilized it.
Rate limiting by host. Jitter. Backoff.
And a Postgres lock so reruns don’t stomp each other.

1) I stopped using “one cron to rule them all”

I used to do this:
“Cron hits /api/scrape and that endpoint scrapes everything.”

Brutal.
One slow host makes the whole run slow.
And Vercel timeouts become your scheduler.

Now I split it.
One cron endpoint schedules work items.
Then another endpoint processes one source at a time.

That gives me:

better observability
cheap retries
isolation when a source is flaky

Here’s the scheduler route.
Next.js 14 App Router. Runs on Vercel.

// app/api/cron/schedule/route.ts
import { NextResponse } from "next/server";
import { createClient } from "@supabase/supabase-js";

export const runtime = "nodejs";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

export async function GET(req: Request) {
  // Simple auth. Don't leave cron endpoints open.
  const token = new URL(req.url).searchParams.get("token");
  if (token !== process.env.CRON_TOKEN) {
    return NextResponse.json({ error: "unauthorized" }, { status: 401 });
  }

  // Keep it explicit. I only schedule enabled sources.
  const { data: sources, error } = await supabase
    .from("scrape_sources")
    .select("id")
    .eq("enabled", true);

  if (error) {
    return NextResponse.json({ error: error.message }, { status: 500 });
  }

  // One job per source.
  const jobs = sources!.map((s) => ({
    source_id: s.id,
    status: "queued" as const,
  }));

  const { error: insertErr } = await supabase.from("scrape_runs").insert(jobs);
  if (insertErr) {
    return NextResponse.json({ error: insertErr.message }, { status: 500 });
  }

  return NextResponse.json({ scheduled: jobs.length });
}

That table is small.
One row per source per run.
I don’t schedule “200 jobs”. I schedule “N sources”.
N is stable.

2) I rate-limit per host, not per run

I wasted 4 hours here.
Most of it was wrong.

I first added await new Promise(r => setTimeout(r, 500)).
It felt responsible.
It wasn’t.

Different hosts have different thresholds.
Also, parallelism matters.
Two concurrent requests to the same host can trip a limit even if each request is “slow enough”.

So I built a tiny per-host limiter.
In-memory.
Works fine because this is one worker invocation doing one source.

// lib/rateLimit.ts
const nextAllowedAt = new Map();

function sleep(ms: number) {
  return new Promise((r) => setTimeout(r, ms));
}

export async function perHostLimit(host: string, minIntervalMs: number) {
  const now = Date.now();
  const allowed = nextAllowedAt.get(host) ?? 0;

  const waitMs = Math.max(0, allowed - now);
  if (waitMs > 0) await sleep(waitMs);

  // Add jitter so multiple invocations don't sync up perfectly.
  const jitter = Math.floor(Math.random() * 250);
  nextAllowedAt.set(host, Date.now() + minIntervalMs + jitter);
}

Then I wrap fetches.
Host comes from the URL.

// lib/http.ts
import { perHostLimit } from "./rateLimit";

export async function limitedFetch(url: string, init?: RequestInit) {
  const host = new URL(url).host;

  // Default: 1 request / 1200ms per host.
  // I override per source when needed.
  await perHostLimit(host, 1200);

  const res = await fetch(url, {
    ...init,
    headers: {
      "user-agent": "pmhnp-job-scraper/1.0",
      ...(init?.headers ?? {}),
    },
  });

  return res;
}

This alone cut down 429s a lot.
Not to zero.
But enough that retries became rare.

3) Retries: I only retry the stuff that’s retryable

My earlier retry logic was “retry everything 3 times.”
That’s how you get banned.

Now I classify.
429 and 503? Retry.
403? Stop. Probably blocked.
404? Stop. Probably removed.

Also.
I log every failure in Postgres.
Because “I’ll check logs later” is a lie.

// lib/retry.ts
function sleep(ms: number) {
  return new Promise((r) => setTimeout(r, ms));
}

export async function fetchWithBackoff(
  fn: () => Promise,
  opts: { maxAttempts: number }
) {
  let attempt = 0;

  while (true) {
    attempt++;
    const res = await fn();

    if (res.ok) return res;

    const retryable = res.status === 429 || res.status === 503 || res.status === 502;
    if (!retryable || attempt >= opts.maxAttempts) return res;

    // Exponential backoff + jitter.
    const base = 500 * Math.pow(2, attempt - 1);
    const jitter = Math.floor(Math.random() * 300);
    await sleep(base + jitter);
  }
}

Then in the scraper.
I record failures with the exact status.
That’s what I filter on when re-running.

I learned the hard way that retrying 403s is pointless.
It just looks like a bot hammering a wall.
Because it is.

4) Idempotency: I use a Postgres advisory lock

This one saved me.

Vercel cron can overlap.
Deploys happen.
Manual re-runs happen.
And I don’t want two invocations scraping the same source.

I use pg_try_advisory_lock.
It’s a single SQL statement.
No extra tables.
The lock auto-releases when the DB connection closes.

Since I’m on Supabase Postgres, I call it through rpc().
So I created two functions.
One to acquire. One to release.

-- supabase/migrations/20260317_advisory_locks.sql
create or replace function public.try_lock_source(p_source_id bigint)
returns boolean
language sql
as $$
  select pg_try_advisory_lock(p_source_id);
$$;

create or replace function public.unlock_source(p_source_id bigint)
returns boolean
language sql
as $$
  select pg_advisory_unlock(p_source_id);
$$;

Then the worker route grabs the lock.
If it can’t, it exits cleanly.
No drama.

// app/api/cron/run-source/route.ts
import { NextResponse } from "next/server";
import { createClient } from "@supabase/supabase-js";

export const runtime = "nodejs";

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

export async function POST(req: Request) {
  const { sourceId } = (await req.json()) as { sourceId: number };

  const { data: locked, error: lockErr } = await supabase
    .rpc("try_lock_source", { p_source_id: sourceId });

  if (lockErr) {
    return NextResponse.json({ error: lockErr.message }, { status: 500 });
  }

  if (!locked) {
    // Another run is already processing this source.
    return NextResponse.json({ skipped: true, reason: "locked" });
  }

  try {
    // Do the scrape. Insert jobs. Update scrape_runs.
    // (I keep that code in a separate module.)
    return NextResponse.json({ ok: true });
  } finally {
    await supabase.rpc("unlock_source", { p_source_id: sourceId });
  }
}

This is boring.
That’s why it’s good.

One thing that bit me — advisory locks are per DB session.
If you’re using a pooler that multiplexes sessions, behavior can get weird.
On Supabase, I run this through the normal Postgres connection behind the API.
It’s been consistent for me.

5) I store a “run log” so I can replay failures

When scraping breaks, it breaks in patterns.
One host changes HTML.
One API starts returning 503 for 20 minutes.

If I don’t store run outcomes, I end up re-scraping everything.
That’s noisy.
And it increases dedupe pressure downstream.

So I store:

source_id
status (queued | running | success | failed)
http_status
error_message
timestamps

Then my “re-run” cron just selects failures.
No guessing.

I’m not showing the whole schema here.
But the behavior is simple:
query scrape_runs where status = 'failed' and created_at > now() - interval '24 hours'.
Requeue those.

It’s not glamorous.
It’s the difference between a stable system and a nightly gamble.

Results

Before these changes, my daily scrape had frequent partial failures.
On an average day I’d see 18–35 failed fetches across sources, mostly 429 and 503.
Rerunning meant scraping everything again, which made rate limiting worse.

After splitting scheduling + per-source runs, adding per-host throttling, and using advisory locks, failures dropped to 2–6 per day.
Those are usually real issues now (HTML changed, endpoint removed).
The job board stays at 8,000+ active listings, and I keep adding ~200 new jobs daily without the pipeline flaking out.

Key takeaways

Don’t run “one cron endpoint” for everything. Schedule units of work.
Rate-limit by host. Not by loop iterations.
Retry only 429/502/503. Stop on 403/404.
Use Postgres advisory locks to prevent overlapping work.
Persist failures in a table so reruns are targeted, not noisy.

Closing

If you’re scraping on Vercel, overlap happens.
Even when you swear it won’t.

Do you use Postgres advisory locks for cron idempotency, or do you prefer a queue (like pg-boss / BullMQ) even for small scrape workloads?

Cursor + Claude: my vibe-coding safety net

Sathish — Thu, 12 Mar 2026 15:01:38 +0000

I don’t trust AI edits unless tests + types pass.
I keep a tiny prompt template that forces diffs.
I use scripts to catch “works on my machine” bugs.
You’ll get 4 copy-paste snippets for Next.js.

Context

I build small SaaS projects. Usually solo. Usually fast.

Cursor + Claude makes me faster. Also makes me sloppy.

My failure mode is consistent. I accept a “clean” refactor. It compiles. Then a week later I find a broken edge case in prod logs. Brutal.

So I stopped treating AI like a pair programmer. I treat it like a code generator that must go through gates.

This post is the exact safety net I use. Not theory. It’s the stuff that saved me after spending 4 hours on a “simple” refactor where most of it was wrong.

1) I start with a diff-only prompt. No essays.

Cursor chats love to explain. Explanations don’t ship.

I want a patch. Or nothing.

So I keep a prompt template in a file. Then I paste it into Cursor chat. Every time.

It forces: constraints, file list, and a diff output.

# prompts/patch-only.txt

You are modifying an existing Next.js + TypeScript repo.

Rules:
- Output ONLY a unified diff (git patch). No explanation.
- If you need new files, include them in the diff.
- Don’t change formatting or rename things unless required.
- Keep functions pure when possible.
- Do not add new dependencies.

Context:
- The failing behavior: 
- The expected behavior: 

Files you may edit:
- - Acceptance criteria:- `pnpm typecheck` passes- `pnpm test` passes- No unused imports```

This sounds strict. It is.One thing that bit me — if I let the model “clean up” unrelated code, the review cost explodes. The strict diff rule keeps the blast radius small.And yeah. Sometimes it refuses. Good. That means my request was vague.## 2) I add “typecheck” and “lint” scripts firstVibe coding without scripts is just gambling.I want one command that tells me “safe enough.”In Next.js + TS repos, I standardize on 4 scripts. I add them even in tiny weekend projects.

```json{  "scripts": {    "dev": "next dev",    "build": "next build",    "start": "next start",    "lint": "next lint",    "typecheck": "tsc -p tsconfig.json --noEmit",    "test": "vitest run",    "check": "pnpm -s lint && pnpm -s typecheck && pnpm -s test"  }}```

That `check` script is the whole point.Cursor can generate code fast. My job is making sure it doesn’t silently degrade the codebase.Also. I don’t run `next build` as my typecheck.Because `next build` can pass while I still have type holes in non-imported files. `tsc --noEmit` catches stuff that never gets bundled.## 3) I force the model to write tests for the bugIf I don’t write the test, I forget the bug.If the model doesn’t write the test, it “fixes” symptoms.My pattern: reproduce bug → lock it in with a test → then refactor.Here’s a real example I hit a lot: unsafe env parsing.AI-generated code loves `process.env.X as string`. That turns “missing env” into “undefined flows into prod.”I use a tiny env parser. Then I test it.

```ts// src/env.tsexport function requiredEnv(name: string): string {  const value = process.env[name];  if (!value || value.trim().length === 0) {    throw new Error(`Missing required env: ${name}`);  }  return value;}export function optionalEnv(name: string, fallback: string): string {  const value = process.env[name];  return value && value.trim().length > 0 ? value : fallback;}```

And the test. Small. Fast.

```ts// src/env.test.tsimport { describe, expect, it } from "vitest";import { optionalEnv, requiredEnv } from "./env";describe("env", () => {  it("throws when required env is missing", () => {    delete process.env.MY_REQUIRED;    expect(() => requiredEnv("MY_REQUIRED")).toThrow(      "Missing required env: MY_REQUIRED"    );  });  it("uses fallback for optional env", () => {    delete process.env.MY_OPTIONAL;    expect(optionalEnv("MY_OPTIONAL", "fallback")).toBe("fallback");  });});```

This is boring code. That’s why it works.Then in my app code, I only import from `src/env.ts`. No direct `process.env` reads.AI will still try. I reject the diff.## 4) I run a “dead import” check because AI loves leftoversClaude writes code fast. It also leaves crumbs.Unused imports. Unused variables. Half-removed helpers.Lint catches some of it. Not all.So I add a tiny Node script to fail CI if TypeScript has “unused” diagnostics.No extra dependencies. Just `tsc` output parsing.

```js// scripts/typecheck-strict.mjsimport { execSync } from "node:child_process";const cmd = "pnpm -s tsc -p tsconfig.json --noEmit";try {  execSync(cmd, { stdio: "pipe" });  process.exit(0);} catch (err) {  const output = (err.stdout?.toString() || "") + (err.stderr?.toString() || "");  // These show up constantly after AI edits.  const noisy = [    "TS6133", // 'x' is declared but its value is never read.    "TS6196"  // 'Foo' is declared but never used.  ];  const hasUnused = noisy.some((code) => output.includes(code));  // Print full output so CI logs show the file/line.  process.stderr.write(output);  process.exit(hasUnused ? 2 : 1);}```

Then I wire it in.

```json{  "scripts": {    "typecheck:strict": "node scripts/typecheck-strict.mjs",    "check": "pnpm -s lint && pnpm -s typecheck:strict && pnpm -s test"  }}```

Exit code `2` is intentional.When I’m debugging CI, I can tell the difference between “real type errors” vs “AI left junk.”Yeah, it’s picky.That’s the point. I don’t want to ship a refactor with 7 unused imports that confuse me later.## 5) My Cursor workflow: chat to propose, composer to apply, terminal to judgeI don’t let chat directly “decide” correctness.My loop is mechanical:1. Chat proposes a patch.2. I paste it into Composer.3. I scan the diff like it’s a PR.4. I run `pnpm check`.When it fails, I paste the exact failure back.Not “it doesn’t work.”I paste the actual error. Like:- `TS2322: Type 'string | undefined' is not assignable to type 'string'`- `ReferenceError: window is not defined`- `TestingLibraryElementError: Unable to find an element by...`Then I say: “Fix only what’s needed. Patch only.”This prevents the worst pattern I used to do.I’d accept an AI fix… then ask for “cleanup.”Cleanup is where bugs breed.If I want cleanup, I do it after the tests are green. In separate diffs. Small pieces.## ResultsThis process reduced my rollback moments from “a couple times a week” to 0 in the last 14 days.Real numbers:- I ran `pnpm check` 62 times across 6 branches.- I rejected 19 AI patches because they changed unrelated files.- I caught 11 type errors that `next build` didn’t surface in my local flow.The biggest win wasn’t speed. It was fewer surprise regressions.And my code reviews got faster because diffs stayed small.## Key takeaways- Make the model output diffs, not explanations.- Add `typecheck` and `check` scripts on day 1.- Don’t accept refactors without a test that reproduces the bug.- Treat unused imports as a failed build, not “later cleanup.”- Keep changes small. One behavior per patch.## ClosingCursor + Claude works best for me when it’s boxed in.Strict prompts. Strict scripts. Strict diffs.What’s your non-negotiable gate for AI-generated code: `tsc`, unit tests, E2E, or something else entirely?

React Native offline queue with SQLite sync

Sathish — Wed, 11 Mar 2026 15:01:40 +0000

I log workout sets offline. Always.
I queue writes in SQLite, not AsyncStorage.
I replay the queue with idempotency keys.
I fixed duplicate syncs and “ghost sets”.

Context

My fitness app has one job.
Log a set in ~5 seconds.

Internet can’t be a dependency.
Basements. Bad gym Wi‑Fi. Airplane mode because people hate notifications.
So the app is offline-first.

I started with the classic mistake.
“Just save locally and sync later.”
Spent 4 hours on it.
Most of it was wrong.

The failures were dumb but real.
Duplicate sets after a retry.
Sets that looked saved, then vanished after a relaunch.
And one brutal bug: I’d sync the same queue item twice when the app resumed.

SQLite fixed it.
Not because it’s trendy.
Because I needed transactions, querying, and a real queue.

1) I store an outbox. Not “pending: true”

I tried pending flags on the main sets table.
It worked until it didn’t.
Deletes were messy.
Edits were messy.
And retries became “which version is the truth?”

So I split it.
Real data tables.
And an outbox table with operations.

I’m on Expo.
I use expo-sqlite.

// db.ts
import * as SQLite from 'expo-sqlite';

export const db = SQLite.openDatabaseSync('gym.db');

export function migrate() {
  // WAL helps concurrency on mobile. Less "database is locked" pain.
  db.execSync(`PRAGMA journal_mode = WAL;`);

  db.execSync(`
    CREATE TABLE IF NOT EXISTS sets (
      id TEXT PRIMARY KEY NOT NULL,
      workout_id TEXT NOT NULL,
      exercise_id TEXT NOT NULL,
      reps INTEGER NOT NULL,
      weight REAL NOT NULL,
      created_at INTEGER NOT NULL
    );

    CREATE TABLE IF NOT EXISTS outbox (
      id TEXT PRIMARY KEY NOT NULL,
      op TEXT NOT NULL,           -- 'insert_set' | 'delete_set'
      payload TEXT NOT NULL,      -- JSON string
      idempotency_key TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      sent_at INTEGER             -- null until acked
    );

    CREATE INDEX IF NOT EXISTS idx_outbox_sent_at
      ON outbox(sent_at, created_at);

    CREATE UNIQUE INDEX IF NOT EXISTS uq_outbox_idem
      ON outbox(idempotency_key);
  `);
}

Why the unique index?
Because mobile apps retry.
Because I retry.
Because the OS kills the process mid-request.
If the same action gets enqueued twice, I want SQLite to say no.

And yeah.
The first time I added that index, my app crashed on launch.
UNIQUE constraint failed: outbox.idempotency_key.
It was my own duplicate rows from earlier tests.
I deleted the DB.
Moved on.

2) I enqueue + write in one transaction

This is the whole trick.
One transaction.
Either both writes happen, or neither does.

Before this, I’d insert into sets first.
Then enqueue the outbox.
If the app died between those two statements… I had a local set with no sync record.
A ghost set.

So now I do it atomically.

// logSet.ts
import { db } from './db';
import { randomUUID } from 'expo-crypto';

type SetRow = {
  id: string;
  workout_id: string;
  exercise_id: string;
  reps: number;
  weight: number;
  created_at: number;
};

export function logSet(input: Omit) {
  const now = Date.now();
  const setId = randomUUID();

  // Same user action => same idempotency key.
  // If UI double-taps, SQLite blocks the duplicate outbox row.
  const idempotencyKey = `insert_set:${setId}`;

  db.withTransactionSync(() => {
    db.runSync(
      `INSERT INTO sets (id, workout_id, exercise_id, reps, weight, created_at)
       VALUES (?, ?, ?, ?, ?, ?)` ,
      [setId, input.workout_id, input.exercise_id, input.reps, input.weight, now]
    );

    db.runSync(
      `INSERT INTO outbox (id, op, payload, idempotency_key, created_at, sent_at)
       VALUES (?, ?, ?, ?, ?, NULL)` ,
      [
        randomUUID(),
        'insert_set',
        JSON.stringify({ ...input, id: setId, created_at: now }),
        idempotencyKey,
        now,
      ]
    );
  });

  return setId;
}

No loading spinner.
No network call.
The UI updates from local SQLite immediately.
Sync is background work.

One thing that bit me — withTransactionSync will throw, but my UI didn’t show it.
I had one silent failure from a bad payload (NaN weight).
Now I validate inputs before writing.
Boring. Necessary.

3) I process the outbox with a lease

My second dumb bug.
App resumes.
I start sync.
NetInfo fires twice.
I start sync again.
Now two loops are racing.

Result.
Two requests for the same outbox row.
My API was “mostly idempotent”… until it wasn’t.

Fix.
A lease.
One sync loop at a time.
And I claim rows before sending.

// syncOutbox.ts
import { db } from './db';

let syncing = false;

type OutboxRow = {
  id: string;
  op: string;
  payload: string;
  idempotency_key: string;
  created_at: number;
  sent_at: number | null;
};

export async function syncOutbox(apiBaseUrl: string) {
  if (syncing) return;
  syncing = true;

  try {
    while (true) {
      // Grab the next unsent row.
      const row = db.getFirstSync(
        `SELECT * FROM outbox
         WHERE sent_at IS NULL
         ORDER BY created_at ASC
         LIMIT 1`
      );

      if (!row) break;

      // Claim it before sending. Prevents double-send on re-entry.
      const claimedAt = Date.now();
      db.runSync(`UPDATE outbox SET sent_at = ? WHERE id = ? AND sent_at IS NULL`, [claimedAt, row.id]);

      const stillClaimed = db.getFirstSync<{ c: number }>(
        `SELECT COUNT(1) AS c FROM outbox WHERE id = ? AND sent_at = ?`,
        [row.id, claimedAt]
      );

      if (!stillClaimed || stillClaimed.c !== 1) {
        // Someone else claimed it (or it was already sent). Skip.
        continue;
      }

      const res = await fetch(`${apiBaseUrl}/sync/outbox`, {
        method: 'POST',
        headers: {
          'content-type': 'application/json',
          'idempotency-key': row.idempotency_key,
        },
        body: JSON.stringify({ op: row.op, payload: JSON.parse(row.payload) }),
      });

      if (!res.ok) {
        // Unclaim it so we retry later.
        db.runSync(`UPDATE outbox SET sent_at = NULL WHERE id = ? AND sent_at = ?`, [row.id, claimedAt]);
        throw new Error(`sync failed: ${res.status} ${await res.text()}`);
      }

      // Acked. Delete from outbox.
      db.runSync(`DELETE FROM outbox WHERE id = ?`, [row.id]);
    }
  } finally {
    syncing = false;
  }
}

This isn’t a perfect distributed lock.
It’s good enough.
Single device.
Single DB.
My problem was re-entrancy.
This kills it.

Also.
I delete acked rows.
I don’t keep a “synced history” in outbox.
If I need audit logs, that’s a different table.

4) I make the server idempotent (or don’t bother)

If your server isn’t idempotent, offline sync is cosplay.
Harsh.
But true.

The client can do everything right.
Then a request times out.
You retry.
Now you inserted twice.

I fix it with an idempotency_keys table.
I’m using Postgres on the backend.
This is minimal.
Works.

-- postgres.sql
CREATE TABLE IF NOT EXISTS idempotency_keys (
  key TEXT PRIMARY KEY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS sets (
  id UUID PRIMARY KEY,
  workout_id UUID NOT NULL,
  exercise_id UUID NOT NULL,
  reps INT NOT NULL,
  weight DOUBLE PRECISION NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
);

// server.ts (Node 20 + Express)
import express from 'express';
import pg from 'pg';

const { Pool } = pg;
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const app = express();
app.use(express.json());

app.post('/sync/outbox', async (req, res) => {
  const key = String(req.header('idempotency-key') || '');
  if (!key) return res.status(400).send('missing idempotency-key');

  const { op, payload } = req.body as { op: string; payload: any };

  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // If the key already exists, we already processed it.
    const inserted = await client.query(
      `INSERT INTO idempotency_keys(key) VALUES($1)
       ON CONFLICT (key) DO NOTHING
       RETURNING key`,
      [key]
    );

    if (inserted.rowCount === 0) {
      await client.query('COMMIT');
      return res.status(200).json({ ok: true, deduped: true });
    }

    if (op === 'insert_set') {
      await client.query(
        `INSERT INTO sets(id, workout_id, exercise_id, reps, weight, created_at)
         VALUES($1, $2, $3, $4, $5, to_timestamp($6 / 1000.0))
         ON CONFLICT (id) DO NOTHING`,
        [payload.id, payload.workout_id, payload.exercise_id, payload.reps, payload.weight, payload.created_at]
      );
    } else {
      throw new Error(`unknown op: ${op}`);
    }

    await client.query('COMMIT');
    res.status(200).json({ ok: true });
  } catch (e: any) {
    await client.query('ROLLBACK');
    res.status(500).send(e.message);
  } finally {
    client.release();
  }
});

app.listen(3000);

Two layers.
Client idempotency.
Server idempotency.
You want both.

And yeah, I got this wrong first.
I only deduped by set.id.
Then I added delete/edit operations later.
Now the idempotency key is per operation.
Same set can be inserted, edited, deleted.
Different keys.

Results

Before the outbox, I could reproduce duplicates in under 2 minutes.
Airplane mode on.
Log 12 sets.
Kill the app.
Reopen.
Turn internet on.
I’d end up with 14–18 sets server-side.
Gross.

After the outbox + lease + idempotency:
I ran that same test 30 times.
Logged 360 sets total.
Server ended with exactly 360 sets.
No duplicates.
No missing rows.

UI speed also improved.
Set logging stays under 100ms on my Pixel 7.
Even with 400+ exercises cached.

Key takeaways

Don’t mix “real data” and “pending sync” in one table. Use an outbox.
Wrap “write data” + “enqueue op” in one SQLite transaction, or you’ll ship ghost rows.
Guard your sync loop. Re-entrancy happens on resume and flaky connectivity.
Client-side dedupe helps, but server idempotency is the actual safety net.
Delete acked outbox rows. If you need history, store history explicitly.

Closing

If you’re doing offline-first in React Native, what’s your strategy for edits and deletes?
Do you enqueue full row snapshots, JSON patches, or something else that won’t explode your outbox size?

Forem: Sathish

React Native offline-first: SQLite write-ahead log

Context

1) I stop fighting concurrency. I enable WAL.

2) I batch writes. One transaction. No drama.

3) I treat SQLITE_BUSY like a normal thing. Retry it.

4) I measure the write path. Otherwise I’m guessing.

Results

Key takeaways

Closing

Next.js job board: stable URLs for scraped jobs

Context

1) I stopped trusting slugs from scraped text

2) I made the URL format boring on purpose

3) I pushed uniqueness into Postgres, not my scraper

4) I kept old URLs alive when the “identity” changes

Results

Key takeaways

Closing

Cursor + Claude: stop shipping flaky UI states

Context

1) I write the states down. Then I force Claude to agree

2) I kill boolean soup with a typed reducer

3) I wire the reducer to real async code (without useEffect spaghetti)

4) I add one invariant test. It catches the dumb stuff

Results

Key takeaways

Closing

React Native offline-first: instant search in SQLite

Context

1) I stopped using LIKE. I indexed.

2) I query FTS5 like a grown-up

3) I debounced typing. Otherwise you DDOS your own DB

4) I highlight matches without doing fancy stuff

Results

Key takeaways

Closing

Cursor + Claude: stop shipping broken auth flows

Context

1) I force all session reads through one function

2) I make middleware do one job: block obvious bad routes

3) I fuzz my auth endpoints with a 30-line script

4) I add 3 tests. Only 3. They’re mean.

Results

Key takeaways

Closing

React Native offline-first: conflict rules in SQLite

Context

1) I store revisions per row. Not timestamps.

2) Every local write also writes an outbox op

3) Sync is pull-then-push. Always in that order.

4) Conflicts: I don’t “merge” everything. I pick winners.

Results

Key takeaways

Closing

Next.js job board search: Postgres FTS + trgm

Context

1) I stopped doing ILIKE. It’s a trap.

2) I rank with one SQL function. No client math.

3) I call it via Supabase RPC. Clean boundary.

4) I debug search with EXPLAIN ANALYZE. Every time.

Results

Key takeaways

Closing

Cursor Rules + Claude: stop breaking builds

Context

1) I write Cursor Rules first. Not prompts.

2) I force a “definition of done”: tests + typecheck

3) I run a “no new deps” tripwire

4) I make Claude work diff-first (and I reject big diffs)

5) I keep a failure log. One line per screw-up.

Results

Key takeaways

Closing

React Native SQLite: 0ms UI with debounced writes

Context

1) I measured the stutter. Then I got mad.

2) I made the UI the source of truth

3) I made writes idempotent. Offline-first demands it.

4) I wired it into the “tap to log” UI

1) I stopped doing `ILIKE`. It’s a trap.

4) I debug search with `EXPLAIN ANALYZE`. Every time.