The latest updates on your projects. Learn more about Vercel for GitHub.

Project	Deployment	Actions	Updated (UTC)
stack-auth-hosted-components	Ready	Preview, Comment	Mar 18, 2026 4:48pm
stack-backend	Ready	Preview, Comment	Mar 18, 2026 4:48pm
stack-dashboard	Ready	Preview, Comment	Mar 18, 2026 4:48pm
stack-demo	Ready	Preview, Comment	Mar 18, 2026 4:48pm
stack-docs	Ready	Preview, Comment	Mar 18, 2026 4:48pm

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

Greptile Summary

This PR extends the ClickHouse / external-DB sync pipeline to cover two new entity types: project-level user permissions (ProjectUserDirectPermission) and notification preferences (UserNotificationPreference). It follows the established sync pattern used by existing tables: adding sequenceId / shouldUpdateSequenceId columns to both Postgres models, registering new sequencer CTEs, wiring withExternalDbSyncUpdate into the CRUD handlers, recording soft-deletes via DeletedRow before cascade deletes, and providing full ClickHouse table + view + row-level-security definitions alongside Postgres external-DB sync mappings.

Key changes:

• schema.prisma + migration: adds nullable sequenceId and boolean shouldUpdateSequenceId (default true) to both tables with appropriate indexes.
• sequencer/route.ts: two new FOR UPDATE SKIP LOCKED CTEs that assign global sequence IDs to unsynced rows.
• external-db-sync.ts: new ExternalDbSyncTarget union variants and recordExternalDbSyncDeletion handlers; new bulk-deletion helpers recordExternalDbSyncProjectPermissionDeletionsForUser and recordExternalDbSyncNotificationPreferenceDeletionsForUser.
• permissions.tsx / crud.tsx / notification-preference/crud.tsx: sync hooks wired into grant, revoke, and user-delete paths.
• db-sync-mappings.ts: full sync mapping definitions for both tables.
• clickhouse-migrations.ts: new ClickHouse base tables, views, grants, and row-isolation policies.
• E2E tests for Postgres and ClickHouse sync of both new entity types.

Issues found:

• In db-sync-mappings.ts, the DeletedRow union branch for notification_preferences casts notificationCategoryId to ::uuid in both internalDbFetchQuery variants, while the active-rows branch returns it without a cast and the target column is text. This is inconsistent and will throw a runtime Postgres error if any category ID is not a valid UUID.
• run-cron-jobs.ts replaces a deliberate 0–120 s randomized Vercel cron jitter with a fixed 1 s wait and removes the explanatory comment, which may cause thundering-herd pressure on the database when multiple Vercel instances fire simultaneously.
• waitForSyncedNotificationPreferenceDeletion is defined in test utils but never invoked, leaving the notification preference deletion sync path without test coverage.

Confidence Score: 3/5

• Mostly safe to merge, but contains a type-cast bug that will surface as a runtime error when deleted notification preferences are synced, and an undocumented aggressive polling change.
• The overall sync pattern is solid and mirrors prior implementations, and the migration is safe on populated tables. However, the ::uuid cast for notificationCategoryId in the deleted-row fetch branches is semantically wrong (target is text) and will cause Postgres runtime errors if any category ID is not UUID-formatted. The cron wait reduction from randomized 0–120 s to 1 s removes deliberate Vercel jitter protection without documentation. The missing deletion test also leaves a code path unverified.
• packages/stack-shared/src/config/db-sync-mappings.ts (incorrect ::uuid cast at lines ~1965 and ~2001) and apps/backend/scripts/run-cron-jobs.ts (aggressive polling change).

Important Files Changed

Sequence Diagram

sequenceDiagram
    participant API as API Handler
    participant PG as Postgres (internal)
    participant SEQ as Sequencer (cron)
    participant SYNC as ExternalDB Sync Worker
    participant EXT as External DB (Postgres / ClickHouse)

    note over API,EXT: Grant / Update flow
    API->>PG: upsert ProjectUserDirectPermission<br/>withExternalDbSyncUpdate → shouldUpdateSequenceId=true
    API->>PG: upsert UserNotificationPreference<br/>withExternalDbSyncUpdate → shouldUpdateSequenceId=true

    SEQ->>PG: CTE: SELECT id WHERE shouldUpdateSequenceId=TRUE FOR UPDATE SKIP LOCKED
    SEQ->>PG: UPDATE SET sequenceId=nextval(), shouldUpdateSequenceId=FALSE
    SEQ->>SYNC: enqueueExternalDbSyncBatch(tenancyIds)

    SYNC->>PG: internalDbFetchQuery (active rows UNION deleted rows)<br/>WHERE sequence_id > last_synced
    SYNC->>EXT: externalDbUpdateQuery (upsert / delete row + update _stack_sync_metadata)

    note over API,EXT: Revoke / Delete flow
    API->>PG: recordExternalDbSyncDeletion → INSERT into DeletedRow<br/>(ProjectUserDirectPermission or UserNotificationPreference)
    API->>PG: DELETE from source table (cascade or explicit)

    SEQ->>PG: CTE: SELECT id FROM DeletedRow WHERE shouldUpdateSequenceId=TRUE
    SEQ->>PG: UPDATE DeletedRow SET sequenceId=nextval()
    SEQ->>SYNC: enqueueExternalDbSyncBatch(tenancyIds)

    SYNC->>PG: internalDbFetchQuery (deleted rows branch)
    SYNC->>EXT: externalDbUpdateQuery (DELETE from target table)

_{Last reviewed commit: "clickhouse sync proj..."}