---
name: system-of-record-vs-derived-data
description: Use when an application writes to several stores (database plus search index, cache, or warehouse) and you must decide which is authoritative, when two stores have diverged, when keeping a new downstream system in sync, or when a migration must reach multiple stores.
tags: [derived-data, system-of-record, CDC, dual-write, data-architecture, event-sourcing]
allowed-tools: Bash, Read, Edit
---

# Systems of Record Vs. Derived Data Architecture

## Current State

Before applying this skill, run `scripts/current-state.sh` from the root of the repository you are working in. It only reads files, and reports:

- Dual-write patterns (writing to two systems in the same function)
- Cache invalidation on write
- Derived/secondary storage that mirrors primary

## R — Original Text (Reading)

> If it is possible for you to funnel all user input through a single system that decides on an ordering for all writes, it becomes much easier to derive other representations of the data by processing the writes in the same order. Whether you use CDC or an event sourcing log is less important than the principle of deciding on a total order.
>
> Allowing the application to directly write to both the search index and the database introduces the problem... in which two clients concurrently send conflicting writes, and the two storage systems process them in a different order. In this case, neither the database nor the search index is "in charge" of determining the order of writes, so they may make contradictory decisions and become permanently inconsistent with each other.
>
> — Kleppmann & Riccomini, Chapter 13: A Philosophy of Streaming Systems

______________________________________________________________________

## I — Methodological Framework (Interpretation)

Every piece of data in a system has one authoritative source of truth — the system of record — and any number of derived representations produced by transforming that source. The critical property: derived data can be safely discarded and regenerated by reprocessing the source. The system of record cannot be regenerated from anything else; if it is lost, the data is gone.

This distinction resolves a family of architectural bugs. The most common is the dual-write anti-pattern: application code writes to two systems (e.g., PostgreSQL and Elasticsearch) in a single request. Under normal operation this appears to work. Under concurrent writes with network variability, the two systems receive writes in different orders and permanently diverge — PostgreSQL ends up with record A, Elasticsearch ends up with record B — with no error, no transaction, and no way to know which is right.

The fix: designate one system as the system of record; make all others derived. Never write to a derived system directly from application code. Instead, use Change Data Capture (CDC) or an event log to propagate changes from the system of record to derived systems in order. If a derived system diverges, it can be rebuilt from scratch by replaying the CDC stream from the beginning. If the system of record is consistent, all derived systems will eventually converge.

The operational corollary: derived data systems do not need the same consistency guarantees as systems of record. A search index can tolerate eventual consistency because it is regenerable. A primary database cannot.

The event log as source of truth is the architectural generalization: instead of designating a mutable database as the system of record, treat an append-only event log as the primary source of truth. All databases, indexes, and caches are derived views that can be regenerated by replaying the log. This provides both a system of record and a complete history of changes.

______________________________________________________________________

## A1 — Past Application (From the Book)

### Case 1: PostgreSQL + Elasticsearch Dual-Write Divergence

- **Question:** A team maintains both a PostgreSQL database and an Elasticsearch index. Application code writes to both on every user action. After 6 months, the systems frequently disagree. How is this fixed architecturally, permanently?
- **Use of Methodology:** The framework diagnoses this as a dual-write violation: there is no single authority deciding write order. Two concurrent writes to the same record arrive in opposite orders at each system. Neither knows which is correct. No error is raised.
- **Conclusion:** (1) Designate PostgreSQL as the system of record. (2) Remove all direct application writes to Elasticsearch. (3) Use CDC (e.g., Debezium) to stream changes from PostgreSQL's WAL to Elasticsearch in order. (4) If Elasticsearch diverges, rebuild it by replaying the CDC stream from t=0.
- **Result:** Consistency is now structurally guaranteed rather than hoped for. All writes enter through PostgreSQL's ordered commit log; Elasticsearch sees changes in that same order. Future divergence is recoverable in minutes, not months.

### Case 2: UK Railway Gauge Migration — Dual-Format Co-Existence for Gradual Migration

- **Question:** A system has two downstream derived representations (old schema, new schema) and must migrate traffic from one to the other without downtime. How do you apply the system-of-record principle to a gradual migration?
- **Use of Methodology:** The framework extends the principle: during a schema migration, the system of record emits to both the old and new derived formats simultaneously (dual-format co-existence). Traffic is shifted incrementally to the new format. Once migration is complete, the old derived format is removed. The system of record never changes; only the derived representations change.
- **Conclusion:** Zero-downtime schema migration is achieved by running both derived formats in parallel temporarily, similar to adding a third rail to a railway to support both gauges simultaneously before decommissioning the old gauge.
- **Result:** The BART system still uses non-standard gauge today — a reminder that migration costs can permanently exceed migration benefits, and dual-format co-existence can accidentally become permanent. The correct exit criterion is explicit: the old derived format is removed only when all consumers have migrated.

______________________________________________________________________

## A2 — Trigger Scenario (Future Trigger) ★

1. An engineering post-mortem reveals that the PostgreSQL `users` table and the Redis user cache disagree on 0.3% of records after 6 months of operation. No one can determine which system is correct.
2. A team is adding a new data warehouse for analytics. The plan is to write to both the OLTP database and the warehouse from application code. An architect asks whether this is the right pattern.
3. A search index needs to be rebuilt after the mapping was deleted accidentally. The question is: what do we rebuild from?
4. A company is migrating from one database to another. The migration plan involves writing to both databases in parallel for 3 months. The question is whether this creates a dual-write problem.
5. An event-sourced system stores all state changes as immutable events. A business requirement asks for the current state of an entity. The question is whether to maintain a materialized view or always compute current state from the log.

### Language Signals

- "The database and the search index are out of sync"
- "Which one is the source of truth?"
- "We write to both places on every user action"
- "If we rebuild from scratch, what do we rebuild from?"
- "The cache and the database disagree — which wins?"
- "We had a dual-write race condition"
- "We're slowly drifting apart across all our systems"
- "Can we safely delete the secondary copy and regenerate it?"

### Distinguishing from Adjacent Skills

- Difference from `batch-vs-stream-processing-selection`: That skill selects how derived data is *computed* (batch jobs vs. stream processors). This skill determines *what is authoritative* (which system owns the source of truth) and *what is derived* (which systems are regenerable views). You need this skill's answer before choosing how to compute derivations.
- Difference from `replication-topology-selection`: Replication topology is about copying the same data across nodes for fault tolerance. The system-of-record distinction is about different representations of data across different systems serving different purposes. A replicated database is still one system of record; PostgreSQL + Elasticsearch are two different systems.
- Difference from `consistency-model-selection`: Consistency model selection asks what guarantee reads need from a replicated store. This skill asks which store is authoritative. They compose: the system of record needs strong consistency for writes; derived stores may be eventually consistent.

______________________________________________________________________

## E — Execution Steps

1. **Identify all storage systems the application writes to**

   - List every database, cache, search index, message broker, data warehouse, and external API that the application code (not replication) writes to. Include writes in the same request path and in background jobs.
   - Completion criteria: A complete list of write destinations exists.

2. **For each pair of systems, determine whether application code writes to both**

   - Flag every pair where the application writes to both as a potential dual-write. Dual-writes are not the problem — the problem is whether there is a total order enforced on those writes.
   - Completion criteria: All dual-write pairs are identified.

3. **Designate one system as the system of record**

   - For each group of related data, choose the single authoritative system. The system of record is the one from which all others can be rebuilt. Typically: the OLTP database that has the most comprehensive transactional guarantees. The event log in an event-sourced system.
   - Completion criteria: Every piece of data has exactly one designated system of record. This is documented.

4. **Remove direct application writes to derived systems**

   - Derived systems (search indexes, caches, data warehouses, read models) must no longer be written to by application code. All writes enter through the system of record only.
   - Completion criteria: Code review confirms no application code writes to derived systems directly. All paths to derived systems go through the system of record's output (CDC, event log).

5. **Establish a propagation mechanism from system of record to derived systems**

   - Choose: CDC (captures changes from the database's write-ahead log, e.g., Debezium); event log replay (if the system is event-sourced, consumers replay the log); batch ETL (acceptable for analytical derived systems with high latency tolerance).
   - The propagation mechanism must maintain order: the derived system must receive changes in the same order they were committed to the system of record.
   - Completion criteria: All derived systems receive updates from the system of record's ordered change stream. No other write path exists.

6. **Verify that derived systems can be rebuilt from scratch**

   - Test: delete the derived system (or a test clone of it) and rebuild by replaying the CDC stream or event log from the beginning. The result must be identical to the current state of the derived system.
   - Completion criteria: Rebuild was completed successfully. The time to rebuild is documented (it is now the recovery time for that derived system).

______________________________________________________________________

## B — Boundary ★

### Do Not Use This Skill When

- There is only one storage system. The system of record is trivially that system.
- Two systems serve completely different data domains with no overlap. The system-of-record principle applies to the same logical data represented in multiple systems.
- The systems are intended to be co-equal for geographic distribution with conflict resolution. Active-active multi-region replication (e.g., for local writes with low latency) is a case where both systems temporarily act as sources, with conflict resolution — this is a multi-leader replication pattern, not a derived-data pattern.

### Failure Patterns from the Book

- **ce26 — Dual Writes Race Condition**: Two clients concurrently update the same record. Application writes to the database first, then to the search index. Due to timing, the two systems receive the writes in opposite orders. The database has version B as final; the search index has version A as final. No error. Permanent silent divergence. The root cause: neither system has authority over write order.
- **Railway gauge analogy (cases.md)**: Dual-format co-existence can become permanent if the exit criterion ("remove old derived format when all consumers have migrated") is not enforced. The BART system still uses non-standard gauge. The lesson: migration completion is a business decision that requires explicit governance, not just a technical capability.

### Author's Blind Spots / Era Limitations

- The book presents CDC (Debezium, Kafka Connect) as the primary propagation mechanism for derived data systems. This assumes the system of record is a traditional mutable database with a WAL. Event-sourced systems (where the log is the source of record) compose more naturally with this pattern, but the migration path from mutable-database to event-sourced architecture is not described.
- The log-as-source-of-truth thesis in Ch. 13 is presented as a philosophy and architecture, not as a comparison against distributed transactions. The book's own admission is that this is "more opinionated than previous chapters." The trade-off — that derived systems are asynchronously updated and do not immediately reflect writes — is real and affects any feature requiring read-your-writes consistency from a derived system.
- Schema evolution in event stores is barely addressed. When the system of record is an append-only event log, schema changes to historical events are extremely difficult. The book's treatment of backward/forward compatibility (Ch. 5) assumes a database with current state; applying those principles to an immutable event log requires additional techniques (upcasting, versioned event types) that are not covered.

### Easily Confused Adjacent Methodology

- **Read replicas** are copies of the same data in the same format, kept in sync by replication. They are a form of derived data, but specifically for read scaling. The system-of-record pattern covers a broader class: different representations, different schemas, different systems (search indexes, caches, warehouses). Read replicas are a special case.
- **CQRS (Command Query Responsibility Segregation)** is the application-level pattern of separating write models (commands) from read models (queries). The system-of-record / derived-data distinction is the storage-level implementation of CQRS. Knowing one implies the other: in a CQRS system, the write model is the system of record and the read models are derived data.

______________________________________________________________________

## Related Skills

- contrasts-with: `replication-topology-selection` — replication copies the same data to multiple nodes within one logical system for fault tolerance; the system-of-record pattern distinguishes different representations of data across different systems serving different purposes; read replicas are a special case of derived data.
- composes-with: `batch-vs-stream-processing-selection` — SoR/derived-data architecture determines what is authoritative and what is regenerable; batch vs. stream selection determines how derived representations are computed from the authoritative source.
- composes-with: `consistency-model-selection` — the system of record requires strong consistency for writes; derived systems may use eventual consistency because they are regenerable from the authoritative source.
- composes-with: `schema-evolution-compatibility-planning` — CDC-based propagation from the system of record to derived systems must maintain schema backward compatibility so derived consumers do not break when the source schema evolves.

______________________________________________________________________

## Audit Information

- **Verification Passed**: V1 ✓ / V2 ✓ / V3 ✓
- **Test pass rate**: TBD (see test-prompts.json)
- **Distillation Time**: 2026-05-04

______________________________________________________________________

## Provenance

- **Source:** Designing Data-Intensive Applications, 2nd Edition — Martin Kleppmann & Chris Riccomini — Chapter 13: A Philosophy of Streaming Systems