Fake EDW schema reference

This page is the SAIR project contract. It lives under docs/projects/sair/ so a later project mock can follow the same pattern: docs/projects/<project>/.

The contract is invented in this repo. Real customer Ellipse/EDW DDL is operator-supplied and is not here. Column names follow what NeuralOps SAIR workflows consume (site, plant_no, EQUIP_NO / equipment, work_order, actual_finish, …).

How the tables and views are applied

sql-estate/databases/edw/02_schema.sql is the script that creates the base tables (CREATE TABLE, guarded by IF NOT EXISTS) and the seven views (CREATE OR ALTER VIEW). 01_create_database.sql creates the edw database when it is missing. 03_seed.sql MERGEs rows back to the git baseline.

sql-estate/seed/apply.sh runs those scripts in lexical order:

• Local: docker compose up -d --wait. The sql-estate-seed sidecar runs apply.sh after SQL Server is healthy. Re-running is safe; the scripts are idempotent.
• Azure: Bicep creates the database during operator azd up. Postprovision, and .github/workflows/azure-estate.yml on merge to main (or workflow_dispatch on main), run the same apply.sh with SQLCMD_AUTH=aad and SQLCMD_SKIP_CREATE_DATABASE=1. That skips 01_create_database.sql and re-applies 02_schema.sql and 03_seed.sql. A docs-only merge does not seed.

Scripts directory: sql-estate/databases/edw/.

QDox correlation: vw_WorkOrder_Enriched.work_order and .EQUIP_NO match WorkOrder / equipment on catalogue rows in fake-host/app/personalities/qdox/fixtures/catalogue.json. equipment is never sourced from QDox (NeuralOps QDoxClient contract); it always comes from this SQL row.

Keys

Every view is filterable by (site, plant_no) — the SAIR run inputs. Equipment identity is EQUIP_NO (Ellipse-style), with (site, plant_no, EQUIP_NO) as the asset key. equipment is a view alias of EQUIP_NO for consumers that SELECT site, plant_no, equipment, work_order.

SQL Server's default CI collation treats EQUIP_NO and equip_no as the same identifier, so views do not duplicate case-folded names. Python clients that lowercase keys still match EQUIP_NO → equip_no.

Site and plant hierarchy (seed)

The estate uses site SA001, two-segment process scope SA001-0100, three-segment pump type scope SA001-0100-PMP, and four-segment asset slots. PLANT_NO_BASE stores the row's canonical full slot; family scopes are derived, not stored as alternate subject identifiers.

The subject slot has exactly one IS occupant (71689591009919). The other workbook equipment identifiers are historical DI, DC, or SP lineage rows. All nine QDox-backed work orders remain attached to their original workbook EQUIP_NO and now share the subject slot.

Three EDW-only rows exercise graceful degradation and family aggregation: EDGE-INSPECT (subject, no document, zero cost), EDGE-SIBLING, and EDGE-VALVE. They do not expand the nine-file QDox corpus.

Views

dbo.vw_WorkOrder_Enriched

Enriched work-order rows for SAIR ingest/report (edw_workorder_enriched, edw_workorder_enriched_family).

dbo.vw_Site_Equipment

Asset register (edw_site_equipment, edw_equipment_family).

dbo.vw_WorkOrder_Cost_Totals

Per-WO cost breakdown (edw_workorder_cost_totals).

dbo.vw_Maintenance_Schedule

Preventive schedules (edw_maintenance_schedule).

dbo.vw_Failure_Codes

Failure-code catalogue crossed onto every (site, plant_no) so plant-scoped probes return rows (edw_failure_codes).

dbo.vw_CrossAsset_Peer_Metrics

Peer stats by EQPT_TYPE attached to each asset. NeuralOps report-gen no longer registers this source (metrics are computed client-side), but the view remains the estate contract so probes and older consumers still work.

dbo.vw_Asset_Attributes

Name/value attributes (edw_asset_attributes).

Probe queries

Candidates for NeuralOps source probe_query (and laptop smoke after azd up). Each must return ≥1 row against the git seed.

-- vw_WorkOrder_Enriched
SELECT TOP (5) site, plant_no, EQUIP_NO, equipment, work_order, actual_finish, cost_total, has_documents
FROM dbo.vw_WorkOrder_Enriched
WHERE site = N'SA001' AND plant_no = N'SA001-0100-PMP-0001';

-- vw_Site_Equipment
SELECT TOP (5) site, plant_no, EQUIP_NO, PARENT_EQUIP, EQPT_TYPE_DESC, EQUIP_STATUS
FROM dbo.vw_Site_Equipment
WHERE site = N'SA001';

-- vw_WorkOrder_Cost_Totals
SELECT TOP (5) site, plant_no, work_order, labour_cost, material_cost, contractor_cost, total_cost
FROM dbo.vw_WorkOrder_Cost_Totals
WHERE total_cost = 0;  -- zero-cost edge case (EDGE-INSPECT)

-- vw_Maintenance_Schedule
SELECT TOP (5) site, plant_no, EQUIP_NO, schedule_id, freq_days, next_due
FROM dbo.vw_Maintenance_Schedule
WHERE site = N'SA001';

-- vw_Failure_Codes
SELECT TOP (5) site, plant_no, failure_code, failure_class
FROM dbo.vw_Failure_Codes
WHERE site = N'SA001' AND plant_no = N'SA001-0100-PMP-0001';

-- vw_CrossAsset_Peer_Metrics
SELECT TOP (5) site, plant_no, EQUIP_NO, EQPT_TYPE, peer_count, avg_cost, mtbf_days
FROM dbo.vw_CrossAsset_Peer_Metrics
WHERE EQPT_TYPE = N'PMP';

-- vw_Asset_Attributes
SELECT TOP (5) site, plant_no, EQUIP_NO, attr_name, attr_value, attr_uom
FROM dbo.vw_Asset_Attributes
WHERE plant_no = N'SA001-0100-PMP-0001';

Local:

docker compose up -d --wait
sqlcmd -S localhost,1433 -U sa -P "CalabMocks!2024" -C -d edw \
  -Q "SELECT work_order, EQUIP_NO, has_documents FROM dbo.vw_WorkOrder_Enriched;"

Azure (Entra token, after azd up + CI seed):

sqlcmd -S "$(azd env get-value SQL_SERVER_FQDN)" -d edw -G -C \
  -Q "SELECT TOP (5) * FROM dbo.vw_WorkOrder_Enriched;"