Logical design for the national evaluation database. Nine schemas, thirty-nine tables, four hundred and sixty-two columns. Target platform PostgreSQL 16 / PostGIS 3.4. No physical implementation exists.
Logical design only. No physical implementation exists. Column types indicate intent; DDL has not been written. Cardinality estimates assume national coverage at L0, the 1 ha cell.
Target platform: PostgreSQL 16 with PostGIS 3.4 and the PostGIS extension. Storage estimates assume default page layout and no compression.
All 39 tables are documented in the Tables tab. ref 12 · geo 4 · obs 1 · thr 4 · crop 2 · src 3 · res 3 · conf 4 · wx 5 — one sub-tab per schema, 462 columns in total. The Reference and Confidence sub-tabs carry seeded values inside the card where the values are settled.
ref.domain_value. src.data_source.origin, obs.cell.aquifer_type and res.area_ledger.tier are typical. As separate tables they would add 22 tables and 22 foreign keys, and adding a permitted value would need a migration; as rows they carry label_ar, sort_order and is_active, and adding one is an INSERT.The number on each tab counts what that tab contains. Tables counts tables; Lookup counts vocabularies; Design counts 8 design decisions, Class ranges 52 elements, Lineage 9 pipeline hops.
bigint cell identifier; no per-cell geometry is stored. Boundary polygons are generated on demand via grid.cell_boundary(). Hexagonal tessellation gives uniform 6-neighbour adjacency at constant edge distance, which removes the diagonal-vs-orthogonal ambiguity present in square-grid contiguity tests. Approximately 215 M cells over the national land area (2.15 M km² ÷ 10,000 m²). A standard indexing system was considered and rejected: no H3 resolution equals one hectare — resolution 10 is 1.505 ha and resolution 11 is 0.215 ha, which would take the grid to about 1 000 M cells. A custom hexagonal grid delivers exactly the one-hectare cell named in the Terms of Reference.value_num/value_text polymorphism an EAV design requires.obs.* rather than reprocessing source rasters. Given that no threshold version has been approved, revision is expected.version_id. Supersession sets effective_to; rows are not deleted. Result tables carry the version used, so outputs from different versions are distinguishable and not silently comparable.MAX(rank) over member elements. classify.element_breakdown().is_governing and classify.crop_breakdown().governing_element identify which element produced the assignment, making any class traceable to a single observation without recomputation.src.data_source.origin takes external or ministry. Rows with origin = 'ministry' have no acquirable substitute and constitute the project's external dependency set.MIN() where elements tie. Aggregating across all elements would report a value inconsistent with the assignment rule.obs.*, res.* and conf.* are LIST-partitioned on province_id (13 partitions). Provincial delivery is the primary access pattern, giving partition pruning on most queries. classify.element_breakdown() is sub-partitioned by element_code given its estimated 9.4 B rows.ref thr crop geo obs res conf src. Cross-schema FKs are permitted; circular references are not.<entity>_id. Natural keys use the domain code, e.g. ref.element.element_code.rooting_depth_cm, ece_ds_m, etc_m3_ha. Removes unit ambiguity at query time.real for measured continuous values (7 significant digits is sufficient given source precision). numeric(3,2) for confidence scores where exact decimal comparison is required. smallint for bounded integers and indices.timestamptz throughout. obs.*.obs_date is the reference date of the source data, not the load date; load metadata is held on src.source_set.res.hectare_result.cap_governing, geo.zone.viable_systems. Avoids junction tables for sets of fewer than ten short values.completeness. Reference and key columns are NOT NULL.thr.class_range and remain registered for expert calibration. The bounds are physical and instrumental limits proposed for confirmation, and 106 columns carry one. Columns with neither badge hold keys, geometry, dates or free text.obs.cell is keyed on (cell_id, water_source_id), so a cell within reach of a well and a treated-wastewater outfall carries two rows. The cell takes the best class across its sources, and res.hectare_result.wat_source_id records which one produced it. Without that column the water class cannot be interpreted — a hectare can be W3 on groundwater and W1 on a desalination connection, and those are different answers to different questions.thr.class_range carries an optional co_element_code with its own bounds, NULL on every one-dimensional range. WP-C-02 sodium adsorption ratio is the only element that uses it. FAO-29 assesses infiltration hazard from SAR and ECw jointly, because at a given SAR the infiltration rate improves as salinity rises — an SAR of 6 is a serious problem at ECw 0.3 dS/m and none at ECw 1.2 dS/m. A single-column range table cannot express a threshold that moves with a second measurement.Two kinds of thing live in this database, and the whole design turns on keeping them apart.
rooting_depth_cm = 79.4. A fact about the hectare, loaded by the ETL. No threshold, no algorithm, no judgement. 49 of them.C2. Depends entirely on a threshold someone chose, in an admin area, under review. Not stored, except where the map cannot afford to compute it.A class is not data — it is a function: class = f(value, threshold_version). Given 79.4 and version 1, the answer is C2 deterministically, every time. Storing it stores the answer to a sum whose operands are both already on the row.
thr.class_range are illustrative values carried from the Al Lith pilot, not calibrated national ones, and thr.version holds one version at status under_review. An admin area is being built where those parameters get set: an operator changes a class range, sees a sample, approves it.f(measurement, thr.class_range). The panel reads one cell — 33 values against 85 threshold rows, sub-millisecond.ref.class.thr.crop_requirement. Storing it was 4.3 billion rows holding an answer that goes stale.A class filter becomes a value-range filter, resolved before the scan:
-- “show cells where salinity is C4” SELECT value_lo, value_hi FROM thr.class_range WHERE element_code = 'LQ-SC-01' AND class_id = 4 AND version_id = $1; --> 8, 16 SELECT cell_id FROM obs.cell WHERE ece_ds_m >= 8 AND ece_ds_m < 16;
Step 1 hits an 85-row table that stays in memory permanently. Step 2 is an indexed range scan on an ordinary column — the same index type and the same speed as filtering a stored class, and it follows a threshold change immediately instead of after a reprocessing run. Index the measurement columns people filter on: ece_ds_m, rooting_depth_cm, slope_pct, sand_encr_m_y.
version_id, because a measurement does not depend on a threshold. Ask for v1 and join thr.class_range at v1; ask for v2 and join at v2. Both answerable from the same row, so a published figure stays reproducible — which for a ministry deliverable is a requirement, not a convenience.LQ-SP-03 and SQ-A-03 are both rooting depth, one compared against thr.class_range for capability and the other against thr.crop_requirement for a named crop. Storing them separately would put the same number in two places, and two copies of one measurement diverge the first time one ETL job re-runs and the other does not.obs.cell therefore holds one row per hectare with 33 measurements: 17 capability, 6 land rights, and the 10 suitability factors that are genuinely their own. The 16 mirrors carry no column of their own — ref.element.storage_column points them at the capability column, and the classification routine reads one value twice.obs.cell stays separate, and this is not an inconsistency. It is keyed on (cell_id, water_source_id), because a hectare can be served by a well and a desalination connection at once, each with its own salinity, lift and licence. Folding it into a per-cell row would force one source per hectare and silently discard the alternative — which is exactly the comparison an investment case turns on.The system never parses the composite code. C4s is stored for display, but the parts are already separate columns: cap_class_id, cap_subclass and cap_governing. The interface joins three reference tables and composes a sentence.
SELECT c.code,
COALESCE(n.override_en,
c.plain_en || CASE WHEN r.cap_subclass IS NULL THEN ''
ELSE ' The limitation is ' || s.phrase_en || '.' END
) AS headline,
c.implication_en AS what_it_means,
e.name_en, e.plain_description AS governing_element
FROM res.hectare_result r
JOIN ref.class c ON c.class_id = r.cap_class_id
LEFT JOIN ref.subclass s ON s.letter = r.cap_subclass
LEFT JOIN ref.element e ON e.element_code = r.cap_governing
LEFT JOIN ref.class_note n
ON n.class_id = r.cap_class_id
AND (n.subclass IS NULL OR n.subclass = r.cap_subclass)
AND n.is_active
WHERE r.cell_id = $1 AND r.version_id = $2;Composing rather than storing every combination means adding a subclass letter costs one row, not five, the Arabic is written once per class and once per letter rather than once per pair, and C4s and C4z are guaranteed to differ in exactly one clause.
E0 and W0 would compose with no limitation clause, which reads as cleared — the exact misreading locked rule 8 exists to prevent. C1 and S1 carry no subclass letter, so the clause is omitted rather than left empty. N1 and N2 both render as N, because nothing yet distinguishes currently from permanently unsuitable. ref.class_note.reason records why each exists — without it a later editor deletes the row as redundant and reintroduces the defect.The methodological rules governing this study are not left to application code. Where a rule can be expressed as a database constraint it is expressed as one, so that a value violating it cannot be stored at all. The list below is the set of constraints that carry methodological weight; ordinary referential integrity is not itemised.
ref.class, and carries a CHECK that the referenced row belongs to the scheme declared on its module. A capability column cannot hold S2; a suitability column cannot hold C2. The S1–N2 series is reachable only from suitability.res.hectare_result carries three separate class columns and no combined score column. There is nowhere to write an average, which is a stronger guarantee than a rule not to compute one.thr.crop_requirement.weight is retained for source fidelity and is referenced by no generated column, no view and no classification routine. It is provenance, not an input.obs.cell (cell_id, version_id) WHERE is_selected. A cell may carry several source rows but exactly one may be marked as the source of the published class.classify.element_breakdown() (cell_id, module_id, version_id) WHERE is_governing. Exactly one element may be recorded as governing per cell per module; ties are resolved to the lower confidence before the flag is set.thr.class_range over (element_code WITH =, version_id WITH =, numrange(low, high) WITH &&). Two classes cannot claim the same value. Supersession is CHECK (effective_to IS NULL OR effective_to > effective_from); rows are never deleted.version_id is NOT NULL and part of the primary key on every result and confidence table. A result produced under one threshold version cannot be silently compared with another, because the two cannot occupy the same key.obs.land_rights columns DEFAULT to E0 and are never inferred upward. An unrecorded legal state is recorded as unknown and counted against completeness; it is never promoted to E1. Unknown is not the same finding as clear.ref.class.is_terminal is TRUE for CN, W4, E3 and N2. Downstream development categories read the flag rather than string-matching class codes.geo.zone has no crop column and no FK to crop.crop. Crop verdicts reach zones only through res.zone_crop, which is an attribute table. A crop cannot participate in forming a zone boundary because the boundary table cannot reference it.ref.element.storage_table and storage_column are validated against information_schema.columns at deployment. A catalogue entry pointing at a column that does not exist fails the build rather than producing silent nulls at classification time.numeric(3,2) CHECK BETWEEN 0 AND 1. Band assignment is a generated column from the score, not an independently written value, so score and band cannot disagree.cell_id given cell identifier locality.classify.element_breakdown().A controlled vocabulary: a short closed list of permitted values attached to one column. Every entry here is held as rows in ref.domain_value, with a CHECK constraint generated from it. Nothing in this tab is a table.
Reference entities that are tables — the 64 elements, the 19 classes, the 25 units, the 16 authorities, the 13 provinces — are documented in the Tables tab under Reference, where each card carries its column definitions and its seeded values together.
These 22 are rows rather than tables because each is two to seven values feeding a single column. As tables they would add 22 tables and 22 foreign keys, and adding a permitted value would need a migration. As rows they carry label_ar, sort_order and is_active — none of which a bare CHECK constraint can hold — and adding one is an INSERT.
name_ar below is a first draft written for correction. The Arabic Execution Plan and the bilingual Data Requirements Matrix already fix terminology for this engagement, and those terms take precedence wherever they differ.ref.element is the join point of the whole database, carrying storage_table and storage_column so the classification routine resolves columns from the catalogue rather than from hard-coded mapping. Each card expands to its column definitions and, where the values are settled, the seeded rows themselves.LQ-SC-03 only — it is a land property rather than a crop diagnostic, so the suitability module carries no equivalent factor. z is reserved for salinity and sodicity; carbonate, gypsum and pH are nutrient and toxicity limitations and carry n. Description column reads: storage_column · unit · subclass letter.dS/m from appearing beside dS m-1 on two elements measuring the same thing.src.data_source.acquisition_status. Repeated where one vocabulary serves more than one column — which is how a collision becomes visible instead of latent.method_lead · data_lead · committee.satellite_modelled.conf.component.obs.cell holds one row per hectare: 49 measurements including the selected water source, the four module answers, and six columns materialised for map filtering. Alternative water sources sit in a jsonb column. LIST-partitioned on province_id, then HASH on cell_id.jsonb holding alternative sources. Loaded by the ETL, independent of any threshold.MAX(rank) over 33 elements cannot be evaluated per tile.cell_id, province_id, version_id, obs_date, source_set_id, computed_at.class, rank, confidence and fragile for each of the 59 scorable elements — 295 values that would be columns 71 through 365. They are a deterministic function of the 50 inputs and thr.class_range, so they are computed when a cell is opened: 33 values against 85 threshold rows, sub-millisecond.cap_governing answers “show everything limited by salinity”; no arrangement of the 50 inputs answers that without evaluating the whole comparison first.LQ-SP-01 and SQ-A-01 read this column. Available water capacity.LQ-SP-02 and SQ-A-02 read this column. Soil workability.LQ-SP-03 and SQ-A-03 read this column. Rooting conditions.LQ-SP-04 and SQ-A-04 read this column. Surface sealing and crusting.LQ-SC-01 and SQ-B-01 read this column. Salinity (ECe).LQ-SC-02 and SQ-B-02 read this column. Sodicity (ESP).LQ-SC-03. Nutrient availability.LQ-SC-04 and SQ-B-04 read this column. Toxicity (boron).LQ-W-01 and SQ-C-01 read this column. Drainage condition.LQ-W-02 and SQ-C-02 read this column. Flood hazard.LQ-W-04 and SQ-C-03 read this column. Waterlogging risk.LQ-T-01 and SQ-D-01 read this column. Terrain (slope).LQ-T-02 and SQ-D-02 read this column. Water erosion.LQ-T-03 and SQ-D-03 read this column. Wind erosion.LQ-T-04 and SQ-D-04 read this column. Sand encroachment.LQ-C-02 and SQ-E-02 read this column. Thermal regime.LQ-C-03 and SQ-E-03 read this column. Radiation.EP. Protected area.EV. Rangeland, forest and afforestation.ET. Tenure.EZ. Zoning designation.EX. Conflicting use.EH. Hazard designation.SQ-E-01. Moisture deficit (aridity).SQ-E-04. Length of growing period.SQ-A-05. Soil texture.SQ-A-06. Coarse fragments.SQ-B-05. Calcium carbonate.SQ-B-06. Gypsum content.SQ-B-07. Soil pH.SQ-E-05. Frost risk.SQ-F-01. Irrigation demand (ETc).SQ-F-02. Crop nutrient requirement.WP-A-01.WP-A-02. Licensed abstraction ÷ service area. Not physical availability.WP-A-03.WP-B-01.WP-B-02.WP-B-03.WP-C-01.WP-C-02. Classed jointly with ecw_ds_m — see thr.class_range.WP-C-03.WP-C-04.WP-C-05. NULL unless the source is treated wastewater.WP-D-01.WP-D-02. Network distance, not Euclidean.WP-E-01. Basin-level — every cell in a basin shares it.WP-E-02. Above 100 means over-allocated.WP-C-02 only: FAO-29 assesses infiltration hazard from SAR and ECw jointly, because at a given SAR the infiltration rate improves as salinity rises.co_element_code is NULL.conf.result_confidence.band — a single vocabulary, not a parallel one. Denormalised here for display.conf.element_confidence. Kept textually distinct so a hand-entered expectation can never be mistaken for a computed score.field_measured, satellite_modelled, source_validated. The vocabulary is defined once in ref.domain_value; this table supplies only its multiplier.The Tables tab states that rooting_depth_cm accepts 0–300. It does not state that 42 cm is C4. That mapping is this table — thr.class_range — and without it a developer can store a valid measurement and still not know how to evaluate the land.
All four modules appear below, and they do not have the same shape. Capability has one numeric range per class per element. Water quality has three published tiers rather than five classes. Land rights has no numeric ranges at all — the class comes from a register state. Suitability has ranges that vary by crop, so they cannot be shown in a single row.
Each row is one element. Each cell is the interval of the measurement that yields that class. Intervals cannot overlap: an EXCLUDE constraint on numrange(value_lo, value_hi) per element per version prevents two classes claiming one value. Direction matters — the arrow beside each unit shows it. ↑ means higher is better, so C1 sits at the top of the range; ↓ means lower is better, so C1 sits at the bottom.
Table and column are given for every element so a developer can go straight from a class range to the attribute it tests. They are not hard-coded in the classification routine — they are read from ref.element.storage_table and ref.element.storage_column at run time, and the values shown here are those catalogue entries.
v1.0-alues stands at under_review, maturity source_validated, which is what caps confidence component 4 at 0.80 on every output in the study. A developer should read the structure as final and the numbers as provisional.LQ-C-03 Radiation has no class ranges recorded in any delivered module, and LQ-SC-03 Nutrient availability has only C1 and C2. They are shown empty rather than filled by interpolation, because a range invented to complete a table is indistinguishable from a calibrated one once it is in the database.One query per element, resolved from the catalogue rather than hard-coded:
-- 1. resolve where the value lives, from the catalogue SELECT storage_table, storage_column FROM ref.element WHERE element_code = 'LQ-SP-03'; -- obs.capability | rooting_depth_cm -- 2. read the value and find the range containing it SELECT r.class_id FROM thr.class_range r WHERE r.element_code = 'LQ-SP-03' AND r.version_id = 1 AND 42.0 >= r.value_lo AND 42.0 < r.value_hi; -- → C4
Then MAX(rank) across the 19 results gives the cell class, and the element holding that rank is flagged is_governing. No averaging, no weighting: the worst-rated element decides, and it is named.
WP-C-02 Sodium adsorption ratio cannot be divided into ranges on its own at all. FAO-29 evaluates SAR jointly with ECw for infiltration hazard — at a given SAR the infiltration rate rises as salinity rises, so an SAR of 6 is a problem at ECw 0.3 and not at ECw 1.2. A single-column range table cannot represent a two-variable surface. WP-C-02 needs either a two-dimensional lookup keyed on both values, or an explicit decision to assess infiltration as a derived element. This is a schema question, not a calibration question, and it should be resolved before the water module is built.CN would place most of the Kingdom in the terminal class on this element alone, which is very unlikely to be the intended reading and is exactly why calibration is required. Recorded at extracted maturity (0.65), below every other range in the study.E0–E3 is assigned directly from the state of a register, which is why all six columns are categorical. The table and column are given so the mapping can be wired once the registers arrive.E1 rather than E2 depends on the designation categories each authority actually uses — which protected-area classes permit managed agriculture, which zoning classes bar it outright. Those category lists arrive with the registers themselves, as requests MD-04 to MD-07. Until then every hectare stands at E0, which denotes an unknown legal state and is never promoted to clear.thr.crop_requirement, keyed on crop_id as well as element_code — 1,268 records across 56 crops from ALUES 0.2.1. The 26 factors and the columns they read are below.SQ-C-02 flood hazard uses the LQ-W-02 ranges above, and only SQ-A-03, SQ-B-01, SQ-B-04 and the eight others marked per crop vary by crop.Resolving a suitability class takes one more key than a capability class.
SELECT r.class_id FROM thr.crop_requirement r WHERE r.element_code = 'SQ-A-03' AND r.crop_id = 17 -- the crop being tested AND r.system_id = 1 -- and the production system AND r.version_id = 1 AND 42.0 >= r.value_lo AND 42.0 < r.value_hi;
A cell therefore carries one capability class and up to 63 suitability classes, one per crop in the library. This is why classify.crop_breakdown() is keyed on crop_id and system_id while res.hectare_result is not.
thr.crop_requirement, not here.Capability asks whether the land can carry the activity. Suitability asks what it will produce.
That means capability is evaluated per industry. The same hectare can be capable for aquaculture and not for an orchard, because the two activities ask different things of the ground. An element that has no meaning for an activity is marked N and takes no part in the class.
Suitability then splits by crop and production system and evaluates every factor, because at that point a crop has been named and every factor has a referent.
MAX(rank).thr.crop_requirement, not in whether a factor participates.S code — which the interface must render as not applicable, never as N2.res.hectare_result.cap_class_id cannot hold it alone. A res.capability_system table is needed — one row per cell per industry — and res.hectare_result retains a single class for a nominated reference industry, stated explicitly.irrigated_open — and say so on the module. The alternative is a zone set per industry, which multiplies the area ledger by eight.How the routine reads it.
-- capability, for one industry
SELECT ec.element_code, ec.class_id, ec.rank
FROM classify.element_breakdown($1,$2) ec
JOIN thr.element_applicability a
ON a.element_code = ec.element_code AND a.system_id = $2
WHERE ec.cell_id = $1 AND a.mark <> 'N' -- N takes no part
ORDER BY ec.rank DESC; -- first row governs
-- suitability, for one crop under one system: no applicability filter
SELECT class_id FROM thr.crop_requirement
WHERE crop_id = $1 AND system_id = $2 AND version_id = $3;Capability filters on the matrix. Suitability does not, because every factor applies once a crop is named.
A cell carries 70 stored columns and 295 values computed when it is opened. This page lists all of them: what each one is, whether it is stored or derived, and if derived, from what.
The distinction is the whole design. An input is a fact about the hectare, loaded by the ETL, independent of any threshold. A computed value is a judgement about that fact, and it changes the moment someone changes a threshold in the admin area. Storing judgements makes changing them expensive; storing facts does not.
jsonb for alternatives.version_id records which threshold version produced them.C1–CN.Maximum limitation: the worst class among the 17 capability elements, each rated against thr.class_range.s for soil physical.The subclass letter of the governing element, from ref.element.subclass_letter.W0–W4.Maximum limitation over the 15 water parameters of the selected source. W0 means no record held — it is not a rating.E0–E3.Any intersection, not majority by area. The most restrictive designation wins. E0 where no register is held — never promoted.thr.class_range. The detail panel reads one cell: 33 values against 85 threshold rows, sub-millisecond. Storing them would cost ~500 GB, turn a threshold change into a rewrite of 215 M rows, and force one threshold version per row.SELECT class_id FROM thr.class_range WHERE element_code=$1 AND version_id=$2 AND value >= value_lo AND value < value_hi. An 85-row table, permanently in memory.ref.class.rank. Feeds the MAX(rank) that produces the module answer.rank equals the module maximum. Ties resolve to the lowest confidence.CBRT( SQRT(method × resolution) × boundary × threshold ). Method and resolution are constant per source; boundary is one subtraction.LQ-SP-02 workability, LQ-SP-04 sealing, LQ-SC-03 nutrient availability, and the two suitability mirrors. The inputs exist — texture, bulk density, organic carbon are all published layers — but the function that combines them into an index has never been written down. Until it is, there is no target quantity to compute, and confidence cannot be scored either: component 1 asks how a value was acquired, and there is no acquisition path to name. They are unscorable, not scored low — which is why the ceiling table reports 59 of 64 rather than scoring all 64 with five poor figures at the bottom.-- suitability of one crop on one hectare
SELECT cr.element_code, cr.class_id
FROM thr.crop_requirement cr
JOIN obs.cell c ON c.cell_id = $1
WHERE cr.crop_id = $2 AND cr.system_id = $3 AND cr.version_id = $4
AND the cell’s value for cr.element_code BETWEEN cr.value_lo AND cr.value_hi
ORDER BY cr.class_id DESC LIMIT 1; -- maximum limitation againThe element values come from the same 33 columns. A crop class and a capability class are the same measurement rated against two different threshold sets — which is why the 16 mirrored elements share one column.
One element followed from source raster to published hectare, naming the table and column at every hop. Its purpose is to show that the 39 tables form a single path, and that any published figure can be walked backwards to the measurement that produced it.
The values below are illustrative. No database exists and no cell has been processed. The arithmetic is real — every score shown is the stated formula evaluated on the stated inputs — but the observed value of 42.0 cm is a chosen example, not a reading.
LQ-SP-03 — Rooting conditions. Capability module, Soil Physical group, subclass letter s.v1.0-alues · status under_review · maturity source_validated (0.80).origin = 'external', licence CC-BY 4.0, native resolution 250 m, access route API. Because the origin is external the project can acquire it without ministry involvement — unlike every column in obs.land_rights.
SOILGRIDS_250 to LQ-SP-03 at priority = 1, with derivation = 'model' and the chain recorded verbatim. This row is why the element later scores 0.70 on acquisition method: a model prediction, not a direct sensor reading.
storage_table = 'obs.capability' and storage_column = 'rooting_depth_cm' from the catalogue and resolves the column dynamically. Adding or retiring an element is a catalogue edit, not a code change.
obs_date set to the reference date of the source and not the load date. The measured value is kept, not just the class it produced — which is what makes threshold revision a query rather than a raster reprocess.
LQ-SP-03 under version v1.0-alues, the range containing 42.0 is C4 = 25–50 cm. Ranges cannot overlap: an EXCLUDE constraint on the range prevents two classes claiming the same value. The range is source_validated, not approved — traced to its publication but not assessed for Saudi conditions.
satellite_modelled = 0.70. Resolution: a 250 m pixel against a 107.46 m cell gives 1/(1 + 0.5·log₂(250/107.46)) = 0.68. Boundary: 42.0 is 8.0 cm from the 50 cm edge, source RMSE 6 cm, so Φ(8/6) = Φ(1.33) = 0.91. Threshold maturity = 0.80.
source_conf = √(0.70 × 0.68) = 0.690confidence = ∛(0.690 × 0.91 × 0.80) = 0.79 — band medium.MAX(rank) runs across all 17 capability elements on the cell. If no element ranks worse than 4, rooting depth governs, is_governing is set on its classify.element_breakdown() row, and the cell is C4s — the subclass letter taken from the governing element, not from the set of all limitations. The published confidence is 0.79, the governing element’s own figure, never an average across the 19. Water and land rights are answered separately on the same row and are not combined with it.
geo.zone has no FK to crop.crop; crop verdicts attach afterwards through res.zone_crop. Zones then aggregate to the area ledger at governorate, province and national tier. The hectare in this trace lands in one ledger category, and can be walked back through all nine hops to a 42.0 cm reading from a 250 m model prediction.
Every figure is reversible. A hectare in the ledger resolves to a zone, a zone to its cells, a cell to its governing element, that element to a class, the class to a class range and a stored measurement, and the measurement to a named source with a recorded derivation chain. No step is inferred at query time.
Revision does not require reprocessing. Because step 4 persists the measurement, a new threshold version re-runs steps 5 to 9 as a query over obs.*. Step 1 to 4 are untouched. Both versions coexist, distinguishable by version_id.
The weakest link is visible, not averaged away. The published confidence at step 8 is the figure computed at step 7 for one element. Where a cell is governed by an inferred element with no measurement anywhere in the country, the published number says so.