200apps Corpus

Appearance

Architecture & Technical Design · master area

Schema Design ​

Tables, columns, constraints — what the system remembers. Designed in the domain's language, indexed for the actual reads, with the next three migrations sketched at the same time.

Owners: Tech Lead, Developer Phase it lives in: What We Build (Volume III) The corpus principle this enacts: The artefacts the chain produces survive the conversation — including the schema.

Where it lives in the chain ​

How to do this ​

The schema is derived from the domain model, not the other way around. The discipline:

  • Names from the brief. exams.graded_at, not forms.timestamp_2.
  • Constraints in the database. Not-null, foreign key, unique, check. The database is the last line of integrity — if it can be wrong, it will be.
  • Indexes for actual queries. Read the queries the application will run; index those. Premature indexing is bloat; absent indexing is a 3am page.
  • Soft delete vs hard delete is named explicitly. A deleted_at column with no enforcement is a soft-delete pattern that 60% of queries forget to filter on.
  • Audit fields — created_at, updated_at, created_by — added by convention, not by exception.

What good practice looks like ​

The schema PR contains:

  • The migration up.
  • The migration down, tested.
  • The query patterns this schema supports, listed.
  • The expected row counts — "100K exams/year, 30 submissions per exam, 24-month retention." Catches the index decision early.
  • The next three migrations sketched in the ADR — "in 3 months we'll add cross-school sharing; in 6 we'll add language preferences; in 12 we'll partition by year." Not promised, sketched — so the current design doesn't paint itself into a corner.

A team that designs schema by accretion ends up with a database shaped like the order of the requirements, not like the domain. Every join is a translation; every report is a refactor; every migration is an outage risk. Schema designed once, with the domain in mind, pays compound interest.

200apps · How We Work · NWIRE

Internal corpus. Voice and structure © 200apps.