Data Ingestion / Bulk-Upload / ETL / Staging Architecture

Evidence base: live smartinventory_qa (PostgreSQL 14.8) queried read-only, plus references/db-analysis/raw/{schema_full.sql, function_inventory.tsv, table_inventory.tsv}. All row counts are either exact count(*) over small tables or pg_class.reltuples estimates for large ones (noted inline).

Scope

This domain covers how external data (Excel/CSV/KML, GIS shapefiles, user lists) gets into the SmartInventory master model. It is one of the largest and most repetitive parts of the database:

Object family	Count	Role
fn_uploader_* functions	92	The bespoke per-entity upload engine (validate + insert)
fn_bulk_* functions	28	Secondary “bulk” engines (users, buildings, tickets, resources, associations)
fn_landbase_* functions	32	Basemap/landbase subsystem (its own parallel uploader)
temp_du_* tables	44 live (43 in dump)	Per-entity upload staging tables (“du” = data-upload)
temp_*_csv tables	10	Legacy/orphan direct-load CSV staging (not wired to any function)
temp_pole_accessories	1 (1.1M rows)	Orphan flat staging table (no function references it)
gis_data_* tables	11 (all empty)	Basemap reference layers (nation/state/city/road/rail/waterbody)
bulk_user_* tables	9	User-list bulk-upload staging + mapping
data_uploader_template	884 rows	The column-level template registry that drives all uploads
upload_summary	1,174 rows	The central upload-job ledger

Total ingestion-related stored procedures: ~150 functions (uploader + bulk + landbase). Staging tables currently hold ~1.95M rows of residual data, almost all of it orphaned post-job residue (see below).

Data model & relationships

The active ingestion pipeline is config-driven by layer_details (the 66-row entity registry) and data_uploader_template, and it is logged by upload_summary.

The “du” prefix mystery — solved

temp_du_* = temp data-upload. This is confirmed directly from layer_details:

SELECT layer_name, geom_type, is_data_upload_enabled, data_upload_table, data_upload_max_count
FROM layer_details WHERE coalesce(data_upload_table,'')!='';
-- Pole     | Point   | t | temp_du_pole     | 10000
-- Cable    | Line    | t | temp_du_cable    | 10000
-- Building | Point   | t | temp_du_building | 50000
-- LandBase | Point   | t | temp_du_landbase | 20000   ...

So layer_details.data_upload_table names the staging table per entity, data_upload_max_count is the per-upload row cap (Building 50k, Cable/Pole/Duct 10k, most accessories 100), and is_data_upload_enabled gates whether the UI offers upload. temp_du_building / temp_du_landbase are simply the Building and Landbase staging tables — not a separate concept.

Pipeline shape (file → staging → validate → commit → master)

Excel/KML file
   │  (app parses against data_uploader_template column map)
   ▼
upload_summary  ──INSERT job row (id, user_id, file_name, entity_type, plan_id,
   │              total_record, status='START')   ← fn_uploader_getuploadid reads it
   ▼
temp_du_<entity>  ──app bulk-inserts raw rows, all VARCHAR, tagged with
   │                upload_id, batch_id, row_order, is_valid=true, is_processed=false
   ▼
fn_uploader_validate_parent_details(upload_id, entity_type)   ← orchestrator
   │   ├─ fn_uploader_validate_entity            (entity-specific rules, e.g. structure buffer)
   │   ├─ custom-projection reprojection (ST_TRANSFORM via global_settings)
   │   ├─ default-parent resolution (vw_layer_mapping)
   │   ├─ network-status normalisation (PLANNED→P, AS_BUILD→A, DORMAENT→D)
   │   ├─ duplicate network_id detection
   │   ├─ user-permission-area check (user_permission_area)
   │   └─ geometry dispatch → fn_uploader_validate_{point,line,polygon}_parent_details
   │        (lines additionally → fn_uploader_check_termination_points: snap A/B ends
   │         to existing point entities within LineUploadBufferInMeter)
   │   ⇒ rows that fail get is_valid=false + error_msg set in place
   ▼
fn_uploader_insert_<entity>(upload_id, batch_id)   ← one function PER entity (~45 of them)
   │   FOR each temp_du row WHERE is_valid AND NOT is_processed AND batch_id=…:
   │     ├─ resolve network_code via fn_get_clone_network_code / layer_details.network_id_type
   │     ├─ INSERT INTO att_details_<entity>(...)  RETURNING system_id
   │     ├─ INSERT INTO {point|line|polygon}_master(system_id, entity_type, sp_geometry,
   │     │     approval_flag='A', db_flag=upload_id, …)
   │     ├─ UPDATE temp_du_<entity> SET is_processed=true
   │     └─ PERFORM fn_geojson_update_entity_attribute(...)   (refresh cached GeoJSON)
   ▼
master tables: att_details_<entity> + {point|line|polygon}_master
   (linked back to the job via point_master.db_flag = upload_id, and
    att_details.source_ref_type='DU', source_ref_id=upload_id)

Key join keys / linkage

• upload_summary.id (upload_id) is the spine: it appears as a column in every temp_du_* table and as db_flag / source_ref_id on the committed master rows. This is what fn_uploader_getuploadlogs uses to show “this batch’s results”.
• temp_du_*.batch_id chunks a single upload into commit batches (each fn_uploader_insert_* takes (upload_id, batch_id)).
• temp_du_*.is_valid / error_msg carry per-row validation outcome; is_processed carries commit state.
• data_uploader_template.layer_id → layer_details.layer_id maps Excel headers (template_column_name, e.g. Pole_Name) to DB columns (db_column_name, e.g. pole_name) with is_mandatory, max_length, is_dropdown, min/max_value, is_kml_attribute vs is_excel_attribute.

Landbase = a parallel, self-contained uploader

Landbase (basemap: roads, parcels, boundaries, POIs) has its own registry and engine: landbase_layer_master (23 layers), landbase_dropdown_master (69 values), landbase_layer_columns_settings (350), staging temp_du_landbase (37,423 rows) → fn_landbase_uploader_insert → att_details_landbase (6,502 rows) + sp_landbase. Validation routes through fn_uploader_validate_landbase_details (a special-case branch inside fn_uploader_validate_parent_details). Landbase attributes are stored as generic attribute_1 … attribute_10 columns rather than typed columns — a deliberately schemaless sub-model layered on top of an otherwise rigid one.

gis_data_* = raw basemap reference layers

The 11 gis_data_* tables (gis_data_nation/state/city/roadnetwork/rail/waterbody_s/ r4gstateboundary/jiocenterboundary/feederservingarea/micro_jp_boundary/tb_pm_upd) are all empty (0 rows). Their columns reveal Reliance-specific basemap schemas (riluniqueid, rilfeaturecode, transportedgeid, transportmode, speedclass) — i.e. shapefile/GIS-import landing tables for the RJio basemap, populated once during seeding and since cleared (or seeded only in production).

What data is actually held

upload_summary (job ledger) — measured

• 1,174 upload jobs, dated 2023-06-12 → 2026-02-03 (the system is actively used).

• Status: 1,064 OK, 102 FAILED, 8 INVALID_INPUTS. execution_type is always START.

• Per-entity activity (uploads / total rows / success / failed):

  Entity	Jobs	Total rows	Success	Failed
  Cable	370	10,661	2,009	7,847
  Pole	108	36,855	8,239	17,580
  Manhole	53	20,636	8,444	12,084
  Building	42	43,880	279	563
  Duct	37	26,558	12,278	7,480
  LandBase	27	41,805	27,820	12,998
  Tower	11	15,860	5,281	5,295

  The failure rates are very high (Cable ~74% failed, Pole ~48%). Most rows that arrive are rejected by validation — the uploader is being used iteratively to converge on a clean dataset, with the staging tables accumulating the rejects.

Staging tables — what is resident now (reltuples)

The largest resident tables are not the active temp_du_* pipeline:

Table	Rows	Verdict
temp_pole_accessories	1,095,858	Orphan — flat (component_name, type, sub_category, location, lat/lng, row_number_col); no stored function references it
temp_auto_network_plan	334,363	Intermediate processing scratch for auto-planning (plan_id, entity_type, entity_network_id, lat/lng)
temp_pole_csv	181,560	Orphan legacy CSV staging (is_uploaded, is_duplicate flags); no function refs
temp_building_csv	129,948	Orphan CSV staging
temp_duct_csv / temp_manhole_csv / temp_trench_csv / temp_cable_csv	89k / 81k / 75k / 75k	Orphan CSV staging
temp_du_building	42,899	Active-pipeline residue (see below)
temp_du_landbase	37,423	Active-pipeline residue
temp_du_duct	21,090	residue
temp_landbase_csv	19,545	Orphan CSV staging

Two distinct families confirmed:

1. temp_*_csv and temp_pole_accessories are referenced by zero stored functions (verified by awk over schema_full.sql). They are direct COPY/app-side landing tables from an older or out-of-band import path and are effectively abandoned data.
2. temp_du_* are the live, function-driven pipeline.

Staging tables are never cleaned up

temp_du_building holds 42,899 rows; broken down by created_on:

2023:  42,890 rows  (20,300 processed,  21,447 invalid)
2024:       9 rows

So a staging table that should be transient still carries ~43k rows from 2023, mixing committed (is_processed=true), rejected (is_valid=false), and uncommitted rows. There is a fn_uploader_delete_record(entity, upload_id) that does DELETE … WHERE upload_id=…, but it is evidently not called on the normal success path — residue accumulates.

The template registry — measured

data_uploader_template = 884 rows. Flags across all rows: 80 mandatory, 67 dropdown, 559 KML-attribute, 859 Excel-attribute, 80 CDB-attribute. Cable alone has 118 template columns (Duct 34, Building 29, POD 29). Sample Pole map: Latitude/Longitude are the only mandatory columns (float8, max_length 20); everything else (Pole_Name, Pole_Height, Specification, Parent_Network_Id…) is optional VARCHAR.

Business logic — key stored functions

• fn_uploader_validate_parent_details (234 lines; a 799-line _test and a 623-line _bkp_14012021 variant also exist) — the validation orchestrator. Reads layer_details for the entity, branches LANDBASE vs normal, applies custom-projection reprojection (ISCUSTOMPROJECTIONALLOWED + customProjection SRID from global_settings), resolves default parent from vw_layer_mapping, validates/normalises network_status, detects duplicate network_id per (network_id, parent_network_id), enforces user_permission_area, then dispatches by geometry type. All work is done as UPDATE … SET is_valid=false, error_msg=… against the staging table.

• fn_uploader_validate_point/line/polygon_parent_details (342 / 348 / 128 lines) — geometry-specific parent/containment validation.

• fn_uploader_check_termination_points (168 lines) — for line entities (Cable/Duct), snaps each line’s A/B endpoint to an existing point entity within LineUploadBufferInMeter; rejects with a descriptive error_msg if not found, and rewrites sp_geometry to add the snapped vertex.

• fn_uploader_insert_<entity> (~45 functions, e.g. fn_uploader_insert_pole 193 ln, fn_uploader_insert_cable 238 ln, fn_uploader_insert_duct 114 ln) — the commit step. Each one is hand-written for one entity, loops temp_du_<entity> rows, computes the network code (fn_get_clone_network_code or manual), inserts into att_details_<entity>

  • the geometry master, sets is_processed, and refreshes the cached GeoJSON. The bodies are 80–95% identical across entities — copy-paste with the table/column names swapped.

• fn_uploader_getuploadlogs (239 ln) — builds the results grid. Dynamically composes SQL per geom type and per status (SUCCESS/FAILED/SHOW_ON_MAP), joining the master view back to {geom}_master on db_flag=upload_id. For FAILED it reads the staging table directly (is_valid=false).

• fn_uploader_check_template / fn_uploader_get_entity_template — gate that a template exists and return the column map (Excel vs KML attributes) to the UI.

• fn_uploader_delete_record / fn_uploader_get_invalid_record_count — both just build … WHERE upload_id=… dynamically against layer_details.data_upload_table.

• fn_bulk_user_upload_process (310 ln) — the separate user-list bulk loader: copies bulk_user_upload_detail into a LOCAL TEMP TABLE, validates PAN/user-type/limits, then loops to create users + module/JO-type/JO-category/service-facility mappings. Gated by fn_bulk_user_upload_chk_limit which enforces licensed web/mobile user caps.

• fn_landbase_uploader_insert (92 ln) — the landbase commit: resolves landbase_layer_id and category/sub-category/classification IDs from landbase_dropdown_master, resolves parent province, builds geometry (with optional line buffering via ST_buffer_meters), inserts into att_details_landbase.

Design choices & trade-offs

1. Shadow staging table per entity (temp_du_<entity>). Each table mirrors the target att_details_<entity> shape but all-VARCHAR plus control columns (upload_id, batch_id, is_valid, error_msg, is_processed, row_order). This gives the app a loose landing zone where bad data can sit and be annotated, and where validation can run as set-based UPDATEs. Trade-off: 44 near-duplicate table definitions to maintain, and any new attribute must be added in three places (template, staging table, att_details).

2. One bespoke uploader function per entity (~45 insert + the validate family). There is no generic importer — adding an entity means writing a new ~150-line PL/pgSQL function that is a clone of an existing one. The presence of _test, _bkp_<date>, _31staug2021, _backup_04062021 variants right in the live schema shows the maintenance reality: people copy a function, tweak it, and leave the old copy behind.

3. Config-driven framing, code-driven execution. The shape (which columns, which staging table, max counts, mandatory flags) is data in layer_details + data_uploader_template — good. But the execution (the actual INSERT) is hard-coded per entity, so the config never fully closes the loop into a single engine.

4. In-place validation marking. Rejects are not moved to an errors table; they stay in the staging table with is_valid=false. Simple, but means staging tables conflate raw, valid, committed, and rejected rows simultaneously.

5. Dynamic SQL everywhere. Almost every function builds SQL by string-concatenating layer_details.data_upload_table and parameter values, then EXECUTEs it. This makes the functions generic over table name — at the cost of safety and readability (see risks).

Issues, risks & anti-patterns (evidence-backed)

• SQL injection by construction. Validation/insert functions concatenate user-influenced values straight into dynamic SQL, e.g. in fn_uploader_check_termination_points: error_msg=''… '||rec.a_network_id||' …'' where … system_id='||rec.system_id. network_id values originate from uploaded files. No format(%L/%I) or quote_literal. (Consistent with the project-wide injection findings.)

• No idempotency / no dedupe key. Re-running an insert is guarded only by is_processed=true, set after the INSERT. There is no unique constraint on network_id in att_details_* (the DB has only 84 FKs and most tables lack a PK), so a crash mid-loop or a re-submitted file can double-insert. Duplicate detection exists only within one upload batch (the duplicate-network_id check), not against already-committed master data.

• Staging tables never purged → unbounded growth & data leakage. temp_du_building carries 42,890 rows from 2023; temp_pole_csv (181k) and temp_pole_accessories (1.1M) are pure orphan residue. Roughly 1.95M rows of stale staging data sit in an 8.8 GB DB. fn_uploader_delete_record exists but isn’t on the happy path.

• Two abandoned import paths. The temp_*_csv family and temp_pole_accessories are referenced by no stored function — a second, older ingestion mechanism (direct COPY / app-side scripts) was never decommissioned, leaving large confusing tables.

• Stringly-typed staging. latitude, longitude, pole_height, etc. are VARCHAR in temp_du_pole, cast at insert time (rec.latitude::double precision). A bad value throws deep in the loop rather than being caught as a clean per-row validation error.

• Copy-paste backups in the live schema. fn_uploader_validate_parent_details has at least 4 variants (_test 799 ln, _bkp_14012021 623 ln, 1, base). It is impossible to know from the catalog which is canonical. Same for getuploadlogs, insert_bdb, insert_ont.

• Hard-coded business rules inside per-entity functions. UNIT default dimensions (length=10, width=5, height=5), structure min-distance buffer, network-status enum (P/A/D) are baked into PL/pgSQL, not config.

• No batch atomicity at the orchestration layer. Validate and insert are separate calls; a partially-inserted batch leaves the staging table half-is_processed and the master half-populated, with the only recovery being manual.

Implications for the new platform

Replace, do not port. The “shadow staging table + 92 bespoke functions” model is the single clearest example of per-entity sprawl in this database.

Keep the good ideas (as data, not code):

• The template registry concept (data_uploader_template: column map, mandatory, dropdown, max-length, min/max, KML-vs-Excel) is genuinely useful — carry it forward as the declarative contract for a single generic importer.
• The job ledger (upload_summary: per-job total/success/failed, status, file name) is a sound audit model — keep it (with a proper enum status and a finished-at timestamp).
• The validate-then-commit with per-row error capture UX is right; users clearly iterate to clean data (74% reject rates prove it). Keep the “annotate each row with why it failed”.

Redesign:

• One generic, schema-driven ingestion engine. Read the template/column-map; load any file into a single generic staging table (e.g. (import_id, row_no, payload jsonb, status, errors jsonb)); validate with composable, declarative rules; commit via a single parameterised path. Eliminate the 44 staging tables and ~45 insert functions.
• Parameterised SQL only (format(%I/%L) / bound params) — close the injection holes.
• Idempotency keys + uniqueness. A natural key (network_id scoped to parent/circle) with a unique constraint, plus an import_id-level upsert, so re-runs are safe.
• Transactional batches with explicit staging lifecycle (PENDING → VALIDATED → COMMITTED → PURGED) and automatic cleanup; treat staging as ephemeral.
• Typed staging payload (JSONB or typed columns), validate coordinates/numbers up front.
• Unify landbase + asset import under the same engine; landbase’s generic attribute_1..10 model suggests the team already wants schemaless attributes — provide that cleanly (JSONB) instead of as a one-off.
• Decommission the dead CSV path — do not migrate temp_*_csv / temp_pole_accessories.