The Prisma migration chain began with an ALTER on the operations table, so
a fresh database could not be initialized from migrations alone (the first
migration failed with no table to ALTER). Separately, the login_token /
login_token_expires_at columns and index existed in schema.prisma and were
used at runtime by src/auth.ts (magic-link login) but were never created by
any migration, so a migrate-only database crashed with
"column users.login_token does not exist".
- Add a 0_init baseline migration that creates the base tables (the
pre-2025-12-12 shape), sorting before the first incremental migration so
the existing ALTER migrations apply cleanly on top.
- Add 20260601000000_add_login_token to create the missing magic-link
columns and index (IF NOT EXISTS, safe on installs that already have them
from a prior db push).
- Add migration_lock.toml (provider = postgresql), the standard companion to
a real migration baseline.
- Document baselining for existing databases: `migrate resolve --applied
0_init` for installs with migration history, and resolving the whole chain
for db-push installs with none (covers the Helm/Docker unattended paths).
- Add regression tests asserting the baseline exists and that every @map
column in schema.prisma stays backed by a migration (guards the drift class
that caused this bug, not just login_token).
Verified by replaying all migrations on a fresh Postgres: the chain applies
cleanly and the resulting schema matches schema.prisma exactly.
Closes#8187
- Tighten recovery guard to the idempotent drop-then-create shape (require
both DROP and CREATE INDEX CONCURRENTLY). A bare CREATE (20260511000000) is
now refused by gate, deterministically — matching its documented fail-loud
intent instead of relying on the CREATE erroring by accident. Reconciles
README with the committed migration + migration-sql.spec.ts.
- Escape single quotes in print_manual_recovery so the printed escape-hatch
commands are copy-pasteable for SQL containing quotes (e.g. the full-state
WHERE op_type IN ('SYNC_IMPORT', ...) migration).
- Fix MIGRATE_LOG temp-file leak across retry attempts.
- Add an in-script per-step timeout (with_timeout) so the Dockerfile CMD /
helm initContainer paths (no outer timeout) can't hang forever on a blocked
concurrent build; 124 fails loudly.
- Harden parse_failing_migration: sentence-anchored P3009 parse + reject
names outside the migration charset (path-traversal defence).
- Gate also accepts SQLSTATE 25001 (stable Postgres contract) not just the
localizable English message.
- MAX_ATTEMPTS -> documented tight bound (6); fail_loudly wording fixed
(was contradictory for the non-CONCURRENTLY case).
- Tests: bare-CREATE refusal, P3009 decoy-token parser hardening; trim
migration-sql.spec.ts to the architectural-invariant subset (no hardcoded
names) per 'test behavior not implementation'.
Full server suite: 36 files, 727 passed / 5 skipped.
prisma migrate deploy wraps each migration in a transaction; CONCURRENTLY
index migrations fail it (P3018/25001) and later deploys then stick (P3009).
Recovery was duplicated and migration-name-hardcoded in the host deploy.sh
and the in-image migrate-deploy.sh. The host script self-updates only via a
best-effort git pull, so a stale host deploy.sh had no recovery branch for a
new CONCURRENTLY migration and failed the deploy (the reported incident).
- migrate-deploy.sh: single, name-agnostic recovery. Parses the failing
migration from Prisma's own output, gates on the txn-block/P3009 signature
AND the migration's own SQL containing INDEX CONCURRENTLY, runs that SQL
out-of-band statement-by-statement, and only marks it applied if every
statement succeeded; otherwise fails loudly with manual steps. Bounded
retry loop; aborts instead of looping on re-failure.
- deploy.sh: ~290 lines of hardcoded host-side recovery removed; now invokes
the in-image scripts/migrate-deploy.sh (always version-locked to
prisma/migrations in the pulled image) and keeps only timeout/exit policy.
- tests: drive the script end-to-end via a fake npx (P3018, stuck P3009,
non-CONCURRENTLY refusal, statement-failure, re-failure abort, genuine
error passthrough, multi-migration chain); migration-sql.spec.ts updated
to the new contract.
- prisma/migrations/README.md: authoring rules the recovery relies on.
Design: docs/plans/2026-05-15-generic-concurrently-migration-recovery-design.md
* docs(sync): add super sync server perf plan
* perf(sync): implement supersync server perf phases
* fix(sync): bracket auth cache invalidation
* fix(sync): avoid empty replay state stringify
* fix(sync): harden supersync batch uploads
* fix super sync review findings
* fix(sync): guard payload bytes backfill rollout
* perf(sync): speed up payload_bytes backfill and index its scan
Raise the backfill batch size (DEFAULT 5->500, MAX 25->1000) so a
100M-row operations table backfills in minutes rather than tens of
hours. Add a CONCURRENTLY partial index on (user_id, id) WHERE
payload_bytes = 0: it drains to empty post-backfill so the boot-time
backfill self-check and the BOOL_OR quota probe stop doing a full
sequential scan to prove absence, and it makes the backfill's per-user
keyset paging a true index seek. Wire the new concurrent-index
migration into both deploy scripts' P3018 recovery path. Add
migration-SQL guard tests for the ADD COLUMN (metadata-only fast path)
and the new partial index.
* fix(sync): bound auth cache invalidation map and bracket every delete
The auth verification cache's invalidationVersions map grew one entry
per lifetime-invalidated user with no eviction (unbounded heap on a
long-lived single replica). Cap it at the same 10k LRU bound as the
entries map, re-inserting the just-invalidated user at the MRU tail so
the CAS race protection still holds for the only window that matters
(one DB round trip). Bracket the passkey/magic-link registration
cleanup deletes with pre+post invalidate to match the documented
convention, and invalidate on verifyEmail so a freshly-verified user
isn't denied for up to the cache TTL.
* perf(sync): skip the redundant exact replay-state measurement
The delta accounting is a proven over-estimate of the serialized state
size, so when the running bound stays within the cap the true size is
too and the final exact JSON.stringify is provably redundant. Skip it
in that case (still measure-and-throw whenever the bound does not prove
safety). This collapses the common small/incremental replay back to
zero expensive full stringifications, matching the old per-op loop
instead of regressing it. Name the entity-key JSON overhead constant
and document that assertReplayStateSize's return value is load-bearing.
* refactor(sync): split processOperationBatch into pipeline stages
Extract the 297-line batch upload method into a thin orchestrator plus
six named single-responsibility stage helpers (validate+clamp, intra-
batch dedupe, classify existing duplicates, conflict-detect, reserve
seq + insert, full-state clock). Behavior-preserving: every stage
writes terminal rejections into the shared results array by index and
the two empty-set guards short-circuit exactly as before. Also share
the timestamp clamp, the duplicate-op SELECT, and the merged
full-state clock persistence between the batch and legacy paths so
they cannot silently diverge.
* test(sync): pin batch error-code divergence and aggregate-once
Strengthen the intra-batch duplicate test to assert same-id /
different-content yields DUPLICATE_OPERATION (deliberate divergence
from the legacy INVALID_OP_ID), and document the divergence in the
plan. Replace the single-full-state aggregate test with two
full-state ops + a spy asserting _aggregatePriorVectorClock runs
exactly once and last-write-wins — the old test could not catch a
per-op-aggregate regression. Add a makeOp fixture factory. Correct
the plan's overstated replay-stringification numbers.
---------
Co-authored-by: copilot-swe-agent[bot] <198982749+Copilot@users.noreply.github.com>
Co-authored-by: johannesjo <1456265+johannesjo@users.noreply.github.com>
Two related improvements to snapshot/full-state handling:
- Persist the incoming op's vector clock as `user_sync_state.latest_full_state_vector_clock`
so downloads can skip the expensive aggregate scan when the snapshot clock is
still valid; falls back to the legacy aggregate when missing or invalid.
- Treat retried snapshot uploads (same opId) as idempotent successes instead of
409/DUPLICATE_OPERATION so a dropped response doesn't push clients into the
download-and-merge path. Per-namespace request dedup keeps ops and snapshot
caches isolated.
When a full-state op (SYNC_IMPORT/BACKUP_IMPORT) arrived from another
client, mergeRemoteOpClocks reset the local clock to a "minimal" form
but only preserved the current client's counter from the incoming op's
clock. If the current client had issued ops (e.g. GLOBAL_CONFIG) not
reflected in the incoming full-state op's clock, its counter was dropped,
causing subsequent ops to reuse the same counter value. Downstream clients
then saw these ops as EQUAL (duplicate) and skipped them silently.
Fix: take max(mergedClock[clientId], currentClock[clientId]) when
rebuilding the clock after a full-state op reset.
Also add __SP_E2E_BLOCK_WS_DOWNLOAD flag to WsTriggeredDownloadService
to allow E2E tests to block automatic WS-triggered downloads during
concurrent conflict scenarios.
Fix archive conflict test by blocking WS downloads on Client A during
the concurrent edit phase so it doesn't auto-receive B's rename via
WebSocket before archiving (restoring the intended conflict scenario).
Fix LWW singleton test to assert convergence rather than specific winner.
Fix renameTask helper to avoid Playwright/Angular re-render races.
Fix shepherd.js import paths that broke the Angular dev server build.
- Thread syncImportReason field from operation creation through compact
codec, sync providers, server database, and back to receiving clients
- Constrain server Zod schema to z.enum() matching client SyncImportReason
type for defense-in-depth validation
- Use !== undefined in codec for consistency with entityId/entityIds
- Rename piggybackedImport/incomingSyncImport to piggybackedFullStateOp/
incomingFullStateOp for accuracy
Existing passkeys were stored with double-encoded credential IDs due to
a bug where SimpleWebAuthn's credentialInfo.id (UTF-8 bytes of base64url
string) was stored directly instead of being decoded to raw bytes first.
This migration script converts existing passkeys from the old format
(ASCII bytes of base64url string) to the correct format (raw credential
ID bytes).
Run with: npx ts-node prisma/migrations/migrate-passkey-credentials.ts
Authentication changes:
- Add passkey (WebAuthn) as primary login method
- Add email magic link as fallback for devices without passkey support
- Remove password-based authentication entirely
New features:
- Passkey registration and login via @simplewebauthn/server
- Magic link login with 15-minute expiry tokens
- Passkey recovery via email link
- Self-hosted simplewebauthn-browser.min.js for reliability
Database changes:
- Add Passkey model for WebAuthn credentials
- Add PasskeyChallenge model for registration/auth challenges
- Add loginToken and loginTokenExpiresAt fields for magic links
- Add passkeyRecoveryToken fields for passkey recovery
UI changes:
- Login form: email + "Login with Passkey" + "Send Login Link"
- Register form: email + terms checkbox + "Register with Passkey"
- Consistent token display UI for both passkey and magic link login
- Remove password fields and forgot password flow
Security:
- CSP-compliant magic link redirect using external script
- Rate limiting on all auth endpoints
- Single-use magic link tokens
Security fixes implemented:
1. CRITICAL: Fix missing await in email verification (pages.ts:139)
- verifyEmail() was called without await, causing race condition
- Response was sent before verification completed
2. CRITICAL: Fix XSS vulnerability in password reset page (pages.ts)
- Added safeJsonForScript() to properly escape tokens in JS context
- JSON.stringify alone doesn't escape </script> sequences
- Now escapes <, >, & as unicode (\u003c, \u003e, \u0026)
3. CRITICAL: Fix XSS in privacy HTML template (server.ts)
- Added escapeHtml() function for all template interpolations
- Prevents XSS if environment variables contain malicious content
4. HIGH: Enable Content Security Policy (server.ts)
- CSP was disabled (contentSecurityPolicy: false)
- Now enabled with strict directives:
- default-src 'self', object-src 'none', frame-ancestors 'none'
5. HIGH: Block wildcard CORS in production (config.ts)
- CORS_ORIGINS=* with credentials is a security vulnerability
- Now throws error in production, warns in development
6. HIGH: Add password reset flow (auth.ts, api.ts, email.ts, schema.prisma)
- Secure token generation with crypto.randomBytes(32)
- 1-hour expiry, one-time use tokens
- Revokes all sessions on password reset
- Prevents email enumeration (same response for all cases)
- Rate limited: 5 requests/15min for forgot-password
- Rate limited: 10 requests/15min for reset-password
Test coverage:
- 45 new tests across 4 test files
- Tests for XSS prevention, CORS blocking, CSP headers
- Tests for password reset flow (API and unit level)
- Fixed pre-existing flaky boundary test in retention-config.spec.ts
Tombstones were used for tracking deleted entities but are no longer
needed with the operation log architecture. This removes:
- Tombstone table from Prisma schema
- Tombstone-related methods from SyncService
- Tombstone mocks and tests from all test files
- Database migration to drop tombstones table
The operation log now handles deletions through DEL operations,
making the separate tombstone tracking redundant.
Remove the parentOpId field from the operation log system. It was
designed for conflict resolution chain tracking but was never
implemented - the field was always undefined/null.
Client changes:
- Remove from Operation interface
- Remove from compact codec encoding/decoding
- Update tests
Server changes:
- Remove from Operation type and Zod validation
- Remove from persistence and download mapping
- Add database migration to drop the column
Add storage quota system to prevent single users from consuming
excessive disk space on the sync server.
Changes:
- Add storageQuotaBytes and storageUsedBytes fields to User model
- Add STORAGE_QUOTA_EXCEEDED error code for quota violations
- Add quota calculation and enforcement methods to SyncService
- Block uploads (POST /ops, POST /snapshot) when quota exceeded
- Allow downloads to continue when over quota (so users can delete data)
- Return storage usage/quota in GET /status response
Default quota: 100MB per user
- Add isPayloadEncrypted field to Zod OperationSchema (root cause fix)
- Add isPayloadEncrypted column to Prisma schema with migration
- Store and return isPayloadEncrypted in server sync service
- Remove debug logging from encryption services
- Remove potential key exposure from DecryptNoPasswordError
- Remove unnecessary console.log in fallback decryption
- Hide duplicate Advanced encryption fields when SuperSync selected
- Enable previously skipped encryption E2E test
- Add data/ folder to gitignore
- Removed `prisma/` from .gitignore
- Added `packages/super-sync-server/prisma/schema.prisma` to version control as it contains the essential database definition.
