Bound exact event migration counts with primary-key pagination

Reviewed-on: #17

.gitea/workflows/check.yml

@@ -49,12 +49,15 @@ jobs:
               subprocess.run([str(venv/'bin/paper-atlas'),'--help'],cwd=outside,check=True)
               subprocess.run([python,'-I','-B','-m','paper_atlas','collect','cvf','--help'],cwd=outside,check=True)
               subprocess.run([python,'-I','-B','-m','paper_atlas','maintain','postgres','--help'],cwd=outside,check=True)
+              subprocess.run([python,'-I','-B','-m','paper_atlas','maintain','event-identity','--help'],cwd=outside,check=True)
               subprocess.run([python,'-I','-B','-m','paper_atlas','import','records','--help'],cwd=outside,check=True)
               subprocess.run([python,'-I','-B','-m','paper_atlas','import','arxiv','--help'],cwd=outside,check=True)
               subprocess.run([python,'-I','-B','-m','paper_atlas','maintain','backfill','--help'],cwd=outside,check=True)
               subprocess.run([python,'-I','-B','-m','paper_atlas','audit','public-openreview','--help'],cwd=outside,check=True)
+              subprocess.run([python,'-I','-B','-m','paper_atlas','audit','histories','--help'],cwd=outside,check=True)
               subprocess.run([python,'-I','-B','-c','from paper_atlas.postgres.writer import PostgresStore, WriteConnection'],cwd=outside,check=True)
               smoke = "from paper_atlas.db.store import Store; from paper_atlas.config import DEFAULT_REGISTRY; import json; assert {'COLM','MM','MLSys'} <= {v['acronym'] for v in json.loads(DEFAULT_REGISTRY.read_text())['venues']}; s=Store('installed.sqlite'); s.conn.close()"
               subprocess.run([python,'-I','-B','-c',smoke],cwd=outside,check=True)
               subprocess.run([python,'-I','-B','-m','paper_atlas','query','--db','installed.sqlite','--stats'],cwd=outside,check=True)
+              subprocess.run([python,'-I','-B','-m','paper_atlas','audit','histories','--db','installed.sqlite','--examples','0'],cwd=outside,check=True)
           PY

README.md

@@ -86,3 +86,5 @@ paper-atlas collect colm --cache /data/paper-index/raw/http-cache \
 CI 在已授权的远程 runner 上运行测试、构建 wheel，并从源码目录外验证安装后的命令及随包资源。数据库全量去重和覆盖率是独立的数据验收步骤，不因代码测试通过而自动完成。
 
 开发通过工作分支和 PR 交付，由用户手动合并。
+
+同年多届的 `event_key`、显式迁移、旧文件刷新与查询边界见 [会议事件身份](docs/event-identity.md)。

docs/event-identity.md

@@ -0,0 +1,44 @@
+# 同年多届会议的事件身份
+
+`venue`、`year`、`track` 描述系列、年份和投稿轨道；`event_key` 区分同系列、同年、同轨道的实际会议事件。例如 WWW 1995 的春季与秋季会议分别使用 `www-1995-spring` 和 `www-1995-fall`，两者仍是 `main`。相同论文可以在两届有不同投稿与决定；论文去重不会把这两次投稿合成一次。
+
+普通单届数据继续使用空键。新输入在同一组已有具名事件或已知同年多届时必须提供明确键；不能任选第一个 edition。已有来源刷新可依据自己的原投稿关系回到原事件，不能被显式改到另一事件。精确固定 DBLP 快照、WWW Fall 书目名、1995 年及 `www1995f.html` 卷共同支持旧秋季文件的归属；不按标题猜年份或季节。
+
+规范 JSONL 行及 manifest 都可提供 `event_key`，非空值限制为最多80个小写字母、数字及 `._:-`，首字符为字母或数字。manifest 提供键时，每行必须一致；一份文件仍仅对应一个事件及轨道。跨行冲突会回滚整份业务导入。身份回填同样按事件选择，不能借机把来源移到另一届。
+
+## 新样本与既有数据
+
+新建 SQLite 样本的 `editions` 唯一键为 `(venue_id, year, track, event_key)`。已有 SQLite/PostgreSQL 不会因普通读写命令自动升级。其普通单届导入继续可用；WWW1994/1995或带事件键的导入会要求先迁移。冻结的 SQLite 生产备份仍受 cutover 标记保护，不能写入或升级。
+
+先在 PostgreSQL 生成只读计划：
+
+```sh
+PGSERVICEFILE=/etc/paper-atlas/pg_service.conf paper-atlas maintain event-identity \
+  --pg-service foundry-research --pg-schema research --out /data/paper-index/event-plan.json
+```
+
+检查计划的数据库身份、原 edition IDs、业务表数量、目标来源与历史指纹。精确计数在同一快照中按主键分页，每页最多5000行，只读取主键；不使用估计行数。CLI向stderr持续报告计数进度，避免大库长时间无输出。已有未标记 WWW1995 edition 必须逐投稿都有固定 DBLP Fall 卷证据，才会只为原 edition 填入 `www-1995-fall`；缺证或混合卷会拒绝计划。本文命令不表示生产已经迁移。
+
+应用时传入同一文件及命令输出的文件 SHA256：
+
+```sh
+PGSERVICEFILE=/etc/paper-atlas/pg_service.conf paper-atlas maintain event-identity \
+  --pg-service foundry-research --pg-schema research \
+  --apply-plan /data/paper-index/event-plan.json --plan-sha256 PLAN_FILE_SHA256 \
+  --out /data/paper-index/event-applied.json
+```
+
+原生 PostgreSQL writer 使用现有 schema advisory lock，并在事务中锁定 editions，核对计划后添加列及替换唯一约束。SQLite 样本使用独占写事务，重建父表时保留 IDs，并在提交前检查外键；不级联删除子记录。额外列、未知唯一约束或 SQLite 额外索引会拒绝自动升级，须单独评估。未改变原版四列的排序内容哈希、任何业务表数量或目标投稿/决定/来源内容，才可提交；失败回滚。迁移标记与 DDL 同事务。原计划立即重试为 `already_applied`；若后续业务已经变化，应重新生成计划，不重放旧计划。
+
+ECML 与 PKDD 的联合举办前事件已有不同 venue/edition，保持原身份即可。这项迁移不会把它们合并，也不会创建新的论文、投稿或决定。WWW 春季及1994两届的逐篇采集、来源核验和导入仍须另做。
+
+## 查询与验收
+
+```sh
+paper-atlas query --pg-service foundry-research --pg-schema research \
+  --venue WWW --year 1995 --track main --event-key www-1995-fall
+```
+
+详细投稿历史及 `known_acceptances` 返回 `event_key`。`--event-key` 可与 `--status` 一起限定同一事件中的最新已知决定。未迁移的旧库仍可普通查询，其事件键为空；不能声称已经区分季节。`audit histories` 的新库事件输出也保留键。
+
+远程测试应覆盖同一作品的两届独立投稿、拒稿后另一届录用、原 IDs 与 Unicode/NUL 来源内容保留、旧文件刷新、歧义及错误键拒绝、整文件回滚、明确事件的身份回填、旧计划失效、重试与真实 PostgreSQL 迁移。生产应用后还须独立核验约束、来源和历史、全文索引及 foundry-core 健康；单元测试不替代生产或最终全库去重验收。

docs/usage.md

@@ -79,10 +79,10 @@ paper-atlas maintain merge --db /data/paper-index/samples/example.sqlite \
 ## 4. 查询与分析前检查
 
 ```sh
-paper-atlas query --db /data/paper-index/papers.sqlite --query '"language model"' --limit 20
-paper-atlas query --db /data/paper-index/papers.sqlite --tag arxiv:category=cs.AI
-paper-atlas query --db /data/paper-index/papers.sqlite --venue ICLR --year 2025 --status rejected
-paper-atlas query --db /data/paper-index/papers.sqlite --paper 123
+paper-atlas query --pg-service foundry-research --query '"language model"' --limit 20
+paper-atlas query --pg-service foundry-research --tag arxiv:category=cs.AI
+paper-atlas query --pg-service foundry-research --venue ICLR --year 2025 --status rejected
+paper-atlas query --pg-service foundry-research --paper 123
 paper-atlas coverage --db /data/paper-index/papers.sqlite --out /data/paper-index/collected/coverage.json
 ```
 
@@ -91,10 +91,26 @@ paper-atlas coverage --db /data/paper-index/papers.sqlite --out /data/paper-inde
 例如，查找在 ICLR 有拒稿记录、但在其他已知投稿中已有录用的论文：
 
 ```sh
-paper-atlas query --db /data/paper-index/papers.sqlite \\
+paper-atlas query --pg-service foundry-research \
   --venue ICLR --status rejected --outcome accepted
 ```
 
 `known_acceptances` 列出目前仍为录用状态的各次投稿及会议、年份、track 和决定证据，`publication_outcome` 汇总为 accepted 或 no_known_acceptance。汇总会检查所有会议和 track，不把某次拒稿覆盖到整篇论文；同一次投稿的后续撤稿也会更新该次状态并保留原决定。no_known_acceptance 只表示现有来源中没有查到录用，不能称为“从未录用”。
 
 `--stats` 中 papers 是当前规则下的论文实体数；source_records 是来源记录数；decisions 是决定观察数。一个投稿可能有多个来源和多条观察，不能相加当作论文数。
+
+## 5. 投稿历史关系审计
+
+```sh
+PGSERVICEFILE=/etc/paper-atlas/pg_service.conf paper-atlas audit histories \
+  --pg-service foundry-research --pg-schema research --examples 20 \
+  --out /data/paper-index/collected/history-audit.json
+```
+
+使用一个只读一致性快照，区分论文实体、投稿和决定观察；分别统计同次投稿曾出现负面与录用观察、不同投稿共存负面与当前已知录用、不同会议之间的共存。workshop/main 是不同 track，属于同一会议时不算跨会议；会议别名也不会增加一个会议。当前已知录用使用与 `query --outcome` 相同的“每次投稿最新观察”规则，后续撤稿的该次投稿不再算当前录用，但旧录用决定仍保留。
+
+报告不凭观察时间、届次年份或行顺序断言“拒稿发生在录用之前”。样例保留真实 `decision_date`、`observed_at`、原始标签、证据 URL 和决定来源，供进一步核查；日期缺失仍保持未知。`negative` 统计包含 rejected、desk_rejected 和 withdrawn，其原始状态不互相替换。
+
+总数覆盖快照内全部投稿，样例由 `--examples` 限定为 0–100 篇；每篇最多展示 20 次投稿、每次最多 50 条决定、每条决定最多 100 个来源，并明确标记截断。样例优先保留负面/录用的不同投稿对。四项来源关系检查报告缺失关联和来源论文归属不一致，不自动修复。
+
+这些统计依赖现有论文身份关联，不证明实体合并正确，也不证明原始决定签名、公开拒稿全集、标签类型或最终全库去重已完成。SQLite 独立样本可使用 `--db`；冻结备份不能代表当前 PostgreSQL 的历史情况。

paper_atlas/audits/histories.py

@@ -0,0 +1,200 @@
+"""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 = []
+        from paper_atlas.db.events import has_event_key
+        attempts_sql = ATTEMPTS
+        if has_event_key(connection):
+            attempts_sql = attempts_sql.replace('e.year,e.track,', 'e.year,e.track,e.event_key,', 1).replace('e.year,e.track\n', 'e.year,e.track,e.event_key\n')
+        for raw in connection.execute(attempts_sql):
+            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()

paper_atlas/audits/public_openreview.py

@@ -39,7 +39,9 @@ def audit(db, inventory, out, *, pg_service=None, pg_schema=None):
             if json.loads(path.with_suffix('.manifest.json').read_text()) != manifest:
                 raise ValueError('Collected manifest differs from inventory')
             count = 0
-            for line in path.read_text().splitlines():
+            # Unicode paragraph/line separators are valid inside JSON strings;
+            # split physical byte lines, not Unicode text boundaries.
+            for line in path.read_bytes().splitlines():
                 row = json.loads(line)
                 if any(row[key] != manifest[key] for key in ('source', 'venue', 'year', 'track')):
                     raise ValueError('Record edition differs from inventory')

paper_atlas/cli.py

@@ -25,6 +25,7 @@ COMMANDS = {
     ('catalogue','dblp'): ('paper_atlas.collectors.dblp', {}, '盘点 DBLP 历史会议目录'),
     ('import','arxiv'): ('paper_atlas.ingestion.arxiv', {}, '导入 arXiv 元数据 ZIP'),
     ('import','records'): ('paper_atlas.ingestion.records', {}, '导入核验过的会议文件'),
+    ('maintain','event-identity'): ('paper_atlas.ingestion.event_identity', {}, '按核验计划增加同年会议事件身份'),
     ('maintain','merge'): ('paper_atlas.ingestion.maintenance', {}, '协调单写入合并与恢复'),
     ('maintain','backfill'): ('paper_atlas.ingestion.backfill', {}, '回填既有投稿的来源身份'),
     ('maintain','arxiv-tags'): ('paper_atlas.ingestion.arxiv_tags', {}, '恢复逐来源记录的 arXiv 类别证据'),
@@ -35,6 +36,7 @@ COMMANDS = {
     ('audit','arxiv'): ('paper_atlas.audits.arxiv', {}, '核验 arXiv 原始编号、重复行与 ZIP 完整性'),
     ('audit','arxiv-tags'): ('paper_atlas.audits.arxiv_tags', {}, '逐记录核验 arXiv 类别及出处完整性'),
     ('audit','public-openreview'): ('paper_atlas.audits.public_openreview', {}, '核对公开负面决定与已有投稿历史'),
+    ('audit','histories'): ('paper_atlas.audits.histories', {}, '只读核对跨会议投稿历史及决定来源关系'),
 }
 
 

paper_atlas/db/events.py

@@ -0,0 +1,71 @@
+"""Stable event identity within a venue/year/track, independent of paper identity."""
+import json
+import re
+
+
+def has_event_key(c):
+    if getattr(c, 'postgres', False):
+        return bool(c.execute("SELECT 1 FROM information_schema.columns WHERE table_schema=current_schema() AND table_name='editions' AND column_name='event_key'").fetchone())
+    return any(row[1] == 'event_key' for row in c.execute('PRAGMA table_info(editions)'))
+
+
+def validate_event_key(value):
+    if value is not None and (not isinstance(value, str) or
+            (value != '' and not re.fullmatch(r'[a-z0-9][a-z0-9._:-]{0,79}', value))):
+        raise ValueError('Invalid conference event_key')
+    return value
+
+
+def pinned_www_fall(source, metadata):
+    from paper_atlas.collectors.dblp import XML_SHA
+    b = metadata.get('bibliography') or {}
+    urls = b.get('url') or []
+    return (source == 'dblp' and metadata.get('snapshot_sha256') == XML_SHA
+            and metadata.get('bibliographic_year') == 1995
+            and str(metadata.get('proceedings_key', '')).startswith('conf/www/')
+            and b.get('booktitle') == ['WWW (Fall)'] and len(urls) == 1
+            and urls[0].split('#')[0] == 'db/conf/www/www1995f.html')
+
+
+def find_edition(store, venue_id, venue, year, track, record, external_id,
+                 event_key=None, *, create=True):
+    c = store.conn
+    validate_event_key(event_key)
+    enabled = has_event_key(c) if getattr(store, 'event_identity', None) is None else store.event_identity
+    known_multi = venue.upper() == 'WWW' and int(year) in (1994, 1995)
+    source = c.execute('SELECT src.name,r.metadata_json FROM source_records r JOIN sources src ON src.id=r.source_id WHERE r.id=?', (record,)).fetchone()
+    inferred = ('www-1995-fall' if known_multi and int(year) == 1995 and source
+                and pinned_www_fall(source[0], json.loads(source[1])) else None)
+    if inferred:
+        if event_key is not None and event_key != inferred:
+            raise ValueError('event_key conflicts with pinned WWW Fall source evidence')
+        event_key = inferred
+    if not enabled:
+        if event_key or known_multi:
+            raise ValueError('Explicit event identity migration required before this conference import')
+        row = c.execute('SELECT id FROM editions WHERE venue_id=? AND year=? AND track=?', (venue_id, year, track)).fetchone()
+        if row or not create:
+            return row[0] if row else None
+        c.execute('INSERT INTO editions(venue_id,year,track) VALUES(?,?,?) ON CONFLICT DO NOTHING', (venue_id, year, track))
+        return c.execute('SELECT id FROM editions WHERE venue_id=? AND year=? AND track=?', (venue_id, year, track)).fetchone()[0]
+    anchored = c.execute('''SELECT DISTINCT e.id,e.event_key FROM editions e JOIN submissions s ON s.edition_id=e.id
+        JOIN submission_sources ss ON ss.submission_id=s.id WHERE e.venue_id=? AND e.year=? AND e.track=?
+        AND ss.source_record_id=? AND ss.source_submission_id=?''', (venue_id, year, track, record, external_id)).fetchall()
+    if len(anchored) > 1:
+        raise ValueError('Source record already has ambiguous conference events')
+    if anchored:
+        if event_key is not None and event_key != anchored[0][1]:
+            raise ValueError('Cannot move an existing source to another conference event')
+        event_key = anchored[0][1]
+    if event_key is None:
+        rows = c.execute('SELECT event_key FROM editions WHERE venue_id=? AND year=? AND track=?', (venue_id, year, track)).fetchall()
+        if len(rows) > 1 or any(row[0] for row in rows):
+            raise ValueError('Explicit event_key required; conference event is ambiguous')
+        event_key = ''
+    if known_multi and not event_key:
+        raise ValueError('Explicit event_key required for same-year WWW events')
+    row = c.execute('SELECT id FROM editions WHERE venue_id=? AND year=? AND track=? AND event_key=?', (venue_id, year, track, event_key)).fetchone()
+    if row or not create:
+        return row[0] if row else None
+    c.execute('INSERT INTO editions(venue_id,year,track,event_key) VALUES(?,?,?,?) ON CONFLICT DO NOTHING', (venue_id, year, track, event_key))
+    return c.execute('SELECT id FROM editions WHERE venue_id=? AND year=? AND track=? AND event_key=?', (venue_id, year, track, event_key)).fetchone()[0]

paper_atlas/db/schema.sql

@@ -29,7 +29,8 @@ CREATE TABLE IF NOT EXISTS venues (
 CREATE TABLE IF NOT EXISTS editions (
  id INTEGER PRIMARY KEY, venue_id INTEGER NOT NULL REFERENCES venues(id),
  year INTEGER NOT NULL, track TEXT NOT NULL DEFAULT 'main',
- UNIQUE(venue_id,year,track)
+ event_key TEXT NOT NULL DEFAULT '',
+ UNIQUE(venue_id,year,track,event_key)
 );
 CREATE TABLE IF NOT EXISTS venue_aliases (
  venue_id INTEGER NOT NULL REFERENCES venues(id), alias TEXT NOT NULL,

paper_atlas/db/store.py

@@ -36,6 +36,8 @@ class Store:
         self.conn.execute('PRAGMA synchronous=NORMAL')
         self.conn.execute('PRAGMA cache_size=-32768')
         migrate_sources(self.conn)
+        from paper_atlas.db.events import has_event_key
+        self.event_identity = has_event_key(self.conn)
 
     def source(self,name,url):
         self.conn.execute('INSERT INTO sources(name,homepage) VALUES(?,?) ON CONFLICT DO NOTHING',(name,url))
@@ -142,15 +144,15 @@ class Store:
             tag=c.execute('SELECT id FROM tags WHERE namespace=? AND value=?',(namespace,value)).fetchone()[0]
             c.execute('INSERT INTO paper_tags VALUES(?,?,?,?) ON CONFLICT DO NOTHING',(paper,tag,record,evidence))
 
-    def decision(self,paper,record,venue,year,track,external_id,url,status,raw_value,aliases=None,identities=None,observed_at=None,decision_date=None,source_decision_id='',submitted_at=None,guard_status=False):
+    def decision(self,paper,record,venue,year,track,external_id,url,status,raw_value,aliases=None,identities=None,observed_at=None,decision_date=None,source_decision_id='',submitted_at=None,guard_status=False,event_key=None):
         c = self.conn
         existing=c.execute('SELECT id FROM venues WHERE lower(acronym)=lower(?)',(venue,)).fetchone()
         if existing:venue_id=existing[0]
         else:venue_id=inserted_id(c,"INSERT INTO venues(acronym,name,field,venue_type) VALUES(?,?,?,'conference')",(venue,venue,'unclassified'))
         for alias in aliases or []:
             c.execute('INSERT INTO venue_aliases VALUES(?,?) ON CONFLICT DO NOTHING',(venue_id,alias))
-        c.execute('INSERT INTO editions(venue_id,year,track) VALUES(?,?,?) ON CONFLICT DO NOTHING',(venue_id,int(year),track))
-        edition = c.execute('SELECT id FROM editions WHERE venue_id=? AND year=? AND track=?',(venue_id,int(year),track)).fetchone()[0]
+        from paper_atlas.db.events import find_edition
+        edition = find_edition(self,venue_id,venue,int(year),track,record,external_id,event_key)
         # A previous event in this row may already have merged the input paper.
         paper=c.execute('SELECT paper_id FROM source_records WHERE id=?',(record,)).fetchone()[0]
         submission=attach_submission(self,edition,paper,record,external_id,identities or [])

paper_atlas/ingestion/backfill.py

@@ -37,9 +37,14 @@ def backfill(db,files, *, pg_service=None, pg_schema=None):
                     seen.add(row['external_id'])
                     if any(row.get(key)!=manifest.get(key) for key in ('source','venue','year','track')):
                         raise ValueError('Evidence row differs from edition manifest')
+                    if 'event_key' in manifest and row.get('event_key')!=manifest['event_key']:
+                        raise ValueError('Event identity differs from manifest')
                     require_track_record(row, manifest)
                     record=c.execute('SELECT r.id,r.paper_id,r.metadata_json FROM source_records r JOIN sources s ON s.id=r.source_id WHERE s.name=? AND r.external_id=?',(row['source'],row['external_id'])).fetchone()
-                    edition=c.execute('SELECT e.id FROM editions e JOIN venues v ON v.id=e.venue_id WHERE lower(v.acronym)=lower(?) AND e.year=? AND e.track=?',(row['venue'],row['year'],row['track'])).fetchone()
+                    from paper_atlas.db.events import find_edition
+                    venue=c.execute('SELECT id FROM venues WHERE lower(acronym)=lower(?)',(row['venue'],)).fetchone()
+                    eid=find_edition(store,venue[0],row['venue'],row['year'],row['track'],record[0],row['external_id'],row.get('event_key'),create=False) if record and venue else None
+                    edition=(eid,) if eid is not None else None
                     if not record or not edition:raise ValueError('Evidence backfill requires an already imported record and edition')
                     if not c.execute('SELECT 1 FROM submission_sources ss JOIN submissions s ON s.id=ss.submission_id WHERE ss.source_record_id=? AND ss.source_submission_id=? AND s.edition_id=?',(record[0],row['external_id'],edition[0])).fetchone():
                         raise ValueError('No existing submission to backfill')

paper_atlas/ingestion/event_identity.py

@@ -0,0 +1,217 @@
+"""Explicit, plan-bound edition event-key upgrade; never reimport submissions."""
+import argparse
+from contextlib import closing
+import hashlib
+import json
+from pathlib import Path
+import sqlite3
+import sys
+
+from paper_atlas.db.backend import database_arguments, open_read_connection, require_sqlite_writable
+from paper_atlas.db.events import has_event_key, pinned_www_fall
+from paper_atlas.postgres.schema import TABLES, quote
+
+MARKER = 'edition_event_identity_v1:'
+
+
+def digest(value):
+    return hashlib.sha256(json.dumps(value, ensure_ascii=False, sort_keys=True, separators=(',', ':')).encode()).hexdigest()
+
+
+def values(rows):
+    return [[r[i] for i in range(len(r))] for r in rows]
+
+
+def exact_counts(c, *, page_size=5000, progress=None):
+    """Count every row in the same snapshot through bounded primary-key pages."""
+    if not isinstance(page_size, int) or page_size < 1:
+        raise ValueError('Positive count page size required')
+    result = {}
+    for table in TABLES:
+        if table == 'schema_migrations':
+            continue
+        if getattr(c, 'postgres', False):
+            columns = [r[0] for r in c.execute("SELECT a.attname FROM pg_constraint pc JOIN pg_attribute a ON a.attrelid=pc.conrelid AND a.attnum=ANY(pc.conkey) WHERE pc.conrelid=?::regclass AND pc.contype='p' ORDER BY array_position(pc.conkey,a.attnum)", (table,))]
+        else:
+            columns = [r[1] for r in sorted(c.execute('PRAGMA table_info('+quote(table)+')'), key=lambda r:r[5]) if r[5]]
+        if not columns:
+            raise ValueError('Primary key required for exact count: '+table)
+        fields = ','.join(quote(column) for column in columns)
+        last = None; count = 0; pages = 0
+        if progress:
+            progress({'table':table, 'rows_counted':0, 'complete':False})
+        while True:
+            where = (' WHERE ('+fields+') > ('+','.join('?' for _ in columns)+')') if last is not None else ''
+            batch = values(c.execute('SELECT '+fields+' FROM '+quote(table)+where+' ORDER BY '+fields+' LIMIT ?', (*last, page_size) if last is not None else (page_size,)).fetchall())
+            count += len(batch); pages += 1
+            complete = len(batch) < page_size
+            if progress and (complete or pages % 20 == 0):
+                progress({'table':table, 'rows_counted':count, 'complete':complete})
+            if complete:
+                break
+            last = batch[-1]
+        result[table] = count
+    return result
+
+
+def inspect(c, *, progress=None):
+    enabled = has_event_key(c)
+    if getattr(c, 'postgres', False):
+        columns = [r[0] for r in c.execute("SELECT column_name FROM information_schema.columns WHERE table_schema=current_schema() AND table_name='editions' ORDER BY ordinal_position")]
+        unique = [r[0] for r in c.execute("SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid='editions'::regclass AND contype='u'")]
+        expected_unique = ['UNIQUE (venue_id, year, track, event_key)' if enabled else 'UNIQUE (venue_id, year, track)']
+    else:
+        columns = [r[1] for r in c.execute('PRAGMA table_info(editions)')]
+        indexes = list(c.execute('PRAGMA index_list(editions)'))
+        unique = [tuple(r[2] for r in c.execute('PRAGMA index_info('+quote(index[1])+')')) for index in indexes if index[2]]
+        expected_unique = [('venue_id','year','track','event_key') if enabled else ('venue_id','year','track')]
+        if len(indexes) != 1:
+            raise ValueError('Unexpected edition indexes; require a reviewed migration')
+    if columns != ['id','venue_id','year','track'] + (['event_key'] if enabled else []) or unique != expected_unique:
+        raise ValueError('Unexpected edition schema; require a reviewed migration')
+    rows = values(c.execute('SELECT id,venue_id,year,track FROM editions ORDER BY id'))
+    keys = values(c.execute('SELECT id,event_key FROM editions ORDER BY id')) if enabled else [[r[0], ''] for r in rows]
+    counts = exact_counts(c, progress=progress)
+    targets = values(c.execute("SELECT e.id FROM editions e JOIN venues v ON v.id=e.venue_id WHERE v.acronym='WWW' AND e.year=1995 AND e.track='main' ORDER BY e.id"))
+    assignments = []
+    for (eid,) in targets:
+        current_key = dict(keys)[eid]
+        if current_key:
+            continue
+        evidence = values(c.execute('''SELECT s.id,src.name,r.id,r.external_id,r.metadata_json FROM submissions s
+            JOIN submission_sources ss ON ss.submission_id=s.id JOIN source_records r ON r.id=ss.source_record_id
+            JOIN sources src ON src.id=r.source_id WHERE s.edition_id=? ORDER BY s.id,r.id''', (eid,)))
+        count = c.execute('SELECT count(*) FROM submissions WHERE edition_id=?', (eid,)).fetchone()[0]
+        supported = {r[0] for r in evidence if pinned_www_fall(r[1], json.loads(r[4]))}
+        if not count or len(supported) != count:
+            raise ValueError('Existing unlabelled WWW1995 edition lacks complete pinned Fall evidence')
+        assignments.append({'edition_id':eid, 'event_key':'www-1995-fall', 'submissions':count,
+                            'source_evidence_sha256':digest(evidence)})
+    # Protect all rows in the affected attempt history, including non-DBLP evidence.
+    target_ids = [r[0] for r in targets]
+    protected = {}
+    for table,sql in {
+        'submissions': 'SELECT s.* FROM submissions s WHERE s.edition_id IN ({}) ORDER BY s.id',
+        'decisions': 'SELECT d.* FROM decisions d JOIN submissions s ON s.id=d.submission_id WHERE s.edition_id IN ({}) ORDER BY d.id',
+        'submission_sources': 'SELECT ss.* FROM submission_sources ss JOIN submissions s ON s.id=ss.submission_id WHERE s.edition_id IN ({}) ORDER BY ss.submission_id,ss.source_record_id,ss.source_submission_id',
+        'decision_sources': 'SELECT ds.* FROM decision_sources ds JOIN decisions d ON d.id=ds.decision_id JOIN submissions s ON s.id=d.submission_id WHERE s.edition_id IN ({}) ORDER BY ds.decision_id,ds.source_record_id,ds.source_decision_id',
+        'source_records': 'SELECT DISTINCT r.* FROM source_records r JOIN submission_sources ss ON ss.source_record_id=r.id JOIN submissions s ON s.id=ss.submission_id WHERE s.edition_id IN ({}) ORDER BY r.id',
+    }.items():
+        records = values(c.execute(sql.format(','.join('?' for _ in target_ids)), target_ids)) if target_ids else []
+        protected[table] = {'count':len(records), 'sha256':digest(records)}
+    return {'event_column':enabled, 'editions_sha256':digest(rows), 'edition_ids':[r[0] for r in rows],
+            'event_keys':keys, 'counts':counts, 'assignments':assignments, 'protected':protected}
+
+
+def database_identity(c, db=None):
+    if getattr(c, 'postgres', False):
+        r = c.execute('SELECT current_database(),current_schema()').fetchone()
+        return {'engine':'postgres', 'database':r[0], 'schema':r[1]}
+    return {'engine':'sqlite', 'path':str(Path(db).resolve())}
+
+
+def plan(db=None, *, pg_service=None, pg_schema=None, progress=None):
+    with closing(open_read_connection(db, pg_service=pg_service, pg_schema=pg_schema)) as c:
+        return {'format':1, 'database':database_identity(c, db), 'before':inspect(c, progress=progress)}
+
+
+def apply_plan(c, proposal, *, progress=None):
+    before = proposal['before']
+    if proposal.get('format') != 1:
+        raise ValueError('Unsupported event identity plan')
+    if getattr(c, 'postgres', False):
+        c.execute('LOCK TABLE editions IN ACCESS EXCLUSIVE MODE')
+    current = inspect(c, progress=progress)
+    signature = digest(proposal)
+    marker = MARKER + signature
+    if c.execute('SELECT 1 FROM schema_migrations WHERE name=?', (marker,)).fetchone():
+        expected_keys = dict(before['event_keys'])
+        expected_keys.update({a['edition_id']:a['event_key'] for a in before['assignments']})
+        if (not current['event_column'] or current['editions_sha256'] != before['editions_sha256']
+                or current['protected'] != before['protected'] or current['counts'] != before['counts']
+                or dict(current['event_keys']) != expected_keys):
+            raise ValueError('Applied plan no longer matches current data; generate a new plan')
+        return {'status':'already_applied', 'updated_editions':0, 'after':current}
+    if current != before:
+        raise ValueError('Event identity plan is stale; generate a new plan')
+    if not current['event_column']:
+        if getattr(c, 'postgres', False):
+            constraints = c.execute("SELECT conname,pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid='editions'::regclass AND contype='u'").fetchall()
+            matches = [name for name,definition in constraints if definition == 'UNIQUE (venue_id, year, track)']
+            if len(matches) != 1 or len(constraints) != 1:
+                raise ValueError('Unexpected legacy edition uniqueness')
+            c.execute("ALTER TABLE editions ADD COLUMN event_key TEXT COLLATE \"C\" NOT NULL DEFAULT ''")
+            c.execute('ALTER TABLE editions DROP CONSTRAINT '+quote(matches[0]))
+            c.execute('ALTER TABLE editions ADD CONSTRAINT editions_event_identity_unique UNIQUE(venue_id,year,track,event_key)')
+        else:
+            unique = [tuple(r[2] for r in c.execute('PRAGMA index_info('+quote(index[1])+')'))
+                      for index in c.execute('PRAGMA index_list(editions)') if index[2]]
+            if unique != [('venue_id','year','track')]:
+                raise ValueError('Unexpected legacy edition uniqueness')
+            c.execute("CREATE TABLE editions_event_upgrade(id INTEGER PRIMARY KEY,venue_id INTEGER NOT NULL REFERENCES venues(id),year INTEGER NOT NULL,track TEXT NOT NULL DEFAULT 'main',event_key TEXT NOT NULL DEFAULT '',UNIQUE(venue_id,year,track,event_key))")
+            c.execute('INSERT INTO editions_event_upgrade(id,venue_id,year,track) SELECT id,venue_id,year,track FROM editions')
+            c.execute('DROP TABLE editions')
+            c.execute('ALTER TABLE editions_event_upgrade RENAME TO editions')
+    for item in before['assignments']:
+        result = c.execute("UPDATE editions SET event_key=? WHERE id=? AND event_key=''", (item['event_key'],item['edition_id']))
+        if result.rowcount != 1:
+            raise ValueError('Edition assignment changed during upgrade')
+    c.execute('INSERT INTO schema_migrations(name) VALUES(?)', (marker,))
+    after = inspect(c, progress=progress)
+    expected_keys = dict(before['event_keys'])
+    expected_keys.update({a['edition_id']:a['event_key'] for a in before['assignments']})
+    if (after['editions_sha256'] != before['editions_sha256'] or after['protected'] != before['protected']
+            or after['counts'] != before['counts'] or dict(after['event_keys']) != expected_keys):
+        raise ValueError('Event upgrade changed protected data')
+    if not getattr(c, 'postgres', False) and c.execute('PRAGMA foreign_key_check').fetchall():
+        raise ValueError('Foreign key violation after event upgrade')
+    return {'status':'applied', 'updated_editions':len(before['assignments']), 'after':after}
+
+
+def upgrade(proposal, db=None, *, pg_service=None, pg_schema=None, progress=None):
+    if pg_service:
+        from paper_atlas.postgres.writer import WriteConnection
+        with closing(WriteConnection(pg_service, pg_schema)) as c:
+            if proposal['database'] != database_identity(c):
+                raise ValueError('Plan targets a different database')
+            try:
+                result = apply_plan(c, proposal, progress=progress); c.commit(); return result
+            except BaseException:
+                c.rollback(); raise
+    require_sqlite_writable(db)
+    if not Path(db).is_file():
+        raise ValueError('Existing SQLite sample required')
+    with closing(sqlite3.connect(db, timeout=30)) as c:
+        if proposal['database'] != database_identity(c, db):
+            raise ValueError('Plan targets a different database')
+        c.execute('PRAGMA foreign_keys=OFF')  # Outside the transaction; no cascading deletes.
+        try:
+            c.execute('BEGIN IMMEDIATE')
+            result = apply_plan(c, proposal, progress=progress); c.commit(); return result
+        except BaseException:
+            c.rollback(); raise
+        finally:
+            c.execute('PRAGMA foreign_keys=ON')
+
+
+def main(argv=None, *, prog=None):
+    p = argparse.ArgumentParser(prog=prog); database_arguments(p)
+    p.add_argument('--out', required=True)
+    p.add_argument('--apply-plan'); p.add_argument('--plan-sha256')
+    args = p.parse_args(argv)
+    if bool(args.apply_plan) != bool(args.plan_sha256):
+        p.error('--apply-plan and --plan-sha256 must be supplied together')
+    target = Path(args.out)
+    if target.exists():
+        raise ValueError('Refusing to overwrite an existing plan/report')
+    progress = lambda event: print(json.dumps({'phase':'exact_row_count', **event}), file=sys.stderr, flush=True)
+    if args.apply_plan:
+        raw = Path(args.apply_plan).read_bytes()
+        if hashlib.sha256(raw).hexdigest() != args.plan_sha256:
+            raise ValueError('Plan file SHA256 differs from approved bytes')
+        result = upgrade(json.loads(raw), args.db, pg_service=args.pg_service, pg_schema=args.pg_schema, progress=progress)
+    else:
+        result = plan(args.db, pg_service=args.pg_service, pg_schema=args.pg_schema, progress=progress)
+    target.write_text(json.dumps(result, ensure_ascii=False, indent=2)+'\n')
+    print(json.dumps({'out':str(target), 'sha256':hashlib.sha256(target.read_bytes()).hexdigest(),
+                      'status':result.get('status','planned')}), flush=True)

paper_atlas/ingestion/records.py

@@ -42,9 +42,11 @@ def ingest_file(db,path, *, pg_service=None, pg_schema=None):
                 row = json.loads(line)
                 if row['source'] != source_name:
                     raise ValueError('Source differs from manifest')
-                if any(key in manifest and manifest[key]!=row[key] for key in ('venue','year','track')):
+                if any(key in manifest and manifest[key]!=row.get(key) for key in ('venue','year','track','event_key')):
                     raise ValueError('Conference edition differs from manifest')
                 require_track_record(row, manifest)
+                from paper_atlas.db.events import validate_event_key
+                validate_event_key(row.get('event_key'))
                 external = row['external_id']
                 if external in seen:
                     raise ValueError('Duplicate external ID in one collected file: '+external)
@@ -65,7 +67,7 @@ def ingest_file(db,path, *, pg_service=None, pg_schema=None):
                         status=event['status']
                         if events and status is None:raise ValueError('Explicit decision event requires a status')
                         if status is not None and status not in ('accepted','rejected','desk_rejected','withdrawn','under_review','unknown'):raise ValueError('Unsupported decision event')
-                        edition=store.decision(paper,record,row['venue'],row['year'],row['track'],external,event.get('evidence_url',row['url']),status,event.get('raw_value',''),row.get('venue_aliases'),identities=identities,observed_at=event.get('observed_at') or row_observed,decision_date=event.get('decision_date'),source_decision_id=event.get('source_decision_id',''),submitted_at=row.get('submitted_at'),guard_status=source_name in (PUBLIC_SOURCE,ARCHIVE_SOURCE))
+                        edition=store.decision(paper,record,row['venue'],row['year'],row['track'],external,event.get('evidence_url',row['url']),status,event.get('raw_value',''),row.get('venue_aliases'),identities=identities,observed_at=event.get('observed_at') or row_observed,decision_date=event.get('decision_date'),source_decision_id=event.get('source_decision_id',''),submitted_at=row.get('submitted_at'),guard_status=source_name in (PUBLIC_SOURCE,ARCHIVE_SOURCE),event_key=row.get('event_key'))
                     editions.add(edition)
                 else:
                     raise ValueError('This importer requires an explicit supported decision status')

paper_atlas/postgres/writer.py

@@ -85,3 +85,5 @@ class WriteConnection:
 class PostgresStore(Store):
     def __init__(self, service, schema=None):
         self.conn = WriteConnection(service, schema)
+        from paper_atlas.db.events import has_event_key
+        self.event_identity = has_event_key(self.conn)

paper_atlas/querying.py

@@ -14,7 +14,9 @@ ACCEPTED_PAPERS = "SELECT s.paper_id FROM submissions s JOIN decisions d ON d.su
 
 
 def known_acceptances(conn,paper):
-    return [dict(row) for row in conn.execute('''SELECT s.id AS submission_id,v.acronym AS venue,e.year,e.track,
+    from paper_atlas.db.events import has_event_key
+    event = 'e.event_key' if has_event_key(conn) else "''"
+    return [dict(row) for row in conn.execute('''SELECT s.id AS submission_id,v.acronym AS venue,e.year,e.track,'''+event+''' AS event_key,
         d.id AS decision_id,d.decision_date,d.observed_at,d.evidence_url
         FROM submissions s JOIN editions e ON e.id=s.edition_id JOIN venues v ON v.id=e.venue_id
         JOIN decisions d ON d.submission_id=s.id WHERE s.paper_id=? AND d.status='accepted' AND '''+LATEST_DECISION+
@@ -33,6 +35,7 @@ def main(argv=None, *, prog=None):
     parser.add_argument('--venue', help='Conference acronym')
     parser.add_argument('--year', type=int)
     parser.add_argument('--track',help='Exact track, e.g. main, findings, workshop')
+    parser.add_argument('--event-key',help='Exact event within a venue/year/track')
     parser.add_argument('--status', choices=['accepted','rejected','desk_rejected','withdrawn','under_review','unknown'])
     parser.add_argument('--outcome',choices=['accepted','no_known_acceptance'],help='跨会议的已知录用情况；未查到录用不等于从未录用')
     parser.add_argument('--limit', type=int, default=20)
@@ -47,6 +50,11 @@ def main(argv=None, *, prog=None):
         conn = sqlite3.connect(Path(args.db).resolve().as_uri()+'?mode=ro', uri=True)
         conn.row_factory = sqlite3.Row
         conn.execute('BEGIN')  # One consistent read snapshot while ingestion continues.
+    from paper_atlas.db.events import has_event_key
+    event = 'e.event_key' if has_event_key(conn) else "''"
+    if args.event_key is not None and event == "''":
+        conn.close()
+        raise ValueError('Event identity migration required for event-key queries')
     if args.stats:
         result = {name:conn.execute('SELECT count(*) FROM '+name).fetchone()[0] for name in ('papers','source_records','submissions','decisions','tags','match_candidates')}
         result['records_by_source'] = [dict(r) for r in conn.execute('SELECT s.name,count(r.id) AS imported_records FROM sources s LEFT JOIN source_records r ON r.source_id=s.id GROUP BY s.id ORDER BY s.name')]
@@ -73,13 +81,15 @@ def main(argv=None, *, prog=None):
             where.append('EXISTS(SELECT 1 FROM paper_tags pt JOIN tags t ON t.id=pt.tag_id WHERE pt.paper_id=p.id AND t.namespace=? AND t.value=?)'); params.extend([namespace,value])
         if args.outcome:
             where.append('p.id '+('IN' if args.outcome=='accepted' else 'NOT IN')+' ('+ACCEPTED_PAPERS+')')
-        if args.venue or args.year or args.status or args.track:
+        if args.venue or args.year or args.status or args.track or args.event_key is not None:
             clause = ['1=1']
             if args.venue:
                 if conn.execute("SELECT 1 FROM sqlite_master WHERE type='table' AND name='venue_aliases'").fetchone():
                     clause.append('(lower(v.acronym)=lower(?) OR EXISTS(SELECT 1 FROM venue_aliases va WHERE va.venue_id=v.id AND lower(va.alias)=lower(?)))'); params.extend([args.venue,args.venue])
                 else:
                     clause.append('lower(v.acronym)=lower(?)');params.append(args.venue)
+            if args.event_key is not None:
+                clause.append('e.event_key=?');params.append(args.event_key)
             if args.track:
                 clause.append('e.track=?');params.append(args.track)
             if args.year:
@@ -101,7 +111,7 @@ def main(argv=None, *, prog=None):
                 paper['tags'] = [dict(r) for r in conn.execute('SELECT t.namespace,t.value,pt.evidence_key FROM paper_tags pt JOIN tags t ON t.id=pt.tag_id WHERE pt.paper_id=?',(row['id'],))]
                 paper['sources'] = [dict(r) for r in conn.execute('SELECT s.name,r.external_id,r.url,r.title,r.abstract,r.retrieved_at FROM source_records r JOIN sources s ON s.id=r.source_id WHERE r.paper_id=?',(row['id'],))]
                 submissions = []
-                for sub in conn.execute('SELECT s.id,s.source_submission_id,s.submitted_at,v.acronym,e.year,e.track FROM submissions s JOIN editions e ON e.id=s.edition_id JOIN venues v ON v.id=e.venue_id WHERE s.paper_id=? ORDER BY e.year,s.id',(row['id'],)):
+                for sub in conn.execute('SELECT s.id,s.source_submission_id,s.submitted_at,v.acronym,e.year,e.track,'+event+' AS event_key FROM submissions s JOIN editions e ON e.id=s.edition_id JOIN venues v ON v.id=e.venue_id WHERE s.paper_id=? ORDER BY e.year,s.id',(row['id'],)):
                     submission = dict(sub)
                     submission['decisions'] = []
                     for d in conn.execute('SELECT id,status,raw_value,evidence_url,observed_at,decision_date FROM decisions WHERE submission_id=? ORDER BY observed_at,COALESCE(decision_date,observed_at),id',(sub['id'],)):

tests/test_event_identity.py

@@ -0,0 +1,202 @@
+from contextlib import closing, redirect_stdout, contextmanager
+import io
+import json
+import os
+from pathlib import Path
+import sqlite3
+import tempfile
+import unittest
+import uuid
+from importlib.resources import files
+
+from paper_atlas.db.store import Store
+from paper_atlas.db.backend import open_store
+from paper_atlas.db.events import has_event_key
+from paper_atlas.collectors.dblp import XML_SHA
+from paper_atlas.ingestion.event_identity import plan, upgrade, exact_counts
+from paper_atlas.ingestion.records import ingest_file
+from paper_atlas.ingestion.backfill import backfill
+from paper_atlas.postgres.client import connect
+from paper_atlas.postgres.schema import quote
+from paper_atlas.postgres.migration import migrate
+from paper_atlas import querying
+
+
+@contextmanager
+def opened_store(**kwargs):
+    store=open_store(**kwargs)
+    try:
+        yield store
+    finally:
+        store.conn.close()
+
+
+def legacy(db):
+    sql = files('paper_atlas.db').joinpath('schema.sql').read_text().replace(" event_key TEXT NOT NULL DEFAULT '',\n", '').replace('UNIQUE(venue_id,year,track,event_key)', 'UNIQUE(venue_id,year,track)')
+    c = sqlite3.connect(db); c.executescript(sql); c.close()
+    s = Store(db)
+    p,r = s.record('dblp','conf/www/Fall95','https://example.org/fall','Same paper in two events',
+                    'Abstract\x00with\x01text',['A Person'],{'arxiv':'2501.00001'})
+    s.record_tags(p,r,'dblp',[{'namespace':'dblp:booktitle','value':'WWW (Fall)'}])
+    s.decision(p,r,'Legacy',1995,'main','conf/www/Fall95','https://example.org/accept','accepted','Accept')
+    s.conn.execute("UPDATE venues SET acronym='WWW',name='WWW'")
+    meta={'snapshot_sha256':XML_SHA,'proceedings_key':'conf/www/1995f','bibliographic_year':1995,
+          'bibliography':{'booktitle':['WWW (Fall)'],'url':['db/conf/www/www1995f.html#Fall95']}}
+    s.conn.execute('UPDATE source_records SET metadata_json=?',(json.dumps(meta),));s.conn.commit();s.conn.close()
+    return p,r
+
+
+def add(store, external, event=None, status='accepted', venue='WWW', year=1995):
+    p,r = store.record('fixture',external,'https://example.org/'+external,'Same paper in two events',
+                       'Abstract\x00with\x01text',['A Person'],{'arxiv':'2501.00001'})
+    edition = store.decision(p,r,venue,year,'main',external,'https://example.org/'+status,status,status,event_key=event)
+    return p,r,edition
+
+
+def file(root, name, rows, event=None):
+    p=root/(name+'.jsonl');p.write_text(''.join(json.dumps(r)+'\n'for r in rows))
+    manifest={'source':'fixture','venue':'WWW','year':1995,'track':'main','count':len(rows),'list_url':'https://example.org/'+name}
+    if event is not None:manifest['event_key']=event
+    p.with_suffix('.manifest.json').write_text(json.dumps(manifest));return p
+
+
+class EventIdentityTests(unittest.TestCase):
+    def setUp(self):
+        t=tempfile.TemporaryDirectory();self.addCleanup(t.cleanup);self.root=Path(t.name);self.db=self.root/'sample.sqlite'
+
+    def test_same_paper_distinct_events_keep_both_attempts_and_decisions(self):
+        s=Store(self.db);self.addCleanup(s.conn.close)
+        paper,_,spring=add(s,'spring','www-1995-spring','rejected');_,_,fall=add(s,'fall','www-1995-fall')
+        s.conn.commit();self.assertNotEqual(spring,fall)
+        with redirect_stdout(io.StringIO())as out:querying.main(['--db',str(self.db),'--paper',str(paper)])
+        row=json.loads(out.getvalue())[0];self.assertEqual({a['event_key']for a in row['submissions']},{'www-1995-spring','www-1995-fall'})
+        self.assertEqual([a['event_key']for a in row['known_acceptances']],['www-1995-fall'])
+        self.assertEqual({a['latest_status']for a in row['submissions']},{'rejected','accepted'})
+        with redirect_stdout(io.StringIO())as out:querying.main(['--db',str(self.db),'--venue','WWW','--event-key','www-1995-spring','--status','rejected'])
+        self.assertEqual(len(json.loads(out.getvalue())),1)
+
+    def test_explicit_migration_preserves_ids_history_and_source_bytes(self):
+        paper,record=legacy(self.db);proposal=plan(self.db);self.assertFalse(proposal['before']['event_column'])
+        result=upgrade(proposal,self.db);self.assertEqual(result['updated_editions'],1)
+        self.assertEqual(result['after']['protected'],proposal['before']['protected'])
+        self.assertEqual(result['after']['editions_sha256'],proposal['before']['editions_sha256'])
+        self.assertEqual(upgrade(proposal,self.db)['status'],'already_applied')
+        s=Store(self.db);self.addCleanup(s.conn.close)
+        self.assertEqual(s.conn.execute('SELECT id,event_key FROM editions').fetchall(),[(1,'www-1995-fall')])
+        # An old file without event_key still has its original source anchor.
+        self.assertEqual(s.decision(paper,record,'WWW',1995,'main','conf/www/Fall95','https://example.org/accept','accepted','Accept'),1)
+        self.assertEqual(s.conn.execute('SELECT count(*) FROM submissions').fetchone()[0],1)
+        self.assertEqual(s.conn.execute('PRAGMA foreign_key_check').fetchall(),[])
+
+    def test_unverified_legacy_volume_is_not_automatically_labelled(self):
+        legacy(self.db)
+        with sqlite3.connect(self.db)as c:c.execute("UPDATE source_records SET metadata_json='{}'")
+        with self.assertRaisesRegex(ValueError,'complete pinned Fall'):plan(self.db)
+        with sqlite3.connect(self.db)as c:self.assertFalse(has_event_key(c))
+
+    def test_stale_plan_rolls_back_before_schema_change(self):
+        legacy(self.db);proposal=plan(self.db)
+        with sqlite3.connect(self.db)as c:c.execute("UPDATE editions SET track='unspecified'")
+        with self.assertRaisesRegex(ValueError,'stale'):upgrade(proposal,self.db)
+        with sqlite3.connect(self.db)as c:self.assertFalse(has_event_key(c));self.assertEqual(c.execute('SELECT track FROM editions').fetchone()[0],'unspecified')
+
+    def test_unscoped_source_and_wrong_refresh_event_fail(self):
+        s=Store(self.db);self.addCleanup(s.conn.close);_,r,e=add(s,'first','www-1995-fall');s.conn.commit()
+        p,r2=s.record('fixture','unscoped','https://example.org/u','Other work',None,[])
+        with self.assertRaisesRegex(ValueError,'event_key'):s.decision(p,r2,'WWW',1995,'main','unscoped','https://example.org/u','accepted','Accept')
+        with self.assertRaisesRegex(ValueError,'Cannot move'):s.decision(p,r,'WWW',1995,'main','first','https://example.org/u','accepted','Accept',event_key='www-1995-spring')
+        self.assertEqual(s.conn.execute('SELECT count(*) FROM editions').fetchone()[0],1)
+
+    def test_pinned_fall_cannot_claim_spring(self):
+        p,r=legacy(self.db);upgrade(plan(self.db),self.db);s=Store(self.db);self.addCleanup(s.conn.close)
+        with self.assertRaisesRegex(ValueError,'pinned WWW Fall'):s.decision(p,r,'WWW',1995,'main','conf/www/Fall95','https://example.org/u','accepted','Accept',event_key='www-1995-spring')
+
+    def test_manifest_event_mismatch_rolls_back_the_whole_file(self):
+        s=Store(self.db);s.conn.close()
+        base={'source':'fixture','venue':'WWW','year':1995,'track':'main','status':'accepted','title':'Input work','url':'https://example.org/p','authors':[]}
+        path=file(self.root,'bad',[dict(base,external_id='a',event_key='www-1995-spring'),dict(base,external_id='b',event_key='www-1995-fall')],'www-1995-spring')
+        with self.assertRaisesRegex(ValueError,'edition differs'):ingest_file(self.db,path)
+        with sqlite3.connect(self.db)as c:
+            self.assertEqual(c.execute('SELECT count(*) FROM papers').fetchone()[0],0)
+            self.assertEqual(c.execute('SELECT count(*) FROM editions').fetchone()[0],0)
+
+    def test_backfill_selects_the_explicit_event_and_preserves_attempts(self):
+        s=Store(self.db);add(s,'spring','www-1995-spring','rejected');add(s,'fall','www-1995-fall');s.conn.commit();s.conn.close()
+        row={'source':'fixture','external_id':'fall','venue':'WWW','year':1995,'track':'main','event_key':'www-1995-fall','status':'accepted','identifiers':{'openreview':'forumFall'}}
+        path=file(self.root,'backfill',[row],'www-1995-fall')
+        with redirect_stdout(io.StringIO()):result=backfill(self.db,[path])
+        self.assertEqual(result['submissions_after'],2)
+        with sqlite3.connect(self.db)as c:
+            self.assertEqual(c.execute("SELECT e.event_key FROM submission_identifiers si JOIN submissions s ON s.id=si.submission_id JOIN editions e ON e.id=s.edition_id WHERE si.scheme='openreview' AND si.value='forumFall'").fetchone()[0],'www-1995-fall')
+
+    def test_exact_paged_counts_cover_composite_key_boundaries(self):
+        s=Store(self.db);self.addCleanup(s.conn.close)
+        for i in range(7):add(s,'entry'+str(i),'www-1995-fall')
+        s.conn.commit();events=[];counts=exact_counts(s.conn,page_size=2,progress=events.append)
+        self.assertEqual(counts['source_records'],7)
+        self.assertEqual(counts['identifiers'],8)
+        self.assertEqual(counts['submission_sources'],7)
+        self.assertEqual([e['rows_counted']for e in events if e['table']=='identifiers'and e['complete']],[8])
+
+    def test_legacy_single_event_writes_and_reads_remain_available(self):
+        legacy(self.db);s=Store(self.db);self.addCleanup(s.conn.close)
+        add(s,'ordinary',venue='COLM',year=2025);s.conn.commit()
+        with redirect_stdout(io.StringIO())as out:querying.main(['--db',str(self.db),'--venue','COLM'])
+        self.assertEqual(json.loads(out.getvalue())[0]['known_acceptances'][-1]['event_key'],'')
+        with self.assertRaisesRegex(ValueError,'migration required'):
+            querying.main(['--db',str(self.db),'--event-key','www-1995-fall'])
+
+    def test_unexpected_extra_edition_column_requires_review(self):
+        legacy(self.db)
+        with sqlite3.connect(self.db)as c:c.execute('ALTER TABLE editions ADD COLUMN custom_data TEXT')
+        with self.assertRaisesRegex(ValueError,'Unexpected edition schema'):plan(self.db)
+
+    def test_file_spanning_two_events_without_manifest_key_rolls_back(self):
+        Store(self.db).conn.close()
+        base={'source':'fixture','venue':'WWW','year':1995,'track':'main','status':'accepted','title':'Input work','url':'https://example.org/p','authors':[]}
+        path=file(self.root,'two',[dict(base,external_id='a',event_key='www-1995-spring'),dict(base,external_id='b',event_key='www-1995-fall')])
+        with self.assertRaisesRegex(ValueError,'exactly one'):ingest_file(self.db,path)
+        with sqlite3.connect(self.db)as c:self.assertEqual(c.execute('SELECT count(*) FROM submissions').fetchone()[0],0)
+
+    def test_cutover_sqlite_backup_remains_protected(self):
+        legacy(self.db);proposal=plan(self.db);Path(str(self.db.resolve())+'.postgresql-cutover.json').write_text('{}')
+        with self.assertRaisesRegex(RuntimeError,'cut over'):upgrade(proposal,self.db)
+
+
+@unittest.skipUnless(os.environ.get('PAPER_ATLAS_TEST_PG_SERVICE'),'Requires approved remote PostgreSQL test service')
+class PostgreSQLEventIdentityTests(unittest.TestCase):
+    def setUp(self):
+        self.service=os.environ['PAPER_ATLAS_TEST_PG_SERVICE'];self.schema='pa_event_'+uuid.uuid4().hex[:12]
+        t=tempfile.TemporaryDirectory();self.addCleanup(t.cleanup);self.root=Path(t.name);self.db=self.root/'seed.sqlite';legacy(self.db)
+        self.addCleanup(self.cleanup)
+        with redirect_stdout(io.StringIO()):migrate(self.db,self.service,self.root/'migrate.json',schema=self.schema,min_free_gib=0)
+
+    def cleanup(self):
+        with connect(self.service)as c:
+            for (name,)in c.execute('SELECT nspname FROM pg_namespace WHERE starts_with(nspname,%s)',(self.schema,)).fetchall():c.execute('DROP SCHEMA '+quote(name)+' CASCADE')
+
+    def test_native_upgrade_preserves_ids_and_codec_then_accepts_separate_event(self):
+        proposal=plan(pg_service=self.service,pg_schema=self.schema)
+        result=upgrade(proposal,pg_service=self.service,pg_schema=self.schema)
+        self.assertEqual(result['after']['protected'],proposal['before']['protected'])
+        self.assertEqual(upgrade(proposal,pg_service=self.service,pg_schema=self.schema)['status'],'already_applied')
+        with opened_store(pg_service=self.service,pg_schema=self.schema)as s:
+            paper,_,spring=add(s,'spring','www-1995-spring','rejected');s.conn.commit()
+            self.assertNotEqual(spring,1)
+            self.assertEqual(s.conn.execute('SELECT abstract FROM papers WHERE id=?',(paper,)).fetchone()[0],'Abstract\x00with\x01text')
+        with redirect_stdout(io.StringIO())as out:querying.main(['--pg-service',self.service,'--pg-schema',self.schema,'--paper',str(paper)])
+        row=json.loads(out.getvalue())[0];self.assertEqual(len(row['submissions']),2);self.assertEqual(len(row['known_acceptances']),1)
+
+    def test_native_paged_counts_preserve_encoded_composite_key_order(self):
+        with opened_store(pg_service=self.service,pg_schema=self.schema)as s:
+            for value in ['a\x00b','a\x01b','a\x02b','z']:
+                s.conn.execute('INSERT INTO identifiers(scheme,value,paper_id) VALUES(?,?,?)',('custom',value,1))
+            s.conn.commit();counts=exact_counts(s.conn,page_size=2)
+            self.assertEqual(counts['identifiers'],6)
+            self.assertEqual(counts['submissions'],1)
+
+    def test_stale_native_plan_is_rejected_and_lock_released(self):
+        proposal=plan(pg_service=self.service,pg_schema=self.schema)
+        with opened_store(pg_service=self.service,pg_schema=self.schema)as s:s.conn.execute("UPDATE editions SET track='unspecified'");s.conn.commit()
+        with self.assertRaisesRegex(ValueError,'stale'):upgrade(proposal,pg_service=self.service,pg_schema=self.schema)
+        with opened_store(pg_service=self.service,pg_schema=self.schema)as s:self.assertFalse(has_event_key(s.conn))

tests/test_histories.py

@@ -0,0 +1,114 @@
+from pathlib import Path
+import tempfile
+import unittest
+
+from paper_atlas.audits.histories import report
+from paper_atlas.db.store import Store
+
+
+def add_history_fixture(store):
+    def event(work, forum, venue, year, track, status, observed):
+        paper, record = store.record('history_fixture', work + '/' + forum,
+            'https://example.org/' + forum, work + ' title\x00 preserved', 'Abstract', ['Jane Doe'],
+            {'arxiv': work})
+        store.decision(paper, record, venue, year, track, forum,
+            'https://example.org/' + forum + '/' + str(status), status, status or '',
+            identities=[('openreview', forum)], observed_at=observed)
+        return paper
+
+    # The negative observation was collected later: observation order is not chronology.
+    event('2501.00001', 'HistoryA01', 'ICLR', 2024, 'main', 'rejected', '2026-02-01T00:00:00Z')
+    event('2501.00001', 'HistoryA02', 'ICML', 2025, 'main', 'accepted', '2026-01-01T00:00:00Z')
+    event('2501.00002', 'HistoryB01', 'ICLR', 2024, 'main', 'rejected', '2026-01-01T00:00:00Z')
+    event('2501.00002', 'HistoryB01', 'ICLR', 2024, 'main', 'accepted', '2026-02-01T00:00:00Z')
+    event('2501.00003', 'HistoryC01', 'ICLR', 2024, 'main', 'desk_rejected', '2026-01-01T00:00:00Z')
+    event('2501.00003', 'HistoryC02', 'ICLR', 2024, 'workshop_example', 'accepted', '2026-02-01T00:00:00Z')
+    event('2501.00004', 'HistoryD01', 'ICML', 2025, 'main', 'accepted', '2026-01-01T00:00:00Z')
+    event('2501.00004', 'HistoryD01', 'ICML', 2025, 'main', 'withdrawn', '2026-02-01T00:00:00Z')
+    event('2501.00004', 'HistoryD02', 'ICLR', 2024, 'main', 'rejected', '2026-01-01T00:00:00Z')
+    event('2501.00005', 'HistoryE01', 'COLM', 2026, 'main', None, '2026-01-01T00:00:00Z')
+    store.conn.commit()
+
+
+class HistoryTests(unittest.TestCase):
+    def setUp(self):
+        self.tmp = tempfile.TemporaryDirectory()
+        self.addCleanup(self.tmp.cleanup)
+        self.db = Path(self.tmp.name) / 'sample.sqlite'
+        self.store = Store(self.db)
+        self.addCleanup(self.store.conn.close)
+
+    def test_distinct_attempts_venues_and_latest_acceptances_are_not_observation_counts(self):
+        add_history_fixture(self.store)
+        result = report(self.db)
+        self.assertEqual(result['counts'], {
+            'papers_with_submissions': 5, 'papers_with_multiple_submissions': 3,
+            'papers_with_historical_negative': 4, 'papers_with_current_known_acceptance': 3,
+            'papers_with_negative_and_acceptance_in_distinct_attempts': 2,
+            'papers_with_negative_and_acceptance_in_distinct_venues': 1,
+            'submissions': 8, 'decision_observations': 9,
+            'attempts_with_historical_negative': 5,
+            'attempts_with_historical_negative_and_acceptance': 2,
+        })
+        self.assertEqual(result['latest_attempt_status_counts'],
+                         {'accepted': 3, 'desk_rejected': 1, 'rejected': 2, 'unknown': 1, 'withdrawn': 1})
+        self.assertEqual(len(result['examples']), 2)
+        first = result['examples'][0]
+        self.assertIn('\x00', first['title'])
+        self.assertEqual({r['venue'] for r in first['attempts']}, {'ICLR', 'ICML'})
+        self.assertTrue(all(r['decisions'][0]['sources'] for r in first['attempts']))
+        self.assertTrue(all(r['decisions'][0]['decision_date'] is None for r in first['attempts']))
+        self.assertIn('Not inferred', first['chronology'])
+        self.assertEqual(result['status'], 'observed_history_audit_not_final_dedup')
+        self.assertTrue(all(v == 0 for v in result['evidence_relationship_checks'].values()))
+
+    def test_missing_and_mislinked_source_evidence_is_reported_without_repair(self):
+        add_history_fixture(self.store)
+        self.store.conn.execute('DELETE FROM decision_sources WHERE decision_id=1')
+        self.store.conn.execute('DELETE FROM submission_sources WHERE submission_id=1')
+        # Record3 belongs to paper2; move it to paper1 to create a real mismatch.
+        self.store.conn.execute('UPDATE source_records SET paper_id=1 WHERE id=3')
+        self.store.conn.commit()
+        result = report(self.db, examples=0)
+        checks = result['evidence_relationship_checks']
+        self.assertEqual(checks['decisions_without_source_links'], 1)
+        self.assertEqual(checks['submissions_without_source_links'], 1)
+        self.assertEqual(checks['submission_source_paper_mismatches'], 1)
+        self.assertEqual(checks['decision_source_paper_mismatches'], 2)
+        self.assertEqual(self.store.conn.execute('SELECT paper_id FROM source_records WHERE id=3').fetchone()[0], 1)
+
+    def test_example_limit_does_not_change_global_counts(self):
+        add_history_fixture(self.store)
+        none, one = report(self.db, examples=0), report(self.db, examples=1)
+        self.assertEqual(none['counts'], one['counts'])
+        self.assertEqual(none['examples'], [])
+        self.assertEqual(len(one['examples']), 1)
+
+    def test_empty_snapshot_and_invalid_limit_do_not_create_missing_database(self):
+        empty = report(self.db)
+        self.assertTrue(all(v == 0 for v in empty['counts'].values()))
+        self.assertEqual(empty['latest_attempt_status_counts'], {})
+        absent = self.db.parent / 'absent.sqlite'
+        with self.assertRaisesRegex(ValueError, 'Examples'):
+            report(absent, examples=-1)
+        with self.assertRaisesRegex(ValueError, 'Existing database'):
+            report(absent)
+        self.assertFalse(absent.exists())
+
+    def test_truncated_example_keeps_the_cross_venue_acceptance_visible(self):
+        paper, record = self.store.record('fixture', 'many', 'https://example.org/many',
+                                          'Many attempts', 'Abstract', [])
+        for index in range(26):
+            accepted = index == 25
+            forum = f'BoundedForum{index}'
+            self.store.decision(paper, record, 'ICML' if accepted else 'ICLR', 2025, 'main', forum,
+                                'https://example.org/' + forum, 'accepted' if accepted else 'rejected',
+                                'Accept' if accepted else 'Reject', identities=[('openreview', forum)])
+        self.store.conn.commit()
+        result = report(self.db)
+        example, = result['examples']
+        self.assertEqual((example['attempt_count'], len(example['attempts'])), (26, 20))
+        self.assertTrue(example['attempts_truncated'])
+        self.assertEqual({row['latest_observed_status'] for row in example['attempts']},
+                         {'accepted', 'rejected'})
+        self.assertEqual(result['counts']['submissions'], 26)

tests/test_postgres.py

@@ -175,6 +175,22 @@ class PostgreSQLIntegrationTests(unittest.TestCase):
         finally:
             reader.close()
 
+    def test_history_audit_matches_sqlite_for_actual_postgresql_snapshot(self):
+        from paper_atlas.audits.histories import report
+        from tests.test_histories import add_history_fixture
+        store = Store(self.db)
+        try:
+            add_history_fixture(store)
+        finally:
+            store.conn.close()
+        expected = report(self.db)
+        self.assertEqual(self.migrate()['status'], 'complete')
+        actual = report(pg_service=self.service, pg_schema=self.target)
+        self.assertEqual(actual['engine'], 'postgresql')
+        self.assertEqual(actual['schema'], self.target)
+        for key in ('counts', 'latest_attempt_status_counts', 'evidence_relationship_checks', 'examples'):
+            self.assertEqual(actual[key], expected[key], key)
+
     def interrupt_after_first_table(self):
         calls = 0
         def interrupt(*args):

tests/test_public_openreview.py

@@ -152,6 +152,35 @@ class PublicOpenReviewTests(unittest.TestCase):
             finally:
                 store.conn.close()
 
+    def test_unicode_separators_in_abstract_survive_audit_and_import(self):
+        for separator in ('\u0085', '\u2028', '\u2029'):
+            with self.subTest(separator=repr(separator)), tempfile.TemporaryDirectory() as tmp:
+                root = Path(tmp)
+                abstract = 'First paragraph' + separator + 'Second paragraph'
+                result = self.collect(root / 'source', {
+                    'venues/icml/icml2025.jsonl': [self.row(abstract=abstract)]})
+                path = Path(result['files'][0]['file'])
+                data = path.read_bytes()
+                self.assertIn(separator.encode(), data)
+                self.assertEqual(data.count(b'\n'), 1)
+                db = root / 'db.sqlite'
+                store = Store(db)
+                store.conn.close()
+                inventory = root / 'source/public-openreview-inventory.json'
+                before = audit(db, inventory, root / 'before.json')
+                self.assertEqual(before['status'], 'ready_for_serial_import')
+                self.assertEqual(before['records_checked'], 1)
+                ingest_file(db, path)
+                after = audit(db, inventory, root / 'after.json')
+                self.assertEqual(after['status'], 'ready_for_serial_import')
+                self.assertEqual(after['existing_same_edition_attempts'], 1)
+                store = Store(db)
+                try:
+                    self.assertEqual(store.conn.execute(
+                        'SELECT abstract FROM source_records').fetchone()[0], abstract)
+                finally:
+                    store.conn.close()
+
 
 if __name__ == '__main__':
     unittest.main()

tests/test_submission_evidence.py

@@ -133,7 +133,7 @@ class SubmissionTests(unittest.TestCase):
         c.execute("INSERT INTO papers(id,title,title_key) VALUES(1,'Paper','paper')")
         c.execute("INSERT INTO source_records VALUES(1,1,'paper',1,'https://example.org/p','Paper','Abstract','[]','{}',CURRENT_TIMESTAMP)")
         c.execute("INSERT INTO venues VALUES(1,'ICLR','ICLR','machine_learning','conference')")
-        c.execute("INSERT INTO editions VALUES(1,1,2026,'main')")
+        c.execute("INSERT INTO editions(id,venue_id,year,track) VALUES(1,1,2026,'main')")
         c.execute("INSERT INTO submissions VALUES(1,1,1,1,'paper',NULL)")
         c.execute("INSERT INTO decisions VALUES(1,1,'accepted','Accept','https://example.org/decision','2026-01-01T00:00:00Z',NULL)")
         c.commit();c.close()