Gregario sync / outbox schema drift

Three columns drifted. Only queued_at was on purpose.

Gregario is local-first: a ride is written to SQLite on the phone, queued in an outbox table, and pushed to Postgres later with last-write-wins. That means outbox is declared twice — once as an Expo SQLite migration, once as a Drizzle table — and nothing in the build compares the two. Eleven columns, three of them no longer agree. One divergence is deliberate and documented. The other two have been quietly changing what syncs.

Columns compared
11

One table, two declarations, no shared source of truth.

Drifted
3

Nullability, timestamp precision, one extra column.

Drifted on purpose
1

queued_at. Local telemetry, never on the wire.

The two declarations

11 columns · 3 marked

Line for line, in file order. The key in the left gutter is shared: 07 on the client is 07 on the server, so when the two panels stack on a narrow screen you can still pair them up. Long lines scroll inside their own panel. A drift is a break in the alignment, and every broken line is marked in place.

◆ Drift 1 — nullability ■ Drift 2 — timestamp precision ○ Drift 3 — column on one side only

Client — SQLite, Expo
apps/mobile/src/db/migrations/0004_outbox.sql
01CREATE TABLE outbox (
02 id TEXT PRIMARY KEY NOT NULL,
◆ 03 device_id TEXT,◆ Drift 1 · nullable here
04 entity TEXT NOT NULL,
05 entity_id TEXT NOT NULL,
06 op TEXT NOT NULL,
07 payload TEXT NOT NULL,
08 attempts INTEGER NOT NULL DEFAULT 0,
09 last_error TEXT,
■ 10 updated_at INTEGER NOT NULL, -- epoch milliseconds■ Drift 2 · millisecond
11 synced_at INTEGER, -- epoch milliseconds
○ 12 queued_at INTEGER NOT NULL DEFAULT (unixepoch() * 1000)○ Drift 3 · client only
13);
Server — Drizzle, Postgres
apps/api/src/db/schema/outbox.ts
01export const outbox = pgTable("outbox", {
02 id: uuid("id").primaryKey(),
◆ 03 deviceId: text("device_id").notNull(),◆ Drift 1 · NOT NULL there
04 entity: text("entity").notNull(),
05 entityId: uuid("entity_id").notNull(),
06 op: text("op").notNull(),
07 payload: jsonb("payload").notNull(),
08 attempts: integer("attempts").notNull().default(0),
09 lastError: text("last_error"),
■ 10 updatedAt: timestamp("updated_at", { withTimezone: true, precision: 0 }).notNull(),■ Drift 2 · whole seconds
11 syncedAt: timestamp("synced_at", { withTimezone: true, precision: 3 }),
○ 12 · no counterpart○ Drift 3 · absent by design
13});

What each drift costs

2 to fix · 1 to keep

Ordered by what it is doing to real rides right now, not by how old the divergence is.

Drift 1 — device_id is nullable on the phone and NOT NULL on the server

Fix · line 03

The device id is read out of SecureStore asynchronously at launch. A ride started in the first few hundred milliseconds after a cold start queues its first rows with device_id = NULL. SQLite accepts them. The push then fails the whole batch on the Postgres NOT NULL, the retry loop treats any non-2xx as “still offline”, and the rows stay queued forever with attempts climbing.

Why it is silent: the failure looks exactly like a tunnel. Nothing surfaces because the outbox has no ceiling on attempts and no alert on queue age — the only visible symptom is a queue that never reaches zero on a handful of devices. Fix on the client, not the server: the server's constraint is the correct one.

Drift 2 — updated_at keeps milliseconds locally, whole seconds on the server

Fix · line 10

precision: 0 rounds to whole seconds on insert, so two edits 300 ms apart can arrive at the server with identical updated_at. Last-write-wins compares with a strict >, an equal timestamp is not greater, and the incoming write loses. The newer edit is discarded without an error, a log line or a conflict record.

The tell that this was a slip: synced_at one line below is precision: 3. Both columns store the same kind of value from the same clock; only one of them was changed. Also worth fixing: the tie-break is currently whatever order rows arrived in, which is not a decision anyone made.

Drift 3 — queued_at exists only on the client

By design · line 12

This is the intentional one. queued_at measures how long a row sat before it synced; it feeds the queue-latency readout on the in-app debug screen and is meaningless on the server, where the equivalent information is the request timestamp. It is not an oversight and it should not be mirrored.

What keeps it safe: buildBatch() names its columns explicitly rather than selecting everything, so queued_at cannot reach the wire. That is the only thing standing between a deliberate local column and an unknown-field rejection, and it is one careless SELECT * away from stopping being true.

What to change

Two of the three want a migration; the third wants a test. In order:

  1. Add NOT NULL to device_id in a new client migration and backfill the queued NULL rows from SecureStore on next launch. Leave the server alone.
  2. Change the server's updated_at to precision: 3 so it matches synced_at and the phone, then make the last-write-wins tie-break explicit instead of leaving it to arrival order.
  3. Keep queued_at, and add a test that asserts the push projection lists its columns by name, so the deliberate drift stays deliberate.

None of this is urgent in the sense of an outage. All of it is the kind of divergence that gets harder to see the longer the two files live apart, which is the argument for generating one declaration from the other before there is a fourth.