API and Data Modeling

Begin where the promise can fail

Suppose the system-design prompt is team file sharing. The first API sketch is tempting:

POST /files
GET /files/{id}
DELETE /files/{id}

Then the interviewer adds one ordinary fact: files can be several gigabytes, so clients should upload bytes directly to object storage.

Now POST /files hides the design. If metadata is committed before the upload finishes, readers can discover a file whose bytes do not exist. If bytes arrive first, an abandoned upload can consume storage without a durable owner. If the completion request is retried, the system might create two visible versions. The route names were plausible; the promise was missing.

Begin with the user-visible result instead:

A workspace member can upload a new version. Teammates with permission see it only after the complete object has passed verification. Retrying a successful completion does not create another version.

That sentence supplies the first invariant, the authorization boundary, the retry behavior, and a lifecycle. It is enough to start modeling.

Turn the flow into a contract

The useful interview artifact is not a full OpenAPI document. It is the small part of the contract that constrains the architecture. For the upload flow:

POST /files/upload-sessions
Body:
  workspace_id, folder_id, filename, size_bytes, content_type,
  checksum, client_token
Returns:
  upload_id, file_id, upload_url, upload_headers, expires_at
Errors:
  forbidden, folder_not_found, quota_exceeded, unsupported_type
Guarantee:
  A session reserves metadata but does not make a version visible.

POST /files/{file_id}/versions/complete
Body:
  upload_id, checksum
Returns:
  file_id, version_id, status, created_at
Errors:
  upload_not_found, checksum_mismatch, expired_upload, conflict
Guarantee:
  Repeating completion for the same upload returns the same version.

The contract makes design obligations visible. client_token needs a stable scope and uniqueness rule. Completion needs an idempotency record or a unique relationship between the upload session and file version. The server must authorize the workspace and folder before issuing an upload URL. Verification must finish before a version becomes available.

There is no prize for attaching every familiar API concern to every route. An unsafe write needs duplicate behavior. A list needs ordering and pagination. A derived read needs a freshness promise. An administrative mutation may need approval and audit more than a public rate limit. Name a behavior when it protects this flow.

Let the lifecycle do some work

The version record might move through these states:

initiated -> uploading -> verifying -> available
                         \-> failed
initiated/uploading -> expired
available -> deleted-for-users -> retention-expired

The names are less important than the legal transitions. Only available versions can become the file’s current visible version. An expired session cannot be completed. A checksum failure cannot be repaired by changing the expected checksum after the fact. User deletion can hide a version while retention or legal-hold policy still prevents physical removal.

These rules reveal the transaction boundary. When verification succeeds, the system must make the version available and advance current_version_id without exposing an intermediate state. That may be one relational transaction. Object deletion, search cleanup, thumbnail removal, and analytics events need not join it, but each deferred action needs a retry and repair story.

If two services can independently declare a version available, the model has hidden conflict resolution. A clear owner is more valuable than another box on the diagram.

Decide who may make each fact true

For this design, workspace membership owns whether a user belongs to the tenant and what tenant-wide role applies. File metadata owns folders, file names, version lifecycle, the current-version pointer, and file-specific policy references. Object storage holds bytes and reports whether an object is present; it does not decide who may download them. A sharing boundary may own revocable share-link records, while the metadata boundary remains responsible for deciding whether a link can expose this file now.

Search documents, thumbnails, previews, feeds, and analytics records are copies made for particular reads. Calling them derived is not enough. Say which canonical records rebuild them, how stale they may be, and what happens when deletion or permission changes outrun propagation.

This distinction changes the download contract:

GET /files/{file_id}/download
Returns:
  version_id, signed_download_url, expires_at
Errors:
  not_found, forbidden, unavailable
Guarantee:
  The service checks current authorization before issuing a short-lived URL.

Possession of an object key is not the authorization model. Neither a stale search result nor an old thumbnail may grant access that canonical policy has revoked.

Model reads before choosing stores

The same entities support very different access paths:

• direct file lookup needs an authoritative read by workspace and file ID;
• folder browsing needs a stable order and cursor, even while files are added or moved;
• recent files may use a workspace-and-time index;
• full-text search needs extracted content, ranking, and permission-aware filtering, with an explicit lag budget;
• audit review needs append-oriented events retained independently of the mutable file row;
• cleanup needs to find expired uploads, deleted versions, derived objects, and objects that have lost their metadata owner.

Those paths force useful questions. If folder pages sort only by a mutable name, what happens when names change between requests? If search embeds an ACL snapshot, what closes the exposure window after a permission revocation? If an upload session expires, can cleanup identify its object without scanning the bucket?

Only now is storage choice concrete enough to discuss. This comparison earns its table because the decision requires simultaneous lookup across shared attributes:

Storage choice is not a personality test. Choose the storage model that fits the write invariant, read pattern, scale pressure, and repair story.

Most credible designs combine models. The useful sentence is: “This store is canonical for these facts; this other store is derived for this read path; here is how it catches up or gets rebuilt.” That prevents caches, search indexes, feed tables, and analytics pipelines from becoming accidental truth.

For team file sharing, relational metadata is a strong starting point because uniqueness, lifecycle transitions, folder membership, and current-version updates need constraints. Object storage holds immutable version bytes. Search is derived. Audit can use an append-oriented log or store when its volume and retention warrant one. That combination is a consequence of the model, not a default stack.

The rejected choices are part of the answer. Keeping multi-gigabyte bytes in ordinary relational rows entangles serving, backup, and replication paths. Making search authoritative turns index lag into a permission and deletion hazard. A share token with no server-side record is easy to issue but hard to revoke or constrain with later enterprise policy.

Make the model absorb change

An interviewer may now ask for resumable uploads. Add uploaded-range or part state to the session, but preserve the rule that partial versions are invisible. A request for folder moves forces a choice: are paths computed from the hierarchy, or copied into descendants? The answer determines cycle checks, write amplification, and permission inheritance behavior.

Legal hold does not require a new generic “compliance service” on the first diagram. It requires the data model to separate user-visible deletion from retention eligibility, record the reason and authority for the hold, and make physical cleanup respect it. Full-content search adds extraction workers and a permission-aware index, but it does not move ownership of the file or its policy into search.

These changes are useful tests because they travel through the model. If each one forces an unrelated redesign, the original entities and invariants were probably too shallow.

Schema evolution deserves the same treatment. Adding an optional field is easy only when old clients, old events, and old records have defined behavior without it. Renaming an enum value or changing a lifecycle meaning is a contract migration. State which versions coexist, where translation occurs, and when old data is backfilled or retired.

Be selective in the room

Two or three well-modeled flows are usually stronger than a complete endpoint inventory. Choose the write whose failure would violate the product promise and the read whose shape most affects storage. For file sharing, upload completion and permission-aware discovery expose far more architecture than CRUD coverage.

A compact narration might be:

I will model upload as initiate and complete because bytes bypass the app
server. File metadata owns version visibility; object storage only holds the
bytes. Completion is idempotent, and only a verified version can become
current. Folder reads are authoritative and cursor-paginated. Search is a
permission-aware derived index with a rebuild path, so lag cannot grant access.

If the conversation drifts into component names, recover by returning to one operation: what the caller asks, which state changes, who may change it, and which read must reflect it. The next chapter can draw the boxes after these facts have given the boxes something to own.

Practice the reasoning

Use a prompt you already know—ticketing, chat, ride matching, feature flags, document editing, or notifications—and produce four small artifacts:

1. Write one command and one query for the core flow. Include authorization, errors, and either duplicate behavior or freshness, as the operation needs.
2. State three invariants without database language. If an invariant is only “the row exists,” it is not yet a product rule.
3. Name the canonical owner and every derived copy of one important fact. Give each copy a freshness expectation and rebuild source.
4. Change one requirement—deletion, retention, sharing, search, regional placement, or migration—and trace the alteration through contract, state, reads, and storage.

Before moving on, ask whether you can state the main API promise in one sentence, name the owner of every consequential fact, distinguish canonical state from derived views, and explain one rejected storage choice. Any answer you cannot give marks the next place to work.

One-page field reference

Use this card before drawing components.

CONTRACT TO DATA MODEL LOOP

1. Promise
   - Actor, action, user-visible result.
   - Failure or retry that could break the result.

2. Operation contract
   - Inputs, outputs, authorization, errors.
   - Duplicate behavior for writes.
   - Ordering, pagination, and freshness for reads.

3. State
   - Entities, invariants, lifecycle, legal transitions.
   - Transaction and conflict boundary.

4. Ownership
   - Canonical owner.
   - Derived copies, lag, and rebuild path.
   - Audit, deletion, retention, and repair.

5. Access paths
   - Lookup, list, search, aggregate, traverse, or stream.
   - Stable keys, indexes, filters, and cursors.

6. Storage choice
   - Relational, document, key-value, graph, time-series,
     blob, search, vector, or combination.
   - One rejected alternative and its failure mode.

Senior rule:
APIs expose promises; models protect invariants.

The work is complete when the contract has narrowed the architecture. In the file-sharing example, initiate-and-complete upload protects visibility, metadata owns the lifecycle, object storage holds bytes without owning access, and search remains repairable derived state. High-Level Architecture and Data Flow can now place those decisions on a causal path. For practical-round depth, review API Design and Data Modeling and SQL.