05 — Network Planning / Auto-Design, Route Computation & BOM/BOQ Costing (SmartPlanner)

Evidence base: live smartinventory_qa (PostgreSQL 14.8, PostGIS 3.2.2, pgRouting 3.3.1), references/db-analysis/raw/ dumps (schema_full.sql, function_inventory.tsv, columns.tsv, table_inventory.tsv). All row counts/values below are from queries actually run against the QA DB on 2026-06-16.

Scope

This domain covers three loosely-coupled subsystems that share the master-geometry spine (point_master/line_master/polygon_master + att_details_*, keyed by system_id):

1. Interactive auto network planning (“SmartPlanner” / draw-a-route) — temp_auto_network_plan (400,280 rows / 186 plans), att_details_network_plan (76 saved plans), fn_network_planning_* (30 fns), fn_network_planning_auto_create_{trench,duct,cable} helpers.
2. Batch auto-design & FAT auto-splicing — sp_plan_details/sp_plan_rings/sp_temp_auto_network_plan, design_id_generator (5,579 rows), fat_process_summary (829), fat_process_details (382), fn_fat_generate_splicing (2,895 src lines — the single largest function in this domain), fn_auto_* (27), fn_process_* (57).
3. Route computation (SmartFiber feasibility) — the only real pgRouting usage in the DB: fn_sf_get_routes (two overloads, 98 & 258 lines) over routing_data/routing_data_noded (+ bg_* variants), built by fn_fs_create_routing_nodes / fn_fs_create_routingdata.
4. BOM/BOQ material take-off & costing — bom_boq_info (573), bom_boq_info_draft (3,548), bom_boq_master (29 config rows), construction_bom_details (1,327), item_template_master (230, the rate card), plus ~40 fn_get_bom_boq_* report functions.

Total managed objects in scope: ~30 tables and ~200 stored functions (most of which are dated backup copies of ~12 real functions).

Data model & relationships

Planning persistence is a two-phase staging model

The interactive planner is a stage-then-commit pipeline keyed by plan_id:

• Stage: the UI draws a polyline; the app computes split points (spans of manhole_pole_span meters) and writes one row per intended node into temp_auto_network_plan (a staging table, not “temp” in the SQL sense — it is a persistent table with 400k rows). Each row carries plan_id, entity_type (Manhole|Pole), fraction (0–1 position along the line via ST_LineInterpolatePoint), is_middle_point, lat/long, sp_geometry, plus back-references that get filled in on commit: cable_id, duct_id, trench_id, *_network_id, and a loop_* block (loop_length, loop_parent/associated entity, loop_sequence_id).
• Commit: fn_network_planning_save_auto_planning(...) reads the staged rows and materialises real network entities.

att_details_network_plan (76 rows, PK planid) is the header — the saved plan parameters: plan_name, start_point/end_point, cable_type (overhead/underground), is_create_trench, is_create_duct, pole_manhole_distance, cable_length, planning_mode, end_point_buffer, is_loop_required, loop_length, layer_id (comma-string of created layers). sp_temp_auto_network_plan is a near-identical clone with an extra route_no (the batch/ring variant).

Batch SmartPlanner config (sp_plan_details)

A far richer planning spec than the interactive table — one row per batch plan: plan_topology (e.g. Underground), primary_spliter_ratio/secondary_spliter_ratio (e.g. 1_12), manhole_pole_span (800), fms_port_size, max_demand_points_spur, max_demand_points_ring, max_cable_length, cable_durm_length, extra_buffer_cost_bom_percentage, use_restricted_area/use_restricted_road, splitter_count_in_fdc_box, router_at_gp/odf_at_gp/rack_at_gp, MSC lat/long, is_processed, running_status, bom_boq_entities. sp_plan_rings links plan → named rings. This is the “give me a serving area + a rate-config, auto-generate the whole FTTx tree” entry point.

Routing graph (pgRouting)

The graph is not the live line_master — it is a materialised snapshot table built on demand:

• fn_fs_create_routingdata(dest_table) does CREATE TABLE … AS SELECT c.system_id AS id, …, ST_Length(ST_Transform(…,26986))/1000 AS length, 0 AS cost, 0 AS reverse_cost, lm.sp_geometry AS geom FROM line_master lm JOIN vw_att_details_cable c ON lm.system_id=c.system_id WHERE lm.entity_type='Cable'. Edges = existing fiber Cables; cost = cable length in km.
• fn_fs_create_routing_nodes() then runs the full pgRouting topology build: pgr_createTopology → pgr_analyzegraph → pgr_nodeNetwork → pgr_createTopology → pgr_analyzegraph (tolerance 0.000001), producing routing_data_noded (800 edges) + routing_data_noded_vertices_pgr (1,735 nodes). The bg_* variants (bg_routing_data_noded_vertices_pgr = 3,911 nodes) are a larger/other dataset.
• fn_sf_get_routes(source, dest, start_buffer, end_buffer[, cores]) snaps source/dest points to graph vertices via ST_DWithin, then calls pgr_dijkstra('SELECT id, source, target, cost, reverse_cost FROM routing_data', …, directed:=True), joins back to _noded.geom, and returns the ordered edge path. The 5-arg overload also checks ODF free-port availability per route.

FAT auto-splicing model (fat_process_*)

Keyed by fat_process_id and a SubArea (sub_area_system_id → polygon_master where entity_type='SubArea'). fat_process_summary (829) = one run per SubArea with process_status (Success/Failed/InProcess) and approval_status (Pending/Discarded/…). fat_process_details (382) = the computed splice plan: splitter ↔ parent (FDB/FMS/BDB), connection_ring_name, input_1/2_cable_system_id + …_fiber_no, is_connected. fat_process_running_status is a per-area lock (bt_lock).

Design-ID hierarchy

design_id_generator (5,579 rows) mints structured codes along the FTTx hierarchy: columns city_code, psa, fsa, dsa, csa, ring_number, sequence_id, psa_system_id, fsa_system_id, feature_type. feature_type distribution: blank 4,007, DISTRIBUTION CABLE 647, FEEDER CABLE 394, S2 267, S1 264 (S1/S2 = primary/secondary splitter tiers). gis_design_id is a column on 155 att_details_* tables — a near-universal “which design produced this entity” stamp (e.g. att_details_cable has 99 distinct gis_design_id values). design_sequence_master (1,009) backs sequence allocation.

Catalog

Object	Rows	Purpose
temp_auto_network_plan	400,280 / 186 plans	Staging: one row per planned point node (Manhole/Pole) before commit
sp_temp_auto_network_plan	449	Same, batch/ring variant (adds route_no)
att_details_network_plan	76	Saved interactive-plan headers (parameters)
sp_plan_details	1	Batch SmartPlanner config (topology, splitter ratios, demand limits, BOM buffer %)
sp_plan_rings	12	Plan → ring mapping
design_id_generator	5,579	Structured design-code allocation (city/psa/fsa/dsa/csa/ring/seq)
design_sequence_master	1,009	Sequence counters per design level
routing_data_noded	800	pgRouting noded edge graph (from Cables)
routing_data_noded_vertices_pgr	1,735	pgRouting vertices
bg_routing_data_noded_vertices_pgr	3,911	Larger routing graph vertices
fat_process_summary	829	FAT auto-splicing run per SubArea
fat_process_details	382	Computed splice connections (splitter↔cable fibers)
bom_boq_info	573	Materialised BOM line items (qty × rate)
bom_boq_info_draft	3,548	Draft BOM working set (42 cols)
bom_boq_master	29	Config: which layers/columns feed the BOM report
bom_boq_revision_info	467	BOM revision tracking
construction_bom_details	1,327	Overhead-construction take-off (pole/clamp/cable counts per town)
item_template_master	230	The rate card (cost_per_unit, service_cost_per_unit)

What data is actually held

• temp_auto_network_plan (400,280 rows, 186 plans, 3 users): entity_type is only Manhole (383,601) and Pole (16,679) — these are the placed-node points, not cables. Plans are highly skewed: top plan 186 = 231,957 rows (115,981 mid-points); plans 300/301/289 = 73k/34k/21k. cable_network_id/duct_id/trench_id are mostly 0 (these are filled only on a committed underground plan). The table is a 4 GB staging area that is never garbage-collected.
• bom_boq_info (573 rows, 5 FSAs, 12 entity types): rate card resolves — Secondary splitter avg ₹6,285 (capex ₹25,140), Clamp avg ₹913, Protective/Suspension/Termination Set ~₹53–73, Duct ₹1,000. Most rows have jpf_total_capex = 0 (rate not yet loaded). Quantities are small (this is QA data).
• item_template_master: 155 of 230 items have cost_per_unit > 0; average non-zero rate ₹2,684. This single table is the cost source the BOM report multiplies against.
• sp_plan_details: the one real batch plan — Underground, splitter ratios 1:12 / 1:12, 800 m span, not yet processed.
• FAT: 829 runs across SubAreas with a real Success/Failed/InProcess mix → the auto-splicing tool is actively used.
• Routing graphs are small (800–3,911 edges/nodes) and clearly per-region rebuilds, not the whole 95k-cable network.

Business logic (key stored functions)

fn_network_planning_save_auto_planning (531 lines; main overload at schema_full.sql:141985). The heart of interactive auto-design. Given a drawn line + parameters it: (1) validates via fn_network_planning_validate; (2) resolves cable_type → point entity (overhead→Pole, else Manhole); (3) pulls audit_item_master_id per layer via fn_get_template_detail; (4) inserts the plan header into att_details_network_plan; (5) loops the staged temp_auto_network_plan rows in system_id order, and for each: computes the point geom via ST_LineInterpolatePoint(line, fraction), resolves region/province (fn_getregionprovince), clones a network code (fn_get_clone_network_code), and inserts a real Manhole/Pole (att_details_* + point_master, network_status='P', source_ref_type='planning', source_ref_id=plan_id); optionally inserts a SpliceClosure (with fn_bulk_insert_port_info); then between consecutive points calls fn_network_planning_auto_create_trench → _auto_create_duct → _auto_create_cable to lay the linear segments, updating the staging row’s trench_id/duct_id/cable_id; and records cable-to-cable splicing into a temp temp_connection set. So “auto network planning” here = auto-place poles/manholes/splice-closures along a route at fixed spans, then auto-generate the trench/duct/cable conduit stack and the splice plumbing, all stamped as Planned and tagged to the originating plan. (Note: 717-line …_27122024 and 528-line …_test_by_rr are dated backups — the production body is the 531-line one.)

fn_fat_generate_splicing (2,895 lines; schema_full.sql:35218). Batch FAT (Fiber Access Terminal) auto-splicing for a green-field SubArea (FSA). It refuses to run if the area already has connections ('Tool will run on Green Field only'), discards prior Pending runs, then traces the ring/spur topology: finds FDPs (min 2), distribution & spur connectivity points, ring loops, and computes which splitter port splices to which cable fiber — populating fat_process_details with splitter↔parent and input_N_cable_fiber_no assignments. This is the auto-design “wire up the whole serving area” engine, and at 2,895 lines is the most complex single procedure in the domain.

fn_sf_get_routes (98 / 258 lines; schema_full.sql:177138 / 177244). SmartFiber dark-fiber feasibility. Runs pgr_dijkstra over the cable graph to find the shortest existing-fiber path between two points within buffer radii; the 5-arg overload additionally enforces p_core_required against ODF free ports and returns multiple ranked routes. Supporting cast: fn_sf_get_existing_fiber_route (190 lines), fn_sf_get_available_cores, fn_sf_get_past_feasibilities (history), fn_sf_get_export_bom_data. fn_fs_create_routing_nodes (66 lines) rebuilds the pgRouting topology.

fn_get_bom_boq_report_new (up to 425 lines; schema_full.sql:64443, 4 overloads). Material take-off engine. It opens a cursor over bom_boq_master ⋈ layer_details (config rows that name, per layer, the entity_sub_type_column, calculated_length_column, gis_length_column, order_by_statement), and for each enabled+permitted layer dynamically builds SQL that aggregates from the layer’s att_details_* table: counts and lengths split three ways by network_status — P=Planned, A=As-built, D=Dormant — joined to item_template_master for cost_per_unit/service_cost_per_unit. So BOQ = Σ(entity count or length) × unit rate, faceted by lifecycle status, region/province/project/work-order/purpose filters, and optionally a geom/route window. Loss/optical reporting via fn_get_bom_boq_loss_detail; overhead-construction specifics via fn_construction_bom_details* (pole/clamp counts).

fn_auto_provisioning (631 lines; schema_full.sql:17105). Customer-side auto-provisioning: given a CAN/box/splitter port + WCR list, auto-creates the ONT/drop and splices it to a splitter port (returns ont_system_id). Related: fn_auto_codification (489–499 lines) auto-assigns network codes to entities, fn_process_validate_network (multiple, ~234–299 lines) validates connectivity, fn_process_point_to_point_network_planning (265 lines) the P2P planner variant.

Design choices & trade-offs

• Stage-then-commit via a persistent “temp” table. temp_auto_network_plan lets the UI iterate on node placement before committing real entities. Trade-off: it is a 400k-row / 4 GB un-pruned staging table with no PK and only plan_id to scope it; old plans are never deleted.
• Materialised, on-demand routing snapshot instead of routing the live graph. The pgRouting graph is a separate CREATE TABLE AS from line_master Cables, then pgr_nodeNetwork/pgr_createTopology. Pros: stable node IDs, fast Dijkstra on a small (800–3,900-edge) graph, no impact on the live 95k-cable table. Cons: the graph is stale the moment a cable changes and must be manually rebuilt; it duplicates geometry; multiple parallel graphs (routing_data vs bg_routing_data) drift.
• Costing as runtime dynamic SQL over a flag-driven config (bom_boq_master + layer_details). Adding a layer to the BOM is config, not code — consistent with the DB’s “config-not-code via flag columns” philosophy. Cost is the price of EXECUTE-string SQL: unindexable, hard to test, and the report function exists in ~40 dated copies.
• Lifecycle baked into every aggregate (P/A/D). Planned vs As-built vs Dormant is a first-class split everywhere in BOQ — a deliberate “digital-twin tracks design→build delta” choice.
• Green-field-only auto-splicing. fn_fat_generate_splicing deliberately refuses brown-field areas, sidestepping the hard merge/reconciliation problem.

Issues, risks & anti-patterns

1. Backup-copy sprawl in the function catalog. ~12 real procedures hide behind ~200 dated duplicates (fn_fat_generate_splicing_bk9thmay2023, fn_network_planning_save_auto_planning_27122024, fn_get_bom_boq_report_new_2021aug02, _test, _bkp, _duplicate, …). There is no way to know from the DB which overload the app actually calls — a major reverse-engineering hazard.
2. 2,895-line procedures. fn_fat_generate_splicing is untestable, untraceable business logic embedded in PL/pgSQL with hand-managed temp tables (TMP_FAT_FDP_DETAILS, TEMP_FAT_CABLES_DETAIL, …). Auto-design correctness is unverifiable without re-running it.
3. Unbounded staging table. temp_auto_network_plan = 4 GB, no PK, never pruned; plan_id 186 alone is 232k rows.
4. Stale routing graph. No trigger keeps routing_data in sync with line_master; feasibility results can silently route over cables that no longer exist. Multiple parallel graphs (routing_data/bg_routing_data) with no documented difference.
5. Stringly-typed money & ratios. primary_spliter_ratio='1_12' (text), bom_boq_entities a 1,000-char CSV, rates as numeric(10,2) with no currency, ~73% of jpf_total_capex = 0. Costing has no FK to a currency/effective-date rate table — item_template_master.cost_per_unit is a single point-in-time number.
6. EXECUTE-string BOQ. fn_get_bom_boq_report_new builds SQL by string-concatenating column names from information_schema and bom_boq_master config — fragile, injection-adjacent, and impossible to plan-optimize.
7. Geometry SRID inconsistency flows through here: routing builds at 4326, computes length by transforming to EPSG:26986 (a Massachusetts state-plane CRS!) for an India/RJio dataset — a likely cut-and-paste bug producing wrong km lengths.
8. Plan→entity provenance is a soft string (source_ref_type='planning', source_ref_id=plan_id), not an FK; deleting a plan orphans nothing/everything.

Implications for the new platform

Keep (the domain concepts are sound):

• The two-phase plan → commit model (draft design, then materialise) — but back it with a real plan/plan_node/plan_segment schema with FKs and a status enum, not a 400k-row un-pruned staging table.
• The P/A/D lifecycle as a first-class entity status — it is genuinely the digital-twin value proposition. Make it an enum, computed BOQ deltas as views/materialised views.
• The rate-config-driven BOQ (which layers/columns feed the take-off) — but as typed configuration + parameterized queries, not EXECUTE-string SQL.
• Structured design codes (design_id_generator hierarchy) — formalize as a code-allocation service.

Redesign:

• Routing engine decision (high-value evidence for the rewrite): existing pgRouting usage is narrow and shallow — a single pgr_dijkstra shortest-path over a small (≈800–3,900-edge), manually-rebuilt, per-region cable snapshot for dark-fiber feasibility, plus a pgr_createTopology/pgr_nodeNetwork build step. No max-flow, no k-shortest-path, no TSP, no continuous topology. This does not justify a graph database. pgRouting + CTEs (or even recursive SQL) over a properly-maintained edge table covers every real use here; the main fix needed is keeping the routing graph in sync with the network (trigger/materialised view) and fixing the EPSG:26986 length bug. Auto-design tracing (fn_fat_generate_splicing, validate-network) is recursive graph walking that maps cleanly to recursive CTEs.
• Decompose the mega-functions (fn_fat_generate_splicing 2,895 lines, auto-planning 531) into testable, version-controlled application/service code; the DB should hold data + thin set-based queries, not 3k-line imperative procedures.
• Real money model: currency + effective-dated rate cards with FKs, replacing the single mutable cost_per_unit column and CSV/1_12-string config.
• Garbage-collect / scope plan staging; tie plan provenance to entities by FK, not soft strings.
• One canonical version of each procedure — eliminate the dated-backup sprawl; use migrations + git for history.