Skip to content
Vinxi/Kernel THE BOOK OF RECORD

Operations (WFM/SmartOps), Feasibility (SmartSQ), Fault/Maintenance, Utilization & Wireless

Evidence-backed analysis of smartinventory_qa (PostgreSQL 14.8). All row counts are live count(*) results run during analysis (the catalog reltuples estimates in table_inventory.tsv are stale/zero for most of these low-volume tables). No PII was extracted; categorical columns report top-N counts only.

These are five distinct product modules layered on top of the core inventory (point_master / line_master / polygon_master + att_details_*, documented elsewhere). They share the same spine: integer system_id keys, the network_id / parent_network_id hierarchy, region_id / province_id scoping, the approval/audit metadata block, and layer_details capability flags. Each module is gated by a boolean flag on layer_details rather than by a separate schema.

Scope

ModuleCore tablesKey functionsLive data volume
WFM / SmartOpstbl_wfm_* (15), wfm_* (~20), user_timesheetfn_wfm_* (13)Mostly config/reference rows; almost no transactional data in QA
Network ticketingatt_details_networktickets, ticket_master, ticket_type_master, ticket_status_master, ticket_steps_master, ticket_type_role_mapping, hpsm_ticket_master, networkticket_traking_detailsfn_wfm_troubleticket_report, fn_wfm_orderfulfilment_report, fn_wfm_get_*_reportnetworktickets 190, ticket_master 210, hpsm 67
Feasibility / SmartSQfeasibility_input/history/geometry/inside_cable, feasibility_cable_type, dark_fiber_feasible_routes, user_feasibility_history, bg_routing_data(_noded), routing_datafn_sf_* (21) inc. fn_sf_get_routes, fn_sf_get_existing_fiber_route, fn_sf_get_available_coreshistory/input/geometry 0 in QA (transient); feasibility_cable_type 54
Fault & maintenanceatt_details_fault, att_details_fault_status_historyfault triggers; status workflowfault 95, status_history 122
Utilizationentity_utilization_settings, connection_info, att_details_cable_infofn_utilization_get_level, fn_get_utilization_report_* (23 utiliz fns)settings 33, connection_info 26,325, cable_info ~1,007,850
Wireless / mobileatt_details_tower (85 cols), att_details_sector (86), att_details_antenna (104), att_details_microwavelink (114), att_details_tower_pop(shared CRUD)tower 74, sector 15, antenna 12, microwavelink 0, tower_pop 0

These modules are functionally rich but data-light in QA. They are real, configured features (status workflows, role mappings, slot catalogs, utilization bands, cable-type price lists are all populated) but carry little live operational data — consistent with a QA/template instance. The exception is the inventory they read: att_details_cable_info (~1M core/port rows) and connection_info (26k) are the substrate that feasibility and utilization compute over.

Data model & relationships

Network ticketing — the real WFM spine

There are three parallel ticket modules discriminated by ticket_status_master.module and ticket_type_master.module:

  • Customer — consumer service requests; ticket_master (one row per customer ticket; holds PII: customer_name, contact_no, address, email, latitude/longitude). Statuses 1–3 (Assigned → InProgress → Completed). Types: New Connection / Shift Customer / Change Mapping.
  • Network_Ticket — internal network work orders; att_details_networktickets (50 cols). Statuses 4–9 + 1003 (Assigned → InProgress → Completed → Verified → Approved / Rejected / Unassigned). Types: Survey (SRV), Construction (CON), O&M, Planning (PL), Sanity (NT001), PST_Ticket, Audit.
  • HPSM — IT helpdesk integration (hpsm_ticket_master, 146 cols). Statuses 1000–1002 (Closed / In progress / Open). Types: Account Lock/Unlock, System Crash, Files/Template, etc. This is an HP Service Manager bridge, not network ops.

Join model:

  • att_details_networktickets.ticket_type_id → ticket_type_master.id, .ticket_status_id → ticket_status_master.id.
  • ticket_type_role_mapping (154 rows) is the RBAC grid: per (ticket_type_id, role_id) → is_create / is_edit / is_view / is_approve.
  • ticket_steps_master (64 rows) defines an ordered workflow per (module_id, order_type, service_type, rfs_type) via step_order — the configurable state machine.
  • networkticket_traking_details is the per-ticket audit trail (action_by, action_name, action_on, action_remark) — empty in QA.
  • Tickets carry the full inventory provenance block: network_id, csa/dsa/area/subarea_system_id, project_id/planning_id/workorder_id/purpose_id, gis_design_id, served_by_ring, origin_from/origin_ref_*, plus region_id/province_id. A Network_Ticket is effectively a work order attached to a place in the network hierarchy.

WFM / SmartOps appointment & scheduling layer (tbl_wfm_*, wfm_*)

This is a field-engineer (FE) appointment-booking + skills layer, mostly catalog data:

  • Order taxonomy: tbl_wfm_ordertype (19; oid) → tbl_wfm_servicetype (77; ttid, FK oid; columns add_service / remove_service / workflow as text blobs). tbl_wfm_jobstatus (27; action/status/sub_status).
  • Slots: tbl_wfm_slot (54; from_time/to_time as integers, sdid) and tbl_wfm_slot_duration; bookings in tbl_wfm_slot_confirmation (70; managerid, feuserid, appointment_date, iscanceled) and requests in tbl_wfm_slot_request (0).
  • Skills: tbl_wfm_skill (3; skid/name) ↔ tbl_wfm_fe_skill (userid→skid). tbl_wfm_servicetype.skid lists required skills.
  • JO (job-order) dimensions: wfm_jo_type_master (7), wfm_jo_category_master (2), wfm_service_facility_master (9; carries slot_duration), wfm_tt_type/wfm_tbl_tt_type (trouble-ticket types, 55/13). These are mapped to users and roles via wfm_user_jo_type_mapping, wfm_user_jo_category_mapping, wfm_user_service_facility_mapping, wfm_role_jo_type_mapping, wfm_role_service_facility_mapping (all empty).
  • Rostering: user_timesheet (1 row) holds user_id, manager_id, start_date/end_date, start_time/end_time, working_days (a substring-matched day list), is_active. This is the availability source for assignment.
  • Notifications: wfm_notification (0), wfm_notification_template (7), wfm_email_sms_log (0).

The WFM layer is disconnected from att_details_networktickets by FK (only 84 FKs exist DB-wide; none here) — linkage is by convention through referenceid/hpsm_ticketid integer columns and reporting functions that join on these by hand.

Feasibility / SmartSQ

  • Request: feasibility_input (feasibility_id, customer_id/name [PII], start_lat_lng/end_lat_lng, cores_required, cable_type_id, buffer_radius_a/b).
  • Result: feasibility_history (history_id, feasibility_id, core_level_result, feasibility_result, history_display_id) — the verdict.
  • Computed geometry: feasibility_geometry (proposed new-build cable segments with material_cost/service_cost) and feasibility_inside_cable (existing reusable cables with available_cores/total_cores/network_status P|A).
  • Cable-type price list: feasibility_cable_type (54; cores, material_price_per_unit, service_price_per_unit) + feasibility_demarcation_type (8).
  • Dark fiber: dark_fiber_feasible_routes (per request_id/route_id: geojson_existing_built, geojson_new_built, kml_*, cable_sytem_ids [sic], total_existing_length, total_new_length).
  • Routing substrate: routing_data / bg_routing_data (pgRouting edge table: id, source, target, cost, reverse_cost, geom) and its noded variant bg_routing_data_noded. Empty in QA (built per request).
  • Access log: user_feasibility_history (who ran a feasibility, from which IP/browser/machine).
  • is_feasibility_layer + feasibility_network_group on layer_details declare which entity types participate (see below).

Fault & maintenance

  • att_details_fault (72 cols) is a point entity registered in point_master (is_fault_entity flag) but also carries a self-contained fault_entity_system_id / fault_entity_type / fault_entity_network_id pointer to the faulted asset, plus parent_*, primary/secondary_pod_system_id, and the full hierarchy block. A fault is “a problem at a location, pointing at an inventory asset.”
  • att_details_fault_status_history (12 cols) is the resolution journal: fault_system_id, fault_status, rca (root-cause text), requested_by, request_comment, timestamps. The fault’s own fault_status/status_remark/status_updated_by/on hold the current state; history rows accumulate transitions.

Utilization

Not a table of its own — it is computed on demand from:

  • connection_info (26,325 rows) — port-level connections (source/destination_system_id, _entity_type, _port_no, is_through_connection).
  • att_details_cable_info (~1M rows) — per-core records for cables (cable_id, a_end_status_id, b_end_status_id).
  • entity_utilization_settings (33) — per (layer_id, region_id, province_id, network_status) (or is_default) the band thresholds utilization_range_{low,moderate,high,over}_{from,to}.
  • is_utilization_enabled flag on layer_details declares which layers expose utilization.

Wireless / mobile

Standard att_details_* point/line entities sharing the inventory spine. att_details_microwavelink is a link referencing two towers (tower_a_system_id/tower_b_system_id) and two antennas (antenna_a/b_system_id) with RF parameters (frequencies, modulation, bandwidth, polarization, nms_ip, and — notably — plaintext user_name/password columns). att_details_tower_pop is a join (tower↔pop). Wireless is gated by is_networktype_required.

What data is actually held (measured)

Network tickets (190 in att_details_networktickets):

  • reference_type: GIS 170 + gis 10 (≈95%), CRM 5 + crm 5 — origin is overwhelmingly the GIS app, with case-inconsistent values.
  • ticket_type_id: Survey(4) 67, Construction(7) 105, Planning(18) 8, O&M(9) 6, Audit(26) 2, Sanity(24) 2.
  • ticket_status_id: Assigned(4) 88, InProgress(5) 48, Approved(8) 25, Rejected(9) 15, Completed(6) 13 — i.e. most tickets are mid-flight, a few fully approved.
  • origin_from is empty for all 190 (the newer provenance columns are unused here).

ticket_master (210 customer tickets): Completed 133, Assigned 47, InProgress 26, Open 2.

Faults (95):

  • fault_type is free text and dirty: “FIBER CUT” 51 (the only real category), “fault” 7, “broken” 3, then dozens of junk values (“sdsd”, “test 1”, “h”, “43”, “Rat bite”, “jgjhfgj”…) each appearing 1–2×. No controlled vocabulary.
  • fault_status: Unresolved 93, Resolved 2 — effectively no closure workflow exercised.
  • fault_entity_type: Cable 23, BDB 2, Duct 2, FAT 1, Central_Office 1, blank/null 66 — most faults aren’t linked to a specific asset.
  • fault_ticket_type: GIS 9, blank 86.

Wireless:

  • Tower 74: network_type, tower_type, tenancy all essentially empty (blank for 73–74 rows) — towers are geometry placeholders with metadata unfilled.
  • Sector 15: technology is dirty free text — “5G” 2, “4G” 1, “PTMP” 1, plus junk (“fvg”, “ghj”, “sa”, “123”, “1”, “6”, “7”).
  • Antenna 12: antenna_type Microwave 6, RF 4, test 2.
  • MicrowaveLink 0, tower_pop 0 — the link/relationship layer is unused in this dataset.

Utilization settings (33): band thresholds configured per layer/region; connection_info 26k and att_details_cable_info ~1M are the real inputs.

WFM: servicetype 77, slot 54, slot_confirmation 70, jobstatus 27, ordertype 19 — catalog is configured; user_timesheet has 1 row, slot_request/notification/all user-role mappings are 0. The scheduling engine is wired but barely used in QA.

Layer flags (layer_details, 50 layers with any of these flags set):

  • is_feasibility_layer = true on demarcation/serviceable points: ADB, BDB, CDB, FDB, DP, CX, GX, HTB, MPOD, POD, ONT, Splitter, Gipipe, Handhole, Manhole, Structure.
  • feasibility_network_group = 'Passive Layers' groups Antenna, Cabinet, Conduit, Microduct, MicrowaveLink, OpticalRepeater, PatchPanel, Sector, Tower, Vault, Gipipe (note VSAT has the literal string 'false' — a stringly-typed bug).
  • is_fault_entity = true: Cable, Duct, SpliceClosure, FMS, Splitter, ONT, GX, CX, HTB, POD, MPOD, ADB/BDB/CDB/FDB, Cabinet, Vault, PatchPanel, OpticalRepeater, Rebar, Reinstatement, Gipipe, Structure (faults can be raised against most physical assets).
  • is_utilization_enabled = true: Cable, Duct, SpliceClosure, Trench, Splitter, ONT, GX, CX, FMS, FDB, BDB, CDB (port/core-bearing entities).
  • is_maintainence_charges_enabled = true: Cable, Duct, SpliceClosure, Trench, FiberLink, Handhole, Manhole, ROW (linear/civil assets that accrue OPEX).
  • is_networktype_required = true for almost every layer except ROW.

Business logic (key stored functions)

fn_sf_get_routes(source, dest, start_buffer, end_buffer, core_required) — 258 src-lines (schema_full.sql ~177244). The feasibility/serviceability engine. It:

  1. Runs pgr_dijkstra over bg_routing_data (a pgRouting edge graph), seeding start/end vertices via ST_DWithin of the source/dest points against edge geometry within the buffers (geodesic, SRID 4326).
  2. Builds candidate routes into a temp table, merges geometries (ST_LineMerge), and orders by length.
  3. For each edge, finds the matching Cable in line_master/att_details_cable by endpoint coincidence, and keeps only cables where fn_sf_get_available_cores(system_id) >= p_core_required.
  4. Applies ODF free-port logic at FMS endpoints (a_entity_type='FMS'). Net effect: “can I reach destination from source over existing cable that still has ≥N free cores?” This is the literal definition of a feasibility check.

fn_sf_get_available_cores(p_cableid) — 12 lines. Counts rows in att_details_cable_info where cable_id=? and a_end_status_id in (1,2) and b_end_status_id in (1,2). The comment encodes the status vocabulary: 1=available, 2=connected, 3=Reserved, 4=Faulty, 5=Reserved&Connected, 6=Reserved&Faulty. (Defining “available” as in (1,2) — i.e. counting connected cores as available — looks like a deliberate-but-questionable rule.)

fn_sf_get_existing_fiber_route(request_id, source, dest, ...) — 190 lines. Dark-fiber variant: pre-filters routing_data to edges with enough cores (fn_sf_get_available_cores(system_id) >= p_fiber_cores) into a temp graph, then pgr_dijkstra. Computes existing-vs-new-build segments, lengths, GeoJSON+KML, and overwrites dark_fiber_feasible_routes for that request_id (a write performed inside a SELECT-style read function).

fn_sf_get_cable_geoms(history_id) — assembles a feasibility result for display: UNION of proposed new-build geometry from feasibility_geometry (with material_cost/service_cost from feasibility_demarcation_type) and reusable existing cable from feasibility_inside_cable (tagged inside_P/inside_A by network_status).

fn_utilization_get_level(system_id, entity_type) — 80 lines (~219341). Core utilization calculator:

  1. Resolves layer_table/geom_type/layer_id from layer_details and dynamically EXECUTEs select * from <layer_table> where system_id=?.
  2. Picks band thresholds from entity_utilization_settings matching (layer_id, region, province, network_status) else is_default.
  3. Counts used ports from connection_info (both source and destination sides, excluding through-connections and same-entity-type loops).
  4. For LINE entities total = core count from att_details_cable_info; for points, parses point_master.no_of_ports (SPLIT_PART(no_of_ports,':',2) — a "label:count" encoded string).
  5. utilization% = used/total*100, then buckets into L/M/H/O against the bands. Returns abbr + message + totals.

fn_wfm_validate_user_roster / fn_wfm_validate_user_timesheet — 12 / 19 lines. Both query user_timesheet for an FE on a given date, matching working_days like '%'||day||'%' (substring match — fragile) and start_time/end_time. The validate function returns text 'success'/'failed'. This is the availability gate for assignment.

Reporting functions (fn_wfm_troubleticket_report 181, fn_wfm_orderfulfilment_report 177, fn_wfm_get_tt_jo_report 131, fn_wfm_get_jo_report 121, fn_get_utilization_report_* up to 238 lines): large hand-built, paginated, JSON-returning report builders driven by ~10–16 string parameters (region/province/status/search/sort/page), filtered by date and ticket_source_id. The utilization report functions exist in 3–4 near-duplicate copies (_bkp, _backup, _backup_12082024, _new) — copy-paste versioning in the DB.

Design choices & trade-offs

  • Config-driven module activation via layer_details flags (is_feasibility_layer, is_fault_entity, is_utilization_enabled, is_maintainence_charges_enabled, is_networktype_required). One registry row turns a capability on for an entity type — flexible, but it pushes all dispatch into dynamic SQL (EXECUTE 'select * from '||layer_table) and means behavior is data-not-code.
  • Three ticket sub-systems share two master tables (ticket_*_master) discriminated by a module string. Customer / Network_Ticket / HPSM are unified at the lookup level but split across different fact tables (ticket_master vs att_details_networktickets vs hpsm_ticket_master), so there is no single “ticket” entity.
  • Feasibility leverages pgRouting (pgr_dijkstra) over a dedicated edge graph (bg_routing_data/routing_data) decoupled from line_master. Real graph algorithms, not ad-hoc BFS — a genuine strength. But the graph is rebuilt into temp tables per request, and feasibility logic is embedded in 200+ line plpgsql with heavy RAISE NOTICE debug logging left in.
  • Utilization is computed, not stored — always fresh, but every report re-derives port counts from a ~1M-row att_details_cable_info and 26k connection_info with per-row fn_sf_get_available_cores calls (N+1 inside Dijkstra loops).
  • Wireless reuses the generic att_details_* pattern rather than a bespoke RF schema — consistent with the rest of the platform, at the cost of 85–114-column tables mostly empty for these few rows.
  • Provenance everywhere: tickets, faults, and wireless entities all carry the same ~20-column hierarchy/origin/approval block (csa/dsa/area/subarea_system_id, project/planning/workorder/purpose_id, origin_from, gis_design_id, served_by_ring). Strong traceability, massive column duplication.

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

  1. No referential integrity. None of these tables has FKs to its lookups (ticket_type_id, ticket_status_id, cable_type_id, jo_type_id, skill ids, assigned_to users). Orphan/stale references are unconstrained.
  2. Free-text where there should be lookups. att_details_fault.fault_type is garbage (“sdsd”, “Rat bite”, “43”, “jgjhfgj”); att_details_sector.technology mixes “5G” with “ghj”/“123”. No enum/lookup enforcement → reporting and analytics are unreliable.
  3. Case-inconsistent categorical data. reference_type = GIS vs gis, CRM vs crm in the same column → every GROUP BY double-counts.
  4. Questionable core-availability rule. fn_sf_get_available_cores counts status (1,2) = available + connected as “available,” so feasibility may report capacity that is already in use. The commented-out stricter version (=1) suggests this was intentionally loosened.
  5. Plaintext credentials in inventory. att_details_microwavelink stores user_name/password (NMS device creds) in clear varchar(200). Combines with the known DB-wide hardcoded-key/SQLi findings.
  6. Write-inside-read functions. fn_sf_get_existing_fiber_route performs DELETE ... ; INSERT ... into dark_fiber_feasible_routes despite being a query-style function — side effects hidden in a “get” call.
  7. Copy-paste function versioning. fn_get_utilization_report_summary has _backup, _backup_12082024; fn_get_utilization_show_on_map and fn_sf_get_past_feasibilities have multiple overloads/bkps. Dead code accumulates in the DB with no source control.
  8. Fragile string logic. Rostering matches working_days LIKE '%Mon%' (substring); utilization parses ports via SPLIT_PART(no_of_ports,':',2) (a "label:count" string). Both break on bad data.
  9. feasibility_network_group is stringly-typed — most rows hold 'Passive Layers' or empty, but VSAT holds the literal 'false', a clear data bug.
  10. Module wiring exists but is unused in QA (slot_request, notifications, all WFM user/role mappings, microwavelink, tower_pop = 0 rows; faults 93/95 Unresolved). Hard to judge production behavior from QA; the model is more elaborate than the data exercises.
  11. Debug noise in production functions — RAISE INFO/NOTICE scattered through fn_sf_get_routes and fn_utilization_get_level.

Implications for the new platform

Keep / carry forward (the genuine domain value):

  • The work-order lifecycle: Survey → Construction → O&M → Audit with Assigned → InProgress → Completed → Verified → Approved/Rejected, plus a configurable ticket_steps_master-style state machine and a ticket_type_role_mapping-style RBAC grid. This is a real, useful WFM model.
  • The feasibility concept computed via a routing engine: “reach X from Y over existing fiber with ≥N free cores, else cost the new build.” Keep pgRouting/graph-based routing (aligns with the topology-engine direction: pgRouting + CTEs) but make the graph a first-class maintained structure, not a per-request temp table.
  • Computed utilization with configurable bands per region/network-status — good idea; keep entity_utilization_settings as a tuning surface.
  • Fault → status-history journal with RCA — keep the audit trail; it’s the right shape.

Redesign:

  • Unify the ticket model. One polymorphic Ticket/WorkOrder entity with typed subtypes, not three fact tables (ticket_master / att_details_networktickets / hpsm_ticket_master) joined by convention. Push HPSM out to an integration boundary.
  • Enforce lookups & FKs for status, type, fault_type, technology, cable_type, skills, assignee, network_status. Replace free-text fault_type/technology with controlled vocabularies. Normalize case at write time.
  • Fix the core-availability semantics — model port/core state as a typed enum with an unambiguous “available” definition; never count connected cores as free.
  • Persist feasibility results as proper records (request → result → segments) rather than transient temp tables + side-effecting “get” functions; separate read from write.
  • Materialize/maintain utilization incrementally (or via a maintained connection graph) instead of re-scanning ~1M att_details_cable_info rows per report; eliminate N+1 fn_sf_get_available_cores calls inside route loops.
  • Move credentials out of inventory — never store device user_name/password in entity tables; use a secrets store.
  • Replace flag-driven dynamic SQL dispatch (is_*_layer + EXECUTE 'select * from '||layer_table) with a typed capability/component model; collapse the duplicated 20-column provenance block into a shared, normalized lineage record.
  • Wireless can remain a thin module, but give microwave links a proper two-endpoint relationship with typed RF attributes rather than 114 mostly-null columns.