Data and migrations

SQLite is the durable product truth for an ordinary App. One native database owner is shared by isolated Action and View Guests, so data survives Guest eviction without creating competing embedded SQLite connections.

Fresh install uses the final schema

schema.sql describes the complete current shape, not a historical sequence. A new installation executes it once and sets the runtime-owned user_version toschemaVersion after successful validation.

CREATE TABLE events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  kind TEXT NOT NULL,
  detail TEXT,
  created_at INTEGER NOT NULL
);

CREATE INDEX events_created_at ON events(created_at);

Writes belong to Actions

PocketPi.data.transaction(() => {
  PocketPi.data.query(
    "INSERT INTO events(kind, detail, created_at) VALUES(?, ?, ?)",
    ["refresh", "completed", Math.floor(Date.now() / 1000)],
  );
});

transaction() owns BEGIN IMMEDIATE, COMMIT andROLLBACK. It calls the native App commit hook only after SQLite commits. A thrown error rolls back and does not increment the App revision.

Views read through bounded Projections

const history = PocketPi.projection.many(
  `SELECT id, kind, detail, created_at
   FROM events
   ORDER BY id DESC
   LIMIT $limit`,
  () => ({ "$limit": 20 }),
  (rows) => model.update({ events: rows }),
);

A Projection runs when registered and again when the foreground View sees a newer App revision. It should return only the rows and columns the current View needs. Closed Views do not poll, and a normal frame with no revision change performs zero SQLite queries.

Revision is invalidation, not reactive data

The revision is a monotonic in-memory counter. It does not contain changed rows and it is not a SQLite watch event. Multiple successful commits before the next visible frame coalesce into one Projection refresh. If another commit races that refresh, the newer revision remains stale and is picked up on the following frame.

Advance schemaVersion only for shape changes

version identifies the source release shown to people. schemaVersionidentifies SQLite compatibility. A code/View-only update changes version but keeps the same schemaVersion.

// app.json
"version": "1.2.0",
"schemaVersion": 2

Add one file per forward step

-- migrations/2.sql
ALTER TABLE events ADD COLUMN source TEXT;
CREATE INDEX events_source ON events(source);

• migrations/2.sql moves schema 1 to 2.
• migrations/3.sql moves schema 2 to 3.
• An update from 1 to 3 must contain both steps.
• Downgrades and missing intermediate steps are rejected before live mutation.
• Do not include transaction control or set PRAGMA user_version.

Update rehearsal and recovery

The runtime copies the quiescent database, applies every candidate migration, then evaluates candidate Actions and View against that copy. Only after rehearsal succeeds does it run the same steps in one live transaction and swap source. If power is lost after physical approval,.update/release is the recovery signal and boot completes the interrupted update.

App data has one owner and one mutation path. Do not open an independent SQLite connection, write from view.js, or keep the only copy of durable state in View.state.