Use Realtime Collaborative Editor as the Foundational Architecture Pattern
Deterministic ADR derived from topology, simulation, and advisor intelligence for Realtime Collaborative Editor. Traceable to YAML knowledge entities.
Context
Collaborative editing requires changes from one user to be propagated to all other users editing the same document within milliseconds. Persistent WebSocket connections create high connection counts that overwhelm standard database connection limits. Ephemeral presence data (cursor positions, active users) must not be stored in the primary database. The system must handle concurrent conflicting edits gracefully. Primary operational risks include: WebSocket servers maintain long-lived connections: each connected user holds a server-side resource; Redis pub/sub channel proliferation: one channel per active document; cleanup required on disconnect; Conflict resolution at the application layer: last-write-wins is the default but causes data loss under concurrent edits.
Decision
We will adopt the **Realtime Collaborative Editor** architecture pattern. This is a expert-complexity architecture appropriate for teams at enterprise architecture team level or above. The advisor rates this pattern as 'expert_only' operational maturity.
Rationale
An architecture for multi-user document editing where users see each other's changes in near real-time. PostgreSQL provides durable state persistence, Redis coordinates ephemeral session state and pub/sub for live change propagation, and connection pooling protects the database from WebSocket-induced connection churn. The primary architectural strength is: A connection pool bounds the total database connections an application can open, preventing connection storms during traffic…. A connection pool bounds the total database connections an application can open, preventing connection storms during traffic spikes and protecting the database server from exceeding its connection limit. Key trade-off: Pooler becomes a new single point of failure if not replicated. Operational note: PgBouncer transaction-mode pooling is most effective for stateless APIs. Evidence: PgBouncer reduces PostgreSQL connections by 10–100x in typical deployments. Core technology stack: postgresql, redis.
Architectural Strengths
- ✓A connection pool bounds the total database connections an application can open, preventing connection storms during traffic…
Accepted Tradeoffs
- ⚠Redis pub/sub provides low-latency change propagation but is fire-and-forget (no durability)
- ⚠Connection pooling reduces database connections but adds routing complexity for WebSocket backends
- ⚠Operational transformation or CRDT conflict resolution significantly increases application complexity
- ⚠Session affinity requirements for WebSocket connections limit horizontal scaling flexibility
Risks
All database connections in the pool are in use; new requests queue and then time out, causing cascading latency and errors across all dependent services.
Alternatives Considered
AI Retrieval-Augmented Generation Platform shares core technology (postgresql, redis) with the chosen architecture but applies different structural patterns; Realtime Collaborative Editor is a better fit for the identified workload profile.
Analytics Data Platform shares core technology (postgresql) with the chosen architecture but applies different structural patterns; Realtime Collaborative Editor is a better fit for the identified workload profile.
API Gateway Platform shares core technology (postgresql, redis) with the chosen architecture but applies different structural patterns; Realtime Collaborative Editor is a better fit for the identified workload profile.
Audit and Compliance Platform shares core technology (postgresql, redis) with the chosen architecture but applies different structural patterns; Realtime Collaborative Editor is a better fit for the identified workload profile.
Scaling Thresholds
Signals indicating the architecture is approaching its scaling limits:
Tier 1: WebSocket Connection Ceiling
Signal: Server memory growing with active connections; file descriptor limits approached; new WebSocket connections refused
Evolution: Increase file descriptor limits (ulimit); move to dedicated WebSocket server tier; implement connection multiplexing (multiple documents per connection where safe)
Tier 2: Database Write Contention
Signal: Consecutive writes to the same document causing lock contention; write latency rising; auto-save batching queue depth increasing
Evolution: Move to operational transformation or CRDT-based conflict resolution; batch writes and resolve conflicts in-process before database commit; consider append-only event log for document operations
Tier 3: Redis Channel Explosion
Signal: Redis memory growing; high number of active pub/sub channels per Redis instance; SUBSCRIBE/UNSUBSCRIBE operations becoming significant overhead
Evolution: Shard Redis pub/sub by document range; implement channel expiry; consider dedicated messaging tier (e.g. Ably, Pusher) for very high session counts
Migration Path
Short-polling API with version-based conflict detection → WebSocket + Redis pub/sub live propagation
User-visible edit conflicts > 5% of sessions; poll interval causes noticeable latency in collaborative sessions
Last-write-wins conflict resolution → Operational transformation (OT) or CRDT-based conflict resolution
Data loss complaints from users editing simultaneously; conflict rate measurably degrading user experience
Operational Requirements
- Minimum team maturity: Enterprise Architecture Team: This scenario has expert operational complexity. It is recommended for Enterprise Architecture Team teams or higher.
- Runbooks and alerting for high-severity risks: 1 high-severity risk identified. Each requires a documented runbook, alerting threshold, and on-call response procedure before running in production.
- Cache sizing and eviction policy configuration: Redis or equivalent cache requires correct maxmemory configuration, eviction policy selection (allkeys-lru is common), and cold-start warming strategy after restarts.
- PostgreSQL: scenario includes high_write_throughput or write_heavy workload: Deploy PgBouncer in transaction-mode pooling before relying on vertical scaling