ISP Ports, Splicing & Fiber Connectivity

Evidence-backed analysis of the live smartinventory_qa DB (PostgreSQL 14.8). All counts/values come from queries run against the live server on 2026-06-16, or from the pre-dumped artifacts in references/db-analysis/raw/. READ-ONLY analysis.

This is the domain that matters most for the greenfield graph engine: how fiber connectivity, splicing, ports, and the indoor (inside-plant / ISP) hierarchy are represented. Headline finding: connectivity is an explicit, single-row, directed edge table (connection_info), keyed by composite logical identifiers — NOT by foreign keys, and NOT implied via shared network_id. Graph traversal is done imperatively in PL/pgSQL by spilling edges into temp work-tables and finishing with a WITH RECURSIVE walk. This maps reasonably cleanly to a property-graph model, with important caveats below.

Scope

Tables and functions covered, with measured volumes:

Connectivity / edge tables

Object	Rows (measured)	Cols	Purpose
`connection_info`	26,325	27	The central connectivity edge table. One row = one directed port-to-port connection (a splice, patch, or through-connection).
`att_details_cable_info`	1,010,094	21	Fiber-strand registry: one row per (cable, tube, core, fiber). The “ports” of a cable.
`fat_connection_info`	5,324	15	Staging/output of the FAT auto-splicing engine (no PK).
`connection_master`	1	9	Config/lookup (effectively unused).

ISP port & equipment tables

Object	Rows	Cols	Purpose
`isp_port_info`	~301,300 (measured; est. 280k)	22	Port inventory for indoor equipment (HTB/FMS/Splitter/ONT/PatchPanel/CX/GX). Stores port number, direction, status, and inline destination columns (mostly unused).
`isp_fdb_info`	486	95	Fiber Distribution Box (indoor splicer).
`isp_htb_info`	183	95	Horizontal Termination Box (indoor splicer; huge port counts).
`isp_opticalrepeater_info`	1	92	Optical repeater (barely populated).
`isp_port_master`, `isp_type_master`	0	—	Empty lookup tables.

ISP indoor hierarchy (building interior)

Object	Rows	Purpose
`att_details_building`	142,673	The building (outdoor footprint, OSP-side).
`att_details_bld_structure`	142,467	Building structure: `no_of_floor`, `no_of_shaft`, `no_of_flat`, `home_pass`, `business_pass`. FK → `att_details_building(system_id)`.
`isp_floor_info`	142,665	Floors of a structure (`structure_id`).
`isp_shaft_info`	176,544	Vertical riser shafts (`structure_id`, `with_riser`, `shaft_position`).
`isp_room_info`	77	Rooms/units within a floor (`floor_id`, `structure_id`).

Splice / patch component tables

Object	Rows	Purpose
`att_details_spliceclosure`	2,847	Outside-plant splice closures (OSP joints).
`att_details_splice_tray`	12	Trays inside a closure (`parent_system_id`, `tray_number`, `no_of_ports`).
`att_details_patchpanel`	62	Patch panels (`no_of_input_port`, `no_of_output_port`).
`att_details_patchcord`	162	Patch cords; stores A/B endpoints inline (`a_system_id`/`a_entity_type`, `b_system_id`/`b_entity_type`).
`att_details_fiber_link`	118	Logical end-to-end fiber link (a “service path” abstraction over physical splices); no PK.

ISP model catalogue (config-not-code for indoor equipment templates)

isp_model_mapping (702), isp_model_info (58), isp_model_rules (54), isp_model_type_master (41), isp_model_master (7), isp_base_model (60), isp_brand_master (60), isp_model_color_master (117), plus isp_template_{fdb,htb,room,opticalrepeater}. These define how indoor box models map ports and what child models may attach (parent/child model-id rules) — the basis for fn_splicing_isp_model_get_connection.

Key functions

Traversal / read: fn_get_schematic_view (478 src lines), fn_getchildren_connection_info_path (recursive CTE), fn_get_connection_info_new (385), fn_otdr_get_fiber_cut_details (383), fn_process_validate_network (299), fn_api_get_fault_location_detail (433).
Splice write / rules: fn_splicing_save_connections (405), fn_splicing_insert_into_connection_info (343, trigger-style), fn_validate_splicing (400), fn_splicing_get_connection (238), fn_splicing_isp_model_get_connection (188), fn_splicing_save_osp_split_connection/_isp_split_connection.
FAT (Fiber Access Terminal) auto-splice: fn_fat_generate_splicing (2,895 src lines — the single largest function in this domain), plus 4 dated backup copies (_bk9thmay2023, _bkp252023, etc.).
Trigger: fn_trg_update_core_port_status (342) keeps fiber/core port status in sync on connection changes.

Data model & relationships

The connectivity spine: `connection_info` is an explicit edge list

connection_info is a directed, single-row edge table. Each row is one connection with a fully-described source and destination:

connection_id (PK)
source_system_id, source_network_id, source_entity_type, source_port_no, source_entity_sub_type, source_tray_system_id, source_display_name
destination_system_id, destination_network_id, destination_entity_type, destination_port_no, destination_entity_sub_type, destination_tray_system_id, destination_display_name
is_cable_a_end, is_through_connection, is_customer_connected, is_deleted
splicing_source, fat_process_id, created_on/by, approved_on/by

Endpoints are identified by the composite logical key (entity_type, system_id, port_no) — the same system_id/entity_type discriminator used by the geometry master tables (point_master/line_master) and the per-entity att_details_* tables. There is no foreign key from connection_info to anything (it has a PK but the fk_graph.tsv dump shows zero FKs referencing it). The endpoint tables it logically points at (cables, splitters, FMS, FDB, HTB, etc.) live in different tables depending on entity_type.

Is it bidirectional / symmetric? No. A reciprocal-edge self-join (a.src=b.dst AND a.dst=b.src on matching ports) found only 16 reciprocal pairs out of 26,325 rows. The fact that Cable → SpliceClosure (6,959) and SpliceClosure → Cable (6,840) both appear is because a cable enters AND leaves a closure through different ports/cores — these are distinct physical connections, not stored duplicates. So traversal must walk edges in both directions (the functions carry an explicit is_backward_path / is_upstream flag for exactly this reason).

How a cable’s “ports” work

For point equipment (HTB, FMS, Splitter, ONT, PatchPanel), connection_info.source_port_no references a row in isp_port_info via (parent_entity_type, parent_system_id, port_number). A measured join confirmed 2,450 connection_info edges are backed by an isp_port_info record (the linkage is logical, not FK-enforced).

For cables, the “port” is a fiber strand: att_details_cable_info holds one row per (cable_id, tube_number, core_number, fiber_number) with tube_color, core_color, fiber_usage_status, is_connected, and link_system_id. With 1,010,094 strand rows this is the largest table in the domain. connection_info.source_port_no for a Cable endpoint corresponds to the core/fiber number here. The fn_trg_update_core_port_status trigger flips att_details_cable_info.is_connected / *_status_id when a connection is saved/deleted.

The indoor (ISP) containment hierarchy

Containment is by structure_id / floor_id / shaft_id integer columns and the generic parent_system_id + parent_entity_type pattern — again, no FK constraints (only the att_details_bld_structure.building_id → att_details_building.system_id FK exists):

att_details_building (142,673)                 -- OSP footprint / address
  └─ att_details_bld_structure (142,467)       -- FK building_id; no_of_floor/shaft/flat, home_pass/business_pass
       ├─ isp_floor_info (142,665)             -- structure_id; no_of_units
       │    └─ isp_room_info / UNIT (77)        -- floor_id, structure_id; unit/flat
       ├─ isp_shaft_info (176,544)             -- structure_id; vertical riser (with_riser, shaft_position)
       ├─ isp_fdb_info (486)                   -- structure_id, floor_id, shaft_id, building_id
       └─ isp_htb_info (183)                   -- structure_id, floor_id, shaft_id, building_id
            └─ isp_port_info  (port_number, input_output, port_status)  -- parent_entity_type='HTB'/'FMS'/...

So a building has structures; each structure has floors and shafts; floors have rooms/units; and FDB/HTB termination boxes are placed by structure/floor/shaft. The boxes own ports in isp_port_info, and those ports become endpoints in connection_info.

Splice closures, trays, patch panels (OSP + ISP joints)

att_details_spliceclosure (2,847) is the OSP joint; att_details_splice_tray (12, parent_system_id→closure, tray_number, no_of_ports) sits inside. connection_info carries source_tray_system_id / destination_tray_system_id so a splice records which tray it lives in (21,408 of 26,325 edges reference a tray). Patch panels (no_of_input_port/no_of_output_port) and patch cords (A/B endpoints stored inline as a_*/b_* columns AND mirrored as connection_info rows) round out the splicer set.

`att_details_fiber_link` — the logical-path layer

This is a higher-level abstraction: a named end-to-end fiber link with start_point_*/end_point_* equipment+port, total_route_length, otdr_distance, main_link_id/redundant_link_id. It is derived documentation over the physical connection_info splices rather than the source of truth (only 118 rows, no PK).

What data is actually held (measured distributions)

isp_port_info (~301,300 rows):

port_type: GEO 291,821 (97%); ODFPORT 6,630; OpticalPort 2,708; OpticalPort-4 96; PowerPort 36; port 16.
parent_entity_type: HTB 204,576; FMS 71,672; Splitter 12,874; equipment 9,486; PatchPanel 1,440; ONT 1,181; CX 52; GX 2.
input_output: O 156,658 / I 144,649 (cleanly split — every box has in & out ports).
port_status: Vacant 301,286 vs Connected only 21. The inline destination columns (destination_system_id/destination_entity_type) are populated on just 21 rows (all → Customer). The inline destination on isp_port_info is essentially dead — real connectivity lives in connection_info.
Ports-per-parent: HTB averages ~1,013 ports per box (202 HTBs, 204,576 ports — these are very large termination frames); FMS ~127; Splitter ~28; PatchPanel 24; ONT ~6.6.

connection_info (26,325 rows):

Top source→dest pairs: Cable→SpliceClosure 6,959; SpliceClosure→Cable 6,840; Splitter→Splitter 4,304; FMS→FMS 1,957; FMS→Cable 1,696; BDB→Cable 825; Cable→FDB 437; ONT→ONT 264; Equipment→Equipment 217; PatchCord↔Equipment 105 each; Cable→CDB 85.
splicing_source (provenance): OSP SPLIT 7,032; OSP_SPLICING 6,716; Splice All 6,058; Junk-Migration 3,459; PROVISIONNING 1,434; EQUIPMENT_SPLICING 465; Mobile Splicing 405; ISP_SPLICING 402; Bulk Splicing 71.
is_through_connection: 7,148 true / 19,177 false (through-connections = pass-through splices, not terminations).
is_deleted: all 26,325 are false (soft-delete flag exists but unused here).
Graph size: 5,934 distinct source nodes across the 26,325 edges — a sparse, mostly-tree-shaped graph.

att_details_cable_info (1,010,094 rows): in this QA snapshot every strand is fiber_usage_status='Dark' and is_connected=false — i.e. the strand inventory is fully provisioned but (in QA) nothing is lit end-to-end. The connectivity that does exist is recorded at the equipment/closure level in connection_info.

Splicer entities (from layer_details flags): is_isp_splicer=true for ADB, BDB, CDB, CX, FDB, FMS, GX, HTB, ONT, OpticalRepeater, SpliceClosure, PatchPanel. is_virtual_port_allowed=true for ADB, BDB, CDB, FDB, SpliceClosure (and many OSP layers) — meaning these allow ports to be created on demand rather than from a fixed model. is_middleware_entity=true only for FMS.

Business logic (stored functions)

The DB is the source of truth and the business logic lives in PL/pgSQL. Connectivity logic falls into three groups.

1. Writing splices — `fn_splicing_save_connections` (405 lines)

Accepts a JSON p_connection payload, explodes it into temp_connection / temp_connections work-tables, validates, and inserts rows into connection_info. It resolves entity sub-types, handles cable A/B-end orientation (is_source_cable_a_end), tray assignment, and “through” connections. fn_splicing_insert_into_connection_info (343, runs as a trigger-style helper) performs the actual insert and (via fn_trg_update_core_port_status) flips the corresponding att_details_cable_info strand status and isp_port_info port status. fn_validate_splicing (400) enforces splicing rules (port direction I/O matching, capacity, model rules). OSP/ISP cable-split splices are handled by fn_splicing_save_osp_split_connection / fn_splicing_save_isp_split_connection (when a cable is cut at a new closure, existing splices are re-pointed).

2. Reading a path — `fn_get_schematic_view` (478 lines) + `fn_getchildren_connection_info_path`

This is the core traversal engine, and it tells the rewrite a lot:

Creates a session CPF_TEMP_RESULT temp table.
Imperatively loops (LOOP ... END LOOP) over connection_info, following edges outward/upstream from the seed (p_entity_system_id, p_entity_port_no, p_entity_type), resolving display names, port text (fn_get_port_text), via-entities (e.g. patch cords are “via” hops), color codes, and cable core info, pushing each hop as a row with a parent_connection_id.
Joins each hop to point_master + layer_details to fetch no_of_ports, entity_category, and the is_virtual_port_allowed rule.
Spills the result into temp_path_result, then runs a final WITH RECURSIVE mpath CTE in fn_getchildren_connection_info_path that walks parent_connection_id = connection_id to build the ordered path array and emit JSON for the schematic/tree UI.

So the “graph query” is half imperative cursor-walk, half recursive CTE over a materialized temp table — not a native graph query, and not a single recursive CTE over connection_info directly. The same shape appears in fn_get_connection_info_new, fn_otdr_get_fiber_cut_details (OTDR fault distance → which strand/closure), and fn_api_get_fault_location_detail.

3. Auto-generating splices from geometry — `fn_fat_generate_splicing` (2,895 lines)

The largest function in the domain (with 4 dated backup copies still in the DB). Given a FAT/FSA service area, it:

Inserts all cables that spatially ST_INTERSECTS the FSA into a temp table (with start/end-point buffers via ST_BUFFER_METERS).
Uses a WITH RECURSIVE spatial trace to follow cables that snap (within buffer radius) to the feeding FDP/closure, computing each cable’s near-end (AEND/BEND) by ST_DISTANCE.
Auto-generates the splice rows into fat_connection_info (then promoted to connection_info, tagged fat_process_id / splicing_source).

This shows connectivity is partly derived from geometry (spatial proximity of cable endpoints to closures) rather than purely hand-entered — important context for the rewrite’s topology engine.

fn_splicing_isp_model_get_connection (188) applies the isp_model_* rules (parent/child model-id mapping in isp_model_mapping/isp_model_rules) to decide which indoor-box ports legally connect — the indoor analogue of the geometry-driven OSP logic.

Design choices & trade-offs

Connectivity as an explicit edge table — good bones. connection_info is a clean directed edge list (source endpoint, destination endpoint). This is the single best-designed thing in the domain and is the closest existing analogue to a graph-DB edge. It is genuinely an explicit graph, not connectivity implied via shared network_id.
Heterogeneous endpoints via (entity_type, system_id, port_no) discriminator. A connection can join a cable strand, a splitter port, an FMS port, a closure tray, a patch cord, etc., without a table per pair. Flexible — but the polymorphism is enforced only in application/function code.
Strand inventory pre-materialized (att_details_cable_info, 1M rows). Every fiber of every cable exists as a row up-front, so port availability is a simple lookup — at the cost of a million rows mostly sitting “Dark”.
Config-not-code for indoor equipment via isp_model_* (port layouts, parent/child rules) and layer_details capability flags (is_isp_splicer, is_virtual_port_allowed, is_middleware_entity). Behaviour is data-driven.
Traversal is procedural, not declarative. Path queries are imperative PL/pgSQL cursor-walks that materialize temp tables and finish with a recursive CTE — chosen presumably for fine control over display/ordering/via-hops, but it is slow and hard to reason about.

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

No referential integrity on the graph. connection_info has zero FKs; endpoint integrity (source_system_id actually existing, port in range, direction valid) is enforced only by functions. fk_graph.tsv confirms the only FK in the whole domain is att_details_bld_structure.building_id. Orphaned edges are possible and not prevented by the DB.
Duplicated/dead inline connectivity. isp_port_info carries destination_system_id/destination_network_id/destination_entity_type columns, but only 21 of ~301,300 rows use them — a second, abandoned representation of connectivity that disagrees with connection_info. Same pattern in att_details_patchcord (A/B endpoints stored inline and as connection_info rows). Two sources of truth.
Stringly-typed everything. entity_type, port_type, port_status, splicing_source are free varchar. Real data shows casing/spelling drift: Equipment vs EQUIPMENT, PatchPanel vs PATCHPANEL, SpliceClosure vs spliceclosure, equipment (lowercase) in isp_port_info. Every traversal function is littered with UPPER(...) to paper over this.
Code-as-data sprawl / backups in the schema. fn_fat_generate_splicing has 4 dated backup copies (_bk9thmay2023, _bkp252023, _bkp26april2023, _bkp5thmay23) totalling ~11k lines of near-duplicate logic; fn_splicing_save_connections has 4 dated backups; fn_validate_splicing, fn_get_schematic_view exist in 3–6 numbered/_test/_bkup variants. Impossible to know which is canonical without reading the app.
Procedural traversal won’t scale. The schematic walk builds session temp tables and loops per-hop; OTDR/fault and BOM reports do the same. On a denser production graph this is a performance and concurrency liability (temp-table churn, no shared traversal index).
Provenance reveals data-quality debt. splicing_source = 'Junk-Migration' (3,459 rows, 13% of all edges) is explicitly labelled junk migrated data still living in the connectivity table.
Mixed physical/logical layers. att_details_fiber_link (logical end-to-end link) and connection_info (physical splice) are not formally tied (no FK; link_system_id is a loose pointer on att_details_cable_info), so the logical service path can silently drift from the physical splices.
“Direction” is overloaded. Connectivity correctness depends on three separate booleans interpreted by code: input_output (I/O on the port), is_cable_a_end (which end of a cable), and is_backward_path/is_upstream (traversal direction). Easy to get wrong.

Implications for the new platform

Keep (the model is graph-shaped already):

The explicit edge concept of connection_info maps almost 1:1 onto a property-graph edge or a topology_edge table. Source/destination port endpoints become typed graph endpoints. This is the cleanest migration target in the whole DB and validates the graph-engine direction.
Strand-level granularity (att_details_cable_info: tube/core/fiber) is the right resolution for fiber tracing — the new model needs a first-class “fiber strand” / “port” node so a splice connects strands, not just boxes.
The indoor containment hierarchy (building → structure → floor/shaft → room/unit → FDB/HTB → port) is sound; model it as containment edges, not 95-column wide tables.
Config-driven equipment models (isp_model_* port layouts + connection rules) are worth carrying forward as a typed component/port-template system.

Redesign:

Replace the procedural traversal (temp-table cursor-walk + final recursive CTE in fn_get_schematic_view/fn_getchildren_connection_info_path) with native graph traversal — either pgRouting/recursive CTEs directly over a proper (node, edge) schema with indexes, or a graph DB. The current approach is the strongest argument for the rewrite’s graph engine.
Enforce integrity: endpoints must be real FKs (or graph-native node references); port direction and capacity must be constraints, not function-only checks.
Collapse to one source of truth: drop the dead inline destination_* columns on isp_port_info and the redundant A/B columns on patch cords; let the edge table be authoritative.
Strong typing: entity_type/port_type/port_status/splicing_source become enums/reference tables; eliminate the pervasive UPPER() casing workarounds.
Separate physical splices from logical links explicitly (the att_details_fiber_link concept) with a real relationship, and keep provenance (splicing_source, fat_process_id) as edge metadata so geometry-derived vs manual splices remain distinguishable.
Decide how geometry feeds topology: fn_fat_generate_splicing derives splices from spatial proximity. The new platform should make this an explicit, re-runnable “infer topology from geometry” step rather than a 2,895-line stored procedure with four backup copies.

Bottom line on the central question

Connectivity is stored as an explicit edge graph in connection_info (directed, single-row-per-connection, composite logical endpoints), supplemented by a 1M-row fiber-strand inventory for sub-port granularity. It is not implied via shared network_id (that hierarchy is for containment/scoping, not connectivity), and the inline destination_*/a_*/b_* columns on port and patch-cord tables are a vestigial second representation that is effectively unused. The graph model therefore maps cleanly to the new platform — the real work is enforcing integrity, strong-typing the endpoints, and replacing procedural traversal with native graph queries.