Data

Data#

Data outlives the campaign. The collection the operator stands up today (recon hits, evidence captures, indicator feeds, case notes) will be queried, joined, and re-analyzed long after the system that produced it is torn down. The shape of that data, who owns it, and how it changes decide what you can ask of it later.

Modeling#

The schema is the most consequential design artifact in any system. Get the model right and the code mostly writes itself; get it wrong and every feature carries the cost of working around the model. The discipline below is the standard starting point.

  • Start by understanding the domain. Words used by the business should show up as types in the schema.

  • Distinguish entities (things with identity) from values (things defined entirely by their attributes).

  • Capture invariants in the schema where you can: NOT NULL, CHECK, foreign keys, unique constraints.

  • Prefer append-only to in-place updates for important state changes; the history is often more valuable than the current value.

Polyglot Persistence#

Different data shapes prefer different stores. Pick per use case, not by fashion. The list below covers the major store families; most products start (and often end) on a single relational database, with specialty stores added only when the workload genuinely outgrows it.

  • Relational, entities with relationships, transactions, ad-hoc queries.

  • Document, self-contained aggregates with flexible shape.

  • Key-value, single-key lookups at high throughput.

  • Wide-column, large datasets keyed by a partition key with many columns.

  • Graph, traversal-heavy queries (recommendations, fraud, identity).

  • Search, full-text and faceted queries.

  • Time-series, high-write, time-indexed metrics.

Most products start (and often end) on a single relational database.

Database per Service#

In a microservices world, each service owns its data; other services see it only through the service’s API. The opposite, a shared schema, is the single most reliable way to recreate a distributed monolith.

CQRS#

Command Query Responsibility Segregation; use different models for writes (commands) and reads (queries). The pattern splits the system into a normalized write side and one or more denormalized read projections, with the two communicating through events.

  • Writes go through validated, transactional command handlers against a normalized model.

  • Reads come from one or more denormalized projections, optimized for the specific query.

  • The two sides communicate via events or a change-data-capture stream.

Worth it when read and write needs diverge sharply; overkill when they don’t.

Event Sourcing#

Persist the sequence of events that changed state, not just the current state. Current state is derived by folding events together. The pattern gets you a perfect audit trail by construction; the costs are schema evolution, snapshotting, and cross-aggregate consistency.

  • Strong audit trail by construction.

  • Easy to add new projections after the fact.

  • Hard parts: schema evolution of events, snapshotting for performance, cross-aggregate consistency.

Often paired with CQRS.

Sagas#

A saga is a long-running business process that spans multiple services. Because there’s no global transaction, sagas implement compensation: each step has a rollback step, and on failure the system runs the rollbacks for steps that already completed. Two coordination styles dominate.

  • Choreography, each service reacts to events and emits new ones; no central coordinator. Simple at small scale; hard to follow at large.

  • Orchestration, a coordinator drives the steps and handles failures. Easier to reason about, but the coordinator becomes a critical service.

Compensation is rarely a perfect inverse; build for eventual consistency and design user-visible behavior accordingly (status pages, “pending” states).

Migrations#

Schema changes are some of the highest-risk deploys in any system. Forward-only, expand-and-contract, lock-aware; the discipline below is what separates “we ran a migration” from “we took the database down”:

  • Forward-only, online, in small steps.

  • Expand-and-contract for breaking changes: 1. add new column / table / endpoint; 2. dual-write or backfill; 3. switch readers; 4. remove the old.

  • Tools: Flyway, Liquibase, Alembic, sqlx-migrate, Prisma migrate, gh-ost / pt-online-schema-change for online MySQL changes.

  • Lock-aware: long-running migrations on big tables can block writes; test against production-sized data.

Data Consistency#

Within a single relational DB, transactions give strong consistency. Across services, the global transaction goes away and the choice becomes which weaker model the system can tolerate. Pick the weakest one that meets the business requirement; weaker models scale further, cost less, and tolerate more failure.

  • Strong (synchronous, distributed transactions / 2PC), expensive, fragile.

  • Read-your-writes / monotonic reads via session pinning or causal tokens.

  • Eventual, the default for cross-service state. Make it visible to users.

Pick the weakest consistency model that meets the business requirement; weaker is faster, cheaper, and more available.

Privacy and Lifecycle#

Privacy and retention are schema decisions, not policy afterthoughts. Classification, minimization, retention, and the ability to actually delete personal data when the law demands it. If these are not designed in early, every later attempt to add them is rework.

  • Classify data, public, internal, sensitive, regulated.

  • Minimize, don’t store what you don’t need.

  • Retention policies, delete after they’re no longer needed.

  • Right to erasure (GDPR, CCPA), design schemas where deletion is possible.

  • Backups are personal data too; same retention rules apply.

Contents