Module Capstone: Data Model and Storage Baseline

Learning / Data Systems / Data Architecture and Platforms / 016

LESSON

Data Architecture and Platforms

016 30 min advanced CAPSTONE

Day 464: Module Capstone: Data Model and Storage Baseline

The core idea: A data model and storage baseline turns product invariants into a concrete contract about where facts become authoritative, how they are indexed and propagated, and how the system is repaired when those copies drift or fail.

Today's "Aha!" Moment

In Systems of Record vs Derived Data, PayLedger decided that PostgreSQL settles payout truth while Elasticsearch and the warehouse project that truth for different consumers. In Storage Engines: Log-Structured vs Page-Oriented, the platform learned why those systems pay very different I/O and maintenance costs even when they represent the same business entities. This capstone freezes those lessons into one production baseline for the launch of same-day payroll runs.

That baseline has to answer uncomfortable questions before traffic does. Which rows are allowed to define whether worker w_2841 in payroll run apr-2026-same-day has really been paid? Which indexes belong on the hot transactional path, and which queries should be pushed into a derived search surface instead? If support sees stale search results during a cutoff window, what is the approved recovery path, and which store is allowed to win the argument?

The useful insight is that "data model" and "storage design" are not separate documents. The model decides what must stay authoritative, idempotent, and auditable. The storage baseline decides which physical systems can carry those responsibilities without pushing unacceptable write amplification, lag, or recovery risk into the business. Once those decisions are explicit, the next lessons on B-Tree Path and Maintenance Costs and LSM Compaction and Amplification become focused cost analysis instead of architecture guesswork.

Why This Matters

PayLedger is not trying to optimize an abstract benchmark. It is closing payroll for large employers with a short same-day payout window. During that window, the platform writes status transitions, settlement attempts, operator notes, search documents, and finance facts against the same underlying payout activity. If the data model is vague, every team starts solving its own local problem by adding fields, indexes, and direct update paths where they seem convenient.

That is how platforms drift into contradictory truths. Support starts relying on a search document as if it were authoritative because it is easier to query. Product teams add a new composite index to the payout table for an operational filter that really belongs in a derived store. Finance backfills a warehouse model and accidentally changes a number that somebody had already treated as official. None of these are exotic failures; they are what happens when the baseline never made authority, freshness, rebuildability, and physical cost explicit.

The production value of a storage baseline is that it turns those implicit decisions into bounded rules. You know which tables carry the business contract, which read surfaces are allowed to lag, which indexes are worth their write-path cost, and how to reconstitute projections after a bug or reindex. That is what lets a data platform grow without quietly turning every copy into a second source of truth.

Learning Objectives

By the end of this session, you will be able to:

  1. Explain how an authoritative data model is anchored in business invariants - Identify which entities, keys, and state transitions must remain canonical for the payout workflow.
  2. Map access patterns to storage roles with explicit cost trade-offs - Decide what belongs in the transactional store, derived search surface, and analytics layer based on mechanism rather than convenience.
  3. Evaluate whether a baseline is operationally complete - Check that replay, reindex, retention, and recovery procedures preserve the same contract as the write path.

Core Concepts Explained

Concept 1: The baseline begins with authoritative facts, not with tables or engines

For PayLedger, the core business question is not "how should we normalize the schema?" It is "what facts must the platform be able to defend after retries, duplicate requests, support escalations, and audit review?" In the same-day payroll launch, the irreducible facts are that a payroll run exists for a tenant, that each payout belongs to that run, that a payout state transition is legal only in certain directions, and that a settlement attempt can be retried safely without creating a second payment.

That immediately shapes the canonical entities. A minimal authoritative core might look like this:

payroll_run(run_id, tenant_id, period_start, period_end, status)
payout(payout_id, run_id, worker_id, amount_cents, currency, status, version)
settlement_attempt(attempt_id, payout_id, idempotency_key, processor_ref, status)
payout_event(event_id, payout_id, version, event_type, occurred_at)

The important part is not the exact column list. The important part is the contract hidden inside it. payout.version prevents a stale writer from quietly overwriting a newer state. settlement_attempt.idempotency_key gives the platform one stable handle for retries against an external processor. payout_event preserves a durable change history that downstream systems can consume without being granted mutation rights. In other words, the model keeps business truth compact and enforceable instead of bloating the source tables with every field that might be useful for search or reporting.

This is the lesson from Systems of Record vs Derived Data made concrete. The source model should contain the fields needed to validate and commit the payout workflow, plus the identifiers and versions required to project that workflow elsewhere. It should not absorb denormalized support labels, warehouse-only dimensions, or convenience counters just because those fields are useful somewhere downstream. The trade-off is discipline: the authoritative schema stays narrower, but projection pipelines and joins do more work. That is acceptable because the system of record is paying for correctness, not for every possible query shape.

Concept 2: Storage roles follow access patterns only after authority is fixed

Once the authoritative model is clear, the physical baseline becomes much easier to justify. PayLedger keeps PostgreSQL as the only writer for payroll_run, payout, and settlement_attempt because those tables need transactional updates, foreign-key integrity, and bounded point or range reads during payout execution. The hot indexes belong to those operational paths: lookup by payout ID, scan by tenant and payroll run, and limited operational filters such as recent failed payouts for one tenant. Those indexes are expensive because a page-oriented engine pays for them on every write, so the baseline should treat them as a budget, not as free convenience.

The derived surfaces exist because different read paths deserve different physical structures:

API write
  -> PostgreSQL ledger tables + outbox
       |-> B-tree indexes for OLTP lookups and bounded range scans
       |-> WAL/archive for recovery
       |
       +-> CDC/outbox -> Elasticsearch payout_search
       +-> CDC/outbox -> warehouse payout_facts

Support search gets Elasticsearch because operators need broad filtering across employer, bank, failure code, and free-text notes, and those queries do not justify loading the authoritative ledger with search-oriented indexes. Finance gets warehouse facts because long scans and aggregations over many payroll runs are analytically useful but not part of the commit path. Both are intentionally downstream of committed source events. If either surface falls behind, PayLedger loses convenience, not the right to decide whether a payout settled.

This is where the previous lesson's engine comparison becomes operational. PostgreSQL's page-oriented layout is a good fit for the authoritative OLTP path until teams overload it with indexes that exist only to satisfy secondary read surfaces. Elasticsearch and warehouse storage absorb denormalization and replay better because they are already optimized around append, refresh, and rebuild patterns. The trade-off is that every derived store must come with an explicit freshness budget and a replay story. A fast query is not enough; the team has to know how stale it may be and how to restore it from the authoritative stream.

Concept 3: A storage baseline is not real until evolution and repair are specified

Many architecture documents stop after naming stores and sketching a schema. Production baselines fail later, when a new feature asks for "one more field" or an incident forces a replay. Suppose PayLedger adds a support filter for beneficiary nickname during the same-day launch. Without a baseline, one engineer adds the field to the source row, another adds a composite B-tree index to keep the filter fast, and a third patches the Elasticsearch document directly during an outage to hide projector lag. Each local choice feels reasonable. Together they destroy the authority boundary and inflate the write path.

An operationally complete baseline answers those questions before the edit lands:

That list sounds restrictive because it is. The baseline is supposed to slow down ambiguous changes. If a new field or query cannot answer "is this authoritative, derived, or analytical?" it is not ready for the hot path yet. The reward is that growth remains legible: schema evolution, reindexing, and backfills become controlled exercises instead of silent changes to the system's definition of truth. That is the ground the next lessons will stand on when they quantify B-tree traversal cost, compaction debt, and selectivity planning in 017.md, 018.md, and 019.md.

Troubleshooting

Issue: A new product requirement adds columns and indexes to the source payout tables every time support asks for a new filter.

Why it happens / is confusing: Without an explicit baseline, teams treat the system of record as a universal query engine. The immediate query gets faster, but the write path quietly accumulates index maintenance and page churn that have nothing to do with authoritative state changes.

Clarification / Fix: Classify each new field and query as authoritative, derived, or analytical before changing storage. If the filter is for support exploration rather than payout commitment, project it into search instead of paying for it on every transactional write.

Issue: Search and warehouse backfills finish successfully, but the counts do not match what operators believed during the incident.

Why it happens / is confusing: Humans often remember the projection they were staring at, not the authority boundary. A derived store can be stale, partially reindexed, or rebuilt with corrected logic while the authoritative ledger remained consistent the whole time.

Clarification / Fix: Reconcile against canonical IDs and versions from the source-of-record tables, not against whatever projection happened to be visible during the incident. Treat derived stores as disposable views whose job is to converge back to source truth.

Issue: Recovery drills restore the database, but downstream systems still cannot prove whether a payout was retried safely.

Why it happens / is confusing: Restoring bytes is not the same as restoring workflow guarantees. If idempotency keys, event history, or projector replay order are missing from the baseline, the platform may come back structurally healthy while operationally ambiguous.

Clarification / Fix: Make replay order, idempotency handles, and invariant checks part of the baseline itself. A successful drill should prove that one payout still maps to one safe settlement history and one rebuildable set of projections.

Advanced Connections

Connection 1: Systems of Record vs Derived Data sets the authority boundary; this capstone turns it into an operating contract

The earlier lesson established the philosophical split between authoritative and derived data. This capstone makes that split enforceable by naming the canonical entities, the acceptable indexes on the hot path, the freshness budgets for derived stores, and the recovery order when projections drift. Payment, payroll, and ledger systems all need this step because "the source of truth" is not useful unless engineers can tell what that rule means during normal feature work.

Connection 2: Storage Engines: Log-Structured vs Page-Oriented explains the physical bills that the next lessons will price explicitly

This baseline says why PostgreSQL should spend its write budget on transactional rows and a small index set, while search and analytics stores absorb denormalization, refresh, and replay. The follow-up lessons on B-Tree Path and Maintenance Costs, LSM Compaction and Amplification, and Index Strategy and Selectivity Planning will quantify the costs hidden behind those role assignments.

Resources

Key Insights

  1. The authoritative model is a business contract before it is a schema - Keys, versions, and legal state transitions matter because they decide what the platform can defend after retries and failures.
  2. Storage roles should be assigned with an explicit cost budget - Every source index, search document field, and warehouse projection moves work into a different write, read, or rebuild path.
  3. A baseline is complete only when replay and recovery preserve the same truth as the write path - Derived stores stay safe only if they are rebuildable from canonical history rather than patched into partial authority.
PREVIOUS Storage Engines: Log-Structured vs Page-Oriented NEXT B-Tree Path and Maintenance Costs

← Back to Data Architecture and Platforms

← Back to Learning Hub