Data Model — Per-Building BOM Dictionary from Rosetta Stones

Foundation: BBC · BIM_COBOL · MANIFESTO · TestArchitecture · ACTION_ROADMAP · SourceCodeGuide

4 databases, 120+ tables, zero ambiguity. Every element traces from a library product through a BOM recipe to a placed instance. The split is deliberate — catalog, ERP (discipline metadata + compliance), recipe, and output never share a database, so each concern scales independently.

This specification governs the creation of {PREFIX}_BOM.db dictionaries. Each building has its own BOM dictionary (SH_BOM.db, DX_BOM.db), reproduced from fresh by the IFCtoBOM Java pipeline: ./scripts/run_RosettaStones.sh classify_sh.yaml. No hand-editing. No patching. Code produces data.

Source authority: - TheRosettaStoneStrategy.md §Stage 2: IFCtoBOM pipeline writes m_bom + m_bom_line per building - BOMBasedCompilation.md §4 Rule 8 (Cheating Maxim): dx/dy/dz MUST be parent-relative, NEVER world-space centroids - BOMBasedCompilation.md §4: Tack convention — dx/dy/dz = where child's LBD sits in parent (the geometric foundation)

Updated: 2026-04-26 Principle: 4-DB split (validation.db merged into ERP.db). - component_library.db = product LOD catalog (M_Product, geometry, orientation) - ERP.db = discipline metadata + compliance rules (schedules, types, placement rules, AD_Val_Rule, AD_Clash_Rule, AD_Occupancy_Class) — see DISC_VALIDATION_DB_SRS.md - {PREFIX}_BOM.db = per-building spatial arrangement (m_bom + m_bom_line with dx/dy/dz) - output.db = transactional (fresh each compile)

At compile time, run_RosettaStones.sh creates library/_SH_compile.db (or _DX_compile.db) — a temp copy of {PREFIX}_BOM.db enriched with shared schema + C_DocType. Java reads via -Dbom.db=library/_SH_compile.db. Note: M_Product is transitionally copied to BOM DB for BOMWalker; target: read from library only.

Note: Discipline metadata migration complete (session 41). All discipline tables (ad_space_type, ad_element_mep, ad_wall_face, placement_rules, etc.) now live exclusively in ERP.db. Java code (MEPAD, MEPBOMResolver, ManifestResolver, CalibrationDAO) reads from ERP.db. component_library.db reduced from 81→21 tables (LOD catalog only). See DISC_VALIDATION_DB_SRS.md §6.

---

1. Extraction Pipeline — I_Element_Extraction → m_bom + m_bom_line

1.1 Data Source

I_Element_Extraction in component_library.db is the authoritative extraction archive. Each row is one IFC element with its world-space AABB (min_x/max_x, min_y/max_y, min_z/max_z) and a resolved M_Product_ID linking to the product catalog.

Building	building_type	Type
Sample House	SampleHouse	Single-storey residential
Duplex	Duplex	Paired duplex, 2-storey

Element counts and storey breakdowns: see PROGRESS.md gate table.

1.2 Tack Convention

See BOMBasedCompilation.md §4 for the full specification.

Every m_bom_line carries (dx, dy, dz) — the 3D position within the parent where the child's LBD corner sits. LBD = Left-Back-Down = (minX, minY, minZ). Centroid is never stored in the BOM — it is computed at the output stage only.

World coordinate reconstruction (all 3 axes accumulated):

element_LBD = building_origin + tack_from[1] + tack_from[2] + ... + tack_from[N]
centroid    = element_LBD + (width/2, depth/2, height/2)

Invariant: All dx >= 0, dy >= 0, dz >= 0. A child's LBD is always to the right/front/above its parent's LBD.

1.3 Segment-to-Floor BOM Mapping

Each distinct segment in I_Element_Extraction maps to one FLOOR-type m_bom header. For buildings, a segment is a storey (IfcBuildingStorey). For infrastructure, a segment is a facility part (IfcBridgePart, IfcRoadPart, IfcRailwayPart). The storey column in elements_meta stores the segment name regardless of domain. See InfrastructureAnalysis.md §4 for the hierarchy mapping.

SH (SampleHouse):

Storey	Floor BOM ID	bom_category	Elements
Ground Floor	SH_GF_STR	GF	27
Roof	SH_ROOF_STR	RF	2
Unknown	SH_CW_STR	CW	26

DX (Duplex):

Storey	Floor BOM ID	bom_category	Elements
Level 1	DX_L1_STR	L1	571
Level 2	DX_L2_STR	L2	485
Roof	DX_ROOF_STR	RF	11
T/FDN	DX_FDN_STR	FN	7
Unknown	DX_MISC_STR	MS	25

1.4 BUILDING BOM Structure

Each extracted building produces one BUILDING-type m_bom with:

Field	Value	Source
bom_id	BUILDING_SH_STD / BUILDING_DX_STD	Convention
bom_type	—	Root = BOM with no parent m_bom_line. Tier = M_Product_Category. See DISC_VALIDATION_DB_SRS.md §10.4.5
m_product_category_id	RE / CO / IN	Top-level M_Product_Category — classification axis
origin_x/y/z	Building world LBD (only BUILDING BOM); (0,0,0) for children — see §4 in BOMBasedCompilation.md	Extraction min corner
aabb_width/depth/height_mm	Building envelope	(max - min) × 1000
entity_type	D	Dictionary (read-only)

World position is derived from M_BOM_Line dx/dy/dz tack offsets (the BOM itself carries WHERE). {PREFIX}_BOM.db is a pure dictionary — no world coords. co_empty_space tables were removed S74 (W008) — placement is via M_BOM_Line dx/dy/dz directly.

Children: Each child BOM is linked via a line carrying the tack offset: dx = position where child's LBD sits within parent (always >= 0, BOMBasedCompilation.md §4). Child BOMs have origin=(0,0,0) — only BUILDING carries world origin.

Element lines within each FLOOR/DISCIPLINE BOM:

Field	Source
child_product_id	I_Element_Extraction.M_Product_ID
role	I_Element_Extraction.ifc_class
dx/dy/dz	tack offset (child LBD position within parent, §4) from parent BOM (BOMBasedCompilation.md §4)
allocated_width/depth/height_mm	Element AABB × 1000

Instance metadata (storey, element_ref, ordinal, orientation, material_name, material_rgba) is not stored in {PREFIX}_BOM.db dictionary. It remains in I_Element_Extraction (component_library.db) and is written to output.db at compile time.

Recipe vs Placement: Each m_bom_line is a type line — one row per unique product type within its parent BOM. For formula-driven elements (TILE, ROUTE, WIRE), a single type line carries qty=N and the compiler expands to N placement instances. For irregular elements, qty=1 (one line = one instance). In a properly factored BOM, COUNT(m_bom_line) << element count. See BOMBasedCompilation.md §2.1.6 for the full recipe-vs-placement contract.

TE factorization DONE (sessions 8-11): TE_BOM.db stores 1,131 factored recipe lines (505 products × verb formulas) expanding to 48,428 placement instances at compile time. CLUSTER/TILE/FRAME/ROUTE verbs encode 97.6% of the BOM. See TerminalAnalysis.md §BOM Factorization for details.

1.5 Integrity Hash

SHA-256 of sorted (bom_id, child_product_id, round(dx,6), round(dy,6), round(dz,6)) for all extracted BOM lines. Stored in ad_sysconfig(config_key='RSTB_INTEGRITY_HASH'). Detects external tampering of m_bom_line data.

1.6 Per-Building BOM Population — Reproducible from Fresh

Each building has its own BOM dictionary, regenerated deterministically:

SH / DX (IFCtoBOM Java pipeline — current):

./scripts/run_RosettaStones.sh classify_sh.yaml   # → SH_BOM.db
./scripts/run_RosettaStones.sh classify_dx.yaml   # → DX_BOM.db

The IFCtoBOM pipeline (IFCtoBOMPipeline.java) — see WorkOrderGuide.md §Step 5 for full table: 1. Load classification YAML (Order input) + read IFC extraction to I_Element_Extraction (ExtractionPopulator) 2. Pre-flight: Auto-discover spatial containers from extraction (YAML override if present); FAIL on NULL M_Product_ID 3. ProductRegistrar.ensureProductCatalog(): M_Product → component_library.db (persistent, reusable) 4. ProductRegistrar.ensureProductImages(): geometry link (INSERT OR IGNORE) 5. Pre-flight: FAIL if any product has no geometry_hash 6. ProductRegistrar.ensureProducts(): copy to BOM DB (transitional for BOMWalker) 7. ScopeBomBuilder / CompositionBomBuilder / StructuralBomBuilder / FloorRoomBomBuilder 8. BomValidator: 9 check methods pre-commit (counts, normalization, offsets, AABB, tack I/O, element refs, product normalization, extraction reconciliation — any FAIL = rollback) 9. IntegrityHash: computes SHA-256 fingerprint (§1.5)

At compile time: run_RosettaStones.sh creates library/_SH_compile.db (or _DX_compile.db) — a temp copy of {PREFIX}_BOM.db enriched with library/schema_snapshot_bom.sql + C_DocType rows. Java reads via -Dbom.db=library/_SH_compile.db (System.getProperty("bom.db")) — no hardcoded paths. Compile DBs are auto-cleaned; only {PREFIX}_BOM.db persists.

Migrations: migration/archive/*.sql — historical only, never executed. migration/migration_P02_SH_product_link.sql — dead code (replaced by ExtractionPopulator.java).

---

2. {PREFIX}_BOM.db — Schema Reference

Read-only at compile time. Each per-building dictionary contains domain config, BOM hierarchy, and product catalog.

C_DocType (6 rows — domain config)

"Construction Order" — one document type. Classification lives on M_Product_Category, not here.

RESOLVED (session 30, R27): IFCtoBOM now writes C_DocType into {PREFIX}_BOM.db during extraction. Shell injection removed from run_RosettaStones.sh. DSL content read from YAML-adjacent DSL file. StubDataSeeder workaround retained for unit tests (in-memory DBs have no BOM pipeline).

Column	Type	Notes
C_DocType_ID	INTEGER PK	Opaque. Value = RE_SH, RE_DX, CO_TE, etc. See §8 PK convention
Name	TEXT	Display name
DocBaseType	TEXT	Domain classification (RE/CO/IN/ST)
DocSubType	TEXT	Building prefix (SH/DX/TE)
ProjectName	TEXT	Building instance name
DSLContent	TEXT	DSL template text
OutputDbPath	TEXT	Output DB path
ReferenceDbPath	TEXT	Reference DB path
ExpectedElements	INTEGER	Standard element count
Provenance	TEXT	EXTRACTED / GENERATIVE
AabbWidthMm, AabbDepthMm, AabbHeightMm	REAL	Standard domain AABB
IsActive	INTEGER	Active flag

m_bom (BOM headers)

2 selection dimensions: M_Product_Category (substitution shelf) + AD_Org (discipline). AABB is spatial qualification only — envelope fit, not selection.

Column	Type	Notes
M_BOM_ID	INTEGER PK	Opaque. Value = BUILDING_SH_STD, SH_GF_STR, BED_SET. See §8 PK convention
bom_name	TEXT	Display name
bom_type	TEXT	Legacy tier label. Root is identified by having no parent m_bom_line. Tier selection uses M_Product_Category. See DISC_VALIDATION_DB_SRS.md §6.5
bom_category	TEXT	Functional role — FK → M_Product_Category
m_product_category_id	INTEGER FK	Domain classification (RE/CO/IN) — see M_Product_Category
origin_x, origin_y, origin_z	REAL	BUILDING BOM: world LBD; all others: (0,0,0). See BOMBasedCompilation.md §4.
aabb_width_mm, aabb_depth_mm, aabb_height_mm	INTEGER	Envelope dimensions
group_by	TEXT	Grouping key
entity_type	TEXT	D=Dictionary, U=User, A=Application
is_active	INTEGER

m_bom_line (BOM children — recipe type lines, NOT instance placements)

Each row is a type line: one unique product within its parent BOM. The compiler expands type lines into placement instances via verb formulas (TILE, ROUTE, FRAME) or flat qty. Instance-level data (per-element coordinates) belongs in output.db. The walker decides BOM-vs-leaf by whether child_product_id resolves to an m_bom row — component_type plays no role (BOMBasedCompilation.md §2.2.1).

Column	Type	Notes
bom_child_id	INTEGER PK	Auto-increment
bom_id	TEXT FK	→ m_bom.bom_id
child_product_id	TEXT	→ M_Product or m_bom.bom_id (walker decides by existence)
component_type	TEXT	iDempiere compat only — compilation ignores this column
role	TEXT	IFC class (extracted) or functional role
dx, dy, dz	REAL	tack offset (child LBD position within parent, §4) from parent BOM (metres, BOMBasedCompilation.md §4)
allocated_width_mm, allocated_depth_mm, allocated_height_mm	INTEGER	Per-instance AABB (mm)
storey	TEXT	NULL in {PREFIX}_BOM.db dictionary — output.db only (from I_Element_Extraction)
element_ref	TEXT	NULL in {PREFIX}_BOM.db dictionary — output.db only (from I_Element_Extraction)
ordinal	INTEGER	NULL in {PREFIX}_BOM.db dictionary — output.db only (from I_Element_Extraction)
orientation	TEXT	NULL in {PREFIX}_BOM.db dictionary — output.db only (from I_Element_Extraction)
material_name, material_rgba	TEXT	NULL in {PREFIX}_BOM.db dictionary — material lives on M_Product
entity_type	TEXT	D=Dictionary
sequence	INTEGER	Sort order
is_active	INTEGER
rotation_rule	TEXT	Rotation encoding
fit_priority	INTEGER	Tiebreaker (default=20)

M_Product (transitional copy — master in component_library.db §3)

Each distinct M_Product_ID in I_Element_Extraction becomes an M_Product row. BOM assembly stubs (MAKE references) get sentinel dims (0.001). This is a transitional copy. The master catalog lives in component_library.db (see §3 M_Product). Target: BOMWalker reads from component_library.db directly.

Column	Type	Notes
M_Product_ID	INTEGER PK	Opaque. Value = product identifier (e.g. SH_WALL_001). See §8
product_type	TEXT	DOOR/WALL/FURNITURE/SET/FLOOR/etc.
width, depth, height	REAL	Dimensions (metres)
ifc_class	TEXT	IFC entity type
extracted_from	TEXT	Source building
material_name, material_rgba	TEXT	Catalog material
M_Product_Category_ID	INTEGER FK	→ M_Product_Category. See §8 PK convention
cost_uom	TEXT	Trade UOM — see table below. Drives 5D cost (qty × unit_cost). Future: C_UOM_ID INTEGER FK
is_active	INTEGER

cost_uom values (trade unit of measure per product type):

UOM	Meaning	Used For	Standard
EA	Each (discrete item)	Fittings, terminals, valves, doors, furniture, alarms	—
M	Linear meter	Pipe segments, duct segments, beams, columns, rails	—
M2	Square meter	Walls, slabs, roofs, coverings, masonry courses	—
M3	Cubic meter	Concrete (footings, earthworks fill) only	—
KG	Kilogram	Reinforcing bar (rebar)	PWD 203A, NRM2, AIQS

See DISC_VALIDATION_DB_SRS §10.4.11 T3.5 for the DV029 migration that corrects MEP/rebar/covering UOMs from extraction defaults.

M_Product_Category (flat classification — iDempiere standard)

M_Product_Category is FLAT. No parent FK. No tree. This follows iDempiere standard where M_Product_Category is a simple grouping tag. The building→floor→room→leaf cascade is expressed by the BOM tree (M_BOM → M_BOM_Line parent-child relationships), not by the category table. To enforce swap-pool constraints, match m_product_category_id at the same BOM level — you never need to traverse a category tree.

Note: Parent_Category_ID is legacy — the BOM cascade already expresses hierarchy through M_BOM_Line parent-child relationships.

127 rows total (DV018 hierarchy + DV031 disciplines + DV027 PK migration). AD_Org_ID column (DV036) wires M_Product → M_Product_Category → AD_Org_ID per DISC_VALIDATION_DB_SRS.md §6.4. Representative categories:

Level	M_Product_Category_ID	Name	Example Buildings
Top-level	RE, CO, IN, IP	Residential, Commercial, Infrastructure, Industrial Plant	SH, DX, TE, BR
Floor	GF, L1, L2, L3, RF, FN, MS	Ground Floor, Level 1–3, Roof, Foundation, Misc	All buildings
Room (RE)	LIVING, KITCHEN, BEDROOM, BATHROOM, DINING, MASTER, CORRIDOR	Room types — swap pool at room level	SH, DM, DX, FK, AC
Discipline (CO)	ARC, STR, FP, ELEC, ACMV, CW, SP, LPG	Discipline types — swap pool at discipline level	TE, WA, WL, WT
Infra segment	SUP, DCK, ABT, TRK, ROAD, RAIL, GEO	Segment types	BR, RD, RL
Structural	PAIR, HALF_UNIT, SLAB, PORCH	Assembly containers	DX

Full list: migration/DV018_product_category_cascade.sql

---

3. component_library.db — Product LOD Catalog + Geometry

Source of truth for product definitions, geometry, orientation, and extraction archive. M_Product is the persistent product catalog (created by ProductRegistrar.ensureProductCatalog(), INSERT OR IGNORE = reused across buildings). M_Product_Image links products to geometry. See WorkOrderGuide.md §"Drift Prevention" for enforced guards.

Discipline metadata (ad_space_type, ad_element_mep, ad_wall_face, placement_rules, etc.) lives in ERP.db — see DISC_VALIDATION_DB_SRS.md.

I_Element_Extraction (IFC extraction archive)

The source of truth for extracted building element positions. Read by the IFCtoBOM Java pipeline (SH/DX) or RosettaStoneToBOM.py (TE legacy) to generate m_bom + m_bom_line.

Column	Type	Notes
placement_id	INTEGER PK
building_type	TEXT	SampleHouse / Duplex
storey	TEXT	Storey name
ifc_class	TEXT	IFC entity type
element_ref	TEXT	Element reference
ordinal	INTEGER	Position order
min_x, max_x, min_y, max_y, min_z, max_z	REAL	World-space AABB
orientation	TEXT	NS/EW/POINT
material_name, material_rgba	TEXT	Material
M_Product_ID	TEXT	FK → M_Product (same DB — master catalog)

M_Product (persistent product catalog)

Master product catalog. Created by ProductRegistrar.ensureProductCatalog() from extraction data. INSERT OR IGNORE = products reused across buildings. Transitionally copied to {PREFIX}_BOM.db for BOMWalker compatibility.

Column	Type	Notes
M_Product_ID	INTEGER PK	Value = element_ref (deterministic). See §8
product_type	TEXT	Derived from IFC class (WALL, DOOR, etc.)
width, depth, height	REAL	Intrinsic dimensions in metres
ifc_class	TEXT	Source IFC entity type
extracted_from	TEXT	Always 'IFC_EXTRACTION'
building_type	TEXT	Source building

M_Product_Image (product → geometry mapping)

Auto-created by ProductRegistrar.ensureProductImages() — deterministic join of I_Element_Extraction × I_Geometry_Map. INSERT OR IGNORE. Never manually migrated.

Column	Type	Notes
M_Product_ID	TEXT	FK → M_Product (same DB)
geometry_hash	TEXT	FK → LOD_Object

LOD_Object (canonical meshes)

Column	Type	Notes
geometry_hash	TEXT PK	SHA-256 of geometry
vertices	BLOB	Vertex data (read only by bake_library_blend.py, never at runtime)
faces	BLOB	Face indices (read only by bake_library_blend.py, never at runtime)
vertex_count, face_count	INTEGER

S173 note: BLOBs are consumed once by the bake script to produce library/library.blend. At runtime, meshes are linked from library.blend — no BLOB reads, no from_pydata(). Extracted DBs are meshless (NULL BLOBs) to keep file size small (e.g. Hospital: 8MB vs 232MB with BLOBs).

---

4. output.db — Transactional (Fresh Each Compile)

Created by CompilationPipeline. C_Order created from C_DocType at compile time.

Coordinate Flow (compilation)

{PREFIX}_BOM.db                       component_library.db
  m_bom.origin_x/y/z  ──┐              M_Product_Image ──→ MeshBinder
  m_bom_line.dx/dy/dz ──┤
  m_bom_line.allocated ──┤
                         ↓
              PlacementCollectorVisitor
                anchor = origin + Σ(line.dx + child.origin)
                cx = anchor + dx
                minX = cx - halfW
                         ↓
              output.db
                elements_meta + elements_rtree (world AABB)
                c_orderline (WHAT)
                (co_empty_space removed S74 — WHERE = M_BOM_Line dx/dy/dz)
                W_Verb_Node (HOW)

Key Tables

Table	Purpose
c_order	Building instance (from C_DocType)
c_orderline	Element instances (from BOM explosion)
elements_meta	Element metadata (guid, ifc_class, storey)
elements_rtree	Spatial index (world AABB)
building_bbox	S200: Pre-computed building×discipline bbox summary for instant Preview
~~co_empty_space~~	(removed S74 — W008)
~~co_empty_space_line~~	(removed S74 — W008)
W_Verb_Node	Verb execution audit trail
base_geometries	Geometry meshes (copied from component_library) — see SQLite3D_Schema.md §BLOB Encoding
element_instances	Element → geometry mapping

Why a Hospital element may reference a Terminal geometry_hash — BlendMeshResolver

element_instances.geometry_hash points to the canonical mesh for that element type, which may originate from a different building. This is intentional — not a data error.

At build time, scripts/blend_mesh_resolver.py reads redirect rules from component_library.db → geometry_hash_redirect and replaces low-quality deprecated hashes with canonical high-quality hashes (e.g. Terminal JKR sprinkler mesh replaces Revit generic). The building ownership (elements_meta.building) is never changed — only the mesh pointer.

Active redirects as of 2026-04-13 (sprinkler family):

Deprecated hash	Verts	Source building	→ Canonical hash	Verts	Sub-type	Reason
bae71afd973eed3a	72	Hospital, WBDG	0d509e532be0f5f2	1996	pendent	Revit generic → JKR Terminal
bd5df7dd600f7582	72	Hospital	0d509e532be0f5f2	1996	pendent	Revit generic → JKR Terminal
a11f25b406f779a8	72	HHS_Office_Federated	0d509e532be0f5f2	1996	pendent	Revit generic → JKR Terminal
389dd0da96979230	72	HHS_Office_MEP	0d509e532be0f5f2	1996	pendent	Revit generic → JKR Terminal
5d058cfe6e236b89	72	Ifc4_Revit	0d509e532be0f5f2	1996	pendent	Revit generic → JKR Terminal
92605cd3f82bcf3d	72	WBDG_Office_MEP	0d509e532be0f5f2	1996	pendent	Revit generic → JKR Terminal
f3b8de02e5e03caa	1996	Terminal (duplicate family)	0d509e532be0f5f2	1996	pendent	Lower-instance Terminal variant → primary
49f8fcde5a3bb02e	2002	Terminal (duplicate family)	795e6eb5665d5b31	2002	upright	Lower-instance Terminal variant → primary

Canonical mesh 0d509e532be0f5f2 = JKR ME18 sprinkler head pendent (Terminal, 565 instances, 1996 verts) Canonical mesh 795e6eb5665d5b31 = JKR ME18 sprinkler head upright (Terminal, 175 instances, 2002 verts)

Rule store: component_library.db → geometry_hash_redirect Admin tool: tools/geometry_redirect.py Spec: internal/BlendMeshResolver.md

Rotation Correction — diagnosing and fixing orientation mismatches

When a deprecated mesh is replaced by a canonical mesh, the element's stored rotation_x/y/z (in element_transforms) may no longer be correct. Different IFC families model the same object with different local-axis conventions — e.g. Revit sprinklers are modeled inverted (Z-down) with rotation_x = π to flip them right-side up, while Terminal JKR sprinklers are Z-up with rotation_x = 0.

How to diagnose:

1. Load a building with redirected elements via RTree +MESH
2. If elements appear upside-down or misaligned in their bounding boxes:
3. Query the rotation pattern of affected elements vs the canonical source:

   -- Affected building (e.g. Hospital):
   SELECT round(t.rotation_x,2), round(t.rotation_y,2), round(t.rotation_z,2), COUNT(*)
   FROM elements_meta m JOIN element_instances i ON m.guid = i.guid
   JOIN element_transforms t ON m.guid = t.guid
   WHERE lower(m.element_name) LIKE '%sprinkler%'
   GROUP BY round(t.rotation_x,2), round(t.rotation_y,2), round(t.rotation_z,2);
   
   -- Repeat for the canonical source building (e.g. Terminal)
   

4. The difference in rotation between the two is the correction needed. Example: Hospital has rotation_x = π, Terminal has rotation_x = 0. Correction = rotation_x_correction = -π (cancels the π flip).

How to apply:

-- Add correction columns (one-time, already done):
ALTER TABLE geometry_hash_redirect ADD COLUMN rotation_x_correction REAL DEFAULT 0.0;
ALTER TABLE geometry_hash_redirect ADD COLUMN rotation_y_correction REAL DEFAULT 0.0;
ALTER TABLE geometry_hash_redirect ADD COLUMN rotation_z_correction REAL DEFAULT 0.0;

-- Set correction for affected redirects:
UPDATE geometry_hash_redirect
SET rotation_x_correction = -3.14159265358979
WHERE deprecated_hash IN ('bae71afd973eed3a', 'bd5df7dd600f7582', ...);

The stingy loader (FedRTreeLoadMesh) reads the correction at load time and applies it only when the element's stored rotation matches the deprecated pattern (within 0.01 rad). Elements already at correct orientation are untouched. Extracted DBs are never modified — they stay faithful to the source IFC.

---

5. Cross-DB FK Map

output.db Column	References	Target DB
c_order.C_DocType_ID	C_DocType.C_DocType_ID	{PREFIX}_BOM.db
c_orderline.M_Product_ID	M_Product.product_id	{PREFIX}_BOM.db (transitional copy; master in component_library.db)
element_instances.geometry_hash	LOD_Object.geometry_hash	component_library.db
~~co_empty_space_line.bom_id~~	(removed S74 — W008)	—

---

6. AD Data Placement — The 4-DB Architecture

6.1 Current State — Where AD Tables Live

Planned evolution — building → AD_Client (not yet implemented)

The building column in elements_meta (e.g. T0_Hospital, T0_Terminal) is the interim owner discriminator for the city-map / RTree viewer. In iDempiere, this maps to AD_Client — the top-level owner entity (Hospital Authority, KLIA Authority, etc.).

Future: elements_meta.building TEXT → elements_meta.AD_Client_ID INTEGER FK → AD_Client(ID, Value, Name)

AD_Client does NOT conflict with M_Product_Category (RE/CO/IN/IP): - AD_Client = who owns the project (Hospital Trust owns the Hospital building) - M_Product_Category = what type of building (Complex/CO, Residential/RE, Infrastructure/IN) - One client may own projects of different categories (Hospital Trust: Complex + Residential wings)

C_BPartner becomes the manufacturer/supplier (JKR, Victaulic) — not the building owner.

The geometry_hash_redirect table is unaffected by this migration: mesh shape is a library concern, not a client concern. Redirecting bad Hospital sprinkler hashes to the canonical Terminal JKR mesh does not change elements_meta.building = 'T0_Hospital' — ownership is intact.

The migration is mechanical: building value equals AD_Client.Value verbatim. No data transform needed.

Table	ERP.db	component_library.db	{PREFIX}_BOM.db	Purpose
AD_Org	16 rows	—	—	Discipline definitions (ARC, STR, FP...)
M_Product_Category	IFC→discipline map	IFC→discipline map	0 rows (DM only)	Product classification
AD_SysConfig	8 rows (schema versions)	—	2 rows (integrity hash, expected elements)	Different schema, different purpose
ad_val_rule + param (mined)	415 + 1,245 rows	—	—	Mined dimension ranges
AD_Val_Rule + Param (compliance)	63 + authored	—	—	Compliance rules (merged into ERP.db)
C_DocType	—	—	1 row per building	Building type classification
M_Product	2,477 rows	2,475 rows (master)	2–93 rows (transitional copy)	Product catalog
M_Attribute*	—	—	per-building	Instance customization
m_bom + m_bom_line	—	—	per-building	Spatial BOM (the core per-building data)

6.2 Duplication Analysis

M_Product is the most duplicated table: - component_library.db is the master catalog (created by ProductRegistrar.ensureProductCatalog()) - ERP.db has a near-identical copy (2,477 vs 2,475) used for validation queries - {PREFIX}_BOM.db has small transitional copies for BOMWalker compatibility (target: remove)

M_Product_Category exists in both ERP.db and component_library.db with identical IFC class mappings. Only one authoritative copy is needed.

AD_SysConfig is NOT truly duplicated — different schemas serve different purposes: - ERP.db: Name/Value pairs tracking schema migration versions (DV001–DV015) - BOM databases: config_key/config_value pairs tracking per-building integrity hashes

C_DocType lives only in BOM databases (1 row each, e.g., RE_SH, RE_DX). In iDempiere, C_DocType is shared system configuration — it defines all document types centrally. Currently written by IFCtoBOMPipeline per-building; at compile time, schema_snapshot_bom.sql enriches the compile DB with additional C_DocType rows.

6.3 The 4-DB Split

In iDempiere, AD tables (Application Dictionary) are shared across all organisations. They live once, centrally — not per-product or per-order. ERP.db serves this role (renamed from disc_validation.db in S76; validation.db merged in — all compliance rules now live in ERP.db alongside mined dimension rules).

Note: ERP.db contains two sets of ad_val_rule / ad_val_rule_param rows with different provenance — mined dimension ranges (415 rules) and authored compliance rules (63 rules). Same table, different provenance. Distinguished by source column.

Layer	Database	Contains
ERP shared	ERP.db	AD_Org, M_Product_Category, C_DocType (master), AD_SysConfig (schema versions), ad_val_rule (mined + compliance), AD_Clash_Rule, AD_Occupancy_Class, all ad_* discipline metadata
Product catalog	component_library.db	M_Product (master), M_Product_Image, LOD_Object, I_Element_Extraction, geometry
Per-building BOM	{PREFIX}_BOM.db	m_bom, m_bom_line, M_Attribute*, ad_sysconfig (per-building integrity), C_DocType (compile-time copy)
Transactional	output.db	c_order, c_orderline, elements_meta, W_Verb_Node

Remaining consolidation targets: - M_Product_Category: single authoritative copy in ERP.db (currently also in component_library.db) - M_Product in ERP.db: evaluate whether validation queries can read from component_library.db directly - BOM databases keep transitional M_Product copies until BOMWalker migration completes

See MANIFESTO.md §Three Concerns for the WHAT/HOW/WHERE rationale behind this split.

---

7. DocBaseType → M_Product_Category Migration

Status: Steps 1–7 DONE. Classification lives on m_product_category_id at every BOM level. doc_base_type removed (S84, W012). doc_sub_type removed — prefix on bom_id carries this. AD_Org_ID replaces bom_category strings for discipline routing. C_DocType = ONE "Construction Order".

7.1 What Was Done

• S75: M_Product_Category DV018 migration (117 rows), BUILDING BOM backfill
• S76: disc_validation.db renamed to ERP.db (~100 files)
• S77: Java routing migrated to m_product_category_id (19 source + 12 test files)
• S78: AD_Org_ID FK on m_bom, C_OrderLine (W009 migration)
• S79: Discipline enum replaces deriveDiscipline() in compile path

---

8. iDempiere PK Convention

Every master table follows the iDempiere TableName_ID / Value / Name convention:

• TableName_ID — INTEGER PRIMARY KEY AUTOINCREMENT. Opaque surrogate key. Never shown to users, never used in business logic.
• Value — TEXT NOT NULL UNIQUE. The search key (what was previously the TEXT PK). Lookups by business key use WHERE Value = ?.
• Name — TEXT NOT NULL. Display name for UI and reports.
• Foreign keys always reference _ID, never Value. Joins are integer comparisons.

This is standard iDempiere convention: every X_ generated class has getTableName_ID(), getValue(), getName(). The migration from TEXT PKs to INTEGER PKs preserves the old TEXT identifier as Value.

8.1 Migration Status

Table	Status	Since
M_Product	INTEGER PK	S91
C_Order	INTEGER PK	S91
C_DocType	INTEGER PK	S91
AD_Org	INTEGER PK (manual)	pre-S90
ad_val_rule	INTEGER PK	S90
M_BOM (ERP.db)	INTEGER PK	DV025
m_bom (BOM.db)	INTEGER PK	S100-p86 Phase A
M_Product_Category	INTEGER PK	Phase B
13 AD tables	INTEGER PK	Phase C

8.2 Convention Rules

1. New tables must use TableName_ID INTEGER PRIMARY KEY AUTOINCREMENT from creation. No TEXT PKs for new tables.
2. Existing tables migrated in phases: Phase A (m_bom), Phase B (M_Product_Category), Phase C (remaining AD tables).
3. _int sidecar columns (transitional pattern from S90-S92) are dropped as each table completes migration. Phase D dropped M_Product_Category_ID_int (S92).
4. loadByValue(String) on BasePO enables WHERE Value = ? lookups — replaces the old TEXT PK pattern without changing caller semantics.

AD_Org unifies discipline identifiers from TEXT strings to integer FK. See DISC_VALIDATION_DB_SRS.md §6.

---

Authoritative reference. See BOMBasedCompilation.md for tack convention and pipeline stages, MANIFESTO.md for the three-concern model (WHAT/HOW/WHERE), TheRosettaStoneStrategy.md for verification strategy, StrategicIndustryPositioning.md for competitive landscape and scorecard.

Copyright (c) 2025-2026 Redhuan D. Oon. MIT Licensed.