Data export and restore

In an app with no server, the export file is the only copy of a user's data that can outlive the phone, and restore is the only recovery path they can invoke themselves. Both are dangerous in opposite directions: a lossy export silently becomes someone's backup, and a half-applied restore destroys the very database it was meant to protect. This skill governs the portable artifacts; the on-device

VACUUM INTO

snapshot and WAL rules belong to

persistence-drift

Depth lives in

references/formats-and-encoding.md

(CSV/JSON/PDF mechanics) and

references/restore-and-merge-policy.md

(validation ladder, merge strategies).

Non-negotiable rules

Backup and export are two different features — never conflate them. Backup is exact, machine-read, round-trippable, and the only thing restore accepts. Export (CSV for a spreadsheet, PDF for a human) is an interop artifact, lossy by design, and must be refused as a restore source. WHY: the moment a CSV is restorable, a format built for readability becomes the file someone's records depend on.
Every backup carries an envelope, and restore reads it first.
```
formatVersion
```
,
```
schemaVersion
```
,
```
appVersion
```
,
```
exportedAtUtc
```
, a checksum over the payload, then the payload. Restore verifies the checksum, refuses
```
schemaVersion
```
greater than the app's with a plain message, and migrates an older payload through the same forward-only path as the database (
```
run-migration
```
). WHY: an unversioned file cannot be refused, only misread.
Restore is all-or-nothing and never edits the live database in place. Import into a fresh staging database, validate everything, close every handle, then publish by atomic rename with the previous file kept as a rollback until the new one opens cleanly. WHY: a partial restore is worse than a failed one — it leaves a state the user cannot describe and you cannot reproduce.
Machine formats carry canonical values only. Integer minor units, SI integers, ISO-8601 UTC instants, stable ids, untranslated enum codes. Never a localized numeral, a formatted date, a currency symbol, or a translated label in a file that will be parsed. Localize only in human-facing exports, which are never re-imported. WHY:
```
١٢٫٥
```
and
```
12.5
```
and
```
12,5
```
are the same quantity and three different parses.
Identity is content-assigned, never a rowid. Every exportable row carries a stable id (UUID/ULID) minted at creation. WHY: re-importing the same file must be idempotent — with autoincrement ids it duplicates every record instead.
State the merge policy and show it before it runs. Replace-all (wipe then import) and merge-by-id (last-write-wins on a stored
```
updatedAtUtc
```
) are different promises; the confirmation names which one is about to happen and what will be lost. WHY: "Restore" reads as "add my data back" to a user who is about to lose a week.
Stream to a temp file, publish by rename. Build the artifact through an
```
IOSink
```
into the app's own temp directory and rename it into place only when the last byte is flushed. WHY: a 100k-row export assembled in a
```
String
```
OOMs a cheap phone, and a half-written file must never be shareable.
Nothing leaves the sandbox without an explicit user action, through an injected Gateway. No auto-upload, no background sync, no "helpful" cloud copy. The share sheet and file-save picker sit behind a
```
ShareGateway
```
/
```
FileExportGateway
```
(
```
service-boundary-and-native
```
), faked in tests.
CSV is escaped twice: RFC 4180 and against formula injection. Quote any field containing the delimiter, a quote, CR or LF, doubling embedded quotes; and neutralize a leading
```
=
```
,
```
+
```
,
```
-
```
,
```
@
```
, tab or CR so a spreadsheet renders the text instead of executing it. WHY: an unescaped cell is a code-execution vector in the recipient's spreadsheet, and a lost row in yours.
Export and restore return typed results, never throw at the UI.
```
Result<ExportArtifact, ExportFailure>
```
/
```
Result<RestoreReport, RestoreFailure>
```
with a distinct failure per refusal reason (checksum, unsupported version, malformed payload, no space, permission denied). WHY: "Restore failed" with no reason is indistinguishable from data loss to the person reading it.
A failed export leaves no artifact. Delete the temp file on every failure path and never publish a partial one. WHY: a truncated file that looks like a backup is the failure mode that surfaces months later.
Say what an export is. Exports are plaintext and unencrypted unless you built encryption on purpose; the UI must say so at the moment of sharing, and the claim must match the code (see
```
release-and-store-shipping
```
for claim wording).

The envelope

dart

/// The only shape restore accepts. Version fields come FIRST so a truncated or
/// foreign file is rejected by the header rather than by a mid-parse exception.
({
  int formatVersion,        // this envelope's own shape — bump independently of the DB
  int schemaVersion,        // the DB schema the payload was written from
  String appVersion,        // provenance for support, never a compatibility check
  DateTime exportedAtUtc,   // UTC, from the injected Clock
  String payloadSha256,     // checksum over the payload bytes, verified before parsing
  Map<String, Object?> payload,
});

formatVersion

and

schemaVersion

are separate on purpose: the envelope can gain a field without a database migration, and the database can migrate without changing the file shape. A single "version" number conflates them and forces a false choice on the next change. Complete, runnable shape:

examples/backup_envelope.dart

Restore: validate, stage, swap

dart

Future<Result<RestoreReport, RestoreFailure>> restore(File picked) async {
  // 1. Header and integrity BEFORE anything is parsed or opened.
  final envelope = readEnvelope(picked);
  switch (envelope) {
    case Err(:final failure): return Err(failure);           // malformed / bad checksum
    case Ok(:final value) when value.schemaVersion > kSchemaVersion:
      return const Err(RestoreFailure.newerThanApp());       // refuse, never guess
    case Ok():
      break;
  }

  // 2. Import into a STAGING database — the live one is never touched yet.
  final staging = await openStagingDatabase();
  final imported = await staging.transaction(() => _importAll(envelope.value.payload));
  if (imported case Err(:final failure)) {
    await staging.close();
    await deleteStagingFiles();                              // user's data still intact
    return Err(failure);
  }

  // 3. Migrate the staged copy forward through the SAME path as the app database.
  //    4. Close every handle, then publish by rename, keeping the old file until the
  //       new one opens cleanly. See references/restore-and-merge-policy.md.
  return publishStagedDatabase(staging);
}

The staging file, the migration path, and the rename ordering are the whole correctness argument — a restore that writes directly into the open app database has no failure mode that leaves the user where they started.

Choosing a format

Need	Format	Restorable?
Full-fidelity backup the app reads back	Envelope + JSON payload (or a copied DB file)	Yes — the only one
Rows for a spreadsheet or another tool	CSV, RFC 4180, canonical values	No
Something a human reads, prints, or files	PDF, fully localized and formatted	No

Human-facing exports localize at the edge like any other rendering (

value-objects-money-and-units

i18n-rtl-l10n

); machine formats never do. Copying the live database file directly is a valid backup only through the WAL-safe primitive in

persistence-drift

— never

File.copy

on an open database.

Tests that must exist

Round trip on a hostile fixture. Export → import into an empty database → export again → byte-identical. The fixture carries apostrophes, quotes, commas, embedded newlines, emoji, RTL text with bidi marks, whitespace-only strings, the largest supported number, a DST-ambiguous local instant, and a null-vs-empty pair.
```
'test1'
```
survives everything and proves nothing.
Idempotent re-import. Importing the same file twice produces the same state under merge-by-id — not duplicates.
Every refusal is a test: truncated file, flipped checksum byte,
```
schemaVersion
```
one higher than the app, an older
```
schemaVersion
```
that must migrate, empty payload, and a file that is not this format at all. Each asserts the typed failure and that the live database is byte-unchanged.
Failure leaves no artifact. Force a mid-write failure; assert no publishable file exists in the export directory.
CSV escaping. A cell containing
```
=cmd()
```
, one containing
```
a,b"c\nd
```
, and an RTL cell each survive a write→read round trip through a real parser.

Anti-patterns

CSV as the backup format — no envelope, no types, no versioning; it will be someone's only copy.
An unversioned export file — the first schema change makes every existing file either unreadable or, worse, silently misread.
Restoring straight into the live database — no rollback exists; a mid-import failure leaves a state nobody can describe.
Autoincrement rowids as export identity — re-import duplicates everything.
Formatting numbers or dates in a machine format — locale-dependent output that round-trips only on the machine that wrote it.
Building the export in a
String
/
StringBuffer
— fine for 100 rows, an OOM crash at 100k, on the cheapest device you support.
Writing directly to the shareable path — a crash mid-write publishes a truncated file that still looks like a backup.
Auto-uploading or auto-backing-up "for safety" — it converts an offline app into a data processor, changing the store privacy declaration and breaking the claim.
Trusting a picked file's extension or name — validate the envelope, not the path.
A generic "Import failed" message — the user cannot tell a wrong file from lost data; name the reason from the typed failure.
Silently merging when the user expected replace (or vice versa) — state the policy in the confirmation, in the same words as the code.

Definition of done

Backup and export are separate paths; export artifacts are refused by restore.
Every backup carries
```
formatVersion
```
,
```
schemaVersion
```
,
```
appVersion
```
,
```
exportedAtUtc
```
, and a payload checksum, in that header-first order.
Restore verifies the checksum, refuses a newer
```
schemaVersion
```
, and migrates an older payload through the same forward-only path as the database.
Restore imports into a staging database and publishes by rename; a failure leaves the live database byte-unchanged.
Machine formats carry canonical values and stable content ids only.
The merge-vs-replace policy is explicit in code and named in the confirmation UI.
Writes stream to a temp file and are published by atomic rename; failures delete the temp file and publish nothing.
Share/save goes through an injected Gateway on an explicit user action; nothing uploads automatically.
CSV output is RFC 4180 quoted and formula-injection escaped.
Export/restore return typed
```
Result
```
s with one failure per refusal reason.
Round-trip, idempotent-re-import, every-refusal, no-partial-artifact, and CSV escaping tests pass on a hostile fixture.

Related skills

```
persistence-drift
```
— the WAL-safe on-device snapshot primitive and the DAO layer the importer writes through; never
```
File.copy
```
a live database.
```
run-migration
```
— the forward-only path an older payload is migrated through.
```
error-handling-typed-results
```
— the
```
Result
```
/
```
Failure
```
spine and the never-lose-data guarantees this feature is the user-facing end of.
```
value-objects-money-and-units
```
— canonical storage (minor units, SI, UTC) that makes a machine format round-trip.
```
i18n-rtl-l10n
```
— localize-at-render, and why localized numerals never enter a file.
```
service-boundary-and-native
```
— the
```
ShareGateway
```
/file-picker seam and its fake.
```
release-and-store-shipping
```
— the privacy claims an export feature must not break.
```
testing-strategy
```
— hostile fixtures, property/round-trip tests, injected
```
Clock
```
.

References

RFC 4180 — Common Format and MIME Type for CSV Files: https://www.rfc-editor.org/rfc/rfc4180
Dart —
```
dart:io
```
```
IOSink
```
,
```
File.rename
```
: https://api.dart.dev/stable/dart-io/File-class.html
```
path_provider
```
— app directories: https://pub.dev/packages/path_provider
```
share_plus
```
— share sheet (incl.
```
sharePositionOrigin
```
): https://pub.dev/packages/share_plus
```
file_selector
```
— save/open dialogs: https://pub.dev/packages/file_selector
```
crypto
```
— SHA-256 for the payload checksum: https://pub.dev/packages/crypto
OWASP — CSV Injection: https://owasp.org/www-community/attacks/CSV_Injection

data-export-and-restore

NPX Install

Tags

SKILL.md Content