Chapter 5: LLD Deliverables: Database Schema and API Contracts

Why this matters

A database schema and an API contract are the two LLD deliverables most likely to be consumed by people (or systems) outside the immediate design conversation — other engineers writing queries against the schema, or entirely separate teams/services integrating with the API contract. Getting these precisely specified matters more than almost any other LLD artifact, because ambiguity here doesn't stay contained to one component's implementation — it propagates outward to every consumer.

Database schema: precise enough to actually create the tables from

[BEGINNER] Directly building on the companion Database book's Chapter 2: an LLD's database schema deliverable should specify tables, columns with types, keys, and constraints — precisely enough that CREATE TABLE statements could be written directly from it with no further design decisions needed.

CREATE TABLE bookings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    guest_id UUID NOT NULL REFERENCES guests(id),
    room_id UUID NOT NULL REFERENCES rooms(id),
    check_in DATE NOT NULL,
    check_out DATE NOT NULL CHECK (check_out > check_in),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE INDEX idx_bookings_room_dates ON bookings(room_id, check_in, check_out);
-- indexed specifically to support the availability-check query pattern
-- (companion Database book, Chapter 6's composite index guidance)

[SENIOR INSIGHT] Note the CHECK (check_out > check_in) and CHECK (total_cents >= 0) constraints — directly the companion Database book's Chapter 2 lesson about pushing invariants into the schema itself, structurally, rather than trusting every application code path to enforce them by convention. An LLD schema deliverable that omits these constraints "because the application already validates this" is leaving a real, structural protection on the table for very little cost.

API contracts: precise enough to build against independently

[BEGINNER] An API contract specifies the exact request/response shape for every endpoint — precisely enough that a client-side engineer (or an entirely separate team) could build against it correctly without needing to ask clarifying questions or inspect the actual implementation.

## POST /bookings
### Request
{
  "guestId": "uuid",
  "roomId": "uuid",
  "checkIn": "2026-03-01",   // ISO 8601 date
  "checkOut": "2026-03-05"
}

### Response (201 Created)
{
  "bookingId": "uuid",
  "status": "pending",
  "totalCents": 45000
}

### Error responses
- 400 Bad Request: checkOut is not after checkIn, or dates are malformed.
- 409 Conflict: the room is not available for the requested date range.
- 404 Not Found: guestId or roomId does not reference an existing record.

[IN PRACTICE] The error response section is frequently the part omitted from a rushed API contract, and it's frequently the part a consuming client actually needs most — a client integrating against this API needs to know not just the happy path, but every distinct failure mode it needs to handle differently (a 409 conflict probably needs different user-facing messaging than a 400 validation error), directly the companion Coding Standards book's Chapter 5 point about using a small, consistent set of well-defined error types that callers can handle programmatically, now applied at the API-contract-documentation level.

Why these two deliverables specifically need the most precision

[SENIOR INSIGHT] Class and sequence diagrams (Chapter 4) primarily inform the engineers implementing within one component — some ambiguity there gets resolved through direct conversation during implementation, since everyone involved is on the same team, working closely together. A database schema and an API contract, by contrast, are consumed by parties who may not be in that same close conversational loop at all (a separate team building a client, a future engineer querying the schema months later with no memory of the original design discussion) — ambiguity here can't be resolved through quick, informal clarification, which is exactly why these two deliverables warrant the highest bar for precision and completeness among all of LLD's artifacts.

Common mistakes

1. Specifying a database schema without constraints, relying entirely on application-level validation and missing the structural protection a CHECK/NOT NULL/foreign key constraint provides for free.
2. Specifying an API contract's happy path without its error responses, leaving consuming clients to discover failure modes through trial and error rather than through the contract itself.
3. Treating schema and API contract precision as equally important to class/sequence diagram precision, missing that these two specifically are consumed by parties outside the close conversational loop and therefore warrant the highest precision bar.
4. Not indexing a schema deliverable according to the system's actual known query patterns (established in the HLD's data flow, Chapter 3), leaving performance as an afterthought rather than a designed-in property of the schema.

Discussion / interview questions

1. "What should a complete database schema LLD deliverable include, beyond just table and column names?" Strong answer: Data types, primary/foreign keys, constraints (CHECK, NOT NULL, UNIQUE) that structurally enforce business invariants, and indexes aligned with the system's actual known query patterns — precise enough that CREATE TABLE statements could be written directly from it.

2. "Why should an API contract document error responses as thoroughly as the happy-path response?" Strong answer: A consuming client needs to handle each distinct failure mode differently (a validation error versus a conflict versus a not-found), and without documented error responses, the client team has to discover these through trial and error rather than through the contract — an incomplete contract pushes discovery cost onto every consumer.

3. "Why might database schemas and API contracts warrant more precision than a class diagram meant for the same component's internal team?" Strong answer: Class diagrams are primarily consumed by engineers implementing within the same close conversational loop, where ambiguity can be resolved through quick, informal clarification; schemas and API contracts are frequently consumed by parties outside that loop (other teams, future engineers with no memory of the original discussion) who can't resolve ambiguity as easily, warranting the highest precision bar among LLD artifacts.

4. "Why would you add a CHECK constraint to a database schema even if the application already validates the same rule in code?" Strong answer: A database constraint provides structural, universal enforcement regardless of which code path writes to the table — application-level validation alone can be bypassed by a different code path, a script, or a future engineer who doesn't know the convention exists (directly the companion Database book's Chapter 2 and OOP book's Chapter 1 "structural prevention over convention" theme).

Chapter summary / cheat sheet

• A database schema LLD deliverable should be precise enough to create tables directly from — types, keys, CHECK/NOT NULL constraints (structural invariant enforcement), and indexes aligned with known query patterns.
• An API contract should specify exact request/response shapes for both the happy path AND every distinct error response a consumer needs to handle differently.
• These two deliverables specifically warrant the highest precision bar in LLD, since they're consumed by parties outside the immediate, close conversational loop who can't resolve ambiguity through quick informal clarification.
• Constraints and documented error contracts are both instances of the same underlying principle: push correctness into the structure itself, rather than relying on every consumer to independently get an unstated convention right.

---