skip to content

When one team runs both mapping-first generation and hand-written scripts against the same database, how do you decide who owns the schema?

level: principalimportance: should knowfreq 40%

answer

  1. one source of truth, one derived
  2. mapping-first versus schema-first
  3. two writers, no arbiter
  4. fail the build when they disagree
  5. authority follows accountability

basics

~20 s

Pick one artifact as the source of truth — the mapping or the scripted schema — and make the other derived and continuously checked against it. Either direction works; two independent writers with no arbiter is what produces drift nobody can resolve.

solid answer

~40 s

There are two coherent workflows. **Mapping-first**: the mapping is authoritative, changes are drafted by diffing it against the last known schema, then reviewed. **Schema-first**: the database is authoritative, scripts are written by hand, and the mapping is reverse-engineered or hand-aligned to it. Both ship successfully; the failure is running both at once with no agreement about which wins, so a hand edit and a regenerated mapping each undo the other. Decide by asking who else reads the database, whether the schema needs features the mapping cannot express, and which artifact your reviews actually inspect. Then make the choice enforceable: derive the second artifact mechanically, and fail the build when the two disagree. Keep the environment gradient explicit — generation is allowed to write on a laptop and nowhere else.

go deeper

for a junior

Know that a project decides, once, whether the code's mapping or the database is the definition of the schema, and that the other side is kept in step with it rather than edited independently.

for a middle

Contrast the two workflows concretely: what is authored by hand, what is produced from it, and the characteristic way each one drifts when someone bypasses the rule.

for a senior

Bring the enforcement mechanism: replay the scripts into an empty database in the build, compare against the mapping, fail on a difference, and remove write access from the non-owning side in deployed environments.

for a principal

Treat it as an ownership and accountability question. Ask who else consumes the schema, what the mapping cannot express, and who answers for a bad deployed shape; the tooling should follow that answer.

## Two coherent workflows, one incoherent mixture | | Mapping-first | Schema-first | |---|---|---| | **Source of truth** | The mapping in the codebase | The schema, evolved by reviewed scripts | | **How the other side is produced** | A change is drafted by diffing the mapping, then reviewed and edited | The mapping is reverse-engineered, or written by hand to match | | **Review artifact** | The generated-then-edited script | The hand-written script | | **Typical drift** | Someone edits a database directly and the mapping never learns | Someone regenerates the mapping and overwrites human refinements | | **Fits when** | The service owns its tables outright and the model leads the design | The database predates the service, is shared, or needs features the mapping cannot express | Both columns describe teams that ship. The dangerous state is the third one: **two writers, no arbiter**. A hand-applied fix that the mapping does not know about will be proposed for removal by the next generated diff; a regenerated mapping will discard naming and structure a human chose deliberately. Each side keeps undoing the other, and because both are acting reasonably, the argument never resolves on its merits. ## The questions that actually decide it 1. **Who else reads these tables?** Reporting jobs, another service, an analytics extract, an operations team writing ad-hoc queries. The more consumers the schema has outside this codebase, the less defensible it is for one application's mapping to be its definition. 2. **Can the mapping express what the schema needs?** Partitioning, specialised or partial indexes, storage options, check constraints, views, generated columns. If important parts of the schema live outside what the mapping models, mapping-first quietly means "the mapping owns some of it", which is not ownership. 3. **Which artifact does review actually inspect?** Reviewers read what is in the change. If nobody in the team can read a schema statement critically, mapping-first with a generated draft is honest about where the competence sits — and the opposite is also true. 4. **Who is accountable when the deployed schema is wrong?** Ownership should sit with the group that carries the consequences and has the authority to say no. 5. **How closely does the model track the schema?** A rich model whose shape drives the tables favours mapping-first; a stable integration-shaped database that many things depend on favours schema-first. ## Making the decision enforceable A decision that lives in a document loses to whoever types fastest. Give it teeth: - **Derive the second artifact mechanically.** In the build, construct a database by replaying the scripts from empty, then compare it with the mapping and **fail on any difference**. That single check makes the two shapes agree by construction, whichever one you declared authoritative. - **Take write access away from the non-owner.** If scripts own the schema, no deployed environment may run the layer in a mode that writes; the layer validates or does nothing. - **Make direct edits visible rather than forbidden.** They will happen during incidents. What matters is that the same comparison detects them the next morning, and that the fix is folded back into the owning artifact. - **Write the environment gradient down.** Generation may rebuild a laptop or a per-test database; every shared environment is scripted. Ambiguity here is where "it worked on my machine" schemas come from. ## What each choice costs Mapping-first buys speed and keeps one shape in one place, and pays for it in reviewability: the change reaching production is machine-drafted, and the parts of the schema the mapping cannot model have no home. Schema-first buys explicit, reviewable evolution and full access to the engine's capabilities, and pays for it in duplication — every change is made twice, in the script and in the mapping, and the two can disagree between those two moments. ## The judgment to voice The interesting answer is not which workflow is better. It is that **schema authority is an organisational fact before it is a tooling choice**: whoever can change the deployed shape owns it, whatever the documentation says. The tooling's job is to make that ownership match the accountability. When a team cannot say in one sentence which artifact wins, the drift they are complaining about is a symptom, and re-running the generator will not fix it.

  • How do you actually detect that the mapping and the scripted schema have diverged?
    Build a database in the pipeline by replaying the scripts from empty, then run the same structural comparison the layer would run at startup, and fail on any difference. It is cheap, it needs no shared environment, and it converts drift into a build failure rather than a deployment surprise.
  • The database is shared with other consumers but the team wants mapping-first. What do you propose?
    Narrow the claim rather than the workflow. The mapping may own the tables only this service writes; everything shared is scripted and treated as an interface with its own review. Write the boundary down, and enforce it by giving the service an account that cannot alter the shared objects.
  • Why not simply let each developer use whichever workflow they prefer locally?
    Locally is fine, and rebuilding a throwaway database from the mapping is a genuine productivity win. What must not vary is what reaches a shared environment: one artifact of record, reviewed, with the build proving the two shapes still agree.

saying these in an interview costs you the question

  • Declares one workflow universally correct without asking who reads the database
  • Runs both workflows and calls the resulting drift a tooling problem
  • Assumes the mapping can express everything the schema needs
  • Leaves the ownership decision written down but unenforced
  • Regenerates the mapping over hand-tuned definitions and calls it a sync