Schema Reconnaissance (Phase 0)

This page is the committed record of the Phase 0 schema reconnaissance required before building the advise layer (see the RHT Advise Layer implementation brief, §1). It was run informally in chat on 2026-07-30 and re-verified here against the live rht_user/rht_db Postgres instance (via docker compose -f docker-compose.dev.yml up -d db, PostgreSQL 16.13) and against backend/api/models.py / backend/api/views.py at the current working tree.

Bottom line up front: the advise-layer brief’s proposed advise.* view contract (§2) cannot be built directly on top of the current schema. There is no run registry, no results table, no intervention magnitude, no scenario dimensions beyond rainfall intensity, and zero completed simulation runs. See §8 for the itemised gap list — this is what Phase 0.5 exists to close.

1. Schemas, tables, views¶

One non-system schema:

The 15 tables are 5 application tables (api_*, all Django-managed) plus 10 Django/DRF framework tables (auth_*, django_*) not relevant to the advise layer.

2. Geometry storage¶

SELECT * FROM geometry_columns;
-- ERROR:  relation "geometry_columns" does not exist

Confirmed: this is plain django.db.backends.postgresql, not django.contrib.gis / PostGIS. django.contrib.gis is absent from INSTALLED_APPS (backend/core/settings.py). Every geometry-bearing column (api_interventionarea.geometry, api_aggregationarea.geometry) is a JSONField stored as jsonb, holding GeoJSON (Polygon/MultiPolygon) directly — no SRID column, no native geometry type, no spatial index. Any advise view that needs a centroid must compute it in Python/application code (e.g. via shapely) rather than ST_Centroid, or the migration must add PostGIS first.

3. Application tables¶

api_scenario — orphaned return-period model¶

Row count: 0. No FK to or from any other table. Despite being data-empty and architecturally disconnected, it is not dead code: it has a live admin registration, serializer, ModelViewSet, and is routed at /api/scenarios/. Nothing in the current data model or views writes to it. This is the only place return_period exists anywhere in the schema, and it isn’t wired to api_interventionscenario.

api_interventionscenario — conflates scenario definition + single run status¶

Row count: 3. All 3 sample rows below, in full (redacted: none needed, no PII columns beyond an integer FK id).

climate_scenario (rainfall intensity, 5 fixed choices) is the only structured forcing dimension. There is no surge height, sea-level rise, storm duration, or return-period field here — return_period lives only on the disconnected api_scenario model above. simulation_status="completed" is a reachable code path (set by the raster-upload import flow in views.py), so this is a live-data fact, not a schema constraint: nothing prevents completed runs, there simply aren’t any yet.

This table conflates “scenario” and “run”. There is no separate run identity, no run history — re-simulating would mutate this same row’s simulation_status in place. No engine, engine_version, grid_res_m, or is_baseline field exists anywhere.

api_interventionarea — measure instances (structured, but no magnitude)¶

InterventionType enum (5 values, all defined in code): water_square, compartmentalization, green_roofs, permeable_pavement, vegetation_parks.

Row count: 4 (the recon draft said 6 — corrected here against a live COUNT(*)). All 4 rows:

Only 3 of the 5 enum values (green_roofs, water_square, permeable_pavement) appear in actual data; compartmentalization and vegetation_parks are unused so far. No quantity/magnitude/unit field of any kind — a green roof intervention area is just a polygon plus a type tag, with no coverage percentage, capacity, or height. This is a structured table (option (a) in the brief’s Phase 0 question about intervention encoding — not a JSONB blob or free text), but it cannot answer “how much” of anything.

api_aggregationarea — district geography (usable as-is)¶

Row count: 92 (matches the draft). All 92 are city=rotterdam, aggregation_type=neighborhoods, source=pdok_cbs — i.e. every row currently in the table is a real CBS BU-coded (buurt-level) Rotterdam neighbourhood. Sample (first 5 of 92):

Names are Dutch only — no name_en column or parallel English table. aggregation_type supports districts/catchments/wards too, but only neighborhoods rows exist today, so district-level (wijk) or ward-level aggregation would need new rows, not new columns.

api_logentry — application audit log (not in the recon draft, not relevant to advise)¶

Row count: 0. Application-level structured log (level/source/event/message/context jsonb), FK to auth_user. Included here for completeness only — no bearing on the advise schema.

4. Run registry¶

There is no run registry. api_interventionscenario is the closest thing, and it identifies a scenario definition, not a run: there is no run id distinct from the scenario id, no engine/engine_version, no grid_res_m, no is_baseline flag, and no way to represent more than one run of the same scenario (re-running overwrites simulation_status in place). 3 rows exist, 0 are completed.

Separately, the raster/FIAT filesystem tree at HOST_RASTER_ROOT (data/rat_data-500/rotterdam/) contains 7 named Rotterdam scenario folders with actual model output (1_base_scenario through 7_IRP_100_years_storagebasins, several with flood_depth/, velocity/, and FIAT/ subfolders) — none of which correspond by name or id to the 3 api_interventionscenario rows. The filesystem and the database are two unsynchronized sources of truth for “what runs exist.” Reconciling them (or picking one as canonical) is required before advise.run can be populated, and is exactly the gap RHT-80 and RHT-81 exist to close.

5. Intervention encoding¶

Structured table, not a JSONB blob or free text: api_interventionarea (type enum + polygon), FK’d to api_interventionscenario. See §3 above — the gap is magnitude, not structure.

6. Measure vocabulary¶

No dedicated vocabulary table. The candidate vocabulary is the InterventionArea.InterventionType enum, defined once in code and mirrored 1:1 in ontology/rhdt.ttl (per prior review): water_square, compartmentalization, green_roofs, permeable_pavement, vegetation_parks — 5 types, not the 8 (GRN_ROOF, WADI, WATER_SQ, PERM_PAVE, PUMP_CAP, QUAY_RAISE, SEWER_UP, RETENTION) assumed in eval/golden.yaml’s header. See RHT-78 for the reconciliation decision.

7. District naming¶

CBS/PDOK-sourced, Dutch-only names on api_aggregationarea, neighborhoods level only today (92 rows, all Rotterdam). See §3. No alias/synonym table exists for districts. There are three separate, independently-defined CITY_ALIASES dicts in the codebase (backend/api/_kg.py, and one each hardcoded on RasterCatalogView and FiatCatalogView in views.py) — all three hold the same 3 entries (rotterdam, chennai, tambaram→chennai) but are not shared/imported from a single source. None of them cover district-level names — they’re city-level only. (Correction to the informal 2026-07-30 recon note, which described “a 2-entry city-name dict”: there are 3 dicts, 3 entries each.)

8. Results tables¶

None exist in Postgres. Per-district and per-building outcome metrics (flood depth, damage, buildings affected) are not in the database in any form. They live as:

• Raster GeoTIFFs (flood depth, velocity) under HOST_RASTER_ROOT/<city>/<scenario>/<collection>/*.tif, discovered by RasterCatalogView via a filesystem walk — not DB-tracked. See Raster & Scenario Data Layout for the full directory contract.

• FIAT damage geopackages (*.gpkg) under .../<scenario>/FIAT/, discovered the same way by FiatCatalogView, which reads gpkg_contents via raw sqlite3 for the catalog listing and shells out to ogr2ogr to serve geometry as GeoJSON on demand. No FIAT_DAMAGE_KEYS-style constant exists in the codebase (this was in the informal draft and does not check out against the code — dropped here). The actual per-building damage field names inside the FIAT geopackages have not yet been inventoried; that inventory is in scope for RHT-80, not this recon.

No aggregation from raster/FIAT outputs to a per-run, per-district row (max_depth_m, buildings_affected, est_damage_eur, …) happens anywhere today. advise.result_district has nothing to source from until that pipeline exists.

9. Extensions¶

SELECT extname, extversion FROM pg_extension;
--  extname | extversion
-- ---------+------------
--  plpgsql | 1.0

pg_trgm is not installed (confirmed against both the live instance and full migration history — zero references anywhere). Needed by the Phase 2 resolver’s trigram matching; see RHT-74.

Where the proposed view contract does not match reality¶

Per-view assessment of the brief’s §2 advise.* contract against the above:

• advise.district — buildable today from api_aggregationarea (neighborhoods rows), but name_en and in_model_extent have no source column. name_en needs a translation table or a per-row default of NULL/same-as-Dutch; in_model_extent needs a decision rule (e.g. “has at least one completed run touching it”) since nothing marks extent explicitly today.

• advise.scenario — not buildable as specified. Only return_period_yr equivalent exists (on the disconnected, empty api_scenario table) and only rainfall (climate_scenario) exists on api_interventionscenario. surge_m, rain_mm_24h (only mm/hour exists, not a 24h total), slr_cm, duration_h have no source anywhere. Blocked on RHT-76.

• advise.measure_vocab — no table exists; must be created from the InterventionType enum (5 values, not the brief’s assumed 8). Blocked on RHT-78.

• advise.intervention — magnitude/unit have no source column on api_interventionarea. Blocked on RHT-77.

• advise.run — not buildable at all. No run identity separate from scenario, no engine/engine_version/grid_res_m/is_baseline, and the on-disk scenario folders (7) don’t correspond to the DB rows (3). This is the critical-path gap. Blocked on RHT-75 (registry fields) and RHT-81 (real completed runs with the required dimension-difference structure).

• advise.result_district — not buildable at all, no results table exists in Postgres in any form. This is the single largest gap; see §8. Blocked on RHT-80.

• advise.run_diff — depends entirely on advise.run existing with real, multiple, dimensionally-varied runs. Cannot be defined, let alone materialized, until then. Blocked on RHT-82, which itself depends on RHT-75/80/81.

• advise.alias — no alias/synonym table exists for districts or measures; only city-level alias dicts (§7) which are the wrong granularity. Blocked on RHT-79.

Every golden question in eval/golden.yaml that references run ids 101/114/116/118/120/122 is unanswerable as written — none of those runs exist, and no two existing runs (there are 0 completed) differ in a known, single dimension. The golden set’s own header anticipates this (“if no such pairs exist ... generating those runs becomes the critical path”) — it is the critical path. This is tracked as Phase 0.5 in Linear rather than folded into Phase 1, because it is schema and data work the original brief didn’t anticipate, not advise-schema work proper.