Taxonomy standardization playbook

How to take one domain (e.g. "valuation requests") from messy categorical data to standardized input → storage → output, anchored on the Supabase schema. Built 2026-06-12 (WS0 of the data-standardization plan; full plan in .claude/plans/standardize-the-data-input-cheeky-lemon.md).

Conventions (user-confirmed 2026-06-12)

1. English snake_case machine keys for ALL workflow/status/type vocabularies — including finance statuses and valuations_base.approval_status. The Arabic wording (incl. dialect terms like «شرينا له») is preserved verbatim as i18n display labels; PDFs/reports render labels via export/label_manifest.json, never raw keys.

2. Kuwait domain nouns stay canonical Arabic — superseded 2026-08-02. Every one of the columns this exempted is now English-canonical: property_category («سكن خاص»→residential_private), al_wada («ارض/مبنى»→land/built), valuation_property_type, document_type, building usage. Arabic is a display label everywhere, and assets/translations is keyed on the machine key, never the Arabic string. The only Kuwait domain noun still stored in Arabic is paci_areas.area_name — its area_key is the English machine key and the FK points at that; the Arabic name is the label. Raw scraper facets (real_estate_transactions.category/property_type) are also still Arabic, but those are free text from an external source, not a vocabulary (see the normalize-trigger tier).

3. Public forms store canonical values too — friendly wording lives in option labels, not stored values.

4. Flutter repo owns every writer and Edge Function — compatible writers deploy before a tighter database constraint lands. The frozen React build retains only rollback-compatible endpoint access during the transition.

5. Enforcement: CHECK constraints (the default — see the enum caveat below); FK → paci_areas for staff-input area columns; normalize-trigger, no CHECK for scraper-written tables (real_estate_transactions); '' is illegal everywhere (NULL = not provided).

   The original "no new pg enums" rule was overtaken by the H1/H2 enum waves (20260716220000, 20260717150000), which converted 18 vocabularies to real Postgres enum types — including property_category, kw_governorate, property_al_ard_status, deal_type, deal_stage, payment_method, valuation_urgency and valuation_workflow_stage. That conversion is not a free tightening: it changes the failure mode for old clients. Read the next section before converting anything else.

The pieces

Piece	Where
Taxonomy registry (source of truth)	contracts/taxonomy_registry.json
Export copy for Flutter-owned edge functions (synced like label_manifest)	export/taxonomy_registry.json via dart run tool/export_taxonomy_registry.dart, then dart run tool/sync_export_edge_bundle.dart
Conformance audit CLI	dart run tool/audit_taxonomy_conformance.dart [--emit-sql] [--db] (--db needs psql + SUPABASE_AUDIT_DB_URL, read-only role; or run the emitted SQL via the Supabase MCP)
Manual introspection SQL	tool/sql/taxonomy_introspection.sql
Property-picker option audit CLI	dart run tool/audit_property_dropdown_options.dart [--db-url=… | --input=… | --emit-sql] — see "Every offered option must be saveable and labelable" below
Lock tests (registry ↔ Dart consts ↔ i18n catalog)	test/tool/taxonomy_registry_lock_test.dart
Lock test (picker field keys ↔ audited columns)	test/tool/property_dropdown_saveability_test.dart
CI lane	.github/workflows/taxonomy.yml (static every PR; live-DB weekly/dispatch) + .github/workflows/contract.yml (option audit against the migration replay, every relevant PR)
Normalization infra (live since 2026-06-12)	public.taxonomy_aliases + public.taxonomy_normalize() BEFORE-row trigger + public.taxonomy_normalization_log — migration supabase/migrations/20260612120000_taxonomy_normalization_infra.sql
Alias seed (824 rows / 90 columns)	supabase/migrations/20260803120000_seed_taxonomy_aliases.sql. The trigger resolves nothing without it — it was live-only until 2026-08-03, so every database built from migrations had an empty alias table and a no-op normalizer

Registry column fields

tier ∈ hard-check (CHECK constraint or pg enum type — the registry does not distinguish them, but they behave differently for old clients; see "The safety net does NOT cover pg enum columns" below) · curated-catalog (dropdown_options-backed, CHECK where data allows) · reference-fk (FK to a reference table, e.g. paci_areas) · normalize-trigger (trigger-only — scraper/external writers must never break) · free-text (legitimately free; never enum). status ∈ planned → drifted → conformant (audit hard-fails regressions) · grandfathered (deliberately non-canonical; audit warns only). lock_schema_const: true pins a Dart const (mapped in the lock test) to the vocabulary.

is_array: true marks a text[]/array column. It is not cosmetic — the census generator emits unnest(col) instead of col::text, _auditForeignKeys skips the column (no per-element FK), and generate_taxonomy_audit_sql.dart picks its EXISTS branch from it. Get it wrong and the audit measures the wrong thing rather than erroring: on 2026-08-07 properties_base.marketing_method (text[]) was registered without the flag, so the census compared the stringified array {social_media,in_market,direct_customer} against the scalar accepted set and a registry bug arrived as a hard data-conformance failure. The --db audit now cross-checks the flag against pg_attribute/pg_type (array-flag-mismatch, always fail, at every status) — so set the flag in the same PR as the migration that creates the array column.

The live --db audit also builds its stored-value census from that same catalog snapshot. Registry columns absent from public tables are reported in a note and excluded from the live census query, so a renamed or removed column cannot abort the entire audit with a PostgreSQL missing-column error. Static --emit-sql output remains registry-based.

To tell which flavour a hard-check column actually is:

select a.attname,
       case when t.typtype = 'e' then 'pg enum: ' || t.typname else 'text + CHECK' end
from pg_attribute a
join pg_class c on c.oid = a.attrelid
join pg_type  t on t.oid = a.atttypid
where c.relname = 'properties_base' and a.attnum > 0 and not a.attisdropped;

Rollout-safety mechanism

Old mobile builds keep writing legacy values for weeks (app-store lag). The taxonomy_normalize() BEFORE-row trigger rewrites legacy → canonical (per taxonomy_aliases) before CHECK evaluation, so every domain ships a canonical-only CHECK on day one without breaking old clients, offline-queue drains, or in-flight web writers. taxonomy_normalization_log counts rewrites per day; a domain tightens only after ~30 quiet days.

⚠ The safety net does NOT cover pg enum columns

This is a hard boundary, not a nuance. A BEFORE-row trigger runs on an already-typed NEW record, so for a pg enum column the value must survive the cast before the trigger body ever executes. Postgres rejects it first:

insert into properties_base (property_category, …) values ('سكن خاص', …);
ERROR:  invalid input value for enum property_category: "سكن خاص"

No alias lookup happens. taxonomy_normalize() cannot help, and neither can seeding taxonomy_aliases. Verified 2026-08-03 on a clean replay: on the same row, al_hala/al_wada (text + CHECK) folded ممتازة→excellent and مبنى→built, while property_category (enum) errored at the cast.

tier	legacy value from an old client	net effect
text + CHECK	rewritten by the trigger, then passes	absorbed — old clients keep working
pg enum	rejected at the cast, 22P02	hard write failure — the client's save fails

Consequences:

• Converting a column to a pg enum ENDS its legacy-tolerance window. Do it only once taxonomy_normalization_log has been quiet for that column for ~30 days and the oldest supported mobile build emits canonical values. Step 9 ("Tighten") is the earliest safe point, not step 5.
• Once a column is an enum, its *Legacy Dart const and its taxonomy_aliases rows are dead weight for writes — nothing can reach them. Keep them only if a reader still needs to interpret historical values from elsewhere.
• The audit's "is Arabic possible here?" question therefore has two different answers: for enum columns it is structurally impossible; for CHECK columns it is possible-but-rewritten, which is why the alias seed matters.
• Prefer CHECK for any vocabulary that is still moving. Reach for an enum only when the value set is genuinely settled and you want writes to fail loudly.

taxonomy_aliases itself is seeded by supabase/migrations/20260803120000_seed_taxonomy_aliases.sql (824 rows / 90 columns). It was live-only until then — zero rows on any database built from migrations, which made the trigger a silent no-op on CI and made every "legacy is folded before the CHECK" test vacuous. Four pgTAP assertions in supabase/tests/database/property_enum_constraints_test.sql now fail loudly if that seed is lost again.

Per-domain steps

Tags: [REG] registry · [DB] migration · [FLT] Flutter · [EDGE] Flutter-owned edge functions · [REL] release/ops.

1. [REG] Census. dart run tool/audit_taxonomy_conformance.dart --emit-sql, run census.sql (psql or Supabase MCP). Capture every stored variant + count. Author/extend the registry entries: canonical set (apply the language rule), alias map (every observed variant → canonical), tier, null policy. Columns with entity-like cardinality (e.g. clients.lead_source_detail broker names) → reclassify free-text + file a separate entity-extraction task.

2. [REG] PR the registry change (census pasted into the PR). Rerun dart run tool/export_taxonomy_registry.dart. The lock-test failure list is the Flutter work order.

3. [FLT] Writers. lib/data/schemas/<domain>.dart: ...Canonical = registry values, ...Legacy = alias keys (validators accept both so editing a legacy row never 4xxs — valuations.dart pattern). Add ChoiceI18nEntry labels for every canonical key; regenerate: flutter test test/tool/write_export_label_manifest_test.dart then dart run tool/sync_choice_i18n_keys.dart. Pickers emit canonical only (free TextFormField → LocalizedChoiceDropdown/AreaPickerField with the dynamicChoiceOptions(fieldKey, fallback) merge).

4. [EDGE] Mirror. Edge-function zod schemas accept canonical+legacy; writers normalize via the synced _shared/export/taxonomy_registry.json aliases. Deploy the Flutter-owned edge functions BEFORE step 5.

5. [DB] Migration — normalize + protect (house shape, one file; clone supabase/migrations/20260609180000_standardize_client_property_taxonomy.sql): a. CREATE TABLE _canon_backup_<domain>_<date> AS SELECT id, <cols> … b. Optional _canon_map_<domain> for human review of nontrivial remaps (REQUIRED for the WS9 document_type remap — never guess; unresolved rows get a needs_review flag instead). c. Disable guard triggers; DROP CONSTRAINT IF EXISTS old CHECKs. d. UPDATE remap legacy→canonical per alias map; '' → NULL; defaults. e. Seed taxonomy_aliases for the domain + attach the trigger: CREATE TRIGGER taxonomy_normalize_biu BEFORE INSERT OR UPDATE ON public.<table> FOR EACH ROW EXECUTE FUNCTION public.taxonomy_normalize(); f. ADD CONSTRAINT … CHECK (col IS NULL OR col IN (<canonical only>)) NOT VALID; then VALIDATE CONSTRAINT (no long locks). g. Sync dropdown_options (deactivate legacy rows, insert canonical); remap saved_filters + form_fields.static_options (clone …191000 + …200000). h. Re-enable guard triggers.

6. [FLT/WEB] Readers. Kanban/report buckets keyed to canonical only; unknown values go to a fail-loud quarantine bucket (never the silent byStage[x] ??= orphan-column pattern); PDFs/exports resolve labels via label_manifest; sweep vocabulary literals (e.g. 'مغلق') into lib/data/schemas/ consts.

7. [REL] Mobile release emitting canonical. Old builds are absorbed by the trigger — non-blocking.

8. [REG] Flip status to conformant — CI (taxonomy.yml) now hard-fails any regression for those columns.

9. [DB] Tighten (after taxonomy_normalization_log is quiet ~30 days): delete the domain's taxonomy_aliases rows, drop _canon_backup_*, delete the Flutter ...Legacy consts. This is also the earliest point at which the column may be converted to a pg enum — that conversion removes the trigger's ability to absorb a legacy write, so it must not happen while the log is still recording rewrites (see the enum caveat above).

   Check the gate per column, not per domain — the log is keyed on (table_name, column_name):

   select table_name || '.' || column_name as col, max(day) as last_rewrite, sum(hits) as hits
   from public.taxonomy_normalization_log
   group by 1 order by 2 desc nulls last;

   Worked example (2026-08-02): properties_base.al_hala, .sikka, .naw_al_shari and .saha had been quiet since 2026-07-01 (32 days) and were constrained; properties_base.al_wada had a rewrite that same week — driven by supabase/functions/import-data forwarding free-form staff payloads — so its *Legacy const stayed and it was not converted.

Verification per domain

• Census anti-join = 0 non-canonical (or quarantine-only for scraper tables); pg_constraint shows the CHECK; insert-rejection smoke test.
• Report/aggregate totals snapshot-identical before/after the remap.
• flutter test test/tool/taxonomy_registry_lock_test.dart test/tool/sync_choice_i18n_catalog_test.dart green (pinned SDK: /c/Users/aalde/fvm/versions/3.44.0/bin/flutter test).
• dart run tool/audit_taxonomy_conformance.dart --db → zero fail findings.

Workstream order

WS0 foundations (done 2026-06-12) → WS1 free wins (empty/clean tables) → WS2 live-bug batch (interactions EN/AR, client_notes drift, WhatsApp hamza) → {WS3 brokerage ∥ WS5 valuations spine ∥ WS6 geography foundation} → WS4 finance → WS7 geography enforcement → WS8 requests restructure (al_wasf AND al_majmua are newline-joined multi-selects) → WS9 valuations heavy remap (document_type ~65 variants) → WS10 public+AI guardrails → WS11 output hardening. Census 2026-06-12 notes: paci_areas (219 rows) is missing real areas (أبو فطيرة، الخيران، فيلكا…) — WS6 must complete the reference table itself; real_estate_transactions.area non-canonical rows are mostly '' (276 of 283).

Live admin catalogs vs static fallbacks (2026-07-01)

Some columns are curated-catalog tier: values are admin-managed in Supabase dropdown_options, with schema-canonical machine keys as the offline fallback only.

Layer	Role
Primary	dropdown_options row set (admin UI) — Flutter dynamic_dropdowns.dart, React useDynamicDropdown
Fallback	lib/data/schemas/*.dart *Canonical consts (keys only; labels from i18n)
Never	A third hand-maintained picker list in features/*/constants.dart

hard-check columns: fixed vocabulary in the registry; pickers derive from schema consts. Note the two flavours behave differently on a legacy write — deals.stage is a pg enum (deal_stage), so a legacy value fails at the cast, while requests.al_hala is text + CHECK, so the normalize trigger rewrites it first. Same tier in the registry, opposite failure modes.

reference-fk columns (al_mintaqa → paci_areas.area_key): pickers always DB-backed (AreaCanon / usePaciAreas); static kuwait_area_labels.dart is display-only fallback, regenerated via tool/codegen_kuwait_area_labels.dart.

free-text columns (properties_base.al_maktab external office names): no enum; labels are the stored value; optional dropdown_options suggestions.

normalize-trigger columns (MoJ scraper facets): labels via transactions.values.* i18n; filters normalize at the query boundary (governorateKey(), etc.).

Every offered option must be saveable and labelable (2026-08-14)

An active dropdown_options row is a promise to the user that the value can be chosen. Two independent things have to be true for that promise to hold:

1. the value has a bilingual entry in contracts/choice_labels.json for the column it writes — otherwise the Arabic UI renders the raw machine key;
2. the value is inside the column's enforced vocabulary (single-column CHECK, or the column's enum type) where one exists — otherwise choosing it fails the save with 23514.

choice_labels.json is the authority for (1), not assets/translations, even though propertyChoiceLabel consults i18n first. The catalog is the master the i18n syncer derives from, and it is what generates export/label_manifest.json — so a key present only in i18n still prints raw in PDFs and exports, where no t() exists.

setback_type / three_sides broke both. It was seeded by 20260705090134_sync_brokerage_request_dropdown_options_english.sql, never added to choice_labels.json for irtidad_type, and left outside properties_base_irtidad_type_canon (20260802123000_constrain_property_enum_columns.sql). It stayed harmless for six weeks only because a third bug — the loader discarding every categorized row — kept the picker from offering it; fixing that loader is what made it reachable. Deactivated by 20260814100000_deactivate_unsaveable_setback_type_option.sql.

The registry's own dropdown-legacy-active sweep could not have caught it. That check iterates registry columns with a catalog_key, and 11 of the 14 property picker columns carry none — it is a per-column catalog diff, not a per-value usability check.

The check

dart run tool/audit_property_dropdown_options.dart iterates every dropdown_options.field_key in propertyPickerDropdownTargets (tool/src/property_dropdown_saveability.dart) and reports each offending active option_value with its field_key, the column it writes, and which of the two checks failed (unlabelled, unsaveable, or both). Exit 1 on any offender; one offender: {json} line per offender for CI artifacts.

Run in two lanes, against two databases, because neither subsumes the other:

Lane	Database	Catches
contract.yml, after supabase db reset --local	the from-empty migration replay	a seed migration that adds a bad option — fails the PR that writes it
taxonomy.yml live-db (weekly/dispatch)	production, read-only	a row an admin added through the admin UI, which exists in no migration

Four picker catalogs were never English-synced — closed 2026-08-14

Standing this check up surfaced a second, larger gap. The Arabic→English dropdown_options sync landed as migrations for rented_status / property_condition / street_type (20260705082540), street_count / saha / sikka / setback_type / subdividable (20260705090134) and direct_type (20260609191000) — but never for property_status, property_category, built_or_land or marketing_method. Production carried the English keys because it was repaired out of band; the repo never received that change, so a from-empty replay was the only database still serving those 18 legacy Arabic rows as active. Every one was unlabelled, and the ones on properties_base.al_ard / .property_category (pg enums — 22P02 at the cast, not 23514) and on marketing_method (CHECK) were unsaveable as well.

Nothing surfaced it for six weeks because liveDropdownOptionsProvider returned {} from 2026-07-08 until #946 — every picker served its hardcoded fallback, so the broken catalog was never reached. Un-gating the feed is what made it reachable, which is why the fix had to land alongside it.

Closed by 20260814054348_sync_property_picker_dropdown_options_english.sql, in the shape the two 2026-07-05 sync migrations established: deactivate the rows matching option_value ~ '[؀-ۿ]', then INSERT … SELECT … WHERE NOT EXISTS the English canonical rows. A no-op against production (0 updated, 0 inserted) — the UPDATE only matches Arabic rows and the INSERT is guarded by the (field_key, option_value) unique key production already satisfies.

Two things to know before writing the next one of these:

• The catalog WINS over the fallback const. lib/features/properties/constants.dart is only what the picker shows when the live fetch is empty, so a catalog narrower than that const silently shrinks the picker the moment the live feed works. That is why the migration inserts all 22 values — the whole canonical vocabulary of the four pickers — rather than one replacement per deactivated Arabic row (property_category alone would have gone 11→8).
• taxonomy_normalize() folds dropdown_options.category too (الوساطة - العقارات → brokerage_properties), so write the English canonical category — it is what the trigger produces anyway, and what 20260705082540 already wrote. Production stores an off-taxonomy properties on the marketing_method rows, another artefact of the out-of-band repair; nothing in the migration can reach those rows, so they are left alone.

Because the sync ships in the same PR as the check, neither lane needs a tolerance file — both run strict. Keep it that way: a baseline here would be a committed list of options the user is offered and cannot use.

Adding or fixing an option

• Widening the vocabulary — add the value to contracts/choice_labels.json (then dart run tool/codegen_choice_labels.dart --write) and widen the column CHECK in a migration. Both, in the same PR; either alone still fails.
• Retiring the option — set is_active = false in a migration (never out of band, per the standing order in docs/), which takes the row out of scope entirely.
• A new picker — adding a key to propertyDropdownFieldKeyAliases (lib/features/properties/providers/dynamic_dropdowns.dart) without declaring the column it writes in propertyPickerDropdownTargets fails test/tool/property_dropdown_saveability_test.dart. That lock is what keeps the dependency-free tool from silently drifting behind the app; do not "fix" it by deleting the assertion.

Deliberate limits

• Active rows only. A deactivated row cannot be offered.
• Single-column CHECKs only. A multi-column CHECK is a cross-field rule; harvesting its literals would invent a closed set the column does not have.
• free-text tier waives the label half. external_brokerage_office → properties_base.al_maktab holds proper nouns with no committed catalog. Every waiver must carry a labelsWaivedBecause reason.
• A column with no CHECK and no enum is label-checked only — "where one exists" means skip, not fail. Today that is muajjar, yufrraz, street_configuration and al_maktab.
• Enum arrays ARE covered. atttypid on a myenum[] column is the array type and joins no pg_enum row, so a naive join reads an enum array as unconstrained and every option on it passes the saveability half silently. Both this tool and _enumsSql in tool/audit_taxonomy_conformance.dart resolve typcategory = 'A' through typelem (#945). Today's two array picker columns (marketing_method, tags) are text[] guarded by CHECKs, so this is insurance, not a live path.