BR IFIC data design

The core downloads MDB extracts, builds a queryable DuckDB and exposes HF records with declared endpoints. Maintained queries are SQL files. The only application retained is the separately maintained Japan overview.

Inputs and rebuilding

/data/brific/derived/brific.duckdb is disposable. Rebuilding replaces its entire contents. Original ITU archives, the acquisition manifest, maintained SQL and research inputs live outside that database.

The downloader accepts explicit issue numbers and gets publication dates from pub_current_circ. It validates the archive and issue metadata, reuses cached files and records archive/MDB hashes. There is no separately maintained issue/date list.

The builder checks each MDB's issue metadata and selects fxm_* plus circ, pub, pub_current_circ, ref_adm, ref_ctry, ref_stn_cls, ref_nat_srv and ref_notice_typ. It loads every row in those tables. Other tables remain in the source MDBs.

Input versions are selected with one condition:

version = "v2" if issue >= 3042 else "v1"

For each table/version, the builder exports the matching MDB tables into temporary <issue>.csv files, checks their headers agree and loads them together. mdb-export supplies the column names; null/date formatting is explicit. Source fields remain VARCHAR and source_issue is added as INTEGER. The builder creates each table once. It does not infer missing columns or alter a growing schema.

Temporary CSVs and the database build live under work/. CSVs are discarded after their table loads. Once the source tables load, the connection closes and the completed file replaces the database. The DuckDB CLI then loads Spatial and applies the four view files in a separate transaction. An unsuccessful build is disposable and needs no recovery procedure. There are no incremental imports, database backups or migrations.

Source views and geometry

v1.* and v2.* preserve the original layouts. The 36 common non-geography tables are exposed under their source names using UNION ALL BY NAME; the 12 v2-only tables have direct views. ALL preserves source rows. Added or removed fields retain their source names, with NULL where that version did not publish the field.

Geometry is interpreted once, in locations:

The two SQL branches name common latitude/longitude fields and combine with UNION ALL BY NAME. Every join includes source_issue. Point IDs, circle radii and zones survive; missing coordinates remain NULL. Coordinates are not rounded or matched. Original DMS, altitude and precision fields remain in the versioned source tables.

geom is a GEOMETRY('OGC:CRS84') point constructed with ST_SetCRS(ST_Point(longitude, latitude), 'OGC:CRS84') from the normalized coordinates. It is NULL if either coordinate is absent. For CIRCLE rows it represents the centre, with radius_km retained separately; MULTIPOINT members remain individual points. Coordinates are treated as WGS84, with longitude/latitude axis order (OGC:CRS84).

The original geography tables remain accessible as v1.fxm_geo, v1.fxm_geo_pt, v2.fxm_geo and v2.fxm_geo_pt. locations is the common geography relation; there is no misleading combined raw geography table.

HF relations

hf_records has one row per (source_issue, terrakey) with 3 ≤ assigned frequency < 30 MHz. It exposes source identifiers, administration, station ID/type/class, frequency in MHz, bandwidth in kHz, emission and the primary geography key.

These dates retain their separate meanings. A row appearing in an extract is not necessarily newly registered: extracts include older related records. Repeated appearances remain separate, with their source issue. There is no change detection, cross-issue matching or inferred lifecycle.

hf_endpoints joins three declared relationships to locations:

• Primary geography from fxm_terra: TX/TP records have role TX; RX records have role RX.
• Associated receiver geography from fxm_rx: role RX.
• Associated transmitter geography from fxm_tx: role TX.

It retains source_issue, terrakey, relationship, original station type, role, ant_key, geo_key, geo_pt, site name, country, geometry type, decimal coordinates, point geometry (geom), radius and zone. Primary rows have no invented antenna key. Left joins retain declared geography references even when coordinates are unavailable. MULTIPOINT members remain individual rows.

The endpoint query joins these relations for inspection. Geographic interests and dated leads are maintained in HF findings. There is no automatic screening, report generation or general map in the core.

SQL and Python boundary

Python selects files, invokes mdbtools, manages temporary files and loads raw source tables using sql/init.sql and sql/load_source_table.sql. It supplies quoted identifiers and bound CSV paths to the loading statement. The DuckDB CLI separately applies source_views.sql, locations.sql, hf_records.sql and hf_endpoints.sql in that order. Interactive queries and standalone SQL execution also use the CLI.

Spatial is installed once per DuckDB version with INSTALL spatial; during setup. Connections querying the geography views require LOAD spatial;. The endpoint query and documented CLI commands load it explicitly. Raw source loading does not need Spatial.

The project remains non-installable, with dependencies managed by pyproject.toml and uv.lock. tools/common.py supplies the Python scripts' shared data-directory option. The SvelteKit Japan map runs sql/queries/japan_routes.sql once at startup, keeps the route results in memory and closes the database. Curated TOML supplies its operator identities, field sites and terrestrial links. The authored dossier pages load their photo catalogue separately. See the app README for the page-loading paths.