openengineer/examples/software-auth-context.md

Example: Database Schema Selection (Software Engineering)

Status: Draft Phase: The Bedrock Phase

What This Example Demonstrates

Context record structure (OE-0003) in a contemporary software engineering decision.

The Observation

A startup's user authentication service experienced increasing query latency as the user base grew from 10,000 to 500,000 accounts. Database query times exceeded the 200ms service-level objective.

Engineering Translation

When the original decision-maker leaves and a new engineer must modify the authentication service, the new engineer needs to understand why the current schema was chosen — not just what it is. Without that context, the new engineer may propose a schema change that re-introduces a problem the original engineer already solved.

Context Record

Field	Content
Decision	Migrate user sessions from JSON column in PostgreSQL to a dedicated Redis cache, keeping PostgreSQL as the persistent store
Observation	Profiling showed 73% of slow queries were session-read operations against a JSONB column exceeding 50KB average row size. The JSONB column required table-level locking for updates.
Alternatives	(A) Shard the PostgreSQL database by user ID — rejected: session data is transient and sharding introduces operational complexity disproportionate to the benefit. (B) Move all auth data to MongoDB — rejected: transactional integrity for credential updates is required; MongoDB's document model does not provide the needed isolation guarantees. (C) Cache only in application memory — rejected: eliminates cross-instance session sharing, blocking horizontal scaling.
Constraints	Must maintain ACID compliance for credential writes. Must support horizontal scaling to 10+ instances. Must not exceed $200/month infrastructure cost at projected 12-month scale. Deployment must complete within a single maintenance window.
Reasoning	Redis provides sub-millisecond reads for session data, satisfies horizontal scaling through cluster mode, and costs approximately $30/month at projected scale. PostgreSQL retains ACID compliance for credential writes. The separation of concerns (Redis for transient session reads, PostgreSQL for persistent credential data) matches each store to the access pattern it handles best.
Verification	Load testing at 2x projected scale (1M concurrent sessions) showed p99 read latency of 4ms (down from 340ms). Write latency remained at 12ms. Failover test confirmed zero session loss during Redis node failure (replication recovery within 11 seconds, within the 30-second session timeout).
Lineage	Builds on the initial PostgreSQL-only architecture (context record CR-2024-001). Addresses the scalability limitation identified in that record's Open Questions field.
Assumptions	Session data size will remain below 10KB per session. Redis cluster mode will maintain sub-5ms p99 at projected scale. The application layer can handle the dual-store consistency model (read from Redis, fall back to PostgreSQL on cache miss).
Open Questions	At what user count does Redis cluster operational complexity exceed the benefit? Should session data have a TTL-based expiration policy in addition to the explicit logout mechanism?

Self-Fading Assessment

This example transports the reader from the abstract context record structure to a concrete software engineering decision. Once the reader understands how each field captures a real engineering reasoning process, the example has served its purpose.