Skip to content
Vinxi/Kernel THE BOOK OF RECORD

SmartInventory Database — Data Model & Design Study (Overview)

Source: live smartinventory_qa (PostgreSQL 14.8, ~8.8 GB), analyzed read-only on 2026-06-16. This is the real source of truth — business logic lives in PostgreSQL stored functions, not the .NET app. Full DDL + 2,210 function bodies are dumped (gitignored) to references/db-analysis/raw/schema_full.sql; structured inventories alongside it.

This overview captures the macro architecture and cross-cutting design choices. The eight numbered companion docs in this folder drill into each domain with evidence.

1. What this database is

A digital twin of FTTx / fiber-optic networks (outside-plant + inside-plant), as built/surveyed for telecom operators. The instance is a multi-customer QA mashup: the network geometry carries Reliance Jio “circle” codes (circle_rjid, temp_rjio_*), the equipment/template catalogue is Safaricom (Kenya), and a BharatNet deployment is exposed inside the same DB via a postgres_fdw server (1,734 foreign tables — see 08). “Circle” = the 22 Indian telecom (TRAI) service areas. It holds a substantial real network:

Geometry classMaster tableRowsDominant entity types
Pointspoint_master424kPole 183k · Structure 142k · Manhole 84k · POD 6k · SpliceClosure 2.9k · Cabinet 1.4k · distribution boxes (ADB/BDB/CDB/FDB) · ONT/Splitter/Customer
Linesline_master257kCable 95k · Duct 94k · Trench 76k · PatchCord · Microduct
Polygonspolygon_master143kBuilding 142k · planning areas (Area/SubArea/CSA/DSA/SurveyArea/ROW) · Sector

The shape (huge passive infra: poles, ducts, structures, buildings; small active/service counts) indicates a network-build / survey dataset more than a live-subscriber one.

2. Object inventory (scale)

ObjectCountNotes
Schemas (user)6public is a monolith; tiger/topology/tiger_data are PostGIS; source_schema empty (clone template); APP_LCO (18 tbls) = a SAP-integrated material/contract/vendor module (MTO/MIN/CO), not “cable-operator” tenancy
Base tables1,04925,910 columns total (~24.7/table)
Views513–517incl. vw_*_map (map-serving) + report/history views
Functions3,428 total / 2,210 user-definedthe real business logic
Triggers300on 148 tables (mostly audit)
FK constraints84i.e. integrity essentially not enforced
Indexes1,090only 35 GIST (spatial), 0 GIN

Extensions: PostGIS 3.2.2 (+topology, sfcgal, tiger geocoder, address_standardizer), pgRouting 3.3.1, hstore, pgcrypto, postgres_fdw (cross-DB federation), tablefunc (crosstab), cube, earthdistance, fuzzystrmatch, pg_stat_statements. No Apache AGE — graph is net-new for the rewrite.

3. The three structural keystones

(a) Geometry/attribute decoupling. Every spatial feature is a row in one of three master tables (point_master / line_master / polygon_master), keyed by integer system_id, with an entity_type varchar discriminator, the sp_geometry column (SRID 4326), and a baked-in approval workflow + audit envelope (approval_flag, approval_date, approver_id, creator_remark, approver_remark, created_by/on, modified_by/on, network_status, status, db_flag, source_ref_id/type). Domain attributes live in a parallel att_details_<entity> table joined 1:1 by system_id. (See 01 and 02.)

(b) layer_details — the schema-driven entity registry. 66 rows (= 66 layers/entity types) × 114 columns, of which ~80 are boolean capability flags (is_osp_layer, is_isp_layer, is_splicer, is_cpe_entity, is_barcode_enabled, is_template_required, is_data_upload_enabled, is_history_enabled, is_feasibility_layer, …) plus config pointers (layer_table, layer_view, layer_template_table, report_view_name, history_view_name) and UI-driving URLs (layer_form_url, template_form_url, save_entity_url). This is “config-not-code” implemented as an 80-flag wide row per entity — the direct ancestor of the new platform’s schema-driven model. (See 01.)

(c) The network_id / parent_network_id hierarchy spine. network_id appears in 207 tables, parent_network_id in 190. Containment/scoping/codification is expressed through this self-referential network-code hierarchy (plus “circle”), not a tenant_id. Two crucial nuances the deep-dives established: (i) containment is computed from geometry at save-time (fn_get_parent_info via ST_WITHIN), not stored as edges — fragile and non-deterministic (see 01); and (ii) physical connectivity is a separate, explicit edge table — connection_info (26k port-to-port edges), not derived from network_id (see 04). So this one DB conflates three different relationship types — containment (spatial), connectivity (edge table), and tenancy/scoping (string codes + a postgres_fdw link to a second customer’s DB).

4. Cross-cutting design choices & trade-offs (the verdict)

  1. Stringly-typed, integrity-light schema. 49% of all 25,910 columns are varchar; only 84 FKs and 63% of tables lack a primary key; only 3 uuid columns (everything is integer/serial keyed). Relationships are by convention (system_id, network_id) and enforced in stored procs, not constraints. → Data-quality drift, orphan rows, and no DB-level guarantees. The rewrite should restore typed columns, real FKs, and (for distributed/multi-tenant) UUID/ULID keys.
  2. Per-entity table sprawl. 93 att_details_* + 68 audit_att_* + 39 item_template_* + 43 temp_du_* + isp/temp/csv families. Adding an attribute = ALTER TABLE (and its audit twin, and its CSV-staging twin). → The single biggest maintenance tax; the forks confirm customers rebuilt parts of this. The rewrite’s dynamic-entity/attribute engine targets exactly this.
  3. Trigger-based shadow-table auditing. 68 audit_* tables populated by 300 triggers / 207 fn_trg_* cause heavy write-amplification (e.g. audit_att_details_cable 590k×125 vs base 95k) and force every schema change to be mirrored. (See 03.)
  4. Logic-in-the-database. 2,210 stored functions hold the domain logic (codification, splicing, auto-planning, uploaders, validation, the fn_api_* surface). Portable and close to data, but untested, unversioned outside the live DB, hard to refactor, and stringly-parameterized. (See 08 for the catalog.)
  5. GIS correctness gaps. Only 35 GIST indexes for 802 geometry columns, and 579 geometry columns at SRID 0 (no spatial reference) — spatial queries on most columns are unindexed/seq-scan and reprojection-unsafe. Core masters do use SRID 4326. (See 01.)
  6. Bespoke ingestion. 92 fn_uploader_* + 27 fn_bulk_* + a staging table per entity (temp_*_csv, temp_du_*) — no generic importer. (See 06.)
  7. pgRouting is present but barely used. Real pgr_* usage is a single pgr_dijkstra shortest-path (fn_sf_get_routes) over a small (≤3,900-edge), manually-rebuilt cable snapshot — no max-flow, k-shortest, or TSP. → Concrete evidence the topology engine can be pgRouting + recursive CTEs first, with a graph store added only where it pays; current functionality does not justify a graph DB. (See 04, 05.)
  8. Vestigial workflow & stringly-typed everything. The built-in approval workflow is dead (approval_flag='A' on ~100% of rows); money/ratios, entity types, faults, and “technology” are free-text where lookups belong (rampant UPPER() / case-inconsistency). The real lifecycle is network_status (Planned/As-built/Dormant). (See 01, 07.)
  9. The function count is inflated by in-DB backups. Of ~2,105 fn_*, a large share are dated _bkp_<date>/_test copies (the densest function, fn_fat_generate_splicing at 2,895 lines, has 4 backup copies). Editing-in-production with copy-paste versioning is the norm. (See 05, 08.)
  10. No real multi-tenancy or isolation. No tenant key; isolation leans on string network_id/“circle”, per-user geo scope that is effectively disabled (82/95 users = all-province), and a postgres_fdw link that exposes a second customer’s 1,734 tables from this DB. (See 08.)
  11. Pervasive security debt in the data layer. Reversible (AES, not hashed) user passwords; plaintext SAP/AD/OAuth credentials and NMS device passwords in tables; an empty-password API client; SQL injection in the uploader. (See 07, 08 and §8 below.)
  12. Two dead/orphan data paths. A temp_*_csv + temp_pole_accessories (1.1M rows) staging path referenced by zero functions, and isp_port_info’s inline destination_* connectivity columns (used on 21 rows) — both are abandoned alternate representations carried as dead weight. (See 04, 06.)

5. Implications for the new platform (headline)

  • Replace the 3-master + att_details_ + layer_details flags* triad with a typed, schema-driven custom-entity model (entity types + attribute schemas as data, not 80 boolean columns and a table-per-entity).
  • Make connectivity an explicit graph (ports/splices/fibers as edges) rather than implied via shared records + network_id (see 04 for how it’s done today).
  • First-class multi-tenancy (real tenant boundary), typed keys (UUID), enforced integrity, modern audit (temporal/CDC, not shadow tables), and a generic ingestion layer.
  • Keep what works: PostGIS + pgRouting, the codification concept, the approval workflow, the config-driven entity idea (but as schema, not flag columns).

6. Per-domain deep dives (companion docs)

#DocCovers
0101-core-geometry-entity-registry.mdmasters, layer_details registry, network/codification spine, lifecycle/approval, status history, map views
0202-attribute-and-template-model.mdthe 93 att_details_* wide tables, item_template_*, dynamic/EAV (jsonb/hstore) overlay
0303-audit-history-triggers.md68 audit_* tables, *_history, the 300 triggers / fn_trg_*
0404-isp-ports-splicing-connectivity.mdinside-plant hierarchy, isp_port_info, splicing/FAT, the fiber connectivity graph
0505-planning-routing-bom.mdauto network planning, pgRouting usage, BOM/BOQ costing
0606-data-ingestion-staging.mdfn_uploader_*/fn_bulk_*, temp/csv staging sprawl, landbase import
0707-operations-feasibility-wireless.mdWFM/SmartOps, SmartSQ feasibility, fault/maintenance, wireless
0808-platform-security-tenancy-catalog.mdusers/permissions, LCO/tenancy, secrets-in-DB, reference data, function/view catalog

7. Per-domain headline findings (synthesized from 01–08)

  • 01 Core / registry. Masters↔att_details join is clean (zero orphans for Pole/Cable). Containment hierarchy is spatially derived at save-time (ST_WITHIN), not stored. Two code systems coexist: network_id (~100% populated, formatted via layer_mapping.network_code_format) vs gis_design_id (the 499-line fn_auto_codification batch, run on only 7 of 424k rows). Hazards: SRID-0 columns holding 4326 data; point_master.modified_on typed time (date lost); 60 un-indexed GeoJSON cache tables; 6+ duplicate codification functions.
  • 02 Attributes / templates. Triad = 93 wide att_details_* (~60% boilerplate spine, 91–152 cols, volume in 6 entities, ~70 near-empty) + a 230-row template/spec library (vendor/model catalogue, created_by=0=global) + an EAV overlay. Three EAV generations; the clean gen-3 typed EAV (osp_attribute_def/_value + fn_create_dynamic_entity_wide_view) is the rewrite’s target model — already prototyped, left test-only.
  • 03 Audit / triggers. 127 audit_* shadow tables + 300 triggers (~54% audit; rest are manual cascade-delete FK substitutes + label denormalization). Audit tables aren’t supersets — they drop columns and hardcode column lists inside 117 trigger functions → silent schema drift. Biggest audit tables are zero-indexed / no-PK. Change-data ≈ 38% of all rows, ~1.2 GB.
  • 04 ISP / splicing / connectivity. Connectivity is an explicit directed edge table connection_info (26k rows; source/destination = entity_type+system_id+port_no) → graph model maps cleanly. But: zero FKs, stringly-typed endpoints, 13% edges tagged Junk-Migration, a dead inline-port representation, and procedural traversal (fn_get_schematic_view cursor-walk + recursive CTE) that won’t scale. Fiber-strand granularity in att_details_cable_info (1.01M rows).
  • 05 Planning / routing / BOM. Auto-planning is stage-then-commit (temp_auto_network_plan = 4 GB, 400k rows / 186 plans; fn_network_planning_save_auto_planning 531L generates the trench→duct→cable→splice stack). FAT auto-splicer is the densest logic (2,895L). pgRouting shallow (one dijkstra). BOQ = dynamic-SQL over bom_boq_master×layer_details×item_template_master rates. Bug: EPSG:26986 (Massachusetts) projection used for lengths on India data.
  • 06 Ingestion / staging. “du” = data-upload: per-layer all-VARCHAR temp_du_* staging (44) → fn_uploader_validate_parent_details → fn_uploader_insert_<entity>, driven by an 884-row data_uploader_template col-map and an upload_summary ledger (1,174 jobs, 2023→Feb 2026 = actively used). ~45 near-identical insert functions; SQL injection via concatenated network_id; no idempotency/purge (43k stale rows); ~74% Cable reject rate.
  • 07 Operations / feasibility / wireless. Three parallel ticketing subsystems (ticket_master / att_details_networktickets / hpsm_ticket_master) on a module-discriminated state machine (ticket_steps_master + role grid); WFM (tbl_wfm_*) configured but barely used. Feasibility (SmartSQ) is a real pgRouting engine but its available-cores rule counts connected cores as available (questionable). Faults are free-text (only “FIBER CUT” real; 93/95 unresolved). Wireless reuses generic att_details_* and stores plaintext NMS passwords.
  • 08 Platform / security / tenancy / catalog. RBAC = dual-path module gating + per-role×per-layer CRUD + per-user geo scope (scope effectively off: 82/95 = all-province). No tenant key; postgres_fdw exposes a second customer’s 1,734 tables. APP_LCO = SAP material/contract module. Catalog: 2,105 fn_* (fn_get 799 / fn_trg 207 / fn_api 63 …), 509 views (388 vw_att_* = 85 map / 91 audit / 73 report). tbl_covid_* (11) are dead/empty legacy.

8. Security findings in the data layer (compounding the repo-side findings)

These are data-layer issues found live; they add to the repo-side findings (auth backdoors, hardcoded AES key, committed cloud keys). Treat collectively as a live remediation incident separate from the rewrite (rotate, hash, parameterize, remove the cross-customer FDW link, enable MFA):

FindingWhereNote
Reversible user passwordsuser_master.passwordAES+base64, not hashed (lengths ÷4, no hash signature; 27 reused)
Plaintext system credentialsAPP_LCO.APP_SETTINGSSAP / AD / OAuth secrets in plaintext
Plaintext device passwordsatt_details_microwavelinkNMS/EMS login passwords stored as free-text columns
Empty-password API clientapi_consumer_mastera consumer with blank secret
Plaintext OIDC tokenstoken storagestored unencrypted; MFA configured but disabled
SQL injectionuploader (fn_uploader_*)network_id concatenated into dynamic SQL
Cross-customer exposurepostgres_fdw bharatnet_server1,734 foreign tables of a different deployment reachable from this DB

Status: complete. All eight section docs are written and their findings are reflected above. Raw query artifacts + the 23 MB schema dump are in references/db-analysis/ (gitignored).