Data Model

Database: admin/admin.db (SQLite). Shared by FastAPI engine and Flask admin panel via filesystem. Both services use plain sqlite3 or SQLAlchemy respectively — no ORM dependency in the engine.

Stores calculation profiles. Each profile is a versioned, domain-scoped container for rules. At most one profile can be active per domain.

CREATE TABLE _tbl_calcrule_profiles (



    id                  INTEGER PRIMARY KEY AUTOINCREMENT,



    slug                TEXT UNIQUE NOT NULL,



        -- 'tco-dc-cooling', 'pfp-clearance-hdb', 'edr-entity-validation'



    name                TEXT NOT NULL,



        -- 'TCO Calculator — Data Centre Cooling'



    description         TEXT,



    technology_variants TEXT DEFAULT '[]',



        -- JSON array: '["traditional","rdhx","dlc","grc"]'



    domain              TEXT NOT NULL DEFAULT 'tco',



        -- Bounded context: 'tco' | 'edr' | 'pfp' | 'pfp_table'



    is_active           INTEGER NOT NULL DEFAULT 0,



        -- 1 = active within domain. At most one active per domain.



    version             TEXT DEFAULT '1.0.0',



    created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,



    updated_at          DATETIME DEFAULT CURRENT_TIMESTAMP



);

Current rows:

Stores individual calculation rules. Each rule belongs to exactly one profile. Rules are executed in descending priority order within a pipeline run.

CREATE TABLE _tbl_calcrule_rules (



    id             INTEGER PRIMARY KEY AUTOINCREMENT,



    profile_id     INTEGER NOT NULL REFERENCES _tbl_calcrule_profiles(id) ON DELETE CASCADE,



    rule_id        TEXT NOT NULL,



        -- Logical ID: 'pfp_hdb_clr_002', 'infra_001_servers_per_rack'



    name           TEXT NOT NULL,



    formula        TEXT NOT NULL,



        -- Python expression evaluated in sandboxed namespace



    output         TEXT,



        -- Dot-path output field: 'result.clearance_mm', 'capex.racks_capex'



    rule_type      TEXT,



        -- 'pfp_clearance' | 'pfp_selection' | 'pfp_sza'



        -- 'infrastructure' | 'capex' | 'opex' | 'tco' | 'scoring' | 'edr_validation'



    technology     TEXT,



        -- NULL = all; 'traditional' | 'rdhx' | 'dlc' | 'grc' | 'hdb' | 'fs'



    priority       INTEGER DEFAULT 0,



        -- Execution order: higher runs first



    enabled        INTEGER NOT NULL DEFAULT 1,



    skip_if_set    INTEGER NOT NULL DEFAULT 0,



        -- If 1: skip when output field already has a non-None value



    excel_source   TEXT,



    excel_baseline REAL,



    description    TEXT,



    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,



    updated_at     DATETIME DEFAULT CURRENT_TIMESTAMP,



    UNIQUE (profile_id, rule_id)



);

_tbl_calcrule_rule_audit — GAP-05

Audit trail for rule mutations. Write one row per changed field in every PATCH handler before applying the update. Enables GET /rules/{id}/history.

CREATE TABLE _tbl_calcrule_rule_audit (



    id          INTEGER PRIMARY KEY AUTOINCREMENT,



    rule_id     TEXT    NOT NULL,



        -- Logical rule_id (not FK — preserves history after delete)



    profile_id  INTEGER,



    changed_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,



    changed_by  TEXT,



        -- API key prefix (first 8 chars) or 'admin-panel'



    operation   TEXT NOT NULL,



        -- 'CREATE' | 'PATCH' | 'DELETE'



    field       TEXT,



        -- Field name changed (NULL for CREATE/DELETE)



    old_value   TEXT,



        -- JSON-serialised previous value



    new_value   TEXT



        -- JSON-serialised new value



);



CREATE INDEX idx_audit_rule_id   ON _tbl_calcrule_rule_audit(rule_id);



CREATE INDEX idx_audit_changed_at ON _tbl_calcrule_rule_audit(changed_at);

_tbl_api_keys — GAP-02

RBAC key store. Keys stored as SHA-256 hashes. Per-key scopes and expiry. Replace env-var comparison in require_api_key with hash lookup against this table.

CREATE TABLE _tbl_api_keys (



    id          INTEGER PRIMARY KEY AUTOINCREMENT,



    key_hash    TEXT UNIQUE NOT NULL,



        -- SHA-256 of the raw key — never store plaintext



    client_id   TEXT NOT NULL,



        -- 'pyrometrix-revit', 'admin', 'ci-pipeline'



    scopes      TEXT NOT NULL DEFAULT '["compute"]',



        -- JSON array: ["compute"] | ["compute","rules:read"] | ["admin"]



    expires_at  DATETIME,



        -- NULL = never expires (dev/internal only)



    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,



    last_used   DATETIME,



    enabled     INTEGER NOT NULL DEFAULT 1



);

Format used in nexus/rules/pfp/*.json. These files are import-format only — the DB is the runtime source of truth after seeding.

{



  "id":          "pfp_hdb_clr_002",



  "name":        "HDB EI60 Clearance — Double Drywall",



  "type":        "pfp_clearance",



  "technology":  "pfp",



  "priority":    6998,



  "formula":     "result.clearance_mm = 25 if (...) else None",



  "output":      "result.clearance_mm",



  "skip_if_set": true,



  "enabled":     true,



  "description": "Clearance for EI60 rated HDB penetration through DBL DW",



  "ruleset":     "HDB"



}

Lookup-table format used in nexus/rules/pfp/table_*.json. Row matching uses hierarchical specificity: cat_a → cat_g → cat_c → position. More specific matches win over general matches.

{



  "id":           "tbl_hdb_001",



  "type":         "pfp_table_row",



  "s1": { "cat_a": "Pipe", "cat_g": "Steel", "cat_c": null },



  "s2": { "cat_a": "Wall", "cat_g": "Concrete", "cat_c": null },



  "fire_rating":  "EI60",



  "position":     null,



  "clearance_mm": 25,



  "top_mm":       25,



  "side_mm":      25,



  "bottom_mm":    25



}

Files in nexus/config/datasources/*.yaml drive IngestionPipeline. The type field selects the DataSource connector backend.

name:        edr-uatdb



description: "EDR v6 UAT2 SQLite database"



pipeline:    edr







source:



  type: sqlite           # sqlite | csv | json | excel | access | postgres | odbc | rest



  path: "/path/to/db"







extractors:



  - id: equipment



    table: FACT_100_Equip_UAT2



    entity_class: Equipment



    limit: 2000



    field_map:



      Equipment_Tag: entity.tag



      Description:   entity.description



    value_filters:



      Status: ["Active", "Commissioned"]



    class_rules:



      - field: entity.tag



        pattern: "^EL[-_]"



        entity_class: Electrical

Supported connector types: sqlite, csv, json, excel, access (.accdb via pyodbc), postgres, odbc, rest

Target architecture: rules stored as graph nodes in DeltaPrism property graph database. Enables dependency analysis, version history as edges, and conflict detection between rules.

Node: Rule



  Properties: id, name, formula, priority, output, type, technology, skip_if_set



  Edges:



    BELONGS_TO     → Profile node



    DEPENDS_ON     → Rule node  (formula references output of another rule)



    SUPERSEDES     → Rule node  (version history)



    CONFLICTS_WITH → Rule node  (same output, overlapping conditions)







Node: Profile



  Properties: slug, name, domain, version, is_active



  Edges: HAS_RULE → Rule







Node: Domain



  Properties: key (tco | edr | pfp), description



  Edges: HAS_PROFILE → Profile

Integration: profile_store.py gains a NEXUS_RULE_STORE=deltaprism backend switch. The existing connector layer (datasource.py) can reach DeltaPrism via ODBC or REST. No changes to the rule execution engine or API surface required.