docs/plans/2026-05-14-super-sync-server-perf.md
Date: 2026-05-14 Status: proposal — phases sequenced so independent low-risk wins land first while the large upload-batching work proceeds in parallel.
Scope drawn from an audit of packages/super-sync-server/ covering: upload processing, snapshot generation/replay, quota accounting, encrypted-op handling, auth, and deployment defaults.
Revision note (post-review): Phases 0b, 1, 2 and 4 were tightened after a subagent review surfaced design issues in the original draft. Specifically: a forgotten
userSyncState.upsertfor first-time users, intra-batch duplicateop.idhandling, multi-entity (entityIds[]) op support, full-state-op aggregate-VC writes, and apg_column_sizevs.computeOpStorageBytesmismatch in the quota backfill. See each phase for the revised approach.
prisma/migrations/<ts>_add_encrypted_ops_partial_index/migration.sql
DROP INDEX CONCURRENTLY IF EXISTS "operations_user_id_server_seq_encrypted_idx";
CREATE INDEX CONCURRENTLY "operations_user_id_server_seq_encrypted_idx"
ON "operations"("user_id", "server_seq")
WHERE "is_payload_encrypted" = true;
snapshot.service.ts:1083 and :1114 count(*) WHERE is_payload_encrypted=true over a seq range. Today this scans the range and filters. With the partial index, the common case (no encrypted ops for the user) becomes an empty-index probe.operations_user_id_full_state_server_seq_idx already covers the op_type IN (...) filter for the findFirst at snapshot.service.ts:1103. (That findFirst also filters isPayloadEncrypted: false, which isn't in the partial-index predicate — currently a cheap index scan + flag recheck rather than a single probe. Not worth a second partial index.)EXPLAIN ANALYZE against a production-like distribution requires populated DB state. Run on staging after the migration applies, with 1M ops for a user holding 0-100 encrypted rows; run ANALYZE operations after the migration and record the expected plan. Also re-check the latest full-state findFirst path that filters isPayloadEncrypted: false; the existing full-state partial index does not include that predicate, so many encrypted full-state rows can still force scan-time rechecks.packages/super-sync-server/src/sync/services/snapshot.service.ts:879-900i % 1000 === 0 → JSON.stringify(state) with delta-based accounting, with a carve-out for full-state ops:
replayOpsToState (which is called once per replay batch from generateSnapshot), compute baseBytes = Buffer.byteLength(JSON.stringify(initialState), 'utf8') once. Track estimatedBytes = baseBytes and accumulatedDelta = 0.Buffer.byteLength(JSON.stringify(payload || ''), 'utf8') to accumulatedDelta. Overestimating is safe; deletes contribute 0.SYNC_IMPORT, BACKUP_IMPORT, or REPAIR, the op replaces state wholesale. The upper-bound counter would otherwise keep accumulating across the wipe and produce false "State too large" throws. After applying such an op, force a real measurement: estimatedBytes = Buffer.byteLength(JSON.stringify(state), 'utf8'), reset accumulatedDelta = 0.accumulatedDelta) when estimatedBytes + accumulatedDelta > 0.8 * MAX_REPLAY_STATE_SIZE_BYTES. Throw if the real value still exceeds the cap.:935-952 can fan one op into many; "delta per op = byteLength(payload)" still upper-bounds growth correctly (sum of fanned payloads ≥ state growth). No special handling needed.accumulatedDelta, so the bound does not stay pinned — a delete-heavy stream after a large import triggers at most a handful of extra measurements, and only when the imported base alone is already near the cap. Effectively unreachable for normal data; signed-delta accounting is not worth the added complexity.snapshot.service.spec.ts replay tests + add: 1500 small CREATE/UPDATE ops trigger zero full stringifications; a single SYNC_IMPORT triggers exactly one.storage-quota.service.ts:114-117 (the findUnique({ select: { snapshotData: true } }); .length is read at :120)findUnique for the same octet_length(snapshot_data) $queryRaw already used at snapshot.service.ts:187-191 (getCachedSnapshotBytes). Don't pull a multi-MB bytea blob back to Node just to read .length.calculateStorageUsage agrees with prepareSnapshotCache.bytes (both are the gzip output length).calculateStorageUsage actually runs. storage-quota.service.ts:84 describes it as "at most once per quota-cleanup event (rare per user)." If frequency is truly rare, this is a polish fix rather than a hot-path win.helm/supersync/values.yaml:178-184requests.memory: 128Mi, limits.memory: 256Mi.limits.memory to 1Gi; raise requests.memory to 512Mi. Add a comment naming the constants that drive the upper bound (MAX_SNAPSHOT_DECOMPRESSED_BYTES, MAX_REPLAY_STATE_SIZE_BYTES) and note the image-level NODE_OPTIONS=--max-old-space-size=896.docs/sync-and-op-log/ for any documentation citing 256Mi.The plan-as-originally-drafted underspecified four real cases. The revised design below makes each explicit.
processOperation into a batch primitiveFile: packages/super-sync-server/src/sync/sync.service.ts — caller at :459-467, worker at :634-790+, and the upsert/counter/syncDevice tail at :445-518.
New shape, in order, inside the existing tx:
Validate all ops in memory (validationService.validateOp) — no DB. Produce a decisions: Array<{ op, status: 'valid' | 'rejected', errorCode? }>.
Dedupe by op.id within the batch. If two ops in the same batch share an id, accept the first and reject subsequent ones as DUPLICATE_OPERATION — by id only, not content. This must happen before reserving sequence numbers; otherwise lastSeq advances for the duplicate and a server_seq gap is left when the row is silently skipped at insert time.
Deliberate divergence from the legacy per-op path (C4): for an intra-batch [A, A'] where the second op shares A's id but has different content, the legacy loop inserts A then catches A' at the DB and returns INVALID_OP_ID; the batch path returns DUPLICATE_OPERATION. Both are terminal rejections with no persisted row and no sequence gap, so all sync invariants hold — but the client treats them differently (DUPLICATE_OPERATION → marked synced silently; INVALID_OP_ID → hard rejection + error surfaced). This is an accepted behavior change while both paths coexist behind SUPERSYNC_BATCH_UPLOAD: the batch outcome (idempotent, non-noisy) is the preferred one. Pinned by the sync.service.spec.ts test "rejects an intra-batch same-id op as DUPLICATE_OPERATION even when its content differs". If the legacy path is retired, this divergence retires with it.
Prefetch existing op-id duplicates in one query:
const existing = await tx.operation.findMany({
where: { id: { in: validOpIds } },
select: {
/* fields needed by isSameDuplicateOperation */
},
});
Build Map<opId, existingRow>. For each op whose id is in the map, run isSameDuplicateOperation and audit as either DUPLICATE_OPERATION (idempotent retry) or INVALID_OP_ID (collision with a different op).
Prefetch latest-entity-op-per-(entityType, entityId) for every entity touched in the batch.
Multi-entity ops carry entityIds: string[] (not just entityId) — see sync.service.ts detectConflict (lines 140-156). The prefetch set must be:
const entityKeys = new Set<string>();
for (const op of batch) {
const ids = op.entityIds ?? (op.entityId ? [op.entityId] : []);
for (const id of ids) entityKeys.add(`${op.entityType}::${id}`);
}
Then one DISTINCT ON raw query (or one findMany with an in-app reduction) keyed on (entityType, entityId) ordered by serverSeq DESC, restricted to the touched set. Uses @@index([userId, entityType, entityId, serverSeq]).
Conflict detection in memory against the prefetched map, updating the map as each non-full-state op is accepted so intra-batch conflicts (two ops on the same entity inside one batch) resolve in order — matches today's serial semantics. Full-state ops (SYNC_IMPORT/BACKUP_IMPORT/REPAIR) bypass conflict detection, as in detectConflict lines 129-136; they are not entity-scoped (entityType: 'ALL', no entityId), so map invalidation is a no-op.
Reserve sequence numbers and ensure user_sync_state row exists in one round trip.
The original draft proposed tx.userSyncState.update(...) for the increment, which throws P2025 on a brand-new user (the row doesn't exist yet) and also leaves the existing tx.userSyncState.upsert at :445-449 redundantly grabbing the same row lock. Replace both with one statement:
INSERT INTO user_sync_state (user_id, last_seq)
VALUES ($userId, $delta)
ON CONFLICT (user_id) DO UPDATE
SET last_seq = user_sync_state.last_seq + $delta
RETURNING last_seq
lastSeq from the result is the new high-water mark; accepted[i].serverSeq = lastSeq - accepted.length + i + 1. Skip the statement entirely when accepted.length === 0 (and skip the rest of the batch tail).
Bulk insert with one tx.operation.createMany({ data: rows }). Do NOT pass skipDuplicates: true. Phase 1's correctness assumes the in-memory dedupe (step 2) and prefetch (step 3) have caught all duplicate ids; a row-level dup at insert time means our snapshot was stale and the right answer is to fail the batch with P2002 on the operations primary key → outer 40001-style retry, not to silently drop a row whose sequence number we already reserved.
Run _aggregatePriorVectorClock once if the batch contained any accepted full-state op (isFullStateOpType(op.opType)). This call (sync.service.ts:600-628) reads historical ops via jsonb_each_text LATERAL; it's not prefetchable. Use beforeServerSeq = lastAcceptedFullStateOp.serverSeq with WHERE server_seq < beforeServerSeq, so the aggregate includes prior history and accepted earlier-in-batch ops but excludes the full-state op itself and any later batch inserts. Persist latestFullStateSeq and latestFullStateVectorClock once. If a batch somehow contains two full-state ops, process them in batch-order — the last write wins, matching today's per-op-loop behavior.
Storage counter update (sync.service.ts:504-518) — keep the acceptedDeltaBytes accumulation, summing computeOpStorageBytes(op) over accepted ops. Preserve the isCleanSlate SET-vs-INCREMENT branching exactly.
syncDevice.upsert (:476-495) — per-batch already; stays as is.
Drop the per-op re-check at sync.service.ts:786-794. The safety it covered is delivered by the shared user_sync_state.lastSeq row-write, which forces concurrent batches to serialize: the second writer blocks on the row lock, then fails with 40001 (serialization failure) on commit. RR isolation alone does NOT provide this — PostgreSQL RR does not run full serializable snapshot isolation. The row-lock pattern is what makes the new design safe.
Already recorded as ARCHITECTURE-DECISIONS.md Decision #4 ("Batch Uploads Under RepeatableRead", line 121). Anyone proposing to remove the lastSeq increment from the hot path (e.g. sharded sequence assignment, distributed counters) must re-read that decision before doing so.
tests/sync.service.spec.ts. Mock surfaces use the existing hand-rolled vi.mock('../src/db', …) pattern (not Prisma $on('query'), which isn't wired). Use vi.spyOn(prisma.operation, 'findMany') etc. to assert call counts.
findMany for dup-id prefetch, 1 findMany (or $queryRaw) for entity prefetch, 1 INSERT ... ON CONFLICT for the sync-state row, 1 operation.createMany, 1 syncDevice.upsert, 1 UPDATE users counter, optionally 1 _aggregatePriorVectorClock.op.id: [A, A] — first accepted, second audited DUPLICATE_OPERATION, lastSeq advances by exactly 1, exactly one row inserted.[op1, op2] on the same entity — op1 wins, op2 rejected as concurrent.entityIds: [a, b, c] correctly drives the prefetch and conflict-detection.user_sync_state row → upload succeeds; the INSERT ... ON CONFLICT creates the row with last_seq = accepted.length._aggregatePriorVectorClock runs exactly once at the end and sees only rows with server_seq < fullState.serverSeq.P2034 / 40001 handles the loser. (This is unchanged in spirit but the failure mode shifts from "per-op re-check" to "shared row lock" — verify it still works.)[accept, reject, accept, reject, accept] → persisted rows have contiguous serverSeq = N, N+1, N+2, lastSeq advances by exactly 3, no gaps anywhere.OP_REJECTED, DUPLICATE_OPERATION, INVALID_OP_ID, CONFLICT_*).timestamp > now + maxClockDriftMs produces one persisted row with the clamped timestamp plus one additional TIMESTAMP_CLAMPED audit event. The clamp is not a rejection — both outcomes coexist for the same op.e2e/tests/sync/ (not e2e/sync/) — add one batch-of-50 upload test and assert latency drop vs. baseline.SUPERSYNC_BATCH_UPLOAD, default false for one release, true the next.src/config.ts:172-179): batchUpload = (SUPERSYNC_BATCH_UPLOAD === 'true') && (SUPERSYNC_PAYLOAD_BYTES_BACKFILL_COMPLETE === 'true'). The first condition without the second throws at startup with a message pointing operators at npm run migrate-payload-bytes. The DB-side complement is the startup self-check (see Cross-cutting): if batchUpload === true but operations still contains rows with payload_bytes = 0, the server refuses to boot. This closes the trust hole if an operator flips the env flag too early.sync.routes.ts:85, 601-604): MAX_OPS_PER_BATCH = SUPER_SYNC_MAX_OPS_PER_UPLOAD = 100 (from packages/shared-schema/src/supersync-http-contract.ts:5). Enforced before Zod parsing, returns HTTP 413 with errorCode: 'PAYLOAD_TOO_LARGE'. Same value also enforced inside the Zod schema as .max(SUPER_SYNC_MAX_OPS_PER_UPLOAD) so the OpenAPI contract stays in sync.TIMESTAMP_CLAMPED audit — never both terminal outcomes, never neither, never gapped sequence numbers.payload_bytes BIGINT NOT NULL DEFAULT 0 to operations.computeOpStorageBytes, not pg_column_sizeThe original draft proposed UPDATE ... SET payload_bytes = pg_column_size(payload) + pg_column_size(vector_clock). This is wrong.
pg_column_size(payload) returns the TOAST-compressed on-disk size — typically much smaller than the uncompressed value for large JSONB.computeOpStorageBytes(op) (in sync.const.ts), which returns Buffer.byteLength(JSON.stringify(payload ?? null), 'utf8') + Buffer.byteLength(JSON.stringify(vectorClock ?? {}), 'utf8') — i.e. the uncompressed UTF-8 length.These are different numbers by design. The file's own comment at storage-quota.service.ts:88-103 already calls out this mismatch as the historical bug. Backfilling with pg_column_size seeds drift instead of fixing it: the SUM-query and the increment-counter will disagree on every reconcile after deployment.
Correct backfill: stream operations rows per user in small batches from a one-time Node script, compute computeOpStorageBytes(row) per row, and batch the writes:
UPDATE operations
SET payload_bytes = v.bytes::bigint
FROM (VALUES ...) AS v(id, bytes)
WHERE operations.id = v.id
This preserves correctness while avoiding one network round trip per row. Run it as a separate migrate-payload-bytes.ts (mirroring the existing migrate-passkey-credentials.ts pattern) outside the Prisma migration framework — Prisma migrations run synchronously at startup, and a synchronous backfill on a 100M-row operations table would block the server for hours.
There is no clean SQL equivalent of Buffer.byteLength(JSON.stringify(payload)) over JSONB. octet_length(payload::text) is close but reads/detoasts every row, which is the very disk-I/O DoS the file's comment warns about.
payload_bytes per op using computeOpStorageBytes so the on-row value matches the increment-counter value the hot path is already adding. Both code paths are wired today: batch path at sync.service.ts:1115, legacy per-op path at :1393. This is the consistency calculateStorageUsage needs and the reason an old per-op insert deployed under SUPERSYNC_BATCH_UPLOAD=false does not seed drift while batch is rolled out.storage-quota.service.ts:109-112 with the CASE WHEN form already shipped at :97-120:
SELECT COALESCE(
SUM(
CASE
WHEN payload_bytes > 0 THEN payload_bytes
ELSE octet_length(payload::text)::bigint +
octet_length(vector_clock::text)::bigint
END
),
0
) AS total
FROM operations
WHERE user_id = $1
ELSE branch (option (b) in the design analysis) is the only candidate on the same UTF-8 scale as computeOpStorageBytes. Detoasting cost is bounded — only un-backfilled rows hit it, and the set drains monotonically to zero.pg_column_size entirely. No detoasting on backfilled rows, no I/O DoS.calculateStorageUsage and the cached storage_used_bytes counter agree to the byte.payload_bytes = 0 (pre-backfill) still produces a conservative fallback SUM. Hard-cut rollout on backfill completion: SUPERSYNC_BATCH_UPLOAD=true must require an operator-set completion flag after npm run migrate-payload-bytes finishes.Pick after profiling Phase 0d in production:
prepareSnapshotCache (snapshot.service.ts:175-184) with a streaming pipeline: a streaming JSON stringifier feeding zlib.createGzip(), collecting chunks into a final Buffer.concat(...).MAX_SNAPSHOT_DECOMPRESSED_BYTES AND event-loop blocking — not memory pressure alone. The 3-5× wall-clock regression vs native JSON.stringify makes this a memory-vs-latency trade.snapshotData is only used for byte-count accounting and gunzip-then-parse round-tripping (verified — no hash or content comparison anywhere in src/). So byte-for-byte stability is NOT required; round-trip correctness is. Add a property-based test: random state → stream-stringify-gzip → gunzip-parse → deep-equal original.JSON.stringify(state) into a worker_threads worker. Structured-cloning a large state into the worker temporarily doubles heap and can cost more event-loop time than the stringify it avoids. Worker offload only makes sense if replay and stringify both move to the worker.If 0d alone is sufficient in prod (no OOMs, no event-loop-blocking signals), defer this phase indefinitely.
packages/super-sync-server/src/auth-cache.ts, wired into verifyToken at auth.ts:114-158.Map<userId, { tokenVersion: number, isVerified: boolean, expiresAt: number }>. TTL 30s, max 10k entries.payload.tokenVersion === cached.tokenVersion && cached.isVerified → return valid.All tokenVersion: { increment: 1 } write sites plus account deletion must invalidate. Already wired in code with // AUTH_CACHE_INVALIDATION: comments adjacent to each write — kept here for future-PR awareness:
auth.ts:77/:80 (revokeAllTokens)auth.ts:96/:102 (replaceToken)auth.ts:108 (post-write second invalidate after the new token version is read back)passkey.ts:592/:616 (passkey recovery — pre- and post-write)passkey.ts:278 (unverified-user delete in registration flow)api.ts:210/:213/:215 (prisma.user.delete in account deletion — both pre- and post-delete invalidate)isVerified currently has no flip-to-zero path (passkey.ts:277 deletes unverified users rather than flipping the flag). The assumption is already documented at auth-cache.ts:80 — if a future code path adds verification revocation, the cache will serve stale "valid" for up to TTL.
helm/supersync/values.yaml:193 caps maxReplicas: 1, so in-process LRU is safe. Comment explicitly so a future multi-instance rollout doesn't accidentally introduce 30s revocation lag.verifyToken calls — expect ~10× p50 latency drop on warm cache.(opsInBatch, txDurationMs, dbRoundtrips) to uploadOps so we can quantify the win. Existing audit log handles per-op decisions; add a single batch-summary line.SUPERSYNC_PAYLOAD_BYTES_BACKFILL_COMPLETE=true flag is operator-trusted. To prevent a too-early flip, the server runs a cheap EXISTS (SELECT 1 FROM operations WHERE payload_bytes = 0 LIMIT 1) probe at startup whenever batchUpload === true and refuses to boot if any unbackfilled rows remain.calculateStorageUsage returns a hasUnbackfilledRows flag (computed from a BOOL_OR(payload_bytes = 0) over the same single scan). updateStorageUsage skips the users.storage_used_bytes write when the flag is true, so an approximate SUM-with-octet_length-fallback never replaces the exact incrementally-maintained counter mid-backfill. The forced-reconcile marker is preserved across the skip so the next call (after backfill completes) reconciles correctly.ARCHITECTURE-DECISIONS.md Decision #4 ("Batch Uploads Under RepeatableRead"); already merged.docs/sync-and-op-log/operation-log-architecture-diagrams.md §upload-path if it diagrams the per-op loop.CREATE INDEX CONCURRENTLY; migrate deploy can run the production workaround, but migrate dev wraps migration SQL in a transaction where CONCURRENTLY is forbidden.last_seq read that crosses the JavaScript boundary must hard-fail if it is not a safe integer instead of blindly calling Number(...).vi.mock('../src/db', …) with hand-rolled mocks, not Prisma $on('query') interceptors. Test-count assertions must use vi.spyOn on the mock surfaces.| Phase | Hot path affected | Expected win | Risk |
|---|---|---|---|
| 0a | Snapshot fast-path validation | Eliminates seq-range scan on encrypted-op count; full-state findFirst rechecks unchanged | very low |
| 0b | Snapshot replay | ~5–10× fewer full stringifications on large replays; zero (no regression) on the common small/incremental replay | low |
| 0c | Quota reconcile | Skips blob load (tens of MB) | very low |
| 0d | All routes (memory headroom) | Stops OOMs near snapshot cap | low (ops change) |
| 1 | Upload (every client batch) | ~5× fewer DB round trips on 25-op batch; shorter user_sync_state row lock; throughput-positive even under contention | medium-high |
| 2 | Quota reconcile (slow path) | Removes pg_column_size table scan; consistency between SUM and counter | medium (schema + backfill) |
| 3 | Snapshot upload memory | ~30-40% lower peak heap if streaming wins in profiling | medium |
| 4 | Auth on every request | ~10× p50 latency drop on warm cache | low |