thebench.bio #1 Ecosystem for the Bio-Tech Industry
All SQL questions

Question 6 of 6 Advanced Scenario

How would you preserve a source-to-claim audit trail in relational tables?

Short answer

Give every source, version, claim and evidence relationship stable identifiers, and preserve immutable lineage across edits. A URL alone is not an auditable claim.

Why

Design sources(source_id, publisher, URL, access_date, document_version), claims(claim_id, statement, status), and claim_evidence(claim_id, source_id, locator, role, reviewer_id, decision_date). Store the cited page, section or exact passage locator where licensing permits, along with the particular source version and claim interpretation. Add change history for reviewer decisions, mappings and content versions rather than overwriting the earlier mapping in place.

Constraints should prevent broken references and accidental duplicate mappings, but allow one source to support multiple claims and conflicting evidence to be recorded explicitly. In a biotech content system, distinguish evidence selected for reading from independent scientific approval. Run checks for orphaned claim links, withdrawn sources, stale versions and records marked approved without reviewer evidence.

An audit trail should explain who concluded what, from which material, and when.

Common mistakes

  • Storing a single source URL in the question row and treating it as proof that every sentence was verified