Section 16: Production, Workflow & Scoring Tables

The primary output table for the casualty data production workflow. The spProduction_Casualties stored procedure (Section 12e) reads from the ABSD_ casualty tables (ABSD_CADI, ABSD_CAGE1, etc.) and writes a denormalised, production-ready version of each casualty event to this table. Downstream products and publications read from here rather than directly from the ABSD_ source tables.

Production Table Family (23 tables)

Beyond casualty output, the Production family covers multiple analytical outputs:

A generic Entity-Attribute-Value (EAV) extension table for storing vessel-level data fields that do not fit the fixed ABSD_ schema. When a new data requirement emerges that does not warrant a permanent schema change, the field is stored here using the FIELD_NAME + value columns pattern. The codebook for all valid field names is in tblFlexible_Bucket_CB.

Codebook defining all valid FIELD_NAME values for the flexible bucket. Each row defines one attribute type — its human-readable description, data type, visibility flag, and uniqueness constraint.

Defines the 5-level ship type classification hierarchy used across LR products. Level 1 is the broadest category (e.g. Cargo Carrying); Level 5 is the most granular (e.g. Crude Oil Tanker > 250,000 DWT). Each row is one Level 5 leaf node with its full ancestry chain.

Hierarchy Example

The master trigger guard table. Every single trigger in the database checks this table at line 1 — if any row exists, the trigger exits immediately without executing. This single-row pattern disables all 282 triggers simultaneously, enabling bulk data loads, maintenance operations, and SSIS backfill jobs to run without cascading trigger side-effects.

Standard Guard Pattern (in every trigger)

-- First line of every trigger body
IF (SELECT COUNT(*) FROM TRIGGER_DISABLE) > 0
    RETURN

-- Table-specific guard (prevents recursion within the same trigger)
IF (SELECT COUNT(*) FROM TRIGGER_DISABLE_OVGE) > 0
    RETURN

In addition to the global TRIGGER_DISABLE, table-specific guard tables prevent trigger recursion when a trigger on table A writes to table B, which itself has a trigger. The pattern is identical: one-column table, presence = disabled.

Stores the raw indicator values for each risk dimension — the actual measurements from AIS data, PSC inspection records, class status, and flag performance. One row per vessel (LRNO = PK). Updated by the spRiskScreeningDataUpdate scheduled job.

AIS Behaviour Dimensions

Flag & PSC Dimensions

Class & Certification Dimensions

Parallel table to tblRiskScreeningData — same column names but values are computed risk scores (integer 0–100) rather than raw measurements. Each dimension's raw value is converted to a 0–100 risk score using a configurable scoring matrix. The overall risk score is a weighted sum of dimension scores.

Schema: identical column names to tblRiskScreeningData, suffixed with _Score for clarity in stored procedures. LRNO is PK. All score columns are int (0–100), with 0=no risk, 100=highest risk.

Third parallel table — same dimensions as tblRiskScreeningData but values are short human-readable narrative strings (varchar(100)) explaining each dimension's contribution to the risk profile. Used by client-facing risk report generators.

Example values: AISLastSeen_Narrative = 'Last seen 14 days ago', Detentions_Narrative = '3 detentions in past 3 years', Flag_Narrative = 'Flag classified as High Risk by Paris MOU'.

Per-vessel sustainability and environmental performance indicators. One row per vessel (LRNO = PK). Populated by the spSustainabilityDataUpdate scheduled job from ABSD_ technical data, AIS data, and third-party environmental certifications.

Parallel narrative table for tblSustainabilityData. Same column names with varchar(100) narrative strings per dimension. Used by sustainability report generators and ESG data product feeds.

Example: CO2Emissions_Narrative = 'Estimated 12,400 tonnes CO₂/year — above average for ship type', TechnicalEfficiency_Narrative = 'EEDI Phase 0 vessel — pre-regulation build'.

Audit log for the VDM (Vessel Data Model) — LR's internal schema governance system. The VDM defines the authoritative field list and data dictionary for the vessel record. When a VDM-managed field's schema definition changes (new field, field rename, type change, deprecation), the change is logged here.