08 — Platform Plumbing: Identity, Permissions, Tenancy/LCO, Secrets, Reference Data & Catalog Overview

Evidence base: live read-only analysis of smartinventory_qa (PostgreSQL 14.8, ~8.8 GB, largely a Reliance Jio FTTx dataset) plus the pre-dumped artifacts in references/db-analysis/raw/. Every count/value below was measured. No secret values are reproduced in this document — only their location and characterization (a security finding for the rewrite).

Scope

This document covers the “platform plumbing” of SmartInventory: how users are identified and authorized, how one operator’s data is (or is not) isolated, where configuration and secrets live, the controlled vocabularies the platform depends on, odd legacy artifacts, and a high-level catalog of the 2,105 fn_* functions and 509 views.

Area	Objects	Volume
Identity & RBAC	`user_master` (91/95), `role_master` (17), `module_master` (376), `user_module_mapping` (21.6k), `role_module_mapping` (8.2k), `role_permission_entity` (2.4k), `layer_permission_boundary` (412), `user_permission_area` (53k), + `*_history` shadows (175k+149k+102k)	~510k auth rows
Geography scope	`region_boundary` (59), `province_boundary` (644), `tbl_subdistrict_boundary`, `tbl_block_boundary`, `city_aoi` (4k), `site_circle_master` (22)	~5k
Tenancy / LCO	`APP_LCO` schema (18 tables: SAP material/contract/vendor mgmt), `network_id`/`parent_network_id` hierarchy (in 207/190 tables), `circle_` columns, `postgres_fdw` → `bharatnet_server` (1,734 foreign tables*)	n/a
Config & secrets	`APP_LCO.APP_SETTINGS` (35), `mail_settings`, `connection_master`, `api_consumer_master` (10), `otp_authentication_configuration` (2), `adoid_authentication`	~50
Reference / lookup	~25 `_master` / `_type` tables (1–1,000 rows)	~3k
Odd artifacts	`tbl_covid_` (11 — all empty), `province_boundary`/`region_boundary`, `fn_api_` (63)	n/a
Catalog overview	2,105 `fn_*` functions, 509 views	n/a

Identity/config/tenancy tables all live in the single monolithic public schema (the source_schema schema is an empty clone-template that mirrors public column-for-column — every secret column shown below has a phantom duplicate there). The only true side-schema is APP_LCO (18 tables).

Data model & relationships

Identity & RBAC

The model is a classic admin-built RBAC, but with two parallel grant paths and a third geography overlay:

user_master (user_id PK)
  ├─ role_id ───────► role_master (role_id)
  │                     └─ role_module_mapping  (role_id → module_id)   ← role path
  │                     └─ role_permission_entity (role_id × layer_id → add/edit/delete/viewonly)
  ├─ user_module_mapping (user_id → module_id)                          ← per-user path (overrides/augments)
  ├─ template_id / group_id (role-template & group abstractions)
  ├─ is_all_provience_assigned  (boolean "see-everything" flag)
  ├─ is_admin_rights_enabled
  └─ user_permission_area (user_id → region_id / province_id / city_id / subdistrict_id / block_id)
        └─ geographic scoping against region_boundary / province_boundary / tbl_subdistrict_boundary / tbl_block_boundary

Module gating is dual-sourced. role_module_mapping (8,168 rows / 29 roles) grants modules by role; user_module_mapping (21,594 rows / 96 users) grants modules directly to users. Both feed the same effective menu — so a user’s accessible modules = role-granted ∪ user-granted. module_master holds 376 modules (369 active), each with module_abbr (the 90+ module codes: CMM, NM, WFM, ISPV, FLK, ACF…), a type (Web 212 / Admin 140 / Mobile 24), parent_module_id for a 2-level menu tree (82 distinct parents), and form_url. Modules are the feature-flag surface of the app.
Layer-level CRUD is role_permission_entity: 2,382 rows mapping 16 roles × 66 layers to four booleans (add/edit/delete/viewonly) — i.e. per-role permissions on each GIS entity type. layer_permission_boundary (412) and layer_rights_template_permission (1,287) add geography-boxed and template-based variants of the same CRUD grant.
Geography scope is user_permission_area: a user is granted a set of (region, province, subdistrict, block) IDs. fn_get_user_permission_area(userid) (31 lines) resolves these IDs to human-readable names via the boundary tables. This is the only data-row scoping mechanism for ordinary users.
History/audit is exhaustive: user_permission_area_history (175k), user_module_mapping_history (149k), role_module_mapping_history (102k), audit_user_master (423), audit_role_template_permission (16k), layer_permission_boundary_history (580) — all populated by fn_trg_* triggers (fn_trg_user_module_mapping_history, fn_trg_user_permission_area_history, fn_trg_role_module_mapping_history, fn_trg_audit_role_template_permission).

Tenancy: there is no tenant key — three weak partitioning mechanisms instead

The single biggest finding for the rewrite: there is no tenant_id / operator_id column anywhere. Isolation in this single DB is attempted via three overlapping, application-enforced mechanisms:

network_id / parent_network_id self-referential hierarchy — network_id appears in 207 tables, parent_network_id in 190. It is a containment code (network → child network), not a tenant boundary, and there is no central network_master dictionary (only network_status_master, 3 rows: Planned / As-Built / Dormant, and entity_network_code_history, 21 rows). Scoping is therefore by string code embedded in every row.
“Circle” = India telecom service area — site_circle_master (22 rows) enumerates TRAI circles (West Bengal, Mumbai Metro, Karnataka…, categorized A/B/C/Metro). Columns circle_rjid/circlename/circle_name/circlecode recur across 17+ tables. This is geographic partitioning of one operator’s network, not multi-tenant isolation. (Note: circle_master, despite the name, is a map-circle geometry entity table, unrelated to telecom circles.)
postgres_fdw cross-deployment federation — a single foreign server bharatnet_server exposes 1,734 foreign tables that mirror the entire schema of a separate deployment (India’s national rural-broadband “BharatNet” network) into this Jio instance. So a second customer’s whole database is reachable from this one connection — the opposite of isolation.

Tenancy: the `APP_LCO` module

APP_LCO (18 tables) is not “Local Cable Operator” tenancy — it is a SAP-integrated material/contract/vendor management module for the Jio/Reliance supply chain (“LCO” = the Reliance “LCO” SAP API namespace, seen in endpoints like apifactory/lco-cocreate, lco-venddetail). It has its own RBAC independent of public:

APP_LCO table	rows	purpose
`APP_SETTINGS` / `APP_SETTINGS_HIST`	35 / 70	key/value config incl. plaintext SAP & AD credentials (see Secrets)
`VENDOR_MASTER` / `VENDOR_ADRESS_MASTER` / `PRMID_VENDOR_MAPPING`	914 / 1 / 914	vendors (PAN/GSTIN — Indian tax IDs)
`MATERIAL_MASTER`	281	SAP material catalog (18-digit codes, UOM KM/EA)
`CONTRACT_ORDER` / `_HISTORY` / `CONTRACT_GEOGRAPHY`	769 / 0 / 0	SAP Contract Orders (CO)
`MTO`/`MIN`/`FSA` family (`FSA_MASTER_ATTR` 793, `FSA_MTO_`, `MIN_ALLOCATION`)	up to 793	Material Take-Off, Material Issue Note, Field Service Area allocation
`ACTIVITY_MASTER` / `ROLE_ACTIVITY_MAPPING`	10 / 20	LCO-specific activity-based RBAC (e.g. `/MINCreate`, `/MTOCreate`, `/ContractOrderCreate`, `/VendorMapping`)
`NLOG`	11.6k	LCO operation log
`LCO_REPORT`	matview	reporting

So the DB actually contains two unrelated authorization systems (public RBAC + APP_LCO activity RBAC).

What data is actually held

95 users (user_master): 89 active, 82 of type partner (external contractors) vs 13 own (Jio staff). application_access: BOTH 83, WEB 9, MOBILE 3 (channel gate). 82 of 95 have is_all_provience_assigned=true — meaning the geographic-scope mechanism is effectively disabled in practice; almost everyone sees every province. 29 users have admin rights.
17 roles: super admin, gis user, admin, System Administrator, Mobile User, Partner, View User, WFM Contractor, plus obvious test roles (test2, Qa3, Role-1, CP sing ROle, Edit R, software Tester).
13.5k login-history rows across 70 users; user_login/user_login_history capture client_ip, mac_address, os_name, session_id, refresh_token, access_token (device-fingerprint-level auditing).
Geography: 59 regions, 644 provinces, 22 telecom circles, 4,002 city AOIs.
OTP MFA is configured but OFF: otp_authentication_configuration has Web + Mobile rows, both is_otp_enabled = false (temp_lock 3 attempts / perm_lock 5).

Business logic (key stored functions)

Authorization logic lives in PL/pgSQL, not app code:

fn_get_user_permission_area(userid) (31 lines) — resolves a user’s user_permission_area rows to region/province/subdistrict/block names via STRING_AGG.
fn_trg_set_user_permissions() (30 lines, trigger on province_boundary) — auto-grants every newly added province to all users with is_all_provience_assigned=true, and cascades a DELETE of user_permission_area rows when a province is removed. This is why scope is so broad in practice.
fn_user_rights_save_user_permission_area (125 lines) / fn_user_rights_save_wfm_user_permission_area (168 lines) — persist scope grants; the WFM variant is a heavier parallel path.
fn_user_rights_get_roles (87 lines), fn_user_rights_get_role_permission (37, returns JSON), fn_user_rights_get_templates (71), fn_user_rights_get_layer_template_permission (35) — the admin-screen read side, returning JSON menus.
fn_get_role_module_mapping (8) / fn_insert_role_module_mapping (22), set_user_permission_and_module_permission (17) — module-grant CRUD.
fn_get_create_ticket_permission_by_geom (61) — spatial permission: can this user create a ticket at this geometry? (geometry-gated RBAC).
fn_api_updateapisettings (45) — mutates the APP_SETTINGS/api-config at runtime (the SP-API can rewrite its own config, incl. credentials).

Secrets in the database (security finding)

Location	Column(s)	Storage characterization
`user_master.password` (95 rows)	`varchar(50)`	Reversibly encrypted, NOT hashed. Lengths are exactly 8/12/16/20/24 (all multiples of 4, base64 charset, 0 match MD5/SHA-256/bcrypt) — the signature of AES-CBC + base64 ciphertext. 68 distinct of 95 ⇒ password reuse. (Consistent with the known legacy “hardcoded AES key” finding — encryption is reversible by anyone with the app key.)
`APP_LCO.APP_SETTINGS.CONFIG_VALUE` (35 rows)	`varchar`	Plaintext credentials. Active Directory service password, SAP REST API user/passwords (MTO/MIN/CO), and OAuth client-id/secret (`SECOClientID`/`SECOSecret`) are all stored as literal plaintext key/value rows, alongside live SAP/AD endpoint URLs (`.ril.com`, `.jio.com`).
`mail_settings`	`email_password varchar(500)`, `smtp_host`, `usedefaultcredentials`	SMTP relay credentials (empty in this QA instance, but the column exists).
`connection_master`	`connection_string varchar(300)`, `is_encrypted bool`	App DB connection string with an `is_encrypted` flag (=false here; empty value in QA).
`api_consumer_master` (10 rows)	`user_name`, `password varchar(100)`	Inbound API client credentials for 9 integration sources (CRM, HOBS, NMS, LMS, infor, GIS, Lepton-GIS, Mobile, WEB). Password lengths 0–20 — at least one consumer has an empty password, and they look unhashed.
`adoid_authentication`, `user_login(_history)`	`access_token`, `refresh_token`	Azure-AD/OIDC bearer + refresh tokens persisted in plaintext varchar.
`att_details_microwavelink.password varchar(200)` (+ audit/views)	device password	Equipment management credential stored in a domain attribute table (and surfaced in `vw_*` views/reports).
`tbl_covid_*.api_key`	`api_key`	API keys in dead COVID tables (empty).

Net: the DB holds plaintext or reversibly-encrypted user passwords, integration credentials, SAP/AD service accounts, OAuth secrets, and OIDC tokens. The source_schema clone duplicates every one of these columns.

Reference / lookup vocabularies (to seed in the new platform)

Measured controlled vocabularies (codes/values only; PII excluded):

wfm_tt_type — trouble-ticket taxonomy (3-level): categories Enterprise / Trouble Ticket; Enterprise types DIA, IPVPN, Metro Ethernet, Cloud Direct Connect, FAST/FASTER, IBiz, MicroBiz… each with Equipment_Failure/Last_Mile_Facility_Issue; consumer types Fiber / DOCSIS Internet / TV Subscription with fault subtypes (CPE_Issue, LOS_Isolated, RX_Issue, WIFI_Issue, Modem_Not_Registered, No_Signal…).
termination_point_master — the network-connectivity rule table: which entity types may terminate on which host layer (Cable→{HTB, ONT, BDB, FDB, ADB, CDB, SpliceClosure, FMS, Handhole, OpticalRepeater}; Duct→{WallMount, Manhole, Pole, Tree, PatchPanel}; MicrowaveLink→Antenna), flagged is_isp_tp/is_osp_tp.
splitter_loss_master — PON optical-budget reference: insertion loss (dB) per split ratio (1:2, 1:16, 1:32, 1:128…) × wavelength (1–7). Core to feasibility/planning math.
feasibility_cable_type — fiber-count cable SKUs (2F/4F/12F/22F/46F/96F…) with per-unit material/service price.
network_status_master — entity lifecycle: Planned (P) / As-Built (A) / Dormant (D).
attachment_type_master — document types × workflow state (Application Form, Drawing, Demand Note, Payment Receipt, Permit × Apply/Approved/Rejected).
ticket_steps_master — provisioning job-order steps keyed by RFS type (Non-RFS, A/B/C-RFS): Customer Information → Choose Distribution Box → NAP/Port → CPE → Connection Testing → Signature → Activation.
APP_LCO.MATERIAL_MASTER — SAP material catalog (281 rows): HDPE duct, OFC armoured/micro cables 6F–288F, joint closures, FAT 48F, pole clamps; UOM KM/EA.
bom_boq_master (~30 layers) and res_dropdown_master are config-as-data, not vocab: the former stores embedded SQL fragments (e.g. att.total_Core||' F '||fn_get_cable_type(att.cable_type)) used to group BOM entities (injection/maintenance risk); the latter is an i18n/resource-string registry that incidentally documents the product’s module map (Smart Inventory / Smart Planner / Smart Feasibility).
Pollution warning: isp_brand_master holds only brand1; isp_model_type_master and landbase_dropdown_master are heavily polluted with test rows (Test, test1234, b2). entity_notification_status and fat_process_running_status are misnamed — they are runtime state/job-lock tables (boolean flags), not lookups.

Odd artifacts

tbl_covid_* (11 tables, all 0 rows) — vestige of a COVID-19 containment-zone mapping product Lepton shipped on the same engine (tbl_covid_data_containment_zone, _locality, tbl_covid_contactus, _mobile_app_log, _legend_detail). Dead weight carried into the FTTx schema; safe to drop. They also each carry an api_key column (empty).
province_boundary / region_boundary and their many copies — province_boundary (644), plus _2024_07_22, _2024_07_22_new, _2024_07_22_new_new, _bkp, _new, kenya_provinces*, germany_province, tbl_province_boundary — manual dated backup tables (no migration discipline). region_boundary similarly has 5 dated copies. These boundaries double as the geographic permission scope (see RBAC).
fn_api_* (63 functions) — the stored-procedure API surface exposed to integration consumers. Categorized:
- Serviceability / feasibility (the OSS surface): fn_api_getserviceability, fn_api_get_ossserviceability, fn_api_get_nearby_available_devices (408 ln), fn_api_get_nearby_available_splitter (442 ln), fn_api_get_nearby_db_and_splitter_info, fn_api_reserve_feasibility_route / fn_api_release_feasibility_route, fn_api_check_blackhole.
- Fault / fiber-cut localization (NMS integration): fn_api_get_fault_location_detail (433 ln), fn_api_get_fiber_cut_details (269 ln), fn_api_update_alarmstatusdetails.
- Customer lifecycle (CRM integration): fn_api_get_customer_info, fn_api_save_customer_info (327 ln), fn_api_save_customer_association (177 ln), fn_api_shift_customer, fn_api_getcustomerdistance.
- Port / resource provisioning: fn_api_update_port_status (146 ln), fn_api_update_reservefatport / fn_api_update_releasefatport, fn_api_get_splitter_port_info, fn_api_get_ont_details, fn_api_get_resource_data.
- Ticketing / WFM: fn_api_nwt_insert_update_ticket, fn_api_wfm_ticket_steps, fn_api_get_user_tickets_info.
- Self-mutating config: fn_api_updateapisettings, fn_api_reset_xml, fn_api_save_logs.
- Many have _test / _backup / dated twins committed alongside the live version.

Catalog overview

Functions — 2,105 `fn_*` (of 3,428 total functions; languages: 2,287 plpgsql, 809 C/PostGIS-pgRouting, 310 SQL, 21 internal)

By prefix:

Prefix	Count	Purpose
`fn_get_*`	799	Read/query layer. Sub-themes: export (44), entity (41), user (23), network (23), layer (22), bom (21), fiber (16), isp (12)…
`fn_trg_*`	207	Trigger bodies (audit/history shadow-table population, cascade logic)
`fn_uploader_*`	92	Bulk data-upload validation & ingest
`fn_isp_*`	92	Inside-plant (OLT/ONT/card/slot/port) logic
`fn_api_*`	63	The SP-API surface (above)
`fn_update_*`	57	Mutations
`fn_process_*`	57	Batch/async processing
`fn_splicing_*`	56	Fiber splicing
`fn_nwt_*`	56	Network tickets
`fn_save_*`	51	Persist (often returning JSON)
`fn_validate_*`	36	Validation rules
`fn_delete_` (33), `fn_landbase_` (32), `fn_network_` (30), `fn_bulk_` (27), `fn_auto_` (27), `fn_res_` (25), `fn_sf_` (21), `fn_row_` (20), `fn_check_` (20), `fn_wfm_` (13), `fn_codification_` (12), `fn_user_` (11), `fn_fat_*` (11)	—	secondary domains

Grouped by business area (≈ where logic lives): read/query 731 · CRUD/mutation 227 · triggers 191 · data upload/import 159 · export/report 129 · ISP 126 · network-tickets/WFM 115 · validation 86 · splicing/fiber 78 · SP-API 64 · BOM/BOQ costing 49.

20 largest functions by source lines (densest business logic):

lines	function	note
2,895	`fn_fat_generate_splicing`	FAT auto-splicing — the single densest function
2,809 / 2,788 / 2,754 / 2,728	`fn_fat_generate_splicing_bk9thmay2023`, `_bkp5thmay23`, `_bkp26april2023`, `_bkp252023`	four dated backups of it committed in-DB
905	`fn_get_area_potential`	demand/serviceability potential
852	`sp_fn_network_planning_save_auto_planning`	auto network planning
799	`fn_uploader_validate_parent_details_test`	upload validation
718 / 675 / 671	`fn_get_sld_data_2`, `_copy`, `fn_get_sld_data`	schematic line diagram (JSON) — 3 copies
717 / 622	`fn_network_planning_save_auto_planning_27122024`, `_20231208_test`	planning (dated)
701 / 573	`fn_poc_point_to_point_auto_network_planning`, `fn_poc_auto_network_planning`	PoC planning
631	`fn_isp_save_att_model_mapping`	ISP model mapping (JSON)
631 / 568 / 568	`fn_auto_provisioning`, `_test2411`, `_test`	auto-provisioning (3 copies)
623	`fn_uploader_validate_parent_details_bkp_14012021`	upload validation backup

The dense logic clusters in: FAT auto-splicing, auto network planning, auto-provisioning, SLD generation, area-potential, and upload validation — and a striking share of the largest functions are dated backup copies living in the live catalog.

Views — 509 `CREATE VIEW`

Category	Count	Notes
`vw_att_*` (per-entity attribute views)	388	the dominant family; of these *82 are `_map` (map-rendering), 85 `_audit`, 73 `_report`**
map views overall (`*_map`)	85	feed the GIS map UI
audit/history views (`_audit`/`_history`)	91	over the shadow tables
report views (`*_report`)	73	reporting layer
`vw_wfm_` (10), `vw_user_` (8), `vw_process_` (8), `vw_layer_` (8), `vw_isp_` (7), `vw_get_` (7), `vw_entity_*` (6)	54	functional views
`vw_api_` (3), `vw_dashboard_` (2), `vw_bom_` (2), `vw_feasibility_` (2)…	rest	misc

The view layer is overwhelmingly a per-entity ×3 fan-out (vw_att_<entity> + vw_att_<entity>_map + vw_att_<entity>_audit + vw_att_<entity>_report) — a direct consequence of the per-entity table sprawl (93 att_details_* tables). Several views (e.g. vw_*_microwavelink*, vw_user_*) re-expose the password columns characterized above.

Design choices & trade-offs

Admin-built dual-path RBAC. Modules can be granted by role or per-user; CRUD is per-role × per-layer; scope is per-user geography. Flexible, but the effective permission of a user requires unioning four tables, and in practice scope is bypassed by the is_all_provience_assigned flag (82/95 users).
Config-as-data taken to an extreme. Modules, layer capabilities (layer_details, see doc 03), BOM grouping SQL, and even integration credentials/endpoints are all rows, editable at runtime — including via a stored procedure (fn_api_updateapisettings). This is operationally flexible but means security-critical state is just data.
Tenancy by convention, not by constraint. No tenant key; isolation depends entirely on the app remembering to filter by network_id/circle, and on FDW boundaries. With only 84 FKs DB-wide, the DB enforces none of it.
In-DB versioning of logic. Backups of large functions and dated copies of boundary tables are committed straight into the catalog instead of source control / migrations.

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

Reversible/weak credential storage. user_master.password is AES+base64 (lengths 8–24, all ÷4, no hash signatures), reusable across 27 accounts; recoverable with the known hardcoded app key. Must be replaced with a one-way KDF (Argon2id/bcrypt).
Plaintext secrets in data rows. APP_LCO.APP_SETTINGS stores AD/SAP/OAuth credentials in cleartext; api_consumer_master has at least one empty-password client; adoid_authentication/user_login* persist OIDC tokens in plaintext. None belongs in the DB — move to a secrets manager.
No tenant isolation. Single shared schema, no tenant key, and a postgres_fdw server exposing 1,734 foreign tables of a second customer’s deployment from one connection. A compromised app role can read across deployments.
MFA available but disabled (is_otp_enabled=false for both Web and Mobile).
source_schema is a full empty clone that duplicates every secret-bearing column — extra attack surface with no purpose.
Embedded SQL in bom_boq_master — SQL fragments stored as data and concatenated at runtime = injection + refactor hazard.
Catalog hygiene — dead tbl_covid_* tables, 5–6 dated copies of boundary tables, and multiple _test/_bkp/dated copies of the largest functions pollute the live catalog.

Implications for the new platform

Keep / model forward

The controlled vocabularies (trouble-ticket taxonomy, termination-point connectivity rules, splitter-loss optical budgets, cable-type SKUs, network lifecycle statuses, material catalog) — these encode real telecom domain knowledge; seed them as first-class, typed reference data (drop the test pollution).
The capability model (modules = features, layer = entity-CRUD, geography = scope) is sound conceptually; reimplement as a real RBAC/ABAC with a typed permission catalog.
The SP-API intent (serviceability, fault localization, provisioning, customer lifecycle) is the integration contract OSS/CRM/NMS depend on — preserve the semantics behind fn_api_*, but re-express as a versioned service API, not stored procedures that can rewrite their own config.

Redesign

Introduce a real tenant_id (and operator/circle/network as typed dimensions) enforced at the row level (RLS) — do not rely on FDW or string network_id for isolation.
Hash passwords (Argon2id), externalize all secrets to a vault/KMS, mandate MFA, and remove credential columns from the data model entirely.
Replace config-as-data-with-embedded-SQL and in-DB function backups with declarative config + migrations + source control.
Collapse the per-entity ×4 view/table fan-out (388 vw_att_*) into a typed, generalized entity model.