The Attribute & Template Model

How SmartInventory describes what a network asset is: a wide column-per-attribute table per entity type, a reusable equipment-spec template library, and two generations of dynamic/EAV “escape hatch” for fields the schema never anticipated.

Scope

This document covers the three layers that together hold every non-geometric attribute of a network asset. Geometry/identity lives in the three masters (point_master/line_master/polygon_master, keyed by system_id); these tables hang off system_id.

Layer	Tables	Count	Real row volume
Wide per-entity attribute tables	att_details_* (+ isp_*_info for ISP)	93 att_details_*	~1.16M rows (pole 183k, building 143k, cable 95k, duct 94k, manhole 84k, trench 76k dominate; long tail of ~70 tables at <1000 rows or empty)
Template (equipment-spec) library	item_template_* (39), isp_template_* (4), item_template_master	43 template tables + audit/non_design_*/temp_* variants	item_template_master 230, item_template_* <250 each — small reference libraries
Dynamic / EAV overlay	dynamic_controls (+ dropdown master), osp_attribute_def/_value/_dependency, equipment_model_attribute_def, 54 *_attribute_value tables, 120 other_info jsonb columns	—	Mostly scaffolded but unused: osp_attribute_def 17, osp_attribute_value 7, dynamic_controls 29 (test data), entity_additional_attributes 464

Key functions: fn_get_template_detail (465 lines), fn_getelementtemplate, fn_get_template_column_list, fn_user_rights_get_templates, fn_get_gis_attribute (206), fn_get_entity_advance_attribute (117), fn_create_dynamic_entity_wide_view (138), fn_get_osp_dynamic_info, fn_get_equipment_dynamic_info, fn_refresh_dynamic_view_on_attr_def (trigger).

The whole model is wired together by the layer_details registry (66 rows): each entity type row names its layer_table, its layer_template_table, its dynamic_attribute_table, and carries the flags is_template_required, is_dynamic_control_enable, is_dynamic_enabled, is_dynamic_attribute_enable.

---

Data model & relationships

1. The wide table: att_details_<entity>

Each entity type gets its own physical table whose columns are the domain attributes. They are joined 1:1 to the geometry/identity masters on system_id (e.g. point_master.system_id = att_details_pole.system_id). There is no shared base table and no inheritance — it is literal table-per-class with copy-paste columns.

Width varies wildly: att_details_cable 152 cols, att_details_duct 123, att_details_trench 118, att_details_building 107, att_details_antenna 104, att_details_manhole/att_details_spliceclosure 101, att_details_pole 91, att_details_splitter 98, att_details_pod 94. (Source: table_inventory.tsv col count.)

A large shared “spine” is duplicated into every table. Counting column names across the 93 tables (columns.tsv), the most common columns are:

Column(s)	Appears in N of 93 tables	Role
created_on, created_by, modified_on, modified_by	79–84	audit metadata (also in masters)
system_id	76	PK / join key to masters
parent_system_id, parent_network_id, parent_entity_type	63	self-referential network hierarchy
status, network_status	57–62	lifecycle/approval state
network_id, province_id, region_id	61	identity + scoping
target_ref_id/code/description, origin_from/ref_*, request_ref_id/requested_by/approved_by, codification_sequence, gis_design_id, sequence_id	58–60	provenance / external-system codification (RJio/operator integration)
area_system_id, csa_system_id, dsa_system_id, subarea_system_id	58	planning-polygon containment denormalised onto every entity
served_by_ring, prms_id, jc_id, mzone_id	53–56	operator network-identifier fields
vendor_id, type, brand, model, construction, activation, accessibility, specification, category, subcategory1..3, item_code	most physical entities	equipment classification — the exact columns the template library fills
other_info (jsonb)	34 att_details + 33 audit	the jsonb escape hatch (see §3)

So roughly half of a “91-column” pole table is boilerplate; only the entity-specific tail (pole_height, pole_no, pole_type, primary/secondary_pod_system_id, elevation, barcode…) is genuinely about poles. att_details_cable adds optical-physics columns (total_core, no_of_tube, wavelength, attenuation_db, chromaticdispersion, a_system_id/b_system_id endpoints); att_details_building adds occupancy/coverage columns (home_pass, no_of_flat, rfs_status, tenancy, building_height).

ISP entities break the naming convention: FDB/HTB/OpticalRepeater/Room store attributes in isp_fdb_info, isp_htb_info, etc. (the layer_table pointer accommodates this), proving the att_details_ prefix is convention, not contract.

2. The template library: item_template_<entity> + item_template_master

A template is a saved equipment-spec definition — a reusable “catalogue item” (a specific vendor’s 8-metre wooden pole, a 96-core underground cable) that pre-fills the classification attributes when a planner places a new entity, so they don’t retype vendor/model/category every time.

• item_template_<entity> mirrors a subset of the matching att_details_<entity> classification columns. item_template_pole (21 cols) = specification, category, subcategory1..3, item_code, vendor_id, type, brand, model, construction, activation, accessibility, pole_type + audit. item_template_cable (27 cols) adds cable_type, total_core, no_of_tube, no_of_core_per_tube, cable_category. These are exactly the spine equipment-columns from §1.
• item_template_master (230 rows, 46 cols) is the cross-entity master catalogue / BOM dictionary: category_reference, subcategory_1..3, specification, code, vendor_id, no_of_input_port, no_of_output_port, no_of_port, cost_per_unit, currency_type, unit_measurement, is_brfs/crfs/arfs (RFS lifecycle flags), cable_length, cable_type, length/width/height, is_master, is_default. Per-entity templates join back to it on specification = code = vendor_id.
• created_by = 0 marks global/system templates; non-zero is a user’s personal template. fn_get_template_detail unions both and prefers the user’s (ORDER BY created_by DESC LIMIT 1).
• Real data is operator-specific: item_template_pole rows are “Wooden Pole 8 Meters” / “3rd Party Pole”, vendors Safaricom / Electrical — i.e. a Safaricom (Kenya) catalogue, distinct from the RJio bulk data, confirming this QA DB is multi-customer.
• layer_details.layer_template_table points each entity to its template table (38 of 66 layers); is_template_required is TRUE for 43 layers; template_form_url gives the UI form. Every template table has a parallel audit_item_template_* shadow + fn_trg_audit_item_template_* trigger.

3. The dynamic / EAV overlay (three generations)

There are three independent mechanisms for attributes the fixed schema never anticipated. layer_details has a separate flag for each:

(a) other_info jsonb (oldest, ubiquitous). 120 jsonb columns total; 34 are att_details_*.other_info (+ 33 audit mirrors, + isp_*_info.other_info, + dynamic_controls.other_info). This is a per-row key/value bag. Measured fill (live, sampled): att_details_pole 181,960 / 182,876 rows non-empty (99.5%), att_details_cable ~17.6k, but att_details_building only 1 and att_details_duct 343 — so usage is per-entity, not systemic. Inspecting keys: pole’s other_info mostly holds record_system_id (an internal pointer) plus dynamic-control field values (poleisright, polenois, testt1_test); cable’s holds bulk-import staging metadata (queue_id, process_id, num_fibre, layer_name) and shadow copies of real columns (cable_type, remarks, created_on) — i.e. it doubles as an ETL scratchpad, not a clean custom-field store.

(b) dynamic_controls + dynamic_controls_dropdown_master (runtime custom-field UI, gen 2). dynamic_controls (29 rows) defines extra form fields per layer: entity_id (= layer_id), field_label/field_name, control_type (TEXT/DROPDOWN), control_value_type (float/integer/alphanumeric/character), is_mandatory, is_visible, min/max_length, default_value, field_order, control_css_class. Dropdown options live in dynamic_controls_dropdown_master (display_text/value_text/is_default). The entered values are written into the entity’s other_info jsonb under field_name (confirmed: pole’s poleisright/testt1_test keys match dynamic_controls rows for entity_id 14). So gen-2 is a render layer over gen-1 storage. layer_details.is_dynamic_control_enable = TRUE for 29 of 66 layers. Current content (testt1_test, wrong, poleisright) is clearly developer test data, not production config.

(c) osp_attribute_def / osp_attribute_value / osp_attribute_dependency — a proper typed EAV (gen 3, new, unrolled-out). This is the textbook EAV:

• osp_attribute_def (17 rows) = attribute metadata: entity_type, attribute_key, display_name, data_type (TEXT/NUMBER/DATE/BOOLEAN/DROPDOWN), is_required/visible/system/readonly/active, display_order, default_value, dropdown_options (jsonb), min/max_value, group_name, system_column (the bridge — if set, the attribute is backed by a real wide-table column; if NULL it is a pure dynamic attribute).
• osp_attribute_value (7 rows) = typed values keyed by (entity_system_id, attribute_def_id) with separate value_text/value_number/value_date/value_boolean columns.
• osp_attribute_dependency (0 rows) = cascading-dropdown rules (parent_def_id → child_def_id, dependency_type, cascade_source).
• equipment_model_attribute_def/_value/_section_config/_dependency = the identical pattern scoped to equipment models (model_type_id) rather than entity types — i.e. EAV attributes on the template/catalogue side too.
• 54 per-entity *_attribute_value tables exist (pole_attribute_value, cable_attribute_value, …, each 11 cols mirroring osp_attribute_value) — the framework is scaffolded for every layer, but only osp_attribute_value (7) and equipment_attribute_value (15) hold any data; the rest are empty. layer_details.is_dynamic_attribute_enable = TRUE for only 8 of 66 layers, with dynamic_attribute_table naming the value table.
• osp_attribute_def content reveals intent: Pole.pole_type has system_column='pole_type' (mapped to the real column), Pole.latitude is is_system=true → read from the wide table; but Pole.pole_test, Pole.Deepak_1/2/3 have system_column=NULL → pure dynamic. The Deepak_* keys betray it as in-development.

---

What data is actually held (measured)

• Volume is concentrated in ~6 entity types. Of the 1.16M att_details_* rows, pole (183k) + building (143k) + cable (95k) + duct (94k) + manhole (84k) + trench (76k) ≈ 580k+; ~70 of the 93 tables are under 1,000 rows or empty (att_details_dp, att_details_gx, att_details_microwavelink, att_details_site*, etc.). The schema provisions for far more entity diversity than this dataset uses.
• Stringly-typed categoricals. att_details_pole.status: A (active) dominates, D (deleted) rare; network_status: A vs P (planned). att_details_cable.cable_type: top values Underground (≫), Aerial (Structured), Overhead, ISP, Wall Clamped — with case variants (Underground vs underground) and nulls, i.e. no enum/lookup enforcement.
• Classification columns (vendor_id, type, brand, model) are integers referencing lookup masters but with no FK (only 84 FKs exist DB-wide).
• other_info is dual-purpose: ~99% of poles carry it, but largely for an internal record_system_id and ETL/import staging, not curated custom data.
• The EAV (gen 3) is essentially empty in production — it is a recently-built redesign, not a live feature.

---

Business logic (key stored functions)

• fn_get_template_detail(p_userid, p_entitytype, p_sub_entitytype) — 465 lines, SETOF json. A giant IF/ELSIF over every entity type; each branch selects the matching item_template_<entity>, LEFT JOIN item_template_master b ON a.specification=b.specification AND a.item_code=b.code AND a.vendor_id=b.vendor_id, filtering created_by = p_userid (and unioning created_by=0 globals), ORDER BY created_by DESC LIMIT 1. This is how the UI pre-fills a placement form from a template. Inline comments mention per-entity “migrations” (CX/GX branches), confirming it is hand-edited per entity addition.
• fn_getelementtemplate(elementtype, userid) — 275 lines; returns template geometry/dimensions (element_height/width/length), template_form_url, istemplatefilled, entity_category/class — drives whether the template form is shown and whether it’s been completed.
• fn_get_gis_attribute(p_system_id, p_entity_type) — 206 lines. Hard-coded crosstab per entity type (AREA/SUBAREA/DSA/CSA branches build TEMP TABLE temp_gis_*_attribute with RJio-specific columns: PRMS_ID, JC_SAP_ID, R4G_STATE_CODE, JPF_ID…) then FN_GET_CROSS_TAB_QUERY pivots to (db_column_name, db_column_value) pairs for the info panel. RJio business rules baked into SQL.
• fn_get_entity_advance_attribute(p_systemid, p_entitytype, p_geomtype) — 117 lines; per-entity IF branches that join ISP topology (isp_entity_mapping, isp_floor_info, isp_shaft_info, att_details_bld_structure, att_details_building) to roll up context (building/structure/floor/shaft names, primary/secondary splitter counts) for BDB/FDB/ONT/FMS.
• fn_create_dynamic_entity_wide_view(p_entity_type) — 138 lines, the gen-3 EAV engine. Reads layer_details for layer_table + dynamic_attribute_table (requires is_dynamic_attribute_enable=TRUE), then dynamically generates CREATE VIEW vw_att_details_<entity>_report that (a) selects the system_column-backed attributes straight from the wide table and (b) pivots the EAV *_attribute_value rows via MAX(CASE WHEN attribute_key=… THEN value_text/number/date/boolean END), choosing the value column by data_type. This re-flattens EAV back into a wide view on demand.
• fn_refresh_dynamic_view_on_attr_def() — trigger on osp_attribute_def (AFTER INSERT/UPDATE/DELETE) that calls the above to rebuild the dynamic view whenever an attribute definition changes (DDL-on-data-change).
• fn_get_osp_dynamic_info / fn_get_equipment_dynamic_info — return (display_column_name, updated_value, is_updated) triples by LEFT JOIN osp_attribute_def → osp_attribute_value (filtered is_system=false, is_visible=true, is_active=true), coalescing the four typed value columns into one text column for display.

---

Design choices & trade-offs

The triad is a deliberate “fixed core + escape hatch” strategy. Curated, queryable, indexable attributes live in wide columns; the rare/operator-specific ones go into the dynamic layers. The layer_details registry + system_column bridge in osp_attribute_def mean the same logical attribute can be promoted from EAV to a real column without breaking the read path — a genuinely thoughtful migration design (gen 3).

• Wide table-per-entity gives strong typing, native SQL filtering/sorting, real indexes, and obvious column semantics — fast for the 6 high-volume entities. But it means schema rigidity: every new attribute is an ALTER TABLE, every new entity type is a brand-new table + audit shadow + trigger + a hand-edited branch in fn_get_template_detail/fn_get_gis_attribute/etc. Columns are duplicated across 93 tables (the ~60-column spine), so a change to, say, the codification fields is 93 ALTERs.
• Sparse columns: tables like att_details_cable (152 cols) are wide because they accumulate every customer’s needs; for any single row most of the optical/loop/splitting columns are NULL.
• Templates are a clean, useful pattern (DRY equipment specs, BOM costing via item_template_master) — but implemented as another per-entity table family with the same copy-paste classification columns and the same 465-line dispatch function.
• Three overlapping dynamic mechanisms are the cost of evolution: jsonb bag (gen 1) → dynamic_controls form-builder writing into that bag (gen 2) → typed EAV with per-entity value tables and view regeneration (gen 3). They coexist; only gen 1 has production data, gen 3 is nearly empty.

---

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

1. Massive logic duplication in dispatch functions. fn_get_template_detail (465 lines), fn_get_gis_attribute (206), fn_get_entity_advance_attribute (117) are giant per-entity IF ladders. Adding an entity means editing several functions by hand (the CX/GX comments prove this is the real workflow). High defect surface, no abstraction.
2. No referential integrity on classification. vendor_id/type/brand/model are bare integers; template↔master joins are on (specification, item_code, vendor_id) string triples, not keys — brittle and case-sensitive.
3. Unenforced categoricals. cable_type shows Underground vs underground; dropdown_options live in jsonb, not constraints. Data quality depends entirely on app discipline.
4. other_info is overloaded — simultaneously custom-field store, dynamic-control value store, and ETL staging scratchpad (queue_id, process_id), with shadow copies of real columns (cable_type, remarks duplicated in jsonb). Source-of-truth ambiguity.
5. entity_additional_attributes (464 rows) is mislabelled EAV — it is a fixed wide table of RJio hub-maintenance fields (hm_power_bb, hm_olt_bb, splicing_machine, otdr…), not generic. Another bespoke per-need table.
6. Three competing dynamic systems, none retired. Maintenance burden + confusion about where a custom field actually lives.
7. DDL-on-data trigger: fn_refresh_dynamic_view_on_attr_def runs DROP VIEW / CREATE VIEW on every osp_attribute_def write — a data-plane operation issuing DDL, with locking and failure-mode risk if attribute defs are edited under load.
8. Test/dev artefacts in a QA DB: dynamic_controls (“testt1_test”, “wrong”, “poleisright”), osp_attribute_def (“Deepak_1/2/3”, “pole_test”) — features being built, not configured.
9. 63% of these tables have no PK (DB-wide finding); att_details_* rely on system_id uniqueness by convention.

---

Implications for the new platform

Keep the intent, replace the implementation.

• Adopt a single, principled attribute model. A small, strongly-typed core of columns shared by all assets (identity, lifecycle/status, audit, hierarchy, classification FKs) + a typed-EAV or jsonb “custom attributes” layer for the long tail — but exactly one mechanism, not three. The gen-3 osp_attribute_def/_value design (with a system_column promotion bridge and a metadata-driven view generator) is the right idea and the best starting point; productionise it instead of the jsonb bag.
• Replace table-per-entity with one (or few) polymorphic asset tables + a entity_type/asset_class discriminator and a proper attribute schema registry (the modern equivalent of layer_details + osp_attribute_def). This kills the 93×ALTER problem, the audit-shadow-per-table sprawl, and the giant IF-ladder functions.
• Enforce integrity: real FKs to vendor/model/category lookups; CHECK/enum or lookup-FK on status and *_type; PKs everywhere; UUIDs over serial integers.
• Keep templates as a first-class concept (equipment-spec catalogue + BOM costing is genuinely valuable) but model them as rows in a generic equipment_model/catalogue_item table with their own attribute set, not 43 parallel tables.
• Move per-entity dispatch logic out of monolithic SQL functions into a metadata-driven service (one code path parameterised by entity type), so adding an asset type is configuration, not a function edit.
• Separate ETL/import staging from the attribute store — never reuse other_info as a scratchpad.
• Decouple SRID/geometry from attributes (already separate via masters) and avoid DDL-emitting triggers on the hot path.