01 — Core Geometry Masters, Entity Registry & Network/Codification Spine

Source: live smartinventory_qa (PostgreSQL 14.8 + PostGIS 3.2.2), read-only analysis 2026-06-16. All counts are from queries run against the live DB (or references/db-analysis/raw/ dumps); table-inventory reltuples estimates are noted as such where they differ from live count(*).

This domain is the structural keystone of SmartInventory. Three “master” tables hold all geometry; one 114-column registry (layer_details) describes every entity type; a self-referential network_id hierarchy plus a stored-function “codification” engine assigns codes; and per-entity GeoJSON caches + map views surface it to the front end.

---

Scope

Object group	Objects	Rows (live)	Role
Geometry masters	point_master, line_master, polygon_master	424,887 · 265,469 · 143,325	All spatial features, decoupled from attributes
ISP line master	isp_line_master	127	Inside-plant logical cable geometry (separate, varchar line_geom)
GeoJSON caches	60 × <entity>_geojson_master (pole_, building_, duct_, …)	~0.85M total (Pole 183k, Building 143k, Duct 94k, …)	Precomputed GeoJSON Features for map serving
Entity registry	layer_details (66 × 114), layer_details_column (89), codification_layer_details (7), layer_mapping (168)	—	Config-not-code: drives forms, saves, reports, codification
Network spine	network_id in 537 columns / parent_network_id in 192 columns; vw_layer_mapping	—	Self-referential network hierarchy + spatial parent resolution
Codification engine	fn_auto_codification, fn_get_network_code, fn_get_parent_info, fn_get_line_network_code, fn_codification_get_design_sequence, auto_codification_logs	—	Assigns GIS design IDs + network codes
Lifecycle	entity_status_history	1,347,425	Append-only status-transition log
Map surfacing	~60 vw_att_details_<entity>_map views	—	Master ⋈ att_details + computed icon/label for the map
Audit shadows	audit_point_master (537k), audit_line_master (310k), audit_polygon_master	—	Trigger-populated before/after history

point_master carries 33 distinct entity_type values, line_master 6, polygon_master 10 — together exactly the 66 layers in layer_details (the rest are ISP/abstract layers with no master rows).

---

Data model & relationships

The master ⋈ att_details ⋈ layer_details triangle

Every spatial feature has three coordinated rows joined by an integer system_id (the universal entity key, not a true PK across tables — there are only 84 FKs in the whole DB):

                       layer_details (registry, 66 rows)
                         ▲  layer_name = entity_type
                         │  layer_table → att_details_<entity>
                         │
  <geom>_master  ── system_id ⋈ system_id ──  att_details_<entity>
  (geometry +              (1:1)               (domain attributes:
   workflow +                                   network_id, codification_seq,
   audit envelope)                              parent links, dropdowns…)
        │
        └── system_id ⋈ system_id ── <entity>_geojson_master (cached GeoJSON)

• entity_type (varchar discriminator, NOT NULL on all three masters) selects which att_details_* table and which layer_details row apply. E.g. a point_master row with entity_type='Pole' joins att_details_pole on system_id and is described by layer_details where layer_name='Pole' (layer_table='att_details_pole').
• The join is clean for primary entities: point_master where entity_type='Pole' (182,868 rows) has zero orphans against att_details_pole; line_master/Cable likewise has zero orphans. The decoupling is consistently maintained by the save functions.
• layer_details.layer_table, .layer_view, .report_view_name, .history_view_name are string pointers the app dereferences at runtime, and the codification/network functions read them via dynamic SQL (EXECUTE 'UPDATE ' || layer_table || …).

Master table column groups (point_master, 30 cols — line/polygon are near-identical subsets)

Group	Columns	Notes
Identity / discriminator	system_id (int), entity_type (varchar(100), NOT NULL), common_name, display_name, entity_category	entity_category sub-discriminates (e.g. Cable: Feeder/Distribution/Drop)
Geometry	sp_geometry (geometry), longitude/latitude (point only, double), st_x/st_y (point), center_line_geom + sp_centroid + buffer_width (polygon only)	Polygon keeps a derived centerline + centroid; point duplicates coords as both lon/lat and st_x/st_y
Approval workflow (vestigial)	approval_flag char(1), approval_date, approver_id, creator_remark, approver_remark	See findings — effectively auto-approved
Lifecycle / status	network_status varchar(25) NOT NULL, status varchar(10), is_virtual bool NOT NULL, is_processed bool, is_buried bool (point), db_flag int	network_status P/A/D = Planned / As-built / Dormant
Audit	created_by/created_on, modified_by/modified_on	point_master.modified_on is mistyped time without time zone (a bug — drops the date)
Codification linkage	gis_design_id varchar	Set here only when codification runs; the network code itself lives in att_details
External / provenance	source_ref_id, source_ref_type, project_id	source_ref_type records origin: WEB / planning / DU / Network_Ticket / Mobile
Misc (point)	no_of_ports (varchar!)	Stringly-typed port count

isp_line_master is a separate, smaller pattern for inside-plant logical cables: line_geom is character varying (WKT-in-a-string, not a geometry type), plus a_node_type/b_node_type, structure_id, geom_source — i.e. ISP geometry is modeled outside PostGIS.

GeoJSON caches (<entity>_geojson_master)

60 identically-shaped tables, 5 columns each: id, system_id, province_id, region_id, geojson jsonb. The geojson holds a complete GeoJSON Feature (keys: type, geometry, properties, entity_type, layer_title). They are materialized read caches for map serving, refreshed by fn_geojson_update_entity_attribute(...) (called at the end of fn_auto_codification and on every save). Indexed only on system_id and province_id (btree) — no GIN on the jsonb, no GIST on geometry (geometry is inside the json text). Region/province filtering is the access pattern, not bbox.

Network spine & codification metadata tables

• layer_mapping (168 rows): the child→parent layer graph. Columns layer_id, parent_layer_id, parent_sequence, is_used_for_network_id, network_code_format, is_default_parent. A child layer can have multiple candidate parents ordered by parent_sequence; the winner is chosen by spatial containment at save time. Surfaced as vw_layer_mapping.
• codification_layer_details (7 rows): maps a layer to a 2-char layer_abbrevation per layer_feature_type (e.g. SpliceClosure→SC, Trench→TR, Cable→FC, Pole→PO, Manhole→CH) — used by fn_auto_codification to build the GIS design ID.
• connection_info: the splicing/connectivity table (source/destination system_id + port), kept in sync with display names during codification.
• auto_codification_logs, entity_network_code_history: audit of codification runs / code changes.

---

What data is actually held (measured distributions)

Approval workflow is effectively dead. approval_flag is 'A' on virtually every row (point 424,800 ‘A’ vs only 87 ‘M’; line all ‘A’; polygon 143,325 ‘A’ + 36 blank). approver_id is non-null on 100% (424,887/424,887) of point_master rows and creator_remark is populated on 424,852 — i.e. these are defaulted on insert, not the product of a real reviewer step. The approval columns exist but encode no live workflow in this dataset.

network_status (the real lifecycle dimension):

Status	point_master	line_master	Meaning
A	377,843	241,407	As-built
P	46,863	24,029	Planned
D	181	33	Dormant

status (separate column): point_master 422,247 A, ~2,077 blank, 503 D, 50 S, 10 R (Rejected).

Geometry (sampled via TABLESAMPLE): despite geometry_columns registering all master geom columns at SRID 0, the stored geometries are SRID 4326 (WGS84): points = ST_Point, lines = mostly ST_LineString with some ST_MultiLineString, polygons = ST_Polygon. The SRID-0 column typemod is a definition-time omission; map views compensate with explicit ST_GeomFromText(..., 4326).

Provenance (source_ref_type, point_master): WEB 272,722 · null 144,874 · planning 4,742 · DU (data-upload) 1,450 · Network_Ticket 616 (mixed casing) · Mobile 129. Most features were drawn in the web editor; a meaningful slice came from bulk upload and planning.

is_virtual / is_processed: essentially all false (only 2 virtual points). db_flag: mostly 0 (381,871) with clusters of upload-batch IDs (336→33,899; 315→1,059; …) — db_flag doubles as an upload-batch tag (and is surfaced as upload_id in the map views).

Codification reality: gis_design_id is set on only 7 of 424,887 point_master rows, but the network code lives in att_details: att_details_pole.network_id is populated on 100% (182,876/182,876) while att_details_pole.gis_design_id is 0%. So in this dataset every entity has a network_id (from fn_get_network_code) but the heavier hierarchical GIS design ID (fn_auto_codification) was largely never run.

entity_status_history (1,347,425 rows): status is A 1,344,949 · D 1,564 · blank 555 · S 297 · R 60. Top entity_type volumes: CABLE 430,746 · DUCT 263,841 · POLE 183,464 · BUILDING 142,933 · STRUCTURE 142,530 · MANHOLE 86,163 · TRENCH 76,619. (CABLE/DUCT have ~4× and ~2.8× their master row counts → multiple transitions logged per entity.) Columns: audit_id, entity_system_id, entity_type, status, status_updated_on/by, status_remarks, created_on/by. It is an append-only state-transition ledger keyed by (entity_system_id, entity_type) — the canonical source for “when did this asset go Planned→As-built→Dormant”.

---

The layer_details registry (66 × 114)

layer_details is the single configuration table that turns SmartInventory from code into config. One row per entity type (= per map layer). The 114 columns fall into groups:

Pointers / config strings — layer_table, layer_view, layer_template_table, report_view_name, additional_report_view_name, history_view_name, vc_view_name, other_info_view, audit_table_name, entity_geojson_table, dynamic_attribute_table, data_upload_table, delta_metadata_table. The app + the stored functions dereference these by name in dynamic SQL.

UI-driving URLs — layer_form_url, template_form_url, save_entity_url, mobile_url. The .NET front end fetches these to render/post forms; the geometry-class + abbreviations (layer_abbr, map_abbr, layer_display_abbr, layer_title) and zoom bounds (minzoomlevel/maxzoomlevel, minboundvalue/maxboundvalue, map_layer_seq, layer_seq, isvisible) drive the map.

~80 boolean capability flags, grouped by purpose (live tallies across the 66 rows):

Purpose	Representative flags	#layers ON
Plant type	is_osp_layer / is_isp_layer	55 OSP, 20 ISP
Network role	is_network_entity, is_networktype_required, is_network_ticket_entity, is_middleware_entity	47 network entities
Splicing	is_splicer, is_isp_splicer, is_trayinfo_enabled, is_split_allowed	11 splicers
CPE / customer	is_cpe_entity, is_fiber_link_enabled, is_pod_association_allowed	5 CPE
Mobile	is_mobile_layer, is_visible_in_mobile_lib, is_visible_on_mobile_map, is_mobile_isp_layer, is_offline_allowed, is_remark_required_from_mobile	43 mobile
Barcode / asset tag	is_barcode_enabled (+ barcode_column)	25
Templates / BOM	is_template_required, is_project_spec_allowed, is_bomboq_enabled, is_vendor_spec_required	43 templated
Upload	is_data_upload_enabled (+ data_upload_max_count, data_upload_table)	33
Reporting	is_report_enable (+ report_view_name)	55
History	is_history_enabled (+ history_view_name)	59
Feasibility	is_feasibility_layer, feasibility_network_group	16
Permissions / rights	is_layer_for_rights_permission, is_row_association_enabled	—
Cloning	is_clone, is_multi_clone, is_reference_allowed	—
Dynamic attrs	is_dynamic_attribute_enable (+ dynamic_attribute_table), is_dynamic_control_enable	—
Misc capabilities	is_info_enabled, is_lmc_enabled, is_utilization_enabled, is_at_enabled, is_maintainence_charges_enabled, is_vsat_enabled, is_fault_entity, is_loop_allowed, is_logicalview_enabled, is_site_enabled, is_vector_layer_implemented, is_tp_layer	—

Codification config columns — network_id_type (A=auto-generated / M=manual), network_code_seperator (single char), is_networkcode_change_enabled, is_label_change_allowed, layer_network_group, parent_layer_id. Live: 63 layers are auto (A), 3 manual (M); separator is - on 60 layers, _ on 4 (Area/SubArea/DSA/CSA — the planning hierarchy), null on 2. Geometry split: 40 Point, 11 Polygon, 11 Line, 1 Circle, 3 null.

Full 66-layer catalog

Columns: layer_id · layer_name · layer_abbr · geom_type · parent (via layer_mapping) · layer_table · class (NE/SPL/CPE) · plant. Plant: all OSP except a handful (FiberLink, LandBase, Competitor, Clamp/Other = -; UNIT = ISP). Class tags from layer_catalog.tsv: NE = network entity, SPL = splicer, CPE = customer premises.

id	layer	abbr	geom	att/source table	class
1	Area	ARA	Polygon	att_details_area	—
2	SurveyArea	SVA	Polygon	att_details_surveyarea	—
3	Building	BLD	Point	att_details_building	NE
4	SubArea	SBA	Polygon	att_details_subarea	—
5	Structure	—	Point	att_details_bld_structure	NE
6	ADB	ADB	Point	att_details_adb	NE SPL
7	BDB	BDB	Point	att_details_bdb	NE SPL
8	CDB	CDB	Point	att_details_cdb	NE SPL
9	Splitter	SPL	Point	att_details_splitter	NE
10	POD	POD	Point	att_details_pod	NE
13	Manhole	MH	Point	att_details_manhole	NE
14	Pole	POL	Point	att_details_pole	NE
15	Tree	TRE	Point	att_details_tree	NE
16	Trench	TRH	Line	att_details_trench	NE
17	Duct	DCT	Line	att_details_duct	NE
19	Cable	CBL	Line	att_details_cable	NE
20	SpliceClosure	SC	Point	att_details_spliceclosure	NE SPL
21	Customer	CUS	Point	att_details_customer	—
22	MPOD	MOD	Point	att_details_mpod	NE
23	ONT	ONT	Point	att_details_ont	NE SPL CPE
25	WallMount	WMT	Point	att_details_wallmount	NE
27	UNIT	UNT	Point	isp_room_info	ISP
28	HTB	HTB	Point	isp_htb_info	NE SPL CPE
29	FDB	FDB	Point	isp_fdb_info	NE SPL
33	Floor	FLR	—	isp_floor_info	—
34	SHAFT	SHFT	—	—	—
35	Loop	LOP	Point	att_details_loop	NE
36	FMS	FMS	Point	att_details_fms	NE SPL
37	Coupler	CUP	Point	att_details_coupler	NE
38	ProjectArea	PRA	Polygon	att_details_projectarea	—
39	ROW	ROW	Polygon	att_details_ROW	—
40	PatchCord	PAT	Line	att_details_PatchCord	NE
43	Equipment	EQMT	Point	att_details_model	NE
44	DSA	DSA	Polygon	att_details_dsa	—
45	CSA	CSA	Polygon	att_details_csa	—
46	Rack	RCK	Point	att_details_rack	NE
49	Fault	FAU	Point	att_details_fault	NE
51	Clamp	CLM	Point	att_details_other	NE
54	Tower	TWR	Point	att_details_tower	NE
55	PIT	PIT	Circle	att_details_row_pit	—
56	Other	OTH	Point	att_details_other	NE
57	Competitor	COM	Point	att_details_competitor	—
58	Sector	SCT	Polygon	att_details_sector	NE
59	Antenna	ANT	Point	att_details_Antenna	NE
60	Province	PRV	Polygon	province_boundary	—
61	Network_Ticket	NT	Polygon	att_details_networktickets	—
62	MicrowaveLink	MWL	Line	att_details_microwavelink	NE
63	Microduct	MD	Line	att_details_microduct	NE
64	SpliceTray	—	—	att_details_splice_tray	—
65	Cabinet	CBT	Point	att_details_cabinet	NE
66	Vault	VLT	Point	att_details_vault	NE
67	Conduit	CON	Line	att_details_conduit	NE
68	OpticalRepeater	OR	Point	isp_opticalrepeater_info	NE SPL CPE
69	LandBase	LBL	Point	att_details_landbase	—
70	Handhole	HH	Point	att_details_handhole	NE
71	PatchPanel	PPL	Point	att_details_patchpanel	NE SPL
72	VSAT	—	Point	att_details_vsat_hub	—
73	Restricted_Area	RARA	Polygon	att_details_restricted_area	—
75	Gipipe	GIP	Line	att_details_gipipe	NE
76	Slack	SLK	Point	att_details_Slack	NE
77	Reinstatement	RIT	Line	att_details_reinstatement	NE
78	Rebar	RBR	Line	att_details_rebar	NE
79	DP	DP	Point	att_details_dp	NE
81	FiberLink	FBR	Line	att_details_fiber_link	NE
82	CX	CX	Point	att_details_cx	NE SPL CPE
83	GX	GX	Point	att_details_gx	NE SPL CPE

(Note inconsistent table casing — att_details_ROW, att_details_Antenna, att_details_PatchCord, att_details_Slack are mixed-case while the rest are lower — a portability hazard.)

---

Business logic (key stored functions)

Two distinct “code” systems

SmartInventory assigns two identifiers, by two separate engines:

1. network_id — the per-entity hierarchical network code, generated at save time (fn_get_network_code), stored in att_details_<entity>.network_id. Populated on ~100% of entities.
2. gis_design_id — a longer, planning-hierarchy design code (City + layer-abbr + zero-padded sequence, nested Area→SubArea→DSA→CSA→leaf), generated by the heavy batch fn_auto_codification, stored in both att_details.gis_design_id and the master’s gis_design_id. Rarely run in this dataset.

fn_get_network_code(etype, gtype, parent_sysid, parent_etype, geometry) — 115 src lines (schema_full.sql L101233)

The save-time network-code generator:

1. Calls fn_get_parent_info(...) to resolve the parent entity and its network_code_format.
2. Reads network_code_seperator, layer_table, network_id_type from layer_details for the child.
3. If network_id_type='A' (auto): gets the next sequence via fn_get_sequence_id(...), with a fallback that runs SELECT COALESCE(MAX(sequence_id),0)+1 on the child’s layer_table scoped by (parent_system_id, parent_entity_type, parent_network_id). It then formats the code as parent_network_id || seperator || <static-prefix> || <zero-padded sequence>, where the format string’s trailing XX/NN placeholders become the zero-pad width.
4. If network_id_type='M' (manual): returns the format string verbatim, prefixed with the parent code.

fn_get_parent_info(etype, gtype, parent_sysid, parent_etype, geometry) — (schema_full.sql L103395)

The spatial parent-resolution heart of the network spine. For a feature with no explicit parent, it:

1. Reads candidate parents from vw_layer_mapping where is_used_for_network_id=true, ordered by parent_sequence (polygon/Province parents first).
2. For each candidate, runs ST_WITHIN(<this geometry>, parent.sp_geometry) against <parent_geom_type>_master (e.g. a Pole’s parent SubArea is found by point-in-polygon) and takes the first containing parent (ORDER BY is_virtual).
3. For top-level features, intersects province_boundary to get the province_abbreviation as the root network id; honors a global_settings key IsNLD (national long-distance) to use 'NLD' for cross-province lines.
4. Returns the parent’s network_id (read via dynamic SQL from the parent’s layer_table) and that mapping’s network_code_format.

This is the crux: containment in geometry, not a foreign key, defines the network tree. A wrongly drawn boundary silently re-parents children.

fn_auto_codification(p_system_id, p_entity_type, p_user_id) — 499 src lines (schema_full.sql L12621)

The planning-area GIS-design-ID batch generator (the largest function in the domain). It:

• Creates four ON COMMIT DROP temp tables, pulls the entity subtree via fn_auto_codification_get_entities(...), validates (city code must exist on the FSA/SubArea; PSA/Area boundary must exist).
• Computes a max codification_sequence per (entity_type, parent, category) — with hand-coded special cases for Area, SubArea, DSA (RING2 starts at 49), Cable (Feeder=FC/Distribution=DC/Drop=CC), Trench (FS/DS/CS), Splitter (Primary=S1/Secondary=S2).
• Builds the GIS design ID hierarchically: city_code || layer_abbr || seq for Area; children prepend the parent’s design id; CSA is located by ST_WITHIN against parent geometry; checks for duplicate design ids and aborts with a JSON log.
• Writes results back via dynamic UPDATE <layer_table> and UPDATE <geom>_master, syncs connection_info display names, and calls fn_geojson_update_entity_attribute(...) to refresh the GeoJSON cache. Logs to auto_codification_logs.

Supporting functions: fn_codification_get_design_sequence (102 lines — sequence→code), fn_get_line_network_code (102 — handles line a/b-node parents), fn_get_clone_network_code (clone/multi-clone codes), fn_isp_get_network_code (ISP variant). The inventory shows heavy churn: fn_auto_codification_test, _test1, _check, _duplicate, _bak15oct2022, _newtest — multiple near-duplicate copies, evidence of edit-in-prod development.

Map surfacing — vw_att_details_<entity>_map (~60 views)

Each map view joins <geom>_master ⋈ att_details_<entity> on system_id + literal entity_type, then computes presentation fields. Example (vw_att_details_pole_map): COALESCE(ST_GeomFromText('Point(lon lat)',4326), point.sp_geometry) for geometry, a big CASE building icon_path from pole_type × network_status (icons/Planned/…, icons/AsBuild/…, icons/Dorment/…), db_flag AS upload_id, and network_id AS label_column. The current network_status is read from a live edit-info view (vw_att_details_edit_entity_info via hstore attribute_info) falling back to the stored network_status. A second _map_m variant exists for mobile.

Triggers on masters

point_master/line_master/polygon_master each have an audit trigger (fn_trg_audit_point_master, etc.) feeding the audit_*_master shadow tables; line_master additionally has fn_trg_update_entity_region_province (spatial enrichment of region/province). att_details_fms has fn_sync_fms_latlon_to_point_master — i.e. some attribute edits push geometry back up to the master.

---

Design choices & trade-offs

• Geometry/attribute decoupling (3 masters + N att_details). Pro: one uniform place for geometry, workflow envelope, audit, and map serving; the 60 map views and the codification engine all assume it. Con: every entity is a 2-table affair joined by a convention (system_id + entity_type string), not enforced relationships.
• Config-not-code via layer_details (80 flags + URL pointers). Pro: new entity types are largely data, not deploys; this is the direct conceptual ancestor of the new platform’s schema-driven model. Con: 114 columns of mostly-boolean config is unwieldy; flags interact implicitly; the app reads URLs/table names out of a DB row.
• Spatial containment as the network hierarchy. Pro: matches how planners think (a pole “is in” a SubArea). Con: the parent link is recomputed from geometry every save (ST_WITHIN loops in plpgsql) instead of stored as an edge — fragile, non-deterministic under boundary edits, and not queryable as a graph.
• Two code systems (network_id vs gis_design_id). Format-string driven (network_code_format with nnn/xxx placeholders in layer_mapping, 168 rows; separators in layer_details). Powerful but baroque: sequence logic has per-entity hard-coded branches inside a 499-line function.
• Precomputed GeoJSON caches. Pro: fast map reads filtered by province/region. Con: 60 denormalized copies that must be re-synced on every edit/codification (fn_geojson_update_entity_attribute), with no GIN/GIST — purely id/region lookups.

---

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

1. The approval workflow is vestigial. approval_flag='A' and approver_id non-null on ~100% of master rows; there is no observable creator→approver state in the data. Carrying these 5 columns on every geometry row is dead weight that implies governance the system doesn’t actually perform.
2. SRID mismatch. Master geom columns are typed/registered SRID 0 (geometry_columns.tsv) but hold SRID-4326 data; map views paper over it with explicit ST_GeomFromText(...,4326). Any spatial operator that trusts the declared SRID will misbehave.
3. Wrong data type on point_master.modified_on = time without time zone (loses the date). A latent audit-correctness bug.
4. Stringly-typed geometry & counts. isp_line_master.line_geom is varchar (WKT in text, outside PostGIS); point_master.no_of_ports is varchar. Inconsistent with the PostGIS masters.
5. Containment-derived parenting is non-deterministic. fn_get_parent_info resolves parents by ST_WITHIN at save time; redrawing a boundary silently changes lineage, and there is no stored edge to audit. With only 35 GIST indexes DB-wide, these per-save spatial loops are also slow at scale.
6. No GIN on the GeoJSON caches — 60 jsonb tables queried only by system_id/province_id; geometry buried in text means no spatial index on the cache layer.
7. Function sprawl / edit-in-prod. fn_auto_codification exists as 6+ near-duplicate variants (_test, _test1, _check, _duplicate, _bak15oct2022, _newtest); the dynamic-SQL fallbacks (MAX(sequence_id)+1 under concurrency) are race-prone.
8. Mixed-case table names (att_details_ROW, att_details_Antenna, att_details_PatchCord, att_details_Slack) — quoting hazards, esp. for the dynamic SQL that builds table names from layer_details.layer_table.
9. gis_design_id largely unpopulated (7/424k masters; 0% of att_details_pole) — the heavy codification path was effectively never run here, so its correctness is unverified at scale by real data.

---

Implications for the new platform

Keep (conceptually):

• The decoupled geometry + per-type attribute idea, and especially the layer_details-as-registry pattern — it is the proven seed of a schema-driven entity model. Re-express the 80 flags as a typed capability/feature model (per the ECS/component approach) rather than 80 booleans.
• The two-tier code idea (a stable hierarchical network code per asset) — but as explicit, stored, queryable identifiers.
• The status-transition ledger (entity_status_history) — a clean append-only lifecycle log keyed by (entity, type); reusable nearly as-is.
• Precomputed map tiles/GeoJSON for serving — but as proper vector tiles or GIN-indexed jsonb.

Redesign:

• Store the network/containment hierarchy as explicit edges (the new platform’s graph/topology engine), not as a ST_WITHIN recomputation. This makes lineage deterministic, auditable, and traversable.
• Single geometry model in PostGIS at a declared SRID (4326) with real GIST indexes; drop the SRID-0/varchar-WKT inconsistencies and the time-typed timestamp.
• Drop or genuinely implement the approval workflow — don’t carry 5 dead columns on every feature.
• Collapse the master + att_details split into one entity with a typed attribute model, or keep the split but enforce it with real FKs/constraints (here there are 0 between master and att_details).
• Replace the 499-line, branch-per-entity codification function with a declarative, data-driven code-format spec (the layer_mapping.network_code_format placeholder idea is salvageable) and concurrency-safe sequences.
• Eliminate the 60 hand-maintained GeoJSON cache tables in favor of on-demand tile generation or a single generic cache with spatial indexing.