Keep legacy source refreshes in their verified joint editions

Reviewed-on: #17

.gitea/workflows/check.yml

@@ -49,7 +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','joint-editions','--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

@@ -61,7 +61,7 @@ paper-atlas collect colm --cache /data/paper-index/raw/http-cache \
 
 `paper-atlas collect --help` 查看来源；`paper-atlas import --help` 查看导入方式。完整批次步骤见[使用指南](docs/usage.md)。
 
-共享实例名为 `foundry-postgres`，数据位于 `foundry_data` 库的 `research` schema；其他项目可按权限复用同一组表。库名通过 libpq service 配置、schema 通过 search_path 或 `--pg-schema` 指定，不写死在查询中。PostgreSQL 凭据放在服务器权限为 `0600` 的 libpq service 文件中；命令行只使用服务名称。`paper-atlas maintain postgres --help` 提供迁移入口：先完整核验 21 张业务表，再发布目标 schema。SQLite FTS5 内部表由 PostgreSQL GIN 索引替代。切换后 SQLite 为备份，不能继续用旧导入器写入它；后续生产导入须适配 PostgreSQL，不能把 SQLite 更新当作生产更新。
+共享实例名为 `foundry-postgres`，数据位于 `foundry_data` 库的 `research` schema；其他项目可按权限复用同一组表。库名通过 libpq service 配置、schema 通过 search_path 或 `--pg-schema` 指定，不写死在查询中。PostgreSQL 凭据放在服务器权限为 `0600` 的 libpq service 文件中；命令行只使用服务名称。`paper-atlas maintain postgres --help` 提供迁移入口：先完整核验 21 张业务表，再发布目标 schema。SQLite FTS5 内部表由 PostgreSQL GIN 索引替代。切换后 SQLite 为只读备份；`import records`、`import arxiv` 和 `maintain backfill` 使用 `--pg-service foundry-research` 写入主库，整个写入会话只允许一个协调写入者，会议文件失败时整体回滚并保留失败批次记录。
 
 ## 数据如何组织
 
@@ -86,3 +86,5 @@ paper-atlas collect colm --cache /data/paper-index/raw/http-cache \
 CI 在已授权的远程 runner 上运行测试、构建 wheel，并从源码目录外验证安装后的命令及随包资源。数据库全量去重和覆盖率是独立的数据验收步骤，不因代码测试通过而自动完成。
 
 开发通过工作分支和 PR 交付，由用户手动合并。
+
+历史联合会议的既有届次归属可通过 [`maintain joint-editions`](docs/joint-editions.md) 先只读规划，再按计划哈希执行；保留论文与投稿决定来源，不重导入。

docs/joint-editions.md

@@ -0,0 +1,67 @@
+# Repairing joint conference membership
+
+EURAI's [official history](https://www.eurai.org/ecai/history) identifies ECAI 2018
+and 2022 as joint IJCAI/ECAI conferences. The pinned DBLP volumes
+`conf/ijcai/2018` and `conf/ijcai/2022` use `IJCAI` as their booktitle and title.
+Existing records can therefore already be present under IJCAI while an ECAI query
+shows a missing year. Adding ECAI as a global alias to the ordinary IJCAI venue
+would incorrectly include all other IJCAI years.
+
+The existing canonical `ECAI+IJCAI` venue has both member aliases. This maintenance
+command changes only the venue reference of the two existing `main` edition rows.
+Edition IDs, submission IDs, decisions, source text, authors, identifiers, tags,
+source links and coverage records stay intact. It does not merge or create papers
+or submissions, resolve per-paper tracks, or turn reviews into negative decisions.
+The DBLP collector applies the joint mapping only to these two exact main volume
+keys; original booktitle/title remain in source metadata with the membership evidence.
+The shared ingestion entry point also resolves these exact pinned source volumes before
+creating an edition, so refreshing an older normalized file cannot recreate a separate
+IJCAI edition. Other sources, years, workshop volumes and additional tracks retain their existing rules.
+
+## Review a read-only plan
+
+Use a runtime containing this repair command. The configured PostgreSQL writer lock
+is used only for the apply operation; do not replace a newer ingestion runtime with
+an older main-based feature branch.
+
+```sh
+PGSERVICEFILE=/etc/paper-atlas/pg_service.conf paper-atlas maintain joint-editions \
+  --pg-service foundry-research --pg-schema research --out joint-plan.json
+```
+
+The preflight requires the existing joint venue and exactly its two member aliases,
+one nonempty unambiguous edition per year, and matching pinned DBLP volume evidence
+for every existing submission. A colliding target edition, missing provenance or
+unexpected source scope fails closed. The plan binds current database/schema, IDs,
+counts and full fingerprints of affected papers, identifiers, source records, tags,
+submissions, decisions and their provenance, and coverage rows. Observation time is
+recorded separately from conference year. These are scoped checks, not a final global
+identity or conference completeness audit.
+
+## Apply the exact reviewed plan
+
+Compute the file's SHA256 after review and pass it explicitly:
+
+```sh
+PGSERVICEFILE=/etc/paper-atlas/pg_service.conf paper-atlas maintain joint-editions \
+  --pg-service foundry-research --pg-schema research --plan joint-plan.json \
+  --plan-sha256 "$PLAN_SHA256" --out joint-applied.json
+```
+
+The plan hash is checked before opening a writer. Current evidence is checked again
+under the dataset writer lock; both edition updates and an audit marker commit in one
+transaction. Protected row hashes are checked again before commit. Changed evidence
+requires a fresh reviewed plan. Existing output reports are preserved; choose a new
+path for another run. SQLite samples use `--db sample.sqlite`; the frozen production
+backup remains protected.
+
+If a response is lost after commit, rerun with the same plan and a new output path.
+When protected evidence is unchanged and both editions already point to the joint
+venue, the result is `already_joint` with zero updates. It does not replay imports or
+regenerate source records. Keep the plan, apply report and independent postcheck
+alongside collection evidence. The audit marker is `joint_ijcai_ecai_2018_2022_v1`.
+
+Publication counts can differ between the DBLP volume and the current publisher
+page. This membership repair preserves all existing rows rather than deleting entries
+to force an aggregate match; section-level main/invited/other classification and
+publication completeness require separate per-paper evidence.

docs/operations.md

@@ -30,6 +30,20 @@ SQLite 允许文本包含 `U+0000`（NUL），PostgreSQL `text` 不允许。迁
 
 远程集成测试通过 `PAPER_ATLAS_TEST_PG_SERVICE` 显式指定，使用随机前缀 schema 并仅清理自己的 fixture；没有该配置时会跳过，应另外报告“未运行”，不能用 SQLite 测试代替 PostgreSQL 验证。
 
+## PostgreSQL 增量写入
+
+`import records`、`import arxiv`、`maintain backfill` 使用 `--pg-service` 选择主库，`--pg-schema` 可覆盖配置的 search_path。写入只接受已经发布、拥有完整业务表和受支持编码标记的 schema，不自动建库或引导空表。SQLite 样本仍使用 `--db`，已切换备份继续禁止写入。
+
+写入连接按数据库/schema 获取会话级 advisory lock，文件提交、回滚以及 arXiv 分批提交均不释放；关闭连接才释放，其他写入者立即得到明确的占用错误。只读查询继续使用独立一致性快照。锁是所有调用方必须遵守的协议，其他项目不要绕过接口并发修改同一论文和投稿；[PostgreSQL 会话锁说明](https://www.postgresql.org/docs/17/explicit-locking.html#ADVISORY-LOCKS)解释了其事务边界。
+
+论文身份匹配、跨来源合并、投稿/决定来源和标签维护复用同一套领域逻辑。PostgreSQL 使用原生 RETURNING 和 identity 序列分配 ID，继承迁移时设定的历史 ID 高水位；失败和冲突尝试可以留下序列空洞，不能把最大 ID 当作行数。绑定文本按 `nul-soh-v1` 编码，结果解码，GIN 随论文写入自动更新。
+
+会议文件的数据变化在同一事务内完成，manifest 数量不符等错误会回滚整份文件，`ingest_runs` 另保留 failed、已处理数和零写入数；身份回填整个队列一次提交。arXiv 每 500 行提交数据和 checkpoint，异常只回滚尚未提交部分，重试必须仍为同一压缩包。大批次开始前核对独立审计清单、当前主库冲突和磁盘空间；完成后对数量、实际来源/决定关联及检索做数据验收，代码测试成功不代表生产导入成功。
+
+`audit public-openreview --pg-service ...` 在只读快照中核验两个受支持公开负面决定来源的固定 revision、逐文件哈希、manifest、原始出处标记和既有同届投稿。最新决定排序与查询端一致：观察时间、决定日期（为空时回退观察时间）、ID。实际导入持有写锁后再次复核该投稿的最新状态，阻止过期预检或弱来源标签覆盖冲突决定；冲突文件整体回滚并等待进一步证据核查，其他会议录用不构成同一次投稿的冲突。
+
+会议导入按原始字节流计算 SHA-256，在提交前与 manifest 提供的 `file_sha256` 比较；不符则回滚整份文件。成功批次的 checkpoint 会记录实际读取内容的哈希，便于复核导入时使用的文件版本。
+
 代码以固定 commit 部署，已经启动的任务继续使用其原版本。目录重构不要求迁移数据库，也不更改已有 JSONL 和 manifest 格式。
 
 ## arXiv 原始编号与类别证据

docs/usage.md

@@ -18,7 +18,7 @@ paper-atlas query --pg-service foundry-research --paper 123
 
 CLI 自动还原迁移中的 NUL/SOH 文本转义，详情中的来源摘要和筛选参数保留原字符。直接 SQL 读取可能看到 `U+0001` 转义序列；跨项目 ETL 应按迁移元数据中的 `text_codec` 使用 `paper_atlas.postgres.text.encode/decode`。存储格式和无损哈希验收见[运行维护](operations.md#postgresql-迁移)。
 
-迁移与切换见[运行维护](operations.md#postgresql-迁移)。下述 SQLite 导入命令仅用于未切换的索引或独立样本；切换后的生产源拒绝旧写入。尚未适配的覆盖审计可只读检查冻结备份，不能当作 PostgreSQL 当前状态。
+迁移与切换见[运行维护](operations.md#postgresql-迁移)。`import records`、`import arxiv` 和 `maintain backfill` 均支持 `--pg-service` 和可选 `--pg-schema`；与 `--db` 二选一。SQLite 导入仅用于未切换的索引或独立样本。尚未适配 PostgreSQL 的维护、覆盖审计命令仍可只读检查冻结备份，不能当作主库的当前状态。
 
 ## 1. 下载固定快照
 
@@ -41,12 +41,35 @@ paper-atlas audit public-openreview --help
 
 其他会议的公开拒稿使用 `collect public-openreview`，再以 `audit public-openreview` 核对既有投稿身份和决定冲突。来源版本、逐文件状态盘点和未解决记录分别保存在 inventory 与 held 清单中，具体命令见[数据源说明](data-sources.md#其他会议的公开拒稿)。这类公开子集不能当作会议完整投稿名单。
 
-## 3. 串行导入
-
-首次 arXiv 导入使用 `paper-atlas import arxiv --db ... --archive ...`。现有数据库合并已核验的会议文件使用：
+主库切换后，预检须直接连接当前 PostgreSQL，不能用冻结的 SQLite 备份推断当前决定：
 
 ```sh
-paper-atlas maintain merge --db /data/paper-index/papers.sqlite \
+paper-atlas audit public-openreview --pg-service foundry-research \
+  --inventory /data/paper-index/collected/public-openreview-v1/public-openreview-inventory.json \
+  --out /data/paper-index/collected/public-negative-pg-preflight.json
+```
+
+预检使用只读一致性快照，报告会记录实际数据库、schema、文件哈希和同届次/轨道的决定冲突；其他会议的录用保留为独立历史。预检不预留写锁，实际导入这两类公开负面决定时会在写事务中再次复核同一次投稿的最新状态；过期预检报告不能覆盖已经变化的决定。
+
+## 3. 串行导入
+
+PostgreSQL 主库逐文件导入已经核验的资料：
+
+```sh
+paper-atlas import records --pg-service foundry-research \
+  /data/paper-index/collected/selected-file.jsonl
+paper-atlas maintain backfill --pg-service foundry-research \
+  --queue /data/paper-index/collected/audited-identity-queue.json
+```
+
+文件和 queue 须替换为实际核验通过的路径。会议导入按文件提交，失败时该文件的论文、标签和决定更改全部回滚，失败批次日志保留；重跑会按来源标识与决定证据去重。回填使用已有源记录，只合并经独立身份佐证的投稿，不重新导入文本。所有写入者须遵守同一数据库/schema 的会话锁；其他项目也应通过相同的写入接口更新共享论文和投稿。
+
+未来需要导入新的 arXiv 元数据包时使用 `paper-atlas import arxiv --pg-service foundry-research --archive ... --storage-path ...`。每 500 行提交断点，恢复同一文件时保留已提交 ID 和标签证据；`--storage-path` 指定运行主机上要监测的文件系统，在数据库主机执行时应指向数据库卷。已完成的历史压缩包不需要重复导入。
+
+SQLite 独立样本的首次 arXiv 导入使用 `paper-atlas import arxiv --db ... --archive ...`。SQLite 协调器仍适用于未切换的索引：
+
+```sh
+paper-atlas maintain merge --db /data/paper-index/samples/example.sqlite \
   --archive /data/paper-index/raw/arxiv-metadata.zip \
   /data/paper-index/collected/selected-file.jsonl
 ```
@@ -56,10 +79,10 @@ paper-atlas maintain merge --db /data/paper-index/papers.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
 ```
 
@@ -68,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,196 @@
+"""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()

paper_atlas/audits/public_openreview.py

@@ -4,14 +4,14 @@ from contextlib import closing
 import hashlib
 import json
 from pathlib import Path
-import sqlite3
 
 from paper_atlas.collectors.public_openreview import SOURCE, REVISION
 from paper_atlas.collectors.icml_public_archive import SOURCE as ARCHIVE_SOURCE, REVISION as ARCHIVE_REVISION
-from paper_atlas.db.submissions import timestamp
+from paper_atlas.db.submissions import timestamp, latest_status
+from paper_atlas.db.backend import database_arguments, open_read_connection
 
 
-def audit(db, inventory, out):
+def audit(db, inventory, out, *, pg_service=None, pg_schema=None):
     inventory = Path(inventory).resolve()
     result = json.loads(inventory.read_text())
     specs = {SOURCE: (REVISION, 'curated_public_status_assertion'),
@@ -20,17 +20,18 @@ def audit(db, inventory, out):
     if source not in specs or result.get('revision') != specs[source][0]:
         raise ValueError('Unsupported public source inventory')
     revision, evidence_kind = specs[source]
-    db = Path(db).resolve()
-    if not db.is_file():
-        raise ValueError('Existing database required for identity and status audit')
     conflicts = []
     seen = set()
     checked = 0
     existing_attempts = 0
     files = []
     checksums = {}
-    with closing(sqlite3.connect(db.as_uri() + '?mode=ro', uri=True)) as c:
-        c.execute('BEGIN')
+    with closing(open_read_connection(db, pg_service=pg_service, pg_schema=pg_schema)) as c:
+        if pg_service:
+            identity = c.execute('SELECT current_database(),current_schema()').fetchone()
+            database, actual_schema = identity[0], identity[1]
+        else:
+            database, actual_schema = str(Path(db).resolve()), None
         for manifest in result['files']:
             path = Path(manifest['file'])
             if hashlib.sha256(path.read_bytes()).hexdigest() != manifest['file_sha256']:
@@ -38,7 +39,9 @@ def audit(db, inventory, out):
             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')
@@ -62,12 +65,11 @@ def audit(db, inventory, out):
                     raise ValueError('Multiple canonical attempts for one forum')
                 if existing:
                     existing_attempts += 1
-                    latest = c.execute('''SELECT status FROM decisions WHERE submission_id=?
-                        ORDER BY observed_at DESC,COALESCE(decision_date,'') DESC,id DESC LIMIT 1''', existing[0]).fetchone()
-                    if latest and latest[0] not in ('unknown', 'under_review', row['status']):
+                    latest = latest_status(c, existing[0][0])
+                    if latest and latest not in ('unknown', 'under_review', row['status']):
                         conflicts.append({'forum': forum, 'venue': row['venue'], 'year': row['year'],
                                           'track': row['track'], 'submission_id': existing[0][0],
-                                          'existing_status': latest[0], 'curated_status': row['status']})
+                                          'existing_status': latest, 'curated_status': row['status']})
                 count += 1
             if count != manifest['count']:
                 raise ValueError('Collected record count differs from inventory')
@@ -76,7 +78,8 @@ def audit(db, inventory, out):
             checksums[str(path)] = manifest['file_sha256']
     if checked != result['normalized_records']:
         raise ValueError('Normalized inventory total differs from checked records')
-    report = {'source': source, 'revision': revision, 'database': str(db),
+    report = {'source': source, 'revision': revision, 'database': database,
+              'engine': 'postgresql' if pg_service else 'sqlite', 'schema': actual_schema, 'pg_service': pg_service,
               'inventory': str(inventory), 'checked_at': timestamp(), 'records_checked': checked,
               'existing_same_edition_attempts': existing_attempts, 'conflicts': conflicts,
               'status': 'blocked' if conflicts else 'ready_for_serial_import',
@@ -94,11 +97,11 @@ def audit(db, inventory, out):
 
 def main(argv=None, *, prog=None):
     parser = argparse.ArgumentParser(prog=prog)
-    parser.add_argument('--db', required=True)
+    database_arguments(parser)
     parser.add_argument('--inventory', required=True)
     parser.add_argument('--out', required=True)
     args = parser.parse_args(argv)
-    report = audit(args.db, args.inventory, args.out)
+    report = audit(args.db, args.inventory, args.out, pg_service=args.pg_service, pg_schema=args.pg_schema)
     if report['status'] == 'blocked':
         raise SystemExit('Conflicting existing decisions require review before import')
 

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','joint-editions'): ('paper_atlas.ingestion.joint_editions', {}, '按已核验计划修正既有联合会议届次归属'),
     ('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/collectors/dblp_records.py

@@ -88,6 +88,8 @@ def normalize_row(row,volumes,targets,include_workshops=False,through_year=2026)
               'scope_rule':edition['rule'],'author_names_raw':fields.get('author',[]),'publtype':row.get('publtype'),
               'bibliography':{key:value for key,value in fields.items() if key in ('booktitle','year','ee','url','pages','volume','number','publisher','series','isbn')},
               'ambiguous_identifiers':ambiguous,'publication_evidence':'inproceedings bibliography; not an original decision note'}
+    if edition.get('joint_edition_evidence'):
+        metadata['joint_edition_evidence'] = edition['joint_edition_evidence']
     if edition.get('track_scope_note'):
         metadata['track_scope_note']=edition['track_scope_note']
     flagged=bool(re.search(r'\b(?:withdrawn|retracted)\b',row.get('publtype') or '',re.I))

paper_atlas/collectors/dblp_scope.py

@@ -96,8 +96,15 @@ def scope(row,volume,through_year=2026):
     elif suffix.casefold() in ('poster','posters') and venue not in ('ICLR','ACL','EMNLP','NAACL','EACL','COLING','CoNLL'):track='poster'
     if venue=='AAAI+IAAI':track='proceedings_unspecified'
     if venue=='AAMAS' and year<2002:return None,'predecessor_not_aamas'
+    joint_evidence = None
+    if (family == 'ijcai' and base == 'IJCAI' and track == 'main'
+            and year in (2018, 2022) and volume['key'] == 'conf/ijcai/' + str(year)):
+        venue, aliases = 'ECAI+IJCAI', ['ECAI', 'IJCAI']
+        joint_evidence = 'https://www.eurai.org/ecai/history'
     result = {'venue':venue,'venue_aliases':aliases,'year':year,'track':track,'bibliographic_year':int(bibliographic[0]),
               'rule':family+':'+base,'proceedings_title':title,'edition_year_basis':year_basis}
+    if joint_evidence:
+        result['joint_edition_evidence'] = joint_evidence
     if venue=='MM' and track=='main':
         # A main MM volume also includes doctoral, tutorials, companion papers
         # and other sections. DBLP XML omits the per-paper TOC section.

paper_atlas/db/backend.py

@@ -2,6 +2,48 @@
 from pathlib import Path
 
 
+def database_arguments(parser):
+    target = parser.add_mutually_exclusive_group(required=True)
+    target.add_argument('--db', help='Writable SQLite sample; never the frozen production backup')
+    target.add_argument('--pg-service', help='Protected libpq service for the published PostgreSQL dataset')
+    parser.add_argument('--pg-schema', help='Optional schema; otherwise use the configured search_path')
+
+
+def open_store(db=None, *, pg_service=None, pg_schema=None):
+    if bool(db) == bool(pg_service) or (pg_schema and not pg_service):
+        raise ValueError('Choose exactly one of SQLite db or PostgreSQL service; schema requires PostgreSQL')
+    if pg_service:
+        from paper_atlas.postgres.writer import PostgresStore
+        return PostgresStore(pg_service, pg_schema)
+    from paper_atlas.db.store import Store
+    return Store(db)
+
+
+def open_read_connection(db=None, *, pg_service=None, pg_schema=None):
+    if bool(db) == bool(pg_service) or (pg_schema and not pg_service):
+        raise ValueError('Choose exactly one of SQLite db or PostgreSQL service; schema requires PostgreSQL')
+    if pg_service:
+        from paper_atlas.postgres.client import ReadConnection
+        return ReadConnection(pg_service, pg_schema)
+    import sqlite3
+    path = Path(db).resolve()
+    if not path.is_file():
+        raise ValueError('Existing database required for read-only audit')
+    connection = sqlite3.connect(path.as_uri() + '?mode=ro', uri=True)
+    try:
+        connection.execute('BEGIN')
+    except BaseException:
+        connection.close()
+        raise
+    return connection
+
+
+def inserted_id(connection, sql, params=()):
+    if getattr(connection, 'postgres', False):
+        return connection.execute(sql + ' RETURNING id', params).fetchone()[0]
+    return connection.execute(sql, params).lastrowid
+
+
 def require_sqlite_writable(path):
     marker = Path(str(Path(path).resolve()) + '.postgresql-cutover.json')
     if marker.exists():

paper_atlas/db/store.py

@@ -5,8 +5,8 @@ import re
 import sqlite3
 import unicodedata
 from pathlib import Path
-from paper_atlas.db.submissions import attach_submission,migrate_sources,write_decision
-from paper_atlas.db.backend import require_sqlite_writable
+from paper_atlas.db.submissions import attach_submission,migrate_sources,write_decision,latest_status
+from paper_atlas.db.backend import require_sqlite_writable, inserted_id
 
 
 def normalized(value):
@@ -38,7 +38,7 @@ class Store:
         migrate_sources(self.conn)
 
     def source(self,name,url):
-        self.conn.execute('INSERT OR IGNORE INTO sources(name,homepage) VALUES(?,?)',(name,url))
+        self.conn.execute('INSERT INTO sources(name,homepage) VALUES(?,?) ON CONFLICT DO NOTHING',(name,url))
         return self.conn.execute('SELECT id FROM sources WHERE name=?',(name,)).fetchone()[0]
 
     def merge(self,target,other,reason):
@@ -55,7 +55,7 @@ class Store:
         c.execute('UPDATE identifiers SET paper_id=? WHERE paper_id=?',(target,other))
         c.execute('UPDATE source_records SET paper_id=? WHERE paper_id=?',(target,other))
         c.execute('UPDATE submissions SET paper_id=? WHERE paper_id=?',(target,other))
-        c.execute('INSERT OR IGNORE INTO paper_tags SELECT ?,tag_id,source_record_id,evidence_key FROM paper_tags WHERE paper_id=?',(target,other))
+        c.execute('INSERT INTO paper_tags SELECT ?,tag_id,source_record_id,evidence_key FROM paper_tags WHERE paper_id=? ON CONFLICT DO NOTHING',(target,other))
         c.execute('DELETE FROM paper_tags WHERE paper_id=?',(other,))
         c.execute('UPDATE paper_merges SET surviving_paper_id=? WHERE surviving_paper_id=?',(target,other))
         # Reattach pending candidate evidence without losing colliding rows.
@@ -65,7 +65,7 @@ class Store:
             left = target if left == other else left
             right = target if right == other else right
             if left != right:
-                c.execute('INSERT OR IGNORE INTO match_candidates VALUES(?,?,?,?,?,?)',(min(left,right),max(left,right),method,score,evidence,status))
+                c.execute('INSERT INTO match_candidates VALUES(?,?,?,?,?,?) ON CONFLICT DO NOTHING',(min(left,right),max(left,right),method,score,evidence,status))
         c.execute('DELETE FROM papers WHERE id=?',(other,))
 
     def record(self,source,external_id,url,title,abstract,authors,identifiers=None,metadata=None,published=None):
@@ -111,18 +111,19 @@ class Store:
                 c.execute("UPDATE papers SET title=?,abstract=COALESCE(NULLIF(?,''),abstract),title_key=?,authors_json=CASE WHEN ?='[]' THEN authors_json ELSE ? END WHERE id=? AND title=? AND COALESCE(abstract,'')=COALESCE(?,'')",(title,abstract,key,json.dumps(authors,ensure_ascii=False),json.dumps(authors,ensure_ascii=False),paper,old[1],old_abstract))
             c.execute("UPDATE papers SET abstract=CASE WHEN abstract IS NULL OR abstract='' THEN ? ELSE abstract END WHERE id=?",(abstract,paper))
         else:
-            paper = c.execute('INSERT INTO papers(title,abstract,title_key,authors_json,first_published) VALUES(?,?,?,?,?)',(title,abstract,key,json.dumps(authors,ensure_ascii=False),published)).lastrowid
+            paper = inserted_id(c,'INSERT INTO papers(title,abstract,title_key,authors_json,first_published) VALUES(?,?,?,?,?)',(title,abstract,key,json.dumps(authors,ensure_ascii=False),published))
         for scheme,value in ids:
-            c.execute('INSERT OR IGNORE INTO identifiers VALUES(?,?,?)',(scheme,value,paper))
+            c.execute('INSERT INTO identifiers VALUES(?,?,?) ON CONFLICT DO NOTHING',(scheme,value,paper))
         metadata=dict(metadata or {});metadata['record_identifiers']=dict(ids)
         if old and not abstract:
             previous={'abstract':old[2],'observed_at':old[3]} if old[2] else json.loads(old[4]).get('last_known_abstract')
             if previous:metadata['last_known_abstract']=previous
-        c.execute('INSERT INTO source_records(source_id,external_id,paper_id,url,title,abstract,authors_json,metadata_json) VALUES(?,?,?,?,?,?,?,?) ON CONFLICT(source_id,external_id) DO UPDATE SET paper_id=excluded.paper_id,url=excluded.url,title=excluded.title,abstract=excluded.abstract,authors_json=excluded.authors_json,metadata_json=excluded.metadata_json,retrieved_at=CURRENT_TIMESTAMP',(source_id,external_id,paper,url,title,abstract,json.dumps(authors,ensure_ascii=False),json.dumps(metadata,ensure_ascii=False,default=str)))
+        from paper_atlas.db.submissions import timestamp
+        c.execute('INSERT INTO source_records(source_id,external_id,paper_id,url,title,abstract,authors_json,metadata_json,retrieved_at) VALUES(?,?,?,?,?,?,?,?,?) ON CONFLICT(source_id,external_id) DO UPDATE SET paper_id=excluded.paper_id,url=excluded.url,title=excluded.title,abstract=excluded.abstract,authors_json=excluded.authors_json,metadata_json=excluded.metadata_json,retrieved_at=excluded.retrieved_at',(source_id,external_id,paper,url,title,abstract,json.dumps(authors,ensure_ascii=False),json.dumps(metadata,ensure_ascii=False,default=str),timestamp()))
         record = c.execute('SELECT id FROM source_records WHERE source_id=? AND external_id=?',(source_id,external_id)).fetchone()[0]
         for other,_,_ in title_matches:
             if other != paper and c.execute('SELECT 1 FROM papers WHERE id=?',(other,)).fetchone():
-                c.execute('INSERT OR IGNORE INTO match_candidates VALUES(?,?,?,?,?,?)',(min(paper,other),max(paper,other),'exact_title_unconfirmed',None,json.dumps({'title':title}), 'pending'))
+                c.execute('INSERT INTO match_candidates VALUES(?,?,?,?,?,?) ON CONFLICT DO NOTHING',(min(paper,other),max(paper,other),'exact_title_unconfirmed',None,json.dumps({'title':title}), 'pending'))
         return paper,record
 
     def record_tags(self,paper,record,source,tags):
@@ -137,22 +138,40 @@ class Store:
         c=self.conn
         c.execute('DELETE FROM paper_tags WHERE source_record_id=? AND evidence_key=?',(record,evidence))
         for namespace,value in sorted(values):
-            c.execute('INSERT OR IGNORE INTO tags(namespace,value) VALUES(?,?)',(namespace,value))
+            c.execute('INSERT INTO tags(namespace,value) VALUES(?,?) ON CONFLICT DO NOTHING',(namespace,value))
             tag=c.execute('SELECT id FROM tags WHERE namespace=? AND value=?',(namespace,value)).fetchone()[0]
-            c.execute('INSERT OR IGNORE INTO paper_tags VALUES(?,?,?,?)',(paper,tag,record,evidence))
+            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):
+    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):
         c = self.conn
-        existing=c.execute('SELECT id FROM venues WHERE acronym=? COLLATE NOCASE',(venue,)).fetchone()
+        # An already collected file may still call these exact pinned volumes
+        # IJCAI. Resolve membership before finding/creating an edition so a
+        # later refresh cannot split the repaired joint event again.
+        if venue == 'IJCAI' and year in (2018,2022) and track == 'main':
+            from paper_atlas.collectors.dblp import XML_SHA
+            evidence=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()
+            if evidence and evidence[0] == 'dblp':
+                metadata=json.loads(evidence[1])
+                if (metadata.get('snapshot_sha256') == XML_SHA
+                        and metadata.get('proceedings_key') == 'conf/ijcai/'+str(year)
+                        and metadata.get('bibliographic_year') == year
+                        and (metadata.get('bibliography') or {}).get('booktitle') in (['IJCAI'],['IJCAI/ECAI'])):
+                    venue='ECAI+IJCAI'
+                    aliases=sorted(set(aliases or []) | {'ECAI','IJCAI'})
+        existing=c.execute('SELECT id FROM venues WHERE lower(acronym)=lower(?)',(venue,)).fetchone()
         if existing:venue_id=existing[0]
-        else:venue_id=c.execute("INSERT INTO venues(acronym,name,field,venue_type) VALUES(?,?,?,'conference')",(venue,venue,'unclassified')).lastrowid
+        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 OR IGNORE INTO venue_aliases VALUES(?,?)',(venue_id,alias))
-        c.execute('INSERT OR IGNORE INTO editions(venue_id,year,track) VALUES(?,?,?)',(venue_id,int(year),track))
+            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]
         # 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 [])
+        if guard_status and status in ('rejected','desk_rejected','withdrawn'):
+            current = latest_status(c, submission)
+            if current and current not in ('unknown','under_review',status):
+                raise ValueError('Curated decision conflicts with current canonical submission status: ' + current)
         if submitted_at:
             from paper_atlas.db.submissions import timestamp
             c.execute('UPDATE submissions SET submitted_at=COALESCE(submitted_at,?) WHERE id=?',(timestamp(submitted_at),submission))

paper_atlas/db/submissions.py

@@ -58,21 +58,29 @@ def identities_for_row(row):
 def migrate_sources(c):
     if c.execute("SELECT 1 FROM schema_migrations WHERE name='submission_source_baseline_v1'").fetchone():return
     with c:
-        c.execute('INSERT OR IGNORE INTO submission_sources SELECT id,source_record_id,source_submission_id FROM submissions')
-        c.execute("INSERT OR IGNORE INTO decision_sources SELECT d.id,s.source_record_id,'' FROM decisions d JOIN submissions s ON s.id=d.submission_id")
+        c.execute('INSERT INTO submission_sources SELECT id,source_record_id,source_submission_id FROM submissions WHERE 1=1 ON CONFLICT DO NOTHING')
+        c.execute("INSERT INTO decision_sources SELECT d.id,s.source_record_id,'' FROM decisions d JOIN submissions s ON s.id=d.submission_id WHERE 1=1 ON CONFLICT DO NOTHING")
         c.execute("INSERT INTO schema_migrations(name) VALUES('submission_source_baseline_v1')")
 
 
 def next_id(c,table,audit,former):
     # Merged IDs are kept in audit; never allocate them to a later unrelated event.
+    if getattr(c, 'postgres', False):
+        return c.next_id(table)
     return max(c.execute('SELECT COALESCE(max(id),0) FROM '+table).fetchone()[0],c.execute('SELECT COALESCE(max('+former+'),0) FROM '+audit).fetchone()[0])+1
 
 
+def latest_status(c, submission):
+    row = c.execute('''SELECT status FROM decisions WHERE submission_id=?
+        ORDER BY observed_at DESC,COALESCE(decision_date,observed_at) DESC,id DESC LIMIT 1''', (submission,)).fetchone()
+    return row[0] if row else None
+
+
 def merge_submissions(c,target,other):
     if target==other:return
     editions=c.execute('SELECT id,edition_id FROM submissions WHERE id IN (?,?)',(target,other)).fetchall()
     if len(editions)!=2 or editions[0][1]!=editions[1][1]:raise ValueError('Cannot combine submissions from different editions')
-    c.execute('INSERT OR IGNORE INTO submission_sources SELECT ?,source_record_id,source_submission_id FROM submission_sources WHERE submission_id=?',(target,other))
+    c.execute('INSERT INTO submission_sources SELECT ?,source_record_id,source_submission_id FROM submission_sources WHERE submission_id=? ON CONFLICT DO NOTHING',(target,other))
     c.execute('DELETE FROM submission_sources WHERE submission_id=?',(other,))
     for decision,status,raw,url,observed,date in c.execute('SELECT id,status,raw_value,evidence_url,observed_at,decision_date FROM decisions WHERE submission_id=?',(other,)).fetchall():
         duplicate=c.execute('SELECT id,decision_date FROM decisions WHERE submission_id=? AND status=? AND raw_value=? AND evidence_url=? AND observed_at=?',(target,status,raw,url,observed)).fetchone()
@@ -80,7 +88,7 @@ def merge_submissions(c,target,other):
             surviving=duplicate[0]
             if date and duplicate[1] and date!=duplicate[1]:raise ValueError('Conflicting dates for the same decision observation')
             if date and not duplicate[1]:c.execute('UPDATE decisions SET decision_date=? WHERE id=?',(date,surviving))
-            c.execute('INSERT OR IGNORE INTO decision_sources SELECT ?,source_record_id,source_decision_id FROM decision_sources WHERE decision_id=?',(surviving,decision))
+            c.execute('INSERT INTO decision_sources SELECT ?,source_record_id,source_decision_id FROM decision_sources WHERE decision_id=? ON CONFLICT DO NOTHING',(surviving,decision))
             c.execute('DELETE FROM decision_sources WHERE decision_id=?',(decision,))
             c.execute('UPDATE decision_merges SET surviving_decision_id=? WHERE surviving_decision_id=?',(surviving,decision))
             c.execute("INSERT INTO decision_merges(former_decision_id,surviving_decision_id,reason) VALUES(?,?,'same decision observation on merged submission')",(decision,surviving))
@@ -114,9 +122,9 @@ def attach_submission(store,edition,paper,record,external_id,identities):
     else:
         submission=next_id(c,'submissions','submission_merges','former_submission_id')
         c.execute('INSERT INTO submissions(id,paper_id,edition_id,source_record_id,source_submission_id) VALUES(?,?,?,?,?)',(submission,paper,edition,record,external_id))
-    c.execute('INSERT OR IGNORE INTO submission_sources VALUES(?,?,?)',(submission,record,external_id))
+    c.execute('INSERT INTO submission_sources VALUES(?,?,?) ON CONFLICT DO NOTHING',(submission,record,external_id))
     for scheme,value in identities:
-        c.execute('INSERT OR IGNORE INTO submission_identifiers VALUES(?,?,?,?)',(edition,scheme,value,submission))
+        c.execute('INSERT INTO submission_identifiers VALUES(?,?,?,?) ON CONFLICT DO NOTHING',(edition,scheme,value,submission))
     return submission
 
 
@@ -130,5 +138,5 @@ def write_decision(store,submission,record,status,raw,url,observed_at=None,decis
     else:
         decision=next_id(c,'decisions','decision_merges','former_decision_id')
         c.execute('INSERT INTO decisions(id,submission_id,status,raw_value,evidence_url,observed_at,decision_date) VALUES(?,?,?,?,?,?,?)',(decision,submission,status,raw,url,observed,date))
-    c.execute('INSERT OR IGNORE INTO decision_sources VALUES(?,?,?)',(decision,record,source_decision_id or ''))
+    c.execute('INSERT INTO decision_sources VALUES(?,?,?) ON CONFLICT DO NOTHING',(decision,record,source_decision_id or ''))
     return decision

paper_atlas/ingestion/arxiv.py

@@ -1,4 +1,4 @@
-"""Stream the official metadata ZIP into SQLite. No PDFs, source files or builds."""
+"""Stream the official metadata ZIP into the configured metadata database."""
 import argparse
 import json
 import time
@@ -7,7 +7,8 @@ import zipfile
 from pathlib import Path
 
 
-from paper_atlas.db.store import Store
+from paper_atlas.db.backend import database_arguments, open_store, inserted_id
+from paper_atlas.db.submissions import timestamp
 
 
 def refresh_categories(store,paper,record,categories):
@@ -17,28 +18,29 @@ def refresh_categories(store,paper,record,categories):
     store.conn.execute("DELETE FROM paper_tags WHERE source_record_id=? AND evidence_key='arxiv:categories' AND tag_id IN (SELECT id FROM tags WHERE namespace='arxiv:category')",(record,))
 
 
-def ingest(db, archive):
-    store = Store(db)
+def ingest(db, archive, *, pg_service=None, pg_schema=None, storage_path=None):
+    store = open_store(db,pg_service=pg_service,pg_schema=pg_schema)
     conn = store.conn
-    conn.execute("INSERT OR IGNORE INTO sources(name,homepage) VALUES('arxiv','https://arxiv.org')")
-    source = conn.execute("SELECT id FROM sources WHERE name='arxiv'").fetchone()[0]
-    previous = conn.execute('SELECT id,checkpoint_json FROM ingest_runs WHERE source_id=? AND status IN (\'running\',\'failed\') ORDER BY id DESC LIMIT 1', (source,)).fetchone()
-    fingerprint = {'path':str(Path(archive).resolve()), 'bytes':Path(archive).stat().st_size, 'mtime_ns':Path(archive).stat().st_mtime_ns}
-    start = 0
-    if previous:
-        checkpoint = json.loads(previous[1] or '{}')
-        if checkpoint.get('archive') == fingerprint:
-            run = previous[0]
-            start = checkpoint.get('line', 0)
-            conn.execute("UPDATE ingest_runs SET status='running',error=NULL WHERE id=?", (run,))
-        else:
-            raise RuntimeError('Pending run belongs to a different archive; resolve it explicitly')
-    else:
-        run = conn.execute('INSERT INTO ingest_runs(source_id,checkpoint_json) VALUES(?,?)', (source,json.dumps({'archive':fingerprint,'line':0}))).lastrowid
-    conn.commit()
-    line_number = start
-    last = time.monotonic()
+    run = None
     try:
+        source = store.source('arxiv','https://arxiv.org')
+        previous = conn.execute('SELECT id,checkpoint_json FROM ingest_runs WHERE source_id=? AND status IN (\'running\',\'failed\') ORDER BY id DESC LIMIT 1', (source,)).fetchone()
+        fingerprint = {'path':str(Path(archive).resolve()), 'bytes':Path(archive).stat().st_size, 'mtime_ns':Path(archive).stat().st_mtime_ns}
+        start = 0
+        if previous:
+            checkpoint = json.loads(previous[1] or '{}')
+            if checkpoint.get('archive') == fingerprint:
+                run = previous[0]
+                start = checkpoint.get('line', 0)
+                conn.execute("UPDATE ingest_runs SET status='running',error=NULL WHERE id=?", (run,))
+            else:
+                raise RuntimeError('Pending run belongs to a different archive; resolve it explicitly')
+        else:
+            run = inserted_id(conn,'INSERT INTO ingest_runs(source_id,checkpoint_json) VALUES(?,?)', (source,json.dumps({'archive':fingerprint,'line':0})))
+        conn.commit()
+        line_number = start
+        last = time.monotonic()
+        monitored = Path(storage_path) if storage_path else (Path(db).parent if db else Path(archive).parent)
         with zipfile.ZipFile(archive) as zipped:
             # Reading the member to EOF also checks its CRC.
             with zipped.open('arxiv-metadata-oai-snapshot.json') as stream:
@@ -54,20 +56,21 @@ def ingest(db, archive):
                     paper,record = store.record('arxiv',external_id,'https://arxiv.org/abs/'+external_id,title,abstract,authors,{'arxiv':external_id,'doi':row.get('doi')},metadata,(row.get('versions') or [{}])[0].get('created'))
                     refresh_categories(store,paper,record,row.get('categories') or '')
                     if line_number % 500 == 0:
-                        if shutil.disk_usage(Path(db).parent).free < 4*1024**3:
+                        if shutil.disk_usage(monitored).free < 4*1024**3:
                             raise RuntimeError('Stopped before exhausting disk: less than 4 GiB free')
                         conn.execute('UPDATE ingest_runs SET records_seen=?,records_written=?,checkpoint_json=? WHERE id=?',(line_number,line_number,json.dumps({'archive':fingerprint,'line':line_number}),run))
                         conn.commit()
                         if time.monotonic()-last >= 20:
                             print(json.dumps({'run':run,'rows':line_number}),flush=True)
                             last = time.monotonic()
-        conn.execute("UPDATE ingest_runs SET status='complete',finished_at=CURRENT_TIMESTAMP,records_seen=?,records_written=?,checkpoint_json=? WHERE id=?",(line_number,line_number,json.dumps({'archive':fingerprint,'line':line_number}),run))
+        conn.execute("UPDATE ingest_runs SET status='complete',finished_at=?,records_seen=?,records_written=?,checkpoint_json=? WHERE id=?",(timestamp(),line_number,line_number,json.dumps({'archive':fingerprint,'line':line_number}),run))
         conn.commit()
         print(json.dumps({'status':'complete','rows':line_number}),flush=True)
     except BaseException as exc:
         conn.rollback()
-        conn.execute("UPDATE ingest_runs SET status='failed',error=? WHERE id=?",(str(exc),run))
-        conn.commit()
+        if run is not None:
+            conn.execute("UPDATE ingest_runs SET status='failed',error=? WHERE id=?",(str(exc),run))
+            conn.commit()
         raise
     finally:
         conn.close()
@@ -75,10 +78,11 @@ def ingest(db, archive):
 
 def main(argv=None, *, prog=None):
     parser = argparse.ArgumentParser(prog=prog)
-    parser.add_argument('--db', required=True)
+    database_arguments(parser)
     parser.add_argument('--archive', required=True)
+    parser.add_argument('--storage-path', help='Local filesystem to monitor for free space; specify the database volume when running beside PostgreSQL')
     args = parser.parse_args(argv)
-    ingest(args.db,args.archive)
+    ingest(args.db,args.archive,pg_service=args.pg_service,pg_schema=args.pg_schema,storage_path=args.storage_path)
 
 
 if __name__ == '__main__':

paper_atlas/ingestion/backfill.py

@@ -4,7 +4,7 @@ import hashlib
 import json
 from pathlib import Path
 
-from paper_atlas.db.store import Store
+from paper_atlas.db.backend import database_arguments, open_store
 from paper_atlas.db.submissions import attach_submission,identities_for_row
 from paper_atlas.collectors.pmlr_tracks import require_track_manifest, require_track_record
 
@@ -19,15 +19,15 @@ def queue_files(path):
     return files
 
 
-def backfill(db,files):
+def backfill(db,files, *, pg_service=None, pg_schema=None):
     for filename in files:
         manifest=json.loads(Path(filename).with_suffix('.manifest.json').read_text())
         require_track_manifest(manifest)
         if manifest['source'] in ('colm','neurips','iclr_program') and manifest.get('parser_version',0)<2:
             raise ValueError('Recollect old parser output before submission evidence backfill')
-    store=Store(db);c=store.conn;count=0
-    before=c.execute('SELECT count(*) FROM submissions').fetchone()[0]
+    store=open_store(db,pg_service=pg_service,pg_schema=pg_schema);c=store.conn;count=0
     try:
+        before=c.execute('SELECT count(*) FROM submissions').fetchone()[0]
         for filename in files:
             path=Path(filename);manifest=json.loads(path.with_suffix('.manifest.json').read_text());seen=set()
             with path.open() as stream:
@@ -39,7 +39,7 @@ def backfill(db,files):
                         raise ValueError('Evidence row differs from edition 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 v.acronym=? COLLATE NOCASE AND e.year=? AND e.track=?',(row['venue'],row['year'],row['track'])).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()
                     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')
@@ -62,8 +62,8 @@ def backfill(db,files):
 
 
 def main(argv=None, *, prog=None):
-    parser=argparse.ArgumentParser(prog=prog);parser.add_argument('--db',required=True);parser.add_argument('--queue',required=True)
-    args=parser.parse_args(argv);backfill(args.db,queue_files(args.queue))
+    parser=argparse.ArgumentParser(prog=prog);database_arguments(parser);parser.add_argument('--queue',required=True)
+    args=parser.parse_args(argv);backfill(args.db,queue_files(args.queue),pg_service=args.pg_service,pg_schema=args.pg_schema)
 
 
 if __name__ == '__main__':

paper_atlas/ingestion/joint_editions.py

@@ -0,0 +1,204 @@
+"""Evidence-bound repair of two existing IJCAI/ECAI edition memberships."""
+import argparse
+from contextlib import closing
+import hashlib
+import json
+from pathlib import Path
+
+from paper_atlas.db.backend import database_arguments, open_read_connection, open_store
+from paper_atlas.db.submissions import timestamp
+from paper_atlas.collectors.dblp import XML_SHA
+
+YEARS = (2018, 2022)
+TARGET = 'ECAI+IJCAI'
+MEMBERS = {'ECAI', 'IJCAI'}
+EVIDENCE = 'https://www.eurai.org/ecai/history'
+MARKER = 'joint_ijcai_ecai_2018_2022_v1'
+
+
+def _digest(c, sql, params=()):
+    sha = hashlib.sha256(); count = 0
+    for row in c.execute(sql, params):
+        sha.update((json.dumps([row[i] for i in range(len(row))], ensure_ascii=False,
+                              separators=(',', ':')) + '\n').encode())
+        count += 1
+        if count > 100000:
+            raise ValueError('Joint repair evidence scope exceeded bounded row limit')
+    return {'rows': count, 'sha256': sha.hexdigest()}
+
+
+def fingerprints(c, ids):
+    marks = ','.join('?' for _ in ids)
+    submissions = 'SELECT id FROM submissions WHERE edition_id IN (' + marks + ')'
+    papers = 'SELECT paper_id FROM submissions WHERE edition_id IN (' + marks + ')'
+    decisions = 'SELECT id FROM decisions WHERE submission_id IN (' + submissions + ')'
+    checks = {
+        'papers': ('id IN (' + papers + ')', 'id'),
+        'identifiers': ('paper_id IN (' + papers + ')', 'scheme,value'),
+        'source_records': ('paper_id IN (' + papers + ')', 'id'),
+        'paper_tags': ('paper_id IN (' + papers + ')', 'paper_id,tag_id,evidence_key'),
+        'submissions': ('edition_id IN (' + marks + ')', 'id'),
+        'decisions': ('submission_id IN (' + submissions + ')', 'id'),
+        'submission_sources': ('submission_id IN (' + submissions + ')', 'submission_id,source_record_id,source_submission_id'),
+        'submission_identifiers': ('edition_id IN (' + marks + ')', 'edition_id,scheme,value'),
+        'decision_sources': ('decision_id IN (' + decisions + ')', 'decision_id,source_record_id,source_decision_id'),
+        'edition_sources': ('edition_id IN (' + marks + ')', 'edition_id,source_id,list_url'),
+    }
+    return {table: _digest(c, 'SELECT * FROM ' + table + ' WHERE ' + where + ' ORDER BY ' + order, ids)
+            for table, (where, order) in checks.items()}
+
+
+def inspect(c):
+    target = c.execute('SELECT id FROM venues WHERE acronym=?', (TARGET,)).fetchall()
+    if len(target) != 1:
+        raise ValueError('Exactly one existing canonical joint venue required')
+    target = target[0][0]
+    aliases = {r[0] for r in c.execute('SELECT alias FROM venue_aliases WHERE venue_id=?', (target,))}
+    if aliases != MEMBERS:
+        raise ValueError('Joint target must already have exactly ECAI and IJCAI aliases')
+    editions = []
+    for year in YEARS:
+        rows = c.execute('''SELECT e.id,e.venue_id,v.acronym FROM editions e JOIN venues v ON v.id=e.venue_id
+            WHERE v.acronym IN ('IJCAI','ECAI+IJCAI') AND e.year=? AND e.track='main' ''', (year,)).fetchall()
+        if len(rows) != 1:
+            raise ValueError('Expected one existing edition per year; missing or colliding editions require separate review')
+        edition, venue, acronym = (rows[0][i] for i in range(3))
+        count = c.execute('SELECT count(*) FROM submissions WHERE edition_id=?', (edition,)).fetchone()[0]
+        if not 0 < count <= 5000:
+            raise ValueError('Existing edition must have a nonempty bounded submission scope')
+        # Every existing submission must be supported by its own pinned DBLP
+        # volume record. No title/author matching, new attempts or text writes.
+        evidence = c.execute('''SELECT s.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=? AND src.name='dblp' ORDER BY s.id,r.id''', (edition,)).fetchall()
+        seen = set()
+        for row in evidence:
+            sid, external, metadata = (row[i] for i in range(3))
+            meta = json.loads(metadata)
+            bibliography = meta.get('bibliography') or {}
+            if (meta.get('snapshot_sha256') != XML_SHA
+                    or meta.get('proceedings_key') != 'conf/ijcai/' + str(year)
+                    or meta.get('bibliographic_year') != year
+                    or bibliography.get('booktitle') not in (['IJCAI'], ['IJCAI/ECAI'])
+                    or not external.startswith('conf/ijcai/')):
+                raise ValueError('Source evidence outside the exact verified IJCAI/ECAI volume')
+            seen.add(sid)
+        if len(seen) != count:
+            raise ValueError('Every submission must have matching original volume evidence')
+        editions.append({'id': edition, 'year': year, 'track': 'main', 'from_venue_id': venue,
+                         'from_venue': acronym, 'target_venue_id': target, 'submissions': count,
+                         'evidence_url': EVIDENCE, 'proceedings_key': 'conf/ijcai/' + str(year)})
+    ids = tuple(e['id'] for e in editions)
+    return {'format_version': 1, 'kind': MARKER, 'checked_at': timestamp(), 'editions': editions,
+            'content_fingerprints': fingerprints(c, ids), 'target_aliases': sorted(aliases),
+            'status': 'already_joint' if all(e['from_venue_id'] == target for e in editions) else 'ready',
+            'scope': 'Only these two existing edition venue references; no source text, paper, submission, decision, track, identifier or tag change.'}
+
+
+def validate_plan(expected):
+    if not isinstance(expected, dict):
+        raise ValueError('Repair plan must be a JSON object')
+    if set(expected) != {'format_version','kind','checked_at','editions','content_fingerprints','target_aliases','status','scope','database'}:
+        raise ValueError('Unexpected repair plan fields')
+    if (type(expected['format_version']) is not int or expected['format_version'] != 1
+            or expected['kind'] != MARKER or expected['status'] not in ('ready','already_joint')):
+        raise ValueError('Unsupported repair plan')
+    if (not isinstance(expected['database'],dict) or not isinstance(expected['content_fingerprints'],dict)
+            or not isinstance(expected['editions'],list) or len(expected['editions']) != 2
+            or expected['target_aliases'] != sorted(MEMBERS)):
+        raise ValueError('Invalid repair plan structure')
+    for edition, year in zip(expected['editions'], YEARS):
+        if not isinstance(edition,dict) or edition.get('year') != year:
+            raise ValueError('Repair plan must name the two exact ordered years')
+        for key in ('id','year','from_venue_id','target_venue_id','submissions'):
+            if type(edition.get(key)) is not int or edition[key] <= 0:
+                raise ValueError('Repair plan IDs/counts must be positive integers')
+
+
+def apply_plan(c, expected):
+    validate_plan(expected)
+    actual = inspect(c)
+    if actual['content_fingerprints'] != expected['content_fingerprints'] or actual['target_aliases'] != expected['target_aliases']:
+        raise ValueError('Stale repair plan: protected content or aliases changed')
+    if len(expected['editions']) != 2:
+        raise ValueError('Exactly two editions required')
+    for old, now in zip(expected['editions'], actual['editions']):
+        if set(old) != set(now):
+            raise ValueError('Unexpected edition fields')
+        for key in ('id','year','track','target_venue_id','submissions','evidence_url','proceedings_key'):
+            if old[key] != now[key]:
+                raise ValueError('Stale repair plan: edition differs')
+        if now['from_venue_id'] != old['from_venue_id'] and now['from_venue_id'] != now['target_venue_id']:
+            raise ValueError('Edition membership differs from plan')
+    if actual['status'] == 'already_joint':
+        return {**actual, 'status':'already_joint', 'updated_editions':0}
+    for edition in actual['editions']:
+        if edition['from_venue_id'] == edition['target_venue_id']:
+            continue
+        changed = c.execute('''UPDATE editions SET venue_id=? WHERE id=? AND venue_id=? AND year=? AND track=?''',
+                            (edition['target_venue_id'],edition['id'],edition['from_venue_id'],edition['year'],edition['track']))
+        if changed.rowcount != 1:
+            raise ValueError('Concurrent edition change; entire repair must roll back')
+    after = inspect(c)
+    if after['status'] != 'already_joint' or after['content_fingerprints'] != actual['content_fingerprints']:
+        raise ValueError('Postcheck changed protected content; entire repair must roll back')
+    c.execute('INSERT INTO schema_migrations(name) VALUES(?) ON CONFLICT DO NOTHING', (MARKER,))
+    return {**after, 'status':'applied', 'updated_editions':sum(e['from_venue_id'] != e['target_venue_id'] for e in actual['editions'])}
+
+
+def database_identity(c, db):
+    if getattr(c, 'postgres', False):
+        row = c.execute('SELECT current_database(),current_schema()').fetchone()
+        return {'engine':'postgresql','database':row[0],'schema':row[1]}
+    return {'engine':'sqlite','path':str(Path(db).resolve())}
+
+
+def run(db, out, *, pg_service=None, pg_schema=None, plan=None, plan_sha256=None):
+    out = Path(out).resolve()
+    if not out.parent.is_dir():
+        raise ValueError('Report parent directory must already exist')
+    if out.exists():
+        raise ValueError('Preserve existing evidence report; choose a new output path')
+    expected = None
+    if plan:
+        raw = Path(plan).read_bytes()
+        if hashlib.sha256(raw).hexdigest() != plan_sha256:
+            raise ValueError('Repair plan hash differs before opening database')
+        expected = json.loads(raw)
+        validate_plan(expected)
+    elif plan_sha256:
+        raise ValueError('Plan hash requires an existing plan')
+    if db and out == Path(db).resolve():
+        raise ValueError('Report must not overwrite database')
+    writer = open_store(db, pg_service=pg_service, pg_schema=pg_schema) if expected is not None else None
+    c = writer.conn if writer else open_read_connection(db, pg_service=pg_service, pg_schema=pg_schema)
+    with closing(c):
+        try:
+            if expected is not None and not getattr(c, 'postgres', False):
+                c.commit(); c.execute('BEGIN IMMEDIATE')
+            identity = database_identity(c, db)
+            if expected is not None and expected['database'] != identity:
+                raise ValueError('Plan belongs to another database/schema')
+            report = apply_plan(c, expected) if expected is not None else inspect(c)
+            report['database'] = identity
+            if expected is not None:
+                report['plan_sha256'] = plan_sha256
+            if expected is not None:
+                c.commit()
+        except BaseException:
+            if expected is not None: c.rollback()
+            raise
+    out.write_text(json.dumps(report,ensure_ascii=False,indent=2)+'\n')
+    print(json.dumps({'status':report['status'],'editions':len(report['editions']),
+                      'updated_editions':report.get('updated_editions',0),'report':str(out)}))
+    return report
+
+
+def main(argv=None, *, prog=None):
+    parser = argparse.ArgumentParser(prog=prog); database_arguments(parser)
+    parser.add_argument('--out', required=True)
+    parser.add_argument('--plan', help='Apply an already reviewed plan; omitted means read-only preflight')
+    parser.add_argument('--plan-sha256', help='Required exact plan SHA256 when applying')
+    args = parser.parse_args(argv)
+    run(args.db,args.out,pg_service=args.pg_service,pg_schema=args.pg_schema,plan=args.plan,plan_sha256=args.plan_sha256)

paper_atlas/ingestion/records.py

@@ -1,15 +1,18 @@
 """Import normalized conference JSONL files with coverage evidence."""
 import argparse
+import hashlib
 import json
 import time
 from pathlib import Path
 
-from paper_atlas.db.store import Store
+from paper_atlas.db.backend import database_arguments, open_store, inserted_id
 from paper_atlas.db.submissions import identities_for_row,timestamp
 from paper_atlas.collectors.pmlr_tracks import require_track_manifest, require_track_record
+from paper_atlas.collectors.public_openreview import SOURCE as PUBLIC_SOURCE
+from paper_atlas.collectors.icml_public_archive import SOURCE as ARCHIVE_SOURCE
 
 
-def ingest_file(db,path):
+def ingest_file(db,path, *, pg_service=None, pg_schema=None):
     path = Path(path)
     manifest_path = path.with_suffix('.manifest.json')
     manifest = json.loads(manifest_path.read_text()) if manifest_path.exists() else {}
@@ -18,21 +21,24 @@ def ingest_file(db,path):
         raise ValueError('Recollect proceedings with parser_version>=2 before identity matching')
     if manifest.get('source')=='iclr_program' and manifest.get('parser_version',0)<2:
         raise ValueError('Recollect ICLR with parser_version>=2 before submission identity matching')
-    store = Store(db)
-    c = store.conn
     source_name = manifest.get('source')
     if not source_name:
         raise ValueError('A source manifest is required')
-    source_id = store.source(source_name,manifest['list_url'])
-    run = c.execute('INSERT INTO ingest_runs(source_id,checkpoint_json) VALUES(?,?)',(source_id,json.dumps({'file':str(path)}))).lastrowid
-    c.commit()
+    store = open_store(db, pg_service=pg_service, pg_schema=pg_schema)
+    c = store.conn
+    run = None
     count = 0
     missing_abstracts = 0
     seen = set()
     editions = set()
     try:
-        with path.open() as stream:
+        source_id = store.source(source_name,manifest['list_url'])
+        run = inserted_id(c,'INSERT INTO ingest_runs(source_id,checkpoint_json) VALUES(?,?)',(source_id,json.dumps({'file':str(path),'expected_sha256':manifest.get('file_sha256')})))
+        c.commit()
+        digest = hashlib.sha256()
+        with path.open('rb') as stream:
             for line in stream:
+                digest.update(line)
                 row = json.loads(line)
                 if row['source'] != source_name:
                     raise ValueError('Source differs from manifest')
@@ -59,7 +65,7 @@ def ingest_file(db,path):
                         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'))
+                        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))
                     editions.add(edition)
                 else:
                     raise ValueError('This importer requires an explicit supported decision status')
@@ -67,6 +73,8 @@ def ingest_file(db,path):
                 missing_abstracts += not bool((row.get('abstract') or '').strip())
         if not count:
             raise ValueError('Empty metadata file')
+        if manifest.get('file_sha256') and digest.hexdigest() != manifest['file_sha256']:
+            raise ValueError('Collected file hash differs from manifest')
         if len(editions) != 1:
             raise ValueError('A manifest must describe exactly one conference edition and track')
         edition = next(iter(editions))
@@ -75,13 +83,14 @@ def ingest_file(db,path):
             raise ValueError(f'Manifest count {expected} differs from records {count}')
         complete=expected==count and manifest.get('status') not in ('partial','blocked') and not manifest.get('missing_abstract_count',0) and not missing_abstracts
         c.execute('INSERT INTO edition_sources(edition_id,source_id,list_url,status,expected_count,imported_count,last_success_at) VALUES(?,?,?,?,?,?,?) ON CONFLICT(edition_id,source_id,list_url) DO UPDATE SET status=excluded.status,expected_count=excluded.expected_count,imported_count=excluded.imported_count,last_success_at=excluded.last_success_at,last_error=NULL',(edition,source_id,manifest['list_url'],'complete' if complete else 'partial',expected,count,time.strftime('%Y-%m-%dT%H:%M:%SZ',time.gmtime())))
-        c.execute("UPDATE ingest_runs SET status='complete',finished_at=CURRENT_TIMESTAMP,records_seen=?,records_written=? WHERE id=?",(count,count,run))
+        c.execute("UPDATE ingest_runs SET status='complete',finished_at=?,records_seen=?,records_written=?,checkpoint_json=? WHERE id=?",(timestamp(),count,count,json.dumps({'file':str(path),'file_sha256':digest.hexdigest()}),run))
         c.commit()
         print(json.dumps({'file':str(path),'status':'imported','records':count,'missing_abstract_count':missing_abstracts}),flush=True)
     except BaseException as exc:
         c.rollback()
-        c.execute("UPDATE ingest_runs SET status='failed',finished_at=CURRENT_TIMESTAMP,error=? WHERE id=?",(str(exc),run))
-        c.commit()
+        if run is not None:
+            c.execute("UPDATE ingest_runs SET status='failed',finished_at=?,records_seen=?,records_written=0,error=? WHERE id=?",(timestamp(),count,str(exc),run))
+            c.commit()
         raise
     finally:
         c.close()
@@ -89,11 +98,11 @@ def ingest_file(db,path):
 
 def main(argv=None, *, prog=None):
     parser = argparse.ArgumentParser(prog=prog)
-    parser.add_argument('--db',required=True)
+    database_arguments(parser)
     parser.add_argument('files',nargs='+')
     args = parser.parse_args(argv)
     for filename in args.files:
-        ingest_file(args.db,filename)
+        ingest_file(args.db,filename,pg_service=args.pg_service,pg_schema=args.pg_schema)
 
 
 if __name__ == '__main__':

paper_atlas/postgres/writer.py

@@ -0,0 +1,87 @@
+"""Native transactional writes reusing the shared identity/provenance policy."""
+import re
+import zlib
+
+from paper_atlas.db.store import Store
+from paper_atlas.postgres import text
+from paper_atlas.postgres.client import connect
+from paper_atlas.postgres.schema import TABLES, quote
+
+
+class Result:
+    def __init__(self, cursor):
+        self.cursor = cursor
+
+    def fetchone(self):
+        row = self.cursor.fetchone()
+        return tuple(map(text.decode, row)) if row is not None else None
+
+    def fetchall(self):
+        return [tuple(map(text.decode, row)) for row in self.cursor.fetchall()]
+
+    def __iter__(self):
+        return (tuple(map(text.decode, row)) for row in self.cursor)
+
+    @property
+    def rowcount(self):
+        return self.cursor.rowcount
+
+
+class WriteConnection:
+    postgres = True
+
+    def __init__(self, service, schema=None):
+        self.connection = connect(service)
+        try:
+            if schema is None:
+                schema = self.connection.execute('SELECT current_schema()').fetchone()[0]
+            if not isinstance(schema, str) or not re.fullmatch(r'[a-z][a-z0-9_]{0,35}', schema):
+                raise ValueError('Invalid PostgreSQL write schema')
+            annotation = self.connection.execute("SELECT obj_description(oid,'pg_namespace') FROM pg_namespace WHERE nspname=%s", (schema,)).fetchone()
+            if not annotation or annotation[0] != 'paper-atlas:text-codec=' + text.CODEC:
+                raise RuntimeError('Writes require a published dataset with the supported text codec')
+            tables = {r[0] for r in self.connection.execute("SELECT table_name FROM information_schema.tables WHERE table_schema=%s AND table_type='BASE TABLE'", (schema,))}
+            if not set(TABLES) <= tables:
+                raise RuntimeError('Published business tables are incomplete or inaccessible')
+            if not self.connection.execute('SELECT pg_try_advisory_lock(%s,%s)', (615174, zlib.crc32(schema.encode()) & 0x7fffffff)).fetchone()[0]:
+                raise RuntimeError('Another writer holds this PostgreSQL dataset; retry after it closes')
+            self.schema = schema
+            self.connection.execute('SET search_path TO ' + quote(schema) + ',pg_catalog')
+            if not self.connection.execute("SELECT 1 FROM schema_migrations WHERE name='submission_source_baseline_v1'").fetchone():
+                raise RuntimeError('Submission provenance baseline is missing')
+            self.connection.autocommit = False
+        except BaseException:
+            self.connection.close()
+            raise
+
+    def execute(self, sql, params=()):
+        # The shared domain uses static SQL and bound parameters. It uses no
+        # SQLite-specific syntax; this adapter only binds/decodes text values.
+        return Result(self.connection.execute(sql.replace('?', '%s'), tuple(map(text.encode, params))))
+
+    def next_id(self, table):
+        if table not in ('submissions', 'decisions'):
+            raise ValueError('Unsupported explicit identity sequence')
+        return self.connection.execute("SELECT nextval(pg_get_serial_sequence(%s,'id'))", (quote(self.schema) + '.' + quote(table),)).fetchone()[0]
+
+    def commit(self):
+        self.connection.commit()
+
+    def rollback(self):
+        self.connection.rollback()
+
+    def close(self):
+        # Ends the session and releases its advisory lock, including after an
+        # error/rollback. File/chunk commits deliberately retain the writer lock.
+        self.connection.close()
+
+    def __enter__(self):
+        return self
+
+    def __exit__(self, kind, error, traceback):
+        self.rollback() if kind else self.commit()
+
+
+class PostgresStore(Store):
+    def __init__(self, service, schema=None):
+        self.conn = WriteConnection(service, schema)

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_joint_editions.py

@@ -0,0 +1,187 @@
+from contextlib import closing, redirect_stdout
+import hashlib
+import io
+import json
+import os
+from pathlib import Path
+import tempfile
+import unittest
+from unittest.mock import patch
+import uuid
+
+from paper_atlas.collectors.dblp import XML_SHA
+from paper_atlas.collectors.dblp_scope import scope
+from paper_atlas.collectors.dblp_records import normalize_row
+from paper_atlas.db.store import Store
+from paper_atlas.db.backend import open_store
+from paper_atlas.ingestion.joint_editions import (TARGET, MARKER, inspect, apply_plan, run)
+from paper_atlas.postgres.client import connect
+from paper_atlas.postgres.migration import migrate
+from paper_atlas.postgres.schema import quote
+from paper_atlas import querying
+
+
+def seed(store):
+    c = store.conn
+    c.execute("INSERT INTO venues(acronym,name,field,venue_type) VALUES(?,?,'reasoning','conference')", (TARGET,TARGET))
+    target = c.execute('SELECT id FROM venues WHERE acronym=?',(TARGET,)).fetchone()[0]
+    for alias in ['ECAI','IJCAI']:
+        c.execute('INSERT INTO venue_aliases VALUES(?,?)',(target,alias))
+    for year in [2018,2019,2022]:
+        metadata = {'snapshot_sha256':XML_SHA,'proceedings_key':'conf/ijcai/'+str(year),
+                    'bibliographic_year':year,'bibliography':{'booktitle':['IJCAI']}}
+        p,r=store.record('dblp','conf/ijcai/fixture'+str(year),'https://dblp.org/rec/fixture',
+                         'Original publication '+str(year),'Abstract\x00with\x01data',[],
+                         {'doi':'10.24963/ijcai.'+str(year)+'/1'}, {})
+        store.record_tags(p,r,'dblp',[{'namespace':'dblp:booktitle','value':'IJCAI'}])
+        store.decision(p,r,'IJCAI',year,'main','fixture'+str(year),'https://example.org/decision','accepted','listed')
+        stored=json.loads(c.execute('SELECT metadata_json FROM source_records WHERE id=?',(r,)).fetchone()[0]);stored.update(metadata)
+        c.execute('UPDATE source_records SET metadata_json=? WHERE id=?',(json.dumps(stored),r))
+    c.commit()
+
+
+def plan(c):
+    return {**inspect(c),'database':{'engine':'sqlite','path':'fixture'}}
+
+
+class JointEditionTests(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)
+        seed(self.store); self.c=self.store.conn
+
+    def test_apply_preserves_every_protected_row_and_edition_id(self):
+        before=plan(self.c); ids=[r['id']for r in before['editions']]
+        result=apply_plan(self.c,before);self.c.commit()
+        self.assertEqual(result['updated_editions'],2)
+        self.assertEqual(result['content_fingerprints'],before['content_fingerprints'])
+        self.assertEqual([r['id']for r in result['editions']],ids)
+        untouched=self.c.execute('SELECT v.acronym FROM editions e JOIN venues v ON v.id=e.venue_id WHERE e.year=2019').fetchone()[0]
+        self.assertEqual(untouched,'IJCAI')
+        self.assertEqual(self.c.execute('PRAGMA foreign_key_check').fetchall(),[])
+        self.assertEqual(self.c.execute('SELECT count(*) FROM schema_migrations WHERE name=?',(MARKER,)).fetchone()[0],1)
+        with redirect_stdout(io.StringIO())as out:
+            querying.main(['--db',str(self.db),'--venue','ECAI'])
+        self.assertEqual(len(json.loads(out.getvalue())),2)
+        self.assertEqual(apply_plan(self.c,before)['updated_editions'],0)
+
+    def test_refresh_of_old_normalized_source_does_not_recreate_ordinary_edition(self):
+        before=plan(self.c);apply_plan(self.c,before)
+        for year in [2018,2022]:
+            row=self.c.execute('''SELECT r.paper_id,r.id,d.observed_at FROM source_records r
+                JOIN submissions s ON s.source_record_id=r.id JOIN decisions d ON d.submission_id=s.id
+                WHERE r.external_id=?''',('conf/ijcai/fixture'+str(year),)).fetchone()
+            self.store.decision(row[0],row[1],'IJCAI',year,'main','fixture'+str(year),
+                                'https://example.org/decision','accepted','listed',observed_at=row[2])
+        self.assertEqual(inspect(self.c)['content_fingerprints'],before['content_fingerprints'])
+        self.assertEqual(self.c.execute('SELECT count(*) FROM editions').fetchone()[0],3)
+
+    def test_any_changed_source_text_rejects_stale_plan_before_membership_updates(self):
+        before=plan(self.c)
+        self.c.execute("UPDATE source_records SET title='New evidence' WHERE external_id='conf/ijcai/fixture2018'")
+        with self.assertRaisesRegex(ValueError,'Stale repair plan'):
+            apply_plan(self.c,before)
+        self.assertEqual(inspect(self.c)['status'],'ready')
+
+    def test_conflicting_target_edition_is_never_merged_or_deleted(self):
+        target=self.c.execute('SELECT id FROM venues WHERE acronym=?',(TARGET,)).fetchone()[0]
+        self.c.execute("INSERT INTO editions(venue_id,year,track) VALUES(?,2018,'main')",(target,))
+        with self.assertRaisesRegex(ValueError,'colliding'):
+            inspect(self.c)
+        self.assertEqual(self.c.execute('SELECT count(*) FROM editions WHERE year=2018').fetchone()[0],2)
+
+    def test_wrong_source_volume_and_snapshot_fail_closed(self):
+        original=self.c.execute("SELECT metadata_json FROM source_records WHERE external_id='conf/ijcai/fixture2018'").fetchone()[0]
+        for key,value in [('proceedings_key','conf/ijcai/2018w'),('snapshot_sha256','wrong'),('bibliographic_year',2017),('bibliography',{'booktitle':['Other']})]:
+            m=json.loads(original);m[key]=value
+            self.c.execute("UPDATE source_records SET metadata_json=? WHERE external_id='conf/ijcai/fixture2018'",(json.dumps(m),))
+            with self.subTest(key=key),self.assertRaisesRegex(ValueError,'Source evidence'):
+                inspect(self.c)
+
+    def test_missing_source_support_and_wrong_aliases_are_not_guessed(self):
+        self.c.execute("DELETE FROM venue_aliases WHERE alias='ECAI'")
+        with self.assertRaisesRegex(ValueError,'aliases'):
+            inspect(self.c)
+        self.c.rollback()
+        self.c.execute('DELETE FROM submission_sources WHERE source_record_id=(SELECT id FROM source_records WHERE external_id=?)',('conf/ijcai/fixture2022',))
+        with self.assertRaisesRegex(ValueError,'Every submission'):
+            inspect(self.c)
+
+    def test_cli_plan_readonly_hash_binding_identity_and_repeat_apply(self):
+        out=Path(self.tmp.name)/'plan.json'
+        with redirect_stdout(io.StringIO()):run(self.db,out)
+        self.assertEqual(inspect(self.c)['status'],'ready')
+        raw=out.read_bytes();sha=hashlib.sha256(raw).hexdigest()
+        with patch('paper_atlas.ingestion.joint_editions.open_store')as writer:
+            with self.assertRaisesRegex(ValueError,'hash differs'):
+                run(self.db,Path(self.tmp.name)/'bad.json',plan=out,plan_sha256='0'*64)
+            writer.assert_not_called()
+        with redirect_stdout(io.StringIO()):
+            result=run(self.db,Path(self.tmp.name)/'applied.json',plan=out,plan_sha256=sha)
+            again=run(self.db,Path(self.tmp.name)/'retry.json',plan=out,plan_sha256=sha)
+        self.assertEqual(result['updated_editions'],2);self.assertEqual(again['updated_editions'],0)
+        self.assertEqual(result['plan_sha256'],sha);self.assertEqual(again['plan_sha256'],sha)
+        altered=json.loads(raw);altered['database']['path']='another.sqlite'
+        bad=Path(self.tmp.name)/'wrong-db.json';bad.write_text(json.dumps(altered))
+        with self.assertRaisesRegex(ValueError,'another database'):
+            run(self.db,Path(self.tmp.name)/'wrong-result.json',plan=bad,plan_sha256=hashlib.sha256(bad.read_bytes()).hexdigest())
+
+    def test_invalid_empty_plan_fails_before_opening_writer(self):
+        f=Path(self.tmp.name)/'empty.json';f.write_text('{}')
+        with patch('paper_atlas.ingestion.joint_editions.open_store')as writer:
+            with self.assertRaises(ValueError):
+                run(self.db,Path(self.tmp.name)/'empty-result.json',plan=f,plan_sha256=hashlib.sha256(f.read_bytes()).hexdigest())
+            writer.assert_not_called()
+
+    def test_collector_only_changes_the_two_exact_main_volume_keys(self):
+        for year in [2018,2019,2022]:
+            volume={'key':'conf/ijcai/'+str(year),'fields':{'title':['IJCAI '+str(year)],'year':[str(year)]}}
+            row={'record_type':'inproceedings','key':'conf/ijcai/One'+str(year),'fields':{'booktitle':['IJCAI'],
+                 'year':[str(year)],'crossref':[volume['key']],'title':['Original research title'],
+                 'author':['Jane Doe'],'ee':['https://doi.org/10.24963/ijcai.'+str(year)+'/1']}}
+            result,error=normalize_row(row,{volume['key']:volume},{'IJCAI','ECAI'})
+            self.assertIsNone(error)
+            self.assertEqual(result['venue'],TARGET if year in(2018,2022)else'IJCAI')
+            self.assertEqual(result['metadata']['bibliography']['booktitle'],['IJCAI'])
+            self.assertEqual(result['title'],'Original research title')
+            if year in(2018,2022):
+                self.assertIn('joint_edition_evidence',result['metadata'])
+                row['fields']['booktitle']=['IJCAI (Workshop)']
+                scoped,_=scope(row,volume)
+                self.assertEqual(scoped['venue'],'IJCAI')
+                self.assertEqual(scoped['track'],'workshop')
+
+
+@unittest.skipUnless(os.environ.get('PAPER_ATLAS_TEST_PG_SERVICE'),'Requires approved remote PostgreSQL service')
+class PostgreSQLJointTests(unittest.TestCase):
+    def setUp(self):
+        self.service=os.environ['PAPER_ATLAS_TEST_PG_SERVICE'];self.target='pa_joint_'+uuid.uuid4().hex[:12]
+        self.tmp=tempfile.TemporaryDirectory();self.addCleanup(self.tmp.cleanup)
+        self.db=Path(self.tmp.name)/'sample.sqlite';s=Store(self.db);seed(s);s.conn.close()
+        self.addCleanup(self.cleanup)
+        with redirect_stdout(io.StringIO()):migrate(self.db,self.service,Path(self.tmp.name)/'migration.json',schema=self.target,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.target,)).fetchall():
+                c.execute('DROP SCHEMA '+quote(name)+' CASCADE')
+
+    def test_native_repair_is_idempotent_keeps_codec_and_provenance(self):
+        p=Path(self.tmp.name)/'plan.json'
+        with redirect_stdout(io.StringIO()):
+            before=run(None,p,pg_service=self.service,pg_schema=self.target)
+            after=run(None,Path(self.tmp.name)/'applied.json',pg_service=self.service,pg_schema=self.target,plan=p,plan_sha256=hashlib.sha256(p.read_bytes()).hexdigest())
+        self.assertEqual(after['content_fingerprints'],before['content_fingerprints'])
+        with closing(open_store(pg_service=self.service,pg_schema=self.target).conn)as c:
+            self.assertEqual(apply_plan(c,before)['updated_editions'],0)
+            self.assertEqual(c.execute('SELECT abstract FROM papers ORDER BY id LIMIT 1').fetchone()[0],'Abstract\x00with\x01data')
+
+    def test_native_stale_source_plan_preserves_both_original_memberships(self):
+        with closing(open_store(pg_service=self.service,pg_schema=self.target).conn)as c:
+            before=plan(c)
+            c.execute("UPDATE source_records SET title='Changed' WHERE external_id=?",('conf/ijcai/fixture2022',));c.commit()
+            with self.assertRaisesRegex(ValueError,'Stale repair plan'):
+                apply_plan(c,before)
+            c.rollback()
+            self.assertEqual(inspect(c)['status'],'ready')

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_postgres_ingestion.py

@@ -0,0 +1,298 @@
+from contextlib import closing, redirect_stdout
+import io
+import json
+import os
+from pathlib import Path
+import sqlite3
+import tempfile
+from types import SimpleNamespace
+import unittest
+from unittest.mock import patch
+import uuid
+import zipfile
+
+from paper_atlas.db.backend import open_store, open_read_connection
+from paper_atlas.db.store import Store
+from paper_atlas.ingestion import arxiv
+from paper_atlas.ingestion.backfill import backfill
+from paper_atlas.ingestion.records import ingest_file
+from paper_atlas.postgres.client import connect, ReadConnection
+from paper_atlas.postgres.migration import migrate
+from paper_atlas.postgres.schema import quote
+
+
+class BackendSelectionTests(unittest.TestCase):
+    def test_ambiguous_targets_fail_before_opening_any_database(self):
+        for kwargs in ({}, {'db': 'example', 'pg_service': 'example'}, {'db': 'example', 'pg_schema': 'research'}):
+            with self.assertRaisesRegex(ValueError, 'Choose exactly one'):
+                open_store(**kwargs)
+
+    def test_readonly_audit_does_not_create_an_absent_sqlite_database(self):
+        with tempfile.TemporaryDirectory() as tmp:
+            path = Path(tmp) / 'absent.sqlite'
+            with self.assertRaisesRegex(ValueError, 'Existing database'):
+                open_read_connection(path)
+            self.assertFalse(path.exists())
+
+
+@unittest.skipUnless(os.environ.get('PAPER_ATLAS_TEST_PG_SERVICE'), 'Requires the approved remote PostgreSQL test service')
+class PostgreSQLIngestionTests(unittest.TestCase):
+    def setUp(self):
+        self.service = os.environ['PAPER_ATLAS_TEST_PG_SERVICE']
+        self.schema = 'pa_write_' + uuid.uuid4().hex[:12]
+        self.temp = tempfile.TemporaryDirectory()
+        self.addCleanup(self.temp.cleanup)
+        self.root = Path(self.temp.name)
+        source = self.root / 'seed.sqlite'
+        store = Store(source)
+        paper, record = store.record('arxiv', '2401.00001', 'https://arxiv.org/abs/2401.00001',
+                                     'Existing reference paper', 'Existing abstract', [])
+        store.decision(paper, record, 'ICLR', 2024, 'main', 'seed', 'https://example.org/seed', 'rejected', 'Reject')
+        store.conn.execute("INSERT INTO paper_merges(former_paper_id,surviving_paper_id,reason) VALUES(900,?,'fixture')", (paper,))
+        store.conn.execute("INSERT INTO submission_merges VALUES(5000,1,'fixture',CURRENT_TIMESTAMP)")
+        store.conn.execute("INSERT INTO decision_merges VALUES(6000,1,'fixture',CURRENT_TIMESTAMP)")
+        store.conn.execute("UPDATE sqlite_sequence SET seq=1000 WHERE name='papers'")
+        store.conn.commit(); store.conn.close()
+        self.addCleanup(self.cleanup)
+        with redirect_stdout(io.StringIO()):
+            migrate(source, self.service, self.root / 'migration.json', schema=self.schema, min_free_gib=0)
+
+    def cleanup(self):
+        with connect(self.service) as pg:
+            names = pg.execute('SELECT nspname FROM pg_namespace WHERE starts_with(nspname,%s)', (self.schema,)).fetchall()
+            for name, in names:
+                pg.execute('DROP SCHEMA ' + quote(name) + ' CASCADE')
+
+    def store(self):
+        return open_store(pg_service=self.service, pg_schema=self.schema)
+
+    def row(self, source='openreview_snapshot', external='forumNew1', status='rejected', year=2025, venue='ICLR'):
+        return {'source': source, 'external_id': external, 'url': 'https://example.org/' + external,
+                'venue': venue, 'year': year, 'track': 'main', 'status': status,
+                'title': 'A newly observed shared paper', 'abstract': 'Before\x00survivor and literal\x01\x02',
+                'authors': ['Jane Doe', 'John Smith'], 'identifiers': {'arxiv': '2501.99999'},
+                'observed_at': str(year) + '-01-20T00:00:00Z'}
+
+    def file(self, name, rows, count=None):
+        path = self.root / (name + '.jsonl')
+        path.write_text(''.join(json.dumps(r, ensure_ascii=False) + '\n' for r in rows))
+        first = rows[0]
+        manifest = {key: first[key] for key in ('source', 'venue', 'year', 'track')}
+        manifest.update(parser_version=2, list_url='https://example.org/' + name,
+                        count=len(rows) if count is None else count, missing_abstract_count=0, status='complete')
+        path.with_suffix('.manifest.json').write_text(json.dumps(manifest))
+        return path
+
+    def ingest(self, path):
+        with redirect_stdout(io.StringIO()):
+            ingest_file(None, path, pg_service=self.service, pg_schema=self.schema)
+
+    def test_native_writes_keep_nul_historical_ids_and_later_acceptance(self):
+        store = self.store()
+        try:
+            paper, record = store.record('public', 'new-forum', 'https://example.org/new', 'New research identity',
+                                         'Left\x00right\x01\x02', [], {'arxiv': '2501.99999'})
+            self.assertGreater(paper, 1000)
+            store.decision(paper, record, 'ICLR', 2025, 'main', 'new-forum', 'https://example.org/reject', 'rejected', 'Reject')
+            _, second = store.record('official', 'new-pub', 'https://example.org/pub', 'Changed title', None, [], {'arxiv': '2501.99999'})
+            store.decision(paper, second, 'COLM', 2026, 'main', 'new-pub', 'https://example.org/accept', 'accepted', 'Accept')
+            store.conn.commit()
+            self.assertEqual(store.conn.execute('SELECT abstract FROM papers WHERE id=?', (paper,)).fetchone()[0], 'Left\x00right\x01\x02')
+            ids = store.conn.execute('SELECT s.id,d.id FROM submissions s JOIN decisions d ON d.submission_id=s.id WHERE s.paper_id=?', (paper,)).fetchall()
+            self.assertEqual(len(ids), 2)
+            self.assertTrue(all(sid > 5000 and did > 6000 for sid, did in ids))
+        finally:
+            store.conn.close()
+        reader = ReadConnection(self.service, self.schema)
+        try:
+            from paper_atlas.querying import known_acceptances
+            self.assertEqual(known_acceptances(reader, paper)[0]['venue'], 'COLM')
+        finally:
+            reader.close()
+
+    def test_file_import_is_idempotent_and_refreshes_only_own_tags(self):
+        row = self.row(); row['tags'] = [{'namespace': 'openreview_snapshot:area', 'value': 'theory'}]
+        path = self.file('reject', [row]); self.ingest(path)
+        store = self.store()
+        try:
+            paper = store.conn.execute("SELECT paper_id FROM identifiers WHERE scheme='arxiv' AND value='2501.99999'").fetchone()[0]
+            store.conn.execute("INSERT INTO tags(namespace,value) VALUES('custom:topic','manual') ON CONFLICT DO NOTHING")
+            tag = store.conn.execute("SELECT id FROM tags WHERE namespace='custom:topic'").fetchone()[0]
+            store.conn.execute('INSERT INTO paper_tags VALUES(?,?,NULL,?)', (paper, tag, 'manual'))
+            store.conn.commit()
+        finally:
+            store.conn.close()
+        row['tags'][0]['value'] = 'learning'; self.ingest(self.file('reject', [row]))
+        self.ingest(self.file('accept', [self.row('official', 'published', 'accepted', 2026, 'COLM')]))
+        store = self.store()
+        try:
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM papers').fetchone()[0], 2)
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM submissions WHERE paper_id=?', (paper,)).fetchone()[0], 2)
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM decisions d JOIN submissions s ON s.id=d.submission_id WHERE s.paper_id=?', (paper,)).fetchone()[0], 2)
+            tags = set(store.conn.execute('SELECT t.namespace,t.value FROM paper_tags p JOIN tags t ON t.id=p.tag_id WHERE p.paper_id=?', (paper,)))
+            self.assertEqual(tags, {('custom:topic', 'manual'), ('openreview_snapshot:area', 'learning')})
+        finally:
+            store.conn.close()
+
+    def test_bad_manifest_rolls_back_whole_file_and_keeps_failed_run(self):
+        with self.assertRaisesRegex(ValueError, 'Manifest count'):
+            self.ingest(self.file('bad', [self.row()], count=2))
+        store = self.store()
+        try:
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM papers').fetchone()[0], 1)
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM source_records').fetchone()[0], 1)
+            self.assertEqual(store.conn.execute('SELECT status,records_seen,records_written FROM ingest_runs').fetchone(), ('failed', 1, 0))
+        finally:
+            store.conn.close()
+
+    def test_changed_file_hash_rolls_back_data_before_commit(self):
+        path = self.file('changed', [self.row()])
+        manifest = json.loads(path.with_suffix('.manifest.json').read_text())
+        manifest['file_sha256'] = '0' * 64
+        path.with_suffix('.manifest.json').write_text(json.dumps(manifest))
+        with self.assertRaisesRegex(ValueError, 'hash differs'):
+            self.ingest(path)
+        store = self.store()
+        try:
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM papers').fetchone()[0], 1)
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM source_records').fetchone()[0], 1)
+            self.assertEqual(store.conn.execute('SELECT status,records_written FROM ingest_runs').fetchone(), ('failed', 0))
+        finally:
+            store.conn.close()
+
+    def test_writer_lock_survives_commit_and_rollback_but_readers_continue(self):
+        first = self.store()
+        try:
+            first.conn.commit()
+            with self.assertRaisesRegex(RuntimeError, 'Another writer'):
+                self.store()
+            first.conn.rollback()
+            with self.assertRaisesRegex(RuntimeError, 'Another writer'):
+                self.store()
+            reader = ReadConnection(self.service, self.schema)
+            try:
+                self.assertEqual(reader.execute('SELECT count(*) FROM papers').fetchone()[0], 1)
+            finally:
+                reader.close()
+        finally:
+            first.conn.close()
+        reopened = self.store(); reopened.conn.close()
+
+    def test_unpublished_schema_is_rejected_without_bootstrapping_tables(self):
+        staging = self.schema + '_unfinished'
+        with connect(self.service) as pg:
+            pg.execute('CREATE SCHEMA ' + quote(staging))
+        with self.assertRaisesRegex(RuntimeError, 'published dataset'):
+            open_store(pg_service=self.service, pg_schema=staging)
+        with connect(self.service) as pg:
+            self.assertEqual(pg.execute('SELECT count(*) FROM information_schema.tables WHERE table_schema=%s', (staging,)).fetchone()[0], 0)
+
+    def test_negative_preflight_uses_current_pg_attempt_and_blocks_stale_import(self):
+        import hashlib
+        from paper_atlas.collectors.public_openreview import SOURCE, REVISION
+        from paper_atlas.audits.public_openreview import audit
+        row = self.row(SOURCE, 'negativeForum')
+        row['identifiers']['openreview'] = 'negativeForum'
+        row['metadata'] = {'evidence_kind': 'curated_public_status_assertion',
+                           'source_revision': REVISION, 'original_decision_signature_verified': False}
+        path = self.file('negative', [row])
+        manifest = json.loads(path.with_suffix('.manifest.json').read_text())
+        manifest.update(file=str(path), file_sha256=hashlib.sha256(path.read_bytes()).hexdigest())
+        path.with_suffix('.manifest.json').write_text(json.dumps(manifest))
+        inventory = self.root / 'negative-inventory.json'
+        inventory.write_text(json.dumps({'source': SOURCE, 'revision': REVISION,
+                                        'normalized_records': 1, 'files': [manifest]}))
+        # An acceptance in another conference does not block this rejection.
+        self.ingest(self.file('other-conference', [self.row('official', 'acceptedElsewhere', 'accepted', 2026, 'COLM')]))
+        def preflight(name):
+            with redirect_stdout(io.StringIO()):
+                return audit(None, inventory, self.root / (name + '.json'), pg_service=self.service, pg_schema=self.schema)
+        first = preflight('before')
+        self.assertEqual(first['status'], 'ready_for_serial_import')
+        self.assertEqual(first['engine'], 'postgresql')
+        self.assertEqual(first['schema'], self.schema)
+        self.ingest(path)
+        self.assertEqual(preflight('existing')['existing_same_edition_attempts'], 1)
+        store = self.store()
+        try:
+            paper, record = store.conn.execute('''SELECT r.paper_id,r.id FROM source_records r
+                JOIN sources s ON s.id=r.source_id WHERE s.name=? AND r.external_id=?''', (SOURCE, row['external_id'])).fetchone()
+            store.decision(paper, record, 'ICLR', 2025, 'main', row['external_id'],
+                           'https://example.org/formal', 'accepted', 'Accept', observed_at='2027-01-01T00:00:00Z')
+            # Equal observation time, but an explicitly earlier decision date.
+            # NULL dates must fall back to observed_at, not the empty string.
+            store.decision(paper, record, 'ICLR', 2025, 'main', row['external_id'],
+                           'https://example.org/older', 'rejected', 'Reject', observed_at='2027-01-01T00:00:00Z',
+                           decision_date='2026-12-31T00:00:00Z')
+            store.conn.commit()
+        finally:
+            store.conn.close()
+        blocked = preflight('blocked')
+        self.assertEqual(blocked['status'], 'blocked')
+        self.assertEqual(blocked['conflicts'][0]['existing_status'], 'accepted')
+        self.assertEqual(blocked['files'], [])
+        # The earlier ready report cannot authorize overwriting current status.
+        with self.assertRaisesRegex(ValueError, 'conflicts with current'):
+            self.ingest(path)
+        reader = open_read_connection(pg_service=self.service, pg_schema=self.schema)
+        try:
+            self.assertEqual(reader.execute('SHOW transaction_read_only').fetchone()[0], 'on')
+            from paper_atlas.db.submissions import latest_status
+            sid = reader.execute("SELECT submission_id FROM submission_identifiers WHERE scheme='openreview' AND value=?", ('negativeForum',)).fetchone()[0]
+            self.assertEqual(latest_status(reader, sid), 'accepted')
+        finally:
+            reader.close()
+
+    def test_backfill_merges_evidence_without_reimporting_or_losing_history(self):
+        files = []
+        for source, external, title in [('official', 'legacy', 'First bibliographic name'), ('openreview_snapshot', 'forum', 'Renamed submission')]:
+            row = self.row(source, external, 'accepted')
+            row.update(title=title, authors=[], identifiers={}, evidence_url='https://example.org/same-decision')
+            path = self.file(source, [row]); self.ingest(path)
+            row['identifiers'] = {'openreview': 'sharedForum'}
+            self.file(source, [row]); files.append(path)
+        with redirect_stdout(io.StringIO()):
+            result = backfill(None, files, pg_service=self.service, pg_schema=self.schema)
+        self.assertEqual((result['submissions_before'], result['submissions_after']), (3, 2))
+        store = self.store()
+        try:
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM papers').fetchone()[0], 2)
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM decision_sources').fetchone()[0], 3)
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM decision_merges').fetchone()[0], 2)
+        finally:
+            store.conn.close()
+
+    def test_arxiv_chunk_resume_preserves_committed_ids_and_category_evidence(self):
+        archive = self.root / 'arxiv.zip'
+        rows = [{'id': f'2501.{i:05d}', 'title': f'Checkpoint paper {i}', 'abstract': 'Metadata abstract',
+                 'authors': [], 'categories': 'cs.AI'} for i in range(1, 502)]
+        with zipfile.ZipFile(archive, 'w') as zipped:
+            zipped.writestr('arxiv-metadata-oai-snapshot.json', ''.join(json.dumps(r) + '\n' for r in rows))
+        calls = 0
+        original = arxiv.refresh_categories
+        def interrupt(*args):
+            nonlocal calls
+            calls += 1
+            if calls == 501:
+                raise RuntimeError('controlled interruption')
+            return original(*args)
+        with patch('paper_atlas.ingestion.arxiv.shutil.disk_usage', return_value=SimpleNamespace(free=8 * 1024**3)), \
+             patch('paper_atlas.ingestion.arxiv.refresh_categories', side_effect=interrupt), redirect_stdout(io.StringIO()):
+            with self.assertRaisesRegex(RuntimeError, 'controlled interruption'):
+                arxiv.ingest(None, archive, pg_service=self.service, pg_schema=self.schema)
+        store = self.store()
+        try:
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM papers').fetchone()[0], 501)
+            preserved = store.conn.execute("SELECT paper_id FROM identifiers WHERE scheme='arxiv' AND value='2501.00001'").fetchone()[0]
+        finally:
+            store.conn.close()
+        with redirect_stdout(io.StringIO()):
+            arxiv.ingest(None, archive, pg_service=self.service, pg_schema=self.schema)
+        store = self.store()
+        try:
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM papers').fetchone()[0], 502)
+            self.assertEqual(store.conn.execute("SELECT paper_id FROM identifiers WHERE scheme='arxiv' AND value='2501.00001'").fetchone()[0], preserved)
+            self.assertEqual(store.conn.execute('SELECT count(*) FROM paper_tags').fetchone()[0], 501)
+            self.assertEqual(store.conn.execute('SELECT status,records_written FROM ingest_runs').fetchone(), ('complete', 501))
+        finally:
+            store.conn.close()

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()