persistence-drift

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Persistence — Drift / SQLite

持久化 — Drift / SQLite

The on-device store is the single source of truth: with no server, everything must rebuild from this DB alone after process death, reboot, or restore. Push invariants into the schema, confine Drift to one layer, make every mutation one durable transaction, and store canonical values only. Applies to any

lib/data/

Drift table, DAO, repository, connection setup, or backup.

Read the reference for the task at hand:

```
references/schema-and-daos.md
```
— audit-column mixin, STRICT/CHECK/FK invariants, canonical column types, the index +
```
EXPLAIN QUERY PLAN
```
gate, DAO↔repository split, files-on-disk-with-relative-paths for blobs.
```
references/backup-and-wal.md
```
— the checkpoint→vacuum→verify-by-reopen primitive, WAL rules, the backup-before-anything lesson, optional SQLCipher (key-first, assert-cipher, header-check).
```
references/persistence-without-drift.md
```
— the same discipline for a small app on plain JSON files (injected base dir, debounce + lifecycle flush, atomic writes, lenient decode).

Run

scripts/check-drift-confinement.sh

and

scripts/check-persistence-bans.sh

before a PR. Migrations and their tests: see

run-migration

设备端 store 是唯一的事实来源：没有 server，因此进程死亡、设备重启或恢复后，所有内容都必须仅依靠此 DB 重建。将不变量下沉到 schema，将 Drift 限制在单一层内，使每次 mutation 都成为一次持久化 transaction，并且只存储规范值。适用于 lib/data/ 中的任何 Drift table、DAO、repository、connection 配置或备份。

根据当前任务阅读对应的 reference：

```
references/schema-and-daos.md
```
— audit-column mixin、STRICT/CHECK/FK 不变量、规范 column 类型、index +
```
EXPLAIN QUERY PLAN
```
准入检查、DAO↔repository 分层，以及将 blob 以相对路径存放到磁盘上的规则。
```
references/backup-and-wal.md
```
— checkpoint→vacuum→重新打开验证的基础流程、WAL 规则、任何操作前先备份的经验法则，以及可选的 SQLCipher（先设置 key、断言 cipher、检查 header）。
```
references/persistence-without-drift.md
```
— 小型 app 使用普通 JSON 文件时仍应遵循同样的纪律（注入 base dir、debounce + lifecycle flush、原子写入、宽松 decode）。

提交 PR 前运行

scripts/check-drift-confinement.sh

和

scripts/check-persistence-bans.sh

。迁移及其测试：参见

run-migration

。

Non-negotiable rules

不可妥协的规则

Drift lives only in
lib/data/
; everything else sees value types.
```
package:drift
```
and
```
package:sqlite3
```
are imported nowhere else — a banned-import lint/grep enforces it. DAOs map rows to immutable value objects; no
```
Table
```
,
```
Companion
```
,
```
TableInfo
```
, or generated row class crosses the boundary. A feature reaching for a raw query is a layering break that also blocks pure testing of everything above it.
Put invariants in the schema, not at call sites. Tables are
```
STRICT
```
(no silent coercion); enumerable columns get
```
CHECK (col IN (...))
```
, ranges get
```
CHECK (qty BETWEEN 0 AND n)
```
/
```
CHECK (amount_minor >= 0)
```
, relations are foreign keys with an explicit
```
onDelete
```
, uniqueness is a (partial)
```
UNIQUE INDEX
```
. A corrupt row must be unrepresentable at the storage layer, not merely policed in Dart.
foreign_keys
and
synchronous
are set in
beforeOpen
/
setup
on EVERY open;
journal_mode = WAL
is set idempotently there too.
```
foreign_keys
```
and
```
synchronous
```
are per-connection and are not persisted in the file, so they must be re-asserted on every open.
```
journal_mode = WAL
```
, by contrast, is persisted in the database file header and survives across connections — but it is still set idempotently in setup so a freshly created or restored DB adopts it.
```
PRAGMA foreign_keys = ON
```
unconditionally (SQLite defaults it OFF and silently no-ops FK actions when off);
```
journal_mode = WAL
```
for concurrent durable reads;
```
synchronous = FULL
```
on any store holding non-regenerable user data (WAL+NORMAL "might rollback following a power failure"). Seeding, and only seeding, goes inside
```
if (details.wasCreated)
```
.
One
db.transaction
per mutation; every query inside awaited; persist before publish. A mutation that writes several rows (parent + dependents, or a debit + a credit) is all-or-nothing. A missing
```
await
```
inside
```
transaction(() async {
```
lets a query run after the transaction closes — Drift calls this data loss; it is a release blocker. The DAO
```
Future
```
resolves only after the durable commit; the committed write then makes the watched
```
.watch()
```
stream re-emit the new state on its own — never an optimistic pre-commit update, never a manual
```
state = …
```
republish, never "save later". (The write→UI-update rule is owned by
```
state-management-riverpod
```
.)
Store canonical values only; convert at the presentation edge. Money as integer minor units keyed to the real ISO-4217 exponent (never a
```
REAL
```
/
```
double
```
), other quantities as SI integers, true instants as UTC epoch millis. The local calendar day (anything that drives a day boundary) is a serial-day integer, never a
DateTime
instant — an instant reintroduces the DST/timezone off-by-one. No display strings, localized numerals, or formatted values in any column; switching locale/unit must leave stored rows byte-identical.
Derived state is recomputed on read, never stored as a second authority. Counts, streaks, running totals, histograms are pure folds over their source rows, computed by one
```
watch…
```
/query fold next to the data. A stored copy is a second source of truth that drifts and that any future sync must reconcile twice.
The repository is the single write path and the single source of truth. DAOs hold single-table queries; repositories own cross-table transactions and row→value-object mapping, and return a typed
```
Result<T, Failure>
```
for fallible work. Feature code depends on the repository abstraction, never on a DAO or Drift row.
Reads are scoped
.watch()
streams; pagination is keyset, not
OFFSET
. Never subscribe to an unscoped app-wide stream (it recomputes on every write); scope by owner/entity + time window. History uses
```
WHERE ts < :cursor ORDER BY ts DESC LIMIT n
```
;
```
OFFSET
```
degrades badly on large tables.
Blob bytes never live in SQLite. Store files on disk (app-private) with only a metadata row; persist the path relative to a base directory and resolve to absolute at read time — an absolute path dies on iOS reinstall/restore when the container UUID changes, and the row survives while the file renders blank with no error. BLOBs bloat the DB and slow every checkpoint and backup.
Back up via
wal_checkpoint(TRUNCATE)
+
VACUUM INTO
, then verify by reopen — never
File.copy
a live WAL DB. A raw copy of a WAL-mode DB captures a torn state across the
```
-wal
```
/
```
-shm
```
sidecars and is corrupt and unrestorable. A backup that was not re-opened and integrity-checked did not succeed. Detail in
```
references/backup-and-wal.md
```
.
Schema evolution is forward-only, append-only, and snapshot-guarded — and it is a separate ritual. Bump
```
schemaVersion
```
, add a new
```
stepByStep
```
step (never edit a shipped one), commit the schema snapshot, and ship no migration without a content-level test. That whole workflow lives in
```
run-migration
```
; this skill defines the tables it migrates.

Drift 只能存在于
lib/data/
；其他所有层只能看到值类型。
```
package:drift
```
和
```
package:sqlite3
```
不得在其他任何位置导入——由 banned-import lint/grep 强制执行。DAO 将数据行映射为不可变值对象；任何
```
Table
```
、
```
Companion
```
、
```
TableInfo
```
或生成的 row class 都不得越过边界。feature 直接使用 raw query 属于分层破坏，也会使其上层所有代码无法进行纯测试。
将不变量放在 schema 中，而不是调用点。 表必须是
```
STRICT
```
（不允许静默类型强制转换）；可枚举列使用
```
CHECK (col IN (...))
```
，范围使用
```
CHECK (qty BETWEEN 0 AND n)
```
/
```
CHECK (amount_minor >= 0)
```
，关系使用带明确
```
onDelete
```
的 foreign key，唯一性使用（partial）
```
UNIQUE INDEX
```
。损坏的行必须在 storage 层就无法表示，而不只是由 Dart 代码进行约束。
每次打开时，都必须在
beforeOpen
/
setup
中设置
foreign_keys
和
synchronous
；
journal_mode = WAL
也要在那里以幂等方式设置。
```
foreign_keys
```
和
```
synchronous
```
是 per-connection 属性，不会持久化到文件中，因此每次打开时都必须重新声明。相比之下，
```
journal_mode = WAL
```
会持久化在数据库文件头中，并可跨 connection 保留——但仍应在 setup 中以幂等方式设置，以确保新建或恢复的 DB 采用该模式。无条件执行
```
PRAGMA foreign_keys = ON
```
（SQLite 默认关闭，关闭时 FK 操作会静默失效）；使用
```
journal_mode = WAL
```
支持并发的持久化读取；对于存储不可再生成用户数据的 store，使用
```
synchronous = FULL
```
（WAL+NORMAL“在断电后可能会回滚”）。只有 seeding 操作可以放入
```
if (details.wasCreated)
```
中。
每次 mutation 使用一个
db.transaction
；transaction 内的每个 query 都必须 await；先持久化，再发布。写入多行的 mutation（父记录及依赖记录，或一笔借方与一笔贷方）必须做到全有或全无。在
```
transaction(() async {
```
内遗漏
```
await
```
，会让 query 在 transaction 关闭后继续运行——Drift 将其视为数据丢失；这是 release blocker。DAO 的
```
Future
```
仅在持久化 commit 完成后才 resolve；commit 的写入随后会由被监听的
```
.watch()
```
stream 自动重新发出新状态——绝不能进行乐观的 pre-commit 更新，绝不能手动重新发布
```
state = …
```
，也绝不能“稍后保存”。（write→UI 更新规则由
```
state-management-riverpod
```
负责。）
只存储规范值；在展示边界进行转换。 金额使用与实际 ISO-4217 exponent 对应的整数最小单位（绝不使用
```
REAL
```
/
```
double
```
），其他数量使用 SI 整数，真实时间点使用 UTC epoch millis。本地日历日（任何决定日边界的内容）是 serial-day 整数，绝不能是
DateTime
时间点——时间点会重新引入 DST/timezone 差一错误。列中不得存储显示字符串、本地化数字或格式化值；切换 locale/unit 后，存储的行必须保持逐字节一致。
派生状态在读取时重新计算，绝不作为第二事实来源存储。 计数、连续记录数、累计总计和直方图都是源行上的纯 fold，由数据旁一个
```
watch…
```
/query fold 计算。存储副本会成为第二个事实来源并发生漂移，而且未来的任何 sync 都必须对其协调两次。
repository 是唯一写入路径和唯一事实来源。 DAO 负责单表 query；repository 负责跨表 transaction 和 row→value-object 映射，并为可能失败的工作返回有类型的
```
Result<T, Failure>
```
。feature 代码依赖 repository 抽象，绝不依赖 DAO 或 Drift row。
读取使用带作用域的
.watch()
stream；分页使用 keyset，而非
OFFSET
。绝不订阅无作用域的应用级 stream（每次写入都会触发重新计算）；应按 owner/entity + time window 限定范围。历史记录使用
```
WHERE ts < :cursor ORDER BY ts DESC LIMIT n
```
；
```
OFFSET
```
在大型表上性能会严重下降。
BLOB 字节绝不存放在 SQLite 中。 将文件存放到磁盘（app-private），数据库中只保留 metadata row；将 path 持久化为相对于 base directory 的路径，并在读取时解析为绝对路径——当 container UUID 变化时，绝对路径会在 iOS 重新安装或恢复后失效，届时 row 仍然存在，但文件会无报错地显示为空白。BLOB 会让 DB 膨胀，并拖慢每次 checkpoint 和 backup。
通过
wal_checkpoint(TRUNCATE)
+
VACUUM INTO
备份，然后通过重新打开进行验证——绝不通过
File.copy
复制活动 WAL DB。直接复制 WAL-mode DB 会跨
```
-wal
```
/
```
-shm
```
sidecar 捕获到撕裂状态，导致文件损坏且无法恢复。未重新打开并执行完整性检查的备份不算成功。详情见
```
references/backup-and-wal.md
```
。
Schema 演进只能向前、只能追加，并受 snapshot 保护——而且它是独立流程。 提高
```
schemaVersion
```
，添加新的
```
stepByStep
```
step（绝不修改已经发布的 step），提交 schema snapshot，并且任何 migration 都必须配套内容级测试。整个流程由
```
run-migration
```
负责；本 skill 定义由其迁移的 table。

Confining Drift to one layer

将 Drift 限制在单一层

One

@DriftDatabase

, per-feature

@DriftAccessor

DAOs, repositories on top. Use the

NativeDatabase

FFI backend, never

sqflite

text

lib/data/
  db/        app_database.dart   (@DriftDatabase, schemaVersion, MigrationStrategy)
             connection.dart     (open + beforeOpen pragmas; optional SQLCipher key)
             tables/             one Table subclass per entity + audit mixin
  daos/      one @DriftAccessor per feature (single-table queries)
  repositories/  cross-table transactions; map rows -> value objects; expose .watch

When multi-package (workspace): the same boundary becomes a package (e.g.
packages/data
) that is the only pubspec depending on
drift
/
sqlite3
, enforced by a banned-import gate. For a single-package app the boundary is the
lib/data/
directory and the grep in
scripts/check-drift-confinement.sh
. Do not reach for a workspace to get this boundary — a directory + lint is enough.

使用一个

@DriftDatabase

，每个 feature 使用一个

@DriftAccessor

DAO，并在其上构建 repository。使用

NativeDatabase

FFI backend，绝不使用

sqflite

。

text

lib/data/
  db/        app_database.dart   (@DriftDatabase, schemaVersion, MigrationStrategy)
             connection.dart     (open + beforeOpen pragmas; optional SQLCipher key)
             tables/             one Table subclass per entity + audit mixin
  daos/      one @DriftAccessor per feature (single-table queries)
  repositories/  cross-table transactions; map rows -> value objects; expose .watch

多 package（workspace）时： 同一边界会成为一个 package（例如
packages/data
），它是唯一依赖
drift
/
sqlite3
的 pubspec，并由 banned-import gate 强制执行。对于单 package app，边界是
lib/data/
目录以及
scripts/check-drift-confinement.sh
中的 grep 检查。不要为了实现此边界而引入 workspace；目录加 lint 就足够了。

Schema: invariants in the table

Schema：将不变量放入 table

dart

// lib/data/db/tables/orders.dart — the ONLY layer importing package:drift.
import 'package:drift/drift.dart';

@DataClassName('OrderRow') // generated row type stays inside lib/data/
class Orders extends Table with AuditColumns {
  TextColumn get status =>
      text().check(status.isIn(const ['draft', 'placed', 'shipped', 'cancelled']))();
  IntColumn get amountMinor => integer()();          // integer minor units, never REAL
  TextColumn get currencyCode => text().withLength(min: 3, max: 3)();
  IntColumn get placedOnDay => integer().nullable()(); // LOCAL calendar day as serial int
  IntColumn get placedAtUtcMs => integer().nullable()(); // true instant, UTC epoch millis

  @override
  List<String> get customConstraints => const [
        'CHECK (amount_minor >= 0)',
        // a placed order must carry the day that drives its boundary
        "CHECK (status = 'draft' OR placed_on_day IS NOT NULL)",
      ];

  @override
  bool get isStrict => true; // no silent type coercion
}

AuditColumns

mixin on Table

) gives every table a stable text PK (a UUID — collision-free, merge-safe, stable across export),

createdAt

updatedAt

UTC-ms,

rowRevision

, and

isDeleted

deletedAt

for soft-delete. Stamp

updatedAt

rowRevision

through one shared write wrapper; filter

is_deleted = 0

in one shared base-query helper — analytics included. See

references/schema-and-daos.md

dart

// lib/data/db/tables/orders.dart — the ONLY layer importing package:drift.
import 'package:drift/drift.dart';

@DataClassName('OrderRow') // generated row type stays inside lib/data/
class Orders extends Table with AuditColumns {
  TextColumn get status =>
      text().check(status.isIn(const ['draft', 'placed', 'shipped', 'cancelled']))();
  IntColumn get amountMinor => integer()();          // integer minor units, never REAL
  TextColumn get currencyCode => text().withLength(min: 3, max: 3)();
  IntColumn get placedOnDay => integer().nullable()(); // LOCAL calendar day as serial int
  IntColumn get placedAtUtcMs => integer().nullable()(); // true instant, UTC epoch millis

  @override
  List<String> get customConstraints => const [
        'CHECK (amount_minor >= 0)',
        // a placed order must carry the day that drives its boundary
        "CHECK (status = 'draft' OR placed_on_day IS NOT NULL)",
      ];

  @override
  bool get isStrict => true; // no silent type coercion
}

AuditColumns

（

mixin on Table

）为每张表提供稳定的 text PK（UUID——无冲突、适合合并、在导出后保持稳定）、

createdAt

updatedAt

UTC-ms、

rowRevision

，以及用于软删除的

isDeleted

deletedAt

。通过一个共享的写入包装器更新

updatedAt

rowRevision

；在一个共享的基础 query helper 中统一过滤

is_deleted = 0

——analytics 也包括在内。参见

references/schema-and-daos.md

。

The connection: pragmas per open

Connection：每次打开都设置 pragma

dart

// lib/data/db/connection.dart
LazyDatabase openConnection() => LazyDatabase(() async {
      final dir = await getApplicationSupportDirectory(); // internal DB, not Documents
      final file = File(p.join(dir.path, 'app.sqlite'));
      return NativeDatabase.createInBackground(
        file,
        setup: (raw) {
          // foreign_keys & synchronous are PER-CONNECTION (not persisted) — set on every open.
          // WAL persists in the file header, but set it idempotently so a fresh/restored DB adopts it.
          raw.execute('PRAGMA journal_mode = WAL;');
          raw.execute('PRAGMA synchronous = FULL;');
          raw.execute('PRAGMA foreign_keys = ON;');
          raw.execute('PRAGMA busy_timeout = 5000;');
        },
      );
    });

MigrationStrategy.beforeOpen

, re-assert

foreign_keys = ON

and seed only under

if (details.wasCreated)

. During a migration, toggle

foreign_keys

outside any transaction (it is a no-op inside one) and run

PRAGMA foreign_key_check

afterwards.

dart

// lib/data/db/connection.dart
LazyDatabase openConnection() => LazyDatabase(() async {
      final dir = await getApplicationSupportDirectory(); // internal DB, not Documents
      final file = File(p.join(dir.path, 'app.sqlite'));
      return NativeDatabase.createInBackground(
        file,
        setup: (raw) {
          // foreign_keys & synchronous are PER-CONNECTION (not persisted) — set on every open.
          // WAL persists in the file header, but set it idempotently so a fresh/restored DB adopts it.
          raw.execute('PRAGMA journal_mode = WAL;');
          raw.execute('PRAGMA synchronous = FULL;');
          raw.execute('PRAGMA foreign_keys = ON;');
          raw.execute('PRAGMA busy_timeout = 5000;');
        },
      );
    });

在

MigrationStrategy.beforeOpen

中，重新声明

foreign_keys = ON

，并且仅在

if (details.wasCreated)

条件下执行 seed。迁移期间，必须在 transaction 外切换

foreign_keys

（在 transaction 内设置为静默无操作），并在之后运行

PRAGMA foreign_key_check

。

DAO + repository: one transaction, mapped to value objects

DAO + repository：一个 transaction，映射为值对象

A DAO holds single-table queries for one table (

window

is a plain-Dart

TimeWindow

VO of UTC-ms bounds — never a Flutter

DateTimeRange

in the data layer):

dart

// lib/data/daos/orders_dao.dart — the only door to SQL for the Orders table.
@DriftAccessor(tables: [Orders])
class OrdersDao extends DatabaseAccessor<AppDatabase> with _$OrdersDaoMixin {
  OrdersDao(super.db);

  /// Reactive, SCOPED, SINGLE-TABLE read. Rows map to value objects — callers see no Drift symbol.
  Stream<List<Order>> watchForAccount(String accountId, TimeWindow window) =>
      (select(orders)
            ..where((o) =>
                o.accountId.equals(accountId) &
                o.isDeleted.equals(false) & // always filter soft-deletes
                o.placedAtUtcMs.isBetweenValues(window.startMs, window.endMs))
            ..orderBy([(o) => OrderingTerm.desc(o.placedAtUtcMs)]))
          .watch()
          .map((rows) => rows.map(_toModel).toList());
}

The repository owns the cross-table transaction (a DAO scoped to

[Orders]

cannot reference

lineItems

— that is rule 7) and maps rows to value objects:

dart

// lib/data/repositories/order_repository.dart — the single write path across tables.
class OrderRepository {
  OrderRepository(this._db);
  final AppDatabase _db;

  /// ONE transaction, every query awaited, persist-before-publish.
  Future<void> place(Order order, LineItem item) {
    return _db.transaction(() async {
      await _db.into(_db.orders).insertOnConflictUpdate(order.toRow()); // await — required
      await _db.into(_db.lineItems).insert(item.toCompanion());          // await — required
    }); // Future resolves only after the durable commit; the watched stream re-emits — no manual republish.
  }
}

The repository maps

OrderRow

→ the immutable

Order

value object (integer minor units → a

Money

value object,

placedOnDay

serial → a calendar-day type) so no feature ever sees a canonical

int

or a Drift class. Keyset pagination:

..where((o) => o.placedAtUtcMs.isSmallerThanValue(cursor))..limit(n)

DAO 持有一个 table 的 single-table query（

window

是由 UTC-ms 边界组成的普通 Dart

TimeWindow

VO——数据层绝不能使用 Flutter

DateTimeRange

）：

dart

// lib/data/daos/orders_dao.dart — the only door to SQL for the Orders table.
@DriftAccessor(tables: [Orders])
class OrdersDao extends DatabaseAccessor<AppDatabase> with _$OrdersDaoMixin {
  OrdersDao(super.db);

  /// Reactive, SCOPED, SINGLE-TABLE read. Rows map to value objects — callers see no Drift symbol.
  Stream<List<Order>> watchForAccount(String accountId, TimeWindow window) =>
      (select(orders)
            ..where((o) =>
                o.accountId.equals(accountId) &
                o.isDeleted.equals(false) & // always filter soft-deletes
                o.placedAtUtcMs.isBetweenValues(window.startMs, window.endMs))
            ..orderBy([(o) => OrderingTerm.desc(o.placedAtUtcMs)]))
          .watch()
          .map((rows) => rows.map(_toModel).toList());
}

repository 负责跨表 transaction（限定为

[Orders]

的 DAO 无法引用

lineItems

——这就是规则 7），并将数据行映射为值对象：

dart

// lib/data/repositories/order_repository.dart — the single write path across tables.
class OrderRepository {
  OrderRepository(this._db);
  final AppDatabase _db;

  /// ONE transaction, every query awaited, persist-before-publish.
  Future<void> place(Order order, LineItem item) {
    return _db.transaction(() async {
      await _db.into(_db.orders).insertOnConflictUpdate(order.toRow()); // await — required
      await _db.into(_db.lineItems).insert(item.toCompanion());          // await — required
    }); // Future resolves only after the durable commit; the watched stream re-emits — no manual republish.
  }
}

repository 将

OrderRow

→ 不可变

Order

值对象（整数最小单位 →

Money

值对象，

placedOnDay

serial → calendar-day type）进行映射，因此任何 feature 都不会看到规范

int

或 Drift class。Keyset pagination：

..where((o) => o.placedAtUtcMs.isSmallerThanValue(cursor))..limit(n)

。

Riverpod wiring (state + DI)

Riverpod wiring（状态 + DI）

The database and each repository are provided; scoped

.watch()

streams map onto stream providers; derived views are computed providers. Keep the core persistence-agnostic — a repository interface with a single write path and derive-don't-store — and let Riverpod be the injection and consumption mechanism.

dart

final appDatabaseProvider = Provider<AppDatabase>((ref) {
  final db = AppDatabase(openConnection());
  ref.onDispose(db.close); // close the connection with the provider
  return db;
});

final orderRepositoryProvider = Provider<OrderRepository>(
  (ref) => OrderRepository(ref.watch(appDatabaseProvider)),
);

// Scoped stream — family carries the scope; autoDispose tears the watch down.
final ordersProvider = StreamProvider.autoDispose
    .family<List<Order>, OrderScope>((ref, scope) =>
        ref.watch(orderRepositoryProvider).watchForAccount(scope.accountId, scope.window));

Never open the DB in a widget; never expose a DAO through a provider. See

state-management-riverpod

for Notifier/AsyncNotifier discipline over these repositories.

数据库和每个 repository 都通过 provider 提供；带作用域的

.watch()

stream 映射为 stream provider；派生视图由 computed provider 计算。保持核心 persistence-agnostic——使用具有单一写入路径且采用 derive-don't-store 原则的 repository interface——并让 Riverpod 充当注入和消费机制。

dart

final appDatabaseProvider = Provider<AppDatabase>((ref) {
  final db = AppDatabase(openConnection());
  ref.onDispose(db.close); // close the connection with the provider
  return db;
});

final orderRepositoryProvider = Provider<OrderRepository>(
  (ref) => OrderRepository(ref.watch(appDatabaseProvider)),
);

// Scoped stream — family carries the scope; autoDispose tears the watch down.
final ordersProvider = StreamProvider.autoDispose
    .family<List<Order>, OrderScope>((ref, scope) =>
        ref.watch(orderRepositoryProvider).watchForAccount(scope.accountId, scope.window));

绝不要在 widget 中打开 DB；绝不要通过 provider 暴露 DAO。有关这些 repository 上的 Notifier/AsyncNotifier 规范，请参见

state-management-riverpod

。

Anti-patterns

反模式

A
package:drift
symbol leaking past
lib/data/
, or a widget/Notifier issuing a raw query — the row shape is a data-layer secret; map to value objects at the boundary.
A non-
STRICT
table, or validating enums/ranges at every call site instead of
```
CHECK
```
in the schema — a corrupt row must be impossible to write.
Assuming a pragma persists —
```
foreign_keys
```
/
```
synchronous
```
/a cipher key are per-connection; set them in
```
setup
```
/
```
beforeOpen
```
on every open.
```
PRAGMA foreign_keys
```
inside a transaction is a silent no-op.
synchronous = NORMAL
on a store of non-regenerable data — SQLite says WAL+NORMAL transactions may roll back after power loss.
A
DateTime
instant stored as the local day — reintroduces the DST/timezone rollover bug. Local day = serial int; instants = UTC ms.
A stored
count
/
total
/
streak
treated as authority — recompute from source rows; a stored copy drifts.
Splitting a mutation across transactions, dropping an
await
inside
transaction(() async {
, or an optimistic pre-commit update — each is a data-loss footgun; commit first and let the watched stream re-emit.
OFFSET
pagination, or an unscoped
.watch()
— both degrade and over-recompute; keyset + scoped streams.
BLOB bytes in SQLite, or an absolute media path — files on disk with a path relative to a base dir; absolute paths die on reinstall/restore.
File.copy
of a live WAL DB for a backup — torn, unrestorable; checkpoint +
```
VACUUM INTO
```
+ verify-by-reopen.
sqflite
backend, or
eraseDatabaseOnSchemaChange
reachable in release — use
```
NativeDatabase
```
FFI; that flag wipes real data.

package:drift
符号越过
lib/data/
，或 widget/Notifier 直接发起 raw query——row 结构是 data layer 的机密；必须在边界处映射为值对象。
table 未使用
STRICT
，或未在 schema 中使用
CHECK
，而是在每个调用点验证 enum/范围——损坏的行必须无法写入。
假设 pragma 会持久化——
```
foreign_keys
```
/
```
synchronous
```
/cipher key 是 per-connection 的；每次打开时都必须设置。在 transaction 内执行
```
PRAGMA foreign_keys
```
会静默无操作。
对存储不可再生成数据的 store 使用
synchronous = NORMAL
——SQLite 指出 WAL+NORMAL transaction 在断电后可能会回滚。
将
DateTime
时间点存储为本地日——会重新引入 DST/timezone rollover bug。本地日 = serial int；时间点 = UTC ms。
将存储的
count
/
total
/
streak
视为事实来源——应从源行重新计算；存储副本会漂移。
将 mutation 拆分到多个 transaction、遗漏
transaction(() async {
内的
await
，或进行乐观的 pre-commit 更新——每一种都是数据流失陷阱；先 commit，再让被监听的 stream 重新发出。
使用
OFFSET
分页，或使用无作用域的
.watch()
——两者都会导致性能下降和过度重新计算；应使用 keyset + scoped stream。
将 BLOB 字节存放在 SQLite 中，或使用绝对媒体路径——文件存放到磁盘，并使用相对于 base dir 的路径；绝对路径会在重新安装或恢复后失效。
通过
File.copy
复制活动 WAL DB 作为备份——会得到撕裂且无法恢复的数据；应使用 checkpoint +
```
VACUUM INTO
```
+ verify-by-reopen。
使用
sqflite
backend，或让
eraseDatabaseOnSchemaChange
在 release 版本中可达——应使用
```
NativeDatabase
```
FFI；该 flag 会清除真实数据。

Definition of done

完成标准

```
package:drift
```
/
```
package:sqlite3
```
appear only under
```
lib/data/
```
(grep green); DAOs return value objects; no Drift type crosses the boundary. Backend is
```
NativeDatabase
```
(FFI), not
```
sqflite
```
.
Every table is
```
STRICT
```
; enums are
```
CHECK (... IN (...))
```
; ranges are
```
CHECK
```
ed; relations are foreign keys with an explicit
```
onDelete
```
; uniqueness is a (partial)
```
UNIQUE INDEX
```
; hot queries have a matching index proven by
```
EXPLAIN QUERY PLAN
```
in a test.

journal_mode = WAL

synchronous = FULL

foreign_keys = ON

busy_timeout

are set in

setup

beforeOpen

on every open; seeding is only under

if (details.wasCreated)

Each mutation is exactly one
```
db.transaction
```
; every query inside is awaited; the write
```
Future
```
resolves before any UI update, and the watched stream re-emits (persist-before-publish, never optimistic, no manual republish).
Canonical storage only: integer minor units + ISO-4217 code, SI ints, UTC epoch ms; the local day is a serial int, never a
```
DateTime
```
; no formatted/localized values in any column.
Derived state is recomputed on read (one fold next to the data), never stored as authority.
Reads are owner/window-scoped
```
.watch()
```
streams mapped to value objects; pagination is keyset, never
```
OFFSET
```
.
Blob bytes are files on disk with a path relative to a base dir; no BLOB columns.
Backups use
```
wal_checkpoint(TRUNCATE)
```
+
```
VACUUM INTO
```
+ verify-by-reopen; no
```
File.copy
```
of a live DB.
Schema changes are paired with the
```
run-migration
```
ritual (bumped
```
schemaVersion
```
, append-only step, committed snapshot, content test).

```
package:drift
```
/
```
package:sqlite3
```
仅出现在
```
lib/data/
```
下（grep 通过）；DAO 返回值对象；任何 Drift type 都不得越过边界。Backend 为
```
NativeDatabase
```
（FFI），而不是
```
sqflite
```
。
每张表都是
```
STRICT
```
；enum 使用
```
CHECK (... IN (...))
```
；范围使用
```
CHECK
```
；关系使用带明确
```
onDelete
```
的 foreign key；唯一性使用（partial）
```
UNIQUE INDEX
```
；高频 query 有匹配的 index，并通过测试中的
```
EXPLAIN QUERY PLAN
```
验证。

每次打开时都在

setup

beforeOpen

中设置

journal_mode = WAL

、

synchronous = FULL

、

foreign_keys = ON

、

busy_timeout

；仅在

if (details.wasCreated)

条件下执行 seeding。

每次 mutation 恰好使用一个
```
db.transaction
```
；其中每个 query 都经过 await；write 的
```
Future
```
在任何 UI 更新前 resolve，并由被监听的 stream 重新发出（persist-before-publish，绝不乐观更新，也不手动重新发布）。
仅存储规范值：整数最小单位 + ISO-4217 code、SI int、UTC epoch ms；本地日是 serial int，绝不是
```
DateTime
```
；任何 column 中都不得存储格式化或本地化的值。
派生状态在读取时重新计算（数据旁只有一个 fold），绝不作为事实来源存储。
读取使用按 owner/window 限定作用域并映射为值对象的
```
.watch()
```
stream；分页使用 keyset，绝不使用
```
OFFSET
```
。
Blob 字节以文件形式存放到磁盘，并使用相对于 base dir 的路径；不得存在 BLOB column。
备份使用
```
wal_checkpoint(TRUNCATE)
```
+
```
VACUUM INTO
```
+ verify-by-reopen；不得通过
```
File.copy
```
复制活动 DB。
Schema 变更与
```
run-migration
```
流程配套（提高
```
schemaVersion
```
、仅追加 step、提交 snapshot、内容测试）。

Related skills

References

Reference

Drift docs: https://drift.simonbinder.eu/
Drift transactions: https://drift.simonbinder.eu/dart_api/transactions/
Drift migrations: https://drift.simonbinder.eu/Migrations/
SQLite STRICT tables: https://sqlite.org/stricttables.html
SQLite WAL: https://sqlite.org/wal.html
SQLite
```
VACUUM INTO
```
: https://sqlite.org/lang_vacuum.html#vacuuminto
SQLite foreign keys (per-connection pragma): https://sqlite.org/foreignkeys.html
```
path_provider
```
: https://pub.dev/packages/path_provider

Drift 文档：https://drift.simonbinder.eu/
Drift transaction：https://drift.simonbinder.eu/dart_api/transactions/
Drift migration：https://drift.simonbinder.eu/Migrations/
SQLite STRICT table：https://sqlite.org/stricttables.html
SQLite WAL：https://sqlite.org/wal.html
SQLite
```
VACUUM INTO
```
：https://sqlite.org/lang_vacuum.html#vacuuminto
SQLite foreign key（per-connection pragma）：https://sqlite.org/foreignkeys.html
```
path_provider
```
：https://pub.dev/packages/path_provider

persistence-drift

Original

Translation

Persistence — Drift / SQLite

持久化 — Drift / SQLite

Non-negotiable rules

不可妥协的规则

Confining Drift to one layer

将 Drift 限制在单一层

Schema: invariants in the table

Schema：将不变量放入 table

The connection: pragmas per open

Connection：每次打开都设置 pragma

DAO + repository: one transaction, mapped to value objects

DAO + repository：一个 transaction，映射为值对象

Riverpod wiring (state + DI)

Riverpod wiring（状态 + DI）

Anti-patterns

反模式

Definition of done

完成标准

Related skills

相关 skill

References

Reference