Intake and the Legacy-Field Pattern

The hardest thing a research database has to represent is not a fact. It is the precise shape of an absence: not known, not looked for, known but withheld, or contested. Flatten those into one null and you have destroyed information that took someone an afternoon to establish.

Section 1Two bad options

Return to 林文德, official 3021, whose birth year loaded as 民國45年.

That string is correct. It is the forty-fifth year of the Republic of China, which is 1956. The source said it, the researcher transcribed it faithfully, and nothing is wrong with it — except that no query can compute with it. “What was the average age of party leaders at appointment?” cannot be answered from a text column containing mixed era years, Gregorian years, and nulls.

So something has to change. The two obvious moves are both wrong.

The pattern in this chapter takes a third route: add, never alter. Keep the original exactly as it is, under a name that says what it is. Add validated fields beside it. Let a rule and a researcher fill those in, with evidence, one record at a time.

Section 2What intake may not do

Intake is a procedural-control layer. It creates or maps canonical records, preserves source evidence, and reports procedural failures. It does not evaluate research quality and it does not make audit decisions.

Three prohibitions, each of which is violated routinely in systems built without them.

It does not guess

An unparseable value is preserved verbatim and left unreviewed. Converting 民國45年 to 1956 without a recorded human decision manufactures certainty the lab does not have — and, worse, manufactures it at scale, uniformly, so that the resulting errors are correlated and invisible.

This is not an argument against automation. It is an argument about where the automation's output goes. A script that proposes 1956, for a researcher to confirm against the bulletin, is useful. A script that writes 1956 is fabricating evidence.

It does not create audit decisions

A row that loads with a clean, four-digit, Gregorian birth year has not thereby been approved by anyone. The architecture contract states the prohibition explicitly: do not create a synthetic violation, resolved violation, de facto approval, or completeness attestation for a subject that satisfies a rule.

The temptation is real, because it makes reporting tidier — every record would have an audit trail. But an attestation nobody made is worse than no attestation, since a reader cannot distinguish it from one somebody did make.

It does not silently drop rows

Covered in Chapter 3: a row that cannot be represented is a reported procedural failure, not a disappearance.

Section 3The legacy-field pattern

Five steps. This is the core recipe of the chapter, and it generalises to any field where the source format and the analytical format differ.

Step 1 is a preserving transform — a class C migration in Chapter 2's taxonomy — and it gets the full rehearsal treatment.

Section 4Eleven columns for a birth year

Applying the pattern to birth information produces this. It looks like a lot for one date; §5 explains why each one is load-bearing.

Here is 林文德 after a researcher has resolved him:

Three things are worth reading off that table.

The legacy value never moves. If in three years someone disputes the conversion, the original is still there, unmodified, alongside a recorded decision and a cited source.

birth_date stays null. It is a derived convenience populated only when precision is day. Writing 1956-01-01 and calling it a date would be a fabrication, and a particularly insidious one because it looks like data.

The era encoding is preserved as structure. roc_solar and 45 mean that anyone can independently verify the conversion, and that a systematic parser error could later be found and corrected across every affected record.

Section 5The status vocabulary

This is the most important idea in the chapter, and the one most often absent from otherwise careful systems.

Consider a naive completeness rule: every official must have a birth year. Now give it to a researcher and watch what happens. They work through the queue. Most records resolve. Then they reach 陳淑芬, official 3104, whose birth year is not recorded in any source they can find.

The rule demands a value. The researcher has no value. They have three options: leave it in violation forever, which clogs the queue; skip it, which is untracked; or put something plausible in.

People are conscientious, and under a queue that will not clear they will eventually estimate. A completeness rule with no vocabulary for absence is a machine that converts unknown into wrong — permanently, and at scale, because the resulting values are indistinguishable from established ones.

The fix is to let the researcher say something true in every case:

The principle, stated in the architecture contract: a guessed value must never be required merely to clear a violation.

Why unknown and not_collected are both needed

They look redundant. They are the difference between a finished task and an unstarted one.

unknown means a researcher searched and the information does not appear to exist in accessible sources. That is a finding. It tells the next person not to repeat the search, and it tells an analyst that the missingness is a property of the historical record.

not_collected means nobody has looked, and by present scope nobody will. That is a scope statement. It tells the next person that the search is still available if their question needs it.

An analyst treating missing data has to know which is which. If birth years are missing because they are genuinely unrecorded, that missingness may correlate with era, region, or prominence — and is itself a finding worth reporting. If they are missing because the lab only collected them for a subset, the missingness is an artefact of scope and must be handled entirely differently.

And conflicting

The sixth value is the one that most systems lack and most research needs. 黃建宏's two records differ by two years. Once identity is resolved, if they turn out to be one man, the lab holds two sourced, contradictory birth years.

Forcing a choice would discard evidence. conflicting lets the database record the actual state of knowledge: two sources, both cited, in disagreement. That is not a failure to clean the data. It is a finding, and for some research questions it is a more interesting one than the birth year would have been.

Section 6Representation, not completeness

Step 4 adds a constraint. Its scope is narrow and the narrowness is the point.

def add_intrinsic_constraint(apps, schema_editor):
    """Enforce representational validity, not audit completeness."""

-- the constraint body:
ALTER TABLE twpolitics.officials
  ADD CONSTRAINT twp_official_birth_intrinsic_ck CHECK (
        birth_status IN ('unreviewed','known','unknown',
                           'not_collected','withheld','conflicting')
    AND (birth_year   IS NULL OR birth_year   BETWEEN 1 AND 9998)
    AND (birth_month  IS NULL OR birth_month  BETWEEN 1 AND 12)
    AND (birth_day    IS NULL OR birth_day    BETWEEN 1 AND 31)
    AND (birth_precision IS NULL OR
         birth_precision IN ('year','month','day'))
    AND (birth_qualifier IS NULL OR
         birth_qualifier IN ('exact','approximate'))
    AND (birth_source_calendar IS NULL OR
         birth_source_calendar IN ('gregorian','roc_solar'))
    AND CASE WHEN birth_status = 'known' THEN ... END
  );

Notice what is absent. There is no requirement that birth_year be populated. That question belongs to a rule, because rules are versioned and constraints are not. A lab's completeness standard will change; the fact that months run from 1 to 12 will not.

The test to apply to any proposed constraint: could a correct, honest research record fail this? If yes, it belongs in a rule. If no — if failing it means the value is not even expressible — it belongs in the schema.

The trailing CASE WHEN birth_status = 'known' clause is worth a note, because it is the one place the constraint does encode a relationship: if the status claims the value is known, the value fields have to be coherent with that claim. That is still representational. It does not require anything to be known; it requires that a record not claim knowledge it does not carry.

Section 7Bootstrap versus delivery

Adding eleven columns to a table with 50,902 existing rows raises a question the schema cannot answer: what goes in them, for rows that predate the rule?

There are two distinct transactions here, and conflating them is a documented failure mode.

Setting birth_status = 'unreviewed' across 50,902 existing officials is a bootstrap. It is not intake, it is not a scan, and labelling it as either is how a routine backfill ends up running without the guards a production data mutation deserves. The contract's phrasing is blunt: rule setup must never be mislabeled as new-data intake.

Section 8Minting provisional records

One more intake operation, which will matter a great deal in Chapter 8.

A source records that 吳美玲 chaired a party branch in 2003. The party-leadership row can be created — party, position, dates are all present. But official_id is null, because no existing official record has been matched to her.

Three options. Leave it null: honest but unqueryable, and she appears in no analysis of party leaders. Guess at an existing official with a similar name: fast, and sometimes wrong in a way nobody will ever detect. Or mint a provisional official: create a new record asserting only that a person was observed in this role.

The third is right, because it is the only one that is both true and usable. It asserts exactly what the source supports, and it creates something that can be referenced, analysed, and later merged if she turns out to be someone already in the database.

Four constraints:

• Conservative. Mint only where the reference is absent. Never re-mint where an evidenced link already exists — that destroys a known-good link and manufactures work.
• Idempotent. Replaying the routine over the same source rows creates nothing new.
• Marked. A provisional record is distinguishable from an established one, so downstream consumers and rules can treat it appropriately.
• Scoped by an explicit exclusion list. For Taiwan, do not mint for family_records alters. A person named as someone's relative is not thereby an official, and minting would defeat the referential rule whose whole job is to match family mentions to existing officials without creating new ones.

Minting is invoked through an explicit contract, never as a side effect of a form. The architecture contract requires it: creating a provisional target, when permitted, is an intake procedure invoked through an explicit contract rather than an implicit FK-form side effect.

The data is now in, attributed, and structured so that a researcher can improve it without destroying it. What is still missing is the machinery that decides which records need attention and routes them to people. That is the audit layer, and Chapter 6 takes apart its central object: the rule.