contextIsKey complete result bundle
Company: AsterVale Living
Evidence: Bundled AsterVale Living DataHub metadata
Change: customer_id varchar -> bigint

==============================================================================
IMPACT REPORT
==============================================================================
{
  "affected_assets": [
    {
      "critical": false,
      "hop": 1,
      "name": "fct_order_sales",
      "owners": [
        "commerce-analytics@astervale.demo"
      ]
    },
    {
      "critical": true,
      "hop": 2,
      "name": "loyalty_customer_value",
      "owners": [
        "loyalty-platform@astervale.demo"
      ]
    },
    {
      "critical": false,
      "hop": 2,
      "name": "regional_returns",
      "owners": [
        "store-operations@astervale.demo"
      ]
    },
    {
      "critical": true,
      "hop": 3,
      "name": "executive_revenue_dashboard",
      "owners": [
        "finance-data@astervale.demo"
      ]
    }
  ],
  "change": {
    "column": "customer_id",
    "current_type": "varchar",
    "proposed_type": "bigint",
    "source": "stg_orders.customer_id"
  },
  "company": "AsterVale Living",
  "evidence": {
    "confidence": "HIGH",
    "datahub_assets": 4,
    "hidden_sql_objects": 4,
    "regions": 5,
    "source": "Bundled AsterVale Living DataHub metadata"
  },
  "regional_exposure": [
    {
      "asset_names": [
        "fct_order_sales",
        "loyalty_customer_value",
        "executive_revenue_dashboard"
      ],
      "owners": [
        "commerce-analytics@astervale.demo",
        "loyalty-platform@astervale.demo",
        "finance-data@astervale.demo"
      ],
      "policy_flags": [
        "CUSTOMER_DATA",
        "CA_PRIVACY_REVIEW",
        "EXECUTIVE_REPORTING"
      ],
      "region": "NORTHEAST",
      "risk": "HIGH",
      "sql_objects": [
        "usp_reconcile_loyalty_customer",
        "vw_customer_revenue_bridge"
      ]
    },
    {
      "asset_names": [
        "fct_order_sales",
        "regional_returns",
        "executive_revenue_dashboard"
      ],
      "owners": [
        "commerce-analytics@astervale.demo",
        "store-operations@astervale.demo",
        "finance-data@astervale.demo"
      ],
      "policy_flags": [
        "EXECUTIVE_REPORTING"
      ],
      "region": "SOUTH",
      "risk": "MEDIUM",
      "sql_objects": [
        "usp_match_regional_returns",
        "vw_customer_revenue_bridge"
      ]
    },
    {
      "asset_names": [
        "fct_order_sales",
        "regional_returns",
        "executive_revenue_dashboard"
      ],
      "owners": [
        "commerce-analytics@astervale.demo",
        "store-operations@astervale.demo",
        "finance-data@astervale.demo"
      ],
      "policy_flags": [
        "EXECUTIVE_REPORTING"
      ],
      "region": "MIDWEST",
      "risk": "MEDIUM",
      "sql_objects": [
        "usp_match_regional_returns",
        "vw_customer_revenue_bridge"
      ]
    },
    {
      "asset_names": [
        "fct_order_sales",
        "loyalty_customer_value",
        "regional_returns",
        "executive_revenue_dashboard"
      ],
      "owners": [
        "commerce-analytics@astervale.demo",
        "loyalty-platform@astervale.demo",
        "store-operations@astervale.demo",
        "finance-data@astervale.demo"
      ],
      "policy_flags": [
        "CUSTOMER_DATA",
        "CA_PRIVACY_REVIEW",
        "EXECUTIVE_REPORTING"
      ],
      "region": "WEST",
      "risk": "HIGH",
      "sql_objects": [
        "usp_reconcile_loyalty_customer",
        "usp_match_regional_returns",
        "vw_customer_revenue_bridge"
      ]
    },
    {
      "asset_names": [],
      "owners": [],
      "policy_flags": [
        "REGION_METADATA_MISSING"
      ],
      "region": "UNKNOWN",
      "risk": "REVIEW",
      "sql_objects": [
        "usp_export_customer_segments"
      ]
    }
  ],
  "sql_dependencies": [
    {
      "confidence": "HIGH",
      "manual_review_reason": null,
      "match_kind": "convert",
      "object_name": "usp_reconcile_loyalty_customer",
      "object_type": "SQL_STORED_PROCEDURE",
      "proposed_sql": "SELECT customer_id FROM loyalty.member_snapshot WHERE TRY_CONVERT(BIGINT, customer_id) = @customer_id;",
      "regions": [
        "NORTHEAST",
        "WEST"
      ],
      "schema_name": "loyalty",
      "snippet": "SELECT customer_id FROM loyalty.member_snapshot WHERE TRY_CONVERT(INT, customer_id) = @customer_id;"
    },
    {
      "confidence": "MEDIUM",
      "manual_review_reason": "Verify both join operands are BIGINT before changing the source.",
      "match_kind": "join",
      "object_name": "usp_match_regional_returns",
      "object_type": "SQL_STORED_PROCEDURE",
      "proposed_sql": null,
      "regions": [
        "SOUTH",
        "MIDWEST",
        "WEST"
      ],
      "schema_name": "operations",
      "snippet": "SELECT r.return_id FROM operations.returns r JOIN commerce.orders o ON r.customer_id = o.customer_id;"
    },
    {
      "confidence": "HIGH",
      "manual_review_reason": null,
      "match_kind": "cast",
      "object_name": "vw_customer_revenue_bridge",
      "object_type": "VIEW",
      "proposed_sql": "SELECT TRY_CAST(customer_id AS BIGINT) AS customer_id, net_revenue FROM finance.revenue_daily;",
      "regions": [
        "NORTHEAST",
        "SOUTH",
        "MIDWEST",
        "WEST"
      ],
      "schema_name": "finance",
      "snippet": "SELECT CAST(customer_id AS INT) AS customer_id, net_revenue FROM finance.revenue_daily;"
    },
    {
      "confidence": "LOW",
      "manual_review_reason": "Contains dynamic SQL, so the referenced object and runtime type need manual review.",
      "match_kind": "dynamic_sql",
      "object_name": "usp_export_customer_segments",
      "object_type": "SQL_STORED_PROCEDURE",
      "proposed_sql": null,
      "regions": [],
      "schema_name": "exports",
      "snippet": "DECLARE @sql nvarchar(max) = N'SELECT customer_id FROM ' + @table; EXEC sys.sp_executesql @sql;"
    }
  ],
  "unresolved_risks": [
    "Hidden consumers outside the observed lineage may still depend on `customer_id`.",
    "Critical downstream assets require coordinated rollout: executive_revenue_dashboard, loyalty_customer_value.",
    "Verify both join operands are BIGINT before changing the source.",
    "Contains dynamic SQL, so the referenced object and runtime type need manual review."
  ]
}

==============================================================================
DISCOVERY QUERY
==============================================================================
-- Read-only contextIsKey dependency discovery for SQL Server
DECLARE @column_name sysname = N'customer_id';

SELECT
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS object_name,
    o.type_desc AS object_type,
    m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE m.definition LIKE N'%' + @column_name + N'%'
ORDER BY schema_name, object_name;


==============================================================================
PROPOSED FIXES
==============================================================================
-- contextIsKey generated review draft
-- No statement in this file has been executed.

-- loyalty.usp_reconcile_loyalty_customer [HIGH]
SELECT customer_id FROM loyalty.member_snapshot WHERE TRY_CONVERT(BIGINT, customer_id) = @customer_id;

-- operations.usp_match_regional_returns [MEDIUM]
-- MANUAL REVIEW: Verify both join operands are BIGINT before changing the source.
-- Original: SELECT r.return_id FROM operations.returns r JOIN commerce.orders o ON r.customer_id = o.customer_id;

-- finance.vw_customer_revenue_bridge [HIGH]
SELECT TRY_CAST(customer_id AS BIGINT) AS customer_id, net_revenue FROM finance.revenue_daily;

-- exports.usp_export_customer_segments [LOW]
-- MANUAL REVIEW: Contains dynamic SQL, so the referenced object and runtime type need manual review.
-- Original: DECLARE @sql nvarchar(max) = N'SELECT customer_id FROM ' + @table; EXEC sys.sp_executesql @sql;


==============================================================================
VALIDATION QUERIES
==============================================================================
-- Validate conversion coverage before cutover
SELECT
    COUNT_BIG(*) AS total_rows,
    SUM(CASE WHEN customer_id IS NOT NULL AND TRY_CONVERT(BIGINT, customer_id) IS NULL
        THEN 1 ELSE 0 END) AS conversion_failures
FROM stg_orders;

-- Validate downstream null and key behavior after migration
SELECT customer_id, COUNT_BIG(*) AS row_count
FROM stg_orders
GROUP BY customer_id
HAVING customer_id IS NULL;


==============================================================================
ROLLBACK RUNBOOK
==============================================================================
-- ROLLBACK RUNBOOK, owner approval required
-- 1. Keep the original customer_id VARCHAR contract available during rollout.
-- 2. Route consumers back to the original field or compatibility view.
-- 3. Revert stored procedure drafts in reverse dependency order.
-- 4. Re-run validation queries and confirm regional owners have recovered.


==============================================================================
SARIF RESULTS
==============================================================================
{
  "$schema": "https://json.schemastore.org/sarif-2.1.0.json",
  "runs": [
    {
      "results": [
        {
          "level": "note",
          "locations": [
            {
              "physicalLocation": {
                "artifactLocation": {
                  "uri": "database://loyalty/usp_reconcile_loyalty_customer"
                }
              }
            }
          ],
          "message": {
            "text": "Review generated convert migration fix."
          },
          "properties": {
            "confidence": "HIGH"
          },
          "ruleId": "changeproof/convert"
        },
        {
          "level": "warning",
          "locations": [
            {
              "physicalLocation": {
                "artifactLocation": {
                  "uri": "database://operations/usp_match_regional_returns"
                }
              }
            }
          ],
          "message": {
            "text": "Verify both join operands are BIGINT before changing the source."
          },
          "properties": {
            "confidence": "MEDIUM"
          },
          "ruleId": "changeproof/join"
        },
        {
          "level": "note",
          "locations": [
            {
              "physicalLocation": {
                "artifactLocation": {
                  "uri": "database://finance/vw_customer_revenue_bridge"
                }
              }
            }
          ],
          "message": {
            "text": "Review generated cast migration fix."
          },
          "properties": {
            "confidence": "HIGH"
          },
          "ruleId": "changeproof/cast"
        },
        {
          "level": "warning",
          "locations": [
            {
              "physicalLocation": {
                "artifactLocation": {
                  "uri": "database://exports/usp_export_customer_segments"
                }
              }
            }
          ],
          "message": {
            "text": "Contains dynamic SQL, so the referenced object and runtime type need manual review."
          },
          "properties": {
            "confidence": "LOW"
          },
          "ruleId": "changeproof/dynamic_sql"
        }
      ],
      "tool": {
        "driver": {
          "name": "contextIsKey",
          "version": "0.1.0"
        }
      }
    }
  ],
  "version": "2.1.0"
}