metadata.json
{
"version": "1.1.0",
"organization": "Capgo",
"date": "October 2026",
"abstract": "Design offline-first Capacitor apps: SQLite source of truth with @capgo/capacitor-fast-sql, outbox sync queue, delta pulls, conflict resolution, network detection, background sync, and avoiding WebView storage eviction and service worker pitfalls.",
"triggers": [
"offline",
"offline first",
"sync data",
"work offline",
"outbox",
"conflict resolution",
"fast sql",
"localStorage evicted",
"service worker capacitor",
"networkStatusChange"
],
"references": [
"https://capgo.app/docs/plugins/fast-sql/",
"https://capacitorjs.com/docs/guides/storage",
"https://capacitorjs.com/docs/apis/network",
"https://capgo.app/docs/plugins/background-task/",
"https://capacitorjs.com/docs/ios/troubleshooting"
]
}
references/fast-sql-setup.md
# Fast SQL Platform Setup
Load when installing `@capgo/capacitor-fast-sql` (8.x for Capacitor 8). For migrating from another SQLite plugin, use `sqlite-to-fast-sql` instead.
The plugin runs a local HTTP server on `localhost` to move query results without the Capacitor bridge, so both platforms must allow cleartext to loopback.
## iOS (`ios/App/App/Info.plist`)
```xml
<key>NSAppTransportSecurity</key>
<dict>
<key>NSAllowsLocalNetworking</key>
<true/>
</dict>
```
Merge into an existing `NSAppTransportSecurity` dict; do not add a second one. This only permits loopback cleartext.
## Android
`android/app/src/main/res/xml/network_security_config.xml`:
```xml
<?xml version="1.0" encoding="utf-8"?>
<network-security-config>
<domain-config cleartextTrafficPermitted="true">
<domain includeSubdomains="false">localhost</domain>
<domain includeSubdomains="false">127.0.0.1</domain>
</domain-config>
</network-security-config>
```
```xml
<application android:networkSecurityConfig="@xml/network_security_config" ...>
```
If the app already has a network security config (e.g. for pinning), add the `domain-config` to it instead of replacing it. Avoid `android:usesCleartextTraffic="true"`; it opens cleartext to every host.
## Web fallback
Uses sql.js (WASM). By default it loads from a CDN; for offline PWA builds bundle it and call before the first connect:
```ts
import { CapgoCapacitorFastSql } from '@capgo/capacitor-fast-sql';
await CapgoCapacitorFastSql.configureWeb({ sqlJsUrl: '/assets/sql-wasm.js', wasmUrl: '/assets/sql-wasm.wasm' });
```
## API cheat sheet
```ts
import { FastSQL, KeyValueStore } from '@capgo/capacitor-fast-sql';
const db = await FastSQL.connect({ database: 'app' }); // SQLConnection
const rows = await db.query('SELECT * FROM todos WHERE done = ?', [0]);
const { rowsAffected, insertId } = await db.run('DELETE FROM todos WHERE id = ?', [id]);
await db.executeBatch([{ statement: 'INSERT ...', params: [...] }]);
await db.transaction(async (tx) => { await tx.run('...'); });
const kv = await KeyValueStore.open({ database: 'app', store: 'settings' });
await kv.set('lastSync', 1730000000);
const v = await kv.get('lastSync');
```
Options: `encrypted` + `encryptionKey` (iOS/Android; Android needs the SQLCipher dependency per plugin README), `readOnly`. Load the encryption key from secure storage, never hardcode it.
Check the installed version's `SQLBatchOperation` type in `node_modules/@capgo/capacitor-fast-sql/dist/esm/definitions.d.ts` before writing batch code.
## Migrations
Keep a `PRAGMA user_version` integer and apply ordered migration steps inside a transaction at startup:
```ts
const [{ user_version }] = await db.query('PRAGMA user_version');
if (Number(user_version) < 1) {
await db.transaction(async (tx) => {
await tx.run('CREATE TABLE todos (id TEXT PRIMARY KEY, text TEXT, done INTEGER, updated_at INTEGER, dirty INTEGER, deleted INTEGER DEFAULT 0)');
await tx.run('PRAGMA user_version = 1');
});
}
```
Live updates can ship new JS against an old on-device schema; migrations must run on every app start and be forward-only.
references/sync-engine.md
# Sync Engine: Outbox, Pull, Conflicts
Load when implementing or debugging the sync loop.
## Tables
```sql
CREATE TABLE todos (
id TEXT PRIMARY KEY,
text TEXT NOT NULL,
done INTEGER NOT NULL DEFAULT 0,
updated_at INTEGER NOT NULL, -- local edit time (display only)
server_version INTEGER, -- last version seen from server
dirty INTEGER NOT NULL DEFAULT 0,
deleted INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE outbox (
id TEXT PRIMARY KEY, -- also the idempotency key
entity TEXT NOT NULL,
op TEXT NOT NULL, -- upsert | delete
payload TEXT NOT NULL,
created_at INTEGER NOT NULL,
attempts INTEGER NOT NULL DEFAULT 0,
last_error TEXT
);
CREATE TABLE sync_state (entity TEXT PRIMARY KEY, cursor TEXT);
```
## Single-flight sync
```ts
let running: Promise<void> | null = null;
export function sync() {
running ??= (async () => {
try {
await ensureFreshAuth();
await push();
await pull();
} finally {
running = null;
}
})();
return running;
}
```
## Push (drain outbox in order)
```ts
async function push() {
const items = await db.query('SELECT * FROM outbox ORDER BY created_at LIMIT 50');
for (const item of items) {
try {
const res = await fetch(`${API}/${item.entity}/${JSON.parse(String(item.payload)).id}`, {
method: item.op === 'delete' ? 'DELETE' : 'PUT',
headers: { 'Content-Type': 'application/json', 'Idempotency-Key': String(item.id) },
body: item.op === 'delete' ? undefined : String(item.payload),
});
if (res.status === 409) { await markConflict(item, await res.json()); continue; }
if (res.status === 401) return; // keep entries; re-auth later
if (!res.ok && res.status >= 500) throw new Error(`HTTP ${res.status}`);
if (!res.ok) { await deadLetter(item, res.status); continue; } // 4xx: will never succeed
const rowId = JSON.parse(String(item.payload)).id;
await db.transaction(async (tx) => {
await tx.run('DELETE FROM outbox WHERE id = ?', [item.id]);
// clear dirty only if no newer outbox entry targets the same row
await tx.run(
`UPDATE todos SET dirty = 0 WHERE id = ? AND NOT EXISTS
(SELECT 1 FROM outbox WHERE entity = 'todo' AND json_extract(payload, '$.id') = ?)`,
[rowId, rowId],
);
});
} catch (e) {
await db.run('UPDATE outbox SET attempts = attempts + 1, last_error = ? WHERE id = ?', [String(e), item.id]);
return; // stop on network error, keep order; retry with backoff
}
}
}
```
`json_extract` needs SQLite JSON1 (bundled in current iOS/Android SQLite builds; confirm on your minimum OS). Alternative: store `row_id` as its own outbox column.
Backoff: `delay = min(2^attempts * 1000, 5 * 60_000)` plus jitter. Show items with `attempts > N` or dead-lettered entries in the UI.
## Pull (delta by server cursor)
```ts
async function pull() {
const [state] = await db.query('SELECT cursor FROM sync_state WHERE entity = ?', ['todo']);
const res = await fetch(`${API}/todos?since=${encodeURIComponent(String(state?.cursor ?? ''))}`);
const { items, cursor } = await res.json(); // cursor issued by server
await db.transaction(async (tx) => {
for (const r of items) {
const [local] = await tx.query('SELECT dirty FROM todos WHERE id = ?', [r.id]);
if (local?.dirty) { await resolveConflict(tx, r); continue; }
await tx.run(`INSERT INTO todos (id, text, done, updated_at, server_version, dirty, deleted)
VALUES (?, ?, ?, ?, ?, 0, ?)
ON CONFLICT(id) DO UPDATE SET text = excluded.text, done = excluded.done,
updated_at = excluded.updated_at, server_version = excluded.server_version, deleted = excluded.deleted`,
[r.id, r.text, r.done ? 1 : 0, r.updatedAt, r.version, r.deleted ? 1 : 0]);
}
await tx.run('INSERT INTO sync_state (entity, cursor) VALUES (?, ?) ON CONFLICT(entity) DO UPDATE SET cursor = excluded.cursor', ['todo', cursor]);
});
}
```
The server must return tombstones for deletes, or deleted rows reappear on other devices.
## Conflict policies
| Policy | How | Good for |
|--------|-----|----------|
| Server-authoritative + version check | Client sends `server_version`; server returns 409 on mismatch; client re-applies or asks user | Most business data |
| Last-write-wins | Compare server-assigned timestamps; never device clocks | Low-value fields, settings |
| Per-field merge | Track changed fields in outbox payload; merge non-overlapping | Forms with many independent fields |
| CRDT | Library-managed (Yjs, Automerge) | Collaborative text / lists |
Ask the user which policy fits; do not silently pick last-write-wins for financial or shared data.
## Background sync
`@capgo/capacitor-background-task` schedules periodic background work (iOS BGTaskScheduler / Android WorkManager style). Keep the task short, call the same `sync()`, and expect the OS to delay or skip runs.
## Tests
- Unit-test `push`/`pull` against a fake API with: timeout after server commit (must not duplicate), 409, 401 mid-drain, 500 then success.
- Device test with airplane mode and app kill between write and sync.
SKILL.md
---
name: capacitor-offline-first
description: Designs and implements offline-first data in Capacitor apps - local SQLite source of truth with @capgo/capacitor-fast-sql (`FastSQL.connect`, `KeyValueStore`), an outbox/sync queue, delta pulls, conflict resolution, @capacitor/network `networkStatusChange`, background sync with @capgo/capacitor-background-task, and why localStorage/IndexedDB get evicted and service workers break plugin injection in native WebViews. Use when the app must work without internet, data disappears after iOS storage pressure, sync duplicates or loses writes, or `Plugin not implemented` appears after adding a service worker. Do not use for migrating an existing SQLite plugin (sqlite-to-fast-sql), shipping web bundle updates offline (capgo-live-updates), HTTP security/certificate pinning (capacitor-security), or general performance (capacitor-performance).
---
# Offline-First Capacitor Apps
## When to Use
TRIGGER when:
- The app must read and write while offline and sync later
- Choosing local storage (Preferences vs SQLite vs IndexedDB) for user data
- Writes are duplicated, lost, or overwritten during sync
- Cached data vanishes on iOS after low-storage events
- A service worker was added and native plugins stopped working (`Plugin not implemented`)
Do not use:
- Replacing `@capacitor-community/sqlite` / Ionic secure storage: `sqlite-to-fast-sql`
- Getting new web code to devices (OTA): `capgo-live-updates`
- Encryption key storage, SSL pinning: `capacitor-security`
- Query speed / bridge overhead tuning: `capacitor-performance`
## Decisions (make these before writing code)
| Question | Default answer |
|----------|----------------|
| Where does UI read from? | Local DB only. Network writes into the DB; UI observes the DB. |
| Storage for records | SQLite via `@capgo/capacitor-fast-sql` |
| Storage for small settings / tokens | `@capacitor/preferences` (not for secrets; see `capacitor-security`) |
| localStorage / IndexedDB | Transient only: iOS can evict WebView storage under pressure |
| App shell offline | Already offline: Capacitor bundles web assets in the binary (or Capgo live-update bundle). No service worker needed. |
| Service worker in native builds | Avoid. Android SW blocks Capacitor plugin injection; iOS SW needs `WKAppBoundDomains`, which also blocks injection unless `ios.limitsNavigationsToAppBoundDomains` is set. Keep SW for the PWA build only. |
| IDs | Client-generated UUIDs (`crypto.randomUUID()`), so offline creates need no server round trip |
| Conflict policy | Ask the user/product owner: last-write-wins per record, per-field merge, or server-authoritative with user review |
| Deletes | Soft delete (tombstone) until the server acknowledges |
## Workflow
1. **Inspect**: current storage (`localStorage`, `@capacitor/preferences`, other SQLite plugins), API shape (does it support `updated_since` / version fields / idempotency keys?), auth refresh flow, existing service worker registration.
2. **Report** the plan (schema, outbox, conflict policy) to the user before invasive changes. For large apps, list each entity as a TODO.
3. **Install**: `npm install @capgo/capacitor-fast-sql @capacitor/network && npx cap sync`, then the platform setup in [references/fast-sql-setup.md](references/fast-sql-setup.md) (required: iOS ATS local networking, Android localhost cleartext).
4. **Schema + outbox**: [references/sync-engine.md](references/sync-engine.md).
5. **Triggers for sync**: app start, `networkStatusChange` -> connected, App `resume`, after each local write (debounced), optional background task.
6. **UI**: show pending count / last synced time; never block a write on the network.
7. **Verify** (below).
Only load a reference when its topic is in play.
## Minimal shape
```ts
import { FastSQL } from '@capgo/capacitor-fast-sql';
import { Network } from '@capacitor/network';
import { App } from '@capacitor/app';
const db = await FastSQL.connect({ database: 'app' });
await db.execute(`CREATE TABLE IF NOT EXISTS outbox (
id TEXT PRIMARY KEY, entity TEXT NOT NULL, op TEXT NOT NULL,
payload TEXT NOT NULL, created_at INTEGER NOT NULL, attempts INTEGER NOT NULL DEFAULT 0
)`);
export async function saveTodo(todo: Todo) {
await db.transaction(async (tx) => {
await tx.run('INSERT OR REPLACE INTO todos (id, text, done, updated_at, dirty) VALUES (?, ?, ?, ?, 1)',
[todo.id, todo.text, todo.done ? 1 : 0, Date.now()]);
await tx.run('INSERT INTO outbox (id, entity, op, payload, created_at) VALUES (?, ?, ?, ?, ?)',
[crypto.randomUUID(), 'todo', 'upsert', JSON.stringify(todo), Date.now()]);
});
void sync(); // fire and forget; sync() is single-flight
}
Network.addListener('networkStatusChange', (s) => { if (s.connected) void sync(); });
App.addListener('resume', () => void sync());
```
The data row and its outbox entry are written in one transaction so a crash cannot leave one without the other.
## Traps
- `Network.getStatus().connected === true` does not mean the API is reachable (captive portals, VPN, server down). Treat fetch failures as offline; use `connected` only as a hint to retry.
- `networkStatusChange` can fire several times in a row; make `sync()` single-flight.
- Send an idempotency key (the outbox row id) with every mutation; retries after a timeout otherwise duplicate records.
- Process the outbox in order per entity; a delete must not overtake its create.
- Advance the pull cursor using the **server's** timestamp/version from the response, never the device clock.
- Auth tokens expire while offline: refresh before draining the outbox, and keep entries on 401 instead of dropping them.
- Do not store large blobs in SQLite rows; save files with `@capacitor/filesystem` and keep the path in the DB.
- iOS background execution is opportunistic. Background tasks help but cannot guarantee sync timing; foreground sync on `resume` is the reliable path.
## Verification
1. Airplane mode on device: create, edit, delete records; kill and relaunch the app; data is still there.
2. Turn network back on: outbox drains to 0, server reflects all changes once (no duplicates).
3. Conflict test: edit the same record on two devices offline, reconnect both; result matches the chosen policy.
4. Flaky network: Android emulator network throttling / iOS Network Link Conditioner (Settings -> Developer); retries back off, no data loss.
5. Native checks:
```bash
grep -rn "serviceWorker.register" src/ | grep -v "isNativePlatform" # should be empty or web-guarded
/usr/libexec/PlistBuddy -c "Print :NSAppTransportSecurity:NSAllowsLocalNetworking" ios/App/App/Info.plist
grep -n "networkSecurityConfig\|usesCleartextTraffic" android/app/src/main/AndroidManifest.xml
```
6. Inspect the DB: `SELECT COUNT(*) FROM outbox` exposed in a debug screen, or pull the file (`adb exec-out run-as com.example.app ls databases/`).
## Error Handling
| Error / symptom | Cause | Fix |
|-----------------|-------|-----|
| `"CapgoCapacitorFastSql" plugin is not implemented on android` / `Plugin not implemented` | Service worker or `WKAppBoundDomains` blocking injection, or missing `npx cap sync` | Remove SW from native build / set `ios.limitsNavigationsToAppBoundDomains`; run `npx cap sync` |
| iOS: `The resource could not be loaded because the App Transport Security policy requires the use of a secure connection` | Fast SQL localhost transport blocked by ATS | Add `NSAllowsLocalNetworking` |
| Android: `CLEARTEXT communication to localhost not permitted by network security policy` | Cleartext to localhost blocked | Add `network_security_config.xml` localhost exception |
| `SQLITE_CONSTRAINT: UNIQUE constraint failed` during pull | Server row id already exists locally | Use `INSERT ... ON CONFLICT(id) DO UPDATE` |
| `database is locked` | Parallel writers / long transaction | Serialize writes; keep transactions short; single-flight sync |
| Data gone after iOS storage pressure | Stored in localStorage / IndexedDB | Move to SQLite |
| Duplicate server records after reconnect | Retried POST without idempotency key | Send outbox id as `Idempotency-Key`; server dedupes |
## Resources
- Fast SQL plugin: https://capgo.app/docs/plugins/fast-sql/
- Capacitor storage guide: https://capacitorjs.com/docs/guides/storage
- Network API: https://capacitorjs.com/docs/apis/network
- Background task plugin: https://capgo.app/docs/plugins/background-task/
- iOS troubleshooting (WKAppBoundDomains): https://capacitorjs.com/docs/ios/troubleshooting