referential integrity audit derived

{
  "input": {
    "body": {
      "child": "order_id,customer_id,amount\nO-1,C-001,10.10\nO-2,C-001,20.20\nO-3,C-002,5.00\nO-4,C-999,7.50\nO-5,,3.00\n",
      "parent": "customer_id,name,region\nC-001,Acme,ap-southeast-2\nC-002,Globex,us-east-1\nC-003,Initech,eu-west-1\nC-003,Initech Duplicate,eu-west-1\n",
      "child_key": [
        "customer_id"
      ],
      "parent_key": [
        "customer_id"
      ]
    },
    "type": "http",
    "method": "POST",
    "bodyType": "json"
  },
  "output": {
    "type": "json",
    "example": {
      "intact": false,
      "limits": {
        "maxRows": 5000,
        "maxListed": 200,
        "maxColumns": 64,
        "maxCsvBytes": 262144,
        "maxDatasets": 10,
        "maxFindings": 500,
        "maxCategories": 1000,
        "maxKeyColumns": 4
      },
      "passed": false,
      "complete": true,
      "findings": [
        {
          "key": "C-003",
          "row": 5,
          "code": "PARENT_KEY_DUPLICATED",
          "detail": "parent row 5 repeats the key C-003, so a reference to it is ambiguous.",
          "dataset": "parent"
        },
        {
          "key": "C-999",
          "code": "ORPHAN_REFERENCE",
          "detail": "child key C-999 has no matching parent, so row 5 reference something that does not exist.",
          "dataset": "child"
        }
      ],
      "request_id": "example-request",
      "service_id": "referential_integrity_audit",
      "assumptions": [
        "Containment is checked in one direction only. A child key with no parent is a broken reference; a parent key nothing references is normal and is reported as a count rather than a finding.",
        "Keys are canonicalised exactly, so 42.0 and 42 are the same key, and an identifier beyond exact numeric range is compared as text rather than collapsed.",
        "An empty reference is permitted by default, because an optional foreign key is a legitimate design."
      ],
      "limitations": [
        "Referential integrity between the two datasets supplied. Whether the parent set is complete is not knowable here.",
        "intact is true only when both datasets were fully read, every parent key was unique, and every child reference resolved."
      ],
      "observed_at": "2026-10-02T00:00:00.000Z",
      "orphan_keys": [
        {
          "key": "C-999",
          "rows": [
            5
          ]
        }
      ],
      "not_examined": [],
      "finding_count": 2,
      "child_row_count": 5,
      "service_version": "1.0.0",
      "findings_by_code": {
        "ORPHAN_REFERENCE": 1,
        "PARENT_KEY_DUPLICATED": 1
      },
      "orphan_key_count": 1,
      "orphan_row_count": 1,
      "parent_key_count": 3,
      "parent_row_count": 4,
      "empty_reference_count": 1,
      "unreferenced_key_count": 1,
      "duplicate_parent_key_count": 1
    }
  }
}