UNIFIED SOYUZ: Defence Architecture

Food is a factor of social stability.

Objectives:

• ≥ 95% internal support basic products;
• strategic reserve ≥ 12 months;
• autonomy of the seed fund ≥ 80%.

Metrics: Food Autonomy Ratio (KPA).

2.5. Technological value added

The commodity model increases vulnerability. Target vector:

• growth of the share of products with high added value ≥ 50% in the export structure;
• increase in R&D expenditures to ≥ 3% GDP;
• growth in the share of high-tech industries ≥ 25% GDP.

• Center for Strategic Risk Monitoring (quarterly audit).
• Coordinating Council for Technological Autonomy.
• Integration of industrial policy with the educational system.
• Strategic production clusters of the full cycle.
• Long-term investment planning (horizon 10–15 years).

4. Economic model "Defense through development"

Formula:

Economic sustainability = Manufacturing × Technology × Financial autonomy × Personnel

If one element is weakened, the system is vulnerable.

5. Economic Risk Matrix (Enhanced Version)

• 0–24h: criticality inventory, capacity redistribution, line conversion.
• 24–72h: launch of alternative supplies/replacements, accelerated certification.
• up to 30d: deployment of bottlenecks, warehouse reserves, long-term contracts.

KPI

• the time of replacement of the critical component (months);
• the share of localization by cryptic positions (%);
• stock of critical components (meat coating).

9) Education/Personnel/R&D (Engineering circuit)

The threshold of failure: a steady shortage of key specialists or a leakage of competencies.

Cascade: Technology → Industry → Defense.

Recovery plan

• 0–24h: prioritization of key competencies, “personnel register”.
• 24–72h: incentive packages, accelerated retraining programs.
• up to 30d: engineering schools, R&D grants, order for applied development.

KPI

• Engineering/year by priority;
• R&D as a percentage of GDP (%)
• closing time of vacancies in crithsferes (days).

10) Media/Infoenvironment/Cognitive Resilience

The threshold of failure: mass polarization, falling confidence below the threshold or successful information operations that cause management paralysis.

Cascade: social sustainability → public administration → economy.

Recovery plan

• 0–24h: single crisis communication center, quick fact-contour, panic prevention.
• 24–72h: awareness campaigns, media literacy, work with community leaders.
• to 30d: system strategy of semantic safety (education + Culture + Media).

KPI

• the speed of refuting the destructive throw (clock);
• Confidence index for institutions;
• Percentage of the population with basic media literacy (%)

Annex G. “War Room” — single incident headquarters (pattern structure)

Composition:

energy, communications, cyber, finance, logistics, social block, media, industry.

24/7 under stress scenario, weekly exercises in basic mode.

SUSTAINABILITY PASSPORT

(Standard for the Critical System of the Union)

1. Identification of system

• System name:
• Outline (military / technological / economic / cognitive / institutional):
• Criticality level: I/II/III
• Responsible authority/coordinator:

2. Target function

What is the system obliged to provide under any conditions? (formulation in 1–2 lines, without common words)

3. Refusal threshold (red zone)

Specifically:

• Degradation time:
• Scale:
• Loss of functionality (%):

4. Reservations

• Physical reserve (power, warehouses, equipment):
• Information reserve (duplicate channels):
• Personnel reserve:
• Financial reserve:

5. Response Scenario. 0–24 hours

(first action) 24–72 hours

(stabilization) Up to 30 days

(Recovery and Reinforcement)

6. KPI

• Recovery time (RTO):
• Permissible data loss (RPO):
• % of autonomy:
• Frequency of Stress Tests:
• Readiness index (combined):

7. Maturity level

1 — declarative

2 — partially implemented

3 - operating

4 - Regularly tested

5 - Stress-Resistant

Example 1

Sustainability passport — Power system

Outline: economic and technological

Criticality: I

Target function:

Ensuring continuous power supply to critical infrastructure and industry.

Threshold of rejection:

Loss of > 15% power in the region for > 6 hours.

Reservation:

• 20% distributed generation
• 100% Reservation of critical objects
• 30 days of stock key components

KPI:

• Recovery time < 12 hours
• 95% self-powered objects
• 2 stress test per year

Example 2

Sustainability passport — Financial system

Outline: economic

Criticality: I

Target function:

Continuity of calculations and social payments.

Threshold of rejection:

Inability of mass operations > 24 hours.

Reservation:

• Alternative clearing mechanism
• 24 months of coverage of import of critical items
• 2 Independent Data Center

KPI:

• 99% payments on time
• Recovery of mass payments < 6 hours
• Share of autonomous calculations ≥ 70%

Example 3

Sustainability passport — Communication and management

Contour: institutional-military

Criticality: I

Target function:

Guaranteed management at all levels.

Threshold of rejection:

Violation of stable communication > 2 hours.

Reservation:

• Duplicate channels (optics + satellite + radio reserve)
• Standby Control Centers
• Cryptographic double contour

KPI:

• Switching to reserve < 10 minutes
• 100% key nodes with duplication
• Quarterly Exercises

How to use it strategically

If the document is sent officially, I recommend:

• Make 10 passports (on all critical systems).
• Bring them into the summary table “Union Sustainability Index”.
• Establish an annual audit procedure.
• Add the section “dynamics of indicators for the year 3”.

I. CONSOLIDATED UNION SUSTAINABILITY INDEX (SIU)

1. Index Logic

Sustainability is not a sum, but a product of contours.

If one circuit is weak, the whole system is vulnerable.

Formula: 

SIU = (W × T × E × C × I)^{1/5},

Where:

• W - Military Contour
• T - Technological
• E - Economic
• C - Cognitive (social)
• I - Institutional

Each circuit is assessed on a scale of 0–100.

A geometric mean is used so that the weak link reduces the overall indicator.

2. Internal calculation of each contour

Each circuit = is averaged by 5 parameters:

3. The Interpretation Scale

4. Example of calculation (conditional)

W = 78

T = 62

E = 70

C = 65

I = 60

SIU ≈ 66 Conclusion: the system is stable, but the technological and institutional contours require strengthening.

II. ROAD MAP: TRANSITION 2 → 5

Maturity levels

2 — Partially implemented

3 - Operates

4 - Regularly tested

5 - Resistant to stress scenario

STAGE 1 (0–2 years)

Transition 2 → 3. Purpose: Formalize the system.

• Approval of Sustainability Passports.
• Create a single registry of critical dependencies.
• Launch the annual audit SIU.
• Create a risk monitoring center.
• Start the quarterly stress tests.

Result: The system is controlled.

STAGE 2 (2–5 years)

Transition 3 → 4. Objective: To test the system in practice.

• Regular cross-sectoral exercises.
• Duplication of control centers.
• Increase reserves to regulatory levels.
• Localization of 60–75% critical components.
• Increase in R&D ≥ 3% GDP.

The result: the system withstands basic stress.

STAGE 3 (5–10 years)

Transition 4 → 5. Objective: Full strategic autonomy.

• Complete double control circuit.
• 80–90% autonomy of critical industries.
• Resistance to financial isolation.
• High Confidence Index (>75).
• SIU ≥ 80.

The result: Aggression becomes irrational.

III. Control mechanism

Once a year:

• Recount SIU.
• Updated passports.
• Public (or closed) report on the dynamics.
• Adjustment of the road map.

Main

Sustainability is not a report. This is a controlled dynamics of indicators.

PLATFORM "SOYUZ"

Prototype interface (MVP)

1 - Main screen — STRATEGIC DASHBOARD

Central element:

SIU - Consolidated Sustainability Index

Large circular indicator:

• 0–40 red zone
• 40–60 yellow
• 60–75 green
• 75+ dark green

Below it is the dynamics for 12 months.

Right — 5 contours

Click on each - the transition to detail.

2 - Outline Screen (Example: Economic)

Block 1 — KPI in real time

• Industrial autonomy (%)
• Financial sustainability
• Energy balance
• Food index
• Logistic stability

Each with a traffic light.

Block 2 — Risk map

Matrix: Probability × Damage

Risks are automatically highlighted when the threshold is exceeded.

Block 3 — Trends

Schedule 1 / 3 / 5 years

• Forecast with current dynamics.

3 - REQUIREMENTS REGISTER module

Interface:

Filter:

• Industry
• Level of risk
• Import share
• Period of replacement

Table:

| Component | Risk | Replacement | Term | Reserve |

The system automatically allocates critical positions.

4 - MODULE “STRASS-TEST”

Screen Scripts:

• Optimistic
• Basic
• Stress

The user chooses the event:

• Financial blocking
• Cyberattack
• Logistic failure
• Energy incident

The system calculates:

• Fall SIU
• Recovery Time
• Cascading effects

5️⃣ WAR ROOM DIGITAL

The mode is activated automatically when:

• Fall SIU Below 60
• exceeding the critical risk threshold

The screen is divided into 4 zones:

• Map of events
• Critical KPI
• Challenges and solutions
• Reaction timer

6 - Access Level Architecture

Level 1 - Strategic

Only indexes and summary analytics.

Level 2 — Operational

Industry indicators and risks.

Level 3 — Technical

Deep data and parameters.

7 - Technical architecture MVP

• Backend: Secure Server Outline
• Database: distributed
• Analytics: calculation module SIU
• Interface: web + secure intranet
• Logs: continuous audit of actions

8 - Minimum startup functionality (6–9 months)

• Calculation SIU.
• Dashboard 5 contours.
• Passports of sustainability.
• Basic stress test.
• Manual input KPI.

Without too much complexity.

I. Economic Impact of Sustainability Growth

1. Basic Logic

Sustainability reduces:

• The risk premium,
• volatility,
• Losses from crises,
• Costs of import substitution in emergency mode.

And increases:

• investment attractiveness,
• depth of processing,
• added value,
• technological independence.

2. Impact on GDP. 

Divide the effect by 4 blocks:

1 - Reduction of crisis losses

Historically, crises have “eaten” 3–8% GDP in a stress scenario.

Increase SIU with 60 → 80 Reduces Losses on 30–50%.

Savings: ≈ 1,5–3% GDP annually in the horizon of 10 years.

2 - Growth of technological value added

Increase in the share of high-tech:+10% to the export structure

= +2–3% GDP in the medium term.

3 - Reduction of financial costs

Sustainability reduces:

• The cost of borrowing,
• sanctions losses,
• Capital drain.

Estimated effect: +0,5–1,5% GDP.

4 - Infrastructure multiplier

Investments in:

• energy,
• digitalization,
• industrial cooperation, 
• give the multiplier 1,4–1,8.

3. Cumulative effect

During the transition SIU 65 → 80: Potential GDP growth: +3–6% to the base trajectory in the horizon 7–10 years.

Moving on to digital management.

Architecture of the platform “Soyuz”

1. Objective

Real stability monitoring in 24/7 mode. Not once a year. A living system.

2. Main modules

MODULE 1 — SIU-CORE

• Automatic calculation of the sustainability index.
• Dashboard 5 contours.
• Dynamics for 1–5–10 years.

MODULE 2 — REGISTER OF CRITICAL DEPENDENCES

• Component → Industry → Supplier → Risk.
• Signals over the threshold.

MODULE 3 — STRESS TEST ENGINE

• Simulation of scenarios.
• Evaluation of Cascading Effects.
• Forecast recovery time.

MODULE 4 — KPI MONITORING

• Energy.
• Communications.
• Finance.
• Logistics.
• Education.
• OPC.

MODULE 5 — WAR ROOM DIGITAL

• Data integration in crisis.
• Decision-making centre.
• History of incidents.

3. Technological architecture

The platform includes:

• protected data-contour,
• Distributed nodes,
• analytical layer (AI),
• Strategic management interface.

4. Levels of access

• Strategic (combined index).
• Operational (industry indicators).
• Technical (specific metrics and alarms).

5. Phase of implementation

Stage 1 (1–2 years)

• Launch SIU-CORE.
• Passports of sustainability.
• Basic stress tests.

Stage 2 (3–5 years)

• Integration of industry data.
• Automatic signals.
• The Digital Monitoring Center.

Stage 3 (5–10 years)

• Complete digital twin of sustainability.
• Prediction based on AI.
• Predictive protection.

The final bundle. Growth SIU →Risk reduction →Investment growth →GDP growth →Increased sustainability. Closed loop.

Data Model (data schema) of the SOYUZ platform for MVP. This can be immediately given to the architect/developer: entities, connections, fields, rules for versioning and calculation SIU.

Data Model of Soyuz Platform (MVP)

0) Model Principles

• Event: Everything important is recorded as a measurement / incident / decision / test.
• Versionality: Passports, KPI and thresholds have versions (for the audit to see history).
• Hierarchy: System → Contour → Subsystem → KPI → Measurement.
• Standards vs Fact: Separately store target values and actual measurements.

1) Directory (core)

1.1 Contour. Contours of stability (5 pieces).

• contour_id (PK)
• code (W, T, E, C, I)
• name
• weight (default 0.2)
• description

1.2 Critical System. 10 critical systems (energy, communications, finance).

• system_id (PK)
• name
• category (energy/comm/finance/…)
• criticality_level (1/2/3)
• owner_org
• region_scope (national/region/local)
• status (active/archived)

1.3 Subsystem. Subsystems within CriticalSystem.

• subsystem_id (PK)
• system_id (FK → CriticalSystem)
• name
• description
• status

1.4 OrgUnit. Organizations/units.

• org_id (PK)
• name
• type (operator/regulator/analysis/…)
• contact_role (not personal data, only role)
• status

1.5 UserRole (RBAC). Roles of access.

• role_id (PK)
• name (strategic/operational/technical/auditor/admin)
• permissions (json/bitmask)

2) Sustainability passports (versions and controls)

2.1 ResiliencePassport. Passport of the system (the current “cap”).

• passport_id (PK)
• system_id (FK)
• contour_id (FK)
• current_version_id (FK → PassportVersion)
• maturity_level (1–5)
• created_at, updated_at

2.2 PassportVersion. Passport version (audit and changes).

• version_id (PK)
• passport_id (FK)
• version_no (int)
• target_function (text)
• failure_threshold (text + structured fields below)
• rto_target_hours (float)
• rpo_target_hours (float)
• autonomy_target_pct (float)
• test_frequency_per_year (int)
• approved_by_org (FK → OrgUnit)
• effective_from, effective_to
• notes

2.3 FailureThreshold. (optional if need to structure)

• threshold_id (PK)
• version_id (FK)
• metric (e.g. “capacity_loss_pct”, “outage_hours”)
• operator (>, >=, <…)
• value (float)
• unit

2.4 ReserveProfile. Profile of reserves according to the passport version.

• reserve_id (PK)
• version_id (FK)
• reserve_type (physical/data/people/finance)
• description
• coverage_days (float, nullable)
• capacity_units (nullable)
• last_verified_at

3) KPI and measurements

3.1 KPI. Directory KPI.

• kpi_id (PK)
• name
• unit (%, hours, days, index)
• aggregation (avg/min/max/last/weighted)
• direction (higher_is_better / lower_is_better)
• description

3.2 KPIdefinition. Binding KPI to system/subsystem and loop + Standards.

• kpi_def_id (PK)
• kpi_id (FK)
• system_id (FK)
• subsystem_id (FK, nullable)
• contour_id (FK)
• target_value (float)
• warning_value (float)
• critical_value (float)
• weight_in_contour (float) (sum on contour = 1.0)
• effective_from, effective_to
• source_type (manual/api/import)
• owner_org (FK)

3.3 Measurement. Actual measurements KPI.

• measurement_id (PK)
• kpi_def_id (FK)
• timestamp
• value (float)
• quality_flag (ok/suspect/missing)
• ingested_at
• source_ref (line: file/sensor/system)

4) Risks and dependencies

4.1 RiskRegister. The risk register (including for the matrix is probable× damages).

• risk_id (PK)
• name
• type (tech/finance/cyber/logistics/info/…)
• description
• probability (1–5)
• impact (1–5)
• risk_score (computed = prob×impact)
• owner_org (FK)
• status (open/mitigating/closed)
• last_reviewed_at

4.2 DependencyItem. Critical dependencies (component/software/material).

• dep_id (PK)
• system_id (FK)
• subsystem_id (FK, nullable)
• item_name
• item_type (component/material/software/service)
• current_source (domestic/foreign/mixed)
• import_share_pct (float)
• substitution_plan (text)
• substitution_due_months (int)
• reserve_coverage_months (float)
• risk_level (low/med/high/critical)
• owner_org (FK)
• updated_at

5) Incidents and War Room

5.1 Incident. Incident/event (real or teaching).

• incident_id (PK)
• system_id (FK)
• incident_type (cyber/outage/supply/finance/info/…)
• severity (1–5)
• started_at, ended_at
• status (open/contained/recovered/closed)
• summary
• root_cause (nullable)
• created_by_role (FK → UserRole)

5.2 IncidentImpact. Cascade effect (which KPI / systems are affected).

• impact_id (PK)
• incident_id (FK)
• kpi_def_id (FK, nullable)
• affected_system_id (FK, nullable)
• delta_value (float, nullable)
• notes

5.3 DecisionLog. War Room Solutions Journal.

• decision_id (PK)
• incident_id (FK)
• time
• decision_text
• assigned_org (FK)
• deadline
• status (planned/in_progress/done)
• evidence_ref (nullable)

6) Stress tests and scenarios

6.1 Scenario

• scenario_id (PK)
• name (optimistic/base/stress + custom)
• description
• assumptions (text/json)
• created_at

6.2 StressTestRun. Run the test.

• run_id (PK)
• scenario_id (FK)
• initiated_at
• initiated_by_role
• scope (national/region/system)
• status

6.3 StressTestResult. Results by systems/contours.

• result_id (PK)
• run_id (FK)
• system_id (FK)
• contour_id (FK)
• expected_siu_drop (float)
• expected_rto_hours (float)
• critical_findings (text)
• recommended_actions (text)

7) Calculation of indices (SIU)

7.1 ContourScore. The total score of the contour for the period.

• contour_score_id (PK)
• contour_id (FK)
• period_start, period_end
• score_0_100
• computed_at
• method_version (to change the method)

7.2 SIUScore. Index SIU.

• siu_id (PK)
• period_start, period_end
• siu_0_100
• w_score, t_score, e_score, c_score, i_score
• computed_at
• method_version

Calculation rule (MVP)

• We consider each ContourScore as a weighted average of KPI by KPI Definition.weight_in_contour for the period (for example, last/avg).
• SIU = geometric average of five contours:
  SIU = (W × T × E × C × I)^{1/5}
• If there is a lot of missing along the contour, we introduce a fine (for example, -5...-15) to stimulate the data.

8) Communications (short)

• Contour 1—N Resilience Passport
• CriticalSystem 1—N ResiliencePassport, 1—N Subsystem, 1—N KPIDefinition, 1—N Incident, 1—N DependencyItem
• KPI 1—N KPIDefinition
• KPIDefinition 1—N Measurement
• Incident 1—N DecisionLog, 1—N IncidentImpact
• Scenario 1—N StressTestRun 1—N StressTestResult
• ContourScore + SIUScore — periodic units

9) Minimum data set to run MVP

To make the platform “live” at once:

• 10 CriticalSystem
• 5 Contour
• by 5 KPI per circuit (25 KPI min)
• 1 Passport to the system (10 Passports) + Version
• weekly import Measurement (even manual)
• 1 basic stress test scenario

DDL-skeleton under PostgreSQL (tables/keys/indexes) and wireframe navigation/screens strictly under our Data Model.

1) PostgreSQL DDL (skeleton)

-- =========================================

-- PLATFORM "SOYUZ" - MVP DDL (PostgreSQL)

-- =========================================

-- 0) Enums (can be replaced by directories)

DO $$ BEGIN

  CREATE TYPE risk_status AS ENUM ('open','mitigating','closed');

EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN

  CREATE TYPE incident_status AS ENUM ('open','contained','recovered','closed');

EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN

  CREATE TYPE quality_flag AS ENUM ('ok','suspect','missing');

EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN

  CREATE TYPE kpi_direction AS ENUM ('higher_is_better','lower_is_better');

EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN

  CREATE TYPE kpi_aggregation AS ENUM ('avg','min','max','last','weighted');

EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN

  CREATE TYPE system_status AS ENUM ('active','archived');

EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN

  CREATE TYPE reserve_type AS ENUM ('physical','data','people','finance');

EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN

  CREATE TYPE dependency_type AS ENUM ('component','material','software','service');

EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN

  CREATE TYPE source_type AS ENUM ('manual','api','import');

EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- 1) Core reference tables

CREATE TABLE IF NOT EXISTS contour (

  contour_id        BIGSERIAL PRIMARY KEY,

  code              TEXT NOT NULL UNIQUE CHECK (code IN ('W','T','E','C','I')),

  name              TEXT NOT NULL,

  weight            NUMERIC(6,4) NOT NULL DEFAULT 0.2000,

  description       TEXT

);

CREATE TABLE IF NOT EXISTS org_unit (

  org_id            BIGSERIAL PRIMARY KEY,

  name              TEXT NOT NULL UNIQUE,

  type              TEXT,

  contact_role TEXT, -- only role, no personal data

  status            system_status NOT NULL DEFAULT 'active'

);

CREATE TABLE IF NOT EXISTS user_role (

  role_id           BIGSERIAL PRIMARY KEY,

  name              TEXT NOT NULL UNIQUE, -- strategic/operational/technical/auditor/admin

  permissions       JSONB NOT NULL DEFAULT '{}'::jsonb

);

CREATE TABLE IF NOT EXISTS critical_system (

  system_id         BIGSERIAL PRIMARY KEY,

  name              TEXT NOT NULL UNIQUE,

  category          TEXT NOT NULL, -- energy/comm/finance/logistics/...

  criticality_level SMALLINT NOT NULL CHECK (criticality_level BETWEEN 1 AND 3),

  owner_org         BIGINT REFERENCES org_unit(org_id),

  region_scope      TEXT NOT NULL DEFAULT 'national',

  status            system_status NOT NULL DEFAULT 'active'

);

CREATE TABLE IF NOT EXISTS subsystem (

  subsystem_id      BIGSERIAL PRIMARY KEY,

  system_id         BIGINT NOT NULL REFERENCES critical_system(system_id) ON DELETE CASCADE,

  name              TEXT NOT NULL,

  description       TEXT,

  status            system_status NOT NULL DEFAULT 'active',

  UNIQUE(system_id, name)

);

-- 2) Passports (versioned)

CREATE TABLE IF NOT EXISTS resilience_passport (

  passport_id        BIGSERIAL PRIMARY KEY,

  system_id          BIGINT NOT NULL REFERENCES critical_system(system_id) ON DELETE CASCADE,

  contour_id         BIGINT NOT NULL REFERENCES contour(contour_id),

  current_version_id BIGINT, -- FK added after passport_version exists

  maturity_level     SMALLINT NOT NULL DEFAULT 2 CHECK (maturity_level BETWEEN 1 AND 5),

  created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  updated_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  UNIQUE(system_id, contour_id)

);

CREATE TABLE IF NOT EXISTS passport_version (

  version_id            BIGSERIAL PRIMARY KEY,

  passport_id           BIGINT NOT NULL REFERENCES resilience_passport(passport_id) ON DELETE CASCADE,

  version_no            INT NOT NULL,

  target_function       TEXT NOT NULL,

  failure_threshold_txt TEXT, -- short description of the failure threshold

  rto_target_hours      NUMERIC(10,2),

  rpo_target_hours      NUMERIC(10,2),

  autonomy_target_pct   NUMERIC(6,2),

  test_frequency_per_year INT,

  approved_by_org       BIGINT REFERENCES org_unit(org_id),

  effective_from        DATE NOT NULL DEFAULT CURRENT_DATE,

  effective_to          DATE,

  notes                 TEXT,

  created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  UNIQUE(passport_id, version_no)

);

ALTER TABLE resilience_passport

  ADD CONSTRAINT fk_passport_current_version

  FOREIGN KEY (current_version_id) REFERENCES passport_version(version_id);

CREATE TABLE IF NOT EXISTS failure_threshold (

  threshold_id    BIGSERIAL PRIMARY KEY,

  version_id      BIGINT NOT NULL REFERENCES passport_version(version_id) ON DELETE CASCADE,

  metric          TEXT NOT NULL,

  operator        TEXT NOT NULL, -- >, >=, < ...

  value           NUMERIC(18,6) NOT NULL,

  unit            TEXT

);

CREATE TABLE IF NOT EXISTS reserve_profile (

  reserve_id        BIGSERIAL PRIMARY KEY,

  version_id        BIGINT NOT NULL REFERENCES passport_version(version_id) ON DELETE CASCADE,

  reserve_type      reserve_type NOT NULL,

  description       TEXT,

  coverage_days     NUMERIC(10,2),

  capacity_units    TEXT,

  last_verified_at  TIMESTAMPTZ

);

-- 3) KPI model

CREATE TABLE IF NOT EXISTS kpi (

  kpi_id        BIGSERIAL PRIMARY KEY,

  name          TEXT NOT NULL UNIQUE,

  unit          TEXT NOT NULL,

  aggregation   kpi_aggregation NOT NULL DEFAULT 'avg',

  direction     kpi_direction NOT NULL DEFAULT 'higher_is_better',

  description   TEXT

);

CREATE TABLE IF NOT EXISTS kpi_definition (

  kpi_def_id        BIGSERIAL PRIMARY KEY,

  kpi_id            BIGINT NOT NULL REFERENCES kpi(kpi_id),

  system_id         BIGINT NOT NULL REFERENCES critical_system(system_id) ON DELETE CASCADE,

  subsystem_id      BIGINT REFERENCES subsystem(subsystem_id) ON DELETE SET NULL,

  contour_id        BIGINT NOT NULL REFERENCES contour(contour_id),

  target_value      NUMERIC(18,6),

  warning_value     NUMERIC(18,6),

  critical_value    NUMERIC(18,6),

  weight_in_contour NUMERIC(8,6) NOT NULL DEFAULT 0.200000,

  effective_from    DATE NOT NULL DEFAULT CURRENT_DATE,

  effective_to      DATE,

  source_type       source_type NOT NULL DEFAULT 'manual',

  owner_org         BIGINT REFERENCES org_unit(org_id),

  CHECK (weight_in_contour >= 0 AND weight_in_contour <= 1)

);

CREATE INDEX IF NOT EXISTS idx_kpi_def_system ON kpi_definition(system_id);

CREATE INDEX IF NOT EXISTS idx_kpi_def_contour ON kpi_definition(contour_id);

CREATE INDEX IF NOT EXISTS idx_kpi_def_kpi ON kpi_definition(kpi_id);

CREATE TABLE IF NOT EXISTS measurement (

  measurement_id BIGSERIAL PRIMARY KEY,

  kpi_def_id     BIGINT NOT NULL REFERENCES kpi_definition(kpi_def_id) ON DELETE CASCADE,

  timestamp      TIMESTAMPTZ NOT NULL,

  value          NUMERIC(18,6) NOT NULL,

  quality_flag   quality_flag NOT NULL DEFAULT 'ok',

  ingested_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  source_ref     TEXT

);

CREATE INDEX IF NOT EXISTS idx_measurement_kpi_def_time ON measurement(kpi_def_id, timestamp DESC);

CREATE INDEX IF NOT EXISTS idx_measurement_time ON measurement(timestamp DESC);

-- 4) Risks & dependencies

CREATE TABLE IF NOT EXISTS risk_register (

  risk_id          BIGSERIAL PRIMARY KEY,

  name             TEXT NOT NULL,

  type             TEXT NOT NULL,

  description      TEXT,

  probability      SMALLINT NOT NULL CHECK (probability BETWEEN 1 AND 5),

  impact           SMALLINT NOT NULL CHECK (impact BETWEEN 1 AND 5),

  risk_score       SMALLINT GENERATED ALWAYS AS (probability * impact) STORED,

  owner_org        BIGINT REFERENCES org_unit(org_id),

  status           risk_status NOT NULL DEFAULT 'open',

  last_reviewed_at TIMESTAMPTZ

);

CREATE INDEX IF NOT EXISTS idx_risk_score ON risk_register(risk_score DESC);

CREATE INDEX IF NOT EXISTS idx_risk_status ON risk_register(status);

CREATE TABLE IF NOT EXISTS dependency_item (

  dep_id                  BIGSERIAL PRIMARY KEY,

  system_id               BIGINT NOT NULL REFERENCES critical_system(system_id) ON DELETE CASCADE,

  subsystem_id            BIGINT REFERENCES subsystem(subsystem_id) ON DELETE SET NULL,

  item_name               TEXT NOT NULL,

  item_type               dependency_type NOT NULL,

  current_source          TEXT NOT NULL, -- domestic/foreign/mixed

  import_share_pct        NUMERIC(6,2),

  substitution_plan       TEXT,

  substitution_due_months INT,

  reserve_coverage_months NUMERIC(10,2),

  risk_level              TEXT NOT NULL, -- low/med/high/critical

  owner_org               BIGINT REFERENCES org_unit(org_id),

  updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW()

);

CREATE INDEX IF NOT EXISTS idx_dep_system ON dependency_item(system_id);

CREATE INDEX IF NOT EXISTS idx_dep_risk ON dependency_item(risk_level);

-- 5) Incidents & War Room

CREATE TABLE IF NOT EXISTS incident (

  incident_id     BIGSERIAL PRIMARY KEY,

  system_id       BIGINT NOT NULL REFERENCES critical_system(system_id) ON DELETE CASCADE,

  incident_type   TEXT NOT NULL, -- cyber/outage/supply/finance/info/...

  severity        SMALLINT NOT NULL CHECK (severity BETWEEN 1 AND 5),

  started_at      TIMESTAMPTZ NOT NULL,

  ended_at        TIMESTAMPTZ,

  status          incident_status NOT NULL DEFAULT 'open',

  summary         TEXT,

  root_cause      TEXT,

  created_by_role BIGINT REFERENCES user_role(role_id)

);

CREATE INDEX IF NOT EXISTS idx_incident_system ON incident(system_id, started_at DESC);

CREATE INDEX IF NOT EXISTS idx_incident_status ON incident(status);

CREATE TABLE IF NOT EXISTS incident_impact (

  impact_id          BIGSERIAL PRIMARY KEY,

  incident_id        BIGINT NOT NULL REFERENCES incident(incident_id) ON DELETE CASCADE,

  kpi_def_id         BIGINT REFERENCES kpi_definition(kpi_def_id) ON DELETE SET NULL,

  affected_system_id BIGINT REFERENCES critical_system(system_id) ON DELETE SET NULL,

  delta_value        NUMERIC(18,6),

  notes              TEXT

);

CREATE TABLE IF NOT EXISTS decision_log (

  decision_id   BIGSERIAL PRIMARY KEY,

  incident_id   BIGINT NOT NULL REFERENCES incident(incident_id) ON DELETE CASCADE,

  time          TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  decision_text TEXT NOT NULL,

  assigned_org  BIGINT REFERENCES org_unit(org_id),

  deadline      TIMESTAMPTZ,

  status        TEXT NOT NULL DEFAULT 'planned', -- planned/in_progress/done

  evidence_ref  TEXT

);

CREATE INDEX IF NOT EXISTS idx_decision_incident ON decision_log(incident_id, time DESC);

-- 6) Scenarios & stress tests

CREATE TABLE IF NOT EXISTS scenario (

  scenario_id  BIGSERIAL PRIMARY KEY,

  name         TEXT NOT NULL UNIQUE, -- optimistic/base/stress/custom

  description  TEXT,

  assumptions  JSONB NOT NULL DEFAULT '{}'::jsonb,

  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()

);

CREATE TABLE IF NOT EXISTS stress_test_run (

  run_id            BIGSERIAL PRIMARY KEY,

  scenario_id       BIGINT NOT NULL REFERENCES scenario(scenario_id),

  initiated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  initiated_by_role BIGINT REFERENCES user_role(role_id),

  scope             TEXT NOT NULL DEFAULT 'national', -- national/region/system

  status            TEXT NOT NULL DEFAULT 'running'

);

CREATE TABLE IF NOT EXISTS stress_test_result (

  result_id           BIGSERIAL PRIMARY KEY,

  run_id              BIGINT NOT NULL REFERENCES stress_test_run(run_id) ON DELETE CASCADE,

  system_id           BIGINT NOT NULL REFERENCES critical_system(system_id) ON DELETE CASCADE,

  contour_id          BIGINT NOT NULL REFERENCES contour(contour_id),

  expected_siu_drop   NUMERIC(10,2),

  expected_rto_hours  NUMERIC(10,2),

  critical_findings   TEXT,

  recommended_actions TEXT

);

CREATE INDEX IF NOT EXISTS idx_stress_result_run ON stress_test_result(run_id);

CREATE INDEX IF NOT EXISTS idx_stress_result_system ON stress_test_result(system_id);

-- 7) Scores (computed aggregates)

CREATE TABLE IF NOT EXISTS contour_score (

  contour_score_id BIGSERIAL PRIMARY KEY,

  contour_id       BIGINT NOT NULL REFERENCES contour(contour_id),

  period_start     DATE NOT NULL,

  period_end       DATE NOT NULL,

  score_0_100      NUMERIC(6,2) NOT NULL CHECK (score_0_100 BETWEEN 0 AND 100),

  computed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  method_version   TEXT NOT NULL DEFAULT 'mvp_v1',

  UNIQUE(contour_id, period_start, period_end, method_version)

);

CREATE TABLE IF NOT EXISTS siu_score (

  siu_id       BIGSERIAL PRIMARY KEY,

  period_start DATE NOT NULL,

  period_end   DATE NOT NULL,

  siu_0_100    NUMERIC(6,2) NOT NULL CHECK (siu_0_100 BETWEEN 0 AND 100),

  w_score      NUMERIC(6,2) NOT NULL,

  t_score      NUMERIC(6,2) NOT NULL,

  e_score      NUMERIC(6,2) NOT NULL,

  c_score      NUMERIC(6,2) NOT NULL,

  i_score      NUMERIC(6,2) NOT NULL,

  computed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  method_version TEXT NOT NULL DEFAULT 'mvp_v1',

  UNIQUE(period_start, period_end, method_version)

);

-- Helpful indexes for dashboard queries

CREATE INDEX IF NOT EXISTS idx_siu_latest ON siu_score(period_end DESC);

CREATE INDEX IF NOT EXISTS idx_contour_score_latest ON contour_score(period_end DESC, contour_id);

Note by MVP

• V DDL There is no user table (login/authentication). For MVP enough RBAC Roles + External SSO/LDAP Later.
• There are many scales: contour.weight + kpi_definition.weight_in_contour. In MVP it is possible to fix contours on 0.2 and control only KPI-weights.

2) Wireframe interface (menu + Screens)

Global Navigation (Left Menu)

• Home
• Contours
• W Military
• T Technologies
• E Economy
• C Cognitive
• I Institutional
• Critical systems
• Energy
• Contact
• Finance
• Logistics
• Food
• Health care
• Public Administration
• OPK
• Personnel/R & D
• Infosreda
• Sustainability Passports
• KPI and Measurements
• Risks and dependencies
• Stress tests
• War Room
• Reports / Exports
• Settings
• Directories
• Thresholds/scales
• Access Roles

Screen 1: Home (Strategic Dashboard)

Top panel:

• Period (week/month/quarter)
• Filter (country/region/system)
• Button “Start the stress test”
• Mode indicator (Normal / Alert / War Room)

Body:

• Big SIU gauge
• 5 contour tiles (W/T/E/C/I): score + Trend + status
• “Top-5 risks” (from risk_register by risk_score)
• “Top-5 Dependency_item by risk_level=critical)
• “Active incidents” (from incident status=open/containered)

Screen 2: Outline (example: Economics)

Tabs:

• KPI
• Risks
• Dependencies
• Events (incidents)
• Recommendations (from the results of stress tests)

KPI-insert:

• Table KPI with thresholds (target/warn/critical)
• Trend chart for each KPI (last 90 days)
• “Data failures” (quality_flag=missing) — a fine in the index

Screen 3: Critical system (Example: Energy)

System Cap:

• Level of criticality
• Responsible body (org_unit)
• Current passport (version)

Blocks:

• KPI systems (including subsystems)
• Failure threshold (from passport_version + failure_threshold)
• Reserves (from reserve_profile)
• Incidents in the system
• Button “Open War Room on the system”

Screen 4: Sustainability Passports

• List of passports (system + tour + maturity)
• Passport opening → version history
• Version Editor (if role allows)
• Button “Appoint current” (updates current_version_id)

Screen 5:KPI and Measurements

• Catalog KPI
• Links KPI to systems (kpi_definition)
• Import Measurements / Manual Input
• Data quality control (ok/suspect/missing)
• Audit of sources (source_ref)

Screen 6: Risks and dependencies

• Matrix Probability× (heatmap)
• Risk Register (risk_register)
• Dependency_item register with filters:
• Risk_level
• import_share_pct
• substitution_due_months

Screen 7: Stress tests

• Scripts (scenario)
• Runs (stress_test_run)
• Results (stress_test_result):
• Expected fall SIU
• Expected RTO
• Recommendations

Screen 8: War Room

Layout 4 Panels:

• Map/timeline of incident
• KPI "Red Zone"
• Decision_log (Decision_log)
• Tasks/times/responsible (decision_log status)

Auto-Login to the War Room if:

• siu_score < 60 or
• The Severity Incident ≥ 4 or
• Risk_score ≥ 20 (5×4, 5×5)

1) ContourScore and SIU (SQL)

1.1 Normalization KPI to scale 0–100

The idea: each measurement KPI translate into a score of 0–100 relative to the thresholds critical_value / warning_value / target_value.

• If higher_is_better:
• value ≥ target → 100
• value ≤ critical → 0
• between critical and target - linear interpolation (can be complicated later)
• If lower_is_better: mirror

-- =========================================

-- KPI scoring view (0..100)

-- Uses latest measurement per KPI definition within period

-- =========================================

CREATE OR REPLACE VIEW v_kpi_latest_in_period AS

SELECT

  kd.kpi_def_id,

  kd.kpi_id,

  kd.system_id,

  kd.subsystem_id,

  kd.contour_id,

  kd.target_value,

  kd.warning_value,

  kd.critical_value,

  kd.weight_in_contour,

  k.direction,

  k.aggregation,

  m.timestamp,

  m.value,

  m.quality_flag

FROM kpi_definition kd

JOIN kpi k ON k.kpi_id = kd.kpi_id

JOIN LATERAL (

  SELECT m1.*

  FROM measurement m1

  WHERE m1.kpi_def_id = kd.kpi_def_id

  ORDER BY m1.timestamp DESC

  LIMIT 1

) m ON TRUE

WHERE kd.effective_to IS NULL OR kd.effective_to >= CURRENT_DATE;

-- Score each KPI definition to 0..100

CREATE OR REPLACE VIEW v_kpi_score_latest AS

SELECT

  v.*,

  CASE

    WHEN v.quality_flag = 'missing' THEN 0

    WHEN v.direction = 'higher_is_better' THEN

      CASE

        WHEN v.target_value IS NULL OR v.critical_value IS NULL THEN NULL

        WHEN v.value >= v.target_value THEN 100

        WHEN v.value <= v.critical_value THEN 0

        ELSE ROUND( ( (v.value - v.critical_value) / NULLIF((v.target_value - v.critical_value),0) ) * 100, 2)

      END

    WHEN v.direction = 'lower_is_better' THEN

      CASE

        WHEN v.target_value IS NULL OR v.critical_value IS NULL THEN NULL

        WHEN v.value <= v.target_value THEN 100

        WHEN v.value >= v.critical_value THEN 0

        ELSE ROUND( ( (v.critical_value - v.value) / NULLIF((v.critical_value - v.target_value),0) ) * 100, 2)

      END

    ELSE NULL

  END AS score_0_100

FROM v_kpi_latest_in_period v;

Note: warning_value is not yet used in the formula. In v2 you can make a “broken” function: critical→warning→target.

1.2 ContourScore = weighted average KPI by contour

-- =========================================

-- Contour score = weighted average of KPI scores

-- =========================================

CREATE OR REPLACE VIEW v_contour_score_current AS

SELECT

  c.contour_id,

  c.code AS contour_code,

  CURRENT_DATE AS period_start,

  CURRENT_DATE AS period_end,

  ROUND(

    SUM(ks.score_0_100 * kd.weight_in_contour) / NULLIF(SUM(kd.weight_in_contour),0),

    2

  ) AS score_0_100,

  SUM(CASE WHEN ks.score_0_100 IS NULL THEN 1 ELSE 0 END) AS null_scores_cnt,

  SUM(CASE WHEN ks.quality_flag = 'missing' THEN 1 ELSE 0 END) AS missing_cnt

FROM contour c

JOIN kpi_definition kd ON kd.contour_id = c.contour_id

JOIN v_kpi_score_latest ks ON ks.kpi_def_id = kd.kpi_def_id

GROUP BY c.contour_id, c.code;

1.3 SIU = geometric average 5 Contours + Penalty for missing

-- =========================================

-- SIU score = geometric mean of 5 contour scores

-- + optional penalty for missing data

-- =========================================

CREATE OR REPLACE VIEW v_siu_score_current AS

WITH cs AS (

  SELECT

    contour_code,

    score_0_100,

    missing_cnt

  FROM v_contour_score_current

),

pivot AS (

  SELECT

    MAX(CASE WHEN contour_code='W' THEN score_0_100 END) AS w_score,

    MAX(CASE WHEN contour_code='T' THEN score_0_100 END) AS t_score,

    MAX(CASE WHEN contour_code='E' THEN score_0_100 END) AS e_score,

    MAX(CASE WHEN contour_code='C' THEN score_0_100 END) AS c_score,

    MAX(CASE WHEN contour_code='I' THEN score_0_100 END) AS i_score,

    SUM(missing_cnt) AS total_missing

  FROM cs

)

SELECT

  CURRENT_DATE AS period_start,

  CURRENT_DATE AS period_end,

  w_score, t_score, e_score, c_score, i_score,

  -- Base geometric mean

  ROUND(

    POWER(

      NULLIF(w_score,0) * NULLIF(t_score,0) * NULLIF(e_score,0) * NULLIF(c_score,0) * NULLIF(i_score,0),

      1.0/5.0

    ),

    2

  ) AS siu_base,

  -- Penalty: -0.5 per missing KPI (cap -15)

  GREATEST(

    ROUND(

      POWER(

        NULLIF(w_score,0) * NULLIF(t_score,0) * NULLIF(e_score,0) * NULLIF(c_score,0) * NULLIF(i_score,0),

        1.0/5.0

      ) - LEAST(total_missing * 0.5, 15),

      2

    ),

    0

  ) AS siu_0_100

FROM pivot;

1.4 (Optional) materialized views for speed

-- Materialize contour and SIU daily/weekly via cron/job

CREATE MATERIALIZED VIEW IF NOT EXISTS mv_contour_score_current AS

SELECT * FROM v_contour_score_current;

CREATE MATERIALIZED VIEW IF NOT EXISTS mv_siu_score_current AS

SELECT * FROM v_siu_score_current;

-- Refresh commands:

-- REFRESH MATERIALIZED VIEW mv_contour_score_current;

-- REFRESH MATERIALIZED VIEW mv_siu_score_current;

2) Seed-data (MVP)

2.1 Contours, roles, organizations, 10 critical systems

-- =========================================

-- SEED DATA - CORE

-- =========================================

-- Contours

INSERT INTO contour (code, name, weight, description) VALUES

('W','Military',0.2,'Containment, Controllability, Cyber Resilience, Exercise'),

('T','Technological',0.2,'Localization, R&D, Software, Personnel, Production Depth'),

('E','Economic',0.2,'Industry, Finance, Energy, Food, Logistics'),

('C','Cognitive',0.2,'Trust, infosustainability, education, culture'),

('I','Institutional',0.2,'Coordination, decision speed, risk audit, management reservation')

ON CONFLICT (code) DO NOTHING;

-- Roles

INSERT INTO user_role (name, permissions) VALUES

('strategic',  '{"read":"all","write":"none"}'),

('operational','{"read":"all","write":"kpi,incident,decision"}'),

('technical', '{"read":"all","write":"kpi,measurement,deps"}'),

('auditor',   '{"read":"all","write":"none"}'),

('admin',     '{"read":"all","write":"all"}')

ON CONFLICT (name) DO NOTHING;

-- Org units (approx.)

INSERT INTO org_unit (name, type, contact_role, status) VALUES

('Soyuz Monitoring Center','analysis','coordinator','active'),

('Critinfra Operator','operator','duty','active'),

('Cybercenter','operator','duty','active'),

('Financial Outline','operator','coordinator','active'),

('Logistic outline','operator','coordinator','active'),

('Inforeda/communications','operator','coordinator','active'),

('R&D and personnel','operator','coordinator','active')

ON CONFLICT (name) DO NOTHING;

-- 10 Critical systems

INSERT INTO critical_system (name, category, criticality_level, owner_org, region_scope, status)

SELECT x.name, x.category, x.crit, ou.org_id, 'national', 'active'

FROM (VALUES

('Power system','energy',1),

('Comm', 'Comm',1),

('Financial system','finance',1),

('Logistics and Transport','logistics',1),

('Food system', 'food',1),

('Health', 'health',2),

('Continuity of government', 'governance',1),

('OPC and Industrial Mobilization','defense_industry',1),

('Personnel and R&D','hr_rnd',2),

('Infosred and cognitive resilience','info',1)

) AS x(name, category, crit)

JOIN org_unit ou ON ou.name = 'Soyuz Monitoring Center'

ON CONFLICT (name) DO NOTHING;

2.2 KPI (25 pieces: 5 for each contour)

-- =========================================

-- SEED DATA - KPI CATALOG (25)

-- =========================================

-- W (higher better unless stated)

INSERT INTO kpi (name, unit, aggregation, direction, description) VALUES

('W1: Control autonomy','%','last','higher_is_better','Share of autonomous control circuits'),

('W2: Share of protected channels','%','last','higher_is_better','Share of protected communication channels'),

('W3: Time to switch to backup','min','last','lower_is_better','Minutes to switch to backup channels'),

('W4: Cyber Resistance (Reflection Threshold)','index','last','higher_is_better','Index of Attack Resistance'),

('W5: Frequency of intercontour teachings','per_year','last','higher_is_better','Learning/year');

-- T

INSERT INTO kpi (name, unit, aggregation, direction, description) VALUES

('T1: Localization of critical components','%','last','higher_is_better','Range of localized critical components'),

('T2: Share of domestic software in cryptosystems','%','last','higher_is_better','Application of domestic software'),

('T3: R&D to GDP','%GDP','last','higher_is_better','R&D intensity'),

('T4: Graduation of engineers by priorities','per_year','last','higher_is_better','Number of graduates/year'),

('T5: Time of replacement of critical component','months','last','lower_is_better','Term of replacement of bottleneck');

-- E

INSERT INTO kpi (name, unit, aggregation, direction, description) VALUES

('E1: Industrial autonomy','%','last','higher_is_better','Self-sufficiency of production'),

('E2: The continuity of mass payments','%','last','higher_is_better','Share of payments without delay'),

('E3: Energy autonomy','%','last','higher_is_better','Internal energy supply'),

('E4: Food Reserve Days','days','last','higher_is_better','Reserve Coverage'),

('E5: SLA delivery of krytgruzk','%','last','higher_is_better','Shipping krytgruzk in SLA');

-- C

INSERT INTO kpi (name, unit, aggregation, direction, description) VALUES

('C1: Trust index for institutions','index','last','higher_is_better','Public trust'),

('C2: The rate of refutation of inputs','hours','last','lower_is_better','Times to neutralise'),

('C3: Media literacy rate','%','last','higher_is_better','Population with basic media literacy'),

('C4: System Education Coverage','%','last','higher_is_better','Program Coverage Percentage'),

('C5: Index of social connectedness','index','last','higher_is_better','Polarization/connectedness');

-- I

INSERT INTO kpi (name, unit, aggregation, direction, description) VALUES

('I1: Decision cycle time','hours','last','lower_is_better','From event to decision'),

('I2: Passport coverage','%','last','higher_is_better','Share of systems with passports'),

('I3: Regularity of risk audit','per_year','last','higher_is_better','Audits/year'),

('I4: Duplication of control centers','%','last','higher_is_better','Coating with backup centers'),

('I5: Execution on time','%','last','higher_is_better','Percentage of tasks completed on time')

ON CONFLICT (name) DO NOTHING;

2.3 Linking KPI to circuits and 10 systems (KPIDefinition)

To MVP We all know that: each of 25 KPI tying to all systems (this is normal for MVP, then split into subsystems). Weight inside the contour = 0.2 (5 KPI).

-- =========================================

-- SEED DATA - KPI DEFINITIONS (25 KPI x 10 systems)

-- =========================================

-- Helper: get contour_id by code

WITH contours AS (

  SELECT contour_id, code FROM contour

),

systems AS (

  SELECT system_id FROM critical_system WHERE status='active'

),

kpimap AS (

  SELECT kpi_id, name FROM kpi

),

defs AS (

  SELECT

    s.system_id,

    c.contour_id,

    k.kpi_id,

    -- targets/warn/critical per KPI name (MVP rough norms)

    CASE

      WHEN k.name LIKE 'W1:%' THEN 85

      WHEN k.name LIKE 'W2:%' THEN 80

      WHEN k.name LIKE 'W3:%' THEN 10

      WHEN k.name LIKE 'W4:%' THEN 80

      WHEN k.name LIKE 'W5:%' THEN 4

      WHEN k.name LIKE 'T1:%' THEN 70

      WHEN k.name LIKE 'T2:%' THEN 80

      WHEN k.name LIKE 'T3:%' THEN 3

      WHEN k.name LIKE 'T4:%' THEN 100000 -- conditional scale, later normalized

      WHEN k.name LIKE 'T5:%' THEN 12

      WHEN k.name LIKE 'E1:%' THEN 75

      WHEN k.name LIKE 'E2:%' THEN 99

      WHEN k.name LIKE 'E3:%' THEN 90

      WHEN k.name LIKE 'E4:%' THEN 180

      WHEN k.name LIKE 'E5:%' THEN 95

      WHEN k.name LIKE 'C1:%' THEN 75

      WHEN k.name LIKE 'C2:%' THEN 6

      WHEN k.name LIKE 'C3:%' THEN 60

      WHEN k.name LIKE 'C4:%' THEN 50

      WHEN k.name LIKE 'C5:%' THEN 70

      WHEN k.name LIKE 'I1:%' THEN 12

      WHEN k.name LIKE 'I2:%' THEN 100

      WHEN k.name LIKE 'I3:%' THEN 4

      WHEN k.name LIKE 'I4:%' THEN 80

      WHEN k.name LIKE 'I5:%' THEN 90

      ELSE NULL

    END AS target_value,

    CASE

      WHEN k.name LIKE 'W3:%' THEN 20

      WHEN k.name LIKE 'T5:%' THEN 18

      WHEN k.name LIKE 'C2:%' THEN 12

      WHEN k.name LIKE 'I1:%' THEN 24

      ELSE NULL

    END AS warning_value,

    CASE

      WHEN k.name LIKE 'W1:%' THEN 50

      WHEN k.name LIKE 'W2:%' THEN 40

      WHEN k.name LIKE 'W3:%' THEN 60

      WHEN k.name LIKE 'W4:%' THEN 40

      WHEN k.name LIKE 'W5:%' THEN 1

      WHEN k.name LIKE 'T1:%' THEN 40

      WHEN k.name LIKE 'T2:%' THEN 50

      WHEN k.name LIKE 'T3:%' THEN 1

      WHEN k.name LIKE 'T4:%' THEN 20000

      WHEN k.name LIKE 'T5:%' THEN 36

      WHEN k.name LIKE 'E1:%' THEN 50

      WHEN k.name LIKE 'E2:%' THEN 90

      WHEN k.name LIKE 'E3:%' THEN 70

      WHEN k.name LIKE 'E4:%' THEN 30

      WHEN k.name LIKE 'E5:%' THEN 80

      WHEN k.name LIKE 'C1:%' THEN 45

      WHEN k.name LIKE 'C2:%' THEN 48

      WHEN k.name LIKE 'C3:%' THEN 30

      WHEN k.name LIKE 'C4:%' THEN 20

      WHEN k.name LIKE 'C5:%' THEN 40

      WHEN k.name LIKE 'I1:%' THEN 72

      WHEN k.name LIKE 'I2:%' THEN 60

      WHEN k.name LIKE 'I3:%' THEN 1

      WHEN k.name LIKE 'I4:%' THEN 40

      WHEN k.name LIKE 'I5:%' THEN 60

      ELSE NULL

    END AS critical_value

  FROM systems s

  JOIN contours c ON TRUE

  JOIN kpimap k ON (

    (c.code='W' AND k.name LIKE 'W%:%') OR

    (c.code='T' AND k.name LIKE 'T%:%') OR

    (c.code='E' AND k.name LIKE 'E%:%') OR

    (c.code='C' AND k.name LIKE 'C%:%') OR

    (c.code='I' AND k.name LIKE 'I%:%')

  )

)

INSERT INTO kpi_definition (

  kpi_id, system_id, contour_id,

  target_value, warning_value, critical_value,

  weight_in_contour, source_type, owner_org

)

SELECT

  d.kpi_id, d.system_id, d.contour_id,

  d.target_value, d.warning_value, d.critical_value,

  0.2,

  'manual',

  (SELECT org_id FROM org_unit WHERE name='Soyuz Monitoring Center' LIMIT 1)

FROM defs d

ON CONFLICT DO NOTHING;

Important: KPI such as “Engineer’s graduation” requires normalization by population/plan. In MVP it is just a demonstration. In the combat version, we do “engineers on 100k” or “% of the plan execution”.

2.4 Multiple measurements to make the dashboard immediately show SIU

-- =========================================

-- SEED DATA - MEASUREMENTS (latest values)

-- =========================================

-- Set the "today" measurements for all kpi_definition.

-- For demonstration: values near target (with small deviations).

INSERT INTO measurement (kpi_def_id, timestamp, value, quality_flag, source_ref)

SELECT

  kd.kpi_def_id,

  NOW(),

  CASE

    WHEN k.name LIKE 'W1:%' THEN 82

    WHEN k.name LIKE 'W2:%' THEN 77

    WHEN k.name LIKE 'W3:%' THEN 14

    WHEN k.name LIKE 'W4:%' THEN 75

    WHEN k.name LIKE 'W5:%' THEN 3

    WHEN k.name LIKE 'T1:%' THEN 60

    WHEN k.name LIKE 'T2:%' THEN 72

    WHEN k.name LIKE 'T3:%' THEN 2.2

    WHEN k.name LIKE 'T4:%' THEN 70000

    WHEN k.name LIKE 'T5:%' THEN 16

    WHEN k.name LIKE 'E1:%' THEN 70

    WHEN k.name LIKE 'E2:%' THEN 98.5

    WHEN k.name LIKE 'E3:%' THEN 88

    WHEN k.name LIKE 'E4:%' THEN 120

    WHEN k.name LIKE 'E5:%' THEN 93

    WHEN k.name LIKE 'C1:%' THEN 68

    WHEN k.name LIKE 'C2:%' THEN 10

    WHEN k.name LIKE 'C3:%' THEN 45

    WHEN k.name LIKE 'C4:%' THEN 38

    WHEN k.name LIKE 'C5:%' THEN 62

    WHEN k.name LIKE 'I1:%' THEN 18

    WHEN k.name LIKE 'I2:%' THEN 70

    WHEN k.name LIKE 'I3:%' THEN 2

    WHEN k.name LIKE 'I4:%' THEN 55

    WHEN k.name LIKE 'I5:%' THEN 84

    ELSE NULL

  END AS value,

  'ok'::quality_flag,

  'seed_mvp'

FROM kpi_definition kd

JOIN kpi k ON k.kpi_id = kd.kpi_id

WHERE (kd.effective_to IS NULL OR kd.effective_to >= CURRENT_DATE);

How to check that the calculation works (fast)

-- 1) View KPI scores

SELECT contour_id, COUNT(*) cnt, ROUND(AVG(score_0_100),2) avg_score

FROM v_kpi_score_latest

GROUP BY contour_id

ORDER BY contour_id;

-- 2) Contours

SELECT * FROM v_contour_score_current ORDER BY contour_code;

-- 3) SIU

SELECT * FROM v_siu_score_current;

-- =========================================

-- DASHBOARD PACK (single query views)

-- SIU + contour tiles + top risks + top deps + active incidents

-- =========================================

-- 1) Latest SIU (from materialized if exists, else from view)

CREATE OR REPLACE VIEW v_dashboard_siu_latest AS

SELECT *

FROM v_siu_score_current;

-- 2) Contour tiles

CREATE OR REPLACE VIEW v_dashboard_contours AS

SELECT

  contour_id,

  contour_code,

  score_0_100,

  missing_cnt,

  null_scores_cnt

FROM v_contour_score_current

ORDER BY contour_code;

-- 3) Top risks (by risk_score)

CREATE OR REPLACE VIEW v_dashboard_top_risks AS

SELECT

  risk_id,

  name,

  type,

  probability,

  impact,

  risk_score,

  status,

  last_reviewed_at

FROM risk_register

WHERE status IN ('open','mitigating')

ORDER BY risk_score DESC, last_reviewed_at NULLS LAST

LIMIT 5;

-- 4) Top dependencies (critical first, then high; shortest due date first)

CREATE OR REPLACE VIEW v_dashboard_top_dependencies AS

SELECT

  dep_id,

  item_name,

  item_type,

  system_id,

  import_share_pct,

  substitution_due_months,

  reserve_coverage_months,

  risk_level,

  updated_at

FROM dependency_item

WHERE risk_level IN ('critical','high')

ORDER BY

  CASE risk_level WHEN 'critical' THEN 0 ELSE 1 END,

  substitution_due_months NULLS LAST,

  reserve_coverage_months NULLS FIRST,

  updated_at DESC

LIMIT 5;

-- 5) Active incidents (open/contained)

CREATE OR REPLACE VIEW v_dashboard_active_incidents AS

SELECT

  i.incident_id,

  i.system_id,

  cs.name AS system_name,

  i.incident_type,

  i.severity,

  i.started_at,

  i.status,

  i.summary

FROM incident i

JOIN critical_system cs ON cs.system_id = i.system_id

WHERE i.status IN ('open','contained')

ORDER BY i.severity DESC, i.started_at DESC

LIMIT 10;

-- 6) Single "dashboard pack" view as JSON (handy for API)

CREATE OR REPLACE VIEW v_dashboard_pack_json AS

SELECT jsonb_build_object(

  'siu', (SELECT to_jsonb(s) FROM v_dashboard_siu_latest s),

  'contours', (SELECT jsonb_agg(to_jsonb(c) ORDER BY c.contour_code) FROM v_dashboard_contours c),

  'top_risks', (SELECT jsonb_agg(to_jsonb(r)) FROM v_dashboard_top_risks r),

  'top_dependencies', (SELECT jsonb_agg(to_jsonb(d)) FROM v_dashboard_top_dependencies d),

  'active_incidents', (SELECT jsonb_agg(to_jsonb(a)) FROM v_dashboard_active_incidents a)

) AS dashboard;

-- =========================================

-- OPTIONAL: ROLLUPS (weekly/monthly) for fast charts

-- Uses measurements to compute contour scores by period.

-- =========================================

-- Helper: choose period bucket. For weekly: date_trunc('week', ts), monthly: date_trunc('month', ts)

-- 1) KPI score per period (latest measurement within period)

CREATE OR REPLACE VIEW v_kpi_latest_by_week AS

WITH m2 AS (

  SELECT

    m.kpi_def_id,

    date_trunc('week', m.timestamp)::date AS period_start,

    (date_trunc('week', m.timestamp) + INTERVAL '6 days')::date AS period_end,

    m.timestamp,

    m.value,

    m.quality_flag,

    ROW_NUMBER() OVER (

      PARTITION BY m.kpi_def_id, date_trunc('week', m.timestamp)

      ORDER BY m.timestamp DESC

    ) AS rn

  FROM measurement m

)

SELECT

  kd.kpi_def_id,

  kd.kpi_id,

  kd.system_id,

  kd.subsystem_id,

  kd.contour_id,

  kd.target_value,

  kd.warning_value,

  kd.critical_value,

  kd.weight_in_contour,

  k.direction,

  m2.period_start,

  m2.period_end,

  m2.timestamp,

  m2.value,

  m2.quality_flag

FROM m2

JOIN kpi_definition kd ON kd.kpi_def_id = m2.kpi_def_id

JOIN kpi k ON k.kpi_id = kd.kpi_id

WHERE m2.rn = 1;

-- Score weekly

CREATE OR REPLACE VIEW v_kpi_score_by_week AS

SELECT

  v.*,

  CASE

    WHEN v.quality_flag = 'missing' THEN 0

    WHEN v.direction = 'higher_is_better' THEN

      CASE

        WHEN v.target_value IS NULL OR v.critical_value IS NULL THEN NULL

        WHEN v.value >= v.target_value THEN 100

        WHEN v.value <= v.critical_value THEN 0

        ELSE ROUND( ( (v.value - v.critical_value) / NULLIF((v.target_value - v.critical_value),0) ) * 100, 2)

      END

    WHEN v.direction = 'lower_is_better' THEN

      CASE

        WHEN v.target_value IS NULL OR v.critical_value IS NULL THEN NULL

        WHEN v.value <= v.target_value THEN 100

        WHEN v.value >= v.critical_value THEN 0

        ELSE ROUND( ( (v.critical_value - v.value) / NULLIF((v.critical_value - v.target_value),0) ) * 100, 2)

      END

    ELSE NULL

  END AS score_0_100

FROM v_kpi_latest_by_week v;

-- 2) Contour score by week (across all systems, MVP global)

CREATE OR REPLACE VIEW v_contour_score_by_week AS

SELECT

  c.contour_id,

  c.code AS contour_code,

  ks.period_start,

  ks.period_end,

  ROUND(

    SUM(ks.score_0_100 * kd.weight_in_contour) / NULLIF(SUM(kd.weight_in_contour),0),

    2

  ) AS score_0_100,

  SUM(CASE WHEN ks.score_0_100 IS NULL THEN 1 ELSE 0 END) AS null_scores_cnt,

  SUM(CASE WHEN ks.quality_flag = 'missing' THEN 1 ELSE 0 END) AS missing_cnt

FROM contour c

JOIN kpi_definition kd ON kd.contour_id = c.contour_id

JOIN v_kpi_score_by_week ks ON ks.kpi_def_id = kd.kpi_def_id

GROUP BY c.contour_id, c.code, ks.period_start, ks.period_end;

-- 3) SIU by week

CREATE OR REPLACE VIEW v_siu_by_week AS

WITH cs AS (

  SELECT period_start, period_end, contour_code, score_0_100, missing_cnt

  FROM v_contour_score_by_week

),

pivot AS (

  SELECT

    period_start,

    period_end,

    MAX(CASE WHEN contour_code='W' THEN score_0_100 END) AS w_score,

    MAX(CASE WHEN contour_code='T' THEN score_0_100 END) AS t_score,

    MAX(CASE WHEN contour_code='E' THEN score_0_100 END) AS e_score,

    MAX(CASE WHEN contour_code='C' THEN score_0_100 END) AS c_score,

    MAX(CASE WHEN contour_code='I' THEN score_0_100 END) AS i_score,

    SUM(missing_cnt) AS total_missing

  FROM cs

  GROUP BY period_start, period_end

)

SELECT

  period_start,

  period_end,

  w_score, t_score, e_score, c_score, i_score,

  ROUND(

    POWER(

      NULLIF(w_score,0) * NULLIF(t_score,0) * NULLIF(e_score,0) * NULLIF(c_score,0) * NULLIF(i_score,0),

      1.0/5.0

    ),

    2

  ) AS siu_base,

  GREATEST(

    ROUND(

      POWER(

        NULLIF(w_score,0) * NULLIF(t_score,0) * NULLIF(e_score,0) * NULLIF(c_score,0) * NULLIF(i_score,0),

        1.0/5.0

      ) - LEAST(total_missing * 0.5, 15),

      2

    ),

    0

  ) AS siu_0_100

FROM pivot

ORDER BY period_end DESC;

If you want the MVP to feel “alive” immediately, run this quick seed for risks/dependencies/incidents too:

-- =========================================

-- Optional seed: risks, dependencies, incident

-- =========================================

INSERT INTO risk_register (name, type, description, probability, impact, owner_org, status, last_reviewed_at)

VALUES

('Technological blocking of critical components','tech','Restriction of supply/equipment','5','5',

 (SELECT org_id FROM org_unit WHERE name='Soyuz Monitoring Center'), 'open', NOW()),

('Cyber attack on critical infrastructure','cyber','Attacks on energy/communications/logistics','5','4',

 (SELECT org_id FROM org_unit WHERE name='Cybercenter'), 'open', NOW()),

('Logical gap on the critical cargo','logistics','Breaking routes/nodes','4','4',

 (SELECT org_id FROM org_unit WHERE name='Logistic contour'), 'mitigating', NOW())

ON CONFLICT DO NOTHING;

INSERT INTO dependency_item (system_id, item_name, item_type, current_source, import_share_pct,

                            substitution_plan, substitution_due_months, reserve_coverage_months,

                            risk_level, owner_org)

VALUES

((SELECT system_id FROM critical_system WHERE name='Communication and management'),

 'Critical Network Chipset','component','foreign',85,

 'Development of domestic analogue + contract production',24,6,'critical',

 (SELECT org_id FROM org_unit WHERE name='Soyuz Monitoring Center')),

((SELECT system_id FROM critical_system WHERE name='The financial system'),

 'Components HSM (cryptomodules)','component','mixed',40,

 'Localization of production and certification',18,9,'high',

 (SELECT org_id FROM org_unit WHERE name=Cybercenter))

ON CONFLICT DO NOTHING;

INSERT INTO incident (system_id, incident_type, severity, started_at, status, summary, created_by_role)

VALUES

((SELECT system_id FROM critical_system WHERE name='Energy System'),

 'outage',4,NOW() - INTERVAL '3 hours','contained','Local failure in dispatch node',

 (SELECT role_id FROM user_role WHERE name='operational'))

ON CONFLICT DO NOTHING;

Use this one-liner to fetch the whole dashboard payload:

SELECT dashboard FROM v_dashboard_pack_json;