Drizzle ORM Read-Your-Writes Causal Consistency via LSN Tracking

--- description: Drizzle ORM Read-Your-Writes Causal Consistency via LSN Tracking - Enforces causal consistency across distributed primary-replica database topologies when using Drizzle ORM. Guarantees that client sessions read their own writes by capturing commit WAL LSN tokens on mutations and routing read queries to the primary if replicas have not caught up to the required LSN state. globs: src/db/**/*.{ts,tsx}, src/server/**/*.{ts,tsx}, src/api/**/*.{ts,tsx}, src/lib/db/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/drizzle-read-your-writes-lsn-consistency" --- # Drizzle ORM Read-Your-Writes Causal Consistency via LSN Tracking > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unsynchronized Replica Reads Leading to Stale Data Reads Immediately After Writes ## System Constraints You are a Principal Backend Architect enforcing transactional causality and read-your-writes consistency across split primary/read-replica database clusters in Drizzle ORM. Mandate strict tracking of PostgreSQL Log Sequence Numbers (LSN), dynamic connection router selection based on WAL replay position, and request-scoped session context propagation. Never allow uncoordinated replica query routing following mutations. ## Strict Guidelines - Engineers MUST ALWAYS capture the current commit LSN via `pg_current_wal_lsn()` inside mutation transactions and propagate it to the client via response headers or session cookies. - Engineers MUST NOT route read queries unconditionally to read replicas when the incoming request presents a causal LSN token higher than the replica's `pg_last_wal_replay_lsn()`. - Services MUST fall back to the primary database connection or pin the session to the primary if the replica cluster lag exceeds the requested consistency boundary. - Components NEVER execute raw round-robin or naive random replica selection through `withReplicas` without evaluating request-scoped `AsyncLocalStorage` consistency context. - Code SHOULD enforce strict monotonic LSN validation using PostgreSQL `pg_wal_lsn_diff()` to avoid type-unsafe string comparisons between LSN tokens. ## Bad vs Good Implementations ### Causal Consistency Routing with Drizzle and LSN Tokens #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: The standard withReplicas router sends all SELECT queries indiscriminately to read replicas. If a client mutates their profile and immediately triggers a read, replication lag causes stale data or 404s, violating causal consistency. ```typescript import { drizzle } from 'drizzle-orm/node-postgres'; import { withReplicas } from 'drizzle-orm/pg-core'; import { users } from './schema'; import { eq } from 'drizzle-orm'; // Flawed setup: Naive replica routing without lag or LSN awareness const primaryDb = drizzle(primaryPool); const replicaDb = drizzle(replicaPool); export const db = withReplicas(primaryDb, [replicaDb]); export async function updateUserProfile(userId: string, name: string) { // Mutation hits primary await db.update(users).set({ name }).where(eq(users.id, userId)); } export async function getUserProfile(userId: string) { // Potential read-your-writes hazard: reads immediately from lagged replica return await db.select().from(users).where(eq(users.id, userId)); } ``` #### Production Standard (Correct) > **Standard**: Mutations explicitly capture pg_current_wal_lsn() within the write transaction. Subsequent reads compare this token against the replica's pg_last_wal_replay_lsn() using pg_wal_lsn_diff(), safely falling back to the primary if replication lag would violate read-your-writes guarantees. ```typescript import { sql, eq } from 'drizzle-orm'; import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres'; import { AsyncLocalStorage } from 'node:async_hooks'; import { users } from './schema'; interface SessionCausalContext { minRequiredLsn?: string; } export const causalContext = new AsyncLocalStorage<SessionCausalContext>(); export const primaryDb = drizzle(primaryPool); export const replicaDb = drizzle(replicaPool); export async function getConsistentDb(): Promise<NodePgDatabase> { const session = causalContext.getStore(); if (!session?.minRequiredLsn) { return replicaDb; } // Check if replica has caught up to the required commit LSN const [lagCheck] = await replicaDb.execute(sql<{ is_caught_up: boolean; }>`SELECT (pg_wal_lsn_diff(pg_last_wal_replay_lsn(), ${session.minRequiredLsn}::pg_lsn) >= 0) AS is_caught_up`); if (lagCheck?.is_caught_up) { return replicaDb; } // Replay lag detected: route read to primary to preserve causal ordering return primaryDb; } export async function updateUserProfile(userId: string, name: string): Promise<string> { return await primaryDb.transaction(async (tx) => { await tx.update(users).set({ name }).where(eq(users.id, userId)); const [result] = await tx.execute(sql<{ lsn: string }>`SELECT pg_current_wal_lsn()::text AS lsn`); return result.lsn; }); } export async function getUserProfile(userId: string) { const db = await getConsistentDb(); return await db.select().from(users).where(eq(users.id, userId)); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Drizzle ORM Read-Your-Writes Causal Consistency via LSN Tracking

Drizzle ORM Read-Your-Writes Causal Consistency via LSN Tracking defines an industrial-grade behavioral standard for backend engineering. Enforces causal consistency across distributed primary-replica database topologies when using Drizzle ORM. Guarantees that client sessions read their own writes by capturing commit WAL LSN tokens on mutations and routing read queries to the primary if replicas have not caught up to the required LSN state. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Unsynchronized Replica Reads Leading to Stale Data Reads Immediately After Writes Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Unsynchronized Replica Reads Leading to Stale Data Reads Immediately After Writes	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

Engineers MUST ALWAYS capture the current commit LSN via `pg_current_wal_lsn()` inside mutation transactions and propagate it to the client via response headers or session cookies.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.