Clinical Evidence and RWE Pipelines

# Record a SHA-256 checksum for every raw extract before any transform runs
sha256sum extracts/*.csv extracts/*.ndjson > extracts.sha256

# Later: show each raw copy is unchanged (prints OK or FAILED per file)
sha256sum --check extracts.sha256

-- Synthetic illustration: source codes with no mapping to a standard
-- concept, most frequent first. These are the records a sample-based
-- mapping check never sees.
CREATE TEMP TABLE IF NOT EXISTS source_observation (
  record_id TEXT PRIMARY KEY,
  source_system TEXT NOT NULL,
  source_vocabulary TEXT NOT NULL,
  source_code TEXT NOT NULL
);
CREATE TEMP TABLE IF NOT EXISTS concept_map (
  source_vocabulary TEXT NOT NULL,
  source_code TEXT NOT NULL,
  target_concept_id INTEGER NOT NULL,
  PRIMARY KEY (source_vocabulary, source_code)
);

SELECT o.source_system,
       o.source_vocabulary,
       o.source_code,
       COUNT(*) AS records
FROM source_observation o
LEFT JOIN concept_map m
  ON m.source_vocabulary = o.source_vocabulary
 AND m.source_code = o.source_code
WHERE m.target_concept_id IS NULL
GROUP BY o.source_system, o.source_vocabulary, o.source_code
ORDER BY records DESC, o.source_code;

-- Synthetic illustration: rows in a study dataset that cannot be traced to
-- a raw extract recorded in the checksum manifest.
CREATE TEMP TABLE IF NOT EXISTS raw_extract (
  extract_sha256 TEXT PRIMARY KEY,
  file_name TEXT NOT NULL,
  received_utc TEXT NOT NULL
);
CREATE TEMP TABLE IF NOT EXISTS analysis_row (
  row_id INTEGER PRIMARY KEY,
  dataset_id TEXT NOT NULL
);
CREATE TEMP TABLE IF NOT EXISTS row_source (
  row_id INTEGER NOT NULL REFERENCES analysis_row(row_id),
  source_record_id TEXT NOT NULL,
  extract_sha256 TEXT NOT NULL,
  PRIMARY KEY (row_id, source_record_id)
);

SELECT a.dataset_id,
       a.row_id
FROM analysis_row a
WHERE a.dataset_id = 'study-001'
  AND NOT EXISTS (
    SELECT 1
    FROM row_source s
    JOIN raw_extract e ON e.extract_sha256 = s.extract_sha256
    WHERE s.row_id = a.row_id
  )
ORDER BY a.row_id;