-- בדיקת כיסוי הקישור ממלאי לריצות
-- Documentation example; not executed in target environment.
WITH active_sessions AS (
  SELECT _id
  FROM {{schema}}.workflowsessionitem
  WHERE deletedat IS NULL
  GROUP BY _id
)
SELECT
  count(*) AS stock_rows,
  count_if(st.workflowsessionitemid IS NULL) AS no_session_id,
  count_if(st.workflowsessionitemid IS NOT NULL AND s._id IS NULL) AS missing_active_session,
  count_if(s._id IS NOT NULL) AS matched_active_session
FROM {{schema}}.stock st
LEFT JOIN active_sessions s ON s._id=st.workflowsessionitemid
WHERE st.deletedat IS NULL;

-- בדיקת ריבוי רשומות מלאי לאותה ריצה
-- Documentation example; not executed in target environment.
SELECT workflowsessionitemid, count(*) AS stock_rows
FROM {{schema}}.stock
WHERE deletedat IS NULL
  AND workflowsessionitemid IS NOT NULL
GROUP BY workflowsessionitemid
HAVING count(*) > 1
ORDER BY stock_rows DESC
LIMIT 20;

-- חיבור שעות לפקודה בלי לשכפל את כמות הפקודה
-- Documentation example; not executed in target environment.
WITH hours_per_order AS (
  SELECT workorderid, count(*) AS reports,
         sum(duration)/3600000.0 AS reported_hours
  FROM {{schema}}.timelogreport
  WHERE deletedat IS NULL
    AND "end" IS NOT NULL
    AND duration > 0 AND duration <= 43200000
  GROUP BY workorderid
)
SELECT wo._id, wo.workordernumber, wo.quantity,
       h.reports, h.reported_hours
FROM {{schema}}.workorder wo
LEFT JOIN hours_per_order h ON h.workorderid=wo._id
WHERE wo.deletedat IS NULL;

{
  "dialect": "Trino",
  "schema_placeholder": "{{schema}}",
  "contains_customer_data": false,
  "tables": [
    {
      "name": "workorder",
      "grain": "One work order",
      "fields": [
        {
          "name": "_id",
          "meaning": "Internal record identifier; inspect its exposed type and uniqueness.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "workordernumber",
          "meaning": "Recorded work-order number for display and lookup.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "sku",
          "meaning": "Recorded item/SKU identifier.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "workflowname",
          "meaning": "Recorded workflow/process name; names are not version identifiers.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "statustype",
          "meaning": "Technical work-order status code.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "statusname",
          "meaning": "Display name of the work-order status.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "createdat",
          "meaning": "Record creation timestamp.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "releasedat",
          "meaning": "Recorded work-order release timestamp.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "start",
          "meaning": "Recorded start timestamp for this table’s record.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "end",
          "meaning": "Recorded end timestamp; interpret it in the context of the table.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "expecteddate",
          "meaning": "Recorded order due timestamp.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "quantity",
          "meaning": "Recorded quantity; unit of measure depends on the item and context.",
          "unit": "item_dependent",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "validquantity",
          "meaning": "Reported good quantity on the order.",
          "unit": "item_dependent",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "invalidquantity",
          "meaning": "Reported rejected quantity on the order.",
          "unit": "item_dependent",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "progress",
          "meaning": "Recorded progress value; do not assume a universal percentage scale.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "deletedat",
          "meaning": "Soft-delete marker; active facts are normally selected with IS NULL.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        }
      ]
    },
    {
      "name": "workflowsessionitem",
      "grain": "One workflow run on a unit, batch or quantity",
      "fields": [
        {
          "name": "_id",
          "meaning": "Internal record identifier; inspect its exposed type and uniqueness.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "workorderid",
          "meaning": "Recorded internal work-order identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "workflowid",
          "meaning": "Recorded workflow-version identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "workflowname",
          "meaning": "Recorded workflow/process name; names are not version identifiers.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "partsku",
          "meaning": "SKU recorded on a production run.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "serials",
          "meaning": "Array of serial values; may be empty or contain several.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "userid",
          "meaning": "Recorded user identifier; association depends on the record type.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "status",
          "meaning": "Raw saved status code; its vocabulary depends on the table and process.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "created",
          "meaning": "Record creation timestamp.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "start",
          "meaning": "Recorded start timestamp for this table’s record.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "end",
          "meaning": "Recorded end timestamp; interpret it in the context of the table.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "nettimespent",
          "meaning": "Recorded run net time, in seconds.",
          "unit": "seconds",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "quantity",
          "meaning": "Recorded quantity; unit of measure depends on the item and context.",
          "unit": "item_dependent",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "nextnodename",
          "meaning": "Next step name recorded on the run.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "nextchaptername",
          "meaning": "Next chapter name recorded on the run.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "progress",
          "meaning": "Recorded progress value; do not assume a universal percentage scale.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "deletedat",
          "meaning": "Soft-delete marker; active facts are normally selected with IS NULL.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        }
      ]
    },
    {
      "name": "stock",
      "grain": "One stock record, not necessarily one unit",
      "fields": [
        {
          "name": "_id",
          "meaning": "Internal record identifier; inspect its exposed type and uniqueness.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "workflowsessionitemid",
          "meaning": "Recorded production-run identifier.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "workordernumber",
          "meaning": "Recorded work-order number for display and lookup.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "sku",
          "meaning": "Recorded item/SKU identifier.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "serial",
          "meaning": "Recorded serial value; do not assume uniqueness without SKU and identity rules.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "skurev",
          "meaning": "Item/product revision recorded on stock.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "skudesc",
          "meaning": "Item description observed on stock records.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "stockstatus",
          "meaning": "Recorded inventory status.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "productionstatus",
          "meaning": "Recorded stock production status, separate from stock status.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "quantity",
          "meaning": "Recorded quantity; unit of measure depends on the item and context.",
          "unit": "item_dependent",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "availablequantity",
          "meaning": "Recorded available quantity; not a guarantee of order allocation.",
          "unit": "item_dependent",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "kitstatus",
          "meaning": "Saved kit-complete flag; does not recalculate component coverage.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "isserial",
          "meaning": "Saved serial-management flag.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "created",
          "meaning": "Record creation timestamp.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "source",
          "meaning": "Recorded source/origin value.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "deletedat",
          "meaning": "Soft-delete marker; active facts are normally selected with IS NULL.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        }
      ]
    },
    {
      "name": "timelogreport",
      "grain": "One time report, possibly linked to several runs",
      "fields": [
        {
          "name": "_id",
          "meaning": "Internal record identifier; inspect its exposed type and uniqueness.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "workorderid",
          "meaning": "Recorded internal work-order identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "workordernumber",
          "meaning": "Recorded work-order number for display and lookup.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "workflowid",
          "meaning": "Recorded workflow-version identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "userid",
          "meaning": "Recorded user identifier; association depends on the record type.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "sessionids",
          "meaning": "Array of linked run identifiers; expanding it can duplicate report duration.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "sku",
          "meaning": "Recorded item/SKU identifier.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "chaptername",
          "meaning": "Chapter name stored on the time report.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "start",
          "meaning": "Recorded start timestamp for this table’s record.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "end",
          "meaning": "Recorded end timestamp; interpret it in the context of the table.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "firstend",
          "meaning": "First stored end timestamp, used for the limited end-change comparison.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "duration",
          "meaning": "Stored time-report duration, in milliseconds.",
          "unit": "milliseconds",
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "recordtype",
          "meaning": "Stored report creation/record type.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "type",
          "meaning": "Stored type or classification; use this table’s definition and vocabulary.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "managerapprovalstatus",
          "meaning": "Saved manager-approval status.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "deletedat",
          "meaning": "Soft-delete marker; active facts are normally selected with IS NULL.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        }
      ]
    },
    {
      "name": "formdata",
      "grain": "One saved form record with any recorded context",
      "fields": [
        {
          "name": "_id",
          "meaning": "Internal record identifier; inspect its exposed type and uniqueness.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "formid",
          "meaning": "Recorded form-definition identifier.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "sessionid",
          "meaning": "Recorded run context on a form record; field name differs from stock.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "workorderid",
          "meaning": "Recorded internal work-order identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "workordernumber",
          "meaning": "Recorded work-order number for display and lookup.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "stringnumber",
          "meaning": "Display number of the saved form record.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "status",
          "meaning": "Raw saved status code; its vocabulary depends on the table and process.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "created",
          "meaning": "Record creation timestamp.",
          "unit": "timestamp",
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "deletedat",
          "meaning": "Soft-delete marker; active facts are normally selected with IS NULL.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        }
      ]
    },
    {
      "name": "form",
      "grain": "One form-type definition",
      "fields": [
        {
          "name": "_id",
          "meaning": "Internal record identifier; inspect its exposed type and uniqueness.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "name",
          "meaning": "Definition/display name, not a guaranteed unique identity.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "deletedat",
          "meaning": "Soft-delete marker; active facts are normally selected with IS NULL.",
          "unit": "timestamp",
          "evidence": "repository_documented_not_tenant_tested"
        }
      ]
    },
    {
      "name": "workflow",
      "grain": "One version of a workflow definition",
      "fields": [
        {
          "name": "_id",
          "meaning": "Internal record identifier; inspect its exposed type and uniqueness.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "recordid",
          "meaning": "Documented cross-version workflow identity; verify in the target environment.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "name",
          "meaning": "Definition/display name, not a guaranteed unique identity.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "normalizedversion",
          "meaning": "Stored normalized workflow version number.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "currentreleasedversion",
          "meaning": "Flag marking a currently released workflow version.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "latestversion",
          "meaning": "Flag marking the latest version; latest need not be released.",
          "unit": null,
          "evidence": "saved_sql_definition_read"
        },
        {
          "name": "deletedat",
          "meaning": "Soft-delete marker; active facts are normally selected with IS NULL.",
          "unit": "timestamp",
          "evidence": "saved_sql_definition_read"
        }
      ]
    },
    {
      "name": "usermodel",
      "grain": "One user record",
      "fields": [
        {
          "name": "_id",
          "meaning": "Internal record identifier; inspect its exposed type and uniqueness.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "displayname",
          "meaning": "Recorded user display name.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "username",
          "meaning": "Recorded username.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        }
      ]
    },
    {
      "name": "workflowsessionitemrecord",
      "grain": "One run event/record, not necessarily an execution signature",
      "fields": [
        {
          "name": "_id",
          "meaning": "Internal record identifier; inspect its exposed type and uniqueness.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "workflowsessionitemid",
          "meaning": "Recorded production-run identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "nodeid",
          "meaning": "Recorded workflow-step/node identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "originalnodeid",
          "meaning": "Original step/node identifier used by documented templates; verify the target type and matching rule.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "userid",
          "meaning": "Recorded user identifier; association depends on the record type.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "start",
          "meaning": "Recorded start timestamp for this table’s record.",
          "unit": "timestamp",
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "end",
          "meaning": "Recorded end timestamp; interpret it in the context of the table.",
          "unit": "timestamp",
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "deletedat",
          "meaning": "Soft-delete marker; active facts are normally selected with IS NULL.",
          "unit": "timestamp",
          "evidence": "repository_documented_not_tenant_tested"
        }
      ]
    },
    {
      "name": "sessioncustomfieldview",
      "grain": "One saved field value with run and time context",
      "fields": [
        {
          "name": "workflowsessionitemid",
          "meaning": "Recorded production-run identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "fieldid",
          "meaning": "Recorded field-definition identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "date",
          "meaning": "Timestamp associated with the saved field value.",
          "unit": "timestamp",
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "nodeid",
          "meaning": "Recorded workflow-step/node identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "originalnodeid",
          "meaning": "Original step/node identifier used by documented templates; verify the target type and matching rule.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "sessionnodeid",
          "meaning": "Run-specific node identifier; do not substitute a workflow-definition node ID.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "userid",
          "meaning": "Recorded user identifier; association depends on the record type.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "valuestring",
          "meaning": "Stored text value.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "valuenumber",
          "meaning": "Stored numeric value; measurement meaning and unit depend on the field.",
          "unit": "field_dependent",
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "valueboolean",
          "meaning": "Stored Boolean value.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "valuedate",
          "meaning": "Stored date value.",
          "unit": "timestamp",
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "deletedat",
          "meaning": "Soft-delete marker; active facts are normally selected with IS NULL.",
          "unit": "timestamp",
          "evidence": "repository_documented_not_tenant_tested"
        }
      ]
    },
    {
      "name": "field",
      "grain": "One field definition",
      "fields": [
        {
          "name": "_id",
          "meaning": "Internal record identifier; inspect its exposed type and uniqueness.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "title",
          "meaning": "Recorded field title.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "type",
          "meaning": "Stored type or classification; use this table’s definition and vocabulary.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "subtype",
          "meaning": "Recorded field subtype.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "min",
          "meaning": "Configured input minimum; not automatically a quality specification limit.",
          "unit": "field_dependent",
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "max",
          "meaning": "Configured input maximum; not automatically a quality specification limit.",
          "unit": "field_dependent",
          "evidence": "repository_documented_not_tenant_tested"
        }
      ]
    },
    {
      "name": "stockitemview",
      "grain": "One component row linked to a parent stock record",
      "fields": [
        {
          "name": "stockid",
          "meaning": "Recorded parent-stock identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "sku",
          "meaning": "Recorded item/SKU identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "serial",
          "meaning": "Recorded serial value; do not assume uniqueness without SKU and identity rules.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "quantity",
          "meaning": "Recorded quantity; unit of measure depends on the item and context.",
          "unit": "item_dependent",
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "deletedat",
          "meaning": "Soft-delete marker; active facts are normally selected with IS NULL.",
          "unit": "timestamp",
          "evidence": "repository_documented_not_tenant_tested"
        }
      ]
    },
    {
      "name": "workorderkititemview",
      "grain": "One required kit-component row for an order",
      "fields": [
        {
          "name": "workorderid",
          "meaning": "Recorded internal work-order identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "sku",
          "meaning": "Recorded item/SKU identifier.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "rev",
          "meaning": "Recorded component/item revision.",
          "unit": null,
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "quantity",
          "meaning": "Recorded quantity; unit of measure depends on the item and context.",
          "unit": "item_dependent",
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "issuedquantity",
          "meaning": "Recorded issued component quantity; compare using compatible units.",
          "unit": "item_dependent",
          "evidence": "repository_documented_not_tenant_tested"
        },
        {
          "name": "deletedat",
          "meaning": "Soft-delete marker; active facts are normally selected with IS NULL.",
          "unit": "timestamp",
          "evidence": "repository_documented_not_tenant_tested"
        }
      ]
    }
  ],
  "relationships": [
    {
      "id": "session_workorder",
      "from_table": "workflowsessionitem",
      "from_field": "workorderid",
      "to_table": "workorder",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/sessions.sql"
    },
    {
      "id": "stock_session",
      "from_table": "stock",
      "from_field": "workflowsessionitemid",
      "to_table": "workflowsessionitem",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/stock.sql"
    },
    {
      "id": "session_workflow",
      "from_table": "workflowsessionitem",
      "from_field": "workflowid",
      "to_table": "workflow",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/workflow-specification.sql"
    },
    {
      "id": "time_workorder",
      "from_table": "timelogreport",
      "from_field": "workorderid",
      "to_table": "workorder",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/labour-time.sql"
    },
    {
      "id": "time_user",
      "from_table": "timelogreport",
      "from_field": "userid",
      "to_table": "usermodel",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/labour-time.sql"
    },
    {
      "id": "time_workflow",
      "from_table": "timelogreport",
      "from_field": "workflowid",
      "to_table": "workflow",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/labour-time.sql"
    },
    {
      "id": "time_sessions",
      "from_table": "timelogreport",
      "from_field": "sessionids[]",
      "to_table": "workflowsessionitem",
      "to_field": "_id",
      "expected_cardinality": "array_many_to_many",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/labour-time.sql"
    },
    {
      "id": "form_definition",
      "from_table": "formdata",
      "from_field": "formid",
      "to_table": "form",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "saved_sql_join_read",
      "reference": "sql/form-record-links.sql"
    },
    {
      "id": "form_session",
      "from_table": "formdata",
      "from_field": "sessionid",
      "to_table": "workflowsessionitem",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/form-record-links.sql"
    },
    {
      "id": "form_workorder",
      "from_table": "formdata",
      "from_field": "workorderid",
      "to_table": "workorder",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/form-record-links.sql"
    },
    {
      "id": "record_session",
      "from_table": "workflowsessionitemrecord",
      "from_field": "workflowsessionitemid",
      "to_table": "workflowsessionitem",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/step-records.sql"
    },
    {
      "id": "value_session",
      "from_table": "sessioncustomfieldview",
      "from_field": "workflowsessionitemid",
      "to_table": "workflowsessionitem",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/field-values-light.sql"
    },
    {
      "id": "value_field",
      "from_table": "sessioncustomfieldview",
      "from_field": "fieldid",
      "to_table": "field",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/field-values-light.sql"
    },
    {
      "id": "component_parent",
      "from_table": "stockitemview",
      "from_field": "stockid",
      "to_table": "stock",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/as-built-components.sql"
    },
    {
      "id": "kit_workorder",
      "from_table": "workorderkititemview",
      "from_field": "workorderid",
      "to_table": "workorder",
      "to_field": "_id",
      "expected_cardinality": "many_to_optional_one",
      "cardinality_measured": false,
      "foreign_key_enforced": "not_verified",
      "evidence": "repository_documented_not_tenant_tested",
      "reference": "sql/kit-lines.sql"
    }
  ],
  "agent_rules": [
    "Confirm the authorized database and schema before querying. This file grants no access or write permission.",
    "Resolve table and field availability/types with focused inspection; null data_type means unknown, not varchar.",
    "Read the matching saved SQL and its filters; never infer the metric solely from its name.",
    "Treat identity_candidate and expected_cardinality as hypotheses to validate, not enforced database constraints.",
    "Filter soft-deleted facts with deletedat IS NULL. Place dimension soft-delete filters in LEFT JOIN ON when preserving unmatched facts; keep the original 47–48 behavior when reproducing it.",
    "Prefer internal IDs to names or business numbers. Do not invent an ID relationship from a similarly named field.",
    "Do not assume stock-to-session is one-to-one. Measure multiplicity and unmatched keys before joining.",
    "Never sum timer duration after expanding sessionids without an explicit allocation rule. Summed timer hours are not attendance.",
    "Do not inner-join historical facts to version definitions unless the requested population explicitly requires a matching definition.",
    "Use workflow._id for one version; workflow.recordid is a documented cross-version identity requiring target verification. Names are display labels.",
    "Do not infer pass/fail tolerances from field.min/max; these are input limits. Tenant field vocabulary and measurement units require discovery.",
    "Do not interpret event records as execution signatures or start/end differences as actual labour without event classification.",
    "Use read-only SELECT for diagnostics; validate totals, missing keys and row counts before and after joins. New examples in this map were not executed.",
    "Do not carry tenant values, record contents, identifiers or credentials into reusable documentation or another environment."
  ]
}