localpocket 0.1.0

Local-first SQLite database for Dart/Flutter with eventually-consistent PocketBase synchronization. Implements the plan in plan.md (Phases 1-2).

LocalPocket

LocalPocket is a local-first SQLite database for Dart and Flutter.

It gives your app fast local reads and writes, then synchronizes changes with a remote backend such as PocketBase when connectivity is available.

Your app → LocalPocket → SQLite
                    ↘ sync backend (optional)

Your app reads and writes locally first. Network synchronization happens separately, so the user interface does not need to wait for the network.

Current status: LocalPocket is actively developed. The local database, sync engine, outbox, conflict handling, files API, FTS5 support, and PocketBase adapter are implemented and tested. Review the platform notes before using web/WASM or very large encrypted blobs in production.

Why use LocalPocket?

• Offline-first: local CRUD works without a network connection.
• SQLite underneath: typed columns, indexes, transactions, migrations, and query planning.
• Safe synchronization: changes are recorded in a durable outbox and retried later.
• Conflict handling: local and remote changes can be merged with configurable policies.
• Reactive queries: watch collections or individual records after committed changes.
• File attachments: content-addressed blob storage with deduplication and a separate sync lane.
• Full-text search: optional SQLite FTS5 search with BM25 ranking.
• Encryption options: AES-256-GCM field encryption and injectable database encryption support.

Installation

Add LocalPocket to pubspec.yaml:

dependencies:
  localpocket: ^0.1.0

Then run:

dart pub get

For desktop Dart or Flutter tests, add the FFI database implementation:

dev_dependencies:
  sqflite_common_ffi: ^2.3.0

For mobile, inject the SQLite factory used by your application. LocalPocket does not choose a platform database factory for you.

5-minute quick start

This example uses an in-memory database, which is convenient for tests and demos:

import 'package:localpocket/localpocket.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';

Future<void> main() async {
  sqfliteFfiInit();

  final db = await LocalPocket.open(
    path: inMemoryDatabasePath,
    factory: databaseFactoryFfi,
    stores: [
      CollectionSchema(
        name: 'patients',
        version: 1,
        fields: [
          Field.text('name', required: true),
          Field.int('age'),
          Field.bool('active'),
        ],
        indexes: const [IndexSpec(['name'])],
      ),
    ],
  );

  final patients = db.collection('patients');
  await patients.put({'name': 'Jane Doe', 'age': 30, 'active': true});

  final page = await patients.query()
      .where('active', eq: true)
      .orderBy('name')
      .limit(20)
      .fetch();

  print(page.items);
  await db.close();
}

Persistent databases

Use a file path instead of inMemoryDatabasePath when data should survive app restarts:

final db = await LocalPocket.open(
  path: '/path/to/app/local.db',
  factory: databaseFactoryFfi,
  stores: [mySchema],
);

On desktop, use sqflite_common_ffi. On mobile, use the platform SQLite factory. On web, use a browser-compatible SQLite/WASM factory supplied by your application and review the web limitations below.

Always close the database when finished:

await db.close();

Define your data model

A CollectionSchema describes one local table. Fields are stored in typed SQLite columns.

final taskSchema = CollectionSchema(
  name: 'tasks',
  version: 1,
  fields: [
    Field.text('title', required: true),
    Field.text('status'),
    Field.int('priority'),
    Field.bool('completed'),
    Field.date('due_at'),
    Field.json('metadata'),
    Field.jsonList('labels'),
  ],
  indexes: const [IndexSpec(['status', 'priority'])],
);

Available field types:

Field	Dart value	SQLite storage
Field.text	String	TEXT
Field.int	int	INTEGER
Field.real	num	REAL
Field.bool	bool	INTEGER (0/1)
Field.date	epoch-millisecond int	INTEGER
Field.enumValue	one of the declared strings	TEXT
Field.json	Map or List	canonical JSON TEXT
Field.jsonList	List	canonical JSON TEXT
Field.ref	record ID String	TEXT

Fields not declared in the schema are preserved in an overflow JSON field, so newer remote fields are not silently discarded.

Required and unique fields

final userSchema = CollectionSchema(
  name: 'users',
  version: 1,
  fields: [
    Field.text('email', required: true, uniqueWhenActive: true),
    Field.text('display_name'),
  ],
);

CRUD operations

final tasks = db.collection('tasks');

await tasks.put({
  'id': 'abc123abc123abc',
  'title': 'Buy milk',
  'priority': 1,
});

final task = await tasks.get('abc123abc123abc');

await tasks.patch('abc123abc123abc', {'completed': true});
await tasks.archive('abc123abc123abc');
await tasks.restore('abc123abc123abc');

// Permanent local deletion, including local file references.
await tasks.purge('abc123abc123abc');

Standalone mutations use full durability by default. If your application explicitly accepts normal SQLite durability for an operation:

await tasks.patch(
  'abc123abc123abc',
  {'completed': true},
  durability: DurabilityClass.normal,
);

Bulk writes and transactions

For many records, use putAll or an explicit transaction. LocalPocket writes the domain record, outbox intent, and sync state atomically.

await db.transaction((tx) async {
  await tx.collection('tasks').putAll([
    {'title': 'First task', 'priority': 1},
    {'title': 'Second task', 'priority': 2},
  ]);
});

A new record normally creates three logical local rows: the domain row, the outbox row, and the sync-state row. They commit together.

Queries

Queries require .limit(n) or explicit .all().

final page = await db.collection('tasks')
    .query()
    .where('priority', gte: 1)
    .where('completed', eq: false)
    .orderBy('priority')
    .limit(50)
    .fetch();

for (final task in page.items) {
  print(task['title']);
}

Pagination and projections

final firstPage = await db.collection('tasks')
    .query()
    .orderBy('priority')
    .select(['id', 'title', 'priority'])
    .limit(50)
    .fetch();

var cursor = firstPage.nextCursor;
while (cursor != null) {
  final page = await db.collection('tasks')
      .query()
      .orderBy('priority')
      .select(['id', 'title', 'priority'])
      .limit(50)
      .keysetAfter(cursor!)
      .fetch();
  print(page.items);
  cursor = page.nextCursor;
}

The query builder automatically adds id as a stable tie-breaker to ordered queries.

Reactive queries

final subscription = db.collection('tasks')
    .query()
    .where('completed', eq: false)
    .orderBy('priority')
    .limit(50)
    .watch()
    .listen((tasks) => print('Open tasks: ${tasks.length}'));

await subscription.cancel();

Use watchOne(id) when you only need one record.

Full-text search

Enable FTS5 in a schema:

final articleSchema = CollectionSchema(
  name: 'articles',
  version: 1,
  fields: [Field.text('title', required: true), Field.text('body')],
  fts: const FtsSpec(['title', 'body']),
);

Search it:

final results = await db.collection('articles')
    .search('local database')
    .limit(20)
    .fetch();

Synchronization

LocalPocket separates local CRUD from synchronization. The engine pulls remote changes, applies them locally, pushes local outbox changes, and runs anti-entropy sweeps.

final engine = SyncEngine(pocket: db, backend: myBackend);
await engine.start();

final report = await engine.syncNow();
print('Pushed: ${report.pushed}');

await engine.stop();

PocketBase

import 'package:localpocket/pocketbase.dart';
import 'package:localpocket/sync.dart';

final backend = PocketBaseBackend(
  baseUrl: Uri.parse('https://your-pocketbase.example.com'),
  tokenProvider: myTokenProvider,
  stores: const ['tasks'],
);

final engine = SyncEngine(pocket: db, backend: backend);
await engine.start();

TokenProvider is supplied by your application. Store tokens in platform-secure storage; LocalPocket does not persist them in SQLite.

Sync status and conflicts

final statusSubscription = engine.status.listen((status) {
  print('State: ${status.state}; pending: ${status.pending}');
});

The default conflict policy is remote-wins for overlapping fields. Field-specific resolvers include CounterResolver, SetUnionResolver, LocalWinsResolver, AppendOnlyResolver, and CustomResolver.

Files and attachments

Configure a BlobStore when opening the database:

final db = await LocalPocket.open(
  path: '/path/to/local.db',
  factory: databaseFactoryFfi,
  stores: [taskSchema],
  blobStore: NativeBlobStore('/path/to/blob-directory'),
);

Attach and open files:

final file = await db.files.attach(
  store: 'tasks',
  recordId: taskId,
  bytes: imageStream,
  name: 'photo.jpg',
  expectedSize: imageSize,
);

final bytes = await db.files.open(
  store: 'tasks',
  recordId: taskId,
  refId: file.refId,
);

The native blob store hashes data while streaming, deduplicates by content hash, and publishes files atomically. For web applications, inject a browser-compatible BlobStore.

await db.files.gc();
await db.files.enforceStorageCap(maxBytes: 500 * 1024 * 1024);

Encryption and migrations

Mark supported fields as encrypted and provide a cipher:

final cipher = AesGcmFieldCipher(List<int>.filled(32, 7));

final schema = CollectionSchema(
  name: 'secrets',
  version: 1,
  fields: [
    Field.text('public_name'),
    Field.text('private_note', encrypted: true),
  ],
);

final db = await LocalPocket.open(
  path: '/path/to/local.db',
  factory: databaseFactoryFfi,
  stores: [schema],
  fieldCipher: cipher,
);

Use real key management in production. Encrypted fields cannot be indexed, sorted, or included in FTS.

For schema evolution, increase CollectionSchema.version and add forward StoreMigration steps. Test migrations against real-data copies before release.

Durability model

LocalPocket uses a single serialized writer queue. A normal mutation preserves this invariant:

If local domain state is committed, its durable outbox intent is committed with it.

For bulk work, use one explicit transaction or putAll rather than thousands of standalone transactions. Large batches use more memory and hold the writer longer.

Web and platform notes

• The core API accepts an injected DatabaseFactory.
• B11 is a native FFI smoke test using the web profile, not a browser WASM benchmark.
• One-shot isolate offloading is not used by current async helpers because it is unsupported in common JavaScript builds and slower for tested page sizes.
• NativeBlobStore is conditionally exported for native dart:io platforms. Web applications need a browser-compatible blob store.
• Browser/WASM/IndexedDB, SharedWorker, OPFS, and browser multipart behavior should be tested in the actual target browser.

Performance notes

LocalPocket is designed around bounded work and indexed queries:

• Point reads use a combined domain/sync query.
• Keyset pagination avoids deep OFFSET scans.
• Pure-create bulk inserts batch domain, outbox, and sync-state writes.
• Projections reduce decode work.
• Watch notifications are debounced and emitted after commit.
• Successful batch synchronization settles under one local transaction.

Benchmark results vary with backend, device, storage, build mode, data shape, and system load. Treat the suite as a regression signal, not a universal guarantee.

Running tests and benchmarks

dart analyze
dart test
dart run benchmark/benchmark.dart

Additional focused profiles are available in benchmark/ and probe/. Live PocketBase tests require network access and credentials and are skipped by default.

Package layout

lib/
  localpocket.dart       Local SQLite API
  sync.dart              Sync engine, conflicts, files, blob stores
  pocketbase.dart        PocketBase adapter and HTTP transport
benchmark/               Benchmarks and recorded results
probe/                   Performance probes
test/                    Unit, integration, security, and performance tests

Design principles

1. SQLite is the source of truth for local state.
2. Domain state and synchronization intent commit atomically.
3. Remote events are hints; pull is the authoritative ingest path.
4. Absence from a remote query does not automatically mean deletion.
5. Unknown fields are preserved through the overflow JSON field.
6. Work is bounded: batches, queues, memory, and query results have explicit limits.
7. Correctness comes before clever optimizations.

License

See LICENSE.