XRAY: How to fix Xray error: ”foreign key constraint” and blocking_bit_mask (SQLSTATE 23503)

Introduction 

Sometimes you may meet following error in Xray:

Xray UI shows 500 “Oops” , and 
Xray logs show PublicData update failures and foreign key violations, for example: 

[ERROR] Failed to complete online PublicData update
Caused by: Key (public_vulns_tbl_id)=(1800780580838666253) is not present in table "public_vulnerabilities".
Caused by: ERROR: insert or update on table "public_vulnerabilities_references" violates foreign key constraint "references_public_vulnerabilities_fk" (SQLSTATE 23503)
...
[ERROR] Failed to get user issues by watch id ... : failed to search user issues by watch ...
Caused by: sql: Scan error on column index 7, name "blocking_bit_mask": converting NULL to int is unsupported

Cause

The root cause of the error is: 

1. SQLSTATE 23503 indicates a foreign key violation. In this case, a row is being inserted/updated in public_vulnerabilities_references referencing a public_vulns_tbl_id that does not exist in public_vulnerabilities, suggesting inconsistent PublicData content (partial/failed update, leftover data, or migration inconsistency).
   
2. Separately, user_issues_matched_policies.blocking_bit_mask contains NULL values. Xray expects this field to be an integer and fails when scanning a NULL into an int. The column should have default 0 and should not be NULL.

Resolution 

1. Fix the NULL data in blocking_bit_mask
Run:

UPDATE public.user_issues_matched_policies
SET blocking_bit_mask = 0
WHERE blocking_bit_mask IS NULL;

2. Enforce a default value at the schema level
Run:

ALTER TABLE public.user_issues_matched_policies
ALTER COLUMN blocking_bit_mask SET DEFAULT 0;

3. Restart Xray and re-validate, the error should be fixed now.