Files
paper-atlas/paper_atlas/audits/histories.py
codex-bot 5a925457b0
Metadata integrity checks / metadata (push) Successful in 3m0s
Audit cross-venue submission histories without inferring chronology
2026-11-08 04:30:19 +08:00

197 lines
10 KiB
Python

"""Audit linked submission histories without inferring event chronology."""
import argparse
from collections import Counter
import json
from pathlib import Path
import sqlite3
from paper_atlas.db.backend import database_arguments, open_read_connection
from paper_atlas.db.submissions import timestamp
from paper_atlas.querying import LATEST_DECISION
ATTEMPTS = '''SELECT s.paper_id,s.id AS submission_id,e.venue_id,v.acronym AS venue,e.year,e.track,
count(d.id) AS decision_observations,
MAX(CASE WHEN d.status IN ('rejected','desk_rejected','withdrawn') THEN 1 ELSE 0 END)
AS historical_negative,
MAX(CASE WHEN d.status='accepted' THEN 1 ELSE 0 END) AS historical_acceptance,
COALESCE(MAX(CASE WHEN ''' + LATEST_DECISION + ''' THEN d.status END),'unknown')
AS latest_observed_status
FROM submissions s JOIN editions e ON e.id=s.edition_id JOIN venues v ON v.id=e.venue_id
LEFT JOIN decisions d ON d.submission_id=s.id
GROUP BY s.paper_id,s.id,e.venue_id,v.acronym,e.year,e.track
ORDER BY s.paper_id,e.venue_id,s.id'''
def _paper_counts(attempts):
negative = sum(row['historical_negative'] for row in attempts)
accepted = sum(row['latest_observed_status'] == 'accepted' for row in attempts)
overlap = sum(row['historical_negative'] and row['latest_observed_status'] == 'accepted'
for row in attempts)
venues = {}
for row in attempts:
counts = venues.setdefault(row['venue_id'], [0, 0])
counts[0] += row['historical_negative']
counts[1] += row['latest_observed_status'] == 'accepted'
return {
'papers_with_submissions': 1,
'papers_with_multiple_submissions': int(len(attempts) > 1),
'papers_with_historical_negative': int(negative > 0),
'papers_with_current_known_acceptance': int(accepted > 0),
'papers_with_negative_and_acceptance_in_distinct_attempts': int(negative * accepted > overlap),
'papers_with_negative_and_acceptance_in_distinct_venues':
int(negative * accepted > sum(n * a for n, a in venues.values())),
}
def _example(connection, attempts):
# Keep a negative/accepted pair visible even when a paper has many attempts.
accepted = [row for row in attempts if row['latest_observed_status'] == 'accepted']
first = accepted[0] if accepted else None
other_venue = next((row for row in accepted if row['venue_id'] != first['venue_id']), None)
pair = None
for negative in attempts:
if negative['historical_negative'] and first:
candidate = first if first['venue_id'] != negative['venue_id'] else other_venue
if candidate:
pair = (negative, candidate)
break
if pair is None:
for negative in attempts:
if negative['historical_negative'] and first:
candidate = first if first['submission_id'] != negative['submission_id'] else (
accepted[1] if len(accepted) > 1 else None)
if candidate:
pair = (negative, candidate)
break
selected = {}
for row in list(pair or ()) + attempts:
if len(selected) < 20:
selected.setdefault(row['submission_id'], dict(row))
for row in selected.values():
row['decisions'] = []
for decision in connection.execute('''SELECT id,status,raw_value,evidence_url,
decision_date,observed_at FROM decisions WHERE submission_id=?
ORDER BY observed_at DESC,COALESCE(decision_date,observed_at) DESC,id DESC LIMIT 50''',
(row['submission_id'],)):
entry = dict(decision)
sources = [dict(source) for source in connection.execute('''
SELECT so.name,r.external_id,ds.source_decision_id FROM decision_sources ds
JOIN source_records r ON r.id=ds.source_record_id JOIN sources so ON so.id=r.source_id
WHERE ds.decision_id=? ORDER BY so.name,r.external_id LIMIT 101''', (entry['id'],))]
entry['sources'] = sources[:100]
entry['sources_truncated'] = len(sources) > 100
row['decisions'].append(entry)
row['decisions_truncated'] = row['decision_observations'] > len(row['decisions'])
paper_id = attempts[0]['paper_id']
return {'paper_id': paper_id,
'title': connection.execute('SELECT title FROM papers WHERE id=?', (paper_id,)).fetchone()[0],
'counts': _paper_counts(attempts), 'attempt_count': len(attempts),
'attempts': list(selected.values()), 'attempts_truncated': len(attempts) > len(selected),
'chronology': 'Not inferred from observed_at, edition year, or row order.'}
def report(db=None, *, pg_service=None, pg_schema=None, examples=20):
if not 0 <= examples <= 100:
raise ValueError('Examples must be between 0 and 100')
started_at = timestamp()
connection = open_read_connection(db, pg_service=pg_service, pg_schema=pg_schema)
if not pg_service:
connection.row_factory = sqlite3.Row
try:
if pg_service:
identity = connection.execute('SELECT current_database(),current_schema()').fetchone()
database, schema = identity[0], identity[1]
else:
database, schema = str(Path(db).resolve()), None
names = ['papers_with_submissions', 'papers_with_multiple_submissions',
'papers_with_historical_negative', 'papers_with_current_known_acceptance',
'papers_with_negative_and_acceptance_in_distinct_attempts',
'papers_with_negative_and_acceptance_in_distinct_venues']
totals = Counter({key: 0 for key in names})
totals.update(submissions=0, decision_observations=0,
attempts_with_historical_negative=0,
attempts_with_historical_negative_and_acceptance=0)
latest = Counter()
sample_attempts = []
def consume(rows):
if not rows:
return
counts = _paper_counts(rows)
totals.update(counts)
if counts['papers_with_negative_and_acceptance_in_distinct_attempts'] and len(sample_attempts) < examples:
sample_attempts.append(rows)
current = []
for raw in connection.execute(ATTEMPTS):
row = dict(raw)
if current and row['paper_id'] != current[0]['paper_id']:
consume(current)
current = []
current.append(row)
totals['submissions'] += 1
totals['decision_observations'] += row['decision_observations']
totals['attempts_with_historical_negative'] += row['historical_negative']
totals['attempts_with_historical_negative_and_acceptance'] += int(
row['historical_negative'] and row['historical_acceptance'])
latest[row['latest_observed_status']] += 1
consume(current)
# These are relationship checks, not a substitute for the final entity/FK audit.
integrity = {}
checks = {
'submissions_without_source_links': '''SELECT count(*) FROM submissions s
WHERE NOT EXISTS(SELECT 1 FROM submission_sources ss WHERE ss.submission_id=s.id)''',
'decisions_without_source_links': '''SELECT count(*) FROM decisions d
WHERE NOT EXISTS(SELECT 1 FROM decision_sources ds WHERE ds.decision_id=d.id)''',
'submission_source_paper_mismatches': '''SELECT count(*) FROM submission_sources ss
JOIN submissions s ON s.id=ss.submission_id JOIN source_records r ON r.id=ss.source_record_id
WHERE s.paper_id<>r.paper_id''',
'decision_source_paper_mismatches': '''SELECT count(*) FROM decision_sources ds
JOIN decisions d ON d.id=ds.decision_id JOIN submissions s ON s.id=d.submission_id
JOIN source_records r ON r.id=ds.source_record_id WHERE s.paper_id<>r.paper_id''',
}
for name, query in checks.items():
integrity[name] = connection.execute(query).fetchone()[0]
return {'report_started_at': started_at, 'checked_at': timestamp(),
'engine': 'postgresql' if pg_service else 'sqlite', 'database': database,
'schema': schema, 'pg_service': pg_service,
'status': 'observed_history_audit_not_final_dedup', 'counts': dict(totals),
'latest_attempt_status_counts': dict(sorted(latest.items())),
'evidence_relationship_checks': integrity,
'examples': [_example(connection, rows) for rows in sample_attempts],
'example_limit': examples,
'interpretation': [
'Counts use existing canonical paper links, not independently verified real-world identity.',
'Negative includes rejected, desk_rejected and withdrawn observations; these remain distinct decisions.',
'Known acceptance follows the latest observed decision per attempt, matching query --outcome.',
'Distinct attempts/venues require separate submission/venue IDs; aliases do not create new venues.',
'Coexisting evidence does not prove rejection happened before acceptance. observed_at is collection time.',
'No known acceptance is not proof of never accepted. Source signatures and completeness are not established here.',
'Examples are bounded; counts include all attempts and observations in one read-only snapshot.',
'This does not perform entity merging, final deduplication, or label namespace/type validation.',
]}
finally:
connection.close()
def main(argv=None, *, prog=None):
parser = argparse.ArgumentParser(prog=prog)
database_arguments(parser)
parser.add_argument('--examples', type=int, default=20)
parser.add_argument('--out')
args = parser.parse_args(argv)
result = report(args.db, pg_service=args.pg_service, pg_schema=args.pg_schema, examples=args.examples)
text = json.dumps(result, ensure_ascii=False, indent=2)
if args.out:
path = Path(args.out)
stage = path.with_suffix('.part')
stage.write_text(text + '\n')
stage.replace(path)
else:
print(text)
if __name__ == '__main__':
main()