Designing the Schema

A canonical schema that never needed changing once audit and release requirements arrived would be a schema designed by someone who already knew the answers to research questions they had not yet asked. Schema change is not a failure. It is what learning about your data looks like.

Section 1Starting from a blank page

The Taiwan Politics database begins with a question a political scientist would recognise: who holds power in Taiwan, through what positions, in what organisations, and how are they connected?

Turning that into tables is the ordinary work of data modelling. Entities become tables, relationships become foreign keys. The lab does this in dbdiagram.io, a browser tool that takes a compact text notation and renders an entity-relationship diagram.

Table officials {
  official_id       integer  [pk]
  official_name     varchar
  official_birthyear integer
  official_sex      varchar
}

Table party_leaders {
  party_leader_id           integer  [pk]
  official_id               integer
  party_id                  integer
  party_leader_position_name varchar
  term_startdate            integer
  term_enddate              varchar
}

Ref: party_leaders.official_id > officials.official_id
Ref: party_leaders.party_id > parties.party_id

dbdiagram is genuinely good at this stage. It is fast, it renders a picture you can show a collaborator who does not read SQL, and it exports PostgreSQL DDL. For a schema that does not exist yet, it is the right tool.

Two things in that fragment will matter later, and it is worth noticing them now.

First, party_leaders.official_id has no [not null]. That is deliberate — Chapter 1's first consequence. A party-leadership record whose person has not yet been matched to an official must still be admissible.

Second, look at the date columns: term_startdate is an integer and term_enddate is a varchar. That is not a typo in this tutorial; it is what the real schema says. The sources gave start dates as years and end dates as messy strings, and the schema faithfully recorded that. It is honest, and it also means those two columns cannot be compared as dates — a problem we will return to when we build a consistency rule in Chapter 6's sequel.

Section 2The anxiety, stated

Here is the worry, in the form most people actually feel it:

“The intake layer, the audit layer, and the download layer are all going to force changes to the schema I designed. Does that mean I designed it wrong? Should I have anticipated all of them? And once I start changing it, which artefact is the real schema — my diagram, or the database?”

Three questions. The answers, in order: no; no, except for three specific things; and that last one is the only question that actually matters.

Consider what the Taiwan schema has acquired since it was first drawn:

Two of those seven were foreseeable, and both were retrofitted at real cost — the UUID backfill ran over a quarter of a million rows. The other five came out of contact with the actual data and could not have been designed in advance without inventing a vocabulary that would have been wrong.

So: go with the flow. But reserve the two things that were foreseeable, plus one more. Section 5 has the list.

First, though, the question that actually matters.

Section 3The ownership rule

The anxiety is not really about change. It is about two artefacts both claiming to define the schema. If your diagram says one thing and your database says another, you no longer have a design; you have two conflicting documents and no way to tell which is authoritative.

The rule that removes this, permanently:

dbdiagram.io owns the schema until the first successful load. Django migrations own it from that moment forward, permanently.

After the handoff, the dbdiagram file is no longer a design document. It is a picture. When you need a picture — for a paper, a codebook, a conversation with a collaborator — you regenerate it from the live database. You never edit it and replay it.

This dissolves the anxiety, because there is now exactly one authoritative artefact at every moment, and a clearly marked instant when authority transfers.

Section 4Why it is enforced in code

Rules that live only in documents get broken by well-meaning people in a hurry. This one is enforced where it cannot be missed.

The original Taiwan loading pipeline lives in postgres/scripts/twpolitics/ as three numbered scripts. The first builds the schema. Here is what it does, and what happened to it:

def run_schema(sql_file):
    raise RuntimeError('Destructive Taiwan schema rebuild is retired. '
                       'Use a preserving, versioned migration.')
    engine = get_engine()
    with open(sql_file, "r", encoding="utf-8") as f:
        sql = f.read()
    with engine.begin() as conn:
        conn.exec_driver_sql(sql)

Why so blunt? Because of what the SQL file contains. Before executing, the script appends drop_twpolitics_schema.sql to the schema definition. The combined script drops the entire schema and recreates it.

That was completely correct behaviour in week one, when the tables were empty and rebuilding was the fastest way to iterate on a design. Run today, against 252,271 canonical rows, thousands of audit decisions, and every piece of provenance the lab has assembled, it would be a catastrophe from which the only recovery is a backup restore.

The script was not deleted, and that is deliberate. It remains as documentation of how the schema was originally built, with a guard that makes it impossible to run. The module-level entry point carries the same raise, so even importing and calling it directly fails.

Section 5Three things to reserve

Most later additions are cheap. Three are expensive enough to be worth designing in from the start.

1. A stable identifier that is not the business key

Every canonical row needs an identifier that will still be valid after the row has been corrected, merged, or re-sourced.

A natural key will not do. A name, a district code, an election year — these are exactly the values the audit layer exists to change. An identifier built from data that can be corrected is an identifier that can be invalidated by a correction.

The Taiwan database retrofitted this: every one of its 252,271 canonical records now carries a stable external UUID. It worked, but it cost a migration over the whole dataset plus a reconciliation exercise to prove nothing had been mismatched. Designing it in costs one column.

2. A provenance hook

One nullable column per canonical table, pointing at the immutable record of the source row that produced it. In Taiwan this is intake_source_id, a one-to-one relation to audits.IntakeSource, present on twelve tables.

The column itself is trivial — a nullable foreign key. What is expensive is reconstructing, eighteen months later, which spreadsheet cell produced which row. The lab is doing exactly that reconstruction now: a retained 139,146-row source snapshot and a 2,954-row normalisation are being reconciled against canonical records after the fact, and the work is substantial.

Add the hook at creation even if nothing populates it on day one. An empty column costs nothing; an absent one costs a research project.

3. Nullability for anything a researcher will complete later

This follows directly from admit-then-audit, and it is the one people get wrong because it feels like bad practice.

A NOT NULL on a field that researchers are supposed to fill in means the row cannot be admitted. If it cannot be admitted, the audit system never sees it. If the audit system never sees it, nobody is ever assigned the task of filling it in. The constraint intended to guarantee completeness has instead guaranteed that the incomplete records are invisible.

So: officials.official_birthyear nullable. officials.official_sex nullable. party_leaders.official_id nullable. These are the design working, not the design failing.

Section 6What not to anticipate

Having reserved those three, resist reserving more. Specifically, do not pre-create:

• Audit, issue, or evidence tables in the domain schema. They belong in a separate audits schema. Mixing them means a researcher browsing the domain tables cannot tell which rows are data and which are bookkeeping.
• Per-rule status or flag columns. Chapter 1 covered why: they go stale silently. The architecture contract forbids them.
• Validated companion fields on speculation. Add birth_status, birth_precision, and birth_source_calendar when the birth rule is designed, not a year earlier. You will get the vocabulary wrong if you guess it before seeing the data, and a wrong vocabulary that has already been populated is far more expensive than an absent one.

The general shape of the advice: reserve structure, not semantics. An identifier and a provenance hook are structural — they do not encode any claim about what the data means. A status vocabulary is semantic, and semantics should follow evidence.

Section 7Four classes of change

After the handoff, every schema change is a Django migration, and they are not all equally dangerous. Sorting them into four classes makes it obvious how much ceremony each one deserves.

Prefer class A, always

When a choice exists, express a change as add something rather than alter something.

If a legacy column has the wrong type, do not retype it. Add a correctly typed companion and leave the original alone. That is exactly the official_birthyear → legacy_birthyear plus eleven birth_* columns pattern, and it is better than an in-place retype for two reasons: it is trivially reversible, and it preserves the original value as evidence rather than destroying it in the act of improving it.

Class C needs a rehearsal, not a code review

A rename looks harmless. It is not, because you cannot tell by reading it whether it will preserve everything you need preserved. The standard this project uses:

1. Write the migration reversible in both directions.
2. Restore a frozen snapshot of production to a throwaway database.
3. Run it forward, backward, and forward again.
4. Prove that table OIDs, sequence OIDs, row counts, and content fingerprints are unchanged.
5. Add guards that refuse partial or colliding states, and that no-op on SQLite.
6. Then ask for approval.

The Taiwan career-table relocation is the worked example: three tables and two sequences moved between schemas, with all three table OIDs, both sequence OIDs, every row count, and the complete 95,055-row resume-event and 50,902-row career-sequence view outputs verified identical afterwards.

Class D: prefer archival

Destructive migrations cannot be undone, so they need an immutable export with checksums before execution, and approval that names exactly what is being destroyed.

But reach for an alternative first. When the lab needed to retire 41 obsolete rule versions, it did not delete them. It ran an archive cutover: the rules were deactivated and removed from current navigation, while every registry row, violation, decision, and piece of evidence stayed exactly where it was, reachable through read-only archive routes. The researchers got a clean slate; the historical record survived intact.

That pattern — deactivate and hide, rather than drop — solves most cases that look like they need a class D migration.

Section 8The flow, in order

For a genuinely new schema, the full sequence:

Step 7 deserves a note, because “a migration that emits no SQL” sounds pointless. Its job is to tell Django's migration system that these tables exist and are now managed, without touching them. The proof that it worked is that the database is byte-identical afterwards.

For an existing schema, the handoff may not have happened yet. In this project it has been completed for twpolitics and not for skpolitics, whose models file is currently three lines long. Steps 6 through 8 are the entire job there — which is why porting the Taiwan rules to South Korea is not simply a matter of re-pointing them at a different schema.

Keeping the picture current

One loose end. If dbdiagram stops being authoritative, something has to keep an accurate diagram available — for codebooks, for the public schema description each database's About page promises, and for onboarding.

The answer is to generate it from the database, by reading information_schema and emitting DBML or an ERD. It is a small script and it eliminates an entire category of drift.

Until that exists, treat the checked-in SQL files as dated snapshots rather than current state. They already are: postgres/schemas/tw_politics.sql shows none of the eleven birth_* columns, none of the sex_* columns, no legacy_birthyear, and no provenance tables.

The next chapter takes the staged spreadsheets and actually loads them — and shows what has to be preserved at that moment if it is ever to be recoverable.