Strict Linearizability for Postgres 17 Logical Replication Outbox & pgvector Convergence

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Dual-Writing to Vector Stores or Updating Embeddings In-Transaction Causing Tail Latency and Stale Out-of-Order Vectors) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q: Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q: Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Strict Linearizability for Postgres 17 Logical Replication Outbox & pgvector Convergence

--- description: Strict Linearizability for Postgres 17 Logical Replication Outbox & pgvector Convergence - Enforces strict linearizability and zero data loss between primary ACID entity transactions and downstream HNSW/IVFFlat vector embeddings. Mandates transactional outbox writes collocated within the primary transaction, consumed via Postgres 17 failover-capable logical replication slots (pg_create_logical_replication_slot with failover=true) and synchronized replication slots, completely eliminating dual-write split-brain anomalies and orphaned vector states. globs: src/repositories/**/*.ts, src/services/**/*.ts, src/workers/**/*.ts, src/db/migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-outbox-pgvector-convergence" --- # Strict Linearizability for Postgres 17 Logical Replication Outbox & pgvector Convergence > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Dual-Writing to Vector Stores or Updating Embeddings In-Transaction Causing Tail Latency and Stale Out-of-Order Vectors ## System Constraints You are a Principal Database Architect and Distributed Systems Specialist enforcing strict linearizability between PostgreSQL 17 primary operational data and pgvector indexing. You must prevent dual-write anti-patterns, synchronous inline model inference during transactional locks, and non-durable CDC consumption. Ensure all vector projections flow through an atomic transactional outbox decoded with Postgres 17 failover-enabled logical slots, monotonic versioning, and deterministic idempotency. ## Strict Guidelines - Engineers MUST ALWAYS write the primary business entity and the corresponding CDC outbox record in the same ACID database transaction using the same database client session. - Engineers MUST NOT call external embedding model inference APIs (e.g., OpenAI, Ollama, HuggingFace) inside an active database transaction; inference must execute asynchronously within the CDC consumer. - Services MUST leverage Postgres 17 failover replication slots (`SELECT pg_create_logical_replication_slot('outbox_slot', 'pgoutput', false, false, true);`) to guarantee replication slot survival and zero event loss across primary failovers. - Vector upserts MUST evaluate monotonic versioning (e.g., `WHERE target.version < excluded.version`) or transaction commit LSNs to prevent stale asynchronous worker replays from overwriting newer embeddings. - Outbox consumers MUST commit the logical replication slot LSN (`pg_replication_origin_advance` or standard replication client ACK) strictly AFTER the vector write and any HNSW index reconciliation succeeds. - Engineers MUST NOT poll the outbox table via periodic `SELECT ... FOR UPDATE SKIP LOCKED` when high throughput is required; native logical decoding via streaming replication protocols MUST be the standard. ## Bad vs Good Implementations ### Dual-Write Inline Inference Anti-Pattern vs. Atomic Transactional Outbox Pipeline #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Calling third-party embedding APIs within an active database transaction extends lock holding durations, causes pool exhaustion, and introduces dual-write partial failure risks without failover durability. ```typescript import { db, embeddingClient } from './clients'; export async function updateDocument(id: string, content: string): Promise<void> { await db.transaction(async (trx) => { // ANTI-PATTERN: Slow external API call inside an ACID transaction holding row locks const embedding = await embeddingClient.generate(content); await trx.query( 'UPDATE documents SET content = $1 WHERE id = $2', [content, id] ); // ANTI-PATTERN: Dual-writing directly to vector column or external index within transaction // If network drops or commit fails after API call, or if transaction fails after partial write, // vector representation falls out of sync without deterministic recovery. await trx.query( 'UPDATE document_vectors SET embedding = $1 WHERE document_id = $2', [JSON.stringify(embedding), id] ); }); } ``` #### Production Standard (Correct) > **Standard**: Decouples vector computation from relational write operations using an atomic outbox write, preserving tight transactional bounds. Asynchronous CDC consumers handle embedding calculation and apply updates with monotonic version guards to guarantee convergence. ```typescript import { db } from './clients'; import { z } from 'zod'; interface DocumentUpdate { id: string; tenantId: string; content: string; version: number; } export async function updateDocumentLinearizable( data: DocumentUpdate ): Promise<void> { await db.transaction(async (trx) => { // 1. Mutate domain state with monotonic version increment const updated = await trx.query( `UPDATE documents SET content = $1, version = version + 1 WHERE id = $2 AND tenant_id = $3 RETURNING id, tenant_id, content, version`, [data.content, data.id, data.tenantId] ); if (updated.rowCount === 0) { throw new Error('OptimisticLockError: Record updated or missing.'); } const row = updated.rows[0]; // 2. Collocated write to Outbox table within identical transaction boundary await trx.query( `INSERT INTO vector_outbox ( aggregate_type, aggregate_id, payload, version ) VALUES ($1, $2, $3, $4)`, [ 'DOCUMENT', row.id, JSON.stringify({ tenantId: row.tenant_id, content: row.content }), row.version ] ); }); // Note: Postgres 17 CDC consumer reads 'vector_outbox' via failover-slot 'pgoutput', // computes embeddings offline, and conditionally upserts into vector table using monotonic check: // `INSERT INTO document_embeddings (id, embedding, version) VALUES ($1, $2, $3) // ON CONFLICT (id) DO UPDATE SET embedding = EXCLUDED.embedding, version = EXCLUDED.version // WHERE EXCLUDED.version > document_embeddings.version;` } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Strict Linearizability for Postgres 17 Logical Replication Outbox & pgvector Convergence

Strict Linearizability for Postgres 17 Logical Replication Outbox & pgvector Convergence defines an industrial-grade behavioral standard for backend engineering. Enforces strict linearizability and zero data loss between primary ACID entity transactions and downstream HNSW/IVFFlat vector embeddings. Mandates transactional outbox writes collocated within the primary transaction, consumed via Postgres 17 failover-capable logical replication slots (pg_create_logical_replication_slot with failover=true) and synchronized replication slots, completely eliminating dual-write split-brain anomalies and orphaned vector states. 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: Dual-Writing to Vector Stores or Updating Embeddings In-Transaction Causing Tail Latency and Stale Out-of-Order Vectors 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	Dual-Writing to Vector Stores or Updating Embeddings In-Transaction Causing Tail Latency and Stale Out-of-Order Vectors	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 write the primary business entity and the corresponding CDC outbox record in the same ACID database transaction using the same database client session.

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.