API and Data Model Design for System Design

An API contract and a data model are two views of the same system promises. The API says which operations callers may request and observe. The data model says which facts the system can preserve, constrain, find, and evolve. When they are designed independently, the gaps appear as duplicate writes, slow queries, impossible migrations, and several stores all claiming to be the truth.

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

• turn workflows into resource and event contracts;
• separate HTTP safety, idempotence, validation, and asynchronous acceptance;
• derive entities, relationships, constraints, and indexes from invariants and access patterns;
• design stable cursor pagination and conditional updates;
• choose partition keys and retention policies without hiding hot keys or deletion work;
• distinguish authoritative state from caches, search indexes, events, and analytical views;
• evolve contracts through mixed-version deployments and recover derived state after failure.

Prerequisites: the design framework and workload estimates from the first three lessons. The downloadable lab uses Python's standard library and an in-memory SQLite database.

Begin with workflows, not tables or endpoints#

Continue the notification example from the previous lessons. Its first useful slice is narrow:

1. A tenant submits one notification for one recipient.
2. The service accepts it durably and returns an identity.
3. A worker attempts delivery and records the outcome.
4. The tenant reads status; the recipient lists recent notifications.

Now state the promises that must remain true:

• a retry with the same tenant-scoped idempotency key and the same payload represents one logical submission;
• another tenant may use the same key without collision;
• a delivered notification cannot silently return to delivering;
• recipient history never crosses the tenant boundary;
• history order is deterministic even when two rows share a timestamp;
• analytics may lag, but the accepted notification and its current state may not disappear because an analytical pipeline failed.

These are invariants: conditions that valid system state must preserve. An access pattern is a concrete read or write, including its filters, order, expected result size, frequency, and consistency need. Together they are stronger design inputs than “use REST and PostgreSQL.”

Create an access-pattern table before choosing indexes:

“List all notifications” is not yet an access pattern. Which tenant? Which recipient? What order? What page size? Does a newly inserted row need to appear during traversal? Those answers change the key and index.

Design the resource contract#

A compact HTTP surface could be:

POST /notifications
GET  /notifications/{notification_id}
GET  /recipients/{recipient_id}/notifications?limit=50&cursor=...

Treat the paths as a starting point, not proof of good design. Define authentication, tenant derivation, request and response fields, limits, timeouts, errors, and retry behavior. Do not trust a client-supplied tenant_id when the authenticated principal already determines the tenant.

Methods describe intended semantics

RFC 9110 separates safe and idempotent methods:

• Safe methods such as GET and HEAD have essentially read-only standardized semantics. Logging a read is incidental; making GET /jobs/run-cleanup start destructive work violates the contract.
• An idempotent method has the same intended server effect when an identical request is made multiple times. PUT, DELETE, and safe methods are idempotent by standardized semantics.
• Idempotence does not require identical response status codes or forbid logging every attempt.
• POST is not standardized as idempotent, but an application can make a particular operation retry-safe with a stable operation identity and deduplication contract.

For notification creation, the server assigns the resource identity, so POST /notifications is natural. Require an Idempotency-Key for callers that will retry. Document its scope, payload comparison rule, retention window, and behavior while the first request is still in progress.

Hand-worked ambiguous retry

Assume client acme sends:

Idempotency-Key: send-8f31
payload fingerprint: F(tenant=acme, recipient=user-7, channel=push, body=...)

The first request commits notification n-1042, but the response is lost. The client cannot distinguish “server never received it” from “server committed and the response vanished,” so it retries.

Inside one transaction, the service enforces:

UNIQUE (tenant_id, idempotency_key)

The retry finds (acme, send-8f31) → n-1042 and returns the original resource identity. Two transport attempts produced one logical notification. If the client reuses send-8f31 with a different fingerprint, the service rejects the changed intent instead of silently returning an unrelated result.

The guarantee has a boundary. A local uniqueness constraint cannot atomically cover an arbitrary remote provider call. Later lessons will develop outboxes, consumer deduplication, and reconciliation for those boundaries.

202 Accepted is not completion

If the service durably accepts work for asynchronous processing, it may return 202 Accepted with the notification identity and a status URL. RFC 9110 defines 202 as accepted but not completed; the work may still fail or never be acted upon. Do not return a response that reads like “delivered successfully.”

Use 201 Created when the request has created the resource and the creation semantics are complete. Use 204 No Content only when a successful response intentionally has no content; it cannot also carry a JSON response body.

Errors are part of the contract

RFC 9457 defines application/problem+json for machine-readable HTTP problem details. A validation response might be:

{
  "type": "https://imazanwar.com/problems/invalid-notification",
  "title": "Notification is invalid",
  "status": 422,
  "detail": "body must contain 1 to 500 characters",
  "instance": "/api-problems/01J5...",
  "invalid_fields": [{"name": "body", "reason": "too_long"}]
}

The type identifies the problem class for machines; instance identifies this occurrence. Clients should not parse the human detail string to decide behavior. The HTTP status retains its normal meaning: problem details complement it rather than inventing new semantics. Never expose stack traces, SQL, internal paths, secrets, or another tenant's identifiers.

Useful distinctions include:

• 400 for malformed request syntax or framing;
• 401 when valid authentication credentials are missing, with the required authentication challenge;
• 403 when the server understands the request but refuses it;
• 404 when the resource is absent or its existence is intentionally concealed;
• 409 for a resolvable conflict with current state;
• 412 when a request precondition such as If-Match fails;
• 422 when content syntax and media type are understood but instructions are semantically invalid;
• 429 for rate limiting and 503 for temporary overload or maintenance, optionally with Retry-After where the relevant standard permits it.

Do not turn this into a universal retry table. A retry is appropriate only when the operation is retry-safe, the failure is plausibly transient, and the caller's deadline and documented API contract allow it.

Make concurrency visible with version tokens#

Suppose two operators read notification preference version v7. One disables email while the other changes the quiet-hours window. If both send unconditional updates, the later write can erase the earlier one.

Return an ETag representing the selected representation version and require:

If-Match: "v7"

The first update produces v8. The second request's precondition no longer matches, so the server returns 412 Precondition Failed rather than overwriting unseen work. This is optimistic concurrency control expressed through HTTP. The server must define what the validator represents; a timestamp rounded too coarsely is not automatically a safe version.

Design pagination as a consistency contract#

Offset pagination is convenient:

ORDER BY created_at DESC
LIMIT 50 OFFSET 50000

But large offsets can require scanning or discarding substantial work, and concurrent inserts or deletes can shift row positions between requests. It also lacks a unique order when timestamps tie.

For recipient history, choose the total order:

(created_at DESC, notification_id DESC)

If the last row on page one is (2026-08-22T12:30:00Z, n-1042), page two asks for rows strictly after that position in the chosen order:

WHERE tenant_id = :tenant
  AND recipient_id = :recipient
  AND (created_at, notification_id) < (:created_at, :notification_id)
ORDER BY created_at DESC, notification_id DESC
LIMIT 51;

Fetch one extra row to determine whether another page exists. Encode the boundary in a cursor the client treats as opaque. Base64 is encoding, not encryption or integrity protection; sign the cursor if tampering matters, and bind it to tenant, filters, and sort order.

There is no IETF standard that mandates cursor or offset mechanics. RFC 8288 standardizes link relations such as next and prev, which may carry navigation URLs, but the API still must define ordering and concurrent-write behavior. Keyset pagination gives stable continuation through a moving dataset; it does not automatically create a database snapshot across all pages.

Try it yourself

Derive the history query

A history endpoint filters by tenant_id and recipient_id, sorts newest first, and may contain equal timestamps. Write the cursor fields and a matching B-tree index. What breaks if the cursor stores only created_at?

Reveal solution

Use (created_at, notification_id) as the cursor and an index such as (tenant_id, recipient_id, created_at DESC, notification_id DESC). The equality predicates form the leading index columns; the remaining columns match traversal order. A timestamp-only cursor cannot place rows uniquely, so tied rows may be skipped or repeated at page boundaries.

Model identities, relationships, and ownership#

An entity has an identity that persists while its attributes change. A relational table is an unordered collection of rows; primary and foreign keys express identity and relationships. A document aggregate groups data that is commonly loaded and changed as one unit.

For the notification slice:

Notification
  notification_id, tenant_id, recipient_id, channel, body,
  state, created_at, updated_at

DeliveryAttempt
  attempt_id, notification_id, provider, attempt_number,
  outcome, provider_reference, started_at, finished_at

IdempotencyRequest
  tenant_id, idempotency_key, request_fingerprint,
  notification_id, created_at, expires_at

Notification owns current authoritative state. DeliveryAttempt is one-to-many history: retrying should add an attempt rather than overwrite the evidence of the previous failure. IdempotencyRequest maps a caller's logical operation to the accepted resource.

Use stable opaque identifiers in public contracts. Email addresses, usernames, and phone numbers can change, collide, expose information, or create awkward foreign-key updates. A human-readable slug can be an attribute with its own uniqueness and rename policy rather than the sole identity.

Normalize or embed according to relationship shape

DDIA compares relational and document models by the assumptions they make easy. Embedding a bounded set of values that is normally read and replaced with its parent can improve locality. Independent many-to-many relationships favor references: copying one recipient preference into every notification makes an update a large consistency problem.

“Schemaless” does not mean “no schema.” It usually moves interpretation and validation toward readers. Historical records may have mixed shapes, and every consumer still makes assumptions. A schema enforced on write rejects invalid states earlier; schema-on-read can make heterogeneous ingestion or gradual interpretation easier. Choose where compatibility and cleanup costs belong.

JSONB is useful in PostgreSQL for genuinely variable attributes and supports specialized indexes, but it is not an excuse to hide important invariants, join keys, or common filters inside an unvalidated blob. Keep fields relational when their types, constraints, relationships, or access patterns matter to correctness.

Put invariants in the strongest practical boundary#

Application checks improve error messages, but concurrent requests can both pass a “does this exist?” check. Enforce critical local invariants in the authoritative database where possible:

• NOT NULL for required stored values;
• CHECK for row-local rules such as enumerated states or body length;
• UNIQUE (tenant_id, idempotency_key) for operation identity;
• primary keys for row identity;
• foreign keys for permitted references.

In PostgreSQL, primary and unique constraints create unique B-tree indexes. A foreign key does not automatically create an index on the referencing columns, so common joins and parent deletion/update checks may need one. PostgreSQL also warns against using CHECK for assumptions involving other rows or tables; use the appropriate uniqueness, exclusion, foreign-key, transaction, or carefully designed trigger mechanism.

Some invariants cross service or datastore boundaries and cannot be made true by one SQL constraint. Name that boundary. “A notification and its outbox intent commit together in one database transaction” is precise. “Exactly once everywhere” is not.

State transitions are invariants too

Represent the lifecycle explicitly:

An enum or CHECK ensures only known state names; it does not by itself validate transitions between two valid names. Enforce transitions with a conditional update, transaction protocol, or database mechanism appropriate to the ownership boundary. Record attempt history separately when operations need auditability and debugging.

Derive indexes from queries#

An index is a maintained copy of selected keys, not a free speed switch. It consumes storage and makes writes, vacuuming, replication, and migrations more expensive.

For PostgreSQL B-tree indexes, equality predicates on leading columns followed by the first range or ordering columns are an effective design pattern. The recipient-history query maps to:

CREATE INDEX notifications_recipient_history
ON notifications
  (tenant_id, recipient_id, created_at DESC, notification_id DESC);

The permutation is meaningful. An index beginning with recipient_id does not provide the same tenant-first access and isolation properties. PostgreSQL may sometimes use skip scans or apply later-column filters, so “leftmost prefix” is a planning heuristic, not a claim that every later-only predicate is impossible.

A retry worker may need only failed rows:

CREATE INDEX notifications_retry_work
ON notifications (tenant_id, next_attempt_at, notification_id)
WHERE state = 'failed';

A partial index is useful when its predicate selects the operational subset. PostgreSQL can use it only when the query condition implies the index predicate at planning time; parameterized or differently expressed predicates can prevent a match. Confirm with representative data and EXPLAIN, not just the DDL.

Do not add indexes for hypothetical screens. For every index, write the query it serves, its expected selectivity and order, and the write/storage cost you accept.

Choose a partition key after the transaction boundary#

A partition key determines which related operations can remain local and which require scatter, coordination, or a derived view. Evaluate candidates against:

• routing: does the request already know the key?
• locality: do common reads and atomic writes share it?
• distribution: can one tenant or celebrity dominate a partition?
• ordering: which key needs ordered events or updates?
• evolution: can the system reassign or split hot ownership later?

Partitioning notification state by tenant_id keeps tenant-scoped idempotency and administration local, but one huge tenant can become a hot partition. Partitioning by notification_id distributes individual writes but makes recipient history a scatter query unless a recipient-oriented derived view exists. Hashing does not split one indivisible hot key.

Use the previous lesson's high scenario and hottest-key share. If a proposed key receives 15% of 74,000 peak operations/s, its owner sees about 11,100 operations/s regardless of the average across partitions. The key choice must survive that number or have an explicit isolation/splitting strategy.

Give every dataset an ownership and retention class#

Classify data before drawing arrows:

• Authoritative: the accepted notification, current state, and invariants. Losing it changes the system's answer.
• Operational history: delivery attempts and idempotency records, retained long enough for retries, dispute handling, and debugging.
• Derived: recipient timelines, search indexes, analytics aggregates, and caches that can be rebuilt from an identified source.
• Ephemeral: leases, transient buffers, and cached representations whose loss affects performance rather than truth.

Retention is part of the model, not a storage housekeeping footnote. Define which clock starts the period, legal or product exceptions, deletion latency, backup expiration, search/cache invalidation, and how tombstones propagate. A database TTL often schedules cleanup; it does not prove bytes vanish instantly from replicas, backups, or derived systems.

A derived dataset must name its rebuild source, checkpoint, and reconciliation procedure. “Derived” without a tested rebuild path is merely undocumented authority.

Evolve from one transaction to derived views#

Begin with the smallest boundary that preserves the invariants. When read models or integrations need asynchronous work, evolve deliberately:

Drag canvas to move−

100%

+FitFull screen

Diagram key and text version

• Entry
• External
• Process
• Data
• Async

1. Tenant client → Notification API: POST /notifications
2. Notification API → Response lost: reply disappears
3. Response lost → Notification API: same key retry
4. Notification API → Authoritative DB: commit state
5. Notification API → Outbox records: commit intent
6. Outbox records → Idempotent projector: publish / replay
7. Idempotent projector → Recipient history: upsert
8. Idempotent projector → Analytics: aggregate

The API writes the notification and outbox intent in one local transaction. A publisher may emit the same event more than once if it crashes after publishing but before recording progress, so the projector deduplicates or performs an idempotent upsert. Lag makes derived views stale; it does not change which store owns current state.

The event contract should distinguish identities:

{
  "specversion": "1.0",
  "type": "com.imazanwar.notification.state-changed.v1",
  "source": "/notification-service",
  "id": "event-n-1042-delivered",
  "subject": "n-1042",
  "time": "2026-08-22T12:30:00Z",
  "data": {
    "tenant_id": "acme",
    "notification_id": "n-1042",
    "state": "delivered"
  }
}

CloudEvents standardizes envelope context such as id, source, specversion, and type. The pair (source, id) identifies a distinct event. It does not define broker delivery guarantees, ordering, retry policy, or the compatibility rules for the business payload. specversion: 1.0 versions the CloudEvents specification, not this notification schema.

Name events as facts that already occurred, such as NotificationDelivered, rather than commands such as DeliverNotification. Include only fields consumers need, define the ordering scope, and expect redelivery. Avoid copying a complete mutable entity into every event unless that snapshot contract is intentional.

Plan for mixed versions#

During a rolling deployment, old and new code coexist; messages can also outlive the process that wrote them. Compatibility is therefore a dataflow property, not merely /v1 in a URL.

Safer additive changes include introducing optional request fields with defined defaults and adding response or event fields that consumers are required to ignore when unknown. Renaming or changing the meaning/type of an existing field is usually breaking. A migration may require:

1. deploy readers that understand old and new shapes;
2. begin writing the new shape while retaining compatibility;
3. backfill old data and verify counts/invariants;
4. move all readers to the new field;
5. stop old writes, then remove old data only after the compatibility window.

Test real old-client/new-server and new-client/old-server combinations where they can occur. Schema tools can detect structural changes; they cannot decide whether changing amount from cents to dollars preserved meaning.

Failure-path review#

Before approving the design, walk these paths:

• Response lost after commit: retry returns the original notification identity.
• Same idempotency key, different intent: reject the conflict; do not guess.
• Publisher crashes after emitting: projector handles a duplicate event.
• Projector is down: authoritative writes continue if allowed; lag is measured and bounded.
• Derived history is corrupted: rebuild from the authoritative/outbox history and reconcile.
• Hot tenant overwhelms one partition: isolate, split, throttle, or revise the partition strategy.
• Cursor is tampered with: reject it or verify a signature; never let it escape tenant authorization.
• Deletion request arrives: remove or tombstone authority, propagate to derived systems, and let backup retention follow its documented policy.
• Index is missing or misordered: query-plan and latency alerts reveal a scan before saturation becomes an outage.

Try it yourself

Separate truth from views

A team stores current notification state in PostgreSQL, recipient history in a document store, search results in a search engine, and daily counts in an analytical warehouse. It says all four are “eventually authoritative.” Redesign the ownership statement and describe recovery after the history projector loses a day.

Reveal solution

Choose one authoritative record for accepted identity, current state, and invariants—here, PostgreSQL. Declare recipient history, search, and daily counts as derived with explicit freshness objectives. Retain a replayable outbox/event source or another change log. After the projector gap, resume from the last durable checkpoint or replay the missing range, use idempotent upserts keyed by notification identity/version, compare source and view counts, and repair discrepancies. Eventual consistency describes visibility timing; it does not make every copy authoritative.

A repeatable interview and production workflow#

1. Choose one critical workflow and its tenant/security boundary.
2. State invariants and allowed state transitions.
3. List reads/writes with filters, order, size, consistency, and rate.
4. Design HTTP resources and event facts around those semantics.
5. Define validation, errors, retries, concurrency, and pagination.
6. Model identities, relationships, authority, and transaction boundaries.
7. Map each critical access pattern to a key/index or derived view.
8. Test partition candidates against locality, distribution, and hot keys.
9. Assign retention, deletion, rebuild, and reconciliation policies.
10. Walk ambiguous retries, mixed versions, lag, duplicates, and recovery.

In an interview, do not recite every status code or database feature. Trace one important write and one important read from contract to durable state, then expose the failure path and trade-off. In production, turn the same trace into contract tests, constraints, query-plan checks, lag objectives, and recovery drills.

Common design failures#

• CRUD before invariants: endpoints exist, but valid transitions and duplicate behavior are undefined.
• Tables before queries: indexes and partition keys become guesses.
• Client-supplied tenant trust: a data-model key cannot repair broken authorization.
• 202 presented as success completion: accepted work later fails invisibly.
• Offset pagination without movement semantics: concurrent changes skip or repeat rows.
• Timestamp-only cursor: equal timestamps lack a total order.
• Every field in JSONB: important constraints and common queries become harder to enforce and optimize.
• One index per column: real predicates and sort order are not served, while writes pay the cost.
• “Schemaless” interpreted as “no contract”: compatibility bugs move to every reader.
• Cache, search, queue, and database all called truth: recovery has no source of authority.
• Partition by tenant without skew analysis: the largest customer becomes one failure domain.
• TTL described as immediate erasure: replicas, backups, caches, and derived stores are ignored.
• CloudEvents treated as exactly-once: an envelope format does not define delivery semantics.

What does 202 Accepted guarantee for an asynchronous notification request?

The request was accepted for processing, but processing is not complete and may still failThe provider has delivered the notification exactly onceThe response can never be retriedA JSON body is forbidden

Check answer

Which index best matches tenant-scoped recipient history ordered newest first with stable cursor pagination?

(tenant_id, recipient_id, created_at DESC, notification_id DESC)(state)(created_at) without a unique tie-breakerOne independent index on every column

Check answer

Which statement correctly separates authoritative and derived data?

A derived view identifies a source and can be rebuilt; temporary lag does not transfer authority to itEvery eventually consistent copy is authoritativeA cache becomes authoritative whenever it has a high hit rateAn event envelope guarantees a rebuild will succeed

Check answer

Study optional flashcards

Invariant

A condition every valid state transition must preserve.

Flip card

Access pattern

A read or write described by filters, order, result size, rate, and consistency need.

Flip card

Idempotence

Repeating an identical request has the same intended server effect as making it once.

Flip card

Keyset pagination

Continue from the last ordered key values rather than a shifting row offset.

Flip card

Authoritative state

The owned record used to decide the system's correct answer.

Flip card

Derived state

Rebuildable data produced from an identified authoritative or replayable source.

Flip card

Forward compatibility

Older code can read data written by newer code.

Flip card

Backward compatibility

Newer code can read data written by older code.

Flip card

Memory chart#

Workflow → invariants → access patterns
         → resource/event contracts
         → entities + relationships + constraints
         → query-shaped indexes
         → locality-shaped partition key
         → ownership + retention + rebuild

Retry identity: tenant + idempotency key + same intent
Stable cursor: filter context + ordered values + unique tie-breaker
Async acceptance: 202 is accepted, not completed
Derived view: named source + checkpoint + replay + reconciliation

This completes the solving-framework module: requirements, estimates, contracts, and data ownership now connect. The next lesson begins the core building blocks with networking, HTTP request paths, DNS, TLS, proxies, and CDNs.

References#

• Martin Kleppmann: Designing Data-Intensive Applications — Chapters 2 and 4 on data-model trade-offs, relationships, schema evolution, service dataflow, and network-call failure semantics.
• RFC 9110: HTTP Semantics — method safety and idempotence, conditional requests, success/error status semantics, and retry constraints.
• RFC 9457: Problem Details for HTTP APIs — standardized machine-readable error objects, extensions, and disclosure risks.
• RFC 8288: Web Linking — registered navigation link relations; it does not prescribe cursor mechanics.
• PostgreSQL: Constraints — row constraints, uniqueness, primary keys, and referential integrity behavior.
• PostgreSQL: Multicolumn indexes — column order, leading constraints, and skip-scan behavior.
• PostgreSQL: Partial indexes — predicate-selected indexes and planner implication requirements.
• PostgreSQL: JSON types — json/jsonb representation and indexing trade-offs.
• CloudEvents specification — portable event context attributes and event identity.